跳到主要内容

Excel商业图表制作

本章概览​

商业图表是数据可视化的重要形式,能够快速传达关键信息并支持商业决策。本章将介绍如何制作专业的商业图表,包括仪表盘、财务图表、KPI可视化等实用技巧。

学习目标

  • 掌握商业仪表盘设计原则
  • 学会制作财务三大报表图表
  • 熟练制作KPI监控图表
  • 掌握高级图表技巧和美化方法
  • 学会数据故事化呈现

24.1 商业图表设计原则​

24.1.1 KISS原则(Keep It Simple & Stupid)​

简洁性

  • 一图一主题
  • 去除冗余元素
  • 突出核心数据

清晰性

  • 标题明确
  • 坐标轴标注完整
  • 图例易于理解

示例对比

❌ 错误:
- 3D效果堆砌
- 过多颜色
- 冗余网格线
- 无明确标题

✓ 正确:
- 2D扁平化
- 3-5种主色
- 简洁网格
- 清晰标题

24.1.2 配色方案​

品牌色

  • 使用公司VI标准色
  • 保持风格一致

功能色

红色: 警示、负面、亏损
绿色: 安全、正面、盈利
蓝色: 中性、专业、可信
灰色: 辅助、次要信息
橙色/黄色: 警告、注意

配色工具

  • Coolors.co
  • Adobe Color
  • ColorHunt.co

色盲友好

  • 避免红绿搭配
  • 使用纹理/形状辅助区分

24.1.3 图表类型选择​

对比类 → 柱形图、条形图

趋势类 → 折线图、面积图

占比类 → 饼图、圆环图、树状图

分布类 → 散点图、直方图

关系类 → 散点图、气泡图

组成类 → 堆积柱形图、瀑布图

24.2 销售仪表盘​

24.2.1 仪表盘布局设计​

经典布局

┌───────┬───────┬───────┐
│ KPI卡片 │ KPI卡片 │ KPI卡片 │
│ 总销售额│ 增长率  │ 客户数  │
├───────┴───────┴───────┤
│    月度趋势图            │
│  (组合图:柱+线)         │
├───────────┬───────────┤
│  地区分布  │  产品排行   │
│  (地图)    │  (条形图)   │
└───────────┴───────────┘

F型布局

  • 左上:最重要信息(KPI)
  • 中间:趋势分析
  • 右侧:细分维度

24.2.2 KPI卡片制作​

方法1:文本框+格式

1. 插入文本框
2. 输入标题和数值
3. 设置字体:
   - 标题: 14pt 灰色
   - 数值: 36pt 蓝色粗体
4. 添加边框和背景色
5. 插入形状装饰

方法2:数据标注

1. 创建单数据点图表
2. 删除所有元素只保留数据标签
3. 调整标签格式和位置

动态箭头指示

# 同比增长率
=本月销售额/去年同期-1

# 条件格式图标集
>0: ↑ 绿色
=0: → 黄色
<0: ↓ 红色

24.2.3 销售趋势图​

组合图表

数据结构:
月份  销售额  目标  完成率
1月   1000   1200   83%
2月   1200   1200  100%
3月   1300   1200  108%

图表:
- 柱形图: 销售额 vs 目标
- 折线图(次坐标轴): 完成率

创建步骤

  1. 插入簇状柱形图
  2. 右键"完成率"系列 → 更改系列图表类型 → 折线图
  3. 勾选"次坐标轴"
  4. 美化:
    • 销售额柱:蓝色
    • 目标柱:灰色半透明
    • 完成率线:橙色,加粗

趋势线添加

右键销售额系列 → 添加趋势线 → 线性
勾选"显示公式"和"显示R²值"

24.2.4 产品排行榜​

数据准备

# 自动排序Top 10
=SORT(产品表, 2, -1)  # 按销售额降序

# 或使用LARGE+INDEX
排名  产品名                      销售额
1    =INDEX(产品,MATCH(LARGE(销售额,1),销售额,0))  =LARGE(销售额,1)
2    =INDEX(产品,MATCH(LARGE(销售额,2),销售额,0))  =LARGE(销售额,2)
...

