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

實(shí)例11制作員工考勤情況統(tǒng)計(jì)表—使用模板創(chuàng)建工作表—設(shè)置錯誤提示消息—引用其他工作表的數(shù)據(jù)—設(shè)置DATEDIF函數(shù)及其參數(shù)—應(yīng)用漸變數(shù)據(jù)條格式

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

員工考勤制度是否嚴(yán)謹(jǐn)，直接關(guān)系公司的長期運(yùn)營。為了穩(wěn)定發(fā)展，更好地為小區(qū)服務(wù)，祥和小區(qū)物業(yè)公司制定了員工考勤制度，并為每位員工制作考勤卡，將正常工作時數(shù)、加班時數(shù)、病假、帶薪假都統(tǒng)計(jì)在內(nèi)，作為工資發(fā)放和年終業(yè)績評估時的參考。

編排效果

掌握技能

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

使用模板創(chuàng)建工作表。

設(shè)置錯誤提示消息。

引用其他工作表的數(shù)據(jù)。

設(shè)置DATEDIF函數(shù)及其參數(shù)。

應(yīng)用漸變數(shù)據(jù)條格式。

從模板中創(chuàng)建考勤卡

工作簿模板包含了已經(jīng)設(shè)計(jì)好的工作表，用戶可根據(jù)需要迅速生成。Excel2010包含了本地模板和網(wǎng)絡(luò)模板，本地模板不需要連接互聯(lián)網(wǎng)，通過新建即可生成具有專業(yè)特色的工作表。

1.新建Excel文檔。

2.單擊“文件”選項(xiàng)卡→“新建”命令。

3.在“可用模板”列表中，單擊“樣本模板”項(xiàng)，進(jìn)入“樣本模板”列表。3.單擊1.新建2.單擊

4.在“樣本模板”列表中，單擊“考勤卡”項(xiàng)。

5.在右側(cè)的預(yù)覽區(qū)中，單擊“創(chuàng)建”按鈕完成“考勤卡”的創(chuàng)建。4.單擊5.單擊

設(shè)置單元格格式

根據(jù)模板完成創(chuàng)建后，Excel自動新建了名為“考勤卡1”的工作簿，其中包含一個名為“考勤卡”的工作表。

1.單擊C7單元格，輸入“0111張小雨”；單擊C16單元格，輸入“1-7”。

在表格中所輸入的日期時間格式是新建工作表時模板中自帶的格式，根據(jù)需要，可重新設(shè)置。1.輸入

2.選中C16、C21:C27單元格。

3.右鍵單擊選中的單元格的任意位置，打開快捷菜單。

4.單擊“設(shè)置單元格格式”命令，打開“設(shè)置單元格格式”對話框。

5.單擊“數(shù)字”選項(xiàng)卡→“日期”分類。

6.在“類型”下拉列表中，選中“3月14日”項(xiàng)。

7.單擊“確定”按鈕返回工作表。3.右擊4.單擊2.選中

設(shè)置數(shù)據(jù)有效性

公司實(shí)行計(jì)時工資制度，正常工作時間的工資為50元，加班工資為原工資的1.5倍，病假工資為原工資的0.8倍，帶薪假工資與原工資相同。另外，員工請假不得超過2個小時，否則不予發(fā)放病假工資。

模板生成時內(nèi)含公式，因此在單元格中輸入數(shù)據(jù)，可立刻計(jì)算出該員工的計(jì)時工資。

1.輸入如圖所示的數(shù)據(jù)。1.輸入

2.選中F21:F27單元格。

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

4.在“設(shè)置”選項(xiàng)卡的“允許”欄中，選擇“整

數(shù)”項(xiàng)。

5.數(shù)據(jù)介于1、2之間，輸入最小值“1”，最大值“2”。

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

設(shè)置錯誤提示消息

1.打開“數(shù)據(jù)有效性”對話框，單擊“出錯警告”選項(xiàng)卡。

2.單擊“樣式”欄→“停止”項(xiàng)。

3.在“錯誤信息”中輸入“病假不符合規(guī)定”。

4.單擊“確定”按鈕完成設(shè)置，返回工作表。1.單擊3.輸入2.選中4.單擊

5.在F27單元格中，輸入“3”，按“Enter”鍵。5.輸入

6.當(dāng)輸入的數(shù)字不在設(shè)置范圍時，系統(tǒng)彈出“錯誤消息提示”對話框，單擊“取消”按鈕，返回工作表。6.單擊

建立副本

1.重命名“考勤卡”工作表標(biāo)簽為“考勤卡0111”。

2.右鍵單擊“考勤卡0111”工作表標(biāo)簽。

3.在打開的快捷菜單中，單擊“移動或復(fù)制”命令，打開“移動或復(fù)制工作表”對話框。3.單擊2.右擊1.重命名

4.選中“建立副本”復(fù)選框。

5.單擊“確定”按鈕完成副本的建立。4.選中5.單擊

6.重命名工作表標(biāo)簽為“考勤卡0110”。

7.將鼠標(biāo)放置于“考勤卡0111”工作表標(biāo)簽，按下“Ctrl”鍵的同時向右拖動鼠標(biāo)。當(dāng)黑色箭頭位于“考勤卡0111”工作表標(biāo)簽右側(cè)位置時，釋放鼠標(biāo)，并重命名副本工作表為“考勤卡0120”。6.重命名7.拖動

修改數(shù)據(jù)

