如何将Excel函数LINEST中的固定区域替换为变量实现动态调用？

一、纯公式实现（无需VBA） #

不用手动分配范围变量，直接结合你已有的XMATCH/ADDRESS结果，用以下两种方式构建动态区域：

1. 用INDEX构建动态区域（推荐，非易失） #

如果已经用XMATCH算出X/Y列的最后非空行号，直接用INDEX定位区域边界，性能比文本拼接更稳定：
假设X列起始行是3，最后非空行用XMATCH("*",$A:$A,-1)获取；Y列同理，LINEST公式可写成：

=LINEST($B$3:INDEX($B:$B,XMATCH("*",$B:$B,-1)), $A$3:INDEX($A:$A,XMATCH("*",$A:$A,-1))^{1,2,3,4,5})

如果你的起始行不是固定值，也可以把起始行换成单元格引用（比如$Z$1存起始行号），公式改成：

=LINEST(INDEX($B:$B,$Z$1):INDEX($B:$B,XMATCH("*",$B:$B,-1)), INDEX($A:$A,$Z$1):INDEX($A:$A,XMATCH("*",$A:$A,-1))^{1,2,3,4,5})

2. 用INDIRECT结合ADDRESS结果 #

如果已经通过ADDRESS得到了区域的最后单元格地址（比如$X$1存Y列最后单元格$B$18，$X$2存X列最后单元格$A$18），可以直接拼接成完整区域：

=LINEST(INDIRECT("$B$3:"&RIGHT($X$1,LEN($X$1)-FIND("$",$X$1,2))), INDIRECT("$A$3:"&RIGHT($X$2,LEN($X$2)-FIND("$",$X$2,2)))^{1,2,3,4,5})

注意：INDIRECT是易失函数，大量使用可能拖慢表格，优先选INDEX方案。

二、VBA自定义函数实现 #

如果需要更灵活的复用，写个自定义函数直接传入区域参数即可：

1. 基础自定义函数 #

按Alt+F11打开VBA编辑器，插入模块后粘贴以下代码：

Function DynamicLinest(yStart As Range, yEnd As Range, xStart As Range, xEnd As Range, degree As Integer) As Variant
    Dim yRange As Range, xRange As Range
    Dim xArray As Variant
    Dim i As Integer
    
    Set yRange = Range(yStart, yEnd)
    Set xRange = Range(xStart, xEnd)
    
    '构建多项式数组
    ReDim xArray(1 To xRange.Rows.Count, 1 To degree)
    For i = 1 To degree
        xArray(, i) = Application.Power(xRange.Value, i)
    Next i
    
    DynamicLinest = Application.LinEst(yRange.Value, xArray)
End Function

使用时直接传入起始和结束单元格，或者结合你的动态地址：

=DynamicLinest($B$3,INDIRECT($X$1),$A$3,INDIRECT($X$2),5)

2. 自动识别非空区域的简化版 #

如果你的数组是连续非空的，还可以写个自动找最后行的版本：

Function AutoLinest(yCol As String, xCol As String, startRow As Integer, degree As Integer) As Variant
    Dim lastYRow As Integer, lastXRow As Integer
    Dim yRange As Range, xRange As Range
    Dim xArray As Variant
    Dim i As Integer
    
    lastYRow = Cells(Rows.Count, yCol).End(xlUp).Row
    lastXRow = Cells(Rows.Count, xCol).End(xlUp).Row
    
    Set yRange = Range(yCol & startRow & ":" & yCol & lastYRow)
    Set xRange = Range(xCol & startRow & ":" & xCol & lastXRow)
    
    ReDim xArray(1 To xRange.Rows.Count, 1 To degree)
    For i = 1 To degree
        xArray(, i) = Application.Power(xRange.Value, i)
    Next i
    
    AutoLinest = Application.LinEst(yRange.Value, xArray)
End Function

使用示例（从第3行开始，Y列是B，X列是A，5次多项式）：

=AutoLinest("B","A",3,5)

关于范围变量的疑问 #

不需要手动为每个数组单独分配范围变量，不管用公式还是VBA，都可以直接通过动态计算的起始/结束位置构建区域——公式法用INDEX/INDIRECT直接拼接，VBA法通过函数参数传入动态地址或列名即可。

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