条形图制作

1. 选中排名数据
2. 插入 → 条形图
3. 设置:
   - 反转类别轴(1名在上)
   - 添加数据标签
   - 调整条形间距(60%)
4. 美化:
   - 第1名:高亮色
   - 其他:渐变色

数据条替代方案

选中销售额列 → 条件格式 → 数据条
优势:在表格中直观显示

24.3 财务图表​

24.3.1 收入成本分析​

瀑布图

项目        金额
营业收入    10000
-成本       -6000
-费用       -2000
-税金        -500
净利润       1500

创建瀑布图

1. 插入 → 瀑布图
2. 设置汇总:
   - 右键"营业收入" → 设置为汇总
   - 右键"净利润" → 设置为汇总
3. 美化:
   - 收入:蓝色
   - 支出:橙色
   - 利润:绿色

堆积柱形图替代

数据结构需要调整:
项目   起始值  增减值
收入     0    10000
成本   10000  -6000
费用    4000  -2000
税金    2000   -500
利润    1500     0

24.3.2 利润结构分析​

圆环图+标注

数据:
成本   60%
费用   20%
税金    5%
利润   15%

制作:
1. 插入圆环图
2. 调整圆环宽度(30-40%)
3. 中心添加文本框:"利润率 15%"
4. 添加数据标签(百分比)

树状图(Microsoft 365)

层级数据:
├─ 成本 (60%)
│  ├─ 原材料 (40%)
│  └─ 人工 (20%)
├─ 费用 (20%)
│  ├─ 销售费用 (12%)
│  └─ 管理费用 (8%)
└─ 利润 (15%)

插入 → 树状图
自动显示层级占比

24.3.3 现金流量图​

组合图:流入流出净额

月份  现金流入  现金流出  净现金流
1月    5000     4000      1000
2月    6000     4500      1500
3月    4500     5000      -500

图表:
- 堆积柱形图:流入(蓝)+流出(红)
- 折线图(次坐标):净现金流(绿)
- 零刻度线加粗(突出正负)

24.3.4 财务指标仪表盘​

关键指标卡片

┌──────┬──────┬──────┬──────┐
│毛利率│净利率│ROE   │流动比│
│ 40%  │ 15%  │ 18%  │ 1.5  │
└──────┴──────┴──────┴──────┘

同比对比

使用迷你柱形图:
本年: ████████ 40%
去年: ██████   35%  ↑5pp

24.4 KPI监控图表​

24.4.1 仪表盘指针图​

半圆环图模拟

数据准备:
类别      值
已完成    75
未完成    25
隐藏部分  100  # 用于隐藏下半圆

步骤:
1. 插入圆环图
2. "隐藏部分"设置为无填充
3. 圆环图选项 → 首扇区角度 270°
4. 调整圆环内部大小 40%
5. 中心添加文本框:完成率75%

添加指针(可选):
- 插入形状 → 箭头
- 旋转到对应角度

刻度标注

手动添加文本框:
0%   25%   50%   75%   100%

24.4.2 子弹图​

组合图表模拟

数据:
实际: 75
目标: 100
优秀: 120

图表:
1. 实际:条形图(深蓝)
2. 目标:条形图(浅灰)
3. 优秀:散点图(标记目标线)

Excel子弹图制作

数据结构:
        实际  差额  目标标记
实际值   75    25      ●

1. 插入堆积条形图
2. "差额"设置为无填充
3. 添加次坐标轴散点图标记目标

24.4.3 热力图​

条件格式色阶

数据:
      Q1   Q2   Q3   Q4
北京  100  120  110  150
上海   80   90  100  110
广州   90   85   95  100

设置:
选中数据 → 条件格式 → 色阶 → 绿-黄-红

数据条美化

条件格式 → 数据条 → 管理规则 → 编辑
- 仅显示数据条
- 负值另设颜色
- 最小值/最大值固定

24.4.4 完成进度图​

简单进度条

使用REPT函数:
=REPT("█", A1/10) & REPT("░", 10-A1/10)

示例:
75% → ███████░░░

条形图进度

