巧用Excel股价图制作箱线图

箱线图（Box Plot）是展示数据分布的重要统计图表，它直观地呈现最小值、第一四分位数（Q1）、中位数、第三四分位数（Q3）和最大值。虽然Excel 2016及以上版本提供了内置的箱线图，但早期版本或更复杂的自定义需求常让人困扰。其实，Excel的股价图可以巧妙转化为箱线图，方法简单，效果专业。

一、理解股价图与箱线图的对应关系

Excel的“开盘-盘高-盘低-收盘”股价图（以及带成交量的变体）本质上是五个数据系列的组合。我们可以将箱线图的五个统计量映射到这些系列：

• 最小值 → 盘低（Low）
• 第一四分位数（Q1） → 开盘（Open）
• 中位数 → 收盘（Close）
• 第三四分位数（Q3） → 盘高（High）？
• 最大值 → ？

注意：标准的“开盘-盘高-盘低-收盘”只有四个系列，而箱线图需要五个。我们可以利用“成交量-开盘-盘高-盘低-收盘”图（即五个系列），将成交量系列用于最大值或Q3？实际上更好的方案是：将Q3设为盘高，最大值设为“成交量”系列，但成交量系列在图表中会显示为柱形，需要隐藏或调整。或者，我们可以只使用四个系列的股价图，通过误差线或其他技巧模拟第五个点。更简单的方式是：使用“带成交量的股价图”，将成交量数据设为最大值，然后隐藏成交量柱体，只留下线型。具体映射如下：

但实际做出来，箱体的上下边界应分别为Q1和Q3，而须线延伸至最小值和最大值。如果我们把Q3映射为盘高，那么箱体上边界就是盘高，但盘高通常比Q3大，会导致箱体变形。所以更好的方法是：不要直接使用标准的股价图系列，而是通过组合图或调整数据顺序来实现。

二、数据准备

我们以一组示例数据说明。假设有10个观测值：

数据：2, 5, 6, 7, 8, 9, 10, 12, 15, 20

计算五数概括：

• 最小值：2
• Q1：6
• 中位数：8.5
• Q3：12
• 最大值：20

在Excel中按以下结构准备数据（假设数据放在A1:E2区域，第一行为系列名称，第二行为数值）：

注意：我们将最小值设为盘低，Q1设为开盘，中位数设为收盘，Q3设为盘高，最大值设为额外系列（如叫“实收”或“成交量”）。但这样箱体上边界为盘高（Q3），下边界为开盘（Q1），中位线为收盘，须线则从开盘/盘高延伸至最小/最大值？不，股价图中的盘低和盘高本身就是上下影线，所以设置正确的话，图表会自动绘制出从开盘到盘高的箱体（实际是开盘和收盘之间的区域着色，盘高盘低为影线）。然而，开盘和收盘通常位于箱体内部，而盘低和盘高是极值。在我们的设置中，开盘（Q1）小于收盘（中位数），且盘高（Q3）大于收盘，盘低（最小值）小于开盘，所以股价图会正确显示：下影线从盘低到开盘，箱体从开盘到收盘，上影线从收盘到盘高。但这样缺乏Q3到最大值的部分，因为我们将Q3设为盘高，最大值设为额外系列。实际上，我们需要的是上影线延伸到最大值，而箱体上边界是Q3。为了达到这个效果，我们可以调整系列顺序：将最大值设为盘高，Q3设为额外系列（比如成交量），但这样箱体上边界会变成最大值，导致箱体过长。最简洁的方法是放弃使用成交量系列，而采用“开盘-盘高-盘低-收盘”图，但通过添加误差线来补足最大值和最小值？不，已经有最小值和最大值了。实际上，如果我们将Q1设为开盘，中位数设为收盘，最小值设为盘低，Q3设为盘高，那么箱体就变成了从Q1到Q3？不对，股价图的箱体是开盘到收盘之间的区域，如果开盘<收盘则箱体为开盘到收盘，而盘低和盘高为影线。所以我们需要让开盘=Q1，收盘=Q3？但中位数怎么表示？标准箱线图的中位数是箱体中的一条线。如果我们让开盘=Q1，收盘=Q3，那么箱体就是合适的，但中位数需要用另一个系列表示，比如添加一个股价图中的“收盘”系列？那样会覆盖。更好的方法是：使用“开盘-盘高-盘低-收盘”图，但将四个系列的意义重新定义：

