如何创建动态公式按指定公司计算最近12条数据的平均值
2026-5-11
适配多列的最近12条记录平均值公式解决方案
你已经写出了单列计算的核心公式，现在我们把它扩展成支持按公司名称匹配列、可向下拖拽复用的版本，完美适配新增公司列的场景。
核心公式（数组公式）
假设你的结果区域中，C2单元格是要计算的公司名称（比如"Company1"），在D2输入以下公式（Excel 365/2021可直接回车，旧版本需按Ctrl+Shift+Enter触发数组计算）：
=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(INDEX($G:$XFD,,MATCH($C2,$G$2:$XFD$2,0))),ROW($G:$G)),ROW(INDIRECT("1:"&MIN(12,COUNT(INDEX($G:$XFD,,MATCH($C2,$G$2:$XFD$2,0))))))),ROW($G:$G),INDEX($G:$XFD,,MATCH($C2,$G$2:$XFD$2,0))))
公式拆解（帮你理解每一步）
- 匹配公司列：
MATCH($C2,$G$2:$XFD$2,0)
从表头行（G2到XFD2，覆盖所有可能的列）中找到当前公司名称对应的列位置，比如"Company1"会返回1，对应G列。 - 动态引用公司列：
INDEX($G:$XFD,,MATCH(...))
根据上面得到的列位置，自动引用对应的整个公司数据列（比如G:G、H:H），不管公司列在哪个位置都能精准匹配。 - 筛选有效行号：
IF(ISNUMBER(...),ROW($G:$G))
找出该公司列中所有包含数值的行号，忽略空单元格。 - 取最近12条记录：
LARGE(...,ROW(INDIRECT("1:"&MIN(12,COUNT(...)))))
提取最大的前N个行号（N是该公司列的有效记录数和12的较小值，确保最多取最近12条）。 - 获取对应数值并计算平均：
LOOKUP(...)+AVERAGE()
通过行号找到对应的数值，最后计算这些数值的平均值。
使用步骤
- 在结果区域的
C列依次输入要计算的公司名称（比如C2=Company1、C3=Company2、C4=Company3）； - 在
D2输入上面的公式，完成后拖拽填充到D3、D4，自动计算对应公司的结果； - 当新增Company4到任意列（比如J2），只需在
C5输入"Company4"，拖拽D4的公式到D5，就能自动计算Company4的最近12条记录平均值。
简化版（适合横向拖拽场景）
如果你的结果区域是和公司列横向对齐的（比如在G34计算Company1的平均，H34计算Company2），直接用你原来的公式横向拖拽即可，Excel会自动把G:G替换为H:H、I:I：
=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(G:G),ROW(G:G)),ROW(INDIRECT("1:"&MIN(12,COUNT(G:G))))),ROW(G:G),G:G))
这样新增Company4列后，只需把公式拖拽到对应列的单元格，就能自动计算。
内容的提问来源于stack exchange，提问作者Saverio
