Excel动画图表示例：Excel也可以创建可视化的随时间而变化的排名

excelperfect

标签：Excel图表

好的动画图表，能够更生动地讲述数据背后的故事。

本文示例使用Excel图表以动画的方式显示数据随时间的变化。（注：本文学习整理自myonlinetraininghub.com，可在该网站下载示例，也可到知识星球完美Excel社群中下载示例。）

准备

本文列出的创建动画图表的步骤并不是孤立地考虑的，必须考虑整个过程。需要什么原始数据？如何将其聚合以显示想要什么？在绘制图表之前，是否需要对聚合数据进行进一步处理？使用哪种类型的图表？哪些数据（和其他信息）对查看者有用？需要VBA来自动化所有这些？

在继续之前，以上内容都需要考虑，至少要找到初步答案。如果没有把整个过程考虑清楚，可能会发现遇到了一些障碍，在那里有些东西不会像希望的那样工作。

收集数据

首先要做的是决定需要显示哪些数据，然后将其收集在一起。

这很容易解决，因为知道要显示一支球队在排行榜上的位置，所以需要计算他们在获胜或平局中的得分。

如果球队得分相等，首先计算出他们的进球差（进球数-失球数），然后再看进球数。

如果两支球队都有下图1所示的数据。

图1

两队都赢了3场比赛，获胜得3分，所以得9分；双方都打了一场平局，平局得1分。所以他们的总分是10分。

要决定哪支球队在积分榜上名列前茅，现在需要看一下进球差，如果是相同的，那么就看进球数。两支球队的进球差距都是6，所以需要看看他们的进球情况。United进了12球，Wanderers进了9球，所以United高于Wanderers。

计算排位的公式是：

得分Points+ (进球差GD * 0.001) + (得球GS * 0.0001)

United的排位分是：

10 + 0.006 +0.0012 = 10.0072

Wanderers的排位分是：

10 + 0.006 +0.0009 = 10.0069

具有更大值的United在表中排名更高。

稍后将看到为什么计算出这个值很重要，以及为什么选择了0.001和0.0001的比例因子。

下图2是将每周数据输入表格的情况。

图2

创建数据透视表

数据透视表非常适合此情况，它可以按照图表中需要的方式对数据进行汇总。

图3

球队是按字母顺序排列的，稍后在绘制图表之前会进行整理，可以看到他们的得分、进球差和得球数。

筛选是周数，因此通过更改它，数据透视表将显示季节中该周的数据。

创建要绘制图表的数据

显然，图表需要显示每个队的得分。但正如已经提到的，它还需要能够根据进球差和得球数来区分哪个球队在积分榜上名列前茅。

还想使用每个球队的俱乐部徽章来显示与该俱乐部相关的数据。

使用簇状条形图，可以完成所有这些。系列1是俱乐部的实际得分。系列2是调整后的积分，考虑到进球差和进球得分，可以使用俱乐部的徽章作为本系列的标签。

为了创建所需的数据，创建了一个表，该表使用GETPIVOTDATA从数据透视表中获取值。在这个阶段，表仍然是按俱乐部名称的字母顺序排列的。

图4

然后使用SORT函数，可以根据第三列（得分+球差+得球）得到一个正确排序的表格。

图5

注意到得分（Points）和Points+GD+GS的整数部分是不一样的。

如果只是用公式：

得分Points+ (进球差GD * 0.001) + (得球GS * 0.0001)

计算表中Liverpool的值是86和86.056。但因为在图表上绘制了两个系列，不希望每个系列的标签重叠，所以在Points+GD+GS上添加了一个小值，使其沿x轴稍微移动，这样它就不会位于系列1标签的顶部。

对Points+GD+GS使用的实际公式是：

得分Points+ (进球差GD * 0.001) + (得球GS * 0.0001) + ($I$1*0.1)

其中，$I$1是周数。

注意：之前提到，为GD和GS选择了小比例因子。这是因为不希望有明显改变图表上条形图长度的值，只需要一个非常小的差异，让球队在相同的点上被分开。

创建和格式化图表

1.选择要绘制的数据

图6

2.选择簇状条形图

从功能区“插入”选项卡“图表”组中“簇状条形图”，结果如下图7所示。

图7

