Chapter01对公司常用数据进行基本统计 1
1.1员工年假表 14
1.1.1知识点及效果图 14
1.1.2操作步骤 14
1.填充员工所属的部门 14
知识链接——TODAY（）函数 16
2.计算员工的年龄和工龄 16
知识链接——MONTH（）、YEAR（）函数 16
知识链接——INT（）函数 18
3.计算员工的年假天数 18
1.2员工加班记录表 20
1.2.1知识点及效果图 20
1.2.2操作步骤 20
1使用数据有效性填充部门和职位 20
知识链接——设置数据有效性 20
2.计算加班时间 22
知识链接——DATE（）函数 22
3.计算加班费 24
1.3 员工月度出勤统计表 24
1.3.1知识点及效果图 25
1.3.2操作步骤 25
1创建动态标题辅助表格 25
知识链接——插入和设置数字调节按钮 27
2.完善月度考勤表 29
3计算员工缺勤和缺席天数 30
更改步长值 35
Chapter02按照分类汇总进行统计 38
2.1产品生产单的简单分类汇总 38
2.1.1知识点及效果图 38
2.1.2操作步骤 38
1对产品名称进行排序 38
知识链接——简单排序 39
2按产品名称对总成本进行分类汇总 39
知识链接——设置单级分类汇总 39
知识链接——隐藏和显示明细数据 40
3.按生产日期对产品总成本进行分类汇总 41
2.2产品生产单的多级分类汇总 42
2.2.1知识点及效果图 42
2.2.2操作步骤 42
1.分别对生产车间和产品名称进行排序 42
知识链接——复杂排序 43
2.创建嵌套分类汇总 44
知识链接——设置多级分类汇总 44
3.隐藏和显示各车间各产品的汇总情况 45
知识链接——多级分级显示 45
2.3 使用基他方式统计 45
2.3.1知识点及效果图 46
2.3.2操作步骤 46
1.使用分类汇总函数对生产单进行统计 46
知识链接——显示公式 47
知识链接——SUBTOTAL（）函数 48
2.插入批注对汇总结果进行说明 49
知识链接——插入批注 49
3利用组合方式汇总产品总成本 52
求最大生产成本 55
Chapter03使用统计函数统计数据 58
3.1 使用统计函数分析销量 58
3.1.1知识点及效果图 58
3.1.2操作步骤 58
1.计算销量的最大值和最小值 58
2.计算销量的两个最大值和最小值 60
3.计算销量的众数、中位数和平均值 61
4.计算销量的25％、75％处的数据以及标准偏差 63
3.2 使用统计函数按销量排名并进行分段统计 65
3.2.1知识点及效果图 65
3.2.2操作步骤 66
1定义销售员和销量 66
知识链接——定义名称 66
2.按销售量排名 68
3分段统计销量 69
3.3使用统计函数统计保单销量 72
3.3.1知识点及效果图 72
3.3.2操作步骤 72
1.计算各种险种的投保率 72
知识链接——选择性粘贴 73
2.计算各种保险金额的比例值 75
3.分别按销售员和车型统计销售量 76
按销售员和车型统计 78
Chapter04使用图表统计 80
4.1 经营收入与生产成本对比折线图的制作 80
4.1.1知识点及效果图 80
4.1.2操作步骤 80
1.完善企业经营数据表 80
知识链接——插入公式 81
2.创建企业经营、生产成本对比图 82
知识链接——插入和设置折线图 82
3.设置企业经营、生产成本对比图 84
知识链接——添加趋势线 86
4.2 经营收入按年份对比柱状图的制作 87
4.2.1知识点及效果图 87
4.2.2操作步骤 88
1.创建经营收入的按年份对比图 88
知识链接——插入柱形图 88
知识链接——更改图表的效果 88
2.美化经营收入的按年份对比图 90
知识链接——编辑数据系列 91
3.移动经营收入的按年份对比图 92
4.3 企业长期经营状况的饼图制作 92
4.3.1知识点及效果图 93
4.3.2操作步骤 93
1.完善企业经营数据表 93
2.创建生产效益饼图 94
知识链接——插入饼图 94
知识链接——更改数据源 94
3.美化企业生产效益饼图 95
知识链接——在图表中设置 96
艺术字 96
圆环图的使用 98
Chapter05数据的描述统计分析 100
5.1 利用描述统计工具分析企业各月销售情况 100
5.1.1知识点及效果图 100
5.1.2操作步骤 100
1.计算销售额全距和方差 100
2.计算销售额标准差和四分位数 102
3.利用描述统计工具分析企业各月 103
销售额 103
知识链接——添加分析工具库 104
知识链接——描述统计工具 104
5.2 利用直方图和动态图表分析各月的销售额 105
5.2.1知识点及效果图 106
5.2.2操作步骤 106
1创建各月份销售额分布直方图 106
知识链接——直方图工具 107
2.计算销售额增长率、定基发展速度和环比发展速度 109
知识链接——公式的各种引用方法 109
3.企业各月销售额的动态图表分析 112
知识链接——折线图的创建和设置 112
5.3 利用泊松分布制定企业销售目标 113
5.3.1知识点及效果图 114
5.3.2操作步骤 114
1.计算销售额平均值和高于该平均值的周数 114
知识链接——AVERAGE（）函数 115
2.利用泊松分布函数计算概率 116
3.通过泊松函数计算不同周数的置信度 117
制作销售动态散点图 119
Chapter06使用排序和筛选分析数据 122
6.1 对销售记录表进行排序 122
6.1.1知识点及效果图 122
6.1.2操作步骤 122
1.对销售金额进行排序 122
2.对销售组别、销售月份和销售型号进行排序 123
3.对销售型号按照自定义序列进行排序 125
知识链接——自定义排序 125
6.2 对销售记录表进行筛选 126
6.2.1知识点及效果图 127
6.2.2操作步骤 127
1.对销售记录表进行自动筛选和自定义筛选 127
知识链接——自动筛选和自定义筛选 128
2.对销售记录表进行“与条件筛选 131
知识链接——“与条件筛选 131
3.对销售记录表进行“或条件筛选 134
知识链接——“或条件”筛选 134
6.3 利用筛选函数对销售记录表进行筛选 136
6.3.1知识点及效果图 136
6.3.2操作步骤 136
1.使用筛选函数对销售部数进行筛选 136
2.使用筛选函数对销售金额进行筛选 138
知识链接——IF（）、 GESTEP（）函数 138
3.计算热销型号 138
条件格式的使用 140
Chapter07数据的透视分析 142
7.1 企业员工性别和年龄透视分析 142
7.1.1知识点及效果图 142
7.1.2操作步骤 142
1.创建员工性别结构透视表 142
知识链接——创建数据透视表 142
知识链接——设置数据透视表 143
2.创建员工性别结构透视图 145
知识链接——创建数据透视图 145
3.创建员工年龄透视表 147
7.2 企业员工学历层次透视分析 150
7.2.1知识点及效果图 150
7.2.2操作步骤 151
1创建学历层次透视表 151
2.更新和排序学历层次透视表数据 153
知识链接——更新数据透视表 153
知识链接——排序数据透视表 154
3.美化学历层次透视表 155
知识链接——套用数据透视表格式 155
7.3 企业人力资源综合分析 155
7.3.1知识点及效果图 155
7.3.2操作步骤 156
1.企业人力资源综合透视表分析 156
知识链接——设置数据透视表中的值字段 157
2.企业人力资源综合透视图分析 158
知识链接——移动和更改数据透视图 158
3.美化企业人力资源综合透视图 159
知识链接——设置数据透视图 159
更改数据透视图的布局 162
Chapter08使用复合图表和双轴图表分析数据 162
8.1 企业资金结构分析 164
8.1.1知识点及效果图 164
8.1.2操作步骤 164
1.创建企业资金结构分布图 164
知识链接——创建复合饼图 164
2.设置企业资金结构分布图的数据系列 165
知识链接——设置数据标签选项 165
3.美化企业资金结构分布图 166
8.2 企业收支分析图表 167
8.2.1知识点及效果图 167
8.2.2操作步骤 168
1.企业收支图表分析 168
知识链接——创建双轴图表 169
2.更新企业收支图表 170
知识链接——添加数据系列 170
3.创建月利润与增长率的双轴折线图 173
知识链接——创建双轴折线图 173
8.3 创建企业收支层次分析图表 174
8.3.1知识点及效果图 174
8.3.2操作步骤 175
1.创建企业月利润分析图表 175
知识链接——创建条形图 175
2组合企业收支层次分析图表 178
知识链接——组合条形图与饼图 178
3.创建柱状-面积图分析企业收支情况 180
知识链接——创建柱状-面积图 180
使用柱状-散点图进行企业收支趋势分析 183
Chapter09使用交互图表分析数据 186
9.1 创建销售额滚动图表 186
9.1.1知识点及效果图 186
9.1.2操作步骤 186
1.定义计划与实际销售额表格中的部分字段 186
2.创建实际与计划销售额图表 187
3.创建销售额滚动图表 190
知识链接——创建滚动条 190
知识链接——设置控件格式 191
9.2 创建带下拉列表的销售统计图表 192
9.2.1知识点及效果图 192
9.2.2操作步骤 192
1创建各产品销售额比例图 193
知识链接——INDEX（）函数 193
2编辑各产品销售额比例图 194
3.创建带下拉列表的各产品销售额比例图 194
知识链接——在图表中创建组合框 195
知识链接——设置组合框 195
9.3 创建带复选框的销售统计图表 196
9.3.1知识点及效果图 196
9.3.2操作步骤 197
1.为销售统计表部分数据定义名称 197
知识链接——根据内容定义名称 197
2.创建动态销售统计图中的复选框 198
知识链接——创建复选框 198
知识链接——设置复选框的格式 199
3.动态显示各产品的销售统计情况 201
更改折线图中的数据点 204
Chapter10数据的假设检验分析 206
10.1 产品生产的单、双因素差分析 206
10.1.1知识点及效果图 206
10.1.2操作步骤 206
1.产品生产的单因素方差分析 207
知识链接——单因素方差分析 207
2产品生产的可重复双因素方差分析 209
知识链接——可重复双因素分析 209
3.产品生产的无重复双因素方差分析 210
知识链接——无重复双因素分析 210
10.2 产品生产的t检验法 211
10.2.1知识点及效果图 211
10.2.2操作步骤 211
1.平均值的成对二样本的t检验假设方法 211
2.使用平均值的成对二样本的t检验方法测试产品稳定性 212
知识链接——平均值的成对二样本分析 212
3.使用双样本等方差假设法测试 214
知识链接——t-检验：双样本等方差假设 214
4.使用双样本异方差假设法测试 215
知识链接——t-检验：双样本异方差假设 215
10.3 产品生产的Z检验和F检验分析 216
10.3.1知识点及效果图 217
10.3.2操作步骤 217
1.产品生产的Z检验法 217
知识链接ABS（）SQRT（）函数 218
2.产品生产的双样本平均差检验 220
知识链接——应用Z检验法 220
3.产品生产的F检验法 221
知识链接——应用F检验法 222
F检验分析的更改 223
Chapter11规划求解与最优化方案选择 223
11.1 生产成本最小化规划求解 226
11.1.1知识点及效果图 226
11.1.2操作步骤 226
1.加载““规划求解”分析工具 227
知识链接——加载“规划求解”分析工具 227
2.创建成本最小化求解模型 228
3.进行生产成本最小化规划求解 229
知识链接——创建规划求解 229
知识链接——设置规划求解选项 229
知识链接——添加约束条件 229
4.创建成本最小化规划求解报告 231
知识链接——生成敏感性报告 232
11.2 利润最大化规划求解 233
11.2.1知识点及效果图 233
11.2.2操作步骤 234
1.创建销售额利润最大化求解模型 234
2.进行销售利润最大化规划求解 235
3.创建利润最大化规划求解报告 237
知识链接——生成运算结果报告 237
11.3创建并管理方案 239
11.3.1知识点及效果图 239
11.3.2操作步骤 239
1.将规划求解结果保存为方案 240
知识链接——创建方案 241
2.创建方案摘要 242
知识链接——创建方案摘要 243
3.合并方案 244
知识链接——合并方案 244
方案数据透视表 245
Chapter12数据的预测分析 248
12.1 使用直线法预测销售成本 248
12.1.1知识点及效果图 248
12.1.2操作步骤 248
1.线性拟合法预测销售成本 248
知识链接——数组公式的使用 249
2使用TREND（） 函数预测销售成本 252
12.2 使用指数法和图表法预测销售成本 253
12.2.1知识点及效果图 254
12.2.2操作步骤 254
1.使用指数法预测销售成本 254
知识链接——LOGEST（）函数 255
2.使用图表法预测销售成本 256
知识链接——利用趋势线预测数据 259
12.3 使用分析工具预测销售成本 262
12.3.1知识点及效果图 262
12.3.2操作步骤 262
1.“移动平均法”预测销售成本 263
知识链接——加载分析工具库 263
知识链接——“移动平均”分析工具 264
2.“指数平滑法”预测销售成本 265
知识链接——“指数平滑”分析工具 266
比较预测销售成本与实际销售成本的差异 269
Chapter13问卷调查分析 272
13.1 创建日常护肤品市场调查问卷 272
13.1.1知识点及效果图 272
13.1.2操作步骤 272
1.创建日常护肤品使用调查问卷 272
知识链接——添加各种表单控件 273
知识链接——设置表单控件 274
2.美化日常护肤品使用调查问卷 277
知识链接——插入背景 277
13.2 调查结果统计 278
13.2.1知识点及效果图 278
13.2.2操作步骤 278
1.创建资料库 278
2.设置自动接收问卷结果 279
知识链接——删除数据有效性 281
知识链接————VLOOKUP（函数 282
3调查结果统计 283
知识链接——选择性粘贴值和格式 283
13.3 问卷调查结果综合分析 284
13.3.1知识点及效果图 284
13.3.2操作步骤 284
1.我国消费者的基本资料结构分布情况 284
2.消费者肤质状况分析 288
3.各类护肤品市场拥有量分析 289
知识链接——SUMPRODUCT（）函数 291
知识链接——创建柱状-折线双轴图 292
4.主要品牌市场占有率分析 293
知识链接——创建并设置动态饼图 294
5.销售渠道分析 296
6购买因素分析 296
品牌理念的理解率比较 298
Chapter14数据的抽样分析 300
14.1 抽样调查员工的性别构成 300
14.1.1知识点及效果图 300
14.1.2操作步骤 300
1设置公式指代员工性别 300
知识链接——使用IF（）函数指代数字 301
2.随机抽样员工性别 301
知识链接——RAND（）函数 302
3.变换抽样性别的数据 302
知识链接——使用 INDEX（）函数计算数组公式 303
14.2 抽样调查员工的年龄构成 304
14.2.1知识点及效果图 304
14.2.2操作步骤 304
1随机抽样员工年龄 304
知识链接——随机抽样公式的应用 304
2.创建频数分布表 306
知识链接——FREQUENCY（）函数 306
3.创建各年龄段员工分布图 307
14.3 通过抽样分析工具分析员工的工资 310
14.3.1知识点及效果图 310
14.3.2操作步骤 310
1.通过抽样统计法分析员工工资 310
知识链接——抽样分析工具 311
2.通过抽样回归分析企业员工的工资 314
知识链接——回归分析工具 316
置信工资区间 318
Chapter15企业销售统计与图表分析 320
15.1 按商品类别进行统计分析 320
15.1.1知识点及效果图 320
15.1.2操作步骤 320
1.按商品类型进行分类汇总 320
知识链接——移动和复制工作表 321
2.创建按类别统计销售额图表 323
知识链接——用汇总结果创建簇状柱形图 323
3.创建销售量结构分析图表 325
知识链接——用汇总结果创建分离型三维饼图 325
15.2 铵销售员和商品型号进行统计分析 327
15.2.1知识点及效果图 327
15.2.2操作步骤 327
1.创建按销售员分析工作表 328
知识链接——SUMPRODUCT（）函数 328
2.创建各销售员销售各类产品统计图表 329
知识链接——创建并设置簇状柱形图 329
3.创建各销售员销售业绩比例图表 330
知识链接——创建并设置分离型三维饼图 330
15.3 日销售额走势分析 332
15.3.1知识点及效果图 332
15.3.2操作步骤 332
1.对销售类别进行自定义排序 332
知识链接——自定义排序 333
2.对日销售记录表进行分类汇总 334
知识链接——嵌套分类汇总 335
3.创建日总销售额和总销售量走势图 336
知识链接——手动设置折线图 338
按商品类型统计数据 341
Chapter16设备采购情况统计与分析 344
16.1 企业采购清单的汇单求解 344
16.1.1知识点及效果图 344
16.1.2操作步骤 344
各类用品购买成本的单级分类汇总 344
2.各采购日期内采购数据的二级分类汇总 346
3.使用分类汇总函数对采购数据进行统计 348
知识链接——删除分类汇总 349
知识链接——利用SUBTOTAL（）函数求和 349
知识链接——创建组 351
16.2 各用品采购数据的透视分析 352
16.2.1知识点及效果图 352
16.2.2操作步骤 352
1.创建采购数据透视表 353
2设置采购数据透视表 354
4知识链接——在区域间移动字段 354
知识链接——选择值显示方式 356
知识链接——设置透视表数字格式 356
3.美化采购数据透视表 357
16.3 各用品采购数据的透视图描述 358
16.3.1知识点及效果图 358
16.3.2操作步骤 358
1.创建采购数据透视图 358
2.设置采购数据透视图 359
知识链接——堆积柱形图的应用 360
知识链接——更改坐标轴数字格式 361
知识链接——在透视图中设置筛选 363
办公用品数量透视图 364