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

图书标准信息

• 作者 赛贝尔资讯
• 出版社 清华大学出版社
• 出版时间 2022-10
• 版次 1
• ISBN 9787302612643
• 定价 79.80元
• 装帧 其他
• 开本 32开
• 纸张 胶版纸
• 页数 464页
• 字数 568千字

【内容简介】

本书在函数应用方面做到详尽、实用、易操作、能举一反三，同时也是一本 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 计算平均分时值将文本项也计算在内 . 219 

技巧 4 统计各月份的平均销售额（计算区域含文本值） . 220 

3．AVERAGEIF 函数（按条件求平均值） 220 

技巧 5 统计各班级平均分  221 

技巧 6 计算月平均出库数量  222 

技巧 7 通配符模糊匹配求平均值 . 223 

技巧 8 排除新店计算平均利润 . 224 

4．AVERAGEIFS 函数（按多条件求平均值） 225 

技巧 9 计算一车间女职工平均工资  225 

技巧 10 求介于某一区间内的平均值  226 

技巧 11 统计指定店面所有男装品牌的平均利润 . 226 

技巧 12 忽略 0 值求指定班级的平均分  228 

5．GEOMEAN 函数（求几何平均值） .228 

技巧 13 比较两种产品的销售利润的稳定性 . 229 

6．TRIMMEAN 函数（去头尾后求平均值） .230 

技巧 14 通过 10 位评委打分计算选手的最后得分 . 230 

6.2 条目数统计函数 .231 

7．COUNT 函数（统计数目） .231 

技巧 15 根据签到表统计到会人数 . 231 

技巧 16 统计各个部门获取交通补贴的人数 . 232 

技巧 17 统计其中一科得满分的人数  233 

8．COUNTA 函数（求包括文本和逻辑值的数目） 234 

技巧 18 统计课程的总报名人数 . 234 

技巧 19 统计非正常出勤的人数 . 235 

9．COUNTIF 函数（按条件统计数目） 236 

技巧 20 统计某课程的报名人数 . 236 

技巧 21 统计工资大于或等于 5000 元的人数  236 

技巧 22 按学历统计人数  237 

技巧 23 在成绩表中分别统计及格人数与不及格人数  238 

技巧 24 统计出成绩大于平均分数的学生人数 . 239 

技巧 25 统计同时在两列数据中都出现的条目数  240 

技巧 26 统计连续 3 次考试都进入前 10 名的人数 . 241 

10．COUNTIFS 函数（按多条件统计数目） .242 

技巧 27 统计指定部门销量达标人数  242 

技巧 28 统计各店面男装的销售记录条数（双条件）  243 

技巧 29 统计指定商品每日的销售记录数  244 

11．COUNTBLANK 函数（统计空单元格的数目） .245 

技巧 30 统计缺考人数  245 

6.3 最大值与最小值统计函数 246 

12．MAX 函数与 MIN 函数 .246 

技巧 31 快速返回数据区域中的最大值  246 

技巧 32 计算单日销售金额并返回最大值  247 

12．MAXIFS（按条件求最大值） .248 

技巧 33 返回企业女性员工的最大年龄  249 

技巧 34 返回上半月单笔最高销售金额  249 

技巧 35 分别统计各班级第一名成绩  250 

12．MINIFS（按条件求最小值） .251 

技巧 36 返回指定产品的最低报价 . 251 

14．MAXA 函数与 MINA 函数（包含文本与逻辑值时求最大值） 252 

技巧 37 返回最低利润额（包含文本）  252 

16．LARGE 函数（返回某一数据集中的某个最大值） .253 

技巧 38 返回排名前 3 位的销售金额  253 

技巧 39 计算成绩表中前 5 名的平均值 . 254 

17．SMALL 函数（返回某一数据集中的某个最小值） .255 

技巧 40 统计成绩表中后 5 名的平均分  255 

技巧 41 统计成绩表中后 5 名的平均分（忽略 0 值）  256 

18．MODE.MULT 函数 256 

技巧 42 统计生产量抽样数据中的众数  257 

6.4 排位统计函数 .258 

19．RANK.EQ 函数（返回数字的排位） .258 

技巧 43 为学生考试成绩排名次 . 258 

技巧 44 对不连续单元格排名次  259 

