第1章 菜鸟必学――函数与公式基础1
1．1 公式与函数的关系1
1．2 公式的编写与填充技巧2
1．3 函数的组成2
1．4 函数录入技巧2
1．5 函数编辑技巧3
1．6 常用函数的应用4
1．7 Excel公式运算符5
1．7．1 算术运算符6
1．7．2 比较运算符7
1．7．3 连接运算符9
1．7．4 引用运算符10
1．7．5 运算符优先顺序10
1．8 单元格引用11
1．8．1 相对引用11
1．8．2 绝对引用11
1．8．3 混合引用12
1．9 查看公式运算过程13
1．9．1 使用【公式求值】命令13
1．9．2 使用功能键F914
1．10 常用函数进阶应用15
1．10．1 SUM函数15
1．10．2 AVERAGE函数16
1．10．3 COUNT函数18
1．10．4 MAX函数19
1．10．5 MIN函数20
1．10．6 综合应用21
第2章 成为高手的必经之路――数组技术23
2．1 数组的定义23
2．2 数组维度24
2．3 数组与单元格24
2．3．1 数组与单元格区域的对应关系24
2．3．2 将数组写入单元格区域25
2．4 数组的分类25
2．5 数组运算的规律与应用26
2．5．1 一维数组与单值的运算26
2．5．2 一维数组与一维数组的运算（方向相同）27
2．5．3 一维数组与一维数组的运算（方向不同）30
2．5．4 一维数组与二维数组的运算31
2．5．5 二维数组与单值的运算32
2．5．6 二维数组与二维数组的运算34
2．6 运算符“*”“+”与数组结合的应用37
2．6．1 数组间的“乘”运算37
2．6．2 数组间的“加”运算39
2．7 数组构造42
2．7．1 用ROW函数构造垂直序列数组42
2．7．2 用COLUMN函数构造水平序列数组44
2．7．3 获取行、列数：ROWS函数与COLUMNS函数45
第3章 让电脑帮助人脑――Excel对数据的判断处理46
3．1 逻辑函数46
3．1．1 IF函数46
3．1．2 IF函数的嵌套应用48
3．1．3 IF函数的引用特性49
3．1．4 AND函数50
3．1．5 OR函数51
3．1．6 NOT函数53
3．2 Excel公式的错误值53
3．2．1 常见错误值53
3．2．2 识别错误值（ISERROR，ISERR，ISNA）54
3．2．3 处理错误值函数（IFERROR，IFNA）55
3．2．4 构造利用错误值55
3．3 信息函数58
3．3．1 IS类信息函数58
3．3．2 N函数59
3．3．3 T函数60
3．3．4 N/T+IF函数60
第4章 数据汇总知多少――常用汇总统计函数应用62
4．1 求和类函数62
4．1．1 计算乘积之和函数：SUMPRODUCT62
4．1．2 单条件求和函数：SUMIF64
4．1．3 多条件求和函数：SUMIFS69
4．2 计数类函数73
4．2．1 计算数字个数函数：COUNT73
4．2．2 非空计数函数：COUNTA74
4．2．3 单条件计数函数：COUNTIF76
4．2．4 多条件计数函数：COUNTIFS78
4．3 平均类函数79
4．3．1 条件求平均函数：AVERAGEIF79
4．3．2 多条件求平均函数：AVERAGEIFS81
4．3．3 修剪平均函数：TRIMMEAN82
4．4 求最大值、最小值的函数84
4．4．1 条件求最大值、最小值84
4．4．2 极大值、极小值函数：LARGE与SMALL85
第5章 时间都去哪儿了――日期时间函数的应用88
5．1 日期函数89
5．1．1 年月日函数：YEAR、MONTH、DAY89
5．1．2 日期函数：DATE91
5．1．3 系统日期、时间函数：TODAY、NOW92
5．1．4 月份函数：EDATE94
5．1．5 月末函数：EOMONTH95
5．1．6 日期处理函数：DATEDIF96
5．2 星期与工作日函数100
5．2．1 计算星期几函数：WEEKDAY100
5．2．2 计算第几周函数：WEEKNUM101
5．2．3 计算工作日函数：WORKDAY103
5．2．4 自定义工作日与休息日函数：WORKDAY．INTL105
5．2．5 计算工作日天数函数：NETWORKDAYS108
5．2．6 自定义工作日天数函数：NETWORKDAYS．INTL109
5．3 时间函数113
5．3．1 时分秒函数：HOUR、MINUTE、SECOND113
5．3．2 时间函数：TIME114
第6章 我行我“数”――数学函数117
6．1 取整函数117
6．1．1 向下取整函数：INT117
6．1．2 截尾取数函数：TRUNC119
6．2 求余函数：MOD120
6．3 舍入函数123
6．3．1 四舍五入函数：ROUND123
6．3．2 向下舍入函数：ROUNDDOWN125
6．3．3 向上舍入函数：ROUNDUP126
6．3．4 按倍数向下舍位函数：FLOOR128
6．3．5 按倍数向上进位函数：CEILING129
6．4 随机函数131
6．4．1 随机小数函数：RAND131
6．4．2 随机整数函数：RANDBETWEEN132
第7章 字字珠玑――文本处理函数134
7．1 文本长度计算函数：LEN和LENB134
7．2 文本提取函数137
7．2．1 从左提取字符函数：LEFT、LEFTB137
7．2．2 从右提取字符函数：RIGHT、RIGHTB139
7．2．3 从指定位置提取字符函数：MID、MIDB141
7．3 文本查找函数144
7．3．1 区分大小写、不支持通配符的查找函数：FIND、FINDB145
7．3．2 不区分大小写、支持通配符的查找函数：SEARCH、SEARCHB147
7．4 文本替换函数150
7．4．1 按址替换函数：REPLACE、REPLACEB150
7．4．2 按值替换函数：SUBSTITUTE152
7．5 字符与数字代码函数155
7．5．1 将字符转换为数字代码的函数：CODE155
7．5．2 将数字代码转换为字符的函数：CHAR155
7．6 重复函数：REPT157
7．7 不可见字符清除函数159
7．7．1 清除不可见字符函数：CLEAN160
7．7．2 清除空格字符专用函数：TRIM160
7．8 文本字符连接函数162
7．8．1 文本字符连接函数：CONCATENATE162
7．8．2 另类文本字符连接函数：PHONETIC162
7．9 格式转换函数：TEXT163
7．10 字母转换函数167
7．10．1 大写字母函数：UPPER167
7．10．2 小写字母函数：LOWER167
7．10．3 将单词首字母转换为大写函数：PROPER168
第8章 数据追捕高手――查找和引用函数170
8．1 查找址函数：MATCH170
8．2 查找值函数175
8．2．1 垂直查找函数：VLOOKUP175
8．2．2 水平查找函数：HLOOKUP179
8．2．3 查找函数：LOOKUP183
8．3 引用函数189
8．3．1 引用函数①：INDEX189
8．3．2 引用函数②：CHOOSE195
8．3．3 引用函数③：INDIRECT198
8．3．4 引用函数④：OFFSET203
8．4 其他函数206
8．4．1 转置函数：TRANSPOSE207
8．4．2 单元格地址函数：ADDRESS208
8．4．3 超链接函数：HYPERLINK209
第9章 让函数水平更上一层楼――高级函数213
9．1 获取单元格信息的函数：CELL213
9．2 矩阵乘积函数：MMULT216
9．3 分类汇总函数：SUBTOTAL220
9．4 频率函数：FREQUENCY223
第10章 为函数世界添砖加瓦――Excel 2019新增函数227
10．1 条件函数227
10．1．1 多条件判断函数：IFS227
10．1．2 多条件求最大值函数：MAXIFS228
10．1．3 多条件求最小值函数：MINIFS230
10．2 合并函数231
10．2．1 无分隔符合并函数：CONCAT231
10．2．2 有分隔符合并函数：TEXTJOIN234
第11章 函数中的隐形绝招――三维引用技术238
11．1 什么是三维引用238
11．2 三维引用函数239
11．2．1 三维引用函数：INDIRECT239
11．2．2 三维引用函数：OFFSET241
11．3 支持三维引用的函数244
第12章 啥事儿都能帮上忙――函数与Excel其他功能结合245
12．1 函数与Excel其他功能结合应用245
12．1．1 函数与条件格式245
12．1．2 函数与数据透视表256
12．1．3 函数与数据验证（数据有效性）257
12．1．4 函数与高级筛选260
12．2 Excel函数与名称结合应用262
12．2．1 用名称简化公式262
12．2．2 名称与图片链接263
12．2．3 名称与合并计算264
12．2．4 名称与图表266
12．2．5 名称与表单控件268
实例目录
第1章 菜鸟必学――函数与公式基础
实例1 求每个销售员在第1季度的总业绩\\平均业绩\\最高业绩\\最低业绩\\月份数4
实例2 使用SUM函数计算每个销售员的业绩在总业绩中的占比6
实例3 两科成绩同时大于或等于90分的学生奖励100元8
实例4 上半年和下半年业绩均为优的员工奖金为10 000元，其他奖金为8000元9
实例5 九九乘法表的制作12
实例6 求家庭收支明细表中的累计余额15
实例7 计算每位客服人员的奖金是多少17
实例8 计算员工一周的出勤率18
实例9 计算员工上班迟到的分钟数19
实例10 计算两科成绩同时大于或等于90分的学生的平均分20
实例11 计算每个参赛选手的最后得分21
第2章 成为高手的必经之路――数组技术
实例12 计算采购离合器产品的总金额27
实例13 根据各离合器产品的单价和数量求总金额28
实例14 计算2015―2017年业绩等级为全优的人数28
实例15 利用数组制作九九乘法表30
实例16 计算各科成绩低于平均分的学生人数32
实例17 计算业绩大于或等于450万元的销售员的补助总金额33
实例18 统计两年各项产品的销售情况同时合格的人数34
实例19 综合应用：根据各农产品不同等级对应的单价和数量计算出金额36
实例20 求连续3个月销售电脑数量大于或等于50台的人数37
实例21 求部门是销售部且工资大于或等于10 000元的员工的总工资38
实例22 求销售电脑数量至少有1个月大于或等于80台的达标人数40
实例23 求部门是销售部或者工资大于或等于10 000元的员工的总工资41
实例24 求每个员工上半年获得最高工资的月份43
实例25 求各班学生的最高分44
第3章 让电脑帮助人脑――Excel对数据的判断处理
实例26 根据价格表计算出采购清单的采购金额47
实例27 判断两科成绩同时大于或等于90分的学生为优秀（IF函数）49
实例28 根据选择的起始月份和终止月份汇总相应的销售数量49
实例29 判断两科成绩同时大于或等于90分的学生为优秀（AND函数）51
实例30 判断三科成绩中只要有一科成绩大于或等于90分的学生为合格52
实例31 计算采购清单中的商品金额54
实例32 统计因故没有考试和考试不及格的人数56
实例33 求考试及格的人数57
实例34 计算1月到4月业绩大于或等于50万元的平均业绩58
实例35 计算成绩大于或等于80分以上的学生的平均分60
第4章 数据汇总知多少――常用汇总统计函数应用
实例36 计算各产品的总金额63
实例37 求各种花卉的销售总数量64
实例38 求花卉的销售数量大于或等于700件的总数量（SUMIF函数忽略第3参数的
应用）65
实例39 求竞赛中初中和高中最高分的总和分别是多少（SUMIF函数第2参数通配符的
应用）66
实例40 求某竞赛中初中和高中最高分的总和分别是多少（数据结构有变化）68
实例41 求二级苹果的总数量（SUMIFS函数的普通应用）70
实例42 求各等级、各品名的水果的总数量71
实例43 统计广东科技公司的总销售额（万元）（通配符应用）72
实例44 统计出上、下半年均有销售的蔬菜品种数73
实例45 统计每个人的达标率75
实例46 统计成绩为85~95分的学生人数76
实例47 统计出每个人抽奖抽到的奖品种类是多少77
实例48 统计各店男、女服装的销售数量分别是多少78
实例49 统计出各店的平均销售额80
实例50 统计每个参赛选手的最后得分81
实例51 统计每个参赛选手的最后得分（弃权不计）83
实例52 求各班的最高分和最低分84
实例53 统计每个参赛选手的最后得分（升级版）86
第5章 时间都去哪儿了――日期时间函数的应用
实例54 根据入职日期计算出员工工龄，入职时是上半年还是下半年，是平年还是闰年89
实例55 计算指定年份的天数92
实例56 计算每个人的年龄93
实例57 计算挖掘机设备的租用到期时间94
实例58 计算出指定日期所在月份的天数95
实例59 判断员工是否已过试用期（试用期为3个月）97
实例60 计算员工工龄98
实例61 根据出生日期对未来近10天内即将过生日的员工做提醒99
实例62 根据培训日期计算出总的培训费用100
实例63 计算一个月内所有日期属于当月的第几周102
实例64 根据工作日推算证件办理完成的最后日期103
实例65 计算证件的领取日期104
实例66 根据办理各事项所需工作日推算完成日期106
实例67 根据超市收货日期计算不同类别供货商的结款日期107
实例68 计算指定月份的员工出勤天数108
实例69 计算2017年指定月份的工作日天数（不含节假日）110
实例70 计算2017年全年每月的工作日天数111
实例71 根据上课时长计算出各讲师的课酬113
实例72 根据开始时间和通话时间长度（分钟）计算结束时间114
案例73 根据月份计算对应的季度118
第6章 我行我“数”――数学函数
实例74 计算某公司2017年每月业绩占所在季度的百分比118
实例75 截尾保留金额小数点后两位，然后求和119
实例76 制作循环序列数121
实例77 根据上下班时间计算工作时间122
实例78 将工资金额四舍五入到小数点后1位123
实例79 提取金额中十位、个位部分的值125
实例80 根据各讲师的讲课时间计算培训费用126
实例81 根据分数判断其属于哪一个等级128
实例82 根据课程时长计算费用129
实例83 根据月份计算对应的季度130
实例84 生成8：00―12：00之间的随机时间（RAND函数）131
实例85 生成8：00―12：00之间的随机时间（RANDBETWEEN函数）133
第7章 字字珠玑――文本处理函数
实例86 计算字符串中单、双字节各自的字符长度135
实例87 根据月份计算对应的季度136
实例88 提取字符串中的不定长姓名137
实例89 所有成绩大于或等于80分的学生为通过，否则为空138
实例90 提取字符串中的数字140
实例91 根据身份证号判断性别140
实例92 根据分数判断对应的等级142
实例93 计算各职称名单对应的人数143
实例94 提取职员信息中的部门信息145
实例95 提取编号中的部分数据（尺码提取）146
实例96 根据信息判断学生属于哪个阶段147
实例97 提取文本字符串中的英文名149
实例98 隐藏地址中的部分信息150
实例99 给品名的英文单词加括号151
实例100 提取路径下的文件名152
实例101 提取字符串中多个数量的最大值154
实例102 判断分数，及格打“√”，不及格打“×”156
实例103 将字符组别重新排序156
实例104 根据分数在单元格中制作条形图158
实例105 根据项目的开始日期和结束日期制作进度图158
实例106 清除文本字符串中的换行符160
实例107 以分隔符提取多条不同长度的数据161
实例108 快速连接单元格区域数据，使其成为文本字符串162
实例109 根据身份证号码提取性别164
实例110 使用TEXT函数判断分数的等级165
实例111 给QQ号码的每个数字之间加分隔符166
实例112 判断单词首字母是否大写167
实例113 将英文语句的首字母设为大写，其他为小写168
实例114 将中英文对译中的英文部分转换成句首字母大写169
第8章 数据追捕高手――查找和引用函数
实例115 根据业绩查询对应等级171
实例116 根据业绩等级返回对应奖金172
实例117 计算每个人当前业绩距上一个等级还差多少万元173
实例118 计算不重复的部门数量174
实例119 根据蔬菜单价表计算蔬菜的金额176
实例120 根据提成百分比和业绩计算提成金额177
实例121 多条件查询177
实例122 查找指定学生的各科成绩179
实例123 根据购买课程类别和购买金额计算折扣后金额180
实例124 计算指定科目所有分数的平均分182
实例125 根据分数判断等级（LOOKUP向量垂直查询）183
实例126 根据分数判断等级（LOOKUP向量水平查询）184
实例127 根据书籍编号查询书籍名187
实例128 提取字符串中最后的数字部分188
实例129 根据分数判断对应等级191
实例130 制作二维查询系统192
实例131 制作模糊查询系统193
实例132 根据课程编号返回课程类别195
实例133 制作工资条196
实例134 重组查询区域后的VLOOKUP查询197
实例135 将单列数据转为多行多列数据199
实例136 将多行多列数据转换为单列数据200
实例137 将二维数据表转换为一维数据表201
实例138 统计每个人各年的总业绩204
实例139 统计各个产品最后两天的总销量205
实例140 整理转置数据207
实例141 制作员工请假销假监控系统210
第9章 让函数水平更上一层楼――高级函数
实例142 以活动单元格为条件制作动态查询系统214
实例143 统计成绩表的最高总分及其对应的姓名217
实例144 统计出任意一科及以上成绩大于或等于90分的人数219
实例145 自动编写序号220
实例146 比赛评分计算表222
实例147 统计差、中、良、优四个等级各自的人数223
实例148 提取相同日期中的第一条记录224
实例149 计算球赛的最多连胜次数225
第10章 为函数世界添砖加瓦――Excel 2019新增函数
实例150 根据成绩判断对应的等级228
实例151 多条件求最高销售金额229
实例152 多条件求最低价格230
实例153 合并各月份的业绩数据232
实例154 提取各姓名对应的分数233
实例155 根据部门提取合并对应的人员名单234
实例156 将文本字符串中的数字进行排序235
第11章 函数中的隐形绝招――三维引用技术
实例157 统计各科成绩总分的最高分239
实例158 统计各月销量占总销量的百分比240
实例159 将订单按数量进行拆分241
实例160 统计各个日期最高平均数量及对应城市243
第12章 啥事儿都能帮上忙――函数与Excel其他功能结合
实例161 标记出第4~6名的分数246
实例162 隔日标记日期记录247
实例163 用颜色动态标记活动单元格所在行的数据248
实例164 用数据条标记未来3天即将到期的记录251
实例165 动态标记日历253
实例166 在数据透视表中根据汇总金额计算提成金额256
实例167 利用数据验证制作二级下拉菜单257
实例168 利用数据验证验证身份证号259
实例169 筛选包含“变速箱”且全年总销量大于或等于350件的商品260
实例170 统计最高总分及对应姓名262
实例171 动态引用查询系统的图片263
实例172 动态获取合并计算的数据源264
实例173 制作动态图表266
实例174 使用表单控件制作动态图表268
鄂公网安备 42010302001612号 