Chapter 01 商務數(shù)據分析的基礎知識 1
01 開始商務數(shù)據分析 ·····························································2
STEP 01 現(xiàn)在可以馬上進行的商務數(shù)據分析 ····························2
02 日常業(yè)務和商務數(shù)據分析的關系 ·······································8
STEP 01 日常業(yè)務改善的流程和商務數(shù)據分析的關系 ·············8
03 商務數(shù)據分析的流程 ·························································9
STEP 01 商務數(shù)據分析的方法 ·················································9
04 商務數(shù)據分析的基本方法 ················································10
STEP 01 算出比率加以分析 ···················································10
STEP 02 繪制圖表加以分析 ··················································· 11
STEP 03 改變切入點加以分析 ···············································12
STEP 04 改變維度加以分析 ···················································13
Chapter 02 導入外部數(shù)據 15
01 將分析數(shù)據導入Excel ·····················································16
STEP 01 數(shù)據的導出與導入 ···················································16
02 將文本數(shù)據導入Excel ·····················································17
STEP 01 文本數(shù)據格式的種類 ···············································17
STEP 02 導入文本數(shù)據 ··························································17
STEP 03 美化并以Excel 格式存儲 ········································20
03 在Excel 中導入Access 的數(shù)據·······································25
STEP 01 導入Access 的數(shù)據 ················································25
04 加上篩選條件后導入數(shù)據 ················································28
STEP 01 加上篩選條件導入數(shù)據 ············································28
05 直接將外部數(shù)據制作成數(shù)據透視表 ··································31
STEP 01 運用數(shù)據透視表導入外部數(shù)據 ·································31
06 將Web 數(shù)據導入Excel ···················································35
STEP 01 Web 數(shù)據的導入······················································35
07 將Web 數(shù)據以外部數(shù)據的形式導入Excel ······················37
STEP 01 以外部數(shù)據的形式導入Web 數(shù)據 ···························37
08 練習問題 ·········································································40
Chapter 03 數(shù)據透視表 41
01 何為數(shù)據透視表·······························································42
STEP 01 數(shù)據透視表概要 ·······················································42
STEP 02 數(shù)據表與數(shù)據透視表的構成 ·····································44
02 制作數(shù)據透視表·······························································46
STEP 01 準備數(shù)據透視表 ·······················································46
STEP 02 制作數(shù)據透視表 ·······················································49
03 更改數(shù)據透視表的布局 ····················································52
STEP 01 更改布局 ·································································52
04 篩選數(shù)據透視表的數(shù)據 ····················································55
STEP 01 顯示指定的數(shù)據 ·······················································55
05 制作添加篩選條件的數(shù)據透視表 ·····································57
STEP 01 添加頁字數(shù)后根據各項來更改數(shù)據透視表 ···············57
06 根據報表篩選的項目區(qū)分工作表 ·····································60
STEP 01 制作項目的統(tǒng)計表 ···················································60
07 將數(shù)據透視表中的數(shù)值數(shù)據進行排序 ······························62
STEP 01 數(shù)值的排序 ······························································62
STEP 02 將明細顯示的數(shù)值進行排序 ·····································63
08 顯示前10 項 ······································································64
STEP 01 顯示前10 項 ····························································64
09 將項目的順序加以排序 ····················································67
STEP 01 將項目的順序進行排序 ············································67
10 將數(shù)據透視表的合計值用百分比表示 ······························68
STEP 01 顯示百分比 ······························································68
STEP 02 設置基準值以比較數(shù)據 ············································69
11 更改總計的類型 ·······························································71
STEP 01 更改值匯總方式 ·······················································71
12 將數(shù)據透視表的數(shù)據創(chuàng)建成組 ·········································73
STEP 01 將日期組成組 ··························································73
13 更新數(shù)據透視表的數(shù)據 ····················································76
STEP 01 更新數(shù)據 ·································································76
14 數(shù)據透視圖 ······································································78
STEP 01 制作數(shù)據透視圖 ·······················································78
15 數(shù)據透視表的格式化 ·······················································82
STEP 01 空白單元格中顯示“0” ··········································82
16 將數(shù)據透視表以“值”的形式顯示在別的工作表中 ·········85
STEP 01 以“值”的形式顯示在其他工作表中 ······················85
17 練習問題 ·········································································87
Chapter 04 圖表（Excel 2010） 89
01 圖表的構成元素·······························································90
STEP 01 圖表的構成元素和可以設置的內容 ··························90
02 制作圖表 ·········································································91
STEP 01 制作圖表操作 ··························································91
STEP 02 更改圖表類型 ··························································95
03 更改圖表的布局·······························································98
STEP 01 更改圖表整體的布局 ···············································98
STEP 02 更改網格線單位 ·····················································100
STEP 03 更改網格線 ····························································101
STEP 04 在圖表內顯示數(shù)據 ·················································102
04 選擇元素設置格式 ·························································104
STEP 01 選擇元素與格式設置對話框 ···································104
05 在既有的圖表上添加數(shù)據 ··············································106
STEP 01 在和圖表相同的工作表中添加數(shù)據 ························106
06 在既有圖表上追加其他工作表的數(shù)據 ····························109
STEP 01 添加與圖表分屬不同工作表的數(shù)據 ························109
07 組合不同類型的圖表 ····················································· 111
STEP 01 制作圖表 ································································111
08 保存自定義圖表····························································· 116
STEP 01 保存圖表為模板 ····················································· 116
STEP 02 使用已保存的圖表 ················································· 117
09 練習問題 ·······································································122
Chapter 05 單變量求解●規(guī)劃求解●方案●運算列表 123
01 何為單變量求解·····························································124
STEP 01 單變量求解概要 ·····················································124
STEP 02 單元格與“單變量求解”對話框的對應 ·················125
02 利用單變量求解逆運算數(shù)值 ···········································126
STEP 01 運用單變量求解求出目標值的zui佳數(shù)值 ·················126
03 何為規(guī)劃求解 ································································128
STEP 01 規(guī)劃求解的概要 ·····················································128
STEP 02 單元格與“規(guī)劃求解參數(shù)”對話框的對應 ·············130
04 添加規(guī)劃求解功能 ·························································132
STEP 01 安裝規(guī)劃求解 ························································132
05 用規(guī)劃求解求出zui佳解答 ··············································134
STEP 01 明確列出規(guī)劃求解的目的與限制條件 ····················134
STEP 02 在規(guī)劃求解中設置參數(shù)后求解 ·······························135
06 何為方案 ·······································································140
STEP 01 方案概要 ·······························································140
STEP 02 “添加方案”與“方案變量值”對話框 ·················141
STEP 03 “方案管理器”對話框 ··········································142
07 創(chuàng)建方案 ·······································································143
STEP 01 方案的創(chuàng)建 ····························································143
08 執(zhí)行方案 ·······································································146
STEP 01 方案的執(zhí)行 ····························································146
09 綜合顯示方案的結果 ·····················································147
STEP 01 將多個方案綜合顯示 ·············································147
10 表格（Excel 2010）······················································149
STEP 01 轉換成表格 ····························································149
STEP 02 在表格中添加合計行 ·············································153
STEP 03 在表格中輸入計算式 ·············································154
11 練習問題 ········································································158
Chapter 06 關于銷售的商業(yè)數(shù)據分析 159
01 運用Z 圖表來分析銷售額變化的傾向 ····························160
STEP 01 需準備的商務數(shù)據 ·················································163
STEP 02 Excel 操作①：算出營業(yè)額累計和移動年計 ··········164
STEP 03 Excel 操作②：制作Z 圖表 ···································165
STEP 04 解讀結果 ·······························································168
02 運用ABC 分析來弄清重點管理項目 ······························169
STEP 01 需準備的商務數(shù)據 ·················································172
STEP 02 Excel 操作①：銷售額的排序和算出銷售
實際成果的合計值 ·················································172
STEP 03 Excel 操作②：計算銷售構成比率和構成
比率的累計 ····························································175
STEP 04 Excel 操作③：顯示劃分的等級順位 ·····················176
STEP 05 Excel 操作④：制作柏拉圖 ····································178
STEP 06 Excel 操作⑤：顯示ABC 的劃分等級順位 ············181
STEP 07 解讀結果 ·······························································184
03 提早預測并選定能增加利潤的商品 ································187
STEP 01 需準備的商務數(shù)據 ·················································189
STEP 02 Excel 操作①：輸入運算公式 ································189
STEP 03 Excel 操作②：交叉比率的排序 ····························191
STEP 04 Excel 操作③：制作氣泡圖 ····································191
STEP 05 Excel 操作④：編輯圖表 ·······································199
STEP 06 解讀結果 ·······························································203
04 預測暢銷商品的銷售數(shù)量 ··············································205
STEP 01 需準備的商務數(shù)據 ·················································206
STEP 02 Excel 操作①：制作變化傾向圖 ····························207
STEP 03 Excel 操作②：添加趨勢線 ····································209
STEP 04 Excel 操作③：算出銷售數(shù)量的預測 ·····················213
STEP 05 解讀結果 ·······························································215
05 選定拍賣商品 ································································217
STEP 01 需準備的商務數(shù)據 ·················································220
STEP 02 Excel 操作①：制作需求曲線 ································220
STEP 03 Excel 操作②：算出基準價格的預測銷售數(shù)量 ·······229
STEP 04 Excel 操作③：算出價格彈性 ································231
STEP 05 解讀結果 ·······························································233
06 練習問題 ·······································································236
Chapter 07 關于企劃的商業(yè)數(shù)據分析 239
01 確立商品的市場定位 ·····················································240
STEP 01 需準備的商務數(shù)據 ·················································242
STEP 02 Excel 操作①：輸入計算式 ····································243
STEP 03 Excel 操作②：制作氣泡圖 ····································243
STEP 04 Excel 操作③：編輯圖表 ·······································250
STEP 05 Excel 操作④：在圖表中標出商品名稱 ··················253
STEP 06 分割成4 個領域 ····················································254
STEP 07 解讀結果 ·······························································256
02 從氣象數(shù)據預測夏季的銷售數(shù)量 ···································258
STEP 01 需準備的商務數(shù)據 ·················································262
STEP 02 Excel 操作①：算出相關系數(shù)與制作散點圖 ··········262
STEP 03 解讀結果① ····························································267
STEP 04 Excel 操作②：添加回歸直線 ································267
STEP 05 Excel 操作③：算出預測值 ····································269
STEP 06 解讀結果② ····························································271
03 求出新開店鋪的有效賣場面積 ·······································273
STEP 01 需準備的商務數(shù)據 ·················································275
STEP 02 Excel 操作①：求出商圈人口、賣場面積以及
銷售額的關系 ························································275
STEP 03 解讀結果① ····························································277
STEP 04 Excel 操作②：在新店鋪用地的商圈人口附近
加上趨勢線 ····························································278
STEP 05 解讀結果② ····························································281
STEP 06 Excel 操作③：根據商圈人口算出預測銷售額 ·······282
STEP 07 Excel 操作④：根據TREND 函數(shù)算出預測銷
售額·······································································282
目錄.indd 5 2016-1-12 16:53:59
CONTENTS
6
STEP 08 Excel 操作⑤：使用單變量求解算出賣場面積 ·······283
STEP 09 解讀結果③ ····························································285
04 運用雷達圖來分析商品的強項與弱項 ····························287
STEP 01 需準備的商務數(shù)據 ·················································289
STEP 02 Excel 操作①：制作雷達圖 ····································289
STEP 03 Excel 操作②：編輯圖表 ·······································290
STEP 04 解讀結果 ·······························································293
05 判定對提升利潤有貢獻的方法 ·······································296
STEP 01 需準備的商務數(shù)據 ·················································303
STEP 02 Excel 操作①：掌握現(xiàn)狀 ·······································303
STEP 03 解讀結果① ····························································310
STEP 04 Excel 操作②：模擬的準備 ····································310
STEP 05 Excel 操作③：改善方案的分析 ····························315
STEP 06 Excel 操作④：制作方案 ·······································316
STEP 07 解讀結果② ····························································320
06 練習問題 ·······································································323
Chapter 08 關于生產的商業(yè)數(shù)據分析 325
01 分析勞動生產力以確切指出原因 ···································326
STEP 01 需準備的商務數(shù)據 ·················································328
STEP 02 Excel 操作①：算出勞動生產力及其3 個指標 ·······329
STEP 03 Excel 操作②：比較去年和今年 ····························333
STEP 04 解讀結果 ·······························································335
02 導出產生zui大利潤的產量 ··············································337
STEP 01 需準備的商務數(shù)據 ·················································339
STEP 02 Excel 操作①：目的與生產條件的公式化 ··············340
STEP 03 Excel 操作②：規(guī)劃求解的設置與操作 ··················342
STEP 04 解讀結果① ····························································347
STEP 05 Excel 操作③：產品數(shù)據的更改與規(guī)劃求解
的操作 ···································································347
STEP 06 Excel 操作④：運算結果報表的編輯 ·····················349
STEP 07 解讀結果② ····························································350
03 求出zui佳的訂貨點 ·························································352
STEP 01 需準備的商務數(shù)據 ·················································357
STEP 02 Excel 操作①：算出研制周期中的平均銷售數(shù)量 ···358
STEP 03 Excel 操作②：算出安全庫存 ································359
STEP 04 Excel 操作③：算出訂貨點與訂貨量 ·····················360
STEP 05 解讀結果 ·······························································361
目錄.indd 6 2016-1-12 16:53:59
7
CONTENTS
04 練習問題 ·······································································363
Chapter 09 關于會計、財務的商業(yè)數(shù)據分析 365
01 根據客戶的業(yè)績設置信用范圍（交易限額） ·················366
STEP 01 需準備的商務數(shù)據 ·················································370
STEP 02 Excel 操作①：求出信用范圍的基準金額，
準備評價表 ····························································371
STEP 03 Excel 操作②：求出流動比率以及自有資本比率 ···372
STEP 04 Excel 操作③：根據流動比率與自有資本比率
設置信用范圍 ························································373
STEP 05 Excel 操作④：信用范圍的設置 ····························376
STEP 06 解讀結果 ·······························································377
02 決定購入或是租借 ·························································379
STEP 01 需準備的商務數(shù)據 ·················································384
STEP 02 Excel 操作①：準備資金流動表 ····························384
STEP 03 Excel 操作②：求出凈現(xiàn)值 ····································387
STEP 04 解讀結果 ·······························································390
03 練習問題 ·······································································393
附錄 圖表（Excel 2003）（見二維碼）
01 整理圖表的格式
STEP 01 圖表的構成元素和可以設置的內容
02 制作圖表（Excel 2003）
STEP 01 制作圖表
03 更改圖表類型
STEP 01 更改圖表的類型
04 選擇元素設置格式
STEP 01 選擇元素與格式設置對話框
05 在既有的圖表上添加數(shù)據
STEP 01 在和圖表相同的工作表中添加數(shù)據
STEP 02 在既有的圖表上添加其他工作表的數(shù)據
06 組合不同類型的圖表
STEP 01 制作圖表
07 保存自定義圖表
STEP 01 保存圖表
STEP 02 使用已保存的圖表
08 練習問題
目錄.indd 7 2016-1-12 16:53:59
目錄.indd 8 2016-1-12 16:53:59
鄂公網安備 42010302001612號 