Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

我仍然记得我意识到自己在做电子表格时犯了七年错误的那一刻。那是2019年，我和我的团队坐在一家中等规模物流公司的会议室里，看着我们的CFO手动在三个不同的Excel文件中复制粘贴数据，以生成我们的季度报告。这个过程花了她四个小时。每个季度四个小时，按她每小时$150的价格，因为没有人愿意去学VLOOKUP。

那天改变了一切。我是Sarah Chen，过去11年来，我一直担任财务运营顾问，帮助超过200家公司优化数据工作流程。我看到企业在可以用少量公式自动化的电子表格任务上浪费了成千上万个小时。讽刺的是，大多数人认为他们需要掌握数百个函数才能“擅长Excel”。但实际上，他们并不需要。在我的经验中，20个公式处理约95%的现实商业场景。

这不是一本关于每个电子表格函数的全面百科全书。这是我用来为客户节省超过12,000小时的手动工作的经得起考验的武器库。这些是当你试图结账、分析销售数据，或弄清楚为什么你的库存数字不匹配时，真正重要的公式。

基础：驱动一切的基础数学

在我们进行复杂操作之前，让我们谈谈形成我所建立的每一个财务模型基础的三个公式。这些可能看起来显而易见，但我见过高级分析师在这些公式上出错，导致公司损失真的钱。

SUM 是工作马。语法非常简单：=SUM(A1:A10) 添加该范围内的所有内容。但大多数人不知道的是：你可以使用SUM与多个不连续的范围。=SUM(A1:A10, C1:C10, E5) 工作完美，而且比写出单个单元格引用要干净得多。我曾审计过一个财务模型，其中某人写了=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10，而不是使用SUM。当他们需要插入一行时，公式就坏了。不要成为那种人。

AVERAGE 同样简单，但却出奇的强大。=AVERAGE(B2:B50) 给出你数据集的均值。这里的关键洞察是理解何时使用AVERAGE与AVERAGEIF（我们稍后会讨论）。在我的咨询工作中，我发现大约60%的时间人们使用AVERAGE时，他们实际上需要条件平均值。如果你在计算平均销售额，但想要排除零或异常值，普通的AVERAGE会误导你。

COUNT 和 COUNTA 是个性不同的双胞胎。COUNT 只计算数字单元格，而 COUNTA 计算任何非空单元格。这个区别比你想象的更重要。当我审计数据质量时，我使用=COUNTA(A:A)-COUNT(A:A) 来即时找到列A中包含文本而不是数字的单元格数量。如果这个数字高于预期，我就知道数据输入出现了问题。这种简单的检查为我的客户发现了数千美元的会计错误。

查找函数：s

如果我只能教某人三个公式，其中两个会是查找函数。这是手动匹配数据花费30分钟与写一个在30秒内完成的公式之间的区别。

"根据我与超过200家公司的工作经验，20个公式处理约95%的现实商业场景。大多数人浪费时间尝试掌握他们永远不会用到的数百个函数。”

VLOOKUP 是经典的，尽管有更新的替代品，但它仍然非常有用。语法是 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。这是一个来自上个月的真实例子：我有一个客户在一个表中有5000个客户记录，另一个表中有5000个交易记录。他们需要将客户姓名与交易ID匹配。=VLOOKUP(A2, Customers!A:D, 3, FALSE) 在三秒钟内完成了手动需要几个小时的工作。

每个人都会搞错的关键是：那个第四个参数。FALSE代表精确匹配，TRUE代表近似匹配。在99%的商业用例中，你想要FALSE。我见过整个财务报告因为有人将其留为TRUE（或省略，默认为TRUE）而导致近似匹配，而不是准确匹配。

XLOOKUP 是现代替代方案，老实说，在几乎每个方面都更好。=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 听起来复杂，但实际上更简洁。杀手级特性？它可以向左查找，而VLOOKUP做不到。我最近将一个客户的整个报告系统从VLOOKUP迁移到XLOOKUP，减少了约40%的公式错误，因为XLOOKUP更优雅地处理缺失数据。

INDEX-MATCH 是高级用户的选择。它实际上是两个公式的组合：=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))。这个组合比VLOOKUP更加灵活，并在处理大型数据集时更快。当我处理超过50,000行的电子表格时，INDEX-MATCH的表现明显更好。学习曲线更陡，但是一旦掌握，你就不会再回去了。我在需要根据两个标准同时查找值的复杂财务模型中使用它。

条件逻辑：让你的电子表格思考

这是电子表格从计算器转变为决策工具的地方。条件公式可以让你在数据中构建逻辑，并且对于任何自动化报告都是绝对必要的。

IF 是电子表格自动化的入门工具。=IF(logical_test, value_if_true, value_if_false) 让你创建分支逻辑。一个简单的例子：=IF(B2>1000, "高价值", "标准") 根据购买金额对客户进行分类。但真正的力量来自于嵌套IF。我构建了五个嵌套IF语句的佣金计算器，可以自动根据多个标准确定销售代表的奖金。关键是要保持可读性——使用适当的缩进和注释。

IFS 是嵌套IF的更清晰替代方案，在较新版本的电子表格中可用。你可以写成=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")，而不是 =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F")))。它更易读，并且不容易出现括号错误。我大约在三年前转向IFS，我的公式调试时间减少了一半。

SUMIF和SUMIFS 是条件求和的强大工具。=SUMIF(range, criteria, [sum_range]) 将满足条件的值相加。=SUMIFS 将这一点扩展到多个条件。真实例子：我曾有一家零售客户需要按区域和产品类别计算总销售额。=SUMIFS(Sales!C:C, Sales!A:A, "西部", Sales!B:B, "电子产品") 就给他们提供了这个。在学习SUMIFS之前，他们使用数据透视表处理所有数据，这过于复杂，更新速度也慢。

COUNTIF和COUNTIFS的工作方式相同，但是计数而不是求和。我经常用这些进行数据质量检查。=COUNTIF(A:A, "错误") 告诉我列A中有多少单元格包含单词“错误”。=COUNTIFS(A:A, ">100", B:B, "<50") 计算列A大于100且列B小于50的行。这些公式为我节省了无数的手动数据验证时间。