Excel函数应用教程：从基础用法到常见场景

Excel 函数能把重复的计算和数据处理规则写成公式：统计销售额、判断是否达标、从表格中查找价格、拆分文本，或计算两个日期之间的天数。学会使用函数，不只要记住函数名，更要理解参数、单元格引用、数据类型和错误值。本文从公式基础讲起，再通过常见工作场景展示函数用法，并说明版本兼容、参数边界与安全注意事项。

一、函数公式的基本结构

Excel 公式通常以等号 = 开头。函数名后面使用圆括号，参数之间用逗号分隔。例如：

=SUM(B2:B10)

这个公式将 B2 到 B10 单元格中的数值相加。公式里的冒号表示一个连续区域；逗号通常表示多个独立参数。函数参数可能是数字、文本、单元格引用、区域或其他函数的结果。

=IF(C2>=60,"合格","不合格")
=ROUND(AVERAGE(B2:B10),2)
=SUM(B2:B10,C2:C10)

第一条根据 C2 的值返回文字；第二条先计算平均值，再保留两位小数；第三条把两个区域的数值相加。函数可以嵌套，但嵌套越深越难检查，复杂公式应尽量拆成辅助列或多个步骤。

如果公式无法输入，先检查分隔符：多数英文区域设置使用逗号，但有些系统会依据区域设置要求使用分号。函数名称、参数分隔符和日期格式也可能受 Excel 语言及操作系统区域设置影响。

二、单元格引用：复制公式时最容易出错的部分

公式中的引用决定了复制或填充公式时，地址如何变化。Excel 常见的引用方式有三种：

• 相对引用：A1。向下复制一行，通常会变成 A2。
• 绝对引用：$A$1。行号和列号都固定，复制时地址不变。
• 混合引用：$A1 固定列，或 A$1 固定行。

例如，D2 中的单价需要乘以固定税率单元格 H1：

=C2*(1+$H$1)

向下填充后，C2 会依次变为 C3、C4，而 H1 保持不变。输入公式时选中引用并按 F4，可以循环切换相对、绝对和混合引用；部分笔记本电脑还需要同时按 Fn。

如果引用另一个工作表的单元格，可写成 工作表名!A1；工作表名称含空格或特殊字符时，Excel 通常会用单引号括起名称，例如：

='一季度数据'!B2

三、求和、计数与条件统计

1. SUM：求和

SUM 用于把数字相加，常见于金额、数量和工时汇总：

=SUM(B2:B100)

函数会将区域中的数值纳入计算；文本通常不会作为数值相加。注意，单元格里看起来像数字的内容可能实际是文本，导致求和遗漏。可以通过检查单元格格式、使用“分列”或合适的数据转换方式排查，不要只靠更改单元格显示格式，因为显示格式不一定会把文本转换成数字。

2. COUNT、COUNTA 与 COUNTBLANK

这几个函数统计的对象不同：

=COUNT(B2:B100)
=COUNTA(B2:B100)
=COUNTBLANK(B2:B100)

• COUNT 统计包含数字的单元格；日期和时间在 Excel 中通常以数值形式存储，也会计入。
• COUNTA 统计非空单元格。返回空字符串 "" 的公式单元格通常也会被视为非空，因此可能造成“看起来空白但仍被计数”。
• COUNTBLANK 统计空白单元格，也可能把公式返回空字符串的单元格计为空白。

应根据“要统计数字”“要统计有内容的格子”还是“要统计空白”选择函数，不能把这几种口径混用。

3. SUMIF、SUMIFS 与 COUNTIF、COUNTIFS

按一个条件求和，可以使用 SUMIF。例如，A 列是部门，C 列是金额，统计“销售部”的金额：

=SUMIF(A2:A100,"销售部",C2:C100)

SUMIF 的基本参数依次是条件区域、条件、求和区域。如果省略求和区域，函数会对条件区域本身求和。条件可以写成文本、数字或比较表达式。要使用“大于 100”的条件，可写成：

