《Excel 2021基礎與應用》課件 項目八 Excel 2021 在實際工作中的應用

Excel2021在實際工作中的應用4171任務1建立員工考勤系統(tǒng)2任務2

建立員工簡歷表和學歷信息統(tǒng)計表3任務3

建立財務報表目錄CONTENTS418任務1建立員工考勤系統(tǒng)419學習目標1.能描述Excel在日常辦公中的重要作用。2.能使用Excel2021制作員工考勤系統(tǒng)表格。420任務描述考勤系統(tǒng)的建立是為了強化公司的管理，對員工的考勤情況進行記錄，可以更加便利地了解公司員工的出缺勤情況。而用Excel2021建立的考勤系統(tǒng)，可以實現(xiàn)隨著月份改變，表格進行自動更新，以提高工作效率。另外，改變出缺勤情況的同時可以實現(xiàn)系統(tǒng)中與其關聯(lián)的多個表數(shù)據(jù)的改變。421本任務建立的考勤系統(tǒng)包括出缺勤記錄表、出缺勤記錄統(tǒng)計表以及與之相關聯(lián)的出缺勤獎金表三個部分。出缺勤記錄表如圖所示，主要記錄了員工在一個月的工作日內每天的出缺勤情況；出缺勤記錄統(tǒng)計表如圖所示，主要是統(tǒng)計此月員工的出缺勤情況，包括出勤天數(shù)、請假天數(shù)、出勤率等；出缺勤獎金表如圖所示，記錄了與員工出缺勤情況相關聯(lián)的出缺勤獎金金額。422出缺勤記錄表423出缺勤記錄表424出缺勤記錄統(tǒng)計表425出缺勤記錄統(tǒng)計表相關知識三個工作表的表標題中的時間可以通過TODAY、MONTH、YEAR函數(shù)自動更新，這樣工作人員可以隨時用這個模板來輸入數(shù)據(jù)，不必每次都更改日期。在出缺勤記錄表中插入相對應的考勤記錄符號，來完成出缺勤情況的輸入。在出缺勤記錄統(tǒng)計表中使用COUNTIF函數(shù)，并對其參數(shù)進行跨工作表的引用，完成相應出缺勤情況的統(tǒng)計。在出缺勤獎金表中使用IF函數(shù)，計算出應發(fā)獎金金額，最終通過SUM函數(shù)求出本月獎金總額，從而完成獎金的計算。426實踐操作1.員工出缺勤記錄表的建立（1）啟動Excel2021，新建空白工作簿，并將其命名為“出勤記錄表”。此表需要標題中的月份隨著時間的變化而變化，因此在單元格A1中輸入公式“="員工"&YEAR(TODAY())&"年"&MONTH(TODAY())&"月出缺勤記錄表"”，完成后按Enter鍵，效果如圖所示。單元格A1中輸入的公式返回的是當前日期的年份和月份，實現(xiàn)根據(jù)日期不同，標題中年份和月份的自動更新，提高工作效率。427輸入標題428429將單元格區(qū)域A1:Q1合并，并設置居中顯示，同時將標題設置為宋體、16號，如圖所示。標題格式設置完成后的效果430（2）在第2行輸入表頭及員工的姓名，并設置自動調整列寬，如圖所示。表頭及員工姓名輸入完成后的效果431（3）將單元格A3和A4合并后，輸入“1號”，并設置居中顯示。隨后采用快速輸入的方法，將鼠標指針移到合并后的單元格的右下角，按住鼠標左鍵，將填充柄拖動至“31號”，輸入完成后的效果如圖所示。日期輸入完成后的效果432（4）輸入各員工的出缺勤情況，將Sheet1的標簽改為“出缺勤記錄表”，并設置表格邊框及格式，如圖所示。數(shù)據(jù)輸入完成后的效果4332.員工出缺勤記錄統(tǒng)計表的建立通常要對員工每月的出缺勤情況進行統(tǒng)計，這時在Sheet2中建立出缺勤記錄統(tǒng)計表。（1）在Sheet2的單元格A1中輸入公式“=YEAR(TODAY())&"年"&MONTH(TODAY())&"月出缺勤記錄統(tǒng)計表"”，按Enter鍵，效果如圖所示。標題輸入后的效果434（2）將單元格區(qū)域A1:P1合并，并設置居中顯示，同時將標題設置為宋體、12號，如圖所示。標題格式修改后的效果435（3）在第2行和第1列輸入員工姓名和天數(shù)等內容，同時設置表格邊框、自動調整列寬及居中顯示，如圖所示。輸入員工姓名和天數(shù)等內容后的效果436（4）為了統(tǒng)計員工的出缺勤情況，在單元格B3中輸入公式“=COUNTIF(出缺勤記錄表!C3:C64,"★")/2”。按Enter鍵后，單元格即顯示“丁力”的出勤情況，將單元格區(qū)域B3:P11設置為居中顯示，如圖所示?！岸×Α背銮谟嬎?37438按照類似的方法在單元格B4中輸入公式“=COUNTIF(出

缺

勤

記

錄

表!C3:C64,"☆")/2”，統(tǒng)計出差的天數(shù)。當函數(shù)參數(shù)較復雜時，可以通過鼠標選中相應單元格的方式，完成函數(shù)參數(shù)的設置。（5）按照類似的方法，在其他單元格中分別輸入公式，統(tǒng)計各員工出勤、出差、事假、病假和無故缺勤的天數(shù)。439（6）選中單元格B5，輸入公式“=(B3＋B4)”，按Enter鍵，即可完成“丁力”出勤天數(shù)的計算，如圖所示。“丁力”出勤天數(shù)計算440（7）選中單元格B8，輸入公式“=(B6＋B7)”，按Enter鍵，即可完成“丁力”請假天數(shù)的計算，如圖所示?！岸×Α闭埣偬鞌?shù)計算441（8）選中單元格B10，輸入公式“=(B3＋B4)/23”，按Enter鍵，即可計算出“丁力”在該月的出勤率，并將計算結果設置為以百分數(shù)形式顯示，如圖所示?！岸×Α背銮诼视嬎?42（9）為了直觀地觀察員工出缺勤的總情況，要計算出該月全勤的人數(shù)。選中單元格B11，在編輯欄中輸入公式“=COUNTIF(B10:P10,100%)”，按Enter鍵，即可在單元格B11中顯示該月全勤人數(shù)，并將數(shù)值對齊方式設置為左對齊，如圖所示。全勤人數(shù)計算443（10）輸入完成后，將Sheet2標簽改為“出缺勤記錄統(tǒng)計表”，并將“出勤天數(shù)”與“請假天數(shù)”兩行的背景填充顏色設置為黃色，如圖所示。至此，完成了員工出缺勤記錄統(tǒng)計表的建立。表格完成后的效果4443.員工出缺勤獎金表的建立員工的出缺勤獎金與出缺勤情況是密切相關的，下面在Sheet3中建立員工出缺勤獎金表。（1）選中單元格A1，輸入公式“=YEAR(TODAY())&"年"&MONTH(TODAY())&"月出缺勤獎金表"”，按Enter鍵，即可完成出缺勤獎金表標題的輸入，同樣合并單元格區(qū)域A1:P1，并設置居中顯示，將字體設置為宋體、16號，如圖所示。輸入表標題445（2）輸入員工姓名等內容，設置自動調整行高和列寬，并設置表格邊框，如圖所示。

