高效随身查——Excel 2021必学的函数与公式应用技巧（视频教学版）

工作堆积如山，加班加点总也忙不完？ 
百度搜索很多遍，依然找不到确切的答案？ 
大好时光，怎能全耗在日常文档、表格与 PPT 操作上？ 
别人工作很高效、很利索、很专业，我怎么不行？ 
您是否羡慕他人早早做完工作，下班享受生活？ 
您是否注意到职场达人，大多都是高效能人士？ 
没错！ 
工作方法有讲究，提高效率有捷径： 
一两个技巧，可节约半天时间； 
一两个技巧，可解除一天烦恼； 
一两个技巧，可少走许多弯路； 
一本易学书，菜鸟也能变高手； 
一本实战书，让您职场中脱颖而出； 
一本高效书，不必再加班加点、匀出时间分给其他爱好。 
1. 这是一本什么样的书 
（1）着重于解决日常疑难问题和提高工作效率：与市场上很多同类图书不同，本书并非单纯讲解工具使用，而是点对点地快速解决日常办公中的疑难和技巧，着重帮助提高工作效率。 
（2）注意解决一类问题，让读者触类旁通：日常工作问题可能很多，各有不同，事事列举既繁杂也无必要，本书在选择问题时注意选择一类问题，给出思路、方法和应用扩展，方便读者触类旁通。 
（3）应用技巧详尽、丰富：本书选择了几百个应用技巧，足够满足日常办公方面的工作应用。 
（4）图解方式，一目了然：读图时代，大家都需要缓解压力，本书图解的方式可以让读者轻松学习，毫不费力。 
2. 这本书是写给谁看 
（1）想成为职场“白骨精”的小 A：高效干练，企事业单位的主力骨干，白领中的精英，高效办公是必需的！ 
（2）想干点“更重要”的事的小B：日常办公耗费了不少时间，掌握点技巧，可节省 2/3 的时间，去干点个人发展的事更重要啊！ 
（3）想获得领导认可的文秘小C：要想把工作及时、高效、保质保量地做好， 
让领导满意，怎么能没点办公绝活？ 
（4）想早下班逗儿子的小 D：人生苦短，莫使金樽空对月，享受生活是小 D 的人生追求，一天的事情半天搞定，满足小 D 早早回家陪儿子的愿望。 
（5）不善于求人的小 E：事事求人，给人的感觉好像很谦虚，但有时候也可能显得自己很笨，所以小 E 这类人，还是自己多学两招。 
3. 此书的创作团队 
本系列图书的创作团队都是长期从事行政管理、HR 管理、营销管理、市场分析、财务管理和教育 / 培训的工作者，以及微软办公软件专家。他们在电脑知识普及、行业办公中具有十多年的实践经验，出版的书籍广泛受到读者好评。而且本系列图书所有写作素材都是采用企业工作中使用的真实数据报表，这样编写的内容更能贴近读者使用及操作规范。 
本书由赛贝尔资讯组织策划与编写，参与编写的人员有张发凌、吴祖珍、姜楠、韦余靖等老师，在此对他们表示感谢！尽管作者对书中的列举文件精益求精，但疏漏之处仍然在所难免。如果读者朋友在学习的过程中，遇到一些难题或是有一些好的建议，欢迎和我们交流。 

本书在函数应用方面做到详尽、实用、易操作、能举一反三，同时也是一本 Excel 疑难问题解答手册，是您工作中案头必备工具书，方便随用随查！无论您是初学者，还是经常使用 Excel 的行家，学习本书都会有一个质的飞跃。无论何时何地，当需要查阅时打开本书就会找到您需要的内容。 本书共 11 章，分别讲解函数是什么？公式要怎么用、逻辑函数范例、文本函数范例、日期与时间函数范例、数学函数范例、统计函数范例、财务函数范例、查找和引用函数范例、信息函数范例、数据库函数范例、用公式设置单元格格式及限制数据输入，以及 Excel 函数与公式问题集等内容。 本书所讲操作技巧，皆从实际出发，贴近读者的实际办公需求，全程配以模拟实际办公的数据截图辅助用户学习和掌握。海量内容、涉及全面、语言精练、开本合适，易于翻阅和随身携带，帮您在有限的时间内，保持愉悦的心情的同时快速地学习知识点和技巧。在职场的晋升中，本书将会助您一臂之力。不管您是初入职场，还是工作多年，都能够通过本书的学习，获得提升，从而更受企业青睐。

