جمع کردن سلول ها با استفاده از رنگ پس زمینه خاص

Question

پیتر ستون هایی از مبالغ دلاری دارد که مشتریان بدهکار هستند. وقتی مشتری پرداخت می کند، با پر کردن سلول با رنگ زمینه زرد، آن سلول را با رنگ زرد برجسته می کند. پیتر متعجب است که آیا راهی برای جمع کردن تنها سلول هایی که در یک ستون خاص برجسته شده اند وجود دارد؟

چند راه برای حل این مشکل وجود دارد که در بخش های بعدی به هر یک از آنها پرداخته می شود.

فیلتر کنید و از SUBTOTAL استفاده کنید

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

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

1. اطمینان حاصل کنید که داده های شما دارای عناوین ستونی تعریف شده است.
2. یک سلول از داده های خود را انتخاب کنید.
3. تب Data روبان را نمایش دهید.
4. روی ابزار Filter در گروه Sort & Filter کلیک کنید. اکسل یک فلش کشویی را در کنار عناوین ستون قرار می دهد.
5. روی فلش کشویی کنار عنوان ستون حاوی سلول های رنگی کلیک کنید. تعدادی از گزینه های فیلتر نمایش داده می شوند.
6. روی لیست کشویی By Color کلیک کنید. بسته به ویژگی های قالب بندی سلول شما، گزینه های رنگی مختلف ظاهر می شود.
7. نشانگر ماوس را روی گزینه Cell Color قرار دهید و سپس روی رنگی که می خواهید در فیلتر کردن خود استفاده کنید کلیک کنید. در مورد پیترز، این رنگ زرد خواهد بود. اکسل بلافاصله داده ها را با رنگ انتخاب شده فیلتر می کند.
8. اطمینان حاصل کنید که فقط مقدار FALSE دارای علامت تیک در کنار آن باشد.
9. در یک سلول در جدول داده کلیک کنید. این گزینه های فیلتر را که برای اولین بار در مرحله 5 مشاهده می شوند، رد می کند.

در این مرحله می توانید فرمولی (در هر سلولی که می خواهید) وارد کنید که مجموع مورد نظر را برمی گرداند. با فرض اینکه مجموعی از مقادیر C2:C93 را می خواهید، از موارد زیر استفاده می کنید:

=SUBTOTAL(9,C2:C93)

این کار به این دلیل کار می کند که تابع SUBTOTAL فقط مقادیری را جمع می کند که پس از فیلتر کردن قابل مشاهده باقی می مانند. در این حالت به این معنی است که فقط سلول های زرد جمع می شوند.

داده های خود را اصلاح کنید

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

وقتی به مجموع سلول های رنگی نیاز دارید، چگونه کمک می کند؟ ساده - شما مجموع را بر اساس رنگ (از طریق قالب بندی شرطی اعمال می شود) نیستید، بلکه بر اساس مقدار در ستون کنار مقدار است. به عنوان مثال، فرض کنید که مبالغ شما در ستون A است، بنابراین در ستون B اگر مبلغ مجاور پرداخت شده باشد، حرف "Y" را وارد کنید. اکنون تنها کاری که باید انجام دهید این است که از فرمول زیر برای تعیین مجموع تمام خطوط مشخص شده به عنوان پرداخت شده استفاده کنید:

=SUMIF(B:B,"Y",A:A)

مزیت سازمان دهی مجدد داده های شما به این روش این است که اطلاعات شما در مورد آنچه پرداخت شده است - و چه چیزی تا به حال - بلافاصله برای فرمول های دیگر "در دسترس" است و حتی می تواند به برنامه های دیگر صادر شود. (شما نمی توانید رنگ یک سلول را به برنامه دیگری صادر کنید.)

از ماکرو استفاده کنید

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

Function SumYellow(rTarget As Range)
    Dim c As Range
    Dim dTotal As Double

    Application.Volatile
    dTotal = 0
    For Each c In rTarget
        If c.Interior.Color = vbYellow Then
            dTotal = dTotal + c.Value
        End If
    Next c
    SumYellow = dTotal
End Function

برای استفاده از UDF در کاربرگ خود، چیزی شبیه به زیر را وارد کنید، با این فرض که محدوده حاوی مقادیر A2:A93 است:

=SumYellow(A2:A93)

اگر فکر می کنید ممکن است سلولی در محدوده مشخص شده به جای عدد عددی حاوی یک مقدار متن باشد، باید ماکرو را کمی تغییر دهید:

Function SumYellow(rTarget As Range)
    Dim c As Range
    Dim dTotal As Double

    Application.Volatile
    dTotal = 0
    For Each c In rTarget
        If c.Interior.Color = vbYellow Then
            If IsNumeric(c) Then dTotal = dTotal + c.Value
        End If
    Next c
    SumYellow = dTotal
End Function

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