数据:
已完成  75
未完成  25

条形图 → 堆积条形图 → 删除图例和坐标轴

Harvey球(圆形进度)

使用饼图:
已完成  75%
未完成  25%

设置:
- 顺时针从12点开始
- 已完成:蓝色
- 未完成:浅灰
- 删除所有标签

24.5 高级图表技巧​

24.5.1 动态图表​

切片器控制

1. 数据转换为表格(Ctrl+T)
2. 插入 → 切片器 → 选择字段
3. 基于表格创建图表
4. 切片器自动控制图表

优势:
- 交互式筛选
- 多维度切换
- 美观直观

下拉框控制

1. 数据验证创建下拉列表
2. 使用INDEX/MATCH引用选择值
3. 图表数据源引用公式结果

示例:
下拉框选择地区 → 图表显示该地区数据

滚动条控制时间窗口

1. 开发工具 → 插入 → 滚动条
2. 链接单元格:$A$1
3. 使用OFFSET函数:
   =OFFSET(数据!$A$1, $A$1, 0, 12, 1)
4. 图表引用OFFSET结果

效果:滚动条控制显示12个月窗口

24.5.2 组合多图表​

拼图式仪表盘

技巧:
1. 创建多个小图表
2. 删除图表区背景
3. 去除边框
4. 对齐分布
5. 组合为整体

工具:
绘图工具 → 对齐 → 左对齐/顶端对齐
绘图工具 → 对齐 → 横向分布/纵向分布

叠加图表

用途:在图表上叠加另一图表

示例:
- 折线图背景上叠加柱形图
- 地图上叠加气泡图

方法:
1. 创建第一个图表
2. 创建第二个图表
3. 第二个图表背景设置为无填充
4. 调整大小和位置对齐

24.5.3 图片填充​

柱形图填充图片

1. 右键数据系列 → 设置数据系列格式
2. 填充 → 图片或纹理填充
3. 选择图片
4. 堆叠选项:
   - 堆叠:图片重复堆叠
   - 堆叠并缩放:单个图片

应用场景

产品销量图:用产品图片填充柱形
人口统计:用人物图标填充
财务报表:用货币图标填充

24.5.4 数据标签格式​

自定义数据标签

右键数据标签 → 设置数据标签格式
标签选项:
□ 系列名称
☑ 类别名称
☑ 值
□ 百分比
☑ 显示引导线

数字格式:
- 货币:¥#,##0
- 百分比:0.0%
- 自定义:"同比"0.0%"↑"

标签包含单元格值

右键数据标签 → 设置数据标签格式
标签选项 → 标签包括 → 单元格中的值
选择包含文字说明的单元格区域

用途:为每个数据点添加个性化注释

24.6 行业应用案例​

案例1:电商GMV仪表盘​

布局

┌──────────┬──────────┬──────────┐
│   GMV    │  订单量   │  客单价   │
│¥1,234万  │  5,678   │  ¥217    │
│  +15.6%↑│  +8.3%↑ │  +6.8%↑ │
├──────────┴──────────┴──────────┤
│        GMV趋势(近30天)          │
│      (面积图+移动平均线)        │
├──────────┬───────────────────────┤
│品类占比  │     TOP10商品         │
│(圆环图)  │   (横向条形图)        │
└──────────┴───────────────────────┘

关键图表

# GMV趋势
- 面积图:每日GMV
- 移动平均线:7日均线
- 目标线:计划GMV

# 品类圆环图
- 中心:总GMV
- 扇区:各品类占比
- 数据标签:金额+百分比

案例2:人力资源仪表盘​

人员结构

- 饼图:部门人数分布
- 柱形图:各部门男女比例
- 金字塔图:年龄结构
- 散点图:工龄vs薪资

人员流动

- 瀑布图:
  期初人数 + 入职 - 离职 - 调岗 = 期末人数

- 折线图:
  月度离职率趋势

招聘漏斗

简历筛选  1000人  100%
初试       500人   50%
复试       200人   20%
Offer     100人   10%
入职        80人    8%

使用条形图堆积显示转化率

案例3:供应链监控​

库存预警

