Chapter 01
公式和函数的基础知识
1.1 什么是Excel公式..................................... 2
1.2 函数真的有那么神秘吗......................... 3
1.2.1 函数的种类............................................... 3
1.2.2 函数的组成............................................... 4
1.2.3 函数应该这样解读.................................... 4
1.3 公式和函数的用途................................... 5
1.3.1 强大的计算能力........................................ 5
1.3.2 便捷的数据分析和处理能力..................... 7
1 统计.............................................................. 7
2 查询.............................................................. 8
3 分析.............................................................. 8
4 替换.............................................................. 8
5 查找.............................................................. 9
Chapter 02
轻松输入公式和函数
2.1 公式的输入方法..................................... 11
2.1.1 公式的构成..............................................11
2.1.2 公式运算符的分类...................................11
1 算数运算符..................................................11
2 比较运算符................................................. 12
3 文本运算符................................................. 13
4 引用运算符................................................. 13
2.1.3 快速输入单元格名称...............................13
1 不用单元格中的实际数据编写公式............ 13
2 单元格名称的由来...................................... 14
3 在公式中引用单元格....................................15
2.1.4 输入公式的注意事项............................... 16
2.2 公式的复制与填充................................. 17
2.2.1 复制公式..................................................17
1 使用快捷键复制...........................................17
2 使用功能按钮复制.......................................17
2.2.2 填充公式................................................. 19
2.3 单元格引用方式..................................... 19
2.3.1 相对引用................................................. 19
2.3.2 绝对引用................................................. 20
2.3.3 混合引用..................................................21
2.3.4 F4键的妙用............................................ 22
2.4 输入函数的技巧..................................... 23
2.4.1 手动输入函数.......................................... 23
2.4.2 根据函数类型选择函数........................... 24
1 在函数库组中选择函数............................... 24
2 在插入函数对话框中选择函数.................... 25
2.4.3 自动输入常用函数.................................. 26
1 对数据进行快速求和.................................. 26
2 一秒计算平均值.......................................... 27
3 快速提取最大值和最小值........................... 27
2.4.3 函数嵌套完成复杂计算........................... 27
00 Excel高效办公应用宝典 文前_001-014.indd 6 2019/4/9 下午5:21
·7·
目录
Chapter 03
公式的编辑和错误检查
3.1 错误公式的重新编辑............................ 30
3.2 公式的转换.............................................. 31
3.2.1 公式和值自由转换...................................31
3.2.2 将公式转换成文本.................................. 32
1 在公式之前加特定符号............................... 32
2 在文本格式下输入公式............................... 32
3 使用分列功能实现转换............................... 33
3.2.3 将公式转换成结果值.............................. 34
3.3 查看公式中指定部分的计算结果..... 35
3.4 隐藏表格中的公式................................. 35
3.5 快速选中公式中引用的单元格.......... 37
3.6 公式中常见错误类型的分析.............. 37
1 #####错误............................................... 37
2 #VALUE!错误............................................. 38
3 #DIV/O!错误............................................... 39
4 #NAME?错误.............................................. 39
5 #N/A错误................................................... 39
6 #REF!错误..................................................40
7 #NUM!错误.................................................40
8 #NULL!错误................................................40
3.7 隐藏公式返回的错误值.......................40
1 使用条件格式隐藏错误值...........................40
2 使用函数隐藏错误值.................................. 42
3 将错误值打印成空白................................... 42
3.8 启用公式审核功能................................. 43
3.8.1 自动错误检测功能.................................. 43
3.8.2 检查错误公式..........................................44
3.8.3 复杂公式分步验证..................................44
3.8.4 单元格追踪............................................. 45
1 追踪错误公式............................................. 45
2 追踪引用单元格..........................................46
3 追踪从属单元格..........................................46
Chapter 04
数组公式的应用
4.1 什么是数组..............................................48
1 常量数组.....................................................48
2 区域数组.....................................................48
3 内存数组.....................................................48
4.2 快速输入数组公式.................................48
1 使用数组公式进行多项计算.......................48
2 输入多单元格数组公式...............................49
3 多单元格数组公式的实际应用....................49
4.3 数组之间的运算规则............................ 50
1 数组的表现方式.......................................... 50
2 结果区域的判断...........................................51
3 单元素数组与数组之间的运算.....................51
4 相同方向的一维数组运算............................51
5 不同方向一维数组之间的运算.................... 52
6 一维数组与二维数组之间的运算................ 52
7 二维数组之间的运算.................................. 52
4.4 修改和删除数组公式............................ 53
Chapter 05
名称的应用和管理
5.1 名称的作用和命名的限制................... 56
5.1.1 名称的作用............................................. 56
00 Excel高效办公应用宝典 文前_001-014.indd 7 2019/4/9 下午5:21
·8·
目录
1 便于理解公式............................................. 56
2 简化公式..................................................... 56
5.1.2 定义名称的限制...................................... 56
5.2 定义名称的方法..................................... 57
5.2.1 定义单元格区域名称............................... 57
5.2.2 定义公式名称.......................................... 58
5.2.3 定义组合区域名称.................................. 59
1 为联合引用区域命名................................... 59
2 为交叉引用区域命名................................... 59
5.2.4 定义常量和常量数组名称....................... 60
5.2.5 批量自动创建名称.................................. 61
5.3 名称的编辑.............................................. 62
5.3.1 修改名称................................................. 62
5.3.2 筛选名称................................................. 62
5.3.3 删除名称................................................. 63
5.4 名称的应用.............................................. 63
5.4.1 在公式中使用名称.................................. 63
1 手动输入..................................................... 63
2 粘贴名称.....................................................64
5.4.2 建立照片查询系统.................................. 65
5.4.3 使用名称制作实时动态图表................... 67
Chapter 06
严谨的统计函数
6.1 根据不同要求计算平均值................... 70
6.1.1 轻松求取平均值...................................... 70
6.1.2 计算去除0值的平均值.............................71
6.1.3 计算可见数据的平均值........................... 72
6.1.4 AVERAGEA函数空值也能被计算............ 73
6.1.5 按条件求平均值...................................... 73
6.1.6 只统计业绩达标的平均销售额................ 74
6.1.7 根据模糊匹配条件计算平均值................ 75
6.1.8 多条件计算平均值.................................. 76
6.1.9 计算指定年龄段的平均年龄................... 77
6.1.10 计算去掉最高分和最低分的平均值....... 77
6.2 巧用函数统计单元格个数................... 78
6.2.1 统计实际工作天数.................................. 78
6.2.2 统计指定商品销售记录...........................80
6.2.3 统计非空单元格个数...............................80
6.2.4 COUNTBLANK函数统计缺席人数........... 81
6.2.5 统计满足要求的单元格个数................... 82
6.2.6 统计两列中相同数据个数....................... 83
6.2.7 COUNTIF函数统计空单元格个数............84
6.2.8 真空单元格和假空单元格的统计............ 85
6.2.9 统计包含指定范围数值的单元格个数..... 86
6.2.10 使用&符号合并条件.............................. 86
6.2.11 统计指定多个错误代码出现的.
总次数................................................... 87
6.2.12 用好通配符也能统计单元格个数..........88
6.2.13 统计包含指定单元格内容的
单元格个数............................................ 89
6.2.14 统计文本单元格个数............................. 89
6.2.15 多条件计数选COUNTIFS函数............... 90
6.2.16 根据多组条件统计销售记录.................. 91
6.3 一眼看穿最大值或最小值................... 91
6.3.1 MAX函数找出最大值.............................. 92
6.3.2 根据指定条件返回最大值....................... 93
6.3.3 根据业绩完成率计算员工提成................ 93
6.3.4 从指定区域数据中提取最大值................ 94
6.3.5 从非空单元格区域中提取最大值............ 94
00 Excel高效办公应用宝典 文前_001-014.indd 8 2019/4/9 下午5:21
·9·
目录
6.3.6 DMAX函数根据条件求指定列中的.
最大值..................................................... 95
6.3.7 MEDIAN函数提取中间值........................ 96
6.3.8 计算普遍基本工资.................................. 97
6.3.9 MIN函数准确挑出最小值........................ 98
6.3.10 只对完成业绩的员工发放提成.............. 98
6.3.11 MAX函数与MIN函数合作计算选手
最终得分............................................... 99
6.3.12 自动更新日期到月底时自动停止......... 100
6.3.13 根据工龄计算年假天数....................... 100
6.3.14 根据指定条件求最小值........................101
6.3.15 统计非空单元格区域的最小值.............101
6.4 排位统计函数的实际应用.................102
6.4.1 RANK函数根据选手成绩排名............... 102
6.4.2 根据销售金额查询员工销售名次.......... 103
6.4.3 为月销售额排名.................................... 104
6.4.4 使用RANK函数实现中国式排名............ 105
6.4.5 提取排名第2的选手得分....................... 105
6.4.6 计算销售金额前5名的平均值............... 106
6.4.7 提取倒数第2名的面试评分................... 107
6.4.8 计算金额最低的3笔费用总和............... 107
Chapter 07
踏实本分的文本函数
7.1 文本长度怎么计算...............................109
7.1.1 LEN函数快速统计字符个数.................. 109
7.1.2 LENB函数计算字节数............................110
7.1.3 提取需要的数据..................................... 111
7.2 数据合并就这么简单.......................... 111
7.2.1 “万能胶”&符号.....................................112
7.2.2 使用&符号统计学生学号.......................112
7.2.3 试用CONCATENATE函数合并文本........113
7.2.4 提取货物的生产日期..............................115
7.2.5 计算员工是否发放奖金..........................115
7.2.6 特殊的文本连接函数PHONETIC............116
7.3 精确定位文本位置............................... 117
7.3.1 FIND函数精确定位字符位置..................117
7.3.2 查找指定字符串.....................................117
7.3.3 按指定位置查找指定字符串................. 118
7.3.4 比FIND更高级的SEARCH函数...............119
7.3.5 使用通配符进行查找.............................119
7.3.6 FINDB及SERCHB函数快速定位字节..... 120
7.4 提取指定字符串................................... 121
7.4.1 提取字符串的前几个字符......................121
7.4.2 提取字符串的后几个字符..................... 122
7.4.3 从指定位置提取字符............................ 122
7.4.4 按字节数提取字符................................ 123
7.5 替换指定字符........................................124
7.5.1 REPLACE函数按指定位置进行替换...... 124
7.5.2 REPLACEB函数按字节替换字符........... 125
7.5.3 使用SUBSTITUTE函数替换.
指定内容............................................... 125
7.5.4 批量替换不同内容................................ 126
7.6 英文输入不再手忙脚乱.....................126
7.6.1 UPPER函数让英文小写变大写.............. 126
7.6.2 LOWER函数让英文大写变小写............. 127
7.6.3 只将首字母大写.................................... 127
7.7 TEXT函数的七十二变........................128
7.7.1 日期格式听我的.................................... 129
7.7.2 快速判断企业产值................................ 129
00 Excel高效办公应用宝典 文前_001-014.indd 9 2019/4/9 下午5:21
·10·
目录
7.7.3 按加班时长统计奖金............................. 130
7.7.4 巧用占位符规范数据格式..................... 130
7.7.5 金额大写不求人.................................... 132
Chapter 08
聪明能干的查找函数
8.1 从参数列表中查找对应信息............134
8.1.1 从编码快速判断商品类别..................... 134
8.1.2 使用CHOOSE函数对学生成绩
进行评级............................................... 134
8.1.3 根据身份证号快速判断性别................. 135
8.2 MATCH函数查找数据定位...............136
8.2.1 MATCH函数查找数值位置.................... 136
8.2.2 快速盘算指定数据是否存在................. 137
8.2.3 重复数据一招现形................................ 138
8.3 从工作表中查找指定数据.................139
8.3.1 VLOOKUP函数精确查找指定数据........ 139
8.3.2 VLOOKUP函数模糊匹配查找数据........ 141
8.3.3 查询指定内容的多项结果..................... 141
8.3.4 HLOOKUP函数按指定行进行查找........ 142
8.3.5 LOOKUP一个函数两种参数.................. 143
1 向量形式................................................... 143
2 数组形式................................................... 143
8.3.6 INDEX函数查找行列交叉处的值........... 145
1 数组形式................................................... 145
2 引用形式................................................... 145
8.4 常见的引用函数...................................147
8.4.1 OFFSET函数的引用原则....................... 147
8.4.2 AREAS函数计算公式引用
单元格区域...........................................148
8.4.3 TRANSPOSE函数的绝技—
“乾坤大挪移”...................................... 149
Chapter 09
思维清晰的逻辑函数
9.1 判断高手IF函数.................................... 152
9.1.1 是非对错IF函数来判断.......................... 152
9.1.2 多条件判断也在行................................ 152
9.1.3 你的积分能兑换哪些商品..................... 153
9.1.4 瞬间洗白错误值.................................... 154
9.2 使用函数判定条件是否成立............ 155
9.2.1 AND函数满足所有条件才可以.............. 155
9.2.2 IF函数让AND函数更好地表达自己....... 156
9.2.3 OR函数满足一个条件就可以................ 156
9.2.4 判断谁有资格获得奖金......................... 158
9.2.5 倔强的NOT函数.................................... 158
9.2.6 一个公式判断哪些员工未通过考核...... 160
Chapter 10
应用频繁的数学函数
10.2 求和界的网红明星............................162
10.1.1 SUM函数轻松做加法.......................... 162
10.1.2 计算学生总成绩.................................. 164
10.1.3 使用SUMIF函数按条件求和................ 164
10.1.4 只对大于某数值的数据求和................ 165
10.1.5 按给定条件进行求和........................... 165
00 Excel高效办公应用宝典 文前_001-014.indd 10 2019/4/9 下午5:21
·11·
目录
10.1.6 按商品统计销量.................................. 166
10.1.7 对模糊查找的数据进行求和................ 167
10.1.8 大范围条件区域求和........................... 168
10.1.9 使用SUMIF函数参与运算.................... 169
10.1.10 按多个指定条件进行求和................. 169
10.2 实用的乘法运算函数....................... 171
10.2.1 乘法运算找PRODUCT..........................171
10.2.2 计算3月份总产量................................ 173
10.2.3 快速计算连续自然数的乘积................ 173
10.2.4 两张数据表也难不住PRODUCT.......... 174
10.2.5 一个函数搞定先乘后加的混合运算..... 174
10.3 取整取零你说了算............................ 175
10.3.1 两数相除只取整数.............................. 175
10.3.2 两数相除四舍五入............................... 177
10.3.3 MOD函数计算剩余金额...................... 178
10.3.4 从商品编码中提取信息....................... 180
10.3.5 促销商品的付款方式—遇到零头
直接进1............................................... 180
10.3.6 ROUNDUP函数与ROUNDDOWN.
函数的使用.......................................... 181
Chapter 11
颇有时间观念的日期与
时间函数
11.1 根据日期或时间代码提取信息.....183
11.1.1 轻松提取年份...................................... 183
11.1.2 计算中秋节是星期几...........................184
11.1.3 计算端午节是一年中的第几周............184
11.2 预算指定日期之后的某一天.........185
11.2.1 EDATE函数预算项目竣工日期............ 185
11.2.2 EOMONTH函数计算最后还款期限..... 186
11.2.3 WORKDAY函数计算活动截止日期..... 187
11.2.4 计算实际休息日预算交货日期............ 188
11.2.5 用TEXT函数快速获取星期几.............. 189
11.3 计算当前日期和时间.......................190
11.3.1 快速录入当前日期............................... 190
11.3.2 快速输入当前日期和时间.................... 190
11.3.3 计算下一届奥运会距今还有几个月......191
11.3.4 计算商品陈列天数.............................. 192
11.4 计算某个具体日期或时间..............192
11.4.1 DATE函数返回规范的日期.................. 192
11.4.2 TIME函数提取标准的时间.................. 193
11.5 计算两个日期之间相差的天数.....194
11.5.1 计算本月实际工作天数....................... 194
11.5.2 计算每周单休时的本月工作天数........ 195
11.5.3 统计全年有多少个休息日.................... 196
11.6 时间函数常用实例............................196
11.6.1 计算退休日期...................................... 196
11.6.2 根据日期计算员工工龄....................... 197
11.6.3 计算工资发放时间............................... 197
Chapter 12
使用公式设置条件格式
和数据验证
12.1 条件格式的应用............................... 200
12.1.1 使用内置条件格式..............................200
1 突出显示库存量大于1000的单元格.........200
2 使用数据条显示订购趋势.........................200
00 Excel高效办公应用宝典 文前_001-014.indd 11 2019/4/9 下午5:21
·12·
目录
12.1.2 自定义条件格式.................................. 201
1 查找唯一值............................................... 201
2 突出显示最近7天的记录...........................202
12.2 公式在条件格式中的应用..............203
12.2.1 标记重复数据...................................... 203
12.2.2 突出显示每个品牌最高销售量............204
12.2.3 突出显示抽检不达标的品牌................ 205
12.2.4 突出显示周末日期...............................206
12.2.5 突出显示7天内可完工的项目.............. 207
12.2.6 编辑条件格式规则..............................208
12.2.7 使用条件格式屏蔽错误值...................209
12.3 数据验证的基本应用.......................210
12.3.1 使用下拉列表输入性别....................... 210
12.3.2 限制只能向单元格中输入日期.............211
12.4 使用公式控制数据验证.................. 212
12.4.1 使用数据有效性限制输入重复值........ 212
12.4.2 禁止对指定区域数据进行修改............ 213
12.4.3 禁止在单元格中输入空格................... 214
12.4.4 限制只能在单元格中输入文本............ 215
12.4.5 保证输入的身份证号码是18位........... 215
12.4.6 设置只能输入当前日期....................... 216
12.4.7 使用数据验证制作多级下拉列表........ 217
Chapter 13
VBA与宏
13.1 宏是什么.............................................. 222
13.1.1 宏是如何运行的.................................. 222
13.1.2 宏是如何生成的.................................. 222
1 录制宏....................................................... 222
2 编写宏代码............................................... 222
13.2 宏的录制及执行................................223
13.2.1 添加“开发工具”选项卡.................... 223
13.2.2 录制宏................................................. 223
13.2.3 在Excel中运行宏................................. 225
1 通过对话框执行宏.................................... 225
2 通过图形执行宏........................................226
3 通过窗体按钮执行宏................................ 227
13.2.4 宏的查看及编辑.................................. 228
13.2.5 保存包含宏的文件............................... 229
13.3 设置宏安全性.....................................230
13.4 加载宏的方法.....................................230
13.4.1 Excel自带的加载宏............................. 231
13.4.2 来自外部的加载宏............................... 231
13.4.3 添加录制的加载宏............................... 232
13.5 VBA的编辑环境................................232
13.5.1 什么是VBE.......................................... 232
13.5.2 菜单栏.................................................234
13.5.3 工具栏.................................................234
13.5.4 工程窗口的构成..................................234
1 Microsoft Excel对象................................234
2 模块.......................................................... 235
3 用户窗体...................................................236
4 类模块.......................................................236
13.5.5 属性窗口............................................. 236
13.5.6 代码窗口............................................. 237
13.5.7 立即窗口............................................. 237
13.5.8 Visual Basic帮助的妙用.....................238
13.6 VBA编程的基本步骤.......................239
13.6.1 编写一个简单的VBA程序.................... 239
13.6.2 使用控件执行VBA程序.......................240
00 Excel高效办公应用宝典 文前_001-014.indd 12 2019/4/9 下午5:21
·13·
目录
Chapter 14
VBA编程知识准备
14.1 常用术语介绍.....................................243
14.1.1 VBA变量.............................................243
1 变量的命名规则........................................243
2 变量的声明方法........................................243
14.1.2 VBA常量.............................................244
14.2 VBA常用语句.................................... 244
14.2.1 判断语句.............................................244
1 单条件判断语句........................................244
2 多条件判断语句........................................245
14.2.2 循环语句.............................................246
1 指定循环次数...........................................246
2 未知循环次数............................................248
14.2.3 With语句.............................................249
14.2.4 跳转语句............................................. 251
1 发生错误时转移........................................ 251
2 发生错误时忽略........................................ 252
14.3 VBA中的运算符................................ 252
1 算术运算符............................................... 252
2 比较运算符............................................... 252
3 逻辑运算符............................................... 253
4 连接运算符............................................... 253
14.4 VBA函数的应用................................254
14.4.1 使用VBA函数...................................... 254
14.4.2 调用工作表函数.................................. 254
14.4.3 自定义函数.......................................... 255
14.4.4 常用对话框函数.................................. 256
1 MsgBox函数............................................. 256
2 InputBox函数........................................... 257
14.5 VBA对象、属性和方法...................258
14.5.1 Application对象.................................. 258
14.5.2 Workbook对象.................................... 259
14.5.3 Worksheet对象..................................260
14.5.4 对象的属性.......................................... 261
14.5.5 对象的方法.......................................... 262
Chapter 15
窗体和控件的应用
15.1 窗体的插入和控件工具箱的
显示...................................................... 264
15.1.1 窗体的插入..........................................264
15.1.2 工具箱的显示或隐藏........................... 265
15.2 关闭和移除窗体................................266
15.2.1 窗体窗口的控制..................................266
15.2.2 显示窗体.............................................266
15.2.3 移除窗体............................................. 267
15.3 窗体的设置......................................... 268
15.3.1 修改窗体名称......................................268
15.3.2 设置窗体背景......................................268
15.3.3 设置窗体标题...................................... 270
1 方法一....................................................... 270
2 方法二....................................................... 270
15.4 窗体事件.............................................. 271
15.4.1 Initialize事件....................................... 271
15.4.2 Activate和Deactivate事件................ 271
15.4.3 QueryClose和Terminate事件............ 272
1 QueryClose事件....................................... 272
2 Terminate事件......................................... 272
00 Excel高效办公应用宝典 文前_001-014.indd 13 2019/4/9 下午5:21
·14·
目录
15.5 窗体控件的常用类型.......................... 273
15.5.1 控件的插入及删除.............................. 273
1 插入控件................................................... 273
2 删除控件...................................................274
15.5.2 标签控件............................................. 274
15.5.3 文本框控件.......................................... 275
15.5.4 复选框控件.......................................... 276
15.5.5 列表框控件.......................................... 277
15.5.6 复合框控件.......................................... 277
15.5.7 命令按钮控件...................................... 278
15.5.8 选项按钮控件...................................... 278
15.5.9 图像控件............................................. 279
15.6 设置控件公用属性........................... 280
15.6.1 设置控件大小和位置...........................280
15.6.2 设置控件字体格式.............................. 281
15.7 响应控件事件.....................................283
15.8 调整Tab键顺序.................................283
Chapter 16
VBA在Excel中的
实际应用
16.1 对工作表的操作............................... 286
16.1.1 插入工作表..........................................286
1 插入单个工作表........................................286
2 一次插入多张工作表................................286
3 在指定工作表位置插入新工作表..............287
16.1.2 为工作表命名...................................... 287
16.1.3 选择工作表.........................................288
1 选择单个工作表........................................288
2 选择多个工作表........................................288
16.1.4 隐藏工作表..........................................288
1 隐藏工作表...............................................288
2 取消工作表隐藏........................................289
16.1.5 删除工作表..........................................290
16.2 对单元格的操作................................290
16.2.1 选择单元格..........................................290
1 选择单个单元格........................................290
2 选择连续的单元格区域.............................292
3 选择不连续的单元格区域.........................292
16.2.2 选择整行或整列.................................. 293
1 选择行.......................................................293
2 选择列.......................................................294
3 选择不连续的行或列................................ 295
16.2.3 选择整个工作表.................................. 295
16.2.4 在单元格中输入数据...........................296
16.2.5 清除单元格内容.................................. 297
16.3 制作工作压力电子调查问卷........ 298
1 制作调查问卷标题....................................299
2 制作个人信息部分....................................299
3 设置单项选择部分....................................300
4 设置多项选择部分.................................... 301
5 设置工作感悟部分....................................302
6 完善调查问卷表........................................302
7 制作按钮提交调查问卷结果.....................303
附录：Excel常用快捷键............................ 306