實例12制作萬年歷—使用時間型、日期型函數—應用邏輯運算類函數—快速填充公式—設置網格線與零值

學習情境

電子萬年歷具備查詢方便、更新及時、可個性化等優點，目前已逐漸替代了紙質日歷。退休的張老師也希望使用Excel做一份萬年歷，方便查看日期。

編排效果

掌握技能

通過本實例，將學會以下技能：

使用時間型、日期型函數。

應用邏輯運算類函數。

快速填充公式。

設置網格線與零值。

輸入數據

1.新建Excel文檔，將其重命名為“萬年歷”。

2.按照實例輸入文本。

3.單擊“全選”按鈕。

4.單擊“開始”選項卡→“對齊方式”組→“居中”按鈕。1.新建2.輸入3.單擊4.單擊

5.選中B1:D1單元格，右擊選中區域，在懸浮工具欄中，單擊“合并后居中”按鈕。

6.單擊B1單元格，輸入“=”號進入公式編輯狀態。

7.繼續輸入“TO”，雙擊公式提示欄中的“TODAY”函數，繼續輸入“)”，按“Enter”鍵完成輸入。5.選中7.雙擊6.輸入

設置時間格式

1.右擊B1單元格。

2.在打開的快捷菜單中，單擊“設置單元格格式”命令，打開“設置單元格格式”對話框。

3.在“數字”選項卡的“分類”欄中，選中“日期”項。

4.在“類型”預覽區中，選中“二〇〇一年三月十四日”項。

5.單擊“確定”按鈕完成設置。1.右擊2.單擊

插入WEEKDAY函數

WEEKDAY函數屬于日期與時間類函數，其功能為返回某日期為星期幾。參數Serial_number表示日期；Return_type用于確定返回值類型的數字。

1.單擊F1單元格。

2.單擊編輯欄中的“插入函數”按鈕，打開“插入函數”對話框。

3.在“或選擇類別”列表框中，選中“日期與時間”類函數。

4.在“選擇函數”列表中，選中“WEEKDAY”函數。

5.單擊“確定”按鈕，打開“函數參數”對話框。

6.單擊“Serial_number”參數后的編輯框，引

用B1單元格；單擊“Return_type”參數后的編輯框，輸入“2”。

7.單擊“確定”按鈕完成輸入，再打開“設置單元格格式”對話框。

8.?在“數字”選項卡的“分類”欄中，選中“特殊”項。

9.在“類型”預覽區中，選中“中文小寫數字”項。

10.單擊“確定”按鈕返回工作表。

插入NOW函數

NOW函數屬于日期與時間類函數，其功能為返回日期時間格式的當前日期和時間。該函數無參數。

1.單擊H1單元格。

2.單擊“公式”選項卡→“函數庫”組→“日期和時間”按鈕。

3.在下拉列表中，選中“NOW”函數。1.單擊3.選中2.單擊

4.右鍵單擊H1單元格，打開“設置單元格格式”對話框。在“數字”選項卡的“分類”欄中，選中“時間”項。

5.在“類型”預覽區中，選中“13?時?30?分?35

秒”項。

6.單擊“確定”按鈕完成設置。4.選中6.單擊5.選中

添加輔助列

1.單擊I1單元格，輸入“1900”。1.輸入

2.按住“Shift”鍵不放，向下拖動表格至151行，單擊I151單元格，選中I1:I151。

3.單擊“開始”選項卡→“編輯”組→“填充”按鈕

→“系列”項。2.選中3.單擊

4.在打開的“序列”對話框中，設置序列產生的位置和類型，單擊“確定”按鈕完成輸入。4.單擊

5.單擊J1單元格，輸入“1”。

6.將鼠標置于J1單元格的右下角，拖動句柄向下填充至J12單元格。

7.選中“填充序列”項。5.輸入7.選中6.拖動

添加下拉列表效果

1.單擊D13單元格。

2.單擊“數據”選項卡→“數據工具”組→“數據有效性”按鈕，打開“數據有效性”對話框。

3.單擊“允許”欄列表框右側的箭頭，在下拉列表中選中“序列”項。

4.單擊“來源”欄的“選擇數據”按鈕，縮小對話框。1.單擊2.單擊3.選中4.單擊

6.再次單擊“選擇數據”按鈕恢復對話框，單擊“確定”按鈕返回工作表。

