#!/usr/bin/env python3 # -*- coding: utf-8 -*- """天隆集团 2025 vs 2026 分季度、分货号 采购波动与同比趋势。 数据源：用户表《刘子研三季度数据-截止至9.30.xls》 sheet「2026年销售数据」 + sheet「2025年销售数据」（同一文件，归属键一致） 口径：标准销售订单 · 已审核 · 非赠品 · 排除关联方；季度按【合同日期】切 2026 侧另与全公司 ERP 导出交叉核对金额 输出：销售分析/天隆集团_2025vs2026_货号分季度同比.xlsx """ import os import pandas as pd ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类' USR = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents/doc_6f16ee249d8e_刘子研三季度数据-截止至9.30(1).xls') ERP = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20260930.xlsx') OUT = os.path.join(ROOT, '销售分析', '天隆集团_2025vs2026_货号分季度同比.xlsx') KW = '天隆' def prep(df, year): df = df.copy() df.columns = [str(c).strip() for c in df.columns] df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce') df['_q'] = df['_d'].dt.quarter df['_yr'] = df['_d'].dt.year df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0) hk = '货号#' if '货号#' in df.columns else '货号' df['_sku'] = df[hk].fillna('（未填货号）').astype(str).str.strip().replace({'': '（未填货号）', 'nan': '（未填货号）'}) k = df['_yr'] == year k = k & df['单据类型'].astype(str).str.contains('标准销售订单', na=False) k = k & df['单据状态'].astype(str).str.contains('已审核', na=False) k = k & ~df['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true']) k = k & ~df['客户'].astype(str).str.contains('宝锐|宝泰|横琴', na=False, regex=True) d = df[k].copy() m = d['客户'].astype(str).str.contains(KW, na=False) for c in ('集团客户#', '集团客户'): if c in d.columns: m = m | d[c].astype(str).str.contains(KW, na=False) return d[m].copy() def qmat(d): pv = (d.groupby(['_sku', '_q']).agg(金额=('_amt', 'sum')).reset_index()) mat = pv.pivot_table(index='_sku', columns='_q', values='金额', aggfunc='sum', fill_value=0.0) for q in (1, 2, 3, 4): if q not in mat.columns: mat[q] = 0.0 mat = mat[[1, 2, 3, 4]] mat.columns = ['%sQ%d' % (str(d['_yr'].iloc[0])[-2:], q) for q in (1, 2, 3, 4)] mat['%s合计' % str(d['_yr'].iloc[0])[-2:]] = mat.sum(axis=1) return mat def main(): x26 = pd.read_excel(USR, sheet_name='2026年销售数据', header=0, engine='xlrd') d26 = prep(x26, 2026) d25 = prep(pd.read_excel(USR, sheet_name='2025年销售数据', header=0, engine='xlrd'), 2025) print('天隆 24/25源: 2025 %d 行 / ¥%.2f ｜ 2026 %d 行 / ¥%.2f' % (len(d25), d25['_amt'].sum(), len(d26), d26['_amt'].sum())) # 交叉核对 2026（全公司 ERP） try: e = pd.read_excel(ERP, sheet_name='Sheet1', header=0) e.columns = [str(c).strip() for c in e.columns] e['_d'] = pd.to_datetime(e['合同日期'], errors='coerce') e['_amt'] = pd.to_numeric(e['价税合计'], errors='coerce').fillna(0) k = (e['_d'].dt.year == 2026) & e['单据类型'].astype(str).str.contains('标准销售订单', na=False) k = k & e['单据状态'].astype(str).str.contains('已审核', na=False) k = k & ~e['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true']) k = k & ~e['客户'].astype(str).str.contains('宝锐|宝泰|横琴', na=False, regex=True) k = k & e['客户'].astype(str).str.contains(KW, na=False) ee = e[k] print(' 交叉核对(全公司ERP 2026 天隆): %d 行 / ¥%.2f ｜ 差额 %.2f' % (len(ee), ee['_amt'].sum(), ee['_amt'].sum() - d26['_amt'].sum())) except Exception as ex: print(' ⚠️ ERP 交叉核对跳过: %s' % str(ex)[:60]) m25, m26 = qmat(d25), qmat(d26) both = m25.join(m26, how='outer').fillna(0.0) y25, y26 = '25合计', '26合计' both['同比Δ'] = both[y26] - both[y25] both['同比%'] = (both['同比Δ'] / both[y25].replace(0, float('nan')) * 100).round(1) qc = [c for c in both.columns if c.endswith('Q1') or c.endswith('Q2') or c.endswith('Q3') or c.endswith('Q4')] both['26波动CV%'] = (both[[c for c in qc if c.startswith('26')]].std(axis=1) / both[[c for c in qc if c.startswith('26')]].mean(axis=1).replace(0, float('nan')) * 100).round(0) both = both.sort_values(y26, ascending=False) print('\n══ 天隆集团 分季度分货号（¥）══') hdr = '%-26s %9s %9s %9s %9s │ %9s %9s %9s %9s │ %10s %8s %6s' % ( '货号', '25Q1', '25Q2', '25Q3', '25Q4', '26Q1', '26Q2', '26Q3', '26Q4', '26年合计', '同比', 'CV%') print(hdr); print('─' * len(hdr)) for sku, r in both.head(15).iterrows(): print('%-26s %9.0f %9.0f %9.0f %9.0f │ %9.0f %9.0f %9.0f %9.0f │ %10.0f %+7.0f%% %5s' % ( str(sku)[:26], r['25Q1'], r['25Q2'], r['25Q3'], r['25Q4'], r['26Q1'], r['26Q2'], r['26Q3'], r['26Q4'], r[y26], r['同比Δ'], ('%.0f' % r['26波动CV%']) if pd.notna(r['26波动CV%']) else '—')) print('─' * len(hdr)) print('%-26s %9.0f %9.0f %9.0f %9.0f │ %9.0f %9.0f %9.0f %9.0f │ %10.0f %+7.0f%%' % ( '【季度合计】', both['25Q1'].sum(), both['25Q2'].sum(), both['25Q3'].sum(), both['25Q4'].sum(), both['26Q1'].sum(), both['26Q2'].sum(), both['26Q3'].sum(), both['26Q4'].sum(), both[y26].sum(), both['同比Δ'].sum())) print('\n货号数: 2025 %d ｜ 2026 %d ｜ 两年都有 %d' % ((m25[y25] > 0).sum(), (m26[y26] > 0).sum(), ((m25[y25] > 0) & (m26[y26] > 0)).sum())) print('仅2025有(流失): %s' % ', '.join(both[(both[y25] > 0) & (both[y26] == 0)].index.tolist()[:8])) print('仅2026有(新增): %s' % ', '.join(both[(both[y26] > 0) & (both[y25] == 0)].index.tolist()[:8])) os.makedirs(os.path.dirname(OUT), exist_ok=True) # 业务注解（用户补充，原话逐字入档；仅文字，不改任何金额） annot = pd.DataFrame([ {'货号/关键词': 'PFM4211*', '注解类型': '商机未延续（海外机会性商机）', '业务说明（用户原话）': 'PFM4211 的是用于 HPV 海外商机，2025 年的机会性商机，' '在 2026 年未能得到延续，主要是客户市场开拓原因', '数据印证': 'PFM4211-3-TL1-S20 2025 = 1,098,000（占西安华伟 66.9%）→ 2026 = 0', '涉及季度': '2025 全年 → 2026 全年'}, {'货号/关键词': 'M5244-ZJ4 / M5244-ZJ4-TP', '注解类型': '稳定性客诉·品种替换', '业务说明（用户原话）': 'M5244-ZJ4 因为稳定性客诉问题，在 2026 年 7 月开始采购受到影响，' '在 8 月底我们用 MD2104-003006 完成替换，因此影响了 Q3 的采购', '数据印证': 'M5244-ZJ4-TP 2025 = 544,800 → 2026 = 94,500；' '替品 MD2104-003006-DB 仅 26Q3 出现 27,760 元（与“8月底完成替换”吻合）', '涉及季度': '26Q2–Q3'}, {'货号/关键词': 'M4211-3', '注解类型': '势头好（HBV 超敏项目）', '业务说明（用户原话）': 'M4211-3 是客户的 HBV 超敏项目，客户反馈今年销售势头非常好', '数据印证': 'M4211-3 2025 = 165,000 → 2026 = 423,300（+156%）', '涉及季度': '26Q1–Q3'}, ], columns=['货号/关键词', '注解类型', '业务说明（用户原话）', '数据印证', '涉及季度']) with pd.ExcelWriter(OUT) as w: both.reset_index().rename(columns={'_sku': '货号'}).to_excel(w, sheet_name='货号x季度x同比', index=False) pd.DataFrame([{'指标': '2025 合计', '值': round(d25['_amt'].sum(), 2)}, {'指标': '2026 合计', '值': round(d26['_amt'].sum(), 2)}, {'指标': '同比差额', '值': round(d26['_amt'].sum() - d25['_amt'].sum(), 2)}, {'指标': '同比%', '值': '%.1f%%' % ((d26['_amt'].sum() / d25['_amt'].sum() - 1) * 100) if d25['_amt'].sum() else '—'}, {'指标': '口径', '值': '标准销售订单·已审核·非赠品·排除关联方；季度按合同日期'}]).to_excel(w, sheet_name='总览', index=False) pd.concat([d25.assign(年份=2025), d26.assign(年份=2026)])[ ['年份', '_d', '单据编号', '客户', '_sku', '物料名称', '销售数量', '含税单价', '_amt', '出货批号#', '销售员'] ].rename(columns={'_d': '合同日期', '_sku': '货号', '_amt': '价税合计'}).sort_values(['年份', '合同日期']) \ .to_excel(w, sheet_name='明细25+26', index=False) annot.to_excel(w, sheet_name='业务注解', index=False) print('\n→ %s' % OUT) if __name__ == '__main__': main()