Microsoft Excel使您可以使用各种图表轻松组织、呈现和分析数据。一个特别强大的图表是箱线图(也称为箱线图),旨在帮助显示数据集中值的分布。
在本文中,我们将介绍如何在Microsoft Excel中创建箱线图,涵盖Excel 365和尚未升级的旧版本。
箱线图:它们是什么?
箱线图是一种显示数据的方法,有助于可视化统计质量,例如数据的分布和可变性。它显示了一个条(盒子)分成两部分,线条(胡须)延伸到盒子的任一侧。
这些元素中的每一个都可视化了一组数字数据的五个数字摘要。它们看起来像这样,可以水平或垂直显示:
为了理解五数总结,我们来看一个样本数据集。
25、26、28、30、32、34、37、38
- 最低。数据集中的最小值。这是左/下晶须的左/底部的端点。
- 第一个四分位数。这是找到 25% 数据点的值。
- 第二个四分位数。这是中位数。它相当于“中间值”。
- 第三个四分位数。这是找到 75% 的数据点的值。
- 最大值。数据集中的最大值。
如何在Excel 365中创建(Excel 365)箱线图(Box Plot)
在Office 365中,Microsoft Excel包含作为图表模板的箱线图,可以轻松为您的数据创建可视图。如果您不确定如何使用Excel,请先学习基础知识(learn the basics first)。
要创建箱线图:
- 打开一个新工作表并输入您的数据。
- 通过单击和拖动选择您的数据集。
- 在功能区中,选择插入(Insert)选项卡。
4.单击插入统计图表(Click Insert Statistic Chart),然后单击Box and Whisker。
注意:如果您插入一个空图表,您可以通过选择图表设计(Chart Design)选项卡并单击选择数据(Select Data)来输入您的数据集。
Excel现在将创建一个基本的方框和晶须图。但是,您可以进一步自定义此Excel图表,以准确显示您想要的统计数据。
如何在Excel 365中格式化(Excel 365)箱线图(Box Plot)
Excel允许您以多种方式设置箱线图设计的样式,从添加标题到更改显示的关键数据点。
图表设计(Chart Design)选项卡允许您添加图表元素(如图表标题、网格线和标签)、更改布局或图表类型,以及使用内置图表样式模板更改框和须线的颜色。
格式(Format)选项卡允许您微调颜色选择、添加文本以及为图表元素添加效果。
要添加更多显示元素,请右键单击箱须图并从下拉菜单中
选择格式化数据系列。(Format Data Series)
选项包括:
- 显示(Show)内点。这会将所有单个数据点显示为第一和第三四分位数内的圆圈。
- 显示(Show)异常点。这会将异常值(异常高或低的数据点)显示为图外的圆圈。
- 显示(Show)平均标记。这会将平均值显示为图表中的十字。
- 显示(Show)平均线。这会在多个数据集的平均点之间显示一条线。
- 四分位数计算。如果您有奇数个数据点,您可以选择通过包括或不包括中位数来计算四分位数。对于较大的数据集,您应该使用独占四分位数范围,对于较小的数据集,包含中位数方法通常更准确。
如何在旧版本(Older Versions)的Excel中创建箱(Box)线图(Whisker Plot)
由于旧版本的 Excel(versions of Excel)(包括Excel 2013和Excel 2016)不包含用于盒须图的模板,因此创建一个要困难得多。
首先,使用以下公式计算四分位数并创建一个表格:
- 最小值:MIN(单元格范围)
- 第一个四分位数:QUARTILE.INC(单元格范围,1)
- 中位数:QUARTILE.INC(单元格范围,2)
- 第三四分位数:QUARTILE.INC(单元格范围,3)
- 最大值:MAX(单元格范围)
注意:对于单元格范围,拖动并选择您的数据集。
接下来,在单独的表格中计算四分位数差异(这些与盒子高度有关):
- Q1的价值
- 中位数减去第一季度
- Q3 减去中位数
- 最大值减去 Q3
- Q1 减去最小值
然后,您可以使用这些值创建图表:
- 单击(Click)插入选项卡,然后选择插入(Insert)柱形图(Insert Column)或条形图(Bar Chart)。
- 单击堆积柱形图(Click Stacked Column Chart)。如果图表显示不正确,请选择图表设计(Chart Design)选项卡,然后单击Switch Row/Column。
- 右键单击(Right-click)图形中表示“ Box 1 – hidden”的部分,然后单击“填充(Fill)”,然后单击“无填充(Fill)” 。
添加顶部晶须:
- 单击(Click)顶部框并选择图表设计(Chart Design)选项卡。
- 单击添加图表元素。
- Click Error Bars > More Error Bars Options。
- 在方向(Direction)下,单击加(Plus)号。在错误数量(Error Amount)下单击Custom > Specify Value。
- 将(Replace)Positive Error Value替换为您为Whisker Top计算的值。
添加底部晶须:
- 单击隐藏的框。
- 在图表设计(Chart Design)选项卡下,选择添加图表元素(Add Chart Element)。
- 单击错误栏>更多错误栏选项。
- 在Direction下,单击Minus并在Error Amount下单击Custom > Specify Value。
- 在对话框中,将Negative Error Value替换为您为(Negative Error Value)Whisker Bottom计算的值。
现在,您的数据集有了一个基本的箱须图。您可以通过添加平均线或点、更改颜色和更改图表样式来进一步自定义。
统计分析从未(Has)如此简单(Been Easier)
幸运的是,有了更新、更强大的程序版本,可视化和分析数据变得更加简单。通过本教程,您应该牢牢掌握如何使用箱须图以及如何在Excel工作簿中进行设置。
How to Create a Box Plot in Microsoft Excel
Microsoft Excel makes it easy for you to organize, present, and analуze data using various сharts. A particularly powerful chart is the box and whisker plot (also known as a box plot), designed to help display the distributiоn of valuеs in a data set.
In this article, we’ll cover how you can create a box plot in Microsoft Excel, covering both Excel 365 and older versions for those yet to upgrade.
Box Plots: What Are They?
A box plot is a method of displaying data that helps visualize statistical qualities like the spread and variability of the data. It shows a single bar (the box) split in two, with lines (the whiskers) extending to either side of the box.
Each of these elements visualizes the five-number summary of a set of numerical data. They look like this and can be displayed horizontally or vertically:
To understand the five-number summary, let’s take a look at a sample data set.
25, 26, 28, 30, 32, 34, 37, 38
- The minimum. The minimum value in the data set. This is the endpoint to the left/bottom of the left/lower whisker.
- The first quartile. This is the value under which 25% of data points are found.
- The second quartile. This is the mediant. It amounts to the “middle value”.
- The third quartile. This is the value above which 75% of data points are found.
- The maximum. The maximum value in the data set.
How to Create a Box Plot in Excel 365
In Office 365, Microsoft Excel includes box plots as a chart template, making it easy to create a visual plot for your data. If you aren’t sure how to use Excel, learn the basics first.
To create a box plot:
- Open a new worksheet and input your data.
- Select your data set by clicking and dragging.
- In the ribbon, select the Insert tab.
4. Click Insert Statistic Chart then Box and Whisker.
Note: If you insert an empty chart, you can input your data set by selecting the Chart Design tab and clicking Select Data.
Excel will now create a bare-bones box and whisker chart. However, you can customize this Excel chart further to display your statistical data exactly how you’d like it.
How to Format a Box Plot in Excel 365
Excel allows you to style the box plot chart design in many ways, from adding a title to changing the key data points that are displayed.
The Chart Design tab allows you to add chart elements (like chart titles, gridlines, and labels), change the layout or chart type, and change the color of the box and whiskers using built-in chart style templates.
The Format tab allows you to fine-tune your color selections, add text, and add effects to your chart elements.
To add further display elements, right-click the box and whisker plot and select Format Data Series from the drop-down menu.
The options include:
- Show inner points. This displays all individual data points as circles inside the first and third quartiles.
- Show outlier points. This displays outliers (abnormally high or low data points) as circles outside the plot.
- Show mean markers. This displays the mean value as a cross within the chart.
- Show mean line. This displays a line between the average points of multiple data sets.
- Quartile calculation. If you have an odd number of data points, you can choose to calculate the quartiles by either including or excluding the median. For larger data sets, you should use the exclusive interquartile range, and for smaller data sets the inclusive median method is generally more accurate.
How to Create a Box and Whisker Plot in Older Versions of Excel
As older versions of Excel (including Excel 2013 and Excel 2016) don’t include a template for the box and whisker chart, creating one is much more difficult.
First, calculate your quartile values using the following formulae and create a table:
- Minimum value: MIN(cell range)
- First quartile: QUARTILE.INC(cell range,1)
- Median: QUARTILE.INC(cell range, 2)
- Third quartile: QUARTILE.INC(cell range, 3)
- Maximum value: MAX(cell range)
Note: For cell range, drag and select your data set(s).
Next, calculate the quartile differences in a separate table (these relate to the box heights):
- The value of Q1
- The median minus the Q1
- Q3 minus the median
- The maximum value minus the Q3
- Q1 minus the minimum value
You can then create a chart using these values:
- Click the Insert tab then select Insert Column or Bar Chart.
- Click Stacked Column Chart. If the chart isn’t displaying correctly, select the Chart Design tab then click Switch Row/Column.
- Right-click the part of the graph that represents “Box 1 – hidden” and click Fill then click No Fill.
To add the top whisker:
- Click the top box and select the Chart Design tab.
- Click Add Chart Element.
- Click Error Bars > More Error Bars Options.
- Under Direction, click Plus. Under Error Amount click Custom > Specify Value.
- Replace the Positive Error Value with the value you calculated for Whisker Top.
To add the bottom whisker:
- Click the hidden box.
- Under the Chart Design tab, select Add Chart Element.
- Click Error Bars> More Error Bars Options.
- Under Direction, click Minus and under Error Amount click Custom > Specify Value.
- In the dialog box, replace the Negative Error Value with the value you calculated for Whisker Bottom.
You now have a basic box and whisker plot for your data set. You can customize this further by adding a mean line or dot, changing the colors, and altering the chart style.
Statistical Analysis Has Never Been Easier
Luckily, with newer, more powerful versions of the program, visualizing and analyzing data has become much simpler. With this tutorial, you should have a firm grasp of how a box and whisker plot is used and how you can set one up in an Excel workbook.