excel - 定义 X 和 Y 将发生变化的回归范围

Question

我需要使用数据分析工具包创建一个简单的回归。问题是，Y 和 X 输入的范围总是不同的。为了说明我想说的内容，这是我需要处理的表格的示例:

        A   B   C   D   E   F   G   H   I   J   K   L
    1   Y   T   T1  T2  T3  T4  T5  T6  T7  T8  T9  T10
    2   19  1                                       
    3   13  2   19                                  
    4   14  3   13  19                              
    5   16  4   14  13  19                          
    6   17  5   16  14  13  19                      
    7   16  6   17  16  14  13  19                  
    8   20  7   16  17  16  14  13  19              
    9   10  8   20  16  17  16  14  13  19          
    10  20  9   10  20  16  17  16  14  13  19      
    11  11  10  20  10  20  16  17  16  14  13  19  
    12  11  11  11  20  10  20  16  17  16  14  13  19
    13  14  12  11  11  20  10  20  16  17  16  14  13
    14  15  13  14  11  11  20  10  20  16  17  16  14
    15  17  14  15  14  11  11  20  10  20  16  17  16
    16  10  15  17  15  14  11  11  20  10  20  16  17
    17  4   16  10  17  15  14  11  11  20  10  20  16
    18  15  17  4   10  17  15  14  11  11  20  10  20
    19  6   18  15  4   10  17  15  14  11  11  20  10
    20  10  19  6   15  4   10  17  15  14  11  11  20
    21  16  20  10  6   15  4   10  17  15  14  11  11
    22          16  10  6   15  4   10  17  15  14  11
    23              16  10  6   15  4   10  17  15  14
    24                  16  10  6   15  4   10  17  15
    25                      16  10  6   15  4   10  17
    26                          16  10  6   15  4   10
    27                              16  10  6   15  4
    28                                  16  10  6   15
    29                                      16  10  6
    30                                          16  10
    31                                              16

在此示例中，Y 输入范围为 A12:A21，这是因为表格最后一列中的第一个条目(单元格 L12 中的“19”)位于第 12 行并且第一列中的最后一个条目表的第 21 行(单元格 A21 中的“16”)；此外，出于同样的原因，X 输入将是区域 B12:L21。

完成第一次回归后，我需要从表中删除两列，然后再进行另一次回归。因此，例如，如果我需要删除 J 列和 L 列，则表格将如下所示:

        A   B   C   D   E   F   G   H   I   J
    1   Y   T   T1  T2  T3  T4  T5  T6  T7  T9  
    2   19  1                                       
    3   13  2   19                                  
    4   14  3   13  19                              
    5   16  4   14  13  19                          
    6   17  5   16  14  13  19                      
    7   16  6   17  16  14  13  19                  
    8   20  7   16  17  16  14  13  19              
    9   10  8   20  16  17  16  14  13  19          
    10  20  9   10  20  16  17  16  14  13      
    11  11  10  20  10  20  16  17  16  14  19  
    12  11  11  11  20  10  20  16  17  16  13  
    13  14  12  11  11  20  10  20  16  17  14  
    14  15  13  14  11  11  20  10  20  16  16  
    15  17  14  15  14  11  11  20  10  20  17  
    16  10  15  17  15  14  11  11  20  10  16  
    17  4   16  10  17  15  14  11  11  20  20  
    18  15  17  4   10  17  15  14  11  11  10  
    19  6   18  15  4   10  17  15  14  11  20  
    20  10  19  6   15  4   10  17  15  14  11  
    21  16  20  10  6   15  4   10  17  15  11  
    22          16  10  6   15  4   10  17  14  
    23              16  10  6   15  4   10  15  
    24                  16  10  6   15  4   17  
    25                      16  10  6   15  10  
    26                          16  10  6   4   
    27                              16  10  15  
    28                                  16  6   
    29                                      10  
    30                                      16  

现在回归将使用输入 Y (A11:A21)，因为表的最后一列中的第一个条目(单元格 J11 中的“19”)位于第 11 行，并且表的第一列中的最后一个条目表(单元格 A21 中的“16”)位于第 21 行。同样，出于同样的原因，X 输入将为 (B11:J21)。

我尝试了一百种不同的方法，但没有成功。这是我最接近创建我需要的东西，但我仍然迷失，因为它不会执行回归:

Sub Prueba1()
    Range("A1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Selection.End(xlToLeft).Select
    Application.Run "ATPVBAEN.XLAM!Regress", Range(Selection, Selection.End(xlDown)).Select, _
    Range(Selection.Offset(, 1), Selection.End(xlToRight)).Select, False, False, , Range("S1") _
    , False, False, False, False, , False
End Sub

Answer 1

此用户定义函数(又名 UDF)会将范围作为参数返回到您的 Application.Run "ATPVBAEN.XLAM!Regress"。

Function regress_range()
    Dim strAddr As String, c As Long

    With Worksheets("Sheet4")   '<~~set this worksheet name!
        With .Cells(1, 1).CurrentRegion
            Set regress_range = .Range(.Cells(.Cells(1, .Columns.Count).End(xlDown).Row, 1), _
                         .Cells(Application.Match(1E+99, .Columns(1)), .Columns.Count))
        End With
    End With

End Function

您需要确保它在第三行中正确引用了正确的工作表。

这将成为运行命令的一部分，例如，

Application.Run "ATPVBAEN.XLAM!Regress", regress_range(), False, False, , Range("S1") _
, False, False, False, False, , False

我仍然担心如果从回归范围中删除列，Range("S1") 可能会如何变化(即右移)。此外，它没有显式引用的父工作表。

从原始数据 block 开始输出:

$A$12:$L$21
$A$11:$J$21