仪表盘指针图:
- 绿色区: 库存正常(50-100%)
- 黄色区: 库存偏低(20-50%)
- 红色区: 库存不足(<20%)

当前库存水平:35%(黄色警告)

周转率趋势

组合图:
- 柱形图:月度销量
- 折线图(次坐标):库存周转天数

目标线:周转天数<30天

交货及时率

项目时间线(甘特图):
项目A  ████████████░░  95%
项目B  █████████░░░░░  65% ⚠
项目C  ██████████████ 100% ✓

24.7 图表导出与分享​

24.7.1 导出为图片​

高质量导出

方法1:另存为图片
右键图表 → 另存为图片 → PNG

方法2:复制为图片
选中图表 → 复制 → 选择性粘贴 → 图片(增强型图元文件)

方法3:截图工具(Windows)
Win + Shift + S → 截取图表区域

分辨率设置

文件 → 选项 → 高级 → 图像大小和质量
设置:"不压缩文件中的图像"
导出分辨率:220-330 ppi

24.7.2 嵌入PPT/Word​

动态链接

Excel中复制图表
PPT/Word中粘贴选项:
- 保留源格式并嵌入工作簿
- 保留源格式并链接数据
- 使用目标主题并嵌入工作簿
- 使用目标主题并链接数据

推荐:链接数据(数据更新时PPT自动更新)

静态图片

粘贴选项:图片
优势:文件小,不可编辑
劣势:无法更新数据

24.7.3 在线分享​

Excel Online

文件 → 共享 → OneDrive
生成共享链接
设置权限:仅查看/可编辑

Power BI嵌入

Excel数据 → Power BI Desktop
创建交互式仪表盘
发布到Power BI服务
生成嵌入代码

24.8 最佳实践​

24.8.1 设计检查清单​

内容完整性

  • 标题清晰明确
  • 数据来源标注
  • 时间范围说明
  • 坐标轴标签完整
  • 图例位置合理
  • 关键数据标注

视觉美观性

  • 配色协调统一
  • 字体大小适中
  • 元素对齐工整
  • 留白恰当
  • 无多余元素

数据准确性

  • 数据源正确
  • 计算公式无误
  • 数值单位统一
  • 百分比正确
  • 时间轴无错

24.8.2 常见错误​

错误1:3D效果滥用

问题:难以准确读数,视觉误导
解决:使用2D图表

错误2:双Y轴刻度不当

问题:刻度设置不合理导致视觉欺骗
解决:明确标注单位,合理设置刻度范围

错误3:饼图分类过多

问题:超过7个分类难以区分
解决:合并小类为"其他",或改用条形图

错误4:颜色使用混乱

问题:过多颜色,无统一规范
解决:限制3-5种主色,建立配色规范

本章小结​

核心要点

  1. 设计原则:简洁、清晰、准确
  2. 仪表盘:KPI卡片、趋势图、排行榜组合
  3. 财务图表:瀑布图、圆环图、现金流图
  4. KPI监控:仪表盘、子弹图、热力图、进度图
  5. 高级技巧:动态图表、组合图表、图片填充

图表类型速查

用途推荐图表
KPI展示卡片、仪表盘指针图
销售趋势组合图(柱+线)
产品排行横向条形图
利润结构瀑布图、圆环图
完成进度子弹图、进度条
多维对比热力图、散点图
占比分析饼图、树状图

学习建议

  • 多观察优秀商业报表
  • 建立个人图表模板库
  • 注重数据故事化
  • 保持风格一致性

下一步学习

  • 第25章:Excel财务函数应用
  • 第26章:Power Query数据处理
  • 第27章:Power Pivot数据建模

思考练习​

  1. 制作一个月度销售仪表盘,包含KPI卡片、趋势图、排行榜
  2. 用瀑布图展示公司收入到利润的构成
  3. 设计一个项目进度监控仪表盘
  4. 制作动态图表,用切片器控制不同维度
  5. 优化一份现有的图表,使其更专业美观

练习提示

  • 先在纸上画布局草图
  • 确定配色方案
  • 注意元素对齐
  • 添加必要标注说明