1、3.5 綜合案例案例分析本節通過建立一個工資表的Excel電子表格，使讀者進一步掌握電子表格的輸入技巧，如何查看數據量大的表格，了解數據透視表的使用。設計要求本電子表格共有四個工作表，其中，一個工作表為9月工資表，如圖3.88所示；一個工作表為9月水電讀數；一個工作表為應發工資統計圖，如圖3.89所示；一個工作表為數據透視表，如圖3.90所示。圖3.88 9月工資表圖3.89 應發工資統計圖圖3.90 數據透視表3.5.2設計步驟打開電子表格文件“工資表原始”，執行以下操作。1 工作表管理（1）修改工作表的名稱具體要求 將sheet1工作表改名為“9月工資表”操作步驟 在“Sheet1”標簽上

2、雙擊，“Sheet1”處于反白狀態，輸入新的工作表名稱：“9月工資表”，按回車鍵確認。（2）刪除工作表具體要求 刪除Sheet2工作表操作步驟 在“Sheet2”工作表標簽上單擊鼠標右鍵，選擇快捷菜單中的“刪除”命令。（3）復制工作表具體要求 將“水電表原始”工作簿的“9月水電讀數”工作表復制到本工作簿文件中。 步驟1打開“水電表原始”工作簿，切換至“9月水電讀數”工作表，選擇“編輯”|“移動或復制工作表”命令，打開“移動或復制工作簿”對話框。步驟2在“移動或復制工作簿”對話框中，如圖3.91所示。在“工作簿”下拉列表中選擇“工資表原始”，在“下列選定工作表之前”選擇“移至最后”，選中“建立副

3、本”復選框。單擊“確定”按鈕后，在本工作簿的最后，新增了一個“9月水電讀數”工作表。圖3.91“移動或復制”工作表對話框圖3.92錄入數據的快捷菜單圖3.93 從列表中選擇數據2 編輯“工資表”數據單擊“工資表”標簽，切換到“工資表”工作表。（1）插入行具體要求 在第11行前插入一行：林致遠，1974-2-8，長沙，財會部，科級，1430。操作步驟選中11行（姓名張志峰）的任一單元格作為活動單元格，選擇“插入”|“行”命令，則11行的前面插入了空行。在空行中輸入數據：林致遠，1974-2-8，長沙，財會部，科級，1430。技巧 輸入數據在輸入分公司、部門、職務等級這幾列的數據時，選中單元格后，

4、單擊鼠標右鍵，在快捷菜單中選擇“從下拉列表中選擇”，如圖3.91所示。單元格的下面出現列表，顯示出上面的行中曾輸入的數據，如圖3.92所示。用戶可直接從列表中選擇需要輸入的數據。（2）填充等差序列具體要求 在A3至A32單元格中填充工號：1到30。操作步驟 工號1-30是等差序列，可采取下列方式填充。Ø 在A3單元格輸入1。將鼠標放在A3單元格的填充柄上，按住Ctrl鍵，鼠標指針變為帶有加號的實心十字架。向下拖曳鼠標，填充到A32單元格。Ø 在A3單元格輸入1，在A4單元格輸入2。選中A3到A4單元格，鼠標指向A4單元格的填充柄，向下拖曳鼠標，填充到A32單元格。Ø

5、; 在A3單元格輸入1，選擇“編輯”|“填充”|“序列”命令，打開“序列”對話框。如圖3.94所示。在“序列產生在”選擇“列”單選按鈕，在“類型”選擇“等差序列”單選按鈕，在“步長值”文本框中輸入1，在“終止值”文本框中輸入30。 圖3.94 “序列”對話框圖3.95“數據有效性”對話框的“輸入信息”選項卡（3）在多個單元格中輸入相同的數據具體要求 在H3至H32單元格中輸入相同的生活補貼：220。操作步驟 在多個單元格中輸入相同的數據，可采取下列方式：Ø 在H3單元格輸入220，鼠標指向H3單元格的填充柄，向下拖曳鼠標，填充到H32單元格。Ø 選中H3到H32單元格，輸入

