☰

如何根据多个条件返回前 10 个名称的列表？

tag-icon • tag-icon microsoft-excel worksheet-function
如何根据多个条件返回前 10 个名称的列表？

我正尝试根据两个不同的标准从数据表中获取前 10 名列表。

姓名 性别 全部的
泰勒 F 268.2341742
泰勒 F 252.2535105
哈勒 F 250.7678059
基督教 米 410.1501907

我想要的是按总数唯一列出的前 10 名女性（或男性）的列表。我得到的是前 10 名女性的列表，但其中包含重复项，而这正是我不想要的。

我已经能够获得总数排名前 10 位的女性名单，但其中包含重复的内容。

这是我使用的公式：

 =INDEX(Results[Name],MATCH(UNIQUE(LARGE(FILTER(Results[SinclairTotal],Results[Sex]="f"),[@Rank])),Results[SinclairTotal],0))

包含所有数据的表格在哪里Results，要填写的表格有以下列：

秩 姓名 全部的
1
2
3
4
5

谢谢

答案1

这似乎不是最简单的任务，因为您需要处理总计等的聚合，但语句LET()应该可以帮助您保持整洁并为您提供溢出数组。通常，允许LET()您为中间值分配名称。我稍微扩展了示例数据，以便在范围内存储更全面的示例（见下文）A1:C15。

输入数据：

姓名 性别 全部的
泰勒 F 268
泰勒 F 252
哈勒 F 251
基督教 米 410
美国广播公司 米 216
定义 F 508
美国广播公司 米 608
伊克 F 174
凯金 米 208
维基百科 F 302
维基百科 F 658
艾吉克 米 528
埃尔茨 F 148
凯金 米 596

案例 1：返回总计

实践中采用的公式如下：

=LET(
    data, $A$1:$C$15,
    criteria, "f",
    topN, 3,
    filteredData, FILTER(data, INDEX(data, , 2) = criteria),
    uniqueNames, UNIQUE(INDEX(filteredData, , 1)),
    totals, SUMIFS(INDEX(data, , 3), INDEX(data, , 1), uniqueNames, INDEX(data, , 2), criteria),
    sortedNames, INDEX(uniqueNames, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    sortedTotals, INDEX(totals, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    HSTACK(SEQUENCE(topN), sortedNames, sortedTotals)
)

现在到了重要的部分，这里发生了什么：首先，我们定义数据的范围（如上所述），我们称之为data。接下来，我们定义criteria我们想要过滤数据的，例如“f”，以及topN我们想要返回的观测值的数量。我在这里使用了前 3 个，以便于说明，但这可以灵活更改。

接下来，我们根据从我们定义的标准过滤数据data并提取uniqueNames，我们需要聚合总数并最终返​​回正确的数据。下一步是一个简单的SUMIFS()语句，用于聚合每个唯一名称的总数，我们根据该语句决定是否将其包含在topN中。一旦我们获得了totals，我们就可以使用这些信息来提取topN中的名称和总数。基本上，在这里我们通过使用、和INDEX()的组合匹配总数来获取函数的行索引。同样的想法也适用于返回聚合总数。最后一步是返回排名，以及以类似表的结构获得的名称和总数。为此，我们使用函数来水平堆叠多个数组。MATCH()LARGE()SEQUENCE()HSTACK()

符合条件“f”的前 3 名的输出如下：

秩 姓名 全部的
1 维基百科 960
2 泰勒 520
3 定义 508

符合条件“m”的前 3 名的输出如下：

秩 姓名 全部的
1 美国广播公司 824
2 凯金 804
3 艾吉克 528

案例 2：返回最大总数（根据评论）

如果您想要返回最大总数，则需要稍微调整语句，LET()因为您还需要计算maxTotals。实际执行的调整公式如下：

=LET(
    data, $A$1:$C$15,
    criteria, "f",
    topN, 3,
    filteredData, FILTER(data, INDEX(data, , 2) = criteria),
    uniqueNames, UNIQUE(INDEX(filteredData, , 1)),
    totals, SUMIFS(INDEX(data, , 3), INDEX(data, , 1), uniqueNames, INDEX(data, , 2), criteria),
    maxTotals,MAXIFS(INDEX(data,,3),INDEX(data,,1),uniqueNames,INDEX(data,,2),criteria),
    sortedNames, INDEX(uniqueNames, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    sortedTotals, INDEX(maxTotals, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    HSTACK(SEQUENCE(topN), sortedNames, sortedTotals)
)

此LET()语句基本上有两个调整。第一个是额外计算maxTotals，即使用MAXIFS()而不是SUMIFS()。SUMIFS()在这种情况下， 也是必需的，因为您根据总计做出决定。此语句中的第二个调整与 一致，sortedTotals因为您想要返回的不是totals而是maxTotals.所有其他方面保持不变。

符合条件“f”的前 3 名的输出如下：

秩 姓名 最大总计
1 维基百科 658
2 泰勒 268
3 定义 508

符合条件“m”的前 3 名的输出如下：

秩 姓名 最大总计
1 美国广播公司 608
2 凯金 596
3 艾吉克 528

补充说明：

如果您想返回任何中间步骤而不是最终结果来追踪正在发生的事情，您可以在语句中更改LET()如下行HSTACK()以返回，例如 sortedNames：

    result, HSTACK(SEQUENCE(topN),sortedNames, sortedTotals),
    sortedNames

相关内容