Excel SUMIFS带有可变参数的sum

Excel SUMIFS带有可变参数的sum_range(Excel SUMIFS sum_range with variable parameter)

所以我有一个excel电子表格指示器，我正在尝试制作一个SUMIFS语句，它将改变我选择的列。 所以我会在左边有列名，我想把它作为一个变化的参数。 见下表。

TABLE1 week1 week2 week3 column1 x x x column2 x x x column3 x x x TABLE2 Week column1 column2 column3 1 x x x 1 x x x 1 x x x 2 x x x 2 x x x 2 x x x

因此在上面的表中，TABLE2保存所有数据，TABLE1基本上是按周计算的数据的总和。 所以我希望SUMIFS读取类似SUMIFS（TABLE2 [????]，TABLE2 [Week]，$ B $ 2）但我无法弄清楚如何获取SUMIFS的sum_range部分让我传递参数关于如何做到这一点的任何提示或想法？

So I have an excel spreadsheet indicator and I am trying to make a SUMIFS statement that will change which column I am selecting from. So I will have the column name on the left and I want to use that as a varying parameter. See the below tables.

TABLE1 week1 week2 week3 column1 x x x column2 x x x column3 x x x TABLE2 Week column1 column2 column3 1 x x x 1 x x x 1 x x x 2 x x x 2 x x x 2 x x x

So in the above tables TABLE2 holds all the data and TABLE1 is basically a sum of the data by week. So I want the SUMIFS to read something like SUMIFS(TABLE2[????], TABLE2[Week], $B$2) But I can't figure out how to get the sum_range part of the SUMIFS to let me pass parameters in. Any tips or ideas on how to do this?

最满意答案

=INDIRECT("TABLE2[" & A3 & "]")

将解析为TABLE2 [column1]（假设A3保存文本“column1”）

所以间接函数可以作为你的sum_range（在excel 2007中测试）

=INDIRECT("TABLE2[" & A3 & "]")

will resolve to TABLE2[column1] (assuming A3 holds the text "column1")

so the indirect function could be put in as your sum_range (tested in excel 2007)