2017年10月13日 星期五

Excel-依起始日期和循環週數標示日期(MOD,INT)

網友問到:如何在 Excel 中能由指定的日期開始,依指定週數循環不同格式,該如何處理?
如下圖,本例在儲存格A2中,指定一個日期為起始日期,第一列會自動標示星期幾,再利用儲儲存格I2的循環週數,自動產生間隔週數的儲存格背景和前景色彩。
Excel-依起始日期和循環週數標示日期(MOD,INT)

【公式設計與解析】
1. 產生日期
儲存格B2:=A2+1
複製儲存格B2,貼至儲存格B2:G2。
儲存格B3:=A2+7
複製儲存格B3,貼至儲存格B3:G17。

2. 產生星期幾
儲存格A1:=A2
複製儲存格A1,貼至儲存格A1:G1。
自定儲存格數值格式:星期三格式

3. 設定日期儲存格的格式化的條件
複製儲存格B2,貼至儲存格B2:G2。
選取儲存格A2:G17,設定格式化的條件。
規則類型:使用公式來決定要格式化哪些儲存格
規則:=MOD(INT((ROW(A2)-2)/$I$2),2)=0
格式:粉紅色背景和紅色前景
Excel-依起始日期和循環週數標示日期(MOD,INT)
公式:=MOD(INT((ROW(A2)-2)/$I$2),2)=0
INT((ROW(A2)-2)/$I$2):將列號除以循環週數取其商,得到其為第幾個循環週期。
MOD(INT((ROW(A2)-2)/$I$2),2)=0:判斷前式的商是否為 2 的倍數(偶數週期數),若是,則顯示不同格式(粉紅色背景和紅色前景)。
Excel-依起始日期和循環週數標示日期(MOD,INT)
另外,
(參考下圖)如果你想要指定一個日期(儲存格I2)為起始,再依循環週數(儲存格I5)顯示不同的格式,該如何處理?
Excel-依起始日期和循環週數標示日期(MOD,INT)
1. 產生日期
儲存格A2:=TODAY()-WEEKDAY(TODAY(),1)+COLUMN(A:A)
關於公式說明,請參考 :Excel-產生最近四週的日期並標示顏色
2. 設定格式
步驟如下:
複製儲存格B2,貼至儲存格B2:G2。
選取儲存格A2:G17,設定格式化的條件。
規則類型:使用公式來決定要格式化哪些儲存格
規則:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)
格式:紅色前景
Excel-依起始日期和循環週數標示日期(MOD,INT)
公式:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)
利用二個條件來判斷是否標示為紅色前景:
(A2>=$I$2):儲存格日期要大於或等於儲存格I2。
MOD(INT((A2-$I$2)/($I$5*7)),2)=0:利用INT((A2-$I$2)/($I$5*7))計算每個儲存格日期是第幾個日期,並且位於第幾個循環週期。再利用 MOD 函數判斷是否是偶數週期,若是給予紅色前景。
Excel-依起始日期和循環週數標示日期(MOD,INT)

沒有留言:

張貼留言

檢視其他文章

好康東東