職場(chǎng)力！最有效的Excel商業(yè)數(shù)據(jù)分析技法

Chapter 01 商務(wù)數(shù)據(jù)分析的基礎(chǔ)知識(shí) 1
01　開(kāi)始商務(wù)數(shù)據(jù)分析 ·····························································2
STEP 01　現(xiàn)在可以馬上進(jìn)行的商務(wù)數(shù)據(jù)分析 ····························2
02　日常業(yè)務(wù)和商務(wù)數(shù)據(jù)分析的關(guān)系 ·······································8
STEP 01　日常業(yè)務(wù)改善的流程和商務(wù)數(shù)據(jù)分析的關(guān)系 ·············8
03　商務(wù)數(shù)據(jù)分析的流程 ·························································9
STEP 01　商務(wù)數(shù)據(jù)分析的方法 ·················································9
04　商務(wù)數(shù)據(jù)分析的基本方法 ················································10
STEP 01　算出比率加以分析 ···················································10
STEP 02　繪制圖表加以分析 ··················································· 11
STEP 03　改變切入點(diǎn)加以分析 ···············································12
STEP 04　改變維度加以分析 ···················································13
Chapter 02　導(dǎo)入外部數(shù)據(jù) 15
01　將分析數(shù)據(jù)導(dǎo)入Excel ·····················································16
STEP 01　數(shù)據(jù)的導(dǎo)出與導(dǎo)入 ···················································16
02　將文本數(shù)據(jù)導(dǎo)入Excel ·····················································17
STEP 01　文本數(shù)據(jù)格式的種類(lèi) ···············································17
STEP 02　導(dǎo)入文本數(shù)據(jù) ··························································17
STEP 03　美化并以Excel 格式存儲(chǔ) ········································20
03　在Excel 中導(dǎo)入Access 的數(shù)據(jù)·······································25
STEP 01　導(dǎo)入Access 的數(shù)據(jù) ················································25
04　加上篩選條件后導(dǎo)入數(shù)據(jù) ················································28
STEP 01　加上篩選條件導(dǎo)入數(shù)據(jù) ············································28
05　直接將外部數(shù)據(jù)制作成數(shù)據(jù)透視表 ··································31
STEP 01　運(yùn)用數(shù)據(jù)透視表導(dǎo)入外部數(shù)據(jù) ·································31
06　將Web 數(shù)據(jù)導(dǎo)入Excel ···················································35
STEP 01　Web 數(shù)據(jù)的導(dǎo)入······················································35
07　將Web 數(shù)據(jù)以外部數(shù)據(jù)的形式導(dǎo)入Excel ······················37
STEP 01　以外部數(shù)據(jù)的形式導(dǎo)入Web 數(shù)據(jù) ···························37
08　練習(xí)問(wèn)題 ·········································································40
Chapter 03　數(shù)據(jù)透視表 41
01　何為數(shù)據(jù)透視表·······························································42
STEP 01　數(shù)據(jù)透視表概要 ·······················································42
STEP 02　數(shù)據(jù)表與數(shù)據(jù)透視表的構(gòu)成 ·····································44
02　制作數(shù)據(jù)透視表·······························································46

STEP 01　準(zhǔn)備數(shù)據(jù)透視表 ·······················································46
STEP 02　制作數(shù)據(jù)透視表 ·······················································49
03　更改數(shù)據(jù)透視表的布局 ····················································52
STEP 01　更改布局 ·································································52
04　篩選數(shù)據(jù)透視表的數(shù)據(jù) ····················································55
STEP 01　顯示指定的數(shù)據(jù) ·······················································55
05　制作添加篩選條件的數(shù)據(jù)透視表 ·····································57
STEP 01　添加頁(yè)字?jǐn)?shù)后根據(jù)各項(xiàng)來(lái)更改數(shù)據(jù)透視表 ···············57
06　根據(jù)報(bào)表篩選的項(xiàng)目區(qū)分工作表 ·····································60
STEP 01　制作項(xiàng)目的統(tǒng)計(jì)表 ···················································60
07　將數(shù)據(jù)透視表中的數(shù)值數(shù)據(jù)進(jìn)行排序 ······························62
STEP 01　數(shù)值的排序 ······························································62
STEP 02　將明細(xì)顯示的數(shù)值進(jìn)行排序 ·····································63
08 顯示前10 項(xiàng) ······································································64
STEP 01　顯示前10 項(xiàng) ····························································64
09　將項(xiàng)目的順序加以排序 ····················································67
STEP 01　將項(xiàng)目的順序進(jìn)行排序 ············································67
10　將數(shù)據(jù)透視表的合計(jì)值用百分比表示 ······························68
STEP 01　顯示百分比 ······························································68
STEP 02　設(shè)置基準(zhǔn)值以比較數(shù)據(jù) ············································69
11　更改總計(jì)的類(lèi)型 ·······························································71
STEP 01　更改值匯總方式 ·······················································71
12　將數(shù)據(jù)透視表的數(shù)據(jù)創(chuàng)建成組 ·········································73
STEP 01　將日期組成組 ··························································73
13　更新數(shù)據(jù)透視表的數(shù)據(jù) ····················································76
STEP 01　更新數(shù)據(jù) ·································································76
14　數(shù)據(jù)透視圖 ······································································78
STEP 01　制作數(shù)據(jù)透視圖 ·······················································78
15　數(shù)據(jù)透視表的格式化 ·······················································82
STEP 01　空白單元格中顯示“0” ··········································82
16　將數(shù)據(jù)透視表以“值”的形式顯示在別的工作表中 ·········85
STEP 01　以“值”的形式顯示在其他工作表中 ······················85
17　練習(xí)問(wèn)題 ·········································································87
Chapter 04　圖表（Excel 2010） 89
01　圖表的構(gòu)成元素·······························································90
STEP 01　圖表的構(gòu)成元素和可以設(shè)置的內(nèi)容 ··························90
02　制作圖表 ·········································································91

