Excel如何基于最近3/5/7/10等历史观测值进行数据预测

前提约定 #

我们默认你的数据按如下结构存储，你可根据实际表格调整公式中的单元格引用：

• A列（A2:A22）：2020/5/1至2020/5/21的日期，按时间升序排列，A23及向下为待预测的后续日期
• B列（B2:B22）：对应日期的实际注册量，B23及向下为待填充的预测值
• D1单元格：你设置的天数选择下拉框，可选值为3/5/7/10等你需要的观测天数

方案1：适用于Excel 365/2021及以上版本 #

使用TAKE函数直接倒取最近N天的观测数据，公式更简洁易维护，在首个待预测单元格（B23）输入如下公式后下拉填充即可：

=FORECAST.LINEAR(A23, TAKE($B$2:$B$22, -$D$1), TAKE($A$2:$A$22, -$D$1))

• 公式逻辑：TAKE(范围, -N)表示从指定范围的末尾提取N条数据，自动匹配你选择的观测天数

方案2：适用于旧版Excel（无TAKE函数） #

使用OFFSET+COUNTA组合构造动态观测范围，在首个待预测单元格（B23）输入如下公式后下拉填充即可：

=FORECAST.LINEAR(A23, OFFSET($B$2, COUNTA($B$2:$B$1000)-$D$1, 0, $D$1, 1), OFFSET($A$2, COUNTA($B$2:$B$1000)-$D$1, 0, $D$1, 1))

• 公式逻辑：COUNTA统计实际注册量的总条数，OFFSET从首个数据行开始偏移定位到最近N天的起始位置，提取N条数据作为观测样本
• 注意：公式中的$B$2:$B$1000可替换为你实际的注册量存储最大范围，不要包含预测值区域避免计数错误

效果验证 #

你可以切换D1的取值确认匹配需求：

• 选择3：自动取5月19-21日的3条数据作为观测样本
• 选择5：自动取5月17-21日的5条数据作为观测样本
• 以此类推

注意事项 #

• 请确保日期列按时间升序排列，最新的实际数据位于观测区域的最底部
• 如果你的实际数据存在空值，365版本可在TAKE外层嵌套FILTER过滤空值：TAKE(FILTER($B$2:$B$22, $B$2:$B$22<>""), -$D$1)

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