赛贝尔资讯是一个由财务、统计、营销、市场分析以及微软MVP专家组成的专业团队，长期从事相关工作，精通Excel，所编著图书以实践、实例见长。

第1章 函数是什么？公式要怎么用？ 1 
1.1 公式的输入与编辑 1 
技巧 1 快速求和（平均值） 1 
技巧 2 公式函数不分家 . 2 
技巧 3 启用“插入函数”对话框编辑函数 . 3 
技巧 4 手写编辑公式 . 6 
技巧 5 公式的修改 . 8 
技巧 6 不记得函数全称也能正确输入函数 . 9 
技巧 7 快速查找和学习*函数用法 9 
技巧 8 使用批量计算 11 
技巧 9 大范围公式复制的方法 11 
技巧 10 跳过非空单元格批量建立公式 12 
技巧 11 普通公式与数组公式 . 14 
技巧 12 为什么数字与“空”单元格相加出错 . 15 
技巧 13 为什么明明显示的是数字而计算结果却为 0 . 16 
技巧 14 暂时保留没有输入完整的公式 17 
技巧 15 将公式运算结果转换为数值 18 
技巧 16 保护公式不被修改 19 
1.2 公式中数据源的引用 20 
技巧 17 相对引用数据源计算 20 
技巧 18 绝对引用数据源计算 21 
技巧 19 引用当前工作表之外的单元格 22 
技巧 20 引用多工作表的同一单元格计算 24 
技巧 21 引用其他工作簿中的数据源 . 25 
技巧 22 用“F4”键快速改变数据源引用类型 27 
技巧 23 为什么要定义名称？ 27 
技巧 24 快速定义名称的方法有哪些？ 28 
技巧 25 引用定义的名称创建公式 . 29 
技巧 26 将公式定义为名称 30 
技巧 27 将表格创建为动态名称实现数据计算即时更新 . 31 
技巧 28 重新修改名称的引用位置 . 33 
1.3 学会分解、理解分式 34 
技巧 29 查看长公式中某一步的计算结果 . 34 
技巧 30 追踪公式引用的单元格 . 35 
技巧 31 通过“公式求值”功能逐步分解公式 . 35 
技巧 32 在单元格中显示所有公式 . 37 

第2章 逻辑函数范例 .39 
1．AND 函数（检验一组数据是否都满足条件） 39 
技巧 1 考评学生的各门课程是否全部及格 . 39 
技巧 2 一次性判断一组数据是否都满足条件 . 40 
2．NOT 函数（对所给参数求反） 41 
技巧 3 筛选掉小于某一年龄的应聘人员 41 
3．OR 函数（检验一组数据是否有一个满足条件） .42 
技巧 4 检验员工是否通过考评 . 42 
4．IF 函数（根据条件判断真假） 43 
技巧 5 利用 IF 函数进行金额校验 . 43 
技巧 6 根据不同返利比计算返利金额 44 
技巧 7 根据业务处理量判断员工业务水平 . 45 
技巧 8 分性别判断成绩是否合格 . 46 
技巧 9 根据消费卡类别与消费额派发赠品 . 48 
技巧 10 根据职工性别和职务判断退休年龄 . 49 
技巧 11 根据年龄与学历对应聘人员二次筛选 50 
技巧 12 根据商品的名称与颜色进行一次性调价 51 
技巧 13 根据 3 项业务的完成率计算综合完成率 . 52 
技巧 14 比较两个采购部门的采购价格 54 
5．IFS 函数（多层条件判断） .55 
技巧 15 比较 IF 与 IFS . 55 
技巧 16 分男女性别判断跑步成绩是否合格 . 56 
技巧 17 实现智能调薪 . 57 
技巧 18 计算个人所得税 58 
6．SWITCH 函数（根据表达式的返回值匹配结果） .62 
技巧 19 只安排周一至周三值班 . 62 
技巧 20 提取纸张大小的规格分类 63 
7．IFERROR 函数（根据条件判断真假） .64 
技巧 21 当被除数为空值（或 0 值）时返回“计算数据源有错误”文字 . 65 