=SUMIF(C2:C100,">100")

按多个条件求和，使用 SUMIFS；例如只统计销售部且金额大于 1000 的记录：

=SUMIFS(C2:C100,A2:A100,"销售部",C2:C100,">1000")

SUMIFS 的第一个参数是求和区域，后面按“条件区域、条件”的顺序成对提供参数。各个条件区域应与求和区域对应同样大小的行列范围，否则可能得到错误结果或意外汇总。

统计符合条件的行数可用 COUNTIF 或 COUNTIFS：

=COUNTIF(A2:A100,"销售部")
=COUNTIFS(A2:A100,"销售部",C2:C100,">1000")

包含通配符的文本条件也很常见：星号 * 匹配任意数量字符，问号 ? 匹配单个字符。比如统计以“华”开头的名称：

=COUNTIF(A2:A100,"华*")

如果要匹配实际的星号或问号，可用波浪号转义，例如 "~*" 表示星号本身。条件文本的大小写通常不敏感；若业务要求区分大小写，应采用其他处理方式，不能默认 COUNTIF 能区分。

四、条件判断：IF、AND、OR 与错误处理

1. IF：根据条件返回不同结果

IF 有三个参数：逻辑测试、条件为真时的结果、条件为假时的结果。后两个参数可以是文字、数字、单元格引用或公式。

=IF(B2>=60,"及格","未及格")

如果只在满足条件时返回内容，也可以把“否则”设为空字符串：

=IF(B2="","",B2*C2)

这可以避免输入值为空时显示 0，但要注意公式返回的 "" 并不等同于真正清空单元格，后续计数、筛选或导出时应按实际需要处理。

2. AND 与 OR：组合多个条件

AND 要求所有条件都成立，OR 则只要求至少一个条件成立：

=IF(AND(B2>=60,C2="完成"),"通过","检查")
=IF(OR(B2="紧急",C2>=7),"优先处理","普通")

如果判定条件较多，可将判断拆开检查，或在支持的 Excel 版本中使用 IFS，以提高可读性。不过，较新函数在旧版 Excel 中可能显示名称前带有 _xlfn.，或无法正确计算，交付文件前应确认目标用户的版本。

3. IFERROR：处理公式错误

IFERROR 在表达式返回错误时给出替代结果：

=IFERROR(A2/B2,0)

它可以避免报表中直接显示错误，但也可能把真实的数据问题隐藏掉。例如，除数为零与源数据缺失都被替换成 0 后，读者无法分辨两者。若需要只处理特定错误，较新的 Excel 可使用 IFNA 专门处理 #N/A；更稳妥的做法是先确认错误原因，再决定返回空白、说明文字还是默认值。

五、文本处理与数据清理

导入的数据常带有多余空格、格式不统一或混在一起的字段。以下函数可以帮助处理这些情况。

1. 拼接文本

使用 & 可以拼接文本和单元格内容：

=A2&" "&B2

例如把姓和名合并，并插入一个空格。也可以使用 CONCAT 或 TEXTJOIN。后者适合用指定分隔符拼接多个单元格，还能选择是否忽略空白值：

=TEXTJOIN(", ",TRUE,A2:C2)

TEXTJOIN 在较旧版本中可能不可用。拼接日期或金额时，单元格的显示格式未必会按预期保留，通常需要用 TEXT 明确格式：

=A2&"，"&TEXT(B2,"yyyy-mm-dd")

格式代码可能受区域设置影响；如果公式需要在不同语言环境间流转，应在目标环境中验证结果。

2. 清除空格与不可见字符

=TRIM(A2)
=CLEAN(A2)

TRIM 会处理常见的 ASCII 空格：移除开头和结尾空格，并把连续的这类空格压缩为一个。它不一定能清除从网页复制来的不换行空格等字符。CLEAN 主要移除文本中的部分非打印字符，但也不是通用的字符清理工具。如果处理后仍无法匹配，可能需要识别具体字符并用 SUBSTITUTE 替换。

