每个人都该掌握的 15 个 Excel 基本公式

通过动手练习学习核心 Excel 公式，尽在我们的 Introduction to Excel 课程。

为什么 Excel 公式很重要？

Excel 公式之所以重要，原因包括：

1. 效率：自动化重复任务，节省时间并减少手动错误。
2. 数据分析：多样化的公式能够实现深入的数据分析，为决策提供支持。
3. 准确性：公式确保结果一致且准确，这在金融与会计等领域尤为关键。
4. 数据处理：支持对大型数据集的高效排序、筛选与处理。
5. 易用性：Excel 提供友好的操作界面，使非技术用户也能进行复杂分析。
6. 通用性：广泛应用于各行各业，熟练掌握 Excel 公式有助于提升就业与职业发展。
7. 自定义：Excel 提供可自定义的公式选项，满足特定数据处理需求。

简言之，Excel 公式是实现高效数据管理、分析与决策的基石工具。

如何使用 Excel 公式

在 Excel 中添加公式相对简单。如果您熟悉任何商业智能软件，这会很自然。

使用公式最有效、最快的方式是手动输入。在下面的示例中，我们将计算表格中运动员的 BMI（身体质量指数）。

BMI = 体重（KG）/（身高（m））²

1. 选择用于显示结果的单元格。您可以使用鼠标选择，也可用方向键定位。

2. 在单元格中键入 =。等号会出现在单元格和编辑栏中。

3. 输入要参与计算的单元格地址。本例中为 E2（体重/KG）。

4. 输入除号 /

5. 将身高从厘米转换为米，需要用 D2 除以 100。

6. 对身高取平方 ^2，然后按下 Enter。

注意：要获取任意单元格的地址，请查看列名（A、B、C，……）并与行号（1、2、3，……）组合。例如，A2、B5、C12。

完成了；我们已成功计算出 A Dijiang 的 BMI。

添加 Excel 公式 | 作者

我们也可以借助图形界面（GUI）添加公式，这也很简单。

在下面的示例中，我们将使用 GUI 添加一个 IF() 函数，把 “M” 转换为 “Male”，把 “F” 转换为 “Female”。

1. 单击编辑栏旁的 fx 按钮。

2. 会弹出一个窗口，显示最常用的函数。

3. 您可以搜索特定公式，或滚动选择。本例中，我们将指定 IF() 函数。

4. 在 logical_test 参数中添加逻辑 B2="M”。

5. 在 value_if_true 中添加 “Male”，在 value_if_false 中添加 “Female”。

其工作方式类似 if-else 语句。如果 logical_test 为 TRUE，公式返回 “Male”，否则返回 “Female”。

使用 UI 添加 Excel 公式 | 作者

如何为整列插入公式

我们已经学会向单行添加公式。现在来学习如何将同一公式应用到整列。

有多种方式可以添加公式：

• 拖动填充柄：选中单元格后，右下角会出现一个小绿方块，即填充柄。按住并向下拖动至最后一行，常用于将公式应用到选定的多行。
• 双击填充柄：选中包含公式的单元格，双击填充柄。几秒内即可将公式应用到整列。
• 快捷键：选中包含公式的单元格以及其下方的空白单元格，按 CTRL + D 应用公式。请确保未选择公式单元格上方的内容。
• 复制粘贴：复制包含公式的单元格（CTRL + C），选择列中的空白行，然后粘贴（CTRL + V）。注意不要用填充柄来选择行。

下图展示了将公式应用于多个单元格的所有方法。

整列应用公式 | 作者

Excel 基本公式

现在我们来回顾 Excel 中的重要公式。首先从常见且重要的函数讲起，它们是内置操作，可在公式中使用以执行特定任务。

本节将使用 DataCamp 的 Olympics 数据集的一个小子集。为简化起见，我们主要使用四位运动员的姓名、性别、年龄、身高和体重列。对于每个示例，您只需写出函数名和参数即可，没什么复杂的。

1. SUM()

SUM() 函数对选定单元格求和。适用于包含数值的单元格，需要两个或更多单元格。

本例中，我们对 C2 到 C5 的区域使用 SUM()，并将结果存放在 C6。它会将 24、23、21、31 相加。该函数也可作用于多列。

=SUM(C2:C5)

查看我们的完整求和指南以了解更多。

2. MIN() 和 MAX()

MIN() 函数需要一个单元格区域，并返回最小值。例如，我们希望在 E6 显示所有运动员的最小体重。MIN() 会找到最小值并返回 60。

=MIN(E2:E5)

