EXCEL課件-Excel2007在數(shù)據處理與分析中的應用

1、Excel2007在數(shù)據處理 與分析中的應用1 2010 年 3月Excel在數(shù)據分析中的應用Excel2007概述2Excel2007是Office軟件中的核心成員，是最優(yōu)秀的電子表格軟件之一，具有強大的數(shù)據處理和數(shù)據分析能力，是個人及辦公事務中進行表格處理和數(shù)據分析的理想工具之一。 如何利用Excel2007的函數(shù)、圖表、高級分析工具、VBA程序等功能進行數(shù)據分析是本次學習的重點。數(shù)據分析過程與工具數(shù)據分析過程與工具Excel在數(shù)據分析中的應用在數(shù)據分析中的應用 內容提要內容提要3函數(shù)函數(shù)工具工具VBA圖表圖表Excel在數(shù)據分析中的應用在數(shù)據分析中的應用 1、數(shù)據處理與分析基礎學習目標1

2、 認識Excel2007的功能與界面2 學會利用數(shù)據條件格式進行數(shù)據處理3 學會利用排序、篩選、分類匯總功能進行數(shù)據分析4Excel在數(shù)據分析中的應用在數(shù)據分析中的應用 1、數(shù)據處理與分析基礎1.1 Excel2007的功能與界面1.2 數(shù)據的輸入、編輯與運算 （案例：銷售產品基本信息表、銷售記錄匯總表）1.3 利用數(shù)據條件格式進行數(shù)據處理1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析5Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.1 Excel2007的功能與界面1.1.1 Excel2007新增的主要功能（1）取消了菜單方式，采用了面向結果的用戶界面，易于找到。（2）更強大的數(shù)據管理

3、能力和安全性。（如更多的行和列1,048,57616,384，1600萬種顏色）（3）更強大的表功能，提供了全新的數(shù)據引用方式，稱為結構化引用，可以方便地構造動態(tài)數(shù)據報表。（4）其他方面： 提供了大量預定義主題和樣式豐富的條件格式自動調整編輯欄函數(shù)記憶式輸入6Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.1 Excel2007的功能與界面1.1.1 Excel2007新增的主要功能改進的篩選和排序功能（可按日期和顏色排序）圖表外觀更美觀、更專業(yè)、布局和樣式更多易于使用的數(shù)據透視表、數(shù)據透視圖快速連接外部數(shù)據源7Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.1 Excel2007的功能與

4、界面1.1.2 Excel2007的用戶界面整個界面由功能區(qū)和工作表區(qū)組成；功能區(qū)有：Office按鈕、選項卡、組、快速訪問工具欄、標題欄、狀態(tài)欄。Office按鈕：相當于早期的“文件”菜單；選項卡：面向 任務，包括功能控件。“開始”有日常操作功能，“頁面布局”與打印有關（有書也稱主菜單）組：每個組都與特定任務相關；（有書也稱工具欄）快速訪問工具欄：獨立顯示，默認有“保存”、“撤銷”和“恢復“三按鈕，可以自定義；標題欄：顯示工作薄薄名稱。狀態(tài)欄：宏錄制按鈕、查看方式、縮放工具。8Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.2 數(shù)據的輸入、編輯與運算目標：建立某企業(yè)產品銷售數(shù)據統(tǒng)計報表（兩

5、個工作表）1.2.1建立產品基本信息表任務1：在“產品營銷數(shù)據處理與分析實例”工作薄建立形如下面的工作表，名稱為“產品基本信息” 企業(yè)所有商品基本信息列表知識點： 表格內容了解、新建工作薄、新建或改名工作表； 格式化工作表。包括對齊方式、設置單元格格式（下劃線、列寬、邊框、填充顏色）9產品編號產品編號 系列系列產品名稱產品名稱 進貨單價進貨單價 銷售單價銷售單價 AP11001 觀音餅觀音餅（花生） 6.5 12.8 AP11002觀音餅觀音餅（桂花） 6.5 12.8 Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.2 數(shù)據的輸入、編輯與運算1.2.2 建立“銷售記錄匯總”表任務2： 建立

6、表框架（見實例）； 區(qū)分數(shù)據來源（原始的、引用的、計算的）； 輸入公式（從信息表引用的、本表計算的）； 數(shù)據復制（客戶名稱、發(fā)貨日期、訂單號、數(shù)量）。思考： 什么是公式？其價值何在？10公式是由“=”號或“+”號開頭，由常數(shù)、函數(shù)、單元格引用以及運算符組成的式子。其價值不僅是計算，重要的是構建計算模型。Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.2 數(shù)據的輸入、編輯與運算1.2.2 建立“銷售記錄匯總”表思考：公式可分為幾大類？11可分三大類：普通公式：如=A1+B1 , =sum(a1:b5)數(shù)組公式：如 =average(B1:B10-A1:A10)（使用數(shù)組公式可減少存儲空間，提高

7、工作效率，詳見“綜合實例”工作薄中相關內容）命名公式：如： Data名稱表示a1:a10 =sum(data)Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.2 數(shù)據的輸入、編輯與運算1.2.2 建立“銷售記錄匯總”表知識點： 跨表引用。 根據“產品編號”自動返回產品基本信息表“系列”等字段內容（vlookup函數(shù)）； 如E3中公式： =VLOOKUP($D2,產品基本信息表!$B$3:$F$38,2,FALSE) 公式計算字段“系列”、“產品名稱”、“銷售單價”、“成本單價” 12要找的值查找區(qū)域第2列精確比較Excel在數(shù)據分析中的應用在數(shù)據分析中的應用相關知識：Vlookup函數(shù)的使用

