通过引用第二个表来引用 Excel 表列

Question

我知道这是一个糟糕的标题。

---

我有两张桌子。第一张是用例，其中包含所有适用的角色的列，然后是 1 或 0角色列来指示该角色是否使用该用例，因此：

ID | Description   | Worker | Manager | Executive
1  | Do stuff      |   1    |    1    |     1
2  | Manage People |   0    |    1    |     1
3  | Collect Bonus |   0    |    0    |     1

第二，角色，列出角色：

Role        | Use Case Count
Worker      |
Manager     |
Executive   |

在里面用例计数列角色表，我试图计算该特定角色的用例数量，因此工人获得 1 个，经理获得 2 个，主管获得 3 个。如果我明确引用，我可以=SUM(UseCases[Worker])在单元格中使用并得到我想要的结果。但我不能使用类似 的东西=SUM(UseCases[@[Role]])，这会引发错误。类似地=SUM(UseCases[A2])不起作用。

我该如何以足够动态的方式解决这个问题，以便添加角色名称或更新它们不会破坏事物？我愿意接受替代解决方案。

Answer 1

这可以用 Sumproduct 公式来实现。第一列中的公式是

=SUMPRODUCT(UseCases[[Worker]:[Executive]]*(UseCases[[#Headers],[Worker]:[Executive]]=[@Role]))

当您将新角色添加到 UseCases 表时，只需确保将它们插入到 Worker 和 Executive 之间，而不是之前或之后。这样，带有:运算符的引用将覆盖all columns starting from Worker to Executive。

编辑：

您还可以使用名称管理器定义命名范围，并在公式中使用范围名称。添加更多角色名称时，只需确保范围名称引用正确的范围即可。

=SUMPRODUCT(RoleData*(RoleHeadings=[@Role]))