1、 Excel數據處理與分析EXCEL是微軟公司office系列辦公軟件的組件之一，它是一個功能強大的電子表格程序。EXCEL不僅可以將整齊而美觀的表格呈現給用戶，還可以用來進行數據的分析和預測，完成許多復雜的數據運算，幫助使用者做出更加有根據的決策。同時它還可以將表格中的數據通過各種各樣的圖形、圖表的形式表現出來，增強表格的表達力和感染力。本章中通過員工工資表、公司銷售統計表這兩個案例，講解了日常工作中Excle的常用功能，使學生能夠掌握響應的Excle使用方法和使用技巧，提高表格的制作水平，從而提高工作效率。本章內容將通過大量的實例來學習Excel數據處理的基本操作。2.1數據輸入工作簿是指

2、在Excel 環境中用來存儲并處理工作數據的文件，它是由若干個工作表組成的。在Excel 中，可以說一個文件就是一個工作簿，工作簿窗口下方有若干個標簽，單擊其中一個標簽就會切換到該工作表。打開Excel 時，映入眼簾的工作界面就是工作表。它由眾多的行和列中的單元格排列在一起構成。工作表能存儲包含字符串、數字、公式、圖表和聲音等豐富的信息或數據，并能夠對這些信息或數據進行各種處理，同時能將工作表打印出來。當工作簿建立之后，就可以在工作簿的每一個工作表中輸入數據了。在Excel工作表的單元格中可以輸入的數據有文本、數字、日期、時間和公式等。一、輸入文本單元格中的文本包括任何字母、數字和鍵盤符號的組

3、合。每個單元格最多可包含32000個字符，如果單元格列寬容不下文本字符串，就要占用相鄰的單元格。如果相鄰單元格中已有數據，就會截斷顯示。二、輸入數字在Excel中，數字可用逗號、科學計數法或某種格式表示。輸入數字時，只要選中需要輸入數字的單元格，按鍵盤上的數字鍵即可。在excel中，輸入的數字數據長度在12為以上時，會自動轉變為科學記數格式。當數據以0開頭時則自動舍棄前面的0。如果要讓長度為12位以上的數字正常顯示，可以通過下面兩種方法來實現。1、實用“”符號輔助輸入在單元格中，先輸入“”（英文輸入狀態下的單引號），接著再輸入身份證號碼。輸入完成后，按鍵即可正常顯示輸入的身份證號碼。2、設置單

4、元格格式為“文本”格式后輸入三、輸入日期和時間日期和時間也是數字，但它們有特定的格式。在輸入日期時用斜線或短線分隔日期的年、月、日。例如，可以輸入“2010/01/26”或“2010-03-26”，如果要輸入當前的日期，按組合鍵Ctrl + ; (分號)即可。在輸入時間時，如果按12小時制輸入時間，需在時間數字后空一格，并鍵入字母a或p，分別表示上午或下午。例如，輸入10:40 p，按Enter鍵后四的結果是22:40:00，如果只輸入時間數字，Excel將按AM(上午)處理，如果要輸入當前的時間，按組合鍵Ctrl + Shift + ; (分號)即可。四、自動填充利用自動填充功能，可以快速地

5、復制數據、復制公式，加快數據的輸入速度。操作方法。選定單元格，拖動該單元格右下方的填充句柄，即將光標移至該單元右下角，至光標變成十字形狀（+）。按住鼠標左鍵不放，向下（向右）拖動。五、數據輸入技巧Excel 中有許多數據輸入的技巧，如在前面介紹過的自動填充功能等。本節將再介紹其他一些數據輸入的技巧。1. 在同一數據列中自動填寫重復錄入項Excel 具有數據記憶式鍵入功能，在同一行或同一列中，如果前面的單元格中已有數據輸入，即在某單元格輸入一個與前面單元格相同的數據時，Excel會自動顯示出該單元格后面的數據。但Excel只能自動完成包含文字的錄入項，或包含文字與數字的錄入項。2. 在多個單元格

6、中輸入相同的數據如果在工作表中有多處重復出現相同的數據，那么在數據輸入時，可首先將這些單元格同時選中，同時選中的操作方法為在選中第一個單元格后按下Ctrl鍵，再依次單擊其他單元格。然后通過編輯欄輸入數據，同時按下Ctrl+Enter鍵。此時數據將同時顯示在被選中的多個單元格中。例1在Excel中建立公司員工工資表以及輸入數據。1. 啟動Excel，新建一個工作簿。并保存為“員工工資表.xls”。2. 在“sheet1”工作表的A1單元格中輸入標題“員工工資表”，選取A1：O1單元格區域，合并該單元格區域，實質字體為“隸書”、“深藍色”、“加粗”、字號為“24”、“水平居中”。3. 在A2：O2

7、單元格區域中依次輸入“員工號”、“月份”、“姓名”等各列標題，字體設為“宋體”、“紅色”、“加粗”，結果如圖所示。圖2.1員工工資表標題行4. 在A3至O12單元格中輸入相應的數據。其中，在A3單元格內輸入數字“GD001”，選定A3單元格，拖動該單元格右下方的填充句柄至A12單元格，完成“員工號”列數據的輸入。5. 選中C3:C12，單擊“格式”“單元格”菜單命令，在彈出的【單元格格式】對話框中單擊“數字”選項卡，在“分類”列表框中選擇“文本”，單擊“確定”按鈕完成設置，在C3:C12單元格中可輸入18位數字的身份證號。6. 輸入“姓名”、“部門”、“職務”、“基本工資”、“職務工資”、“加

