Excel财务小技巧之23「Excel可视化收官篇:交互看板 + 预实分析 + 敏感性分析」

前面我们学习了Excel的基础图表和进阶图表，学完之后，可以完成70%以上结构、占比、趋势等财务分析工作。

本篇进入Excel可视化的收官篇，从静态展示升级到动态交互和分析实战。

一、财务交互式仪表盘（动态可视化核心）

1.交互式仪表盘基础

（1）动态切换的常用Excel工具对比

在开始介绍交互式看板前，先把很多人困惑的数据验证、切片器、表单控件说清楚 —— 这三者都能做 “动态切换”，但能力与场景完全不同：

【三者相同点】

都可实现对筛选条件的选择，如：年份 / 部门 / 区域等

都能通过选择不同的筛选条件，让单元格数值变化

配合公式，都能让图表自动刷新

【三者核心差异】

数据验证：

最简单，直接点选单元格下拉菜单即可，稳定不报错，适合轻量动态图；但只能在单元格内，没有动态按钮的可视化效果，外观不像控制器。

切片器：

切片器的特点是视觉直观、操作便捷，但它只能直接控制数据透视表，无法直接驱动普通单元格公式或非透视表图表。

要实现切片器筛选图表的效果，需要通过 “透视表做中转” 的方式，间接联动，具体操作流程如下：

Step1：切片器直接控制数据透视表

比如用 “客户付款方式” 切片器，筛选左上角的销售数据透视表，透视表数据会随切片器选择实时更新。

Step2：用公式把透视表数据同步到普通数据表

在 G-J 列的数据区域，用=B6这类简单引用公式，将透视表的数据同步出来。这样透视表一变，普通数据表也会自动刷新。

Step3：普通图表绑定引用后的数据表

最后，把下方的柱状图绑定到 G-J 列的普通数据表上。这样就形成了完整的联动链：:切片器 → 透视表 → 引用公式 → 数据表 → 图表

表单控件：

一次做好、永久复用，切换年份/区域/部门时，图表自动刷新，不用重复改数据、重做图；可悬浮排版、支持滑动调节，正式汇报最专业

【三者的选择思路】

简单动态图 → 数据验证 + 公式

透视表筛选 → 切片器

专业仪表盘 → 控件 + 公式

本文着重介绍表单控件，数据验证和切片器可参考往期文章：

Excel财务小技巧之10「数据透视表2-从汇总到深度分析的核心技巧」

Excel财务小技巧之13「数据验证+条件格式」

（2）如何调出表单控件？

先进入文件→ 选项 → 自定义功能区

在右侧主选项卡中，勾选开发工具，点击确定，会发现顶部菜单栏出现开发工具，即可使用插入控件功能

2.表单控件具体介绍

（1）财务分析常用表单控件

一是组合框（下拉菜单）

用途：切换年份、部门等单选场景，是仪表盘最核心控件。

二是滚动条/数值调节钮

用途：快速调整数字（如：预算比例、增长率、单价），配合敏感性分析使用；两者可以完全替代

（2）表单控件的插入方式和核心逻辑

【案例】不同门店销售数据

如何插入控件？

开发工具→插入→表单控件→组合框（窗体控件）

右击控件，设置控件格式：

一是控件大小的设置：

二是在“控制”界面设置链接数据源和显示位置：

其中：

数据源区域即下拉菜单的来源

单元格链接是设置用来显示实际选择的单元格

下拉显示项数：设置下拉菜单最多显示几行，一般填选项总数即可，比如 5 个选项就填5。

核心逻辑：控件返回的是 “序号”，不是文本

比如：我们在下拉菜单中选择朝阳大悦城店，右侧的单元格链接会自动刷新为2，代表系列的第二个选项

（3）动态仪表盘底层核心公式

在了解完表单控件的底层逻辑之后，我们来学习一下动态仪表盘的底层核心公式，以实现基础数据随“单元格链接”变动而变动

【公式一】使用OFFSET+MATCH 联动下拉取数

作用：根据下拉菜单选择的内容，自动抓取对应行/列数据。

基础公式结构：

=OFFSET(数据起始区域,MATCH(下拉单元格,索引区域,0)-1,0,取数行数,取数列数)

举例：

=OFFSET($B$5,$G$6,MATCH(B$5,$H$5:$J$5,0)-1)#使用OFFSET偏移函数#通过下拉菜单控制向下偏移的行数#使用match函数控制偏移的列数

【公式二】SUMIFS