20．RANK.AVG 函数（排位有相同名次时返回平均排位） 260 

技巧 45 用 RANK.AVG 函数对销售额排名  260 

21．PERCENTRANK.INC 函数（返回数字的百分比排位） 261 

技巧 46 将各月销售利润按百分比排位 . 261 

22．MEDIAN 函数（返回数据集的中位数） .262 

技巧 47 统计全年各月利润值中的中位数  262 

23．QUARTILE.INC 函数（返回数据集的四分位数） .263 

技巧 48 统计一组身高数据的四分位数  264 

24．PERCENTILE.INC 函数（返回一组数据的百分位数） .265 

技巧 49 统计一组身高数据的 K 个百分点的值 . 265 

6.5 方差、协方差与偏差 .267 

25．VAR.S（计算基于样本的方差） 267 

技巧 50 估算产品质量的方差  268 

26．VAR.P（计算基于样本总体的方差） 268 

技巧 51 以样本值估算总体的方差 . 269 

27．STDEV.S（计算基于样本估算标准偏差） 269 

技巧 52 估算入伍军人身高的标准偏差  270 

28．STDEV.P（计算样本总体的标准偏差） 271 

技巧 53 以样本值估算总体的标准偏差  272 

29．COVARIANCE.S（返回样本协方差） .272 

技巧 54 计算甲状腺与碘食用量的协方差  273 

30．COVARIANCE.P（返回总体协方差） .273 

技巧 55 以样本值估算总体的协方差  274 

31．DEVSQ（返回平均值偏差的平方和） 274 

技巧 56 计算零件质量系数的偏差平方和  275 

32．AVEDEV 函数（计算数值的平均绝对偏差） .275 

技巧 57 计算一种产品重量的平均绝对偏差 . 276 

第7章 财务函数范例 .277 

7.1 投资计算函数 277 

1．PMT 函数（计算贷款每期付款额） .277 

技巧 1 计算贷款的每年偿还额 . 277 

技巧 2 计算贷款的每月偿还金额  278 

2．IPMT 函数（计算每期偿还额中的利息额） .278 

技巧 3 计算贷款每年偿还金额中的利息金额  279 

技巧 4 计算贷款每月偿还金额中的利息金额  279 

3．PPMT 函数（计算本金） .280 

技巧 5 计算贷款每年偿还金额中的本金金额 . 281 

技巧 6 计算贷款每月偿还金额中的本金金额 . 281 

4．ISPMT 函数（等额本金还款方式下的利息计算） .282 

技巧 7 在等额本金还款方式下计算某贷款的利息  282 

5．FV 函数（计算投资未来值） .283 

技巧 8 计算分期存款的未来值 . 284 

技巧 9 计算购买某项保险的未来值  284 

6．FVSCHEDULE 函数（投资在变动或可调利率下的未来值） .285 

技巧 10 计算某项整存整取存款的未来值 . 285 

7．PV 函数（计算投资现值） 285 

技巧 11 判断购买某项保险是否合算  286 

8．NPV 函数（计算投资净现值） 286 

技巧 12 计算某投资的净现值  287 

9．XNPV 函数（计算一组不定期现金流的净现值） 288 

技巧 13 计算出一组不定期盈利额的净现值 . 289 

10．NPER 函数（计算投资期数） .289 

技巧 14 计算出贷款的清还年数 . 289 

技巧 15 计算出某项投资的投资期数  290 

7.2 偿还率计算函数 .290 

11．IRR 函数（计算一组现金流的内部收益率） 290 

技巧 16 计算某项投资的内部收益率  291 

12．XIRR 函数（计算一组不定期现金流的内部收益率） .291 

技巧 17 计算出一组不定期盈利额的内部收益率  292 

13．MIRR 函数（计算修正内部收益率） 292 

技巧 18 计算某项投资的修正内部收益率  292 

14．RATE 函数（计算年金每个期间的利率） .293 

技巧 19 计算购买某项保险的收益率 . 294 

7.3 资产折旧计算函数 294 

15．SLN 函数（直线法） .294 

技巧 20 用直线法计算出固定资产的每年折旧额  294 

技巧 21 用直线法计算出固定资产的每月折旧额  295 

16．SYD 函数（年数总和法） 296 