8、班津貼”、“獎金”、“缺勤”等列數據。2.2公式和函數的使用電子表格系統除了能進行一般的表格處理外，還應該具有數據計算能力。Excel 作為優秀的電子表格處理軟件，允許使用公式對數值進行計算。2.2.1關于單元格和區域引用引用的作用在于標識工作表上的單元格或單元格區域，并指明公式中所使用的數據的位置。通過引用，可以在公式中使用工作表不同部分的數據，或者在多個公式中使用同一個單元格的數值。還可以引用同一個工作簿中不同工作表上的單元格和其他工作簿中的數據。引用不同工作簿中的單元格稱為鏈接。默認情況下，Excel 使用 A1 引用樣式，此樣式引用字母標識列（從 A 到 IV，共 256 列），引用數

9、字標識行（從 1 到 65,536）。這些字母和數字稱為行號和列標。若要引用某個單元格，請輸入列標和行號。例如，B2 引用列 B 和行 2 交叉處的單元格。一、引用其他工作表中的單元格 下面的示例中，AVERAGE 工作表函數將計算同一個工作簿中名為 Marketing 的工作表的 B1:B10 區域內的平均值。鏈接到同一個工作簿中的另一張工作表上請注意，工作表的名稱和感嘆號 (!) 應位于區域引用之前。二、絕對引用與相對引用的區別一、相對引用 公式中的相對單元格引用（例如 A1）是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復制公

10、式，引用會自動調整。默認情況下，新公式使用相對引用。例如，如果將單元格 B2 中的相對引用復制到單元格 B3，將自動從 =A1 調整到 =A2。圖2.2 相對引用二、絕對引用 單元格中的絕對單元格引用（例如 $A$1）總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對引用保持不變。如果多行或多列地復制公式，絕對引用將不作調整。例如，如果將單元格 B2 中的絕對引用復制到單元格 B3，則在兩個單元格中一樣，都是 $A$1。圖2.3 絕對引用三、混合引用 混合引用具有絕對列和相對行，或是絕對行和相對列。絕對引用列采用 $A1、$B1 等形式。絕對引用行采用 A$1、B$1 等形式。如果公

11、式所在單元格的位置改變，則相對引用改變，而絕對引用不變。如果多行或多列地復制公式，相對引用自動調整，而絕對引用不作調整。例如，如果將一個混合引用從 A2 復制到 B3，它將從 =A$1 調整到 =B$1。圖2.4 混合引用2.2.2公式公式是對數據進行分析與計算的等式，使用公式可以對工作表中的數值進行加法、減法、乘法、除法等計算。所有的公式必須以符號“=”或“+”開始。一個公式是由運算符和參與計算的元素(操作數)組成的。公式的輸入操作類似于輸入文字數據，但輸入一個公式的時候應以一個等號( = )作為開始，然后才是公式的表達式。在單元格中輸入公式的步驟如下。(1)選擇要輸入公式的單元格。(2)在

12、編輯欄的輸入框中輸入一個等號( = )，或者在當前選擇的單元格中輸入一個等號( = )，然后輸入公式表達式。(3)單擊【確認】按鈕或按Enter鍵就可以得到計算結果。公式中常常包含對其他單元格的相對引用。例2.接著上面的操作，在員工工資表中輸入“應發工資”、“缺勤扣款”、“實發工資”等列的數據。操作如下：1. 單擊“員工工資表.xls”的sheet1工作表中的L3單元格，在單元格中輸入公式“=H3+I3+J3+K3”,按回車鍵得出應發工資。2. 在N3單元格中輸入公式“=H3/30*M3”，在O3單元格中輸入公式“=L3-N3”,結果如圖2.5所示。圖2.5輸入公式3. 按住CTRL鍵，單擊M

13、3、O3單元格，選中M3和O3單元格，單擊【格式】【單元格】，在彈出的【單元格格式】對話框中單擊“數字”選項卡，在“分類”列表框中選擇“數值”選項，在“小數位數”中輸入“2”，單擊“確定”按鈕完成設置，設置的數字格式將應用于M3:O3單元格區域中。圖2.6【單元格格式】對話框中“數字”選項卡設置4. 選定L3單元格，拖動該單元格右下方的填充句柄至L12單元格，完成“應發工資”列數據的輸入。使用同樣的方法，完成“缺勤扣款”和“實發工資”列數據的輸入。2.2.3函數函數可以理解為是一種excel已定義好的復雜公式，也可以認為是公式的簡寫形式。函數可以單獨使用，也可以在公式中調用函數。一、函數的語法

14、規定函數使用一些被稱為參數的數據按規定的順序或結構進行計算，單數可以是數字、常量、邏輯值或但與個引用等。函數執行后一般給出一個結果，這個結果成為返回值。函數的結構為：函數名（參數1，參數2，參數3，）二、手工輸入函數對于函數的輸入，可以采用手工的方法，首先單擊選定要輸入函數的單元格，鼠標單擊Excel編輯欄，鍵入一個等號“=”，此時單元格進入公示編輯狀態，在等號后按照公式的組成順序依次輸入各個部分，公式輸入完畢后，單擊編輯欄中的“輸入”(即“”)按鈕或按回車即可。例3. 在員工工資表中輸入“月份”、“性別”等列的數據。操作如下：1. 在D3單元格內輸入“=now()”，按回車結束輸入，D3單元

