数据分析必备的 10 大 Excel 公式清单

作为一名数据分析师，我每天都把 Excel 作为最强大的工具之一来使用。它不仅仅是整理数据；更重要的是把数据转化为有意义的内容。Excel 最强大的方面之一就是公式。这些公式是将原始数据转化为可执行洞察的支柱。

在本文中，我将带你了解每位数据分析师都必须掌握的 10 个 Excel 公式。我会用简单的示例把它们拆解开来，方便你立刻上手应用。如果你想跳过数据收集和清洗，可以看看我整理的最佳数据集提供商清单。

每位数据分析师都应该了解的 10 个 Excel 公式

作为数据分析师，掌握 Excel 公式对于将原始数据转化为可执行洞察至关重要。这些公式能简化数据分析流程，帮助你发现模式、进行计算并做出明智决策。以下是每位数据分析师工具箱中都应具备的 10 个必备 Excel 公式。

1. SUM

SUM 函数是最基础、但也是最有用的 Excel 函数之一。它可以对一段单元格范围内的数字求和。例如，当你在分析销售数据时，可以快速汇总某一特定时期的总销售额。

语法: =SUM(number1, [number2], …)

示例: 如果你的销售数据位于 A2 到 A10 单元格，可以使用 =SUM(A2:A10) 进行求和。

使用场景: 求和通常是数据分析的第一步，用于快速评估数据集中的总量。

2. AVERAGE

AVERAGE 函数用于计算一组数字的平均值。当你想确定数据集的平均水平时非常有用，例如每月平均销售额或问卷受访者的平均得分。

语法: =AVERAGE(number1, [number2], …)

示例: 要计算 B2 到 B15 单元格中学生成绩的平均分，使用 =AVERAGE(B2:B15)。

使用场景: AVERAGE 在分析随时间变化的数据趋势时必不可少。它有助于平滑短期波动并揭示潜在趋势。

3. IF

IF 函数是一种条件公式，会根据给定条件为真或为假执行不同操作。它非常适合用于数据分类，例如根据阈值将销售额标记为“高”或“低”。

语法: =IF(logical_test, value_if_true, value_if_false)

示例: 若要将 C2 单元格的销售额在大于 1000 时标记为“High”，否则标记为“Low”，使用 =IF(C2>1000, “High”, “Low”)。

使用场景: IF 函数在将数据划分为有意义的类别方面非常重要，从而支持更有针对性的分析。

4. VLOOKUP

VLOOKUP（纵向查找）是 Excel 中最常用的函数之一，用于在大型表格或数据库中查找数据。它会在表格的第一列中搜索某个值，并从同一行的其他列返回对应值。

语法: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

示例: 要在位于 A2

的表格中查找 ID 为 103 的产品价格，使用 =VLOOKUP(103, A2:C10, 3, FALSE)。

使用场景: VLOOKUP 非常适合基于共同键将不同表格的数据合并，例如将客户姓名与其对应的购买记录匹配起来。

5. HLOOKUP

HLOOKUP（横向查找）与 VLOOKUP 类似，但它是在表格的第一行中搜索某个值，并从同一列的其他行返回对应值。

语法: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

示例: 若要在以月份为标签的一行中查找 3 月的销售额，使用 =HLOOKUP(“March”, A1:H3, 3, FALSE)。

使用场景: 当你的数据是横向组织时，HLOOKUP 非常理想，可用于从特定列中提取信息。

6. INDEX 和 MATCH

INDEX 和 MATCH 常被用作 VLOOKUP 与 HLOOKUP 的替代方案。它们更灵活，允许你在任意列或任意行中查找值，而不局限于第一列或第一行。

语法:

• INDEX(array, row_num, [column_num])
• MATCH(lookup_value, lookup_array, [match_type])

示例: 要使用 INDEX 和 MATCH 查找 ID 为 103 的产品价格，使用：=INDEX(C2:C10, MATCH(103, A2:A10, 0))。

使用场景: 当 VLOOKUP 无法满足复杂查找需求时，INDEX 和 MATCH 非常强大，尤其是在查找列并非数据范围第一列的情况下。

7. COUNTIF

COUNTIF 用于统计某个范围内满足单一条件的单元格数量。它适合快速汇总符合特定标准的项目，例如统计销售额高于某个数值的次数。

语法: =COUNTIF(range, criteria)

示例: 要统计范围 B2

中销售额高于 500 的单元格数量，使用 =COUNTIF(B2:B10, “>500”)。

使用场景: COUNTIF 非常适合快速数据汇总，帮助识别某个条件在数据集中被满足的频率。

8. SUMIF

SUMIF 结合了 SUM 与 IF 的能力，允许你对满足特定条件的范围内数值进行求和。它非常适合财务分析，例如汇总超过某个金额的支出。

语法: =SUMIF(range, criteria, [sum_range])

示例: 要对 B 列中、且 A 列值为“Product A”的销售额求和，使用 =SUMIF(A2:A10, “Product A”, B2:B10)。

使用场景: SUMIF 适合按特定条件聚合数据，从而对特定细分部分进行有针对性的分析。

9. CONCATENATE（或 TEXTJOIN）

CONCATENATE（或其现代替代函数 TEXTJOIN）允许你将多个单元格中的文本合并为一个。这对于创建标签、全名或合并来自不同来源的数据非常有用。

语法:

• =CONCATENATE(text1, [text2], …)
• =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

示例: 要将 A2 中的名与 B2 中的姓合并，使用 =CONCATENATE(A2, “ “, B2) 或 =TEXTJOIN(“ “, TRUE, A2, B2)。

使用场景: CONCATENATE 在为报告准备数据时很实用，尤其是在创建组合标签或合并来自不同来源的文本数据时。

10. PIVOT TABLES

虽然从传统意义上说数据透视表并不是一个公式，但它是 Excel 的高级功能之一，可用于动态汇总和分析大型数据集。它让你在不改变原始数据集的情况下，重新组织、筛选并汇总数据。

示例: 你可以使用数据透视表按地区、产品或其他任意类别汇总销售数据。

使用场景: 数据透视表对数据分析至关重要。它们能帮助你探索数据内部关系、识别趋势，并轻松生成报告。

结论

掌握这 10 大 Excel 公式极大提升了我的数据分析能力。无论你是刚入门还是希望增强技能，这些函数都是任何数据分析师工具箱中的必备项。从简单求和到高级查找与数据透视表，这些 Excel 公式帮助我提取洞察、识别模式并做出明智决策。Excel 是数据分析的关键工具。

理解这些公式为更复杂的数据处理与分析任务打下了坚实基础。随着我不断探索 Excel 的功能，我发现这些函数不仅节省时间，还开启了探索与解读数据的新方式。