MAX() 与 MIN() 相反，它会返回选定区域内的最大值。本例中返回 82。

=MAX(E2:E5)

3. AVERAGE()

AVERAGE() 用于计算选定单元格的平均值。您可以提供一个区域（C2:C5）或选择单个单元格（C2、C3、C5）。

要计算运动员的平均年龄，我们选择年龄列，应用平均函数，并将结果返回到 C7。它会将所选单元格的总和除以 4。

=AVERAGE(C2:C5) 

想了解更多，请参阅我们关于 AVERAGE() 的详细指南。

4. COUNT()

COUNT() 用于统计所选单元格中包含数字的数量。它不会计数空白单元格或非数字格式。

我们将统计体重的数量，由于没有缺失或字符串，返回值为 4。

=COUNT(E2:E5)

若需统计所有类型（日期时间、字符串、数值）的单元格，请使用 COUNTA()。

COUNTA() 不计数缺失值。对于空白单元格，请使用 COUNTBLANK()。

更多 COUNT() 的用法示例可参见我们的独立指南。

5. POWER()

前面我们学习了使用 ^ 来取幂，这也是 POWER() 的一种替代写法。可以使用 x^y 或 POWER(x,y)；在嵌套公式中 POWER 更清晰，可对单元格进行平方、立方或任意次幂。

本例中，我们先将 D2 除以 100 得到米为单位的身高，再用 POWER() 的第二个参数为 2 进行平方。

=POWER(D2/100,2)

6. CEILING() 和 FLOOR()

正如我们在Excel ROUND() 指南中所述，CEILING() 函数会将数字向上舍入到最近的指定倍数。例如：

• =CEILING(3.24, 1) 将 3.24 向上舍入为 4。
• =CEILING(3.24, 5) 将 3.24 向上舍入为 5。

看下面的示例，我们把 F2 按 1 的倍数取整，得到 4。

=CEILING(F2,1)

可选：=CEILING.MATH(F2,1)

相对地，FLOOR() 函数会将数字向下舍入到最近的指定倍数。例如：

• =FLOOR(3.24, 1) 将3.24 向下舍入为 3。
• =FLOOR(3.24, 5) 将3.24 向下舍入为 0。

如下例所示，它没有把 3.24 舍入为 4，而是向下舍入为 3。

=FLOOR(F2,1)

可选：=FLOOR.MATH(F2,1)

这些函数在处理标准化间隔（如价格、时间或数量按固定单位取整）时尤其有用。

7. CONCAT()

CONCAT() 用于连接字符串。相比旧版 CONCATENATE，优先使用 CONCAT 或 TEXTJOIN；当需要分隔符或忽略空白时，使用 TEXTJOIN。 例如，若要将运动员的年龄与性别连接，可使用 CONCAT()。函数会自动把年龄中的数值转为字符串并拼接。

“24”+“M” = “24M”

=CONCAT(C2,B2)

想了解其更多用法，请参阅我们的Excel CONCAT() 使用指南。

8. TRIM()

正如我们在TRIM() 全面指南中所述，该函数用于删除文本开头、中间和结尾的多余空格。它常用于识别重复值——有时仅仅因为多了空格就会被认为是唯一值。

例如：

1. 在 A3 “A   Lamusi” 中存在两个多余空格，使用 TRIM() 可成功删除。
2. 在 A4 “ Christie Jacoba Aaftink” 开头存在多余空格，无需写任何复杂函数，TRIM() 即可删除。

=TRIM(A4)

9. REPLACE() 和 SUBSTITUTE()

REPLACE() 用于将字符串的一部分替换为新字符串。

REPLACE(old_text, start_num, num_chars, new_text)

• old_text：原始文本或包含文本的单元格。

• start_num：开始替换字符的位置索引。

• num_chars：要替换的字符数。

• new_text：用于替换旧文本的新文本。

例如，我们将 “A Dijiang” 改为 “B Dijiang”，提供起始位置 1、替换字符数 1，以及新字符 “B”。

=REPLACE(A2,1,1,"B")

SUBSTITUTE() 与 REPLACE() 类似。但它无需提供位置与字符数，只需提供旧文本与新文本。

SUBSTITUTE(text, old_text, new_text, [instance_num])

注意： 使用 instance_num 可仅替换特定出现次数

本例中，我们把“Jacoba”替换为“Rahim”，使 A4 显示为 “Christine Rahim Aaftink”。

该函数很实用，因为它不会改变不包含“Jacoba”的文本，如下方 A5 “Per Knut Aaland” 所示。而 REPLACE() 每次都会进行位置替换。

