Excel数据透视表与数据分析范例应用

　　第1篇 数据处理
　　 第1章 数据的输入与格式化
　　 1.1 数据的输入3
　　 1.1.1 输入技巧3
　　 1.数值型数据的输入3
　　 2.日期和时间型数据的输入5
　　 3.文本型数据的输入6
　　 1.1.2 使用数据有效性录入7
　　 1.自定义填充序列7
　　 2.自定义填充范围8
　　 1.1.3 有规律数据的输入9
　　 1.复制填充9
　　 2.序列填充10
　　 3.自定义填充11
　　 1.1.4 相同数据的输入12
　　 1.2 导入外部数据13
　　 1.2.1 导入文本数据13
　　 1.2.2 导入Access数据库数据15
　　 1.2.3 导入Word文档中的表格16
　　 1.2.4 导入网页数据17
　　 1.2.5 有选择地导入Excel工作表中的数据19
　　 1.2.6 刷新工作表中的外部数据21
　　 1.3 数据的格式化23
　　 1.3.1 基本数据的格式设置23
　　 1.文本的格式设置23
　　 2.数值的格式设置25
　　 3.日期和时间的格式设置26
　　 1.3.2 条件格式的设置27
　　 1.3.3 样式的定义和使用30
　　 1.使用内置样式30
　　 2.使用自定义样式31
　　 3.样式的编辑33
　　 1.3.4 自定义格式的设置34
　　 1.使用自定义格式34
　　 2.自定义格式的控制符35
　　 1.3.5 使用宏进行格式化37
　　
　　 第2章 数据的排序、筛选与分类汇总
　　 2.1 数据的排序40
　　 2.1.1 简单排序40
　　 2.1.2 复杂排序41
　　 2.1.3 自定义排序42
　　 2.2 数据的筛选43
　　 2.2.1 自动筛选44
　　 2.2.2 自定义筛选45
　　 2.2.3 高级筛选47
　　 1.满足一个条件的筛选47
　　 2.满足多个条件的筛选48
　　 3.满足多个条件中任意一个条件的筛选49
　　 4.自定义条件的筛选50
　　 2.3 数据的分类汇总52
　　 2.3.1 简单分类汇总52
　　 2.3.2 高级分类汇总53
　　 2.3.3 嵌套分类汇总54
　　 2.3.4 隐藏与显示汇总结果57
　　 2.3.5 分级组合数据58
　　 2.3.6 分页显示分类汇总60
　　 2.4 数据的查询与分析62
　　 2.4.1 使用查询向导分析数据62
　　 2.4.2 使用MicrosoftQuery分析数据66
　　 2.4.3 使用参数查询分析数据69
　　 2.5 打印工作表73
　　 2.5.1 页面设置73
　　 1.设置页面布局73
　　 2.设置页边距74
　　 3.设置页眉和页脚75
　　 4.设置工作表78
　　 2.5.2 打印预览80
　　 2.5.3 打印工作表81
　　 2.5.4 设计窗口布局82
　　
　　 第3章 使用函数分析数据
　　 3.1 逻辑函数86
　　 3.1.1 逻辑函数介绍86
　　 3.1.2 优秀员工评选87
　　 3.1.3 判断闰年与平年88
　　 3.2 日期与时间函数89
　　 3.2.1 日期与时间函数介绍89
　　 1.日期函数90
　　 2.时间函数94
　　 3.2.2 项目进度统计表95
　　 3.2.3 计算加班时间和加班费99
　　 3.2.4 计算员工的工龄100
　　 .3 信息函数101
　　 3.3.1 信息函数介绍101
　　 3.3.2 市场调查信息的人性化显示107
　　 3.3.3 根据身份证号码判断性别108
　　 3.4 数学与三角函数109
　　 3.4.1 数学与三角函数介绍109
　　 1.数学函数109
　　 2.三角函数114
　　 3.数组函数115
　　 3.4.2 计算产品库存及奖金提成116
　　 3.4.3 计算个人所得税122
　　 3.5 数据库函数123
　　 3.5.1 数据库函数介绍123
　　 3.5.2 员工季度销售业绩分析125
　　 3.5.3 统计企业各部门的人员工资128
　　 3.6 查找和引用函数128
　　 3.6.1 查找和引用函数介绍129
　　 3.6.2 提成与工资的自动计算133
　　 3.6.3 动态计算员工总销量136
　　 3.7 统计函数137
　　 3.7.1 统计函数介绍137
　　 1.常用统计函数137
　　 2.数理统计函数140
　　 3.7.2 员工信息统计142
　　 3.7.3 原料采购动态汇总144
　　 3.8 财务函数144
　　 3.8.1 财务函数介绍145
　　 1.折旧计算函数145
　　 2.本金和利息计算函数146
　　 3.投资计算函数146
　　 4.报酬率计算函数147
　　 5.证券计算函数148
　　 3.8.2 项目贷款金额分析150
　　 3.8.3 计算证券利率和价格153
　　 3.9 文本函数154
　　 3.9.1 文本函数介绍154
　　 3.9.2 处理客户信息158
　　 3.9.3 将数字格式转换为中文数字161
　　
　　第2篇 数据透视表与图表
　　 第4章 分析产品销售数据
　　 4.1 产品销售分析表165
　　 4.1.1 创建数据透视表165
　　 4.1.2 认识数据透视表167
　　 1.【数据透视表】工具栏167
　　 2.数据透视表的组成168
　　 3.数据透视表的基本操作169
　　 4.1.3 创建销售统计月报表170
　　 4.1.4 创建销售统计季度报表171
　　 4.1.5 创建销售统计年报表172
　　 4.1.6 设计数据透视表的布局173
　　 1.更改数据透视表布局173
　　 2.查看数据条目174
　　 4.1.7 设置数据透视表的格式175
　　 4.1.8 将分析数据发布到网上176
　　 4.2 产品销售汇总表177
　　 4.2.1 定义数据源名称178
　　 4.2.2 创建数据透视表179
　　 4.2.3 美化数据透视表184
　　
　　 第5章 制作与分析工资统计表
　　 5.1 使用数据透视表分析数据188
　　 5.1.1 创建数据透视表188
　　 5.1.2 编辑数据透视表190
　　 1.移动与复制数据透视表190
　　 2.更改数据源191
　　 3.设置数据透视表字段192
　　 4.设置数据透视表格式194
　　 5.删除数据透视表194
　　 6.刷新数据195
　　 5.1.3 显示与隐藏数据透视表中的数据196
　　 5.1.4 使用数据透视表的总计和分类汇总功能198
　　 5.1.5 分页显示数据透视表199
　　 5.2 使用数据透视图分析数据201
　　 5.2.1 创建数据透视图201
　　 1.使用“数据透视表和数据透视图向导”创建201
　　 2.使用图表工具创建202
　　 5.2.2 编辑数据透视图203
　　 5.2.3 美化数据透视图207
　　 5.2.4 数据透视图与静态图表间的区别和转换211
　　 1.数据透视图与静态图表间的区别211
　　 2.数据透视图与静态图表间的转换212
　　
　　 第6章 刺激基本图表分析数据
　　 6.1 分析客户地区分布数据214
　　 6.1.1 创建簇状柱形图214
　　 6.1.2 创建堆积柱形图215
　　 6.1.3 创建三维柱形图216
　　 6.2 分析产品成本数据218
　　 6.2.1 创建饼图218
　　 6.2.2 设置图表区格式219
　　 6.3 分析产品日销量221
　　 6.3.1 创建折线图221
　　 6.3.2 设置绘图区格式224
　　 6.4 分析产品促销费用225
　　 6.4.1 创建面积图225
　　 6.4.2 设置图表标题及图例格式228
　　 6.5 分析客户拜访次数与销量的关系233
　　 6.5.1 创建XY散点图233
　　 6.5.2 设置坐标轴格式235
　　 6.5.3 设置数据系列格式236
　　 6.6 分析股价行情238
　　 6.6.1 创建股价图238
　　 6.6.2 添加趋势线242
　　 6.6.3 利用函数在K线图中绘制最值243
　　 6.6.4 打印图表246
　　
　　 第7章 使用交互图表分析数据
　　 7.1 创建地区销售业绩自动缩放图表248
　　 7.1.1 创建簇状柱形图248
　　 7.1.2 设置图表格式250
　　 7.1.3 图表的自动缩放254
　　 7.2 使用滚动条控制月份255
　　 7.2.1 创建三维簇状柱形图255
　　 7.2.2 添加“滚动条”控件259
　　 7.3 使用比较直方图分析公司借贷情况261
　　 7.3.1 创建簇状条形图261
　　 7.3.2 设置图表格式262
　　 7.4 创建销售计划模拟图表264
　　 7.4.1 计算“差异百分比”265
　　 7.4.2 制作模拟图表265
　　 7.5 标识产品市场占有率的最大值和最小值267
　　 7.5.1 创建折线图268
　　 7.5.2 制作辅助列269
　　 7.5.3 标识最大值和最小值270
　　 7.6 自动刷新员工绩效考核图表272
　　 7.6.1 创建簇状柱形图273
　　 7.6.2 利用公式更新图表显示274
　　 7.6.3 使用宏自动计算工作表276
　　 7.7 使用复选框显示产品月销售额277
　　 7.7.1 创建数据点折线图277
　　 7.7.2 添加可控制显示数据的复选框279
　　 7.7.3 图表中文本的自动变化283
　　
　　 第8章 图表范例解析
　　 8.1 绘制温度计式任务进程图表286
　　 8.2 绘制瀑布图288
　　 8.3 利用条形图创建代理商销量图表290
　　 8.4 绘制工作任务时间进度Gantt图291
　　 8.5 利用折线图创建全年销售趋势图293
　　 8.6 绘制广告效益图295
　　 8.7 绘制图片比例图296
　　 8.8 绘制速度表式图表反映工作状态298
　　 8.9 利用气泡图绘制销量比例图301
　　 8.10 利用面积图绘制员工销售业绩图303
　　 8.11 利用雷达图绘制客户地区分布图305
　　 8.12 利用曲面图绘制函数图307
　　
　　第3篇 经典范例解析
　　 第9章 销售与营销数据分析
　　 9.1 分析工具库的安装与卸载311
　　 9.1.1 安装与启用分析工具库311
　　 1.安装分析工具库311
　　 2.启用分析工具库312
　　 9.1.2 卸载已加载的分析工具库313
　　 9.2 公司最优营销策略314
　　 9.2.1 应用规划求解314
　　 1.加载并使用规划求解314
　　 2.规划结果取整318
　　 3.生成规划求解报告318
　　 4.规划求解选项设置320
　　 9.2.2 创建销售决策动态模型320
　　 1.创建基本计算表320
　　 2.自动选择最优方案322
　　 3.两种方案的毛利润曲线图表322
　　 4.在图表中添加控件326
　　 9.2.3 营销利润的方案管理330
　　 1.创建规划结果方案330
　　 2.创建方案摘要332
　　 9.3 员工销售业绩分析333
　　 9.3.1 直方图参数介绍334
　　 9.3.2 创建员工销售业绩直方图334
　　 9.4 促销方式对销售额的影响337
　　 9.4.1 单因素方差参数介绍337
　　 9.4.2 创建促销销售额统计表338
　　 9.4.3 使用单因素方差分析数据340
　　 9.5 媒体宣传费用对销售额的影响342
　　 9.5.1 无重复双因素参数介绍342
　　 9.5.2 创建当月销售额统计表343
　　 9.5.3 使用无重复双因素分析数据344
　　 9.6 产品地区销售额的影响因素346
　　 9.6.1 相关系数与回归分析参数介绍346
　　 1.“相关系数”工具的参数介绍347
　　 2.“回归分析”工具的参数介绍347
　　 9.6.2 创建地区销售额统计表348
　　 9.6.3 分析“销售额”的相关性350
　　 9.6.4 使用回归分析工具建立回归模型351
　　 9.7 产品市场调查数据分析354
　　 9.7.1 协方差参数介绍354
　　 9.7.2 创建产品市场调查表355
　　 9.7.3 使用协方差分析数据356
　　
　　 第10章 生产决策数据分析
　　 10.1 投资贷款偿还金额分析360
　　 10.1.1 建立投资贷款查询工作表360
　　 10.1.2 利用模拟运算表分析等额分期偿还额366
　　 1.单变量模拟运算表367
　　 2.双变量模拟运算表368
　　 10.1.3 利用单变量求解分析还款期限369
　　 10.2 随机分配产品库房371
　　 10.2.1 随机数发生器参数介绍371
　　 10.2.2 使用随机数发生器373
　　 10.3 品牌与消费水平对产品定价的影响376
　　 10.3.1 可重复双因素参数介绍376
　　 10.3.2 创建产品定价表377
　　 10.3.3 使用可重复双因素分析数据378
　　 10.4 产品库存分析381
　　 10.4.1 耙贫平均”参数介裳381
　　 10.4.2 创建产品季末库存表382
　　 10.4.3 使用“移动平均”法分析数据383
　　 10.5 产品发货量预测386
　　 10.5.1 爸甘平滑”参数介裳386
　　 10.5.2 创建产品季度发货量记录表387
　　 10.5.3 使用“指数平滑”法分析数据388
　　 10.6 产品质量分析390
　　 10.6.1 “F-检验”参数介绍390
　　 10.6.2 使用“F-检验”分析数据391
　　
　　 第11章 抽样与问卷调查数据分析
　　 11.1 抽样396
　　 11.1.1 简单抽样396
　　 11.1.2 系统抽样399
　　 11.2 设计调查问卷403
　　 11.2.1 问卷的设计步骤403
　　 11.2.2 问卷的设计过程404
　　 1.设计市场调查问卷404
　　 2.保护市场调查问卷409
　　 11.3 设计调查统计表411
　　 11.3.1 创建调查结果统计表411
　　 11.3.2 注释各选项413
　　 11.3.3 建立调查问卷与统计表之间的联系415
　　 11.3.4 实现问卷结果的自动添加417
　　 11.3.5 测试调查问卷419
　　 11.3.6 保护调查问卷工作簿420
　　 11.4 统计调查结果422
　　 11.5 调查问卷的数据分析422