Excel數(shù)據(jù)分析方法、技術(shù)與案例

第 1章 數(shù)據(jù)分析的核心目的與常見誤區(qū) 1
1．1　數(shù)據(jù)分析的三大核心目的　1
1．1．1　發(fā)現(xiàn)問題　1
【案例1——超標(biāo)開支都到哪里去了】　1
【案例2——哪些材料是控制和改善的重點(diǎn)】　2
1．1．2　預(yù)測(cè)未來　4
【案例3——明年銷售預(yù)測(cè)如何做】　4
【案例4——哪個(gè)價(jià)格更合適】　4
1．1．3　優(yōu)化決策　5
【案例5——哪個(gè)價(jià)位適合建立庫存】　5
【案例6——哪個(gè)候選人更適合】　6
1．2　數(shù)據(jù)分析的四大誤區(qū)　6
1．2．1　什么都要分析——貪多　7
【案例7——多多益善的分析結(jié)果，領(lǐng)導(dǎo)一定喜歡嗎】　7
1．2．2　幾個(gè)數(shù)據(jù)想分析出“一朵花”——做夢(mèng)　7
【案例8——想用3個(gè)月數(shù)據(jù)預(yù)測(cè)全年情況】　7
1．2．3　數(shù)據(jù)量大不知道分析啥——抓瞎　8
【案例9——一句話的任務(wù)】　8
1．2．4　不會(huì)可視化圖表——暈菜　8
【案例10——怎樣發(fā)現(xiàn)哪些產(chǎn)品是明星產(chǎn)品】　9
【案例11——哪些價(jià)格是不合理的】　9
1．2．5　機(jī)智地回答領(lǐng)導(dǎo)的“靈魂三問”　10

第　2章 數(shù)據(jù)分析流程與分析元素　12
2．1　數(shù)據(jù)分析流程的五大步驟　12
2．1．1　分析設(shè)計(jì)：先思考，再動(dòng)手　12
2．2．2　數(shù)據(jù)采集：盡量確保原始數(shù)據(jù)的規(guī)范性　12
2．2．3　數(shù)據(jù)整理：規(guī)范化并消除錯(cuò)誤　13
2．2．4　數(shù)據(jù)分析：4個(gè)小步驟　13
2．2．5　數(shù)據(jù)報(bào)告：圍繞說服力進(jìn)行撰寫　13
2．2　“一句話的任務(wù)”案例　14
【案例——一句話的任務(wù)，我該怎么辦】　14
2．3　分析的5個(gè)元素：量?jī)r(jià)額差率及其組合　16
2．3．1　1分鐘了解五大元素　16
2．3．2　單元素分析　17
【案例1——產(chǎn)品1～8月銷售量分析】　17
【案例2——產(chǎn)品全年銷售額】　17
【案例3——貿(mào)易增長(zhǎng)率分析】　18
2．3．3　雙元素分析　18
【案例4——對(duì)產(chǎn)品銷量進(jìn)行“量量”分析】　18
【案例5——網(wǎng)店對(duì)當(dāng)周銷量進(jìn)行“量?jī)r(jià)”分析】　19
【案例6——機(jī)械廠對(duì)產(chǎn)品進(jìn)行“量率”分析】　19
2．3．4　三元素分析　20
【案例7——某公司為考察銷售目標(biāo)完成度進(jìn)行“量量率”分析】　20
2．3．5　五大元素與其他元素的組合分析　21
2．3．6　確定分析維度的3種原則　21

第3章　數(shù)據(jù)采集：快速獲取有價(jià)值的數(shù)據(jù)　23
3．1　數(shù)據(jù)采集必須考慮的三者關(guān)系　23
3．1．1　訴求者：著重溝通　24
【案例1——溝通不暢導(dǎo)致數(shù)據(jù)分析返工】　24
3．1．2　分析者：專注分析　24
3．1．3　采集者：滿足需求　25
【案例2——我給你一個(gè)模板，簡(jiǎn)單明確效率高】　25
3．2　數(shù)據(jù)采集獲取的3個(gè)主要來源　26
3．3　某公司盈利報(bào)告在數(shù)據(jù)采集方面的案例　26
【案例3——多部門數(shù)據(jù)采集與合并】　26