具体操作可见往期文章

Excel财务小技巧之8「4大函数搞定财务多条件汇总」

3.动态仪表盘案例——修改年月实现数据自动刷新

（1）预期功能

通过两个表单控件（年份、月份下拉框），一键切换年份、月份，让各门店、各品类销售数据和对应的图表自动刷新

（2）核心逻辑

一是控件层：输出动态参数

两个控件分别控制年份和月份，选择后会把 “序号” 写入指定单元格（F4、G4），作为公式的动态条件。

二是数据层：SUMIFS 多条件取数

用通用公式，同时匹配门店 + 品类 + 年份 + 月份4 个条件，实时抓取对应数据：

=SUMIFS(手机销售数据!$M:$M,手机销售数据!$E:$E,$H4,手机销售数据!$F:$F,I$3,手机销售数据!$A:$A,CHOOSE($F$4,2024,2025),手机销售数据!$C:$C,$G$4)

CHOOSE($F$4,2024,2025)：

把控件返回的“序号1和2”，翻译成“年份 2024/2025”，直接作为 SUMIFS 的年份筛选条件，避免嵌套多层 IF。

三是展示层：图表自动联动

柱状图直接绑定公式生成的数据区域，切换控件后，数据和图表同步刷新，实现 “选年月→刷数据→看图表” 的全链路动态展示。

（3）小Tips：组合与置于顶层

将表单控件置于图表的顶层：

右击表单控件→点击排序→点击置于顶层

组合：

将图表与空间组合为一个整体，防止图表拖动后，表单与图表分离

按Ctrl，选中表单控件与图表

右击，点击组合选项下的组合

二、预实分析可视化

1.预算达成率条件格式

（1）实现效果

预算达成率（实际完成/预算）小于60%时，标记浅红色；60%~80%之间时，标记浅黄色；80%~100%时，标记浅蓝色

（2）操作步骤

条件格式→突出显示单元格规则→小于

【核心点】条件的顺序不能反（最窄的范围放在最上面）

2.预实分析双柱图

（1）实现效果

通过双柱，展现预算和实际数的对比，并显示预算达成率

（2）操作步骤

Step1：准备基础数据—by产品的预算数、实际数、预算达成率

其中：预算达成率=实际/预算

Step2：插入簇状柱形图

选中产品、预算、实际3列，插入簇状柱形图

修改标题、修改图例位置、清除网格线

效果如下：

Step3：修改预算对应柱的样式

无填充+黑色边框

效果如下：

Step4：修改间隙重叠和系列宽度

Step5：添加数据标签

将预算完成率添加到数据标签

勾选单元格中的值→数据标签区域选择预算达成率对应区域→取消勾选值

结果如下：

Step6：适当调整标签位置和图表大小

最终效果：

3.预算达成子弹图

（1）实现效果

通过同时展示实际值、预算 60% 预警线、预算 80% 及格线及预算 100% 目标线，让预算达成情况一目了然。

超额完成：蓝色实际条完全突破黑色目标线 ✅，代表超额达成（如产品 E、G）。

达标状态：蓝色实际条止于黑色目标线以内，且未露出黄色背景，代表达标（如产品 B、C、H）。

预警区间：

露出黄色背景 → 达成率 60%~80%，存在执行风险（如产品 A、F）；露出红色背景 → 达成率 <60%，严重滞后，需重点跟进整改。

（2）操作步骤

Step1：基础数据准备

预算（80%）和预算（60%）分别代表预算（100%）的80%和60%

Step2：插入簇状条形图

选中B到F列，插入簇状条形图

Step3：修改标题、调整图例位置、删除网格线、逆序类别

结果如下：

Step4：修改系列重叠和间隙宽度

系列重叠100%；间隙宽度 50%~70%

Step5：修改条形样式

预算（100%）为黑色边框无填充

预算（80%）、预算（60%）、实际分别为无边框+黄色、橙色、蓝色

Step6：添加数据标签并调整图表大小

其中：预算达成率来自单元格中的值（预算达成率列）

最终效果

三、本量利分析与敏感性分析（决策级可视化）

财务人员在日常工作中，除了核算历史数据，也需要做一些向前看的测算。比如新产品定价多少合适、销量达到多少才能保本、某个成本项目变动会对最终利润产生多大影响。

本篇通过一个单产品的例子，把本量利分析和敏感性分析放在Excel里实现。文中的图表都是直接用Excel原生功能制作，不需要VBA编程基础。

1.本量利分析

