包含示例和 Copilot 提示的 30 个 Excel 公式和函数

准确、可靠的数据从正确的电子表格基础开始。 使用 减少手动错误，尽早发现错误，并构建在每次数据更改时保持的逻辑 Microsoft Excel 公式和函数。 从简单的汇总到跨表查找，每个公式都内置于Excel 网页版中，无需安装即可使用。

探索 30 个基本 Excel 公式和函数，范围从数据清理到分析。 查看分步指南和实际方案，了解每个函数或使用 Excel 中的 Copilot 提示示例使用 AI 根据说明生成公式。

清理数据进行分析

在运行任何计算之前，第一步是将数据进入一致的可用状态。 使用这些公式可删除多余的空格、合并或拆分文本以及标准化导入的数据，以便公式、查找、 图表和 数据透视表生成 可靠的结果。

TRIM

TRIM 从文本字符串的开头、结尾和单词之间删除多余的空格，在单词之间只留一个空格。

如何使用 TRIM

1. 选择要清理的文本旁边的空单元格。

2. 键入 =TRIM (A2) 。

3. 按 Enter，在列中填充公式。

何时使用 TRIM

• 清理从另一个系统导入的客户名称

• 修复破坏 XLOOKUP 或 COUNTIF 公式的间距问题

• 标准化产品 ID 和文本字段

试试这个 Copilot 提示符"通过删除多余的空格和标准化文本格式来清理此列。"单击复制此 Copilot 提示

CONCAT 和 TEXTJOIN

CONCAT 和 TEXTJOIN 将多个单元格中的值合并为单个文本字符串。 TEXTJOIN 在值之间添加所选分隔符。

如何使用 CONCAT 和 TEXTJOIN

1. 选择应显示合并文本的单元格。

2. 输入包含要联接的单元格的 CONCAT 或 TEXTJOIN 公式。

3. 按 Enter 创建组合值。

何时使用 CONCAT 和 TEXTJOIN

• 组合名字和姓氏

• 从单独的列生成完整地址

• 创建产品标签或显示名称

试试这个 Copilot 提示符"使用 TEXTJOIN 将 A、B 和 C 列中的值合并为单个地址。"单击复制此 Copilot 提示

LEFT 和 RIGHT

LEFT 从文本字符串的开头拉取一组字符，而 RIGHT 从末尾拉取它们，例如产品代码的前 3 个字符或电话号码的最后 4 位数字。

如何使用 LEFT 和 RIGHT

1. 选择应显示结果的单元格。

2. 键入 =LEFT (A2,3) 或 =RIGHT (A2,3) 。

3. 按 Enter 返回所需的字符。

何时使用 LEFT 和 RIGHT

• 从产品 ID 的前面拉取区域或分支代码

• 隔离文件扩展名或引用编号的最后一位数字

• 将固定长度前缀与标识符的其余部分分隔开来

试试这个 Copilot 提示符"从此表中每个产品 ID 中提取前三个字符。"单击复制此 Copilot 提示

SORT

SORT 在新位置生成区域的重新排序副本，使原始数据保持不变。

如何使用 SORT

1. 选择工作表的空白区域。

2. 使用源范围输入 SORT 公式。

3. 按 Enter 创建动态排序列表。

何时使用 SORT

• 按截止日期重新排列项目任务列表，而不会干扰源

• 查看 库存跟踪器 从最低到最高库存水平

• 在审阅或报告之前按排名顺序排列记录

试试这个 Copilot 提示符"创建一个 SORT 公式，该公式返回按截止日期排序的任务。"单击复制此 Copilot 提示

FILTER

FILTER 仅返回满足定义条件的范围中的行，在源更改时自动更新。

如何使用 FILTER

1. 选择工作表的空白区域。

2. 输入 FILTER 公式并定义条件。

3. 按 Enter 显示匹配的记录。

何时使用 FILTER

• 仅从完整客户端列表中拉取活动帐户

• 仅显示 中逾期的项目 项目跟踪器

• 将一个月的条目与全年数据集隔离