技巧 22 用年数总和法计算出固定资产的每年折旧额  296 

17．DB 函数（固定余额递减法） 297 

技巧 23 用固定余额递减法计算出固定资产的每年折旧额 . 297 

18．DDB 函数（双倍余额递减法） 298 

技巧 24 用双倍余额递减法计算出固定资产的每年折旧额 . 298 

19．VDB 函数（计算部分期间内的折旧值） 299 

技巧 25 计算出固定资产部分期间的设备折旧值  299 

20．AMORDEGRC 函数（指定会计期间的折旧值） .301 

技巧 26 计算指定会计期间的折旧值 . 301 

第8章 查找和引用函数范例 .302 

8.1 查找函数 .302 

1．CHOOSE 函数（从参数列表中选择并返回一个值） .302 

技巧 1 配合 IF 函数找出短跑成绩的前三名 . 302 

技巧 2 根据产品不合格率决定产品处理办法  303 

2．LOOKUP 函数（向量型）（按条件查找并返回值） 304 

技巧 3 根据产品编码查询库存数量（向量型语法）  305 

3．LOOKUP 函数（数组型）（按条件查找并返回值） 306 

技巧 4 根据产品编码查询库存数量（数组型语法）  307 

技巧 5 按姓名查询学生的各科目成绩  308 

4．HLOOKUP 函数（横向查找） 310 

技巧 6 根据不同的返利率计算各笔订单的返利金额  310 

技巧 7 快速查询任意科目的成绩序列 .311 

5．VLOOKUP 函数（纵向查找） 313 

技巧 8 产品备案表中查询各产品单价  314 

技巧 9 将多张工作表中的数据合并到一张工作表中  315 

技巧 10 根据多条件计算员工年终奖  317 

技巧 11 使用 VLOOKUP 函数进行反向查询  318 

技巧 12 查找并返回符合条件的多条记录  319 

6．MATCH 函数  INDEX 函数（MATCH 查找并返回找到值所在位置， 

INDEX 函数返回指定位置的值） 323 

技巧 13 了解 MATCH 函数与 INDEX 函数 . 323 

技巧 14 查找任意指定销售员的销售总金额（单条件查找）  325 

技巧 15 查找指定月份指定专柜的销售金额（双条件查找）  326 

技巧 16 返回成绩最高的学生的姓名  327 

技巧 17 返回多次短跑中用时最短的编号  328 

技巧 18 查找迟到次数最多的员工 . 329 

8.2 引用函数 330 

7．ADDRESS 函数（建立文本类型单元格的地址） 330 

技巧 19 查找最大销售额所在位置  330 

8．COLUMN 函数（返回引用的列号） .331 

技巧 20 在一行中快速输入月份 . 332 

技巧 21 实现隔列求总销售金额 . 332 

9．COLUMNS 函数（返回引用的列数） .334 

技巧 22 返回参与考试的科目数量  334 

10．ROW 函数（返回引用的行号） .334 

技巧 23 让数据自动隔 4 行（自定义）加 1（自定义） . 335 

技巧 24 提取季度合计值计算全年销售额 . 336 

技巧 25 根据借款期限返回相应的年数序列  337 

11．ROWS 函数（返回引用的行数） 338 

技巧 26 统计销售记录条数  338 

技巧 27 判断值班人员是否重复 . 339 

12．INDIRECT 函数（返回指定的引用） 340 

技巧 28 解决合并单元格引用数据列出现跳跃的问题  340 

技巧 29 按指定的范围计算平均值 . 342 

技巧 30 INDIRECT 解决跨工作表查询时名称匹配问题 . 344 

13．OFFSET 函数（根据指定偏移量得到新引用） 345 

技巧 31 实现数据的动态查询  346 

技巧 32 对每日出库量累计求和 . 347 

技巧 33 OFFSET 常用于创建动态图表数据源 . 348 

第9章 信息函数范例 .351 

9.1 使用 IS 函数进行判断 351 

1．ISBLANK 函数（判断值是否为空值） .351 

技巧 1 标注出缺考学生  351 

2．ISTEXT 函数（判断数据是否为文本） .352 

技巧 2 快速统计缺考人数  352 

3．ISLOGICAL 函数（判断数据是否为逻辑值） .353 

