數(shù)據(jù)整理與匯總.ppt

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

2、庫使用，其中行表示記錄，列表示字段。 數(shù)據(jù)清單的第一行為字段名稱、變量名稱、或標(biāo)志。 字段名稱下面的行數(shù)據(jù)稱為觀察值、記錄，每一個(gè)單格中包含了一個(gè)變量所發(fā)生的數(shù)值。,1 數(shù)據(jù)排序,1、排序規(guī)則 數(shù)字排序規(guī)則 字母排序規(guī)則 邏輯值排序規(guī)則 漢字排序規(guī)則 漢語拼音序 按筆畫排序 2、排序方式 升序 降序,3、排序案例 【例5.1】某班某次期末考試成績?nèi)鐖D（a）所示，已經(jīng)計(jì)算出了每位同學(xué)的平均分，現(xiàn)需得出一個(gè)名次表，即從高分到低分的成績表，以便為前3名同學(xué)發(fā)獎(jiǎng)學(xué)金。,按日期排序 日期數(shù)據(jù)本質(zhì)是數(shù)字，1900-1-1為數(shù)字1,按漢字筆畫排序,自定義排序 有時(shí)需要按人為指定的次序?qū)δ炒速Y料排序。 【例】

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

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

5、不到要求了。因?yàn)榻處煹男彰植荚诓煌淖侄巍Ｗ詣雍Y選之間是邏輯并的關(guān)系。,高級篩選的條件區(qū)域 的類型 （1）“或”條件 （2）“與”條件 （3）多列的“或”條件 （4） “或”、“與”復(fù)合條件 注意事項(xiàng)：條件區(qū)域不要與數(shù)據(jù)區(qū)域放在同一行，以免被隱藏。,條件區(qū)域的構(gòu)建方法 條件區(qū)域可放在數(shù)據(jù)列表的上方或右側(cè)、左側(cè)。一般放在上方，以免篩選后被隱藏起來。,高級篩選案例 查看下表中“運(yùn)維部”和“信息資源部”人員中，工資高于1000且獎(jiǎng)金高于500元的有哪些。,使用計(jì)算條件的高級篩選 什么是計(jì)算條件 條件是公式或函數(shù)，用原始數(shù)據(jù)計(jì)算出來。 計(jì)算條件的構(gòu)造方法 計(jì)算條件中的標(biāo)題可以是任何文本或都是空白，但

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

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

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

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

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

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

12、Excel彈出數(shù)據(jù)透視表向?qū)υ捒蛉鐖D所示。,上一頁,下一頁,返回本節(jié)首頁,4、建立數(shù)據(jù)透視表的過程,根據(jù)向?qū)б?，?步是指定“數(shù)據(jù)源類型”和確定所需創(chuàng)建的“報(bào)表類型”。選擇“數(shù)據(jù)清單或數(shù)據(jù)庫”作為數(shù)據(jù)源，選擇“數(shù)據(jù)透視表”作為報(bào)表類型。最后單擊“下一步”。,上一頁,下一頁,返回本節(jié)首頁,第2步是確定數(shù)據(jù)區(qū)域，單擊“下一步”按鈕，向?qū)Ыo出第3步操作對話框窗口如圖所示。 第3步確定數(shù)據(jù)透視表的位置。選擇現(xiàn)有工作表，并利用鼠標(biāo)將數(shù)據(jù)透視表的位置確定在E1單元格中，單擊“完成”按鈕，可以結(jié)束操作。,上一頁,下一頁,返回本節(jié)首頁,按向?qū)崾咀鐾甑谝?、二步。在最后的第三步向?qū)υ捒蛑?，選擇“布局”按鈕

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