15、格內將自動顯示系統當前時間。2. 選中D3單元格，單擊鼠標右鍵，在彈出的快捷菜單中選擇“設置單元格格式”選項，在彈出的【單元格格式】對話框中單擊“數字”選項卡，在“分類”列表框中選擇“日期”選項，在“類型”文本框中輸入“2001年3月”，單擊“確定”按鈕完成設置，設置的日期類型將應用于單元格D3中，調整列寬至合適寬度。3. 可以根據身份證號求出性別的值。在E3單元格中輸入公式“=IF(MOD(MID(C3,17,1),2)=1,男,女)”，按“Enter”鍵得到計算結果。其中MID(C3,17,1)作用是將C3單元格中的字符串從第17位起，取1位。MOD(MID(C3,17,1),2)=1，作

16、用是判斷身份證號的第17位能否被2整除，即判斷是奇數還是偶數，如果MOD(MID(C3,17,1),2)=1，則C3單元格中第17位為奇數，否則為偶數。IF(MOD(MID(C3,17,1),2)=1,男,女)，作用是如果MOD(MID(C3,17,1),2)=1成立，返回“男”；否則，返回“女”。圖2.7 性別的計算4. 對于“應發工資”的輸入，除了可以用之前的公式以外，還可以用函數實現輸入。選定L3單元格，單擊“常用”工具欄中的“自動求和”按鈕，L3單元格中顯示求和函數，拖動鼠標選定H3:K3單元格區域，如圖2.8所示，按【Enter】鍵，系統將自動完成求和運算并顯示求和結果。圖2.8求和

17、函數5. 選定D3單元格，拖動該單元格右下方的填充句柄至D12單元格，完成“月份”列數據的輸入。使用同樣的方法，完成“性別”和“應發工資”列數據的輸入。具體數據如圖2.9所示。圖2.9員工工資表數據6. 將鼠標指向工作表標簽，單擊右鍵，在彈出的快捷菜單中選擇“重命名”，將工作表標簽更改為“工資表”。三、粘帖函數對于不熟悉的函數或較復雜的函數，手工輸入時容易出錯，可以采用【插入函數】對話框粘貼函數的方法輸入函數?！静迦牒瘮怠繉υ捒蚴荅xcel輸入公式的重要工具，下面以Count和countif函數的使用為例介紹粘貼函數的過程。Count 和countif函數都是統計函數，利用函數count可以計

18、算單元格區域中數字項的個數，利用函數countif可以計算給定區域內滿足特定條件的單元格的數目。例4在工作表工資明細表中，統計員工總人數以及全勤人數。操作步驟：4. 在工作表“工資表”A14單元格中輸入“總人數”，單擊選定要輸入函數的單元格B14。5. 單擊“編輯欄左側”的“插入函數”按鈕。彈出如下圖所示的【插入函數】對話框。圖2.10【插入函數】對話框輸入信息選項卡6. 如果對于所用的函數不確定，可以在“搜索函數”對應的文本框中輸入需求的簡單描述，單擊“轉到”按鈕，則在“選擇函數”列表中會有一些列的函數。逐個單擊函數，在【插入函數】對話框窗口的下部會出現關系所選函數的相關說明，可根據相關說明

19、選擇適用的函數。本例中，我們可以在“或選擇類別”列表中選擇“統計”，在“選擇函數”列表中選擇”count”，單擊“確定”按鈕，彈出如下“函數參數”窗口，填寫相應的參數，單擊“確定”按鈕，得出總人數。圖2.11【函數參數】對話框7. 在A15單元格中輸入“全勤人數”，單擊選定要輸入函數的單元格B15。8. 單擊“編輯欄左側”的“插入函數”按鈕。彈出如下圖所示的“插入函數”對話框。圖2.12【插入函數】對話框9. 在“或選擇類別”列表中選擇“統計”，在“選擇函數”列表中選擇”countif”，單擊“確定”按鈕，彈出如圖2.13“函數參數”窗口，填寫相應的參數。圖2.13 【函數參數】對話框10.

20、單擊“確定”按鈕，得出全勤人數。結果如圖2.14所示。圖2.14count和countif函數計算結果上述方法的最大優點就是引用的區域很準確，特別是三維引用時不容易發生工作表或工作簿名稱輸入錯誤的問題。2.3工作表的操作工作簿中可以包含多個工作表。新建Excel工作簿的時候，默認情況下含有3張工作表，標簽分別為“sheet1”、“sheet2”、“sheet3”，這和工具菜單下的選項命令的設置有關，如圖2.15所示。如果要再插入新的工作表，可以選擇“插入”菜單下的“工作表”命令。圖2.15 【選項】對話框窗口2.3.1復制和移動工作表復制和移動工作表的操作可以采用鼠標拖動或菜單設置兩種方式。例

21、5復制工作表“工資表”，具體操作如下：1 右擊選定“工資表”工作表的標簽，在出現的快捷菜單中選擇“移動或復制工作表”命令，打開【移動或復制工作表】對話框。2 在“下列選定工作表之前”列表框中選擇“Sheet2”，選定“建立副本”選項，如圖2.16所示。如果沒有選定“建立副本”選項，則只完成工作表的移動操作，不會復制工作表。圖2.16【移動或復制工作表】對話框3 單擊“確定”按鈕完成設置，在“Sheet2”工作表的前面復制了一個名為“工資表（2）”的工作表。圖2.17復制工作表的結果按住Ctrl鍵的同時用鼠標左鍵拖動工作表標簽到目標位置也可以復制工作表。如果不按住Ctrl鍵直接用鼠標左鍵拖動工作

