(3.6)-7（1）數(shù)據(jù)管理Excel數(shù)據(jù)處理與分析

7(1)數(shù)據(jù)管理-排序、篩選、分類匯總本節(jié)學(xué)習(xí)目標(biāo)1、掌握EXCEL排序的方法2、理解條件區(qū)域3、掌握數(shù)據(jù)篩選的方法4、掌握工作表數(shù)據(jù)的分類匯總方法7.1數(shù)據(jù)排序

沒有經(jīng)過排序的數(shù)據(jù)是雜亂無章的，很難快速地查找或理解數(shù)據(jù)，排序有助于快速直觀地顯示、組織和查找所需數(shù)據(jù)，有助于決策報(bào)表的快速制作和更好地理解數(shù)據(jù)，是數(shù)據(jù)分析中不可缺少的常用操作。7.1數(shù)據(jù)排序1、排序規(guī)則數(shù)字排序規(guī)則：數(shù)值大小字母排序規(guī)則：字母表順序邏輯值排序規(guī)則：FALSE(0)，TRUE(1)漢字排序規(guī)則漢語拼音序按筆畫排序2、排序方式升序降序7.1.2數(shù)值排序3、排序案例【例7.1】某班某次期末考試成績?nèi)鐖D（a）所示，已經(jīng)計(jì)算出了每位同學(xué)的平均分，現(xiàn)需得出一個(gè)名次表，即從高分到低分的成績表，以便為前3名同學(xué)發(fā)獎學(xué)金。7.1.2數(shù)值排序按日期排序7.1.3漢字與字符排序按漢字筆畫排序1、選中要排序的數(shù)據(jù)區(qū)域后,單擊”排序和篩選”按鈕2、單擊”自定義排序”按鈕，彈出“排序”對話框3、單擊”選項(xiàng)”按鈕，彈出“選項(xiàng)”對話框3、指定”筆畫序”7.1.4自定義排序次序自定義排序有時(shí)需要按人為指定的次序?qū)δ炒速Y料排序?！纠?.3】某大學(xué)有7個(gè)學(xué)院，每個(gè)學(xué)院的基本情況如圖所示。要求按以下次序排序此表：計(jì)算機(jī)學(xué)院、管理學(xué)院、通信學(xué)院、財(cái)務(wù)學(xué)院、外語學(xué)院、政法學(xué)院、中文學(xué)院。7.1.4自定義排序次序方法單擊Office按鈕→“Excel選項(xiàng)”→“常用”→“使用Excel時(shí)采用的首選項(xiàng)”→“編輯自定義列表”，Excel會彈出“選項(xiàng)”對話框按前面的方式顯示出“排序”對話框中，通過“序列”下面的“自定義序列”指定建立的序列為排序依據(jù)。在此輸入自定義的排序序列7.1.5賬號、零件號等排序賬號、零件號排序的困難由于賬號、零件號常采用不同的區(qū)段進(jìn)行編碼，為排序帶來了困難。賬號、零件號排序的解決方法一種方法是確保同類編號中的任何部分都有相同的長度。例如在上述例子中，將“ZK-999-10”輸入為“ZK-0999-10”，這樣在比較排序時(shí)就不會出錯(cuò)了。另一種方法是把編號的不同部分輸入在不同列的單元格中，如上面兩個(gè)編號用圖7.6所示的方式輸入。在排序時(shí)，對編號所涉及到的三列同時(shí)進(jìn)行排序，排序結(jié)果就不會出錯(cuò)，最后再用“&”運(yùn)算符把排序后的各部分組合起來。7.1.6多關(guān)鍵字排序多關(guān)鍵字排序就是對數(shù)據(jù)表中的數(shù)據(jù)按兩個(gè)或兩個(gè)以上的關(guān)鍵字進(jìn)行排序。多關(guān)鍵字排序可使數(shù)據(jù)在第一關(guān)鍵字相同的情況下，按第二關(guān)鍵字排序，在第一、第二關(guān)鍵字都相同的情況下，數(shù)據(jù)按第三關(guān)鍵字有序，其余的以此類推。在Excel2007中，最多允許有64個(gè)排序關(guān)鍵字，但不管有多少關(guān)鍵字，排序之后的數(shù)據(jù)總是按第一關(guān)鍵字有序的。7.1.6多關(guān)鍵字排序【例7.4】2004年某雜貨店各雇員的銷售數(shù)據(jù)如圖7.7（a）的A1:F7所示，以第一季度為第一關(guān)鍵字、第二季度為第二關(guān)鍵字、第三季度為第三關(guān)鍵字的排序結(jié)果如圖7.7（a）的A9:F15區(qū)域所示，排序的方式是遞增。1、源數(shù)據(jù)表2、單擊“開始”→“排序和篩選”→“自定義排序”命令，彈出“排序”對話框3、依次指定排序關(guān)鍵字3、排序結(jié)果7.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ù)雜篩選。7.2.1

