《跟著名師學(xué)電腦Excel應(yīng)用入門實(shí)例》課件-第12章

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

學(xué)習(xí)情境

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

編排效果

掌握技能

通過本實(shí)例，將學(xué)會(huì)以下技能：

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

應(yīng)用邏輯運(yùn)算類函數(shù)。

快速填充公式。

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

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

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

2.按照實(shí)例輸入文本。

3.單擊“全選”按鈕。

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

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

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

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

設(shè)置時(shí)間格式

1.右擊B1單元格。

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

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

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

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

插入WEEKDAY函數(shù)

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

1.單擊F1單元格。

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

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

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

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

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

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

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

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

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

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

插入NOW函數(shù)

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

1.單擊H1單元格。

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

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

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

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

秒”項(xiàng)。

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

添加輔助列

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

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

3.單擊“開始”選項(xiàng)卡→“編輯”組→“填充”按鈕

→“系列”項(xiàng)。2.選中3.單擊

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

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

6.將鼠標(biāo)置于J1單元格的右下角，拖動(dòng)句柄向下填充至J12單元格。

7.選中“填充序列”項(xiàng)。5.輸入7.選中6.拖動(dòng)

添加下拉列表效果

1.單擊D13單元格。

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

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

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

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

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

插入月份運(yùn)算公式

一年12個(gè)月，每月的天數(shù)雖然不盡相同，但仍可按照一定的規(guī)律來計(jì)算。若是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))”,計(jì)算當(dāng)前選中月共有多少天。按“Enter”鍵完成計(jì)算。1.單擊2.輸入

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

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

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

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

插入日期運(yùn)算公式

觀察眾多日歷可發(fā)現(xiàn)，每月的日歷最多可排列6行，即組成7?×?6的表格。而此表格的第一行第一列為需要判斷的重點(diǎn)，可根據(jù)每月1號(hào)是否為星期一來判斷每月日期中第一個(gè)單元格的值。

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

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

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

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

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

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

公式填充

1.選中C7:H7單元格。

2.單擊“開始”選項(xiàng)卡→“編輯”組→“填充”按鈕

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

3.選中C7:H9單元格。

4.單擊“開始”選項(xiàng)卡→“編輯”組→“填充”按鈕

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

5.選中B7:B9單元格。

6.單擊“開始”選項(xiàng)卡→“編輯”組→“填充”按鈕

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

判斷大于28的日期

根據(jù)7?×?6表格的特征，前4行數(shù)據(jù)無需判斷是否會(huì)超出月份總天數(shù)，而大于28的日期則需要與查詢?cè)路萏鞌?shù)進(jìn)行對(duì)比，且日期最大值不會(huì)超出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單元格中的公式復(fù)制到D10:H10以及C11單元格中。3.拖動(dòng)

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

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

隱藏輔助列

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

1.選中I和J兩列。

2.右鍵單擊選中列的列標(biāo)。

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

4.選中第2和3兩行。

5.單擊“開始”選項(xiàng)卡→“單元格”組→“格式”按鈕。

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

7.選中B5:H11單元格。

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

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

取消零值顯示

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

1.單擊“文件”選項(xiàng)卡→“選項(xiàng)”命令，打開“Excel選項(xiàng)”對(duì)話框，單擊“高