《跟著名師學電腦Excel應用入門實例》課件-第12章

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

學習情境

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

編排效果

掌握技能

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

使用時間型、日期型函數(shù)。

應用邏輯運算類函數(shù)。

快速填充公式。

設(shè)置網(wǎng)格線與零值。

輸入數(shù)據(jù)

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

2.按照實例輸入文本。

3.單擊“全選”按鈕。

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

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

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

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

設(shè)置時間格式

1.右擊B1單元格。

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

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

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

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

插入WEEKDAY函數(shù)

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

1.單擊F1單元格。

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

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

4.在“選擇函數(shù)”列表中，選中“WEEKDAY”函數(shù)。

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

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

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

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

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

9.在“類型”預覽區(qū)中，選中“中文小寫數(shù)字”項。

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

插入NOW函數(shù)

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

1.單擊H1單元格。

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

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

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

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

秒”項。

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

添加輔助列

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

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

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

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

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

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

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

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

添加下拉列表效果

1.單擊D13單元格。

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

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

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

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

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

插入月份運算公式

一年12個月，每月的天數(shù)雖然不盡相同，但仍可按照一定的規(guī)律來計算。若是2月，并且年份能被400整除，或者年份能被4整除但不能被100整除，則該月為29天，否則為28天；若是4、6、9、11月，則該月為30天；其他月份天數(shù)為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.填充

插入日期運算公式

觀察眾多日歷可發(fā)現(xiàn)，每月的日歷最多可排列6行，即組成7?×?6的表格。而此表格的第一行第一列為需要判斷的重點，可根據(jù)每月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的日期

根據(jù)7?×?6表格的特征，前4行數(shù)據(jù)無需判斷是否會超出月份總天數(shù)，而大于28的日期則需要與查詢月份天數(shù)進行對比，且日期最大值不會超出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.輸入

隱藏輔助列

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

1.選中I和J兩列。

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

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

4.選中第2和3兩行。

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

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

7.選中B5:H11單元格。

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

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

取消零值顯示

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

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