AGGREGATE函数无法在计算数组上计算的问题及解决方案咨询

首先明确：这不是Excel的Bug，是AGGREGATE函数的设计限制——它仅支持对单元格区域/引用执行聚合，无法直接处理内存数组（即计算后的变量或动态生成的数组）。

针对你处理带#N/A的大型区域、不想用辅助区域的需求，分两种场景给出替代方案：

一、Excel 365/2021及以上版本（支持动态数组） #

利用FILTER函数先剔除#N/A值，再完成计算与聚合，示例如下：

• 求和（区域乘以2后忽略#N/A）：=SUM(FILTER(A1:A10*2, NOT(ISNA(A1:A10))))
• 求均值：=AVERAGE(FILTER(A1:A10*2, NOT(ISNA(A1:A10))))
• 求最大值：=MAX(FILTER(A1:A10*2, NOT(ISNA(A1:A10))))
  该方式直接在内存中完成所有操作，无需辅助区域。

二、旧版Excel（不支持动态数组） #

通过INDEX+ROW将计算后的结果转化为AGGREGATE可识别的形式，配合忽略错误参数（参数6）实现需求，示例：

• 求和（区域乘以2后忽略#N/A）：=AGGREGATE(9,6,INDEX(A1:A10,ROW(A1:A10))*2)
  这里INDEX(A1:A10,ROW(A1:A10))会生成对应单元格的内存数组，乘以2后，AGGREGATE可正常过滤#N/A并完成聚合。

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