Excel中依次引用列内可用数值重估的公式实现咨询

• 问题根源：原公式中MATCH(TRUE,ISNUMBER(BM:BM),0)始终返回BM列第一个数值的行号，导致下拉填充时重复引用首个数值，无法递进获取后续可用数值。

• 核心修改思路：用SMALL+IF组合动态获取BM列第k个数值的行号（k为当前X列单元格对应的序号，下拉时自动递增），替代固定的MATCH查找逻辑。

• 修改后的公式：

=IFNA(IF(E3=H3;0;IFS(AND(Q3<>"-";INDEX(BM:BM;SMALL(IF(ISNUMBER(BM:BM);ROW(BM:BM));ROW()-ROW(X$2)+1)));INDEX(BM:BM;SMALL(IF(ISNUMBER(BM:BM);ROW(BM:BM));ROW()-ROW(X$2)+1))*(1+Input!$C$10)^((Q3-VLOOKUP('Calcolo Massivo Hoja1'!B3;Perimetro!$B:$C;2;0))/365);AND(R3<>"-";INDEX(BM:BM;SMALL(IF(ISNUMBER(BM:BM);ROW(BM:BM));ROW()-ROW(X$2)+1)));INDEX(BM:BM;SMALL(IF(ISNUMBER(BM:BM);ROW(BM:BM));ROW()-ROW(X$2)+1))*(1+Input!$C$10)^((R3-VLOOKUP('Calcolo Massivo Hoja1'!B3;Perimetro!$B:$C;2;0))/365)));"-")

注：若使用Excel 2019及更早版本，输入公式后需按Ctrl+Shift+Enter触发数组计算；Excel 365/2021支持动态数组，直接回车即可生效。

• 关键逻辑解释：
  • SMALL(IF(ISNUMBER(BM:BM),ROW(BM:BM)),ROW()-ROW(X$2)+1)：筛选BM列中所有数值所在的行号，按从小到大排序后取第ROW()-ROW(X$2)+1个行号。下拉填充时，ROW()随单元格行号递增，序号k自动从1、2、3...依次变化，实现动态定位第N个数值。
  • INDEX(BM:BM, ...)：根据上述动态行号提取对应BM列数值，完成依次引用第1、第2、第N个可用数值的需求。

内容的提问来源于stack exchange，提问作者Raffaele Sabatino