模塊四Excel數(shù)據(jù)管理與分析項目二

職員工資表模塊四Excel數(shù)據(jù)管理與分析項目二職員工資表1項目要求外部數(shù)據(jù)的導(dǎo)入SUM()、AVERAGE()、VLOOKUP()、IF()等函數(shù)的使用。公式的使用。巧用“輔助序列”和“定位”快速制作工資條的方法。項目要求外部數(shù)據(jù)的導(dǎo)入2具體任務(wù)任務(wù)一：制作簡單的職員工資表任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息任務(wù)三：VLOOKUP()函數(shù)的使用任務(wù)四：IF()函數(shù)、YEAR()函數(shù)和NOW()函數(shù)的綜合應(yīng)用任務(wù)五：用公式計算“三金”任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”任務(wù)七：巧用“輔助序列”和“定位”制作工資條相關(guān)知識具體任務(wù)任務(wù)一：制作簡單的職員工資表3任務(wù)一：制作簡單的職員工資表任務(wù)要求：使用公式和SUM()、AVERAGE()、MAX()、MIN()等函數(shù)統(tǒng)計工資表中的各項數(shù)據(jù)。本任務(wù)完成后的效果如圖4-46所示。圖4-46員工工資表效果任務(wù)一：制作簡單的職員工資表任務(wù)要求：使用公式和SUM()、4任務(wù)一：制作簡單的職員工資表1．輸入數(shù)據(jù)

啟動Excel2010軟件，并在Sheet1工作表中輸入如圖4-47所示的表格內(nèi)容。圖4-47員工工資表基本數(shù)據(jù)任務(wù)一：制作簡單的職員工資表1．輸入數(shù)據(jù)圖4-47員工工資5任務(wù)一：制作簡單的職員工資表2．使用公式計算“應(yīng)發(fā)合計”、“公積金”、“扣款合計”和“實發(fā)合計”3．使用自動求和功能插入SUM()函數(shù)并計算工資“各項合計”4．使用函數(shù)向?qū)Чδ懿迦階VERAGE()函數(shù)并計算工資各項平均數(shù)5．使用直接輸入方法插入MAX()、MIN()函數(shù)并計算最高與最低工資任務(wù)一：制作簡單的職員工資表2．使用公式計算“應(yīng)發(fā)合計”、“6任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息任務(wù)要求：從項目二中的“4-2-2素材.xlsx”中導(dǎo)入數(shù)據(jù)到“員工工資表”中，并根據(jù)制表需要隱藏或添加行、列數(shù)據(jù)。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息任務(wù)要求：從項目二7任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息1．導(dǎo)入數(shù)據(jù)①新建工作簿，單擊Sheet1工作表標(biāo)簽，選擇“數(shù)據(jù)”選項卡“連接”組中的“連接”命令，打開“工作簿連接”對話框，如圖4-53所示。圖4-53“工作簿連接”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息1．導(dǎo)入數(shù)據(jù)圖4-8任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中選擇“添加”按鈕，打開“現(xiàn)有連接”對話框，如圖4-54所示。③在對話框中選擇“瀏覽更多”按鈕，打開“選取數(shù)據(jù)源”對話框，如圖4-55所示。圖4-54“現(xiàn)有連接”對話框圖4-55“選取數(shù)據(jù)源”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中選擇9任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④在對話框中選擇“

”按鈕，并在彈出的列表中選擇Excel文件類型，如圖4-56所示。⑤在“選取數(shù)據(jù)源”對話框的左側(cè)文件列表中選擇“4-2-2素材.xlsx”文件，打開“選擇表格”對話框，并在對話框中選擇“員工基本情況表$”，如圖4-57所示，單擊“確定”按鈕即完成數(shù)據(jù)的連接，并返回到“工作簿連接”對話框，如圖4-58所示，然后單擊“關(guān)閉”按鈕，將此對話框關(guān)閉。⑥選擇“數(shù)據(jù)”選項卡“獲取外部數(shù)據(jù)”組中的“現(xiàn)有連接”命令，打開“現(xiàn)有連接”對話框，如圖4-59所示，在對話框中選擇剛才所添加的連接。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④在對話框中選擇10任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息圖4-56選擇Excel文件類型圖4-57“選擇表格”對話框圖4-58“工作簿連接”對話框圖4-59“現(xiàn)有連接”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息圖4-56選擇11任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息⑦在對話框中單擊“打開”按扭，打開“導(dǎo)入數(shù)據(jù)”對話框，如圖4-60所示，單擊“確定”按鈕，即可將數(shù)據(jù)導(dǎo)入到新工作簿中。⑧單擊“文件”｜“保存”命令，將此工作簿文件名設(shè)置為“員工工資表.xlsx”，如圖4-61所示。圖4-60“導(dǎo)入數(shù)據(jù)”對話框圖4-61導(dǎo)入數(shù)據(jù)并保存后的“員工工資表.xlsx”任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息⑦在對話框中單擊12任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息2．清除表中數(shù)據(jù)格式①選擇“開始”選項卡“樣式”組中的“套用表格格式”命令，在打開的下拉列表中選擇“新建表樣式”命令，打開“新建表快速樣式”對話框，如圖4-62所示。圖4-62“新建表快速樣式”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息2．清除表中數(shù)據(jù)格13任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中單擊“格式”命令，打開“設(shè)置單元格格式”對話框，并在此對話框中將邊框和填充均設(shè)置為無，然后在“新建表快速樣式”對話框中單擊“確定”按鈕，完成新建表格樣式。③選擇“A1：J29”單元格，然后選擇“開始”選項卡“樣式”組中的“套用表格格式”命令，在打開的下拉列表中選擇“自定義”樣式中的“表樣式1”命令，快速將原有格式清除。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中單擊14任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④選擇數(shù)據(jù)區(qū)域中的任一單元格，然后選擇“數(shù)據(jù)”選項卡“排序和篩選”組中的“篩選”

