任務(wù)3.1 體驗(yàn)公式和函數(shù)-員工薪酬核算

辦公自動(dòng)化實(shí)用教程（第三版）經(jīng)濟(jì)科學(xué)出版社任務(wù)3.1體驗(yàn)公式和函數(shù)-員工薪酬核算

《辦公自動(dòng)化實(shí)用教程》（第三版）主編：石朝暉劉文娟學(xué)習(xí)目標(biāo)了解工資核算的業(yè)務(wù)處理流程工作表數(shù)據(jù)編輯與格式化數(shù)據(jù)有效性設(shè)置VLOOKUP、TEXT、MID、INT、TODAY、MAX、SUM、IF等函數(shù)的應(yīng)用任務(wù)引入在日常辦公中，經(jīng)常要制作一些表格，并對(duì)其中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、計(jì)算、分析，Excel提供了豐富的內(nèi)置函數(shù)以及強(qiáng)大的統(tǒng)計(jì)分析功能，足以滿足用戶的需要。某公司的財(cái)務(wù)人員每月都要根據(jù)員工的考勤情況進(jìn)行員工薪酬核算，如何快速地實(shí)現(xiàn)數(shù)據(jù)錄入與相關(guān)的數(shù)據(jù)處理呢？任務(wù)分析在公司的員工薪酬核算系統(tǒng)中，涉及到許多數(shù)據(jù)信息——例如，職工基本信息、考勤記錄、加班記錄、個(gè)稅稅率等，將這些數(shù)據(jù)放置在多個(gè)獨(dú)立的工作表中，最終生成員工工資表——多個(gè)工作表之間存在數(shù)據(jù)鏈接關(guān)系，方便信息傳遞。由于表格設(shè)計(jì)的需求，需要進(jìn)行如下的編輯、美化工作：工作表的創(chuàng)建和基本操作在單元格中輸入各種類型數(shù)據(jù)設(shè)置單元格格式設(shè)置表格的邊框和底紋應(yīng)用公式和函數(shù)進(jìn)行計(jì)算任務(wù)效果圖：任務(wù)實(shí)施函數(shù)和單元格地址引用編制“基本數(shù)據(jù)”表編制“員工信息表”編制“1月考勤表”制作“1月工資表”一、函數(shù)和單元格地址引用1.了解函數(shù)Excel2010提供了許多內(nèi)置函數(shù)，包括財(cái)務(wù)、日期與時(shí)間、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)、查找與引用、數(shù)據(jù)庫、文本、邏輯、信息、工程、多維數(shù)據(jù)集、兼容性等12類幾百種函數(shù)，為用戶對(duì)數(shù)據(jù)進(jìn)行運(yùn)算和分析帶來極大的方便。Excel2010函數(shù)由函數(shù)名、括號(hào)和參數(shù)組成。如“SUM（J3+J14）”。當(dāng)函數(shù)以公式的形式出現(xiàn)時(shí)，則應(yīng)在函數(shù)名稱前面輸入等號(hào)“=”，如“=SUM（J3+J14）”。2.單元格的引用類型在Excel中，每一個(gè)單元格都有唯一的行坐標(biāo)和列坐標(biāo)，我們將單元格的行坐標(biāo)和列坐標(biāo)稱為“單元格的引用”。在Excel中可將引用單元格分為3類，相對(duì)引用、絕對(duì)引用和混合引用。單元格的相對(duì)引用如J3，絕對(duì)引用如$J$3，混合引用如J$3或$J3。一、函數(shù)和單元格地址引用相對(duì)引用是指公式所在的單元格與公式中引用的單元格之間的位置關(guān)系是相對(duì)的，如果公式所在的單元格的位置發(fā)生了變化，那么公式中引用的單元格的位置也將隨之發(fā)生變化。如J3單元格有公式：=H3–I3，當(dāng)將公式復(fù)制到J4單元格時(shí)變?yōu)椋?H4-I4。絕對(duì)引用是指被引用的單元格與公式所在的單元格之間的位置關(guān)系是絕對(duì)的，即不管將公式粘貼到任何單元格，公式中所引用的還是原來單元格的位置。如J3單元格有公式：=$H$3–$I$3，當(dāng)將公式復(fù)制到J4單元格時(shí)仍為：=$H$3–$I$3，當(dāng)將公式復(fù)制到J14單元格時(shí)仍為：=$H$3–$I$3。混合引用是介于相對(duì)引用和絕對(duì)引用之間的一種引用，也就是說飲用單元格的行和列之中一個(gè)是相對(duì)的，一個(gè)是絕對(duì)的。混合引用有兩種：一種是行絕對(duì)、列相對(duì)，如J3單元格有公式：=H$3–I$3，當(dāng)將公式復(fù)制到J4單元格時(shí)仍為：=H$3–I$3，另一種是行相對(duì)、列絕對(duì)，如J3單元格有公式：=$H3–$I3，當(dāng)將公式復(fù)制到J4單元格時(shí)變?yōu)椋?$H4–$I4。Excel工作表是承載信息數(shù)據(jù)的主體，這里，我們將需要的不同的數(shù)據(jù)分別存儲(chǔ)在不同的工作表中，以便于數(shù)據(jù)之間的傳遞和鏈接。二、編制“基本數(shù)據(jù)”表在制作員工工資表時(shí)，需要用到很多基礎(chǔ)信息——例如，員工病事假扣款標(biāo)準(zhǔn)、加班費(fèi)用標(biāo)準(zhǔn)、通訊費(fèi)發(fā)放標(biāo)準(zhǔn)、個(gè)人稅率標(biāo)準(zhǔn)等，可以事先建立一個(gè)工作表，將上述基礎(chǔ)信息放置其中，方便取值、使用。1.新建工作簿運(yùn)行MicrosoftOfficeExcel2016程序，創(chuàng)建一個(gè)新工作簿，執(zhí)行“文件”選項(xiàng)卡中的“保存”命令，打開“另存為”對(duì)話框，將其保存為“員工工資表”。右單擊其中的sheet1工作表標(biāo)簽，在彈出的下拉列表中選擇“重命名”命令，將其命名為“基礎(chǔ)數(shù)據(jù)”。然后分別構(gòu)建不同內(nèi)容的數(shù)據(jù)。2.定義名稱為了后邊的其他表格取數(shù)方便，可以將“基礎(chǔ)數(shù)據(jù)”工作表中的部分表格內(nèi)容定義名稱。步驟一選擇A3:D8單元格區(qū)域，單擊“公式”功能區(qū)“定義的名稱”組中的“根據(jù)所選內(nèi)容創(chuàng)建”命令，彈出如圖3.3所示對(duì)話框，勾選“首行”復(fù)選框，單擊“確定”按鈕完成設(shè)置。按照上述操作方法，如表3.1所示為其他表格內(nèi)容定義名稱。單元格區(qū)域 名稱A3:A8 最高學(xué)歷B3:B8 職稱C3:C8 部門D3:D8 職務(wù)V3:V10 稅率W3:W10 速算扣除數(shù)2.定義名稱步驟二選擇F3:H8單元格區(qū)域，單擊“公式”功能區(qū)“定義的名稱”組中的“定義名稱”命令，彈出“新建名稱”對(duì)話框，在“名稱”框中輸入“工資標(biāo)準(zhǔn)”，單擊“確定”按鈕完成設(shè)置。單擊“公式”功能區(qū)“定義的名稱”組中的“名稱管理器”命令，可以打開“名稱管理器”對(duì)話框，查看、編輯和刪除定義好的各種名稱。3.修改工作表標(biāo)簽顏色右單擊“基礎(chǔ)數(shù)據(jù)”工作表標(biāo)簽，從彈出的列表中選擇“工作表標(biāo)簽顏色…”命令，設(shè)置工作表標(biāo)簽顏色為“紅色”。4.取消網(wǎng)格線為了更清晰地顯示表格中的文本和數(shù)字，可以通過取消“視圖”功能區(qū)中“網(wǎng)格線”復(fù)選框的方法來實(shí)現(xiàn)，這樣整個(gè)工作表就會(huì)呈現(xiàn)白紙狀態(tài)。三、編制“員工信息表”員工信息表的結(jié)構(gòu)包括“序號(hào)”、“姓名”、“性別”、“最高學(xué)歷”、“職稱”、“部門”、“職務(wù)”、“聯(lián)系號(hào)碼”、“身份證號(hào)碼”、“入職時(shí)間”、“出生日期”和“工齡”等列字段。在已經(jīng)建好的“基本數(shù)據(jù)”工作表右側(cè)單擊“新工作表”按鈕（“⊕”符號(hào)），新增一個(gè)空白工作表sheet2，然后將其重命名為“員工信息表”。首先在相應(yīng)位置輸入表頭和標(biāo)題行內(nèi)容。1.輸入文本型數(shù)據(jù)由于Excel單元格內(nèi)默認(rèn)的格式是“常規(guī)”，在“序號(hào)”列中直接填入“001”、“002”、……樣式編號(hào)的時(shí)候，Excel會(huì)將其處理成為“1”、“2”、……數(shù)字格式，因此需要事先將該列設(shè)置為“文本”格式然后再輸入編號(hào)。（1）序號(hào)選擇A列，依次單擊“開始”功能區(qū)“數(shù)字”組中的“數(shù)字格式”選項(xiàng)右側(cè)的下三角按鈕，從彈出的列表中選擇“文本”選項(xiàng)。然后在A4單元格中輸入“001”，選擇A4單元格，拖動(dòng)其填充句柄向下復(fù)制該序號(hào)直至出現(xiàn)“015”為止（即A18單元格），完成“序號(hào)”列的填充。1.輸入文本型數(shù)據(jù)（2）身份證號(hào)碼同樣，當(dāng)向Excel單元格內(nèi)輸入的數(shù)字長度超過11位時(shí)就不能正常顯示——顯示為科學(xué)計(jì)數(shù)法；而身份證的長度為18位，結(jié)果為后三位顯示為“000”從而無法從中正確提取信息。因此，在填充“身份證號(hào)碼”列時(shí)，也必須事先將該列（即：I列））設(shè)置為“文本”格式，然后再手工依次輸入每位員工的身份證號(hào)碼。另外，像“郵政編碼”、“聯(lián)系電話”等數(shù)字信息通常不需要參加公式計(jì)算，也可以先將該列設(shè)置為“文本”格式，然后再行輸入。而像“姓名”列這樣的信息，屬于純文本數(shù)值，沒有規(guī)律，需要手動(dòng)逐一輸入。2.數(shù)據(jù)驗(yàn)證在Excel表格的某些數(shù)據(jù)列（例如：員工的“性別”）中，是由一系列固定內(nèi)容構(gòu)成的（例如：“男”或“女”），且不允許出現(xiàn)超出指定內(nèi)容范圍之外的其他數(shù)值輸入，因此可以借助于“數(shù)據(jù)驗(yàn)證”功能來完成輸入工作。（1）性別選擇C列（即：“性別”列），依次單擊“數(shù)據(jù)”功能區(qū)“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗(yàn)證”命令，彈出如圖3.8所示“數(shù)據(jù)驗(yàn)證”對(duì)話框。在“允許”框內(nèi)選擇“序列”，在“來源”框中輸入“男,女”（注意：分隔符使用英文逗號(hào)或分號(hào)）。單擊“確定”按鈕完成設(shè)置，單擊C4單元格就可以看到其右側(cè)出現(xiàn)了一個(gè)下三角按鈕，從下拉列表中列出了“男”和“女”兩個(gè)選項(xiàng)，按照實(shí)際情況，選擇“男”輸入到C4單元格中，其他“性別”列數(shù)據(jù)按照相同的方法依次輸入。（2）最高學(xué)歷在“基礎(chǔ)數(shù)據(jù)”表中，事先已經(jīng)構(gòu)建了有關(guān)“最高學(xué)歷”的全部種類名稱，可以在上述對(duì)話框中的“來源”框中直接引用這些內(nèi)容。選擇D列（即：“最高學(xué)歷”列），打開上述“數(shù)據(jù)驗(yàn)證”對(duì)話框，在“來源”框中引用“基礎(chǔ)數(shù)據(jù)”中的相應(yīng)單元格地址。單擊“確定”按鈕完成設(shè)置。單擊D4單元格右側(cè)的下三角按鈕，從其下拉列表中即可看到數(shù)據(jù)驗(yàn)證的全部設(shè)置內(nèi)容。按照實(shí)際情況，選擇“本科”輸入到D4單元格中。2.數(shù)據(jù)驗(yàn)證（3）其他數(shù)據(jù)驗(yàn)證按照上述方法，分別為“職稱”、“部門”、“職務(wù)”、“聯(lián)系號(hào)碼”和“身份證號(hào)碼”列設(shè)置相應(yīng)的數(shù)據(jù)驗(yàn)證，并完成相應(yīng)的數(shù)據(jù)填充。如表3.2所示。列標(biāo)簽

