在 Excel 中复核指标并完成三张图

这一节不从“选中数据，插入图表”一句话跳到成品。我们会先把月度公式写出来，再说明为什么选折线图和条形图，最后完成留存风险热力图。每一步都要能回到 SQL 结果复核。

先确认“月度数据”工作表有五列：月份、订单数、客户数、销售额和毛利额。下面新增的客单价、毛利率、环比和移动平均都是公式，不手工填写结果。

先建立月度计算区

在 F1:I1 输入四个表头：

这里保留英文缩写，是为了与 SQL 字段和常见分析术语对应。你也可以显示成中文，公式不受影响。

计算客单价

在 F2 输入：

=D2/B2

D2 是月销售额，B2 是当月完成订单数。公式向下填充到 F19。

为什么分母不是客户数？因为客单价描述“一笔订单平均多少钱”。如果用客户数，算出来的是月度客均销售额，回答的是另一件事。两个指标都能算，但名称必须对应分母。

计算毛利率

在 G2 输入：

=E2/D2

把 G2:G19 设置为百分比，保留两位小数。这里用月毛利额除以月销售额，不是把每件商品的毛利率取平均。

如果销售额可能为 0，正式模板可以写成：

=IFERROR(E2/D2,"")

本项目每个月都有销售额，所以简单公式更容易看清口径。

计算销售额环比

首月没有上月，H2 保持空白或写 n/a。在 H3 输入：

=D3/D2-1

向下填充到 H19，并设置为百分比。

这个写法与 =(D3-D2)/D2 等价。判断公式是否正确，可以先用 2024-02 手算：

所以 H3 应显示约 -3.11%。如果显示 -311%，说明百分比被重复乘了 100；如果显示正数，通常是新旧月份写反了。

计算三个月移动平均

窗口开头还不足三个月，我们用已有月份计算：

I2 = D2

I3 = AVERAGE(D2:D3)

I4 = AVERAGE(D2:D4)

从 I5 开始使用完整三个月窗口：

=AVERAGE(D3:D5)

向下填充到 I19。填充后 I6 会自动变为 =AVERAGE(D4:D6)，窗口始终向后移动一行。

移动平均不是为了让曲线更“好看”。它减少单月噪声，让我们判断近期水平是在抬升还是下降。它也会让突然变化变得平缓，所以不能拿移动平均替代原始销售额。

逐项验证公式

先不要画图。抽查首行、中间行和最后一行，避免整列公式一起错。

再做总额对账：

=SUM(D2:D19)

结果应为 229909.45。毛利额合计应为 113296.45。对账通过，才说明 SQL 导出、Power Query 导入和 Excel 公式使用了同一范围。

公式出现错误时怎么查

• #DIV/0!：分母是 0 或空白，先检查订单数和销售额列。
• #VALUE!：参与计算的单元格可能是文本，查看是否混入单位或空格。
• 整列结果相同：填充时用了绝对引用，例如 $D$2/$B$2。
• 只有最后几行空白：填充范围没有覆盖到第 19 行。
• 环比错位：月份没有按升序排列，上一行不再代表上一个月。

这些错误都应该修公式或数据类型，不要把错误结果复制成数值后再手改。

制作月度销售额趋势图

月度销售额有明确的时间顺序，我们使用折线图。柱形图也能显示 18 个月，但折线更容易观察连续变化和转折。

准备连续的图表区域

如果月份和销售额不相邻，可以在 K:L 建一个图表驱动区：

选中 K2:L19，使用“向下填充”。这个辅助区不是重复录入，它用公式引用原始表。查询刷新后，图表数据也会跟着变。

插入折线图

1. 选中 K1:L19，包括表头。
2. 打开“插入”选项卡。
3. 选择“折线图 > 带数据标记的折线图”。
4. 图表标题改为“月度销售额趋势”。
5. 纵轴使用千位分隔，单位保持为元。
6. 横轴按月份升序，不手工重排数据点。
7. 删除无意义的图例；只有一条销售额线时，标题已经说明序列。

