Excel2007版的常用功能（21）:Excel动态图表原理

1.  做复选框：

    开发者工具--插入--选中复选框

复选框的文字可以更改

若想让复选框与图表产生联系：

选中复选框--右键--设置控件格式--控制--单元格链接：选中单元格  此时，选中复选框，单元格显示TRUE；不选中，单元格显示FALSE，if(那个单元格为TRUE,选中某列数据，否则选中空列)  如：=IF($G$2,$B$2:$B$13,$F$2:$F$13)   必须是绝对引用
    公式--定义名称--名称：‘彩盒’引用位置：=IF($G$2,$B$2:$B$13,$F$2:$F$13)--确定--插入--折线图--右键--选择数据--
    添加--系列名称：彩盒，系列值：=sheet1!彩盒(这里选中区域时必须是一行或一列)--确定--确定
    可以将复选框放在图例前面，这时图例会覆盖复选框，将图例右键-置于底层  即可将复选框显示出来
    也可以将复选框文字删除后缩短长度再拖到图例前 

开发者工具--插入 里 有两个部分，上面的“表单控件”与“下面的Active”控件几乎一样，但是上面的“表单控件”是在表格里使用的较简单的控件，使用的自由度比较大，下面的Active”控件稍微复杂些，它的属性可以在VBA里设置和修改的。

当图表里有两个复选框控制两个数据时，当两个数据都显示时，假设两个数据的上限不同，如果一个数据设置消失，就剩下一个数据，这时坐标轴的数据可能会发生变化，因为坐标轴数据设置是根据图表内容实际情况来自动更改的

2.  动态图表实现原理： 

   "利用Offset函数与控件创建动态图表"：

     1、Offset函数概述        

        语法：Offset(reference,rows,cols,height,width)  即：
        offset(基准点，基准点下面第一个单元格开始算起取n行，基准点右边第一个开始算起取n列，取包括自己在内的几行，包括自己在内的几列)  如：
        offset(A1,2,1,1,1)    //取到 B3 的值

         这里以A1为基准点，下移2行右移一列后取到B3，以此为基准点并包括这一点来右移1行1列，因此这里是B3

offset很智能，会自动选中表里有数据的区域，不管给的参数是精确的还是函数得出的，都会智能地匹配到有数据的区域

     2、Offset函数的动态引用示例        
        =offset($A$1,0,0,?,11)  ?表示有多少行就取多少行    如：
        =offset($A$1,0,0,counta($A:$A),11)  
        counta($A:$A)   表示看A列有几个非空数据，也就是有几行

     3、动态图表1 永远返回最后10行数据        
        做法：
            定义名称：公式-定义名称-名称：成交量，引用位置：=offset($B$1,COUNTA($B:$B)-10,0,10,1)
            定义名称：公式-定义名称-名称：日期，引用位置：=offset($A$1,COUNTA($A:$A)-10,0,10,1)
            插入-折线图-右键-选择数据-添加-系列名称：成交量，系列值：=Sheet2!成交量    这时纵坐标值为成交量
            右键折线图-选择数据-编辑-轴标签区域：=Sheet2!日期  这是在修改横坐标值为日期

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

        做法：
        准备数据-开发工具-插入-滚动条-画出来-复制滚动条-粘贴滚动条-右键第一个滚动条-设置控件格式-控制-最小值：1，最大            值：看有多少数据，单元格连接：$D$2-确定-右键第二个滚动条-设置控件格式-控制-最小值：1，最大值：看有多少数据，           单元格连接：$D$4-公式-定义名称：成交量，引用位置：=OFFSET($B$1,$D$2,0,$D$4,1)-确定-插入-折线图-右键折线图-          选择数据-添加-系列名称：成交量，系列值：=Sheet5!成交量-确定-公式-定义名称：日期2，
        引用位置：=offset($A$1,$D$2,0,$D$4,1)-确定-右键柱形图-选择数据-编辑-=Sheet5!日期2-确定
       5. 动态地生成数据透视表
         设置数据区域：
            公式-定义名称-名称：数据区域，引用位置：=offset($A$1,0,0,counta($A:$A),11)   （表示有多少行就取多少行） 
            创建数据透视表-表/区域：数据区域，新表中创建  

           这时，选中的数据区域就是一个动态地区域了，增删数据后，只需右键透视表--刷新  数据透视表即可刷新数据了。

           在透视表里，若是新增数据，是无法通过刷新添加到透视表里的。解决办法：

