第 1章　商务数据分析基础......................................................................1
1.1商务数据概述　...................................................................................2
1.1.1 　商务数据的定义 ..................................................................................2
1.1.2 　商务数据的作用 ..................................................................................2
1.1.3 　商务数据的采集 ..................................................................................3
1.1.4 　将商务数据导入Excel..........................................................................5
1.2　商务数据的清洗 ................................................................................7
1.2.1 　清洗缺失的数据 ..................................................................................7
1.2.2 　清洗重复的数据 ..................................................................................9
1.2.3 　清洗错误的数据 ................................................................................12
1.3　商务数据的分析 ..............................................................................15
1.3.1 　数据分析的基本步骤..........................................................................15
1.3.2 　数据分析方法....................................................................................16
1.3.3 　常用数据分析工具 .............................................................................19
1.3.4 　数据分析报告....................................................................................19
1.4　提高与技巧.....................................................................................20
1.4.1 　数据挖掘..........................................................................................20
1.4.2 　自助式商业智能工具..........................................................................20
第 2章　数据的输入与编辑 ....................................................................21
2.1　输入数据........................................................................................22
2.1.1 　输入数据的一般方法..........................................................................22
2.1.2 　输入特殊字符....................................................................................23
2.1.3 　快速填充数据....................................................................................24
2.2　计算表格中的数据 ...........................................................................25
2.2.1 　输入公式..........................................................................................25
2.2.2 　复制公式..........................................................................................26
2.2.3 　插入函数..........................................................................................27
2.2.4 　应用嵌套函数....................................................................................28
2.2.5 　常用函数介绍....................................................................................29
2.3　美化工作表.....................................................................................30
2.3.1 　套用预设样式....................................................................................30
2.3.2 　应用单元格内置样式..........................................................................32
2.3.3 　创建自定义样式 ................................................................................32
2.4　提高与技巧.....................................................................................34
2.4.1 　表格内容全部显示 .............................................................................34
2.4.2 　清除格式保留数据 .............................................................................35
2.4.3 　删除文本中的空格 .............................................................................36
2.4.4 　输入身份证号码 ................................................................................37
2.4.5 　定义单元格名称 ................................................................................37
2.4.6 　单元格选择技巧 ................................................................................38
第3章　数据的突出显示与可视化...........................................................39
3.1　添加条件格式..................................................................................40
3.1.1 　突出显示数据条 ................................................................................40
3.1.2 　按规定显示数据 ................................................................................42
3.1.3 　修改条件格式....................................................................................45
3.2　商务数据可视化 ..............................................................................47
3.2.1 　商务数据可视化的种类.......................................................................47
3.2.2 　创建图表..........................................................................................49
3.2.3 　编辑图表..........................................................................................50
3.2.4 　美化图表..........................................................................................53
3.3　分析图表数据..................................................................................55
3.3.1 　添加趋势线.......................................................................................55
3.3.2 　添加误差线.......................................................................................57
3.4　提高与技巧.....................................................................................58
3.4.1 　单元格可视化....................................................................................58
3.4.2 　突出正负值.......................................................................................59
3.4.3 　创建复合饼图....................................................................................60
第4章　数据的排序、筛选与分类汇总....................................................61
4.1　数据的排序.....................................................................................62
4.1.1 　简单排序..........................................................................................62
4.1.2 　单一字段排序....................................................................................63
4.1.3 　多重字段排序....................................................................................65
4.1.4 　自定义排序.......................................................................................66
4.2　数据的筛选.....................................................................................68
4.2.1 　自动筛选..........................................................................................68
4.2.2 　自定义筛选.......................................................................................69
4.2.3 　高级筛选..........................................................................................70
4.3　数据的分类汇总 ..............................................................................71
4.3.1 　创建分类汇总....................................................................................71
4.3.2 　创建嵌套分类汇总 .............................................................................72
4.3.3 　分级查看汇总数据 .............................................................................73
4.4　提高与技巧.....................................................................................75
4.4.1 　按笔画排序.......................................................................................75
4.4.2 　用函数进行排序 ................................................................................76
4.4.3 　让序号列不参与排序..........................................................................76
4.4.4 　按字符数量排序 ................................................................................76
4.4.5 　控制单元格填充颜色的排列顺序..........................................................77
4.4.6 　按字体颜色或单元格填充颜色筛选.......................................................77
4.4.7 　合并计算..........................................................................................78
第5章　使用数据透视图表分析数据........................................................79
5.1　数据透视表的应用 ...........................................................................80
5.1.1 　创建数据透视表 ................................................................................80
5.1.2 　设置数据透视表 ................................................................................82
5.1.3 　使用数据透视表 ................................................................................85
5.1.4 　美化数据透视表 ................................................................................91
5.2　切片器的应用..................................................................................93
5.2.1 　插入切片器.......................................................................................94
5.2.2 　更改切片器样式 ................................................................................95
5.2.3 　设置切片器.......................................................................................96
5.2.4 　删除切片器.......................................................................................98
5.3　数据透视图的应用 ...........................................................................98
5.3.1 　创建数据透视图 ................................................................................98
5.3.2 　使用数据透视图 ..............................................................................100
5.3.3 　设置并美化数据透视图.....................................................................103
5.4　提高与技巧...................................................................................107
5.4.1 　处理数据透视表的空白项..................................................................107
5.4.2 　对数据透视表使用条件格式 ..............................................................108
5.4.3 　改变数据透视表的报表布局 ..............................................................108
第6章　产品营销数据分析..................................................................................109
6.1　单款产品销售生命周期分析 ............................................................110
6.1.1 　统计对应日期的销售额.....................................................................110
6.1.2 　判断单款产品的销售生命周期 ...........................................................111
6.2　产品畅/滞销款分析 ........................................................................115
6.2.1 　使用公式计算库销比........................................................................115
6.2.2 　突出显示滞销产品 ...........................................................................116
6.2.3 　创建饼图查看库存金额.....................................................................118
6.3　多店铺销售数据分析......................................................................121
6.3.1 　计算各店铺的成交率和客单价 ...........................................................121
6.3.2 　使用迷你图显示各店铺的销量 ...........................................................124
6.3.3 　创建圆柱图对比各店铺的销售额........................................................126
6.4　历年销售数据分析 .........................................................................129
6.4.1 　分析历年上半年整体销售数据 ...........................................................130
6.4.2 　分析历年上半年各月销售数据 ...........................................................132
6.5　提高与技巧...................................................................................136
6.5.1 　销售额/销售量分析 ..........................................................................136
6.5.2 　季节性产品销量分析........................................................................137
6.5.3 　产品价格体系分析 ...........................................................................137
6.5.4 　产品区域销售情况分析.....................................................................138
第7章　销售费用分析.........................................................................139
7.1　销售收入与成本分析......................................................................140
7.1.1 　计算销售收入、成本、费用和税金.....................................................140
7.1.2 　回归分析销售收入与成本..................................................................143
7.1.3 　利用图表显示销售收入与成本的变化..................................................145
7.2　各项费用明细支出对比...................................................................149
7.2.1 　创建数据透视表统计各项费用 ...........................................................150
7.2.2 　使用数据透视图分析人工成本 ...........................................................153
7.3　提高与技巧...................................................................................156
7.3.1 　调整数据透视表视图效果..................................................................157
7.3.2 　在数据透视表中计算数据..................................................................157
7.3.3 　将数据透视表拆分成多个分表 ...........................................................158
第8章　营销决策分析.........................................................................159
8.1　市场需求量分析 ............................................................................160
8.1.1 　添加分析工具库 ..............................................................................160
8.1.2 　利用“指数平滑”工具分析市场需求量 ..............................................161
8.2　定价策略分析................................................................................164
8.2.1 　创建客户可接受价格比例图表 ...........................................................165
8.2.2 　建立求解变量模型 ...........................................................................166
8.2.3 　计算并分析最优定价........................................................................168
8.3　销售成本预测分析 .........................................................................171
8.3.1 　创建散点图.....................................................................................171
8.3.2 　添加线性预测趋势线........................................................................173
8.3.3 　INT和FORECAST函数的使用.........................................................175
8.4　客户消费能力分析 .........................................................................176
8.4.1 　不同年龄段客户的消费能力分析........................................................177
8.4.2 　不同性别客户的消费能力分析 ...........................................................179
8.5　提高与技巧...................................................................................181
8.5.1 　创建带对比效果的柱形图..................................................................181
8.5.2 　采用自选形状作为折线图的数据点.....................................................182
第9章　竞争对手分析.........................................................................183
9.1　竞争产品市场份额分析..................................................................184
9.1.1 　使用公式计算市场占有率 .................................................................184
9.1.2 　使用函数实现多条件求和..................................................................186
9.1.3 　创建市场占有率对比图.....................................................................188
9.2　竞争对手价格差异分析...................................................................191
9.2.1 　定义名称........................................................................................191
9.2.2 　创建动态图表..................................................................................193
9.3　提高与技巧...................................................................................197
9.3.1 　通过INDEX函数和控件创建动态图表.................................................197
9.3.2 　通过数据有效性创建动态图表 ...........................................................198
第 10章　订单与库存分析....................................................................199
10.1　客户订单记录统计 .......................................................................200
10.1.1 　按产品名称汇总订单......................................................................200
10.1.2 　使用函数统计销售总额...................................................................202
10.1.3 　创建销售额动态图表......................................................................203
10.2　产品出入库情况分析 ....................................................................205
10.2.1 　计算产品的出入库金额...................................................................206
10.2.2 　按多条件方式对数据进行排序 .........................................................207
10.2.3 　计算安全库存、最大库存和最小库存量 ............................................209
10.2.4 　设置条件格式进行预警提示 ............................................................210
10.3　产品库龄分析..............................................................................212
10.3.1 　计算产品的库龄 ............................................................................212
10.3.2 　突出显示库龄长的产品...................................................................213
10.4　提高与技巧.................................................................................214
10.4.1 　突出显示某个数据记录...................................................................214
10.4.2 　自定义数据的图标集......................................................................214
第 11章　财务数据分析.......................................................................215
11.1　利润预测与分析 ..........................................................................216
11.1.1 　利润预测......................................................................................216
11.1.2 　动态分析产品利润 .........................................................................219
11.2　利润比率分析..............................................................................223
11.2.1 　利润表比率的计算 .........................................................................224
11.2.2 　利润表比率变化趋势分析................................................................226
11.3　提高与技巧.................................................................................229
11.3.1 　将图表保存为模板 .........................................................................230
11.3.2 　单变量求解...................................................................................230