动态数组中带±容差的多维度匹配统计函数开发咨询

Got it, let's fix up your code to handle dynamic dimensions and tolerance-based matching instead of exact matches. We'll integrate your existing ChangeMatrixTwo function so everything works seamlessly with sliding window matrices of any column count.

Key Improvements We'll Make #

• Ditch the hardcoded 2-column limit to support any number of columns
• Replace exact value checks with element-wise tolerance matching (each value must fall within ±r of the target)
• Combine the sliding window matrix generation from ChangeMatrixTwo directly into the matching logic

Updated Full Code #

1. Main Matching Procedure: get_matches_with_tolerance #

This sub handles tolerance checks, works with any matrix dimension, and uses your sliding window function to generate the source matrix:

Sub get_matches_with_tolerance()
    Dim sourceArr As Variant, targetArr As Variant
    Dim i As Long, j As Long, k As Long
    Dim matchCount As Double
    Dim tolerance As Double
    Dim numCols As Long ' Define your target column count here
    
    ' Set your tolerance (e.g., ±1)
    tolerance = 1
    ' Set the number of columns for your sliding window matrix
    numCols = 2
    
    ' Generate the sliding window matrix from your source column (A2:A26 here)
    sourceArr = ChangeMatrixTwo(Range("A2:A26"), numCols)
    
    ' Loop through each target row (A2:B26 as in your original code)
    For k = 2 To 26
        matchCount = 0
        ' Grab the current target row as an array
        targetArr = Range("A" & k & ":B" & k).Value
        
        ' Check each row in the source matrix
        For i = 1 To UBound(sourceArr, 1)
            Dim isRowMatch As Boolean
            isRowMatch = True
            
            ' Verify every element in the row is within tolerance
            For j = 1 To UBound(sourceArr, 2)
                If Abs(sourceArr(i, j) - targetArr(1, j)) > tolerance Then
                    isRowMatch = False
                    Exit For ' No need to check other elements if one fails
                End If
            Next j
            
            ' Count the row if all elements passed the tolerance check
            If isRowMatch Then
                matchCount = matchCount + 1
            End If
        Next i
        
        ' Write the result to column N
        Range("N" & k).Value = matchCount
    Next k
End Sub

2. Refined Sliding Window Matrix Function: ChangeMatrixTwo #

I cleaned up the variable declarations and added a boundary check to handle cases where your input column is shorter than the requested number of columns:

Function ChangeMatrixTwo(ByRef inputCol As Range, NumCols As Long) As Variant
    ' Converts a single column into a sliding window matrix of specified column count
    ' Example: Input [1,2,3,4,5,6] with NumCols=2 → [[1,2],[2,3],[3,4],[4,5],[5,6]]
    Dim NewMatrix() As Variant
    Dim i As Long, j As Long, k As Long
    Dim rowSize As Long
    Dim n As Long
    
    n = inputCol.Count
    rowSize = n - NumCols + 1
    
    ' Handle edge case: input column is too short for the requested column count
    If rowSize < 1 Then
        ChangeMatrixTwo = Empty
        Exit Function
    End If
    
    ReDim NewMatrix(1 To rowSize, 1 To NumCols)
    k = 1
    
    For i = 1 To rowSize
        For j = 1 To NumCols
            NewMatrix(i, j) = inputCol(k, 1)
            k = k + 1
        Next j
        k = k - NumCols + 1 ' Slide back to start of next window
    Next i
    
    ChangeMatrixTwo = NewMatrix
End Function

How It Works #

1. Tolerance Matching:
   • For each row, we check if every element is within ±tolerance of the target element using Abs(sourceVal - targetVal) <= tolerance
   • If any element fails the check, we skip the rest of the row to save computation time
2. Dynamic Dimensions:
   • We use UBound(arr, 2) to get the number of columns in the matrix, so it works no matter what numCols you set
   • ChangeMatrixTwo generates the sliding window matrix with exactly the number of columns you specify
3. Integration:
   • The main sub calls ChangeMatrixTwo directly to generate the source matrix, so you don't have to handle intermediate steps separately

Example Usage #

If your source data is in A2:A26, target rows are in A2:B26, and you set tolerance = 1:

• Running get_matches_with_tolerance will write the count of matching rows (within ±1) to column N
• For a target row [9,4], rows like [8,3], [9,4], and [10,5] will all be counted as matches

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