جمع بندی بر اساس قالب بندی در سلول های مجاور

Question

Srinivas داده هایی در هر دو ستون A و B دارد. او باید مقادیر ستون B را که قالب سلول های مربوطه در ستون A زرد است، جمع کند. به عنوان مثال، اگر قالب در سلول A17 زرد است، مقدار سلول B17 باید در مجموع لحاظ شود.

ماکروهای متعددی در اینترنت (از جمله در ExcelTips) موجود است که به شما امکان می دهد جمع بندی شرطی را بر اساس رنگ یا فرمت های دیگر سلول انجام دهید. با این حال، این نیاز متفاوت است، زیرا رنگ سلول مورد بحث نیست، بلکه رنگ سلول یک ستون در سمت چپ است. همانطور که در اینجا نشان داده شده است، هنوز هم می توان با استفاده از یک ماکرو این کار را انجام داد:

Function SumNextYellow(ByVal r As Range)
    Dim c As Range
    Dim a As Double

    For Each c In r
        If c.Offset(0, -1).Interior.ColorIndex = 6 Then Yellow
            a = a + c.Value
        End If
    Next c
    SumNextYellow = a
End Function

تابع را می توان در فرمول کاربرگ استفاده کرد و مرجع محدوده را به عنوان آرگومان می پذیرد. سپس از هر سلول در محدوده عبور می کند، و اگر سلول سمت چپ زرد باشد، مقدار در مجموع گنجانده می شود. (باید توجه داشته باشید که ColorIndex مورد استفاده در ماکرو باید با نسخه اکسل شما آزمایش شود تا از کاربردی بودن آن مطمئن شوید؛ ممکن است در نسخه های مختلف متفاوت باشد.)

یک مثال بسیار قوی تر در فهرست زیر نشان داده شده است. این تابع یک یا چند محدوده سلول را به همراه آرگومان می پذیرد که نمونه ای از قالب بندی را که می خواهید استفاده کنید را نشان می دهد.

Function ColorConditionSum(cSample As Excel.Range, rng As Excel.Range)
 This Function returns sum of values in rng.Columns(2) if
 corresponding cell in rng.Columns(1) is colored with sample
 color (cSample cell)

 Arguments: cSample = cell colored by sample color
            rng = cell range to be processed (2 columns)

    Dim rngCol2 As Excel.Range
    Dim rngConstants As Excel.Range
    Dim rngFormulas As Excel.Range
    Dim lColorIndex As Long
    Dim MySum As Double
    Dim area As Excel.Range
    Dim c As Excel.Range

    ColorConditionSum = False
    If Not TypeOf cSample Is Excel.Range Then Exit Function >>>
    lColorIndex = cSample.Interior.ColorIndex

    MySum = 0
    Set rngCol2 = Nothing
    If TypeOf rng Is Excel.Range Then
        If rng.Columns.Count < 2 Then Exit Function >>>
        On Error Resume Next
        For Each area In rng.Areas
            If rngCol2 Is Nothing Then
                Set rngCol2 = area.Columns(2).SpecialCells(xlCellTypeConstants, 1)
                If rngCol2 Is Nothing Then
                    Set rngCol2 = area.Columns(2).SpecialCells(xlCellTypeFormulas, 1)
                Else
                    Set rngCol2 = Application.Union( _
                        rngCol2, area.Columns(2).SpecialCells(xlCellTypeFormulas, 1))
                End If
            Else
                Set rngCol2 = Application.Union( _
                    rngCol2, area.Columns(2).SpecialCells(xlCellTypeConstants, 1))
                Set rngCol2 = Application.Union( _
                    rngCol2, area.Columns(2).SpecialCells(xlCellTypeFormulas, 1))
            End If
        Next area

        For Each area In rngCol2.Areas
            For Each c In area.Cells
                With c.Offset(0, -1)
                    If .Interior.ColorIndex = lColorIndex Then
                        MySum = MySum + c.Value
                    End If
                End With
            Next c
        Next area
    End If

    ColorConditionSum = MySum
End Function

شما از این تابع به روش زیر در یک کاربرگ استفاده می کنید:

=ColorConditionSum(A10, A12:B22)

در این مورد، A10 سلولی است که دارای رنگ داخلی است که می خواهید مطابقت داشته باشد و A12:B22 محدوده سلول هایی است که باید ارزیابی شوند. مقادیر از ستون دوم در محدوده برداشته شده و قالب بندی در سلول های ستون اول بررسی می شود.