测算不同规模客户年度所需FTE服务投入的Excel方法

已知前提对齐 #

• 单名全职销售（FTE）年标准工作时长为2080小时，测算默认销售所有工作时间全部用于服务自有客户
• 客户规模评级共8档：0（极小型）到7（最大型），默认客户规模越大，单客所需服务投入越高
• 测算优先以单个销售为计算单元，消除不同销售对接客户总量差异带来的结果偏差

分步操作与对应Excel公式 #

1. 原始数据规整 #

先把你的原始表整理成标准结构：每一行对应1名销售，列字段依次为：销售ID/姓名、规模0客户数、规模1客户数、规模2客户数……规模7客户数。
附原始表样本参考：

2. 基础指标计算 #

先给每个销售计算核心基础值，所有公式输入第一行后下拉即可批量填充：

• 新增「单销售总客户数」列（假设规模0-7客户数在B到I列，第2行为第一个销售的数据），输入公式：

  =SUM(B2:I2)
  

• 如果需要看全公司各规模客户的总量分布，新增汇总行，用SUM()函数分别统计每一列（同一规模等级）的客户总数即可，比如统计全公司规模0客户总数：

  =SUM(B:B)
  

3. 不同规模客户相对投入差值计算（核心步骤） #

不要直接用全公司汇总数据计算，否则会被对接客户数极多/极少的销售带偏结果，按以下步骤算相对权重：

1. 在表格空白区域建一个系数计算区，比如Z1:Z8单元格，依次填入规模等级0-7，其中Z1单元格（对应规模0）固定填1，作为服务投入的基准单位，Z2-Z8（对应规模1-7）留空待计算
2. 新增「加权客户总当量」列，对第2行销售，输入SUMPRODUCT函数匹配系数区计算：

   =SUMPRODUCT(B2:I2,$Z$1:$Z$8)
   

3. 新增「单当量服务时长」列，第2行公式为：

   =2080/[对应行加权客户总当量单元格]
   

4. 调出Excel自带的「规划求解」加载项，设置约束条件为Z2≥Z1、Z3≥Z2……Z8≥Z7（匹配规模越大投入越高的常识），求解目标为所有销售的单当量服务时长方差最小，运行求解后就能得到0-7每一档客户相对于规模0客户的服务投入倍数，直接用最高档/最低档系数就能算出大小客户的服务投入差值
5. 要换算单客年服务绝对时长的话，直接用对应规模的系数乘以单当量平均时长即可

4. 快速估算方案（无需规划求解） #

如果只需要快速看大致差值，不需要高精度拟合，可以用简化方法：

• 新增「单客户平均服务时长」列，第2行公式：

  =2080/[对应行总客户数单元格]
  

• 用AVERAGEIFS函数分别统计各规模客户的加权平均单客服务时长，比如统计规模7客户的平均单客时长：

  =AVERAGEIFS([单客户平均服务时长列], [规模7客户数列], ">0")
  

• 最后用规模7的单客平均时长除以规模0的数值，就能得到两类客户的大致服务投入倍数差

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