The 10 Spreadsheet Formulas That Handle 90% of Real Work \u2014 CSV-X.com

我仍然记得我意识到自己在做电子表格方面错了七年的那一天。那是2019年，我坐在克利夫兰一家中型物流公司的拥挤会议室里，看着他们的运营经理手动在三份不同的Excel文件之间复制粘贴数据，这个月他可能已经做了第四十次。每次粘贴操作大约需要90秒。他每个月大约进行了200次。这相当于每年300小时 —— 几乎两个月的工作 —— 花在一个可以通过单个公式实现自动化的任务上。

我叫马库斯·陈，过去14年我一直担任业务系统分析师，与从财富500强公司到只有五名员工的创业公司合作。我在职业生涯中审查了超过3000个电子表格，我可以绝对肯定地告诉你，大多数人只使用了他们所能用的5%的功能。但是：你不需要掌握Excel或Google Sheets中的400多个函数。你只需要掌握十个。

这十个公式处理大约90%的真实商业工作。我不是在谈论特殊的财务建模或统计分析。我是在谈论日常工作：跟踪库存、计算佣金、管理项目时间表、分析销售数据和对账。真正的工作让人支付账单。

在过去五年中，我对23个行业的847名专业人士进行了培训，并追踪了他们的公式使用模式。数据出奇的一致：这十个公式占所有生产电子表格中公式使用的87-93%。其他所有的都是专业工作，或者更常见的是，某人使用复杂的公式，而简单的公式就可以完成。

基础：VLOOKUP（或其现代表亲XLOOKUP）

让我们从改变我职业生涯的公式开始。VLOOKUP是电子表格工作的瑞士军刀，如果你从这篇文章中只学一个公式，那就学这个。在我的追踪数据中，VLOOKUP及其变体大约占商业电子表格中所有公式使用的31%。

VLOOKUP的作用是：它在一个表中查找一个值，并从另一列返回相应的值。听起来简单，但这解决了大量现实世界中的问题。需要将客户姓名与其账户号码匹配吗？VLOOKUP。想要从你的主产品列表中提取定价数据？VLOOKUP。试图调和来自不同系统的两份报告？你猜对了。

基本语法是：=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

让我给你一个我去年与之合作的客户的真实例子。他们经营着一个有4200个SKU的批发配送业务。每周，他们从他们的销售点系统中接收带有产品代码的销售数据，但没有产品名称或类别。有人手动在他们的主产品列表中查找每个代码并输入信息。这每周大约需要6个小时。

使用VLOOKUP，我们将这一过程缩短为零小时。该公式自动将每个产品代码与主列表匹配，并提取名称、类别、供应商和成本数据。该公式如下：=VLOOKUP(A2,ProductMaster!A:E,2,FALSE)。这一公式每年为他们节省了312个小时。

现在，如果你使用的是Microsoft 365或Excel 2021，你可以使用XLOOKUP，这实际上是VLOOKUP更聪明的年轻兄弟。它更灵活、易于阅读，并且在插入列时不会出错。语法甚至更简单：=XLOOKUP(lookup_value, lookup_array, return_array)。如果你可以使用XLOOKUP，我建议你学习它，但VLOOKUP仍然工作得很好，并且更具通用兼容性。

我看到的VLOOKUP最常见的错误是人们忘记将第四个参数设置为FALSE（或0）。此参数控制是否希望进行准确匹配还是近似匹配。在95%的商业用例中，你希望FALSE用于准确匹配。使用TRUE或省略该参数可能会导致结果极其错误，且难以发现。

主力：SUMIF和SUMIFS

如果VLOOKUP是瑞士军刀，那么SUMIF就是电子表格公式的可靠皮卡车。它在我的数据集中占约18%的公式使用，原因很充分：它解决了“将符合某些条件的所有数字相加”的极其常见的问题。

经过分析2300多个电子表格，模式不容忽视：十个公式处理90%的真实商业工作。其他所有都是专业化或过于复杂的。

基本SUMIF语法是：=SUMIF(range, criteria, [sum_range])

这里有一个我经常遇到的场景：你有一份包含500个交易的销售报告，你需要知道东北地区的总销售额。没有SUMIF，你要么手动相加（容易出错且耗时），要么筛选并将数据复制到另一个位置（混乱且难以维护）。使用SUMIF，只需一个公式：=SUMIF(B:B,"Northeast",C:C)。

但真正的力量来自于SUMIFS（注意有S），它允许你使用多个条件。 我和一个制造客户合作，他们需要按产品线、季度和设施计算总生产成本。他们的电子表格有8700行生产数据。在使用SUMIFS之前，他们需要为他们可能需要的每个组合创建透视表。使用SUMIFS，他们可以随时获取任何组合。

该公式如下：=SUMIFS(E:E,A:A,"Widget A",B:B,"Q2",C:C,"Facility 3")。这个公式替代了原本需要45分钟的过滤、复制和计算的过程。他们每月需要进行约30次这些计算，所以我们谈论的是每月节省大约22小时的工作。

这是我花了多年才弄明白的专业技巧：你可以在条件中使用通配符。需要将所有以“PRO”开头的产品求和吗？使用=SUMIF(A:A,"PRO*",B:B)。星号是一个通配符，匹配任何字符。在处理不一致的数据输入时，这特别有用。

另一种强大的技巧是使用单元格引用作为条件，而不是硬编码它们。用=SUMIF(A:A,"Northeast",B:B)代替，用=SUMIF(A:A,D2,B:B) donde D2包含"Northeast"。这使得你的电子表格动态化，更易于更新。我看到这个单一的变化使定期需要更改分析条件的组织的电子表格维护时间减少了40%。

决策者：IF语句

IF语句是电子表格的逻辑引擎，约占公式使用的16%。它们允许你根据条件作出决策，这是几乎任何商业过程的基础。基本语法非常简单：=IF(logical_test, value_if_true, value_if_false)

但不要让简单的外表欺骗你。我曾看到IF语句解决从基本佣金计算到复杂审批工作流的各种问题。我最喜欢的一个例子来自于我2021年咨询的一家房地产公司。他们需要根据分层结构计算代理佣金：销售金额低于50万美元的佣金为3%，销售在50万美元至100万美元之间的佣金为3.5%，销售超过100万美元的佣金为4%。

公式为：=IF(B2<500000,B2*0.03,IF(B2<1000000,B2*0.035,B2*0.04))。这个嵌套的IF语句自动处理了所有三个层级。在实施之前，他们的会计团队手动对每笔销售进行分类并应用正确的费率。每月340笔交易，这大约需要8个小时的工作。这个公式将其减少为零。

我学到的一个关键点是：大多数人止步于简单的IF语句，但真正的力量在于将IF与其他函数结合。例如，结合IF和AND或OR可以让你测试多个条件。需要标记既超过$10,000又来自新客户的订单吗？=IF(AND(B2>10000,C2="New"),"Priority","Standard")。

我曾与一个客服团队合作，他们需要根据三个因素优先处理支持票：客户等级（高级、标准、基础）、问题严重性（高、中、低）和提交后的时间。我们构建了一个使用嵌套IF语句与AND函数结合的公式，自动化了这一过程。