Excel数据透视表与数据分析范例应用PDF电子书下载

第1篇 数据处理 3

第1章　数据的输入与格式化 3

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章　数据的排序、筛选与分类汇总 37

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　使用Microsoft Query分析数据 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章　使用函数分析数据 86

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.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篇 数据透视表与图表 165

第4章　分析产品销售数据 165

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章　制作与分析工资统计表 184

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章　使用基本图表分析数据 212

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章　使用交互图表分析数据 246

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章　图表范例解析 286

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篇 经典范例解析 311

第9章　销售与营销数据分析 311

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章　生产决策数据分析 360

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章　抽样与问卷调查数据分析 391

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