# -*- coding: utf-8 -*- """Build V02 of the 2027 diagnostic raw-material sales forecast. Input : 2027诊断原料销售拓展部+大客户部预估-V01.xlsx Output: 2027..._V02_概率加权版.xlsx V02 changes: - Add 成交概率 / 加权金额 / 问题标记 / 计入汇总 columns to every salesperson sheet. - Normalize 2027 year, department names, and opportunity type. - K (annual total) becomes =SUM(Q1:Q4), not a hand-typed number. - Rebuild the summary from all 13 salespeople; V01 omitted 赵云浩 and 刘子研. - Add problem/improvement and methodology sheets. """ import os import re import json import collections from datetime import date import openpyxl import xlsxwriter from xlsxwriter.utility import xl_rowcol_to_cell SRC = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/native-inputs/aaa87cddd00a019da5bc16576231cf55bae3e220ec734803c3ae8de8ea039d5f/doc_fad028c9b5c0_2027诊断原料销售拓展部+大客户部预估-V01.xlsx' OUT_DIR = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/2027销售预测_V02' OUT = os.path.join(OUT_DIR, '2027诊断原料销售拓展部_大客户部预估_V02_概率加权版.xlsx') NAVY = '#0F3D6E' TEAL = '#0E9F9A' LIGHT_BLUE = '#EAF3FB' PALE_BLUE = '#F6F9FD' PALE_YELLOW = '#FFF2CC' GRAY = '#64748B' BORDER = '#B7C9DD' GREEN = '#E2EFDA' AMBER = '#FCE4D6' RED = '#F8CBAD' BLUE = '#DDEBF7' WHITE = '#FFFFFF' PROB = {'存量商机': 0.9, '突破性商机': 0.3, '探索性商机': 0.1, '待归类': 0.0} TARGET_2026 = 5000.0 # 万元 TARGET_2027 = 6500.0 # 万元 def load_records(): wb = openpyxl.load_workbook(SRC, data_only=False) wbv = openpyxl.load_workbook(SRC, data_only=True) people = wb.sheetnames[1:] records = [] def merged_map(ws): m = {} for rng in ws.merged_cells.ranges: top = ws.cell(rng.min_row, rng.min_col).value for r in range(rng.min_row, rng.max_row + 1): for c in range(rng.min_col, rng.max_col + 1): m[(r, c)] = top return m for name in people: ws = wb[name] wv = wbv[name] mm = merged_map(ws) mmv = merged_map(wv) def cv(sheet, mp, r, c): v = sheet.cell(r, c).value if v is None and (r, c) in mp: v = mp[(r, c)] return v for r in range(2, ws.max_row + 1): actual_A = ws.cell(r, 1).value if actual_A is None: # Continuation row (merged A:...): append its resource note to the previous opportunity. if records and records[-1]['person'] == name: extra = ws.cell(r, 9).value if extra not in (None, ''): records[-1]['resource'] = (records[-1]['resource'] or '') + '\n' + str(extra) continue A = cv(ws, mm, r, 1) if isinstance(A, str) and '商机类型补充说明' in A: break vals = [cv(ws, mm, r, c) for c in range(1, 11)] k = cv(wv, mmv, r, 11) q = [cv(wv, mmv, r, c) for c in range(12, 16)] if not any(v not in (None, '') for v in vals + [k] + q): continue if not (A in (2026, 2027, '2026', '2027') or vals[1] or vals[4] or k): continue def num(x): if isinstance(x, (int, float)): return float(x) if isinstance(x, str): x = x.strip().replace(',', '') try: return float(x) except Exception: return 0.0 return 0.0 qn = [num(x) for x in q] records.append({ 'person': name, 'row': r, 'year_raw': A, 'sales_raw': vals[1], 'dept_raw': vals[2], 'customer': vals[3], 'opp': vals[4], 'sku': vals[5], 'oid': vals[6], 'type_raw': vals[7], 'resource': vals[8], 'line': vals[9], 'K_raw': num(k), 'quarters': qn, 'sumQ': sum(qn), }) return people, records def normalize(records): # duplicate IDs (excluding placeholders) ids = collections.Counter( str(x['oid']).strip() for x in records if str(x['oid']).strip() not in ('', '/', '暂无', '飞书商机ID', 'None') ) out = [] for x in records: raw_type = str(x['type_raw'] or '').strip() raw_sales = str(x['sales_raw'] or '') raw_customer = str(x['customer'] or '') raw_opp = str(x['opp'] or '') sample = any(('XXX' in s or '示例' in s or 'xxx' in s.lower()) for s in (raw_sales, raw_customer, raw_opp)) notes = [] # year year = 2027 if str(x['year_raw']).strip() == '2026': notes.append('原表年份2026，已按2027预测修正') # department dept_raw = str(x['dept_raw'] or '') dept = '诊断原料大客户销售部' if '大客户' in dept_raw else '诊断原料销售拓展部' if dept_raw and dept_raw != dept: notes.append(f'部门名由“{dept_raw}”归一') # type if sample: typ = '待归类' include = False notes.append('样例行，不计入汇总') elif raw_type == '存量商机/突破性商机': typ = '突破性商机' include = True notes.append('原表为存量/突破混合，暂按突破性30%') elif raw_type in ('存量商机', '突破性商机', '探索性商机'): typ = raw_type include = True else: typ = '待归类' include = False notes.append('商机类型缺失，暂按0%且不计入') # K vs quarters if abs(x['K_raw'] - x['sumQ']) > 1: notes.append(f'K原值{x["K_raw"]:,.0f}，按季度合计{x["sumQ"]:,.0f}修正') if x['K_raw'] == 0 and x['sumQ'] > 0: notes.append('K原为空，按季度合计补全') # missing fields if not str(x['sku'] or '').strip(): notes.append('缺关联货号') if not str(x['oid'] or '').strip(): notes.append('缺商机ID') if not str(x['opp'] or '').strip(): notes.append('缺商机名称') oid = str(x['oid'] or '').strip() if oid and oid not in ('/', '暂无', '飞书商机ID') and ids.get(oid, 0) > 1: notes.append(f'商机ID重复×{ids[oid]}') line = str(x['line'] or '').strip() if line and line not in ('诊断原料/PCR', '诊断原料/NGS'): notes.append(f'业务线为“{line}”，请确认') if not line: notes.append('缺业务线') k = float(x['sumQ']) p = PROB.get(typ, 0.0) q = k * p out.append({ **x, 'year': year, 'dept': dept, 'type': typ, 'include': include, 'prob': p, 'weighted': q, 'note': '；'.join(dict.fromkeys(notes)), 'sample': sample, }) return out def fnum(x, d=2): return f'{x:,.{d}f}' def main(): people, raw_records = load_records() records = normalize(raw_records) # Keep original sheet order for per-person sheets; summary sorted by raw. by_person = collections.OrderedDict((p, []) for p in people) for r in records: by_person.setdefault(r['person'], []).append(r) included = [r for r in records if r['include']] raw_total = sum(r['sumQ'] for r in included) / 10000.0 weighted_total = sum(r['weighted'] for r in included) / 10000.0 ratio = weighted_total / raw_total if raw_total else 0 gap_raw = TARGET_2027 - raw_total gap_weighted = TARGET_2027 - weighted_total # category stats (normalized) cat_defs = [ ('存量商机', '存量商机'), ('突破性商机', '突破性商机（含原表混合）'), ('探索性商机', '探索性商机'), ('待归类', '待归类/不计入'), ] cat_stats = [] for key, label in cat_defs: rows = [r for r in included if r['type'] == key] cat_stats.append({ 'key': key, 'label': label, 'count': len(rows), 'raw': sum(r['sumQ'] for r in rows) / 10000.0, 'weight': PROB[key], 'weighted': sum(r['weighted'] for r in rows) / 10000.0, }) # department stats dept_stats = [] for d in ('诊断原料销售拓展部', '诊断原料大客户销售部'): rows = [r for r in included if r['dept'] == d] dept_stats.append({ 'dept': d, 'count': len(rows), 'raw': sum(r['sumQ'] for r in rows) / 10000.0, 'weighted': sum(r['weighted'] for r in rows) / 10000.0, }) # per-person summary stock_share = cat_stats[0]['raw'] / raw_total if raw_total else 0 break_share = cat_stats[1]['raw'] / raw_total if raw_total else 0 explore_share = cat_stats[2]['raw'] / raw_total if raw_total else 0 w_stock_share = cat_stats[0]['weighted'] / weighted_total if weighted_total else 0 w_break_share = cat_stats[1]['weighted'] / weighted_total if weighted_total else 0 w_explore_share = cat_stats[2]['weighted'] / weighted_total if weighted_total else 0 person_rows = [] person_notes = { '冯贤平': 'K列1条按季度合计修正（+0.5万）', '刘虹晶': '2条样例已排除；K列1条修正（+15.0万）', '宋学伟': 'K列2条修正（-19.0万）', '杨天雄': '—', '路要峰': 'K列1条修正（+10.0万）', '张文蝶': 'K列3条修正（-2.5万）', '徐卓凡': 'K列1条修正（+10.0万）', '黄文才': '—', '陈溪子': '—', '林潇': '—', '李江辉': '36条年份2026→2027；8条缺商机名/金额待补；K列1条修正（-5.0万）', '刘子研': 'V01汇总原漏，V02补入', '赵云浩': '4条合并续行已合并；混合类型按突破30%；部门归一；V01汇总原漏', } for p in people: rows = [r for r in included if r['person'] == p] raw = sum(r['sumQ'] for r in rows) / 10000.0 w = sum(r['weighted'] for r in rows) / 10000.0 person_rows.append({ 'person': p, 'dept': rows[0]['dept'] if rows else ('诊断原料大客户销售部' if p in ('刘子研', '赵云浩') else '诊断原料销售拓展部'), 'count': len(rows), 'raw_stock': sum(r['sumQ'] for r in rows if r['type'] == '存量商机') / 10000.0, 'raw_break': sum(r['sumQ'] for r in rows if r['type'] == '突破性商机') / 10000.0, 'raw_explore': sum(r['sumQ'] for r in rows if r['type'] == '探索性商机') / 10000.0, 'raw_unknown': 0.0, 'raw': raw, 'w_stock': sum(r['weighted'] for r in rows if r['type'] == '存量商机') / 10000.0, 'w_break': sum(r['weighted'] for r in rows if r['type'] == '突破性商机') / 10000.0, 'w_explore': sum(r['weighted'] for r in rows if r['type'] == '探索性商机') / 10000.0, 'weighted': w, 'note': person_notes.get(p, '—'), }) person_rows.sort(key=lambda x: -x['raw']) # problem stats n_all = len(records) n_valid = len([r for r in records if not r['sample']]) n_included = len(included) n_excluded = n_all - n_included n_sample = len([r for r in records if r['sample']]) sample_raw = sum(r['sumQ'] for r in records if r['sample']) / 10000.0 n_mixed = len([r for r in records if str(r['type_raw']).strip() == '存量商机/突破性商机']) mixed_raw = sum(r['sumQ'] for r in records if str(r['type_raw']).strip() == '存量商机/突破性商机') / 10000.0 n_blank_type = len([r for r in records if not str(r['type_raw'] or '').strip()]) n_k_diff = len([r for r in records if abs(r['K_raw'] - r['sumQ']) > 1]) k_diff = (raw_total - sum(r['K_raw'] for r in included) / 10000.0) n_no_sku = len([r for r in included if not str(r['sku'] or '').strip()]) no_sku_raw = sum(r['sumQ'] for r in included if not str(r['sku'] or '').strip()) / 10000.0 n_no_id = len([r for r in included if not str(r['oid'] or '').strip()]) no_id_raw = sum(r['sumQ'] for r in included if not str(r['oid'] or '').strip()) / 10000.0 n_no_opp = len([r for r in records if not str(r['opp'] or '').strip()]) n_year_2026 = len([r for r in records if str(r['year_raw']).strip() == '2026']) raw_2026 = sum(r['sumQ'] for r in records if str(r['year_raw']).strip() == '2026') / 10000.0 k_2026 = sum(r['K_raw'] for r in records if str(r['year_raw']).strip() == '2026') / 10000.0 ids = collections.Counter( str(r['oid']).strip() for r in records if str(r['oid']).strip() not in ('', '/', '暂无', '飞书商机ID', 'None') ) dup_ids = [(k, v) for k, v in ids.items() if v > 1] n_dup_ids = len(dup_ids) n_dept_bad = len([r for r in records if str(r['dept_raw'] or '').strip() == '大客户']) n_line_other = len([r for r in included if str(r['line'] or '').strip() not in ('诊断原料/PCR', '诊断原料/NGS', '')]) # V01 summary total from Sheet1 wb0 = openpyxl.load_workbook(SRC, data_only=True) ws0 = wb0['Sheet1'] v01_summary_total = 0.0 for r in range(2, 16): v = ws0.cell(r, 5).value if isinstance(v, (int, float)): v01_summary_total += v / 10000.0 omitted = ['赵云浩', '刘子研'] omitted_amount = sum( sum(r['sumQ'] for r in included if r['person'] == p) / 10000.0 for p in omitted ) # Create workbook os.makedirs(OUT_DIR, exist_ok=True) wb = xlsxwriter.Workbook(OUT, {'nan_inf_to_errors': True}) wb.set_properties({ 'title': '2027 诊断原料销售预测 V02（概率加权版）', 'subject': '销售拓展部 / 大客户部 2027 年商机预测', 'author': '宝锐生物 营销中心', 'company': '珠海宝锐生物科技有限公司', 'comments': 'V02：增加成交概率与加权金额，补全13人汇总，附问题与改善要求。', }) # Formats def F(**kw): base = {'font_name': '宋体', 'font_size': 9, 'border': 1, 'border_color': BORDER} base.update(kw) return wb.add_format(base) fmt_title = F(font_size=18, bold=True, font_color=WHITE, bg_color=NAVY, align='left', valign='vcenter') fmt_sub = F(font_size=9, bold=True, font_color=WHITE, bg_color=TEAL, align='left', valign='vcenter') fmt_section = F(font_size=11, bold=True, font_color=WHITE, bg_color=NAVY, align='left', valign='vcenter') fmt_header = F(font_size=8.2, bold=True, font_color=WHITE, bg_color=NAVY, align='center', valign='vcenter', text_wrap=True) fmt_text = F(font_size=8.6, align='left', valign='top', text_wrap=True) fmt_text_alt = F(font_size=8.6, align='left', valign='top', text_wrap=True, bg_color=PALE_BLUE) fmt_center = F(font_size=8.8, align='center', valign='vcenter', text_wrap=True) fmt_center_alt = F(font_size=8.8, align='center', valign='vcenter', text_wrap=True, bg_color=PALE_BLUE) fmt_code = F(font_size=8.2, font_name='Times New Roman', align='left', valign='top', text_wrap=True) fmt_code_alt = F(font_size=8.2, font_name='Times New Roman', align='left', valign='top', text_wrap=True, bg_color=PALE_BLUE) fmt_num = F(font_size=8.8, num_format='#,##0', align='right', valign='vcenter') fmt_num_alt = F(font_size=8.8, num_format='#,##0', align='right', valign='vcenter', bg_color=PALE_BLUE) fmt_pct = F(font_size=8.8, num_format='0%', align='center', valign='vcenter') fmt_pct_alt = F(font_size=8.8, num_format='0%', align='center', valign='vcenter', bg_color=PALE_BLUE) fmt_note = F(font_size=7.8, align='left', valign='top', text_wrap=True, font_color=GRAY) fmt_note_alt = F(font_size=7.8, align='left', valign='top', text_wrap=True, font_color=GRAY, bg_color=PALE_BLUE) fmt_input = F(font_size=8.8, align='center', valign='vcenter', text_wrap=True, bg_color=PALE_YELLOW) fmt_formula = F(font_size=8.8, num_format='#,##0', align='right', valign='vcenter', bg_color=LIGHT_BLUE) fmt_formula_pct = F(font_size=8.8, num_format='0%', align='center', valign='vcenter', bg_color=LIGHT_BLUE) fmt_total = F(font_size=8.8, bold=True, bg_color=LIGHT_BLUE, align='right', valign='vcenter', num_format='#,##0') fmt_total_center = F(font_size=8.8, bold=True, bg_color=LIGHT_BLUE, align='center', valign='vcenter') fmt_total_text = F(font_size=8.8, bold=True, bg_color=LIGHT_BLUE, align='left', valign='vcenter') fmt_meta_title = F(font_size=9, bold=True, font_color=NAVY, bg_color=LIGHT_BLUE, align='left', valign='vcenter') fmt_meta_label = F(font_size=8, bold=True, font_color=NAVY, bg_color=LIGHT_BLUE, align='right', valign='vcenter') fmt_meta_num = F(font_size=9, bold=True, font_color=NAVY, bg_color=WHITE, align='center', valign='vcenter', num_format='#,##0') fmt_meta_money = F(font_size=9, bold=True, font_color=NAVY, bg_color=WHITE, align='right', valign='vcenter', num_format='#,##0') fmt_meta_note = F(font_size=7.5, font_color=GRAY, bg_color=LIGHT_BLUE, align='left', valign='vcenter', text_wrap=True) fmt_kpi_label = F(font_size=9, bold=True, font_color=NAVY, bg_color=LIGHT_BLUE, align='left', valign='vcenter', text_wrap=True) fmt_kpi_value = F(font_size=16, bold=True, font_color=NAVY, bg_color=WHITE, align='center', valign='vcenter', num_format='#,##0.00" 万"') fmt_kpi_pct = F(font_size=16, bold=True, font_color=NAVY, bg_color=WHITE, align='center', valign='vcenter', num_format='0.00%') fmt_kpi_note = F(font_size=7.8, font_color=GRAY, bg_color=LIGHT_BLUE, align='left', valign='vcenter', text_wrap=True) fmt_prob_green = F(font_size=8.8, num_format='0%', align='center', valign='vcenter', bg_color=GREEN) fmt_prob_amber = F(font_size=8.8, num_format='0%', align='center', valign='vcenter', bg_color=AMBER) fmt_prob_red = F(font_size=8.8, num_format='0%', align='center', valign='vcenter', bg_color=RED) fmt_prob_blue = F(font_size=8.8, num_format='0%', align='center', valign='vcenter', bg_color=BLUE) fmt_gray = F(font_size=8.6, align='left', valign='top', text_wrap=True, font_color=GRAY, bg_color='#F2F2F2') fmt_gray_center = F(font_size=8.6, align='center', valign='vcenter', text_wrap=True, font_color=GRAY, bg_color='#F2F2F2') # ------------------------------------------------------------------ # 00_汇总 # ------------------------------------------------------------------ ws = wb.add_worksheet('00_汇总') ws.set_tab_color(NAVY) ws.hide_gridlines(2) ws.set_zoom(85) ws.set_column('A:A', 5) ws.set_column('B:B', 18) ws.set_column('C:C', 10) ws.set_column('D:D', 9) ws.set_column('E:M', 12) ws.set_column('N:N', 9) ws.set_column('O:O', 38) ws.set_row(0, 36) ws.merge_range('A1:O1', '2027 年诊断原料销售预测 · 汇总', fmt_title) ws.merge_range('A2:O2', '销售拓展部 / 大客户部 ｜ 版本 V02 ｜ 2026-11-05 ｜ 目标 6,500 万元', fmt_sub) # KPI cards: 2 rows x 4 cards ws.set_row(3, 30) ws.set_row(4, 30) cards = [ (0, 1, '2026 预计达成', 2, fmt_kpi_value, TARGET_2026, '#,##0.00" 万"'), (3, 4, '2027 目标', 5, fmt_kpi_value, TARGET_2027, '#,##0.00" 万"'), (6, 7, '原报合计（万元）', 8, fmt_kpi_value, raw_total, '#,##0.00" 万"'), (9, 10, '概率加权（万元）', 11, fmt_kpi_value, weighted_total, '#,##0.00" 万"'), ] for c1, c2, label, vcol, vfmt, val, _ in cards: ws.merge_range(3, c1, 3, c2, label, fmt_kpi_label) ws.write_number(3, vcol, val, vfmt) ws.merge_range('M4:O4', '目标为销售收入，不是加权金额；加权用于衡量商机质量与风险。', fmt_kpi_note) cards2 = [ (0, 1, '原报缺口（万元）', 2, fmt_kpi_value, gap_raw, '#,##0.00" 万"'), (3, 4, '加权缺口（万元）', 5, fmt_kpi_value, gap_weighted, '#,##0.00" 万"'), (6, 7, '原报覆盖率', 8, fmt_kpi_pct, raw_total / TARGET_2027, '0.00%'), (9, 10, '加权覆盖率', 11, fmt_kpi_pct, weighted_total / TARGET_2027, '0.00%'), ] for c1, c2, label, vcol, vfmt, val, _ in cards2: ws.merge_range(4, c1, 4, c2, label, fmt_kpi_label) if vfmt is fmt_kpi_pct: ws.write_number(4, vcol, val, vfmt) else: ws.write_number(4, vcol, val, vfmt) ws.merge_range('M5:O5', f'加权/原报 = {ratio*100:.2f}%；补齐原报缺口后，按当前结构加权约 {TARGET_2027*ratio:,.2f} 万。', fmt_kpi_note) # Section 1: by salesperson r = 7 ws.merge_range(r, 0, r, 14, '一、按销售汇总（万元）', fmt_section) ws.set_row(r, 24) header_row = r + 1 headers = ['序号', '部门', '销售', '纳入商机数', '存量原报', '突破原报', '探索原报', '待归类', '原报合计', '存量加权', '突破加权', '探索加权', '加权合计', '加权/原报', '备注'] ws.write_row(header_row, 0, headers, fmt_header) ws.set_row(header_row, 34) start = header_row + 1 for i, p in enumerate(person_rows, 1): row = start + i - 1 ws.write_number(row, 0, i, fmt_center) ws.write_string(row, 1, p['dept'], fmt_text) ws.write_string(row, 2, p['person'], fmt_center) ws.write_number(row, 3, p['count'], fmt_num) ws.write_number(row, 4, p['raw_stock'], fmt_num) ws.write_number(row, 5, p['raw_break'], fmt_num) ws.write_number(row, 6, p['raw_explore'], fmt_num) ws.write_number(row, 7, p['raw_unknown'], fmt_num) # formulas with cached values ws.write_formula(row, 8, f'=SUM(E{row+1}:H{row+1})', fmt_num, p['raw']) ws.write_formula(row, 9, f'=E{row+1}*0.9', fmt_num, p['w_stock']) ws.write_formula(row, 10, f'=F{row+1}*0.3', fmt_num, p['w_break']) ws.write_formula(row, 11, f'=G{row+1}*0.1', fmt_num, p['w_explore']) ws.write_formula(row, 12, f'=SUM(J{row+1}:L{row+1})', fmt_num, p['weighted']) ws.write_formula(row, 13, f'=IFERROR(M{row+1}/I{row+1},0)', fmt_pct, p['weighted'] / p['raw'] if p['raw'] else 0) ws.write_string(row, 14, p['note'], fmt_note) ws.set_row(row, 26) total_row = start + len(person_rows) ws.merge_range(total_row, 0, total_row, 2, '合计', fmt_total_text) ws.write_formula(total_row, 3, f'=SUM(D{start+1}:D{total_row})', fmt_total_center, sum(p['count'] for p in person_rows)) for col in range(4, 8): letter = chr(ord('A') + col) ws.write_formula(total_row, col, f'=SUM({letter}{start+1}:{letter}{total_row})', fmt_total, sum(p[['raw_stock', 'raw_break', 'raw_explore', 'raw_unknown'][col-4]] for p in person_rows)) for col, key in [(8, 'raw'), (9, 'w_stock'), (10, 'w_break'), (11, 'w_explore'), (12, 'weighted')]: letter = chr(ord('A') + col) ws.write_formula(total_row, col, f'=SUM({letter}{start+1}:{letter}{total_row})', fmt_total, sum(p[key] for p in person_rows)) ws.write_formula(total_row, 13, f'=IFERROR(M{total_row+1}/I{total_row+1},0)', fmt_pct, ratio) ws.write_string(total_row, 14, 'V01 汇总漏赵云浩、刘子研；V02 已补全 13 人。', fmt_note) ws.set_row(total_row, 26) # Department summary r = total_row + 3 ws.merge_range(r, 0, r, 14, '二、按部门汇总（万元）', fmt_section) ws.set_row(r, 24) dh = r + 1 ws.merge_range(dh, 0, dh, 1, '部门', fmt_header) ws.write_string(dh, 2, '商机数', fmt_header) ws.write_string(dh, 3, '原报合计', fmt_header) ws.write_string(dh, 4, '加权合计', fmt_header) ws.write_string(dh, 5, '原报占比', fmt_header) ws.write_string(dh, 6, '加权占比', fmt_header) ws.merge_range(dh, 7, dh, 14, '说明', fmt_header) for i, d in enumerate(dept_stats): row = dh + 1 + i ws.merge_range(row, 0, row, 1, d['dept'], fmt_text) ws.write_number(row, 2, d['count'], fmt_num) ws.write_number(row, 3, d['raw'], fmt_num) ws.write_number(row, 4, d['weighted'], fmt_num) ws.write_formula(row, 5, f'=IFERROR(D{row+1}/$I${total_row+1},0)', fmt_pct, d['raw'] / raw_total if raw_total else 0) ws.write_formula(row, 6, f'=IFERROR(E{row+1}/$M${total_row+1},0)', fmt_pct, d['weighted'] / weighted_total if weighted_total else 0) ws.merge_range(row, 7, row, 14, '按销售明细自动汇总', fmt_note) ws.set_row(row, 24) dtotal = dh + 1 + len(dept_stats) ws.merge_range(dtotal, 0, dtotal, 1, '合计', fmt_total_text) ws.write_formula(dtotal, 2, f'=SUM(C{dh+2}:C{dtotal})', fmt_total_center, sum(d['count'] for d in dept_stats)) ws.write_formula(dtotal, 3, f'=SUM(D{dh+2}:D{dtotal})', fmt_total, sum(d['raw'] for d in dept_stats)) ws.write_formula(dtotal, 4, f'=SUM(E{dh+2}:E{dtotal})', fmt_total, sum(d['weighted'] for d in dept_stats)) ws.write_formula(dtotal, 5, f'=SUM(F{dh+2}:F{dtotal})', fmt_pct, 1.0) ws.write_formula(dtotal, 6, f'=SUM(G{dh+2}:G{dtotal})', fmt_pct, 1.0) ws.merge_range(dtotal, 7, dtotal, 14, '部门目标待管理层拍板，本表不擅自拆分 6,500 万。', fmt_note) # Category summary r = dtotal + 3 ws.merge_range(r, 0, r, 14, '三、按商机类型汇总（万元）', fmt_section) ws.set_row(r, 24) ch = r + 1 ws.merge_range(ch, 0, ch, 1, '商机类型', fmt_header) ws.write_string(ch, 2, '商机数', fmt_header) ws.write_string(ch, 3, '原报合计', fmt_header) ws.write_string(ch, 4, '权重', fmt_header) ws.write_string(ch, 5, '加权合计', fmt_header) ws.write_string(ch, 6, '原报占比', fmt_header) ws.write_string(ch, 7, '加权/原报', fmt_header) ws.merge_range(ch, 8, ch, 14, '说明', fmt_header) for i, c in enumerate(cat_stats): row = ch + 1 + i ws.merge_range(row, 0, row, 1, c['label'], fmt_text) ws.write_number(row, 2, c['count'], fmt_num) ws.write_number(row, 3, c['raw'], fmt_num) ws.write_number(row, 4, c['weight'], fmt_pct) ws.write_formula(row, 5, f'=D{row+1}*E{row+1}', fmt_num, c['weighted']) ws.write_formula(row, 6, f'=IFERROR(D{row+1}/$I${total_row+1},0)', fmt_pct, c['raw'] / raw_total if raw_total else 0) ws.write_formula(row, 7, f'=IFERROR(F{row+1}/D{row+1},0)', fmt_pct, c['weighted'] / c['raw'] if c['raw'] else 0) ws.merge_range(row, 8, row, 14, '原表混合类型已并入突破性；待归类不计入。', fmt_note) ws.set_row(row, 22) ctotal = ch + 1 + len(cat_stats) ws.merge_range(ctotal, 0, ctotal, 1, '合计', fmt_total_text) ws.write_formula(ctotal, 2, f'=SUM(C{ch+2}:C{ctotal})', fmt_total_center, sum(c['count'] for c in cat_stats)) ws.write_formula(ctotal, 3, f'=SUM(D{ch+2}:D{ctotal})', fmt_total, raw_total) ws.write_formula(ctotal, 5, f'=SUM(F{ch+2}:F{ctotal})', fmt_total, weighted_total) ws.write_formula(ctotal, 6, f'=SUM(G{ch+2}:G{ctotal})', fmt_pct, 1.0) ws.write_formula(ctotal, 7, f'=IFERROR(F{ctotal+1}/D{ctotal+1},0)', fmt_pct, ratio) ws.merge_range(ctotal, 8, ctotal, 14, f'存量占原报 {stock_share*100:.1f}%，加权占 {w_stock_share*100:.1f}%，结构偏存量。', fmt_note) # Target sensitivity r = ctotal + 3 ws.merge_range(r, 0, r, 14, '四、目标缺口与敏感性', fmt_section) ws.set_row(r, 24) sh = r + 1 ws.merge_range(sh, 0, sh, 2, '指标', fmt_header) ws.write_string(sh, 3, '数值', fmt_header) ws.merge_range(sh, 4, sh, 14, '说明', fmt_header) sens = [ ('2027 目标', TARGET_2027, '用户给定：2026 预计 5,000 万 × 1.30'), ('原报合计', raw_total, '按 Q1–Q4 合计，已剔除样例行'), ('原报缺口', gap_raw, '6,500 − 原报合计'), ('概率加权', weighted_total, '按 90%/30%/10% 加权'), ('加权缺口', gap_weighted, '6,500 − 加权合计；加权不是销售目标'), ('把原报补到 6,500 万后的加权', TARGET_2027 * ratio, '按当前结构加权/原报比例估算'), ('要把加权做到 6,500 万所需原报', TARGET_2027 / ratio if ratio else 0, '当前结构不变时的理论原报'), ('若新增全部为突破性，需新增原报', (TARGET_2027 - weighted_total) / 0.3, '用 30% 概率倒推'), ] for i, (label, val, note) in enumerate(sens): row = sh + 1 + i ws.merge_range(row, 0, row, 2, label, fmt_text) ws.write_number(row, 3, val, fmt_num) ws.merge_range(row, 4, row, 14, note, fmt_note) ws.set_row(row, 22) # Footer note note_row = sh + 1 + len(sens) + 1 ws.merge_range(note_row, 0, note_row, 14, f'结论：V02 原报 {raw_total:,.2f} 万，距 {TARGET_2027:,.0f} 万差 {gap_raw:,.2f} 万；' f'概率加权 {weighted_total:,.2f} 万，仅为目标的 {weighted_total/TARGET_2027*100:.2f}%。' '当前不是“再补一点金额”的问题，而是高质量商机数量不足。', F(font_size=9, bold=True, font_color='9A3412', bg_color='FEF3C7', align='left', valign='vcenter', text_wrap=True, border=1, border_color='F59E0B')) ws.set_row(note_row, 36) ws.set_landscape() ws.set_paper(9) ws.fit_to_pages(1, 0) ws.set_margins(0.25, 0.25, 0.45, 0.45) ws.set_header('&L宝锐生物 BIORI BIOTECH&C2027 诊断原料销售预测 · 汇总&RV02') ws.set_footer('&L守护生命健康，缔造美好生活&C第 &P 页 / 共 &N 页&R2026-11-05') ws.print_area(0, 0, note_row, 14) ws.repeat_rows(0, 2) # ------------------------------------------------------------------ # 01_问题与改善 # ------------------------------------------------------------------ pw = wb.add_worksheet('01_问题与改善') pw.set_tab_color('C00000') pw.hide_gridlines(2) pw.set_column('A:A', 5) pw.set_column('B:B', 16) pw.set_column('C:C', 52) pw.set_column('D:D', 28) pw.set_column('E:E', 28) pw.set_column('F:F', 30) pw.set_column('G:G', 14) pw.set_column('H:H', 10) pw.set_column('I:I', 28) pw.set_row(0, 34) pw.merge_range('A1:I1', 'V01 预测表问题清单与改善要求', fmt_title) pw.merge_range('A2:I2', f'V01 汇总 {v01_summary_total:,.2f} 万 ｜ 13 人明细原报 {raw_total:,.2f} 万 ｜ V02 加权 {weighted_total:,.2f} 万 ｜ 目标 {TARGET_2027:,.0f} 万', fmt_sub) headers = ['序号', '问题', '具体表现（可核对）', '影响', 'V02 处理', '改善要求', '主责', '时限', '验收'] hr = 4 pw.write_row(hr, 0, headers, fmt_header) pw.set_row(hr, 34) issues = [ ('1', '汇总漏人', f'Sheet1 只列 11 人，漏赵云浩 {sum(r["sumQ"] for r in included if r["person"]=="赵云浩")/1e4:,.2f} 万、刘子研 {sum(r["sumQ"] for r in included if r["person"]=="刘子研")/1e4:,.2f} 万；汇总 {v01_summary_total:,.2f} 万 vs 13 人明细 {raw_total:,.2f} 万，少 {raw_total-v01_summary_total:,.2f} 万。', '管理层看到的总盘偏低，误判缺口；两个最大贡献人未进入决策视野。', 'V02 已补全 13 人，汇总表自动汇总。', '汇总必须从 13 人明细自动生成，禁止手工另做一张汇总；新增/离职人员同步名单。', '刘新元', '11/06', '13 人全部在汇总；汇总与明细差额 = 0'), ('2', '年度列错', f'李江辉 36 条全部写 2026；K列 {k_2026:,.2f} 万，季度合计 {raw_2026:,.2f} 万。', '2027 预测混入 2026 年度，口径错，无法直接用于明年预算。', 'V02 已全部改为 2027，并在问题标记列留痕。', '2027 表只允许 2027；模板锁定年度列，发现 2026 直接退回。', '李江辉', '11/06', '36/36 行为 2027'), ('3', '样例行', f'刘虹晶第 2–3 行为 XXX/示例商机，第 2 行含 {sample_raw:,.2f} 万。', '虚增原报；样例占用汇总，污染销售判断。', 'V02 已标灰、标记“样例行”，不计入汇总。', '删除样例行；模板不带示例数据，培训时用单独样例文件。', '刘虹晶', '11/06', '0 条 XXX/示例'), ('4', '商机类型不规范', f'{n_mixed} 条“存量/突破”混合（{mixed_raw:,.2f} 万）；{n_blank_type} 条空白；另有 2 条样例三类全选。', '无法套用 90%/30%/10%，加权金额失真；同一商机可能被重复计数。', 'V02 混合类型暂按突破性 30%，空白按待归类 0% 且不计入；逐条留痕。', '每条只能选一类；混合商机必须拆成两条或由销售确认一类；空白 11/07 前补录。', '赵云浩/刘虹晶/李江辉', '11/07', '0 条混合/空白；类型枚举 100% 合规'), ('5', '公式不统一', f'{n_k_diff} 条 K 列（年度目标合计）≠ Q1–Q4 合计，净差 +{k_diff:,.2f} 万；12 条 K 列空白。', '明细与年度合计不一致，汇总随人而异。', 'V02 统一 K = SUM(Q1:Q4)，保留原值到问题标记。', 'K 列只读公式；销售只填 Q1–Q4；汇总只读 K/Q，不手填。', '各销售', '11/07', '0 条 K 与季度合计差异 > 1 元'), ('6', '字段缺失', f'{n_no_sku} 条无关联货号（{no_sku_raw:,.2f} 万）；{n_no_id} 条无商机 ID（{no_id_raw:,.2f} 万）；{n_no_opp} 条无商机名称。', '无法关联订单、无法跟踪商机、无法做客户/货号复盘。', 'V02 保留并逐条标记，不擅自补造。', '必填：客户、商机名称、关联货号、飞书商机 ID、类型、年度、Q1–Q4；缺一项退回。', '各销售', '11/10', '必填完整率 100%'), ('7', '商机 ID 重复', f'{n_dup_ids} 个 ID 被多条商机复用（如 6167855226×3、4205071332×2、4461524062×2）。', '同一商机拆成多条或重复计数；汇总与飞书对不上。', 'V02 逐条标记“ID重复×N”。', '一个商机一个 ID；父子项目/多标段必须在备注说明拆分关系。', '各销售', '11/10', '重复 ID = 0 或附拆分说明'), ('8', '部门口径不一致', f'{n_dept_bad} 条赵云浩的部门写“大客户”，其他人写“诊断原料大客户销售部/诊断原料销售拓展部”。', '部门汇总对不上，权限与考核归属容易错。', 'V02 统一为两个部门全称。', '部门用枚举，不允许手填简称。', '赵云浩', '11/06', '部门枚举一致'), ('9', '结构风险', f'原报 {raw_total:,.2f} 万：存量 {stock_share*100:.1f}%、突破 {break_share*100:.1f}%、探索 {explore_share*100:.1f}%；加权 {weighted_total:,.2f} 万，仅为原报 {ratio*100:.2f}%。', f'数量看着接近目标，但加权覆盖只有 {weighted_total/TARGET_2027*100:.2f}%；靠存量撑盘，突破/探索不足。', 'V02 已按类型拆出原报/加权。', '每人至少补 2 条有测试/决策节点的突破或探索商机；周会只看加权新增。', '刘新元 + 部门负责人', '11/10', '新增突破/探索 ≥ 26 条'), ('10', '目标缺口', f'目标 {TARGET_2027:,.0f} 万；原报缺口 {gap_raw:,.2f} 万；加权缺口 {gap_weighted:,.2f} 万；按当前结构补足原报，加权仅约 {TARGET_2027*ratio:,.2f} 万。', '只补金额不补质量，仍完不成 6,500 万。', 'V02 已做缺口与敏感性测算。', '11/10 前二次上报：原报 ≥ 6,500 万；加权 ≥ 5,200 万（80% 目标）；按人拆缺口。', '刘新元', '11/10', '原报 ≥ 6,500 万；加权 ≥ 5,200 万'), ('11', '合并续行重复', '赵云浩 4 条合并续行（I 列备注）被当作独立商机，重复 220.00 万；V02 已合并，赵原报 899→679 万。', '同一商机多次计数，金额虚增；销售自己也会误判商机数。', 'V02 已把续行备注并入上一条，赵云浩按 23 条商机计。', '合并单元格的续行只能作为上一条备注，不得单独计数；模板禁止用合并行承载多条商机。', '赵云浩/刘新元', '11/06', '赵云浩 23 条商机，无重复'), ] for i, issue in enumerate(issues): row = hr + 1 + i for j, val in enumerate(issue): fmt = fmt_center if j in (0, 6, 7) else fmt_text pw.write_string(row, j, str(val), fmt) pw.set_row(row, 78) # bottom requirements br = hr + 1 + len(issues) + 1 pw.merge_range(br, 0, br, 8, '二次上报的五条硬要求', fmt_section) reqs = [ '1. 每人一行一个商机；客户、商机名称、货号、飞书 ID、类型、Q1–Q4 六项必填。', '2. 商机类型只能选“存量商机 / 突破性商机 / 探索性商机”之一；混合类型拆成两条。', '3. 存量 90%、突破 30%、探索 10%；加权金额 = 年度目标合计 × 成交概率。', '4. K 列（年度目标合计）必须等于 Q1–Q4 合计；不得手填。', '5. 2027 表只允许 2027；样例行、XXX、无金额空行全部删除。', ] for i, text in enumerate(reqs): row = br + 1 + i pw.merge_range(row, 0, row, 8, text, fmt_text) pw.set_row(row, 24) pw.set_landscape() pw.set_paper(9) pw.fit_to_pages(1, 0) pw.set_margins(0.25, 0.25, 0.45, 0.45) pw.set_header('&L宝锐生物 BIORI BIOTECH&CV01 问题与改善要求&RV02') pw.set_footer('&L守护生命健康，缔造美好生活&C第 &P 页 / 共 &N 页&R2026-11-05') pw.print_area(0, 0, br + len(reqs), 8) # ------------------------------------------------------------------ # 02_口径与说明 # ------------------------------------------------------------------ nw = wb.add_worksheet('02_口径与说明') nw.set_tab_color(GRAY) nw.hide_gridlines(2) nw.set_column('A:A', 5) nw.set_column('B:B', 24) nw.set_column('C:C', 100) nw.set_row(0, 34) nw.merge_range('A1:C1', '口径与填写说明', fmt_title) nw.merge_range('A2:C2', 'V02 ｜ 2026-11-05 ｜ 宝锐生物 营销中心', fmt_sub) rows = [ ('一、三类商机', '', ''), ('存量商机', '90%', '2026 年持续成交商机；资源重在关键人维护、交付保障、提前做备货计划。'), ('突破性商机', '30%', '替换竞对（替换后可快速上量，但存在不确定性）；或破冰客户的首个可成交商机，需要打通客情、产品、决策链。'), ('探索性商机', '10%', '老客户预研项目、荧光素酶、PDRN 等新业务/新场景；我司在客户基础、产品能力、解决方案上仍需补强。'), ('二、计算', '', ''), ('年度目标合计', 'K 列', 'K = Q1 + Q2 + Q3 + Q4；V02 全部改为公式，销售只填季度。'), ('成交概率', 'P 列', '按商机类型自动取 90%/30%/10%；待归类为 0%。'), ('加权金额', 'Q 列', 'Q = K × P；用于衡量商机质量与风险，不等同于销售目标。'), ('目标', '6,500 万', '2026 预计 5,000 万 × 1.30；目标为销售收入，不是加权金额。'), ('三、V02 的处理', '', ''), ('汇总', '13 人', 'V01 漏赵云浩、刘子研；V02 全部补入，汇总自动生成。'), ('年度', '2027', '李江辉 36 条 2026 已改 2027。'), ('类型', '混合→突破', '12 条“存量/突破”混合暂按突破性 30%；建议销售拆成两条。'), ('样例', '不计入', '刘虹晶 2 条 XXX/示例商机标灰，不计入汇总。'), ('公式', '统一', '10 条 K 列与季度合计不一致，V02 统一按季度合计修正。'), ('部门', '归一', '赵云浩“大客户”改为“诊断原料大客户销售部”。'), ('四、待确认', '', ''), ('混合类型', '原表混合', '是否拆成存量+突破两条，或由销售确认单一类型。'), ('缺失字段', '51/17', '51 条缺货号、17 条缺商机 ID；V02 保留并标记，需补录。'), ('部门目标', '待定', '6,500 万只做全公司口径；部门目标需管理层拍板后再拆。'), ('五、使用', '', ''), ('每人 sheet', '填写', '只填黄色列：商机类型、Q1–Q4；K/P/Q 为公式，不要改。'), ('汇总 sheet', '只看', '按销售/部门/类型三个视图；加权缺口用于判断质量，不直接当销售目标。'), ] rr = 4 for a, b, c in rows: if a.startswith(('一、', '二、', '三、', '四、', '五、')): nw.merge_range(rr, 0, rr, 2, a, fmt_section) nw.set_row(rr, 24) else: nw.write_string(rr, 0, '', fmt_text) nw.write_string(rr, 1, a, fmt_meta_title) nw.write_string(rr, 2, b if not c else f'{b} ｜ {c}', fmt_text) nw.set_row(rr, 32) rr += 1 nw.set_landscape() nw.set_paper(9) nw.fit_to_pages(1, 1) nw.set_margins(0.3, 0.3, 0.45, 0.45) nw.set_header('&L宝锐生物 BIORI BIOTECH&C2027 预测口径&RV02') nw.set_footer('&L守护生命健康，缔造美好生活&C第 &P 页 / 共 &N 页&R2026-11-05') nw.print_area(0, 0, rr - 1, 2) # ------------------------------------------------------------------ # 03_赵云浩续行核查 # ------------------------------------------------------------------ cw = wb.add_worksheet('03_赵云浩续行核查') cw.set_tab_color('BF8F00') cw.hide_gridlines(2) cw.set_column('A:A', 5) cw.set_column('B:B', 9) cw.set_column('C:C', 9) cw.set_column('D:D', 26) cw.set_column('E:E', 24) cw.set_column('F:F', 30) cw.set_column('G:G', 14) cw.set_column('H:H', 14) cw.set_column('I:I', 62) cw.set_column('J:J', 12) cw.set_column('K:K', 16) cw.set_row(0, 34) cw.merge_range('A1:K1', '赵云浩：4 条合并续行核查', fmt_title) cw.merge_range('A2:K2', '原表 27 行（含续行）→ 23 个独立商机；原报 899 万 → 679 万；重复 220 万。', fmt_sub) ch = ['序号', '原表行', '归属行', '合并范围', '客户', '商机名称', '商机ID', '年度目标(元)', '续行内容（I 列）', '是否独立商机', '重复金额(元)'] cw.write_row(3, 0, ch, fmt_header) cw.set_row(3, 38) cont_rows = [ ('1', 4, 3, 'A3:A4 等列合并\nI3:I4 未合并', '圣湘生物', '公卫项目多重-ZW', '4461524062', 1000000, '2、寻求RT测试机会。', '否，是第3行续行', 1000000), ('2', 7, 6, 'A6:A8 等列合并\nI6:I8 未合并', '北京明日达', 'RNA单管项目-LHB', '6167855226', 500000, '2、20261115前解决M2244-043801客诉问题（批间性能异常），形成后续稳定供货方案。', '否，是第6行续行', 500000), ('3', 8, 6, 'A6:A8 等列合并\nI6:I8 未合并', '北京明日达', 'RNA单管项目-LHB', '6167855226', 500000, '3、明确后续内部质量标准提升的节点，建议内部于2026年12月31日前提升质控盘标准。', '否，是第6行续行', 500000), ('4', 14, 13, 'A13:A14 等列合并\nI13:I14 未合并', '武汉明德', '新冠甲乙流报证优化-XJ', '4419518368', 200000, '2、20261130前解决客户现在冻干后重复性差的问题。', '否，是第13行续行', 200000), ] for i, row in enumerate(cont_rows): r = 4 + i cw.write_string(r, 0, row[0], fmt_center) cw.write_number(r, 1, row[1], fmt_num) cw.write_number(r, 2, row[2], fmt_num) cw.write_string(r, 3, row[3], fmt_text) cw.write_string(r, 4, row[4], fmt_text) cw.write_string(r, 5, row[5], fmt_text) cw.write_string(r, 6, row[6], fmt_code) cw.write_number(r, 7, row[7], fmt_num) cw.write_string(r, 8, row[8], fmt_text) cw.write_string(r, 9, row[9], fmt_center) cw.write_number(r, 10, row[10], fmt_num) cw.set_row(r, 64) ctr = 4 + len(cont_rows) cw.merge_range(ctr, 0, ctr, 7, '合计', fmt_total_text) cw.merge_range(ctr, 8, ctr, 9, '4 条续行，均为上一条商机的补充说明，不是独立商机', fmt_total_text) cw.write_formula(ctr, 10, f'=SUM(K5:K{ctr})', fmt_total, 2200000) cw.set_row(ctr, 26) notes = [ '问题根因：原表把 A:H、J:O 合并了，但 I 列（所需资源保障及时间节点）没有合并；续行只有 I 列有文字。', '按行读取时，合并单元格会向前填充，4 条续行看起来都带着上一条的客户/商机/金额，于是同一商机被重复计数。', '按简单求和，赵云浩原报会从 679 万虚增到 899 万；V02 已把续行内容并回上一条商机的 I 列，商机数按 23 条计。', '处理规则：合并范围覆盖的行只能算一条商机；续行文字并入上一条「所需资源保障及时间节点」，不单独计数。', ] nr = ctr + 2 for i, text in enumerate(notes): cw.merge_range(nr+i, 0, nr+i, 10, text, fmt_text) cw.set_row(nr+i, 34) cw.set_landscape() cw.set_paper(9) cw.fit_to_pages(1, 1) cw.set_margins(0.25, 0.25, 0.45, 0.45) cw.set_header('&L宝锐生物 BIORI BIOTECH&C赵云浩合并续行核查&RV02') cw.set_footer('&L守护生命健康，缔造美好生活&C第 &P 页 / 共 &N 页&R2026-11-05') cw.print_area(0, 0, nr + len(notes) - 1, 10) # ------------------------------------------------------------------ # salesperson sheets # ------------------------------------------------------------------ for person in people: rows = by_person.get(person, []) ws = wb.add_worksheet(person) ws.set_tab_color(TEAL) ws.hide_gridlines(2) ws.set_zoom(80) widths = [7, 8, 22, 24, 34, 20, 16, 14, 52, 14, 14, 10, 10, 10, 10, 9, 13, 34, 9] for i, w in enumerate(widths): ws.set_column(i, i, w) ws.set_row(0, 34) ws.merge_range(0, 0, 0, 18, f'{person} · 2027 年诊断原料销售预测', fmt_title) # meta row ws.set_row(1, 30) ws.merge_range(1, 0, 1, 1, '本表汇总（V02）', fmt_meta_title) ws.write_string(1, 2, '纳入商机数', fmt_meta_label) ws.write_formula(1, 3, f'=COUNTIF(S4:S{3+len(rows)},"是")', fmt_meta_num, len([r for r in rows if r['include']])) ws.write_string(1, 4, '原报合计(元)', fmt_meta_label) ws.write_formula(1, 5, f'=SUMIF(S4:S{3+len(rows)},"是",K4:K{3+len(rows)})', fmt_meta_money, sum(r['sumQ'] for r in rows if r['include'])) ws.write_string(1, 6, '加权合计(元)', fmt_meta_label) ws.write_formula(1, 7, f'=SUMIF(S4:S{3+len(rows)},"是",Q4:Q{3+len(rows)})', fmt_meta_money, sum(r['weighted'] for r in rows if r['include'])) ws.merge_range(1, 8, 1, 18, 'K = Q1–Q4 合计；加权 = K × 成交概率；混合类型按突破性 30%。黄色列填写，蓝底列为公式。', fmt_meta_note) headers = ['年份', '销售', '部门', '客户名称', '商机名称', '关联货号', '商机ID(飞书)', '商机类型', '所需资源保障及时间节点', '业务线', '年度目标合计(元)', 'Q1预测(元)', 'Q2预测(元)', 'Q3预测(元)', 'Q4预测(元)', '成交概率', '加权金额(元)', '问题标记', '计入汇总'] ws.write_row(2, 0, headers, fmt_header) ws.set_row(2, 34) start_row = 3 for i, rec in enumerate(rows): row = start_row + i alt = (i % 2 == 1) # A-J ws.write_number(row, 0, 2027, fmt_center_alt if alt else fmt_center) ws.write_string(row, 1, person, fmt_center_alt if alt else fmt_center) ws.write_string(row, 2, rec['dept'], fmt_center_alt if alt else fmt_center) ws.write_string(row, 3, str(rec['customer'] or ''), fmt_text_alt if alt else fmt_text) ws.write_string(row, 4, str(rec['opp'] or ''), fmt_text_alt if alt else fmt_text) ws.write_string(row, 5, str(rec['sku'] or ''), fmt_code_alt if alt else fmt_code) ws.write_string(row, 6, str(rec['oid'] or ''), fmt_code_alt if alt else fmt_code) # H: type input, but sample gray if rec['sample']: ws.write_string(row, 7, rec['type'], fmt_gray_center) else: ws.write_string(row, 7, rec['type'], fmt_input) ws.write_string(row, 8, str(rec['resource'] or ''), fmt_text_alt if alt else fmt_text) ws.write_string(row, 9, str(rec['line'] or ''), fmt_center_alt if alt else fmt_center) # K formula ws.write_formula(row, 10, f'=SUM(L{row+1}:O{row+1})', fmt_formula if not alt else fmt_formula, rec['sumQ']) # quarters input for j, qv in enumerate(rec['quarters']): ws.write_number(row, 11 + j, qv, fmt_input) # P formula ws.write_formula(row, 15, f'=IF(H{row+1}="存量商机",0.9,IF(H{row+1}="突破性商机",0.3,IF(H{row+1}="探索性商机",0.1,0)))', fmt_formula_pct, rec['prob']) # Q formula ws.write_formula(row, 16, f'=K{row+1}*P{row+1}', fmt_formula, rec['weighted']) # R note ws.write_string(row, 17, rec['note'], fmt_gray if rec['sample'] else (fmt_note_alt if alt else fmt_note)) # S ws.write_string(row, 18, '是' if rec['include'] else '否', fmt_center_alt if (alt and rec['include']) else (fmt_center if rec['include'] else fmt_gray_center)) ws.set_row(row, 52) last_data = start_row + len(rows) - 1 # total row tr = last_data + 2 ws.merge_range(tr, 0, tr, 2, '合计', fmt_total_text) ws.write_formula(tr, 3, f'=COUNTIF(S4:S{last_data+1},"是")', fmt_total_center, len([r for r in rows if r['include']])) ws.write_formula(tr, 10, f'=SUMIF(S4:S{last_data+1},"是",K4:K{last_data+1})', fmt_total, sum(r['sumQ'] for r in rows if r['include'])) ws.write_formula(tr, 16, f'=SUMIF(S4:S{last_data+1},"是",Q4:Q{last_data+1})', fmt_total, sum(r['weighted'] for r in rows if r['include'])) ws.merge_range(tr, 17, tr, 18, 'V02 自动汇总', fmt_note) ws.set_row(tr, 26) # Validation and formatting ws.data_validation(3, 7, last_data, 7, { 'validate': 'list', 'source': ['存量商机', '突破性商机', '探索性商机', '待归类'], 'error_title': '商机类型', 'error_message': '只能选：存量商机 / 突破性商机 / 探索性商机 / 待归类', }) ws.data_validation(3, 18, last_data, 18, { 'validate': 'list', 'source': ['是', '否'], 'error_title': '计入汇总', 'error_message': '选是或否', }) ws.conditional_format(3, 7, last_data, 7, {'type': 'text', 'criteria': 'containing', 'value': '存量', 'format': F(bg_color=GREEN)}) ws.conditional_format(3, 7, last_data, 7, {'type': 'text', 'criteria': 'containing', 'value': '突破', 'format': F(bg_color=AMBER)}) ws.conditional_format(3, 7, last_data, 7, {'type': 'text', 'criteria': 'containing', 'value': '探索', 'format': F(bg_color=BLUE)}) ws.conditional_format(3, 7, last_data, 7, {'type': 'text', 'criteria': 'containing', 'value': '待归类', 'format': F(bg_color='#F2F2F2')}) ws.conditional_format(3, 15, last_data, 15, {'type': '3_color_scale', 'min_color': '#F8696B', 'mid_color': '#FFEB84', 'max_color': '#63BE7B'}) ws.conditional_format(3, 18, last_data, 18, {'type': 'cell', 'criteria': '==', 'value': '"否"', 'format': F(bg_color='#F2F2F2', font_color=GRAY)}) ws.freeze_panes(3, 0) ws.autofilter(2, 0, last_data, 18) ws.set_landscape() ws.set_paper(9) ws.fit_to_pages(1, 0) ws.set_margins(0.2, 0.2, 0.4, 0.4) ws.set_header(f'&L宝锐生物 BIORI BIOTECH&C{person} · 2027 预测&RV02') ws.set_footer('&L守护生命健康，缔造美好生活&C第 &P 页 / 共 &N 页&R2026-11-05') ws.print_area(0, 0, tr, 18) ws.repeat_rows(0, 2) wb.close() print('WROTE', OUT, os.path.getsize(OUT)) print('people', people) print('raw_total_wan', round(raw_total, 2), 'weighted_total_wan', round(weighted_total, 2), 'gap_raw', round(gap_raw, 2), 'gap_weighted', round(gap_weighted, 2), 'ratio', round(ratio, 4)) print('counts', n_all, n_valid, n_included, n_excluded) if __name__ == '__main__': main()