求8年月度订单的任意月度活跃订单数计算公式（免同期群分析）

需求背景 #

有从2022年1月开始的月度订单数据（按列存储），每个月份订单对应独立服务周期，需要计算任意指定月份的活跃订单数，且无需大规模同期群分析。核心逻辑：

• 起始月份范围：指定月份的潜在服务起始最早可追溯至100个月前，最晚为当前月份
• 活跃判断：起始月订单的服务周期需覆盖指定月份
• 汇总符合条件的订单数求和

示例数据 #

通用公式 #

假设目标活跃数存放在第4行，对应列的月份在第1行，订单数在第2行，服务周期在第3行，将公式输入到目标单元格（如A4）后向右拖拽即可：

=SUMPRODUCT($A$2:X$2, --(COLUMN(X$1)-COLUMN($A$1:X$1)+1 <= $A$3:X$3), --(COLUMN($A$1:X$1) >= MAX(COLUMN($A$1), COLUMN(X$1)-99)))

注：公式中X需替换为当前目标单元格所在列的列标，拖拽时Excel会自动调整引用

公式逻辑拆解 #

1. 订单数范围：$A$2:X$2 锁定所有起始月的订单数，范围到当前目标列
2. 活跃判断条件：COLUMN(X$1)-COLUMN($A$1:X$1)+1 <= $A$3:X$3
   • 计算当前目标月与各起始月的间隔月数（包含起始月和当前月），判断该间隔是否在对应服务周期内，--将布尔判断转为1/0数值
3. 起始月范围限制：COLUMN($A$1:X$1) >= MAX(COLUMN($A$1), COLUMN(X$1)-99)
   • 确保起始月不早于数据第一列，且不超过当前目标月往前100个月（列号差为99），避免无效数据参与计算
4. 求和计算：SUMPRODUCT将符合双条件的订单数（1×订单数）求和，得到目标月的活跃订单数

示例验证（Apr-22，单元格D4） #

• A列（Jan-22）：间隔月数=4-1+1=4>3 →不计入
• B列（Feb-22）：间隔月数=4-2+1=3≤10 →计入520
• C列（Mar-22）：间隔月数=4-3+1=2>1 →不计入
• D列（Apr-22）：间隔月数=4-4+1=1≤2 →计入110
• 总和：520+110=630，与示例目标一致

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