试试这个 Copilot 提示符"创建一个仅显示状态列等于“活动”的行的 FILTER 公式。"单击复制此 Copilot 提示

UNIQUE

UNIQUE 返回范围中的非重复值的列表，从结果中删除重复的条目。

如何使用 UNIQUE

1. 选择应显示唯一列表的空单元格。

2. 输入 UNIQUE 公式，然后选择要从中拉取不同值的范围。

3. 按 Enter 返回源范围中的每个不同值。

何时使用 UNIQUE

• 创建企业中唯一客户或产品的列表

• 从注册列表中删除重复的名称或 出席表

• 生成用于报告或分析的类别列表

试试这个 Copilot 提示符"创建从客户列返回唯一值列表的公式。"单击复制此 Copilot 提示

TEXTSPLIT

TEXTSPLIT 使用逗号、空格或连字符将文本分隔成不同的列或行。

如何使用 TEXTSPLIT

1. 选择要拆分的文本旁边的空单元格。

2. 使用源单元格和分隔符输入 TEXTSPLIT 公式。

3. 按 Enter 将文本拆分为单独的单元格。

何时使用 TEXTSPLIT

• 将全名分隔为名字和姓氏

• 拆分逗号分隔的标记或类别

• 将产品代码分解为单独的组件

试试这个 Copilot 提示符"将 A 列中的全名拆分为名字和姓氏列。"单击复制此 Copilot 提示

计算总计和平均值

使用计算公式回答日常电子表格问题，包括总计、平均值、计数和排名。

SUM

SUM 将添加所选区域或单元格集中的每个值。

如何使用 SUM

1. 选择应显示总计的单元格。

2. 输入 SUM 公式，然后选择要总计的单元格区域。

3. 按 Enter 计算总计。

何时使用 SUM

• 在 中添加每月支出 预算规划器

• 计算 总业务收入

• 对小时数、单位或数量求和

试试这个 Copilot 提示符"创建一个 SUM 公式，用于添加收入列中的所有值。"单击复制此 Copilot 提示

AVERAGE

AVERAGE 计算所选区域中的平均值。

如何使用 AVERAGE

1. 选择显示平均值的单元格。

2. 输入 AVERAGE 公式，然后选择要求平均值的范围。

3. 按 Enter 计算平均值。

何时使用 AVERAGE

• 查找季度内的平均订单值

• 计算整个团队的平均响应时间

• 查看平均每周小时数或成本

试试这个 Copilot 提示符"创建一个公式，用于计算每月支出列中的平均值。"单击复制此 Copilot 提示

MIN 和 MAX

MIN 返回范围中的最小值，MAX 返回最大值。

如何使用 MIN 和 MAX

1. 为结果选择一个空单元格。

2. 输入 MIN 或 MAX 公式，然后选择要检查的值范围。

3. 按 Enter 返回最低值或最大值。

何时使用 MIN 和 MAX

• 在 中发现离群值 费用报表

• 检查数据输入是否保持在预期范围内

• 在销售列中显示表现最佳和最差的人

试试这个 Copilot 提示符"使用 MIN 和 MAX 公式查找销售列中的最高值和最低值。"单击复制此 Copilot 提示

COUNT 和 COUNTA

COUNT 对包含数字的单元格进行计数，而 COUNTA 对不为空的任何单元格进行计数。

如何使用 COUNT 和 COUNTA

1. 选择应显示计数的单元格。

2. 输入 COUNT 公式对数字进行计数，或输入 COUNTA 公式来计算非空白单元格。

3. 选择要计数的范围，然后按 Enter。

何时使用 COUNT 和 COUNTA

• 对收入列中的数字条目进行计数

• 在跟踪器中对已完成的字段进行计数

• 检查包含数据的行数

试试这个 Copilot 提示符"计算此表中有多少行具有值，并说明 COUNT 或 COUNTA 是更好的公式。"单击复制此 Copilot 提示

COUNTIF

COUNTIF 对满足单个条件的单元格进行计数。

如何使用 COUNTIF

1. 选择应显示结果的单元格。

2. 输入包含范围和条件的 COUNTIF 公式。

3. 按 Enter 对匹配单元格进行计数。