22、表標簽到目標位置則為移動工作表提示：如果要跨工作簿進行工作表的移動或復制，則必須在“移動或復制工作表”對話框中的“工作簿”列表框中選擇目標工作簿。2.3.2工作表的重命名當工作簿中的工作表為多個的時候，默認的工作表標簽為“sheet1”、“sheet2”、“sheet3”為了便于快速區分工作表的內容，通常我們會給工作表起一個有意義的標簽。工作表的重命名常用的有以下三種方法：1、單擊“格式”菜單下的“工作表”子菜單中的“重命名”菜單命令，當前工作表的標簽為反白顯示，可以輸入新的標簽名，為當前工作表重命名。2、鼠標指向需要重命名的標簽，例如“工資表（2）”，單擊鼠標右鍵，在彈出的快捷菜單中選擇“重

23、命名”，工作表的標簽為反白顯示，可以輸入新的標簽名“工資分析表”作為當前工作表的新標簽，如圖2.18所示。圖2.18重命名后的工作表3、雙擊需要重命名的標簽，工作表的標簽為反白顯示，可以輸入新的標簽名為當前工作表重命名。2.3.3工作表的刪除如果要刪除多余的工作表，可執行以下操作：1右鍵單擊“Sheet3”工作表的標簽。2在出現的快捷菜單中選擇“刪除”命令，刪除“Sheet3”工作表。2.4常用的數據處理與分析像其他數據庫軟件創建的數據庫一樣，EXCEL數據也可以方便的對數據進行處理和分析。2.4.1數據有效性數據有效性可以指定允許的數據類型，以及可以接受的值得范圍。利用Excel對數據設置自

24、動檢測功能，減少誤操作。例6.在“工資表”中，要求身份證號碼的輸入長度必須是11位，可以利用數據有效性的功能進行數據的有效性設置。操作如下：1. 單擊工作表“工資表”標簽，選中“工資表”為當前工作表。2. 選擇設定區域C3：C12作為接受條件作用的單元格區域。3. 單擊“數據”菜單中的“有效性”命令，彈出【數據有效性】對話框，【數據有效性】對話框中的“設置”選項卡指明了數據類型及允許值的范圍；“輸入信息”選項卡能夠創建一個提示，告訴用戶哪種數據是允許的?！俺鲥e警告”選項卡是在輸入非法數據時，顯示錯誤的信息。4. 選擇“設置”選項卡，在“允許”項選擇“文本長度”，在“數據”項選擇“等于”，在“長

25、度”項輸入“18”，如圖2.19所示。圖2.19【數據有效性】對話框5. 單擊“輸入信息”選項卡，選擇“選定單元格時顯示輸入信息”，在“標題”項輸入“友情提醒”，在“輸入信息”文本框中輸入“請輸入18位的身份證號”，如圖2.20所示。圖2.20【數據有效性】的“輸入信息”設置6. 單擊“出錯警告”選項卡，設置警告提示信息，如圖2.21所示。圖2.21【數據有效性】窗口“出錯警告”設置7. 單擊“確定”按鈕，完成“身份證號碼”列數據項的有效性設定，此時選定“身份證號碼”列（區域C3：C12）中的任一單元格，都會出現提示信息，效果如圖2.22所示。圖2.22完成數據有效性設置后出現提示信息的效果8

26、. 如果輸入長度不是18位，則會彈出如圖2.23所示的出錯警告，提示“您輸入的身份證號不是18位的。”圖2.23出錯警告對話框例7在“工資表”中，利用數據有效性設定“獎金”輸入值范圍為0至5000之間操作如下：1. 2在“工資表”中選擇設定區域K3：K12作為接受條件作用的單元格區域。2. 單擊“數據”菜單中的“有效性”命令，彈出【數據有效性】對話框，在“允許”項選擇“整數”，在“數據”項選擇“介于”，在“最小值”項輸入“0”，在“最大值”項輸入“5000”，如圖2.24所示。圖2.24【數據有效性】對話框3. 單擊“輸入信息”選項卡，選擇“選定單元格時顯示輸入信息”，在“標題”項輸入“友情提

27、醒”，在“輸入信息”文本框中輸入“輸入值在0至5000之間”。4. 單擊“出錯警告”選項卡，設置警告提示信息，在“樣式”項中選擇“警告”，在“標題”項中輸入“錯誤”，在“錯誤信息”項中輸入“超出值范圍”。5. 單擊“確定”按鈕，完成“獎金”列數據項的有效性設定。2.4.2數據條件格式在Excel中提供了一個功能非常獨特的數據處理功能，它就是“條件格式”。通過數據條件格式的設置，可以將單元格中滿足指定條件的數據進行特定標記。例8在工資表中，設置“實發工資”大于等于7000元的以“紅色”顯示，“實發工資”大于等于5000元且小于7000的以“藍色”顯示，操作如下：1. 在“工資表”中選中設置數據條

28、件格式的單元格區域，即O3:O122. 單擊“格式”菜單下的“條件格式”命令，打開如圖2.25所示的【條件格式】對話框。圖2.25【條件格式】對話框3. 單擊【條件格式】對話框中第二個方框右側的下拉按鈕，選中“大于或等于”選項，在后面的方框中輸入數值“7000”。單擊“格式”按鈕，打開“單元格格式”對話框，將“字體”的“顏色”設置為“紅色”。設置后的對話框如圖2.26所示。圖2.26設置了一個條件后的【條件格式】對話框4. 按“添加”按鈕，并按照上面的操作設置好條件及格式設置，如圖2.27所示。圖2.27設置了兩個條件的【條件格式】對話框5. .設置完成后，按下“確定”按鈕，這時“工資表”中O

