excel动态图表基础教程.xls

动动态态图图表表常常用用控控件件 说到动态图表 我们经常会用到下面两种工具 窗窗体体控控件件工工具具箱箱 组合框滚动条组合框 在这里大家会发现它们两个中的工具按钮有很多都相同 那么在实际使用中有什么区别呢 我在制作动态图表的时候常常用到的是窗体中的工具 因为它比起控件工具箱中的工具在设置上要方便一些 具体如下 窗窗体体控控件件的的设设置置 以组合框为例 右击控件 设置控件格式 控制 其中 数据源区域 表示的是列表框下拉列表中的值 当在组合框中选择后 单元格链接 所在的单元格将返 回此时所选值在列表中的位置 数据源区域单元格链接 张三3 李四 王五 控控件件工工具具箱箱控控件件的的设设置置 右击控件 属性 listfillrange linkedcell 张三李四 李四 王五 注意窗体控件箱中的控件在设置前 需先点击控件工具箱工具栏最左边的设计按钮 王五 李四 注意 无论是窗体控件还是组合框控件中的组合框或者列表框 在数据源设置的时候都只支持一列数据 不支持一行或者多行多列数据 下面举个例子 张三张三张三三 李四李四李四四 王五王五王五五 赵六赵六赵六六 张三李四王五赵六 至此你应该对窗体控件和控件工具箱中的组合框的设置上有所了解了 两两者者的的不不同同1 1 细心的一定会发现 两个组合框返回的值有所不同 窗体中的组合框控件返回的值为筛选值在列表中的位置 见C19中的内容 而控件工具箱中的组合框控件返回值就是筛选值 见C36中的内容 所以不同的控件类型 在做动态图表时选择的公式也是不同的 如果是窗体中的组合框控件我们选择OFFSET函数 或者更简单的情况下用CHOOSE也是完全可以的 就可以了 而控件工具箱中的组合框控件我们则常常要用到VLOOKUP和MATCH函数 两两者者的的不不同同2 2 窗体控件和控件工具箱中的控件还有一个不同点在于自定义设置上 窗体控件可以设置的内容仅限于控件大小和位置 而控件工具箱中的控件可以设置很多属性 包括字体的大小 颜色 控件的大小 颜色等等 所以如果你需要创建个性化的控件还是用控件工具箱比较好 当然一般的使用中窗体控件就完全可以应付了 控控件件的的插插入入 这里讲的控件的插入其实是如何在工作表中插入控件 方法一直接在窗体工具栏中选中你想要插入的控件 然后把光标移到工作表中 此时光标会变为一个十字 然后点击鼠标左键一拉 控件就画好了 方法二先选中图表然后在选择窗体工具栏中的控件然后将光标移动到图表中 画控件 这种方法设置的控件 它和图表是一体的 也就是你在移动图表的同时控件随图表一起移动 见下图 产产品品4一一年年趋趋势势 1月2月3月4月5月6月7月8月9月10月11月12月 0 50 100 150 200 250 300 张三 李四 王五 赵六 张三 产产品品4一一年年趋趋势势 1月2月3月4月5月6月7月8月9月10月11月12月 0 50 100 150 200 250 300 滚动条 我在制作动态图表的时候常常用到的是窗体中的工具 因为它比起控件工具箱中的工具在设置上要方便一些 具体如下 张三 李四 王五 赵六 无论是窗体控件还是组合框控件中的组合框或者列表框 在数据源设置的时候都只支持一列数据 窗体中的组合框控件返回的值为筛选值在列表中的位置 见C19中的内容 而控件工具箱中的组合框控件返回值就是筛选值 见C36中的内容 所以不同的控件类型 在做动态图表时选择的公式也是不同的 如果是窗体中的组合框控件我们选择OFFSET函数 或者更简单的情况下用CHOOSE也是完全可以的 就可以了 而控件工具箱中的组合框控件我们则常常要用到VLOOKUP和MATCH函数 窗体控件和控件工具箱中的控件还有一个不同点在于自定义设置上 窗体控件可以设置的内容仅限于控件大小和位置 而控件工具箱中的控件可以设置很多属性 包括字体的大小 颜色 控件的大小 颜色等等 所以如果你需要创建个性化的控件还是用控件工具箱比较好 当然一般的使用中窗体控件就完全可以应付了 先选中图表然后在选择窗体工具栏中的控件然后将光标移动到图表中 画控件 这种方法设置的控件 它和图表是一体的 也就是你在移动图表的同时控件随图表一起移动 产产品品4一一年年趋趋势势 1月2月3月4月5月6月7月8月9月10月11月12月 0 50 100 150 200 250 300 产产品品4一一年年趋趋势势 1月2月3月4月5月6月7月8月9月10月11月12月 0 50 100 150 200 250 300 其其它它控控件件在在动动态态图图表表中中的的设设置置 1 1 滚滚动动条条 我们这里以窗体中的滚动条为例 滚动条控件我认为在动态图表中主要应用于表现时间序列的图表 对应图表类型主要有两种 分别为折线图和柱形图 首先我们来了解滚动条的设置 从上向下依次为 当前值表示的是滚动条当前的值 最小值滚动条可以滚动到的最小值 最大值滚动条可以滚动到的最大值 步长是你点击左右两个箭头时滚动条数值变化的幅度 页步长是你点击滚动条中间的滑块或者滑块两边的空白时滚动条数值的变化幅度 单元格链接是将滚动条的值返回到单元格中 了解如何设置后我们就可以来做动态图表了 销售日期销售额左边是一组时间序列的数据表示的是某一时期内的销售额 2009 1 132 首先我们画一个滚动条1 2009 1 548 然后将其单元格链接设置为G18 2009 1 845 接着以数据区域做一个折线图 2009 1 1143 2009 1 1546 2009 1 1826 2009 1 2150 2009 2 548 2009 2 825 2009 2 1133 2009 2 1449 2009 2 1729 2009 2 2027 2009 2 2327 2009 2 2641 接下来我们需要定义名称 2009 3 125 DATA1 OFFSET B 18 G 18 2009 3 420 AXIS1 OFFSET A 18 G 18 2009 3 734 用定义的名称去修改图表系列 2009 3 1031 2009 3 1330 2009 3 1629 2009 3 1959 至此动态图表已经完成 但是这个图表仅仅是一个会动的图表而已 它的形式要大于它的内容 随着我们拉动滚动条图表会越来越密 我们就会越来越难从图表上挖掘有用的信息 2009 1 1 2009 1 5 2009 1 8 2009 1 11 2009 1 15 2009 1 18 2009 1 21 2009 2 5 2009 2 8 2009 2 11 2009 2 14 2009 2 17 2009 2 20 2009 2 23 2009 2 26 2009 3 1 2009 3 4 2009 3 7 2009 3 10 2009 3 13 2009 3 16 2009 3 19 0 20 40 60 80 2009 1 1 0 5 10 15 20 25 30 35 2009 1 1 0 5 10 15 20 25 30 35 当然我们不是说上面的绝对不好 还是要看平常使用中是否需要 下面我们适当的修改一下定义的名称 DATA2 OFFSET B 18 G 18 10 AXIS2 OFFSET a 18 G 18 10 用定义的名称去修改图表系列 这一种方式的动态图表每次只显示十期数据 图表简洁流畅 易于表现数据 所以相同的函数通过参数的不同设置就可以做出完全两种形式的动态图表 函数在制作动态图表的时候的作用非常大 在此强烈建议大家好好复习一下下列函数 CHOOSE OFFSET INDEX MATCH 2 2 复复选选框框 我们还是以窗体中的复选框为例 所谓复选框就相当于多选题一样 可以同时勾选几个复选框 他常常应用于折线图 堆积柱形图 面积图 或者簇壮柱形图 首先我们先了解一下复选框的设置 从上向下依次为 未选择它们是设置复选框的初始 形态 对应设置后的复选 框见右边 已选择 混合型 单元格链接是将复选框的值返回到单元格中 下面我们开始用复选框来做动态图表 单位A单位B单位C 一6 52692019 8 154407999 1 551873169 二4 937182171 5 253978057 7 880425379 三7 4491652253 21602887 1 932010189 四5 9485230958 67323701 8 453017616 五2 338387864 6 327246271 6 122210948 六4 58445659 6 433730986 8 386718012 日6 7015568035 79077129 5 839654013 上面是一组数据我们需要将它做成一个可以选择某一个或某几个单位动态显示在图上的图表 我们先画三个复选框 TRUETRUETRUE 接着我们以区域A80 D87做簇壮柱形图 2009 1 5 2009 1 8 2009 1 11 2009 1 15 2009 1 18 2009 1 21 2009 2 5 2009 2 8 2009 2 11 2009 2 14 0 10 20 30 40 50 60 2009 1 5 2009 1 8 2009 1 11 2009 1 15 2009 1 18 2009 1 21 2009 2 5 2009 2 8 2009 2 11 2009 2 14 0 10 20 30 40 50 60 我是复选框 未选择 已选择 混合型 单位A单位B单位C 一二三四五六日 0 2 4 6 8 10 12 单位A 单位B 单位C 接下来我们定义名称 danwei1 IF B 91 B 81 B 87 E 81 E 87 danwei2 IF B 91 C 81 C 87 E 81 E 87 danwei3 IF B 91 D 81 D 87 E 81 E 87 然后用定义的名称去修改图表系列 至此动态图表完成 但是这个图表依然有一个问题 用复选框做的簇壮柱形图的动态图表 由于每一个系列代表一组柱子 且系列柱子之间是并排放置 所以当复选框未选择时就会在图表上留下一个空距 这样很不美观 所以我一般不建议将簇壮柱形图和复选框搭配使用 显然我们将图表类型换做堆积柱形图或者折线图就不会有这样的问题了 除了上面所说的控件以外 单选按钮 列表框 微调项 都可以用来制作动态图表 它们的设置上也和上面提到的大同小异 有兴趣的朋友可以试一下 关于动态图表的控件就讲到这里 也许大家会问为什么没有讲组合框控件呢 因为在本教程里面有很多地方都是以组合框为例子来讲解动态图表的 所以这里就不再重复了 一二三四五六日 0 5 10 15 20 25 30 单位C 单位B 单位A 单位A单位B单位C 一二三四五六日 0 2 4 6 8 10 12 单位A 单位B 单位C 一二三四五六日 0 2 4 6 8 10 12 单位A 单位B 单位C 是你点击滚动条中间的滑块或者滑块两边的空白时滚动条数值的变化幅度 但是这个图表仅仅是一个会动的图表而已 它的形式要大于它的内容 随着我们拉动滚动条图表会越来越密 我们就会越来越难从图表上挖掘有用的信息 2009 1 1 2009 1 5 2009 1 8 2009 1 11 2009 1 15 2009 1 18 2009 1 21 2009 2 5 2009 2 8 2009 2 11 2009 2 14 2009 2 17 2009 2 20 2009 2 23 2009 2 26 2009 3 1 2009 3 4 2009 3 7 2009 3 10 2009 3 13 2009 3 16 2009 3 19 0 20 40 60 80 2009 1 1 0 5 10 15 20 25 30 35 这一种方式的动态图表每次只显示十期数据 图表简洁流畅 易于表现数据 所以相同的函数通过参数的不同设置就可以做出完全两种形式的动态图表 函数在制作动态图表的时候的作用非常大 在此强烈建议大家好好复习一下下列函数 CHOOSE OFFSET INDEX MATCH 2009 1 5 2009 1 8 2009 1 11 2009 1 15 2009 1 18 2009 1 21 2009 2 5 2009 2 8 2009 2 11 2009 2 14 0 10 20 30 40 50 60 但是这个图表依然有一个问题 用复选框做的簇壮柱形图的动态图表 由于每一个系列代表一组柱子 且系列柱子之间是并排放置 所以当复选框未选择时就会在图表上留下一个空距 这样很不美观 一二三四五六日 0 2 4 6 8 10 12 单位A 单位B 单位C 其其它它技技巧巧和和非非控控件件在在动动态态图图表表中中的的应应用用 对于控件 在数据源的设置上也是支持定义名称的 我们以 定义名称的方法 这一课中的例子为例 我们先定义一个名称 LIST CHOOSE 定义名称的方法 B 18 定义名称的方法 B 3 B 16 定义名称的方法 A 20 A 31 然后以定义的名称直接填入控件设置中 数据源区域 即可 对控件数据源区域定义名称主要运用于多级筛选动态图表的情况 用数据有效性来制作动态图表 前面提到组合框和列表框控件在设置数据源的时候只能以单列区域做数据源 在实际做动态图表的时候 我们可能遇到必须使用一行数据作为数据源 此时我们可以用数据有效性来做 张三李四王五赵六张三 78547 91159 43484 65216 张三 1234 0 2 4 6 8 10 CHOOSE 定义名称的方法 B 18 定义名称的方法 B 3 B 16 定义名称的方法 A 20 A 31 张三 1234 0 2 4 6 8 10 动动态态图图表表设设置置 动动态态图图表表系系列列的的设设置置 这里所讲的设置有两方面的内容 一是关于定义名称的动态图表 我们如何用定义的名称替换系列数据源的问题 二是关于如何制作动态变化的图表标题问题 一 如何用定义的名称替换系列数据源的问题 详细设置见动画 步骤如下 1 选中系列 在工具栏中出现图表系列公式如下 SERIES 定义名称的方法 B 3 B 16 定义名称的方法 C 3 C 16 1 2 分别选中红色部分 3 分别将红色部分修改为X AXIS和DATA 然后按回车 4 此时公式自动变换为 SERIES 动态图表 xls X AXIS 动态图表 xls DATA 1 注意 选择的时候一定只选中 以后的部分 否则无法修改图表系列公式 动动态态图图表表标标题题的的设设置置 步骤如下 1 选中图表标题 2 直接 B22 单元格 然后回车 注意 输入公式的时候直接输 某个单元格 就可以了 不要将 辅助区域法 B 22 全部输入 准确的说是只需要输入 然后再点击标题所在的单元格就可以了 他只支持单元格链接 也不需要在编辑框里输入内容 否则图表标题框只是返回一个你输入的公式文本而已 这里所讲的设置有两方面的内容 一是关于定义名称的动态图表 我们如何用定义的名称替换系列数据源的问题 SERIES 定义名称的方法 B 3 B 16 定义名称的方法 C 3 C 16 1 选择的时候一定只选中 以后的部分 否则无法修改图表系列公式 输入公式的时候直接输 某个单元格 就可以了 不要将 辅助区域法 B 22 全部输入 准确的说是只需要输入 然后再点击标题所在的单元格就可以了 他只支持单元格链接 也不需要在编辑框里输入内容 否则图表标题框只是返回一个你输入的公式文本而已 方方法法一一 辅辅助助区区域域法法 1月2月3月4月5月6月7月 产品1150350250300250200400 产品280408080 产品3150100 产品4180120150120240180270 产品51006014060180 产品63008080100120140 产品740402020 产品84016361282416 产品91282412 产品102060281661615 产品111212 产品12466 产品13245 产品14 数据区域1月2月3月4月5月6月7月 列表筛选 辅助区域 产品4180 120150120240180270 产品14 产品2 产品3产品4一年趋势 产品4 产品5 产品6 产品7 产品8 产品9 产品10 产品11 产品12 产品13 产品14 产品4 产产品品4一一年年趋趋势势 1月2月3月4月5月6月7月8月9月10月11月12月 0 50 100 150 200 250 300 8月9月10月11月12月 250200300250 1208080 10050150 12060210210 100100120260 1208010080 1004040 6024124020 40121240 4810880 612 8月9月10月11月12月 12