无需VBA实现Excel动态求和：如何控制参与求和的行数量

你目前的公式通过手动累加H2到H5对应的区域实现求和，现在希望通过单个单元格动态控制参与求和的行数，完全可以不用VBA实现，以下是具体方案：

原公式示例 #

=MIN(IFERROR(INDEX(
INDEX(INDIRECT('Process Hotels'!H2&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)):INDIRECT("AI"&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)),0)+
INDEX(INDIRECT('Process Hotels'!H3&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)):INDIRECT("AI"&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)),0)+
INDEX(INDIRECT('Process Hotels'!H4&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)):INDIRECT("AI"&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)),0)+
INDEX(INDIRECT('Process Hotels'!H5&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)),0),
0),""))

解决方案 #

假设用单元格K1来指定参与求和的行数（比如输入4就对应H2到H5，输入3对应H2到H4），分两种Excel版本处理：

1. Excel 365/2021（支持动态数组） #

利用SEQUENCE生成连续行号，搭配SUM自动完成累加，公式如下：

=MIN(IFERROR(INDEX(
SUM(INDEX(INDIRECT('Process Hotels'!H&SEQUENCE(K1,1,2)&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)):INDIRECT("AI"&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)),0)),
0),""))

• 核心逻辑：SEQUENCE(K1,1,2)生成从2开始的K1个连续数字，对应H2、H3……H(2+K1-1)，SUM直接累加这些对应区域，替代手动逐个相加。

2. 旧版Excel（无动态数组支持） #

用SUMPRODUCT结合ROW函数生成行号，实现动态累加：

=MIN(IFERROR(INDEX(
SUMPRODUCT(INDEX(INDIRECT('Process Hotels'!H&(ROW(INDIRECT("2:"&(1+K1)))&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)):INDIRECT("AI"&MATCH('Process Hotels'!E2,'Input Hotels'!D:D)),0)),
0),""))

• 核心逻辑：ROW(INDIRECT("2:"&(1+K1)))生成2到1+K1的行号列表，SUMPRODUCT完成对这些区域的累加操作，效果和手动相加一致。

优化建议 #

INDIRECT属于易失性函数，大量使用会降低表格计算性能。如果允许，可以先将匹配行号存到辅助单元格（比如L1=MATCH('Process Hotels'!E2,'Input Hotels'!D:D)），然后用INDEX替代INDIRECT引用区域，示例如下：

=MIN(IFERROR(INDEX(
SUM(INDEX('Input Hotels'!$A:$AI,L1,COLUMN(INDIRECT('Process Hotels'!H&SEQUENCE(K1,1,2)))):INDEX('Input Hotels'!$A:$AI,L1,COLUMN('Input Hotels'!AI:AI))),
0),""))

这样能有效提升表格的计算效率。

内容的提问来源于stack exchange，提问作者Paul Pieless