Lumos学习王佩丰Excel第二十一讲：经典Excel动态图表实现原理

一、动态图表实现原理

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

在Excel图表中，系列指的是图表中的数据集合，它通常代表着一个数据源。每个系列都可以包含多个数据点，这些数据点在图表中以特定的形式展现，如柱状图中的柱子，折线图中的线条等。一个图表可以有一个或多个系列，每个系列都可以独立地展示数据，也可以与其他系列的数据进行比较。简单理解，一个系列对应一个图例。

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

• 创建图表

• 系列数值

• 坐标轴数据

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

• 导航栏添加开发工具：选项卡——自定义功能区

• 目标图表

• 创建图表

1. 插入复选框，右键点击即可打开编辑状态，可通过左键移动位置
2. 设置控件格式：点击右键设置“控件格式”，选中一个单元格作为单元格链接，则发现勾选复选框显示TRUE，不勾选复选框显示FALSE
3. 进行函数链接：通过IF函数，实现单元格链接与单元格数据的联动，将IF函数公式剪切到“公式-定义名称-引用位置”，目的是将“彩盒”这个名称与IF函数连接起来。注意，需要全部加上绝对引用

   

   

   

4. 插入折线图，将公式嵌入系列值
5. 固定坐标轴

   

   

6. 调整细节

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

1、Offset函数概述

OFFSET是Excel中的函数，在Excel中，OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

OFFSET（reference,rows,cols,[height],[width]）

• 引用（reference）：基于其偏移量的引用。
• rows：需要左上角单元格引用的向上（负数）或向下（正数）行数。
• cols：需要结果的左上角单元格引用的从左到右的列数，右侧为正数。
• 高度：需要返回引用的行高。
• 宽度：需要返回引用的列宽。

2、Offset函数的动态引用示例-数据透视表的动态引用

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

Counta()函数是Excel中的一个统计函数，用于统计非空单元格的个数。

动态引用展示：

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

1. 写公式-----永远返回最后十行数据

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

   这个应该很容易理解，不多赘述，用到了OFFSET和COUNTA函数。

2. 定义名称

   

3. 创建图表系列

   

4. 日期的动态引用

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

   

   

   

5. 动态变化的图表展示

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

1. 插入控件-滚动条

   

2. 设置控件格式，制作滚动条：第一个滚动条代表从哪行开始取数据，第二个滚动体代表展示多少行数据

   

   
3. 公式关联控件：=OFFSET($B$1,$D$4,0,$D$7,1)

   

   

   

4. 创建图表，关联公式

   

5. 完善细节，展示动态图表

   同创建成交量公式，创建横坐标即日期公式：=OFFSET($A$1,$D$4,0,$D$7,1)

   

三、小贴士

这堂课是本系列课程最难的一节，其实主要解决的是控件-公式-定义名称-图表之前的关联，掌握好关联，实现起来也没有那么难，我们一起加油~~~~