Excel數據分析方法、技術與案例

世界500强企业内训师，Excel Home平台讲师，数据分析师，拥有16年Excel应用与分析经验，5年企业内训师经验，精通Excel函数、图表、建模、透视表和分析系统构建。

他的Excel技术没有华丽的技巧，以实战为主。他推崇的理念是：多用图少用表，让文字等于0；通过数据分析看本质，模型是效率之王。

第 1章 數據分析的核心目的與常見誤區 1

1.1　數據分析的三大核心目的　1

1.1.1　發現問題　1

【案例1——超標開支都到哪裡去了】　1

【案例2——哪些材料是控制和改善的重點】　2

1.1.2　預測未來　4

【案例3——明年銷售預測如何做】　4

【案例4——哪個價格更合適】　4

1.1.3　優化決策　5

【案例5——哪個價位適合建立庫存】　5

【案例6——哪個候選人更適合】　6

1.2　數據分析的四大誤區　6

1.2.1　什麽都要分析——貪多　7

【案例7——多多益善的分析結果，領導一定喜歡嗎】　7

1.2.2　幾個數據想分析出“一朵花”——做夢　7

【案例8——想用3個月數據預測全年情況】　7

1.2.3　數據量大不知道分析啥——抓瞎　8

【案例9——一句話的任務】　8

1.2.4　不會可視化圖表——暈菜　8

【案例10——怎樣發現哪些產品是明星產品】　9

【案例11——哪些價格是不合理的】　9

1.2.5　機智地回答領導的“靈魂三問”　10

第　2章 數據分析流程與分析元素　12

2.1　數據分析流程的五大步驟　12

2.1.1　分析設計：先思考，再動手　12

2.2.2　數據採集：盡量確保原始數據的規範性　12

2.2.3　數據整理：規範化並消除錯誤　13

2.2.4　數據分析：4個小步驟　13

2.2.5　數據報告：圍繞說服力進行撰寫　13

2.2　“一句話的任務”案例　14

【案例——一句話的任務，我該怎麽辦】　14

2.3　分析的5個元素：量價額差率及其組合　16

2.3.1　1分鐘瞭解五大元素　16

2.3.2　單元素分析　17

【案例1——產品1～8月銷售量分析】　17

【案例2——產品全年銷售額】　17

【案例3——貿易增長率分析】　18

2.3.3　雙元素分析　18

【案例4——對產品銷量進行“量量”分析】　18

【案例5——網店對當周銷量進行“量價”分析】　19

【案例6——機械廠對產品進行“量率”分析】　19

2.3.4　三元素分析　20

【案例7——某公司為考察銷售目標完成度進行“量量率”分析】　20

2.3.5　五大元素與其他元素的組合分析　21

2.3.6　確定分析維度的3種原則　21

第3章　數據採集：快速獲取有價值的數據　23

3.1　數據採集必須考慮的三者關系　23

3.1.1　訴求者：著重溝通　24

【案例1——溝通不暢導致數據分析返工】　24

3.1.2　分析者：專註分析　24

3.1.3　採集者：滿足需求　25

【案例2——我給你一個模板，簡單明確效率高】　25

3.2　數據採集獲取的3個主要來源　26

3.3　某公司盈利報告在數據採集方面的案例　26

【案例3——多部門數據採集與合並】　26

第4章　數據規範化處理方法與技巧　29

4.1　詳解數據規範化處理的目的　29

4.2　快速審核數據必備的四大方法　29

4.2.1　篩：通過數據篩選，發現數據問題　30

【案例1——篩選出不正常的類別】　30

4.2.2　查：通過索引、查找、定位，發現數據問題　31

【案例2——用VLOOKUP索引查出異常品號】　31

【案例3——用定位（Ctrl+G）查出異常價格】　32

4.2.3　排：通過對數據進行排序，判斷數據是否存在異常　33

【案例4——通過排序檢查產品的重量是否存在問題】　33

4.2.4　驗：對數據進行比對或驗證，找出數據的異常　34

【案例5——體重數據錄入錯誤，數值結果出現異常】　34

【案例6——用COUNTIF函數發現重復的數據】　34

4.3　數據規範化處理6字秘訣　35

4.3.1　6字秘訣的特點詳解　35

4.3.2　快速記住6字秘訣：右手聯想法　36

4.3.3　刪：刪掉問題數據　37

【案例7——篩選出問題數據後進行刪除】　37

