#!/usr/bin/env python3 # -*- coding: utf-8 -*- """主货号聚类 + 资料缺口任务清单 输入： 附件1 销售额占比 75% 货号（80 个） 附件2 已有资料覆盖矩阵（56 个货号 / 167 份资料） ERP 2026 主档（货号→物料名称、销售额） 输出： 销售分析/主货号聚类与资料缺口清单_2026-10-26.xlsx """ import json import os import re from collections import defaultdict import openpyxl from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' DOC = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents' F1 = os.path.join(DOC, 'doc_1fb1044c5ded_2026-10-24-附件1（销售额占比80%的货号分析）.xlsx') F2 = os.path.join(DOC, 'doc_a1cb1a53ca50_2026-10-24-附件2（已有数据思维导图清单）.xlsx') OUT = os.path.join(ROOT, '销售分析', '主货号聚类与资料缺口清单_2026-10-26.xlsx') SKU_MAP = '/tmp/sku_map.json' CORE = ['冻融稳定性', '加速稳定性', '效期/长期/实时稳定性', '运输/物流稳定性'] FORM_TOKENS = ['NA', 'DG', 'DB', 'TP', 'FP', '5P', 'O20', 'O25', 'S50', 'S70', 'P01', 'P02', 'P03'] def is_component(sku): """-1 / -2 这类是配方组分，不单独作为产品""" return bool(re.search(r'-\d$', sku)) and not re.search(r'-\d{6}$', sku) def base_sku(sku): """主货号：去掉任意位置的形态标记（-NA-/-DG-…）与末尾浓度(-20/-50)， 保留 6 位配方号（如 095203）。""" s = sku.strip().upper() # 形态标记可能出现在中段：M2181-4-NA-095203 for t in FORM_TOKENS: s = re.sub(r'-(%s)(?=-|$)' % t, '', s) s = re.sub(r'-{2,}', '-', s).strip('-') # 末尾浓度档：FE20-50 / E07-20（两位数，且前面不是 6 位配方） m = re.match(r'^(.*?[A-Z]\d*)-\d{2}$', s) if m and not re.search(r'\d{6}$', m.group(1)): s = m.group(1) return s def kind(name): n = name or '' if re.search(r'Premix|Mix|预混', n, re.I): return '预混液' if re.search(r'Taq|RTase|逆转录|UNG|Bst|酶|Enzyme|Polymerase', n, re.I): return '单酶' return '其他' def xfold(name): m = re.search(r'(\d+(?:\.\d+)?)\s*[×xX]', name or '') return m.group(1) + '×' if m else '' def family(name): n = re.sub(r'^\s*\d+(?:\.\d+)?\s*[×xX]\s*', '', name or '') n = re.sub(r'\s*[\(（].*$', '', n).strip() n = n.replace('Ⅱ', 'II').replace('Ⅲ', 'III') return n or '未命名系列' def conc(sku): m = re.search(r'-(\d{2})$', sku) return m.group(1) if m else '' def load_f1(): ws = openpyxl.load_workbook(F1, data_only=True)['销售额占比75%的货号'] out = {} for r in range(2, ws.max_row + 1): sku = str(ws.cell(r, 2).value or '').strip() if not sku: continue out[sku] = {'amt': float(ws.cell(r, 3).value or 0), 'note': str(ws.cell(r, 5).value or ''), 'rank': ws.cell(r, 1).value} return out def load_f2(): wb = openpyxl.load_workbook(F2, data_only=True) ws = wb['覆盖矩阵'] out = {} for r in range(3, ws.max_row + 1): sku = str(ws.cell(r, 2).value or '').strip() if not sku: continue out[sku] = { 'series': str(ws.cell(r, 3).value or ''), 'docs': int(ws.cell(r, 4).value or 0), 'core': {CORE[i]: str(ws.cell(r, 5 + i).value or '').strip() for i in range(4)}, } return out def main(): f1 = load_f1() f2 = load_f2() sm = json.load(open(SKU_MAP, encoding='utf-8')) names, amts = sm['name'], sm['amt'] universe = [] for s in list(f1) + [s for s in f2 if s not in f1]: universe.append(s) # 补：ERP 销售额 ≥ 20 万的货号也纳入（避免只覆盖附件1） for s, a in amts.items(): if a >= 200000 and s not in universe: universe.append(s) groups = defaultdict(list) universe = [re.sub(r'\s+', '', x) for x in universe] f2 = {re.sub(r'\s+', '', k): v for k, v in f2.items()} for sku in universe: if is_component(sku): continue b = base_sku(sku) groups[b].append(sku) rows = [] for base, variants in groups.items(): names_v = [names.get(v, '') for v in variants] kd = '预混液' if any(kind(n) == '预混液' for n in names_v) else ('单酶' if any(kind(n) == '单酶' for n in names_v) else '其他') total = sum(amts.get(v, 0) or f1.get(v, {}).get('amt', 0) for v in variants) def docs_of(v): if v in f2: return f2[v]['docs'], f2[v]['core'] for k, val in f2.items(): if v.startswith(k) or k.startswith(v): return val['docs'], val['core'] return 0, {} docs = sum(docs_of(v)[0] for v in variants) core_have = set() for v in variants: for k, val in (docs_of(v)[1] or {}).items(): if val and val not in ('0', ''): core_have.add(k) missing = [c for c in CORE if c not in core_have] for v in variants: rows.append({ '主货号': base, '变体货号': v, '物料名称': names.get(v, ''), '类型': kind(names.get(v, '')), '主类型': kd, 'X数': xfold(names.get(v, '')), '浓度': conc(v), '2026销售额': round(amts.get(v, 0) or 0, 2), '附件1排名': f1.get(v, {}).get('rank', ''), '附件1备注': f1.get(v, {}).get('note', ''), '现有资料份数': docs_of(v)[0], '主货号资料份数': docs, '已具备核心项': '、'.join(sorted(core_have)) or '无', '缺失核心项': '、'.join(missing) or '无', }) rows.sort(key=lambda r: (-r['2026销售额'], r['主货号'])) bases = sorted({r['主货号'] for r in rows}, key=lambda b: -sum(r['2026销售额'] for r in rows if r['主货号'] == b)) wb = openpyxl.Workbook() H = Font(color='FFFFFF', bold=True) F = PatternFill('solid', fgColor='1F4E79') ws = wb.active ws.title = '主货号聚类' heads = ['主货号', '产品系列', '类型', '变体数', '变体清单', 'X数', '浓度档', '2026销售额', '资料份数', '资料状态', '缺失核心项'] ws.append(heads) for b in bases: rs = [r for r in rows if r['主货号'] == b] fam = family(rs[0]['物料名称']) st = '未收录' if rs[0]['主货号资料份数'] == 0 else ('齐全' if rs[0]['缺失核心项'] == '无' else '部分') ws.append([b, fam, rs[0]['主类型'], len(rs), '、'.join(r['变体货号'] for r in rs), '、'.join(sorted({r['X数'] for r in rs if r['X数']})) or '—', '、'.join(sorted({r['浓度'] for r in rs if r['浓度']})) or '—', round(sum(r['2026销售额'] for r in rs), 2), rs[0]['主货号资料份数'], st, rs[0]['缺失核心项']]) for c in ws[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([20, 30, 10, 7, 50, 9, 10, 13, 9, 9, 40], 1): ws.column_dimensions[get_column_letter(i)].width = w ws.freeze_panes = 'A2' ws2 = wb.create_sheet('变体明细') h2 = ['主货号', '变体货号', '物料名称', '类型', 'X数', '浓度', '2026销售额', '附件1排名', '现有资料份数', '缺失核心项', '附件1备注'] ws2.append(h2) for r in rows: ws2.append([r[h] for h in h2]) for c in ws2[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([18, 22, 46, 8, 8, 7, 13, 9, 9, 36, 30], 1): ws2.column_dimensions[get_column_letter(i)].width = w ws2.freeze_panes = 'A2' ws3 = wb.create_sheet('资料缺口任务清单') h3 = ['任务ID', '主货号', '类型', '变体数', '2026销售额(万)', '现有资料份数', '缺失核心项', '优先级', '建议动作', '责任部门', '预计工时(人日)', '计划开始', '计划完成'] ws3.append(h3) n = 0 for b in bases: rs = [r for r in rows if r['主货号'] == b] miss = rs[0]['缺失核心项'] if miss == '无': continue sales = sum(r['2026销售额'] for r in rs) docs = rs[0]['主货号资料份数'] pri = 'P0' if sales >= 500000 else ('P1' if sales >= 100000 else 'P2') items = miss.split('、') if docs == 0: action = '先建主货号资料档案（收集现有报告），再补：' + miss days = 2 + 2 * len(items) else: action = '补充：' + miss days = 2 * len(items) n += 1 ws3.append([f'T{n:03d}', b, rs[0]['主类型'], len(rs), round(sales / 1e4, 1), docs, miss, pri, action, '产品部/研发', days, '', '']) for c in ws3[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([8, 22, 9, 7, 14, 10, 30, 7, 52, 12, 13, 11, 11], 1): ws3.column_dimensions[get_column_letter(i)].width = w ws3.freeze_panes = 'A2' ws4 = wb.create_sheet('口径说明') for line in [ ['主货号定义', '去掉形态后缀（-NA/-DG/-DB/-TP/-FP/-5P/-O20/-S50…）与浓度后缀（-20/-50/-100）后的系列代码；含 6 位配方号的保留配方号'], ['类型判定', '物料名称含 Premix/Mix/预混 → 预混液；含 Taq/RTase/UNG/Bst/酶 → 单酶'], ['X 数', '从物料名称前缀提取（如 5×、2×、12.5×）'], ['浓度', '货号末尾两位（-20/-50/-100），代表酶浓度档位'], ['资料来源', '附件2 覆盖矩阵 56 个货号 / 167 份资料；附件1 销售额 80 个货号；ERP 2026 销售额补足 ≥20 万货号'], ['缺口判定', '冻融/加速/效期/运输 四项核心资料，主货号下任一变体有即视为已具备'], ['优先级', 'P0：主货号 2026 销售额 ≥50 万；P1：≥10 万；P2：其余'], ['待确认', '责任部门与工时暂留空，等你定人后我再排时间节点并出甘特图'], ]: ws4.append(line) ws4.column_dimensions['A'].width = 14 ws4.column_dimensions['B'].width = 110 wb.save(OUT) print('主货号', len(bases), '｜变体', len(rows), '｜任务', n) print('销售额合计 %.0f 万' % (sum(r['2026销售额'] for r in rows) / 1e4)) print('Top10 主货号:') for b in bases[:10]: rs = [r for r in rows if r['主货号'] == b] print(' %-18s %-4s 变体%d %8.1f万 缺:%s' % (b, rs[0]['主类型'], len(rs), sum(r['2026销售额'] for r in rs) / 1e4, rs[0]['缺失核心项'][:26])) print('✅', OUT) if __name__ == '__main__': main()