8、格式： Vlookup（查找的值，查找區(qū)域，返回的列號，選項）功能： 在表格或數(shù)組的首列查找值 ，并返回表格或數(shù)組中其它列的值 。 選項： FALSE：精確匹配，若找不到返回#N/A TRUE或省略：近似匹配，若找不到返回一個小于要找參數(shù)的最大值。（Excel中的函數(shù)幫助可能有誤，請在編輯欄輸入公式時查看選項含義）13Excel在數(shù)據分析中的應用在數(shù)據分析中的應用VLOOKUP的稍高級應用見綜合實例中相關練習1.2 數(shù)據的輸入、編輯與運算本任務中相關公式說明： 銷售額=數(shù)量 * 銷售單價 總成本 =數(shù)量 * 成本單價 毛利= 銷售額-總成本銷售數(shù)據可從“銷售原始”工作表復制而得。 14Exce

9、l在數(shù)據分析中的應用在數(shù)據分析中的應用1.3 利用數(shù)據條件格式進行數(shù)據處理概述： 條件格式功能，指的是當單元格中的數(shù)據滿足某種條件時就設置某種格式，否則不予設置。利用此功能，可將單元格中的滿足條件的數(shù)據進行特殊標記，以便直觀地查看。方法： 選中數(shù)據區(qū)開始樣式條件格式任務3：對“銷售記錄匯總”表進行 如下操作：（1）把“銷售單價”以圖標集形式顯示（2）把“銷售額”以數(shù)據條形式顯示（3）把 “毛利”低于平均值的數(shù)據設置為黃色。15Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.3 利用數(shù)據條件格式進行數(shù)據處理 知識點：條件格式有以下幾種：（1）突出顯示單元格規(guī)則：對選定區(qū)域內滿足條件的單元格突出

10、顯示，默認的規(guī)則是用某種色彩填充單元格背景。條件包括：大于、小于、等于、文本包含等。（2）項目選取規(guī)則：對選定區(qū)域內小于或大于某個閾值的單元格實施條件格式。條件包括：值最大的10項、高于平均值等。（3）數(shù)據條。以色彩條形圖直觀地表示單元格數(shù)據。數(shù)據條長度代表數(shù)值的大小 。16Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.3 利用數(shù)據條件格式進行數(shù)據處理 知識點：（4）色階。用顏色的深淺表示數(shù)據的分布和變化，包括雙色階和三色階。雙色階使用兩種顏色的深淺程度比較某個區(qū)域的單元格，顏色的深淺表示值的高低。如在綠色和紅色的雙色刻度中，可指定越高越綠，越低越紅。三色階用三種顏色的深淺表示值的高、中、

11、低。（5）圖標集。按閾值把數(shù)據分成3-5個類別 ，每個圖標代表一個值的范圍，根據數(shù)據的大小比例設置圖標的形狀或顏色。17Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析1.4.1 排序概述：排序是對數(shù)據進行重新組織安排的一種方式 ，排序有助于直觀地顯示、組織和查找所需數(shù)據。任務任務4：對銷售記錄匯總表操作對銷售記錄匯總表操作 （1）為查看各“系列”產品的銷售情況，對“系列” 排序（升序或降序）（2）查看不同“發(fā)貨日期”各“系列”產品的銷售情況 按 “發(fā)貨日期”和“系列”兩個字段排序18Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、

12、篩選、分類匯總功能進行數(shù)據分析知識點：按一列排序：光標放于某列中，單擊“升序”/“降序”按鈕，可升序/降序排序；按多字段排序：光標放于表中任意單元格，單擊“排序”按鈕，可按多字段排序。注意： 排序內容是否包含標題欄的選定； 選擇某列排序時的“排序提醒”； 當有復雜表頭時的選擇區(qū)域再排序； 自定義序列排序（如按職務高低排序，見“綜合實例”工作?。?；19Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析知識點：注意： Excel2007對排序字段 不再局限于3個； 不論升降序，空行總在排 在最后； Excel2007可以按單元格顏色排序。思考：排序之后如

13、何回到排序前的狀態(tài)。（必要時引入輔助字段）20姓名姓名性別性別年齡年齡工資工資輔助張三男2534561李四男2332152王五女22 8903Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析1.4.2 自動篩選概述：篩選就是只把滿足條件的數(shù)據行顯示出來，而把不關注的數(shù)據隱藏。篩選有自動篩選和高級篩選兩種。自動篩選易于使用，高級篩選條件可以更復雜。任務5：對銷售記錄匯總表操作對銷售記錄匯總表操作（1） 查看特定系列產品（如“觀音酥”）的銷售情況（自動）篩選（2）查看特定用戶（如“好又多”）、特定系列產品（如“旅游產品 ” ）的銷售情況組合自動篩選21

14、Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析1.4.2 自動篩選知識點： 欲選擇某列中一個項止，先取消本列中的“全選”復選框，然后再單選 ； 注意狀態(tài)欄提示（篩選出的個數(shù)）、列標右側的圖標顯示； 篩選是累加的，后一次篩選在前一次基礎上進行； 解除一列篩選：按“全選”；全部解除：按“篩選”按鈕。22Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析1.4.3 高級篩選概述：可實現(xiàn)多字段間的“或”條件篩選，并能將結果復制到其他區(qū)域。任務6： 查看一段時間內（如2007年 8月上旬）特定用戶（如“好又多”）

