1、韓小良 Excel高效數據分析之道Excel高效數據分析之道讓您的分析報告更有說服力使用Excel的基本素養充分認識Excel是一個科學管理工具Excel是一個科學管理工具管理要用數據說話，而不是拍腦袋做決策一個有說服力報告的重點內容是數據分析表格和圖表養成使用Excel的好習慣合理設計Excel表單，為日常數據管理和處理分析打好基礎原始數據表格與數據處理分析表格分開保存采用合理的方法獲取外部數據等等自定義Excel操作界面添加常用的操作按鈕（格式按鈕、增大/縮小字號按鈕、選擇性粘貼按鈕、粘貼數值按鈕、等等）熟練使用常用的快捷鍵等等讓你的Excel發揮最大的效能安裝完全版（Excel 2003

2、）加載分析工具庫Excel的思考Excel是什么?Excel能做什么?Excel怎樣使用?如何讓你的分析報告更有說服力?用數據說話完美的分析報告用圖表說話本課程目標用數據說話：掌握快速制作各種統計分析報表的方法、技巧和各種應用用圖表說話：掌握制作精美實用分析圖表的方法、技巧和各種應用課程目錄第1部分：不可忽視的基礎性工作第2部分：快速掌握Excel公式和函數第3部分：讓表格數據更加清晰第4部分：用數據說話第5部分：用圖表說話第1部分 標準化與規范化不可忽視的基礎性工作為什么要做標準化與規范化工作Excel表格容易出現的錯誤快速修改非法日期快速轉換文本型數字快速刪除數據中的特殊字符快速分列數據快

3、速填充數據 其他數據整理技巧 為什么要做標準化與規范化工作Excel處理分析數據是非常靈活的，但是不能隨心所欲地設計表格和輸入數據，否則Excel中很多功能或函數將可能無法實現。Excel表格的標準化與規范化，是數據處理分析的基礎。Excel表格的標準化與規范化包括：結構標準化和規范化：很多表格的結構設計是不規范的，使得無法使用函數創建高效率計算公式，也無法使用相關的工具實現數據的高效處理和分析。數據標準化和規范化：很多表格的數據也是不規范的，使得編制的分析報告出現偏差和錯誤。所以，當拿到一個表格時，首先要檢查表格的結構和數據是否規范，是否有錯誤。Excel表格容易出現的錯誤表格結構的常見錯誤

4、和不規范多行標題多列標題合并單元格不同類型數據放在一個單元格表格數據的常見錯誤和不規范文本有很多不必要的空格非法日期文本型數字數據中含有特殊字符數據格式不統一快速修改非法日期Excel是如何處理日期的？日期是特殊的數字。日期永遠是大于零的正整數。正確輸入日期正確輸入日期：2010-10-8，2010/10/8，8/oct/2010， 8-oct-2010錯誤的輸入日期方式：2010.10.08，或者 錯誤日期的類型輸入錯誤從系統導入的日期是文本格式修改非法日期（案例01）基礎知識：常用日期函數及其應用快速轉換文本型數字在一個表格中，有時候需要把數字處理為文本；有時候又需要把文本型數字轉換為純數

5、字，以便利用有關函數進行統計計算。如何將文本型數字轉換為純數字方法1：利用智能標記方法2：利用VALUE函數方法3：利用公式（兩個負號或者乘以1或除以1）方法4：利用選擇性粘貼的批量修改功能（乘以1或者除以1）方法5：利用分列工具如何把數字轉換為文本型數字?使用分列工具使用TEXT函數注意：文本型數字使用普通函數計算是可以的，但使用數據透視表或者建立分類匯總，就會出現錯誤。案例02 將文本型數字轉化為純數字快速刪除數據中的特殊字符從系統中導入的數據中，可能會有很多看不見的“垃圾”字符，會影響到數據的處理和分析，應當予以刪除。比如：刪除數據中的空格刪除字符中的打印不出的特殊字符（案例03）快速分

6、列數據有些情況下，從系統導入的數據是一列數據，需要根據實際情況進行分列。基本方法方法1：使用“分列”工具方法2：實用文本函數方法3：使用數組公式案例04 數據分列基礎知識：文本函數及其應用 快速填充數據有些時候，從系統導入的數據表格有大量的空白單元格，需要進行填充。根據具體情況：取消合并單元格并填充數據（標準的數據表是不允許有合并單元格的）填充為上一行的數據填充為數字0案例05 快速填充數據其他數據整理技巧批量修改數據轉置數據位置復制可見單元格數據 快速刪除空行和空列快速批量插入空行和空列快速刪除工作表中的所有圖形對象第2部分 武裝自己快速掌握Excel公式和函數復雜情況下邏輯判斷及其簡化處理

