如何制作动态图表.xls

序序 在一个偶然的机会,我有幸看到了一位大师的动态图表,顿时被它的结构所吸引,真是爱不释手啊! 后来我使出混身解数,翻公式,看定义,用手抄,拿笔写,终于悟出了里面的“真理“! 在此与大家分享!希望对大家有所帮助! 本篇属本人原创,如有雷同,纯属巧合! 在一个偶然的机会,我有幸看到了一位大师的动态图表,顿时被它的结构所吸引,真是爱不释手啊! 月份 种类 1月2月3月4月5月6月7月8月9月 销售库存销售库存销售库存销售库存销售库存销售库存销售库存销售库存销售库存 电冰箱532869396832917787337444578173048 彩电911784496259723447241927879424856 洗衣机91229764266856356365699942674356055 空调机17758659131849243223193228311726221 电饭煲9392982220182022102478775989565471 电风扇9731504730423188272578303987441438 热水器512029846275461672217744835163143995 家庭影院975954480455382262389772962509233 电磁炉5419765534362949066888125689951040 微波炉451060607484914076509170875254313180 洗衣机91229764266856356365699942674356055 销售库存电冰箱1月2 单选链接单元格 电冰箱6832彩电2月3 下拉链接单元格 彩电9625洗衣机3月 洗衣机4266空调机4月 空调机9131电饭煲5月 电饭煲2018电风扇6月 电风扇4730热水器7月 热水器6275家庭影院 8月 家庭影院8045电磁炉9月 电磁炉5343微波炉10月 微波炉748411月 12月 3月销售库存统计 X轴 标 志 电 冰 箱 彩 电 洗 衣 机 空 调 机 电 饭 煲 电 风 扇 热 水 器 家 庭 影 院 电 磁 炉 微 波 炉 0 20 40 60 80 100 120 台台 数数 销 售库 存 按 种 类 电 冰 箱 彩 电 洗 衣 机 空 调 机 电 饭 煲 电 风 扇 热 水 器 家 庭 影 院 电 磁 炉 微 波 炉 0 20 40 60 80 100 120 台台 数数 销 售库 存 10月11月12月 销售库存销售库存销售库存 915561627066 156455752812 52594766364 166620976458 817686226777 28613456721 763330717276 33794137331 223271444163 797327298635 52594766364 按 月 份 单选链接单元格 下拉链接单元格 电 冰 箱 彩 电 洗 衣 机 空 调 机 电 饭 煲 电 风 扇 热 水 器 家 庭 影 院 电 磁 炉 微 波 炉 0 20 40 60 80 100 120 台台 数数 销 售库 存 按 种 类 电 冰 箱 彩 电 洗 衣 机 空 调 机 电 饭 煲 电 风 扇 热 水 器 家 庭 影 院 电 磁 炉 微 波 炉 0 20 40 60 80 100 120 台台 数数 销 售库 存 1 9 找找思思路路 B 很多老师都喜欢提起“思路“这个词,那我今天也来学学!先找找思路! 首先要做的就是观察,观察静态图表和动态图表有哪些不同? 下面是两张截图,我们来看看! 静态 我们不难发现: 静态的只能显示单一状态的数据,且没有任何选择性的东西! 动态的有两个单选框和一个下拉框,并且通过这三个按钮的选择,图表显示的数据也不同,也许这就是动态吧! 再进一步观察还会发现,我们在单选“按种类“时,下拉框里显示的就是所有的种类,同时X轴显示的又是该种类每个月的情况,“按月份“的时候下拉框里显示的就是月 份,X轴显示的又是该月份每种产品的情况。好像我们的“数据有效性”哦，没那么神奇吧！ 告诉你，先别乱想，既然能这样显示，它们之间肯定有一种“不言而喻”的关系，究竟它们是如何配合的呢，就让我们一起来探讨！ 做做辅辅助助 B 我们在前面的观察中其实应该发现，每一次不同的选择，显示的数据都是一个特定的区域里的！ 例如：图中“电冰箱”其实就是 电冰箱532869396832917 按月份的“1月”时其实就是 电冰箱5328 彩电9117 洗衣机912 空调机177 电饭煲9392 电风扇9731 热水器5120 家庭影院975 电磁炉5419 微波炉4510 而X轴标志实际上就只是在下面两个区域变换： 电冰箱1月 彩电2月 洗衣机3月 空调机4月 电饭煲5月 电风扇6月 热水器7月 家庭影院8月 电磁炉9月 微波炉10月 11月 12月 这些区域可以单独引用出来，也可以原地取数，只是公式的复杂程度不同而已，为便于大家理解，本例采用前者； 我相信你现在开始想“招”了，怎么让他们跟着变！先别急，下面一步是关键！ 做做框框架架 B 选择整个数据区域“A1：Y12”，点“工具栏”的按钮，再点“完成”，显示如下： 哇！怎么乱成一团！哎！未加任何修理的就是这个样子！呵呵！ 现在我们把那三个“神奇“的按钮做上去,在菜单栏空白区域右键-窗体,选择 按钮在图表右上角拖出一个单选框,重复一步就两个了,同样方法,选择 按钮,做出一个下拉框! 初步效果如下: 我们再来看一下这三个按钮的“属性“,在按钮上右键-设置控件格式 在“单元格链接“里单击,然后选中一个任一空白单元格,我们选“示例!R17“,确定;先在“示例!R18“填入一个任意数字,1即可,按左边方法将单元格链接到“示例 !R18“,数据源区域选择“K17:K26“,下拉显示项数填入10,就是K17:K26的个数,确定 回到图表,我们来试操作一下,在两个单选框中选择时,发现“R17“里的数字会跟着变,1和2(因为我们这里就两个单选框),而K17:K26里的种类在下拉框都有显示了, 选择不同的种类时,R18里的数字也会跟着变哦,排第几就显示第几!好像很神奇!好了,这一步就先做到这里! 单 选 框 当 在 窗 体 按 钮 上 做 不 同 选 择 时 所 链 接 的 单 元 格 返 回 一 个 特 定 的 值 ! 可 能 你 打 开 的 格 式 框 没 有 “控 制 “这 个 选 项 ,右 键 菜 单 下 是 否 选 成 了 “控 件 工 具 箱 “呢 ! 做做列列表表 B 来到这一步,大家应该放慢一下速度,静下心来,你应该会联想到什么,(某些函数) 对了,没错,请相信自己的直觉,就是它-OFFSET和CHOOSE 现在把两个单选框的文字依次改为“按种类“(返回1)和“按月份“(返回2) 定义一个名称-下拉列表,引用位置为:=CHOOSE(示例!$R$17,示例!$K$17:$K$26,示例!$M$17:$M$28) 然后把下拉框的数据源区域改为刚才定义的名称:下拉列表 回到图表,操作一下这几个按钮,我想不用我解释你应该就明白其中的道理了! 如果能正确显示的话,我想说,恭喜你,成功一半了!(别说你不会用CHOOSE函数和定义名称哦,很抱歉,该内容不在本教案范围内,请原谅我的任性!_) 做做引引用用 B 下拉列表在变,但我们的数据还是没跟着变啊!别忘了,我们的OFFSET函数和辅助区域还没用到呢! 看一下“按种类取数区“,在A15填入公式=OFFSET(A2,$R$18,),往后填充 看一下“按月份取数区“,在B18填入公式=OFFSET(A3,$R$18*2-1),往后填充 验证一下,当你在下拉时,取数区的数据有没跟着改变!如果没有,请回到第3步! 做做系系列列 B 现在剩下最艰巨的任务就是怎样把时刻变化着的数据引用到柱形图里去! 在图表空表区域右键-源数据: 这个是最原始的状态,示例中只有两个系列,将现有的系列全部删除,点添加: 先假设系列1是显示“销售“,系列2显示“库存“,再看我们单选的是“按种类“(链接为1),因此分类X轴的标志显示就应该是各个月份“1月- 12月“,系列1的值就应该是B15:Y15里销售的值,同样道理,单选“按月份“(链接为2)时,分类X轴的标志显示就应该是各个种类“电冰箱-微波炉“,系列1的值就应该是 B18:B27里的值,系列2同理, 问一下,我们是不是可以用第4步做下拉列表的方法,然后借助R17和R18的值来做公式和定义名称呢!再把定义的名称填到源数据的值里!试试看! 定义名称:公式: X轴=CHOOSE(示例!$R$17,示例!$M$17:$M$28,示例!$K$17:$K$26) 销售=CHOOSE(示例!$R$17,(示例!$B$15,示例!$D$15,示例!$F$15,示例!$H$15,示例!$J$15,示例!$L$15,示例!$N$15,示例!$P$15,示例!$R$15,示例!$T$15,示例!$V$15,示例!$X$15),示例!$B$18:$B$27) 库存=CHOOSE(示例!$R$17,(示例!$C$15,示例!$E$15,示例!$G$15,示例!$I$15,示例!$K$15,示例!$M$15,示例!$O$15,示例!$Q$15,示例!$S$15,示例!$U$15,示例!$W$15,示例!$Y$15),示例!$C$18:$C$27) 如下图: 如果检测都可以正常显示的话,我想说:恭喜你!学会了! 做做美美化化 B 可以尽情发挥你的艺术细胞了! -完- 本例介绍了较简单的动态图表制作方法,同样,也可以 运用其它窗体和组合来实现多级动态,种类下也可加 入二级区域,但这样高难度的作品还是留给大家去发 挥吧! 由于本人水平有限,本篇中如有疏漏还请见谅! 动态(具体可见示例) 7873374445781730 动态的有两个单选框和一个下拉框,并且通过这三个按钮的选择,图表显示的数据也不同,也许这就是动态吧! 再进一步观察还会发现,我们在单选“按种类“时,下拉框里显示的就是所有的种类,同时X轴显示的又是该种类每个月的情况,“按月份“的时候下拉框里显示的就是月 份,X轴显示的又是该月份每种产品的情况。好像我们的“数据有效性”哦，没那么神奇吧！ 告诉你，先别乱想，既然能这样显示，它们之间肯定有一种“不言而喻”的关系，究竟它们是如何配合的呢，就让我们一起来探讨！ 我们在前面的观察中其实应该发现，每一次不同的选择，显示的数据都是一个特定的区域里的！ 这些区域可以单独引用出来，也可以原地取数，只是公式的复杂程度不同而已，为便于大家理解，本例采用前者； 现在我们把那三个“神奇“的按钮做上去,在菜单栏空白区域右键-窗体,选择 按钮在图表右上角拖出一个单选框,重复一步就两个了,同样方法,选择 按钮,做出一个下拉框! 下 拉 选 框 在“单元格链接“里单击,然后选中一个任一空白单元格,我们选“示例!R17“,确定;先在“示例!R18“填入一个任意数字,1即可,按左边方法将单元格链接到“示例 !R18“,数据源区域选择“K17:K26“,下拉显示项数填入10,就是K17:K26的个数,确定 回到图表,我们来试操作一下,在两个单选框中选择时,发现“R17“里的数字会跟着变,1和2(因为我们这里就两个单选框),而K17:K26里的种类在下拉框都有显示了, 选择不同的种类时,R18里的数字也会跟着变哦,排第几就显示第几!好像很神奇!好了,这一步就先做到这里! 可 能 你 打 开 的 格 式 框 没 有 “控 制 “这 个 选 项 ,右 键 菜 单 下 是 否 选 成 了 “控 件 工 具 箱 “呢 ! 定义一个名称-下拉列表,引用位置为:=CHOOSE(示例!$R$17,示例!$K$17:$K$26,示例!$M$17:$M$28) 如果能正确显示的话,我想说,恭喜你,成功一半了!(别说你不会用CHOOSE函数和定义名称哦,很抱歉,该内容不在本教案范围内,请原谅我的任性!_) 下拉列表在变,但我们的数据还是没跟着变啊!别忘了,我们的OFFSET函数和辅助区域还没用到呢! 先假设系列1是显示“销售“,系列2显示“库存“,再看我们单选的是“按种类“(链接为1),因此分类X轴的标志显示就应该是各个月份“1月- 12月“,系列1的值就应该是B15:Y15里销售的值,同样道理,单选“按月份“(链接为2)时,分类X轴的标志显示就应该是各个种类“电冰箱-微波炉“,系列1的值就应该是 B18:B27里的值,系列2同理, 问一下,我们是不是可以用第4步做下拉列表的方法,然后借助R17和R18的值来做公式和定义名称呢!再把定义的名称填到源数据的值里!试试看! =CHOOSE(示例!$R$17,(示例!$B$15,示例!$D$15,示例!$F$15,示例!$H$15,示例!$J$15,示例!$L$15,示例!$N$15,示例!$P$15,示例!$R$15,示例!$T$15,示例!$V$15,示例!$X$15),示例!$B$18:$B$27) =CHOOSE(示例!$R$17,(示例!$C$15,示例!$E$15,示例!$G$15,示例!$I$15,示例!$K$15,示例!$M$15,示例!$O$15,示例!$Q$15,示例!$S$15,示例!$U$15,示例!$W$15,示例!$Y$15),示例!$C$18:$C$27) 48915561