1、經濟與管理應用軟件,主講教師 鐘海,數據整理與匯總,本章學習目標,1、掌握數字與漢字排序的區(qū)別 2、掌握EXCEL排序的方法 3、理解條件區(qū)域 4、掌握數據篩選的方法 5、掌握工作表數據的分類匯總方法 6、掌握數據透視表的功能及用法,人們在分析與決策時往往首先面對的是大量原始數據，這些雜亂無章的數據使我們不能直接觀察到現象的本質，因此需要對數據進行加工、整理、統(tǒng)計，以使現象的本質及其規(guī)則性呈現出來。 Excel的數據整理的基本工具主要包括： 數據排序 分類匯總 數據透視表進行數據整理 篩選 統(tǒng)計圖 直方圖分析工具,數據清單結構 數據清單是指工作表中包含相關數據的數據行列。 數據清單可以作為數據

2、庫使用，其中行表示記錄，列表示字段。 數據清單的第一行為字段名稱、變量名稱、或標志。 字段名稱下面的行數據稱為觀察值、記錄，每一個單格中包含了一個變量所發(fā)生的數值。,1 數據排序,1、排序規(guī)則 數字排序規(guī)則 字母排序規(guī)則 邏輯值排序規(guī)則 漢字排序規(guī)則 漢語拼音序 按筆畫排序 2、排序方式 升序 降序,3、排序案例 【例5.1】某班某次期末考試成績如圖（a）所示，已經計算出了每位同學的平均分，現需得出一個名次表，即從高分到低分的成績表，以便為前3名同學發(fā)獎學金。,按日期排序 日期數據本質是數字，1900-1-1為數字1,按漢字筆畫排序,自定義排序 有時需要按人為指定的次序對某此資料排序。 【例】

3、某大學有7個學院，每個學院的基本情況如圖所示。,要求按以下次序排序此表：計算機學院、管理學院、通信學院、財務學院、外語學院、政法學院、中文學院。,方法 建立自定義序列 在排序對話 框中，通過“選項”指定建立的序列為排序依據。,2 數據篩選,1、概念 數據篩選就是將數據表中所有不滿足條件的記錄行暫時隱藏起來，只顯示那些滿足條件的數據行 。 2、Excel的數據篩選方式 1、自動篩選 自動篩選提供了快速查找工作表數據的功能，只需通過簡單的操作，就能夠篩選出需要的數據 。 2、高級篩選 高級篩選能夠完成自動篩選所不能實現的復雜篩選。,3、自動篩選 案例 某公司的人員檔案是按招聘報到的先后次序建立的，

