excel图表的标尺线

You can add a trendline to a chart in Excel to show the general pattern of data over time. You can also extend trendlines to forecast future data. Excel makes it easy to do all of this.

您可以将趋势线添加到Excel中的图表中，以显示一段时间内数据的一般模式。 您还可以扩展趋势线以预测未来数据。 Excel使所有这些操作变得容易。

A trendline (or line of best fit) is a straight or curved line which visualizes the general direction of the values. They’re typically used to show a trend over time.

趋势线(或最佳拟合线)是可视化值的大致方向的直线或曲线。 它们通常用于显示一段时间内的趋势。

In this article, we’ll cover how to add different trendlines, format them, and extend them for future data.

在本文中，我们将介绍如何添加不同的趋势线，设置其格式并将其扩展以用于将来的数据。

添加趋势线 (Add a Trendline)

You can add a trendline to an Excel chart in just a few clicks. Let’s add a trendline to a line graph.

只需单击几下即可将趋势线添加到Excel图表中。 让我们向折线图添加趋势线。

Select the chart, click the “Chart Elements” button, and then click the “Trendline” checkbox.

选择图表，单击“图表元素”按钮，然后单击“趋势线”复选框。

This adds the default Linear trendline to the chart.

这会将默认的线性趋势线添加到图表中。

There are different trendlines available, so it’s a good idea to choose the one that works best with the pattern of your data.

有不同的趋势线可用，因此最好选择一个与数据模式最匹配的趋势线。

Click the arrow next to the “Trendline” option to use , including Exponential or Moving Average.

单击“趋势 ”选项旁边的箭头以使用 ，包括指数或移动平均线。

Some of the key trendline types include:

一些关键趋势线类型包括：

• Linear: A straight line used to show a steady rate of increase or decrease in values.

  线性：用于显示值稳定增加或减少的直线。

• Exponential: This trendline visualizes an increase or decrease in values at an increasingly higher rate. The line is more curved than a linear trendline.

  指数：此趋势线以更高的速率可视化值的增加或减少。 该线比线性趋势线更弯曲。

• Logarithmic: This type is best used when the data increases or decreases quickly, and then levels out.

  对数：当数据快速增加或减少然后稳定时，最好使用此类型。

• Moving Average: To smooth out the fluctuations in your data and show a trend more clearly, use this type of trendline. It uses a specified number of data points (two is the default), averages them, and then uses this value as a point in the trendline.

  移动平均线：要消除数据中的波动并更清楚地显示趋势，请使用这种趋势线。 它使用指定数量的数据点(默认为两个)，对其求平均值，然后将此值用作趋势线中的点。

To see the full complement of options, click “More Options.”

要查看完整的选项，请单击“更多选项”。

The Format Trendline pane opens and presents all trendline types and further options. We’ll explore more of these later in this article.

“格式趋势线”窗格将打开，并显示所有趋势线类型和更多选项。 我们将在本文后面进一步探讨这些内容。

Choose the trendline you want to use from the list, and it will be added to your chart.

从列表中选择要使用的趋势线，它将被添加到图表中。

将趋势线添加到多个数据系列 (Add Trendlines to Multiple Data Series)

In the first example, the line graph had only one data series, but the following column chart has two.

在第一个示例中，折线图只有一个数据系列，但是下面的柱形图有两个。

If you want to apply a trendline to only one of the data series, right-click on the desired item. Next, select “Add Trendline” from the menu.

如果只想将趋势线应用于一个数据系列，请右键单击所需的项目。 接下来，从菜单中选择“添加趋势线”。

The Format Trendline pane opens so you can select the trendline you want.

将打开“格式趋势线”窗格，以便您可以选择所需的趋势线。

In this example, a Moving Average trendline has been added to the charts Tea data series.

在此示例中，移动平均趋势线已添加到图表Tea数据系列中。

If you click the “Chart Elements” button to add a trendline without selecting a data series first, Excel asks you to which data series you want to add the trendline.

如果单击“图表元素”按钮以添加趋势线而不先选择数据系列，则Excel会询问您要向哪个数据系列添加趋势线。

You can add a trendline to multiple data series.

您可以将趋势线添加到多个数据系列。

In the following image, a trendline has been added to the Tea and Coffee data series.

在下图中，趋势线已添加到“茶和咖啡”数据系列中。

You can also add different trendlines to the same data series.

您还可以向同一数据系列添加不同的趋势线。

In this example, Linear and Moving Average trendlines have been added to the chart.

在此示例中，线性趋势图和移动平均趋势线已添加到图表中。

格式化趋势线 (Format Your Trendlines)

Trendlines are added as a dashed line and match the color of the data series to which they’re assigned. You might want to format the trendline differently—especially if you have multiple trendlines on a chart.

趋势线将添加为虚线，并与为其分配的数据系列的颜色匹配。 您可能希望以不同的方式设置趋势线的格式，尤其是在图表上有多个趋势线时。

Open the Format Trendline pane by either double-clicking the trendline you want to format or by right-clicking and selecting “Format Trendline.”

通过双击要格式化的趋势线或右键单击并选择“格式化趋势线”，打开“格式趋势线”窗格。

Click the Fill & Line category, and then you can select a different line color, width, dash type, and more for your trendline.

单击“填充和线条”类别，然后可以为趋势线选择其他线条颜色，宽度，破折号类型等。

In the following example, I changed the color to orange, so it’s different from the column color. I also increased the width to 2 pts and changed the dash type.

在下面的示例中，我将颜色更改为橙​​色，因此它与列颜色不同。 我还将宽度增加到2点，并更改了破折号类型。

延伸趋势线以预测未来价值 (Extend a Trendline to Forecast Future Values)

A very cool feature of trendlines in Excel is the option to extend them into the future. This gives us an idea of what future values might be based on the current data trend.

Excel中趋势线的一个非常酷的功能是可以将其扩展到将来。 这使我们对基于当前数据趋势的未来价值有一个想法。

From the Format Trendline pane, click the Trendline Options category, and then type a value in the “Forward” box under “Forecast.”

在“设置趋势线格式”窗格中，单击“趋势线选项”类别，然后在“预测”下的“转发”框中键入一个值。

显示R平方值 (Display the R-Squared Value)

The R-squared value is a number that indicates how well your trendline corresponds to your data. The closer the R-squared value is to 1, the better the fit of the trendline.

R平方值是一个数字，指示趋势线与数据的对应程度。 R平方值越接近1，趋势线的拟合越好。

From the Format Trendline pane, click the “Trendline Options” category, and then check the “Display R-squared value on chart” checkbox.

在“格式趋势线”窗格中，单击“趋势线选项”类别，然后选中“在图表上显示R平方值”复选框。

A value of 0.81 is shown. This is a reasonable fit, as a value over 0.75 is generally considered a decent one—the closer to 1, the better.

显示值为0.81。 这是一个合理的拟合，因为通常将0.75以上的值视为一个体面的值-越接近1越好。

If the R-squared value is low, you can try other trendline types to see if they’re a better fit for your data.

如果R平方值较低，则可以尝试其他趋势线类型，以查看它们是否更适合您的数据。

翻译自:

excel图表的标尺线