何时使用 COUNTIF

• 计数 标记为“已付费”的发票

• 对来自一个区域的订单进行计数

• 对具有特定状态或类别的响应进行计数

试试这个 Copilot 提示符"创建一个 COUNTIF 公式，用于计算标记为“完成”的任务数。"单击复制此 Copilot 提示

SUMIF

SUMIF 添加满足单个条件的值。

如何使用 SUMIF

1. 选择应显示总计的单元格。

2. 输入包含条件范围、条件和求和范围的 SUMIF 公式。

3. 按 Enter 计算条件总计。

何时使用 SUMIF

• 从一个渠道添加销售额

• 在一个类别中汇总费用

• 计算 一个项目或客户端的时间表小时数

试试这个 Copilot 提示符"创建一个 SUMIF 公式，该公式仅在频道列等于 Online 的情况下总计销售额。"单击复制此 Copilot 提示

RANK.EQ

排名。EQ 返回某个数字在列表中的位置，从最高到最低或相反。

如何使用 RANK。情 商

1. 选择要排名的值旁边的空单元格。

2. 输入 RANK。具有值和比较范围的 EQ 公式。

3. 按 Enter，在列中填充公式。

何时使用 RANK。情 商

• 按收入对销售人员进行排名

• 按转换率对市场营销活动结果进行排序

• 确定性能最高的产品或区域

试试这个 Copilot 提示符"使用 RANK 对每个销售值进行排名，从最高到最低。EQ 公式。"单击复制此 Copilot 提示

在公式中使用逻辑并处理错误

逻辑公式可帮助电子表格响应不同的条件。 使用它们可基于数据显示不同的结果，将多个规则合并到一个测试中，或者在 出现 Excel 错误 。

IF

当条件为 true 时，IF 返回一个结果;如果条件为 false，则返回另一个结果。

如何使用 IF

1. 选择应显示结果的单元格。

2. 输入条件、真实结果和 false 结果的 IF 公式。

3. 按 Enter 返回匹配结果。

何时使用 IF

• 将任务标记为“正轨”或“需要评审”

• 将分数标记为“高于目标”或“低于目标”

• 将发票标记为“已付”或“逾期发票”

试试这个 Copilot 提示符"创建一个 IF 公式，该公式在 B 列中的值高于 80% 时显示“正轨”，如果不是，则需要评审。"单击复制此 Copilot 提示

IFERROR

IFERROR 捕获公式返回的任何错误，并将其替换为指定的值，例如短划线、零或纯语言注释。

如何使用 IFERROR

1. 选择应显示公式结果的单元格。

2. 将原始公式包装在 IFERROR 中。

3. 添加值或消息以显示是否出现错误。

何时使用 IFERROR

• 将查找错误替换为空白单元格或消息

• 在缺少数据时使报表保持可读性

• 避免共享工作表中的可见公式错误

试试这个 Copilot 提示符"将此查找公式包装在 IFERROR 中，以便在没有匹配结果时显示“找不到”。"单击复制此 Copilot 提示

AND and OR

AND 检查是否所有条件均为 true，而 OR 检查是否至少有一个条件为 true。

如何使用 AND 和 OR

1. 选择应显示逻辑结果的单元格。

2. 输入包含要测试的条件的 AND 或 OR 公式。

3. 对自定义结果单独使用公式或 IF 内部使用公式。

何时使用 AND 和 OR

• 检查是否满足多个审批条件

• 标记与多个类别之一匹配的记录

• 生成更精确的 IF 公式

试试这个 Copilot 提示符"创建一个公式，仅当状态为“完成”且审批列显示“是”时，才将任务标记为“就绪”。"单击复制此 Copilot 提示

SWITCH

SWITCH 将一个值与选项列表进行比较，并返回匹配的结果。

如何使用 SWITCH

1. 选择应显示结果的单元格。

2. 输入具有要检查和可能的匹配项的值的 SWITCH 公式。

3. 为不匹配的值添加默认结果。

何时使用 SWITCH

• 将短状态代码转换为完整标签

• 基于单个字段分配类别