【案例8——找出重復數據後進行刪除】　37

4.3.4　補：補全不完善、缺失的數據　39

【案例9——補上漏掉的打卡數據】　39

4.3.5　替：對不規範、不標準、不統一的數據進行合理替換　40

【案例10——Ctrl+H高效替換相同的數據】　40

【案例11——批處理替換無效數據為保底獎勵】　41

【案例12——批處理運算高效調整大量數值】　42

【案例13——用REPLACE函數智能替換數據】　43

4.3.6　轉：轉換數據格式，讓數據標準化　44

【案例14——為數據設置統一的格式】　44

4.3.7　拆：拆分數據，剝離出有用的部分　46

【案例15——拆分寬度固定的數據】　47

【案例16——拆分分隔符號固定的數據】　48

【案例17——通過合並操作消除數據隱患】　50

4.3.8　分：將數據分類，讓分析更加方便　51

【案例18——利用IF函數為數據分類】　51

4.4　人員信息數據綜合處理的案例　52

【案例19——處理表格中數據不準確、不標準、缺失等問題】　52

第5章　數據分析必備函數和透視技術　60

5.1　必備的數據分類函數　60

5.1.1　if函數的六大經典用法示例　60

5.1.2　iferror函數的經典用法　62

5.1.3　isna函數的經典用法　63

5.2　必備的求和與計數函數　63

5.2.1　sumif單條件求和的15種經典用法　63

5.2.2　countif單條件計數的15種經典用法　66

5.2.3　sumifs多條件求和的13種經典用法　69

5.2.4　countifs多條件計數的13種經典用法　71

5.3　必備的查找與定位函數　72

5.3.1　vlookup函數的7種經典用法　73

5.3.2　hlookup函數的基本用法　77

5.3.3　index函數的基本用法　77

5.3.4　match函數的基本用法　78

5.3.5　offset函數的基本用法　79

5.4　必備的數據透視表與切片器用法　80

5.4.1　創建數據透視表　80

5.4.2　利用透視表做數據分析　82

5.4.3　利用切片器製作分析模型　84

5.4.4　利用切片器製作可視化分析模型　86

第6章　專業數據可視化與圖表變形技術　91

6.1　數據可視化的五大尷尬事　91

6.2　數據可視化圖表變形實例講解　92

6.2.1　一張圖讓你看懂圖表元素　92

6.2.2　數據間的關系：相關性和相對性　94

6.2.3　柱形圖與變形記：對比數據分析圖表　95

6.2.4　二維柱形圖與變形記：對比數據分析圖表　100

6.2.5　堆積柱形圖與變形記：多元素數據分析圖表　106

【案例1——用瀑布圖分析數據變化過程】　113

6.2.6　柱線復合圖與變形記：多元素比較分析圖表　115

【案例2——用帕累托圖分析產品質量原因】　120

6.2.7　條形圖與變形記：排序法分析圖表　122

6.2.8　堆積條形圖與變形記：結構對比分析圖表　132

6.2.9　折線圖與變形記：趨勢分析圖表　137

6.2.10　餅圖與變形記：占比法分析圖表　141

6.2.11　散點圖和氣泡圖：相關性分析圖表　146

6.2.12　動態數據可視化分析模型原理　153

6.2.13　怎樣根據數據選擇圖表　161

第7章　9種常用數據分析方法與實戰案例　165

7.1　分析數據兩大原則　165

7.1.1　先總後分，層層拆解　165

7.1.2　由主要到次要　167

7.2　分析數據的9種常用方法　167

7.2.1　比較法：數據打包與切塊　168

【案例1——某電子公司產品利潤結構分析】　177

7.2.2　排序法：數據排序與比較　178

7.2.3　結構法：數據拆包與分類　181

【案例2——分區域各季度銷售情況分析】　182

7.2.4　階梯法：數據上樓和下樓　184

【案例3——財務利潤預實分析】　185

7.2.5　漏鬥法：數據收斂與發散　185

【案例4——某網店訂單數據分析】　187

7.2.6　關聯法：數據因果與關聯　188

【案例5——價格與重量關系分析】　189

7.2.7　雷達法：數據無關與協同　190

【案例6——如何選擇供應商】　191

7.2.8　象限法：數據定位與象限　192

【案例7——銷售量價與利潤率分析】　193

7.2.9　趨勢法：數據趨勢與預測　194

【案例8——預測未來3年銷量】　195

