☰

如何根据另一列对一列进行分组。然后将其写入另一列

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
如何根据另一列对一列进行分组。然后将其写入另一列

如何分组动物名称根据类别列，并放到对应的列（黄色区域）。例如图中，找到食草动物（B列）类别的动物名称（A列），并放到C列（黄色区域）。

如果食草动物中的每个项目的分数为 30，则将总项目乘以 30 写入总数（标记为 X1 的单元格）

期望结果的示例

答案1

请阅读FILTER() 函数并将其应用到您的数据中：

=FILTER($A$2:$A$29,$B2:$B29=C$2)

现在阅读COUNTIF() 函数并放在第三十行

=COUNTIF($B$2:$B$29,C$2)*30

答案2

由于您标记了 Microsoft Excel 2016，因此您需要绕过不可用的 FILTER。我们可以使用 INDEX 和 AGGREGATE 来实现这一点：

使用C3下面的公式并向下/向右拖动： =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$3:$B$29)/($B$3:$B$29=C$2),ROW(A1))),"")

我们使用 AGGREGATE 和 B 列的行号，从类别范围的开始位置到结束位置：B3:B29。我们用来$锁定范围行和列的位置。

将行号数组除以数组中单元格值的布尔值，结果等于C2按行得出 TRUE 或 FALSE 的值。

因为我们/对数组使用了运算符，所以布尔值被转换为它的数值等价物：TRUE= 1，FALSE= 0。

因此，行号数组除以1's 和0's 数组。我们利用 (行) 号除以0导致错误 (#DIV/0!)；AGGREGATE 的第二个参数：6意味着忽略错误值。这意味着 AGGREGATE 将仅使用满足条件的行号数组，但第一个参数必须使其返回n该数组的第 th 个位置。我们可以使用14SMALL 或15LARGE 来返回n聚合数组的第 th 个位置。我使用ROW(A1)(equals 1) 作为第一个 -A2当向下拖动时，它将变成 (equals 2)，等等。

n如果行号超出聚合数组的行数，则使用 IFERROR ，这将返回索引错误。

对于索引，我使用了整列引用，因此我们不需要计算行减去数组的开头 +1，从而使其变得不必要地复杂。

对于总计，您可以使用 SUMPRODUCT C30（向右拖动）： =SUMPRODUCT(N($B$3:$B$29=C$2))*30

使用与聚合函数中相同的布尔逻辑，N()将 TRUE 和 FALSE 转换为 1 和 0。SUMPRODUCT 将 1 和 0 相加，然后将结果乘以 30。

如果您有较新的 Excel（2021、Office 365），则可以使用以下方式一次性得出总数： =MMULT(TOROW(N(B3:B29<>"")*30),N(B3:B29=C2:E2))。

有关 MMULT 的更多信息这里

相关内容