技巧 3 检验数据是否为逻辑值 . 353 

4．ISNUMBER 函数（判断数据是否为数字） 353 

技巧 4 当出现无法计算时检测数据是否是数值数据  354 

5．ISNA 函数（判断数据是否为错误值“#N/A”） 354 

技巧 5 查询编号错误时显示“无此编号”  355 

6．ISERR 函数（检测给定值是否为“#N/A”以外的错误值） 356 

技巧 6 检验数据是否为错误值 #N/A  356 

7．ISERROR（检测给定值是否为任意错误值） .357 

技巧 7 忽略错误值进行求和运算  357 

8．ISODD 函数（判断数据是否为奇数） 358 

技巧 8 根据身份证号码判断其性别  358 

9．ISEVEN 函数（判断数据是否为偶数） 359 

技巧 9 根据工号返回性别信息  359 

9.2 获取相关信息函数 .360 

10．CELL 函数（返回单元格、位置等） .360 

技巧 10 获得正在选取的单元格地址  361 

技巧 11 获得当前文件的路径、路径和工作表名  362 

技巧 12 分辨日期和数字  362 

技巧 13 判断测试结果是否达标 . 364 

11．ERROR.TYPE 函数（返回错误对应的编号） 364 

技巧 14 根据错误代码显示错误原因  365 

12．TYPE 函数（返回数值类型） 366 

技巧 15 测试数据是否是数值型 . 366 

13．N 函数（返回转换为数值后的值） 367 

技巧 16 用订单生成日期的序列号与当前行号生成定单的编号  367 

第10章 数据库函数范例 369 

10.1 常 规 统 计 .369 

1．DSUM 函数（从数据库中按给定条件求和） .369 

技巧 1 计算指定经办人的订单总金额  369 

技巧 2 计算上半月中指定名称产品的总销售额（满足双条件）. 370 

技巧 3 计算总工资时去除某一个（或多个）部门 . 371 

技巧 4 使用通配符实现利润求和统计  372 

技巧 5 解决模糊匹配造成统计错误问题  372 

2．DAVERAGE 函数（从数据库中按给定条件求平均值） .374 

技巧 6 计算指定班级平均分  374 

技巧 7 计算指定车间指定性别员工的平均工资（双条件）  375 

技巧 8 实现对各科目平均成绩查询 . 376 

3．DCOUNT 函数（从数据库中按给定条件统计记录条数） 377 

技巧 9 计算指定车间、指定性别员工的人数 . 377 

技巧 10 统计出指定班级分数大于指定值的人数  378 

技巧 11 统计出指定性别测试合格的人数 . 379 

技巧 12 统计记录条数时使用通配符  380 

4．DCOUNTA 函数（从数据库中按给定条件统计非空单元格数目） .381 

技巧 13 统计业务水平为“好”的人数 . 381 

技巧 14 统计出指定性别测试合格的人数 . 382 

技巧 15 按条件统计来访总人数（使用通配符）  382 

5．DMAX 函数（从数据库中按给定条件求最大值） 383 

技巧 16 返回指定车间指定性别员工的最高工资  384 

技巧 17 实现查询各科目成绩中的最高分  384 

6．DMIN 函数（从数据库中按给定条件求最小值） 386 

技巧 18 返回指定班级的最低分  386 

技巧 19 实现查询各科目成绩中的最低分  387 

7．DGET 函数（从数据库中提取符合条件的单个值） 388 

技巧 20 在列表或数据库中按条件查询 . 388 

8．DPRODUCT 函数（从数据库中返回满足指定条件的数值的乘积） .389 

技巧 21 判断指定类别与品牌的商品是否被维修过  390 

10.2 方差、标准差计算 .391 

9．DVAR 函数（按条件通过样本估算总体方差） 391 

技巧 22 计算指定机器生产零件直径的总体方差  392 

10．DVARP 函数（按条件计算样本的方差）392 

技巧 23 计算指定机器生产零件直径的样本总体方差  393 

11．DSTDEV 函数（按条件通过样本估算总体标准偏差） .394 

技巧 24 计算不同性别身高数据的总体标准偏差  394 

12．DSTDEVP 函数（按条件计算样本的标准偏差） .395 

技巧 25 计算不同性别身高数据的样本总体标准偏差  396 

