问题描述
我正在尝试创建一个Excel公式，该公式能够对表中的多行求和，其中要求和的行和列由其他单元格的内容确定.
I'm trying to create an Excel formula that is able to sum multiple rows in a table, where the rows and column to be summed are determined by the contents of other cells.
通常，我会使用索引匹配匹配"来实现这一点，但是多行求和使我感到困惑.
Ordinarily I would use Index Match Match to achieve this, but the multiple rows summation has left me stumped.
在这里，我已经看到了带有SUMIFS公式的Index Match的几个示例，但是没有将其与Index Match Match配对的.
I've seen a couple of examples on here of Index Match with a SUMIFS formula, but nothing that pairs this with Index Match Match.
我在不同的Excel工作表上有两个表.第一个看起来有点像这样(实际表是105列x 200行):
I have two tables on different Excel sheets. The first one looks a little this (the actual table is 105 columns x 200 rows):
那是从名为固定成本摘要"的工作表中获得的.第4行包含唯一的员工编号列表. A列是我们会计系统的费用类别，B列是更广泛的类别，应在Excel中使用以将相似项目分组.然后，E列开始包含要汇总的数字信息.
That is from a sheet called "Firm Cost Summary". Row 4 contains a list of unique employee numbers. Column A is the expense category per our accounting system and Column B is a broader category that should be used in Excel to group similar items. Column E onwards then contains the numerical information to be aggregated.
我接下来想做的就是以一种更可表达的格式总结该表格，然后可以用其他方式对其进行操作.该表如下所示:
What I would then like to do is summarise that table in a more presentable format that can then be manipulated in other ways. The table looks like this:
那是在名为员工成本摘要"的工作表上.我想在黄色单元格中填写信息，即每个工作人员的薪水，奖金，福利等总计.理想情况下，这是我在E6单元格中输入的公式，然后我可以向右和向下拖动以填充表格.
That is on a sheet called "Staff Cost Summary". I would like to fill out the info in the yellow cells, i.e. total the salary, bonus, benefits, etc, of each staff member. Ideally this would be a formula I input in cell E6 that I can then drag right and downwards to fill the table.
举个例子，要填写第二个表中的单元格I6，该公式应在单元格A6中查找以找到员工编号(在这种情况下为1)，然后在第一张表的第1行中查找该编号.第一个表的适当列(在这种情况下，列E).
To give an example, to fill out cell I6 in the second table, the formula should look in cell A6 to find the employee number (1 in this case) and look this up in row 1 of the first table to find the appropriate column of the first table (column E in this case).
然后，该公式应在第二个表的单元格I5中查找，以查看我们正在汇总收益，然后向下查找第一个表的B列，以查找应求和的每一行(在这种情况下，第7-10行) ).
The formula should then look in cell I5 of the second table to see that we are looking to aggregate benefits, then look down column B of the first table to find each row that should be summed (rows 7-10 in this case).
牢记这一点，这就是我所拥有的:
With that in mind, here's what I've got:
=INDEX('Firm Cost Summary'!$A$4:$G$10,MATCH('Staff Cost Summary'!$A6,'Firm Cost Summary'!$A$4:$G$10,0),MATCH('Staff Cost Summary'!E$5,'Firm Cost Summary'!$B$4:$B$10,0))
Joe Bloggs的总收益是表1的单元格E7:E10的总和，即5 + 10 + 50 + 100 = 165.
Total benefits for Joe Bloggs are the sum of cell E7:E10 of table 1, i.e. 5 + 10 + 50 + 100 = 165.
该表的B列中显然有多个匹配项，因此上述公式给出的答案为0.
Clearly there are multiple matches in column B of that table, so the above formula gives an answer of 0. Any ideas how I can tweak that to make it work?
推荐答案
将此内容放入E6并上下复制
Put this in E6 and copy over and down
=SUMIFS(INDEX('Firm Cost Summary'!$D:$DD,0,MATCH($A6,'Firm Cost Summary'!$D$4:$DD$4,0)),'Firm Cost Summary'!$B:$B,E$5)
索引/匹配项返回要添加的正确列.
The index/match returns the correct column to be added.
这篇关于使用INDEX MATCH MATCH或SUMIFS聚合数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！