第4章　數(shù)據(jù)規(guī)范化處理方法與技巧　29
4．1　詳解數(shù)據(jù)規(guī)范化處理的目的　29
4．2　快速審核數(shù)據(jù)必備的四大方法　29
4．2．1　篩：通過數(shù)據(jù)篩選，發(fā)現(xiàn)數(shù)據(jù)問題　30
【案例1——篩選出不正常的類別】　30
4．2．2　查：通過索引、查找、定位，發(fā)現(xiàn)數(shù)據(jù)問題　31
【案例2——用VLOOKUP索引查出異常品號(hào)】　31
【案例3——用定位（Ctrl+G）查出異常價(jià)格】　32
4．2．3　排：通過對(duì)數(shù)據(jù)進(jìn)行排序，判斷數(shù)據(jù)是否存在異常　33
【案例4——通過排序檢查產(chǎn)品的重量是否存在問題】　33
4．2．4　驗(yàn)：對(duì)數(shù)據(jù)進(jìn)行比對(duì)或驗(yàn)證，找出數(shù)據(jù)的異?！?4
【案例5——體重?cái)?shù)據(jù)錄入錯(cuò)誤，數(shù)值結(jié)果出現(xiàn)異?！俊?4
【案例6——用COUNTIF函數(shù)發(fā)現(xiàn)重復(fù)的數(shù)據(jù)】　34
4．3　數(shù)據(jù)規(guī)范化處理6字秘訣　35
4．3．1　6字秘訣的特點(diǎn)詳解　35
4．3．2　快速記住6字秘訣：右手聯(lián)想法　36
4．3．3　刪：刪掉問題數(shù)據(jù)　37
【案例7——篩選出問題數(shù)據(jù)后進(jìn)行刪除】　37
【案例8——找出重復(fù)數(shù)據(jù)后進(jìn)行刪除】　37
4．3．4　補(bǔ)：補(bǔ)全不完善、缺失的數(shù)據(jù)　39
【案例9——補(bǔ)上漏掉的打卡數(shù)據(jù)】　39
4．3．5　替：對(duì)不規(guī)范、不標(biāo)準(zhǔn)、不統(tǒng)一的數(shù)據(jù)進(jìn)行合理替換　40
【案例10——Ctrl+H高效替換相同的數(shù)據(jù)】　40
【案例11——批處理替換無效數(shù)據(jù)為保底獎(jiǎng)勵(lì)】　41
【案例12——批處理運(yùn)算高效調(diào)整大量數(shù)值】　42
【案例13——用REPLACE函數(shù)智能替換數(shù)據(jù)】　43
4．3．6　轉(zhuǎn)：轉(zhuǎn)換數(shù)據(jù)格式，讓數(shù)據(jù)標(biāo)準(zhǔn)化　44
【案例14——為數(shù)據(jù)設(shè)置統(tǒng)一的格式】　44
4．3．7　拆：拆分?jǐn)?shù)據(jù)，剝離出有用的部分　46
【案例15——拆分寬度固定的數(shù)據(jù)】　47
【案例16——拆分分隔符號(hào)固定的數(shù)據(jù)】　48
【案例17——通過合并操作消除數(shù)據(jù)隱患】　50
4．3．8　分：將數(shù)據(jù)分類，讓分析更加方便　51
【案例18——利用IF函數(shù)為數(shù)據(jù)分類】　51
4．4　人員信息數(shù)據(jù)綜合處理的案例　52
【案例19——處理表格中數(shù)據(jù)不準(zhǔn)確、不標(biāo)準(zhǔn)、缺失等問題】　52

第5章　數(shù)據(jù)分析必備函數(shù)和透視技術(shù)　60
5．1　必備的數(shù)據(jù)分類函數(shù)　60
5．1．1　if函數(shù)的六大經(jīng)典用法示例　60
5．1．2　iferror函數(shù)的經(jīng)典用法　62
5．1．3　isna函數(shù)的經(jīng)典用法　63
5．2　必備的求和與計(jì)數(shù)函數(shù)　63
5．2．1　sumif單條件求和的15種經(jīng)典用法　63
5．2．2　countif單條件計(jì)數(shù)的15種經(jīng)典用法　66
5．2．3　sumifs多條件求和的13種經(jīng)典用法　69
5．2．4　countifs多條件計(jì)數(shù)的13種經(jīng)典用法　71
5．3　必備的查找與定位函數(shù)　72
5．3．1　vlookup函數(shù)的7種經(jīng)典用法　73
5．3．2　hlookup函數(shù)的基本用法　77
5．3．3　index函數(shù)的基本用法　77
5．3．4　match函數(shù)的基本用法　78
5．3．5　offset函數(shù)的基本用法　79
5．4　必備的數(shù)據(jù)透視表與切片器用法　80
5．4．1　創(chuàng)建數(shù)據(jù)透視表　80
5．4．2　利用透視表做數(shù)據(jù)分析　82
5．4．3　利用切片器制作分析模型　84
5．4．4　利用切片器制作可視化分析模型　86

