如何将自定义函数拟合到数据？以y=ae^(b*x)+c为例

Got it, let's tackle this custom curve fitting problem—both for your specific y=ae^(b*x)+c model and a general approach that works for any custom function. Here's how to do it, starting with LibreOffice Calc since that's where you initially sought help, then a more flexible programming method for broader use cases.

If you want to stick with LibreOffice, the Solver tool is your best bet for custom nonlinear fitting. Here's the step-by-step workflow:

• First, organize your data: Assume your 13 data points correspond to x-values from 1 to 13 (since you didn't provide x's, this is a standard default). Enter x in column A (A1:A13 = 1,2,...,13) and your raw y-values in column B (B1:B13 = your data set).
• Initialize parameter guesses: Pick three blank cells (e.g., D1, D2, D3) to hold initial values for a, b, and c. For your data (which looks like a decaying trend with some noise), try D1=1, D2=-0.1, D3=0.6—initial guesses don't need to be perfect, just reasonable.
• Calculate fitted y-values: In cell C1, enter the formula:

  =$D$1*EXP($D$2*A1)+$D$3
  

  Drag this formula down to C13 to generate fitted values for every x.
• Compute error metric: We'll minimize the sum of squared errors (SSE) to get the best fit. In a cell like E1, enter:

  =SUMSQ(B1:B13-C1:C13)
  

• Launch Solver: Go to Tools > Solver (if it's missing, install it via Tools > Extension Manager first). Configure it like this:
  • Set target cell to E1, with the goal of Minimizing the value.
  • Set variable cells to D1:D3 (your a, b, c parameters).
  • Click Solve—LibreOffice will adjust the parameters automatically to minimize the SSE.
• Review results: Once Solver finishes, D1:D3 will hold your optimized a, b, c values. You can plot the original data vs. the fitted curve to check how well it fits.

If you need a solution that works for any custom function (not just this exponential model), using Python with scipy.optimize.curve_fit is the most flexible approach. Here's how to implement it:

First, install the required libraries if you haven't already:

pip install numpy scipy matplotlib

Then use this code template—you can swap out the custom_func definition for any function you need:

import numpy as np
from scipy.optimize import curve_fit
import matplotlib.pyplot as plt

# Your raw data
y_data = np.array([0.767838478, 0.702426493, 0.733858228, 0.703275979, 0.651456058,
                   0.62427187, 0.742353261, 0.646359026, 0.695630431, 0.659101665,
                   0.598786652, 0.592840135, 0.59199059])
# Corresponding x-values (1 to 13)
x_data = np.arange(1, len(y_data) + 1)

# Define YOUR custom function here—replace this with any model you need
def custom_func(x, a, b, c):
    return a * np.exp(b * x) + c

# Initial guess for parameters (adjust based on your function's behavior)
initial_guess = [1, -0.1, 0.6]

# Run curve fitting
optimized_params, param_covariance = curve_fit(custom_func, x_data, y_data, p0=initial_guess)

# Print the results
print(f"Optimized parameters:")
print(f"a = {optimized_params[0]:.6f}")
print(f"b = {optimized_params[1]:.6f}")
print(f"c = {optimized_params[2]:.6f}")

# Plot original data vs fitted curve
plt.scatter(x_data, y_data, color='blue', label='Original Data')
plt.plot(x_data, custom_func(x_data, *optimized_params), 'r--', label='Fitted Curve')
plt.xlabel('x')
plt.ylabel('y')
plt.legend()
plt.show()

Key Notes for Both Methods #

• Initial guesses matter: For nonlinear fitting, giving a reasonable starting point helps the algorithm converge to the global minimum (instead of getting stuck in a local one). For your data, since it's trending downward, b should be negative—so starting with -0.1 is better than a positive value.
• Validate fit quality: Always visualize the fit and calculate metrics like the R-squared value to confirm how well the model matches your data. For Python, you can compute R-squared with:

  y_fit = custom_func(x_data, *optimized_params)
  r_squared = 1 - (np.sum((y_data - y_fit)**2) / np.sum((y_data - np.mean(y_data))**2))
  print(f"R-squared: {r_squared:.6f}")
  

内容的提问来源于stack exchange，提问作者Foad S. Farimani