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

标签:
学习交流 |
分类: EXCEL学习 |
经典动态图表实现原理:
一、
1.
一列或者一行数据对应一个系列(图例项);插入空白图表时,要一列一列的选择数据,创建系列(图例项);
2.
步骤:
(1)做右侧正方形边框控件:图表控件:开发工具→插入:http://s10/small/002ZiCFRzy79zOTMb2199&690,表单控件:直接在表格中使用,相对较简单;activeX空间:可直接在VBA中使用及调试,相对复杂;选择表单控件中的复选框(第一排从左边数第三个),插入两个复选框;鼠标左键可选择复选框;鼠标右键选中,后鼠标左键可移动复选框位置;
(2)设置两个复选框,一个控制彩盒,一个控制宠物用品;先改复选框的名称:鼠标右键选中复选框,编辑文字即可;结果如图:http://s16/mw690/002ZiCFRzy79zOVPzllaf&690(3)设置复选框与excel表格连接:通过控件控制某个单元格中值的变化:选中“彩盒”的控件,右键:设置控件格式,http://s3/small/002ZiCFRzy79zOXVweme2&690,“宠物用品”控件与上述操作一致,此时是否选中控件时,可看到控件链接的单元格的变化(TRUE/FALSE);
(4)
=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)
此时实现:复选框表单控件与表中单元格链接(通过复选框本身),表中单元格与excel工作表链接(通过if函数),折线图取数为工作表中的数据(将if函数直接定义到名称管理器中,直接引用工作表中名称管理器中的数据。因为做折线图选择数据系列的值,只能为一列或者一行数据或者一个数据,不能为公式形式,若将数据系列对应的if函数判断的值直接当做数据系列值,则会显示出错);
(7)
(8)
(9)
二、
1,
offset函数相当于固定到某个位置,做偏移,取数据区域。
Offset(第一参数,第二参数,第三参数,第四参数,第五参数):
ü
ü
ü
ü
ü
涉及第四参数及第五参数取区域时,包含第一、第二、第三参数已经确定的单元格所在的行列;
例:http://s13/mw690/002ZiCFRzy79zP1OkBSdc&690=OFFSET(A1,2,1,1,1):H00012769=B3;
2,
选中一个数据区域(非整行整列)做数据透视表时,或者先选中excel表中任意单元格再插入数据透视表(上述两种情况均为选择的数据区域做透视表,只有先选择连续的整列或者整行再插入数据透视表时,新增数据时,数据透视表中对应数据才会改变),当为原数据增加新的数据时,刷新数据透视表,则数据未发生改变,此时可以更改数据源(选中数据透视表任一单元格,分析—更改数据源即可),用offset函数解决此类问题:保证做counta()的数据每行都有数据
,做透视表的列为连续数据区域列;
默认列不变:确定数据区域,有多少行数据就取多少行数据:=OFFSET($A$1,0,0,COUNTA($A:$A),11),设置原数据从A~K列,K列为第十一列;
公式:名称管理器,定义名称:数据区域;引用位置:offset函数确定的动态区域;
插入:数据透视表:表/区域填写:数据区域;此时创建好透视表之后,更改原始数据,刷新透视表即可;
3,
保证做counta()的数据每行都有数据
思路:只取最后10行数据,一共17行数据,则从第8行数据开始取,即从a1单元格开始,下移7行即为第八行数据,在向下取10行数据;即一共counta()行,下移counta()-10行,取10行;
步骤:
1)
=OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)
2)
=OFFSET($A$1,COUNTA($A:$A)-10,1,10,1)
3)
4)
5)
4,
设置两个控件:
控件1:控制数据取值区域;数据取值行数不变,更改数据区域取值位置;offset中下移多少行;
控件2:控制取多少数据;offset中取多少行数据;
步骤:
插入两个滚动条:在excel任一空白单元格区域,开发工具→插入http://s6/mw690/002ZiCFRzy79zP5j8qh15&690
1)
3)在第二个滚动条(当做控件2),右键:设置控件格式,更改“控制”下的各项数据,如图所示:最小值取值为1,至少取一行数据:http://s7/small/002ZiCFRzy79zP8ulMy86&690
4)
5)
6)
7)
8)
9)