01　开始商务数据分析 ·····························································2

STEP 01　现在可以马上进行的商务数据分析 ····························2

02　日常业务和商务数据分析的关系 ·······································8

STEP 01　日常业务改善的流程和商务数据分析的关系 ·············8

03　商务数据分析的流程 ·························································9

STEP 01　商务数据分析的方法 ·················································9

04　商务数据分析的基本方法 ················································10

STEP 01　算出比率加以分析 ···················································10

STEP 02　绘制图表加以分析 ··················································· 11

STEP 03　改变切入点加以分析 ···············································12

STEP 04　改变维度加以分析 ···················································13

Chapter 02　导入外部数据 15

01　将分析数据导入Excel ·····················································16

STEP 01　数据的导出与导入 ···················································16

02　将文本数据导入Excel ·····················································17

STEP 01　文本数据格式的种类 ···············································17

STEP 02　导入文本数据 ··························································17

STEP 03　美化并以Excel 格式存储 ········································20

03　在Excel 中导入Access 的数据·······································25

STEP 01　导入Access 的数据 ················································25

04　加上筛选条件后导入数据 ················································28

STEP 01　加上筛选条件导入数据 ············································28

05　直接将外部数据制作成数据透视表 ··································31

STEP 01　运用数据透视表导入外部数据 ·································31

06　将web 数据导入Excel ···················································35

STEP 01　Web 数据的导入······················································35

07　将Web 数据以外部数据的形式导入Excel ······················37

STEP 01　以外部数据的形式导入Web 数据 ···························37

08　练习问题 ·········································································40

Chapter 03　数据透视表 41

01　何为数据透视表·······························································42

STEP 01　数据透视表概要 ·······················································42

STEP 02　数据表与数据透视表的构成 ·····································44

02　制作数据透视表·······························································46

STEP 01　准备数据透视表 ·······················································46

STEP 02　制作数据透视表 ·······················································49

03　更改数据透视表的布局 ····················································52

STEP 01　更改布局 ·································································52

04　筛选数据透视表的数据 ····················································55

STEP 01　显示指定的数据 ·······················································55

05　制作添加筛选条件的数据透视表 ·····································57

STEP 01　添加页字数后根据各项来更改数据透视表 ···············57

06　根据报表筛选的项目区分工作表 ·····································60

STEP 01　制作项目的统计表 ···················································60

07　将数据透视表中的数值数据进行排序 ······························62

STEP 01　数值的排序 ······························································62

STEP 02　将明细显示的数值进行排序 ·····································63

08 显示前10 项 ······································································64

STEP 01　显示前10 项 ····························································64

09　将项目的顺序加以排序 ····················································67

STEP 01　将项目的顺序进行排序 ············································67

10　将数据透视表的合计值用百分比表示 ······························68

STEP 01　显示百分比 ······························································68

STEP 02　设置基准值以比较数据 ············································69

11　更改总计的类型 ·······························································71

STEP 01　更改值汇总方式 ·······················································71

12　将数据透视表的数据创建成组 ·········································73

STEP 01　将日期组成组 ··························································73

13　更新数据透视表的数据 ····················································76

STEP 01　更新数据 ·································································76

14　数据透视图 ······································································78

STEP 01　制作数据透视图 ·······················································78

15　数据透视表的格式化 ·······················································82

STEP 01　空白单元格中显示“0” ··········································82

16　将数据透视表以“值”的形式显示在别的工作表中 ·········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　在图表内显示数据 ·················································102

04　选择元素设置格式 ·························································104

STEP 01　选择元素与格式设置对话框 ···································104

05　在既有的图表上添加数据 ··············································106

STEP 01　在和图表相同的工作表中添加数据 ························106

06　在既有图表上追加其他工作表的数据 ····························109

STEP 01　添加与图表分属不同工作表的数据 ························109

07　组合不同类型的图表 ····················································· 111

STEP 01　制作图表 ································································111

08　保存自定义图表····························································· 116

STEP 01　保存图表为模板 ····················································· 116

STEP 02　使用已保存的图表 ················································· 117

09　练习问题 ·······································································122

Chapter 05　单变量求解●规划求解●方案●运算列表 123

01　何为单变量求解·····························································124

STEP 01　单变量求解概要 ·····················································124

STEP 02　单元格与“单变量求解”对话框的对应 ·················125

