您应该知道的5个有用的高级Excel公式

您应该知道的5个有用的高级Excel公式

随着对Microsoft Excel的持续使用，无论是用于跟踪、预算还是盘点，你可能会学到很多关于可用函数及其公式的知识。但是，发现新事物可能会让人感到困惑或害怕。本指南介绍了一些有用的函数及其公式，供您准备进入新领域时使用。这里有5个有用的高级Excel公式，您应该知道。

1. 查找电子表格中的值：XLOOKUP

当您想根据大型电子表格中的其他数据搜索数据时，XLOOKUP是您的首选功能。与VLOOKUP不同，它只能从左到右搜索值，XLOOKUP可以从左到右或从右到左搜索值，这使它成为一个更灵活的工具。

注意：在撰写本文时，XLOOKUP仅适用于Microsoft 365订阅者。对于其他Excel版本，请查看下一节中的INDEX和MATCH组合。

函数公式为：

XLOOKUP(查找值、查找范围、返回范围、未找到、匹配模式、搜索模式)

只需要前三参数。下面列出了每个参数的说明：

• Lookup_value：要搜索的值。
• 查找范围：包含要搜索的值的范围。
• 返回范围：包含要返回的值的范围。
• 未找到：未找到值时返回的文本。如果省略，“#N/A”是默认值。
• 匹配模式：匹配类型使用“0”表示完全匹配，“#N/A”表示未找到（如果省略则为默认值），“1”表示完全匹配，如果未找到下一个较小的元素，“-1”表示完全匹配和更大的元素element Next 如果未找到，或“2”以匹配使用问号、星号或波浪号的通配符。
• 搜索模式：搜索模式，“1”从第一个元素开始（默认如果省略），“-1”从最后一个元素开始，“2”当“lookup_range”按升序排列，“-2”当“lookup_range”在降序。

例如，我们正在寻找细分市场2的销售额，因此我们使用以下公式：

=XLOOKUP(2,A19:A24,D19:D24)

分解公式，2是“lookup_value”，A19:A24是“lookup_range”，D19:D24是“return_range”参数。

结果74000是扇区2的正确对应值。

如果您想要更动态的东西，在单元格中查找值而不是常量，然后在更改该单元格中的值时收到更新的结果，请参见以下示例：

我们将“lookup_value”2替换为单元格F19：

=XLOOKUP(F19,A19:A24,D19:D24)

当我们输入19时，我们得到结果74000，如果输入不同的值，比如5，我们会自动得到更新后的结果，即61000。

您可以包含参数“未找到”因此，如果未找到匹配项，您不必查看错误消息。我们在论证中加上“不回应”。

=XLOOKUP(F19,A19:A24,D19:D24,"No dice")

当在单元格F19中输入不匹配的值时，您将看到消息而不是“#NA”。

2. 在工作表中查找备选值：INDEX 和 MATCH

虽然XLOOKUP为您提供了查找值的强大方法，但它目前仅适用于Microsoft 365 Excel订阅者。如果您使用的是不同版本的Excel，您可以使用INDEX和MATCH来做同样的事情。

使用INDEX函数，根据您在公式中输入的位置返回单元格中的值。指数公式为：

索引（范围，行号，列号）

使用MATCH函数，返回您在公式中输入的值的位置。匹配公式为：

匹配（值，范围，匹配类型）

将这两个函数及其公式结合起来，将MATCH公式放在INDEX公式中作为查找位置（“row_number”和“column_number”）。

使用与上述XLOOKUP示例相同的数据显示，我们希望查看我们在单元格F19中输入的细分销售额。公式是：

=INDEX(D19:D24,MATCH(F19,A19:A24))

为了分解这个公式，我们从INDEX及其“范围”参数开始，即D19:D24。这是包含我们想要的结果的范围。

MATCH公式使用F19作为“值”参数，使用A19:A24作为包含该值的“范围”参数。

当我们输入19时，我们得到结果75000，这是正确的。

如果我们输入不同的扇区，例如6，公式会自动更新以给出正确的值58000。

3. 使用条件添加或计数：SUMIF、SUMIFS、COUNTIF、COUNTIFS

Excel中的SUM和COUNT函数旨在添加数字和计算单元格，但它仅限于简单的数学运算。这些函数的变体允许您使用条件添加或计数。例如，您只能添加大于12的数字或对包含名称Bill的单元格进行计数。

使用SUMIF和COUNTIF，您可以在一个条件下进行加法或计数，而使用SUMIFS和COUNTIFS，您可以在多个条件下进行加法或计数。下面列出了每个的语法：

SUMIF

SUMIF（范围，标准，sum_range）

COUNTIF

COUNTIF(范围，条件)

SUMIFS

SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2, ...)

COUNTIFS

COUNTIFS（条件范围1，条件1，条件范围2，条件2，...）

有关每个功能的示例，请参见下文。

要仅将B2到B7范围内大于12的数字相加，我们使用SUMIF函数和以下公式：

=SUMIF(B2:B7,">12")

结果是31，因为公式加上了16和15，这是数据集中唯一两个大于12的数字。

在下面的例子中，我们统计相同范围B12到B2中数值小于7的单元格，计算公式如下：

=COUNTIF(B2:B7,"<12")

此公式的结果为4，因为这是范围内值小于12的单元格数。

让我们使用SUMIFS并使用两个条件。要仅将单元格B2至B7中的数字添加到那些在单元格C2至C7中订阅了我们的通讯(Y)且其所在城市为圣地亚哥的单元格D2至D7中的数字，我们使用以下公式：