第3章 文本函数范例 .66 
3.1 提取文本 66 
1．LEFT 函数（从最左侧开始提取指定数目的字符） .66 
技巧 1 提取分部名称 . 66 
技巧 2 从特产名称中提取产地信息 67 
2．LEFTB 函数（按字节数从最左侧提取指定数目的字符） 68 
技巧 3 根据产品编号提取类别 . 68 
3．RIGHT 函数（提取从最右侧开始的指定数目的字符） 69 
技巧 4 从右侧提取字符并自动转换为数值 . 69 
技巧 5 只为满足条件的产品提价 . 70 
技巧 6 发票金额的分列填写 72 
4．RIGHTB 函数（按字节数从最右侧提取指定数目的字符） 75 
技巧 7 提取产品的规格数据 75 
5．MID 函数（从指定位置开始提取字符） .76 
技巧 8 从规格数据中提取部分数据 . 76 
技巧 9 从身份证号码中提取出生年份 77 
技巧 10 从身份证号码中提取性别 . 78 
6．MIDB 函数（按字节数从指定位置开始提取字符） 79 
技巧 11 从房号数据中提取单元号 80 
7．CONCATENATE 函数（合并多个字符） 81 
技巧 12 合并商品货号、码数及颜色 . 81 
技巧 13 合并面试人员的总分数与录取情况 82 
3.2 查找与替换文本 83 
8．FIND 函数（查找指定字符并返回其位置） .83 
技巧 14 查找品名所在位置并提取 . 83 
技巧 15 提取产品的货号 . 85 
技巧 16 问卷调查时实现自动统计答案 86 
9．REPLACE 函数（替换字符中的部分字符） .87 
技巧 17 屏蔽中奖手机号码的后几位数 88 
10．SEARCH 函数（查找字符并返回其起始位置） .89 
技巧 18 从产品的名称中提取重量数据 89 
11．SUBSTITUTE 函数（用新字符替换旧字符） .90 
技巧 19 查找特定文本且将第一次出现的删除，其他保留 . 90 
技巧 20 根据报名学员统计人数 92 
3.3 转换文本格式 93 
12．FIXED 函数（按指定的小数位数进行取整） .93 
技巧 21 解决因四舍五入而造成的显示误差问题 93 
13．WIDECHAR 函数（半角字母转换为全角字母） .94 
技巧 22 将半角英文字母转换为全角英文字母 . 94 
14．UPPER( 将文本转换为大写形式 ) .95 
技巧 23 将小写英文文本一次性转换为大写 95 
15．LOWER 函数（大写字母转换为小写字母） 95 
技巧 24 将大写字母转换为小写字母 96 
16．PROPER 函数（将首字符转换为大写） .96 
技巧 25 一次性将每个单词的首字母转换为大写 96 
17．RMB 函数（四舍五入数值，并添加千分位符号和 ￥ 符号） 97 
技巧 26 将销售额一次性转换为人民币格式 97 
18．DOLLAR 函数（四舍五入数值，并添加千分位符号和 $ 符号） 98 
技巧 27 将销售金额一次性转换为美元货币格式 98 
19．TEXT 函数（将数值转换为指定格式的文本） 99 
技巧 28 让计算得到金额显示为“余款：15,850.00”形式 . 99 
技巧 29 返回值班日期对应在的星期数 100 
技巧 30 按上下班时间计算加班时长并显示为“* 时 * 分”形式 101 
技巧 31 解决日期计算返回日期序列号问题 . 102 
技巧 32 让数据统一显示固定的位数 103 
技巧 33 让合并的日期显示正确格式 104 
20．VALUE 函数（将文本型数字转换成数值型数字） 105 
技巧 34 解决总金额无法计算的问题 105 
3.4 其他文本函数 106 
21．LEN 函数（返回字符串的字符数） 106 
技巧 35 将电话号码的区号与号码分离开 106 
22．REPT 函数（重复文本） .107 
技巧 36 输入身份证号码填写框 . 108 
技巧 37 根据销售额用“★”评定等级 108 
23．TRIM 函数（清除空格） .109 
技巧 38 删除文本单词中多余的空格 109 
24．EXACT 函数（比较两个字符串是否相同） .110 
技巧 39 比较两个店铺的平均售价是否相同 110 