=SUBSTITUTE(A2,CHAR(160)," ")

该示例尝试把常见的不换行空格替换为普通空格，随后可再套用 TRIM。但不同来源的异常字符可能不同，不能假设某一个公式能清理所有编码问题。

3. 截取、查找与替换

=LEFT(A2,3)
=RIGHT(A2,4)
=MID(A2,2,5)
=FIND("-",A2)
=SUBSTITUTE(A2,"旧名称","新名称")

LEFT、RIGHT 和 MID 按字符位置截取文本；FIND 返回查找字符的位置，并区分大小写；找不到内容时会返回错误。若不需要区分大小写，可考虑 SEARCH。在按固定长度截取之前，先检查文本长度是否一致，否则可能误切字段。

六、查找数据：XLOOKUP、VLOOKUP 与 INDEX/MATCH

查找函数常用于根据商品编号取出价格，或根据员工编号匹配部门。选择函数时，应先确认查找方向、匹配方式、重复键处理方式和 Excel 版本。

1. XLOOKUP：新版本中的直接查找方式

假设 A 列是商品编号，B 列是价格，E2 是待查编号：

=XLOOKUP(E2,A2:A100,B2:B100,"未找到",0)

参数依次表示查找值、查找区域、返回区域、未找到时的结果和匹配模式。最后的 0 表示精确匹配。查找区域和返回区域应具有对应的行列结构。XLOOKUP 在较新的 Microsoft 365 和部分新版 Excel 中提供，旧版不一定支持；与使用旧版 Excel 的用户共享文件前要先验证。

如果查找值在表格中重复，通常返回符合搜索方向的第一个匹配项；重复键可能意味着源数据不唯一，不能把“查到一个结果”误认为数据关系正确。

2. VLOOKUP：兼容性较好的传统写法

=VLOOKUP(E2,A2:C100,3,FALSE)

此公式在 A 列查找 E2，并返回查找区域中第 3 列的值；最后的 FALSE 表示精确匹配。VLOOKUP 的重要限制是：查找列必须位于所选区域的第一列，返回列必须在其右侧。省略最后一个参数时，函数默认采用近似匹配，这要求查找列按升序排列；对编号、名称等一般匹配任务，显式写 FALSE 更安全。

返回列序号是固定数字。插入或调整列后，公式可能仍能计算，却返回错误列，因此维护频繁的表格要特别检查列序号。

3. INDEX 与 MATCH：适用于双向查找的组合

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

MATCH 的最后一个参数为 0 时进行精确匹配，返回目标值在区域中的相对位置；INDEX 再按这个位置从结果区域取值。与 VLOOKUP 相比，这种写法不要求结果列位于查找列右边。若查找区域没有匹配项，MATCH 会返回 #N/A，可视需要增加错误处理。

七、日期与时间计算

Excel 通常把日期和时间存为序列数值：日期部分代表日，时间部分代表一天中的小数。因此日期相减通常得到相隔的天数：

=B2-A2

如果 A2、B2 是开始日期和结束日期，该公式计算日历天数差。计算经过了多少个完整的工作日，可使用 NETWORKDAYS，并将节假日区域作为可选参数：

=NETWORKDAYS(A2,B2,H2:H20)

默认工作日是周一至周五，起止日期通常都计入结果；节假日区域应包含 Excel 能识别的日期值。若业务采用轮班或不同周末规则，可查看 NETWORKDAYS.INTL 对应的周末参数，而不是把“工作日”一概当作周一至周五。

提取年份、月份和日期可以使用：

=YEAR(A2)
=MONTH(A2)
=DAY(A2)

这类函数要求单元格内容能被 Excel 识别为日期。看起来像日期的文本未必是日期值，导入时的地区格式差异也可能造成月日颠倒。日期显示格式只控制显示方式，不会自动修正错误的底层日期。

