第1篇 入门及函数篇 2
第1章Excel入门 2
1.1熟悉Excel界面 2
1.1.1 Excel界面浏览 2
1.1.2新界面的优势 4
1.1.3多功能的状态栏 5
1.1.4自定义用户界面 5
1.2 Excel功能区简介 6
1.2.1功能区的功能分布 6
1.2.2调用上下文选项卡 6
1.3调用快捷键 7
1.3.1万能的功能键【Alt】 7
1.3.2调用旧版菜单键 8
1.3.3自定义快捷键 8
1.4设置Excel选项 9
1.4.1 Excel选项功能介绍 9
1.4.2详解公式选项 11
1.5及时调用帮助 12
1.5.1调用函数帮助的4种方式 13
1.5.2 Excel帮助的缺点 14
第2章 初识公式 15
2.1公式的概念与结构 15
2.1.1何谓公式 15
2.1.2公式的结构 16
2.2公式的优缺点 16
2.2.1公式的应用前景 16
2.2.2公式的计算速度 17
2.2.3公式的3个缺点 17
2.2.4用VBA扩展内部公式的功能 18
2.3录入与编辑公式 18
2.3.1如何录入公式 18
2.3.2公式与文本的两种转换方式 19
2.3.3修改公式 20
2.4认识引用 21
2.4.1如何引用单元格 21
2.4.2跨表引用技巧 21
2.4.3相对引用、绝对引用与混合引用 23
2.4.4将引用转换成值 24
2.4.5区分引用与文本 24
2.5认识运算符与优先级 24
2.5.1 Excel公式所支持的运算符 24
2.5.2 Excel公式所支持的数学符号 25
2.5.3了解运算优先级 25
2.6复制公式 26
2.6.1复制公式的必要性 26
2.6.2复制公式的多种技巧 26
2.7定位公式 28
2.7.1选择所有公式 28
2.7.2隐藏所有公式 28
2.7.3删除所有值为错误的公式 29
2.8公式的错误类型 30
2.8.1公式错误的种类 30
2.8.2 #DIV/0！错误 30
2.8.3 #N/A错误 30
2.8.4 #NAME？错误 30
2.8.5 #NUM！错误 31
2.8.6 #VALUE！错误 31
2.8.7 #REF！错误 31
2.8.8检查错误 31
2.9公式求值 32
2.10迭代计算 33
2.10.1何谓迭代计算 33
2.10.2迭代计算的应用：累加 33
2.10.3迭代计算的应用：自动添加不更新的时间 34
2.10.4迭代计算的应用：解方程 35
第3章 公式的灵魂——函数 36
3.1函数的作用 36
3.1.1提升速度 36
3.1.2简化公式 37
3.1.3实现更强大的计算功能 37
3.1.4选择性地统计运算 37
3.2函数的分类 38
3.2.1按用途分类 38
3.2.2按参数分类 38
3.2.3按刷新方式分类 40
3.3函数的参数 40
3.3.1参数的作用 40
3.3.2参数的类型 40
3.3.3参数嵌套 42
3.3.4选择参数的技巧 42
3.3.5省略参数与省略参数的值 42
3.4函数的录入方式 43
3.4.1使用函数库 43
3.4.2使用插入函数向导 44
3.4.3直接录入 44
3.5函数运算中的空单元格与空文本 45
3.6函数与公式的限制 46
3.6.1数字计算精度 46
3.6.2日期与时间计算精度 46
3.6.3浮点运算偏差 47
3.6.4公式长度限制 48
3.6.5参数个数限制 49
3.6.6参数的类型限制 49
3.6.7 IF函数嵌套限制 50
3.6.8功能限制及例外项 50
第4章 数学运算 51
4.1求和运算 51
4.1.1 SUM求和 51
4.1.2 SUMIF条件求和 53
4.1.3 SUMIFS多条件求和 57
4.1.4 SUMPRODUCT乘积之和 57
4.1.5 SUMSQ求平方和 59
4.2求积运算 59
4.2.1 PRODUCT求积 60
4.2.2 MMULT两数组的矩阵积 61
4.3商与余数 65
4.3.1 QUOTIENT求商 65
4.3.2 MOD求余数 66
4.4随机数 68
4.4.1 RAND随机小数 68
4.4.2 RANDBETWEEN随机整数 70
4.5乘方与开方 71
4.5.1 SQRT开平方 71
4.5.2 POWER乘方 72
4.6数据的舍入问题 73
4.6.1 ODD/EVEN取奇数与偶数 74
4.6.2 TRUNC/INT截取为指定位数小数或整数 74
4.6.3 CEILING向上舍入为最接近基数的倍数 75
4.6.4 FLOOR向下舍入为最接近基数的倍数 76
4.6.5 ROUND四舍五入 76
4.6.6 ROUNDDOWN向下舍入 77
4.7分类汇总 78
第5章 逻辑运算 81
5.1真假值运算 81
5.1.1 TRUE/FALSE真假运算 81
5.1.2 AND/OR判断是否符合条件 83
5.1.3 NOT逻辑值求反 85
5.2条件判断 85
5.2.1 IF条件判断 86
5.2.2 IFERROR防错函数 88
第6章 文本处理 90
6.1字符码转换 90
6.1.1 CODE返回字符的数字代码 90
6.1.2 CHAR返回数字参数指定的字符 91
6.2文本合并 93
6.2.1 CONCATENATE串连字符 94
6.2.2自定义合并函数 95
6.3字符转换 97
6.3.1 PROPER将单词转换为首字母大写 97
6.3.2 UPPER/LOWER将单词转换为大写或小写 98
6.3.3 T文本检查及降维应用 99
6.3.4 REPT按指定次数重复文本 101
6.3.5 TRIM去除多余空格 103
6.3.6 TEXT格式化数字 105
6.4字符比较 112
6.5长度计算及提取指定长度的字符 114
6.5.1 LEN字符长度计算 114
6.5.2 LEFT取左边的字符 115
6.5.3 RIGHT取右边的字符 116
6.5.4 MID取中间的字符 118
6.6字符查找与替换 120
6.6.1 FIND区分大小写的字符查找 120
6.6.2 SEARCH支持通配符的字符查找 121
6.6.3 REPLACE替换指定位置的字符 123
6.6.4 SUBSTITUTE替换指定字符 124
第7章 统计运算 126
7.1平均值统计 126
7.1.1 AVERAGE统计参数的算术平均值 126
7.1.2 AVERAGEA统计包括逻辑值与文本的算术平均值 128
7.1.3 AVERAGEIF按条件统计参数的算术平均值 128
7.1.4 AVERAGEIFS按多条件统计参数的算术平均值 130
7.2数量统计 130
7.2.1 COUNT统计包含数字的单元格个数 131
7.2.2 COUNTA统计非空单元格的个数 132
7.2.3 COUNTIF统计区域中满足条件的单元格个数 134
7.2.4 COUNTIFS统计区域中满足多条件的单元格个数 138
7.3极值与中值统计 139
7.3.1 MIN统计参数中的最小值 139
7.3.2 SMALL统计数据中第k个最小值 142
7.3.3 MAX统计参数中的最大值 144
7.3.4 LARGE统计数据中第k个最大值 145
7.3.5 MEDIAN统计一组数据的中值 146
7.3.6 MODE统计一组数据中的众数 147
7.3.7 MODE.MULT统计一组数据的中值的垂直数组 148
7.4排名统计 149
7.5频率分布 151
第8章 日期和时间运算 153
8.1日期运算 153
8.1.1 TODAY获取当前系统日期 153
8.1.2 YEAR/MONTH/DAY获取年/月/日 155
8.1.3 DATE根据指定的年月日序号返回日期 157
8.1.4 EDATE返回指定日期之前或之后几个月的日期序号 158
8.1.5 EOMONTH返回指定日期之前或之后的月末日期序号 159
8.1.6 DATEDIF计算两个日期间的间隔时间 160
8.2时间运算 161
8.2.1 NOW获取当前日期和时间 161
8.2.2 HOUR/MNUTE/SECOND获取时/分/秒 163
8.3星期与工作日运算 163
8.3.1 WEEKDAY将日期转换成代表星期的序号 163
8.3.2 WEEKNUM返回日期在一年中的周数 166
8.3.3 NETWORKDAYS返回两个日期之间的完整工作日数 167
8.3.4 WORKDAY返回指定工作日之前/后的日期 169
第9章 查找与引用 170
9.1引用函数 170
9.1.1 ROW/COLUMN引用行号/列号 170
9.1.2 ROWS/COLUMNS引用行数/列数 173
9.1.3 ADDRESS返回指定行/列的引用地址 174
9.1.4 INDIRECT返回文本指定的引用 175
9.1.5 OFFSET产生指定偏移量的引用 179
9.1.6 INDEX引用区域或者数组中指定行与列的值 181
9.1.7 TRANSPOSE将引用的方向进行转置 184
9.1.8 CHOOSE从参数中引用索引值所指定的值 186
9.1.9 HYPERLINK链接文件、文件夹或者单元格 188
9.2查找函数 192
9.2.1 VLOOKUP在区域中左列查找返回右边指定列对应的值 192
9.2.2 HLOOKUP在区域中上边行查找返回下边指定行对应的值 198
9.2.3 LOOKUP从单行/单列或数组中查找值 199
9.2.4 MATCH返回值的数组或者区域中的对应位置 205
第10章 提取信息 209
10.1奇偶判断 209
10.1.1 ISODD奇数判断 209
10.1.2 ISEVEN偶数判断 211
10.2数据类型判断 211
10.2.1 ISERROR判断参数是否是错误值 211
10.2.2 ERROR.TYPE返回与错误值对应的数字 213
10.2.3 ISTEXT判断参数是否文本 214
10.2.4 ISNUM13ER判断参数是否为数字 215
10.2.5 N数字转换及数组降维 216
10.2.6 ISBLANK判断单元格是否空白 218
10.3获取单元格信息 218
10.3.1 CELL返回引用单元格的多种信息 219
10.3.2 PHONETIC获取代表拼音信息的字符串 221
第11章 财务运算 223
11.1固定资产折旧计算 223
11.1.1 DB用固定余额递减法计算资产折旧值 223
11.1.2 DDB用双倍余额递减法计算资产折旧值 224
11.1.3 SYD返回固定资产按年限总和折旧法计算的折旧金额 225
11.1.4 VDB返回资产用余额递减法计算的部分时期折旧额 226
11.2存款、贷款与利息计算 227
11.2.1 FV基于固定利率和等额分期付款方式返回投资的未来值 227
11.2.2 PV返回投资的将来偿还额的当前总值 228
11.2.3 NPER返回投资或贷款的期数 229
11.2.4 RATE返回投资或者贷款的每期实际利率 230
11.2.5 CUMIPMT返回两个付款期之间贷款累积支付的利息 231
11.2.6 CUMPRINC返回两个付款期之间贷款累积支付的本金 232
第12章 在公式中应用名称 233
12.1名称的概念 233
12.1.1名称的用途与功能 233
12.1.2名称的作用域 235
12.1.3定义名称的规则 236
12.1.4引用类名称 236
12.1.5公式类名称 237
12.2定义名称 237
12.2.1命名常量 237
12.2.2命名区域引用 238
12.2.3批量命名 239
12.2.4命名公式 240
12.2.5编辑名称 241
12.2.6复制名称的问题 242
12.2.7查看名称 243
12.2.8删除名称 246
12.2.9隐藏名称 246
12.3应用名称 247
12.3.1提升编辑效率 247
12.3.2缩减公式长度 248
12.3.3提升公式可读性 250
12.3.4突破函数限制 251
12.3.5跨表动态引用 252
第13章 宏表函数应用 254
13.1认识宏表函数 254
13.1.1什么是宏表函数 254
13.1.2宏表函数的用法 255
13.1.3宏表函数的优缺点 256
13.2宏表函数的应用案例 257
13.2.1创建工作表目录 257
13.2.2同时引用数据与单元格格式 259
13.2.3标识所有公式 263
13.2.4计算工作表的当前页及总页数 264
13.2.5计算各工作表中的图形数量 265
13.2.6计算包括新建的所有工作表的B列产量之和 267
13.2.7将表达式转换成值 268
13.2.8去除表达式中的文本并将表达式转换成值 268
13.2.9创建文件目录 270
13.2.10按分隔符拆分字符串 270
第14章 认识数组 273
14.1数组基础 273
14.1.1数组的概念与特性 273
14.1.2数组的维数 275
14.1.3三维引用解析 277
14.1.4转置数组 280
14.1.5数组与区域引用的区别 280
14.1.6数组运算与数组公式 281
14.1.7什么时候需要按三键录入数组公式 284
14.1.8不按三键也能对内存数组执行数组运算的特殊函数 287
14.2输出数组 290
14.2.1获取数组的元素 290
14.2.2重置数组大小 291
14.2.3合并引用或数组 295
14.2.4数组和引用转常量 297
第15章 数组的高级应用 298
15.1一维、二维数组应用 298
15.2三维引用 310
15.3区域数组应用 317
第2篇 图表篇 326
第16章 图表结构与分类 326
16.1图表的功能与发展 326
16.1.1图表的功能 326
16.1.2图表的发展 329
16.2图表的结构 329
16.3图表的分类 331
16.3.1柱形图 331
16.3.2折线图 333
16.3.3饼图 334
16.3.4条形图 336
16.3.5面积图 336
16.3.6散点图 337
16.3.7股价图 339
16.3.8曲面图 340
16.3.9圆环图 340
16.3.10气泡图 341
16.3.11雷达图 342
16.3.12迷你图 343
16.4如何确保图表传达正确的信息 343
16.4.1了解图表的精确度 343
16.4.2选择正确的数据源 344
16.4.3饼图的负值问题 345
16.4.4数据量与饼图 345
16.4.5饼图的精细调节 346
16.4.6合理使用散点图 346
16.4.7平滑线对图表效果的影响 346
16.4.8图表的美观样式不能喧宾夺主 347
16.4.9三维图表的缺点 347
16.4.10使用简单的图表体现复杂的数据 348
第17章 图表的基本操作 349
17.1一键生成图表 349
17.1.1了解图表相关的功能区选项卡 349
17.1.2单击生成图表 351
17.1.3使用快捷键生成图表 352
17.1.4修改默认图表类型 352
17.1.5图表位置切换 353
17.1.6使用常量数组生成图表 353
17.2编辑图表 355
17.2.1如何快速而精确地选择图表元素 355
17.2.2更新图表数据 356
17.2.3更改图表类型 358
17.2.4组合图表 358
17.2.5添加或删除图表元素 358
17.2.6处理空单元格 359
17.2.7切断图表与工作表的关联 360
17.2.8打印图表 361
17.3设置图表区格式 362
17.3.1填充背景颜色 362
17.3.2调整边框样式 365
17.3.3设置图表阴影与发光 366
17.3.4设置三维效果 367
17.3.5设置图表大小 368
17.3.6设置图表属性 368
17.3.7重设以匹配样式 369
17.4设置绘图区格式 369
17.4.1启动“设置绘图区格式”对话框 369
17.4.2设置绘图区格式 369
17.5设置数据系列格式 369
17.5.1柱形图 370
17.5.2折线图 374
17.5.3饼图 376
17.5.4面积图 378
17.5.5圆环图 379
17.5.6气泡图 380
17.6设置坐标轴格式 380
17.6.1指定纵坐标轴的最大、最小值 380
17.6.2指定纵坐标轴刻度单位 382
17.6.3修改纵坐标轴的显示单位 382
17.6.4设置纵坐标轴的数字格式 383
17.6.5设置横坐标轴刻度间隔与文字对齐方式 383
17.6.6指定横坐标轴类型 384
17.6.7设置横坐标轴标签位置 385
17.6.8设置横坐标轴数字格式 385
17.6.9纵坐标轴交叉 386
17.7设置数据标签格式 387
17.7.1指定数据标签显示内容 387
17.7.2设置数据标签位置 387
17.7.3修改数据标签的数字格式 388
17.7.4数据标签的限制 388
17.8设置网格线 389
17.8.1横纵网格线的显示与隐藏 389
17.8.2设置网格线的线条颜色与线型 389
17.9关于图表标题 389
17.10设置图例格式 389
17.10.1根据实际情况决定是否需要图例 389
17.10.2调整图例位置 390
17.11复制图表格式的方法 390
17.11.1复制图表再更新数据 390
17.11.2选择性粘贴 390
17.11.3创建图表模板 391
第18章 美化图表 393
18.1应用样式美化图表元素 393
18.1.1图表样式 393
18.1.2使用形状样式 394
18.1.3使用艺术字样式 395
18.1.4圆角边框、阴影与三维格式 395
18.1.5掌握美化程度 396
18.2合理使用图片效果 396
18.2.1用图片体现图表系列的对象 396
18.2.2用图片作为饼图切割对象 397
18.2.3成长效果图 399
18.2.4模仿图片效果的折线图 401
18.3图表条件格式 402
18.3.1用背景将图表系列三等分 402
18.3.2用线条任意分割柱形图 404
18.3.3标识最大值与最小值 405
18.3.4创建平均线 406
第19章 图表的高级设计技巧 408
19.1在图表中应用名称 408
19.1.1何时需要应用名称 408
19.1.2应用名称的注意事项 409
19.1.3创建自动适应数据变化的图表 409
19.1.4创建仅显示前十名成绩的图表 411
19.2次坐标轴应用技巧 413
19.2.1创建线柱图 413
19.2.2制作目标与实际的差异图 414
19.2.3利用坐标轴的网格线创建等分线 415
19.3使用控件强化图表 417
19.3.1控件的使用思路 417
19.3.2自由调整前N名的柱形图 418
19.3.3任意标识最大值、最小值和平均值 421
19.3.4自由筛选图表的显示项目 423
第3篇 透视表篇 428
第20章 数据透视表基本操作 428
20.1透视表概念 428
20.1.1什么是数据透视 428
20.1.2何时应该使用数据透视表 429
20.1.3透视表工具菜单 430
20.1.4数据透视表的字段分类 431
20.2如何发挥透视表的功能 433
20.2.1数据透视表的工作方式 433
20.2.2如何才能最大化发挥数据透视表的功能 433
20.3创建一个工资透视表 435
20.3.1布局 436
20.3.2生成透视表 436
20.3.3添加字段 437
20.3.4调整汇总对象 438
20.3.5确定计算的数值对象 439
20.3.6调整计算方式 440
20.3.7同时定义多个计算对象 441
20.3.8报表筛选 441
20.4透视表的相关问题 442
20.4.1推迟布局更新 442
20.4.2清除筛选与全部清除 443
20.4.3数据透视表的更新和缓存问题 443
20.4.4定位透视表部件 444
20.4.5移动数据透视表 444
20.4.6更新透视表数据源 444
20.4.7根据透视表生成明细表 445
20.4.8打印透视表 446
第21章 自定义数据透视表 447
21.1设置透视表外观样式 447
21.1.1定制单元格格式 447
21.1.2报表布局 449
21.1.3在每个项目后插入空行 450
21.1.4应用透视表样式 450
21.1.5自定义样式 451
21.1.6应用主题样式 452
21.1.7展开与折叠字段明细 452
21.1.8调整错误值与空单元格的显示方式 453
21.2调整透视方式 454
21.2.1对透视表执行标签筛选 454
21.2.2对透视表按值筛选 454
21.2.3应用切片器 455
21.2.4将所选内容分组与分类汇总 455
21.2.5将本月产量汇总表按周分组 456
21.2.6按年龄段分组查看平均工资 456
21.3调整值字段汇总与显示方式 457
21.3.1修改值字段汇总方式 457
21.3.2显示个体占总值之百分比 457
21.3.3显示与参照时间的值的百分比 458
21.3.4显示父行汇总的百分比 459
21.3.5显示与前一项的差异 459
21.3.6自定义计算字段 460
21.3.7列出计算字段的公式 463
第22章 透视表的高级应用 464
22.1多重合并计算的透视技巧 464
22.1.1何谓多重合并计算 464
22.1.2多重合并计算数据区域的透视表的特点 465
22.1.3设计具有一个页字段的平均成绩透视表 467
22.1.4设计具有两个页字段的多重合并产量透视表 470
22.1.5多工作簿汇总 472
22.2使用数据透视图 475
22.2.1透视图与图表 475
22.2.2生成透视图的方法 476
22.2.3定义透视图类型与格式 477
22.2.4应用透视图样式 478
22.2.5图表元素的控制方式 478
22.2.6更新透视图的数据源 479
22.3数据透视图的限制 479
22.3.1透视图随透视表变化 479
22.3.2透视图的类型限制 480
22.3.3数据不能自动刷新 480
22.3.4不同版本Excel中的透视图不完全兼容 480
22.3.5不能使用名称 481
第23章 练习题 482
23.1函数类 482
23.2图表类 486
23.3数据透视表类 488