=SUBSTITUTE(A4,"Jacoba","Rahim")

10. LEFT()、RIGHT() 和 MID()

LEFT() 函数从字符串开头返回指定数量的字符。

例如，要从“Christine Jacoba Aaftink”中显示名字，可使用 LEFT() 并指定 9 个字符。结果将显示前 9 个字符：“Christine”。

=LEFT(A2,9)

MID() 函数需要起始位置和长度，从中间提取字符。

例如，要显示中间名，从第 11 位的 “J” 开始，长度为 6（“Jacoba”）。

=MID(A2,11,6)

RIGHT() 函数从字符串末尾返回指定数量的字符。您只需提供字符数。

例如，要显示姓氏 “Aaftink”，我们用 RIGHT() 并指定 7 个字符。

=RIGHT(A2,7)

11. UPPER()、LOWER() 和 PROPER()

UPPER()、LOWER()、PROPER() 是基础字符串处理函数。您也能在 Tableau 或 Python 中找到类似功能。这些函数仅需提供文本、字符串单元格位置或字符串单元格区域。

UPPER() 会将文本全部转为大写。

=UPPER(A1)

LOWER() 会将所选文本转为小写。

=LOWER(A1)

PROPER() 会将字符串转为“首字母大写”格式，例如每个单词的首字母大写，其余为小写。

=PROPER(A1)

12. NOW() 和 TODAY()

NOW() 返回当前日期和时间，TODAY() 仅返回当前日期。这两个函数很简单，我们还可用它们从任意日期时间单元格中提取日、月、年、时和分。

下面的示例返回当前日期时间。

=NOW()

要提取秒数，请使用 SECOND() 函数。

=SECOND(NOW())

类似地，TODAY() 只会返回当前日期。

=TODAY()

要提取日期中的“日”，请使用 DAY() 函数。

此外，您还可以从日期时间字段中提取月份、年份、星期、星期名称、小时和分钟。

=DAY(TODAY())

13. DATEDIF()

这是时间序列数据集中使用最广的函数之一。DATEDIF() 用于计算两个日期之间的差值，并根据您的需要返回天、月、周或年。

注意： DATEDIF 是兼容性遗留函数，在某些单位（如 “MD”）下可能不够准确。尽可能考虑使用 DAYS()、YEARFRAC() 或 DATE 运算。

下面的示例中，我们通过为单位参数提供 “d” 返回天数差。请确保函数的第一个参数为开始日期，第二个参数为结束日期。

start_date < end_date

=DATEDIF(A2,B2,"d")

14. VLOOKUP() 和 HLOOKUP()

本节使用的 worksheet1 包含了 Olympics 数据集的全部数据。

worksheet1

VLOOKUP() 会在表数组的最左列查找值，并从同一行的指定列返回对应值。

注意： VLOOKUP() 和 HLOOKUP() 属于遗留/兼容性函数。Microsoft 建议在新工作中使用 XLOOKUP()，它可替代两者，默认精确匹配、可任意方向查找，并提供 if_not_found 参数。您可阅读我们的XLOOKUP 使用指南，以及XLOOKUP 与 VLOOKUP 对比了解差异。

VLOOKUP(lookup_value, table_array, col_index, range_lookup)

• lookup_value：要查找的值，位于第一列。

• table_array：表格、工作表或多列选定单元格的范围。

• col_index：要提取值的列序号。

• range_lookup：“TRUE”为近似匹配（默认），“FALSE”为精确匹配。

本例中，我们在 worksheet1（B2:H20）的所选列行中查找 A Dijiang（A2）。VLOOKUP() 会在 worksheet1 的姓名列进行查找，并返回第 6 列的值，即团队 “China”。

=VLOOKUP(A2,worksheet1!B2:H20,6,FALSE)

HLOOKUP() 则是在第一行查找值，并从同一列、您指定的行返回结果。

HLOOKUP(lookup_value, table_array, row_index, range_lookup)

本例中，我们将在 D8 单元格显示 A Dijaing 的性别。HLOOKUP() 会在第一行查找姓名，并从同一列的第 2 行返回 “M”。两处的 range_lookup 均设为 FALSE 以进行精确匹配。

=HLOOKUP(B1, B1:E5, 2, FALSE)

15. IF()

IF() 函数非常直观，类似于编程语言中的 if-else。我们提供判断逻辑：若逻辑为真返回某值，否则返回另一值。

例如，若某位运动员的 BMI 小于 24.9，函数返回 “Fit”，否则返回 “Unfit”。这在将数值转换为类别时很有用。