6、數據220，再按Ctrl+Enter鍵。（4）設置數據有效性具體要求 在G3到G32單元格設置數據有效性：1000到4000。步驟1選中G3到G32單元格區域，選擇“數據”|“有效性”命令，打開“數據有效性”對話框。步驟2在“數據有效性”對話框中，選擇“設置”選項卡，在“允許”下拉列表中選擇“小數”，在“數據”下拉列表中選擇“介于”，在“最小值”文本框中輸入“1000”，在“最大值”文本框中輸入“4000”。步驟3選擇“輸入信息”選項卡，如圖3.95所示，在“輸入信息”下拉列表中輸入“1000-4000”。步驟4選擇“出錯警告”選項卡，在“錯誤信息”下拉列表中輸入“基本工資最低1000，最高4

7、000”。當選中基本工資列的單元格為活動單元格時，下面出現標簽顯示設置的輸入信息。若輸入的數據小于1000或大于4000，將會打開出錯警告窗口，顯示所設置的出錯信息。3 自定義序列具體要求 將9月工資表的B3到B32單元格中的數據導入為自定義序列，在“9月水電讀數”的A2到A31單元格中填充此序列。輸入數據時，用戶可以填充“星期一、星期二、星期三”或“甲、乙、丙”等序列，這些都是系統預先設置的序列。通過“工具”|“選項”，用戶可根據自己的需要來定義序列。步驟1選擇“工具”|“選項”命令，打開“選項”對話框。步驟2在“選項”對話框中，選擇“自定義序列”選項卡，如圖3.96所示。單擊“從單元格中導

8、入序列”文本框右邊的按鈕，選定單元格區域B3到B32，單擊“導入”按鈕。各位員工的姓名“陳源、鄧小玲”被定義為一個序列。圖3.96 導入自定義序列步驟3切換到“9月水電讀數”工作表，在A2單元格中輸入“陳源”，鼠標指向單元格的填充柄，向下拖曳到第31行，將其他各位員工的名字將被填充出來。4 編輯公式首先，通過公式數計算第一位職工的崗位津貼、應發工資、水費、電費、個人所得稅、扣款合計和實發工資，再將公式填充到其他行。（1）計算崗位津貼具體要求 根據職務等級計算崗位津貼：廳級職務津貼為3000；處級職務津貼為2000；科級職務津貼為1000；辦事員職務津貼為500。操作步驟 選中I3單元格，輸入公

