數(shù)據(jù)統(tǒng)計與分析

第4章數(shù)據(jù)統(tǒng)計與分析Excel2010主講人姜永生4.1Excel2010概述Excel是MicrosoftOfficesystem中的電子表格程序?？梢允褂肊xcel創(chuàng)建工作簿并設(shè)置工作簿格式，以便分析數(shù)據(jù)。同時可以使用Excel跟蹤數(shù)據(jù)，生成數(shù)據(jù)分析模型，編寫公式以對數(shù)據(jù)進(jìn)行計算，以多種方式透視數(shù)據(jù)，并以各種具有專業(yè)外觀的圖表來顯示數(shù)據(jù)。4.1Excel2010概述4.1.1Excel2010的窗口組成

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

4.1Excel2010概述4.1.1Excel2010的窗口組成

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

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

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

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

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

功能區(qū)各選項(xiàng)卡簡介：?編輯欄、單元格、工作表

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

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

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

4.1Excel2010概述5.1.1Excel2010的窗口組成

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

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

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

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

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

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

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

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

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

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

單擊工作表上方相應(yīng)的列號選定一整列；

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

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

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

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

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

4.2數(shù)據(jù)的輸入4.2.2數(shù)值輸入

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

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

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

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

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

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

?公式審核

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

4.2數(shù)據(jù)的輸入4.2.6數(shù)據(jù)的快速輸入

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

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

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

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

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

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

兩大功能

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

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

4.3數(shù)據(jù)的編輯4.3.2拆分或凍結(jié)窗格

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

4.4數(shù)據(jù)統(tǒng)計與分析4.4.1數(shù)據(jù)的排序、篩選與分類匯總

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

?分類匯總分類匯總是對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行分類統(tǒng)計。分類匯總需要分兩步完成，先排序再匯總。

首先對數(shù)據(jù)按分類的字段排序，

其次執(zhí)行分類匯總命令.

4.4數(shù)據(jù)統(tǒng)計與分析4.4.1數(shù)據(jù)的排序、篩選與分類匯總

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

執(zhí)行自動篩選操作，數(shù)據(jù)清單中必須有列標(biāo)號。（2）自定義自動篩選自動篩選完成后，若不能達(dá)到所需的要求，則可選擇“自定義篩選”命令，實(shí)現(xiàn)用自定義條件篩選所需的數(shù)據(jù)。4.4數(shù)據(jù)統(tǒng)計與分析4.4.1數(shù)據(jù)的排序、篩選與分類匯總

?自動篩選與高級篩選（3）高級篩選執(zhí)行高級篩選命令，數(shù)據(jù)清單必須有列標(biāo)題，同時需要建立條件區(qū)域來設(shè)置復(fù)雜的篩選條件。

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

4.4數(shù)據(jù)統(tǒng)計與分析4.4.1數(shù)據(jù)的排序、篩選與分類匯總

“與”運(yùn)算即多個條件表達(dá)式中若有一個條件為假，則其值為假。在條件區(qū)域輸入時，“與”運(yùn)算的條件表達(dá)式要放置在同一行?！盎颉边\(yùn)算即多個條件表達(dá)式中若有一個條件為真，則其值為真。在條件區(qū)域輸入時，“或”運(yùn)算的條件表達(dá)式要放置在不同行。

4.4數(shù)據(jù)統(tǒng)計與分析4.4.1數(shù)據(jù)的排序、篩選與分類匯總

②應(yīng)用高級篩選③清除數(shù)據(jù)清單的篩選對于不再需要的篩選操作，可以將其功能清除，恢復(fù)數(shù)據(jù)清單原來的狀態(tài)。4.4數(shù)據(jù)統(tǒng)計與分析4.4.2數(shù)據(jù)透視表

數(shù)據(jù)透視表是一種可以快速匯總大量數(shù)據(jù)的交互式方法。使用數(shù)據(jù)透視表可以深入分析數(shù)值數(shù)據(jù)，它可以同時靈活變換三組需要統(tǒng)計的字段對一組數(shù)值進(jìn)行分析和統(tǒng)計，統(tǒng)計可以是求和、平均值、計數(shù)、最大、最小等。數(shù)據(jù)透視表建立:單擊“插入”→“數(shù)據(jù)透視表”按鈕，通過操作向?qū)硗瓿蓜?chuàng)建任務(wù)，最容易出錯的是“數(shù)據(jù)透視表字段列表”中“列”與“行”標(biāo)簽、統(tǒng)計“數(shù)值”項(xiàng)的選擇與“值字段設(shè)置”。

4.4數(shù)據(jù)統(tǒng)計與分析4.4.3常用函數(shù)的使用

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