shield_lock
JinDaGe
← Back to skills
extension
Category: Data & AnalyticsNo API key required

excel-analysis-sunbin

孙斌的 Excel 数据分析规范。对本地 xlsx/xls/csv 做汇总分析、环比统计并生成 Excel 图表时使用。核心要求：分析结果写入独立的新 Excel 文件；汇总表必须带活公式（先复制源数据到明细 sheet，再用 SUMIFS/SUMPRODUCT 引用）；图表用 xlsxwriter 生成并遵循 chart-style 规范；涨红跌绿。触发词：汇总、环比、走势、Excel 图表、数据分析。开始分析前会先让用户选择图表配色风格（麦肯锡/经济学人/FT/彭博/WSJ/Nature/极简黑白/跟随主题）。

personAuthor: user_e49a740ahubcommunity
downloadDownload package

Excel 数据分析规范（孙斌版）

处理销售、运营等本地表格数据，产出带公式和图表的分析 Excel 文件时，遵循以下规范。

配色选择（每次开始分析前先问）

用户没有在需求里指定配色风格时，先贴出下方菜单让用户选（回复编号或名称即可）， 等用户选定后再进入核心工作流；用户已直接说风格（如「麦肯锡配色」「用 FT」）则跳过询问。

图表配色用哪套？（回复编号即可，如 1；以下为当前可选，技能更新新增配色后会同步加进来）
1. 麦肯锡 McKinsey —— 深藏蓝 #003A70｜咨询/战略/管理层汇报（推荐）
2. BCG 波士顿咨询 —— 深绿 #006C57｜增长/国企/咨询汇报
3. 经济学人 Economist —— 标志红 #E3120B｜观点鲜明的数据评论
4. 金融时报 FT —— 蓝 #0F5499｜财经、券商、正式报告
5. 彭博 Bloomberg —— 橙 #FF6600｜终端风、强对比
6. 华尔街日报 WSJ —— 黑 #1A1A1A｜报纸风、严肃
7. Nature —— 蓝 #3C5488｜科研、低饱和
8. 极简黑白 Mono —— 黑白灰｜黑白打印
9. Default —— 跟随 Excel 主题色（页面布局 → 颜色）

规则：

  • 编号/名称对应 --palette：1=mckinsey、2=bcg、3=economist、4=ft、5=bloomberg、6=wsj、7=nature、8=mono、9=default
  • 用户答「随便/你定」：按场景默认——管理层汇报用 mckinsey，增长/国企汇报用 bcg，财经用 ft，黑白打印用 mono，其余用 default，并在交付说明里注明
  • 同一分析任务内沿用首次选择；用户明确要求换配色时才重问
  • 选定后把 --palette <名称> 传给 scripts 脚本（或按 references/palettes.md 字段直接应用），图表与表格样式会一并切换
  • 选项会随 scripts/_palettes.py 更新而增加：展示前优先运行任一脚本的 --list-palettes（如 python multi_sheet_report.py --list-palettes）拉取最新列表；内置 8 项仅作快速参考，若代码库已新增配色，以实际列表为准并全量展示
  • 新增配色后，同步更新本菜单与 references/palettes.md，保持文档与代码一致

核心工作流

  1. 源表体检（探针）：先跑 python scripts/inspect_source.py <源文件> [--header <行号>] 做一键画像——sheet 概览、前几行预览、每列非空率/类型/日期·金额·比率·维度标记、候选列建议。若前几行是标题/说明或表头不在第 1 行，用 --header <行号> 重跑，别靠猜。
  2. 口径确认卡（一次问完，别一问一答多轮）：据体检结果列出 3–4 项让用户一次确认——日期列、金额列、要不要清洗（剔除说明行/空行、金额去 ¥/千分号/"万"单位、文本日期转日期）、是否按某维度分组（如区域）。配色若前面还没定，并入本卡。
  3. 建分析文件：输出到独立的新 Excel 文件（如 月度销售分析.xlsx），与源文件同目录，不修改源文件。
  4. 文件结构（两个 sheet）：
    • 明细数据：把源表原始数据完整复制进来（第一行表头 + 数据，保持原列顺序）
    • 分析结果（如「月度分析」）：汇总表，所有汇总单元格必须用活公式引用明细 sheet，不允许写死数值
  5. 图表：xlsxwriter 生成，插在分析 sheet 数据区右侧（如 E2），遵循 references/chart-style.md 规范。
  6. 校验：写公式时同步传入 Python 计算的正确结果作为缓存值（write_formula 第 5 参数），用 openpyxl data_only=True 回读比对，确保公式与数值一致；同时检查 zip 内 chart 部件存在。

公式规范（详见 references/formula-patterns.md）

  • 汇总一律用 SUMIFS / SUMPRODUCT 引用 明细数据 sheet，公式随数据改动自动重算
  • 按月不分年份：=SUMPRODUCT((MONTH(明细数据!$A$2:$A$3639)=1)*明细数据!$F$2:$F$3639)
  • 环比：=IF(B2=0,"—",(B3-B2)/B2)，首行环比为 —
  • 合计行：=SUM(B2:B13)
  • 衍生指标（合计、环比、占比等）也必须是公式，不允许直接写数值