按鈕，如圖4-63所示。4-63清除格式后效果任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④選擇數(shù)據(jù)區(qū)域中15任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息3．隱藏數(shù)據(jù)隱藏“性別”、“工作部門”、“學(xué)歷”、“身份證號”、“聯(lián)系電話”、“Email地址”列和標(biāo)題行，并在其右邊添加“基本工資”、“工齡”、“工齡工資”、“養(yǎng)老保險”、“醫(yī)療保險”、“失業(yè)保險”、“應(yīng)發(fā)工資”、“實發(fā)工資”列標(biāo)題，如圖4-64所示。圖4-64工資表框架任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息3．隱藏數(shù)據(jù)圖4-16任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息①選擇“性別”所在列Ｃ列，然后選擇“開始”選項卡“單元格”組中的“格式”下拉按鈕，并在下拉菜單中選擇“隱藏和取消隱藏”子菜單中的“隱藏列”命令，如圖4-65所示，即可將“性別”所在列Ｃ列隱藏。圖4-65選擇“隱藏”命令任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息①選擇“性別”所17任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②使用相同的方法將“工作部門”、“學(xué)歷”、“身份證號”、“聯(lián)系電話”、“Email地址”列隱藏。③在表頭右邊添加“基本工資”、“工齡”、“工齡工資”、“養(yǎng)老保險”、“醫(yī)療保險”、“失業(yè)保險”、“應(yīng)發(fā)工資”、“實發(fā)工資”列標(biāo)題。④將“工齡”列的數(shù)字類型設(shè)置為“數(shù)值”，小數(shù)點后保留0位，其他列的數(shù)字格式設(shè)置為“數(shù)值”且小數(shù)點后保留2位，其余格式設(shè)置如圖4-64所示。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②使用相同的方法18任務(wù)三：VLOOKUP()函數(shù)的使用任務(wù)要求：使用VLOOKUP()函數(shù)在“各項工資對照表”中的“基本工資對照表”區(qū)域內(nèi)查找相應(yīng)職務(wù)的基本工資數(shù)據(jù)，并將結(jié)果放在相應(yīng)的單元格中，如表4-1所示。職

務(wù)基本工資職

務(wù)基本工資總經(jīng)理5000技術(shù)員2500副經(jīng)理4000生產(chǎn)工人2000部門主管3200銷售員2000表4-1

基本工資對照表任務(wù)三：VLOOKUP()函數(shù)的使用任務(wù)要求：使用VLOOK19任務(wù)三：VLOOKUP()函數(shù)的使用1．選擇工作簿并進(jìn)行設(shè)置

打開“4-2-2素材.xlsx”工作簿，并將此工作簿中的“各項工資對照表”工作表中A1:I8單元格中的數(shù)據(jù)復(fù)制到“工資表作業(yè)”工作簿的Sheet2工作表中的A1:I8單元格，然后將Sheet2工作表重命名為“各項工資對照表”。任務(wù)三：VLOOKUP()函數(shù)的使用1．選擇工作簿并進(jìn)行設(shè)置20任務(wù)三：VLOOKUP()函數(shù)的使用2．定義名為“基本工資對照表”的單元格區(qū)域①選擇“職員工資表”工作簿中的“各項工資對照表”中的A3:B8單元格。②選擇“公式”選項卡“定義的名稱”組中的“定義名稱”命令，打開“新建名稱”對話框，然后將“在當(dāng)前工作簿中的名稱”文本框中輸入“基本工資對照表”，如圖4-66所示。③單擊“確定”按鈕。圖4-66“定義名稱”對話框任務(wù)三：VLOOKUP()函數(shù)的使用2．定義名為“基本工資對21任務(wù)三：VLOOKUP()函數(shù)的使用3．查找函數(shù)VLOOKUP()的應(yīng)用