列 允許值 數(shù)據(jù)

來源/數(shù)據(jù)關(guān)系性別

C 序列 男,女

手動(dòng)輸入最高學(xué)歷 D 序列 研究生,本科,?？?/p>

=最高學(xué)歷職稱

E 序列 高級(jí),中級(jí),初級(jí),無

=職稱部門

F 序列 研發(fā)部,銷售部,行政部,財(cái)務(wù)部,生產(chǎn)車間=部門職務(wù)

G 序列 總經(jīng)理,部門經(jīng)理,管理人員,銷售人員,基本生產(chǎn)人員=職務(wù)聯(lián)系號(hào)碼 H 文本長度 11 等于身份證號(hào)碼 I 文本長度 18 等于3.輸入日期型數(shù)據(jù)——自定義“入職日期”格式在Excel中，日期型數(shù)值有兩種合法格式：例如“2010/4/20”和“2020-4-20”（分別使用斜杠“/”或短橫“-”分隔年月日），在“員工信息表”中的“入職日期”列，我們使用自定義格式規(guī)范它的顯示方式。右單擊J列，選擇下拉列表中的“設(shè)置單元格格式…”命令，打開“設(shè)置單元格格式”對(duì)話框，切換至“數(shù)字”選項(xiàng)卡，選擇“自定義”選項(xiàng)，然后在右側(cè)的“類型”框中輸入“yyyy-mm-dd”數(shù)字格式（使其整齊美觀）。4.根據(jù)身份證號(hào)碼獲取信息（1）出生日期在K列中，需要通過員工身份證號(hào)碼計(jì)算出每位員工的“出生日期”，在此可以利用文本類函數(shù)MID和TEXT來實(shí)現(xiàn)。在K4單元格中構(gòu)建如下函數(shù)公式：=TEXT(MID(I4,7,8),"0000-00-00")按下Enter鍵，得到計(jì)算結(jié)果：“1982-13-12”。向下復(fù)制公式直至該列最后一行的單元格內(nèi)。【函數(shù)解讀】：MID（文本類）函數(shù)：在一個(gè)文本字符串的指定起始位置開始截取指定長度的字符串。=MID(I4,7,8)的語法格式為：將I4單元格（身份證號(hào)碼）中的文本字符串自第7位開始，截取8位文本長度，得到文本字符串“19820312”。TEXT（文本類）函數(shù)：將數(shù)值轉(zhuǎn)換為按照指定格式表示的文本。=TEXT(MID(I4,7,8),"0000-00-00")的語法格式為：將I4單元格中的身份證號(hào)碼利用MID函數(shù)截取得到出生日期數(shù)值“19820312”，然后利用TEXT函數(shù)，將其按照指定日期格式“0000-00-00”轉(zhuǎn)換為真正的日期格式。（2）工齡員工的工齡要求按照周歲計(jì)算從“入職日期”到當(dāng)前日期的年份數(shù)，在此可以利用INT函數(shù)和TODAY函數(shù)來實(shí)現(xiàn)。在L4單元格內(nèi)構(gòu)建如下函數(shù)公式：=INT((TODAY()-J4)/365)按下Enter鍵，得到計(jì)算結(jié)果（“5”）。利用填充句柄向下復(fù)制公式直至最后一位員工，得到“工齡”列的計(jì)算結(jié)果。【函數(shù)解讀】：Today()函數(shù)：返回當(dāng)前日期；(Today()-J4)/365公式：當(dāng)前日期與入職時(shí)間的差值（天數(shù)），除以365，得到兩者之間的年數(shù)差；INT((TODAY()-J4)/365)公式：INT函數(shù)的語法格式為“向下取整”，利用它將上述年數(shù)差的小數(shù)部分舍去，得到整數(shù)年數(shù)——即“工齡”（滿整年記為1年）。5.工作表的格式化——美化“員工信息表”分別為表頭、列標(biāo)簽和數(shù)據(jù)區(qū)域設(shè)置適當(dāng)?shù)淖煮w格式、對(duì)齊方式、邊框和底紋，調(diào)整行高列寬，使其美觀大方。（1）設(shè)置表頭選擇A1：L1單元格區(qū)域，設(shè)置字體格式為“微軟雅黑14磅”，打開“設(shè)置單元格格式”對(duì)話框，在“對(duì)齊”選項(xiàng)卡中設(shè)置“跨列居中”方式。（2）調(diào)整行高列寬根據(jù)每列實(shí)際內(nèi)容和表格整體美觀需要，分別為各列設(shè)置合適的列寬；適當(dāng)調(diào)整表頭行的行高。（3）設(shè)置對(duì)齊方式員工信息表中的數(shù)據(jù)列均可以采用“居中”對(duì)齊方式顯示。（4）添加邊框和底紋選擇表格數(shù)據(jù)區(qū)域A3:L18，在“開始”功能區(qū)“字體”組中單擊“邊框”命令右側(cè)的下三角按鈕，從彈出的列表中選擇“所有邊框”；選擇標(biāo)題行A3:L3單元格區(qū)域，單擊“開始”功能區(qū)“字體”組中的“填充顏色”命令右側(cè)的下三角按鈕，從彈出的列表中選擇一種合適的底紋顏色。（5）取消網(wǎng)格線在“視圖”功能區(qū)“顯示”組中取消“網(wǎng)格線”復(fù)選框勾選，使得員工信息表中的內(nèi)容更醒目、突出。5.工作表的格式化——美化“員工信息表”6.打印設(shè)置在辦公中，常常需要將制作好的表格打印在A4紙張上，便于查看。在此，要求設(shè)置“員工信息表”最終打印在一張“橫向”的A4紙張上，水平方向上居中對(duì)齊。步驟一單擊“頁面布局”功能區(qū)“頁面設(shè)置”組中的“頁面設(shè)置”對(duì)話框啟動(dòng)器按鈕，彈出“頁面設(shè)置”對(duì)話框。在“頁面”選項(xiàng)卡中選擇“橫向”選項(xiàng)按鈕，紙張大小選擇“A4”。步驟二切換至“頁邊距”選項(xiàng)卡中，勾選“水平居中”復(fù)選框。步驟三切換至“工作表”選項(xiàng)卡中，在“打印區(qū)域”框中用鼠標(biāo)選中“員工信息表”中的A:L列。最后，單擊“打印預(yù)覽”按鈕，查看打印設(shè)置效果。四、編制“1月考勤表”新建一個(gè)工作表，命名為“1月考勤表”，用來記錄員工2020年1月份的出勤情況。1.利用文本函數(shù)CONCATENATE制作表頭在“考勤表”中，如圖3.18所示輸入考勤日期（2020/1/31）和列標(biāo)簽（序號(hào)、姓名、部門、病假天數(shù)、事假天數(shù)、曠工天數(shù)、法定假日加班天數(shù)、平常假日加班天數(shù)）。在A1單元格中通過構(gòu)建如下函數(shù)公式得到表頭內(nèi)容：=CONCATENATE("萬隆燈具有限公司",YEAR(L3),"年",MONTH(L3),"月考勤表")按下Enter鍵，得到表格標(biāo)題“萬隆燈具有限公司2020年1月考勤表”?！拘√崾尽浚何谋竞瘮?shù)CONCATENATE、日期函數(shù)YEAR和MONTHCONCATENATE是文本函數(shù)，其作用是將兩個(gè)或多個(gè)文本字符串聯(lián)接為一個(gè)字符串。YEAR和MONTH都是日期函數(shù)。其中YEAR函數(shù)是返回一個(gè)日期的年份值；MONTH函數(shù)是返回一個(gè)日期的月份值。2.利用VLOOKUP函數(shù)填充“姓名”、“職務(wù)”和“基本工資”（1）建立“序號(hào)”列先將A列設(shè)置為“文本”格式，然后自A6單元格開始向下建立一個(gè)“001、002、003、…015”的“序號(hào)”列。（2）查詢“姓名”利用查詢函數(shù)VLOOKUP，根據(jù)員工序號(hào)從“員工信息表”中查詢到相對(duì)應(yīng)的姓名并填充到“姓名”列中。在B6單元格中構(gòu)建如下函數(shù)公式：=VLOOKUP(A6,員工信息表!A:B,2,0)按下Enter鍵，得到第一位員工的姓名“鄧華超”（如圖3.22所示），利用填充句柄復(fù)制公式向下直至最后一位員工。2.利用VLOOKUP函數(shù)填充“姓名”、“職務(wù)”和“基本工資”（3）查詢“職務(wù)”利用查詢函數(shù)VLOOKUP和定義的名稱，根據(jù)員工序號(hào)從“員工信息表”中查詢相應(yīng)職務(wù)信息并填充到“職務(wù)”列中。在C6單元格中構(gòu)建如下函數(shù)公式：=VLOOKUP(A6,員工信息表!A:G,7,0)按下Enter鍵，得到第一位員工的職務(wù)名稱“總經(jīng)理”，利用填充句柄復(fù)制公式向下直至最后一位員工。（4）查詢“基本工資”在“基礎(chǔ)數(shù)據(jù)”工作表中，企業(yè)工資的發(fā)放標(biāo)準(zhǔn)是由“職務(wù)”為依據(jù)的，相關(guān)數(shù)據(jù)區(qū)域已經(jīng)定義了名稱（“工資標(biāo)準(zhǔn)”），在此可以利用VLOOKUP函數(shù)引用定義的名稱來查詢“基本工資”。在G6和H6單元格中構(gòu)建如下函數(shù)公式：=VLOOKUP(C6,工資標(biāo)準(zhǔn),2,0)如圖3.23所示。按下Enter鍵，得到第一位員工的基本工資（7000元），利用填充句柄向下復(fù)制填充公式直至最后一行，得到所有員工的“基本工資”?！竞瘮?shù)解讀】：垂直查詢函數(shù)VLOOKUP在利用VLOOKUP函數(shù)根據(jù)“職務(wù)”查詢“基本工資”時(shí)，VLOOKUP的幾個(gè)參數(shù)表示的意義為：Lookup_value:查詢“基本工資”所需要的索引值，即：“職務(wù)”；Table_Array：一個(gè)數(shù)據(jù)表，其首列為“職務(wù)”，且包含“基礎(chǔ)工資”列；Col_index_num：數(shù)據(jù)表中從首列計(jì)數(shù)的“基礎(chǔ)工資”所在列序號(hào)；Range_looup:輸入0或者FALSE表示精確匹配。3.利用IF和COUNTBLANK函數(shù)計(jì)算“全勤費(fèi)”公司關(guān)于員工考勤制定了嚴(yán)格的獎(jiǎng)懲制度，規(guī)定：全勤發(fā)放200元獎(jiǎng)金；病假一天扣除日工資的10%；事假一天扣除日工資的50%；曠工一天扣除日工資全部。步驟一在表格中根據(jù)1月份的實(shí)際情況，如實(shí)填寫各項(xiàng)考勤數(shù)據(jù)。步驟二計(jì)算“全勤費(fèi)”。在“1月考勤表”中，如果“病假天數(shù)”、“事假天數(shù)”和“曠工天數(shù)”均為空白單元格，則表示該員工全勤（全勤費(fèi)為200元）。在J6單元格中構(gòu)建如下函數(shù)公式：=IF(COUNTBLANK(E6:G6)=3,200,0)按下Enter鍵，得到第一位員工的全勤費(fèi)（200元），利用填充句柄向下復(fù)制填充公式直至最后一行，得到所有員工的“全勤費(fèi)”?！竞瘮?shù)解讀】：計(jì)數(shù)函數(shù)COUNTBLANK在上述計(jì)算“全勤費(fèi)”的函數(shù)公式中，函數(shù)COUNTBLANK的語法格式為：計(jì)算某個(gè)區(qū)域中空單元格的數(shù)目。如果它的計(jì)算結(jié)果為3，則表示該員工本月無任何病事假及曠工記錄（即：全勤）。在這種情況下，利用IF函數(shù)給出返回值為“200”，否則給出另一種返回值“0”。4.計(jì)算“加班費(fèi)”公司規(guī)定，員工加班費(fèi)的發(fā)放方法是：法定假日加班每天300元，平常假日加班每天200元。在K6單元格中構(gòu)建如下公式：=H6*300+I6*200按下Enter鍵，得到計(jì)算結(jié)果。利用填充句柄，向下復(fù)制上述公式直至最后一位員工。5.計(jì)算“考勤扣款”考勤扣款的計(jì)算公式為：病假天數(shù)*日工資*10%+事假天數(shù)*日工資*50%+曠工天數(shù)*日工資。在L6單元格中，構(gòu)建如下函數(shù)公式——=INT((E6*0.1+F6*0.5+G6)*D6/DAY(EOMONTH($L$3,0)))按下Enter鍵，得到計(jì)算結(jié)果。利用填充句柄，向下復(fù)制上述公式直至最后一位員工。【函數(shù)解讀】：“考勤扣款”函數(shù)公式考勤扣款需要先計(jì)算出“日工資”——即：D6/DAY(EOMONTH($L$3,0))。其中：D6為“基本工資”；EOMONTH($L$3,0)返回值為“考勤日期”（即：$L$3單元格）所在月份的最后一天（表示日期的數(shù)字）；DAY(EOMONTH($L$3,0))返回值為該月最后一天的數(shù)值（介于1-31之間）。6.設(shè)置單元格格式根據(jù)表格的內(nèi)容和打印的需求，為“1月考勤表”進(jìn)行格式化——設(shè)置字體、調(diào)整行高列寬、設(shè)置對(duì)齊方式、添加邊框和底紋等操作。在列標(biāo)簽中的最后兩列——“法定假日加班天數(shù)”和“平常假日加班天數(shù)”的字段名稱文字內(nèi)容太長，顯得“頭重腳輕”，可以將其調(diào)整為兩行顯示，這就用到了“自動(dòng)換行”功能。選擇第5行，單擊“開始”功能區(qū)“對(duì)齊方式”組中的“自動(dòng)換行”命令，然后適當(dāng)調(diào)整列寬行高。【小提示】：快速生成其他月份的考勤表在完成了“1月考勤表”工作表的制作之后，利用工作表的“移動(dòng)或復(fù)制…”功能（建立副本），就可以快速地得到其他月份的考勤表了：僅需要更改每月的考勤日期（例如：2020/3/31），就可以生成新的表頭（例如：萬隆燈具有限公司2020年3月考勤表）。五、制作“1月工資表”1.制作表頭部分（1）制表日期新建一個(gè)工作表，命名為“1月工資表”，然后在R3單元格中構(gòu)建如下函數(shù)公式：=考勤獎(jiǎng)懲!H3按下Enter鍵，得到計(jì)算結(jié)果：2020/1/31（目的：使得每月的考勤表和工資表的制表日期相同，減少手工輸入，提高工作效率）。（2）表頭在A1單元格中構(gòu)建如下函數(shù)公式——=CONCATENATE("萬隆燈具有限公司",YEAR(R3),"年",MONTH(R3),"月工資表")按下Enter鍵，得到計(jì)算結(jié)果——“萬隆燈具有限公司2020年1月工資表”。（3）標(biāo)題行自A5單元格開始向右，依次輸入列標(biāo)簽“序號(hào)”、“姓名”、“最高學(xué)歷”、“職稱”、“部門”、“職務(wù)”、“基本工資”、“績效工資”、“全勤費(fèi)”、“加班費(fèi)”、“考勤扣款”、“通信費(fèi)”、“應(yīng)發(fā)工資”、“社保”、“公積金”、“應(yīng)納稅所得額”、“個(gè)人所得稅”和“實(shí)發(fā)工資”。2.獲取員工基本信息“1月工資表”中的A:F列數(shù)據(jù)，可取自于“員工基本信息”工作表。（1）序號(hào)設(shè)置A列為“文本”格式，自A6單元格開始填充“001、002、003、…”序號(hào)，直至“015”。（2）姓名在B6單元格中構(gòu)建如下函數(shù)公式：=VLOOKUP($A6,員工信息表!$A$3:$G$18,2,0)按下Enter鍵，得到計(jì)算結(jié)果。利用填充句柄向下填充復(fù)制直至最后一行。2.獲取員工基本信息（3）其他員工信息參考上述操作方法，分別在C6:F6中構(gòu)建如表3.3所示函數(shù)公式，得到“最高學(xué)歷”、“職稱”、“部門”、“職務(wù)”信息。表3.3利用VLOOKUP函數(shù)查詢員工信息單元格 列標(biāo)簽

