在Excel中创建折线图时，您将沿 X 轴和Y 轴(Y axis)绘制数据点。这对于随时间推移趋势数据很有用，但如果您还想趋势这些数据点与其“理想”的距离或它们随时间变化的程度如何？
趋势误差和标准偏差是人们在Excel图表中使用误差条功能的最常见原因。(error bar feature)在Excel中添加误差线时，您可以查看图表中每个标记的标准误差或偏差(error or deviation)。
但是，您可以将误差线用于多种目的。基本上(Basically)，只要您想在单个数据点旁边包含高点和低点，误差线都可以提供帮助。
Excel中的误差线可用于面积图、条形图、柱形图、折线图、散点图和气泡图。
误差幅度和标准偏差(Error & Standard Deviation)
在学习如何在Excel中添加误差线之前，了解误差范围和标准差是很重要的。
- 误差(Margin of error)范围是数据点的“不确定性”。当数据来自构成较大总体的样本时，这通常用于统计中。误差幅度告诉您来自该样本的数据可能与整个总体的“真实”结果相差多远。
- 标准偏差(Standard deviation)是用于计算误差范围的组件。标准差是衡量数据分散程度的指标。它告诉您数据点在所有数据点的总体平均值或平均值(average or mean)周围分布了多少。
您可以自己计算误差范围和标准差（Excel 甚至提供标准差函数(Excel even provides standard deviation functions)）。或者，您可以在Excel(Excel)中添加误差线，让Excel为您进行计算。
如何在 Excel 中添加误差线
要在Excel(Excel)中添加误差线，您需要从已创建的现有图表开始。
1. 首先，单击图表，然后选择图表右上角的(right corner)图表元素按钮(Chart Elements button)（+ symbol）。
2. 选中误差线框(Error Bars box)以启用图表中的误差线。然后，选择错误栏选择(Error Bars selection)右侧的箭头。
3. 您看到的弹出框为您提供了几个选项来配置误差线将在图表中显示的误差量。(error amount)
在您可以选择其中一个预配置选项之前，了解每个选项的含义及其工作原理非常重要。
Excel 中的错误栏选项
您可以从Excel中的三个预配置(Excel)错误栏(error bar)选项中进行选择。
- 标准误差(Standard Error)：显示每个数据点的标准误差
- 百分比(Percentage)：Excel 计算并显示每个数据点的特定错误百分比(error percentage)
- 标准偏差(Standard Deviation)：Excel 计算并显示所有值的标准偏差（一个值）
标准差的实际计算有些复杂，超出了本文的范围。
如果您想自己计算标准偏差并显示该值，您可以这样做。
在错误栏下拉框中(error bar dropdown box)，选择列表底部的更多选项。(More Options)这将打开错误栏选项窗口(Error Bar Options window)。选择顶部的图形图标以切换到(graph icon)垂直误差条(Vertical Error Bar)选项。
在Error Amount下，您可以选择Fixed value、Percentage或Standard deviation (s) 并在(Standard deviation)数字字段(number field)中键入一个值来指定这些数量。选择标准误差(Choose Standard error)以仅显示所有数据点的标准误差。
或者，您可以选择自定义(Custom)，然后从电子表格中选择计算出的标准差。
选择具有正误差值(Positive Error Value)和负误差值(Negative Error Value)的标准差计算(deviation calculation)的单元格。
这将显示代表数据点总体偏差的常数值。这可能是一个很宽的范围（如上面的示例），因此您可能必须将 y 轴刻度调整到范围的下端，使其不会显示(t display)在 x 轴下方。
在 Excel 中自定义误差线
如果您已计算电子表格中的误差范围，则使用自定义误差线功能(Custom Error Bars feature)会更加有用。这是因为误差条将显示图表中每个数据点上方和下方的值范围，这些值表示折线图(line graph)中每个点的误差范围。
其他自定义错误栏(custom error bar)选项可让您微调这些栏的显示方式：
- 方向(Direction)：仅在上方（加号）、仅下方（减号(Minus)）或上方和下方（两者）显示错误线。(error line)
- 结束样式(End Style)：如果您想要在误差(error bar)线的每一端有一条小的水平线，请选择Cap，如果您只想要垂直线，请选择No Cap 。
如果您选择绘画图标(paint icon)或五边形图标(pentagon icon)，您将看到许多其他方法可以自定义Excel中误差线的外观。
其中包括更改误差线类型和颜色(error bar line type and color)、透明度和宽度(transparency and width)等等。大多数人将这些设置保留为默认设置，但如果您想微调误差线在图表中的显示方式，他们知道它们是可用的。
你应该在 Excel 中添加错误栏吗？
通常，除非您正在执行统计计算并且需要显示您正在分析的样本数据集存在的错误大小(error size)，否则图表上不需要误差线。
当您尝试使用数据传达相关性或结论时，误差线实际上非常重要，以便您的听众了解这些计算的准确性。
How To Add Error Bars In Excel
When you create line charts in Excel, you’re plotting data points along an X and a Y axis. This is useful for trending data over time, but what if you also want to trend how far those data points are from their “ideal” or how much they vary over time?
Trending margin of error and standard deviation is the most common reason people use the error bar feature in Excel charts. When you add error bars in Excel, you can view a standard error or deviation for every marker in the chart.
However, you can use error bars for a variety of purposes. Basically, anytime you want to include high and low points alongside individual data points, error bars can help.
Error bars are available in Excel for area, bar, column, line, scatter, and bubble charts.
Margin of Error & Standard Deviation
Before you can learn how to add error bars in Excel, it’s important to understand what both margin of error and standard deviation are.
- Margin of error is the “uncertainty” of the data point. This is commonly used in statistics when the data is from a sample that makes up a larger population. The margin of error tells you how far the data from that sample could vary from the “real” result for the entire population.
- Standard deviation is a component that’s used to calculate margin of error. The standard deviation is a measure of how spread out your data is. It tells you how much the data points are spread out around the overall average or mean of all data points.
You could calculate margin of error and standard deviation for yourself (Excel even provides standard deviation functions). Or, you can add error bars in Excel and let Excel do the calculations for you.
How To Add Error Bars In Excel
To add error bars in Excel, you need to start with an existing graph you’ve already created.
1. To get started, click on the chart and then select the Chart Elements button (the + symbol) at the upper right corner of the chart.
2. Check the Error Bars box to enable error bars in your graph. Then, select the arrow to the right of the Error Bars selection.
3. The pop-up box you see provides you with several options to configure the error amount that the error bars will display in the chart.
Before you can select one of the preconfigured options, it’s important to understand what each option means and how it works.
Error Bar Options In Excel
You can choose from three pre configured error bar options in Excel.
- Standard Error: Displays the standard error for each data point
- Percentage: Excel calculates and displays the specific error percentage for each data point
- Standard Deviation: Excel calculates and displays the standard deviation (one value) for all values
The actual calculation for standard deviation is somewhat complex and beyond the scope of this article.
If you want to calculate the standard deviation yourself and display that value instead you can do that.
In the error bar dropdown box, select More Options at the bottom of the list. This will open the Error Bar Options window. Select the graph icon at the top to switch to the Vertical Error Bar options.
Under Error Amount, you can select Fixed value, Percentage, or Standard deviation(s) and type a value into the number field to specify those amounts. Choose Standard error to display just the standard error for all data points.
Or, you can select Custom, and choose your calculated standard deviation from your spreadsheet.
Select the cell with the standard deviation calculation for both the Positive Error Value and the Negative Error Value.
This displays the constant value that represents the overall deviation of data points. This could potentially be a wide range (like in the example above), so you might have to adjust the y-axis scale to the lower end of the range so it doesn’t display below the x-axis.
Customizing Error Bars In Excel
Using the Custom Error Bars feature is even more useful if you’ve calculated the margin of error in your spreadsheet. This is because the error bars will then display the range of values above and below each data point in the chart that represent the range of where the error lies at every point in the line graph.
Other custom error bar options that let you fine-tune how these bars display:
- Direction: Display the error line either only above (Plus), only below (Minus), or above and below (Both).
- End Style: Choose Cap if you want a small horizontal line at each end of the error bar, or choose No Cap if you only want the vertical line.
If you select the paint icon or the pentagon icon, you’ll see many other ways you can customize how error bars look in Excel.
These include changing the error bar line type and color, transparency and width, and much more. Most people leave these settings as default, but know that they’re available if you want to fine tune how your error bars display in your chart.
Should You Add Error Bars In Excel?
Usually, error bars aren’t necessary on graphs unless you’re performing statistical calculations and need to show the error size that exists for the sample data set you’re analyzing.
Error bars are actually very important when you’re trying to convey correlations or conclusions using data, so that your audience understands how accurate those calculations are.