15、的銷售情況 此任務可用高級篩選或自動篩選完成。23Excel在數(shù)據分析中的應用在數(shù)據分析中的應用1.4 利用排序、篩選、分類匯總功能進行數(shù)據分析1.4.3 高級篩選知識點：1、條件的寫法（日期條件中用的是日期的數(shù)值）2、取消高級篩選單擊“清除”思考： 要篩選出銷售數(shù)量不低于100，或者毛利不小于2000元的數(shù)據怎么做。24數(shù)量 毛利=100 =2000發(fā)貨日期 發(fā)貨日期 客戶名稱 =39295 50, sum(B2:B5), 0 )=if(a2=10,“科技處”,if(a2=20,“財務處”,“人事處”)31條件取真時取假時Excel在數(shù)據分析中的應用在數(shù)據分析中的應用2 利用函數(shù)函數(shù)進行數(shù)據

16、分析2.2 sumif函數(shù)的應用 任務1：新建一個工作表，命名為“函數(shù)應用“，統(tǒng)計各客戶購買數(shù)量、購買額等數(shù)據之和。知識點：sumif函數(shù)（1）輸入 要分析的數(shù)據欄目 （從銷售記錄匯總表挑選字段粘貼而來）（2）輸入輸入 客戶名稱客戶名稱 的技巧（將分類匯總結果復制到別處）（3）使用sumif函數(shù)計算各個字段。（4）使用排名函數(shù)RANK按毛利對客戶排名。32客戶名稱客戶名稱 數(shù)量數(shù)量銷售額銷售額 總成本總成本 毛利毛利 毛利排名毛利排名Excel在數(shù)據分析中的應用在數(shù)據分析中的應用相關知識：Sumif函數(shù)SUMIF（條件判斷區(qū)域，條件，求和區(qū)域） 根據指定條件對若干單元格求和。條件可以是數(shù)字、表

17、達式或文本，如：“北京”、“=50”等，要使用引號。也可以是單元格引用。案例中C5處（數(shù)量）的公式： =SUMIF(銷售記錄匯總!$A$2:$A$107,$B5,銷售記錄匯總!$G$2:$G$107) 復制到右側單元格，并修改最后一項中的列號，依次為IKL列。（sumif其他例子見“綜合實例”工作薄）33求和區(qū)域條件區(qū)域條件值Excel在數(shù)據分析中的應用在數(shù)據分析中的應用相關知識：RANK函數(shù)的使用格式： RANK（要排位的數(shù)值，排位區(qū)域，選項）功能： 返回一個數(shù)值在一組數(shù)值中的排位。 選項： 非0： 升序排位 0或省略：降序排位34Excel在數(shù)據分析中的應用在數(shù)據分析中的應用2 利用函數(shù)函

18、數(shù)進行數(shù)據分析2.3 sumifs函數(shù)的應用 任務2：用sumifs函數(shù)對工作表“函數(shù)應用”，統(tǒng)計各客戶購買數(shù)量、購買額等數(shù)據之和。知識點：sumifs函數(shù)（1）“數(shù)量”的公式為=SUMIFS(銷售記錄匯總!$G$2:$G$107,銷售記錄匯總!$A$2:$A$107,B$19) 35客戶名稱客戶名稱 數(shù)量數(shù)量銷售額銷售額 總成本總成本 毛利毛利 毛利排名毛利排名求和區(qū)域條件區(qū)域條件值Excel在數(shù)據分析中的應用在數(shù)據分析中的應用相關知識：sumifs函數(shù)的使用格式： sumifs（求和區(qū)域，條件范圍1，條件 1，條件范圍2 ）功能： 對某一區(qū)域內滿足多重條件的單元格求和，最多127個條件。條

19、件可以是“=39295)*(銷售記錄匯總!$C$3:$C$410=39304)*銷售記錄匯總!$N$3:$N$410)中旬與下旬類似，只是數(shù)值不同。思考： 什么情況下要使用SUMPRODUCT函數(shù)？41Excel在數(shù)據分析中的應用在數(shù)據分析中的應用 3 利用圖表進行數(shù)據分析學習目標1 了解圖表的類型及其組成部分2 掌握圖表的建立、編輯與美化方法3 理解復雜圖表的建立方法4 了解動態(tài)圖表的建立方法42Excel在數(shù)據分析中的應用在數(shù)據分析中的應用 3 利用圖表進行數(shù)據分析3.1 圖表概述3.2 圖表的建立、編輯與美化3.3 復雜圖表的建立3.4 動態(tài)圖表的建立43Excel在數(shù)據分析中的應用在數(shù)

20、據分析中的應用3.1 圖表概述 圖表是EXCEL中一個強有力的工具，利用圖表可以使數(shù)據更加直觀地顯示出來。EXCEL有豐富的圖表類型，其功能不遜于某些專業(yè)圖表軟件。 了解圖表有關術語和組成部分是正確使用圖表的前提。1、有關術語（1）數(shù)據點：即數(shù)據標記，本質上是一個單元格中的數(shù)值圖形表示，不同類型的圖表數(shù)據點形狀不同，柱形圖中表現(xiàn)為一個柱形，折線圖中為一個點，面積圖中為一扇形區(qū)域。（2）數(shù)據系列：圖表中一組相關的數(shù)據點，它來自于數(shù)據表的某行或某列，每個數(shù)據系列有相同的顏色和圖案，并且通過圖例標識。一張圖表有多個數(shù)據系列，但餅圖只有一個。（3）數(shù)據標簽：是指為數(shù)據系列或數(shù)據點添加的標識，默認情況下

21、圖表沒有數(shù)據標簽，用戶可以添加數(shù)據標簽、并修改它的位置和大小。 44Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.1 圖表概述有關術語：（4）網格線：用于查看和評估數(shù)據，有水平和垂直兩種。（5）軸：作為繪圖區(qū)一側邊界的直線，有X軸也稱分類軸，Y軸也稱數(shù)值軸兩種。（6）刻度線與刻度標志：類似于尺子上的刻度。（7）圖例：用于說明每個數(shù)據系列中的數(shù)據點所采用的圖形外表。（8）標題：有圖表標題、分類軸標題和數(shù)值軸標題。識別圖表區(qū)域的技巧：單擊圖表上不同區(qū)域，觀看：圖表工具布局當前所選內容。 45Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.1 圖表概述2、最常見的圖形介紹（1）柱形圖：用于顯示

22、某段時間內數(shù)據的變化，或比較各數(shù)據項之間的差異。分類一般為日期或時間，數(shù)值在垂直方向組織，以便于強調相對于時間的變化。（2）條形圖：也用于顯示各數(shù)據之間的比較。與柱形圖不同的是，其分類在垂直方向（一般為日期或時間），而數(shù)值在水平方向，使觀察者的注意力集中在數(shù)據值的比較上，而不在時間上。（3）折線圖：主要用于顯示各數(shù)據隨時間而變化的趨勢情況。橫坐標幾乎總是表現(xiàn)為時間。（3）餅 圖：用于顯示組成數(shù)據系列的各數(shù)據項與數(shù)據項總和的比例。當只有一個數(shù)據系列，并且用于強調整體中的各個組成部分占整體的比例時十分有效。 46Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.2 圖表的建立任務1： 對前面一章任