• 将长嵌套 IF 公式替换为更简洁的选项

试试这个 Copilot 提示符"创建一个 SWITCH 公式，用于将状态代码转换为标签，例如“未启动”、“正在进行”、“完成”和“已阻止”。"单击复制此 Copilot 提示

跨数据集查找和匹配信息

查找公式跨表连接相关信息。 使用它们来匹配 ID、检索值或查找项的位置，而无需手动扫描行。

XLOOKUP

XLOOKUP 可以在任何方向搜索范围并返回相关值，并且当找不到匹配项时，它可以返回设置的值。

如何使用 XLOOKUP

1. 选择应显示匹配结果的单元格。

2. 输入具有查找值、查找范围和返回范围的 XLOOKUP 公式。

3. 按 Enter 返回匹配值。

何时使用 XLOOKUP

• 将订单 ID 与客户名称匹配

• 从产品列表中拉取价格

• 从查找列不是第一列的表中返回值

试试这个 Copilot 提示符"创建一个 XLOOKUP 公式，该公式将 A 列中的产品 ID 与另一个表中的产品名称相匹配。"单击复制此 Copilot 提示

VLOOKUP

VLOOKUP 可以从左到右搜索表的第一列，并从同一行中的指定列返回值。

如何使用 VLOOKUP

1. 选择应显示匹配结果的单元格。

2. 输入具有查阅值、表范围、列号和匹配类型的 VLOOKUP 公式。

3. 按 Enter 返回匹配值。

何时使用 VLOOKUP

• 使用较旧的 或 共享电子表格

• 将 ID 与简单表中的值匹配

• 从左到右查找信息

试试这个 Copilot 提示符"创建一个 VLOOKUP 公式，该公式将每个员工 ID 与查找表中的员工姓名相匹配。"单击复制此 Copilot 提示

MATCH

MATCH 返回值在列表中的位置，例如查找 库存产品名称 是列中的第三项。

如何使用 MATCH

1. 选择应显示位置的单元格。

2. 输入具有查找值和查找范围的 MATCH 公式。

3. 按 Enter 返回项位置。

何时使用 MATCH

• 查找值在列表中的位置

• 查找表中的列位置

• 与 INDEX 配对，进行灵活查找

试试这个 Copilot 提示符"创建一个 MATCH 公式，用于在此列表中查找所选产品代码的位置。"单击复制此 Copilot 提示

INDEX

INDEX 从区域或表中的特定位置检索值。

如何使用 INDEX

1. 选择应显示结果的单元格。

2. 输入包含数组、行号和列号的 INDEX 公式。

3. 按 Enter 返回位于该位置的值。

何时使用 INDEX

• 从已知行和列返回值

• 使用 MATCH 生成灵活的查找公式

• 从 VLOOKUP 无法访问的搜索列左侧的列中拉取值

试试这个 Copilot 提示符"创建一个 INDEX 和 MATCH 公式，用于返回每个产品代码的价格。"单击复制此 Copilot 提示

分析和汇总大型数据集

使用这些公式可跨多个条件计算值、汇总筛选列表以及比较不同类别的平均值。 他们转向 在不更改源数据的情况下， 将大型表 转换为重点结果。

SUMIFS

SUMIFS 将同时满足两个或更多条件的列中的值相加。

如何使用 SUMIFS

1. 选择应显示结果的单元格。

2. 首先输入包含和范围的 SUMIFS 公式。

3. 添加每个条件范围及其条件。

何时使用 SUMIFS

• 一个区域中一个产品的总收入

• 添加员工在特定周登录的小时数

• 在单个类别中对单个月的支出求和

试试这个 Copilot 提示符"使用 SUMIFS 公式计算区域 B 中产品 A 的总收入。"单击复制此 Copilot 提示

SUBTOTAL

SUBTOTAL 仅使用可见行计算筛选列表的总和、平均值和计数等结果。

如何使用 SUBTOTAL

1. 选择应显示摘要的单元格。

2. 输入包含函数编号和范围的 SUBTOTAL 公式。

3. 将筛选器应用于表以更新可见结果。

何时使用 SUBTOTAL