用 TODAY() 可返回当前日期，NOW() 可返回当前日期和时间；它们是易变函数，工作簿重新计算时结果可能更新。需要保留某一时点的固定日期时，应按业务流程记录为值，而不是依赖会变化的公式。

八、动态数组与版本差异

支持动态数组的 Excel 版本中，部分公式可以一次返回多个结果，并自动向相邻单元格“溢出”。例如：

=UNIQUE(A2:A100)

该公式可返回区域中的唯一值。若溢出区域已有内容，公式会出现 #SPILL!；合并单元格、表格边界或其他空间限制也可能阻止结果展开。不要把溢出公式复制到结果区被占用的地方。

不同 Excel 版本对函数支持、动态数组行为、参数分隔符和计算方式可能不同。共享前可在目标版本打开文件进行检查；对需要兼容旧版的工作簿，优先采用目标版本支持的函数，避免把新函数写进关键流程后才发现对方无法计算。

九、公式限制、常见错误与排查顺序

公式计算受 Excel 版本和工作簿环境约束。以常见的现代桌面版 Excel 为例，一个工作表最多有 1,048,576 行、16,384 列；公式文本长度上限为 8,192 个字符，函数嵌套层数最多为 64 层，单个函数可使用的参数最多为 255 个。具体产品、版本和功能仍可能存在差别；接近限制时，应拆分计算并在实际目标版本中测试。

常见错误值通常提示不同问题：

• #DIV/0!：除数为 0，或参与除法的单元格为空。
• #N/A：常见于查找函数没有找到匹配项。
• #VALUE!：参数类型或运算对象不符合预期。
• #REF!：公式引用的单元格或区域已失效。
• #NAME?：函数名、名称或文本写法无法识别，也可能是当前版本不支持该函数。
• #SPILL!：动态数组结果无法展开到需要占用的单元格。

排查时可按以下顺序进行：先确认单元格里的是公式而不是以文本保存的公式；再检查括号是否配对、参数分隔符是否符合本机设置；然后确认引用区域、绝对引用和数据类型；最后检查查找键是否有前后空格、隐藏字符或重复项。复杂公式可逐段选中表达式，使用公式栏查看，并用 Excel 的“公式求值”功能逐步检查中间结果。

十、公式的安全边界

Excel 函数用于计算和整理数据，不是权限控制或安全隔离机制。隐藏工作表、保护工作表和限制单元格编辑，不能替代文件访问控制；也不能把公式判断当成防止伪造或授权的唯一措施。涉及个人信息、财务或敏感业务数据时，应控制文件访问权限，并按组织的存储、共享和保留规范处理。

打开工作簿前应关注来源。带宏的工作簿可能包含 VBA 代码，来自不可信来源的宏不应仅为查看数据而启用。公式也可能引用外部工作簿或数据源；打开时若出现更新链接提示，应先确认来源和用途，再决定是否更新。不要把公式结果视为已验证的事实，尤其是外部数据可能过期或链接断开的情况。

另一个重要场景是把 Excel 数据导出为 CSV 后交给其他系统。某些表格软件会把以 =、+、- 或 @ 开头的字段解释为公式。若数据包含用户输入，导出前应根据接收系统和使用软件的行为制定中和策略，并用实际目标软件测试；单靠 Excel 中显示为文本、或依赖某个公式处理，并不能保证所有 CSV 消费者都安全。数据清理和公式计算应与访问控制、导出校验等措施分开设计。

结语：先确认数据，再选择函数

编写 Excel 公式时，先明确要处理的数据类型、匹配规则和异常情况，再选函数。求和与统计看条件口径，查找要确认精确或近似匹配，文本处理要识别空格和隐藏字符，日期计算要确认底层值确实是日期。公式能自动化规则，却不会自动保证源数据正确。把引用、参数和错误处理写清楚，并在真实数据与目标版本中验证，通常比追求更短、更复杂的公式更可靠。