想要熟练使用表格公式,核心就是理解公式的编写规则掌握常用函数语法,再结合单元格引用方式完成数据计算统计查询,很多人用表格只会手动求和,遇到多条件统计跨表查找就卡住,本质是没有系统掌握公式基础逻辑,表格公式是以等号作为起始标记,通过函数运算符单元格地址组合,让软件自动完成计算,不需要人工逐个计算,编写公式第一步先选中要输出结果的单元格,输入等号,接着写入函数名称,按要求填入参数,参数之间用逗号分隔,写完按回车就能得到计算结果,后续修改源数据,公式会自动刷新计算结果,学会基础引用类型之后,还可以嵌套多层函数,实现复杂业务计算,最终就能覆盖日常办公绝大多数计算场景。
一 表格公式基础规则
表格所有公式必须以等号开头,没有等号软件会识别成普通文本,不会执行运算。公式里面可以混合运算符函数单元格引用常量数字,运算符分为算术运算符比较运算符文本连接运算符引用运算符四类。
算术运算符包括加减乘除乘方百分号,比较运算符用于判断真假,返回 TRUE 或者 FALSE,文本连接符用来把多段文字合并,引用运算符用来指定单元格区域。
单元格引用是公式的核心,分为三种类型,使用的时候一定要区分清楚。
| 引用类型 | 写法特点 | 拖动填充公式效果 | 适用场景 |
|---|---|---|---|
| 相对引用 | A1 | 行号列标都会跟着偏移 | 批量逐行逐列计算,比如每行求和 |
| 绝对引用 | $A$1 | 行号列标全部锁定,拖动不变 | 固定单价固定参数,计算所有行都引用同一个基准单元格 |
| 混合引用 | $A1 或者 A$1 | 只锁定列或者只锁定行 | 二维表批量计算,乘法表类场景 |
补充要点,跨工作表引用格式是工作表名称加感叹号再加单元格,跨工作簿引用需要带上文件路径,公式输入的时候可以直接鼠标点选单元格,不用手动输入地址,减少输入错误。
二 高频基础函数清单
这部分是最常用的基础计算函数,日常报表使用率最高。
| 函数名称 | 语法 | 功能说明 | 基础示例 |
|---|---|---|---|
| SUM | SUM (数值区域) | 对区域内所有数字求和 | SUM(A1:A10) |
| AVERAGE | AVERAGE (数值区域) | 计算区域平均值 | AVERAGE(B1:B20) |
| MAX | MAX (数值区域) | 取出区域最大值 | MAX(C1:C50) |
| MIN | MIN (数值区域) | 取出区域最小值 | MIN(C1:C50) |
| COUNT | COUNT (区域) | 统计区域里面数字单元格个数 | COUNT(D1:D30) |
| COUNTA | COUNTA (区域) | 统计非空单元格数量,包含文字 | COUNTA(D1:D30) |
| ROUND | ROUND (数值,保留位数) | 按指定位数四舍五入 | ROUND(A1,2) |
| INT | INT (数值) | 向下取整,只保留整数部分 | INT(A1) |
| MOD | MOD (被除数,除数) | 求余数 | MOD(A1,2) |
使用要点,SUM 会自动忽略文本空单元格,COUNT 只识别数字,文字日期如果格式异常不会计入统计,ROUND 的第二个参数填负数就是对整数位做四舍五入。
三 条件类核心函数
条件函数是表格公式进阶重点,单条件多条件判断统计都依靠这一组函数。
IF 函数
语法 IF (判断条件,条件成立返回值,条件不成立返回值)
作用是根据判断结果输出不同内容,支持多层嵌套,实现多等级判断,比如成绩评级,达标不达标判定。
示例 IF (A1>=60, 及格,不及格)
多层嵌套示例 IF (A1>=90, 优秀,IF (A1>=60, 及格,不及格))
SUMIF 单条件求和
语法 SUMIF (条件区域,条件,求和区域)
作用是满足指定条件之后,对对应的数值区域求和。
示例 SUMIF (B:B, 苹果,C:C) 代表 B 列等于苹果的行,对应的 C 列数值求和
SUMIFS 多条件求和
语法 SUMIFS (求和区域,条件区域 1, 条件 1, 条件区域 2, 条件 2)
注意顺序和 SUMIF 不一样,求和区域写在最前面,可以同时设置多个条件,条件之间是同时满足的逻辑。
COUNTIF 单条件计数
语法 COUNTIF (条件区域,条件)
统计符合条件单元格数量。
COUNTIFS 多条件计数
语法 COUNTIFS (条件区域 1, 条件 1, 条件区域 2, 条件 2)
同时满足多个条件,统计符合要求的条目数量。
四 查询匹配类函数
这类函数用来跨表查找匹配数据,做台账对照档案匹配非常常用。
| 函数 | 语法 | 功能 | 注意事项 |
|---|---|---|---|
| VLOOKUP | VLOOKUP (查找值,数据表区域,返回第几列,匹配类型) | 按首列查找,返回对应列的数据 | 要求查找值在所选区域第一列,精确匹配最后参数写 0 |
| XLOOKUP | XLOOKUP (查找值,查找区域,返回区域,找不到返回值) | 新版函数,替代 VLOOKUP,支持反向查找 | 不需要固定列序号,容错能力更强 |
| INDEX | INDEX (区域,行号,列号) | 根据行列位置取出单元格内容 | 经常搭配 MATCH 使用 |
| MATCH | MATCH (查找值,查找区域,匹配类型) | 返回查找值在区域里面的位置序号 | 搭配 INDEX 实现双向查找 |
VLOOKUP 常见坑,查找区域没有锁定,下拉填充的时候区域偏移,解决办法改成绝对引用,查找值存在文本型数字和数值型数字格式不一致,会匹配失败,需要统一单元格格式。
五 文本处理函数
用来拆分合并清洗文字,做数据清洗必备。
| 函数 | 语法 | 功能 |
|---|---|---|
| CONCAT | CONCAT (文本 1, 文本 2) | 合并多个单元格文字,替代旧版 CONCATENATE |
| LEFT | LEFT (文本,字符个数) | 从左侧截取指定长度文字 |
| RIGHT | RIGHT (文本,字符个数) | 从右侧截取指定长度文字 |
| MID | MID (文本,起始位置,字符个数) | 从中间指定位置截取文字 |
| LEN | LEN (文本) | 计算文本字符总长度 |
| TRIM | TRIM (文本) | 清除文字多余空格 |
| SUBSTITUTE | SUBSTITUTE (文本,旧内容,新内容) | 替换指定文字 |
六 日期时间函数
用于报表日期计算,账龄计算到期提醒。
| 函数 | 语法 | 功能 |
|---|---|---|
| TODAY | TODAY() | 自动获取系统当天日期,打开表格自动更新 |
| NOW | NOW() | 获取当前日期加时间 |
| YEAR | YEAR (日期) | 提取年份 |
| MONTH | MONTH (日期) | 提取月份 |
| DAY | DAY (日期) | 提取日 |
| DATEDIF | DATEDIF (起始日期,结束日期,单位) | 计算两个日期间隔,单位 Y 年 M 月 D 天 |
| EDATE | EDATE (起始日期,月数) | 推算几个月之后的日期 |
DATEDIF 属于隐藏函数,输入的时候不会自动提示,但是可以正常使用,计算账龄经常用到。
七 公式编辑操作步骤
第一步 选中目标单元格,输入等号,开始编写公式。
第二步 输入函数名称,软件会弹出参数提示,也可以点击插入函数按钮,通过向导选择函数。
第三步 填入参数,鼠标点选单元格区域,参数之间使用逗号分隔。
第四步 检查引用类型,需要固定的单元格添加美元符号锁定。
第五步 按回车确认,查看计算结果。
第六步 拖动填充柄,把公式批量应用到其他行或者列。
第七步 如果出现报错,根据错误代码排查原因。
八 常见公式报错排查
N/A 代表查找不到匹配值,多见于 VLOOKUP XLOOKUP 匹配失败
VALUE! 参数类型错误,比如对文本做数学运算
DIV/0! 除数为零,除法公式分母是空单元格或者零
REF! 引用的单元格被删除,引用失效
NAME? 函数名字拼写错误,或者函数名称不存在
NUM! 数值超出函数可计算范围
NULL! 引用区域交集错误,区域之间没有重叠
排查思路优先检查函数拼写,然后检查参数数据类型,再检查单元格引用是否偏移,最后检查源数据格式是不是文本数字混杂。
九 嵌套函数编写思路
嵌套就是一个函数的输出结果,作为另外一个函数的参数,比如 IF 里面套 SUMIF,VLOOKUP 外面套 IFERROR,IFERROR 作用是捕获报错,找不到的时候自定义显示空白或者提示文字,语法 IFERROR (原公式,报错时显示内容),可以大幅提升表格容错性。
编写嵌套公式不要一次性写很长,建议分层测试,先单独测试内层函数,确认内层结果正确,再套外层函数,避免长公式出错之后很难定位问题。
【常见问题】
问题 1 表格公式大全里面 SUMIF 和 SUMIFS 核心区别是什么
回答 1 表格公式大全里面 SUMIF 是单条件求和,求和区域写在第三个参数,SUMIFS 是多条件同时满足求和,求和区域写在第一个参数,条件数量没有上限,需要多个筛选条件的时候优先选用 SUMIFS。
问题 2 表格公式大全教程里为什么下拉填充公式结果全部一样
回答 2 表格公式大全教程里出现这个情况一般是使用了绝对引用,参数单元格添加美元符号锁定,拖动填充的时候引用不会偏移,如果需要逐行变化,要改成相对引用,去掉美元符号。
问题 3 表格公式大全里面 VLOOKUP 公式出现 N/A 错误怎么解决
回答 3 表格公式大全里面 VLOOKUP 出现 N/A,优先检查查找值和首列数据格式是否统一,是否存在多余空格,确认查找区域首列包含查找目标,精确匹配模式最后一个参数必须填写 0,同时检查区域有没有因为填充发生偏移。
问题 4 表格公式大全教程中公式输入之后显示文本不计算怎么办
回答 4 表格公式大全教程中公式显示文本不计算,绝大多数原因是单元格没有以等号开头,或者单元格被设置成文本格式,解决办法是把单元格格式改成常规,双击单元格之后重新按回车激活公式计算。
【免责申明】
本表格公式大全及使用教程内容基于通用电子表格软件函数逻辑整理,不同版本软件部分函数存在兼容性差异,部分新版函数旧版本无法使用,教程仅作为学习参考,实际业务计算请自行核验计算结果,重要数据建议提前备份,因使用本教程内容产生的数据错误损失,不承担相关责任。

