所以我有一个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 xSo 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)
更多推荐
发布评论