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

wufei123 2024-05-24 阅读:13 评论:0

在之前的文章《excel图表学习之通过案例，聊聊怎么绘制量筒式柱形图》中，我们了解了绘制量筒式柱形图的方法。而今天我们再分享一个excel图表教程，聊一个让excel图表像网页一样动起来的方法，只要输入关键字，表格数据和图表就会自动改变，特...

在之前的文章《excel图表学习之通过案例，聊聊怎么绘制量筒式柱形图》中，我们了解了绘制量筒式柱形图的方法。而今天我们再分享一个excel图表教程，聊一个让excel图表像网页一样动起来的方法，只要输入关键字，表格数据和图表就会自动改变，特别是公司的数据需要分部门统计时，简直太方便啦！

在我们浏览网页的时候，网页会根据我们的选择来展示不同的内容，有没有想过，把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组球队的成绩柱形图如下。