第4章數據統計與分析Excel2010主講人姜永生4.1Excel2010概述Excel是MicrosoftOfficesystem中的電子表格程序。可以使用Excel創建工作簿并設置工作簿格式，以便分析數據。同時可以使用Excel跟蹤數據，生成數據分析模型，編寫公式以對數據進行計算，以多種方式透視數據，并以各種具有專業外觀的圖表來顯示數據。4.1Excel2010概述4.1.1Excel2010的窗口組成

標題欄：包含了快速工具欄、控制窗口的三個按鈕（最小化按鈕、還原按鈕、關閉按鈕）、程序名稱“MicrosoftExcel”、正在編輯的文檔名稱等。狀態欄：包含當前文檔的頁數/總頁數，總字數、語言種類、當前文檔的插入/改寫狀態、頁面視圖、顯示縮放比例。

4.1Excel2010概述4.1.1Excel2010的窗口組成

功能區各選項卡簡介：?“開始”選項卡“開始”選項卡中包括常用的剪貼板、字體、對齊方式、數字、樣式、單元格和編輯7個組。?“插入”選項卡“插入”選項卡包括表格、插圖、圖表、迷你圖、鏈接、文本、符號和特殊符號8個組?“頁面布局”選項卡“頁面布局”選項卡包括主題設置、頁面設置、調整為合適大小、工作表選項、排列5個組

4.1Excel2010概述1．Excel2010的窗口組成

功能區各選項卡簡介：?“公式”選項卡“公式”選項卡包括數據庫、定義的名稱、公式審核、計算4組，用于實現在Excel2010文檔中數據統計與計算等功能?“數據”選項卡“數據”選項卡包括獲取外部數據、連接、排序和篩選、數據工具和分級顯示5個組，用于在Excel2010文檔中進行數據統計和篩選方面的操作。?“審閱”選項卡“審閱”選項卡包括校對、語言、中文簡繁轉換、批注、更改5個組，主要用于對Excel2010文檔進行校對和更改等操作4.1Excel2010概述1．Excel2010的窗口組成

功能區各選項卡簡介：?“視圖”選項卡視圖”選項卡包括工作薄視圖、顯示、顯示比例、窗口和宏5個組，主要用于設置Excel2010操作窗口的視圖類型，以方便操作?“加載項”選項卡“加載項”選項卡包括菜單命令一個分組，加載項是可為Excel2010安裝附加屬性，如自定義的工具欄或其它命令擴展。?選項卡面板中的按鈕

按鈕分為上下（或左右）兩部分，單擊按鈕時上下兩部分間有分隔線。4.1Excel2010概述4.1.1Excel2010的窗口組成

功能區各選項卡簡介：?編輯欄、單元格、工作表

（1）編輯欄編輯欄左邊為名稱欄，用于顯示活動單元格或區域的地址。（2）工作區工作區用于編輯和處理數據。（3）工作薄與工作表工作簿是計算和儲存數據的文件，一個工作簿就是一個Excel文件，其擴展名為“.xlsx”或“.xls”。一個工作簿可以包含多個工作表，這樣可使一個文件中包含多種類型的相關信息，用戶可以將若干相關工作表組成一個工作簿。4.1Excel2010概述5.1.1Excel2010的窗口組成

（4）單元格Excel2010的工作表由1048576（2^20）行、16384（2^14）列組成，每一行列交叉處即為一單元格。一個單元格最多可以保存32000個字符，每個單元格的地址由列號和行號來引用。①單元格的引用工作表中單元格地址的引用方法分絕對引用、相對引用、混合引用。其區別是引用地址里是否有“$”符號，沒有就是相對引用。

相對引用：以列標和行號組成，如A1、B8，其中不包含符號“$”。

4.1Excel2010概述5.1.1Excel2010的窗口組成

絕對引用：列標和行號前都加符號“$”，如$A$1。

混合引用：列標或行號前加符號，如$A1（列絕對，行相對）；A$1（列相對，行絕對）。

②單元格區域的引用單元格區域的引用與單元格相同，也適用絕對引用、相對引用、混合引用三種引用方式。

A10:A20，表示A列第10到第20行的一系列單元格。B15:E15，表示第15行從B列到E列的一系列單元格。A10:E20，表示A10到E20矩形區域中的所有單元格。③交叉區域的引用兩個或多個單元格區域有交叉，其中部分單元格在兩個單元格區域中都包含，在計算數據時會容易出現重復計算。4.1Excel2010概述5.1.1Excel2010的窗口組成