3.反向分类顺序

选择垂直坐标轴标签，单击鼠标右键，选取“设置坐标轴格式”命令，在“坐标轴选项”中选取“逆序类别”前的复选框，如下图8所示。

图8

现在的图表如下图9所示。系列1（蓝色）将显示每队的得分，系列2的条形比系列1长，将用于显示球队徽章。

图9

4.删除图例。

5.格式化系列2

选择系列2，单击鼠标右键，选取快捷菜单中的“设置数据系列格式”命令，选取“无边框”和“无线条”，如下图10所示。

图10

在“系列选项”中，将系列重叠设置为100%，分类间距设置为45%。

图11

6.对系列2添加标签，如下图12所示。

图12

7.隐藏系列2数据标签中的文本

右键单击数据标签，从快捷菜单中选择“字体”命令，设置字体颜色为白色，字体大小为1。这有效地隐藏了标签中的数字，因此它们不会掩盖将使用的俱乐部徽章。

图13

8.为系列2标签设置图像

仍然选择系列2数据标签，单击鼠标右键，从快捷菜单中选择“设置数据标签格式”命令。在“标签选项”中选取“图片或纹理填充”，如下图14所示。

图14

后面将使用VBA添加球队徽章。

9.格式化系列1

选择系列1，单击添加数据标签，显示每队的得分，如下图15所示。

图15

VBA驱动动画

现在转向VBA，它需要使所有这些都工作起来。

首先希望每个条形都使用球队的颜色。

团队队徽是与该工作簿存储在同一文件夹中的图像。

为了存储所有这些数据，使用了三个集合。颜色集合存储球队主颜色及其高亮颜色的RGB值，第三个集合存储队徽图像文件的名称。

MainColour.Add RGB(253,185, 19), “Wolves”

HighlightColour.AddRGB(0, 0, 0), “Wolves”

Crests.Add “Wolves.png”,“Wolves”

要创建动画，代码需要绘制每周的数据。为了将所选周的数据输入到表中，从而绘制到图表中，代码会更改透视表上的筛选器。此筛选器是工作表中单元格I1中的值。

因此，数据透视表中的数据流入计算表，计算表依次输入排序表。该图表绘制了排序表中的数据。

当然，排序表中团队的顺序与图表中显示的顺序相同。随着代码进度遍历每周，图表数据会发生变化，轴标签会正确反映排行榜。

但应用于条形的格式仍保留在该条形上，它不会仅仅因为球队的位置改变而改变。

使用排序表，代码知道该表中最顶层的球队是图表中的顶层球队。表中第二队在图表中排名第二，等等。

通过从上到下依次检查每个条形，并使用排序表中的球队顺序，可以为每个条形应用正确的颜色和徽章。

联盟中有20支球队，所以代码使用一个从1到20的变量计数器对每支球队进行计算。

从显示球队得分的系列1开始，填充颜色设置为球队的主颜色。

可以使用从S列的排序表中检索球队名称。

Range(“S3”).Offset(counter,0).Value

球队名称是从MainColor系列中获取RBG颜色值的关键。从HighlightColor集合以相同的方式返回条形图轮廓颜色。

'设置条形颜色

With ActiveChart.FullSeriesCollection(1).Points(counter).Format

.Fill.ForeColor.RGB = MainColour(Range(“S3”).Offset(counter,0).Value)

.Line.Visible = msoTrue

.Line.Weight = 1.2

.Line.ForeColor.RGB = HighlightColour(Range(“S3”).Offset(counter,0).Value)

End With

然后，对于系列2标签，代码使用球队名称从Crests集合返回队徽图像文件的名称。

'设置球队队徽

ActiveChart.FullSeriesCollection(2).Points(counter).DataLabel.Select

With Selection.Format.Fill

.Visible = msoTrue

.UserPicture PictureFile:=Crests(Range(“S3”).Offset(counter,0).Value)

Selection.Width = 20

Selection.Height = 20

End With

完成后，可以更新图表标题，增加周数，它跟踪显示数据的周数，然后代码循环并再次执行所有操作。

ActiveChart.ChartTitle.Text= “Premier League Points: Week” & WeekNum

WeekNum =WeekNum + 1

效果如下图16所示。

图16

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。 欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。