23、務5中工作表中 的結果 ，制作 各系列產品銷售額百分比分離型三維餅圖。知識點：（1）建立“總計”行，寫入公式：SUM（范圍）（3）選擇上下數(shù)據兩行插入餅圖分離型三維餅圖；（4）輸入圖表標題“各系列產品銷售額占比分析”；（5）添加百分比數(shù)據標簽；圖表工具-布局-其他數(shù)據標簽選項選擇：類別名稱、百分比；選擇：數(shù)字百分比2位小數(shù)。（6）刪除圖例 47Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.2 圖表的建立任務2 ： 仍對前面的工作表中，制作分離型三維餅圖，分析從各客戶獲取利潤的百分比。要求：（1）圖表標題為“從各客戶獲取利潤占比分析”。（2）圖例在下方。思考：如何選擇數(shù)據源？ 48Excel

24、在數(shù)據分析中的應用在數(shù)據分析中的應用3.2 圖表的建立歸納 ：建立圖表的步驟（1）首先選擇數(shù)據源。要選定圖表數(shù)據所在的單元格，如希望數(shù)據的行、列標志也顯示在圖表中，則選定區(qū)還應包括行/列標題。（2）應用預定義的圖表布局和樣式設置圖表。方法：圖表工具布局/樣式。（3）創(chuàng)建圖表工作表。選中圖表，設計位置移動圖表。（可將嵌入式圖表變成圖表工作表，并可移回 ） 49Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.3 復雜圖表的建立 1、概述： 標準圖表在數(shù)據分析中有時仍不能滿足實際需要，用戶可能需要根據實際情況制作復雜一些的圖表，例如在圖表中添加漲跌柱線、垂直線、系列線和高低點連線，給 圖表增加趨勢

25、線等，也可以運用一些技巧制作復雜圖表，如：組合圖表、雙軸圖表、復合餅圖、甘特圖、圖片圖表、趨勢圖等。50Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 復雜圖表的建立2、在圖形上增加趨勢線 趨勢線簡介：趨勢線以圖形方式顯示數(shù)據的發(fā)展趨勢，常用于預測分析（也稱回歸分析，在圖表中擴展趨勢線，根據實際數(shù)據預測未來數(shù)據），條形圖、折線圖、柱形圖、股價圖、氣泡圖、XY散點圖、非堆積型二維面積圖都 支持趨勢線。任務3 ：根據下表的數(shù)據作出圖，并根據折線圖作出趨勢線（線性、指數(shù)、對數(shù)等不同類型的趨勢線），并利用趨勢函數(shù)預測五月份數(shù)據。51某市1-4月份城鄉(xiāng)居民存款額（萬元）一月二月三月四月6296 6

26、448 6560 6604 Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.3 復雜圖表的建立 在折線圖上增加趨勢線效果圖52y = 103.55x + 6218.16100620063006400650066006700一月二月三月四月系列1線性 (系列1)Excel在數(shù)據分析中的應用在數(shù)據分析中的應用折線圖增加趨勢線制作步驟知識點：操作步驟（1）先作折線圖。（2）右擊數(shù)據系列添加趨勢線，在趨勢線選項中選擇一種類型，并“顯示公式”。（3）根據趨勢線函數(shù)預測5月 份數(shù)據，X取第5個數(shù)據點： Y=103.5*5+6218=6735.5 這是5月 份數(shù)據的預測值。 其他類型的趨勢線制作方法與此類

27、似。53Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 復雜圖表的建立3、組合圖表：在一個圖表中表示兩個甚至多個數(shù)據系列，而不同數(shù)據系列用不同的圖表類型表示。任務4 ：組合圖表（柱形-折線）的制作，基礎數(shù)據如下表。54物流信息系統(tǒng)項目進度表 規(guī)劃咨詢需求調研模塊設計軟件編碼系統(tǒng)集成后期測試計劃工作日252015302510實際工作日193025201525Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.3 復雜圖表的建立 組合圖表（柱形-折線）圖的制作效果圖5505101520253035規(guī)劃咨詢需求調研模塊設計軟件編碼系統(tǒng)集成后期測試計劃工作日實際工作日Excel在數(shù)據分析中的應用在

28、數(shù)據分析中的應用組合圖表制作步驟知識點：操作步驟（1 選中數(shù)據區(qū)域。（2）插入圖表柱形圖（3）將數(shù)值小的系列右擊變?yōu)檎劬€圖56Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 復雜圖表的建立 4、雙軸圖表 即雙數(shù)據坐標軸圖表，是指在一個圖表中在主、次坐標軸上分別繪制一個或多個數(shù)據系列，它們采用兩種不同的坐標軸值來度量數(shù)據。任務5 ：雙軸圖表的制作，基礎數(shù)據如下表。57A A市市20092009年年3 3月上旬新建商品房銷售情況表月上旬新建商品房銷售情況表1 1號號2 2號號3 3號號4 4號號5 5號號6 6號號7 7號號8 8號號9 9號號1010號號總銷售面總銷售面積（積（m m2 2

29、）131200131200 9250092500 100020100020 203050203050 220000220000 8000080000 6700067000 7500075000 8000080000 5600056000銷售均價銷售均價（元（元/m2/m2）85008500 10000100009300930089008900910091009400940087008700850085008300830084008400Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.3 復雜圖表的建立 雙軸圖表的制作效果圖58020004000600080001000012000050000

30、1000001500002000002500001號2號3號4號5號6號7號8號9號10號銷銷售售均均價價銷銷售售總總面面積積總銷售面積（m2）銷售均價（元/m2）Excel在數(shù)據分析中的應用在數(shù)據分析中的應用雙軸圖表制作步驟知識點：操作步驟（1）選中數(shù)據區(qū)域。（2）插入圖表柱形圖（3）將數(shù)值小的系列右擊變?yōu)檎劬€圖（4）在折線圖上右擊，設置數(shù)據系列格式系列選項次坐標軸。（5）添加圖表標題，兩個坐標軸標題 ，設置字體。59Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.3 復雜圖表的建立 5、復合餅圖：餅圖可以用來表達單個數(shù)據與整體結果之間的比例關系，而復合餅圖還可以對數(shù)據進行深層次分析。 任

