☰

如何通过使用报表的多行名称加上多列的名称来定义范围来汇总报表中的一系列数字？

tag-icon • tag-icon microsoft-excel
如何通过使用报表的多行名称加上多列的名称来定义范围来汇总报表中的一系列数字？

附图单元格 M6 有我自己问题的现有公式解决方案。但我想知道是否有更好的公式解决方案？因为通过预定 3 个公式并将它们相加来定义 ACCT 维度很累。此外，将来术语 X 不仅有 ACCT 2 和 ACCT 4 的新定义，还有 ACCT 6、ACCT 7、ACCT 8，我需要添加更多公式来总结。理想情况下，我可以像定义 PROJECT 一样定义 ACCT，只需使用 $L$2:$L$5。

任何想法都将不胜感激！感谢您的时间！

现有公式解决方案

答案1

用于ISNUMBER(MATCH())测试它是否在列表中：

=SUMPRODUCT($C$3:$G$7*(ISNUMBER(MATCH($B$3:$B$7,$I$6:$K$6,0)))*(ISNUMBER(MATCH($C$2:$G$2,$L$2:$L$5,0))))

现在它将测试 Acct 和 Project 是否在各自的列表中，并返回交叉点处的值或不交叉处的 0，以便与 SUMPRODUCT 相加。

答案2

我没有关注这个传奇故事，只是知道它。所以也许下面的内容已经在故事的某个深处被暗示了。但如果没有：

=SUM( INDEX(C3:G7, MATCH(J5:K5, B3:B7, 0), MATCH(L2:L4, C2:G2, 0) ) )

仅INDEX使用表中的数据，使用行MATCH和列标题以及提供的列表作为匹配值来查找要放置在公式的虚拟表中的行和列。然后SUM（忽略可能存在的非数字条目）。

或者随着需求的增长而更好地进行更改LET（这使得它看起来很庞大，但大大简化了维护：

=LET(MatchAccounts, J5:K5, MatchProjects, L2:L4,
      Data, C3:G7, AllAccounts, B3:B7, AllProjects, C2:G2,

      SUM( INDEX(Data, MATCH(MatchAccounts,AllAccounts,0), MATCH(MatchProjects,AllProjects,0) ) ) )

现在，最有可能的所需更改可以放在第一行，次有可能的更改可以放在第二行。无需在公式本身中寻找。添加到感兴趣的行和列的范围非常容易。

它不能很好地处理这些范围内的空白或任何不匹配的条目，只会给出错误#N/A。如果有这样的需求，它应该相对容易满足。

答案3

问题

解决方案# 1（我更喜欢它）=REDUCE(0, A5:C5, LAMBDA(X,R, X+IFERROR(SUMPRODUCT(SUMIF($G$1:$XFD$1, D$1:D$4, INDEX($G:$XFD, MATCH(R, $G:$G, 0), 0))), 0))) 这个想法来自@gns100，谢谢 gns100！

解决方案#2=SUMPRODUCT(($H$2:$ZZ$9999)*(ISNUMBER(MATCH($H$1:$ZZ$1,D$1:D$4,0))*(ISNUMBER(MATCH($G$2:$G$9999,$A5:$C5,0))))) 这个想法来自@Scott Craner，谢谢 Scott！

欢迎任何其他解决方案或想法！

在此处输入图片描述

相关内容