如何绘制单调递减的凹形触底趋势线？

问题分析 #

你之前使用的二次多项式拟合（np.polyfit(x, y, 2)）会因为二次项系数为正，导致曲线后期出现回升，无法满足单调递减且凹形的要求。需要换用天生符合该约束的函数模型，比如双曲线、对数函数、幂函数，这些模型的曲线会持续递减，且下降速度逐渐放缓（符合凹形特征）。

Python实现 #

使用scipy.optimize.curve_fit进行非线性最小二乘拟合，以下是几种可行模型的代码示例：

1. 双曲线模型（y = a + b/x） #

该模型曲线下降速度随x增大逐渐放缓，完全符合需求：

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

# 原始数据
x = np.array([6,8,10,12,16,24,32])
y = np.array([934,792,744,710,699,686,681])

# 定义双曲线模型
def hyperbola(x, a, b):
    return a + b / x

# 拟合参数
params, _ = curve_fit(hyperbola, x, y)
a_fit, b_fit = params

# 生成密集x点绘制平滑曲线
x_new = np.linspace(min(x), max(x), 100)
y_fit = hyperbola(x_new, a_fit, b_fit)

# 可视化
plt.scatter(x, y, label='原始数据')
plt.plot(x_new, y_fit, 'r-', label=f'拟合曲线: y={a_fit:.2f} + {b_fit:.2f}/x')
plt.legend()
plt.xlabel('x')
plt.ylabel('y')
plt.show()

2. 对数模型（y = a + b*ln(x)） #

同样满足单调递减+凹形的约束：

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

x = np.array([6,8,10,12,16,24,32])
y = np.array([934,792,744,710,699,686,681])

# 定义对数模型
def log_model(x, a, b):
    return a + b * np.log(x)

params, _ = curve_fit(log_model, x, y)
a_fit, b_fit = params

x_new = np.linspace(min(x), max(x), 100)
y_fit = log_model(x_new, a_fit, b_fit)

plt.scatter(x, y, label='原始数据')
plt.plot(x_new, y_fit, 'g-', label=f'拟合曲线: y={a_fit:.2f} + {b_fit:.2f}ln(x)')
plt.legend()
plt.xlabel('x')
plt.ylabel('y')
plt.show()

Excel实现 #

Excel可以通过内置趋势线或Solver工具实现需求：

方法1：内置趋势线（推荐） #

1. 插入散点图：选中x、y数据，点击「插入」→「散点图（仅带数据标记）」。
2. 添加趋势线：右键点击数据点，选择「添加趋势线」。
3. 选择模型并显示公式：
   • 选幂函数：在右侧「趋势线选项」中选择「幂」，勾选「显示公式」和「显示R平方值」。得到的公式为y = a*x^b，其中b为负数，曲线单调递减且凹形。
   • 选对数函数：选择「对数」类型，公式为y = a + b*ln(x)，b为负数，同样符合要求。

方法2：自定义双曲线拟合 #

若需要双曲线模型（y = a + b/x），可通过Solver工具拟合参数：

1. 在空白单元格输入a、b的初始值（如a=600，b=2000）。
2. 计算每个x对应的预测y值：输入公式=$A$1 + $B$1/x（假设a在A1，b在B1）。
3. 计算误差平方和：用SUMSQ(实际y区域 - 预测y区域)计算。
4. 启用Solver：点击「数据」→「Solver」（若未找到，需在「文件→选项→加载项」中启用）。
5. 设置Solver：目标单元格选误差平方和，设置为「最小值」，可变单元格选a、b的单元格，点击「求解」得到参数，再用公式生成曲线。

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