2011年12月10日 星期六

Excel-製作萬年月曆

一年又將結束,又有好多單位送來新的年曆,有時候想要製作自己想要的形式,或許利用 Excel 就可以如願。例如:在一張A4大小的紙張上,置放有兩個月的月曆(如下圖)。

【題目需求】

1. 要能產生萬年曆。

2. 一次產生兩個月。

3. 星期六、日要以不同色彩標示。

 

【利用微調按鈕調整年份和月份】

要產生萬年曆,首先要能產生年份和月份,這次以微調按鈕來調整這兩個值。

第一個按鈕來控制年份,設定最小值和最大值(可自訂),儲存格連結設定為「$C$1」(年份顯示的位置)。

輸入儲存格G2:=C1,將兩個月的年份設定成相同。

第一個按鈕來控制月份,設定最小值:1,最大值:11,儲存格連結設定為「$A$1」(月份顯示的位置)。

輸入儲存格E1:=A1,將第二個月的設定為第一個月加1。

儲存格C2:=CHOOSE(A1,"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月")

透過 CHOOSE 函數,利用儲存格A1的月份數值,對應一個中文月份名稱。

 

【產生日期和周幾】

(1) 產生日期

儲存格A3:=IF(MONTH(DATE($C$1,$A$1,ROW(1:1)))=$A$1,DAY(DATE($C$1,$A$1,ROW(1:1))),"")

DATE($C$1,$A$1,ROW(1:1)):往下複製時ROW(1:1) ROW(2:2) ROW(3:3), …,可以產生1, 2, 3, …數列。也就是說可以產生該年該月的每一個日期(年月日格式的日期)

DAY(DATE($C$1,$A$1,ROW(1:1))):取出日期中的「日」。

因為每個月的最後幾天有可能是28, 29, 30, 31,所以利用 MONTH 函數判斷該日期取出的月份如果和儲存格A1不一樣時,即已經跳至下一個月了,就將其顯示為空字串。

(2) 產生周幾

儲存格B3:=IF(A3<>"",DATE($C$1,$A$1,ROW(1:1)),"")

判斷如果A欄中的日期為空字串,則B欄也跟著顯示空字串。接著將該日期的數值格式設定為「週三」格式。

(先慢著複製往下貼上,待格式設定完成再做。)

 

【設定例假日為不同格式】

要將例假日(星期六、日)設定為不同格式,則要設定儲存格的格式化條件。

(1) 設定星期日

選取儲存格A3,設定格式化的條件:

選取「使用公式來決定要格式哪些儲存格」選項,設定如果為星期日:

編輯規則:=WEEKDAY(DATE($C$1,$A$1,ROW(1:1)),1)=1

以 WEEKDAY 函數判斷結果為1時即為星期日。(注意WEEKDAY函數的參數為1)

格式:背景:紅色,前景:紅色

(2) 設定星期六

要設定星期六的格式,做法同(1)設定星期日:

編輯規則:=WEEKDAY(DATE($C$1,$A$1,ROW(1:1)),1)=7

格式:前景:綠色

 

【產生各月的每一天】

此時,選取儲存格A3:B3,複製後貼至儲存格A33:B33(共31天的日期)。如果該月沒有的日期,將會以空白顯示。

 

【自行練習產生第2個月】

如果你已經會產生第1個月的萬年月曆,試著再產生第2個月。如果想要在一張A4紙張中顯示3個月份亦可如法炮製。

 

【延伸學習】

如果不想在列印時印出微調按紐,則在微調按鈕的[控制項格式]對話框中的[摘要資訊]標籤下,取消勾選「列印物件」。

 

【補充資料】

相關函數說明,請參閱微軟網站:

WEEKDAYhttp://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六)之間的整數。

語法:WEEKDAY(serial_number,[return_type])

serial_number:要找的日期的代表序列值。

return_type:決定傳回值類型的數字。

 

CHOOSEhttp://office.microsoft.com/zh-tw/excel-help/HP010342269.aspx

CHOOSE:使用index_num從引數值清單中傳回值。

語法:CHOOSE(index_num,value1,[value2],...)

Index_num:指定所選取的數值引數。

如果index_num1CHOOSE會傳回value1;如果為2,則CHOOSE會傳回value2;依此類推。

 

1 則留言:

  1. A32~A34:=IF(MONTH(DATE($A$2,$C$2,ROW(?:?)))=$C$2,DAY(DATE($A$2,$C$2,ROW(?:?)))) 移到 A35~A37 後 將 =IF(?=?,"?",IF(?=FALSE,"")) 複製到 A32~A34。※本意間中有出現『?』請自行更改!這樣2013年2月就不會出現29~31日,將會以空白顯示,若遇到當月有29~31日也會主動出現!

    回覆刪除

好康東東