輸入員工姓名等內容后的效果446（3）公司員工的出缺勤獎金基數(shù)為200元，采用快速輸入的方法在單元格區(qū)域B3:P3輸入“200”，如圖所示。

輸入獎金基數(shù)447（4）由于獎金金額與員工的出缺勤情況關系密切，因此在“出勤天數(shù)”行并不直接輸入數(shù)據(jù)，而是利用公式與前面的數(shù)據(jù)聯(lián)系起來，這樣如果前面表的數(shù)據(jù)有改動，此表的數(shù)據(jù)就會自動更正。選中單元格B4，輸入公式“=出缺勤記錄統(tǒng)計表!B5”，按Enter鍵，如圖所示。

輸入“丁力”出勤天數(shù)448（5）在單元格B5中輸入公式“=出缺勤記錄統(tǒng)計表!B8”，按Enter鍵，即可完成“丁力”請假天數(shù)的輸入。同樣采用拖動鼠標快速輸入的方法，完成此行其他數(shù)據(jù)的輸入，如圖所示。

輸入請假天數(shù)449（6）在單元格B6中輸入公式“=出缺勤記錄統(tǒng)計表!B9”，按Enter鍵，并采用上述的方法完成此行數(shù)據(jù)的輸入，如圖所示。

輸入無故缺勤天數(shù)450（7）公司采用請假一天扣30元、無故缺勤一天扣100元的制度，因此應發(fā)獎金=獎金基數(shù)－請假天數(shù)×30－無故缺勤天數(shù)×100，但如果計算結果小于0，則應發(fā)獎金應為0。所以，對于單元格B7的輸入，采用如下公式“=IF(B3－B5*30－B6*100>0,B3－B5*30－B6*100,0)”，輸入完成后按Enter鍵，如圖所示。

