Excel中INDIRECT搭配数组、聚合函数计算可购商品数公式报错求解

AI 小北

2026-8-27

问题目标

我使用的软件版本为Excel for Mac 2019，现有一份会随时间动态调整的价格表（示例中价格数据位于B2:B6区域，商品名称位于A2:A6区域），另有同样会动态调整的预算值存放于D2单元格。我无法在价格表中新增累计求和等中间计算列，因此需要仅通过单单元格公式完成三项计算：

在E2单元格输出当前预算可购买的商品件数；
在F2单元格输出购买对应数量商品后的剩余找零；
在G2单元格输出逗号分隔的可购买商品名称列表。

示例数据结构如下：

A         B       C       D         E         F                  G
  +---------+---------+-----+---------+-------+---------+---------------------------+
1 |  Label  |  Price  |     | Budget  | Items | Change  |          Item(s)          |
  +---------+---------+-----+---------+-------+---------+---------------------------+
2 | Item #1 | $ 10.00 |     | $ 40.00 |     3 | $  4.50 | Item #1, Item #2, Item #3 |
  +---------+---------+-----+---------+-------+---------+---------------------------+
3 | Item #2 | $ 20.00 |     |         |       |         |                           |
  +---------+---------+-----+---------+-------+---------+---------------------------+
4 | Item #3 | $  5.50 |     |         |       |         |                           |
  +---------+---------+-----+---------+-------+---------+---------------------------+
5 | Item #4 | $ 25.00 |     |         |       |         |                           |
  +---------+---------+-----+---------+-------+---------+---------------------------+
6 | Item #5 | $ 12.50 |     |         |       |         |                           |
  +---------+---------+-----+---------+-------+---------+---------------------------+

针对E2单元格，我最初尝试编写了如下数组公式：
{=MAX(N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1)}
但使用示例数据计算时，该公式返回结果为-1，不符合预期。

备注：若E2计算结果正确，F2和G2的公式可正常运行，我初步编写的两个单元格公式分别为：{=$D2-SUM(IF((ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1)<=$E2,$B$2:$B$6,0))}（找零计算）、{=TEXTJOIN(", ",TRUE,INDIRECT("$A$2:$A$"&(MIN(ROW($B$2:$B$6))+$E2-1)))}（商品列表拼接）。

现象观测

公式{="$B$2:$B$"&ROW($B$2:$B$6)}可按预期返回{"$B$2:$B$2";"$B$2:$B$3";...;"$B$2:$B$6"}的区域引用文本数组；
公式{=INDIRECT("$B$2:$B$"&ROW($B$2:$B$6))}理论应返回{{$B$2:$B$2},{$B$2:$B$3},...,{$B$2:$B$6}}的嵌套区域数组，但作为1行5列的多单元格数组公式输入时全量返回#VALUE!错误，单单元格下按F9校验计算结果返回{10;#N/A;#N/A;#N/A;12.5}；
公式{=SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2}作为多单元格数组公式输入时可按预期返回{TRUE;TRUE;TRUE;FALSE;FALSE}的布尔数组，但单单元格下按F9校验返回#VALUE!错误；
后续嵌套N函数、行号偏移计算的各版本公式，均存在“多单元格数组输入时结果符合预期、单单元格数组公式输入时返回错误值”的问题，最终外层MAX函数取数得到错误结果-1；
若将中间计算的数组结果通过多单元格数组公式输入到E10:E14区域，再使用=MAX($E$10:$E$14)取最大值，可得到正确结果3。

原因推测

初步判断：在单单元格数组公式计算场景下，INDIRECT函数未被程序识别为可返回数组的函数，和/或SUM等聚合函数在单单元格数组公式语境下未输出数组结果，最终导致整体计算逻辑异常。

已验证可行方案

E2（可购数量）数组公式：
{=IF($B$2<=$D2,MATCH(1,0/(MMULT(N(ROW($B$2:$B$6)>=TRANSPOSE(ROW($B$2:$B$6))),$B$2:$B$6)<=$D2)),0)}
（贡献者：Jos Woolley）
F2（找零）公式：
=IF($E2=0,MAX(0,$D2),$D2-SUM($B$2:INDEX($B$2:$B$6,$E2)))
（贡献者：P.b）
G2（商品列表）公式：
=IF($E2=0,"",TEXTJOIN(", ",TRUE,$A$2:INDEX($A$2:$A$6,$E2)))
（贡献者：P.b）

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