STEP 01　制作圖表操作 ··························································91
STEP 02　更改圖表類(lèi)型 ··························································95
03　更改圖表的布局·······························································98
STEP 01　更改圖表整體的布局 ···············································98
STEP 02　更改網(wǎng)格線單位 ·····················································100
STEP 03　更改網(wǎng)格線 ····························································101
STEP 04　在圖表內(nèi)顯示數(shù)據(jù) ·················································102
04　選擇元素設(shè)置格式 ·························································104
STEP 01　選擇元素與格式設(shè)置對(duì)話框 ···································104
05　在既有的圖表上添加數(shù)據(jù) ··············································106
STEP 01　在和圖表相同的工作表中添加數(shù)據(jù) ························106
06　在既有圖表上追加其他工作表的數(shù)據(jù) ····························109
STEP 01　添加與圖表分屬不同工作表的數(shù)據(jù) ························109
07　組合不同類(lèi)型的圖表 ····················································· 111
STEP 01　制作圖表 ································································111
08　保存自定義圖表····························································· 116
STEP 01　保存圖表為模板 ····················································· 116
STEP 02　使用已保存的圖表 ················································· 117
09　練習(xí)問(wèn)題 ·······································································122
Chapter 05　單變量求解●規(guī)劃求解●方案●運(yùn)算列表 123
01　何為單變量求解·····························································124
STEP 01　單變量求解概要 ·····················································124
STEP 02　單元格與“單變量求解”對(duì)話框的對(duì)應(yīng) ·················125
02　利用單變量求解逆運(yùn)算數(shù)值 ···········································126
STEP 01　運(yùn)用單變量求解求出目標(biāo)值的zui佳數(shù)值 ·················126
03　何為規(guī)劃求解 ································································128
STEP 01　規(guī)劃求解的概要 ·····················································128
STEP 02　單元格與“規(guī)劃求解參數(shù)”對(duì)話框的對(duì)應(yīng) ·············130
04　添加規(guī)劃求解功能 ·························································132
STEP 01　安裝規(guī)劃求解 ························································132
05　用規(guī)劃求解求出zui佳解答 ··············································134
STEP 01　明確列出規(guī)劃求解的目的與限制條件 ····················134
STEP 02　在規(guī)劃求解中設(shè)置參數(shù)后求解 ·······························135
06　何為方案 ·······································································140
STEP 01　方案概要 ·······························································140
STEP 02　“添加方案”與“方案變量值”對(duì)話框 ·················141
STEP 03　“方案管理器”對(duì)話框 ··········································142

