محاسبه فاصله بین وقوع

Question

راجر پرسید آیا راهی برای محاسبه فاصله بین وقوع مقادیر در یک لیست وجود دارد؟ به عنوان مثال، او چندین هزار عدد در ستون A دارد. با نگاهی به مقدار سلول A351، آخرین باری که این مقدار در لیست رخ داد در سلول A246 بود. او فرمولی می خواهد که بتواند در سلول B351 قرار داده شود و 105، تفاوت بین 351 و 246 را برگرداند.

پیاده سازی این رویکرد در اکسل دشوار است زیرا اکسل در جستجوی رو به عقب-بالا یک ستون خیلی خوب نیست. اگر بتوان فرض را برعکس کرد، کار بسیار ساده تر می شود. به عنوان مثال، اگر فرمولی در سلول B246 بتواند مقدار 105 را برگرداند، به جای محاسبه آخرین رخداد، فاصله زمانی تا وقوع بعدی مقدار در سلول A246 را نشان می دهد. فرمول زیر وقوع بعدی مقدار را در سلول A1 محاسبه می کند:

=MATCH(A1,A2:$A$65536,0)

این فرمول را در سلول B1 قرار دهید و هر تعداد سلول لازم است آن را کپی کنید. اگر مقدار ستون A دوباره در ستون رخ ندهد، فرمول خطای #N/A را برمی گرداند. اگر ترجیح می دهید فرمول 0 را برگرداند، موارد زیر کار می کند:

=IF(ISNA(MATCH(A1,A2:$A$65536,0)),0,MATCH(A1,A2:$A$65536,0))

اگر باید حتماً به سمت بالا بشمارید (بجای رخداد بعدی، رخداد قبلی را بیابید)، ساده ترین راه برای انجام آن استفاده از یک تابع تعریف شده توسط کاربر است. تابع زیر، RowInterval، در محدوده ای که شما مشخص کرده اید به عقب نگاه می کند و بازه مورد نظر را برمی گرداند:

Function RowInterval(TestCell As Range, LookHere As Range) As Long
    Dim varValue As Variant
    Dim lngRow As Long

    Application.Volatile
    varValue = TestCell.Value
    
    Check for occurrences of the test value in the search range
    If WorksheetFunction.CountIf(LookHere, varValue) > 0 Then
        With LookHere
            Get the last row of the search range
            lngRow = .Row + .Rows.Count - 1
            Start with the last cell in the search range and work up
            Do Until .Item(lngRow, 1).Value = varValue
                lngRow = lngRow - 1
            Loop
        End With
        Subtract the number of the row containing the found occurrence
        from the number of the row containing the test value
        RowInterval = TestCell.Row - lngRow
    End If
End Function

برای استفاده از تابع، فرمول زیر را در سلول B2 قرار دهید و سپس فرمول را به تعداد سلول های مورد نظر کپی کنید:

=RowInterval(A2,A$1:A1)