جمع کردن سلول های پر شده

Question

تیفانی ستونی دارد که حاوی مقادیر عددی است. برخی از سلول ها با رنگ های مختلف پر شده اند. برخی از آن رنگ ها واضح هستند. برخی از آنها از طریق قالب بندی شرطی اعمال می شوند. او می خواهد بتواند سلول هایی را که حاوی هر رنگ پرکننده ای هستند، صرف نظر از رنگ، جمع کند. به عبارت دیگر، مجموع باید فقط سلول هایی را که رنگ پر ندارند حذف کند.

یکی از راه های انجام این کار استفاده از قابلیت های فیلتر اکسل است. به عنوان مثال، می توانید این مراحل را دنبال کنید:

1. یک سلول در ستون داده خود انتخاب کنید.
2. تب Data روبان را نمایش دهید.
3. روی ابزار Filter کلیک کنید. اکسل فلش های کشویی را در سلول اول ستون قرار می دهد.
4. روی فلش کشویی کلیک کنید. اکسل یک پالت از گزینه های فیلتر را نمایش می دهد.
5. روی فلش سمت راست فیلد By Color کلیک کنید. اکسل سه گزینه را نمایش می دهد: رنگ پر، رنگ سلول و نماد سلول.
6. نشانگر ماوس را روی گزینه Fill Color قرار دهید. اکسل لیستی از رنگ های استفاده شده در ستون را نمایش می دهد.
7. گزینه No Fill را انتخاب کنید. اکسل بلافاصله داده ها را فیلتر می کند تا فقط ردیف هایی که رنگ پر شده در آن ستون ندارند نمایش داده شوند.
8. در یک سلول دیگر، فرمول زیر را وارد کنید:

=SUM(A2:A99)-SUBTOTAL(9,A2:A99)

مقدار بازگشتی از فرمول مجموع تمام سلول هایی است که از هر رنگ پر استفاده می کنند. این کار به این دلیل کار می کند که تابع SUM مجموع تمام سلول های محدوده (در این مورد A2:A99) و تابع SUBTOTAL مجموع تمام سلول های قابل مشاهده در محدوده را برمی گرداند. سلول های قابل مشاهده (غیر پر) را از کل محدوده کم کنید، و در نهایت مجموع تمام سلول های پر شده را خواهید داشت.

اگر ترجیح می دهید داده های خود را فیلتر نکنید، می توانید یک تابع تعریف شده توسط کاربر ایجاد کنید تا مجموع را برگرداند. موارد زیر برای این منظور عالی است:

Public Function SumFilled(Target As Range)
    Dim cell As Range
    Dim sAddr As String

    Application.Volatile
    For Each cell In Target
        sAddr = cell.Address(External:=True)
        If Evaluate("CellHasFill(""" & sAddr & """)") Then
            SumFilled = SumFilled + WorksheetFunction.Sum(cell)
        End If
    Next cell
End Function

Private Function CellHasFill(Addr As String) As Boolean
    CellHasFill = (Range(Addr).DisplayFormat.Interior.ColorIndex <> xlColorIndexNone)
End Function

توجه داشته باشید که یک UDF در اینجا تعریف شده است (SumFilled) و یک تابع که از UDF فراخوانی می شود. این ترتیب ضروری است زیرا قلب ماکرو خاصیت .DisplayFormat.Interior.ColorIndex است که رنگی را که در یک سلول نمایش داده می شود، صرف نظر از اینکه نتیجه یک پر کردن صریح یا یک قالب شرطی باشد، برمی گرداند. این ویژگی - به احتمال زیاد به دلیل یک اشکال (یعنی اشکال) در اکسل - مستقیماً در یک UDF کار نمی کند، اگرچه در یک ماکرو با کد سخت (غیر UDF) کار خواهد کرد. برای اطلاعات بیشتر در مورد این موضوع و چرایی استفاده از این روش راه حل، به موضوع زیر در SubStack مراجعه کنید:

https://stackoverflow.com/questions/52265819/getting-cell-interior-color-fails-when-range-passed-from-worksheet-function/

بنابراین، برای استفاده از UDF می توانید موارد زیر را در کاربرگ خود قرار دهید:

=SumFilled(AA2:A99)

اگر می خواهید از یک ماکرو غیر UDF استفاده کنید، موارد زیر کار خواهند کرد:

Public Sub ClrSum()
    Dim x As Double
    
    Set Rng = Range("A2:A99")
    x = 0
    For Each c In Rng.Cells
        If c.DisplayFormat.Interior.ColorIndex <> xlColorIndexNone Then x = x + c.Value
    Next c
    MsgBox x
End Sub

ماکرو را اجرا کنید و تمام سلول های محدوده A2:A99 را ارزیابی می کند و مجموع نمایش داده شده در یک جعبه پیام را برمی گرداند.

در نهایت، لازم به ذکر است که اگر فرصت دارید در مورد سازماندهی کاربرگ خود تجدید نظر کنید، بهتر است از رنگ به عنوان داده استفاده نکنید. به عبارت دیگر، ممکن است در نظر داشته باشید که یک ستون حاوی یک نشانگر عددی برای وضعیت مقادیر موجود در مقادیر رنگی فعلی خود اضافه کنید. به این ترتیب می توانید به راحتی از توابعی مانند SUMIF برای ارزیابی داده های خود استفاده کنید. اگر کدگذاری رنگ را می خواهید، می توانید قالب بندی شرطی ایجاد کنید که رنگ را بر اساس مقدار موجود در ستون وضعیت اضافه شده تغییر دهد.