使用查找函數(shù)VLOOKUP()計算員工“楊林”作為“總經(jīng)理”的“基本工資”，并將結(jié)果放在相應(yīng)的單元格中。①選擇“sheet1”工作表標(biāo)簽，將光標(biāo)定位于K3單元格中，選擇“公式”選項卡“函數(shù)庫”組最左邊的“插入函數(shù)”

命令圖標(biāo)，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表中選擇“查找與引用”選項，如圖4-69所示。圖4-69選擇函數(shù)類別任務(wù)三：VLOOKUP()函數(shù)的使用3．查找函數(shù)VLOOKU22任務(wù)三：VLOOKUP()函數(shù)的使用②在對話框中選擇VLOOKUP()函數(shù)，如圖4-70所示。③單擊“確定”按扭，打開“函數(shù)參數(shù)”對話框。在此對話框中設(shè)置各個參數(shù)，如圖4-71所示。④單擊“確定”按扭。圖4-70選擇函數(shù)圖4-71“函數(shù)參數(shù)”對話框任務(wù)三：VLOOKUP()函數(shù)的使用②在對話框中選擇VLO23任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用綜合應(yīng)用“IF（）、YEAR()和NOW()”函數(shù)的計算工齡工資，工齡工資計算方法如表4-2所示。工齡（年）工齡工資工齡（年）工齡工資工齡>=2060012>工齡>=630020>工齡>=12500工齡<6150表4-2工齡工資計算方法任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用24任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用1．YEAR()函數(shù)和NOW()函數(shù)的應(yīng)用

使用YEAR()函數(shù)和NOW()函數(shù)計算“楊林”的工齡，并將結(jié)果放置在相應(yīng)的單元格中。①將光標(biāo)定位于L2單元格中，并在編輯欄中輸入公式，如圖4-73所示。②按【Enter】鍵或單擊編輯欄左邊的

按鈕即可完成公式的輸入。圖4-73計算工齡的公式任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用25任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用2．IF()函數(shù)的應(yīng)用

使用IF()函數(shù)計算“楊林”的工齡工資。①將光標(biāo)定位于M2單元格中，并在編輯欄中輸入如圖4-74所示的公式。②按【Enter】鍵或單擊編輯欄左邊的

按鈕即可完成公式的輸入。圖4-74計算工齡工資任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用26任務(wù)五：用公式計算“三金”任務(wù)要求：公司要為每位員工繳納“三金”，即養(yǎng)老保險、醫(yī)療保險和失業(yè)保險，每種保險的繳納都與員工的工資相關(guān)，繳納方案是以員工工資為基數(shù)，然后乘以不同的比例，具體的比例如表4-3所示。表4-3三金繳納比例以員工各項工資總額為基數(shù)保險類型企

業(yè)個

人養(yǎng)老保險20%8%醫(yī)療保險8%2%失業(yè)保險2%1%任務(wù)五：用公式計算“三金”任務(wù)要求：公司要為每位員工繳納“三27任務(wù)五：用公式計算“三金”1．計算“楊林”的養(yǎng)老保險，并將結(jié)果放在相應(yīng)的單元格中

將光標(biāo)定位于N2單元格中，并在編輯欄中輸入公式，如圖4-75所示，然后按【Enter】鍵。圖4-75計算養(yǎng)老保險的公式任務(wù)五：用公式計算“三金”1．計算“楊林”的養(yǎng)老保險，并將結(jié)28任務(wù)五：用公式計算“三金”2．計算“楊林”的醫(yī)療保險和失業(yè)保險，并將結(jié)果放在相應(yīng)的單元格中“楊林”的醫(yī)療保險的計算公式為“=(K3+M3)*2%”?！皸盍帧钡氖I(yè)保險的計算公式為“=(K3+M3)*1%”。任務(wù)五：用公式計算“三金”2．計算“楊林”的醫(yī)療保險和失業(yè)保29任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”任務(wù)要求：使用公式計算出“楊林”的、應(yīng)發(fā)工資與實發(fā)工資。1．計算“楊林”的應(yīng)發(fā)工資：應(yīng)發(fā)工資＝基本工資+工齡工資2．計算“楊林”的實發(fā)工資：實發(fā)工資＝應(yīng)發(fā)工資－（養(yǎng)老保險+醫(yī)療保險+失業(yè)保險）圖4-76計算應(yīng)發(fā)工資的公式圖4-77計算實發(fā)工資的公式任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”任務(wù)要求：使用30任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”3.將工資表中不需要出現(xiàn)的“參加工作年月”、“職務(wù)”、“工齡”列隱藏，如圖4-78所示。圖4-78完成后的結(jié)果任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”3.將工資表31任務(wù)七：巧用“輔助序列”和“定位”制作工資條任務(wù)要求：應(yīng)用“輔助序列”和“定位”巧妙、快速地制作工資條，以確保能快速、簡便地打印工資條。任務(wù)七：巧用“輔助序列”和“定位”制作工資條32任務(wù)七：巧用“輔助序列”和“定位”制作工資條1．在S列和T列中添加輔助數(shù)據(jù)

