Excel 2010及以上版本单单元格公式动态构建重复值数组方案问询

该需求可通过Excel 2010兼容的纯数组公式实现，无需VBA。

前提约定（可替换为你实际的表格引用） #

• 存储重复次数的表为「次数表」：标识符列为A列，P1/P2/P3重复次数对应B/C/D列
• 存储实际数值的表为「数值表」：标识符列为A列，P1/P2/P3实际值对应B/C/D列
• 待查询的标识符存放于$F$1，起始索引x1存放于$G$1，结束索引x2存放于$H$1

完整数组公式 #

录入时需按Ctrl+Shift+Enter生效，若你的Excel区域设置用逗号做公式分隔符，把公式里的分号换成逗号即可：

{=SUM(INDEX(LOOKUP(ROW(INDIRECT("1:"&SUM(VLOOKUP($F$1,次数表!$A:$D,{2,3,4},0))))-1,SUBTOTAL(9,OFFSET(VLOOKUP($F$1,次数表!$A:$D,{2,3,4},0),,,-(COLUMN(次数表!$B:$D)-1))),VLOOKUP($F$1,数值表!$A:$D,{2,3,4},0)),N(IF(1,ROW(INDIRECT($G$1&":"&$H$1)))))}

验证结果 #

用你给出的i04示例验证：

• 当$G$1=3、$H$1=5时，公式返回结果为45.1
• 当$G$1=2、$H$1=3时，公式返回结果为30
  完全符合预期。

逻辑说明 #

1. 先通过SUM(VLOOKUP(...))计算目标标识符对应构建数组的总长度
2. 用SUBTOTAL+OFFSET计算每个P类数值的累计重复次数，作为LOOKUP的分段阈值
3. 用LOOKUP匹配每个索引位置对应的实际数值，得到完整的built_Array动态数组
4. 最后按指定的索引区间求和

唯一原生限制 #

该公式构建的数组最大长度不能超过Excel 2010支持的最大行号1048576，常规业务场景完全够用，超出该长度才需要改用VBA。

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