在创建数据透视图时，选中区域时，这个区域可以是在  “公式--定义名称--名称，选区” 这里定义的名称，这个名称对应的区域就是“选区”，这个选区可以用函数来写，比如if函数，offset函数等。这样，这时的数据是可变的

counta（$A:$A）   表示求A列里有多少条数据

6.  插入滚动条，并链接到某一个单元格上：

    开发者工具--插入--滚动条--画出滚动条--右键滚动条--设置控件格式--控制--设置：最大值，最小值，单元格链接--这时最大值最小值已确定，当滚动条滚动时，相应的数据会在链接到的单元格里显示出来。

   例子：

    如果要用滚动条来控制数据从哪里开始取，取多少，要这样做：

    插入两个滚动条滚动条，设置好最大值最小值和单元格链接后，在 “公式--定义名称--名称，选区” 里做设置，并在选区里写入offset函数：

=offset（基点，选中滚动条链接到的单元格1，选中滚动条链接到的单元格2，向下取几行，向右取几行）           这时，在图表里插入数据选定区域时，写入这个在公式里定义的名称，即可做到滚动条控制数据了。

7. 

使用组合框和vlookup函数制作简单的动态图表：

• 原理：

组合框控件是将文本框与列表框组合起来创建下拉列表框。组合框中选定不同的值，其对应的图表将发生变化。

步骤：

• 将B1设置数据有效性，设置引用的数据区域
• 用vlookup去某一个数据源里查找B1里选中的数据，从这个数据源里返回一个数值，这个数值就是要用图表显示的数据。
• 插入组合框：    选中任意单元格—开发工具—插入—组合框(ActiveX控件)—画出来(这时进入设计模式状态)—开发工具—控件组—属性(弹出“属性”对话框)—在”LinkedCell”右侧的文本框中输入”组合框制图！B1”(B1是创建下拉列表框的单元格)， 在”ListFillRange”右侧的文本框中输入”选项按钮制图！A3:A7”(A3:A7是B1引用的数据)—关闭—单击“设计模式”退出设计模式

这时，组合框里的数据就与B1中选择的数据就同步了，可以直接用组合框来控制B1对数据的选择。

例子：

• 有三个表：表1，表2，表3表1的数据：表2中的数据：表3中的数据：
• 给“表2”中的B1设置数据有效性，数据来源为“表3”中的数据
• 给“表2”中的B2写入函数：=VLOOKUP($B$1,表1!1:7,ROW(A2)+1,0)，并通过拖动，将函数运用到B3:B5中。这时，B1中每次选中一个数据，B2:B5中都有对应的数据显示。
• 选中“表2”中A1:B5—插入—柱形图—簇状柱形图
• 在任意表中(此例为在表1中)—开发工具—插入—(ActiveX控件)按钮—画出来(这时进入设计模式状态)—开发工具—控件组—属性(弹出“属性”对话框)—在”LinkedCell”右侧的文本框中输入”表2！B1”(B1是创建下拉列表框的单元格)， 在”ListFillRange”右侧的文本框中输入”表3！A1:A6”(A1:A6是B1引用的数据)—关闭—单击“设计模式”退出设计模式
• 这时，组合框里的数据就与B1中选择的数据就同步了，可以直接用组合框来控制B1对数据的选择，就选择了图表中对应人员数据的显示。

使用复选框、定义名称、IF函数制作简单的动态图表。