使用Excel公式实现动态历史数据下的可购产品量平均计算

核心思路 #

通过动态识别所有有效历史数据列，先计算每列的「单位金额可购买数量」（数量/对应金额），再求这些单位值的平均值，最后用输入的持有金额乘以该平均值得到结果。新增数据列时，公式会自动纳入计算范围。

假设数据结构 #

• 历史持有金额：第1行（从A1开始，可扩展至任意列）
• 对应可购买数量：第2行（与金额列一一对应）
• 输入持有金额的单元格：F1（可自行调整）
• 结果输出单元格：F2（可自行调整）

公式方案 #

方案1：适用于Excel 365/2021（支持动态数组） #

简洁且自动忽略空值、错误值：

=F1 * AVERAGE(TOCOL(IF(A1:XFD1<>"", A2:XFD2/A1:XFD1, ""), 3))

• TOCOL(...,3)：提取所有非空的单位金额购买值为一维数组，自动过滤空单元格和错误值
• AVERAGE：计算数组的平均值
• 最终用输入金额乘以平均值，得到平均可购买数量

方案2：适用于旧版Excel（无动态数组） #

通过定位最后一个有效数据列实现动态扩展：

=F1 * AVERAGE(OFFSET(A2,0,0,1,MATCH(9.99E+307,A1:XFD1))/OFFSET(A1,0,0,1,MATCH(9.99E+307,A1:XFD1)))

• MATCH(9.99E+307,A1:XFD1)：定位第1行最后一个数值型金额的列号，确保新增列被自动纳入
• OFFSET：根据列号动态获取对应的金额和数量范围
• 计算范围的单位金额购买值平均值，再乘以输入金额

验证示例 #

现有4列历史数据：

计算单位金额购买值的平均值≈0.1116，若输入金额为250000，结果≈250000×0.1116=27900。新增第5列（E1=225000，E2=24000）后，公式会自动加入该列数据重新计算平均值。

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