（1）案例准备与基础数据

假设公司计划推出一款产品，相关数据预估如下：

在Excel中建立如下数据区域，后续图表将引用这些单元格：

进一步构建绘图数据：

先明确销量对应点，为0和预计销量（两点即可确定一条直线）

总收入=单价（200元）*销量

固定成本保持600,000元不变

总成本=固定成本+变动成本（80元）*销量

（2）创建图表

Step1：选中上述绘图区域，插入的图表类型选择XY散点图→带平滑线的散点图→第2个图表

Step2：优化图表：修改标题、调整图例位置、删除网格线、调整Y轴的数据单位（减少不必要的数值显示）

结果如下：

Step3：调整线条颜色

固定成本为灰色虚线，总成本为橙色，收入为蓝色

Step4：插入盈亏平衡点标记

从图中可以看出，盈亏平衡销量是5000件，我们单独插入一组数据：

选中原图表→选择数据→点击添加按钮→编辑数据系列

输入系列名称、X轴、Y轴

Step5：更改图表类型并修改盈亏平衡点样式

2.敏感性分析

（1）基础数据表

【基础数据逻辑】

继续前面的本量利分析案例，搭建如下基础数据表

原始数据代表变动前的各项金额，变动数值代表具体的变动金额

变动百分比=变动金额/原始数值

结果：

对于单价/单位变动成本/固定成本/销量等自变量来说：

结果=原始+变动数值

毛利=（变动后单价-变动后单位变动成本）*变动后数量-变动后固定成本总额

盈亏平衡点=变动后固定成本/（变动后单价-变动后单位变动成本）

【表单控件设计】

在开发工具选项卡插入数值调节钮或者滚动条，调整大小、起始/终止值/步长，设置单元格链接

（2）可视化图表

【实现效果】

随着表单控件变化，图表随之联动；同时图中需要显示盈亏平衡点对应销量和收入

【核心思路】

基础数据表数据来自左表的敏感性分析，以实现表间联动

另外插入两条辅助线：

竖线：X轴对应盈亏平衡点销量；Y轴取0和预计收入

横线：Y轴对应盈亏平衡点收入；X轴取0和预计收入

四、商务汇报可视化规范

前面主要讲了各类图表及具体操作，这部分我们来明确一下商务汇报的可视化规范。

1. 统一输出标准

（1）字体规范

如版权允许，可以使用微软雅黑；或者使用思源黑体/等线/阿里巴巴普惠体/苹方-简等

标题：加粗（字号12-14）

坐标轴/数据标签：常规（字号9-10）

（2）配色建议

红色（RGB 192,0,0）：仅用于超预算、负增长、严重逾期。

黄色（RGB 255,192,0）：仅用于接近预算上限、临期提醒。

主色（深蓝：RGB 31,78,121）：代表收入、资产、完成项——稳健、客观。

强调色（橙色：RGB 237,125,49）：代表增长率、重点单品、需关注项——醒目但不刺眼。

预警色：原则：一张图里红色不要超过3处，多了等于没预警。

（3）删掉Excel的默认审美，保留极简主义

极简主义：删掉所有Excel的“默认审美”。

❌ 删掉：图表区默认的灰色边框、背景色。

❌ 删掉：网格线（如果非要留，把透明度调到80%，改成虚线）。

❌ 删掉：多余图例（如果只有一条折线，直接把图例按Delete删掉，标题里写清楚）。

2.其他注意事项

（1）标签太多，堆在一起

3个解决方式：

字号改成8~9

柱状图标签放在外侧；折线图放在靠上

只保留最大值、最小值、最后一个点的标签，中间的删掉

（2）坐标轴数字太长

修改单位到：万/百万

具体操作：

双击坐标轴数字 → 数字 → 自定义格式代码输入：0.0,"万" 或 0,,"百万"。

或者直接在基础数据上修改

（3）粘贴为图片之后清晰度低

错误做法：直接截图（压缩画质）。

正确做法：在Excel里选中图表 → Ctrl + C 复制 → 在微信/PPT里 → 右键 → 粘贴选项 → 图片（或保留源格式嵌入）。

进阶操作：复制后，选择性粘贴为 “增强型图元文件(EMF)” ，放大缩小不失真。

【本期总结】

Excel可视化部分，我们讲了基础图表、各类高阶图表、交互看板、预实分析、本量利分析等

图表选择规则：

【下期预告】

下期开始，我们进入Excel自带的Power Query和Power Pivot功能的学习