7.3　根據數據選擇分析方法　196

7.4　兩道思考題　196

第8章　利用工具進行高級數據分析　198

8.1　加載並打開分析工具　198

8.2　規劃求解　199

8.2.1　單變量求解　199

【案例1——帽子銷量必須達多少才能確保目標利潤】　201

8.2.2　多變量求解　202

【案例2——規劃求解計算保本點】　203

8.3　利用規劃求解求出最優解　205

【案例3——求出最大利潤的產量組合】　205

8.4　回歸分析　206

8.4.1　一元回歸　207

【案例4——降雨量與樹苗生長高度關系分析】　207

8.4.2　多元回歸　208

【案例5——某地區人口增長率與總收入、消費、人均GDP關系分析】　211

8.5　相關系數　213

【案例6——各科目考試成績相關性分析】　214

8.6　直方圖　215

【案例7——統計指定區域內銷量出現的次數】　215

8.7　指數平滑　217

【案例8——使用不同阻尼系數預測產品銷量】　217

8.8　移動平均　219

【案例9——患兒體溫檢測結果分析】　219

第9章　商務數據的分析案例與模型　222

9.1　發現問題：從備件銷售價中發現經銷商的“貓膩”　222

9.1.1　上任第 1天發現價格問題　222

9.1.2　通過排序發現兩家可疑的經銷商　222

9.1.3　為公司輓回130萬元損失　223

9.2　預測未來：數據回歸推導銷售趨勢預測模型　223

9.2.1　銷售部門做不好預測來求助　223

9.2.2　根據一元回歸的擬合度選擇預測公式　224

9.2.3　銷售部門順利過關　225

9.3　優化決策：利用回歸與相關性分析市占率與客戶滿意度的關系　225

9.3.1　市占率呈下滑趨勢卻沒有任何應對之策　225

9.3.2　用回歸分析市占率與客戶滿意度的關系　226

9.3.3　用相關系數分析如何從客戶滿意度著手提升市占率　228

9.3.4　半年後市占率得到了顯著提高　228

9.4　商務數據分析指標寶典列表　229

第　10章 財務數據的分析案例與模型　230

10.1　發現問題：巧用保本點管控企業收益率　230

10.1.1　銷售額在提升，但利潤率在下滑　230

10.1.2　原來是只顧銷售不顧結構　231

10.1.3　制定一個合理的保本點確保公司盈利　231

10.2　預測未來：利用回歸分析模型預測投資風險　232

10.2.1　朋友想接下一個1700萬元的工廠　232

10.2.2　用回歸分析模型預測10年銷售額　232

10.2.3　根據銷售額預測未來10年的利潤額　233

10.2.4　結論樂觀，決定投資　234

10.3　優化決策：通過項目凈現值優化投資　234

10.3.1　3個投資項目，老總拿不準　234

10.3.2　通過凈現值比較，結果一目瞭然　235

10.3.3　結論清晰可靠，老闆表示滿意　235

10.4　財務數據分析指標寶典列表　235

第　11章 HR數據的分析案例與模型　237

11.1　發現問題：找出高薪還要離職的真正原因　237

11.1.1　質量部離職率出現異常　237

11.1.2　新老兩頭翹，中間有斷層　237

11.1.3　調查原因後開除“老油條”　238

11.2　預測未來：通過回歸分析預測個人能力　239

11.2.1　射擊隊教練遇上三選一難題　239

11.2.2　用回歸分析預測運動員發揮水平　239

11.2.3　科學的選擇帶來最佳的效果　240

11.3　優化決策：通過相關性和回歸分析設計薪酬調整方案　240

11.3.1　因員工離職率高而欲改革薪酬體系　240

11.3.2　分析數據薪酬與能力匹配的問題　240

11.3.3　解決方案：工資總額不變，依據能力調薪　242

11.3.4　謹慎實施，效果顯著　243

11.4　HR數據分析指標寶典列表　243

11.4.1　HR管理中常用的分析方向及分析建議　243

11.4.2　常用的HR分析公式　243

第　12章 生產數據的分析案例與模型　246

12.1　發現問題：應用數據實時分析監控生產進度　246

12.1.1　交付滿足率總是出現問題　246

12.1.2　工人不願承擔換件損失　246

12.1.3　任務分解，層層統計，公開競賽　247

12.2　預測未來：應用回歸分析模型計算安全庫存　249

12.2.1　領導要求減少庫存緩解資金壓力　249