第6章　專業(yè)數(shù)據(jù)可視化與圖表變形技術(shù)　91
6．1　數(shù)據(jù)可視化的五大尷尬事　91
6．2　數(shù)據(jù)可視化圖表變形實(shí)例講解　92
6．2．1　一張圖讓你看懂圖表元素　92
6．2．2　數(shù)據(jù)間的關(guān)系：相關(guān)性和相對(duì)性　94
6．2．3　柱形圖與變形記：對(duì)比數(shù)據(jù)分析圖表　95
6．2．4　二維柱形圖與變形記：對(duì)比數(shù)據(jù)分析圖表　100
6．2．5　堆積柱形圖與變形記：多元素?cái)?shù)據(jù)分析圖表　106
【案例1——用瀑布圖分析數(shù)據(jù)變化過程】　113
6．2．6　柱線復(fù)合圖與變形記：多元素比較分析圖表　115
【案例2——用帕累托圖分析產(chǎn)品質(zhì)量原因】　120
6．2．7　條形圖與變形記：排序法分析圖表　122
6．2．8　堆積條形圖與變形記：結(jié)構(gòu)對(duì)比分析圖表　132
6．2．9　折線圖與變形記：趨勢(shì)分析圖表　137
6．2．10　餅圖與變形記：占比法分析圖表　141
6．2．11　散點(diǎn)圖和氣泡圖：相關(guān)性分析圖表　146
6．2．12　動(dòng)態(tài)數(shù)據(jù)可視化分析模型原理　153
6．2．13　怎樣根據(jù)數(shù)據(jù)選擇圖表　161

第7章　9種常用數(shù)據(jù)分析方法與實(shí)戰(zhàn)案例　165
7．1　分析數(shù)據(jù)兩大原則　165
7．1．1　先總后分，層層拆解　165
7．1．2　由主要到次要　167
7．2　分析數(shù)據(jù)的9種常用方法　167
7．2．1　比較法：數(shù)據(jù)打包與切塊　168
【案例1——某電子公司產(chǎn)品利潤(rùn)結(jié)構(gòu)分析】　177
7．2．2　排序法：數(shù)據(jù)排序與比較　178
7．2．3　結(jié)構(gòu)法：數(shù)據(jù)拆包與分類　181
【案例2——分區(qū)域各季度銷售情況分析】　182
7．2．4　階梯法：數(shù)據(jù)上樓和下樓　184
【案例3——財(cái)務(wù)利潤(rùn)預(yù)實(shí)分析】　185
7．2．5　漏斗法：數(shù)據(jù)收斂與發(fā)散　185
【案例4——某網(wǎng)店訂單數(shù)據(jù)分析】　187
7．2．6　關(guān)聯(lián)法：數(shù)據(jù)因果與關(guān)聯(lián)　188
【案例5——價(jià)格與重量關(guān)系分析】　189
7．2．7　雷達(dá)法：數(shù)據(jù)無關(guān)與協(xié)同　190
【案例6——如何選擇供應(yīng)商】　191
7．2．8　象限法：數(shù)據(jù)定位與象限　192
【案例7——銷售量?jī)r(jià)與利潤(rùn)率分析】　193
7．2．9　趨勢(shì)法：數(shù)據(jù)趨勢(shì)與預(yù)測(cè)　194
【案例8——預(yù)測(cè)未來3年銷量】　195
7．3　根據(jù)數(shù)據(jù)選擇分析方法　196
7．4　兩道思考題　196

