怎么制作不同比例的Excel面板图

Excel教程 2022-01-02 14:03:55

面板图就是分成几个平行的面板，每个面板显示图表中的部分数据的图表。

很多情形下，面板图非常适合显示不同数值范围的数据，甚至可以在不同大小的面板上显示。例如，可以在图表的大面板中查看股票价格，而在其下方较窄的面板中可以看到其他相关的指数，这些面板使用相同的日期坐标轴。或者，工程显示器可以在水平面板中显示多个参数，这些参数共享一个时间坐标轴，或者可能显示一个应用电压的数值轴，或者可能显示一个化学成份浓度的数值轴。

目标

当在图表中显示的数据组之间的范围相差很大时，合理布局并显示这些数据。

示例数据

如下图1所示，每列之间的数据范围相差极大，列A中的为百分数，列B中的数十万，列C中的从数百万到数千万。想要找到适合所有这些数据的比例是非常困难的。可以在主坐标轴和次要坐标轴上绘制2个系列，然后使用技巧来为第3组数据伪造第3个比例，但这不是一个很好的方法。这个额外的轴占用了图表中大量的空间，并且难以跟踪哪些数据对应于哪个轴。

图1

初步创建图表

如果按照通常的方法，对于图1所示的数据创建折线图，如图2所示。系列A几乎与水平坐标轴重合，系列B似乎离水平坐标轴不远，仅系列C显示正常，可读性差异太大。

图2

可以使用对数刻度，这样稍微好一点，但也带来了问题，三个系列都是几乎没有特征的水平线，且系列A和系列C之间有大量未使用的空间，如图3所示。

图3

还有一种方法是使用主坐标轴和次坐标轴，如图4所示。在主坐标轴中绘制系列A，而在次坐标轴中以对数刻度绘制系列B和系列C。这比上面的方式更好，但必须记住哪个系列对应哪个坐标轴。

图4

还可以创建3个单独的图表，每个系列一个图表，这样更清楚地显示数据，如图5-7所示。如果简单地对齐这3个图表，且将其中的2个图表的日期坐标轴隐藏，将会更好。

图5

图6

图7

但是，它们也存在一个缺点，三个图表左侧边距很难相同。

创建面板图

先对数据进行处理，如图8所示。

图8

黄色单元格区域A1:D7是原始数据，橙色单元格区域E1:G7是实际用于绘制的数据，蓝色单元格区域A9:G11是坐标轴数据。

在行9和行10中列B至列D显示的最小值和最大值数据供设置坐标轴数据时参考，在行9和行11中的列E至列F中为坐标轴的最小值、最大值和主要刻度单位（标记间隔）。

红色单元格区域为每个面板的相对大小，图8显示每个面板都是一个单元的高度，即整个图表高度的三分之一。这样，允许用户改变一个或多个面板的相对大小。

使用单元格区域E9:G10中的最小值和最大值，以及E12:G12中面板的相对尺寸，计算在橙色单元格区域中用于实际绘图的数据值。在单元格E2中的公式为：

=((B2-E$9)/(E$10-E$9)*E$12+SUM($D$12:D$12))/SUM($E$12:$G$12)

公式中，(B2-E$9)/(E$10-E$9)*E$12确定系列A面板内单元格B2中的数据值的位置，而SUM($D$12:D$12)添加相对于每个面板底部的偏移量（本例中系列A为0，在底部的面板中），SUM($E$12:$G$12)在整个图表中缩放面板，公式填充到整个单元格区域E2:G7。

选择单元格区域A1:A7，按下Ctrl键的同时选择单元格区域E1:G7，创建折线图。设置垂直坐标轴的最小值为0，最大值为1，主要刻度单位为0.3333333（3个面板，三分之一）。创建的面板图如图9所示。

图9

在垂直坐标轴上添加标签

使用XY散点图来在垂直坐标轴上添加标签。下图10所示的表中提供了每个面板的Y轴比例和标签的数据。在图10中，列I和列J中是水平坐标轴两侧的数据值，X左侧是开始日期减0.5，X右侧是结束日期加0.5，你可以根据图表情况略作调整。这样，可以将面板的垂直标签值交替添加，例如，第1个面板标签在图左侧，第2个面板的标签在图右侧。

列K至列M的值基于前面在E9:G11中的坐标轴比例参数计算，在单元格K2中的公式：

=E$9

将其填充至K2:M2。

在单元格K3中的公式计算下一个坐标轴标签或者如果最大值已经超过的话就放置#N/A（在折线图或XY散点图中，#N/A不会被绘制）：

=IF(K2+E$11>E$10,NA(),K2+E$11)

将其填充至K3:M12。

列N至列P中是实际的Y值，在单元格N2中的公式：

=((K2-E$9)/(E$10-E$9)*E$12+SUM($D$12:D$12))/SUM($E$12:$G$12)

将其填充至N2:P12。