Excel同区域同建筑维度下近5笔交易移动平均值计算问题

原有公式错误原因 #

• 初版AVERAGEIFS公式：仅完成了区域、建筑维度的匹配，会统计对应维度下全部历史交易数据，没有最近5笔的条数限制，不符合移动平均的计算逻辑
• 结合OFFSET的调整版公式：AVERAGEIFS的第一参数要求为规则的单元格区域引用，OFFSET返回的动态区域与后续条件区域的尺寸、范围不匹配，无法触发正确计算，直接返回#VALUE!错误

计算前置确认 #

• 数据集已按交易日期完成升序排列，无需额外排序操作
• 计算需严格匹配同一Area ID、同一Building number两个维度，禁止跨区域、跨楼栋统计
• 统计范围为当前行之前的历史交易，不包含当前行本身，取最近5笔计算平均值
• 各字段对应列关系：
  • B列：交易日期
  • C列：Building number（楼号）
  • D列：Area ID（区域ID）
  • N列：每平方英尺支付金额
  • Q列：移动平均值结果存放列

正确公式方案 #

方案1：适用Excel 365/2021及以上支持动态数组的版本 #

在首条数据行（即表头下第一行，默认第2行）的Q2单元格输入以下公式，回车后直接向下填充整列即可：

=LET(
  match_rows,FILTER(ROW($N$2:N2),($D$2:D2=D2)*($C$2:C2=C2)*(ROW($N$2:N2)<ROW())),
  last_5_amt,TAKE(INDEX($N:$N,match_rows),-5),
  AVERAGE(last_5_amt)
)

逻辑说明：先筛选出当前行之前、同区域同楼号的所有历史交易行号，再提取对应行的每平方英尺支付金额，取排序在最后的5条（数据已按日期升序，最后5条即为时间最近的5笔）计算平均值。若当前维度下历史交易不足5笔，会自动计算已有历史交易的平均值，无报错。

方案2：适用Excel 2019及更早不支持动态数组的版本 #

在Q2单元格输入以下公式，输入完成后按Ctrl+Shift+Enter三键结束数组公式录入，再向下填充整列：

=AVERAGE(LOOKUP(LARGE(IF(($D$2:D2=D2)*($C$2:C2=C2)*(ROW($N$2:N2)<ROW()),ROW($N$2:N2)),ROW(INDIRECT("1:"&MIN(5,COUNTIFS($D$2:D2,D2,$C$2:C2,C2)-1)))),ROW($N:$N),$N:$N))

容错提示：如果某行是对应区域+楼号组合的第一条交易记录，无前置历史数据，公式会返回#DIV/0!错误，可在公式外层套IFERROR(公式,"无历史交易")自定义无数据时的显示内容。

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