按ID和时间段动态计算数据平均值的技术需求

核心需求拆解 #

• 选择用户后自动填充三个指标：
  • 最近有数据月份的Yes占比（Yes数量/(Yes+No总数量)）
  • 上一个有数据月份的Yes占比
  • 年初至今（YTD）累计占比（注意：是该用户所有非空月份中总Yes数/总有效数，不是各月占比的平均值）
• 支持拖拽新增月份数据，自动跳过无数据的空月份，无需手动维护公式范围。

分指标公式实现（兼容Excel 365及旧版本） #

1. 年初至今（YTD）累计占比 #

公式（可直接向下拖拽）：

=COUNTIFS($A:$A, $A2, $D:$ZZ, "Yes")/COUNTIFS($A:$A, $A2, $D:$ZZ, "<>")

• 逻辑说明：
  • $A:$A匹配当前用户ID，确保只统计该用户的数据
  • $D:$ZZ覆盖足够多的列（可根据实际扩展范围），自动包含新增的月份列
  • 分子统计该用户所有非空单元格中的"Yes"数量，分母统计该用户所有非空单元格总数（自动跳过空月份）

2. 最近有数据月份的Yes占比 #

Excel 365/2021（动态数组版，更简洁）：

=LET(
    userData, FILTER($D2:$ZZ2, $D2:$ZZ2<>""),
    lastMonth, TAKE(userData, -1),
    COUNTIF(lastMonth, "Yes")/COUNTA(lastMonth)
)

• 逻辑说明：
  • FILTER筛选出当前用户行的所有非空数据（跳过空月份）
  • TAKE(userData, -1)取最后一组数据（最近的有数据月份）
  • 计算该月份的Yes占比

旧版本Excel兼容版：

=COUNTIF(INDEX($D2:$ZZ2,1,MATCH("*",$D2:$ZZ2,-1)),"Yes")/COUNTA(INDEX($D2:$ZZ2,1,MATCH("*",$D2:$ZZ2,-1)))

• 逻辑说明：
  • MATCH("*",$D2:$ZZ2,-1)找到当前用户行最后一个非空列的位置
  • INDEX定位到该列，计算Yes占比

3. 上一个有数据月份的Yes占比 #

Excel 365/2021（动态数组版）：

=LET(
    userData, FILTER($D2:$ZZ2, $D2:$ZZ2<>""),
    prevMonth, TAKE(userData, -2, 1),
    IFERROR(COUNTIF(prevMonth, "Yes")/COUNTA(prevMonth), "")
)

• 逻辑说明：
  • TAKE(userData, -2, 1)取倒数第二组数据（上一个有数据月份）
  • IFERROR处理只有1个有数据月份的情况（返回空值，避免错误）

旧版本Excel兼容版：

=IFERROR(COUNTIF(INDEX($D2:$ZZ2,1,MATCH("*",$D2:$ZZ2,-1)-1),"Yes")/COUNTA(INDEX($D2:$ZZ2,1,MATCH("*",$D2:$ZZ2,-1)-1)), "")

• 逻辑说明：
  • 找到最后非空列位置后减1，定位到上一个有数据月份
  • IFERROR处理无前置月份的情况

批量使用说明 #

• 所有公式均可直接向下拖拽，自动匹配对应行的用户数据
• 新增月份列时，只要在公式指定的列范围（如$D:$ZZ）内添加，公式会自动纳入新数据，无需手动修改范围

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