在S3和T4單元格中分別輸入1，然后選擇S3:T4單元格，并用自動填充法將其往下復(fù)制到工資表結(jié)尾處，如圖4-79所示。圖4-79自動填充序列任務(wù)七：巧用“輔助序列”和“定位”制作工資條1．在S列和T列33任務(wù)七：巧用“輔助序列”和“定位”制作工資條2．在S3:T29單元格區(qū)域中的空格所在行添加空行①選擇S3:T29單元格，選擇“開始”選項卡“編輯”組中的“查找和選擇”按鈕，在打開的下拉菜單中選擇“定位條件”命令，打開“定位條件”對話框，并在對話框中選擇“空值”，圖4-80所示。②單擊“確定”按鈕，選擇S4:T30單元格區(qū)域中的所有空值的單元格。圖4-80“定位條件”對話框任務(wù)七：巧用“輔助序列”和“定位”制作工資條2．在S3:T234任務(wù)七：巧用“輔助序列”和“定位”制作工資條③選擇“開始”選項卡“單元格”組中的“插入”按鈕，并在下拉菜單中選擇“插入工作表行”命令，系統(tǒng)自動在每次所找到的空值單元格上插入空行，如圖4-81所示。圖4-81自動插入空行任務(wù)七：巧用“輔助序列”和“定位”制作工資條③選擇“開始35任務(wù)七：巧用“輔助序列”和“定位”制作工資條3．在每行的工資空行中粘貼工資表表頭各標(biāo)題①選擇工資表表頭各標(biāo)題所在行（第1行），單擊工具欄中的“復(fù)制”按鈕

，復(fù)制工資表表頭各標(biāo)題。②選擇A3:A55單元格，使用上述方法選擇此單元格區(qū)域中的空值單元格。③單擊工具欄中的“貼粘”按鈕，即可完成工資表表頭各標(biāo)題的貼粘。④

刪除S列和T列中的輔助數(shù)據(jù)。任務(wù)七：巧用“輔助序列”和“定位”制作工資條3．在每行的工資36相關(guān)知識1．單元格引用及其分類

所謂單元格引用，是指在公式和函數(shù)中使用單元格地址來表示單元格中的數(shù)據(jù)。例如，A1表示第A列與第1行交叉的單元格，A1:B5表示A1～B5的連續(xù)的單元格區(qū)域，“A1,B5”則表示A1和B5兩個單元格。

在Excel2010中，根據(jù)處理公式或函數(shù)的需要，引用方式有以下3種：