單擊F13單元格，重復以上步驟，將“有效性條件”序列的“來源”改為J1:J12。5.拖動6.單擊

插入月份運算公式

一年12個月，每月的天數雖然不盡相同，但仍可按照一定的規律來計算。若是2月，并且年份能被400整除，或者年份能被4整除但不能被100整除，則該月為29天，否則為28天；若是4、6、9、11月，則該月為30天；其他月份天數為31天。

1.選中“1990年2月”為查詢年月，單擊A2單元格。

2.輸入公式“=IF(F13=2,IF(OR(D13/400=INT

(D13/400),AND(D13/4=INT(D13/4),D13/100<>

INT(D13/100))),29,28),IF(OR(F13=4,F13=6,F13=9,F13=11),30,31))”,計算當前選中月共有多少天。按“Enter”鍵完成計算。1.單擊2.輸入

3.在B3:H3單元格中，依次輸入星期查詢對照值“1,2,3,4,5,6,7”。

4.單擊B2單元格，輸入公式“=IF(WEEKDAY

(DATE($D$13,$F$13,1),2)=B3,1,E3)”。

5.再次單擊B2單元格，用填充句柄將B2單元格中的公式復制到C2:H2單元格中。3.輸入4.輸入5.填充

插入日期運算公式

觀察眾多日歷可發現，每月的日歷最多可排列6行，即組成7?×?6的表格。而此表格的第一行第一列為需要判斷的重點，可根據每月1號是否為星期一來判斷每月日期中第一個單元格的值。

1.單擊B6單元格，輸入公式“=IF(B2=1,1,0)”。1.輸入

2.單擊C6單元格，輸入公式“=IF(B6>0,B6+1,IF(C2=1,1,0))”。2.輸入

3.將鼠標置于C6單元格的右下角，用填充句

柄將C6單元格中的公式復制到D6:H6單元格中。3.填充

4.單擊B7單元格，輸入公式“=H6+1”。4.輸入

5.單擊C7單元格，輸入公式“=B7+1”。5.輸入

公式填充

1.選中C7:H7單元格。

2.單擊“開始”選項卡→“編輯”組→“填充”按鈕

→“向右”項，將C7單元格中的公式填充至D7:H7單元格中。

3.選中C7:H9單元格。

4.單擊“開始”選項卡→“編輯”組→“填充”按鈕

→“向下”項，將C7:H7單元格中的公式填充至C8：H9單元格中。1.選中2.單擊3.選中4.單擊

5.選中B7:B9單元格。

6.單擊“開始”選項卡→“編輯”組→“填充”按鈕

→“向下”項，將B7單元格中的公式填充至D8和D9單元格中。5.選中6.單擊

判斷大于28的日期

根據7?×?6表格的特征，前4行數據無需判斷是否會超出月份總天數，而大于28的日期則需要與查詢月份天數進行對比，且日期最大值不會超出C11單元格的位置。

1.單擊B10單元格，輸入公式“=IF(H9>=$A$2,

0,H9+1)”。

2.單擊C10單元格，輸入公式“=IF(B10>=$A$2,0,IF(B10>0,B10+1,0))”。

3.使用填充句柄，將C10單元格中的公式復制到D10:H10以及C11單元格中。3.拖動

4.單擊B11單元格，輸入公式“=IF(H10>=A2,

0,IF(H10>0,H10+1,0))”。4.輸入

隱藏輔助列

表格設計完成后，可將設計過程中使用的輔助列隱藏，以免影響視覺效果。

1.選中I和J兩列。

2.右鍵單擊選中列的列標。

3.在打開的快捷菜單中，單擊“隱藏”命令，將I和J列的內容隱藏。1.選中2.右擊3.單擊

4.選中第2和3兩行。

5.單擊“開始”選項卡→“單元格”組→“格式”按鈕。

6.在下拉列表中，單擊“可見性”欄→“隱藏行”項，將第2和3兩行的內容隱藏。

7.選中B5:H11單元格。

8.單擊“開始”選項卡→“樣式”組→“套用表格格式”按鈕→“表樣式中等深淺27”項，應用表格樣式。

4.選中5.單擊6.選中7.選中8.選中

取消零值顯示

默認設置中，表格自動顯示網格線和零值，為了得到更加美觀的效果，可設置網格線和具有零值的單元格不顯示。

1.單擊“文件”選項卡→“選項”命令，打開“Excel選項”對話框，單擊“高