如何在excel中使用列标题和桥表格对列求和

jjjwad0x  于 2023-02-14  发布在  其他
关注(0)|答案(2)|浏览(166)

我在excel里有两组数据,set 1是原始数据,set 2是桥表,想要的输出也加进去了,这个公式我该怎么准备。
组1:

组2:

预期产出:

gk7wooem

gk7wooem1#

这里的解决方案假设标题数量可变,列名中没有特定模式。* 根据问题中列出的标记,假设没有Excel版本约束 *。在单元格H1中,输入以下公式,该公式会立即溢出整个结果:

=LET(in, A1:F5, lk, A8:B12, header, DROP(TAKE(in,1),,1), A, TAKE(lk,,1), 
 B, DROP(lk,,1), data, DROP(in,1,1), REDUCE(TAKE(in,,1), UNIQUE(B),
 LAMBDA(ac,bb, LET(f, FILTER(A, B=bb),values, CHOOSECOLS(data,XMATCH(f, header)),
  sum, MMULT(values, SEQUENCE(ROWS(f),,1,0)), HSTACK(ac, VSTACK(bb, sum))))))

输出如下:

我们使用具有两个输入范围的LET函数 * 仅 *:inlk,所以定义的其他名称都依赖于这样的范围名称,这使得公式易于维护,并适应您的真实的情况。
使用DROPTAKE,我们提取输入范围的每个部分:headerdataAB(第二个表中的列)。我们使用REDUCE/HSTACK模式来连接每次迭代的结果列。检查我对以下问题的回答:有关详细信息,请参阅如何将Excel中表格从垂直转换为水平但长度不同。
我们通过B的唯一值迭代,并为每个值(bb)我们选择列A的值我们使用XMATCHheader中选择相应的索引列(它不包括日期列)。我们使用CHOOSECOOLSdata中选择相应的列(values)。现在我们需要按列求和,我们使用MMULT来实现。结果在sum name中。最后,我们使用HSTACK来连接每次迭代中所选的列,包括来自B的唯一值作为表头。

注意:您可以使用以下数组函数代替MMULT函数,这是个人喜好问题:

BYROW(values, LAMBDA(x, sum(x)))
bksxznpy

bksxznpy2#

您可以尝试对每一行使用带通配符的SUMIFS。例如,对于第一列,放置以下公式并将其向下拖动。

=SUMIFS($B2:$F2,$B$1:$F$1,"=A*")

然后对其他列执行相同的操作,例如对列B执行相同的操作:

相关问题