4、到目前為止，該公司的人員檔案如下圖所示。公司的管理人員可能需要這樣的信息： （1）已婚人員的名單。 （2）各部門的人員檔案信息。 （3）工資高于1 200的人員有哪些。 （4）獎金高于1000的人員有哪些。 （5）各種學歷的人員檔案 如此等等,選擇“數據”|“篩選”|“自動篩選”，Excel會顯示出自動篩選標志?？山璐送瓿筛鞣N自動篩選！,原數據表,具有自動篩標記的數據表,自動篩選的結果示例篩選出運維部的工作人員。(篩選后，箭頭變?yōu)樗{色）,4.自定義篩選 工資高于1 200的人員有哪些。 年齡介于20-40的人有哪些。,5.高級篩選 案例：監(jiān)考表 篩選出某教師的監(jiān)考安排。 這種情況用自動篩選就達

5、不到要求了。因為教師的姓名分布在不同的字段。自動篩選之間是邏輯并的關系。,高級篩選的條件區(qū)域 的類型 （1）“或”條件 （2）“與”條件 （3）多列的“或”條件 （4） “或”、“與”復合條件 注意事項：條件區(qū)域不要與數據區(qū)域放在同一行，以免被隱藏。,條件區(qū)域的構建方法 條件區(qū)域可放在數據列表的上方或右側、左側。一般放在上方，以免篩選后被隱藏起來。,高級篩選案例 查看下表中“運維部”和“信息資源部”人員中，工資高于1000且獎金高于500元的有哪些。,使用計算條件的高級篩選 什么是計算條件 條件是公式或函數，用原始數據計算出來。 計算條件的構造方法 計算條件中的標題可以是任何文本或都是空白，但

6、不能與數據清單中的任一列標相同，這一點正好與前面討論的條件區(qū)域相反。 必須以絕對引用的方式引用數據清單或數據庫之外的單元格。 必須以相對引用的方式引用數據清單或數據庫中之內的單元格。,基于計算條件的高級篩選案例 某商場有2月1號到8號的銷售記錄如圖的A2:F10單元格區(qū)域所示，現在要找出銷售額高于平均值的銷售記錄。,應用：值的計算,值用來表示股票（或投資組合）相對與市場指數的波動程度。 貝塔值采用回歸法計算，將整個市場波動帶來的風險確定為1。當某項資產的價格波動與整個市場波動一致時，其貝塔值也等于1；如果價格波動幅度大于整個市場，其貝塔值則大于1；如果價格波動小于市場波動，其貝塔值便小于1。,

7、計算公式： 其中：ri-股票i的收益率 rm-市場指數的收益率 - ri與rm的協(xié)方差 m2-股票價格指數的方差,協(xié)方差函數COVAR(array1,array2) Array1 第一個所含數據為整數的單元格區(qū)域。 Array2 第二個所含數據為整數的單元格區(qū)域。 方差函數（總體） VARP(number1,number2,.) Number1, number2, . 為對應于樣本總體的 1 到 30 個參數。,3 數據的分類與匯總,1、概述 分類匯總就是對工作表中的某類的數據進行分類統(tǒng)計。 分類匯總的意義：一張無序表單，統(tǒng)計相關數據。 它通過折疊或展開原工作表中的行數據及匯總結果，從匯總和明

8、細兩種角度顯示數據，可以快捷地創(chuàng)建各種匯總報告。分類匯總的數據折疊層次可達8層。 2、分類匯總能夠完成以下事性 在數據表中顯示一組或多組數據的分類匯總及總和。 在分組數據上完成不同的計算，如求和、統(tǒng)計個數、求平均數、求最大（最小值）等。,案例 右圖是某公司的職工工資表。 完成以下事情： 每部門有多少人； 每部門平均工資； 哪個部門的總評獎金最高， 應分發(fā)給各部門的總金額各是多少,通過函數能夠完成這些事件，但分類匯總簡單多了。,3、分類匯總的準備工作 分類匯總表的每個數據列(字段)都有列標題（字段名） 必須對分類字段所在的列排序。 排序的列標題稱為分類匯總關鍵字，在進行分類匯總時，只能指定排序后

9、的列標題為匯總關鍵字。,按部門排序后的工作表,4、建立分類匯總 選擇“數據”|“分類匯總”菜單。,1、指定分類字段,2、指定匯總方式,3、指定匯總字段,4、指定匯總結果的保存方式,5、匯總結果的保存方式 替換當前分類匯總。選擇這種方式時，最后一次的匯總會取代以前的分類匯總。 每組數據分頁。選擇這種方式時，各種不同的分類數據將被分頁保存。 匯總結果顯示在數據下方。選擇這種方式時，原數據的下方會顯示匯總計算的結果。 上述3種方式可同時選中。Excel的默認選擇是第1和第3項。,6、分類匯總結果示例 表格的左側是分級按鈕，可增減表格的顯示范圍。,7、多次分類匯總 Excel可以對同一分類進行多重匯總

10、，若要在同一匯總表中顯示兩個以上的匯總數據，只需對同一數據清單進行兩次不同的匯總運算。第二次分類匯總在第一次的匯總結果上進行。 在前面的匯總結果基礎上，統(tǒng)計每部門的人數。,8、刪除分類匯總 對于剛建立的分類匯總，如果要將其恢復為原始數據表，可以選擇“編輯”中的“撤消”菜單項取消分類匯總，或單擊撤消按鈕。 但若在建立分類匯總之后，又進行了其他操作，上述方法就無效了，可按如下方法刪除分類匯總。 （1）單擊分類匯總表中的任一單元格。 （2）選擇“數據”|“分類匯總”菜單項 （3）單擊“分類匯總”對話框中的“全部刪除”按鈕。,4 數據透視表,數據透視表的功能 數據透視表不僅能夠改變數據表的行、列布局，

11、而且能夠快速匯總大量數據。也能夠基于原數據表創(chuàng)建數據分組，對建立的分組進行匯總統(tǒng)計。,2、源數據表與數據透視表的對應關系,3、案例 假設某電視銷售商場有6位職工，分別是勞得諾、令狐沖、陸大安、任我行、韋小寶、向問天，銷售的電視產品有TCL、長虹、康佳、創(chuàng)維、熊貓幾種品牌。每天的銷售數據都記錄在Excel的一個工作表中，如圖所示，該數據表有300多行數據 。,完成以下統(tǒng)計分析工作 統(tǒng)計各種品牌的電視的總銷售額； 統(tǒng)計每位職工的總銷售額； 統(tǒng)計每位職工銷售的各種類型的電視機的銷售額； 統(tǒng)計各種運輸方式的總數。 數據透視表能夠很方便地完成這些工作。,單擊“數據”菜單中的“數據透視表和圖表報告”選項，

12、Excel彈出數據透視表向導對話框如圖所示。,上一頁,下一頁,返回本節(jié)首頁,4、建立數據透視表的過程,根據向導要求，第1步是指定“數據源類型”和確定所需創(chuàng)建的“報表類型”。選擇“數據清單或數據庫”作為數據源，選擇“數據透視表”作為報表類型。最后單擊“下一步”。,上一頁,下一頁,返回本節(jié)首頁,第2步是確定數據區(qū)域，單擊“下一步”按鈕，向導給出第3步操作對話框窗口如圖所示。 第3步確定數據透視表的位置。選擇現有工作表，并利用鼠標將數據透視表的位置確定在E1單元格中，單擊“完成”按鈕，可以結束操作。,上一頁,下一頁,返回本節(jié)首頁,按向導提示做完第一、二步。在最后的第三步向導對話框中，選擇“布局”按鈕

13、。會見到如圖所示的對話框。,其中 行 拖放到行中的數據字段，該字段中的第一個數據項將占據透視表的一行。 列 與行相對應，放置在列中的字段，該字段中的每個項將占一列。 頁 行和列相當于X軸和Y軸，由它們確定一個二維表格，頁則相當于Z軸(第三維)。拖放在頁中的字段，Excel將按該字段的數據項對透視表進行分頁。 數據 進行計數或匯總的字段名稱。,兩種數據透視表生成方式： 1.在向導中進行布局。 2.在生成的空白透視表中布局。 下圖為 在向導中進行布局。,也可在向導-布局 中不做任何操作，直接按“確定”，在工作表中會生成一個空白的數據透視表布局區(qū)，數據透視表字段列表、數據透視表工具欄三個組件。然后將字段列表中的字段拖入數據透視表布局區(qū)。 如果數據透視表字段列表沒顯現，可點擊數據透視表布局區(qū)或數據透視表工具欄中最右邊的按鈕。,統(tǒng)計各銷售員銷售數據的透視表,雙擊任一單元格，會得到這些數據的來源明細數據,雙擊上表E5單元格得到的明細數據表,利用頁字段創(chuàng)建分頁顯示,頁字段,5 篩選數據 增加或減