excel - 如何区分线性区域和二次区域

Question

我正在编写一个 vba 代码，它能够接收 2 个 x 和 y 点数组，形成散点图，并分析该图以确定它从线性变为二次的点。我将在下面讨论我尝试过的方法，但问题是除了我能想出的“观察它”之外，没有真正的方法可以做到这一点。

我的方法是迭代比较第一个点和第二个点之间的斜率，然后比较第一个点和第三个点等，并查看该值何时开始偏离。问题是这是一个散点图，因此所有点之间的斜率值偏差很大。然后我尝试比较 y 值，看看 y_i 和 y_i+1 之间何时可能出现平方关系。但这并没有真正解决问题，因为散点图中某个点的 y 值并不一定说明其性质。我还考虑过迭代地创建曲线拟合，但我看不出这会如何产生与前两次尝试不同的结果。以下是我当前对所描述的最后一种方法的尝试。 Avg 是 Y 值，P2 是 X 值。 tp 是“转折点”，即图形从线性变为二次的位置。

'Look for turning point where trend becomes nonlinear'
slp(1) = (Avg(2) - Avg(1)) '/ (P2(2) - P2(1))
Dim delta As Variant
ReDim delta(1 To UBound(slp))

For i = 2 To UBound(LP) 'Looks for turning point by comparing current slopes'
    'slp(i) = WorksheetFunction.Slope(P2(i), Avg(i))
    slp(i) = (Avg(i) - Avg(i - 1)) ' / (P2(i) - P2(i - 1))
    delta(i) = slp(i) - slp(i - 1)
Next i
For i = 1 To UBound(delta)
    If delta(i) >= 4000 Then
        tpx(1) = P2(i)
        tpy(1) = Avg(i)
        'tp(1) = slp(i)
        Exit For
    End If
Next i
For i = 1 To UBound(LP) 'Collects points before and after turning point'
    If P2(i) < tpx(1) Then
        x1(i) = P2(i)
        y1(i) = Avg(i)
    ElseIf P2(i) >= tpx(1) Then
        x2(i) = P2(i)
        y2(i) = Avg(i)
    End If
Next i

Plot我附上了情节的屏幕截图，以便更好地了解问题。蓝色区域是目视线性区域，橙色是目视二次区域。

Answer 1

对于相同的数据，可以运行并比较二次和线性最小二乘回归。

各自的均方根误差(RMSE)不同。 RMSE(线性)高于 RMSE(二次)，但根据分散度和形状的差异或多或少有所不同。

在二次形状的情况下，如图 1 所示，由于线性曲线与二次曲线的拟合不良，RMSE(线性)远高于 RMSE(二次)。比率 10 与 1 相差甚远。

在几乎线性形状的情况下，如图 2 所示，二次回归会导致系数 c 值较低。即使是二次曲线，曲线也几乎是线性的。因此 RMSE(线性)和 RMSE(二次)都保持在相同的数量级。该比率接近于1。

RMSE(线性)和 RMSE(二次)之间的比率似乎是形状的良好指标:

• 比率接近 1:形状被称为“线性类型”。

• 比率显着高于 1:该形状被称为“二次型”。

有人可能会反对，分散可能会降低该指标的重要性。这部分是正确的。分散度越大，系数 c 变得相对较小。但即使如图 3 和图 4 所示的高度分散，该比率仍然很重要。

同样，图 3 上的形状被称为“二次型”，图 4 上的形状被称为“线性型”。

尽管如此，定义“线性类型”和“二次类型”之间限制的比率(略高于 1)仍然是主观的(或经验的)。