數(shù)據(jù)篩選3、自動篩選案例【例7.5】某公司的人員檔案是按招聘報(bào)到的先后次序建立的，如圖7.8（a）所示?？梢钥闯?，圖7.8的檔案比較混亂，公司的管理人員可能需要這樣的信息：已婚人員的名單，各部門的人員檔案信息，工資高于1200的人員有哪些，獎金高于1000的人員有哪些，各種學(xué)歷的人員檔案……（1）已婚人員的名單。（2）各部門的人員檔案信息。（3）工資高于1200的人員有哪些。（4）獎金高于1000的人員有哪些。（5）各種學(xué)歷的人員檔案……如此等等7.2.1

數(shù)據(jù)篩選原數(shù)據(jù)表具有自動篩標(biāo)記的數(shù)據(jù)表單擊“數(shù)據(jù)”|“篩選”，Excel會在表格的標(biāo)題邊顯示出自動篩選標(biāo)志?？山璐送瓿筛鞣N自動篩選！7.2.1

數(shù)據(jù)篩選自動篩選的結(jié)果示例——篩選出運(yùn)維部的工作人員。7.2.1

數(shù)據(jù)篩選如果篩選關(guān)鍵字對應(yīng)的列是數(shù)值型數(shù)據(jù)，可通過“數(shù)字篩選”命令指定自定義篩選條件，實(shí)現(xiàn)靈活的數(shù)據(jù)查詢。【例7.6】在圖7.8(a)所示的職工檔案表中，篩選年齡最大的3名職工。1、單擊“數(shù)據(jù)”！“篩選”為數(shù)據(jù)表顯示出自動篩選標(biāo)志2、單擊“年齡”右邊的自動篩選下拉箭頭3、單擊“10個(gè)最大的值”，彈出對話框4、將“10”改為3，即可篩選出年齡最大的3位員工！【例7.7】查看圖7.10中職工檔案表中工資高于2800元的人員1、指定工作表的“篩選”標(biāo)志2、單擊“工資篩選”中的“大于”命令3、在彈出的對話框中輸入工資數(shù)據(jù)5.2

數(shù)據(jù)篩選4、高級篩選高級篩選的條件區(qū)域的類型（1）“或”條件（2）“與”條件（3）多列的“或”條件（4）用“或”連接的“與”條件7.2.2

條件區(qū)域條件區(qū)域的構(gòu)建方法7.2.3

高級篩選1、條件“與篩選”【例7.8】查看例7.5（圖7.8）中“運(yùn)維部”工資和獎金都高于500元的人員。1、單擊“高級”按鈕，彈出高級篩選設(shè)置對話框2、指定篩選結(jié)果的保存方式。2、指定篩選條件和數(shù)據(jù)區(qū)域。7.2.3

高級篩選2．條件“或”的高級篩選【例7.9】對于圖7.8，現(xiàn)要找出其中獎金小于500或已經(jīng)結(jié)婚的工作人員。7.2.3

高級篩選3．與條件同或條件的組合應(yīng)用【例7.10】對于前面討論的職工檔案數(shù)據(jù)表，現(xiàn)要查找運(yùn)維部和信息資源部中工資高于1000且獎金高于500的人員檔案7.2.3

高級篩選4、使用計(jì)算條件的高級篩選什么是計(jì)算條件不能直接用一個(gè)簡單的字段值與常量比較構(gòu)造出來的條件。計(jì)算條件的構(gòu)造方法計(jì)算條件中的標(biāo)題可以是任何文本或都是空白，但不能與數(shù)據(jù)清單中的任一列標(biāo)相同，這一點(diǎn)正好與前面討論的條件區(qū)域相反。必須以絕對引用的方式引用數(shù)據(jù)清單或數(shù)據(jù)庫中之外的單元格。必須以相對引用的方式引用數(shù)據(jù)清單或數(shù)據(jù)庫中之內(nèi)的單元格。7.2.3

