با استفاده از COUNTIF با رنگ

Question

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

پاسخ کوتاه این است که نمی توان از COUNTIF برای بررسی رنگ پس زمینه یا هر قالب بندی استفاده کرد. فقط می تواند مقادیر را آزمایش کند. اگر فقط باید یک یا دو بار تعداد سلول های بنفش را مشخص کنید، می توانید از ویژگی Excels Find and Replace برای فهمیدن آن استفاده کنید. این مراحل را دنبال کنید:

شکل 1. تب Find از کادر محاوره ای Find and Replace.

شکل 2. کادر محاوره ای Find Format.

1. سلول های تشکیل دهنده تقویم شما را انتخاب کنید.
2. Ctrl+F را فشار دهید . اکسل تب Find از کادر محاوره ای Find and Replace را نمایش می دهد.
3. در صورت موجود بودن دکمه Options را کلیک کنید. اکسل کادر محاوره ای را گسترش می دهد. (شکل 1 را ببینید.)
4. مطمئن شوید که کادر Find What خالی است.
5. روی دکمه Format کلیک کنید. اکسل کادر محاوره ای Find Format را نمایش می دهد. (شکل 2 را ببینید.)
6. روی دکمه Choose Format From Cell در پایین کادر محاوره ای کلیک کنید. کادر محاوره ای Find Format ناپدید می شود و نشانگر ماوس به علامت مثبت با قطره چکان در کنار آن تغییر می کند.
7. روی سلولی کلیک کنید که فرمت آن هایی است که می خواهید پیدا کنید. (به عبارت دیگر، روی یک سلول بنفش کلیک کنید.) نشانگر ماوس به حالت عادی باز می گردد.
8. روی Find All کلیک کنید. کادر محاوره ای Find and Replace گسترش می یابد تا تمام سلول های منطبق با قالب را فهرست کند، و تعداد سلول ها در پایین کادر محاوره ای وجود دارد.
9. روی Close کلیک کنید تا کادر گفتگوی Find and Replace حذف شود.

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

Function CountColorIf(rSample As Range, rArea As Range) As Long
    Dim rAreaCell As Range
    Dim lMatchColor As Long
    Dim lCounter As Long

    lMatchColor = rSample.Interior.Color
    For Each rAreaCell In rArea
        If rAreaCell.Interior.Color = lMatchColor Then
            lCounter = lCounter + 1
        End If
    Next rAreaCell
    CountColorIf = lCounter
End Function

برای استفاده از ماکرو، تنها کاری که باید انجام دهید این است که سلولی را تهیه کنید که رنگ پس زمینه مورد نظر و محدوده مورد آزمایش را داشته باشد. به عنوان مثال، اجازه دهید بگوییم که سلول A57 با همان رنگ پس زمینه بنفش که در سلول های تقویم خود استفاده می کنید، فرمت شده است. اگر تقویم در سلول های A1:G6 قرار دارد، می توانید از موارد زیر برای دریافت تعداد سلول های بنفش استفاده کنید:

=CountColorIf(A57, A1:G6)

لازم به ذکر است که اگر رنگ یک سلول در تقویم خود را تغییر دهید، باید کاری انجام دهید تا کاربرگ را مجدداً محاسبه کند. به نظر می رسد که اکسل پس از تغییر رنگ پس زمینه، محاسبه مجدد خودکار انجام نمی دهد.

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

http://www.cpearson.com/excel/colors.aspx
https://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
http://xldynamic.com/source/xld.ColourCounter.html

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

https://www.extendoffice.com/product/kutools-for-excel.html

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