相對引用、絕對引用、混合引用相關(guān)知識1．單元格引用及其分類37相關(guān)知識2．常用函數(shù)及其功能說明。（1）計數(shù)函數(shù)COUNT()格式：COUNT(參數(shù)1，參數(shù)2…)功能：統(tǒng)計參數(shù)表中的數(shù)字參數(shù)和包含數(shù)字的單元格的個數(shù)，只有數(shù)值型數(shù)據(jù)能被統(tǒng)計。例如，COUNT(B5:E7)用于統(tǒng)計B5:E7單元格區(qū)域中數(shù)值型數(shù)據(jù)的單元格個數(shù)。相關(guān)知識2．常用函數(shù)及其功能說明。38相關(guān)知識（2）求平均值函數(shù)AVERAGE()格式：AVERAGE(參數(shù)1，參數(shù)2…)功能：計算所有參數(shù)的算術(shù)平均值。例如，AVERAGE(B5:E7)用于統(tǒng)計B5:E7單元格區(qū)域中的數(shù)據(jù)的平均值，AVERAGE(B5,E7)用于統(tǒng)計B5和E7兩個單元格中的數(shù)據(jù)的平均值。相關(guān)知識（2）求平均值函數(shù)AVERAGE()39相關(guān)知識（3）求和函數(shù)SUM()格式：SUM(參數(shù)1，參數(shù)2…)功能：計算所有參數(shù)的和。例如，SUM(B5:E7)用于統(tǒng)計B5:E7單元格區(qū)域中的數(shù)據(jù)的和，SUM(B5,E7)用于統(tǒng)計B5和E7兩個單元格中的數(shù)據(jù)的和。相關(guān)知識（3）求和函數(shù)SUM()40相關(guān)知識（4）求最大值函數(shù)MAX()格式：MAX(參數(shù)1，參數(shù)2…)功能：計算所有參數(shù)的最大值。例如，MAX(B5:E7)用于統(tǒng)計B5:E7單元格區(qū)域中的數(shù)據(jù)的最大值，MAX(B5,E7)用于統(tǒng)計B5和E7兩個單元格中的數(shù)據(jù)的最大值。相關(guān)知識（4）求最大值函數(shù)MAX()41相關(guān)知識（5）求最小值函數(shù)MIN()格式：MIN(參數(shù)1，參數(shù)2…)功能：計算所有參數(shù)的最小值。例如，MIN(B5:E7)用于統(tǒng)計B5:E7單元格區(qū)域中的數(shù)據(jù)的最小值，MIN(B5,E7)用于統(tǒng)計B5和E7兩個單元格中的數(shù)據(jù)的最小值。相關(guān)知識（5）求最小值函數(shù)MIN()42相關(guān)知識（6）求余函數(shù)MOD()格式：MOD(參數(shù)1，參數(shù)2…)功能：求參數(shù)1除以（整數(shù)）參數(shù)2的余數(shù)。例如，MOD(12,5)返回值為2。相關(guān)知識（6）求余函數(shù)MOD()43相關(guān)知識（7）條件函數(shù)IF()格式：IF(條件，值1，值2…)功能：IF()函數(shù)是一個邏輯函數(shù)，條件為真時返回值1，條件為假時返回值2。例如，IF（B2>=50,“Y”,“N”），當(dāng)B2單元格中的數(shù)據(jù)大于等于50時，返回字符Y，否則返回字符N。相關(guān)知識（7）條件函數(shù)IF()44項目練習(xí)

打開光盤中的“素材”|“4-2”|“作業(yè)”|“學(xué)生成績表（素材）.xlsx”工作簿，并用函數(shù)或公式完成各項數(shù)據(jù)的添加，最終效果見光盤中的“素材”|“4-2”|“作業(yè)”|“學(xué)生成績分析表（樣例）.xlsx”。項目練習(xí)打開光盤中的“素材”|“4-2”|“作45演講完畢，謝謝觀看！演講完畢，謝謝觀看！46模塊四Excel數(shù)據(jù)管理與分析項目二

職員工資表模塊四Excel數(shù)據(jù)管理與分析項目二職員工資表47項目要求外部數(shù)據(jù)的導(dǎo)入SUM()、AVERAGE()、VLOOKUP()、IF()等函數(shù)的使用。公式的使用。巧用“輔助序列”和“定位”快速制作工資條的方法。項目要求外部數(shù)據(jù)的導(dǎo)入48具體任務(wù)任務(wù)一：制作簡單的職員工資表任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息任務(wù)三：VLOOKUP()函數(shù)的使用任務(wù)四：IF()函數(shù)、YEAR()函數(shù)和NOW()函數(shù)的綜合應(yīng)用任務(wù)五：用公式計算“三金”任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”任務(wù)七：巧用“輔助序列”和“定位”制作工資條相關(guān)知識具體任務(wù)任務(wù)一：制作簡單的職員工資表49任務(wù)一：制作簡單的職員工資表任務(wù)要求：使用公式和SUM()、AVERAGE()、MAX()、MIN()等函數(shù)統(tǒng)計工資表中的各項數(shù)據(jù)。本任務(wù)完成后的效果如圖4-46所示。圖4-46員工工資表效果任務(wù)一：制作簡單的職員工資表任務(wù)要求：使用公式和SUM()、50任務(wù)一：制作簡單的職員工資表1．輸入數(shù)據(jù)

啟動Excel2010軟件，并在Sheet1工作表中輸入如圖4-47所示的表格內(nèi)容。圖4-47員工工資表基本數(shù)據(jù)任務(wù)一：制作簡單的職員工資表1．輸入數(shù)據(jù)圖4-47員工工資51任務(wù)一：制作簡單的職員工資表2．使用公式計算“應(yīng)發(fā)合計”、“公積金”、“扣款合計”和“實發(fā)合計”3．使用自動求和功能插入SUM()函數(shù)并計算工資“各項合計”4．使用函數(shù)向?qū)Чδ懿迦階VERAGE()函數(shù)并計算工資各項平均數(shù)5．使用直接輸入方法插入MAX()、MIN()函數(shù)并計算最高與最低工資任務(wù)一：制作簡單的職員工資表2．使用公式計算“應(yīng)發(fā)合計”、“52任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息任務(wù)要求：從項目二中的“4-2-2素材.xlsx”中導(dǎo)入數(shù)據(jù)到“員工工資表”中，并根據(jù)制表需要隱藏或添加行、列數(shù)據(jù)。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息任務(wù)要求：從項目二53任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息1．導(dǎo)入數(shù)據(jù)①新建工作簿，單擊Sheet1工作表標(biāo)簽，選擇“數(shù)據(jù)”選項卡“連接”組中的“連接”命令，打開“工作簿連接”對話框，如圖4-53所示。圖4-53“工作簿連接”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息1．導(dǎo)入數(shù)據(jù)圖4-54任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中選擇“添加”按鈕，打開“現(xiàn)有連接”對話框，如圖4-54所示。③在對話框中選擇“瀏覽更多”按鈕，打開“選取數(shù)據(jù)源”對話框，如圖4-55所示。圖4-54“現(xiàn)有連接”對話框圖4-55“選取數(shù)據(jù)源”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中選擇55任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④在對話框中選擇“