兩個單元格區域間用空格分隔，如（C1:C8A4:E4）例“=sum（C1:C8A4:E4）”表示計算（C1:C8）與（A4:E4）兩個區域的數值之和。④不同工作表中單元格的引用引用另外一個不同工作表中的單元格，采用格式為：工作表名！單元格名。

如Sheet2！A4，表示引用工作表名為Sheet2中的A4單元格。

例“=average(Sheet3!B1:B10)”，表示利用函數計算本工作簿的“Sheet3”工作表中的B1:B10的平均值。

例“=sum(Sheet2:Sheet13!B5)”，表示把包括工作表Sheet2和工作表Sheet13之間所有工作表中的B5單元格的數據相加。4.1Excel2010概述4.1.2Excel2010的常用操作?“文件”選項卡“文件”菜單在整個窗口的左上角。選擇“文件”命令，彈出保存、另存為、打開、關閉、信息、最近所有文件、新建、打印、保存并發送、選項等常用的設置。

4.1Excel2010概述4.1.2Excel2010的常用操作?視圖設置Excel2010工作表包含三種視圖模式，即普通視圖、分頁預覽和頁面布局視圖，可以通過單擊狀態欄中右方的視圖切換按鈕，在三種視圖模式之間進行切換。?單元格和區域的選擇（1）選擇單元格在工作表中用粗邊框框住的單元格稱為“當前活動單元格”，可以通過鼠標指針單擊某單元格使其成為“當前活動單元格”；也可以用鍵盤上四個光標箭頭移動“當前活動單元格”。（2）選擇單元格區域選擇單元格區域即選定多個單元格

4.1Excel2010概述4.1.2Excel2010的常用操作?單元格和區域的選擇（3）選定特殊區域的快捷方式單擊工作表左側相應的行號選定一整行；

單擊工作表上方相應的列號選定一整列；

單擊工作表左上角的全選框選定整個工作表；

選定不連續的區域，則在選中一個區域之后，按住Ctrl鍵，再用上述方法選定其它區域；

選定連續的區域即在選中一個區域之后，按住Shift鍵，再用上述方法選定其它區域。

4.1Excel2010概述4.1.2Excel2010的常用操作?“設置單元格格式”對話框打開“設置單元格格式”對話框的常用方法有三種。方法一：切換到“開始”選項卡，單擊“數字”、“字體”、“對齊方式”組右下角的“對話框啟動器”按鈕。方法二：單擊“開始”→“格式”按鈕，彈出“格式”選項菜單，選擇“設置單元格格式”命令。方法三：光標指向單元格，單擊右鍵在彈出的快捷菜單中選擇“設置單元格格式”命令。打開“設置單元格格式”的對話框，其中包括“數字”、“對齊”、“字體”、“邊框”、“填充”、“保護”6個選項卡

