excel - Excel VBA函数循环范围并测量多个相关性

Question

我正在尝试创建一个 Excel VBA 函数，该函数循环 acycle_range并计算该范围内 5 个单元格条目的每个范围与 5 个单元格条目的 a 的相关性base_range。该函数应返回 cycle_range 中的最大相关性。例如，以下数据应返回 0.506..... 输出，因为第二组循环范围数字7, 8, 9, 0, 8与基本范围具有相关性，这是最高相关性：

cycle range     base range       output
     4              3          0.506253796
     7              7   
     8              3   
     9              2   
     0              9   
     8      
     5      
     4  

我到目前为止的代码如下，它不起作用。从 cycle_range 中添加 rng 和元素很明显存在问题，但不知道该怎么做：

Function best_correl(correl_length As Double, base_range As Range, cycle_range As Range)


Dim i As Double
Dim rng As Range
Dim cycle_range_length As Double
Dim element As Variant
Dim max_correl As Double
Dim curr_correl As Double


cycle_range_length = cycle_range.Count - correl_length


For i = 1 To cycle_range_length

    For element = 1 To correl_length
        rng = rng + element
    Next element

    curr_correl = WorksheetFunction.Correl(base_range, rng)

    If curr_correl > max_correl Then
        max_correl = curr_correl
    End If

Next i


best_correl = max_correl


End Function

与往常一样，非常感谢任何建议，我在这个问题上遇到了困难。谢谢！

PS我从这里抄了一些东西-vba将一组单元格作为范围传递给函数

标签： excelvbauser-defined-functions

Answer 1

Function best_correl(correl_length As Double, base_range As Range, cycle_range As Range)

    Dim c As Range
    Dim cycle_range_length As Long
    Dim max_correl As Double
    Dim curr_correl As Double, n As Long, pos as Long

    cycle_range_length = 1 + (cycle_range.Count - correl_length)
    n = 0
    pos = 0
    For Each c In cycle_range.Resize(cycle_range_length, 1).Cells
        n = n + 1             
        curr_correl = WorksheetFunction.Correl(base_range, c.Resize(correl_length, 1))
        If curr_correl > max_correl Then  
            max_correl = curr_correl
            pos = n  
        End If
    Next c

    'best_correl = max_correl '<< max correlation
    best_correl = pos         '<< position of max 

End Function