第8章　利用工具進(jìn)行高級(jí)數(shù)據(jù)分析　198
8．1　加載并打開分析工具　198
8．2　規(guī)劃求解　199
8．2．1　單變量求解　199
【案例1——帽子銷量必須達(dá)多少才能確保目標(biāo)利潤(rùn)】　201
8．2．2　多變量求解　202
【案例2——規(guī)劃求解計(jì)算保本點(diǎn)】　203
8．3　利用規(guī)劃求解求出最優(yōu)解　205
【案例3——求出最大利潤(rùn)的產(chǎn)量組合】　205
8．4　回歸分析　206
8．4．1　一元回歸　207
【案例4——降雨量與樹苗生長(zhǎng)高度關(guān)系分析】　207
8．4．2　多元回歸　208
【案例5——某地區(qū)人口增長(zhǎng)率與總收入、消費(fèi)、人均GDP關(guān)系分析】　211
8．5　相關(guān)系數(shù)　213
【案例6——各科目考試成績(jī)相關(guān)性分析】　214
8．6　直方圖　215
【案例7——統(tǒng)計(jì)指定區(qū)域內(nèi)銷量出現(xiàn)的次數(shù)】　215
8．7　指數(shù)平滑　217
【案例8——使用不同阻尼系數(shù)預(yù)測(cè)產(chǎn)品銷量】　217
8．8　移動(dòng)平均　219
【案例9——患兒體溫檢測(cè)結(jié)果分析】　219

第9章　商務(wù)數(shù)據(jù)的分析案例與模型　222
9．1　發(fā)現(xiàn)問題：從備件銷售價(jià)中發(fā)現(xiàn)經(jīng)銷商的“貓膩”　222
9．1．1　上任第 1天發(fā)現(xiàn)價(jià)格問題　222
9．1．2　通過排序發(fā)現(xiàn)兩家可疑的經(jīng)銷商　222
9．1．3　為公司挽回130萬元損失　223
9．2　預(yù)測(cè)未來：數(shù)據(jù)回歸推導(dǎo)銷售趨勢(shì)預(yù)測(cè)模型　223
9．2．1　銷售部門做不好預(yù)測(cè)來求助　223
9．2．2　根據(jù)一元回歸的擬合度選擇預(yù)測(cè)公式　224
9．2．3　銷售部門順利過關(guān)　225
9．3　優(yōu)化決策：利用回歸與相關(guān)性分析市占率與客戶滿意度的關(guān)系　225
9．3．1　市占率呈下滑趨勢(shì)卻沒有任何應(yīng)對(duì)之策　225
9．3．2　用回歸分析市占率與客戶滿意度的關(guān)系　226
9．3．3　用相關(guān)系數(shù)分析如何從客戶滿意度著手提升市占率　228
9．3．4　半年后市占率得到了顯著提高　228
9．4　商務(wù)數(shù)據(jù)分析指標(biāo)寶典列表　229

第　10章 財(cái)務(wù)數(shù)據(jù)的分析案例與模型　230
10．1　發(fā)現(xiàn)問題：巧用保本點(diǎn)管控企業(yè)收益率　230
10．1．1　銷售額在提升，但利潤(rùn)率在下滑　230
10．1．2　原來是只顧銷售不顧結(jié)構(gòu)　231
10．1．3　制定一個(gè)合理的保本點(diǎn)確保公司盈利　231
10．2　預(yù)測(cè)未來：利用回歸分析模型預(yù)測(cè)投資風(fēng)險(xiǎn)　232
10．2．1　朋友想接下一個(gè)1700萬元的工廠　232
10．2．2　用回歸分析模型預(yù)測(cè)10年銷售額　232
10．2．3　根據(jù)銷售額預(yù)測(cè)未來10年的利潤(rùn)額　233
10．2．4　結(jié)論樂觀，決定投資　234
10．3　優(yōu)化決策：通過項(xiàng)目?jī)衄F(xiàn)值優(yōu)化投資　234
10．3．1　3個(gè)投資項(xiàng)目，老總拿不準(zhǔn)　234
10．3．2　通過凈現(xiàn)值比較，結(jié)果一目了然　235
10．3．3　結(jié)論清晰可靠，老板表示滿意　235
10．4　財(cái)務(wù)數(shù)據(jù)分析指標(biāo)寶典列表　235