4.1Excel2010概述4.1.2Excel2010的常用操作?“設置單元格格式”對話框（1）“數字”選項卡用于設置單元格中數字的類型與格式，Excel內置了許多數字格式，供選擇，如數值、日期、貨幣等。（2）“對齊”選項卡“對齊”選項卡中的設置，可以在單元格中對文本和數字進行定位、更改方向并指定文本控制功能。（3）“字體”選項卡“字體”選項卡，可以設置所選單元格中的字符的字體屬性，通過“預覽”框可預覽所做的設置。4.1Excel2010概述4.1.2Excel2010的常用操作?“設置單元格格式”對話框（4）“邊框”選項卡Excel中，可以給單個單元格、單元格區域周圍加邊框，還可以從單元格的左上角到右下角或從單元格的左下角到右上角畫線；可更改線條樣式、線條粗細或線條顏色。（5）“填充”選項卡“填充”選項卡上的設置，可以為選定的單元格設置背景色（6）“保護”選項卡“保護”選項卡提供“鎖定”、“隱藏”兩個選項，用來保護工作表中的數據和公式。4.2數據的輸入4.2.1文本輸入Excel文本包括中文、英文、數字或其他鍵盤能鍵入的符號，文本輸入時默認為左對齊。某些數字如電話號碼、郵政編碼常常當作字符處理。此時只須在輸入數字前加上一個撇號“`”，Excel將把它當作文本看待，采用左對齊。如果輸入的文本長度超出單元格寬度，若右邊單元格無內容，則擴展到右邊列；否則，截斷顯示。若出現“#####”字樣，則表示列寬不足。

4.2數據的輸入4.2.2數值輸入

Excel中，輸入的數值可以是數字0～9、+、-、（）、/、E、e、$、%、小數點“.”和千分位符號“,”等，數值輸入時默認為右對齊。在輸入分數時，為避免與日期相混淆，要求在數字前加一個0與空格。輸入負數時，可將負數置于括號“（）”中。Excel數值輸入與數值顯示未必相同，如輸入數字長度超出單元格寬度（采用“常規”格式的數字長度為11位），Excel自動以科學計數法表示。4.2數據的輸入4.2.3日期時間輸入

日期型數據，用斜杠“/”或減號“-”分隔日期的年、月、日部分。時間型數據，系統自動以24小時制表示。如果要以12小時制表示，則在輸入的時間后加一個空格，然后再輸入AM（上午）或PM（下午）。Excel將日期和時間視為數字處理。在鍵入了Excel可以識別的日期或時間數據后，單元格的格式會從“常規”數字格式改為內置的日期或時間格式。默認狀態下，日期和時間項在單元格中右對齊。如果Excel不能識別輸入的日期或時間格式，輸入的內容將被視作文本，并在單元格中左對齊。4.2數據的輸入4.2.3日期時間輸入

如果要在同一單元格中同時鍵入日期和時間，日期與時間之間要用空格分隔。輸入當前日期按組合鍵“Ctrl+；”，輸入當前時間按組合鍵“Ctrl+Shift+：”。4.2數據的輸入4.2.4設置單元格數據類型

常規單元格不包括任何特定的數據格式，通常系統按輸入的數據進行自動判別。給單元格輸入數據，可以先設置單元格數據類型，然后再輸入數據。?設置單元格的數據類型?自定義數字格式要創建自定義數字格式，可使用“設置單元格格式”對話框中的“數字”選項卡，請在“類別”列表中單擊“自定義”選項，然后在“類型”文本框中鍵入自定義數字格式。4.2數據的輸入4.2.5公式與函數輸入?輸入公式輸入公式時，有以下的規則：（1）在輸入公式前，先輸入一個“=”號或“+”號。（2）公式中使用的運算符主要有四種：算術運算符、比較運算符、文本運算符、引用運算符。算術運算符包括：+、-、*、/、%（百分數）和^（乘方）；比較運算符包括：=、＞、＞＝、＜、＜＝、；文本運算符：＆，用于連接兩個文本；

引用運算符包括：:（冒號）、，（逗號）、空格。括號運算：在Excel中，括號只有一種，即圓括號“（）”，平常書寫中的其它括號均要轉換為圓括號才能參加運算。4.2數據的輸入4.2.5公式與函數輸入?輸入函數

Excel提供了許多內置函數，對數據運算和分析帶來了方便。內置的函數包括：財務、日期與時間、數學與三角函數、統計、查找與引用、數據庫、文本、邏輯、信息等14大類。函數的語法形式為：函數名（參數1，參數2，…），其中的參數可以是常量、單元格、區域、區域名或其它函數。（1）函數的輸入方法：直接輸入法，粘貼函數法。（2）自動求和，Excel提供了“自動求和”功能，可以快捷地批量輸入sum函數。4.2數據的輸入4.2.5公式與函數輸入

?公式審核

Excel2010提供了公式審核功能，用于追蹤引用單元格，顯示公式、錯誤檢測等。通過“公式”選項卡中的“公式審核”組所提供的相應功能，來檢查公式使用情況。

4.2數據的輸入4.2.6數據的快速輸入

Excel中輸入有規律的數據，可以考慮使用數據自動輸入功能，方便、高效輸入等差、等比、自定義序列的數據。常用快速填充數據的方法主要有以下幾種：?利用鼠標拖動填充?利用命令填充?選擇列表填充?自定義序列?同時給多個單元格輸入數據?同時給多個工作表中輸入數據?數據有效性輸入

4.3數據的編輯4.3.1數據的編輯?數據修改數據修改有3種方法：一是選擇要修改的單元格，直接重新輸入；二是雙擊要修改的單元格，在單元格中修改；三是選定要修改的單元格，然后在編輯欄內進行修改。?數據刪除數據刪除有兩個概念：數據清除和數據刪除。數據清除是把單元格的內容清除，使之成為空單元格，數據清除后單元格本身仍留在原位置不變。數據刪除是把單元格連同單元格的全部內容都從工作表中刪除。

4.3數據的編輯4.3.1數據的編輯?數據移動和復制在Excel中，復制和移動單元格或單元格區域中的數據，既可以在同一張工作表中進行，也可以在不同的工作表之間進行。數據移動和數據復制都有可以：利用剪貼板移動；利用鼠標拖動。選擇性粘貼是指一個單元格含有多種特性，如內容、格式、批注等，另外它還可能是一個公式，含有有效規則等，數據復制時往往只須復制它的部分特性；此外復制數據的同時還可以進行算術運算、行列轉置等。這些都可以通過選擇性粘貼來實現。

4.3數據的編輯4.3.1數據的編輯?合并與拆分單元格Excel表格編輯過程中，通常會合并與拆分單元格，以符合編輯表格的需求，如表格的標題等。合并與拆分單元格，常用的方法如下：方法一：利用“開始”選項卡的快速合拼功能。方法二：利用“設置單元格格式”對話框。

4.3數據的編輯4.3.1數據的編輯?單元格、行、列的插入Excel編輯表格式，根據需要可隨時插入單元格、行、列，以調整表格滿足實際需求。?查找和替換查找與替換的使用方法與Word中相同，查找與替換是指從當前活動單元格開始搜索指定的內容。?給單元格增加批注雖然每個單元格可以輸入多達32000個字符，但往往不能夠同時表達所有的含義，可以給單元格增加批注，來標記與說明。

4.3數據的編輯4.3.1數據的編輯?撤消操作?快速設置表格格式通過“設置單元格格式”對話框，可以設置表格與單元格格式，Excel2010提供了更加快捷的方式。切換到“開始”選項卡，在“樣式”組中包含“套用表格格式”、“單元格格式”等選項，可快速的幫用戶設置選中表格或單元格格式。?單元格名稱單元格名稱是指給單元格重新定義的名稱。

兩大功能

定位：可通過名稱快速查找單元格的位置。

計算：單元格或單元格區域的名稱，可以直接引用到公式中，以便直觀的看到公式的含義。

4.3數據的編輯4.3.2拆分或凍結窗格

窗格是文檔窗口的一部分，以垂直或水平條為界限并由此與其他部分分隔開。在實際工作中，有時需要多窗口操作，或鎖定Excel工作表中的表格標題。切換到“視圖”選項卡，單擊“拆分”與“凍結窗格”按鈕，可實現分隔窗口或標題鎖定功能。

4.4數據統計與分析4.4.1數據的排序、篩選與分類匯總

?數據的排序（1）快速排序（2）多條件排序（3）按筆劃排序（4）自定義排序

?分類匯總分類匯總是對數據庫中的數據進行分類統計。分類匯總需要分兩步完成，先排序再匯總。

首先對數據按分類的字段排序，

其次執行分類匯總命令.

4.4數據統計與分析4.4.1數據的排序、篩選與分類匯總

?自動篩選與高級篩選（1）自動篩選Excel2010使用自動篩選來篩選數據，可以快速、方便地查找和使用單元格區域數據的子集。

執行自動篩選操作，數據清單中必須有列標號。（2）自定義自動篩選自動篩選完成后，若不能達到所需的要求，則可選擇“自定義篩選”命令，實現用自定義條件篩選所需的數據。4.4數據統計與分析4.4.1數據的排序、篩選與分類匯總

?自動篩選與高級篩選（3）高級篩選執行高級篩選命令，數據清單必須有列標題，同時需要建立條件區域來設置復雜的篩選條件。

①條件區域在使用“高級篩選”命令之前，必須指定一個條件區域，設置出符合條件的行或列，以定義篩選條件。條件區域至少有兩行組成，第一行為字段名，從第二行起輸入查找條件。條件區域中，若有多個條件（復合條件），需要在各條件間進行邏輯運算，其中包括“與”運算和“或”運算。

4.4數據統計與分析4.4.1數據的排序、篩選與分類匯總

“與”運算即多個條件表達式中若有一個條件為假，則其值為假。在條件區域輸入時，“與”運算的條件表達式要放置在同一行。“或”運算即多個條件表達式中若有一個條件為真，則其值為真。在條件區域輸入時，“或”運算的條件表達式要放置在不同行。

4.4數據統計與分析4.4.1數據的排序、篩選與分類匯總

②應用高級篩選③清除數據清單的篩選對于不再需要的篩選操作，可以將其功能清除，恢復數據清單原來的狀態。4.4數據統計與分析4.4.2數據透視表

數據透視表是一種可以快速匯總大量數據的交互式方法。使用數據透視表可以深入分析數值數據，它可以同時靈活變換三組需要統計的字段對一組數值進行分析和統計，統計可以是求和、平均值、計數、最大、最小等。數據透視表建立:單擊“插入”→“數據透視表”按鈕，通過操作向導來完成創建任務，最容易出錯的是“數據透視表字段列表”中“列”與“行”標簽、統計“數值”項的選擇與“值字段設置”。

4.4數據統計與分析4.4.3常用函數的使用

?條件分支IF函數IF函數用于判斷條件表達式（logical_test）是否成立，同時返回指定的值。函數格式為：IF(logical_test,[value_if_true],[value_if_false])?頻率分布統計函數