如何在Excel中基于特定数据点范围绘制回归直线并展示全部数据点？

Got it, let's break down exactly how to plot a linear regression line that only uses your 3rd through last data points (9 to 13 in your example) while keeping all original data points visible in Excel. Here's a step-by-step guide tailored to your sample data: Mydata 5 7 9 8 9 10 13

First, let's get your data organized. I'll assume you've got your y-values in column B (B1=5, B2=7, B3=9, B4=8, B5=9, B6=10, B7=13) and sequential x-values (1 to 7) in column A—you need x-values to map each data point properly in a scatter plot.

1. Create a Scatter Plot with All Data Points #

• Highlight your full dataset (A1:A7 for x-values, B1:B7 for y-values).
• Head to the Insert tab, pick the Scatter chart option (the first one: "Scatter with only Markers"). This will display all your data points on the chart.

2. Add a Separate Data Series for the Regression Subset #

To calculate the regression line only for points 3–7, we need to add this subset as a hidden series:

• Right-click the chart and select Select Data.
• In the pop-up window, click Add.
• For Series X values, select the x-values from row 3 to 7 (A3:A7).
• For Series Y values, select the corresponding y-values (B3:B7).
• Hit OK to add this new series to your chart.

3. Hide the Subset Series (Keep It Invisible) #

We don't want this subset showing up as extra markers, so let's hide it:

• Select the new subset series (use the "Chart Elements" dropdown if you can't click it directly).
• Go to the Format tab, find Marker Options, and set it to None. Now only your original data points are visible.

4. Add the Linear Regression Line for the Subset #

• Right-click the invisible subset series and choose Add Trendline.
• In the Format Trendline pane on the right:
  • Under Trendline Options, pick Linear.
  • Check the boxes for Display Equation on chart and Display R-squared value on chart if you want those stats shown.
  • Leave Set Intercept unchecked unless you have a specific value you need to use.

And you're done! Your chart will now show all 7 original data points, with a regression line that's only calculated from the 3rd through last points.

内容的提问来源于stack exchange，提问作者Eungi Kim