Excel數(shù)據(jù)管理與分析項目二職員工資表ppt課件

1、模塊四 Excel數(shù)據(jù)管理與分析工程二 職員工資表工程要求外部數(shù)據(jù)的導(dǎo)入SUM()、AVERAGE()、VLOOKUP()、IF()等函數(shù)的運用。公式的運用。巧用“輔助序列和“定位快速制造工資條的方法。詳細義務(wù)義務(wù)一：制造簡單的職員工資表義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息義務(wù)三：VLOOKUP()函數(shù)的運用義務(wù)四：IF()函數(shù)、YEAR()函數(shù)和NOW()函數(shù)的綜合運用義務(wù)五：用公式計算“三金義務(wù)六：運用公式計算“應(yīng)發(fā)工資、“實發(fā)工資義務(wù)七：巧用“輔助序列和“定位制造工資條相關(guān)知識義務(wù)一：制造簡單的職員工資表義務(wù)要求：運用公式和SUM()、AVERAGE()、MAX()、MIN()等函

2、數(shù)統(tǒng)計工資表中的各項數(shù)據(jù)。本義務(wù)完成后的效果如圖4-46所示。圖4-46員工工資表效果義務(wù)一：制造簡單的職員工資表1輸入數(shù)據(jù) 啟動Excel 2021軟件，并在Sheet1任務(wù)表中輸入如圖4-47所示的表格內(nèi)容。圖4-47員工工資表根本數(shù)據(jù)義務(wù)一：制造簡單的職員工資表2運用公式計算“應(yīng)發(fā)合計、“公積金、“扣款合計和“實發(fā)合計3運用自動求和功能插入SUM()函數(shù)并計算工資“各項合計4運用函數(shù)導(dǎo)游功能插入AVERAGE()函數(shù)并計算工資各項平均數(shù)5運用直接輸入方法插入MAX()、MIN()函數(shù)并計算最高與最低工資義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息義務(wù)要求：從工程二中的“4-2-2素材.xl

3、sx中導(dǎo)入數(shù)據(jù)到“員工工資表中，并根據(jù)制表需求隱藏或添加行、列數(shù)據(jù)。義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息1導(dǎo)入數(shù)據(jù) 新建任務(wù)簿，單擊Sheet1任務(wù)表標(biāo)簽，選擇“數(shù)據(jù)選項卡“銜接組中的“銜接命令，翻開 “任務(wù)簿銜接對話框，如圖4-53所示。圖4-53 “任務(wù)簿銜接對話框義務(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)入快速獲取員工的根本信息 在對話框中選擇“ 按鈕，并在彈出的列表

4、中選擇Excel文件類型，如圖4-56所示。 在“選取數(shù)據(jù)源對話框的左側(cè)文件列表中選擇“4-2-2素材.xlsx文件，翻開“選擇表格對話框，并在對話框中選擇“員工根本情況表$，如圖4-57所示，單擊“確定按鈕即完成數(shù)據(jù)的銜接，并前往到“任務(wù)簿銜接對話框，如圖4-58所示，然后單擊“封鎖按鈕，將此對話框封鎖。 選擇“數(shù)據(jù)選項卡“獲取外部數(shù)據(jù)組中的“現(xiàn)有銜接命令，翻開 “現(xiàn)有銜接對話框，如圖4-59所示，在對話框中選擇剛剛所添加的銜接。義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息圖4-56 選擇Excel文件類型圖4-57 “選擇表格對話框圖4-58 “任務(wù)簿銜接對話框圖4-59 “現(xiàn)有銜接對話框義

5、務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息 在對話框中單擊“翻開按扭，翻開“導(dǎo)入數(shù)據(jù)對話框，如圖4-60所示，單擊“確定按鈕，即可將數(shù)據(jù)導(dǎo)入到新任務(wù)簿中。 單擊“文件“保管命令，將此任務(wù)簿文件名設(shè)置為“員工工資表.xlsx ，如圖4-61所示。圖4-60 “導(dǎo)入數(shù)據(jù)對話框圖4-61 導(dǎo)入數(shù)據(jù)并保管后的“員工工資表.xlsx義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息2去除表中數(shù)據(jù)格式 選擇“開場選項卡“款式組中的“套用表格格式命令，在翻開的下拉列表中選擇“新建表款式命令，翻開“新建表快速款式對話框，如圖4-62所示。圖4-62 “新建表快速款式對話框義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息 在對