”按鈕，并在彈出的列表中選擇Excel文件類型，如圖4-56所示。⑤在“選取數(shù)據(jù)源”對話框的左側(cè)文件列表中選擇“4-2-2素材.xlsx”文件，打開“選擇表格”對話框，并在對話框中選擇“員工基本情況表$”，如圖4-57所示，單擊“確定”按鈕即完成數(shù)據(jù)的連接，并返回到“工作簿連接”對話框，如圖4-58所示，然后單擊“關(guān)閉”按鈕，將此對話框關(guān)閉。⑥選擇“數(shù)據(jù)”選項卡“獲取外部數(shù)據(jù)”組中的“現(xiàn)有連接”命令，打開“現(xiàn)有連接”對話框，如圖4-59所示，在對話框中選擇剛才所添加的連接。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④在對話框中選擇56任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息圖4-56選擇Excel文件類型圖4-57“選擇表格”對話框圖4-58“工作簿連接”對話框圖4-59“現(xiàn)有連接”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息圖4-56選擇57任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息⑦在對話框中單擊“打開”按扭，打開“導(dǎo)入數(shù)據(jù)”對話框，如圖4-60所示，單擊“確定”按鈕，即可將數(shù)據(jù)導(dǎo)入到新工作簿中。⑧單擊“文件”｜“保存”命令，將此工作簿文件名設(shè)置為“員工工資表.xlsx”，如圖4-61所示。圖4-60“導(dǎo)入數(shù)據(jù)”對話框圖4-61導(dǎo)入數(shù)據(jù)并保存后的“員工工資表.xlsx”任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息⑦在對話框中單擊58任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息2．清除表中數(shù)據(jù)格式①選擇“開始”選項卡“樣式”組中的“套用表格格式”命令，在打開的下拉列表中選擇“新建表樣式”命令，打開“新建表快速樣式”對話框，如圖4-62所示。圖4-62“新建表快速樣式”對話框任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息2．清除表中數(shù)據(jù)格59任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中單擊“格式”命令，打開“設(shè)置單元格格式”對話框，并在此對話框中將邊框和填充均設(shè)置為無，然后在“新建表快速樣式”對話框中單擊“確定”按鈕，完成新建表格樣式。③選擇“A1：J29”單元格，然后選擇“開始”選項卡“樣式”組中的“套用表格格式”命令，在打開的下拉列表中選擇“自定義”樣式中的“表樣式1”命令，快速將原有格式清除。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②在對話框中單擊60任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④選擇數(shù)據(jù)區(qū)域中的任一單元格，然后選擇“數(shù)據(jù)”選項卡“排序和篩選”組中的“篩選”

按鈕，如圖4-63所示。4-63清除格式后效果任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息④選擇數(shù)據(jù)區(qū)域中61任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息3．隱藏數(shù)據(jù)隱藏“性別”、“工作部門”、“學(xué)歷”、“身份證號”、“聯(lián)系電話”、“Email地址”列和標(biāo)題行，并在其右邊添加“基本工資”、“工齡”、“工齡工資”、“養(yǎng)老保險”、“醫(yī)療保險”、“失業(yè)保險”、“應(yīng)發(fā)工資”、“實發(fā)工資”列標(biāo)題，如圖4-64所示。圖4-64工資表框架任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息3．隱藏數(shù)據(jù)圖4-62任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息①選擇“性別”所在列Ｃ列，然后選擇“開始”選項卡“單元格”組中的“格式”下拉按鈕，并在下拉菜單中選擇“隱藏和取消隱藏”子菜單中的“隱藏列”命令，如圖4-65所示，即可將“性別”所在列Ｃ列隱藏。圖4-65選擇“隱藏”命令任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息①選擇“性別”所63任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②使用相同的方法將“工作部門”、“學(xué)歷”、“身份證號”、“聯(lián)系電話”、“Email地址”列隱藏。③在表頭右邊添加“基本工資”、“工齡”、“工齡工資”、“養(yǎng)老保險”、“醫(yī)療保險”、“失業(yè)保險”、“應(yīng)發(fā)工資”、“實發(fā)工資”列標(biāo)題。④將“工齡”列的數(shù)字類型設(shè)置為“數(shù)值”，小數(shù)點后保留0位，其他列的數(shù)字格式設(shè)置為“數(shù)值”且小數(shù)點后保留2位，其余格式設(shè)置如圖4-64所示。任務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的基本信息②使用相同的方法64任務(wù)三：VLOOKUP()函數(shù)的使用任務(wù)要求：使用VLOOKUP()函數(shù)在“各項工資對照表”中的“基本工資對照表”區(qū)域內(nèi)查找相應(yīng)職務(wù)的基本工資數(shù)據(jù)，并將結(jié)果放在相應(yīng)的單元格中，如表4-1所示。職

