Part 01 基础学习 1
Chapter 01 电子表格的基本操作 1
1.1 工作簿的操作 2
1.1.1 新建工作簿 2
1.1.2 保存工作簿 6
1.1.3 设置工作簿配色方案 9
1.2 工作表的操作 13
1.2.1 重命名工作表 13
1.2.2 设置工作表标签的颜色 14
1.2.3 插入与删除工作表 14
1.2.4 移动与复制工作表 16
1.2.5 隐藏和显示工作表 18
1.2.6 调整工作表的显示比例 19
1.2.7 更改工作簿默认包含的工作表数量 20
1.3 单元格的操作 20
1.3.1 插入单元格、行或列 20
1.3.2 删除单元格、行或列 24
1.3.3 合并单元格 26
1.3.4 调整行高和列宽 27
1.3.5 隐藏含有重要数据的行或列 29
Chapter 02 数据的输入与编辑 31
2.1 输入各种不同类型的数据 32
2.1.1 输入文本数据 32
2.1.2 输入数值 34
2.1.3 输入日期 37
2.1.4 输入特殊符号 38
2.2 以填充的方式批量输入数据 39
2.2.1 快速填充相同的数据 39
2.2.2 填充有规律的数据 40
2.2.3 “自动填充选项”功能的使用 41
2.2.4 自定义填充序列 42
2.3 编辑数据 44
2.3.1 修改数据及设置数据格式 44
2.3.2 移动、复制、粘贴、删除数据 46
2.3.3 查找和替换数据 51
2.3.4 设置数据的有效性 56
Chapter 03 使用公式进行运算的基础知识 61
3.1 使用公式进行数据计算 62
3.1.1 公式概述 62
3.1.2 函数及参数的说明 62
3.1.3 公式的运算符 63
3.1.4 输入公式 64
3.1.5 编辑公式 66
3.2 公式中对数据源的引用 67
3.2.1 引用相对数据源及公式的复制 67
3.2.2 引用绝对数据源及公式的复制 68
3.2.3 引用当前工作表之外的单元格 69
3.2.4 引用其他工作簿中的数据源 71
3.3 公式中对名称的使用 72
3.3.1 将单元格区域定义为名称 72
3.3.2 使用定义的名称 73
3.3.3 将公式定义为名称 76
3.3.4 名称管理 76
3.4 运用公式审核工具 78
3.4.1 查看当前工作表中的所有公式 78
3.4.2 追踪引用单元格 79
3.4.3 追踪从属单元格 79
3.4.4 使用“错误检查”功能辅助查找公式错误的原因 79
3.4.5 通过“公式求值”功能逐步分解公式 80
3.5 了解使用公式计算时经常出现的几种错误值 81
3.5.1 “＃DIV/0 ！”错误值 81
3.5.2 “＃N/A”错误值 81
3.5.3 “＃NAME？”错误值 82
3.5.4 “＃NUM！”错误值 83
3.5.5 “＃VALUE！”错误值 83
3.5.6 “＃REF！”错误值 84
Chapter 04 Excel中各函数功能解析 85
4.1 函数基础 86
4.1.1 通过“函数帮助”学习函数 86
4.1.2 函数类型 87
4.2 数学和三角函数 88
4.2.1 数学函数 88
4.2.2 三角函数 97
4.3 文本函数 99
4.4 逻辑函数 108
4.5 日期与时间函数 110
4.5.1 日期函数 110
4.5.2 时间函数 116
4.6 统计函数 117
4.6.1 平均值函数 117
4.6.2 最大值与最小值函数 121
4.6.3 数目统计函数 123
4.6.4 排位统计函数 125
4.6.5 概率分布函数 128
4.6.6 检验函数 134
4.6.7 方差、协方差与偏差函数 136
4.6.8 相关系数函数 138
4.6.9 回归分析函数 139
4.7 财务函数 141
4.7.1 折旧计算函数 141
4.7.2 投资计算函数 145
4.7.3 偿还率计算函数 151
4.7.4 债券计算及其他金融函数 152
4.8 查找函数 162
4.9 信息函数 169
4.10 数据库函数 172
Chapter 05 使用图表直观反映数据 178
5.1 了解图表 179
5.1.1 了解图表的构成 179
5.1.2 常用图表及应用范围概述 180
5.2 以图表形式表现数据 186
5.3 修改图表 188
5.3.1 图表大小和位置的调整 188
5.3.2 图表的复制和删除 190
5.3.3 图表类型的更改 190
5.4 编辑图表 192
5.4.1 添加图表标题 192
5.4.2 设置图例的显示位置 193
5.4.3 编辑图表坐标轴 194
5.4.4 编辑图表数据系列 197
5.4.5 图表中对象边框、填充效果的设置 200
5.5 套用图表样式以快速美化图表 206
Chapter 06 使用数据透视表和数据透视图分析数据 207
6.1 数据透视表的基础知识 208
6.1.1 数据透视表的作用及应用范围 208
6.1.2 数据透视表的结构 209
6.1.3 数据透视表中的各个要素 209
6.2 创建数据透视表 211
6.2.1 单一行标签数据透视表 211
6.2.2 多重行标签数据透视表 213
6.3 编辑数据透视表 213
6.3.1 调节字段的布局 213
6.3.2 更改默认的汇总方式 215
6.3.3 更改数据透视表的值显示方式 217
6.3.4 更改字段的分类汇总方式 218
6.3.5 移动、更新及删除数据透视表 219
6.4 数据透视表的排序 220
6.4.1 按数值字段进行排序 220
6.4.2 设置双标签时数据字段的排序 220
6.5 数据透视表的筛选 222
6.5.1 通过行（列）标签筛选查看数据 222
6.5.2 添加筛选字段查看数据 223
6.5.3 筛选出销售额前3名的记录 223
6.6 数据透视表布局及样式的快速套用 224
6.6.1 数据透视表布局更改 224
6.6.2 数据透视表样式的套用 226
6.7 数据透视图的建立 226
6.7.1 建立数据透视图 226
6.7.2 编辑数据透视图 227
6.7.3 对数据透视图进行筛选 229
6.7.4 更改数据透视图的图表类型 230
Part 02 技巧点拨 231
Chapter 07 文本、逻辑函数应用技巧 231
7.1 文本函数应用技巧 232
1．利用CONCATENATE函数的合并功能自动生成订单编号 232
2．利用LEFT、FIND函数从商品全称中提取其产地信息 232
3．利用CONCATENATE、LEFT、IF函数随意提取有用的数据并合并 233
4．利用IF、LEN函数检验具有固定长度的字符串输入的位数是否正确 233
5．利用IF、MID、LEN函数从身份证号码中提取出生年份 234
6．利用多个提取函数配合IF函数从身份证号码中提取完整的出生年月日 234
7．利用IF、LEN、MID、MOD函数从身份证号码中判断性别 235
8．解决一个MID函数返回值错误的问题 236
9．利用REPT函数一次性输入多个相同的符号 236
10．利用LEN、LEFT、RIGHT函数将8位电话号码的区号与号码分开 237
11．分离7位、8位混合显示的电话号码的区号与号码 237
12．利用SUBSTITUTE函数去掉文本中的所有空格 238
13．利用SEARCH、MID、LEN函数将字符串中数据信息建立为数据表 238
14．SUBSTITUTE函数的嵌套使用 239
15．利用LEN、SUBSTITUTE函数计算各门课程的实际参加人数 240
7.2 逻辑函数应用技巧 241
1．利用IF函数判断给定值是否在指定区间内 241
2．利用IF、LEFT函数根据左起代码返回部门名称 241
3．利用IF、AND函数返回考评结果 241
4．利用OR、AND函数对考核成绩进行综合评定 242
5．利用IF、OR函数识别产品类别 242
6．使用数组公式比较两组庞大的数据 243
7．利用多个逻辑函数实现根据产品的名称与颜色进行一次性调价 243
8．利用IF函数计算个人所得税 244
9．配合利用多个逻辑函数与MOD函数判断给定年份是否闰年 246
Chapter 08 日期与时间函数应用技巧 247
8.1 日期函数应用技巧 248
1．返回当前日期与星期数 248
2．利用DATE函数计算某年的天数 248
3．已知一年中的第几天，计算其准确日期 248
4．使用DATE、TODAY函数创建倒计时牌 249
5．将文本日期转换为标准日期 249
6．利用TODAY函数进行账龄分析 249
7．利用DAYS360函数计算总借款天数 251
8．利用DAYS360函数判断借款是否逾期 251
9．利用DAYS360函数计算固定资产的已使用月份 251
10．利用MONTH、TODAY函数自动填写报表中的月份 252
11．利用DATEDIF函数根据出生日期快速得出年龄 252
12．利用DATEDIF函数计算总借款天数（“＊月＊天”的形式） 252
13．用DATEDIF函数计算精确工龄（“＊年＊个月＊日”形式） 253
14．利用DATEDIF函数根据工龄自动追加工龄工资 253
15．快速返回值班日期对应的星期数 253
16．实现返回指定日期对应的中文星期数 254
17．利用YRAR与TODAY函数计算出年龄 254
18．计算请假天数占全年工作日的百分比 255
19．计算指定日期到月底的天数 255
20．实现查询指定年份中各月的天数 255
21．根据系统日期返回当前月份的各个日期 256
22．根据系统日期返回当前月份各个日期对应的星期数 257
8.2 时间函数应用技巧 259
1．计算出召开会议所耗的小时数 259
2．计算出召开会议所用的分钟数 259
3．计算出召开会议所耗的秒数 259
4．返回当前系统的时间 260
Chapter 09 数字、统计函数应用技巧 261
9.1 数学函数应用技巧 262
9.1.1 求和函数 262
1．利用SUM函数统计某一经办人的总销售金额 262
2．利用SUM函数统计某两位（或多位）经办人的总销售金额 262
3．使用SUM函数求取不同课程和人数的总金额 263
4．使用SUM函数统计不同时间段、不同类别产品的销售金额 263
5．汇总各个月份的销售总额 264
6．利用SUMIF函数统计某个时段的销售总金额 265
7．使用SUMIF函数统计两种类别或多种类别产品总的销售金额 265
8．在SUMIF函数中使用通配符 266
9．使用SUMIFS函数实现多条件统计 266
10．使用SUMIFS函数统计某一日期区间的销售金额 266
11．利用SUMPRODUCT函数统计指定类别产品的总销售金额 267
12．使用SUMPRODUCT函数统计某两位（或多位）经办人的总销售金额 267
13．统计指定部门获取奖金的人数（去除空值） 268
14．统计指定班级分数大于指定值的人数 268
15．从学生档案表中统计某一出生日期区间中指定性别的人数 269
16．统计指定专柜指定类别产品的销售金额合计值 269
17．使用SUMPRODUCT函数统计非工作日的销售金额 269
9.1.2 其他数学函数 270
1．ABS函数在数据比较中的应用 270
2．ABS函数在其他函数中的套用 270
3．利用CEILING函数根据通话总秒数计算总费用 271
4．解决浮点运算造成ROUND函数计算不准确的问题 272
5．使用INT、ROUND、TEXT函数让返回值保留指定位数 272
6．使用ROUNDUP函数计算物品的快递费用 273
7．使用MOD函数计算加班时长 273
8．将小写金额转换为大写金额 274
9.2 统计函数应用技巧 274
9.2.1 平均值函数 274
1．求平均值时忽略计算区域中的0值 274
2．利用AVERAGE函数按指定条件求平均值 275
3．利用AVERAGE函数对同时符合多个条件的数据求平均值 275
4．统计指定月份的平均销售金额 275
5．统计数据表中前5名的平均值 276
6．利用AVERAGEIF函数计算符合条件的数据的平均值 276
7．在AVERAGEIF函数中使用通配符 277
8．使用AVERAGEIFS函数计算出符合多重条件的数据的平均值 277
9．在AVERAGEIFS函数中使用通配符 278
9.2.2 最大值与最小值函数 278
1．利用LARGE函数按指定条件返回最大值 278
2．利用LARGE函数求解指定部门中前3名销售额之和 279
3．利用SMALL函数按指定条件返回最小值 279
4．利用SMALL函数求解指定部门中后3名销售额之和 279
5．利用MAX函数按条件求取最大值 280
6．忽略0值求最小值 280
9.2.3 数目统计函数 281
1．使用COUNT函数按条件统计 281
2．利用COUNTA函数统计参会人数 281
3．利用COUNTBLANK函数统计缺考人数 282
4．使用COUNTIF函数统计出大于指定数值的记录条数 282
5．利用COUNTIF函数返回大于某个单元格中的值的记录条数 282
6．利用COUNTIF函数返回大于平均值的记录条数 283
7．在COUNTIF函数中使用通配符 283
8．利用COUNTIF函数统计出某一数据区间内的记录条数 284
9．统计出满足两个或多个值的记录条数 284
10．COUNTIFS函数中对时间的限定 284
11．统计出一组数据中出现次数最多的数据 285
12．一次性统计出一组数据中各数据出现的次数 285
9.2.4 排位统计函数 286
1．对不连续单元格排名次 286
2．解决当出现相同名次时默认名次数的问题 286
3．将不在同列中的数据统一排名 286
4．只显示符合条件的排名 287
Chapter 10 财务函数应用技巧 288
10.1 折旧计算函数应用技巧 289
1．利用直线法计算出固定资产的每月折旧额 289
2．利用直线法计算出固定资产的每天折旧额 289
3．利用固定余额递减法计算出固定资产的每年折旧额 289
4．利用固定余额递减法计算出固定资产的每月折旧额 290
5．利用年限总和法计算出固定资产的每年折旧额 290
6．利用双倍余额递减法计算出固定资产的每年折旧额 290
7．利用直线法计算累计折旧额 291
8．利用余额递减法计算累计折旧额 291
9．利用年限总和法计算累计折旧额 292
10.2 投资计算函数应用技巧 293
1．按季度（月）支付时计算每期应偿还额 293
2．利用公式复制的方法快速计算各期偿还额中包含的本金额 294
3．利用公式复制的方法快速计算各期偿还额中包含的利息额 294
4．计算住房贷款中每月还款的利息额 295
5．计算住房公积金的未来值 295
6．计算某项投资的投资期数 295
7．按月统计投资额现值 296
8．计算投资期内指定月份要支付的利息额 296
10.3 偿还率计算函数应用技巧 297
1．计算某项投资的内部收益率 297
2．计算某项投资的修正内部收益率 297
3．计算某项借款的收益率 298
4．计算某项保险的收益率 298
Chapter 11 查找、数据库函数应用技巧 299
11.1 查找和引用函数应用技巧 300
1．求取一组数据的反转数据 300
2．使用COLUMN函数建立有规律的三级序列编号 300
3．使用ROW函数建立有规律的三级序列编号 300
4．ROW函数配合其他函数对特定行求和 301
5．使用ROW函数自动控制要显示的行数 301
6．使用LOOKUP函数（向量型语法）实现自动查询 302
7．使用LOOKUP函数（数组型语法）实现自动查询 303
8．使用HLOOKUP函数实现查询 304
9．使用VLOOKUP函数进行查询 305
10．使用VLOOKUP函数进行反向查询 305
11．配合使用INDEX与MATCH函数实现查询 306
12．配合使用INDEX与MATCH函数实现双条件查询 307
13．配合使用INDEX与MATCH函数实现反向查询 307
14．使用INDEX配合多个函数实现查询符合同一条件的所有记录 308
15．使用OFFSET函数实现动态查询 309
11.2 数据库函数应用技巧 310
1．使用DSUM函数实现双条件计算 310
2．统计去除指定条件的记录 310
3．在DSUM函数中使用通配符 311
4．避免DSUM函数的模糊匹配 311
5．在DAVERAGE函数中使用通配符 312
6．使用DAVERAGE函数实现计算后查询 313
7．使用DCOUNT函数实现双条件统计 313
8．从成绩表中统计出某一分数区间的人数 314
9．忽略0值统计记录条数 314
10．使用DCOUNTA函数实现双条件统计 314
11．统计各班成绩最高分 315
Chapter 12 图表创建及编辑技巧 316
12.1 图表创建及数据源的更改技巧 317
1．更改连续显示的图表数据源 317
2．更改不连续显示的图表数据源 318
3．按实际需要更改系列的名称 319
4．利用复制和粘贴的方法向图表中添加数据 319
5．使用颜色标记的区域向图表中添加数据 320
6．更新图表中的数据 320
7．删除图表中的数据 320
8．选择不同工作表的数据源创建图表 320
9．一张图表中使用两种图表类型 321
10．在图表中绘制出被隐藏的数据源 322
11．以空值替代数据源的空值 323
12．将创建的图表保存为模板 324
13．使用模板新建图表 324
12.2 图表基本编辑技巧 325
1．固定图表的大小与位置 325
2．快速复制图表格式 325
3．切换行列以改变图表的表达重点 325
4．更改图表的默认图表类型 326
5．隐藏工作表中的图表 326
6．保护图表不被随意更改 327
7．只允许对工作表中指定的图表进行编辑 327
12.3 坐标轴设置技巧 327
1．实现让水平轴从右向左显示 327
2．删除（恢复）坐标轴的显示 328
3．删除垂直轴线条 328
4．设置水平轴标签竖排显示 329
5．重新设置刻度值的数字格式 329
6．根据需求更改图表刻度线标签的位置 330
7．重新更改分类轴标签的值 331
8．更改垂直轴与水平轴的交叉位置 331
9．设置垂直轴显示在右侧 332
10．设置水平轴标签显示在刻度线上 332
11．设置图表水平轴显示在图表上方 333
12．解决条形图垂直轴标签显示次序颠倒的问题 333
13．使用对数刻度绘制图表 334
12.4 图表数据系列设置技巧 335
1．在添加的数据标签前显示图例项 335
2．重新建立图表数据源与数据标签的链接 336
3．隐藏特定的数据系列 336
4．重新显示被隐藏的数据系列 336
5．设置数据系列分离显示 337
6．设置数据系列重叠显示 337
7．更改数据系列的显示次序 338
8．设置饼图第一扇区的起始角度 338
9．将饼图中的特定扇面分离出来 338
10．重新设置复合饼图中第二绘图区的大小 339
Part 03 行业应用 340
Chapter 13 公式／函数／图表在创建、查询与分析员工档案中的应用 340
范例效果 341
范例概述 341
范例制作 341
13.1 创建员工档案管理表 341
13.1.1 规划员工档案管理表的框架 341
13.1.2 设置表格区域单元格格式及数据有效性 344
13.1.3 设置公式自动返回相关信息 348
1．设置返回性别、年龄、出生日期的公式 348
2．设置计算工龄及工龄工资的公式 350
范例应用 351
13.2 从庞大的档案数据表中查询任意员工的档案 351
13.2.1 建立员工档案查询表框架 351
13.2.2 设置单元格的格式 353
13.2.3 实现快速查询 354
13.3 分析企业员工的年龄层次 355
13.3.1 建立数据透视表分析员工年龄层次 355
13.3.2 建立数据透视图直观地显示各年龄段人数的占比情况 358
13.4 分析企业员工的学历层次 358
13.4.1 建立数据透视表分析员工的学历层次 358
13.4.2 建立数据透视图直观地显示各学历人数的占比情况 360
范例扩展 361
1．利用函数创建文件／资料借阅管理表 361
2．利用函数创建电话记录管理表 361
Chapter 14 公式／函数／图表在员工考勤记录、统计与分析中的应用 363
范例效果 364
范例概述 364
范例制作 364
14.1 创建员工考勤表 364
14.1.1 创建可选择年、月的考勤表表头 364
14.1.2 设置根据当前年、月自动显示当月日期及对应的星期数 367
1．建立公式返回指定月份中对应的日期与星期数 367
2．设置“星期六”、“星期日”显示为特殊颜色 369
3．验证考勤表的表头 371
14.1.3 创建员工考勤记录表表体 372
范例应用 374
14.2 统计和分析员工考勤情况 374
14.2.1 统计各员工本月请假的天数、迟到次数及出勤率 374
1．建立分析标识 374
2．统计各类别请假的天数、迟到次数及出勤率 375
14.2.2 分析各部门的出勤情况 377
1．建立数据透视表分析各部门的出勤情况 377
2．建立数据透视图直观地比较各部门员工的出勤情况 380
范例扩展 381
1．利用函数创建员工加班记录表 381
2．利用函数创建员工加班时长统计表 381
Chapter 15 公式／函数／图表在产品销售数据统计、分析与查询中的应用 382
范例效果 383
范例概述 383
范例制作 383
15.1 创建产品销售数据统计表 383
15.1.1 新建销售记录表并设置表格格式 383
15.1.2 建立企业产品备案表 384
15.1.3 设置公式自动返回产品信息 384
范例应用 386
15.2 统计各类别产品的销售数据 386
15.2.1 使用SUMIF函数建立公式返回统计结果 386
15.2.2 用图表直观地显示各类别产品的销售占比 387
15.3 分析各销售人员的销售业绩 389
15.3.1 统计出各销售员的销售数量与销售金额 389
15.3.2 计算出员工的业绩奖金 390
15.3.3 用图表直观地显示员工的业绩奖金占比 391
15.4 统计各销售人员销售各品种产品的金额 392
15.5 查询各产品的最高销售价格及对应的销售员 395
15.6 查询某个类别产品的所有销售记录 398
15.6.1 建立按类别查询销售记录的工作表 398
15.6.2 建立公式实现自动查询 399
范例扩展 402
1．利用数据透视表创建客户购买产品数量分析表 402
2．利用图表创建各客户应收账款分析图表 402
Chapter 16 公式／函数／图表在产品库存管理、查询与分析中的应用 403
范例效果 404
范例概述 404
范例制作 404
16.1 建立产品库存数据统计报表 404
16.1.1 新建出入库数据统计报表并设置表格格式 404
16.1.2 建立企业产品备案表 405
16.1.3 设置公式自动返回产品信息 405
范例应用 407
16.2 按品牌汇总出入库数据 407
16.2.1 计算各品牌产品的出入库合计数 407
16.2.2 用图表直观地显示各类产品的出库金额 410
16.3 用数据透视表按日期查询出入库数据 411
16.4 出入库累计汇总 413
16.4.1 建立库存汇总统计表 413
16.4.2 汇总出入库数量和金额 414
1．在“出入库数据记录表”中定义名称以方便公式的引用 414
2．设置计算各产品出入库数据的公式 414
16.5 根据本期出入库数据计算销售成本、利润 416
16.5.1 建立表格计算各产品的销售成本及利润 416
16.5.2 建立图表直观地比较各产品的销售成本率 419
范例扩展 420
1．利用函数获取最低采购价与供应商信息 420
2．利用函数获取各类产品采购金额的占比信息 421
3．利用数据透视表创建供应商供货数据分析表 421
Chapter 17 动态图表的应用分析——按钮调节式 422
范例效果 423
范例概述 423
范例制作 423
17.1 选择某公司对各月份数据进行比较 423
17.1.1 建立动态图表数据源 423
17.1.2 添加控件按钮 425
17.1.3 建立按钮调节式动态图表 427
17.2 选择某月份对各公司数据进行比较 428
17.2.1 建立动态图表的数据源 428
1．在“数据表”工作表中定义名称 428
2．通过设置公式建立动态数据源 430
17.2.2 添加控件按钮 431
17.2.3 建立按钮调节式动态图表 431
范例应用 432
17.3 按公司及月份对比数据 432
17.3.1 查看同一公司不同月份的数据 432
17.3.2 查看同一月份中不同公司的数据 433
范例扩展 434
创建“单选项”式产品销售统计动态图表 434
Chapter 18 动态图表的应用分析——下拉菜单式 435
范例效果 436
范例概述 436
范例制作 436
18.1 动态图表数据系列的建立 436
18.1.1 建立辅助序列 436
18.1.2 将图表数据系列定义为名称 437
18.2 使用定义的名称建立图表 438
18.3 添加控件控制数据的显示 440
18.3.1 控制“销售金额”、“首付金额”、“销售套数”数据 440
18.3.2 控制“一居”、“二居”等户型 441
18.4 图表优化设置 442
18.4.1 添加文本框到图表 442
18.4.2 添加百分比数据标签并显示两位小数 443
范例应用 444
18.5 查看不同选项下的图表 444
范例扩展 444
创建“滚动条”式利息与本金计算动态图表 444