第11章 用公式设置单元格格式及限制数据输入 397 

11.1 函数在条件格式中的应用 397 

技巧 1 次日值班人员自动提醒 . 397 

技巧 2 自动标识周末日期  399 

技巧 3 比较两个单元格采购价格是否相同  400 

技巧 4 将成绩大于指定分数的标注为“优” . 401 

技巧 5 突出显示“缺考”或未填写数据的单元格  403 

技巧 6 满足条件的整行突出显示 1  404 

技巧 7 满足条件的整行突出显示 2  405 

技巧 8 突出显示每行的最高与最低分 . 406 

技巧 9 加班时长最长的员工特殊显示  408 

11.2 函数在数据有效性中的应用 410 

技巧 10 避免输入重复值  410 

技巧 11 禁止出库数量大于库存数 .411 

技巧 12 禁止输入文本值  412 

技巧 13 限制输入的数据必须小于两位  413 

技巧 14 禁止输入空格  414 

技巧 15 禁止录入不完整的产品规格  415 

技巧 16 设置单元格输入必须包含指定内容 . 416 

技巧 17 限制数据输入的长度  417 

附录 Excel 函数与公式问题集 418 

1. 公式运算问题 418 

问题 1 想复制公式却找不到填充柄了  418 

问题 2 利用公式计算时不显示计算结果只显示公式  419 

问题 3 两个日期相减时不能得到差值天数，却返回一个日期值  420 

问题 4 更改了数据源的值，公式的计算结果并不自动更新是什么原因  421 

问题 5 公式引用单元格明明显示的是数据，计算结果却为 0  421 

问题 6 数字与“空”单元格，结果却报错 . 422 

问题 7 新输入的行中不能自动填充上一行的公式  423 

问题 8 LOOKUP 查找总是找不到正确结果 . 424 

问题 9 VLOOKUP 查找时，查找内容与查找区域首列内容不精确匹配， 有办法实现查找吗？ . 425 

问题 10 在设置按条件求和（按条件计数等）函数的 criteria（用于条件判断的） 参数时，如何处理条件判断问题 . 426 

问题 11 解决浮点运算造成 ROUND 函数计算不准确的问题 . 427 

2．公式返回错误值问题 428 

问题 12 公式返回“#DIV>0!”错误值 （“0”值或空白单元格被作为了除数） . 428 

问题 13 公式返回“#N>A”错误值 （公式中引用的数据源不正确或不能使用） . 429 

问题 14 公式返回“#NAME?”错误值 1 （输入的函数和名称拼写错误） . 430 

问题 15 公式返回“#NAME?”错误值 2 （公式中使用文本作为参数时未加双引号）  430 

问题 16 公式返回“#NAME?”错误值 3 （在公式中使用了未定义的名称） . 431 

问题 17 公式返回“#NAME?”错误值 4 （引用其他工作表时工作表名称包含空格） 432 

问题 18 公式返回“#NUM!”错误值（引用了无效的参数） . 433 

问题 19 公式返回“#VALUE!”错误值 1 （公式中将文本类型的数据参与了数值运算） 433 

问题 20 公式返回“#VALUE!”错误值 2 （公式中函数使用的参数与语法不一致）  434 

问题 21 公式返回“#VALUE!”错误值 3 （数组运算未按 Shift Ctrl Enter 组合键结束）  435 

问题 22 公式返回“#REF!”错误值 （公式计算中引用了无效的单元格）  435 

3．Excel 应用中的其他常见问题 .436 

问题 23 滑动鼠标中键向下查看数据时，工作表中的内容却随之进行缩放  436 

问题 24 要输入一串产品代码，按下 Enter 键后显示为科学计数方式的数字 . 437 

问题 25 填充序号时不能自动递增 . 438 

问题 26 填充时间时为何不能按分钟数（秒数）递增  439 

问题 27 向单元格中输入数据总是弹出对话框 . 440 

问题 28 对于文本型数字，为它应用了“数值”格式后怎么还是没有变成数值 （无法计算）  441 

问题 29 在 Excel 中编辑时按 Enter 键无法换行  441 

问题 30 在 Excel 中查找时总是无法精确找到 . 442 

问题 31 添加了“自动筛选”后，日期值却不能自动分组筛选  443