務(wù)基本工資職

務(wù)基本工資總經(jīng)理5000技術(shù)員2500副經(jīng)理4000生產(chǎn)工人2000部門主管3200銷售員2000表4-1

基本工資對照表任務(wù)三：VLOOKUP()函數(shù)的使用任務(wù)要求：使用VLOOK65任務(wù)三：VLOOKUP()函數(shù)的使用1．選擇工作簿并進(jìn)行設(shè)置

打開“4-2-2素材.xlsx”工作簿，并將此工作簿中的“各項工資對照表”工作表中A1:I8單元格中的數(shù)據(jù)復(fù)制到“工資表作業(yè)”工作簿的Sheet2工作表中的A1:I8單元格，然后將Sheet2工作表重命名為“各項工資對照表”。任務(wù)三：VLOOKUP()函數(shù)的使用1．選擇工作簿并進(jìn)行設(shè)置66任務(wù)三：VLOOKUP()函數(shù)的使用2．定義名為“基本工資對照表”的單元格區(qū)域①選擇“職員工資表”工作簿中的“各項工資對照表”中的A3:B8單元格。②選擇“公式”選項卡“定義的名稱”組中的“定義名稱”命令，打開“新建名稱”對話框，然后將“在當(dāng)前工作簿中的名稱”文本框中輸入“基本工資對照表”，如圖4-66所示。③單擊“確定”按鈕。圖4-66“定義名稱”對話框任務(wù)三：VLOOKUP()函數(shù)的使用2．定義名為“基本工資對67任務(wù)三：VLOOKUP()函數(shù)的使用3．查找函數(shù)VLOOKUP()的應(yīng)用

使用查找函數(shù)VLOOKUP()計算員工“楊林”作為“總經(jīng)理”的“基本工資”，并將結(jié)果放在相應(yīng)的單元格中。①選擇“sheet1”工作表標(biāo)簽，將光標(biāo)定位于K3單元格中，選擇“公式”選項卡“函數(shù)庫”組最左邊的“插入函數(shù)”

命令圖標(biāo)，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表中選擇“查找與引用”選項，如圖4-69所示。圖4-69選擇函數(shù)類別任務(wù)三：VLOOKUP()函數(shù)的使用3．查找函數(shù)VLOOKU68任務(wù)三：VLOOKUP()函數(shù)的使用②在對話框中選擇VLOOKUP()函數(shù)，如圖4-70所示。③單擊“確定”按扭，打開“函數(shù)參數(shù)”對話框。在此對話框中設(shè)置各個參數(shù)，如圖4-71所示。④單擊“確定”按扭。圖4-70選擇函數(shù)圖4-71“函數(shù)參數(shù)”對話框任務(wù)三：VLOOKUP()函數(shù)的使用②在對話框中選擇VLO69任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用綜合應(yīng)用“IF（）、YEAR()和NOW()”函數(shù)的計算工齡工資，工齡工資計算方法如表4-2所示。工齡（年）工齡工資工齡（年）工齡工資工齡>=2060012>工齡>=630020>工齡>=12500工齡<6150表4-2工齡工資計算方法任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用70任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用1．YEAR()函數(shù)和NOW()函數(shù)的應(yīng)用

使用YEAR()函數(shù)和NOW()函數(shù)計算“楊林”的工齡，并將結(jié)果放置在相應(yīng)的單元格中。①將光標(biāo)定位于L2單元格中，并在編輯欄中輸入公式，如圖4-73所示。②按【Enter】鍵或單擊編輯欄左邊的

按鈕即可完成公式的輸入。圖4-73計算工齡的公式任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用71任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用2．IF()函數(shù)的應(yīng)用

使用IF()函數(shù)計算“楊林”的工齡工資。①將光標(biāo)定位于M2單元格中，并在編輯欄中輸入如圖4-74所示的公式。②按【Enter】鍵或單擊編輯欄左邊的

