在一张 Excel 表中,我有两个不同的数据集,每组有三列,代表非常相似的数据。
我想将其合并为一组三列。
每个数据集都有其最左列作为我想要分组的键。
每个数据集中都有一个键出现一次或从不出现。其他列是数字,可能为空(解释为零)。两个系列的长度可能不同。
就像这样:
[Name_A],[Score_A],[Value_A],[Name_B],[Score_B],[Value_B]
Adam,14,20,Johnny,8,
Johnny,11,,Bernice,5,5
,,,Adam,2,8
应聚合为:
[Name],[Score],[Value]
Adam,16,28
Johnny,19,
Bernice,5,5
最终结果中行的顺序并不重要。
答案1
答案2
合并数据集的关键是提取唯一名称的列表。这是很多如果姓名都在一列中,则更容易。如果您必须按照所示方式排列数据,则有一个 VBA 解决方案,因此如果您需要它,请在下面发表评论。
在下面的第一个表中,我将您的“B”数据移至“A”数据下方,并在每个部分中添加了几行。
此公式从 E2 向下填充,列出名称并删除重复项:
=IFERROR(INDEX((A$2:A$15),MATCH(0,COUNTIF($E$1:E1,A$2:A$15),0)),"")
它是一个数组公式,因此必须用 输入CTRLShiftEnter。
这两个公式从 F2 和 G2 向下填充,将列表中每个名称的分数和值相加:
=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,B$2:B$15))
=IF(LEN(E2)<1,"",SUMIF(A$2:A$15,E2,C$2:C$15))
______________________________________________________________________
怎么运行的:内部COUNTIF()
创建一个数组,其中包含 A 列中每个名称在 E 列不断增长的列表中出现的次数。MATCH()
在该数组中找到第一个 0(对应尚未列出的名称)的位置,并将其用作 中的 row_num INDEX()
,从 A 列中挑选出名称。已经列出的名称不会再次出现。
IFERROR()
当公式用尽唯一值时,将在出现 #NUM! 错误的行中插入空白。
这两个SUMIF()
公式只是将每个姓名的分数和值数字相加。IF()
对于 E 列长度小于 1 的行(即单元格没有姓名并显示为空白),将插入空白。