如何在Excel中排除初始Retainer计算各周期平均Deal Size

问题背景 #

我们作为招聘代理机构，Excel存储的客户招聘交易数据分为两类：

• 多数交易仅单条记录
• Retainer类型交易对应两条关联记录：一条是客户支付的初始Retainer费，另一条是尾款Completion Fee，两条记录通过Retainer ID识别关联

需求是按账单年、季、月计算平均Deal Size，规则如下：

1. 同一Retainer交易需合并两类费用视为整体
2. 合并后的交易仅在Completion Fee对应的账单周期统计，初始Retainer记录直接排除
   此前尝试SUMIF公式无效，要求不使用宏解决。

分步解决方案 #

1. 标记有效统计记录 #

在原始数据中新增一列（例：命名为是否计入统计），用公式筛选出需要参与计算的记录：

=IF(AND(A2="Retainer", B2="Completion Fee"), "是", IF(AND(A2="Retainer", B2="Initial Retainer"), "否", "是"))

• 逻辑：仅将Retainer类型的Completion Fee记录、所有非Retainer交易标记为有效；Retainer的初始费记录标记为无效，直接排除统计。

2. 计算合并后的单交易总额 #

新增一列（例：命名为合并后Deal Size），计算每条有效记录对应的实际交易总金额：

=IF(J2="是", IF(A2="Retainer", SUMIF($D:$D, D2, $C:$C), C2), 0)

• 逻辑：
  • 无效记录返回0，不影响后续求和
  • 非Retainer交易直接取当前记录的费用值
  • Retainer的Completion Fee记录，通过SUMIF根据Retainer ID汇总同交易的初始费+尾款金额

3. 按账单周期计算平均Deal Size #

假设账单年、季、月分别对应E、F、G列，按以下公式计算：

某周期总交易金额

=SUMIFS($K:$K, $E:$E, 2024, $F:$F, 1, $G:$G, 3, $J:$J, "是")

（将2024、1、3替换为目标统计的年、季、月参数）

某周期有效交易数量

=COUNTIFS($E:$E, 2024, $F:$F, 1, $G:$G, 3, $J:$J, "是")

平均Deal Size

=IFERROR(总交易金额/有效交易数量, 0)

使用IFERROR避免无交易数据时出现#DIV/0!错误。

验证要点 #

• Retainer的初始费记录不会计入交易数量与金额统计
• 同一Retainer交易的总费用仅在Completion Fee对应的账单周期内体现
• 普通交易正常按自身账单周期参与统计

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