梯度下降（numpy实现）与Excel多元回归结果差异原因咨询

It’s common to see discrepancies between iterative methods like gradient descent and closed-form solutions (like Excel’s OLS multi-regression) — let’s break down the most likely causes and how to fix them:

1. Gradient Descent Didn’t Fully Converge #

Excel uses the normal equation (closed-form solution) for ordinary least squares (OLS), which directly computes the optimal coefficients in one step. Gradient descent, by contrast, iteratively adjusts weights to minimize the cost function. If:

• You didn’t run enough iterations, the weights might still be approaching the optimal values instead of reaching them.
• Your learning rate is too high (causing the algorithm to oscillate around the minimum) or too low (making convergence take far longer than needed).

Check: Plot your cost function (e.g., mean squared error) against iteration count. If the line hasn’t flattened out, you need more iterations or a tuned learning rate.

2. Missing Feature Scaling #

Gradient descent is highly sensitive to the scale of your features. For example, if x1 ranges from 0-1000 and x2 ranges from 0-1, the algorithm will struggle to adjust the weights for x1 efficiently. Excel’s regression doesn’t require scaling because the normal equation is scale-invariant.

Fix: Standardize your features before running gradient descent:

# Standardize features (mean 0, standard deviation 1)
X_scaled = (X - np.mean(X, axis=0)) / np.std(X, axis=0)
# Add intercept term (Excel includes this by default)
X_scaled = np.hstack((np.ones((X_scaled.shape[0], 1)), X_scaled))

3. Implementation Errors in Your Numpy Code #

Small bugs can easily throw off your results. Common issues include:

• Missing intercept term: Excel adds an intercept (bias) by default. If your code doesn’t add a column of ones to your feature matrix, you’re fitting a line that passes through the origin — which will never match Excel’s result.
• Incorrect gradient calculation: Double-check that your gradient update step correctly computes the derivative of the cost function. For MSE, the gradient should be:

  m = len(y)
  gradients = (2 / m) * X.T.dot(X.dot(weights) - y)
  

• Data mismatches: Ensure you’re loading the exact same data as Excel (no missing rows, no accidental value conversions or filtering).

4. Regularization is Enabled (But Excel Doesn’t Use It) #

If you added L1/L2 regularization (e.g., Ridge/Lasso regression) to your gradient descent code, your coefficients will be shrunk compared to Excel’s vanilla OLS results. Excel’s multi-regression doesn’t apply regularization by default.

Check: If your code includes a regularization term in the cost function or gradient update, remove it to match Excel’s behavior.

Quick Validation Step #

To confirm whether the issue is with your gradient descent implementation or something else, compute the closed-form solution using numpy and compare it to Excel:

# Add intercept term to your feature matrix
X_with_intercept = np.hstack((np.ones((X.shape[0], 1)), X))
# Compute OLS coefficients using normal equation
ols_weights = np.linalg.lstsq(X_with_intercept, y, rcond=None)[0]

If ols_weights matches Excel’s results, the problem is definitely with your gradient descent setup (convergence, scaling, or code bugs).

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