6、話框中單擊“格式命令，翻開“設(shè)置單元格格式對話框，并在此對話框中將邊框和填充均設(shè)置為無，然后在“新建表快速款式對話框中單擊“確定按鈕，完成新建表格款式。 選擇“A1：J29單元格，然后選擇“開場選項卡“款式組中的“套用表格格式命令，在翻開的下拉列表中選擇“自定義款式中的“表款式1命令，快速將原有格式去除。義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息 選擇數(shù)據(jù)區(qū)域中的任一單元格，然后選擇“數(shù)據(jù)選項卡“排序和挑選組中的“挑選 按鈕，如圖4-63所示。4-63 去除格式后效果義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息3隱藏數(shù)據(jù)隱藏“性別、“任務(wù)部門、“學(xué)歷、“身份證號、“聯(lián)絡(luò)、“Email地址列和標(biāo)題

7、行，并在其右邊添加“根本工資、“工齡、“工齡工資、“養(yǎng)老保險、“醫(yī)療保險、“失業(yè)保險、 “應(yīng)發(fā)工資、“實發(fā)工資列標(biāo)題，如圖4-64所示。圖4-64工資表框架義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息 選擇“性別所在列列，然后選擇“開場選項卡“單元格組中的“格式下拉按鈕，并在下拉菜單中選擇“隱藏和取消隱藏子菜單中的“隱藏列命令，如圖4-65所示，即可將“性別所在列列隱藏。圖4-65選擇“隱藏命令義務(wù)二：利用數(shù)據(jù)導(dǎo)入快速獲取員工的根本信息 運用一樣的方法將“任務(wù)部門、“學(xué)歷、“身份證號、“聯(lián)絡(luò)、“Email地址列隱藏。 在表頭右邊添加“根本工資、“工齡、“工齡工資、“養(yǎng)老保險、“醫(yī)療保險、“失業(yè)保

8、險、“應(yīng)發(fā)工資、“實發(fā)工資列標(biāo)題。 將“工齡列的數(shù)字類型設(shè)置為“數(shù)值，小數(shù)點后保管0位，其他列的數(shù)字格式設(shè)置為“數(shù)值且小數(shù)點后保管2位，其他格式設(shè)置如圖4-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ù)的運用1選擇任務(wù)簿并進展設(shè)置 翻開“4-2-2素材.xlsx任務(wù)

9、簿，并將此任務(wù)簿中的“各項工資對照表任務(wù)表中A1:I8單元格中的數(shù)據(jù)復(fù)制到“工資表作業(yè)任務(wù)簿的Sheet2任務(wù)表中的A1:I8單元格，然后將Sheet2任務(wù)表重命名為“各項工資對照表。義務(wù)三：VLOOKUP()函數(shù)的運用2定義名為“根本工資對照表的單元格區(qū)域 選擇“職員工資表任務(wù)簿中的“各項工資對照表中的A3:B8單元格。 選擇“公式選項卡“定義的稱號組中的“定義稱號命令，翻開“新建稱號對話框，然后將“在當(dāng)前任務(wù)簿中的稱號文本框中輸入“根本工資對照表，如圖4-66所示。 單擊“確定按鈕。圖4-66 “定義稱號對話框義務(wù)三：VLOOKUP()函數(shù)的運用3查找函數(shù)VLOOKUP()的運用 運用查找

10、函數(shù)VLOOKUP()計算員工“楊林作為“總經(jīng)理的“根本工資，并將結(jié)果放在相應(yīng)的單元格中。選擇“sheet1任務(wù)表標(biāo)簽，將光標(biāo)定位于K3單元格中，選擇“公式選項卡“函數(shù)庫組最左邊的“插入函數(shù) 命令圖標(biāo)，翻開“插入函數(shù)對話框，在“或選擇類別下拉列表中選擇“查找與援用選項，如圖4-69所示。圖4-69 選擇函數(shù)類別義務(wù)三：VLOOKUP()函數(shù)的運用 在對話框中選擇VLOOKUP()函數(shù)，如圖4-70所示。 單擊“確定按扭，翻開“函數(shù)參數(shù)對話框。在此對話框中設(shè)置各個參數(shù)，如圖4-71所示。單擊“確定按扭。圖4-70 選擇函數(shù)圖4-71 “函數(shù)參數(shù)對話框義務(wù)四：IF() 、YEAR() 和NOW()函數(shù)的綜合運用綜合運用“IF、YEAR()和NOW()函數(shù)的計算工齡工資，工齡工資計算方法如表4- 2所示。工齡（年）工 齡 工 資工齡（年）工 齡 工 資工齡=206