1.?單擊工作表“考勤卡0110”，輸入圖中所示內(nèi)容。1.輸入

2.?單擊工作表“考勤卡0120”，輸入圖中所示內(nèi)容。2.輸入

3.單擊工作表標(biāo)簽欄的“插入工作表”按鈕，插入新工作表Sheet1。

4.在A1:H5單元格中分別輸入圖中所示內(nèi)容，并設(shè)置格式為自動調(diào)整列寬、居中。3.插入4.輸入

引用其他工作表的數(shù)據(jù)

1.單擊A3單元格，輸入“=”號。

2.單擊“考勤卡0110”工作表。

3.單擊C7單元格，編輯欄中出現(xiàn)“=考勤卡0110!C7”，表示引用了“考勤卡0110”工作表C7單元格中的數(shù)據(jù)。按“Enter”鍵完成輸入。3.單擊2.單擊

4.返回工作表Sheet1，單擊A3單元格，選中公式中的“C7”，按“F4”鍵切換引用方式為絕對引用。

復(fù)制絕對引用單元格

1.將鼠標(biāo)置于A3單元格的右側(cè)，填充公式至A4和A5單元格。1.填充

2.修改A4單元格的內(nèi)容為“考勤卡0111!$C$7”，A5單元格的內(nèi)容為“考勤卡0120!$C$7”。

3.單擊編輯欄的“輸入”按鈕完成公式修改。2.修改3.單擊

4.將表格中的“工時總計(jì)”、“病假”、“帶薪假”字段也進(jìn)行1～3步驟的操作，引用并填充至相應(yīng)的單元格中。其中，工時總計(jì)為“正常工作時數(shù)”與“加班時數(shù)”的總和。4.引用

插入DATEDIF函數(shù)

工齡計(jì)算經(jīng)常出現(xiàn)在Excel數(shù)據(jù)的統(tǒng)計(jì)與計(jì)算問題中，而DATEDIF函數(shù)能夠返回兩個日期之間的間隔數(shù)，返回值可自行設(shè)定為年、月或日。

1.單擊F3單元格，輸入“DATEDIF()”函數(shù)。1.輸入

2.在DATEDIF函數(shù)的括號中，單擊B3單元格引用數(shù)據(jù)。

3.繼續(xù)輸入逗號，再輸入“TO”，此時在公式的下方會自動出現(xiàn)函數(shù)提示框。

4.雙擊提示框中的“TOD-AY”函數(shù)。2.單擊3.輸入4.雙擊

5.繼續(xù)輸入逗號，再輸入“Y”，表示以年份返回B3和目前之間的差值。按“Enter”鍵完成輸入。

6.將F3單元格的公式填充至F4和F5單元格。5.輸入6.填充

插入LOOKUP函數(shù)

當(dāng)需要對兩組一一對應(yīng)的數(shù)據(jù)進(jìn)行判斷時，可使用LOOKUP函數(shù)。如員工累計(jì)工作已滿1年不滿10年的，年休假5天；已滿10年不滿20年的，年休假10天；已滿20年的，年休假15天；未滿1年的，無帶薪假。

1.單擊G3單元格，再單擊編輯欄的“插入公式”按鈕，打開“插入函數(shù)”對話框。單擊“或選擇類別”列表框的下拉箭頭，在下拉列表中選擇“查找與引用”類函數(shù)。

2.在“選項(xiàng)函數(shù)”欄中，選中“LOOKUP”函數(shù)。

3.單擊“確定”按鈕，打開“選定參數(shù)”對話框。1.單擊2.選中3.單擊

4.選中第一項(xiàng)。

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

6.單擊“Lookup_value”參數(shù)后面的編輯框，在工作表中單擊單元格F3，引用該單元格的值。

7.單擊“Loopup_vector”參數(shù)后面的編輯框，輸入數(shù)組“{0,1,10,20}”。

8.單擊“Result_vector”參數(shù)后面的編輯框，輸入數(shù)組“{0,5,10,15}”。

9.單擊“確定”按鈕完成輸入。6.引用9.單擊7.輸入8.輸入

增加小數(shù)位

1.在G3單元格中，繼續(xù)輸入公式“-E3/8”，按“Enter”鍵返回工作表。

2.將G3單元格的公式填充至G4和G5單元格。1.輸入2.填充

3.選中G3:G5單元格。

4.單擊“開始”選項(xiàng)卡→“數(shù)字”組→“增加小數(shù)位”按鈕。4.單擊3.選中

插入IF函數(shù)

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

2.繼續(xù)輸入“if”，根據(jù)公式提示欄的提示內(nèi)容，雙擊“IF”項(xiàng)，此時公式中顯示“=IF(”。1.單擊2.雙擊

3.繼續(xù)輸入“（C3-D3-E3）>56，”，表示達(dá)到優(yōu)秀的基礎(chǔ)值為每周7天，每天工作8小時，即56個工時。3.輸入

4.繼續(xù)輸入“"優(yōu)秀","良好")”，表示大于優(yōu)秀基礎(chǔ)值的為優(yōu)秀，小于等于基礎(chǔ)值的為良好。

5.單擊編輯欄中的“輸入”按鈕退出公式編輯狀態(tài)。

6.將H3單元格的公式填充至H4和H5單元格。4.輸入5.單擊6.填充

應(yīng)用單元格樣式

1.單擊A1單元格。

2.單擊“開始”選項(xiàng)卡→“樣式”組→“單元格樣式”按鈕→“標(biāo)題1