第1章 定义Excel数据分析环境 1
分类检索 1
用户环境 1
建立数据表格 1
安全保护数据 1
名词术语 2
Microsoft账户 2
工作表标签 2
功能区／选项卡／组 2
合并单元格／合并后居中／跨越合并 2
001用户操作环境设置 3
实例1-1：Microsoft用户账户设置 3
实例1-2：定制Office背景和Office主题 5
实例1-3：设置默认工作表数与标准字体 6
实例1-4：自由控制功能区显示 8
实例1-5：自定义选项卡和组 11
实例1-6：自定义界面显示元素 13
002Excel数据表格的建立 15
实例1-7：创建员工通讯录工作表 15
实例1-8：创建周末加班申请表 17
003数据的安全保护 19
实例1-9：设置密码保护员工工资工作簿 19
实例1-10：将部门工作分配工作簿标记为最终状态进行保护 21
实例1-11：保护面试最终结果工作表限制人员编辑 23
实例1-12：设置只允许填写考核成绩表中的成绩 24
第2章 数据的输入 27
分类检索 27
各类数据的输入 27
规律与非规律数据的输入 27
有效数据的输入 28
不同的输入方式 28
名词术语 28
数据类型 28
文本型数字 29
科学计数 29
004数字和文本的输入 29
实例2-1：输入以001开头的学员编号 30
实例2-2：轻松输入身份证号 30
实例2-3：登记职员住房面积时自动插入小数点 32
实例2-4：横向登记员工当月工资明细 33
005日期和时间的输入 34
实例2-5：登记销售流水账 34
006分数的输入 35
实例2-6：会议出席情况统计 35
007金额的输入 36
实例2-7：快速填制费用报销单中的大写金额 36
008特殊字符的输入 37
实例2-8：真写员工考勤记录 37
实例2-9：制作转账凭证 38
009相同数据的输入 39
实例2-10：同职位岗位工资的快速填制 40
实例2-11：快速制作多份记账凭证表单 40
010规律数据的输入 41
实例2-12：复制填充连续的调薪时间 42
实例2-13：序列填充连续的产品编号 43
实例2-14：自动为日常工作日程表填充工作日 44
实例2-15：快速输入几个一组的固定商品名称 45
实例2-16：员工姓与名的快速分列输入 46
011随机数据的输入 47
实例2-17：使用RAND()函数输入随机座位编号 47
012有效数据的输入 48
实例2-18：输入不超过10分的有效评分 48
实例2-19：输入不在当前日期范围的值班日期 49
实例2-20：输入不重复的员工工号 50
实例2-21：有效输入不小于当前时间的签到 51
实例2-22：输入不超过总预算额的单项订购金额 52
实例2-23：最高采购量输入提醒 53
013列表选择输入 54
实例2-24：从右键下拉列表中选择年龄段 54
实例2-25：使用有效性列表选择输入所属部门 55
实例2-26：快速选择员工出生省份和对应的城市 56
014选择性粘贴输入 58
实例2-27：选择性粘贴将各分公司营业收入金额由元转换为万元 59
015记录单输入 60
实例2-28：使用记录单登记产品入库情况 60
第3章 格式化数据让其更易分析 62
分类检索 62
数据的对齐与排列 62
动动鼠标让数据显示更明确 62
让数据按个人意愿显示 62
按条件突出显示数据 63
名词术语 63
货币 63
千位分隔符 63
占位符 63
条件格式 64
数据条 64
色阶 64
图标集 64
公式 64
016数据的对齐 64
实例3-1：对齐产品销售单价中小数点 65
017数据的排列 65
实例3-2：让日记账中的月／日分列显示 66
实例3-3：换行显示计量单位 68
实例3-4：实现销售统计表的行列转换 68
实例3-5：比较产销记录表中两个间隔较远的字段 69
018调整小数位数 70
实例3-6：快速增加员工离职率小数位 70
实例3-7：保证销售回款率计算结果的精确度 71
019数据的货币显示 72
实例3-8：为金额添加人民币符号 72
实例3-9：为工资表中的工资数额应用会计专用格式 74
020显示千位分隔符 75
实例3-10：给应收账款添加千位分隔符以便查看 75
021用文本占位符自动添加文本 76
实例3-11：在产品编号中自动添加固定型号 76
022数字占位符的格式占位 78
实例3-12：将客户编号用“0”补足为4位 78
实例3-13：用“#”统一金额格式 79
023字符的重复显示 80
实例3-14：用“＊”重复填充隐私信息 80
024按条件显示格式 81
实例3-15：用盈利／亏损替代具体的金额数字 82
025灵活显示日期时间 82
实例3-16：客户来电日期时间的灵活设置 83
026将满足一定范围的数据以某种格式显示 84
实例3-17：突出显示能承受的贷款每月固定偿还额 85
实例3-18：标识本周新进人员 86
实例3-19：将兼职人员区别显示 87
实例3-20：突出显示重复的电话号码 88
实例3-21：将积分排名前10％的员工以特定格式显示 89
实例3-22：找出工资表中高于平均收入的员工 90
027将数值大小以数据条长短反映 91
实例3-23：使用实心数据条分析产品销售状况 92
实例3-24：用负值条形图表现收入盈亏 92
实例3-25：隐藏工资条形图的工资数额 94
028将数值高低以色阶过渡呈现 95
实例3-26：用双色刻度分析员工培训成绩 95
实例3-27：用三色刻度分析产品需求量 96
029用图标区分不同范围的数据 97
实例3-28：以箭头形状明示职工业绩等级 97
实例3-29：用象限图标识项目完成进度 98
030自定义为满足公式条件的数据设置格式 99
实例3-30：合同到期的提前提醒 99
实例3-31：最低库存预警 101
第4章 数据的查找与更新 103
分类检索 103
“查找”对话框查找 103
名称与记录单查找 103
定位查找 103
名词术语 104
查找／替换／转到／定位 104
常量 104
关键字 104
匹配查找 105
空值 105
在多个单元格中输入数据 105
031关键字查找 106
实例4-1：查找并快速更新订购商品的单位 106
032按格式查找 107
实例4-2：查找最新进货的生鲜类食品 108
033模糊查找 109
实例4-3：查找护理头部的洗护商品 110
034匹配查找 110
实例4-4：查找已到使用期限的固定资产 110
035名称查找 111
实例4-5：快速查找并更新折扣率 111
036记录单查找 112
实例4-6：使用记录单浏览修改固定资产表 112
实例4-7：查找数量在2台以上的记录 114
037差异查找 115
实例4-8：使用差异查找辅助对账 115
038查找数字 116
实例4-9：查找零售价格表中的单价并统一应用货币格式 116
039查找公式结果 118
实例4-10：查看近期商品出库金额 118
040查找错误值 119
实例4-11：查找排名表中出现的公式错误 119
041查找空值 120
实例4-12：在现有库存的空单元格中快速输入“缺货”字样 120
第5章 排序、筛选和分类汇总 123
分类检索 123
数据的排序 123
数据的筛选 123
数据的分类汇总 124
名词术语 124
关键字／字段 124
排序／筛选／分类汇总 125
分类汇总／分级显示 125
合并计算 125
042单一条件排序 125
实例5-1：将员工健康检查评分由高到低排列 126
043多条件排序 126
实例5-2：按入职时间与当前岗位薪资排序员工信息表 126
044按汉语拼音排序 128
实例5-3：按考场人员姓名的汉语拼音排序编制考场名单 128
045按笔划排序 130
实例5-4：按考场人员姓名的笔划编制考场名单 130
046自定义排序 132
实例5-5：使用自定义排序制作部门工作计划 132
047不规则编号排序 133
实例5-6：按办公用品编号排序整理办公用品表 134
048按颜色排序 136
实例5-7：通过颜色排序区分待办事项是否紧急 136
049按图标排序 138
实例5-8：按图标排序查询日常费用开支程度 138
050按行排序 140
实例5-9：按行排序员工奖金评估表 140
051对合并单元格数据排序 142
实例5-10：将小组当月销售额由高到低排序 142
052自动筛选 144
实例5-11：自动筛选统计公司男员工人数 144
053包含合并单元格的自动筛选 145
实例5-12：筛选查看某日所有工作内容 145
054搜索筛选 149
实例5-13：筛选当月全勤人员 149
实例5-14：筛选某月转正的所有人员记录 149
055两个条件内的自定义筛选 150
实例5-15：查看年龄在201到25之间的员工信息 151
056“且”规则的高级筛选 152
实例5-16：按一段时间的消费次数、消费金额划分客户等级 152
057“或”规则的高级筛选 154
实例5-17：对迟到5次以上、缺席2天或业务额低于1万的员工予以劝退 154
058混合规则的高级筛选 155
实例5-18：查询具有法律专业本科学历或有事业单位工作经验的人员 156
059计算条件的高级筛选 157
实例5-19：筛选工龄超过单位平均工龄的人员记录 157
060不重复记录的高级筛选 159
实例5-20：筛选本月有订单的货品名 159
061简单分类汇总 160
实例5-21：汇总统计不同职位的平均工资 161
062选择多个汇总项的分类汇总 162
实例5-22：统计不同部门的基本工资和工资总额 162
063多重分类汇总 164
实例5-23：统计不同部门的工资总额和平均值 164
064嵌套分类汇总 166
实例5-24：汇总统计各部门中不同职位的工资状况 166
065手动建立分级显示 169
实例5-25：以上、中、下旬时间查询当月销售记录 169
066自动生成分级显示 171
实例5-26：自动建立分级显示划分客户所在区域 171
067按位置合并计算 172
实例5-27：按位置合并计算一季度各商品订单额 173
068按分类合并计算 175
实例5-28：按分类合并计算一季度各商品订单额 175
第6章 交互式分析的数据透视表 179
分类检索 179
数据透视表的创建与刷新 179
数据透视表的整理与查看 179
数据透视表的排序与筛选 179
数据透视表的组合与计算 180
数据透视表的多表分析 180
名词术语 180
数据透视表 180
源数据 180
字段 181
数据项 181
汇总函数 181
行字段／列字段／值字段 181
筛选器 181
切片器 181
日程表 181
069数据透视表的建立 182
实例6-1：建立员工档案数据透视表 182
070快速统计重复项 183
实例6-2：快速统计公司人员情况 184
071调整字段排列多维度分析 185
实例6-3：多角度分析公司人员构成 185
072自动刷新数据透视表 187
实例6-4：自动刷新员工档案数据透视表 187
073以不同汇总方式统计 188
实例6-5：以不同方式统计物品库存情况 189
074以不同显示方式分析 190
实例6-6：统计不同物品库存比例 190
075查看数据透视表明细数据 192
实例6-7：查看某商品不同规格型号的库存明细 192
076更改透视表数据源 194
实例6-8：对滞销品清理库存后重新更新透视表 194
077在数据透视表中排序 195
实例6-9：对现有库存距安全库存的情况进行排序 196
078报表筛选实现分页浏览 197
实例6-10：按缺货物品的紧急状态进行筛选 197
079使用筛选按钮筛选 199
实例6-11：对订购额在一定金额以上的正价商品免运费 199
080使用切片器直观筛选数据 200
实例6-12：按商品名、订购日期查询负责商品的销售员 200
081使用日程表实现时段筛选 203
实例6-13：使用日程表查看商品订购情况 203
082组合透视表中的日期项 204
实例6-14：按月份查看商品的订购情况 204
083在透视表中添加计算字段 206
实例6-15：按月计算各商品的订购额 206
084数据透视表的多表分析 208
实例6-16：建立双透视表分析公司产品的订购情况 208
第7章 统计分析、假设检验与数据预测 210
分类检索 210
统计分析 210
假设检验 210
数据预测 211
名词术语 211
方案 211
规划求解 211
描述统计 212
直方图 212
排位 212
相关性 212
抽样 212
回归分析 212
单变量求解 213
模拟运算表 213
方差 213
t/Z/F检验 213
移动平均法 213
指数平滑法 213
085最优方案分析 214
实例7-1：公司销售的最优方案分析 214
086规划求解最优解 216
实例7-2：求解利润最大化生产方案 216
实例7-3：合理安排员工值班 218
087描述统计数据 220
实例7-4：描述统计分析职员工资分布情况 220
088直方图分段统计发生频率 221
实例7-5：直方图分析公司人员流动情况 222
089排位 223
实例7-6：根据全年业绩排位发放年终奖 223
090抽样分析 225
实例7-7：抽样分析公司工资水平 225
实例7-8：抽样调查产品的市场满意度 227
实例7-9：未收款项的抽样调查分析 229
091相关性与回归分析 231
实例7-10：卖场面积与营业额的相关性分析 231
实例7-11：公司产品生产与销售成本的回归分析 232
092单变量求解 234
实例7-12：使用单变量求解实现目标存款额的最低月储蓄额 234
093单变量模拟运算表 235
实例7-13：建立销售员提成的单变量模拟运算表 236
094双变量模拟运算表 237
实例7-14：建立公司固定资产折旧的双变量模拟运算表 237
095单因素方差分析 239
实例7-15：不同行业收入状况的单因素方差分析 239
096双因素方差分析 241
实例7-16：工人和机器设备对产量影响的双因素方差分析 241
实例7-17：产品品牌和销售地区对销量影响的双因素方差分析 242
097t检验 243
实例7-18：产品标准性的t检验 244
实例7-19：t检验分析两种血压测定仪检测值是否有明显差别 245
098Z检验 246
实例7-20：Z检验法分析产品生产情况是否符合设计标准 246
099F检验 248
实例7-21：部门工作效率的F检验分析 248
100时间序列的趋势外推预测 249
实例7-22：使用趋势线预测明年销售额 250
101时间序列的移动平均预测 252
实例7-23：利用移动平均法预测来年工资增长率 252
实例7-24：利用二次移动平均法预测的未来两年办公费用 253
102时间序列的指数平滑预测 255
实例7-25：利用指数平滑法预测公司明年利润额 256
实例7-26：公司产品销量的二次指数平滑预测 258
第8章 数据计算 260
分类检索 260
公式的输入、编辑与复制 260
单元格引用 260
名称的应用 260
数组常景和数组公式 261
错误分析与检查 261
名词术语 261
公式 261
运算符及运算符优先级 261
A1与R1C1引用样式 262
相对引用、绝对引用、混合引用以及三维引用 262
名称 263
数组、数组常量以及数组公式 263
迭代／循环引用 263
103输入与编辑公式 264
实例8-1：输入编辑公式计算产品销售额 264
104复制公式 266
实例8-2：使用“填充柄”复制公式计算销售额 267
实例8-3：使用“粘贴”功能复制公式计算销售额 268
105自动求和、求平均值计算 269
实例8-4：使用自动求和计算材料采购金额 269
实例8-5：使用“自动求和”统计成绩 270
106相对引用计算 272
实例8-6：使用相对引用根据单价和销量计算销售额 272
107绝对引用计算 274
实例8-7：使用绝对引用计算不同商品相同折扣下的销售额 274
108混合引用计算 275
实例8-8：使用混合引用计算同一商品不同折扣不同销售下的销售额 276
109三维引用计算 277
实例8-9：在工资表中使用三维引用计算工龄工资 277
110使用名称计算 279
实例8-10：定义名称计算固定资产计提折旧额 279
实例8-11：根据所选内容创建名称计算工资总额 282
111运用数组常量计算 284
实例8-12：使用数组常量计算员工缴纳个人所得税 284
112运用数组公式计算 287
实例8-13：运用数组公式计算销售商品总额 287
实例8-14：运用数组公式统计满足条件的员工姓名 288
实例8-15：运用数组公式制作日期 289
113启用迭代计算 290
实例8-16：使用迭代计算生成不变的随机数 291
实例8-17：使用迭代计算获取输入时间 294
114式审核 296
实例8-18：审核固定资产计提折旧额公式的正确性 296
实例8-19：修改显示错误值的公式 297
第9章 数据处理函数 300
分类检索 300
数据的逻辑判断与处理函数 300
日期数据的处理函数 300
文本数据的处理函数 300
财务数据处理函数 301
数据查找函数 301
数据集中趋势统计函数 301
数据离散趋势分析统计函数 302
数据概率统计函数 302
数据汇总计算函数 302
名词术语 302
集中趋势 302
离散趋势 303
概率 303
115数据的逻辑判断与处理 303
实例9-1：按年均消费额划分客户等级 304
实例9-2：判断累积积分是否可以领取季度奖 304
实例9-3：判断培训成绩是否合格 305
116处理日期数据 306
实例9-4：计算工作年限 306
实例9-5：计算报表当前填制时间 307
实例9-6：计算转正日期 308
实例9-7：计算项目执行天数 309
117处理文本数据 310
实例9-8：从身份证号中获取出生信息 310
实例9-9：将金额按设置格式显示 311
实例9-10：从产品编号中获取产品类别 312
118处理财务数据 313
实例9-11：计算固定资产线性折旧费 313
实例9-12：使用固定余额递减法计算折旧值 315
实例9-13：计算公司贷款每期偿还额 316
实例9-14：计算投资的未来值 317
实例9-15：计算某项目投资的现值和净现值 317
实例9-16：计算一系列现金流的内部收益率 318
119查找数据 319
实例9-17：查找某积分对应的奖金金额 320
实例9-18：根据员工编号显示员工个人信息 320
实例9-19：计算哪一个月完成了目标 322
实例9-20：查找游戏点数可兑换的礼物 323
实例9-21：从电话簿中提取姓名 324
实例9-22：将满足一定条件的员工调配到集团工作 325
120集中趋势统计 325
实例9-23：算术与几何平均值计算 326
实例9-24：计算众数 327
实例9-25：计算中位数 328
121离散趋势统计 328
实例9-26：计算方差 329
实例9-27：计算标准差 329
实例9-28：计算四分位数 330
实例9-29：计算偏态值 332
实例9-30：计算峰度值 333
122概率统计 334
实例9-31：使用二项分布检查产品合格率 334
实例9-32：使用泊松分布制定公司生产目标 335
123数据汇总计算 337
实例9-33：统计客户当月投诉次数 337
实例9-34：统计某部门业务量总额 338
实例9-35：统计女职员评为中级职称的人数 338
实例9-36：返回不同年龄段的员工人数 339
第10章 单元格图表和数据图 341
分类检索 341
迷你图 341
常用标准图表的使用 341
高级图表 342
组合图 342
动态图表 342
名词术语 343
迷你图／迷你图组 343
标准图表 343
温度计图 344
甘特图 344
瀑布图 344
漏斗图 344
阶梯图 344
柏拉图 344
124使用柱形迷你图比较数值大小 344
实例10-1：使用柱形迷你图分析每月收入状况 345
实例10-2：创建间隔时间不等的柱形迷你图分析网站访问情况 346
125使用折线迷你图分析数据变动趋势 348
实例10-3：创建折线迷你图分析一周股价变动趋势 348
126使用盈亏迷你图反映金额正负变化 350
实例10-4：创建盈亏迷你图分析信用卡是否欠款 350
127使用REPT函数自定义创建单元格迷你图 352
实例10-5：用REPT函数创建商品信用等级图 352
实例10-6：用REPT函数制作销量条形图 353
128使用柱形图纵向比较数据 355
实例10-7：创建簇状柱形图比较产品销量 355
实例10-8：创建堆积柱形图比较各分公司营业额 356
实例10-9：创建百分比堆积柱形图查看近年新进职员学历倾向 357
129使用条形图横向比较分类项 358
实例10-10：创建三维簇状条形图比较各项社团活动参与人数 358
实例10-11：创建水平圆柱图比较项目计划与实际完成情况 359
130使用折线图表现数据发展趋势 362
实例10-12：创建平滑折线图分析年度营业利润增长变动趋势 362
实例10-13：创建带数据标记的堆积折线图分析不同等级客户近年消费趋势 364
实例10-14：创建三维折线图分析两大产品线营业额成长趋势 365
131使用饼图或圆环图分析数据比例关系 367
实例10-15：创建饼图分析员工出勤状况 367
实例10-16：创建分离型饼图进行员工离职原因分析 369
实例10-17：创建复合饼图分析公司年度福利经费开支 371
实例10-18：创建双层复合饼图分析不同年龄段客户的消费能力 372
实例10-19：创建圆环图分析各子公司的销售额 375
实例10-20：创建圆环图分析各岗位工资构成 376
132使用面积图进行数据量与趋势变化分析 377
实例10-21：创建三维面积图分析公司账款回收状况 378
实例10-22：创建堆积面积图分析公司近年来客户的构成 379
133使用散点图和气泡图进行相关性分析 380
实例10-23：创建散点图分析商品售价与销量的相关性 380
实例10-24：创建带平滑线的散点图进行竞争产品销量比较 382
实例10-25：创建气泡图分析各品牌销售状况及市场地位 383
实例10-26：创建气泡图分析因广告投入不同所呈现的销售额的变化 384
134使用雷达图进行倾向／平衡分析测试 386
实例10-27：创建雷达图分析员工体能状况 386
实例10-28：创建雷达图对比A、B产品营养成分 387
135使用温度计图表分析项目完成比例 388
实例10-29：创建温度计图表分析任务完成进度 389
136使用甘特图表现项目执行进度 392
实例10-30：使用悬浮条形图查看项目阶段 392
137使用瀑布图反应数据增减变动 395
实例10-31：通过堆积柱形图创建瀑布图表现库存商品变动情况 395
实例10-32：通过涨跌柱线创建瀑布图分析现金流 396
实例10-33：创建带负值的瀑布图反映员工实发工资情况 398
138使用漏斗图表现数据阶梯型递减 402
实例10-34：创建漏斗图表现客户等级 402
139使用阶梯图分析数据阶梯变动趋势 405
实例10-35：创建阶梯图表现本年费用递增状况 405
140使用柏拉图找到对数据起关键作用的因素 409
实例10-36：创建柏拉图分析产品不良率 409
141使用不等宽柱形图表现值的范围和分布 414
实例10-37：创建不等宽柱形图反映销量与市场占有率 414
142组合图的使用 416
实例10-38：创建线－柱图比较两事业部销售额差异 417
实例10-39：创建双轴线－柱图分析商品销售与折扣率的关系 418
实例10-40：创建双轴线－柱图进行男女比例与消费状况相关分析 419
实例10-41：创建双轴线－面积图进行男女比例与薪资状况的相关分析 421
143解决值差较大的断层图的制作 423
实例10-42：用模拟Y轴法制作断层图 423
144迷你图与数据图表的组合使用 430
实例10-43：在条形图中建立迷你图分析成本变化 430
145创建动态图交互式分析数据 432
实例10-44：使用辅助区域建立动态图分析不同月份各产品订单额 433
实例10-45：定义名称建立随记录项增加的销量流水动态图 435
实例10-46：创建下拉菜单式图表分析近年各部门人数变动 437
第11章 源数据链接、嵌入与输出 440
分类检索 440
建立链接 440
嵌入或被嵌入 440
获取数据 440
共享与输出 441
名词术语 441
屏幕提示 441
嵌入与链接 441
146使用超链接链接外部数据源 442
实例11-1：制作公司员工通讯簿 442
147通过复制粘贴建立链接 444
实例11-2：制作一式三联对账单 444
148通过公式建立链接 447
实例11-3：销售汇总的跨工作簿计算 447
实例11-4：引用计算保险扣款 449
149在工作表中嵌入其他文件对象 450
实例11-5：在日常费用统计表中嵌入费用类别说明文件 450
150将工作表或图表嵌入到其他程序 452
实例11-6：将Excel销售统计图嵌入到PPT中 452
151获取数据库数据 454
实例11-7：将Access中员工记录导入到员工档案表中 454
152获取外部文本文件数据 456
实例11-8：将文本文件中本周工作计划导入到Excel日程安排表中 456
153获取网站数据 458
实例11-9：从网站获取Excel统计数据 459
154传递工作表记录到Access数据库中 460
实例11-10：将员工通讯簿导入到Access数据库中 460
155将数据表存为文本文件 463
实例11-11：将人事通报表存为文本文件 463
156将Excel数据表保存到云 465
实例11-12：将职务调动申请表保存到云 465
157邀请他人协同编辑数据 468
实例11-13：邀请他人协同进行采购物品登记 468
158数据表的打印输出 471
实例11-14：会议议程安排表的打印输出 471