計算機信息技術(shù)基礎(chǔ)（第2版）課件 項目八 電子表格綜合處理

電子表格綜合處理

——員工工資管理與分析項目八

Excel具有強大的數(shù)據(jù)管理功能,可以方便地組織、管理和分析大量的數(shù)據(jù)信息。本項目通過對某公司員工工資數(shù)據(jù)的管理和分析，講述了如何使用記錄單輸入與瀏覽數(shù)據(jù)，如何使用數(shù)據(jù)排序、數(shù)據(jù)篩選、分類匯總等基本的數(shù)據(jù)管理方法，以及如何創(chuàng)建能夠使數(shù)據(jù)產(chǎn)生立體分析效果的數(shù)據(jù)透視表。

項目目標熟練掌握Excel工作簿中記錄單的編輯。熟練掌握Excel工作簿中的數(shù)據(jù)排序、篩選和分類匯總。熟練掌握Excel工作簿中數(shù)據(jù)透視表的創(chuàng)建和編輯。

項目說明

項目實施小張是某公司財務(wù)部的職員，為了提高工作效率和管理水平，她準備使用Excel工作表來管理公司員工的工資數(shù)據(jù)。圖8-1即為小張制作的“職員工資表”。記錄單的創(chuàng)建任務(wù)一創(chuàng)建一個Excel工作簿，以“工資表管理”命名并保存在桌面上，然后在工作表Sheet1中輸入表標題、表頭和數(shù)據(jù)，如圖8-1所示，再使用記錄單輸入與瀏覽工資數(shù)據(jù)，最后將工作表Sheet1更名為“工資表”。1.創(chuàng)建工作簿并輸入標題和表頭（1）新建一個Excel工作簿，以“工資表管理”命名并保存在桌面上。（2）輸入表標題“職員工資表”，設(shè)置字體為“黑體”，字號為“20”，并在A1:H1單元格區(qū)域居中。（3）在單元格區(qū)域A2:H2中依次輸入表頭“職員編號”、“姓名”、“部門”、“職務(wù)”、“基本工資”、“津貼”、“扣款額”和“實發(fā)合計”，設(shè)置字體為“楷體”，字號為“12”，水平居中，單元格填充“橙色”。2.使用記錄單輸入和瀏覽數(shù)據(jù)（1）單擊【快速訪問工具欄】最右側(cè)的下拉按鈕，在彈出的下拉列表中單擊【其他命令】，（2）在右側(cè)【從下列位置選擇命令】下拉列表框中選擇【不在功能區(qū)的命令】。下拉滾動條，找到【記錄單】，然后單擊【添加】按鈕，如圖8-3所示，再單擊【確定】按鈕。這時，快速訪問工具欄上已經(jīng)添加了【記錄單】按鈕，（3）從A3單元格起至G14先輸入若干行數(shù)據(jù)，如圖8-1所示，再選中已輸入數(shù)據(jù)區(qū)域中任意一個單元格，點擊【記錄單】按鈕，打開【Sheet1】對話框，如圖8-5所示，對話框右上角“1/11”表示該工作表現(xiàn)有11條記錄，當前顯示的是第1條記錄。（4）單擊按鈕【上一條】和【下一條】，查看工作表中的記錄。（5）單擊【新建】按鈕，記錄單會自動清空文本框，等待用戶逐條地輸入每個數(shù)據(jù)。輸入如圖8-6所示的記錄內(nèi)容，按Tab鍵可以在字段間切換，單擊【新建】按鈕或按Enter鍵，即可將記錄寫入工作表，并等待下一次輸入。應(yīng)用完畢后，單擊【關(guān)閉】按鈕關(guān)閉對話框。3.設(shè)置數(shù)據(jù)格式（1）將A列到G列數(shù)據(jù)全部輸入完成后，計算H列“實發(fā)合計”下的數(shù)據(jù)，選中H3單元格，在編輯欄中輸入公式：＝E3+F3－G3，得到第一條記錄的結(jié)果，再使用快速填充得到下面每位員工的實發(fā)合計結(jié)果。（2）選中數(shù)據(jù)區(qū)域E3:H14，設(shè)置“貨幣”格式，添加貨幣符號，保留兩位小數(shù)；再為數(shù)據(jù)區(qū)域A2:H14添加邊框，A2:H2填充“橙色”底紋，標題設(shè)置相應(yīng)格式，如圖8-1所示。（3）將工作表Sheet1重命名為“工資表”。數(shù)據(jù)排序任務(wù)二按照“實發(fā)合計”字段進行降序排序。1.將“工資表”工作表中的所有數(shù)據(jù)復制到“Sheet2”工作表中并將其重命名為“排序”。復制數(shù)據(jù)時，點擊“工資表”工作表左上角的【全選】按鈕，可直接選中整張工作表，再使用“復制”和“粘貼”功能將工作表粘貼到Sheet2工作表中的A1單元格即可。2.在“排序”工作表中按照“實發(fā)合計”字段降序排序方法（1）在“排序”工作表中，選中數(shù)據(jù)區(qū)域中任意一個單元格，單擊【數(shù)據(jù)】選項卡的【排序】按鈕，如圖8-7所示，打開【排序】對話框，在【主要關(guān)鍵字】下拉列表中選擇“實發(fā)合計”，并將次序設(shè)置為“降序”，如圖8-8所示，單擊【確定】按鈕方法（2）可在需要進行排序的“實發(fā)合計”列中選擇任意單元格，然后單擊【數(shù)據(jù)】選項卡的按鈕，即可看到排序結(jié)果。方法（3）可右鍵單擊需要進行排序的“實發(fā)合計”列中任意單元格，在彈出的菜單中選擇【排序】命令，然后在其子菜單中選擇【降序】命令。數(shù)據(jù)篩選任務(wù)三使用自動篩選顯示“開發(fā)部”中職務(wù)是“技術(shù)員”的職員工資記錄；使用自定義篩選顯示“實發(fā)合計”金額在“￥7000.00～￥8000.00”的職員工資記錄；使用高級篩選顯示“開發(fā)部基本工資<￥5000.00或財務(wù)部基本工資>￥5000.00”的職員工資記錄。1.使用自動篩選顯示“開發(fā)部”中職務(wù)是“技術(shù)員”的職員工資記錄。（1）將“工資表”工作表中的數(shù)據(jù)復制到Sheet3工作表中，將Sheet3工作表重命名為“自動篩選”。（2）在“自動篩選”工作表中，選中數(shù)據(jù)區(qū)域中任意一個單元格，單擊【數(shù)據(jù)】選項卡的【篩選】按鈕，此時Excel會自動在每個列標題右側(cè)出現(xiàn)黑色三角形按鈕（3）單擊“部門”右側(cè)的按鈕，在列表中取消選擇“全選”復選項，再選擇“開發(fā)部”，單擊【確定】按鈕（4）單擊“職務(wù)”右側(cè)的按鈕，在列表中取消選擇“全選”復選框，再選擇“技術(shù)員”，單擊【確定】按鈕（5）工作表的篩選結(jié)果如圖8-13所示?？梢?，執(zhí)行過篩選操作的列標題“部門”和“職務(wù)”右側(cè)由黑色三角形變成按鈕。2.使用自定義篩選顯示“實發(fā)合計”金額在“￥7000.00～￥8000.00”的職員工資記錄（1）將“工資表”工作表中的數(shù)據(jù)復制到Sheet4工作表中，將Sheet4工作表重命名為“自定義篩選”。（2）在“自定義篩選”工作表中，選中數(shù)據(jù)區(qū)域中任意一個單元格，單擊【數(shù)據(jù)】選項卡的【篩選】按鈕，此時Excel會自動在每個列標題右側(cè)出現(xiàn)黑色三角形按鈕。（3）單擊“實發(fā)合計”右側(cè)的按鈕，指向“數(shù)字篩選”，在其下拉列表中選擇【自定義篩選】命令，如圖8-14所示，打開【自定義自動篩選方式】對話框。（4）設(shè)置篩選條件，如圖8-15所示，“實發(fā)合計”下面的左側(cè)下拉列表框中選擇“大于或等于”，右側(cè)下拉列表中輸入“7000”，默認選中“與”單選項，并在其下面的左側(cè)下拉列表框中選擇“小于或等于”，右側(cè)下拉列表中輸入“8000”，單擊【確定】按鈕，滿足條件的篩選結(jié)果3.使用高級篩選顯示“開發(fā)部基本工資<￥5000.00或財務(wù)部基本工資>￥5000.00”的職員工資記錄。（1）將“工資表”工作表中的數(shù)據(jù)復制到Sheet5工作表中，將Sheet5工作表重命名為“高級篩選”。（2）進入“高級篩選”工作表，在空白區(qū)域輸入篩選條件，其中第一行為篩選項的字段名，第二行開始為對應(yīng)的篩選條件（3）選中數(shù)據(jù)區(qū)A2:H14任意一個單元格，單擊【數(shù)據(jù)】選項卡的【高級】按鈕，如圖8-18所示，打開【高級篩選】對話框。（4）在【高級篩選】對話框中，如圖8-19所示，選擇【方式】選項中的“在原有區(qū)域顯示篩選結(jié)果”，在【列表區(qū)域】文本框中選擇整個數(shù)據(jù)單元格區(qū)域A2:H14，在【條件區(qū)域】文本框中選擇輸入篩選條件的單元格區(qū)域D16:E18，單擊【確定】按鈕，滿足條件的篩選結(jié)果數(shù)據(jù)分類匯總?cè)蝿?wù)四以“部門”字段為分類字段，匯總出各部門職工工資實發(fā)合計的總數(shù)。再使用數(shù)據(jù)條查看全部職工扣款額。1.以“部門”字段為分類字段匯總出各部門職工工資實發(fā)合計的總數(shù)。（1）將“工資表”工作表中的數(shù)據(jù)復制到Sheet6工作表中，將Sheet6工作表重命名為“分類匯總”。（2）在“分類匯總”工作表中，按“部門”升序排序，選中數(shù)據(jù)區(qū)域中的任意一個單元格，單擊【數(shù)據(jù)】選項卡的【排序】按鈕，打開【排序】對話框，在【主要關(guān)鍵字】下拉列表中選擇“部門”，并將排序方式設(shè)置為“升序”，單擊【確定】按鈕。（3）再選中數(shù)據(jù)區(qū)任意一個單元格，單擊【數(shù)據(jù)】選項卡的【分類匯總】按鈕，如圖8-21所示，打開【分類匯總】對話框。在【分類字段】下拉列表中選擇“部門”，在【匯總方式】下拉列表中選擇“求和”，在【選定匯總項】下拉列表中選擇“實發(fā)合計”復選項創(chuàng)建數(shù)據(jù)透視表任務(wù)五以“工資表”工作表中的數(shù)據(jù)為數(shù)據(jù)源，以“部門”為報表篩選項，以“姓名”為行標簽，以“職務(wù)”為列標簽，以“實發(fā)合計”為求和項，在Sheet7工作表中創(chuàng)建數(shù)據(jù)透視表。在數(shù)據(jù)透視表中，只顯示“開發(fā)部”相關(guān)的數(shù)據(jù)。1.在Sheet7工作表中創(chuàng)建數(shù)據(jù)透視表（1）在“工資表”工作表中，選中數(shù)據(jù)區(qū)域任意一個單元格，單擊【插入】選項卡的【數(shù)據(jù)透視表】下拉按鈕，單擊【數(shù)據(jù)透視表】命令，（2）打開【創(chuàng)建數(shù)據(jù)透視表】對話框，單擊“表/區(qū)域”文本框右側(cè)的按鈕，選擇A2:H14單元格區(qū)域。再選擇放置數(shù)據(jù)透視表的位置，選中“現(xiàn)在工作表”，單擊“位置”文本框右側(cè)的按鈕，選擇Sheet7工作表中的A1單元格，然后單擊按鈕返回，單擊【確定】按鈕。1.在Sheet7工作表中創(chuàng)建數(shù)據(jù)透視表（3）創(chuàng)建數(shù)據(jù)透視表之后，可以在【數(shù)據(jù)透視表字段列表】任務(wù)窗格中編輯字段。默認情況下，該窗格中顯示兩部分：上部分用于添加和刪除字段，下部分用于重新排列和定位字段。用鼠標將上部分的“部門”字段拖動至下部分的“報表篩選”區(qū)域，將“姓名”字段拖動至“行標簽”區(qū)域，將“職務(wù)”字段拖動至“列標簽”區(qū)域，將“實發(fā)合計”字段拖動至“數(shù)值”區(qū)域（4）為數(shù)據(jù)透視表套用樣式，單擊【設(shè)計】選項卡，選擇需要套用的樣式，如果默認列表中的樣式不符合要求，可以單擊按鈕選擇更多的樣式。2.在數(shù)據(jù)透視表中只顯示“開發(fā)部”相關(guān)的數(shù)據(jù)（1）單擊數(shù)據(jù)透視表中“部門”后“（全部）”單元格右側(cè)的按鈕，在彈出的列表框中取消選擇“全部”，選中“開發(fā)部”，單擊【確定】按鈕。（2）數(shù)據(jù)透視表顯示“開發(fā)部”所對應(yīng)的數(shù)據(jù)，并求出總和。項目總結(jié)通過員工工資管理與分析案例，我們學習了在Excel中管理大量數(shù)據(jù)的應(yīng)用方法。主要包括：【記錄單】功能可以對大量數(shù)據(jù)進行添加記錄、瀏覽記錄、刪除記錄和查找記錄等操作；【排序】功能可以將數(shù)據(jù)按照用戶希望的順序進行調(diào)整；【篩選】功能可以將滿足用戶指定條件的數(shù)據(jù)顯示出來；【分類