29、3:O12數據是按條件設置的要求以不同顏色顯示出來了。2.4.3數據排序“排序”功能是將數據按照特定的關鍵字進行排列，從而直觀地反映數據間的區別。一、單關鍵字排序例9在“工資分析表”中，按“實發工資”升序排序，即按“實發工資”從小到大排序。操作如下：1. 單擊工作表標簽“工資分析表”，切換到“工資分析表”。2. 單擊“實發工資”列的任意單元格。3. 單擊工具欄中的“升序”按鈕后，“實發工資”列中的數據自動按升序排列顯示，如圖2.28所示。圖2.28按“實發工資”升序排序后的結果二、多關鍵字排序例10在“工資分析表”中，“職務工資”列中可能會有相同的數據。遇到這樣的問題時，可以再設置次要關鍵字“

30、應發工資”，這樣，會在職務工資相同的情況下再按照應發工資的大小來排序。操作如下：1. 單擊“工資分析表”工作表數據區域內的任意單元格。2. 單擊“數據”菜單中的“排序”命令，打開【排序】對話框，在主要關鍵字中選擇“職務工資”按“升序”排序；次要關鍵字中選擇“應發工資”按降序排序，“我的數據區域”中，選擇“有標題行”，如圖2.29所示。圖2.29【排序】對話框3. 單擊“確定”后，排序結果如圖2.30所示。圖2.30排序后的結果三、讓數據按需排序如果你要將數據按某列進行排序，但是日常生活中對這種數據的大小排列可能是既不是按拼音順序，也不是按筆畫順序，怎么辦?可采用自定義序列來排序。例11在“工資

31、分析表”中將員工按其職務進行升序排序。注意，職務的大小既不是按拼音順序，也不是按筆畫順序，而是由小到大的順序為“職員”、“主管”、“經理”、“總監”。操作如下：1. 單擊“工具”菜單下的“選項”命令，打開“選項”對話框，單擊“自定義序列”選項卡，在“自定義序列”列表中選擇“新序列”，在“輸入序列”列表中按職務的從低到高輸入各種職務，如圖2.31所示。圖2.31【選項】對話框2. 單擊“添加”按鈕將剛才輸入的序列添加到“自定義序列”列表中，單擊“確定”按鈕退出。3. 單擊“工資分析表”工作表數據區域內的任意單元格，單擊“數據”菜單下的“排序”命令，打開“排序”對話框，在主要關鍵字中選擇“職務”按

32、“升序”排序。4. 單擊【排序】對話框中的“選項”按鈕，彈出“排序選項”對話框，單擊“自定義排列次序”的下拉按鈕，選中剛才自定義的序列（如圖2.32所示），單擊“確定”按鈕圖2.32【排序選項】對話框5. 按 “確定”按鈕返回，所有數據就按要求進行了排序，結果如圖2.33所示。圖2.33自定義排序后的結果2.4.4數據篩選數據管理時經常需要從眾多的數據中挑選出一部分滿足條件的記錄進行處理。篩選是是Excel提供的最為實用的數據處理功能之一，查找和處理區域中數據子集的快捷方法。篩選區域僅顯示滿足條件的行，該條件由用戶針對某列指定。Microsoft Excel 提供了兩種篩選區域的命令：自動篩選

33、和高級篩選。與排序不同，篩選并不重排區域。篩選只是暫時隱藏不必顯示的行。一、自動篩選數據“自動篩選”適用于簡單條件。例12，利用“工資分析表”復制一個工作表“工資分析表（2）”，在“工資分析表（2）”中篩選出銷售部員工的工資數據。操作如下：1. 按住Ctrl鍵的同時用鼠標左鍵拖動“工資分析表”工作表標簽，復制一個工作表“工資分析表（2）”2. 單擊“工資分析表（2）”中數據區域內的任意單元格，單擊【數據】菜單【篩選】下的【自動篩選】命令，打開自動篩選器，如圖2.34所示。圖2.34打開自動篩選器3. 單擊“部門”字段名后的自動篩選箭頭，在彈出的下拉列表中選擇“銷售”。這時，“工資分析表（2）”

34、中就只顯示了銷售部員工的工資數據，其他部門的數據被隱藏起來。如圖2.35所示。圖2.35篩選結果例13在“工資分析表（2）”中篩選出實發工資在5000元以上（含5000）的員工數據。操作如下：1. 單擊“工資分析表（2）”標簽，單擊“數據”菜單下的“篩選”子菜單中的“全部顯示”菜單命令，使該工作表中所有數據顯示出來。2. 單擊“實發工資”字段名后的自動篩選箭頭，在彈出的下拉列表中選擇“自定義”，彈出【自定義自動篩選方式】對話框。3. 在對話框中“實發工資”下面的下拉列表框中選擇“大于或等于”，在其右側的組合框中輸入“5000”，如圖2.36所示。圖2.36【自定義自動篩選方式】對話框4. 單擊

35、“確定”按鈕，返回工作表中。這時，工作表中多余的表頭被隱藏起來，如圖2.37所示。圖2.37自動篩選結果二、高級篩選高級篩選可以一次性對多個條件進行篩選。高級篩選與自動篩選不同，它要求在一個工作表區域內單獨指定篩選條件，稱為條件區域。條件區域由條件標題行和條件表達式組成，如果條件表達式是在同一行的不同單元格中，Excel 適用AND運算符連接，表示將返回匹配所有單元格中條件的數據，如果表達式是在條件區域中的不同行中，Excel適用OR運算符連接，表示匹配任何一個單元格中條件的數據都將返回。條件區域要與數據區域分開（即和數據區域隔開至少一行或一列）。高級篩選的功能有以下幾個：1、指定與兩列或兩列