按鈕即可完成公式的輸入。圖4-74計算工齡工資任務(wù)四：IF()、YEAR()和NOW()函數(shù)的綜合應(yīng)用72任務(wù)五：用公式計算“三金”任務(wù)要求：公司要為每位員工繳納“三金”，即養(yǎng)老保險、醫(yī)療保險和失業(yè)保險，每種保險的繳納都與員工的工資相關(guān)，繳納方案是以員工工資為基數(shù)，然后乘以不同的比例，具體的比例如表4-3所示。表4-3三金繳納比例以員工各項工資總額為基數(shù)保險類型企

業(yè)個

人養(yǎng)老保險20%8%醫(yī)療保險8%2%失業(yè)保險2%1%任務(wù)五：用公式計算“三金”任務(wù)要求：公司要為每位員工繳納“三73任務(wù)五：用公式計算“三金”1．計算“楊林”的養(yǎng)老保險，并將結(jié)果放在相應(yīng)的單元格中

將光標(biāo)定位于N2單元格中，并在編輯欄中輸入公式，如圖4-75所示，然后按【Enter】鍵。圖4-75計算養(yǎng)老保險的公式任務(wù)五：用公式計算“三金”1．計算“楊林”的養(yǎng)老保險，并將結(jié)74任務(wù)五：用公式計算“三金”2．計算“楊林”的醫(yī)療保險和失業(yè)保險，并將結(jié)果放在相應(yīng)的單元格中“楊林”的醫(yī)療保險的計算公式為“=(K3+M3)*2%”。“楊林”的失業(yè)保險的計算公式為“=(K3+M3)*1%”。任務(wù)五：用公式計算“三金”2．計算“楊林”的醫(yī)療保險和失業(yè)保75任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”任務(wù)要求：使用公式計算出“楊林”的、應(yīng)發(fā)工資與實發(fā)工資。1．計算“楊林”的應(yīng)發(fā)工資：應(yīng)發(fā)工資＝基本工資+工齡工資2．計算“楊林”的實發(fā)工資：實發(fā)工資＝應(yīng)發(fā)工資－（養(yǎng)老保險+醫(yī)療保險+失業(yè)保險）圖4-76計算應(yīng)發(fā)工資的公式圖4-77計算實發(fā)工資的公式任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”任務(wù)要求：使用76任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”3.將工資表中不需要出現(xiàn)的“參加工作年月”、“職務(wù)”、“工齡”列隱藏，如圖4-78所示。圖4-78完成后的結(jié)果任務(wù)六：使用公式計算“應(yīng)發(fā)工資”、“實發(fā)工資”3.將工資表77任務(wù)七：巧用“輔助序列”和“定位”制作工資條任務(wù)要求：應(yīng)用“輔助序列”和“定位”巧妙、快速地制作工資條，以確保能快速、簡便地打印工資條。任務(wù)七：巧用“輔助序列”和“定位”制作工資條78任務(wù)七：巧用“輔助序列”和“定位”制作工資條1．在S列和T列中添加輔助數(shù)據(jù)

在S3和T4單元格中分別輸入1，然后選擇S3:T4單元格，并用自動填充法將其往下復(fù)制到工資表結(jié)尾處，如圖4-79所示。圖4-79自動填充序列任務(wù)七：巧用“輔助序列”和“定位”制作工資條1．在S列和T列79任務(wù)七：巧用“輔助序列”和“定位”制作工資條2．在S3:T29單元格區(qū)域中的空格所在行添加空行①選擇S3:T29單元格，選擇“開始”選項卡“編輯”組中的“查找和選擇”按鈕，在打開的下拉菜單中選擇“定位條件”命令，打開“定位條件”對話框，并在對話框中選擇“空值”，圖4-80所示。②單擊“確定”按鈕，選擇S4:T30單元格區(qū)域中的所有空值的單元格。圖4-80“定位條件”對話框任務(wù)七：巧用“輔助序列”和“定位”制作工資條2．在S3:T280任務(wù)七：巧用“輔助序列”和“定位”制作工資條③選擇“開始”選項卡“單元格”組中的“插入”按鈕，并在下拉菜單中選擇“插入工作表行”命令，系統(tǒng)自動在每次所找到的空值單元格上插入空行，如圖4-81所示。圖4-81自動插入空行任務(wù)七：巧用“輔助序列”和“定位”制作工資條③選擇“開始81任務(wù)七：巧用“輔助序列”和“定位”制作工資條3．在每行的工資空行中粘貼工資表表頭各標(biāo)題①選擇工資表表頭各標(biāo)題所在行（第1行），單擊工具欄中的“復(fù)制”按鈕

，復(fù)制工資表表頭各標(biāo)題。②選擇A3:A55單元格，使用上述方法選擇此單元格區(qū)域中的空值單元格。③