在我们浏览网页的时候,网页会根据我们的选择来展示不同的内容,有没有想过,把excel表格也做成“输入关键字,就自动显示对应的数据和图表”的动态表格呢?比如我们要做工资统计时,把部门作为关键字,每输入一个部门名称,就自动显示该部门对应的工资明细和统计图表。
最近世界杯很火,我们就拿世界杯统计数据举例,最终实现的效果如下:
一、学习目标
动态图表是一种根据我们的选择来实时展示不同信息的图表。此次我们以世界杯小组赛各个球队的比赛成绩为数据制作动态图表,原始数据如下:
实现目标需要满足的条件是:
-
1.有一个下拉框,里面有A-H组8个选项;
-
2.选中任意一个选项,就能自动显示该小组的数据,并自动绘制出相对应的柱形图;
二、制作方法
处理原始数据
在我们得到数据之后,一般都需要对原始数据进行一定的处理,才能作为制图数据。
如上图,在A列前插入新的一列,用来区分同一小组中不同的球队。在单元格A3中输入公式:
=B3&COUNTIF(B$2:B3,B3)。双击向下填充,就可以得到上图的结果。
1、COUNTTF(计数区域,计数条件)
:
如果在计数区域中的单元格满足计数条件,则加1。就A3单元格的公式来分析,在B2:B3这个区域中,返回等于B3单元格的内容(A字母)出现次数。可以数出来是1次,所以COUNTIF返回1;
2、为什么是(B$2:B3,B3)
,而不是(B2:B3,B3)
我们先将公式改成=B3&COUNTIF(B2:B3,B3)
,最终A列呈现的结果如下图:
此时可以看到A列的数据中,每个小组只有前两个队是正确的结果。这是因为计数区域单元格没有绝对引用,向下填充公式时,计数区域会自动改变行号,依次为B2:B3,B3:B4,B4:B5……,大家会发现,这时候的所有计数区域都是2个单元格,所以在A列中最大的号数就是2。
要想让计数累计,就需要锁定计数区域开始单元格的行号,所以公式中计数区域是B$2:B3,向下拖动公式时,依次为B$2:B3,B$2:B4,B$2:B5……
3、&
符号的作用
&的功能就是把单元格B3的内容和COUNTTF函数返回的内容拼接在一起。如A3单元格公式中,B3的内容为“A”,COUNTIF函数返回1,所以最终为 “ A1 ”。
三、制作下拉框
选中单元格M1,点击菜单栏的“数据”,点击“数据验证”,在弹窗中点击“数据验证”
在弹窗中,设置“允许”为列表,“源”为“A,B,C,D,E,F,G,H”,中间用英文逗号隔开。
选中单元格M1,就会出现下拉框
四、制作辅助数据
如下图,我们以区域M2:U6为辅助数据区域,也是真正的制图数据。制作辅助数据区域也很简单,直接将源数据中C2-K2单元格的内容复制到M2-U2单元格区域,然后在M3单元格输入公式:
=IFERROR(VLOOKUP($M$1&ROW(A1),$A$3:$K$34,3+COLUMN(A1)-COLUMN($A1),0),"")
,右拉下拉可得到下表。
公式解析:
IFERROR(VLOOKUP($M$1&ROW(A1),$A$3:$K$34,3+COLUMN(A1)-COLUMN($A1),0),"")
1.COLUMN( 指定单元格/单元格区域)函数
其作用是返回指定单元格或单元格区域的列数。如上,COLUMN(A1)就会返回1;
2.ROW(指定单元格/单元格区域)函数
其作用是返回指定单元格或单元格区域的行号。如上,ROW(A1)就会返回1;
3.VLOOKUP(查找值,查找区域,返回值列号,0)函数
在M3单元格中,要得到A组的第一个球队名称,第一参数为$M$1&ROW(A1)
,得到查找值A1,第二参数为$A$3:$K$34
,表示在源数据的A3-K34单元格区域查找,第三参数为3+COLUMN(A1)-COLUMN($A1)
,由于我们的公式要向右拉,所以要找一下数学规律,从M3单元格开始,依次需要返回的列号是3+0、3+1、3+2、3+3……,所以M3单元格公式的第三参数是
3+COLUMN(A1)-COLUMN(A1)
,此时再右拉一格,N3单元格公式的第三参数变成了
3+COLUMN(B1)-COLUMN(B1)
,此时我们要想办法让3后面的两个列号相减得到1,使用B1列号减A1列号就可以得到1,所以需要将第二个COLUMN的引用单元格列号锁定,所以最终得到公式第三参数为3+COLUMN(A1)-COLUMN($A1)
。
4.IFERROR(正确时返回值,错误时返回值)函数
其作用是定义发生错误时的应对措施。正确时返回第一参数,错误时返回第二参数。
五、制作柱形图
选中数据M3:U6,点击插入选项,选择柱形图,修改图表标题,就可得到A组球队的成绩柱形图如下。
好啦,今天的教程就到这里,你做出来了吗?快试试修改单元格M1的内容为B,看看数据区域M3:U6是否会变化,柱形图是否会自动变化,如果变成了下面的样子,则表明你制作的动态图表成功啦~