• 仅汇总筛选列表中的可见行

• 应用筛选器后查看总计

• 在不更改源数据的情况下创建快速摘要

试试这个 Copilot 提示符"创建一个 SUBTOTAL 公式，该公式仅求和此筛选表中的可见行。"单击复制此 Copilot 提示

AVERAGEIF

AVERAGEIF 计算满足单个条件的值的平均值。

如何使用 AVERAGEIF

1. 选择显示平均值的单元格。

2. 输入包含条件范围、条件和平均值范围的 AVERAGEIF 公式。

3. 按 Enter 计算条件平均值。

何时使用 AVERAGEIF

• 查找一种产品的平均销售额

• 按类别计算平均支出

• 查看一个组的平均分数

试试这个 Copilot 提示符"创建 AVERAGEIF 公式，用于计算东部区域客户的平均订单值。"单击复制此 Copilot 提示

处理日期和截止时间

日期公式计算截止时间、度量事件之间的时间，并使计划保持最新。 使用它们跟踪任务， 查看日程表，并生成随着日期更改而更新的报表。

TODAY

TODAY 返回当前日期，并在每次重新计算工作簿时更新。

如何使用 TODAY

1. 选择应显示当前日期的单元格。

2. 输入不带任何参数的 TODAY 公式。

3. 按 Enter 显示今天的日期。

何时使用 TODAY

• 计算截止日期之前的天数

• 标记过期的任务

• 创建基于当前日期更新的报表

试试这个 Copilot 提示符"创建一个公式，将每个截止日期与今天的日期进行比较，并标记过期的任务。"单击复制此 Copilot 提示

DATEDIF

DATEDIF 根据 以天、月或年为单位计算两个日期之间的差值 日历。

如何使用 DATEDIF

1. 选择应显示日期差异的单元格。

2. 输入包含开始日期、结束日期和单位的 DATEDIF 公式。

3. 按 Enter 返回日期之间的时间。

何时使用 DATEDIF

• 计算 学生作业持续时间

• 查找请求和完成日期之间的天数

• 测量年龄、任期或已用时间

试试这个 Copilot 提示符"创建一个 DATEDIF 公式，用于计算每个项目从开始日期到完成日期所花费的天数。"单击复制此 Copilot 提示

WORKDAY

WORKDAY 返回一个工作天之前或之后的日期，可以排除周末和节假日。

如何使用 WORKDAY

1. 选择应显示截止时间的单元格。

2. 输入包含开始日期和工作日数的 WORKDAY 公式。

3. 如果 schedule 应排除它们。

何时使用 WORKDAY

• 计算项目截止日期

• 计划后续日期

• 规划排除周末的时间线

试试这个 Copilot 提示符"创建一个 WORKDAY 公式，将 10 个工作日添加到每个开始日期并排除周末。"单击复制此 Copilot 提示

在 Excel 中使用 Copilot 创建和理解公式

Copilot 帮助 从纯语言指令 生成公式 ，并确定 Excel 中公式错误的原因。 AI 电子表格助手提供了建议，因此，在将结果应用于重要工作簿之前，查看结果仍然至关重要。 下面是使用 Copilot 的一些方法：

• 让 Copilot 在聊天中用日常术语解释不熟悉的公式。

• 描述 Copilot 聊天中所需的计算，并让 Copilot 建议公式。 在将建议的公式添加到共享或影响较高的电子表格之前，请查看该公式。

• 请求 Copilot 诊断错误，例如公式损坏或电子表格格式缺失。

注意：在 Excel 中使用 Copilot 需要Microsoft 365 个人版或家庭订阅 ( AI 额度计划 (opens in a new tab)) ，a Microsoft 365 高级版 (opens in a new tab)订阅或 商业智能 Microsoft 365 Copilot 副驾驶®订阅 (opens in a new tab)。

使用正确的公式， Excel 电子表格制作者可以有效地组织杂乱的数据、计算值、检查条件、匹配信息以及汇总大型表。 使用本指南从与手头任务匹配的公式开始，或使用 Excel 中的 Copilot 以探索公式选项以使用 AI 完成任务。