07　創(chuàng)建方案 ·······································································143
STEP 01　方案的創(chuàng)建 ····························································143
08　執(zhí)行方案 ·······································································146
STEP 01　方案的執(zhí)行 ····························································146
09　綜合顯示方案的結(jié)果 ·····················································147
STEP 01　將多個(gè)方案綜合顯示 ·············································147
10　表格（Excel 2010）······················································149
STEP 01　轉(zhuǎn)換成表格 ····························································149
STEP 02　在表格中添加合計(jì)行 ·············································153
STEP 03　在表格中輸入計(jì)算式 ·············································154
11　練習(xí)問(wèn)題 ········································································158
Chapter 06　關(guān)于銷(xiāo)售的商業(yè)數(shù)據(jù)分析 159
01　運(yùn)用Z 圖表來(lái)分析銷(xiāo)售額變化的傾向 ····························160
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································163
STEP 02　Excel 操作①：算出營(yíng)業(yè)額累計(jì)和移動(dòng)年計(jì) ··········164
STEP 03　Excel 操作②：制作Z 圖表 ···································165
STEP 04　解讀結(jié)果 ·······························································168
02　運(yùn)用ABC 分析來(lái)弄清重點(diǎn)管理項(xiàng)目 ······························169
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································172
STEP 02　Excel 操作①：銷(xiāo)售額的排序和算出銷(xiāo)售
實(shí)際成果的合計(jì)值 ·················································172
STEP 03　Excel 操作②：計(jì)算銷(xiāo)售構(gòu)成比率和構(gòu)成
比率的累計(jì) ····························································175
STEP 04　Excel 操作③：顯示劃分的等級(jí)順位 ·····················176
STEP 05　Excel 操作④：制作柏拉圖 ····································178
STEP 06　Excel 操作⑤：顯示ABC 的劃分等級(jí)順位 ············181
STEP 07　解讀結(jié)果 ·······························································184
03　提早預(yù)測(cè)并選定能增加利潤(rùn)的商品 ································187
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································189
STEP 02　Excel 操作①：輸入運(yùn)算公式 ································189
STEP 03　Excel 操作②：交叉比率的排序 ····························191
STEP 04　Excel 操作③：制作氣泡圖 ····································191
STEP 05　Excel 操作④：編輯圖表 ·······································199
STEP 06　解讀結(jié)果 ·······························································203
04　預(yù)測(cè)暢銷(xiāo)商品的銷(xiāo)售數(shù)量 ··············································205
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································206
STEP 02　Excel 操作①：制作變化傾向圖 ····························207

