2013年7月25日 星期四

Excel-標示星期六日的年曆

有人想要用 Excel 產生一個年曆(12個月),並且能將星期六日以儲存格底色標示(參考下圖),並且調整年份時,星期六日底色會隨之改變,該如何處理?

image

將圖放大來看,每個月都以1日起始,連續產生日期至最後一天,並且只要是星期六日的日期,則以橙色底色顯示,而且每一個月跨二欄表示。

步驟1:

先在[開發人員]功能表中加入一個[微調按鈕]控制項,並將其格式設定如下:(儲存格連結於A1)

image

步驟2:

接著設定公式,指定每月的第一天:

儲存格A3:=DATE($A$1,INT(COLUMN(A:A)/2)+1,1)

INT(COLUMN(A:A)/2)+1:因為每個佔2欄,當向右複製時會產生1, 2, 3, … , 12。透過 DATE 函數組合成某年某月的第一天。

步驟3:

接著設定公式,指定每月的第二天:

儲存格A4:=IF(MONTH(A$3+ROW(1:1))>MONTH(A$3),"",A$3+ROW(1:1))

因為每個月可能有 28, 29, 30, 31 等天數,所以要讓超出最後一天的日期,顯示為空白。

A$3+ROW(1:1):當向下複製時,會產生 A$3+1、A$3+2、A$3+3、…。

MONTH(A$3+ROW(1:1))>MONTH(A$3):判斷儲存格日期的月份是否大於每月第一個儲存格的月份。如果為真,表示跨到了下一個月了。

步驟4:

接著,要設定星期六日的儲存格底色:

選取儲存格A3,設定以下的格式化規則:=WEEKDAY(A3,2)>5。(星期六為6、星期日為7)

選取儲存格B3,設定以下的格式化規則:=WEEKDAY(A3,2)>5。(和上式相同)

步驟5:

選取儲存格A3:B3,將其格式套用至儲存格A4:B4。(複製儲存格格式)

複製儲存格A4:B4,貼至儲存格A4:B33。(複製儲存格公式)

選取儲存格A3:B33,貼至儲存格A3:X33。(將一月的公式和格式,複製到一月至十二月)

結果:

如此,只要使用微調按鈕,即可改變年份,而每月的日期和星期六日的儲存格底色會隨之改變。

沒有留言:

張貼留言

檢視其他文章

好康東東