36、以上有關的篩選條件及連接符【或】；2、對既定的某列指定3個或更多的篩選條件。例14在“員工工資表.xls”中，假設給出這樣的條件：篩選出科研、銷售、市場三個部門基本工資大于或等于2000的記錄。具體操作如下：1. 按住Ctrl鍵的同時用鼠標左鍵拖動“工資分析表”工作表標簽，復制一個工作表“工資分析表（3）”2. 單擊“工資分析表（3）”標簽切換到“工資分析表（3）”工作表，選中數據區域內的任意單元格。3. 選取“工資分析表（3）”工作表中的任意空白單元格并填寫條件區域，如圖2.38中下方的小方框所示。含義為：部門為“科研”并且基本工資大于或等于2000，或者部門為“銷售”并且基本工資大于或等于

37、2000，或者部門為“市場”并且基本工資大于或等于2000。圖2.38設置條件區域4. 單擊數據區域的任意一個單元格，單擊【數據】菜單中的“篩選”下的“高級篩選”命令，彈出【高級篩選】對話框。5. 在【高級篩選】對話框中，選定“將篩選結果復制到其它位置”選項，分別選取列表區域、條件區域和復制到的位置，復制到其它位置可選取“A20”單元格，設置如圖2.39所示。注意，選取的列表區域和條件區域內必須包含標題行。圖2.39【高級篩選】對話框6. 單擊“確定”，篩選后結果如圖2.40所示。圖2.40高級篩選結果2.4.5分類匯總分類匯總是指將數據按指定的類進行匯總分析，在進行分類匯總前先要對所匯總數據

38、進行排序。分類匯總可以將數據按照不同的類別進行統計。分類匯總不需要輸入公式，也不需要使用函數，Excel將自動處理并插入分類結果。在對數據分類匯總之前，我們要明確三個問題，第一個問題：分類的依據（也稱分類字段）是什么？第二個問題：匯總的對象是什么？第三個問題：匯總的方式是什么？例15在“員工工資表”中，按“性別”統計“職務工資”、“獎金”、“實發工資”平均值，具體操作如下：1. 按住Ctrl鍵的同時用鼠標左鍵拖動“工資分析表”工作表標簽，復制一個工作表“工資匯總表”2. 光標定位在“性別”列的任一單元格，單擊工具欄上的“升序”或“降序”按鈕，按“性別”進行排序。3. 單擊【數據】菜單中的“分類

39、匯總”命令，打開【分類匯總】對話框。4. 在“分類字段”下拉列表中選擇“性別”，在“匯總方式”下拉列表中選擇“平均值”，在“選定匯總項”列表中選定“職務工資”、“獎金”、“實發工資”選項，如圖2.41所示。圖2.41【分類匯總】對話框5. 單擊“確定”按鈕，得到匯總結果如圖2.42所示。圖2.42分類匯總結果提示：在上圖窗口左側顯示了分類匯總的標志，其中是“隱藏明細數據符號”；是分級顯示標記。單擊只顯示總的匯總值，單擊顯示各類匯總值，單擊顯示所有的明細數據。是“顯示明細數據符號”。如果想要刪除匯總的結果，可以單擊數據區域中任一單元格，然后單擊【數據】菜單中的“分類匯總”命令，打開【分類匯總】對

40、話框，在【分類匯總】對話框中單擊【全部刪除】按鈕，則只刪除匯總結果，對原有的數據不刪除。2.4.6合并計算合并計算可以將單獨工作表中的數據合并計算到一個主工作表中。這些工作表可以和主工作表在同一個工作簿中，也可以位于其他工作簿中。例16公司的產品都銷往全國各地，各個地區都會將銷售數據匯報到總公司，如果公司要統計產品的總銷售數據，可以使用“合并計算”功能，實現各地區銷售數量合并計算。假設各地區的銷售數據分別位于華東銷售情況表.xls、華南銷售情況表.xls、華北銷售情況表.xls、華中銷售情況表.xls等不同的工作簿文件中具體數據如圖2.43所示。圖2.43各地區的銷售數據操作步驟如下：1. 依

41、次打開華東銷售情況表.xls、華南銷售情況表.xls、華北銷售情況表.xls、華中銷售情況表.xls等不同的工作簿文件。2. 新建一個工作簿，保存為“總銷售情況表.xls”。3. 在“sheet1”工作表的A1單元格中輸入“總銷售情況表”，選擇A1:F1單元格區域，設置字體為“楷體”、“加粗”，字號為“16”，并單擊格式工具欄中的“合并及居中”按鈕，將A1:F1單元格區域合并為一個單元格。4. 選中A2單元格，單擊“數據”菜單下的“合并計算”，打開如圖2.44所示的【合并計算】對話框。圖2.44【合并計算】對話框5. 在“函數”中選擇“求和”。6. 在“引用位置”中單擊拾取器按鈕，單擊wind

42、ows任務欄中的“華南地區銷售情況表”按鈕，在“華南地區銷售情況表.xls”中選取華南銷售情況數量的區域，在本例中為A2:F6區域，并單擊拾取器按鈕返回到“合并計算”對話框中，單擊“添加”按鈕，將所選的區域添加到“所有引用位置”列表中。7. 根據同樣的操作，逐一將華北、華東、華中地區的銷售數量添加到“所有引用位置”列表中，如圖2.45所示。圖2.45所有引用完成后的【合并計算】對話框8. 設置完成后，單擊“確定”按鈕，即可合并計算出4各地區各季度產品的總銷售數量，如圖2.46所示。圖2.46合并計算的結果2.5圖表在數據分析中的應用圖表可以通過圖形的表達方式將復雜的數據表現出來，能夠更加直觀地

