📌 阶段五 · 报表可视化 · 第21讲 经典Excel动态图表实现原理 · 约 50 分钟

用 OFFSET 与名称做会联动的图表，切换月份图表自动刷新

学习目标

  • 理解图表的数据系列：图表只是把某些单元格区域画了出来
  • 会用复选框控件的单元格链接（TRUE/FALSE）配合 IF 切换数据列
  • 掌握 OFFSET 函数五个参数的含义与动态区域写法
  • 理解动态图表核心原理：图表引用名称，名称背后是会变的公式
  • 完成仪表盘的 KPI 区与月份联动数据区，并在 Excel 中按指引做出会动的图表

知识点讲解

先看懂：图表的数据系列是什么

图表本质上只是把一组单元格区域「画」出来。选中订购日期和两个产品列插入折线图后，右击图表-选择数据，会看到 Excel 自动拆好的「图例项（系列）」：每个系列就是一列数据。

其实空单元格也能起步：点一个空单元格，插入折线图得到空图表，右击-选择数据-图例项（系列）-添加：系列名称写「彩盒」，系列值只选 B2:B13 这一列（注意：系列里创建是单列引用，不能一次框两列）；同理添加第二个系列；水平（分类）轴标签选 A2:A13 的日期。

看懂这一点，就抓住了动态图表的本质：只要让「被引用的区域」动起来，图表就动了。

💡 提示:

  • 添加系列时名称、系列值分开指定；系列值必须是单列（或单行）

控件之一：复选框与单元格链接

Excel 有一组「窗体控件」，先把它请出来：文件-选项-自定义功能区，右侧勾选「开发工具」。然后开发工具-插入-复选框，在表里拉出两个复选框，右键-编辑文字改成「彩盒」「宠物用品」。

关键一步：右键复选框-设置控件格式-控制-单元格链接，选中 G2。从此勾选复选框，G2 显示 TRUE；取消勾选，显示 FALSE。控件变成了一个开关，而 TRUE/FALSE 正好能喂给 IF 函数。

💡 提示:

  • 链接单元格会显示 TRUE/FALSE，位置别被其他数据覆盖

用 IF 让数据列「开」与「关」

有了开关，就写一条会看开关的公式：=IF($G$2,$B$2:$B$13,$F$2:$F$13)。G2 为 TRUE 时返回 B2:B13 的整列数据；为 FALSE 时返回 F2:F13 的空白数据列。注意区域要用绝对引用，否则定义名称后引用会跑偏。

这条公式的价值不在单元格显示，而在下一步——把它装进「名称」里。

=IF($G$2,$B$2:$B$13,$F$2:$F$13)

G2 是复选框的链接单元格：勾选返回 B 列数据区，取消返回 F 列空白区；条件参数直接写 TRUE/FALSE 均可生效

定义名称：把公式装进图表

复制上面的公式（按 ESC 退出编辑），点公式-定义名称：名称输入「彩盒」，引用位置粘贴 =IF($G$2,$B$2:$B$13,$F$2:$F$13)，确定；宠物用品同理再定义一个。之前放公式的 G8 单元格可以删掉了。

然后回到图表：右击空图表-选择数据-添加，系列名称写「彩盒」，系列值输入 =Sheet1!彩盒（注意要带工作表名前缀，感叹号是英文状态），确定后对应的折线出现了。

这就是动态图表的完整机关：图表引用名称 → 名称背后是一段公式 → 公式由控件开关控制 → 点复选框，图表线即隐即现。收尾记得统一纵坐标（右键纵坐标-设置坐标轴格式-设最小值最大值，数字小数位数设 0），再把控件拖到图例前、与图表组合，方便整体移动。

=Sheet1!彩盒

图表系列值引用名称的固定写法：等号-工作表名-感叹号-名称；把 Sheet1 换成你的实际表名

OFFSET 函数：会移动的引用

=OFFSET(reference,rows,cols,[height],[width])，中文记法：以某个单元格为基准，下移 n 行，右移 n 列，取 n 行 n 列。

比如 =OFFSET(A1,2,1,1,1)：从 A1 出发下移 2 行、右移 1 列到 B3，取 1 行 1 列，返回 B3 的值。

它的兄弟用法是做「自适应数据区域」：=OFFSET($A$1,0,0,COUNTA($A:$A),11)——COUNTA($A:$A) 数出 A 列有多少个非空单元格，作为高度；不管表格下面加多少行，这个区域始终罩住全部数据。做法：公式-定义名称-名称写「数据区域」，引用位置贴入公式；然后选中任意有数据的单元格插入数据透视表，表/区域直接填「数据区域」。此后在原表下方追加行，刷新透视表，新数据自动进来。

=OFFSET(A1,2,1,1,1)

基准 A1，下移 2 行右移 1 列，取 1 行 1 列——返回 B3 的值

=OFFSET($A$1,0,0,COUNTA($A:$A),11)

不偏移、从 A1 起取「A 列非空个数」行、11 列宽——数据加行区域自动长高，适合喂给透视表

永远显示最后 10 行

数据每天追加，图表只想看最近 10 行？规律：总行数减 10 就是该下移的行数。以 B1 为基准（表头在 1 行、共 17 行数据），写 =OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)：COUNTA 数出 17 个非空（含表头），减 10 得下移 7 行，从第 8 行起取 10 行——正好是最后 10 行。