02　利用单变量求解逆运算数值 ···········································126

STEP 01　运用单变量求解求出目标值的zui佳数值 ·················126

03　何为规划求解 ································································128

STEP 01　规划求解的概要 ·····················································128

STEP 02　单元格与“规划求解参数”对话框的对应 ·············130

04　添加规划求解功能 ·························································132

STEP 01　安装规划求解 ························································132

05　用规划求解求出zui佳解答 ··············································134

STEP 01　明确列出规划求解的目的与限制条件 ····················134

STEP 02　在规划求解中设置参数后求解 ·······························135

06　何为方案 ·······································································140

STEP 01　方案概要 ·······························································140

STEP 02　“添加方案”与“方案变量值”对话框 ·················141

STEP 03　“方案管理器”对话框 ··········································142

07　创建方案 ·······································································143

STEP 01　方案的创建 ····························································143

08　执行方案 ·······································································146

STEP 01　方案的执行 ····························································146

09　综合显示方案的结果 ·····················································147

STEP 01　将多个方案综合显示 ·············································147

10　表格（Excel 2010）······················································149

STEP 01　转换成表格 ····························································149

STEP 02　在表格中添加合计行 ·············································153

STEP 03　在表格中输入计算式 ·············································154

11　练习问题 ········································································158

Chapter 06　关于销售的商业数据分析 159

01　运用Z 图表来分析销售额变化的倾向 ····························160

STEP 01　需准备的商务数据 ·················································163

STEP 02　Excel 操作①：算出营业额累计和移动年计 ··········164

STEP 03　Excel 操作②：制作Z 图表 ···································165

STEP 04　解读结果 ·······························································168

02　运用ABC 分析来弄清重点管理项目 ······························169

STEP 01　需准备的商务数据 ·················································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　需准备的商务数据 ·················································189

STEP 02　Excel 操作①：输入运算公式 ································189

STEP 03　Excel 操作②：交叉比率的排序 ····························191

STEP 04　Excel 操作③：制作气泡图 ····································191

STEP 05　Excel 操作④：编辑图表 ·······································199

STEP 06　解读结果 ·······························································203

04　预测畅销商品的销售数量 ··············································205

STEP 01　需准备的商务数据 ·················································206

STEP 02　Excel 操作①：制作变化倾向图 ····························207

STEP 03　Excel 操作②：添加趋势线 ····································209

STEP 04　Excel 操作③：算出销售数量的预测 ·····················213

STEP 05　解读结果 ·······························································215

05　选定拍卖商品 ································································217

STEP 01　需准备的商务数据 ·················································220

STEP 02　Excel 操作①：制作需求曲线 ································220

STEP 03　Excel 操作②：算出基准价格的预测销售数量 ·······229

STEP 04　Excel 操作③：算出价格弹性 ································231

STEP 05　解读结果 ·······························································233

06　练习问题 ·······································································236

Chapter 07　关于企划的商业数据分析 239

01　确立商品的市场定位 ·····················································240

STEP 01　需准备的商务数据 ·················································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　从气象数据预测夏季的销售数量 ···································258

STEP 01　需准备的商务数据 ·················································262

STEP 02　Excel 操作①：算出相关系数与制作散点图 ··········262

STEP 03　解读结果① ····························································267

STEP 04　Excel 操作②：添加回归直线 ································267

STEP 05　Excel 操作③：算出预测值 ····································269

STEP 06　解读结果② ····························································271

03　求出新开店铺的有效卖场面积 ·······································273

STEP 01　需准备的商务数据 ·················································275

STEP 02　Excel 操作①：求出商圈人口、卖场面积以及

销售额的关系 ························································275

STEP 03　解读结果① ····························································277

STEP 04　Excel 操作②：在新店铺用地的商圈人口附近

加上趋势线 ····························································278

STEP 05　解读结果② ····························································281

STEP 06　Excel 操作③：根据商圈人口算出预测销售额 ·······282

STEP 07　Excel 操作④：根据TREND 函数算出预测销

售额·······································································282

目录.indd 5 2016-1-12 16:53:59

CONTENTS

6

STEP 08　Excel 操作⑤：使用单变量求解算出卖场面积 ·······283

STEP 09　解读结果③ ····························································285

04　运用雷达图来分析商品的强项与弱项 ····························287

STEP 01　需准备的商务数据 ·················································289