第4章 日期与时间函数范例 . 112 
4.1 返回日期 112 
1．NOW 函数（返回当前日期时间） .112 
技巧 1 为打印报表添加打印时间 .112 
2．TODAY 函数（返回当前日期） 113 
技巧 2 计算员工在职天数 .113 
技巧 3 判断借出图书是否到期 114 
技巧 4 判断应收账款是否到期 115 
3．DATE 函数（返回指定日期的序列号） 116 
技巧 5 建立倒计时显示牌 .116 
技巧 6 计算临时工的工作天数 116 
4．DAY 函数（返回某日期的天数） 118 
技巧 7 计算本月上旬的出库数量 .118 
技巧 8 按本月缺勤天数计算缺勤扣款 119 
技巧 9 显示出全年中各月的天数 . 120 
5．YEAR 函数（返回某日期中的年份） 121 
技巧 10 计算出员工年龄 122 
技巧 11 计算出员工工龄 123 
6．WEEKDAY 函数（返回某日期为星期几） .124 
技巧 12 返回值班日期对应的星期数 124 
技巧 13 判断值班日期是平时加班还是双休日加班 125 
技巧 14 计算平常日与周末日的加班工资 126 
7．MONTH 函数（返回某日期中的月份） 127 
技巧 15 自动填写销售报表中的月份 127 
技巧 16 计算本月账款金额总计 . 128 
8．EOMONTH 函数（返回某个月份最后一天的序列号） .129 
技巧 17 根据活动开始日期计算各月活动天数 . 129 
技巧 18 计算优惠券有效期的截止日期 130 
技巧 19 在考勤表中根据当前月份自动建立日期序列 131 
技巧 20 在考勤表中根据各日期自动返回对应的星期数 . 133 
9．WEEKNUM 函数（返回指定日期是第几周） 134 
技巧 21 计算借书历经周数 134 
4.2 日期计算 135 
10．DATEDIF 函数（计算两个日期之间的年、月和天数） 135 
技巧 22 计算总借款天数 136 
技巧 23 根据员工工龄自动追加工龄工资 137 
11．DAYS360 函数（计算两日期间相差的天数） 138 
技巧 24 计算还款剩余天数 139 
技巧 25 计算固定资产已使用月份 . 140 
技巧 26 利用 DAYS360 函数判断借款是否逾期 141 
12．YEARFRAC 函数（计算两日期间天数占全年天数的百分比） .142 
技巧 27 计算年假占全年天数的百分比 142 
13．WORKDAY 函数（获取间隔若干工作日后的日期） 143 
技巧 28 根据休假天数计算休假结束日期 144 
14．WORKDAY.INTL 函数 .144 
技巧 29 根据休假天数计算休假结束日期（指定一天为法定假日） . 145 
15．NETWORKDAYS 函数（计算两个日期间的工作日） 146 
技巧 30 计算两个日期间的工作日 . 146 
16．NETWORKDAYS.INTL 函数 .147 
技巧 31 计算两个日期间的工作日（指定只有周一为休息日） 149 
17．EDATE 函数（计算与指定日期相隔指定月份数的日期） 149 
技巧 32 根据账龄计算应收账款的到期日期 . 150 
4.3 时 间 函 数 .151 
18．HOUR 函数（返回某时间中的小时数） .151 
技巧 33 计算登录访问的时间的区间 151 
19．MINUTE 函数（返回某时间中的分钟数） .152 
技巧 34 计算停车时间 . 152 
20．SECOND 函数（返回某时间中的秒数） .153 
技巧 35 计算机器运行秒数 153 
技巧 36 显示高于或低于标准时间的值 . 154 
4.4 日期与时间转换 .156 
21．DATEVALUE 函数（将文本日期转换为可识别的日期序列号） .156 
技巧 37 计算到某一指定日期截止的总天数 . 156 
22．TIMEVALUE 函数（将时间转换为对应的小数值） 157 
技巧 38 根据下班打卡时间计算加班时间 158 