7、將繁瑣的加減公式變得更為簡潔和高效讓需要的數據迅速出現在您面前創建高效計算公式的技巧和方法復雜情況下邏輯判斷及其簡化處理很多數據邏輯判斷不是一個IF函數所能解決的，需要使用多個IF函數。在另外一些情況下，還需要聯合使用AND函數或者OR函數進行復雜的數據判斷處理。如果判斷條件超過了7個，就需要使用條件表達式來處理。案例06 復雜的數據判斷處理將繁瑣的加減公式變得更為簡潔和高效低效率的加減公式，既不科學，也容易出錯。在很多情況下，可以使用有關的求和函數來解決。常用的求和函數SUM函數：無條件求和。但SUM函數還有另類用法，比如快速計算表格中的小計的總和 ； 快速對多個工作表求和SUMIF 函數：

8、單條件求和=SUMIF(條件判斷區域，條件值，實際求和區域)SUMIFS函數（Excel 2007新增函數）：多條件求和=SUMIFS(實際求和區域，條件判斷區域1，條件值1，條件判斷區域2，條件值2，.)SUMPRODUCT 函數：計算乘積和，可以用于多條件求和、多條件計數= SUMPRODUCT(數組1，數組2，數組3，.)SUBTOTAL 函數：對數據進行分類匯總，主要用于篩選和列表 = SUBTOTAL(函數類型，計算區域1，計算區域2，.)案例07 高效求和匯總讓需要的數據迅速出現在您面前很多情況需要將需要的數據查找出來，此時有很多查找函數可以選擇使用。查找函數有很多，其中使用最多的

9、是VLOOKUP函數、INDEX函數、MATCH函數、INDIRECT函數。VLOOKUP函數：根據首列數據進行查詢（案例08）=VLOOKUP(查找依據，查找區域，指定列位置，邏輯值)INDEX函數：根據行索引和列索引進行查詢（案例09）= INDEX(查找區域，指定行位置，指定列位置)MATCH函數：對數據進行定位（案例09）= MATCH(查找依據，查找區域，指定方式)INDIRECT函數：對字符串表示的“單元格地址”進行轉換= INDIRECT(字符串表示的“單元格地址”)查找函數綜合應用：案例10 查詢重復數據注意事項：1、無法查找重復值2、不區分大小寫創建高效計算公式的技巧和方法很

10、多實際問題，需要使用多個函數，創建復雜的計算公式來解決。復雜的計算公式，就是很多函數的嵌套。創建高效計算公式的方法：仔細分析問題的本質所在確定所要使用的函數先分解公式，再綜合公式必要時使用輔助列第3部分 讓表格數據更加清晰直觀的數據是好報告的基礎使用自定義數字格式美化表格使用條件格式標識特殊數據讓龐大復雜的表格變得更加容易察看使用列表對數據進行篩選和匯總分析直觀的數據是好報告的基礎一份好的分析報告，首先要使表格的數據易于察看和管理。可以使用相關的工具，把表格數據進行直觀性處理。例如：使用自定義數字格式，可以快速了解跟蹤超預期的數字使用自定義數字格式，可以把大型數字縮小位數顯示使用條件格式，可以

11、把特殊的數據動態標識出來，并進行動態跟蹤分析對數據進行分類匯總和分級顯示，可以讓大型表格變得更加精煉使用自定義數字格式美化表格當數字很大時，表格既不便于查看數據，打印出的表格也很不美觀。可以通過設置數字的自定義格式來美化表格。例如：將數字縮小1千倍顯示將數字縮小1萬倍顯示將數字縮小百萬倍顯示將特殊的數字顯示為指定的顏色案例11 自定義數字格式 使用條件格式標識特殊數據利用條件格式，可以標識表格的特殊數據。比如：標識非法數據設計提前提醒模塊（案例12） 小知識：在Excel 2007中，可以對顏色進行篩選和分類匯總，不論這種顏色是固定格式，還是條件格式。讓龐大復雜的表格變得更加容易察看很多表格中

12、，既有合計數，也有明細數，使得表格看起來很不方便。可以建立分類匯總和分級顯示，讓大型表格變得更加精煉案例13 創建多層次的分類匯總和分級顯示 使用列表對數據進行篩選和匯總分析列表具有自動篩選的全部功能，但又有新的功能：自動擴展篩選區域手動調整篩選區域自動復制篩選區域內的公式可以添加/取消各種方式的分類匯總列表的名稱Excel 2003中稱為“列表”Excel 2007中稱為“表”案例14 列表的應用第4部分：用數據說話快速匯總多個工作簿和工作表數據數據透視表的使用技巧和實際應用快速匯總多個工作簿和工作表數據快速匯總個數不定的多個工作表快速匯總多個結構完全相同的工作表快速匯總結構不同的多個工作表

