用 WorkBuddy 改版销售分析：一张 50/40/10 加权综合完成率，一眼看出谁真达标

用 WorkBuddy 改版销售分析：一张 50/40/10 加权综合完成率，一眼看出谁真达标

本文记录我把月度销售分析的"单一完成率"升级为"综合完成率"（全品×50% + 中高端×40% + IOT销额×10%）的完整过程。附真实代码、改版思路和 4 个踩坑记录。改版前每月要人工算加权、手动标红，改版后丢数据文件说一句话，1 分钟出带综合完成率和标红的 Excel。

一、为什么要改版：单看全品完成率会看走眼

原来的月度分析表只算"全品完成率"一个指标，结果每个月都会出现两种怪现象：

• 客户 A：全品完成率全组垫底（44%），怎么看都是"最差生"。但他的中高端卖得极好（完成率 190%）——其实整体很健康。旧表却把他排在最后，跟进资源全给错了人。
• 客户 B：全品完成率全组第一（78.5%），表面是"标杆"。但中高端和 IOT 都在拖后腿，综合一算反而垫底。旧表却把他当成榜样，让他带节奏。

结论：单一指标会误导跟进方向。老板要看的不是单科成绩，而是"结构健康度"。

于是定了新口径：综合完成率 = 全品×50% + 中高端×40% + IOT销额×10%。

二、口径从哪来：照着新模板的公式做

改版第一步不是自己想算法，而是找口径依据——新模板里已经有同事设好的综合完成率公式：

= E2*0.5 + H2*0.4 + K2*0.1

把它"翻译"成脚本函数：

def comb(ar, mr, ir):
    """综合完成率 = 全品50% + 中高端40% + IOT销额10%"""
    return ar * 0.5 + mr * 0.4 + ir * 0.1

权重写死在函数里，每个月数字在变、算法不变。

三、改版一共做了四件事

① 主表新增"综合完成率"列，从原来的 18 列扩到 19 列，客户行按全品完成率降序，合计行、地区整体行在底部。

② 标红规则统一升级：全品/中高端/IOT/综合四项完成率，只要低于当月时间进度就标红，所有行都执行（含合计和地区整体），不再搞"客户行跟合计行比"那套复杂规则：

TIME_PROG = 25 / 31   # 数据截至8.25 → 80.6%
for cc in (5, 9, 13, 15):                # 全品/中高端/IOT/综合 四列完成率
    for rr in range(4, row_rg + 1):      # 所有行，含合计/地区整体
        v = ws.cell(rr, cc).value
        if isinstance(v, (int, float)) and v < TIME_PROG:
            _red(ws.cell(rr, cc))

③ 客户 DOS 顺带升级：以前要单独导一张 DOS 表再匹配，现在直接从当月 PSI 明细表第 4 列按销量加权算，少一步导表：

def load_dos_weighted(path):
    num, den = {}, {}
    for r in range(2, ws.max_row + 1):
        v = ws.cell(r, 3).value or 0      # Sell Out 销量
        dos = ws.cell(r, 4).value         # DOS 通路周转
        if isinstance(dos, (int, float)):
            num[name] = num.get(name, 0) + v * dos
            den[name] = den.get(name, 0) + v
    return {k: int(round(num[k] / den[k])) for k in den if den[k]}

④ 环比基数用上月源表重算，不信任模板里的人工旧值（原因见坑 1）。

脚本头部只留会变的常量，每次出报告改三行路径、一个日期就行：

CUR_SALES = r"桌面\8.1-8.25 客户销售门店PSI明细表.xlsx"
PRE_SALES = r"桌面\7.1-7.25 客户销售门店PSI明细表.xlsx"
IOT_FILE  = r"桌面\8.1-8.24 客户IOT销额.xlsx"
TIME_PROG = 25 / 31

四、踩过的四个坑（重点看）

坑 1：模板里的旧数会骗人

模板 M 列有个"上月销售额"，看着像环比基数，实际是占位旧数——拿去跟源表对账，怎么试都对不上（换两种口径都不吻合）。教训：环比基数一律用上月源表重算，模板里的人工旧值只能当参考，不能当数据源。

坑 2：dict.update() 里引用还没写入的键 → KeyError

z.update({
    'all_r': ...,
    'comb_r': comb(z['all_r'], z['m_r'], z['i_r'])   # 报错！
})

update 在构造新字典时，z 本身还没更新，这时候引用 z['all_r'] 就炸了。修复：先 update 写入基础字段，再单独赋值综合完成率：

z.update({'all_r': ..., 'm_r': ..., 'i_r': ...})
z['comb_r'] = comb(z['all_r'], z['m_r'], z['i_r'])

坑 3：数据源要甄别，别看着像就往上填

找 IOT 数据时，系统里有个"看板"也带 IOT 字样，仔细一看是活动权益执行通报（订单级明细），根本不是客户销额——差点用错口径填进去。教训：拿到数据先确认是不是同一个统计口径，名字像不代表内容对。

坑 4：Excel 条件格式别原地改

改标红规则时直接改原 sqref 区域，旧规则会残留导致颜色错乱。正确做法：先把整片区域的条件格式清空，再重建。

五、改版效果

这次改版跑出来的真实结果（客户名已隐去）：

• 11 家核心客户，综合达标（≥80.6%）的只有 4 家，其余 7 家都有短板
• 星锐数码：全品完成率全组垫底（44.3%），但中高端 190% 把综合拉到 106.9% 达标——旧表会冤枉它
• 华鑫科技：全品完成率全组第一（78.5%），综合 78.3% 却排垫底区——旧表会高估它

改版后老板看一张表就能定跟进优先级，不用再听我解释"为什么第一名的客户还要重点跟进"。

六、给你的建议

1. 加权口径先找模板公式做依据，别自己拍脑袋定权重，口径有出处才好跟同事对齐
2. 标红规则统一成"低于时间进度"，所有行一把梭，规则简单、好解释、不出错
3. 每次只改头部常量（文件路径/时间进度/任务数），逻辑一行不动——这是"一键出表"的前提

改版这件事的本质：把"每个月人工算加权、人工标红、人工解释"变成"脚本算好、标好、表格自己会说话"。

本文基于真实工作流整理，代码均经过实际运行验证。