excel - 使用 INDIRECT、ADDRESS 和 MATCH 的复杂公式 - 有没有更好的方法？

Question

我需要在辅助列中获得一个由两个 1 绑定(bind)的范围的 MAX。

1 是可变的行数。

举例来说，这是我正在谈论的一个示例:

1   0   -1.10%0   0.00%1   0   1.43%0   1.15%0   2.12%0   2.69%0   1.32%0   0.86%0   -0.69%1~[and so on] 

So, for instance, there are two ranges visible that I'm interested in here -- the range between rows 1 and 4 and the range between rows 4 and 12.

In a third column, wherever there is a 1 in the helper column, I want the MAX of the ranges.

I've managed to cobble together this formula that does the job (this is copied directly from the spreadsheet so it's in row 122 and the data currently goes to row 16120, the helper column is column E and the column with the values is F):

=IF(E122=1,MAX(F122:INDIRECT(ADDRESS(ROW()+MATCH(1,E123:$E$16120,0),COLUMN(F122),4))),"")

我的基本想法是通过查找辅助列的下一个 1(使用 MATCH)来构建范围的底部，将其添加到包含在 ADDRESS 函数中的当前行(使用 ROW 和 COLUMN)，然后使用间接。最后，它位于 IF 内，仅命中辅助列中具有 1 的行。

谁能想到一种更优雅、更简洁的方式？

提前致谢。

Answer 1

如果您有更大的数据集，我建议使用 INDEX而不是 OFFSET !后者是不稳定的，即 Excel 将在每次重新计算时重新计算所有 OFFSET 公式和任何依赖项。另一方面，INDEX 是非 volatile 的，即只有当它的任何前任发生变化时，Excel 才会重新计算公式。

因此，试试这个公式:

=IF(E122=1,MAX(F123:INDEX(F123:$F$16120,MATCH(1,E123:$E$16120,0))),"")