如何在Excel中实现基于每日数据的自动月度预测模型

字段约定 #

先统一两个表的字段对应关系，你可以根据自己的实际表格调整列号：

• 表A（输出表）：A列=患者姓名，B列=预测月份（日期格式，示例值为2021/10/1代表2021年10月），C列=待计算的月度预测值
• 表B（数据表）：A列=单日数据日期，B列=患者姓名，C列=Number of X指标值

适用Excel 365/2021及以上版本公式 #

直接在表A的C2单元格输入以下公式，下拉即可批量计算所有患者所有月份的预测值：

=LET(
target_month, B2,
target_patient, A2,
sum_x, SUMIFS(表B[C列], 表B[B列], target_patient, 表B[A列], ">="&EOMONTH(target_month,-1)+1, 表B[A列], "<="&EOMONTH(target_month,0)),
passed_days, MAX(表B[A列]*(表B[B列]=target_patient)*(YEAR(表B[A列])=YEAR(target_month))*(MONTH(表B[A列])=MONTH(target_month)))-EOMONTH(target_month,-1),
total_days, DAY(EOMONTH(target_month,0)),
IFERROR(sum_x/passed_days*total_days, 0)
)

公式逻辑说明 #

• sum_x部分：用SUMIFS匹配指定患者、指定月份的所有X指标求和，对应你需求里的13这个统计值
• passed_days部分：提取该患者当月在表B中最新的导入日期，减去当月1号得到已过天数，对应示例的15天
• total_days部分：自动计算对应月份的总天数，适配大小月、闰二月
• IFERROR：当月无数据时返回0避免报错，可按需替换为""返回空值

适用旧版Excel/WPS公式 #

如果你的版本不支持LET函数，用以下数组公式，输入后按Ctrl+Shift+Enter触发数组运算即可：

=IFERROR(SUMIFS(表B!C:C,表B!B:B,A2,表B!A:A,">="&EOMONTH(B2,-1)+1,表B!A:A,"<="&EOMONTH(B2,0))/MAX(IF((表B!B:B=A2)*(YEAR(表B!A:A)=YEAR(B2))*(MONTH(表B!A:A)=MONTH(B2)),表B!A:A,0)-EOMONTH(B2,-1))*DAY(EOMONTH(B2,0)),0)

可选优化 #

如果你的表B不会缺漏单日数据，已过天数也可以用COUNTIFS统计匹配的行数计算，比取最大日期更稳定，可自行替换公式中passed_days的对应部分。

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