函數(shù)公式B6 姓名 =VLOOKUP($A6,員工信息表!$A$3:$G$18,2,0)C6 最高學(xué)歷 =VLOOKUP($A6,員工信息表!$A$3:$G$18,4,0)D6 職稱 =VLOOKUP($A6,員工信息表!$A$3:$G$18,5,0)E6 部門 =VLOOKUP($A6,員工信息表!$A$3:$G$18,6,0)F6 職務(wù) =VLOOKUP($A6,員工信息表!$A$3:$G$18,7,0)3.獲取“基本工資”和“績效工資”利用“基礎(chǔ)數(shù)據(jù)”工作表中定義的名稱“工資標(biāo)準(zhǔn)”，可以利用VLOOKUP函數(shù)獲取“基本工資”和“績效工資”。分別在G6和H6單元格中構(gòu)建如下函數(shù)公式：=VLOOKUP($F6,工資標(biāo)準(zhǔn),2,0)=VLOOKUP($F6,工資標(biāo)準(zhǔn),3,0)按下Enter鍵，得到計(jì)算結(jié)果。利用填充句柄向下填充復(fù)制直至最后一行。4.獲取考勤獎(jiǎng)懲考勤獎(jiǎng)懲的三個(gè)信息“全勤費(fèi)”、“加班費(fèi)”和“考勤扣款”均可從“1月考勤表”中，利用VLOOKUP函數(shù)獲取。分別在I6、J6、K6單元格中構(gòu)建如下函數(shù)公式：=VLOOKUP($A6,'1月考勤表'!$A:$L,10,0)=VLOOKUP($A6,'1月考勤表'!$A:$L,11,0)=VLOOKUP($A6,'1月考勤表'!$A:$L,12,0)按下Enter鍵，得到計(jì)算結(jié)果。分別利用填充句柄向下填充復(fù)制直至最后一行。5.計(jì)算“通信費(fèi)”公司制定了通信費(fèi)補(bǔ)貼標(biāo)準(zhǔn)：總經(jīng)理補(bǔ)貼500元；研究生及學(xué)歷以上且職稱為高級(jí)或者是部門經(jīng)理補(bǔ)貼300元；其他職工補(bǔ)貼150元。“通信費(fèi)”的計(jì)算可以使用邏輯函數(shù)AND（與）、OR（或）和IF（條件）嵌套來實(shí)現(xiàn)。在L6單元格中構(gòu)建如下函數(shù)公式——=IF(F6="總經(jīng)理",500,IF(OR(F6="部門經(jīng)理",AND(C6="研究生",D6="高級(jí)")),300,150))按下Enter鍵，得到計(jì)算結(jié)果。分別利用填充句柄向下填充復(fù)制直至最后一行。6.計(jì)算“應(yīng)發(fā)工資”、“社保”、“公積金”、“應(yīng)納稅所得額”和“實(shí)發(fā)工資”根據(jù)公司計(jì)算工資的相關(guān)制度規(guī)定，分別在相應(yīng)列中計(jì)算“應(yīng)發(fā)工資”、“社?！薄ⅰ肮e金”、“應(yīng)納稅所得額”和“實(shí)發(fā)工資”，如表3.4所示。表3.4計(jì)算其他工資項(xiàng)目單元格 項(xiàng)目 計(jì)算方法 函數(shù)公式M6 應(yīng)發(fā)工資 =基本工資+績效工資+全勤費(fèi)+加班費(fèi)-考勤扣款+通信費(fèi) =G6+H6+I6+J6-K6+L6N6 社保 =（應(yīng)發(fā)工資-通信費(fèi)）*10.5% =ROUND((M6-L6)*10.5%,2)O6 公積金 =（應(yīng)發(fā)工資-通信費(fèi)）*10% =ROUND((M6-L6)*10%,2)P6 應(yīng)納稅所得額 =應(yīng)發(fā)工資-通信費(fèi)-社保-公積金-起征點(diǎn) =M6-L6-N6-O6-5000R6 實(shí)發(fā)工資 =應(yīng)發(fā)工資-社保-公積金-個(gè)人所得稅 =ROUND(M6-N6-O6-Q6,2)分別將上述計(jì)算結(jié)果，利用填充句柄向下填充復(fù)制直至最后一行。7.利用數(shù)組公式計(jì)算“個(gè)人所得稅”計(jì)算“個(gè)人所得稅”的方法有多種，利用IF函數(shù)多層嵌套公式冗長且不易更新修改。因?yàn)椤皞€(gè)人所得稅”為七級(jí)超額累進(jìn)，所以采用數(shù)組的方法可以進(jìn)行更為簡便的計(jì)算。在Q6單元格內(nèi)構(gòu)建如下函數(shù)公式：=MAX(0,P6*稅率-速算扣除數(shù))按下Ctrl+Shift+Enter組合鍵，得到數(shù)組公式的計(jì)算結(jié)果。利用填充句柄向下填充復(fù)制直至最后一行。【函數(shù)解讀】：MAX函數(shù)和數(shù)組公式計(jì)算“個(gè)人所得稅”。使用“稅率”和“扣除數(shù)”（事先在“基礎(chǔ)數(shù)據(jù)”工作表中定義名稱）對(duì)應(yīng)的兩列數(shù)組，分別計(jì)算根據(jù)“應(yīng)納稅所得額”需要交納的個(gè)人所得稅，與零值“0”（零值表示“應(yīng)納稅所得額”為負(fù)值的不需要繳納“個(gè)人所得稅”）進(jìn)行最大值（MAX函數(shù)）求算，計(jì)算出來的最大值就是應(yīng)繳的個(gè)人所得稅。數(shù)組是指按照規(guī)則排列的一組數(shù)據(jù)的集合，上述“稅率”和“速算扣除數(shù)”分別代表兩列數(shù)組（各自包含7個(gè)常量），與P6（一個(gè)常量）進(jìn)行四則運(yùn)算——是普通常量四則運(yùn)算計(jì)算量的7倍。數(shù)組計(jì)算需要按下Ctrl+Shift+Enter組合鍵結(jié)