STEP 03　Excel 操作②：添加趨勢(shì)線 ····································209
STEP 04　Excel 操作③：算出銷(xiāo)售數(shù)量的預(yù)測(cè) ·····················213
STEP 05　解讀結(jié)果 ·······························································215
05　選定拍賣(mài)商品 ································································217
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································220
STEP 02　Excel 操作①：制作需求曲線 ································220
STEP 03　Excel 操作②：算出基準(zhǔn)價(jià)格的預(yù)測(cè)銷(xiāo)售數(shù)量 ·······229
STEP 04　Excel 操作③：算出價(jià)格彈性 ································231
STEP 05　解讀結(jié)果 ·······························································233
06　練習(xí)問(wèn)題 ·······································································236
Chapter 07　關(guān)于企劃的商業(yè)數(shù)據(jù)分析 239
01　確立商品的市場(chǎng)定位 ·····················································240
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································242
STEP 02　Excel 操作①：輸入計(jì)算式 ····································243
STEP 03　Excel 操作②：制作氣泡圖 ····································243
STEP 04　Excel 操作③：編輯圖表 ·······································250
STEP 05　Excel 操作④：在圖表中標(biāo)出商品名稱(chēng) ··················253
STEP 06　分割成4 個(gè)領(lǐng)域 ····················································254
STEP 07　解讀結(jié)果 ·······························································256
02　從氣象數(shù)據(jù)預(yù)測(cè)夏季的銷(xiāo)售數(shù)量 ···································258
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································262
STEP 02　Excel 操作①：算出相關(guān)系數(shù)與制作散點(diǎn)圖 ··········262
STEP 03　解讀結(jié)果① ····························································267
STEP 04　Excel 操作②：添加回歸直線 ································267
STEP 05　Excel 操作③：算出預(yù)測(cè)值 ····································269
STEP 06　解讀結(jié)果② ····························································271
03　求出新開(kāi)店鋪的有效賣(mài)場(chǎng)面積 ·······································273
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································275
STEP 02　Excel 操作①：求出商圈人口、賣(mài)場(chǎng)面積以及
銷(xiāo)售額的關(guān)系 ························································275
STEP 03　解讀結(jié)果① ····························································277
STEP 04　Excel 操作②：在新店鋪用地的商圈人口附近
加上趨勢(shì)線 ····························································278
STEP 05　解讀結(jié)果② ····························································281
STEP 06　Excel 操作③：根據(jù)商圈人口算出預(yù)測(cè)銷(xiāo)售額 ·······282
STEP 07　Excel 操作④：根據(jù)TREND 函數(shù)算出預(yù)測(cè)銷(xiāo)
售額·······································································282
目錄.indd 5 2016-1-12 16:53:59
CONTENTS
6
STEP 08　Excel 操作⑤：使用單變量求解算出賣(mài)場(chǎng)面積 ·······283
STEP 09　解讀結(jié)果③ ····························································285
04　運(yùn)用雷達(dá)圖來(lái)分析商品的強(qiáng)項(xiàng)與弱項(xiàng) ····························287
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································289
STEP 02　Excel 操作①：制作雷達(dá)圖 ····································289
STEP 03　Excel 操作②：編輯圖表 ·······································290
STEP 04　解讀結(jié)果 ·······························································293
05　判定對(duì)提升利潤(rùn)有貢獻(xiàn)的方法 ·······································296
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································303
STEP 02　Excel 操作①：掌握現(xiàn)狀 ·······································303
STEP 03　解讀結(jié)果① ····························································310
STEP 04　Excel 操作②：模擬的準(zhǔn)備 ····································310
STEP 05　Excel 操作③：改善方案的分析 ····························315
STEP 06　Excel 操作④：制作方案 ·······································316
STEP 07　解讀結(jié)果② ····························································320
06　練習(xí)問(wèn)題 ·······································································323
Chapter 08　關(guān)于生產(chǎn)的商業(yè)數(shù)據(jù)分析 325
01　分析勞動(dòng)生產(chǎn)力以確切指出原因 ···································326
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································328
STEP 02　Excel 操作①：算出勞動(dòng)生產(chǎn)力及其3 個(gè)指標(biāo) ·······329
STEP 03　Excel 操作②：比較去年和今年 ····························333
STEP 04　解讀結(jié)果 ·······························································335
02　導(dǎo)出產(chǎn)生zui大利潤(rùn)的產(chǎn)量 ··············································337
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································339
STEP 02　Excel 操作①：目的與生產(chǎn)條件的公式化 ··············340
STEP 03　Excel 操作②：規(guī)劃求解的設(shè)置與操作 ··················342
STEP 04　解讀結(jié)果① ····························································347
STEP 05　Excel 操作③：產(chǎn)品數(shù)據(jù)的更改與規(guī)劃求解
的操作 ···································································347
STEP 06　Excel 操作④：運(yùn)算結(jié)果報(bào)表的編輯 ·····················349
STEP 07　解讀結(jié)果② ····························································350
03　求出zui佳的訂貨點(diǎn) ·························································352
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································357
STEP 02　Excel 操作①：算出研制周期中的平均銷(xiāo)售數(shù)量 ···358
STEP 03　Excel 操作②：算出安全庫(kù)存 ································359
STEP 04　Excel 操作③：算出訂貨點(diǎn)與訂貨量 ·····················360
STEP 05　解讀結(jié)果 ·······························································361
目錄.indd 6 2016-1-12 16:53:59
7
CONTENTS
04　練習(xí)問(wèn)題 ·······································································363
Chapter 09　關(guān)于會(huì)計(jì)、財(cái)務(wù)的商業(yè)數(shù)據(jù)分析 365
01　根據(jù)客戶的業(yè)績(jī)?cè)O(shè)置信用范圍（交易限額） ·················366
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································370
STEP 02　Excel 操作①：求出信用范圍的基準(zhǔn)金額，
準(zhǔn)備評(píng)價(jià)表 ····························································371
STEP 03　Excel 操作②：求出流動(dòng)比率以及自有資本比率 ···372
STEP 04　Excel 操作③：根據(jù)流動(dòng)比率與自有資本比率
設(shè)置信用范圍 ························································373
STEP 05　Excel 操作④：信用范圍的設(shè)置 ····························376
STEP 06　解讀結(jié)果 ·······························································377
02　決定購(gòu)入或是租借 ·························································379
STEP 01　需準(zhǔn)備的商務(wù)數(shù)據(jù) ·················································384
STEP 02　Excel 操作①：準(zhǔn)備資金流動(dòng)表 ····························384
STEP 03　Excel 操作②：求出凈現(xiàn)值 ····································387
STEP 04　解讀結(jié)果 ·······························································390
03　練習(xí)問(wèn)題 ·······································································393
附錄　圖表（Excel 2003）（見(jiàn)二維碼）
01　整理圖表的格式
STEP 01　圖表的構(gòu)成元素和可以設(shè)置的內(nèi)容
02　制作圖表（Excel 2003）
STEP 01　制作圖表
03　更改圖表類(lèi)型
STEP 01　更改圖表的類(lèi)型
04　選擇元素設(shè)置格式
STEP 01　選擇元素與格式設(shè)置對(duì)話框
05　在既有的圖表上添加數(shù)據(jù)
STEP 01　在和圖表相同的工作表中添加數(shù)據(jù)
STEP 02　在既有的圖表上添加其他工作表的數(shù)據(jù)
06　組合不同類(lèi)型的圖表
STEP 01　制作圖表
07　保存自定義圖表
STEP 01　保存圖表
STEP 02　使用已保存的圖表
08　練習(xí)問(wèn)題
目錄.indd 7 2016-1-12 16:53:59
目錄.indd 8 2016-1-12 16:53:59