Excel动态计算近5年数据：两大项目成本平均值优化方案

数据列说明 #

• A列：项目名称（Project Title）
• B列：项目年月（Project Month & Year，需为Excel可识别的日期格式）
• C列：项目成本（Project Cost）

需求 #

数据库包含超过5年的持续更新数据，需实现：

1. 仅统计近5年内的项目数据
2. 剔除成本大于等于C$652的异常值
3. 计算剩余有效数据中前两大项目成本的平均值
4. 适配特殊场景：无有效项目时返回"NA"，仅1个有效项目时返回该项目成本

现有公式（静态范围问题） #

当前公式已实现异常值剔除及特殊场景适配，但采用固定单元格范围，无法自动识别新增的近5年数据：

=IF(COUNTIF(C$400:C$625,">0")<1,"NA",ROUND(@IF(COUNTIF(C$400:C$625,">0")=1,LARGE(C$400:C$625,{1}),AVERAGE(LARGE(IF(C$400:C$625<=C$652,C$400:C$625),{1}),LARGE(IF(C$400:C$625<=C$652,C$400:C$625),{2}))),1))

优化方案 #

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

使用LET和FILTER函数实现动态范围筛选，公式简洁易读：

=LET(
    近5年有效成本, FILTER(C:C, (B:B>=EDATE(TODAY(),-60))*(C:C>0)*(C:C<=C$652)),
    有效项目数, COUNTA(近5年有效成本),
    IF(有效项目数=0,"NA",ROUND(IF(有效项目数=1,近5年有效成本,AVERAGE(LARGE(近5年有效成本,{1,2}))),1))
)

• 核心逻辑：EDATE(TODAY(),-60)自动计算当前日期往前推5年（60个月）的起始日期，FILTER筛选出符合「近5年+成本>0+成本≤异常值」的有效数据
• 按有效项目数分支处理：无项目返回"NA"，单项目返回成本值，多项目返回前两大成本的平均值

方案2：旧版Excel（不支持动态数组） #

使用数组公式（输入后按Ctrl+Shift+Enter确认）实现动态筛选：

=IFERROR(ROUND(IF(COUNT(IF((B:B>=EDATE(TODAY(),-60))*(C:C>0)*(C:C<=C$652),C:C))=0,"NA",IF(COUNT(IF((B:B>=EDATE(TODAY(),-60))*(C:C>0)*(C:C<=C$652),C:C))=1,MAX(IF((B:B>=EDATE(TODAY(),-60))*(C:C>0)*(C:C<=C$652),C:C)),AVERAGE(LARGE(IF((B:B>=EDATE(TODAY(),-60))*(C:C>0)*(C:C<=C$652),C:C),1),LARGE(IF((B:B>=EDATE(TODAY(),-60))*(C:C>0)*(C:C<=C$652),C:C),2)))),1),"NA")

• 核心逻辑：通过数组条件筛选符合要求的成本，统计有效项目数后返回对应结果，IFERROR兜底处理错误场景

注意事项 #

• 确保B列的「项目年月」是Excel可识别的日期格式（如2023/10或2023-10），若为文本格式需先用DATEVALUE转换（例如DATEVALUE(B2)）
• 若数据有表头，建议将范围改为B2:C:X（跳过表头行），避免表头干扰计算

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