Excel图表学习之如果让图表像网页一样动起来

时间:2022-12-08
阅读:

在我们浏览网页的时候,网页会根据我们的选择来展示不同的内容,有没有想过,把excel表格也做成“输入关键字,就自动显示对应的数据和图表”的动态表格呢?比如我们要做工资统计时,把部门作为关键字,每输入一个部门名称,就自动显示该部门对应的工资明细和统计图表。

最近世界杯很火,我们就拿世界杯统计数据举例,最终实现的效果如下:

Excel图表学习之如果让图表像网页一样动起来

一、学习目标

动态图表是一种根据我们的选择来实时展示不同信息的图表。此次我们以世界杯小组赛各个球队的比赛成绩为数据制作动态图表,原始数据如下:

Excel图表学习之如果让图表像网页一样动起来

实现目标需要满足的条件是:

  • 1.有一个下拉框,里面有A-H组8个选项;

  • 2.选中任意一个选项,就能自动显示该小组的数据,并自动绘制出相对应的柱形图;

二、制作方法

处理原始数据

在我们得到数据之后,一般都需要对原始数据进行一定的处理,才能作为制图数据。

Excel图表学习之如果让图表像网页一样动起来

如上图,在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列呈现的结果如下图:

Excel图表学习之如果让图表像网页一样动起来

此时可以看到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,点击菜单栏的“数据”,点击“数据验证”,在弹窗中点击“数据验证”

Excel图表学习之如果让图表像网页一样动起来

在弹窗中,设置“允许”为列表,“源”为“A,B,C,D,E,F,G,H”,中间用英文逗号隔开。

Excel图表学习之如果让图表像网页一样动起来

选中单元格M1,就会出现下拉框

Excel图表学习之如果让图表像网页一样动起来

四、制作辅助数据

如下图,我们以区域M2:U6为辅助数据区域,也是真正的制图数据。制作辅助数据区域也很简单,直接将源数据中C2-K2单元格的内容复制到M2-U2单元格区域,然后在M3单元格输入公式:

=IFERROR(VLOOKUP($M$1&ROW(A1),$A$3:$K$34,3+COLUMN(A1)-COLUMN($A1),0),""),右拉下拉可得到下表。

Excel图表学习之如果让图表像网页一样动起来

公式解析:

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组球队的成绩柱形图如下。

Excel图表学习之如果让图表像网页一样动起来

Excel图表学习之如果让图表像网页一样动起来

好啦,今天的教程就到这里,你做出来了吗?快试试修改单元格M1的内容为B,看看数据区域M3:U6是否会变化,柱形图是否会自动变化,如果变成了下面的样子,则表明你制作的动态图表成功啦~

Excel图表学习之如果让图表像网页一样动起来

Excel图表学习之如果让图表像网页一样动起来

返回顶部
顶部