Excel-经典动态图表实现原理

2017-03-17 17:46阅读：

http://blog.sina.cn/dpool/blog/u/2738060731

经典动态图表实现原理：
一、 动态图表实现原理：使用函数创建动态图表
1. 理解图表中的数据系列：
一列或者一行数据对应一个系列（图例项）；插入空白图表时，要一列一列的选择数据，创建系列（图例项）；
2. 利用if创建简单的动态图表：
实现：

步骤：
(1)做右侧正方形边框控件：图表控件：开发工具→插入：

，表单控件：直接在表格中使用，相对较简单；activeX空间：可直接在VBA中使用及调试，相对复杂；选择表单控件中的复选框（第一排从左边数第三个），插入两个复选框；鼠标左键可选择复选框；鼠标右键选中，后鼠标左键可移动复选框位置；
(2)设置两个复选框，一个控制彩盒，一个控制宠物用品；先改复选框的名称：鼠标右键选中复选框，编辑文字即可；结果如图：

(3)设置复选框与excel表格连接：通过控件控制某个单元格中值的变化：选中“彩盒”的控件，右键：设置控件格式，

,“宠物用品”控件与上述操作一致，此时是否选中控件时，可看到控件链接的单元格的变化（TRUE/FALSE）; (4) 针对插入的图表的展示结果：控件链接的单元格是true，则要彩盒对应于excel表中的数据，若是false，则不要彩盒对应于excel表中的数据：if函数判断：（此处彩盒与宠物用品分别链接到G2，G3单元格，将单元格均通过选中复选框使其成为true）：
=if（$G$2,$B$2:$B$13,$F$2:$F$13）（含义：如果G2=TRUE,则返回彩盒对应的数据，否则返回与第二参数区域大小一致的空白区域列）；宠物用品函数也类似：=IF($G$3,$C$2:$C$13,$F$2:$F$13)；
(5) 公式：定义名称：新建名称：彩盒，引用位置：if函数对应的动态位置；
选择excel空白处任一单元格，插入：折线图，选中插入的空白折线图，右键：选择数据，添加数据系列：

系列值的设定：为当前单元格的之前设定的动态区域：彩盒，宠物用品与此设置一致；水平（分类）轴标签：选择为订购日期的区域；此时实现：复选框表单控件与表中单元格链接（通过复选框本身），表中单元格与excel工作表链接（通过if函数），折线图取数为工作表中的数据（将if函数直接定义到名称管理器中，直接引用工作表中名称管理器中的数据。因为做折线图选择数据系列的值，只能为一列或者一行数据或者一个数据，不能为公式形式，若将数据系列对应的if函数判断的值直接当做数据系列值，则会显示出错）；
(7) 通过点击每个复选框，设置彩盒与宠物用品的纵坐标数据一致，相当于将纵坐标固定；
(8) 通过拖拽折线图绘图区的大小，留出空白区域放置复选框表单控件，将复选框拖动到绘图区旁边的空白区域（只拖动复选框，删除复选框中的文字），置于顶层；
(9) 再美化插入的折线图即可；
二、 利用offset函数与控件创建动态图表：
1， offset函数概述：
offset函数相当于固定到某个位置，做偏移，取数据区域。
Offset（第一参数，第二参数，第三参数，第四参数，第五参数）：
ü 第一参数：从某个位置开始；
ü 第二参数：下移第二参数行（若为负值，则为上移第二参数行）；
ü 第三参数：右移第三参数列（若为负值，则为左移第三参数列）；
ü 第四参数：从第一、第二、第三参数固定的位置开始下取第四参数行；
ü 第五参数：从第一、第二、第三参数、第四参数选择的区域开始右取第五参数列；
涉及第四参数及第五参数取区域时，包含第一、第二、第三参数已经确定的单元格所在的行列；
例：

=OFFSET(A1,2,1,1,1)：H00012769=B3；
2， 透视表中的offset：
选中一个数据区域（非整行整列）做数据透视表时，或者先选中excel表中任意单元格再插入数据透视表（上述两种情况均为选择的数据区域做透视表，只有先选择连续的整列或者整行再插入数据透视表时，新增数据时，数据透视表中对应数据才会改变），当为原数据增加新的数据时，刷新数据透视表，则数据未发生改变，此时可以更改数据源（选中数据透视表任一单元格，分析—更改数据源即可），用offset函数解决此类问题：保证做counta()的数据每行都有数据
，做透视表的列为连续数据区域列；
默认列不变：确定数据区域，有多少行数据就取多少行数据：=OFFSET($A$1,0,0,COUNTA($A:$A),11)，设置原数据从A~K列，K列为第十一列；
公式：名称管理器，定义名称：数据区域；引用位置：offset函数确定的动态区域；
插入：数据透视表：表/区域填写：数据区域；此时创建好透视表之后，更改原始数据，刷新透视表即可；
3， 动态图表1：永远返回最后的10行数据：
原始数据表：

保证做counta()的数据每行都有数据
思路：只取最后10行数据，一共17行数据，则从第8行数据开始取，即从a1单元格开始，下移7行即为第八行数据，在向下取10行数据；即一共counta()行，下移counta()-10行，取10行；
步骤：
1) 公式：名称管理器→定义名称：日期1，引用位置：
=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)
2) 名称管理器：新建名称，成交量1，引用位置：
=OFFSET($A$1,COUNTA($A:$A)-10,1,10,1)
3) 在excel空白单元格位置：插入柱形图，右键选择数据，将图例项（系列）及水平（分类）轴标签的值分别设置为当前工作表的名称管理器中设置的日期1与成交量1；
4) 美化图表即可；
5) 此时可在原excel表格中插入数据验证；
4， 动态图表2：通过控件控制图表数据：
设置两个控件：
控件1：控制数据取值区域；数据取值行数不变，更改数据区域取值位置；offset中下移多少行；
控件2：控制取多少数据；offset中取多少行数据；
步骤：
插入两个滚动条：在excel任一空白单元格区域，开发工具→插入

1），此时已插入好第一个滚动条，复制已插入好的第一个滚动条或者再重新插入一个滚动条即可；
2）将控件与excel表关联到一起，才能被函数引用，因为函数要引用具体单元格中的值；在第一个滚动条（当做控件1），右键：设置控件格式，更改“控制”下的各项数据，如图所示：最小值取值为1，至少下移一行数据：

3）在第二个滚动条（当做控件2），右键：设置控件格式，更改“控制”下的各项数据，如图所示：最小值取值为1，至少取一行数据：

4）写offset函数，从列的第一个单元格开始，下移第一个控件链接的单元格行，右移0列，取第二个控件链接的单元格行，1列；
5）公式，名称管理器，定义名称：日期：=OFFSET($A$1,$D$2,0,$D$3,1)；新建名称：成交量：=OFFSET($B$1,$D$2,0,$D$3,1)；
6) 在excel工作表空白单元格处，插入：柱形图，右键选择数据，将图例项（系列）及水平（分类）轴标签的值分别设置为当前工作表的名称管理器中设置的日期与成交量；
7) 将滚动条拖动到绘图区旁边的空白区域，右键：置于顶层；
8) 美化图表即可；
9) 可通过开始：查找和选择，将滚动条与插入的图表组合到一起；

举报/Report

我的更多文章

下载客户端阅读体验更佳

APP专享

新浪博客

Excel-经典动态图表实现原理

分享

我的更多文章

下载客户端阅读体验更佳

疯狂捕鱼