excel - 对 SUMPRODUCT 和 VLOOKUP 公式中的列使用变量

Question

我正在尝试使用这个公式:

=SUMPRODUCT(VLOOKUP(B$4778,$D$4:$DC$4623,{4,5},0))

它工作正常，但我想尝试为 {4,5} 使用变量公式的一部分(要求和的数组中的列)，因为公式需要根据此公式之前的工作表输入进行更改。

我在工作表上有用于设置要搜索的初始列和最终列的单元格(可能有 10 列，但必须从 90 个可用列中选择 10 列)。这些列是与每个年龄相关的人口.所以，如果我需要 10 到 15 岁的人口，我需要总结 5 列。如果是20-25，需要总结5个不同的列。

我尝试使用 Columns 功能，但它似乎对我不起作用。

这些列由用户在单元格中输入搜索范围的上限和下限来选择，然后我将这些值转换为相应的数值列值。

因此，如果他们选择 5 作为下限并选择 10 作为上限，我知道我必须添加 7 才能在数据页(第 12 列)上获得正确的数据列，对于上限(第 17 列)也是如此。

整个可能的搜索区域是 $D$4:$DC$4623 .所以，在公式中，如果我把它写得很长，那将是:

=SUMPRODUCT(VLOOKUP(B$4778,$D$4:$DC$4623,{12,13,14,15,16,17},0))

我更喜欢使用变量将其写出来，如下所示:

=SUMPRODUCT(VLOOKUP(B$4778,$D$4:$DC$4623,{L:U},0)) 

其中变量 L 为 12，变量 U 为 17。

任何人都可以建议一种编写公式的方法吗？

Answer 1

使用这个数组公式:

=SUM(VLOOKUP(B$4778,$D$4:$DC$4623,ROW(INDIRECT(D5 & ":" & E5)),FALSE))

其中 D5 是下层，E5 是上层。

作为一个数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。如果正确完成，则 excel 将输入 {}围绕公式。



或者更好的是使用这个非数组公式:

=SUM(INDEX($D$4:$DC$4623,MATCH(B$4778,$D$4:$D$4623,0),D5):INDEX($D$4:$DC$4623,MATCH(B$4778,$D$4:$D$4623,0),E5))