本书针对市场分析与行销决策工作中的信息录入、数据统计、表格製作、表单设计和图表展示等套用需求，彙编出最贴近实际工作的知识和技巧，全面系统地介绍了Excel的技术特点和套用方法，深入揭示隐藏于高效办公背后的原理和概念，并配合大量典型的套用实例，帮助读者全面掌握Excel在市场分析与行销决策工作中的套用技术。

前言
如何获取云空间资料
第1 章 高效製作专业的销售报表
1.1　快速填充数据 ······················· 9
1.1.1 　通过控制柄填充数据 ·························9
1.1.2 　通过对话框填充数据 ······················· 11
套用技巧　快速建立部门年度销售规划表 ····· 12
1.1.3 　通过快捷选单填充数据 ···················· 15
1.2　利用数据有效性快速设计表单 ············· 16
1.2.1 　製作下拉列錶快速填充数据 ·············· 17
1.2.2 　限制输入整数 ···························· 18
1.2.3 　限制输入日期 ···························· 19
套用技巧　防止输入周末日期 ················· 19
1.2.4 　限制文本长度 ····························· 20
1.2.5 　设定提示讯息 ···························· 21
1.2.6 　设定错误警告 ···························· 21
套用技巧　圈释无效数据 ······················· 22
套用技巧　限制重複录入报价单 ·············· 24
1.3　使用样式快速美化工作表 ··················· 25
1.3.1 　套用预设样式 ··························26
套用技巧　快速删除单元格中的样式 ········ 27
1.3.2 　新建样式 ······························ 29
第2章 使用条件格式和迷你图分析数据
2.1　条件格式及其套用 ···························· 33
2.1.1 　添加条件格式 ·································33
2.1.2 　管理条件格式 ································39
套用技巧　判断输入的身份证号码是否正确 ···40
2.2　使用迷你图显示数据趋势 ··················· 41
2.2.1 　什幺是迷你图 ································42
2.2.2 　什幺情况下可以使用迷你图 ·················42
2.2.3 　迷你图的创建 ··································42
2.2.4 　迷你图的修改 ··································43
套用技巧　使用迷你图分析成本费用 ··········45
第3章 使用排序和筛选快速分析数据
3.1　数据的排序 ····································· 47
3.1.1 　单一栏位的排序 ···································47
套用技巧　排序字母与数字混合内容 ··········50
3.1.2 　多重栏位的排序 ···································53
套用技巧　随机排序 ···································53
3.1.3 　按照用户需要自定义序列排序 ··············55
3.1.4 　对Excel表中的数据进行排序 ················56
3.2　数据筛选的套用 ······························· 58
3.2.1 　快速查找数据 ································58
3.2.2 　在“搜寻”框中设定关键字以进行筛选 ······59
套用技巧　排名靠前或靠后的数字 ··············59
3.2.3 　自定义筛选方式 ···································60
套用技巧　选择不重複的记录 ·····················62
3.2.4 　详解高级筛选 ································62
套用技巧　使用计算条件的高级筛选 ··········66
3.3　利用筛选函式快速筛选数据 ················ 67
套用技巧　使用计算条件的高级筛选 ··········67
3.3.1 　利用GESTEP函式进行一次筛选 ··········68
3.3.2 　用IF、GESTEP函式进行二次筛选 ·······70
3.3.3 　利用INDEX函式显示符合条件记录 ······71
第4章 利用公式与函式快速统计报表
4.1　利用公式快速计算Excel数据 ·············· 73
4.1.1 　公式的简介 ·································73
4.1.2 　公式的输入 ·································75
4.1.3 　公式的修改 ··································77
4.1.4 　公式的複製 ··································78
4.2　单元格的引用功能 ···························· 79
4.2.1 　相对引用 ·································80
4.2.2 　绝对引用 ··································81
4.2.3 　混合引用 ····································82
4.2.4 　调用外部数据 ··································82
4.3　使用名称快速录入公式 ······················ 85
4.3.1 　什幺是名称 ·······························86
4.3.2 　定义名称的规则 ···································87
4.3.3 　定义名称 ·····································87
4.3.4 　利用名称快速录入公式 ·························89
4.4　使用函式快速统计数据 ······················ 90
4.4.1 　函式的插入和修改 ································91
套用技巧　利用统计函式计算销量最大值和最小值 ························91
4.4.2 　使用嵌套函式 ·································93
套用技巧　分段统计销量 ····························93
4.5　解决常见的公式错误 ························· 94
4.5.1 　“####”错误值的处理方法 ·················94
4.5.2 　“#DIV/0!”错误值的处理方法 ·············95
4.5.3 　“#N/A”错误值的处理方法 ·················97
第5章 使用分类快速汇总表单数据
5.1　创建数据分类汇总 ···························· 98
5.1.1 　创建分类汇总 ···································98
5.1.2 　创建嵌套分类汇总 ······························101
5.1.3 　利用分类函式快速汇总数据 ···············103
5.1.4 　删除分类汇总 ···································107
套用技巧　快速汇总每天的销售记录 ········108
5.2　分级查看分类汇总 ···························108
5.2.1 　按照级别查看汇总数据 ·······················108
5.2.2 　显示或隐藏分级数据 ··························109
5.2.3 　分页显示分类汇总 ······························110
5.2.4 　使用样式自定义显示分级数据 ············112
5.3　组合与取消组合分级数据 ·················· 112
5.3.1 　组合数据 ·······································113
5.3.2 　取消组合数据 ·····································115
5.4　对数据进行合併计算 ························ 116
5.4.1 　按位置合併计算 ·································117
5.4.2 　按分类合併计算 ·································120
第6章 快速创建图表分析数据
6.1　灵活地掌握各种图表类型 ··················122
6.2　创建与编辑图表 ······························125
6.2.1 　创建图表 ·····································125
6.2.2 　快速添加图表数据系列 ·······················127
6.2.3 　更改图表大小 ···································129
套用技巧　创建销售业绩下拉选单式图表 ······130
6.2.4 　图表的快速布局和样式设定 ···············130
6.3　图表各部分细节的修饰 ·····················132
6.3.1 　手动更改图表元素的布局 ···················133
套用技巧　为成本结构图添加百分比说明 ······135
6.3.2 　手动更改图表元素的样式 ···················137
套用技巧　设定背景透明的销量对比图表 ······137
6.4　预测与分析图表数据 ························142
6.4.1 　为图表添加趋势线 ······························142
套用技巧　绘製营业额水平参照线 ············143
6.4.2 　为图表添加误差线 ······························144
第7章 利用数据透视錶快速分组数据
7.1　什幺是数据透视表 ···························147
7.2　适用于数据透视表的情况 ··················148
7.3　基本数据透视表的製作 ·····················150
7.3.1 　单一行标籤数据透视表 ·······················151
7.3.2 　多重行标籤数据透视表 ·······················152
7.3.3 　有关栏位勾选的顺序 ··························153
7.3.4 　行标籤、列标籤与Σ数值的概念 ········154
7.3.5 　设定透视表栏位 ·································155
7.4　二维数据透视表 ······························157
7.4.1 　显示组分类汇总 ·································158
7.4.2 　调整报表的数字格式 ··························160
7.4.3 　数据分组设定 ·····································160
7.4.4 　更理想的布局方式 ······························161
7.4.5 　细部数据的摺叠与展开 ·······················162
7.5　让汇总数据更多元化 ························163
7.5.1 　更改文字标籤或栏位名称 ···················164
7.5.2 　数据透视表的汇总方式 ·······················164
7.6　报表筛选 ·······································165
7.6.1 　页栏位的筛选 ·····································166
7.6.2 　标籤或栏位的筛选 ······························167
7.7　数据透视表的排序 ···························168
7.7.1 　快速对标籤或栏位排序 ·······················168
7.7.2 　排序报表中的数值 ······························170
7.8　计算栏位 ·······································171
套用技巧　用透视表分析企业销售业绩 ····172
7.9　使用切片器筛选报表中的数据 ············174
7.9.1 　创建切片器并进行筛选 ·······················175
7.9.2 　调整切片器的大小 ······························176
7.9.3 　更改切片器的位置 ······························177
7.9.4 　套用切片器样式 ·································177
第8章 销售数据的统计与分析
8.1　创建区域销售表 ······························179
8.2　分析各地区销售额情况 ·····················182
8.2.1 　使用函式统计各地区销售额 ···············182
8.2.2 　排序各地区销售额 ······························184
8.2.3 　按名称统计销售额 ······························185
8.3　创建各地区销售额动态比较图 ············188
8.3.1 　创建各地区销售额比较图和结构饼图 ···188
8.3.2 　在工作表中创建选项按钮和複选框 ·····193
8.3.3 　设定公式创建动态数据区域 ···············196
第9章 商品行销数据分析
9.1　定价策略分析 ·································198
9.1.1 　计算价格保留区域百分比 ···················198
9.1.2 　创建消费者可接受价格範围图表 ········199
9.1.3 　绘製文本框标识图表 ··························202
9.2　市场竞争形势分析 ···························202
9.2.1 　计算销售差额 ·····································203
9.2.2 　创建竞争产品销量对比图 ···················204
9.3　品牌区域差异化分析 ························206
9.3.1 　创建差异化分析表 ······························206
9.3.2 　定义名称 ······································207
9.3.3 　创建品牌区域差异化动态图表 ············208
9.4　销售渠道分析 ································· 211
9.4.1 　创建销量比例统计表 ··························211
9.4.2 　创建销售渠道分析图 ··························212
9.4.3 　添加总销量增长率系列 ·······················214
第10章 产品销售的分期付款方式分析
10.1　使用单变数模拟运算表求解每月还款额 ···························215
10.1.1　创建单变数模拟运算表 ······················215
10.1.2　将模拟运算表的结果转换为常量 ·······217
10.2　利用单变数求解逆算年限 ················218
10.2.1　利用单变数求解计算年限的精确值 ····218
10.2.2　确定可操作年限及对应的每期付款额 ···220
10.3　使用双变数模拟运算表计算还款额 ····221
10.4　将模拟运算錶转换为直观的图表 ·······225
第11章 销售费用的分析与预测
11.1　使用直线法预测销售费用 ················229
11.1.1　线性拟合预测销售费用 ······················229
11.1.2　使用TREND函式预测销售费用 ·········231
11.2　使用指数法预测销售费用 ················233
11.2.1　使用LOGEST函式预测销售费用 ·······233
11.2.2　使用GROWTH函式预测销售费用 ·····235
11.3　使用图表法预测销售费用 ················236
11.3.1　创建实际销售费用分析图 ··················236
11.3.2　线性趋势线预测分析 ·························237
11.3.3　指数趋势线预测分析 ·························239
11.4　使用分析工具预测销售费用 ·············240
11.4.1　用“移动平均法”预测销售费用 ·······240
11.4.2　用“指数平滑法”预测销售费用 ·······244
第12章销售成本与利润分析
12.1　销售收入与成本 ····························247
12.1.1　销售收入、成本、费用、税金年度分析 ···················247
12.1.2　分析产品销售成本 ·····························252
12.2　关于企业销售利润最大化分析 ··········255
12.2.1　按成本习性分析销售成本 ··················256
12.2.2　载入“规划求解”工具 ······················258
12.2.3　通过减少广告费增加利润 ··················258
12.2.4　通过减少展览费增加利润 ··················260
12.2.5　通过减少销售成本增加利润 ··············261
12.2.6　创建规划求解报告 ··························263
12.2.7　合併方案 ·······························264