Excel數(shù)據(jù)分析高級應(yīng)用-數(shù)據(jù)分析

《Excel數(shù)據(jù)分析高級應(yīng)用-數(shù)據(jù)分析》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1.認(rèn)識Excel2.如何設(shè)計表格3.函數(shù)的使用4.圖表的制作5.數(shù)據(jù)分析

1（1）排序（2）數(shù)據(jù)篩選（3）數(shù)據(jù)分類匯總（4）數(shù)據(jù)透視表（5）合并計算

2（1）排序（2）數(shù)據(jù)篩選（3）數(shù)據(jù)分類匯總（4）數(shù)據(jù)透視表（5）合并計算

31、排序規(guī)則?數(shù)字排序規(guī)則?字母排序規(guī)則?邏輯值排序規(guī)則?漢字排序規(guī)則?漢語拼音序?按筆畫排序2、排序方式?升序?降序

4排序案例某班某次期末考試成績?nèi)鐖D（a）所示，已經(jīng)計算出了每位同學(xué)的平均分，現(xiàn)需得出一個名次表，即從高分到低分的成績表，以便為前3名同學(xué)發(fā)獎學(xué)金。

5?按日期排序

6?按漢字筆畫排序1、選中要排序的數(shù)據(jù)區(qū)域后,單擊”排序和篩選”按鈕2、單擊”自定義排序”按鈕，彈出“排序”對話框3、單擊”選項”按鈕，彈出“選項”對話框3、指定”筆畫序”

7?自定義排序?有時需要按人為指定的次序?qū)δ炒速Y料排序。?某大學(xué)有7個學(xué)院，每個學(xué)院的基本情況如圖所示。要求按以下次序排序此表：計算機學(xué)院、管理學(xué)院、通信學(xué)院、財務(wù)學(xué)院、外語學(xué)院、政法學(xué)院、中文學(xué)院。

8在此輸入自定義的排序序列?方法?單擊Office按鈕→“文件”→“Excel選項”→“高級”→“編輯自定義列表”，Excel會彈出“選項”對話框?按前面的方式顯示出“排序”對話框中，通過“序序”下面的“自定義序列”指定建立的序列為排序依據(jù)。

9?多關(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)鍵字有序的。

102004年某雜貨店各雇員的銷售數(shù)據(jù)如圖（a）的A1:F7所示，以第一季度為第一關(guān)鍵字、第二季度為第二關(guān)鍵字、第1、源數(shù)據(jù)表三季度為第三關(guān)鍵字的排序結(jié)果如圖（2b、單擊）的“開始”A9:F15→區(qū)域“排所示，排序的方式是遞增。序和篩選”→“自定義排序”命令，彈出“排序”對話框3、依次指定排序關(guān)鍵字3、排序結(jié)果

11（1）排序（2）數(shù)據(jù)篩選（3）數(shù)據(jù)分類匯總（4）數(shù)據(jù)透視表（5）合并計算

121、概念?數(shù)據(jù)篩選就是將數(shù)據(jù)表中所有不滿足條件的記錄行暫時隱藏起來，只顯示那些滿足條件的數(shù)據(jù)行。2、Excel的數(shù)據(jù)篩選方式?1、自動篩選?自動篩選提供了快速查找工作表數(shù)據(jù)的功能，只需通過簡單的操作，就能夠篩選出需要的數(shù)據(jù)。?2、高級篩選?高級篩選能夠完成自動篩選所不能實現(xiàn)的復(fù)雜篩選。

13某公司的人員檔案是按招聘報到的先后次序建立的，如下圖所示?？梢钥闯?，圖中的檔案比較混亂，公司的管理人員可能需要這樣的信息：已婚人員的名單，各部門的人員檔案信息，工資高于1200的人員有哪些，獎金高于1000的人員有哪些，各種學(xué)歷的人員檔案……（1）已婚人員的名單。（2）各部門的人員檔案信息。（3）工資高于1200的人員有哪些。（4）獎金高于1000的人員有哪些。（5）各種學(xué)歷的人員檔案?……如此等等

14原數(shù)據(jù)表具有自單擊“數(shù)據(jù)”|“篩選”，Excel會在表格的標(biāo)題邊顯示動篩標(biāo)出自動篩選標(biāo)志。可借此完成各種自動篩選！記的數(shù)據(jù)表