STEP 02　Excel 操作①：制作雷达图 ····································289

STEP 03　Excel 操作②：编辑图表 ·······································290

STEP 04　解读结果 ·······························································293

05　判定对提升利润有贡献的方法 ·······································296

STEP 01　需准备的商务数据 ·················································303

STEP 02　Excel 操作①：掌握现状 ·······································303

STEP 03　解读结果① ····························································310

STEP 04　Excel 操作②：模拟的准备 ····································310

STEP 05　Excel 操作③：改善方案的分析 ····························315

STEP 06　Excel 操作④：制作方案 ·······································316

STEP 07　解读结果② ····························································320

06　练习问题 ·······································································323

Chapter 08　关于生产的商业数据分析 325

01　分析劳动生产力以确切指出原因 ···································326

STEP 01　需准备的商务数据 ·················································328

STEP 02　Excel 操作①：算出劳动生产力及其3 个指标 ·······329

STEP 03　Excel 操作②：比较去年和今年 ····························333

STEP 04　解读结果 ·······························································335

02　导出产生zui大利润的产量 ··············································337

STEP 01　需准备的商务数据 ·················································339

STEP 02　Excel 操作①：目的与生产条件的公式化 ··············340

STEP 03　Excel 操作②：规划求解的设置与操作 ··················342

STEP 04　解读结果① ····························································347

STEP 05　Excel 操作③：产品数据的更改与规划求解

的操作 ···································································347

STEP 06　Excel 操作④：运算结果报表的编辑 ·····················349

STEP 07　解读结果② ····························································350

03　求出zui佳的订货点 ·························································352

STEP 01　需准备的商务数据 ·················································357

STEP 02　Excel 操作①：算出研制周期中的平均销售数量 ···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　关于会计、财务的商业数据分析 365

01　根据客户的业绩设置信用范围（交易限额） ·················366

STEP 01　需准备的商务数据 ·················································370

STEP 02　Excel 操作①：求出信用范围的基准金额，

准备评价表 ····························································371

STEP 03　Excel 操作②：求出流动比率以及自有资本比率 ···372

STEP 04　Excel 操作③：根据流动比率与自有资本比率

设置信用范围 ························································373

STEP 05　Excel 操作④：信用范围的设置 ····························376

STEP 06　解读结果 ·······························································377

02　决定购入或是租借 ·························································379

STEP 01　需准备的商务数据 ·················································384

STEP 02　Excel 操作①：准备资金流动表 ····························384

STEP 03　Excel 操作②：求出净现值 ····································387

STEP 04　解读结果 ·······························································390

03　练习问题 ·······································································393

附录　图表（Excel 2003）（见二维码）

01　整理图表的格式

STEP 01　图表的构成元素和可以设置的内容

02　制作图表（Excel 2003）

STEP 01　制作图表

03　更改图表类型

STEP 01　更改图表的类型

04　选择元素设置格式

STEP 01　选择元素与格式设置对话框

05　在既有的图表上添加数据

STEP 01　在和图表相同的工作表中添加数据

STEP 02　在既有的图表上添加其他工作表的数据

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

地都能存取数据。因此，只要想分析数据，在任何地方都可以实现。

可是如果不了解方法，即使有如此方便的网络环境也没用。因此，为了让读

者学习到实用的方法，我编写了本书。

本书详细介绍了使用Excel 的15 种数据分析方法。

本书特色如下：

●用职场为背景、以短剧式的会话方式叙述，介绍在某件事感到困惑时该如

何处理。

●关于数据分析的方法，不是以学术的观点，而是以“可以在现场活用”的

观点来介绍。

●对于“**次学习数据分析和Excel”的读者相当体贴，将数据分析或操

作按步骤来介绍。

●利用Excel 所得的结果为“该如何读取呢？”，更深入介绍在事务上zui常

被需求的“意思决定”。

●采用话题进而分析类似案例，对于在各种部门中都能广泛运用。

因此，本书不是“使用Excel 制作出表格（图表）”而已，而是将Excel 作

为数据分析时的工具。

此外，本书支持微软Excel 2010/2007，对于Excel 2003 和Excel 2013 的用户，

在使用上也不会产生障碍。

另外，为了让您深入理解分析方法，本书还在各章准备了练习问题。

希望本书能帮助读者完成各种数据分析实务工作。

日花弘子