13、(特殊情況) 快速匯總結構不同的多個工作表(一般情況)快速匯總多個有關聯的工作表快速匯總個數不定的多個工作表使用相關的函數，可以快速匯總大量工作表數據。例如：使用SUM函數可以快速加總大量工作表使用INDIRECT函數可以動態匯總大量的個數不定的工作表案例15 動態匯總個數不定的工作表（案例1）案例16 動態匯總個數不定的工作表（案例2）快速匯總多個結構完全相同的工作表當多個工作表的結構相同時，可以使用合并計算工具快速進行合并計算，同時還可以創建分類匯總和分級顯示。案例研究:案例17 快速合并多個結構相同的分報表(當前工作簿)案例18 快速合并多個結構相同的分報表(不同工作簿) 快速匯總結構不

14、同的多個工作表（特殊情況）利用多重合并計算數據區域的數據透視表，可以快速把數十個甚至上百個工作表的數據匯總在一起，然后在此基礎上進行相關的統計分析。注意：要匯總的多個工作表數據行數和列數可以不同，但從數據區域的第2列開始必須是數字。案例19 快速匯總多個工作表數據(特殊情況) 三個分公司數據匯總到一張工作表快速匯總結構不同的多個工作表（一般情況）一般情況下，要匯總的各個工作表數據可能含有多列文本，此時就不能采用普通的多重合并計算數據區域的數據透視表來進行匯不總了。最好的解決方法：使用導入外部數據+SQL語句。案例20 快速匯總多個工作表數據(一般情況) 快速匯總多個有關聯的工作表有些情況下，幾

15、個工作表中分別保存不同的數據，但他們通過一個關鍵列數據（比如工號）聯系起來。要將這樣幾個相關聯的工作表數據進行匯總，可使用Microsoft Query。案例21 快速匯總多個有關聯的工作表數據 數據透視表的使用技巧和實際應用數據透視表的基本概念數據透視表的類型準備工作創建數據透視表的基本方法和注意事項設置數據透視的格式利用數據透視表快速編制各類統計分析報表綜合應用之1：對比分析兩個表格數據綜合應用之2：進銷存數據查詢數據透視表的基本概念什么是數據透視表？數據透視表能做什么？怎樣靈活使用數據透視表？數據透視表的類型普通區域數據透視表合并區域數據透視表外部數據源的數據透視表OLAP數據透視表準備

16、工作制作數據透視表的數據區域必須是數據清單數據區域第一行為列標題列標題不能重名數據區域中不能有空行和空列數據區域中不能有合并單元格每列數據為同一種類型的數據整理數據區域修改非法日期把文本型數字轉換為純數字刪除數據區域內的所有空行和空列取消合并單元格并進行填充去掉字符串前后的空格刪除數字中的特殊字符刪除不必要的小計行和總計行將二維表格整理為數據清單等等創建數據透視表的基本方法和注意事項利用數據透視表向導（案例22）按照向導步驟進行操作缺點：無法實現數據源的動態更新，除非使用動態數據區域名稱解決方法：（1）利用導入數據方法；（2）利用OFFSET函數定義動態名稱利用導入數據方法按照導入數據向導進行

17、操作優點：可以實現數據源的動態更新；可以在不打開其他工作簿的情況下創建數據透視表可以以數據庫的數據創建數據透視表缺點：刪除數據項目后，會遺留“假”項目名稱利用數據庫查詢方法按照數據庫查詢向導進行操作優點：不用導入數據，就可以制作需要的報表可以有選擇性地對某些符合條件的數據制作報表可以在不打開其他工作簿的情況下創建數據透視表可以以數據庫的數據創建數據透視表設置數據透視的的格式在數據透視表上對數據透視表進行重新布局修改字段名稱設置字段的數字格式取消數據透視表的行或列匯總取消字段的分類匯總讓數據透視表數據按照某一字段進行排序合并數據標志設置錯誤值的顯示方式更新數據透視表的數據利用數據透視表快速編制各