31、務6：復合條餅 圖的制作，基礎數(shù)據如下表。60HP小型機售后一年客戶維修情況反饋項目項目數(shù)量數(shù)量已經更換過62沒有修理過234修理過1次56修理過2次35修理過3次以上17Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.3 復雜圖表的建立 復合條餅 圖的制作效果圖61更換過15%沒修理過58%一次14%二次9%三次以上4%修理過27%HP小型機小型機維修情況調查圖維修情況調查圖Excel在數(shù)據分析中的應用在數(shù)據分析中的應用復合餅圖制作步驟知識點：操作步驟（1）選擇數(shù)據區(qū)，插入“圖表“餅 圖”“復合條餅圖”，設置數(shù)據標簽（百分比、顯示引導線）（2）右擊主餅 圖設置數(shù)據系列格式系列選項將”第二繪

32、圖區(qū)“2改為3。 （3）優(yōu)化圖表，選中圖例刪除 ，雙擊標題、百分比標簽，并修改其內容、設置字體。62Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立概述 所謂動態(tài)圖表，是指圖表的數(shù)據源可以根據需要動態(tài)變化，從而使數(shù)據圖表也隨之變化，一般有三種方式：1、利用有關函數(shù)設置動態(tài)區(qū)域；2、通過定義數(shù)據區(qū)域名稱，并引入輔助數(shù)據區(qū)域；3、利用動態(tài)控件鏈接圖表中的引用數(shù)據，以實現(xiàn)用戶的自由選擇。63Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立任務7 ：利用CHOOSE函數(shù)和組合框建立動態(tài)圖表64某月各地區(qū)個人儲蓄業(yè)務發(fā)展分析（定期存款）單位：萬元某月各地區(qū)個人儲蓄

33、業(yè)務發(fā)展分析（定期存款）單位：萬元地區(qū)地區(qū)余額余額本月凈增額本月凈增額 累計凈增額累計凈增額本月增長率本月增長率累計增長率累計增長率北京北京12836481283648616.8616.8191768.5191768.5-0.13-0.1312.29212.292天津天津579627.3579627.36302.16302.171778717780.770.779.8919.891河北河北271006.4271006.41734.61734.638310.338310.30.4480.44811.52211.522山西山西365395.8365395.8490.5490.527660.5276

34、60.5-0.259-0.2595.7335.733內蒙內蒙233582.3233582.3501.5501.532508.732508.70.0280.02811.31911.319遼寧遼寧969297969297884.8884.8153092.8153092.80.0630.06313.13213.132Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立 利用CHOOSE函數(shù)和組合框建立動態(tài)圖表效果圖65Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立知識點：操作步驟（1）在原數(shù)據區(qū)外建立動態(tài)數(shù)據區(qū)域，作為數(shù)據源復制地區(qū)列到I3，在J3輸入公式：=C

35、HOOSE($M$3,C4,D4,E4,F4,G4)，要根據M3內容顯示相應內容，向下復制公式。（2）轉置復制“余額”等列標題到L3（3）開發(fā)工具控件插入表單控件組合框，右擊設置控件格式格式控制數(shù)據源區(qū)域/單元格鏈接，下拉項數(shù)，將組合框拖入圖表區(qū)。（4）以動態(tài)數(shù)據區(qū)域建立柱形 圖并格式化。（5）從組合框選擇項目，觀看柱形圖變化。66Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立知識點：相關知識公式解釋 Choose函數(shù)的功能是從值的列表中返回一個值 。 如： Choose（2，”a1”,”a2”,”a3”,”a4”）則返回a2，其中第一參數(shù)2為序號。67Excel在數(shù)據分

36、析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立任務8 ：利用INDIRECT等函數(shù)建立動態(tài)圖表68一季度城鄉(xiāng)居民存款情況一季度城鄉(xiāng)居民存款情況地區(qū)一季度二季度三季度四季度北 京19304 19990 20919 21856 天 津6733 6855 7152 7463 河 北19691 20353 20714 21222 山 西10819 11378 11938 12191 內 蒙5260 5478 5715 5945 遼 寧19284 19850 20417 20767 吉 林7889 8220 8432 8450 黑龍江11660 11872 12046 11989 Excel在數(shù)據分