• 开盘 = Q1
• 收盘 = Q3
• 盘低 = 最小值
• 盘高 = 最大值

这样，箱体从Q1到Q3，影线延伸到最小和最大值，但缺少中位数线。我们可以通过添加一个单独的散点系列来表示中位数，并显示为横线。这个方法最灵活。

三、创建图表步骤

方法一：使用股价图+散点图（推荐）

1. 准备数据：在A1:D2区域放入：
   A1:盘低, B1:开盘, C1:收盘, D1:盘高
   A2:最小值, B2:Q1, C2:Q3, D2:最大值。注意：收盘这里用的是Q3，开盘是Q1。
2. 选择数据，插入“股价图”中的“开盘-盘高-盘低-收盘”图。此时图表显示箱体从Q1到Q3，下影线到最小值，上影线到最大值。
3. 添加中位数：在E1输入“中位数”，E2输入中位数值（8.5）。选中图表，右键“选择数据”，添加系列，系列名称选E1，X轴系列值选择E2（因为只有一个点，需要指定X轴位置？对于散点图，X需设为类别轴值，通常为1）。更简单：新建一个散点图系列，X值为1（或与原来相同的类别），Y值为中位数。具体：右键图表-选择数据-添加系列，系列名称“中位数”，X轴系列值输入=Sheet1!$F$1（假设F1为1），Y轴系列值输入=Sheet1!$E$2。然后右键新系列-更改系列图表类型，改为“带直线的散点图”。此时图表上会出现一个点，我们需要让它变成横线：右键该系列-设置数据系列格式-线条颜色-实线，标记-无。另外，需要调整X轴范围使线条贯穿箱体：如果类别轴是文本型，散点图的X值需要与类别对应，通常将X值设为0.5或其他数值。可以右键横坐标轴-设置坐标轴格式-坐标轴选项，将“坐标轴类型”改为“日期坐标轴”或“文本坐标轴”？或者更简单：直接使用误差线绘制中位线。但散点图方法需小心调整。
4. 美化：去除网格线，调整颜色，添加数据标签等。

方法二：使用带成交量的股价图（调整数据）

1. 数据布局：盘低=最小值，开盘=Q1，收盘=中位数，盘高=Q3，成交量=最大值。
2. 插入“成交量-开盘-盘高-盘低-收盘”图。
3. 此时图表包含柱形（成交量）和K线。我们需要隐藏成交量柱形：右键成交量系列-设置数据系列格式-填充-无填充，边框-无线条。同时调整K线格式：选择任意一个K线，设置颜色等。
4. 这样，K线的箱体从开盘（Q1）到收盘（中位数），影线从盘低（最小值）到盘高（Q3），但缺失了Q3到最大值的部分，因为盘高是Q3而非最大值。所以这个方法实际上只显示了四个点的箱线图，缺少上须。不推荐。

四、格式调整技巧

• 箱体颜色：选中K线，右键“设置数据系列格式”，在“填充”中选择纯色填充，并勾选“颜色”可调；在“边框”中设置线条颜色和宽度。
• 须线样式：K线的影线默认是黑色细线，可在“系列选项”中调整“线条”的粗细和颜色。
• 中位线：如果使用散点图添加中位数线，需将其设置为与K线同宽，并可能使用线条类型为“十字形”或“上减下”等。也可使用误差线：先添加一个误差线系列，然后设置负偏差和正偏差为0，仅保留中线。
• 坐标轴：通常箱线图不需要显示横轴标签或保留一条基线，可删除或隐藏。

五、总结

通过巧妙利用股价图，我们可以在Excel中制作出符合统计规范的箱线图，尤其适用于没有内置箱线图功能的Excel版本。虽然步骤稍多，但一旦掌握，便能灵活扩展。建议使用“开盘-盘高-盘低-收盘”股价图配合散点图中位数线的方法，效果最佳。记得根据实际数据调整系列顺序和格式，确保四个统计量正确对应。赶快动手试试吧！