图中能看到销售额主要在 12000 到 14000 之间波动，2025-05 明显下探，2025-06 回升。图表只负责让变化可见，原因仍要回到品类、城市或订单明细中查询。

如果要同时展示订单数

销售额约为 12000，订单数约为 30，两者量级差很多。直接放在同一坐标轴上，订单数会贴近横轴。

可以建立三列辅助区：月份、销售额、订单数，然后插入组合图：

• 销售额使用柱形或折线，放主坐标轴。
• 订单数使用折线，勾选次坐标轴。
• 左轴标明“销售额（元）”，右轴标明“订单数”。

次坐标轴解决可读性，但也会增加误读风险。不要比较两条线的视觉高度，只比较它们各自的方向和拐点。

制作品类销售额条形图

品类没有时间顺序，我们关心的是排名。因此选择横向条形图，并按销售额从高到低排序。

先让 SQL 生成 category.csv：

.headers on
.mode csv
.once category.csv
 
SELECT
    category,
    COUNT(DISTINCT order_id) AS orders,
    SUM(quantity) AS units,
    ROUND(SUM(revenue), 2) AS revenue,
    ROUND(SUM(gross_profit), 2) AS gross_profit,
    ROUND(SUM(gross_profit) / SUM

用 Power Query 加载到“品类数据”工作表。确认五个品类的销售额合计仍是 229909.45。

插入横向条形图

1. 选中“品类”和“销售额”两列。
2. 打开“插入 > 柱形图”。
3. 在“二维条形图”中选择“簇状条形图”。
4. 保持数码、餐厨、居家、饮水、出行的销售额降序。
5. 标题写“品类销售额（元）”。
6. 横轴从 0 开始，不截断坐标轴制造差距。

为什么不使用饼图？五个品类可以画饼图，但人眼对长度的比较比角度更准确。我们还需要清楚看出餐厨与数码、饮水与出行之间的差距，条形图更合适。

毛利率不要与销售额共用一个横轴。销售额是金额，毛利率是比例。可以把毛利率留在旁边的表中，或另做一张点图，避免一个坐标轴同时解释两种单位。

制作留存风险热力图

留存数据的行是首购月份，列是 M0 到 M6。每个单元格代表某一批客户在首购后第几个月仍然活跃。

先检查表的结构

导入后应满足：

• A 列是 cohort_month。
• B:H 是 M0:M6。
• M0 全部是 100%。
• 留存率是 0 到 1 之间的数值，而不是带 % 的文本。

如果 M0 不是 100%，先回 SQL 检查首购队列人数和活跃人数的口径。热力图不能修复错误的分母。

应用风险色阶

1. 选中 B2:H9，不包含表头和首购月份。
2. 单击“百分比样式”，保留两位小数。
3. 打开“条件格式 > 色阶”。
4. 选择“白—红”双色阶，让低留存显示为红色，高留存接近白色。
5. 检查 0% 是深红色，100% 是白色，确认颜色方向没有反。

这里把颜色定义为“风险浓度”，所以红色表示需要关注，不是表示表现更好。若你使用绿色色阶，也要检查高值与低值的方向。只看色阶名字不够，应该用 0% 和 100% 两个端点验收。

怎样读这张热力图

2024-01 队列的 M1 是 7.14%，M5 是 64.29%。留存并不一定随月龄单调下降，因为本项目统计的是“当月是否活跃”，不是“从首购后一直存活到这个月”。客户可以 M2 没买，M5 又回来。

如果业务要看连续留存或生存率，需要改 SQL 定义，不能只改热力图颜色。

图表完成后的验收

每张图检查四件事：

1. 数据范围是否包含全部行，但没有包含空白行。
2. 横轴或类别顺序是否符合业务含义。
3. 数值单位是否写清，金额与百分比没有共用同一轴。
4. 图中的首尾值是否与表格一致。

再执行一次 SQL 导出和 Excel 刷新。月度表、品类表和留存表更新后，公式与图表都应继续引用正确区域。如果刷新后图表仍显示旧值，先检查图表源范围是否连接到查询表或公式辅助区。

下一节会把四个核心指标和这些图组织成一页看板，并完成刷新、对账和业务结论的最终交付。