=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")

该公式的结是19，因为只有两个客户的Y表示通讯，而圣地亚哥表示城市。比尔布朗有11个，苏史密斯有8个，总共19个。

对于使用COUNTIFS函数的示例，我们计算具有上述相同两个条件的单元格的数量。我们计算订阅通讯(Y)的客户数量以及他们所在的城市圣地亚哥。

=COUNTIFS(C2:C7,"Y",D2:D7,"圣地亚哥")

正如预期的那样，Bill Brown和Sue Smith的分数是2，他们是圣地亚哥唯一的通讯订阅者。

4.不同条件下的测试数据：IFS

Excel的IF函数是根据条件测试数据的强大工具。例如，您可以使用它为学生的数字分数显示字母等级，如果销售人员带来的收入足以赚取奖金，则显示“是”，如果没有，则显示“否”。

IF函数的问题是要根据多个条件测试您的数据，您必须将所有IF语句组合在一起。要纠正这种组混淆，请使用IFS函数。

与上面的SUMIFS和COUNTIFS类似，IFS允许您以清晰易读的方式向公式添加多个条件。

语法为IFS(test1, if_test1_true, test2, if_test2_true, ...)，您可以在其中测试多达127个不同的基准。

使用我们的示例场景之一，我们只是开始提到奖励。如果B2单元格中的金额大于20000，则显示“是的”。如果没有，显示“不”。公式变为：

=IFS(B2>20000,"是",B2<20000,"否")

在第一个测试中，如果B2中的值大于20000，则结果为“是”。在第二次测试中，如果B2中的值小于20000，则结果为true“不”。

幸运的是，我们的销售人员收到了他的报酬。由于它的销售额为21000，因此进入“任性”在奖金栏中。

如果您有一个类似于我们示例的列表，请复制公式。将单元格右下角的填充柄拖到其他销售人员的剩余单元格中。公式会自动更新以适应不同的单元格引用。

再举一个来自IFS的例子，我们有一周中每一天的值班经理列表。嘡嘡前日期显示在单元格D2中时，相应经理的姓名显示在单元格E2中。公式变为：

=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)

这表示如果单元格D2中的值为星期一，则在单元格B2中显示姓名，如果单元格D2中的值为星期二，则在单元格B3中显示姓名，如果单元格D2中的值为星期三，则在单元格中显示姓名B3，等等。

当您更改单元格D2中的星期几时，值班经理的相应姓名将显示在单元格E2中。

通过预先创建公式的少量工作，您可以在Excel工作表中获得一些自动化的回报。

5.使用多个条件过滤：filter

作为Excel用户，您可能知道该应用程序带有内置筛选器功能。但是，如果您想使用一个条件甚至多个条件来过滤数据，那么需要使用过滤功能。这种灵活的功能及其参数可以帮助您在短短几分钟内缩小大型数据集的范围。

该函数的语法是FILTER(range, range=criteria, if_empty)，其中只需要数据集的前两个参数加上条件及其容器范围。如果您想返回一些特别的东西，如果公式不返回零结果，第三参数很有用，例如“没有数据”أو“没有什么”。

在添加多个条件之前，首先了解FILTER函数如何处理单个条件非常重要。例如，您可以筛选B2到B9中的单元格A2到C9中的数据（Harold Hill）。这个公式是：

=FILTER(A2:C9,B2:B9="哈罗德希尔")

Harold Hill有两个结果。容易吗？

让我们向公式中添加更多项。使用星号(*)表示AND并使用加号(+)表示OR添加多个条件。

例如，我们可以同时过滤哈罗德·希尔و电子产品使用以下公式：

=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))

划分公式，A2:C9为数据集，**B2：B9 =“哈罗德希尔”是第一个条件，星号代表AND，并且A2: A9 = “电子产品”**是第二个条件。

我们使用过滤器收到了一个结果，其中公式必须符合两个条件，Harold Hill和Electronics。

在另一个示例中，您可以按衣服或汽车进行过滤：

=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))

除以这个公式，A2:C9就是数据集，**A2: A9 = “服装”是第一个条件，加号代表OR，并且A2: A9 = “自动”**是第二个条件。

这一次，我们收到了两个结果，其中公式必须满足其中的一个条件，而不是同时满足两个条件。

经常问的问题

Q1：如何在Excel中直接找到函数的公式结构？

回答：Excel提供了一个内置工具来查找您需要的功能。有了它，您就可以看到该函数语法的语法。

选择一个空白单元格，转到“公式”选项卡，然后选择功能区左側的“插入函数”。在顶部的搜索框中输入职位，然后单击开始。当您看到所需的函数时，选择它以在框底部查看其描述和语法。

Q2：如何调试无法正常工作的公式？

回答：选择您遇到的公式，然后转到“公式”选项卡。在功能区的格式审核部分中选择“格式评估”。

您将在Evaluate Formula窗口中看到您的公式，其中的一部分带有下划线。点击“Evaluate”可以看到划线部分的计算结果。继续此过程以通过公式的每个部分移动到结果。这使您可以查看公式的处理方式以及可能出現错误的位置。

Q3：如何找出公式错误的原因？

回答：當您创建的公式中看到错误时，您可以做两件事来获取更多信息。首先，单击出现在单元格旁边的错误按钮，然后选择“帮助解决此错误”以在出现的边栏中从Microsoft获取更多详细信息。

或者，选择显示计算步骤以打开上面显示的公式评估窗口。您将看到计算结果和到达那里的过程，这应该有助于确定错误的原因。