151、單擊“數(shù)2、單擊據(jù)”！“篩“年齡”4、將“10”選”為數(shù)據(jù)表右邊的自動改為3，即可?如果篩選關(guān)鍵字對應(yīng)的列是數(shù)值型數(shù)據(jù)，可通過篩選下拉箭“數(shù)字篩顯示出自動篩選篩選出年齡最選”命令指定自定義篩選條件，實現(xiàn)靈活的數(shù)據(jù)查詢。頭標(biāo)志大的3位員工！在下圖所示的職工檔案表中，篩選年齡最大的3名職工。3、單擊“10個最大的值”，彈出對話框

163、在彈出的對話框中輸入工資數(shù)據(jù)1、指定工作表的“篩選”標(biāo)志2、單擊“工資篩選”中的“大于”命令

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

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

19?基于計算條件的高級篩選案例某商場2003年2月1號到8號的銷售記錄如圖7.18的區(qū)域A2:F10所示，找出其中銷售額高于平均值的銷售記錄。

20（1）排序（2）數(shù)據(jù)篩選（3）數(shù)據(jù)分類匯總（4）數(shù)據(jù)透視表（5）合并計算

211、概述?分類匯總就是對工作表中指定的行或列中的數(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ù)、求最大（最小值）等。

22?案例?右圖是某公司的職工工資表。?完成以下事情：?每部門有多少人；?每部門平均工資；?應(yīng)分發(fā)給各部門的總金額各是多少通過函數(shù)能夠完成這些事件，但分類匯總簡單多了。

233、分類匯總的準(zhǔn)備工作?①分類匯總表的每個數(shù)據(jù)列都有列標(biāo)題；?②必須對要進行分類匯總的列排序。?排序的列標(biāo)題稱為分類匯總關(guān)鍵字，在進行分類匯總時，只能指定排序后的列標(biāo)題為匯總關(guān)鍵字。按部門排序后的工作表

244、建立分類匯總?單擊“數(shù)據(jù)”選項卡中的“分類匯總”命令,顯示“分類匯總”對話框。?1、指定分類字段2、指定匯總方式3、指定匯總字段4、指定匯總結(jié)果的保存方式

255、匯總結(jié)果的保存方式1.替換當(dāng)前分類匯總。選擇這種方式時，最后一次的匯總會取代以前的分類匯總。2.每組數(shù)據(jù)分頁。選擇這種方式時，各種不同的分類數(shù)據(jù)將被分頁保存。3.匯總結(jié)果顯示在數(shù)據(jù)下方。選擇這種方式時，原數(shù)據(jù)的下方會顯示匯總計算的結(jié)果。?上述3種方式可同時選中。Excel的默認(rèn)選擇是第1和第3項。

266、分類匯總結(jié)果示例

277、多次分類匯總?Excel可以對同一分類進行多重匯總，若要在同一匯總表中顯示兩個以上的匯總數(shù)據(jù)，只需對同一數(shù)據(jù)清單進行兩次不同的匯總運算。第二次分類匯總在第一次的匯總結(jié)果上進行。?在前面的匯總結(jié)果基礎(chǔ)上，統(tǒng)計每部門的人數(shù)。

281、嵌套分類匯總?在一個已經(jīng)建立了分類匯總的匯總表中再進行另一種分類匯總，兩次的分類匯總關(guān)鍵字不同，這種分類匯總方式稱為嵌套分類匯總。2、嵌套分類匯總的排序方式?建立嵌套分類匯總的前提仍然是要對每個分類匯總關(guān)鍵字排序。第一級匯總關(guān)鍵字應(yīng)該是排序的第一關(guān)鍵字，第二級匯總關(guān)鍵字應(yīng)該是第二排序關(guān)鍵字，其余以此類推。?有幾層嵌套匯總就需要進行幾次分類匯總操作，第二次匯總在第一次的結(jié)果集上操作，第三次在第二次的結(jié)果是操作，其余以此類推。在一個已經(jīng)建立了分類匯總的匯總表中再進行另一種分類匯總，兩次的分類匯總關(guān)鍵字不同，這種分類匯總方式稱為嵌套分類匯總。?建立嵌套分類匯總的前提仍然是要對每個分類匯總關(guān)鍵字排序。第一級匯總關(guān)鍵字應(yīng)該是排序的第一關(guān)鍵字，第二級匯總關(guān)鍵字應(yīng)該是第二排序關(guān)鍵字，其余以此類推。