第　11章 HR數(shù)據(jù)的分析案例與模型　237
11．1　發(fā)現(xiàn)問題：找出高薪還要離職的真正原因　237
11．1．1　質(zhì)量部離職率出現(xiàn)異?！?37
11．1．2　新老兩頭翹，中間有斷層　237
11．1．3　調(diào)查原因后開除“老油條”　238
11．2　預(yù)測(cè)未來：通過回歸分析預(yù)測(cè)個(gè)人能力　239
11．2．1　射擊隊(duì)教練遇上三選一難題　239
11．2．2　用回歸分析預(yù)測(cè)運(yùn)動(dòng)員發(fā)揮水平　239
11．2．3　科學(xué)的選擇帶來最佳的效果　240
11．3　優(yōu)化決策：通過相關(guān)性和回歸分析設(shè)計(jì)薪酬調(diào)整方案　240
11．3．1　因員工離職率高而欲改革薪酬體系　240
11．3．2　分析數(shù)據(jù)薪酬與能力匹配的問題　240
11．3．3　解決方案：工資總額不變，依據(jù)能力調(diào)薪　242
11．3．4　謹(jǐn)慎實(shí)施，效果顯著　243
11．4　HR數(shù)據(jù)分析指標(biāo)寶典列表　243
11．4．1　HR管理中常用的分析方向及分析建議　243
11．4．2　常用的HR分析公式　243

第　12章 生產(chǎn)數(shù)據(jù)的分析案例與模型　246
12．1　發(fā)現(xiàn)問題：應(yīng)用數(shù)據(jù)實(shí)時(shí)分析監(jiān)控生產(chǎn)進(jìn)度　246
12．1．1　交付滿足率總是出現(xiàn)問題　246
12．1．2　工人不愿承擔(dān)換件損失　246
12．1．3　任務(wù)分解，層層統(tǒng)計(jì)，公開競(jìng)賽　247
12．2　預(yù)測(cè)未來：應(yīng)用回歸分析模型計(jì)算安全庫存　249
12．2．1　領(lǐng)導(dǎo)要求減少庫存緩解資金壓力　249
12．2．2　回歸分析模型精確估算庫存　249
12．2．3　驗(yàn)證半年，領(lǐng)導(dǎo)終于放心　251
12．3　優(yōu)化決策：利用數(shù)據(jù)分析最優(yōu)化物流成本　252
12．3．1　3車7點(diǎn)的狀況　252
12．3．2　各點(diǎn)里程情況分析　252
12．3．3　3種不同的派送方法　253
12．3．4　最終的結(jié)果　254
12．4　生產(chǎn)數(shù)據(jù)分析指標(biāo)寶典列表　255

第　13章 質(zhì)量數(shù)據(jù)的分析案例與模型　256
13．1　發(fā)現(xiàn)問題：火速降低產(chǎn)品的投訴率　256
13．1．1　產(chǎn)品連續(xù)遭到客戶投訴　256
13．1．2　分析數(shù)據(jù)抓住主要問題　256
13．1．3　投訴率得到有效抑制　257
13．2　預(yù)測(cè)未來：應(yīng)用回歸分析預(yù)估質(zhì)量索賠率　257
13．2．1　產(chǎn)品連續(xù)遭到客戶投訴　258
13．2．2　回歸分析預(yù)測(cè)8年投訴率　258
13．2．3　按10%的投訴率核算索賠成本　258
13．3　優(yōu)化決策：利用回歸和規(guī)劃求解計(jì)算最優(yōu)質(zhì)量成本　259
13．3．1　質(zhì)量成本的構(gòu)成是怎樣的　259
13．3．2　質(zhì)量成本諸要素之間的邏輯關(guān)系　259
13．3．3　從采集的數(shù)據(jù)中求出平衡點(diǎn)　260
13．4　質(zhì)量數(shù)據(jù)分析指標(biāo)寶典列表　262