18、類統計分析報表數據透視表為我們提供了很多實用的數據統計分析工具，可以快速編制各種統計分析報表，例如：布局透視表設置字段的分類匯總方式（案例22）設置字段的顯示方式（案例22）自定義字段（添加新的分析指標）（案例22）組合字段（案例23）制作明細表快速匯總大量工作表數據綜合應用之1：對比分析兩個表格數據兩個工作表有什么不同？如何快速把兩個工作表的差異數據找出來？案例24 綜合應用之1：對比分析兩個表格數據 綜合應用之2：進銷存數據查詢如何快速了解各個物料、商品等的入庫、出庫以及庫存情況？利用數據透視表，可以非常容易地制作出進銷存數據查詢表格。案例25 綜合應用之2：進銷存數據查詢第5部分 用圖表

19、說話用圖表準確表達出你的觀點繪制圖表基本方法及注意事項圖表的修飾與美化用組合圖表表達更加復雜的信息讓圖表按照您的要求靈活顯示用圖表準確表達出你的觀點圖表類型的選用原則圖表分析數據的出發點：正確分類顯示數據快速轉換圖表數據分析的視角圖表類型的選用原則圖表類型柱形圖、條形圖、折線圖、餅圖、XY 散點圖、面積圖、圓環圖、雷達圖、曲面圖、氣泡圖、股價圖、圓柱圖、圓錐圖和棱錐圖 選用原則和建議首先要突出重點，充分反映數據信息關注事物的本質！不同類型的圖表有不同的適用場合。其次是美化圖表。關于組合圖表幾種圖表類型的組合。在某些情況下，單一的圖表類型無法滿足要求，需要繪制組合圖表。關于動態交互圖表使用動態圖

20、表，可以大大簡化工作量，使得數據分析更加高效。圖表分析數據的出發點：正確分類顯示數據以列數據或者以行數據分類顯示數據，分析問題據的出發點是不同的。 以地區分類：以產品分類：快速轉換圖表數據分析的視角快速轉換圖表數據分析的視角，可以從不同的角度分析數據方法：在Excel 2003中使用Excel的圖表工具欄在Excel 2007中使用相關命令按鈕使用“源數據”對話框繪制圖表基本方法及注意事項繪圖的基本方法圖表的位置默認區域繪圖時一個需要注意的問題X軸是日期時的問題復合餅圖的調整問題Excel 2003的繪圖方法Excel 2003的繪圖基本方法：使用圖表向導使用默認的圖表注意事項：如果使用名稱繪

21、圖，就必須使用圖表向導Excel 2007的繪圖基本方法使用“插入”選項卡使用“選擇數據源”對話框注意事項：如果使用名稱繪圖，就必須使用“選擇數據源”對話框圖表的位置嵌入式圖表適用于數據較少，需要把表格數據與圖表一起查看的場合圖表工作表適用于數據量較大，圖表較大的場合默認區域繪圖時一個需要注意的問題當數據區域的第1列是數字時，采用默認圖表的方法繪制的圖表，會把這列數據作為系列繪制在圖表上。案例26 默認區域繪圖的潛在問題 X軸是日期時的問題如果X軸數據是日期，并且是不連續的，在繪制柱形圖、條形圖、折線圖等時，會以連續的日期在X軸上顯示。案例27 X軸是日期時的問題 復合餅圖的調整問題繪制復合餅

22、圖，要合理設置小餅的項目。方法：按位置調整按數值調整按比例調整案例28 復合餅圖的調整問題圖表的修飾與美化圖表結構及主要元素打扮您的圖表突出標識圖表的重點信息簡單是美圖表結構及主要元素圖表區繪圖區數據系列分類軸數值軸（刻度、顯示單位）圖表標題圖例數據標簽網格線趨勢線、高低點連線、垂直線、漲跌柱線打扮您的圖表美化圖表的基本原則：更加便于關注事物的本質，而不是其他無關緊要的東西。簡單是美設置圖表各元素的格式重點關注圖表區背景、繪圖區背景、數據系列柱形背景等對于折線圖和XY散點圖，要合理設置網格線格式美化圖表常犯的錯誤過渡修飾圖表元素淡化了圖表重點信息案例練習：各種常見圖表的美化及效果對比案例29 圖表美化效果對比突出圖表的重點信息使用自選圖形、圖片等，突出重點數據，使閱讀者一目了然地了解數據信息，發現企業經營存在的問題。案例30 利用自選圖形突出重點 簡單是美要注意：圖表是向別人傳達信息的圖表不是時裝秀因此：圖表的美化不應影響主體信息的表達不要給人一種五彩繽紛的感覺一句話：簡單是美繪制組合圖表：表達更加復雜的信息繪制兩種單位的數據系列的組合圖表差異對比分析圖資金流動分析圖產品合格率分析圖繪制兩種單位的數據系列的組合圖表兩種單位的數據系列是不能繪制在同一個坐標軸上的，而必須分別繪制在主軸和次軸上。例如：銷售量-