298、刪除分類匯總?對于剛建立的分類匯總，如果要將其恢復(fù)為原始數(shù)據(jù)表，可以選擇“編輯”中的“撤消”菜單項取消分類匯總，或單擊撤消按鈕。?但若在建立分類匯總之后，又進行了其他操作，上述方法就無效了，可按如下方法刪除分類匯總。?（1）單擊分類匯總表中的任一單元格。?（2）選擇“數(shù)據(jù)”選項卡中的“分類匯總”命令?（3）單擊“分類匯總”對話框中的“全部刪除”按鈕。

30（1）排序（2）數(shù)據(jù)篩選（3）數(shù)據(jù)分類匯總（4）數(shù)據(jù)透視表（5）合并計算

311.數(shù)據(jù)透視表的功能?①對數(shù)值數(shù)據(jù)進行分類匯總和聚合；②展開或折疊要關(guān)注結(jié)果的數(shù)據(jù)級別，查看感興趣區(qū)域摘要數(shù)據(jù)的明細；③將行移動到列或?qū)⒘幸苿拥叫校ɑ颉巴敢暋保?，以查看源?shù)據(jù)的不同匯總；④對最有用和最關(guān)注的數(shù)據(jù)子集進行篩選、排序、分組和有條件地設(shè)置格式，突出顯示重要信息；⑤提供簡明、帶有批注的聯(lián)機報表或打印報表

32源數(shù)據(jù)透視數(shù)據(jù)

33步驟一：先選中數(shù)據(jù)表中的任一單元格，然后選擇：插入->數(shù)據(jù)透視表，彈出創(chuàng)建數(shù)據(jù)透視表的對話框

34步驟二：將“月份”字段拖拽到“列標(biāo)簽”框中，“客戶”字段拖拽到“行標(biāo)簽”框中，“金額”拖拽到“數(shù)值”框中

35

36

37

38〖案例〗假設(shè)某電視銷售商場有6位職工，分別是勞得諾、令狐沖、陸大安、任我行、韋小寶、向問天，銷售的電視產(chǎn)品有TCL、長虹、康佳、創(chuàng)維、熊貓幾種品牌。每天的銷售數(shù)據(jù)都記錄在Excel的一個工作表中，如圖所示，該數(shù)據(jù)表有300多行數(shù)據(jù)。

39?完成以下統(tǒng)計分析工作?統(tǒng)計各種品牌的電視的總銷售額；?統(tǒng)計每位職工的總銷售額；?統(tǒng)計每位職工銷售的各種類型的電視機的銷售額；?統(tǒng)計各種運輸方式的總數(shù)。數(shù)據(jù)透視表能夠很方便地完成這些工作。

401、建立源數(shù)據(jù)表后，單擊1、“創(chuàng)建數(shù)據(jù)“插入”選項卡中的透視表”對話框“數(shù)據(jù)透視表”彈出“創(chuàng)建數(shù)據(jù)透視表”對話框1、指定數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域后，單擊“確定”按鈕，將彈出數(shù)據(jù)透視表的布局設(shè)置對話框

412、根據(jù)第1步3、用鼠標(biāo)將數(shù)據(jù)設(shè)置創(chuàng)建的數(shù)字段拖放到行、據(jù)透視表列標(biāo)簽和數(shù)值匯總對應(yīng)的文本框中

42?其中數(shù)據(jù)透視表布局中的內(nèi)容?行標(biāo)簽?拖放到行中的數(shù)據(jù)字段，該字段中的第一個數(shù)據(jù)項將占據(jù)透視表的一行。?列標(biāo)簽?與行相對應(yīng)，放置在列中的字段，該字段中的每個項將占一列。?報表篩選?行和列相當(dāng)于X軸和Y軸，由它們確定一個二維表格，頁則相當(dāng)于Z軸。拖放在頁中的字段，Excel將按該字段的數(shù)據(jù)項對透視表進行分頁。?∑數(shù)值