图表规范（详见 references/chart-style.md）

  • 标题简短，14 号字加粗微软雅黑；说明性备注（如"不分年份"）写进表格上方注释行，不塞进图表标题
  • 无网格线、无坐标轴标题、无刻度线（两轴都要关）；Y 轴轴线隐藏，X 轴轴线淡灰 #BFBFBF；轴刻度标签 9 号深灰 #595959 微软雅黑
  • ⚠️ 组合图（柱 + 折线双轴）例外：两个数值轴都保留轴线与刻度线（轴线淡灰、刻度线 outside），仅关网格线，保证左右轴各自可读；左轴刻度 #,##0、右轴刻度 0.0%（详见 references/chart-style.md「组合图」小节）
  • 图例：单系列不显示图例；两个及以上系列显示图例且位置靠上
  • 折线图默认不加数据标签（点位多时拥挤遮挡），靠 Y 轴刻度读数；数据点 ≤ 6 个时才开标签
  • 图表配色由 --palette 决定（8 种预设，见 references/palettes.md）： default 写主题色（跟随「页面布局 → 颜色」）；其余为固定 RGB 的专业配色（麦肯锡/经济学人/FT/彭博/WSJ/Nature/极简黑白）。 表格样式（表头底色、字体色、边框、隔行底色、涨跌幅色）一并随配色切换，由 _palettes.make_formats() 生成
  • 主系列线宽 2.25，圆形标记 size 6；Y 轴 num_format: '#,##0'；图表边框取配色 chart_border
  • 尺寸：默认横版 505×305；条形图竖版 390×460（渲染约 360×460）；饼图/圆环图方形 460×460
  • 柱形/条形图：间隙宽度 120%（series 加 'gap': 120）；数据降序排；条形图分类轴加 'reverse': True 保证大值在顶部（注意 xlsxwriter 条形图轴映射互换，见 chart-style.md）
  • 饼图/圆环图：逐扇区配色（否则全同色）；标签显示类别名 + 占比、不要图例；圆环图标签白色 + 深色调色板
  • ⚠️ 圆环图数据标签禁止写 position（Excel 会判定文件损坏，见 chart-style.md）
  • 饼图小占比分项过多（尾部多个 <2% 挤在一起）时改用复合条饼图：先生成饼图，再调用 scripts/_ofpie.py 的 to_bar_of_pie(输出, 2) 把 <2% 分项移到右侧长条；尺寸改横向 760×430，锚点可放数据区下方（见 chart-style.md）

可复用脚本（scripts/）

  • inspect_source.py — 源表体检探针：一键输出 sheet 概览 / 前几行预览 / 每列类型·非空率·日期·金额·比率·维度标记 / 候选列建议，分析前先跑，减少来回试读。python inspect_source.py <源> [--header <行号>]
  • monthly_summary_report.py — 月度汇总 + 环比 + 折线图：python monthly_summary_report.py <源文件> <输出文件> [日期列号] [金额列号]（默认日期列 1、金额列 6）
  • multi_sheet_report.py — 一个文件多个分析 sheet：python multi_sheet_report.py <源文件> <输出文件> [日期列号] [区域列号] [金额列号]，输出「明细数据 + 月度分析(折线) + 区域分析(柱形) + 区域占比(饼图)」。多维度需求优先用这个脚本，公式共用同一份明细 sheet
    • 新增维度时：复制一个分析 sheet 的写法，改汇总键（列）和图表类型即可；图表锚点各自 sheet 的 E2
    • ⚠️ 合计行要紧接数据末尾，行号用 len(items) + 1（0 基）算，别写死——写死容易多出一个空行
  • regional_summary_report.py — 区域汇总 + 占比 + 图表：python regional_summary_report.py <源文件> <输出文件> [区域列号] [金额列号] [column|bar|pie|doughnut]（默认区域列 2、金额列 6、柱形图；bar 条形图竖版 + 分类轴逆序，pie/doughnut 逐扇区配色 + 类别名/占比标签 + 无图例 + 方形尺寸）
  • _palettes.py — 配色方案库（8 套：图表色 + 表格样式 + 字体），被上面三个脚本 import。 传 --palette <名称> 调用，--list-palettes 查看全部；新增配色只需在 _RGB_PALETTES 里加一条
  • 新分析类型优先参照现有脚本结构复制改造，保持公式缓存值校验逻辑

交付前必做：Excel 实开验证（仅最终交付版）

  • 试跑阶段不实开：中间迭代版本靠脚本自带校验（公式缓存值回读）把关即可；只有最终交付版才用 Excel COM 实开一次，避免每改一版都启动 Excel。若本机 Excel 启动慢或 COM 不可用，可请用户自己双击打开确认。

XML 合法 ≠ Excel 能打开。已踩过一次坑（圆环图带 dLblPos 的文件 XML 完全合法、schema 顺序也对， 但 Excel 直接拒绝打开）。生成文件后用 Excel COM 实开一次：

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false; $xl.DisplayAlerts = $false
try { $wb = $xl.Workbooks.Open($f); $wb.Close($false); "OPEN-OK" } catch { "OPEN-FAIL" }
$xl.Quit()

需要看渲染效果又不想截图时，可用 $ch.Export($png, "PNG") 导出后用 Pillow 做像素分析 （定位图形半径、标签分布、对比度），比截图省事且可量化。

通用约定

  • 全表字体统一微软雅黑：所有单元格格式和图表字体（标题、轴刻度、图例）都必须显式指定 font_name: '微软雅黑'
  • 明细数据 sheet 的日期列必须用日期格式写入（num_format: 'yyyy/m/d'，如 2025/1/1），不允许变成文本或序列数；写入前按列 sniff 数据类型
  • 环比/涨跌着色：涨用红色 #C00000，跌用绿色 #008000（中国市场惯例），仅金融场景需与用户确认
  • 表头样式：加粗、居中、边框、底色 #D9E2F3；数值 #,##0、百分比 0.0%，均居中带边框
  • 文件被占用报 PermissionError 时，输出到新文件名（如加 -v2），不要试图覆盖
  • 交付说明：在回复中给出关键数字结论，不只给文件