輸入“丁力”應發(fā)獎金451（8）在單元格B8中輸入公式“=SUM(B7:P7)”，按Enter鍵，便完成了單元格區(qū)域B7:P7的求和，即本月獎金總額的輸入，將數(shù)字對齊方式設置為左對齊，并把此表標簽改為“出缺勤獎金表”，如圖所示。至此，完成了考勤系統(tǒng)的建立以及該月考勤各項內容的輸入。

輸入本月獎金總額任務2建立員工簡歷表和學歷信息統(tǒng)計表452學習目標1.能建立格式相對復雜的表格。2.能通過員工簡歷表來建立員工的學歷信息統(tǒng)計表。453任務描述簡歷在現(xiàn)代企事業(yè)單位中有著不可替代的作用，它影響著用人單位對應聘者的第一印象。本任務所建的簡歷表只是記錄了員工的一些最基本信息，以供用人單位進行學歷信息統(tǒng)計。本任務首先要建立一個基礎的簡歷表，如圖所示。454

所建簡歷表455為了避免員工的簡歷表信息輸入錯誤，這里采用填寫記錄單的方法來建立“學歷信息統(tǒng)計表”，對公司員工的學歷信息進行分類匯總，以統(tǒng)計員工的學歷情況。456相關知識所謂記錄單，簡單來說就是一個窗口，通過此窗口可以進行數(shù)據(jù)的輸入、查詢、刪除等。457458實踐操作1.簡歷表的建立選中單元格B1，并輸入內容“簡歷表”。在單元格區(qū)域B2:F4分別輸入“姓名”“性別”等標題內容。合并單元格區(qū)域I2:I4，并輸入“照片”。合并單元格區(qū)域B1:I1，并將“簡歷表”設置為居中顯示，字體設置為宋體、28號。將單元格區(qū)域G2:H2、G3:H3、G4:H4分別合并，并設置居中顯示。將單元格區(qū)域B2:I4的文字設置為宋體、16號，并居中顯示，調整列寬。表格上半部分輸入完成后的效果如圖所示。表格上半部分輸入完成后的效果4594602.學歷信息統(tǒng)計表的建立（1）表標題和各列標題的輸入。將Sheet2重命名為“學歷信息統(tǒng)計表”，在此工作表中，首先在單元格A1中輸入表標題，并將其設置為宋體、16號、居中顯示。在編輯欄中輸入單元格A2的公式“=YEAR(TODAY())&"年"&MONTH(TODAY())&"月"”，單元格中顯示年月，并將其設置為宋體、16號、右對齊顯示。在單元格區(qū)域A3:J3分別輸入各列標題，并將其設置為宋體、11號、居中顯示，調整各列寬，如圖所示。461標題輸入完成462（2）記錄單的添加。單擊“文件”|“選項”，打開“Excel選項”對話框，選擇左側的“自定義功能區(qū)”，在右側的“從下列位置選擇命令”中選擇“不在功能區(qū)中的命令”，在其列表中選擇“記錄單…”，在“自定義功能區(qū)”中選擇“主選項卡”，勾選“開始”復選框，單擊“新建組”按鈕，選中“新建組（自定義）”，單擊鼠標右鍵，選擇“重命名”，將其更名為“記錄單”，單擊“添加”按鈕，如圖所示，單擊“確定”按鈕即完成記錄單的添加。