9、式=IF(F3="廳級",3000, IF(F3="處級",2000,IF(F3="科級",1000, 500)（2）計算應發工資具體要求 計算應發工資，應發工資為基本工資、崗位津貼和生活補貼的和。操作步驟 選中J3單元格，輸入公式=G3+H3+I3或=SUM(G3:I3)（3）計算水費具體要求 計算水費，水費為用水度數與水費單價的乘積。操作步驟 選中K3單元格，輸入公式=9月水電讀數!B2*9月水電讀數!F$2 由于輸入公式所在的工作表是“9月工資表”，需要引用的是“9月水電讀數”工作表的單元格，須在單元格名稱前加上表名和！。另外，

10、向下填充公式時，所引用的水費單價F 2單元格的名稱不應變化，須在2前要加上$,表示對行號的絕對引用。（4）計算電費具體要求 計算電費，電費為用電度數與電費單價的乘積。操作步驟 選中L3單元格，輸入公式=9月水電讀數!C2*水電表讀數!F$3（5）計算個人所得稅具體要求 根據應發工資計算個人所得稅，1000元以下不扣稅，10002000元之間扣稅5%，2000以上扣稅10%。操作步驟 選中M3單元格，輸入公式=IF（J3<1000,0,IF(J3<2000,(J3-1000)*0.05,1000*0.05+(J3-2000)*0.1)（6）計算扣款合計具體要求 計算扣款合計，扣款合計

11、為水費、電費和個人所得稅的和。操作步驟 選中N3單元格，輸入公式=K3+L3+M3或=SUM(K3:M3)（7）計算實發工資具體要求 計算實發工資，實發工資為應發工資減去扣款合計。操作步驟 選中O3單元格，在編輯欄中輸入公式=J3-N3（8）填充公式具體要求 將公式填充到其他行。操作步驟 選中I3到O3單元格區域，鼠標指向選定單元格區域右下角的填充柄，填充到第32行，釋放鼠標。5 設置表格格式（1） 設置單元格的數字格式具體要求 設置C3到C32單元格的格式為“年月日”，J3到N32單元格保留小數點后一位。步驟1選中C3到C32單元格，單擊鼠標右鍵，在快捷菜單中選擇“設置單元格格式”命令，打開

12、“單元格格式”對話框。選擇“數字”選項卡，如圖3.97所示。在“分類”列表框中選擇“日期”，在“類型”列表框中選擇“2001年3月14日”。步驟2選中J3到N32單元格，同樣，打開“單元格格式”對話框。選擇“數字”選項卡，如圖3.98所示，在“分類”的列表框中選擇“數字”, 在“小數位數”的數值框中輸入“1”。圖3.97 “單元格格式”對話框的“數字”選項卡圖3.98 “單元格格式”對話框的“數字”選項卡（2） 格式化單元格具體要求 合并A1到O1單元格，設置單元格對齊方式為居中，字體為華文彩云，字號為24，字形加粗，字體顏色為紅色。步驟1選中A1到O1單元格，單擊“格式”工具欄的“合并及居中

13、”按鈕。步驟2在“格式”工具欄的“字體”下拉列表中選擇“華文彩云”，“字號”下拉列表中選擇24，單擊“加粗”按鈕，單擊“字體顏色”按鈕右邊的小三角形，在其菜單中選擇“紅色”。（3） 設置單元格填充顏色具體要求 設置C3到F32及K3到N32的填充顏色為淺綠色，G3到J32及O3到O32單元格的填充顏色為淺黃色操作步驟 選中C3到F32單元格，按住CTRL鍵，再選中K3到N32單元格，單擊“格式”工具欄的“填充顏色”按鈕右邊的小三角形，在下拉菜單中選擇“淺綠色”。選中G3到J32單元格，按住CTRL鍵不動，再選中O3到O32單元格，在“填充顏色”按鈕的下拉菜單中選擇“淺黃色”。（4） 復制格式具

14、體要求 選中A2到O2單元格，設置居中對齊，字形加粗，填充顏色為藍色，字體顏色為白色。并將此格式復制到A3到B32單元格。步驟1選中A2到O2單元格，單擊“格式”工具欄的“居中”按鈕，“加粗”按鈕，單擊“填充顏色”按鈕右邊的小三角形，在其下拉菜單中選擇“深藍色”。單擊“字體顏色”按鈕右邊的小三角形，在其下拉菜單中選擇“白色”。步驟2選中A2單元格，選擇“編輯”|“復制”命令。步驟3選中A3到B32單元格，選擇“編輯”|“選擇性粘貼”命令，打開“選擇性粘貼”對話框。如圖3.99所示，選中“粘貼”下的“格式”單選按鈕。復制格式后，A2到B32單元格的格式為與A2單元格的格式一致，而數據并無變化。圖

15、3.99 “選擇性粘貼”對話框圖3.100“單元格格式”對話框的“邊框”選項卡（5）設置邊框具體要求 設置表格的外邊框為粗線，內邊框為細線。操作步驟 選中A2到O32單元格區域，選擇“格式”|“單元格”命令，打開“單元格格式”對話框。選擇“邊框”選項卡，如圖3.100所示。在“線條樣式”中選擇一種粗線，單擊“預置”的“外邊框”按鈕；在“線條樣式”中選擇一種細線，單擊“預置”的“內邊框”按鈕。（6）設置列寬具體要求 設置表格各列為最適合的列寬。操作步驟 鼠標指向A列的列號處，鼠標指針變為。按住鼠標左鍵不動，向右拖曳鼠標，直到O列的列號，選中A到O列。選擇“格式”|“列”|“最適合的列寬”命令，系

16、統根據各列的內容自動調整各列的寬度。設置完成后，9月工資表如圖3.101所示：圖3.101 格式化后的表格6.查看數據當用戶要處理數據量大的工作表時，可以通過改變顯示比例，暫時地隱藏列，凍結窗格來查看工作表。（1）改變顯示比例具體要求 改變工作表的顯示比例。操作步驟 在“常用”工具欄的“顯示比例”下拉列表框中選擇或輸入顯示比例，可放大或縮小數據表。（2）隱藏列具體要求 隱藏K列到M列，再取消隱藏。操作步驟 鼠標指向K列的列號處，鼠標指針變為。按住鼠標左鍵不動，向右拖曳鼠標，直到M列的列號，選中K列到M列。在列號上單擊鼠標右鍵，在快捷菜單中選擇“隱藏列”命令，或選擇“格式”|“列”|“隱藏”命令

17、，這幾列暫時不會顯示，被隱藏起來。當需要查看隱藏列的時候，選中跨越了隱藏列的J列到N列，在列號上單擊鼠標右鍵，在快捷菜單中選擇“取消隱藏”命令，或選擇“格式”|“列”|“取消隱藏”命令，這幾列又顯示出來。（3）凍結窗格具體要求 將工作表的1、2行和A、B列凍結起來。操作步驟 選中C3單元格，選擇“窗口”|“凍結窗格”命令，則C3單元格上面的1、2行和左邊的A、B列被凍結起來。當窗口向下滾動的時候，第1、2行不會滾動；當窗口向右滾動的時候，第A、B列不會滾動。選擇“窗口”|“取消凍結窗格”命令，用戶可取消窗口的凍結。7.數據處理（1）數據排序具體要求 數據首先按職務等級從低到高排列，職務等級相同

18、的按基本工資從高到低排列。默認情況下，漢字是按拼音順序排列，職務等級將按“辦事員、處級、科級、廳級”的順序排列。如果要求按職務高低來排列數據，首先，要將“辦事員、科級、處級、廳級”定義為一個序列；然后，在排序時設置按照自己定義的序列來排列。步驟1選擇“工具”|“選項”命令，打開“選項”對話框。選擇“自定義序列”選項卡，如圖3.102所示。單擊“自定義序列”列表框的“新序列”，在“輸入序列”編輯框中輸入序列中的各項值，單擊“添加”按鈕，將“辦事員、科級、處級、廳級”定義為一個序列。圖3.102 添加自定義序列步驟2將工資表數據區域中任一單元格作為活動單元格，選擇“數據”|“排序”命令，打開“排序

19、”對話框。步驟3在“排序”對話框中，如圖3.103所示。在“主要關鍵字”下拉列表中選擇“職務等級”；在“次要關鍵字”選擇“基本工資”，再選擇“降序”單選按鈕。步驟4單擊“排序”對話框的“選項”按鈕，打開“排序選項”對話框，如圖3.104所示。在“自定義排序次序”的下拉列表中選擇自定義的序列“辦事員、科級、處級、廳級”。圖3.103“排序”對話框圖3.104“排序選項”對話框（2）自動篩選具體要求 篩選出實發工資最高的10名職工，再取消篩選。篩選出所有姓王和姓楊的職工，按姓名的升序排列。步驟1將工資表數據區域中任一單元格作為活動單元格，選擇“數據”|“篩選”|“自動篩選”命令，在每列的列名上出現

20、小三角形。步驟2單擊“實發工資”列名的三角形，打開下拉菜單，選擇“前10個”命令，打開“自動篩選前10個”對話框，如圖3.105所示。單擊“確定”按鈕，篩選出實發工資最高的10名員工。步驟3在“實發工資”列名的下拉菜單中選擇“全部顯示”命令，則取消了篩選。圖3.105“自動篩選前10個”對話框步驟4單擊“姓名”列名的三角形，在下拉菜單中選擇“自定義”命令，打開“自定義自動篩選方式”對話框。步驟5 在“自定義自動篩選方式”對話框中，如圖3.106所示。在符號下拉列表中選擇“始于”，在條件下拉列表中輸入“王”。單擊“或”單選按鈕，在下一行的符號下拉列表中選擇“始于”，在條件下拉列表中輸入“楊”。在

21、“姓名”列的下拉菜單中選擇“升序排序”命令，篩選的數據如圖3.107所示。圖3.106“自定義自動篩選方式”對話框圖3.107按姓名篩選的數據 （3）高級篩選具體要求 篩選出沙和北京的所有員工及所有職務為廳級的員工，取消篩選。步驟1在工資表數據區域外的單元格輸入篩選條件，如圖3.108所示。第一行為列名，下面分別設置對該列數據要篩選的條件。 步驟2定位到工資表數據區域中任一單元格，選擇“數據”|“篩選”|“高級篩選”命令，打開“高級篩選”對話框。步驟3在“高級篩選”對話框中，如圖3.109所示，系統自動設置了列表區域。單擊條件區域右邊的按鈕，選定剛才輸入條件的單元格區域。單擊“確定”按鈕，篩選

22、出的數據如圖3.110所示步驟4選擇“數據”|“篩選”|“全部顯示”命令，取消篩選。圖3.108自定義篩選條件圖3.109“高級篩選”對話框圖3.110按高級篩選篩選出來的數據（4）數據匯總具體要求 統計出各個分公司的人數，應發工資和實發工資的和步驟1在數據匯總以前，首先要按分類字段進行排序。定位到分公司所在區域（D2到D32）的任一單元格，單擊“常用”工具欄的“升序排序”按鈕，同一分公司的員工排列在一起。步驟2選擇“數據”|“分類匯總”命令，打開“分類匯總”對話框。如圖3.111所示，在“分類字段”的下拉列表中選擇“分公司”，在“匯總方式”下拉列表中選擇“求和”，在“選定匯總項”列表框中，選

23、中需要統計的“應發工資”、“實發工資”字段前的復選框。圖3.111“分類匯總”對話框圖3.112“分類匯總”對話框步驟3再次選擇“數據”|“分類匯總”命令，打開“分類匯總”對話框。如圖3.109所示，在“分類字段”的下拉列表中選擇“分公司”，在“匯總方式”下拉列表中選擇“計數”，在“選定匯總項”列表框中，選中需要統計的“姓名”字段前的復選框，去掉“替換當前分類匯總”復選框。數據表中顯示出統計結果，如圖3.113所示。在數據清單的左上角單擊分級顯示符號“2”，顯示出按分公司對應發工資和實發工資求和的匯總結果；單擊分級顯示符號“3”，顯示出兩次匯總的結果；單擊分級顯示符號“4”，顯示出明細的數據。

24、在“分類匯總”對話框中，單擊“全部刪除”按鈕，則刪除匯總的數據。圖3.113按分公司分類匯總的結果（5）數據透視表具體要求 在新的工作表中建立數據透視表，以“分公司”為頁字段，“部門”為行字段，“職務等級”為列字段字，“姓名”作為數據項，統計出各部門各職務等級的人數。知識點 數據透視表數據透視表是一種對數據進行交叉分析的三維表格。它將數據的排序、篩選和分類匯總三個過程結合在一起，可以轉換行和列以查看源數據的不同匯總結果，可以顯示不同頁面以篩選數據，還可以根據需要顯示所選區域中的明細數據，非常便于用戶組織和統計數據。步驟1將工資表數據區域中任一單元格作為活動單元格，選擇“數據”|“數據透視表和數

25、據透視圖”命令，打開“數據透視表和數據透視圖向導”對話框。第一步是指定數據源類型，如圖3.114所示，選擇“數據列表或數據庫”單選按鈕。單擊“下一步”按鈕。圖3.114選擇數據透視表數據類型步驟2第二步是指定數據源區域，如圖3.115所示，系統會自動設定一個數據源區域。圖3.115選定數據透視表數據源步驟3第三步是選擇數據透視表顯示位置，如圖3.116所示，選擇“新建工作表”單選按鈕，單擊“完成”按鈕。圖3.116選擇數據透視表位置系統新建一個工作表，如圖3.117所示，用戶需設置數據透視表的頁、行、列、數據項以確定匯總不同組合的數據透視表報表。圖3.117新建的數據透視表步驟4從右邊列出的字

26、段列表中選擇所需的字段拖到左邊相應的框中。將“分公司”字段拖至頁框，“部門”字段拖至行框，“職務等級”字段拖至“列”框，“姓名”字段拖至“數據項”，則統計出各部門各職務等級的人數，結果如圖3.118所示。圖3.118設置后的數據透視表l 用戶可以按頁框中的字段篩選數據透視表。單擊“分公司”的小三角形，在下拉菜單中選擇“長沙”，如圖3.119所示，則只顯示出長沙分公司的數據。如果要取消篩選，選擇下拉菜單中的“全部”即可。l 可以按行、列中的字段篩選數據透視表。單擊職務等級右邊的小箭頭，在列表中取消“處級、廳級”復選框的選擇，單擊“確定”按鈕，則匯總結果只統計辦事員、科級的人數，如圖3.120所示

27、。圖3.119篩選長沙分公司的數據圖3.120篩選職務等級后的數據l 可以添加要統計的數據項。如果還要統計各部門各職務等級的實發工資的和。在字段列表中選擇“實發工資”項，拖曳到“數據項”中。匯總結果如圖3.121所示。圖3.121添加數據項后的數據透視表l 可以改變數據項的統計方式。如果要統計的是實發工資的平均值，在“求和項：實發工資”上單擊鼠標右鍵，在快捷菜單中選擇“字段設置”命令，打開“數據透視表字段”對話框，如圖3.122所示。在“匯總方式”下選擇“平均值”，單擊“確定”按鈕，則匯總結果如圖3.123所示。圖3.122數據透視表字段對話框圖3.123改變統計方式后的數據透視表l 可以刪除

28、要統計的數據項。如果不需統計實發工資的平均值，單擊數據右邊的小箭頭，在列表中取消“平均值項：實發工資”復選框的選擇，單擊“確定”按鈕，則實發工資的平均值項被移除。l 可以靈活地設置頁、行、列、數據項的字段用戶可以根據需要靈活設置各字段。例如，將“分公司”字段拖至頁字段，將“職務等級”從數據透視表中拖曳出來，則匯總結果變為各分公司各部門的人數，如圖3.124所示。l 可以生成數據透視圖單擊“數據透視表”工具欄的“圖表向導”按鈕，則系統自動生成自動透視圖，如圖3.125所示。類似于數據透視表，數據透視圖也可以重新進行字段的設置，進行數據的篩選。圖3.124重新設置行、列字段的數據透視表圖3.125

29、數據透視圖8.圖表處理（1）插入圖表具體要求 根據各位職工的姓名和應發工資生成數據點折線圖，設置圖表標題為“應發工資統計圖”，圖表放在新的工作表“工資圖”中。步驟1選中B2到B32單元格區域，再按住CTRL鍵不動，選中J2J32單元格區域。步驟2單擊“常用”工具欄的“圖表向導”按鈕，打開“圖表向導”第一步對話框，如圖3. 126所示。在“圖表類型”的列表中選擇“折線圖”，在“子圖表類型”列表中選擇“數據點折線圖”。步驟3在“圖表向導”第二步對話框中，如圖3.127所示。在“數據區域”文本框顯示：=9月工資表!$B$2:9$B$32,9月工資表!$J$2:$J$32。圖3.126“圖表向導”第一

30、步圖3.127“圖表向導”第二步步驟4在“圖表向導”第三步對話框中，如圖3.128所示，在“圖表標題”的文本框中輸入“應發工資統計圖”。步驟5在“圖表向導”第四步對話框中，設置圖表的位置，如圖3.129所示。選擇“作為新工作表插入”單選按鈕，并輸入工作表的名字“工資圖”，單擊“完成”按鈕。系統產生一個新的工作表“工資圖”顯示數據點折線圖，同時打開“圖表”工具欄。圖3.128“圖表向導”第三步圖3.129“圖表向導”第四步（2）設置圖表選項具體要求 設置圖表中不產生圖例，顯示每個數據標記的數值。步驟1在圖表區的空白處單擊鼠標右鍵，彈出快捷菜單，選擇“圖表選項”命令，打開“圖表選項”對話框。步驟2在“圖表選項”對話框中，選擇“圖例”選項卡，如圖3.130所示，取消“顯示圖例”復選框，則圖表中不顯示圖例。步驟3在“圖表選項”對話框中，