#!/usr/bin/env python3 # -*- coding: utf-8 -*- """M5244 系列统计 + 归集异常核对（货号编码法 vs 物料名称法）""" import os import re from collections import Counter, defaultdict import openpyxl from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' SRC = os.path.join(ROOT, '销售分析', '产品系列资料归档与缺口清单_2026-10-26.xlsx') F2 = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_a1cb1a53ca50_2026-10-24-附件2（已有数据思维导图清单）.xlsx' OUT = os.path.join(ROOT, '销售分析', 'M5244系列统计与归集异常核对_2026-10-26.xlsx') CORE = ['冻融稳定性', '加速稳定性', '效期/长期/实时稳定性', '运输/物流稳定性'] def family(n): n = re.sub(r'^\s*\d+(?:\.\d+)?\s*[×xX]\s*', '', n or '') n = re.sub(r'\s*[\(（].*$', '', n).strip() return re.sub(r'\s+', ' ', n).replace('Ⅱ', 'II').replace('Ⅲ', 'III').replace('Ⅳ', 'IV') def key(sku): s = re.sub(r'\s+', '', sku).upper() s = re.sub(r'-\d$', '', s) for t in ['NA', 'DG', 'DB', 'TP', 'FP', '5P', 'O20', 'O25', 'S50', 'S70', 'P01', 'P02', 'P03']: s = re.sub(r'-%s(?=-|$)' % t, '', s) s = re.sub(r'-{2,}', '-', s).strip('-') if re.match(r'^P[A-Z]', s): s = s[1:] m = re.match(r'^([A-Z]+)(\d+)(.*)$', s) if not m: return s L, D = m.group(1), m.group(2) if L.startswith('F') and len(L) > 1: L = L[1:] if L in ('M', 'MD') and len(D) >= 4: D = D[-3:] return L + D def xfold(name): m = re.search(r'(\d+(?:\.\d+)?)\s*[×xX]', name or '') return m.group(1) + '×' if m else '' def docs_of(sku, f2): if sku in f2: return f2[sku] for k, v in f2.items(): if sku.startswith(k) or k.startswith(sku): return v return {'docs': 0, 'core': {}} def main(): wb = openpyxl.load_workbook(SRC) rows = list(wb['主货号与变体明细'].iter_rows(min_row=2, values_only=True)) seen, recs = set(), [] for r in rows: if r[3] in seen: continue seen.add(r[3]) recs.append({'cur': r[0], 'fam': r[1], 'sku': r[3], 'name': r[4] or '', 'type': r[5], 'comp': r[7], 'dg': r[8], 'na': r[9], 'amt': r[10] or 0, 'key': key(r[3]), 'kf': family(r[4] or '')}) # 附件2 资料 ws2 = openpyxl.load_workbook(F2, data_only=True)['覆盖矩阵'] f2 = {} for i in range(3, ws2.max_row + 1): s = str(ws2.cell(i, 2).value or '').strip() if s: f2[re.sub(r'\s+', '', s)] = {'docs': int(ws2.cell(i, 4).value or 0), 'core': {CORE[j]: str(ws2.cell(i, 5 + j).value or '').strip() for j in range(4)}} m = sorted([r for r in recs if r['key'] == 'M244'], key=lambda r: -r['amt']) docs = sum(docs_of(r['sku'], f2)['docs'] for r in m) have = set() for r in m: for k, v in docs_of(r['sku'], f2)['core'].items(): if v and v not in ('0', ''): have.add(k) miss = [c for c in CORE if c not in have] # 误入 bycur = defaultdict(list) for r in recs: bycur[r['cur']].append(r) mis = [] for cur, rs in bycur.items(): main_key = Counter(r['key'] for r in rs).most_common(1)[0][0] for r in rs: if r['key'] != main_key: mis.append((cur, main_key, r)) # 同键被拆 bykey = defaultdict(set) for r in recs: bykey[r['key']].add(r['cur']) splits = {k: v for k, v in bykey.items() if len(v) > 1} wb2 = openpyxl.Workbook() H = Font(color='FFFFFF', bold=True) F = PatternFill('solid', fgColor='1F4E79') ws = wb2.active ws.title = 'M5244系列统计' ws.append(['货号', '物料名称', 'X档', '组分', 'DG', 'NA', '2026销售额', '当前所在系列', '备注']) for r in m: note = '⚠ 现被归入 MD2104（名称误写为 SensiDirect）' if r['cur'] == 'MD2104' else '' ws.append([r['sku'], r['name'], xfold(r['name']), r['comp'], r['dg'], r['na'], r['amt'], r['cur'], note]) ws.append([]) ws.append(['合计', '', '', '', '', '', round(sum(r['amt'] for r in m), 2), '', '']) ws.append(['资料份数', docs, '', '', '', '', '', '', '']) ws.append(['已具备核心项', '、'.join(sorted(have)) or '无', '', '', '', '', '', '', '']) ws.append(['缺失核心项', '、'.join(miss) or '无', '', '', '', '', '', '', '']) for c in ws[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([24, 56, 7, 7, 6, 6, 13, 14, 40], 1): ws.column_dimensions[get_column_letter(i)].width = w ws2s = wb2.create_sheet('误入清单') ws2s.append(['货号', '物料名称', '现归系列', '该系列主流货号键', '本货号应为', '2026销售额', '判断依据']) for cur, main_key, r in sorted(mis, key=lambda x: -x[2]['amt']): ws2s.append([r['sku'], r['name'], cur, main_key, r['key'], r['amt'], '货号编码 M5244 属 M244（Neoscript Fast RT）；名称写成 SensiDirect RT（ERP 主数据疑似录错）']) for c in ws2s[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([24, 56, 14, 14, 14, 13, 60], 1): ws2s.column_dimensions[get_column_letter(i)].width = w ws3 = wb2.create_sheet('同键拆分待合并') ws3.append(['货号键', '涉及的系列', '系列数', '合计销售额', '建议']) for k, v in sorted(splits.items(), key=lambda x: -sum(r['amt'] for r in recs if r['key'] == x[0])): tot = sum(r['amt'] for r in recs if r['key'] == k) ws3.append([k, ' / '.join(sorted(v)), len(v), round(tot, 2), '按同一个货号键合并为 1 个系列']) for c in ws3[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([10, 46, 8, 14, 34], 1): ws3.column_dimensions[get_column_letter(i)].width = w ws4 = wb2.create_sheet('全部对照') ws4.append(['货号', '物料名称', '名称法系列', '货号法键', '一致', '2026销售额']) for r in sorted(recs, key=lambda x: (x['key'], -x['amt'])): ws4.append([r['sku'], r['name'], r['cur'], r['key'], '是' if r['cur'] == r['key'] else '否', r['amt']]) for c in ws4[1]: c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True) for i, w in enumerate([24, 54, 14, 12, 7, 13], 1): ws4.column_dimensions[get_column_letter(i)].width = w ws4.freeze_panes = 'A2' wb2.save(OUT) print('M5244 系列：%d 个货号 / %.1f 万 ｜ 资料 %d 份 ｜ 缺 %s' % (len(m), sum(r['amt'] for r in m) / 1e4, docs, '、'.join(miss) or '无')) print('误入 %d 条 ｜ 同键被拆 %d 组' % (len(mis), len(splits))) for cur, main_key, r in mis: print(' ⚠ %-18s 在 %-10s → 应为 %s' % (r['sku'], cur, r['key'])) for k, v in sorted(splits.items(), key=lambda x: -sum(r['amt'] for r in recs if r['key'] == x[0])): print(' 合并 %-8s %s' % (k, ' / '.join(sorted(v)))) print('✅', OUT) if __name__ == '__main__': main()