王佩丰24节Excel学习笔记——第二十一讲：经典Excel动态图表实现原理

【以 Excel2010 系列学习，用 Office LTSC 专业增强版 2021 实践】

---

【本章技巧】

1. 使用公式记得要绝对引用；
2. 定义好的名称，引用时要使用文件名+!+定义名，不能写错，否则无结果；
3. 利用offset 函数解决数据透视表做好后，原数据值添加了数据，导致数据透视表无法更新问题。
4. 记住这个公式，取数据透视表中所有的数据，=OFFSET($A$1,0,0,COUNTA($A:$A),11)，使用时把它定义名称公式里即可重复使用。
   解释：=OFFSET($A$1,0,0,COUNTA($A:$A),11)
           以A1为基准，向下0行，向右0列，意思是包含表头，用0就是不用动，取多少行呢？用函数counta($A:$A)，算一下非空值行，取多少列是固定的，此处比如说是11列。
5. 使用offset做动态图表取值。

---

一、动态图表实现原理

1、理解图表中的数据系列

点空白处，插入折线图，在空白折线图上点右键，选择数据源，左侧图列项有两个数据源系列，即彩盒系列和宠物用品系列，每个系列对应一列或一行数据。基于这一对一的关系，可以对数据源进行动态处理。

 

2、手工修改系列中的数值与坐标轴数据

选中图表，右键选择数据源，可以对图例项进行添加或编辑或删除操作，也可以对水平分类轴标签进行编辑数据处理。

3、小试牛刀-利用IF创建简单的动态图表  

步骤：

1、打开Excel，找到文件-选项-自定义功能，勾选开发工具

2、在任意位置添加两个复选框

3、点击右键，可对复选框进行文本内容修改，或都移动位置

4、这里设置好名称好，点设置控件格式，指定一个单元格，看选中与不选中的值各是什么

5、基于此操作可使用IF判断，如果为True，就显示哪些值，如果为False就不显示值

6、写判断语句：=IF($G$2,$B$2:$B$13,$F$2:$F$13)，记得使用绝对引用

7、通过自定义名称，将 if 语句公式定义名称

8、分别定义好两个数据系列，此处为：彩盒、宠物用品

9、点空白处插入折线空白图表，在图表上点右键，选择数据源，添加彩盒和宠物用品系列数据引用的位置，此处是引用定义的名称，注意此处位置要写文件名!定义名称，写错将不会有数据!

10、完成添加后如下图所示，可以美化一下，将复选框拿到图列旁边，可去掉复选框的中文字，然后把图置于底层，就可显示复选框。

二、利用offset函数与控件创建动态图

1、Offset函数概述

        语法：以某个点为基准，下移N行，右移N列，取N行，取N列

        类似经纬度：东京北纬30度，向下多少，再向右多少？

2、Offset函数的动态引用示例

        例：数据透视表自动更新，使用自定义名称，利用offset函数写好数据区域公式，插入数据透视表时，直接引用定义名称，生成的数据表如果数据源有变化，直接右键刷新即可。

        记住这个公式，取数据透视表中所有的数据，=OFFSET($A$1,0,0,COUNTA($A:$A),11)，使用时把它定义名称公式里即可重复使用。
        解释：=OFFSET($A$1,0,0,COUNTA($A:$A),11)
        以A1为基准，向下0行，向右0列，意思是包含表头，用0就是不用动，取多少行呢？用函数counta($A:$A)，算一下非空值行，取多少列是固定的，此处比如说是11列。

        案例：定义名称：数据区域，公式为：=OFFSET($A$1,0,0,COUNTA($A:$A),11)

3、动态图表1 永远返回最后10行数据

4、动态图表2 通过控件控制图表数据

步骤：

1、点开发工具，插入2个滚动条

2、设置滚动条控制属性

3、写公式，定义名称，链接数据源