在 Excel 中大家都很熟悉如果要做到動態色彩效果可以使用「設定格式化條件」功能。如下圖,有一個含有日期的資料表,雖然月份都是由小到大,但是可能有些月份有,有些月份沒有,如何設定才能讓不同月份有所區隔。

 

(一) 月份必須連續,且每一月份皆有,利用儲存格底色分隔月份。(可以用在跨年份)

1. 選取儲存格A3:B27。設定儲存格底色:橙色。

2. 設定格式化條件為:使用公式來決定要格式化哪些儲存格。

3. 輸入公式:=MOD(MONTH($A3),2)=0

4. 設定儲存格底色:黃色。

做法:設定偶數月份(2, 4, 6, 8, 10, 12)為黃色,奇數月份為原來底色。

 

(二) 月份不必連續,不一定每一個月份皆有,利用儲存格底線分隔月份。(可以用在跨年份)

1. 選取儲存格D3:E27。設定儲存格底色:藍色。

2. 設定格式化條件為:使用公式來決定要格式化哪些儲存格。

3. 輸入公式:=MONTH($D3)<>MONTH($D4)

4. 設定儲存格下底線:綠色單線。

做法:只要這個儲存格和下個儲存格的月份不同,即顯示儲存格底線。

 

(三) 月份不必連續,不一定每一個月份皆有,利用儲存格底色分隔月份。(限同一年的月份)

1. 選取儲存格G3:H27。設定儲存格底色:綠色。

2. 設定格式化條件為:使用公式來決定要格式化哪些儲存格。

3. 輸入公式:

=MOD(SUMPRODUCT((FREQUENCY(MONTH($G$3:$G3),
MONTH($G$3:$G3))>0)*1),2)=0

4. 設定儲存格底色:黃色。

做法:

FREQUENCY(MONTH($G$3:$G3),MONTH($G$3:$G3):因為該公式位於 SUMPRODUCT 函數中,所以可以利用 FREQUENCY 函數找出由資料第一列往下每個月份出現的次數。

FREQUENCY(MONTH($G$3:$G3),MONTH($G$3:$G3))>0:其中「>0」為可以將有出現的月份轉為 True,沒有出現的月份轉為 False

將上式「*1」,可以將上式中的 True/False 陣列,轉換為 1/0 陣列。透過 SUMPRODUCT 函數計算個數的總和。使用 MOD 函數將該數除以 2 求得餘數來區隔偶數或奇數。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(4) 人氣()