随着持续使用 微软的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)
当我们在单元格 F2 中输入 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 作为包含该值的“范围”参数。
当我们在单元格 F4 中输入 19 时，我们得到结果 75000，这是正确的。
如果我们在单元格 F19 中输入不同的扇区，例如 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 表示时事通讯，而 San Diego 表示城市。 比尔布朗有 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 窗口中看到您的公式，其中的一部分带有下划线。 单击“评估”以查看下划线部分的计算。 继续此过程以通过公式的每个部分移动到结果。 这使您可以查看公式的处理方式以及可能出现错误的位置。
Q3：如何找出公式错误的原因？
الجواب： 当您在创建的公式中看到错误时，您可以做两件事来获取更多信息。 首先，单击出现在单元格旁边的错误按钮，然后选择“帮助解决此错误”以在出现的边栏中从 Microsoft 获取更多详细信息。
或者，选择显示计算步骤以打开上面显示的公式评估窗口。 您将看到计算结果和到达那里的过程，这应该有助于确定错误的原因。