添加“記錄單”按鈕463464（3）數(shù)據(jù)的輸入。選中所有列標題單元格，單擊“開始”|“記錄單”|“記錄單”按鈕，并打開其對話框，如圖所示。在各個框中輸入各列的基本信息，如圖所示。輸入完成后，單擊“新建”按鈕，即可完成“丁力”相關信息的輸入，如圖所示?！皩W歷信息統(tǒng)計表”記錄單的對話框465“丁力”基本信息的輸入466單擊“新建”按鈕后的效果467468在記錄單對話框中完成其他信息的輸入，輸入完成后的效果如圖所示。輸入完成后的效果469（4）數(shù)據(jù)的查詢和修改。單擊記錄單對話框中右側的“條件”按鈕，彈出如圖所示的對話框。在此對話框中輸入要查詢或修改的數(shù)據(jù)內容，如圖所示。輸入后，按Enter鍵，則工作表將定位到第一條符合此條件的數(shù)據(jù)，繼續(xù)查詢可單擊“下一條”按鈕。查詢和修改數(shù)據(jù)的對話框470查詢或修改的數(shù)據(jù)內容排序后的效果4713.分類匯總學歷信息統(tǒng)計表進行分類匯總前，首先對數(shù)據(jù)進行排序。這里按照“大專、本科、碩士、博士”的順序排列，如圖所示?！胺诸悈R總”對話框472選中數(shù)據(jù)區(qū)域，單擊“數(shù)據(jù)”|“分級顯示”|“分類匯總”按鈕，把“學歷”按“計數(shù)”的匯總方式進行匯總，如圖所示，完成后的效果如圖所示。473分類匯總后的效果任務3建立財務報表474學習目標1.能描述Excel在財務報表中的重要作用。2.能描述財務報表的組成及其相互關系。3.能建立財務報表，同時熟練運用前七個項目的相關操作方法。475任務描述財務報表由資產(chǎn)負債表、利潤表和現(xiàn)金流量表組成。本任務是建立這三個工作表，分別如圖所示。476資產(chǎn)負債表477利潤表478現(xiàn)金流量表479相關知識財務報表是記錄企業(yè)財務狀況的主要形式，它有一個月、一個季度、一年等多種類型，主要由資產(chǎn)負債表、利潤表和現(xiàn)金流量表三部分組成，下面對其進行簡單介紹。資產(chǎn)負債表根據(jù)會計等式“資產(chǎn)=負債＋所有者權益”編制。資產(chǎn)反映了企業(yè)所擁有的資產(chǎn)，包括固定、無形、流動等項目；負債反映了企業(yè)的債務；所有者權益反映了投資者和所有者的權益。利潤表可以直接反映企業(yè)的財務成果。本表分為實現(xiàn)利潤、利潤分配和補充資料三個部分。在此只介紹實現(xiàn)利潤部分，它主要反映了利潤總額的情況。480現(xiàn)金流量表通過現(xiàn)金的流入和流出的值反映現(xiàn)金的變化。依據(jù)《企業(yè)會計準則》，現(xiàn)金流量表包括以下兩個部分。（1）正表部分：反映企業(yè)經(jīng)營活動、投資活動、籌資活動產(chǎn)生的現(xiàn)金流量及其各項目流入、流出的總額和凈額。（2）補充資料部分：提供將凈利潤調節(jié)為經(jīng)營活動現(xiàn)金流量的全部內容，反映企業(yè)一定時期內影響資產(chǎn)或負債，但不形成當期現(xiàn)金收支的所有投資和籌資活動的信息。本任務要完成的是正表部分。481實踐操作1.資產(chǎn)負債表的建立（1）新建工作簿，在單元格A1中輸入“資產(chǎn)負債表”，合并單元格區(qū)域A1:F1，將“資產(chǎn)負債表”設置為宋體、20號，并居中顯示。在單元格A2中輸入如圖所示的相應內容，并合并單元格區(qū)域A2:F2。同時在單元格區(qū)域A3:F33輸入所有的文本數(shù)據(jù)，將其設置為宋體、11號。文本輸入完成后的效果如圖所示。482資產(chǎn)負債表483文本輸入完成后的效果484（2）選中單元格區(qū)域A3:F33，單擊“開始”|“字體”|“邊框”下拉按鈕，在其下拉菜單中選擇“所有框線”，即可完成邊框的設置，如圖所示。485邊框設置完成后的效果（3）選中B、C、E、F四列，單擊“開始”|“數(shù)字”組中的擴展按鈕，在打開的“設置單元格格式”對話框中的“數(shù)字”選項卡中，選擇左側“分類”中的“數(shù)值”，把右側的“小數(shù)位數(shù)”設為“2”，并勾選“使用千位分隔符（,）”復選框，單擊“確定”按鈕，即可完成數(shù)字格式的設置。按照如圖所示輸入數(shù)字內容。其中單元格區(qū)域B15:C15、B18:C18、B25:C25、B29:C29、B33:C33、E18:F18、E24:F25、E31:F31、E33:F33需要輸入公式，在這里暫不輸入。數(shù)值輸入完成后的效果如圖所示。486資產(chǎn)負債表487488數(shù)值輸入完成后的效果（4）在其他剩余的單元格中分別需要輸入以下公式。B15：=SUM(B5:B14)；C15：=SUM(C5:C14)；B18：=SUM(B17)；C18：=SUM(C17)；B25：=SUM(B22,B23,B24)；C25：=SUM(C22,C23,C24)；B29：=SUM(B27:B28)；C29：=SUM(C27:C28)；B33：=SUM(B15,B18,B25,B29,B30)；C33：=SUM(C15,C18,C25,C29,C30)；E18：=SUM(E5:E17)；F18：=SUM(F5:F17)；E24：=SUM(E20:E23)；F24=SUM(F20:F23)；E25=SUM(E18,E24)；F25：=SUM(F18,F24)；E31：=SUM(E27:E30)；F31：=SUM(F27:F30)；E33：=SUM(E25,E31)；F33：=SUM(F25,F31)。每輸入完一個公式，按Enter鍵即可。所有單元格輸入完成后的效果如圖所示。489490輸入完成后的效果（5）將通過合計得到的單元格進行格式設置。選中數(shù)據(jù)區(qū)域，單擊“開始”|“樣式”|“條件格式”下拉按鈕，在其下拉菜單中選擇“突出顯示單元格規(guī)則”|“文本包含”，在其對話框中選擇如圖所示的內容，單擊“確定”按鈕，設置完成后的效果如圖所示。491“文本中包含”對話框492條件格式設置完成后的效果（6）選中工作表最后一行數(shù)據(jù)單元格區(qū)域A33:F33，單擊“開始”|“樣式”|“單元格樣式”下拉按鈕，在其下拉菜單中選擇“主題單元格樣式”中的“紅色，著色2”，完成后的效果如圖所示。493494單元格樣式設置完成后的效果（7）設置完成后，用鼠標右鍵單擊Sheet1的標簽，選擇“重命名”，將“Sheet1”改為“資產(chǎn)負債表”。用鼠標右鍵單擊“資產(chǎn)負債表”，選擇“工作表標簽顏色”|“紅色”，完成后的效果如圖所示。495496標簽修改完成后的效果（8）完成后，選中單元格區(qū)域A1:F33，用鼠標右鍵單擊此工作表標簽，選擇“保護工作表”，在其對話框中進行如圖所示的設置，用戶可以自己設定密碼，單擊“確定”按鈕，即完成對工作表的保護設置。至此，資產(chǎn)負債表的建立完成。497“保護工作表”對話框2.利潤表的建立（1）雙擊Sheet2的標簽，將其改為“利潤表”，單擊鼠標右鍵，在快捷菜單中將工作表標簽設置成“藍色”。（2）在“利潤表”工作表中，按照《企業(yè)會計準則》中的相關要求，輸入如圖所示的文本。498499輸入文本后的效果（3）選中單元格區(qū)域A1:C1，單擊“開始“|“對齊方式”|“合并后居中”按鈕，將“利潤表”字體設置為宋體、加粗、20號。選中單元格區(qū)域A2:C2，單擊“開始”|“對齊方式”|“合并后居中”按鈕。選中單元格C3，單擊“開始”|“對齊方式”|“居中”按鈕。按住Ctrl鍵，并選中單元格A4、A9、A14、A19、A21，單擊“開始”|“對齊方式”|“左對齊”按鈕。按住Ctrl鍵，并選中單元格A5、A7、A10、A11、A15、A18、A20，

