#!/usr/bin/env python3 # -*- coding: utf-8 -*- """天隆集团——按【两个开票抬头】分别统计货号采购额（2025 vs 2026 分季度）。 抬头（取证得出，见下 distinct 客户名）： · 西安华伟 = 客户名含「西安华伟」 → 西安华伟科技有限公司 · 苏州天隆 = 客户名含「苏州天隆」 → 苏州天隆生物科技有限公司 归集口径：集团客户# == '天隆集团'（不猜字符串，先打印 distinct 客户名取证） 数据源：用户表《刘子研三季度数据-截止至9.30.xls》 sheet「2026年销售数据」+ sheet「2025年销售数据」 口径：标准销售订单 · 已审核 · 非赠品 · 排除关联方（宝锐/宝泰/横琴）；季度按【合同日期】； 2026 截止 09-30（Q4 无数据）；金额取「价税合计」。 守恒门禁（写盘前置）：两抬头金额之和 == 该集团总额（2025 / 2026 各一条 assert）。 输出：销售分析/天隆_按抬头_货号统计.xlsx sheet：抬头汇总 / 西安华伟_货号x季度 / 苏州天隆_货号x季度 / 抬头x货号对比 / 明细 """ 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') OUT = os.path.join(ROOT, '销售分析', '天隆_按抬头_货号统计.xlsx') GROUP = '天隆集团' # 集团客户# 归集键（取证：该列含天隆的 distinct 值仅此一个） HEADERS = [('西安华伟', '西安华伟'), ('苏州天隆', '苏州天隆')] # (抬头简称, 客户名匹配子串) KW_TIANLONG = '天隆' # 客户名含天隆（旧口径交叉对照用） NODATA = '（未填货号）' def load(sheet, year): df = pd.read_excel(USR, sheet_name=sheet, header=0, engine='xlrd') df.columns = [str(c).strip() for c in df.columns] df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce') df['_yr'] = df['_d'].dt.year df['_q'] = df['_d'].dt.quarter df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0) hk = '货号#' if '货号#' in df.columns else '货号' df['_sku'] = (df[hk].fillna(NODATA).astype(str).str.strip() .replace({'': NODATA, 'nan': NODATA, 'None': NODATA})) k = (df['_yr'] == year) k &= df['单据类型'].astype(str).str.contains('标准销售订单', na=False) k &= df['单据状态'].astype(str).str.contains('已审核', na=False) k &= ~df['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true']) k &= ~df['客户'].astype(str).str.contains('宝锐|宝泰|横琴', na=False, regex=True) return df[k].copy() def assign_header(names): """把客户名映射到抬头；返回 Series（无匹配 -> NaN）。""" names = names.astype(str) out = pd.Series([None] * len(names), index=names.index, dtype=object) for short, key in HEADERS: out[names.str.contains(key, na=False)] = short return out def qmat(d, tag): """货号 × (Q1..Q4) + 合计，列名如 25Q1.. ; tag 为 '25'/'26'。""" if len(d) == 0: return pd.DataFrame(columns=['%sQ%d' % (tag, q) for q in (1, 2, 3, 4)] + ['%s合计' % tag]) pv = d.groupby(['_sku', '_q']).agg(金额=('_amt', 'sum')).reset_index() m = 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 m.columns: m[q] = 0.0 m = m[[1, 2, 3, 4]] m.columns = ['%sQ%d' % (tag, q) for q in (1, 2, 3, 4)] m['%s合计' % tag] = m.sum(axis=1) return m def main(): d25 = load('2025年销售数据', 2025) d26 = load('2026年销售数据', 2026) all_df = pd.concat([d25.assign(年份=2025), d26.assign(年份=2026)], ignore_index=True) # ── 取证：集团下 distinct 客户名与金额 ── print('══ 取证：集团客户#==%s 下 distinct 客户名 / 金额 ══' % GROUP) grp = all_df[all_df['集团客户#'].astype(str).str.strip() == GROUP].copy() for yr in (2025, 2026): g = grp[grp['年份'] == yr] print(' ── %d 行数 %d 金额 %.2f' % (yr, len(g), g['_amt'].sum())) pv = g.groupby(g['客户'].astype(str)).agg(金额=('_amt', 'sum'), 订单数=('单据编号', 'nunique')) for n, r in pv.sort_values('金额', ascending=False).iterrows(): print(' %-22s 金额 %12.2f 订单 %d' % (n, r['金额'], r['订单数'])) # ── 归属：每行打抬头标签 ── all_df['抬头'] = assign_header(all_df['客户']) grp = all_df[all_df['集团客户#'].astype(str).str.strip() == GROUP].copy() unassigned = grp[grp['抬头'].isna()] print('\n集团行归集：共 %d 行 ｜ 已归抬头 %d 行 ｜ 未归 %d 行' % ( len(grp), grp['抬头'].notna().sum(), len(unassigned))) if len(unassigned): print(' ⚠️ 未归抬头客户名:', unassigned['客户'].astype(str).unique().tolist()) raise SystemExit('存在未归入任何抬头的集团客户行，请先取证再归集。') # ── 抬头汇总 ── rows = [] for short, _ in HEADERS: for yr in (2025, 2026): pass summary = [] for short, _ in HEADERS: sub = grp[grp['抬头'] == short] s25 = sub[sub['年份'] == 2025] s26 = sub[sub['年份'] == 2026] def qsum(s, q): return float(s[s['_q'] == q]['_amt'].sum()) a25, a26 = float(s25['_amt'].sum()), float(s26['_amt'].sum()) summary.append({ '抬头': short, '客户名': ' / '.join(sorted(sub['客户'].astype(str).unique().tolist())), '2025金额': round(a25, 2), '2026金额': round(a26, 2), '同比Δ': round(a26 - a25, 2), '同比%': (round((a26 / a25 - 1) * 100, 1) if a25 else None), '货号数25': int(s25[s25['_amt'] > 0]['_sku'].nunique()), '货号数26': int(s26[s26['_amt'] > 0]['_sku'].nunique()), '订单数25': int(s25['单据编号'].nunique()), '订单数26': int(s26['单据编号'].nunique()), '25Q1': round(qsum(s25, 1), 2), '25Q2': round(qsum(s25, 2), 2), '25Q3': round(qsum(s25, 3), 2), '25Q4': round(qsum(s25, 4), 2), '26Q1': round(qsum(s26, 1), 2), '26Q2': round(qsum(s26, 2), 2), '26Q3': round(qsum(s26, 3), 2), '26Q4': round(qsum(s26, 4), 2), }) # 合计行 tot = {'抬头': '【两抬头合计】', '客户名': ''} money_cols = [c for c in summary[0] if c not in ('抬头', '客户名', '货号数25', '货号数26', '订单数25', '订单数26')] for c in money_cols: tot[c] = round(sum(r[c] for r in summary if isinstance(r[c], (int, float))), 2) # 货号数/订单数合计按集团去重（避免同一货号在两抬头被重复计） tot['货号数25'] = int(grp[(grp['年份'] == 2025) & (grp['_amt'] > 0)]['_sku'].nunique()) tot['货号数26'] = int(grp[(grp['年份'] == 2026) & (grp['_amt'] > 0)]['_sku'].nunique()) tot['订单数25'] = int(grp[grp['年份'] == 2025]['单据编号'].nunique()) tot['订单数26'] = int(grp[grp['年份'] == 2026]['单据编号'].nunique()) tot['同比%'] = (round((tot['2026金额'] / tot['2025金额'] - 1) * 100, 1) if tot.get('2025金额') else None) summary_df = pd.DataFrame(summary + [tot], columns=['抬头', '客户名', '2025金额', '2026金额', '同比Δ', '同比%', '货号数25', '货号数26', '订单数25', '订单数26', '25Q1', '25Q2', '25Q3', '25Q4', '26Q1', '26Q2', '26Q3', '26Q4']) # ── 守恒门禁 ── print('\n══ 守恒门禁（两抬头之和 vs 集团总额）══') gate = {} for yr, tag in ((2025, '25'), (2026, '26')): g = grp[grp['年份'] == yr] grp_total = float(g['_amt'].sum()) parts = {} for short, _ in HEADERS: parts[short] = float(g[g['抬头'] == short]['_amt'].sum()) s = sum(parts.values()) print(' %d: 集团总额 %.2f ｜ %s' % ( yr, grp_total, ' + '.join('%s %.2f' % (k, v) for k, v in parts.items()))) assert abs(s - grp_total) < 0.01, '守恒失败 %d: 两抬头 %.2f != 集团 %.2f' % (yr, s, grp_total) print(' 差额 %.6f ✅ 守恒通过' % (s - grp_total)) gate[yr] = (grp_total, s) # 2026 与集团归集口径（客户名含天隆 的旧口径）交叉对照 tl26 = grp[grp['年份'] == 2026] kw26 = float(tl26[tl26['客户'].astype(str).str.contains(KW_TIANLONG, na=False)]['_amt'].sum()) print(' [交叉] 2026 集团归集总额 %.2f ｜ 客户名含「天隆」 %.2f ｜ 差 %.2f' % ( gate[2026][0], kw26, gate[2026][0] - kw26)) # ── 各抬头 货号×季度 矩阵 ── mats = {} for short, _ in HEADERS: sub = grp[grp['抬头'] == short] s25 = sub[sub['年份'] == 2025] s26 = sub[sub['年份'] == 2026] m = qmat(s25, '25').join(qmat(s26, '26'), how='outer').fillna(0.0) m['两年合计'] = m['25合计'] + m['26合计'] m['同比Δ'] = m['26合计'] - m['25合计'] m['同比%'] = (m['同比Δ'] / m['25合计'].replace(0, float('nan')) * 100).round(1) m = m.sort_values('26合计', ascending=False) # 剔除两年合计均为 0 的货号（零价行：单价空、数量>0、价税合计=0，多为未标价的样品/免单行） zero_sku = m[m['两年合计'] <= 0] m = m[m['两年合计'] > 0] if len(zero_sku): print(' [注] %s 剔除 %d 个零金额货号（价税合计=0，仍保留在明细）' % (short, len(zero_sku))) mats[short] = m # Top5 打印 print('\n══ %s · Top5 货号（含季度分布）══' % short) hdr = ' %-24s %8s %8s %8s %8s │ %8s %8s %8s %8s │ %9s %9s' % ( '货号', '25Q1', '25Q2', '25Q3', '25Q4', '26Q1', '26Q2', '26Q3', '26Q4', '25合计', '26合计') print(hdr) for sku, r in m.head(5).iterrows(): print(' %-24s %8.0f %8.0f %8.0f %8.0f │ %8.0f %8.0f %8.0f %8.0f │ %9.0f %9.0f' % ( str(sku)[:24], r['25Q1'], r['25Q2'], r['25Q3'], r['25Q4'], r['26Q1'], r['26Q2'], r['26Q3'], r['26Q4'], r['25合计'], r['26合计'])) print(' %-24s %8.0f %8.0f %8.0f %8.0f │ %8.0f %8.0f %8.0f %8.0f │ %9.0f %9.0f' % ( '【合计】', m['25Q1'].sum(), m['25Q2'].sum(), m['25Q3'].sum(), m['25Q4'].sum(), m['26Q1'].sum(), m['26Q2'].sum(), m['26Q3'].sum(), m['26Q4'].sum(), m['25合计'].sum(), m['26合计'].sum())) # ── 抬头×货号对比 ── xw, sz = mats['西安华伟'], mats['苏州天隆'] cmp = pd.DataFrame(index=sorted(set(xw.index) | set(sz.index))) cmp['西安华伟_2025'] = xw['25合计'].reindex(cmp.index).fillna(0.0) cmp['西安华伟_2026'] = xw['26合计'].reindex(cmp.index).fillna(0.0) cmp['西安华伟_两年合计'] = cmp['西安华伟_2025'] + cmp['西安华伟_2026'] cmp['苏州天隆_2025'] = sz['25合计'].reindex(cmp.index).fillna(0.0) cmp['苏州天隆_2026'] = sz['26合计'].reindex(cmp.index).fillna(0.0) cmp['苏州天隆_两年合计'] = cmp['苏州天隆_2025'] + cmp['苏州天隆_2026'] cmp['两抬头_两年合计'] = cmp['西安华伟_两年合计'] + cmp['苏州天隆_两年合计'] cmp = cmp.sort_values('两抬头_两年合计', ascending=False) cmp.index.name = '货号' both_cnt = int(((cmp['西安华伟_两年合计'] > 0) & (cmp['苏州天隆_两年合计'] > 0)).sum()) x_only = int(((cmp['西安华伟_两年合计'] > 0) & (cmp['苏州天隆_两年合计'] == 0)).sum()) s_only = int(((cmp['苏州天隆_两年合计'] > 0) & (cmp['西安华伟_两年合计'] == 0)).sum()) print('\n══ 两抬头货号分工 ══') print(' 两抬头都出现: %d 个 ｜ 仅西安华伟: %d 个 ｜ 仅苏州天隆: %d 个 ｜ 货号并集: %d' % ( both_cnt, x_only, s_only, len(cmp))) # ── 明细 ── det = grp[['抬头', '客户', '年份', '_d', '单据编号', '_sku', '物料名称', '销售数量', '_amt', '出货批号#', '销售员']].copy() det = det.rename(columns={'_d': '合同日期', '_sku': '货号', '_amt': '价税合计'}) det = det.sort_values(['抬头', '年份', '合同日期', '单据编号']) # ── 写盘 ── os.makedirs(os.path.dirname(OUT), exist_ok=True) with pd.ExcelWriter(OUT, engine='openpyxl') as w: summary_df.to_excel(w, sheet_name='抬头汇总', index=False) mats['西安华伟'].reset_index().rename(columns={'_sku': '货号'}).to_excel(w, sheet_name='西安华伟_货号x季度', index=False) mats['苏州天隆'].reset_index().rename(columns={'_sku': '货号'}).to_excel(w, sheet_name='苏州天隆_货号x季度', index=False) cmp.reset_index().to_excel(w, sheet_name='抬头x货号对比', index=False) det.to_excel(w, sheet_name='明细', index=False) print('\n→ %s' % OUT) if __name__ == '__main__': main()