43、比較數據之間地變化，以及變化趨勢關系。2.5.1創建圖表Excel圖表可以將數據圖形化，幫助我們更直觀地顯示數據，使數據對比和變化趨勢一目了然，提高信息整理價值，更準確直觀地表達信息和觀點。圖表和工作表是互相鏈接的，當工作表中的數據發生改變時，圖表中對應項的數據也自動改變。創建圖表的操作方很多，我們將通過“圖表向導”創建的方法以為例子說明圖表的建立最基本的圖表。例17下表為某公司各地區銷售統計表，為了讓業績情況更直觀的顯示，請根據圖中的數據轉化為圖表。圖2.47“各地區銷售情況表”數據分析：利用“圖表向導”制作圖表，首要的一步是選擇生成圖表的數據區域，接下來根據“圖表向導”的提示一步一步操作即

44、可快速生成基本的圖表。操作步驟：1. 打開“各地區銷售統計表.xls”中的“銷售情況表”工作表，單擊“常用”工具欄上的“圖表向導”按鈕。或者選擇菜單“插入圖表”命令，打開如圖2.48所示的【圖表向導4步驟之1圖表類型】對話框。圖2.48【圖表向導4步驟之1圖表類型】對話框2. 單擊“標準類型”選項卡，在“圖表類型”中選擇“柱形圖”，在“子圖表類型”中選擇“簇狀柱形圖”，如上圖所示。單擊下方的“按下不放可查看示例”按鈕，可以看到將得到的圖表外觀的預覽。3. 單擊“下一步”按鈕，打開【圖表向導4步驟之2數據源】對話框，單擊“數據區域”最右邊的按鈕，然后按住“Ctrl”鍵并同時拖動鼠標，分別選擇“季

45、度”、“CRT電視”和“液晶電視”列作為數據區域，系列產生在“列”，如圖2.49所示。圖2.49【圖表向導4步驟之2數據源】對話框4. 單擊“下一步”按鈕，打開【圖表向導4步驟之3圖表選項】對話框，選擇“標題”選項卡，在“圖表標題”文本框中輸入“銷售圖表”，“分類（X）軸”文本框中輸入“季度”，“分類（Y）軸”文本框中輸入“數量”，如圖2.50所示；圖2.50【圖表向導4步驟之3圖表選項】對話框5. 如果想在圖表中看到圖表系列所對應的具體數據，可以單擊“數據標志”選項卡，在“數據標簽包括”中選擇“值”，如圖2.51所示。圖2.51【圖表向導4步驟之3圖表選項】對話框“數據標志”選項卡6. 單擊

46、“下一步”按鈕，打開【圖表向導4步驟之4圖表位置】對話框，如圖2.52所示，選定“作為其中的對象插入”。圖2.52【圖表向導4步驟之4圖表位置】對話框7. 單擊“完成”，結果如圖2.53所示。圖2.53創建圖表后的結果2.5.2圖表的編輯與設置圖表創建后，可以根據需要對圖表進行編輯與設置。1、移動圖表有時候，我們希望移動圖表到恰當的位置，讓工作表看起來更美觀，下面的操作我們將學習如何移動圖表。1. 單擊圖表的邊框，圖表的四角和四邊上將出現8個黑色的小正方形。2. 接著一直按住鼠標不放，移動鼠標，這時鼠標指針會變成四向箭頭和虛線，如圖2.54所示，繼續移動鼠標，同時圖表的位置隨著鼠標的移動而改變

47、。圖2.54移動圖表3. 用這樣的方法把圖表移動到恰當的位置即可。2、調整圖表的大小同樣地，我們也可能希望調整圖表的大小，有這種需求時進行下面的操作即可。1. 單擊圖表的邊框，圖表的四角和四邊上將出現8個黑色的小正方形。2. 將鼠標指針移動到某個正方形上，然后拖動它就可以改變圖表的大小。3、添加圖表數據例18下面我們將“銷售情況表”中的“等離子”電視列數據添加到圖表中，其操作如下：1. 先選定插入的圖表，再單擊【圖表】菜單中的“添加數據”命令，打開【添加數據】對話框，如圖2.55所示。圖2.55【添加數據】對話框2. 單擊“選定區域”右邊的拾取器按鈕，在工作表中選取C3:C6區域，（不含標題！

48、）再單擊拾取器按鈕返回如圖2.56所示的【添加數據】對話框。圖2.56選定了區域的【添加數據】對話框3. 單擊“確定”按鈕，在圖表中增加了等離子電視的數據系列。結果如圖2.57所示。圖2.57添加圖表數據后的結果4、刪除圖表數據在圖表中可直接刪除數據，工作表中的數據不發生任何變化。例如要刪除“等離子電視”柱，右擊柱狀圖中的“等離子電視”柱，在打開的快捷菜單中單擊“清除”命令即可在圖標中清除該系列。5、重新設置圖表選項充分利用 “圖表向導”以及“圖表選項”中提供的控制圖表細節的選項，可以得到更合乎我們需求的圖表。例19重新設置例17中生成的“銷售圖表”，其操作如下：1. 先選定插入的圖表，再單擊

49、【圖表】菜單中的“圖表選項”命令，打開【圖表選項】對話框。2. 單擊“標題”選項卡，在“圖表標題”中將“銷售情況表柱形圖”修改為“銷售圖表”。3. 單擊“網絡線”選項卡，選定“分類（X）軸”下“主要網格線”選項。4. 單擊“圖例”選項卡，選定“位置”下的“靠上”選項；單擊“數據表”選項卡，選定“顯示數據表”選項，修改后的效果如圖2.58所示。圖2.58重新設置圖表選項后的結果6、改變圖表位置默認情況下，生成的圖表是做為其中的對象插入到當前工作表的，如果想象改變圖表的位置，把圖表放在一個獨立的工作表的話，我們可以做如下操作：1、 選中要改變位置的圖表，單擊“圖表”菜單下的“位置”菜單命令，打開如

