Excel图表学习11：创建具有不同比例的Excel面板图

使用Excel图表绘制具有不同数据比例和高度的Excel面板图。...





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

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

目标

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

示例数据

如下图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。



图10

选择I1:I12，按住Ctrl键的同时选择N1:N12，复制。

选取图9的图表，单击开始选项卡中的粘贴——选择性粘贴，设置如图11所示。



图11

此时的图表如图12所示。



图12

选择新添加的系列，更改其图表类型为XY散点图，如图13所示。



图13

移动添加系列到主坐标轴，如图14所示。



图14

设置横坐标轴刻度值及位置，使新系列位于垂直坐标轴上，如图15所示。



图15

同样的操作添加其它两个新系列，如图16所示。注意，在添加B坐标轴时，选取X-右侧的数据，使其标签线在图表右侧。



图16

删除图例。此时，你可以使用一些加载宏来添加垂直坐标轴标签（我下载了，但没有实现）。下图17是我手工添加标签后的结果。



图17

隐藏作为标签数据的3个新系列的线和标记，同时添加误差线，对于左侧设置正误差，右侧设置负误差，并设置固定值0.1，结果如图18所示。



图18

具有可变高度的面板图

如果想要可变的面板高度，则不能够使用均匀间隔的网格线在面板之间绘制线条。在本例中，仍然使用图表比例为从0到1，使用1为主要单位，而不使用网格线。在面板相交的位置，沿图表一侧添加XY系列点，然后使用误差线绘制线条。

图19为所使用的数据。在单元格区域S1:T1中的X-左侧的值与上面相同，Y-分隔值使用公式计算，在单元格S2中的公式为：

=SUM($E$12:E$12)/SUM($E$12:$G$12)

误差线的长度为日期间隔加1。



图19

下图20为取消水平网格线的图表。



图20

复制R1:T2，选取图表，使用开始选项卡中的粘贴——选择性粘贴，将系列选取为按行，添加的新系列如图21所示。



图21

隐藏该新系列，同时设置误差线为正误差，自定义指定值为单元格S3:T3，结果如图22所示。



图22

此时，我们将系列C的面板高度改变为2，而其它两个系列仍为1，如图23所示。



图23

下图24是设置系列B所在面板高度0.5，其它两个高度为1的结果。



图24

你只需在工作表第12行的红色区域中修改值，图形面板的高度会相应的变化。

当然，可以创建3个以上的面板，并且每个面板可以具有完全不同的尺寸。最重要的是，面板的高度变化不能干扰展示图表中的数据。

本文为研究peltiertech.com上的图表技巧文章《Excel Panel Charts With Different Scales》时整理的学习笔记，转载请注明出处。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎关注[完美Excel]微信公众号：

方法1—在微信通讯录中搜索“完美Excel”或者“excelperfect”后点击关注。

方法2—扫一扫下面的二维码

    关注 完美Excel