37、析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立 利用INDIRECT等函數(shù)建立動態(tài)圖表效果圖6925%25%25%25%黑龍江黑龍江一季度二季度三季度四季度Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表的建立知識點：操作步驟（1）在原數(shù)據區(qū)外建立動態(tài)數(shù)據區(qū)域，作為數(shù)據源（2）復制標題行到動態(tài)數(shù)據區(qū)域（3）在“地區(qū)”下B15輸入 公式：=INDIRECT(ADDRESS(CELL(row),COLUMN(B4) 向右方各列復制 （4）以動態(tài)數(shù)據區(qū)域建立餅 圖，單擊任一地區(qū)名稱，按F9鍵，即顯示指定地區(qū)的餅 圖。70Excel在數(shù)據分析中的應用在數(shù)據分析中的應用3.4 動態(tài)圖表

38、的建立知識點：相關知識公式解釋（1）CELL(“row”)返回活動單元格的行號，如光標在B4，則返回4（2）column(B4)返回B4單元格的列號，即B（3）address(3,4)則返回單元格地址$D$3 = ADDRESS(CELL(row),COLUMN(B4) 返回光標所在單元格的地址（4）INDIRECT返回由文本字符串指定的引用。在例題中，=INDIRECT(ADDRESS(3,4)將返回“二季度”。又如：A1單元格內容為“A2”，A2的內容是100，A3的公式是=INDIRECT（A1），則A3為100。71 A1A221003Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4

39、利用數(shù)據透視表（圖）進行數(shù)據分析 學習目標 1 掌握數(shù)據透視表、圖的概念和術語。 2 學會建立數(shù)據透視表和數(shù)據透視圖 3 了解EXCEL的數(shù)據鏈接和訪問外部數(shù)據源的一般方法。72Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4 利用數(shù)據透視表（圖）進行數(shù)據分析4.1 數(shù)據透視表簡介4.2 數(shù)據透視表的建立4.3 數(shù)據透視表的美化4.4 數(shù)據透視圖4.5 關于數(shù)據鏈接73Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.1 數(shù)據透視表簡介1、數(shù)據透視表是一種對大量數(shù)據快速匯總和建立交叉列表的交互式表格，是一個功能強大的數(shù)據分析工具。2、數(shù)據透視表的顯著特點是對數(shù)據進行動態(tài)分析，只需要改變字段的位

40、置，即可得到多種分析結果。因此字段的設置是關鍵。74Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.1 數(shù)據透視表簡介3、數(shù)據透視表的主要功能（1）對數(shù)值數(shù)據進行分類匯總和聚合，按分類和子分類對數(shù)據進行匯總；（2）展開或折疊要關注結果的數(shù)據級別，查看感興趣區(qū)域摘要數(shù)據的明細；（3）將行移動到列或將列移動到行（或透視），以查看源數(shù)據不同組合與匯總的結果；（4）對最有用和最關注的數(shù)據子集進行篩選、排序、分組和有條件地設置格式，突出顯示重要信息；（5）提供簡明、帶有批注的聯(lián)機報表 或打印報表。75Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.1 數(shù)據透視表簡介4、何時使用數(shù)據透視表？ 如果要對

41、工作表的數(shù)據進行行、列變換，或者要在數(shù)據量較大的工作表中進行數(shù)據的多種對比分析，就應該使用數(shù)據透視表。 數(shù)據透視表也能完成排序、分類匯總和計數(shù)統(tǒng)計等方面的工作。76Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.1 數(shù)據透視表簡介5、數(shù)據透視表字段列表的運用（1）行標簽。該字段 的一個數(shù)據項占一行，相當于X軸。（2）列標簽。該字段 的一個數(shù)據項占一列，相當于Y軸。（3）報表篩選。由該字段 確定一個二維表，相當于Z軸。按該字段對透視表分頁。（4）數(shù)值 。該字段 中的數(shù)據完成指定的計算，如計數(shù)、求和、求平均等。 77Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.2 數(shù)據透視表的建立任務1：

42、對“銷售記錄匯總”表分析各客戶各系列產品的銷售情況知識點：方法1：（1）光標放在數(shù)據區(qū)，插 入數(shù)據透視表數(shù)據區(qū)確定（在一個新的工作表中建立了透視表）（2）設置行字段區(qū)：客戶名稱、系列。78Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.2 數(shù)據透視表的建立方法2：（1）在一個新的工作表中，插入數(shù)據透視表使用外部數(shù)據源選擇連接瀏覽更多我的電腦選磁盤、文件夾、工作薄、工作表放置位置。（2）設置行字段區(qū)：客戶名稱、系列。79Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.2 數(shù)據透視表的建立知識點：（3）設置數(shù)值區(qū)：數(shù)量、銷售額、總成本、毛利等（4）設報表篩選區(qū)：發(fā)貨日期（5）重設布局。讓行標簽

43、水平顯示（默認情況下在行標簽下的字段都逐一顯示在首行標簽下。下拉-以大綱形式顯示項目標簽/以表格形式顯示）。（6）美化。數(shù)據透視表工具設計-數(shù)據透視表樣式（7）從篩選處下拉。選擇單個日期或“選擇多項”。（8）改名工作表“透視表應用”80Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.2 數(shù)據透視表的建立任務2分析各系列產品的銷售情況知識點：調整行標簽，讓“系列”在“客戶名稱“上面即可。81Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.3 數(shù)據透視表的美化（1）對選中的區(qū)域：開始字體邊框/填充顏色，以添加邊框效果或填充顏色；（2）設計數(shù)據透視表樣式其他（3）設置報表篩選字段，可以根據篩選條

44、件來顯示數(shù)據，可添加多個篩選字段。（4）可以行/列/數(shù)值標簽中刪除或增加字段。標簽中字段次序可調整。（5）數(shù)值字段的計算方式可改變。82Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.3 數(shù)據透視表的美化（6）可控制顯示/不顯示分類匯總和總計（分類匯總是每小組的小計，而總計是一行或一列的總計）。（7）可對選中的字段排序顯示。（8）可對行標簽“選擇字段”或選擇“值篩選”。（9）創(chuàng)建好透視表后，可更改數(shù)據源以創(chuàng)建不同效果的透視 表。選項數(shù)據更改數(shù)據源。（10）清除全部透視 表數(shù)據。選項操作-清除。（11）復制透視表至別處。選項操作選擇整個數(shù)據透視表復制/粘貼 。83Excel在數(shù)據分析中的應用在

45、數(shù)據分析中的應用4.4 數(shù)據透視圖1、概述數(shù)據透視圖是根據透視表的結果轉化而來的，透視表與透視圖相關聯(lián)，它們彼此有對應的字段。透視表中的字段改變了，透視 圖也會變化。數(shù)據透視圖與常規(guī)圖表的意義和操作方法基本相同，其主要不同是透視圖是一種動態(tài)圖表，一張透視圖實際上是一系列圖表，圖表內容可根據數(shù)據項的變化而變化。84Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.4 數(shù)據透視圖2、數(shù)據透視表圖的生成與編輯 任務3： 修改前面的透視表并轉化為透視圖，字段安排如下：（1）報表篩選：發(fā)貨日期，行標簽：客戶名稱，數(shù)值：毛利求和，分離型三維餅形圖；（2）報表篩選：日期，行標簽：客戶名稱，數(shù)值：銷售額求和、

46、總成本求和總成本求和、簇狀柱形圖；（3）行標簽：發(fā)貨日期，數(shù)值：毛利求和，分離型三維餅形圖；（4）報表篩選：日期，行標簽：客戶名稱，列標簽：系列，數(shù)值：銷售額求和，簇狀柱形圖。85Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.4 數(shù)據透視圖2、數(shù)據透視表圖的生成與編輯將透視表轉化為透視圖后，會自動在工作表中生成“數(shù)據透視圖篩選窗格”，通過設置該窗格中的條件，可以生成不同的透視 圖。數(shù)據透視圖的主要編輯有：編輯圖表標題，坐標軸標題，圖例，數(shù)據標簽，坐標軸刻度，數(shù)據系列的樣式，更改數(shù)據源、更改圖表類型數(shù)據透視圖的美化設置、圖標設置、縱坐標軸設置、圖例設置等，與普通圖表的設置類似。86Excel

47、在數(shù)據分析中的應用在數(shù)據分析中的應用4.4 數(shù)據透視圖3、圖表布局與圖表樣式圖表布局是指圖表中各對象的擺放位置。 圖表樣式是指設置圖表效果的一種樣式方案。 EXCEL提供了多種布局和樣式方案可供選擇。方法是： 主菜單：數(shù)據透視工具設置圖表布局； 主菜單：數(shù)據透視工具設置圖表樣式。新增練習：對“綜合實例”工作薄，透視 表圖工作表操作：（1）各省各民族 人數(shù)。（2）各班各民族 人數(shù)（3）各班生源分布（4）各省生源政治面目情況。87Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接1、外部引用在一個工作薄中引用另一工作薄中的數(shù)據稱外部引用，也叫鏈接。前者稱目標工作薄，后者稱源工作薄。

48、鏈接使一個工作薄可以共享另一工作薄中的數(shù)據。打開目標時，源可打開也可關閉，如果先打開源后打開目標，數(shù)據會自動更新。用戶可以自己適時刷新數(shù)據。當修改源數(shù)據時，EXCEL會通過鏈接自動修改鏈接工作薄中的數(shù)據。舉例： 利用外部數(shù)據源（如數(shù)據源在另外一個工作表）生成透視表。（各客戶銷售額統(tǒng)計）88Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接 2、EXCEL與外部數(shù)據庫 企業(yè)數(shù)據往往以數(shù)據庫或特殊文件（如XML、TXT）形式存儲在服務器、網站 或某些辦公室的計算機中，如果將這些數(shù)據導入到EXCEL，就能利用EXCEL進行數(shù)據處理和分析 ，同時 EXCEL也可以作為數(shù)據庫系統(tǒng)的數(shù)據采

49、集工具。第第89頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接 3、EXCEL可訪問的外部數(shù)據庫 包括SQL Server,Oracel, Access,Foxpro等。4、數(shù)據源數(shù)據源就是數(shù)據的來源，是其他軟件如EXCEL訪問數(shù)據庫的一組信息，包括數(shù)據庫服務器的名稱和位置，用于連接數(shù)據庫的驅動程序名稱，以及登錄到數(shù)據庫時需 要的各種信息。EXCEL可通過數(shù)據源訪問各種關系型 數(shù)據庫。5、數(shù)據源的類型主要有l(wèi)ODBC 數(shù)據源lOLE DB數(shù)據源第第90頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接l另外，EXCEL可以直接訪問文本文件和 Acce

50、ss數(shù)據庫。 數(shù)據源的數(shù)據更新后，可通過在EXCEL中右擊數(shù)據列表后的菜單選擇“刷新” 以獲得數(shù)據庫最新的數(shù)據。6、ODBC 數(shù)據源的訪問 前提： 計算機中需要有數(shù)據庫相應的ODBC驅動程序，如果沒有(如MYSQL)，可以到該數(shù)據庫的官方網站進行下載并安裝。第第91頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接訪問步驟：1、 創(chuàng)建ODBC數(shù)據源:控制面板-管理工具-數(shù)據源（ODBC）-文件DSN-選擇ODBC驅動程序如Oracle in OraHome92 ， Microsoft ODBC for Oracle-輸入數(shù)據源名稱及保存位置-直到完成2、 在Excel中 “

51、數(shù)據”“自其他來源”“來自Microsoft Query ”選擇剛建立的數(shù)據源 第第92頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接7、Microsoft Query Microsoft Query是一個功能強大而且靈活的應用程序，通過它可以檢索外部數(shù)據庫中的數(shù)據，在EXCEL中可以調用它，將外部數(shù)據庫中的數(shù)據（甚至是數(shù)據庫中的符合條件的數(shù)據）導入EXCEL。8、訪問外部數(shù)據庫的條件具有訪問外部數(shù)據庫的權限、安裝 Microsoft Query、建立ODBC或其他數(shù)據源驅動程序 。通過WEB查詢不需安裝Microsoft Query，但須有連接網絡的各種設備和身份，還

52、須有可運行的WEB查詢文件（.iqy） 。第第93頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接9、OLE DB數(shù)據源的訪問所有的ODBC數(shù)據源都可以以OLE DB方式導入提供了OLE DB驅動程序的數(shù)據庫可以以OLE DB方式導入訪問步驟：訪問步驟： 在Excel中 “數(shù)據”“來自數(shù)據連接向導”其他/高級選擇OLE DB驅動程序-輸入必要信息。第第94頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用4.5 關于數(shù)據鏈接10、EXECL可直接訪問的數(shù)據源可以在EXCEL和文本文件（如記事本）或Access數(shù)據庫的表記錄間復制粘貼。但這種數(shù)據是“死”的數(shù)據。 如在EXC

53、EL中鏈接到Access數(shù)據庫，Access數(shù)據庫中的數(shù)據更新后，在EXCEL中可“刷新”得到最新數(shù)據。方法：數(shù)據自文本 或數(shù)據自Access。第第95頁頁Excel在數(shù)據分析中的應用在數(shù)據分析中的應用新增的內容5 構造動態(tài)數(shù)據分析報表 學習目標 1 理解EXCEL表的的概念，學會建立EXCEL表的方法。 2 學會使用結構化引用建立動態(tài)報表。 3 學會使用D函數(shù)和結構化引用建立動態(tài)報表。96Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5 構造動態(tài)數(shù)據分析報表5.1 表與動態(tài)報表5.2 D函數(shù)與動態(tài)報表97Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表5.1.1 表的基本概

54、念及其 操作1、問題的提出所謂動態(tài)報表就是指數(shù)據區(qū)域的大小可能會隨時間而變化的工作表。在數(shù)據的統(tǒng)計與分析中，經常需要構造動態(tài)報表。以“函數(shù)應用”工作表為例，隨著時間的推移，當“銷售記錄匯總”表增加或減少數(shù)據行時，原來的公式就不能自動適應，從而就不能得到正確的分析結果。對于前面介紹的透視表和透視圖，同樣有類似問題。只有更新透視表的數(shù)據源方到最新分析結果。Excel2007中，可以通過Excel專用表（簡稱表或表格）快捷地構造出動態(tài)報表，并能方便地對動態(tài)報表進行各種統(tǒng)計分析。 98Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表2、什么是表？如何建立表表也稱表格，是Excel的特

55、殊對象（即03版本的列表、數(shù)據清單 ）它是一個完整的結構，包含表區(qū)域、表數(shù)據區(qū)域、匯總行、標題 行、列標題 、調整大小控制點等內容。 表具有數(shù)據篩選、排序、匯總和計算等功能，并能自動擴展數(shù)據區(qū)域，查通過表來構造動態(tài)報表。 表（表格）工作表（區(qū)域），它有許多特性，有“表工具”選項卡。建立表的方法： 選中工作表中的數(shù)據區(qū)域（也可以是空白區(qū)域），“插入”“表”創(chuàng)建表對話框 。注意列必須要有標題 。 一個工作表中可以插入多個表。 也通過自動套用表樣式也能將區(qū)域轉化為表。99Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表3、關于表樣式Excel為表預定義了許多格式，稱為表樣式，它包括

56、顏色、邊框線、底紋等諸多格式化樣式，格式化工作表最直接的方式就是套用表樣式。在套用表樣式時，Excel會將區(qū)域轉化為表。為工作表自動套用表樣式的方法： 單擊任一單元區(qū)域，開始樣式套用表格樣式選擇一種樣式確定；在“表工具”“設計”中，提供了許多選項。表轉換為區(qū)域：可通過“表工具”“設計”“工具”“轉換為區(qū)域”區(qū)域把表轉換為區(qū)域。100Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表任務1： （1）把 “銷售記錄匯總”表復制為“銷售記錄匯總表”工作表； （2）將普通工作表轉換為表，修改表名為“銷售表”； （3） 在“動態(tài)報表”工作表中完成“統(tǒng)計各客戶本期購買各系列產品的金額”。1

57、01Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表知識點：表的基本操作（1）增加計算列，寫入公式（如=2或=銷售表毛利*2，不一定在最上方單元格內輸入）并回車，公式可擴展至其他行（即整列）。（2）可修改列名稱。（3）可以控制是否顯示“匯總”行。（4）修改表名稱。（5）擴展表格。（6）“表工具”設計其他按鈕。102Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表 通過表能夠方便地構造日常工作中的動態(tài)報表，在各種公式中通過對表的結構化引用，不僅使公式含義清楚，而且能夠擴展公式的計算能力，實現(xiàn)對動態(tài)報表的各類計算 。103Excel在數(shù)據分析中的應用在數(shù)據分析中

58、的應用5.1 表與動態(tài)報表5.1.2 結構化引用1、什么是結構化引用 在對表進行計算的公式中，可以引用表中的單元格，也可以引用表的行、列、數(shù)據區(qū)域、匯總行或標題等表結構，后者稱為結構化引用。其最大特點是對動態(tài)報表的自動識別，無論表的數(shù)據區(qū)域怎樣變化，結構化引用的單元格區(qū)域都 能自動調整，極大地方便了在表中增加、刪除行或列時重寫公式的需要。 簡單而言：公式中引用了表結構，就是結構化引用。104Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表2、結構化引用的語法規(guī)則（1）表名稱：相當于表數(shù)據區(qū)，不包括標題行和匯總行，名稱可修改。（2）列說明符：由括號括起，并引用列數(shù)據，不包括標題

59、行和匯總行。（3）特殊項目說明符：說明表中特殊部分，如#數(shù)據、#全部、#此行、#標題、#匯總等；（4）表說明符：跟在表名稱后， 括起；（5）結構化引用：以表名稱開始，以表說明符結尾。105Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.1 表與動態(tài)報表3、結構化引用示例 在表中輸入一個公式如：=sum(L2：L107)，在表最下方增加數(shù)據（毛利），看公式能否調整？（這是單元格引用）輸入一個公式如：=sum(銷售表毛利)，在表最下方增加數(shù)據（毛利），看公式能否調整？（這是結構化引用，公式中引用了表的結構）106Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.2 D函數(shù)與動態(tài)報表1、D函數(shù) E

60、xcel把每個列都有標題的數(shù)據表稱為數(shù)據庫，并提供了12個專用函數(shù)來簡化對這種表的統(tǒng)計和查找工作，這些函數(shù)均以D打頭，故稱D函數(shù)。如Dsum、Daverage 、Dcount、Dmax,Dmin、Dproduct等。D函數(shù)的調用格式、形式參數(shù)表都 相同。語法格式如下： Dname(單元格區(qū)域，要計算的列字段，條件區(qū))其中：單元格區(qū)域，要求每列都有列標題。列字段，是某列的列標題。條件區(qū)的含義和構造方法同高級篩選。107Excel在數(shù)據分析中的應用在數(shù)據分析中的應用5.2 D函數(shù)與動態(tài)報表2、D函數(shù)與表結合構造動態(tài)數(shù)據分析表可以在D函數(shù)中對表進行訪問，通過在D函數(shù)中對表的結構化引用，能夠輕松地計算