50、圖2.59所示的“圖表位置”對話框。2、 在“圖表位置”中選擇“作為新工作表插入”，單擊“確定”按鈕，即可完成圖表位置的改變。圖2.59【圖表位置】對話框7、更換圖表的類型當生成圖表后，我們有可能希望查看數據在不同圖表類型下的顯示效果，即更換當前圖表的類型，具體操作也是相當簡單的。1. 單擊圖表的邊框，選中圖表。然后選擇菜單“圖表”下的“圖表類型”命令，打開如圖2.60所示的【圖表類型】對話框。2. 我們修改圖表類型為“條形圖”，子類型默認，一直按住“按下不可放可查看示例”按鈕，即可預覽該圖表類型得到的效果圖。如果覺得滿意，單擊“確定”即可完成圖表類型的修改。圖2.60【圖表類型】對話框3.

51、接下來可以嘗試一下更改為其它的圖表類型后的顯示效果。通過這些操作，大家可以明白：同樣的數據在不同的圖表類型下，顯示的效果可以有很大的差別，而具體選擇哪種圖表類型，則由我們希望向觀眾表達的意思來決定。8、刪除圖表當我們想刪除圖表時，單擊圖表的邊框選中它，單擊“Delete”鍵即可刪除它。2.6數據透視表的應用數據透視表是一種對大量數據快速匯總和建立交叉列表的交互式表格，它具有能夠全面、靈活地對數據進行分析、匯總等功能。只需要改變對應的字段位置，即可得到多種分析結果。2.6.1創建數據透視表數據透視表的一個顯著的特點是可以對數據進行動態的分析，可以通過改變字段的位置，得到想要的分析結果。因此字段的

52、設置是建立數據透視表的關鍵，我們要學會分析進行統計的目的，然后合理設置字段即可。例20在員工工資表.xls的“工資表”中，增加一列數據“學歷”（如圖2.61所示），使用數據透視表按來分析各部門員工的學歷情況。圖2.61在“工資表”中增加了“學歷”數據操作方法如下：1. 選定“工資表”中數據區域內任意單元格。2. 單擊“數據”菜單中的“數據透視表和數據透視圖”命令，彈出【數據透視表和數據透視圖向導3步驟之1】對話框，如圖2.62所示。圖2.62【數據透視表和數據透視圖向導3步驟之1】對話框2. 在“請指定待分析數據的數據源類型”選項中選定“Microsoft office Excel數據列表或數

53、據庫”，在“所需創建的報表類型”選項中選中“數據透視表”。3. 單擊“下一步”按鈕，彈出如圖2.63所示的【數據透視表和數據透視圖向導3步驟之2】對話框。圖2.63【數據透視表和數據透視圖向導3步驟之2】對話框4. 單擊“下一步”按鈕，彈出如圖2.64所示的【數據透視表和數據透視圖向導3步驟之3】對話框，在“數據透視表顯示位置”選項中選中“新建工作表”。圖2.64所示的【數據透視表和數據透視圖向導3步驟之3】對話框5. 單擊“布局”按鈕，彈出【數據透視表和透視圖向導布局】對話框，用鼠標拖動對話框右側的“部門”字段按鈕，將其放置到“行”區域；“學歷”字段放置到“列”區域；拖動“職務”字段到“頁”

54、區域；拖動“編號”字段到“數據區域，匯總方式為“計數”如圖2.65所示”。如果需要改變數據的匯總方式，可雙擊數據區域中的“計數項：編號”，打開【數據透視表字段】對話框，如圖2.66所示，在“匯總方式”列表框中選擇合適的匯總方式，單擊“確定”按鈕，返回布局對話框，可改變數據的匯總方式。圖2.65【數據透視表和透視圖向導布局】對話框圖2.66【數據透視表字段】對話框6. 單擊“確定”按鈕，返回到【數據透視表和數據透視圖先到布局】對話框，單擊“完成”按鈕，向導將自動創建新的數據透視表，并在其中顯示統計值，如圖2.67所示。圖2.67數據透視表結果7. 雙擊該工作表標簽，將工作表重命名為“數據透視表”

55、2.6.2設置數據透視條件數據透視表生成后，可通過設置數據透視條件來顯示的數據。在例20創建的數據透視表中（如圖2.66），可做如下設置來顯示不同的數據結果。1.例如要分析各部門主管的學歷情況，可在如圖2.66所示的數據透視表中單擊“職務”右邊的B1單元格右側的下拉按鈕，在彈出的選項列表中選擇“主管”，單擊“確定”按鈕，數據透視表的結果如圖2.68所示。圖2.68關于各部門主管的學歷分析結果2.例如要顯示“銷售”部門的員工的學歷情況，可在如果2.66所示的數據透視表中單擊“部門”字段右側的下拉按鈕，在彈出的選項列表中將除了“銷售”以外的選項的復選項取消，如圖2.69所示，單擊“確定”按鈕完成設置。圖2.69顯示“銷售”部門的員工的學歷情況習題二一、選擇題1. 如要關閉工作簿，但不想退出Excel，可以單擊_。 A、“文件”下拉菜單中的“關閉”命令B、“文件”下拉菜單中的“退出”命