操作三步：①公式-定义名称「成交量」，引用位置贴入上式；②插入折线图-右击-选择数据-添加系列，系列值填 =Sheet2!成交量；③日期轴同理定义名称「日期」：=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)，在「水平（分类）轴标签」里填 =Sheet2!日期。以后追加数据，图表永远滚动显示最新 10 行。

=OFFSET($B$1,COUNTA($B:$B)-10,0,10,1)

基准 B1，下移「非空数-10」行，取 10 行 1 列——永远锁定最后 10 个数据

=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)

日期列的对应区域，作为图表的水平轴标签，和成交量一一对应

滚动条控件：人工驾驶 OFFSET

复选框是开关，滚动条则是滑块调参。开发工具-插入-滚动条，拉出两个；第一个右键-设置控件格式-控制：最小值 1，单元格链接 $D$2（它控制 OFFSET 从哪行开始看）；第二个同样操作链接 $D$4（控制一次看几行）。

然后定义名称「成交量」：=OFFSET($B$1,$D$2,0,$D$4,1)；名称「日期」：=OFFSET($A$1,$D$2,0,$D$4,1)。插入柱形图，系列值填 =Sheet3!成交量，轴标签填 =Sheet3!日期。

效果：拖第一个滚动条，窗口起点逐行下滑；拖第二个，图表展示的数据行数变多变少。OFFSET 的两个参数被滚动条接管，图表成了可以人工驾驶的取景框。

=OFFSET($B$1,$D$2,0,$D$4,1)

$D$2 是滚动条 1 的链接（起点位移），$D$4 是滚动条 2 的链接（窗口行数）——两个控件实时改写引用区域

实战演练

本讲把「仪表盘」从一张空表变成系统的驾驶舱：上半部分是三块 KPI（总销售额、待补货商品数、应收总额），全部公式引用其他工作表；下半部分搭「当前月份」联动数据区——下拉一换，当月销售数字立刻刷新，这正是数据有效性驱动动态图表的最简形态。控件与 OFFSET 命名的完整画图动作与桌面版不同，在线部分把数据机关做扎实，Excel 操作指引放在步骤末尾。

  1. 搭仪表盘骨架 — 新建「仪表盘」表（或用现有仪表盘表），A1 输入大标题「进销存仪表盘」，加粗放大。A3、B3、C3 分别写 KPI 名称：总销售额、待补货商品数、应收总额。
  2. 写 KPI 公式 — A4 输入 =SUM(出库单!I2:I400)；B4 输入 =COUNTIF(库存台账!J2:J31,"⚠补货")；C4 输入 =SUM(应收账款!E2:E13)。三块 KPI 全部引用底层表：底层单据一变，仪表盘数字即时更新（列号请按你工作簿的实际表头微调）。
  3. 做月份选择下拉 — 在 E3 做数据有效性下拉（数据-数据验证-序列），来源填 =销售分析!$A$2:$A$13（即 1～12 月）。这个单元格就是仪表盘的「月份旋钮」。
  4. 搭月份联动数据区 — A6:C6 写表头：月份、出库金额、出库数量。A7 输入 =E3；B7 输入 =SUMPRODUCT((MONTH(出库单!$B$2:$B$400)=$A$7)*出库单!$I$2:$I$400)；C7 输入 =SUMPRODUCT((MONTH(出库单!$B$2:$B$400)=$A$7)*出库单!$G$2:$G$400)。切换 E3 的月份，B7、C7 跟着变——给这一小块区域画个柱形图（或大字卡片），就是最朴素的动态图表。
  5. OFFSET 小实验：理解偏移 — 在 H2 输入 =OFFSET(销售分析!$B$2,5,0)，返回的是 7 月出库金额（B2 是 1 月，下移 5 行）；H3 输入 =OFFSET(销售分析!$B$2,E3-1,0)，让偏移量听月份下拉指挥：E3 选 3 就返回 3 月的金额。两条小公式验证了 OFFSET「基准+偏移」的核心逻辑。
  6. Excel 操作指引：复选框切换数据列 — 在本地 Excel 中：开发工具插入两个复选框「入库金额」「出库金额」，链接到 G2；公式-定义名称「金额曲线」，引用位置 =IF($G$2,销售分析!$B$2:$B$13,销售分析!$C$2:$C$13)；插入折线图-选择数据-添加系列，系列值输入 =Sheet1!金额曲线（换成你的表名）。勾选/取消复选框，两条曲线即隐即现。
  7. Excel 操作指引：OFFSET+滚动条看最近 6 个月 — 定义名称「最近6月出库」，引用位置 =OFFSET(销售分析!$B$2,COUNTA(销售分析!$B:$B)-7,0,6,1)；定义名称「最近6月月份」，引用位置 =OFFSET(销售分析!$A$2,COUNTA(销售分析!$A:$A)-7,0,6,1)；插入柱形图，系列值 =销售分析!最近6月出库、轴标签 =销售分析!最近6月月份。想更炫就加滚动条链接 D2 控制起点：公式改为 =OFFSET(销售分析!$B$2,$D$2,0,6,1)。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

配套练习文件:lecture-21.xlsx(见本页底部「附件下载」)。

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 仪表盘三块 KPI 公式正确，底层单据变动时数字跟着更新
  • ☐ 切换月份下拉，联动数据区的出库金额和数量立刻刷新
  • ☐ 能口述 OFFSET 五个参数各自的作用（基准、下移、右移、行高、列宽）
  • ☐ 能说清动态图表核心原理：图表引用名称，名称背后是会变的公式或区域
  • ☐ 在 Excel 中至少按一种指引（复选框版或滚动条版）做出会动的图表