Excel/Sheets 匹配 2 个工作表然后计数

Excel/Sheets 匹配 2 个工作表然后计数

我有 2 张数据表,还有第三张正在执行计算的数据表。第 1 张表包含具有唯一 ID 的使用数据。第 2 张表包含唯一 ID 以及非唯一组 ID(每个唯一 ID 仅是 1 个组的一部分)。我试图通过将唯一 ID 与组 ID 进行匹配来计算给定使用数据中每个组 ID 的出现次数。

我成功地创建了一个辅助列,用于在使用表中查找给定唯一 ID 对应的组 ID 并对其进行计数。我试图在一次计算中执行整个集合,我尝试过使用 INDEX/MATCH 和 VLOOKUP 执行 COUNTIF/S,但似乎无法让它返回所需的值。

示例数据:
工作表 1 的
A 列 (uniqueid)
6904240
7196869
6917534
7008111
7242448

第 2
页 A 列 I 列
6904240 403
7196869 103
6917534 102
7008111 102
7242448 103

第 3 页
403 计数=1
103 计数=2
102 计数=2

提前致谢!

答案1

尝试这个公式:

=SUMPRODUCT(($I$2:$I$6=M2)/COUNTIFS($I$2:$I$6,$I$2:$I$6,$A$2:$A$6,$K$2:$K$6))

在此处输入图片描述

答案2

在工作表 3 上,以任何可行的方式构建唯一组 ID 列表(例如,UNIQUE()如果必须动态创建,则使用工作表 2 的 I 列上的函数)。按组计数工作表 1 上按“唯一 ID”组织的数据的技巧是简单明了,不要太花哨。

虽然不需要辅助列,但这让我很难过,因为在有用的时候我很喜欢它们。

使用搜索列 I(组 ID)来测试每个唯一 ID 是否与工作表 2 的数据相符XLOOKUP(),并返回工作表 1 的范围所具有的匹配唯一 ID。此测试会生成一组 TRUE 和 FALSE,您将其乘以 1 即可将其转换为 1 和 0,然后将其相加即可计算出计数:

=SUM(  ($A$1:$A$14 = XLOOKUP( A1, $I$1:$I$5, $A$1:$A$5, ,0) ) *1 )

因此,假设工作表 1 的唯一 ID 位于 A1:A14,则工作表 3 上它们的唯一列表中的各个组 ID 位于工作表 3 上的单元格 A1、A2 等中,而其他两个范围是工作表 2 的 A 列和 I 列。

请注意,测试必须在其自己的括号内,然后在最后乘以 1。查找单元格(上面公式中的 A1)使用相对引用,使其查找其左侧,因此您可以将公式直接复制到该列中。

相关内容