12.2.2　回歸分析模型精確估算庫存　249

12.2.3　驗證半年，領導終於放心　251

12.3　優化決策：利用數據分析最優化物流成本　252

12.3.1　3車7點的狀況　252

12.3.2　各點里程情況分析　252

12.3.3　3種不同的派送方法　253

12.3.4　最終的結果　254

12.4　生產數據分析指標寶典列表　255

第　13章 質量數據的分析案例與模型　256

13.1　發現問題：火速降低產品的投訴率　256

13.1.1　產品連續遭到客戶投訴　256

13.1.2　分析數據抓住主要問題　256

13.1.3　投訴率得到有效抑制　257

13.2　預測未來：應用回歸分析預估質量索賠率　257

13.2.1　產品連續遭到客戶投訴　258

13.2.2　回歸分析預測8年投訴率　258

13.2.3　按10%的投訴率核算索賠成本　258

13.3　優化決策：利用回歸和規劃求解計算最優質量成本　259

13.3.1　質量成本的構成是怎樣的　259

13.3.2　質量成本諸要素之間的邏輯關系　259

13.3.3　從採集的數據中求出平衡點　260

13.4　質量數據分析指標寶典列表　262

第　14章 經營管理數據的分析案例與模型　263

14.1　發現問題：多維度透析低收益原因並進行改善　263

14.1.1　總成零件收益為負，總經理發話要解決　263

14.1.2　從設計、採購與銷售3個維度進行分析　263

14.1.3　收益由負變正，總經理表示認可　266

14.2　預測未來：應用數據相關性和回歸分析做投資計劃　267

14.2.1　某行業盈利與多方因素相關　267

14.2.2　多方因素的相關性分析與回歸分析　268

14.2.3　董事會通過投資分析計劃　270

14.3　優化決策：利用數據簡單對比支撐商務決策　270

14.3.1　大客戶要求合並產品並重新定價　270

14.3.2　從雙方立場進行價格分析　271

14.3.3　與客戶協商新價格，確保雙贏　272

14.4　經營數據分析指標寶典列表　272

第　15章 構建分析模型與可視化報告　273

15.1　模型是效率之王　273

【案例1——修改表格數據，計算結果沒變化，老闆不高興】　273

【案例2——同樣的數據不同的分析模型，結果出現天壤之別】　274

15.2　何時需要建模與可視化建模　278

15.3　4種建模類型如何選擇　279

15.4　5C法構建數據分析模型　279

第　16章 利用控件定製高級動態分析模型　281

16.1　構思模型：用什麽效果來呈現　281

16.2　數據採集：確定數據並規範格式　282

16.3　公式鏈接：讓數據統計和圖形繪制同步進行　283

16.3.1　鏈接C30單元格與5個單選項控件　283

16.3.2　鏈接圖表數據源與基礎數據表　284

16.3.3　鏈接圖表數據源與KPI考核項目名稱　285

16.3.4　鏈接圖表數據源與當前累計完成率　286

16.3.5　鏈接圖表數據源與預計累計完成率　286

16.3.6　根據數據源繪制圖表　286

16.3.7　調整“累計實際”的格式　287

16.3.8　調整“累計預計”的格式　288

16.3.9　調整“當月目標”和“當月實際”的格式　288

16.3.10　調整“當月完成”率的格式　289

16.3.11　調整圖表視覺細節　290

16.4　模型檢驗：採用實際數據進行檢驗　293

16.5　安全設置：避免他人誤操作或惡意修改數據　293

16.5.1　開放允許修改的單元格區域　294

16.5.2　隱藏重要的單元格區域　294

16.5.3　執行數據表保護　295

16.6　模型拓展建議　296

第　17章 PowerBI與超級數據透視　297

17.1　2分鐘瞭解PowerBI組件　297

17.2　在Excel中加載數據分析插件　297

17.3　快速瞭解Power Query　302

17.4　快速瞭解Power Pivot　324

17.5　快速瞭解Power View　328

17.6　快速瞭解Power Map　328

第　18章 製作高質量的數據分析報告　330

18.1　口頭報告的要點　330

18.2　Excel報告的要點　331

18.1.1　邊界條件　331

18.1.2　數據結果　331

18.1.3　分析建議　332

18.3　PPT報告的要點　333

18.3.1　PPT報告的邏輯　333

18.3.2　PPT報告的誤區　333

18.4　Word報告的要點　334