單擊“開始”|“對齊方式”|“增加縮進量”按鈕。按住Ctrl鍵，并選中單元格A6、A8、A12、A13、A16、A17，單擊兩次“開始”|“對齊方式”|“增加縮進量”按鈕，并設置數(shù)據(jù)區(qū)域邊框。500501格式設置完成后的效果格式設置完成后的效果如圖所示。（4）在工作表中選中單元格C9，輸入公式“=C6－C7－C8”，單擊“開始”|“字體”|“填充顏色”下拉按鈕，在其下拉菜單中選擇“黃色”，這樣輸入公式的單元格就和其他的單元格區(qū)別開了。在工作表中選中單元格C14，輸入公式“=C9＋C10－C11－C12－C13”，輸入后，單擊“開始”|“字體”|“填充顏色”下拉按鈕，將單元格填充為“黃色”。在工作表中選中單元格C19，輸入公式“=C14＋C15＋C17－C18”，輸入后，單擊“開始”|“字體”|“填充顏色”下拉按鈕，將單元格填充為“黃色”。在工作表中選中單元格C21，輸入公式“=C19－C20”，輸入后，單擊“開始”|“字體”|“填充顏色”下拉按鈕，將單元格填充為“黃色”。502503公式輸入完成后的效果公式輸入完成后的效果如圖所示。504其他數(shù)值數(shù)據(jù)輸入完成后的效果（5）輸入其他數(shù)值數(shù)據(jù)，完成后的效果如圖所示。505條件格式設置完成后的效果（6）選中單元格區(qū)域C4:C21，單擊“開始”|“樣式”|“條件格式”下拉按鈕，在其下拉菜單中選擇“數(shù)據(jù)條”|“漸變填充”|“藍色數(shù)據(jù)條”，這樣數(shù)據(jù)大小就可以直觀顯示，如圖所示。（7）用鼠標右鍵單擊此表的標簽，選擇“保護工作表”，進行保護設置。至此，利潤表的建立完成。506重命名Sheet3的效果3.現(xiàn)金流量表的建立（1）在Sheet3的標簽上單擊鼠標右鍵，選擇“重命名”，將工作表標簽改為“現(xiàn)金流量表”。單擊鼠標右鍵，在快捷菜單中將工作表標簽設置成“綠色”，如圖所示。507（2）在“現(xiàn)金流量表”工作表中，按照《企業(yè)會計準則》中的相關要求，輸入如圖所示的文本。其中“現(xiàn)金流量表”為宋體、20號，每個大標題下的副項目的格式為“增加一個縮進量”，“小計”和“凈額”單元格為居中顯示。508輸入文本后的效果509輸入文本后的效果510凍結窗格后滾動查看效果示意圖（3）凍結窗格。選中單元格B4，單擊“視圖”|“窗口”|“凍結窗格”下拉按鈕，在其下拉菜單中選擇“凍結窗格”。凍結窗格后滾動工作表，可以使前三行保持不動，其余部分被滾動查看，如圖所示。511單元格B8公式輸入完成后的效果（4）在工作表中選中單元格B8，輸入公式“=SUM(B5:B7)”，按Enter鍵確認，完成單元格B8的輸入，如圖所示。512（5）在工作表中選中單元格B13，輸入公式“=SUM(B9:B12)”，按Enter鍵確認。在工作表中選中單元格B14，輸入公式“=B8-B13”，按Enter鍵