43?統(tǒng)計各銷售員銷售數(shù)據(jù)的透視表布局

44?統(tǒng)計各銷售員銷售數(shù)據(jù)的透視表雙擊任一單元格，會得到這些數(shù)據(jù)的來源明細數(shù)據(jù)

45?雙擊上表E5單元格得到的明細數(shù)據(jù)表

46?利用報表篩選（即頁字段）創(chuàng)建分頁顯示頁字段用鼠標(biāo)單擊它，從下拉列表中選擇某個日期，可以查看此日期的銷售數(shù)據(jù)

47使用多重數(shù)據(jù)字段建立透視表?在前面所討論的電視銷售數(shù)據(jù)表中，要查看每位職工銷售了在行標(biāo)簽中放置了”銷多少種產(chǎn)品，每種產(chǎn)品的數(shù)量各是多少，而且還要查看其銷售員姓名售的產(chǎn)品中，每種運輸方式各運輸了多少數(shù)量?！昂汀边\輸方式“兩字段

48修改數(shù)據(jù)透視表的匯總函數(shù)?數(shù)據(jù)透視表中使用的透視函數(shù)稱為匯總函數(shù)，在默認(rèn)情況下，數(shù)據(jù)透視表采用SUM函數(shù)對其中的數(shù)值項進行匯總，用COUNT函數(shù)對表中的文本類型字段項進行計數(shù)。?Excel提供了許多匯總函數(shù)，如下圖所示。在此選擇需要的”匯總函數(shù)“單擊”求和項“的下拉箭頭，從彈出的菜單中選擇”值字段設(shè)置“，彈出值字段設(shè)置對話框

49〖案例〗下圖是某公司各部門2008年預(yù)算與實際支出的數(shù)據(jù)，試比較分析預(yù)算與實際發(fā)生額的差異與差異率。

50比較差異率的數(shù)據(jù)透視表

51步驟一：調(diào)出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А薄Ｏ劝础癆lt”，放開后按“D”，放開后再按“P”,會彈出如右所示的向?qū)?，選擇“多重合并計算數(shù)據(jù)區(qū)域”單擊“下一步”

52步驟二：指定所需的頁字段數(shù)目。選擇“自定義頁字段”，單擊“下一步”

53步驟三：定義頁“預(yù)算”字段區(qū)域。選擇預(yù)算數(shù)據(jù)區(qū)域，單擊“添加”按鈕加到“所有區(qū)域”列表框中，在字段1中輸入“預(yù)算”

54步驟四：定義頁“實際”字段區(qū)域。選擇實際數(shù)據(jù)區(qū)域，單擊“添加”按鈕加到“所有區(qū)域”列表框中，在字段1中輸入“實際”

55步驟五：添加計算項。先選擇需添加計算項的“頁1”下的“預(yù)算”或“實際”單元格，然后選擇：選項->計算->域、項目和集->計算項，彈出如右所示的對話框，在“名稱”中輸入計算項的名稱，“公式”中輸入計算公式

56步驟六：調(diào)整數(shù)據(jù)透視表布局。①排序②去掉求和項

57（1）排序（2）數(shù)據(jù)篩選（3）數(shù)據(jù)分類匯總（4）數(shù)據(jù)透視表（5）合并計算

581、概念?所謂合并計算，就是對來源于同一工作簿的不同工作表，或不同工作簿中的數(shù)據(jù)表進行合并計算。Excel支持將不超過255個工作表中的信息收集到一個主工作表中，支持的工作表可在同一個工作簿中，也可來源于不同的工作簿。

59?案例?某電視機廠生產(chǎn)21英寸、25英寸、29英寸及34英寸幾種規(guī)格的電視產(chǎn)品，主要銷售于西南地區(qū)。該廠每個季度進行一次銷售統(tǒng)計，每次統(tǒng)計分析保存在一個獨立的工作簿中

60單擊“數(shù)據(jù)”選項卡“數(shù)據(jù)工具”組中的“合并計算”按鈕，彈出“合并計算”對話?建立合并計算框。

61?合并計算的結(jié)果