第5章 数学函数范例 .160 
5.1 求和及按条件求和函数 .160 
1．SUM 函数（求和） .160 
技巧 1 一次性对多列数据求和 . 160 
技巧 2 根据销售数量与单价计算总销售额 161 
技巧 3 只统计某两个店铺的合计金额 162 
技巧 4 分奇偶行统计数据 163 
2．SUMIF 函数（按条件求和） 164 
技巧 5 按经办人计算销售金额 165 
技巧 6 统计各部门工资总额 166 
技巧 7 分别统计前半个月与后半个月的销售额 . 166 
技巧 8 用通配符对某一类数据求和 168 
技巧 9 计算销售金额前 3 名合计值 . 169 
3．SUMIFS 函数（按多条件求和） 170 
技巧 10 统计指定仓库指定商品的出库总数量 . 170 
技巧 11 按月汇总出库数量 172 
技巧 12 多条件统计某一类数据总和 173 
技巧 13 按不同性质统计应收款 . 174 
4．SUMPRODUCT 函数（求多组数的乘积之和） .176 
技巧 14 统计销售部女员工人数 . 178 
技巧 15 按月汇总出库数量 179 
技巧 16 统计出指定班级分数大于指定值的人数 180 
技巧 17 统计出指定部门获取奖金的人数（去除空值） . 181 
技巧 18 统计出指定部门、指定职务的员工人数 . 182 
技巧 19 统计非工作日销售金额 183 
技巧 20 分别统计 12 个月内账款与超过 12 个月的账款合计 . 184 
技巧 21 统计某一时间段出现的次数 186 
技巧 22 从学生档案表中统计指定日期区间中指定性别的人数 187 
5.2 数据舍入函数 188 
5．INT 函数（向下取整） 188 
技巧 23 计算平均销售数量时取整数 188 
6．ROUND 函数（四舍五入） 189 
技巧 24 以 1 个百分点为单位计算奖金或扣款 . 190 
7．ROUNDUP 函数（向上舍入） .191 
技巧 25 以 1 个百分点为单位计算奖金（向上舍入） 191 
技巧 26 使用 ROUNDUP 函数计算物品的快递费用 192 
8．ROUNDDOWN 函数（向下舍入） 193 
技巧 27 以 1 个百分点为单位计算奖金（向下舍入） 193 
技巧 28 根据给定时间界定整点范围 194 
9．TRUNC 函数（不考虑四舍五入截去数据小数部分） .195 
技巧 29 计算销售金额时取整或保留指定位数小数 195 
10．CEILING 函数（舍入计算） .196 
技巧 30 以 6 秒（不足 6 秒按 6 秒计算）为单位计算通话费用 . 197 
11．FLOOR 函数（去尾舍入） .198 
技巧 31 计件工资中的奖金计算 . 198 
12．MROUND 函数（按指定倍数舍入） .200 
技巧 32 计算商品运送车次 200 
13．QUOTIENT 函数（返回商品的整数部分） 201 
技巧 33 计算参加某活动的每组人数 . 202 
5.3 阶乘、随机数计算函数 .202 
14．FACT 函数（求指定正数值的阶乘） .202 
技巧 34 求指定正数值的阶乘值 . 203 
15．MULTINOMIAL 函数（计算指定数值阶乘与各数值阶乘乘积的比值） 203 
技巧 35 计算指定数值和的阶乘与各数值阶乘乘积的比值 . 203 
16．RAND 函数（返回一个大于或等于 0 且小于 1 的随机数） .204 
技巧 36 随机获取选手编号 205 
技巧 37 自动生成彩票 7 位开奖号码 . 205 
17．RANDBETWEEN 函数（返回两个数值间的随机数） 206 
技巧 38 自动随机生成 3 位数编号 . 206 
5.4 其他数据学运算函数 .207 
18．ABS 函数（求绝对值） .207 
技巧 39 比较今年销售额与去年销售额 . 207 
技巧 40 计算支出金额总计值 208 
19．MOD 函数（求两个数相除后的余数） .209 
技巧 41 按奇数月与偶数月统计销量 209 
技巧 42 计算每位员工的加班时长 211 
20．SQRT 函数（求算术平方根） 212 
技巧 43 计算指定数值对应的算术平方根 212 
21．GCD 函数（求最大公约数） 212 
技巧 44 返回两个或多个整数的最大公约数 . 213 
22．LCM 函数（求最小公倍数） .213 
技巧 45 计算两个或多个整数的最小公倍数 . 214 
23．POWER 函数（计算方根） 214 
技巧 46 根据指定的底数和指数计算出方根值 . 215 

第6章 统计函数范例 .216 
6.1 平均值计算函数 .216 
1．AVERAGE 函数（求平均值） 216 
技巧 1 快速自动求平均值 216 
技巧 2 在成绩表中忽略 0 值求平均分 217 
2．AVERAGEA 函数（求包括文本和逻辑值的平均值） .219 
技巧 3 计