=IF(G2<24.9,"Fit","Unfit")

Excel 中的其他类型公式

现在我们来看看其他类型的 Excel 公式，包括使用运算符的公式、数组公式，以及基于公式的条件格式。

使用运算符的公式

即便是 A1 + A2 这样简单的表达式也是公式，因为它使用单元格引用和运算符进行计算，与更复杂的公式本质相同。Excel 还会根据 A1 或 A2 的变化动态更新结果。以下均属于公式：

• 加法：=A1 + B1

• 减法：=A1 - B1

• 乘法：=A1 * B1

• 除法：=A1 / B1

这些内容看似基础，却是更高级模式（如序列与递推计算）的基础。如下可创建一个斐波那契序列：

• 启用迭代计算：依次进入 文件 > 选项 > 公式，勾选 启用迭代计算，并设置最大迭代次数。
• 在 A3 输入以下内容（假设 A1=0，A2=1），然后向下填充。

数组公式

数组公式可一次执行多项计算，并返回单个结果或将多个结果填充到一片单元格区域。在涉及多条件或处理大型数据集的计算中尤为有用。

例如：对某一区域的平方值求和 =SUM(A1:A5^2)。如果您考虑在 Excel 中进行回归分析，这可能会很有用。它是公式，因为以等号开头并执行计算，但又不仅仅是前面的 SUM() 示例，因为这里还使用了插入符号（幂运算）。

基于公式的条件格式

Excel 允许通过自定义公式来应用条件格式，而不仅仅依赖预设规则。例如，基于跨多列的行级条件来设置格式，这类逻辑在常规单元格公式中难以实现，但在条件格式中可以正确应用。

例如，我们希望当某行的订单状态为 “Pending” 时高亮整行。假设表格的 C 列包含订单状态（"Pending" 或 "Shipped"），步骤如下：

1. 选择整张表的数据范围（例如 A2:E100）。
2. 前往 开始 > 条件格式 > 新建规则。
3. 选择 使用公式确定要设置格式的单元格。
4. 输入公式：=$C2="Pending"
5. 单击 格式，选择填充颜色并确认。

该公式看起来与可输入到单个单元格中的公式类似。但在条件格式中，它会相对每一行进行应用，并作用于整个选区。若在单元格中，=$C2="Pending" 只会返回 TRUE 或 FALSE，而在条件格式中，Excel 会基于此逻辑动态高亮整行。

用 Excel 为团队赋能

我们已经涵盖了每个人都应掌握的 Excel 公式，但要在团队内同时提升多人能力并非易事。我们有一篇单独的深入指南讨论企业 Excel 培训，这里也值得一提。

我们已经明确，Excel 不仅仅是电子表格工具——它常常是高效数据管理、财务分析和决策的关键。因此，投资 Excel 培训可以提升团队的生产力、准确性与整体效率。主要收益包括：

• 数据分析：高级 Excel 技能有助于更深入地解读数据，支持更优决策。
• 效率：通过电子表格实现自动化可减少手工工作与错误。
• 财务精度：精通 VLOOKUP 与数据透视表等功能可提升财务管理。
• 运营优化：Excel 的数据处理工具可改进大数据集的处理方式，优化运营。
• 竞争优势：熟练运用 Excel 有助于优化流程并获得更深洞察。

如果您希望为团队进行从基础到高级公式的 Excel 培训，DataCamp For Business 是理想选择。无论您的团队是 2 人还是 10,000 人，DataCamp For Business 都提供丰富的 Excel 与电子表格学习资源、可定制学习路径以规模化且量身定制学习体验，以及详尽的报告与洞察，帮助衡量培训成效。立即申请演示，开始您的旅程。

结语

Microsoft Excel 是一款功能强大且易于使用的数据分析与报告工具。本文我们了解了基础 Excel 公式的重要性，以及它们如何赋予我们执行复杂计算的能力。此外，我们学习了在工作表中添加公式的多种方法，并详细讲解了常用的基础公式。

如需进一步学习 Excel 及其在数据分析中的应用，请参加我们的 Data Analysis in Excel 课程。该课程将教授数据探索、处理与分析的基础。如果您偏好 Google 生态并使用 Google 表格，可尝试 Data Analysis in Spreadsheet。

您也可以更进一步，学习电子表格基础。您将学习数据分析、高级数据处理技巧、数据透视表和数据可视化。最后，欢迎查看我们的 Excel 公式速查表作为快速参考，并进一步了解 Excel 中的数据类型。