高級篩選基于計(jì)算條件的高級篩選案例【例7.11】某商場2003年2月1號到8號的銷售記錄如圖7.18的區(qū)域A2:F10所示，找出其中銷售額高于中間值的銷售記錄。7.3數(shù)據(jù)的分類與匯總1、概述分類匯總就是對工作表中指定的行或列中的數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)，它通過折疊或展開原工作表中的行、列數(shù)據(jù)及匯總結(jié)果，從匯總和明細(xì)兩種角度顯示數(shù)據(jù)，可以快捷地創(chuàng)建各種匯總報(bào)告。分類匯總的數(shù)據(jù)折疊層次可達(dá)8層。2、分類匯總能夠完成以下任務(wù)：在數(shù)據(jù)表中顯示一組或多組數(shù)據(jù)的分類匯總及總和。在分組數(shù)據(jù)上完成不同的計(jì)算，如求和、統(tǒng)計(jì)個(gè)數(shù)、求平均數(shù)、求最大（最小值）等。7.3數(shù)據(jù)的分類與匯總案例右圖是某公司的職工工資表。完成以下事情：每部門有多少人；每部門平均工資；哪個(gè)部門的總評獎金最高，應(yīng)分發(fā)給各部門的總金額各是多少

通過函數(shù)能夠完成這些事件，但分類匯總簡單多了。7.3數(shù)據(jù)的分類與匯總3、分類匯總的準(zhǔn)備工作①分類匯總表的每個(gè)數(shù)據(jù)列都有列標(biāo)題；②必須對要進(jìn)行分類匯總的列排序。排序的列標(biāo)題稱為分類匯總關(guān)鍵字，在進(jìn)行分類匯總時(shí)，只能指定排序后的列標(biāo)題為匯總關(guān)鍵字。按部門排序后的工作表7.3數(shù)據(jù)的分類與匯總4、建立分類匯總單擊“數(shù)據(jù)”選項(xiàng)卡中的“分類匯總”命令,顯示“分類匯總”對話框。

1、指定分類字段2、指定匯總方式3、指定匯總字段4、指定匯總結(jié)果的保存方式7.3數(shù)據(jù)的分類與匯總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)。7.3數(shù)據(jù)的分類與匯總6、分類匯總結(jié)果示例7.3.3高級分類與匯總7、多次分類匯總Excel可以對同一分類進(jìn)行多重匯總，若要在同一匯總表中顯示兩個(gè)以上的匯總數(shù)據(jù)，只需對同一數(shù)據(jù)清單進(jìn)行兩次不同的匯總運(yùn)算。第二次分類匯總在第一次的匯總結(jié)果上進(jìn)行。在前面的匯總結(jié)果基礎(chǔ)上，統(tǒng)計(jì)每部門的人數(shù)。7.3.4嵌套分類匯總1、嵌套分類匯總在一個(gè)已經(jīng)建立了分類匯總的匯總表中再進(jìn)行另一種分類匯總，兩次的分類匯總關(guān)鍵字不同，這種分類匯總方式稱為嵌套分類匯總。2、嵌套分類匯總的排序方式建立嵌套分類匯總的前提仍然是要對每個(gè)分類匯總關(guān)鍵字排序。第一級匯總關(guān)鍵字應(yīng)該是排序的第一關(guān)鍵字，第二級匯總關(guān)鍵字應(yīng)該是第二排序關(guān)鍵字，其余以此類推。有幾層嵌套匯總就需要進(jìn)行幾次分類匯總操作，第二次匯總在第一次的結(jié)果集上操作，第三次在第二次的結(jié)果是操作，其余以此類推。在一個(gè)已經(jīng)建立了分類匯總的匯總表中再進(jìn)行另一種分類匯總，兩次的分類匯總關(guān)鍵字不同，這種分類匯總方式稱為嵌套分類匯總。建立嵌套分類匯總的前提仍然是要對每個(gè)分類匯總關(guān)鍵字排序。第一級匯總關(guān)鍵字應(yīng)該是排序的第一關(guān)鍵字，第二級匯總關(guān)鍵字應(yīng)該是第二排序關(guān)鍵字，其余以此類推。7.3.4嵌套分類匯總以部門為第一匯總關(guān)鍵字、婚否為第二匯總關(guān)鍵字