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

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

沒有經(jīng)過排序的數(shù)據(jù)是雜亂無章的，很難快速地查找或理解數(shù)據(jù)，排序有助于快速直觀地顯示、組織和查找所需數(shù)據(jù)，有助于決策報表的快速制作和更好地理解數(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)計算出了每位同學的平均分，現(xiàn)需得出一個名次表，即從高分到低分的成績表，以便為前3名同學發(fā)獎學金。7.1.2數(shù)值排序按日期排序7.1.3漢字與字符排序按漢字筆畫排序1、選中要排序的數(shù)據(jù)區(qū)域后,單擊”排序和篩選”按鈕2、單擊”自定義排序”按鈕，彈出“排序”對話框3、單擊”選項”按鈕，彈出“選項”對話框3、指定”筆畫序”7.1.4自定義排序次序自定義排序有時需要按人為指定的次序?qū)δ炒速Y料排序。【例7.3】某大學有7個學院，每個學院的基本情況如圖所示。要求按以下次序排序此表：計算機學院、管理學院、通信學院、財務學院、外語學院、政法學院、中文學院。7.1.4自定義排序次序方法單擊Office按鈕→“Excel選項”→“常用”→“使用Excel時采用的首選項”→“編輯自定義列表”，Excel會彈出“選項”對話框按前面的方式顯示出“排序”對話框中，通過“序列”下面的“自定義序列”指定建立的序列為排序依據(jù)。在此輸入自定義的排序序列7.1.5賬號、零件號等排序賬號、零件號排序的困難由于賬號、零件號常采用不同的區(qū)段進行編碼，為排序帶來了困難。賬號、零件號排序的解決方法一種方法是確保同類編號中的任何部分都有相同的長度。例如在上述例子中，將“ZK-999-10”輸入為“ZK-0999-10”，這樣在比較排序時就不會出錯了。另一種方法是把編號的不同部分輸入在不同列的單元格中，如上面兩個編號用圖7.6所示的方式輸入。在排序時，對編號所涉及到的三列同時進行排序，排序結(jié)果就不會出錯，最后再用“&”運算符把排序后的各部分組合起來。7.1.6多關(guān)鍵字排序多關(guān)鍵字排序就是對數(shù)據(jù)表中的數(shù)據(jù)按兩個或兩個以上的關(guān)鍵字進行排序。多關(guān)鍵字排序可使數(shù)據(jù)在第一關(guān)鍵字相同的情況下，按第二關(guān)鍵字排序，在第一、第二關(guān)鍵字都相同的情況下，數(shù)據(jù)按第三關(guān)鍵字有序，其余的以此類推。在Excel2007中，最多允許有64個排序關(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ù)據(jù)行。2、Excel的數(shù)據(jù)篩選方式1、自動篩選自動篩選提供了快速查找工作表數(shù)據(jù)的功能，只需通過簡單的操作，就能夠篩選出需要的數(shù)據(jù)。2、高級篩選高級篩選能夠完成自動篩選所不能實現(xiàn)的復雜篩選。7.2.1

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

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

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

數(shù)據(jù)篩選如果篩選關(guān)鍵字對應的列是數(shù)值型數(shù)據(jù)，可通過“數(shù)字篩選”命令指定自定義篩選條件，實現(xiàn)靈活的數(shù)據(jù)查詢?！纠?.6】在圖7.8(a)所示的職工檔案表中，篩選年齡最大的3名職工。1、單擊“數(shù)據(jù)”！“篩選”為數(shù)據(jù)表顯示出自動篩選標志2、單擊“年齡”右邊的自動篩選下拉箭頭3、單擊“10個最大的值”，彈出對話框4、將“10”改為3，即可篩選出年齡最大的3位員工！【例7.7】查看圖7.10中職工檔案表中工資高于2800元的人員1、指定工作表的“篩選”標志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）中“運維部”工資和獎金都高于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．與條件同或條件的組合應用【例7.10】對于前面討論的職工檔案數(shù)據(jù)表，現(xiàn)要查找運維部和信息資源部中工資高于1000且獎金高于500的人員檔案7.2.3

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

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

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

1、指定分類字段2、指定匯總方式3、指定匯總字段4、指定匯總結(jié)果的保存方式7.3數(shù)據(jù)的分類與匯總5、匯總結(jié)果的保存方式替換當前分類匯總。選擇這種方式時，最后一次的匯總會取代以前的分類匯總。每組數(shù)據(jù)分頁。選擇這種方式時，各種不同的分類數(shù)據(jù)將被分頁保存。匯總結(jié)果顯示在數(shù)據(jù)下方。選擇這種方式時，原數(shù)據(jù)的下方會顯示匯總計算的結(jié)果。上述3種方式可同時選中。Excel的默認選擇是第1和第3項。7.3數(shù)據(jù)的分類與匯總6、分類匯總結(jié)果示例7.3.3高級分類與匯總7、多次分類匯總Excel可以對同一分類進行多重匯總，若要在同一匯總表中顯示兩個以上的匯總數(shù)據(jù)，只需對同一數(shù)據(jù)清單進行兩次不同的匯總運算。第二次分類匯總在第一次的匯總結(jié)果上進行。在前面的匯總結(jié)果基礎(chǔ)上，統(tǒng)計每部門的人數(shù)。7.3.4嵌套分類匯總1、嵌套分類匯總在一個已經(jīng)建立了分類匯總的匯總表中再進行另一種分類匯總，兩次的分類匯總關(guān)鍵字不同，這種分類匯總方式稱為嵌套分類匯總。2、嵌套分類匯總的排序方式建立嵌套分類匯總的前提仍然是要對每個分類匯總關(guān)鍵字排序。第一級匯總關(guān)鍵字應該是排序的第一關(guān)鍵字，第二級匯總關(guān)鍵字應該是第二排序關(guān)鍵字，其余以此類推。有幾層嵌套匯總就需要進行幾次分類匯總操作，第二次匯總在第一次的結(jié)果集上操作，第三次在第二次的結(jié)果是操作，其余以此類推。在一個已經(jīng)建立了分類匯總的匯總表中再進行另一種分類匯總，兩次的分類匯總關(guān)鍵字不同，這種分類匯總方式稱為嵌套分類匯總。建立嵌套分類匯總的前提仍然是要對每個分類匯總關(guān)鍵字排序。第一級匯總關(guān)鍵字應該是排序的第一關(guān)鍵字，第二級匯總關(guān)鍵字應該是第二排序關(guān)鍵字，其余以此類推。7.3.4嵌套分類匯總以部門為第一匯總關(guān)鍵字、婚否為第二匯總關(guān)鍵字