第　14章 經(jīng)營(yíng)管理數(shù)據(jù)的分析案例與模型　263
14．1　發(fā)現(xiàn)問題：多維度透析低收益原因并進(jìn)行改善　263
14．1．1　總成零件收益為負(fù)，總經(jīng)理發(fā)話要解決　263
14．1．2　從設(shè)計(jì)、采購與銷售3個(gè)維度進(jìn)行分析　263
14．1．3　收益由負(fù)變正，總經(jīng)理表示認(rèn)可　266
14．2　預(yù)測(cè)未來：應(yīng)用數(shù)據(jù)相關(guān)性和回歸分析做投資計(jì)劃　267
14．2．1　某行業(yè)盈利與多方因素相關(guān)　267
14．2．2　多方因素的相關(guān)性分析與回歸分析　268
14．2．3　董事會(huì)通過投資分析計(jì)劃　270
14．3　優(yōu)化決策：利用數(shù)據(jù)簡(jiǎn)單對(duì)比支撐商務(wù)決策　270
14．3．1　大客戶要求合并產(chǎn)品并重新定價(jià)　270
14．3．2　從雙方立場(chǎng)進(jìn)行價(jià)格分析　271
14．3．3　與客戶協(xié)商新價(jià)格，確保雙贏　272
14．4　經(jīng)營(yíng)數(shù)據(jù)分析指標(biāo)寶典列表　272

第　15章 構(gòu)建分析模型與可視化報(bào)告　273
15．1　模型是效率之王　273
【案例1——修改表格數(shù)據(jù)，計(jì)算結(jié)果沒變化，老板不高興】　273
【案例2——同樣的數(shù)據(jù)不同的分析模型，結(jié)果出現(xiàn)天壤之別】　274
15．2　何時(shí)需要建模與可視化建?！?78
15．3　4種建模類型如何選擇　279
15．4　5C法構(gòu)建數(shù)據(jù)分析模型　279

第　16章 利用控件定制高級(jí)動(dòng)態(tài)分析模型　281
16．1　構(gòu)思模型：用什么效果來呈現(xiàn)　281
16．2　數(shù)據(jù)采集：確定數(shù)據(jù)并規(guī)范格式　282
16．3　公式鏈接：讓數(shù)據(jù)統(tǒng)計(jì)和圖形繪制同步進(jìn)行　283
16．3．1　鏈接C30單元格與5個(gè)單選項(xiàng)控件　283
16．3．2　鏈接圖表數(shù)據(jù)源與基礎(chǔ)數(shù)據(jù)表　284
16．3．3　鏈接圖表數(shù)據(jù)源與KPI考核項(xiàng)目名稱　285
16．3．4　鏈接圖表數(shù)據(jù)源與當(dāng)前累計(jì)完成率　286
16．3．5　鏈接圖表數(shù)據(jù)源與預(yù)計(jì)累計(jì)完成率　286
16．3．6　根據(jù)數(shù)據(jù)源繪制圖表　286
16．3．7　調(diào)整“累計(jì)實(shí)際”的格式　287
16．3．8　調(diào)整“累計(jì)預(yù)計(jì)”的格式　288
16．3．9　調(diào)整“當(dāng)月目標(biāo)”和“當(dāng)月實(shí)際”的格式　288
16．3．10　調(diào)整“當(dāng)月完成”率的格式　289
16．3．11　調(diào)整圖表視覺細(xì)節(jié)　290
16．4　模型檢驗(yàn)：采用實(shí)際數(shù)據(jù)進(jìn)行檢驗(yàn)　293
16．5　安全設(shè)置：避免他人誤操作或惡意修改數(shù)據(jù)　293
16．5．1　開放允許修改的單元格區(qū)域　294
16．5．2　隱藏重要的單元格區(qū)域　294
16．5．3　執(zhí)行數(shù)據(jù)表保護(hù)　295
16．6　模型拓展建議　296

第　17章 PowerBI與超級(jí)數(shù)據(jù)透視　297
17．1　2分鐘了解PowerBI組件　297
17．2　在Excel中加載數(shù)據(jù)分析插件　297
17．3　快速了解Power Query　302
17．4　快速了解Power Pivot　324
17．5　快速了解Power View　328
17．6　快速了解Power Map　328

第　18章 制作高質(zhì)量的數(shù)據(jù)分析報(bào)告　330
18．1　口頭報(bào)告的要點(diǎn)　330
18．2　Excel報(bào)告的要點(diǎn)　331
18．1．1　邊界條件　331
18．1．2　數(shù)據(jù)結(jié)果　331
18．1．3　分析建議　332
18．3　PPT報(bào)告的要點(diǎn)　333
18．3．1　PPT報(bào)告的邏輯　333
18．3．2　PPT報(bào)告的誤區(qū)　333
18．4　Word報(bào)告的要點(diǎn)　334