我遇到过几次这种需求，并且只能得出这样的结论：必须有一种更简单、更具可扩展性的方式来完成我在这里尝试做的事情。
基本上，我需要一个可以进行总计的三维表格......
给定两个表：
| People | One | Two | Three | Four | Five | Six | |--------|-----|-----|-------|------|------|-----| | John | x | x | | | | | | James | | x | x | | | | | Jim | | | x | x | | | | Jean | | | | x | x | | | Jammie | | | | | x | x | | Janis | x | | | | | x |
和
| Event | Data | |-------|------| | One | 1 | | Two | 2 | | Three | 3 | | Four | 4 | | Five | 5 | | Six | 6 |
我正在根据第一个表中的非空白单元格和第二个表中的相应值生成总计：
| Totals | |--------| | 3 | | 5 | | 7 | | 9 | | 11 | | 7 |
我正在使用一个复杂的 vlookup 来生成这个：
=SUM( IF(NOT(ISBLANK(Table1[@One])),VLOOKUP(Table1[[#Headers],[One]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Two])),VLOOKUP(Table1[[#Headers],[Two]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Three])),VLOOKUP(Table1[[#Headers],[Three]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Four])),VLOOKUP(Table1[[#Headers],[Four]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Five])),VLOOKUP(Table1[[#Headers],[Five]],Table2,2,FALSE),0), IF(NOT(ISBLANK(Table1[@Six])),VLOOKUP(Table1[[#Headers],[Six]],Table2,2,FALSE),0) )
显然，这是不可扩展的，而且它为人为错误留下了很大的空间。实际上，真正的应用涉及添加或删除用户和“事件”。我觉得我已经遇到过很多次了，我不能一个人这样做，而且我一定是用一种困难的方式做这件事。有没有更简单的选择？
*请注意，在这个例子中我使用了一个总和，但在某些情况下，我可能有想要执行的文本TEXTJOIN或类似操作。
答案1
此数组公式从下面屏幕截图中的 H2 向下填充，给出了所示的结果。
=SUM(NOT(ISBLANK($B2:$G2))*TRANSPOSE(B$10:B$15))
因为这是一个数组公式，所以必须用CTRL ShiftEnter, 而不是仅仅 来输入Enter。
怎么运行的： NOT(ISBLANK($B2:$G2))True/False给出一个值数组，其中False该范围内的单元格位于空的（请参阅下面的注释）。如果您在公式栏中突出显示公式的该部分并按下键F9，您将看到数组{TRUE,TRUE,FALSE,FALSE,FALSE,FALSE}。（键入CTRL-Z 可撤消该操作）。
(B$10:B$15)只是数据列中的数字列表，并将TRANSPOSE()其从垂直数组转换为水平数组。
在乘法中，True/False值被视为1和0，因此结果是一个数组，其中 是数据值x，而 是其他位置的值。然后SUM()只需将数组相加，并在填充时返回总数。
编辑：
OP 补充了他的疑问，说他可能有文本值并对B6:B10它们进行操作，不是用SUM()，而是另一个函数，可能是TEXTJOIN()。
为了处理非数字值的可能性，此表达式：
IF(NOT(ISBLANK($B2:$G2)),TRANSPOSE(B$10:B$15),)
返回一个数组，其中“x”处的数据值为该数组False。它对文本和数值均有效。
通过在（最后一个逗号之后）value_if_false中包含，可以将值替换为或或作用于数组的函数可能需要的任何内容。IF()False0blank
笔记：
ISBLANK()应该命名为ISEMPTY()，因为单元格必须真正为空才能ISBLANK()返回True。如果单元格包含公式，但显示为空白，ISBLANK()则将返回False。ISTEXT()有同样的问题，因此如果“空白”单元格中确实有公式，则可以将第一个数组更改为(($B2:$G2)="x")- 如果
TRANSPOSE()不存在，则乘法（和IF()函数）将产生一个二维数组，这会变得混乱。
我希望这会有所帮助并祝你好运。
答案2
经过进一步思考，我发现使用 有一个更简单的解决方案SUMIF()。但您的第二张表必须水平排列。
=SUMIF(B2:G2,"x",B$10:G$10)
