شمارش سلول ها با توجه به رنگ در اکسل

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

در ادامه مطلب با ما همراه باشید.

روش اول: با استفاده از دستور VBA

۱. کلیدهایAlt + F11 را فشار دهید تا پنجره Microsoft Visual Basic for Applications باز شود .

شمارش سلول ها با توجه به رنگ در اکسل

۲. در پنجره Microsoft Visual Basic for Applications بر رویInsert > Module کلیک کنید و سپس از کدVBA زیر را کپی و در درون پنجره ماژول قراردهید .

شمارش سلول ها با توجه به رنگ در اکسل

نحوه فعال سازی VBA را اینجا بخوانید

Function CountColor(range_data As range,criteria As range)As Long
Dim datax As range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountColor = CountColor + 1
End If
Next datax
End Function
شمارش سلول ها با توجه به رنگ در اکسل

اکنون میتوانید صفحه را ببندید و از فرمول زیر در داخل جداول اکسل استفاده نمایید.

=CountColor(range_data,criteria)
شمارش سلول ها با توجه به رنگ در اکسل
یه سلول را بعنوان رنگ شرط شمارش مشخص کنید تا با تغییر آن شمارش انجام شود
شمارش سلول ها با توجه به رنگ در اکسل

این تابع به طور خودکار آپدیت نمی شود و شما باید از کلید های ترکیبی Ctrl+Alt+F9 برای آپدیت استفاده کنید.

اگر بخواهید میتوانید این فرمول را به صورت Add-In ذخیره و در فایل های دیگر نیز استفاده نمایید

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

1- ابتدا ستونی که می‌خواهید براساس رنگ فیلتر شود انتخاب کنید.

شمارش سلول ها با توجه به رنگ در اکسل

2- سپس از تب Data، روی دکمه Filter کلیک کنید تا دکمه فیلتر در کنار عنوان ستون ظاهر شود

شمارش سلول ها با توجه به رنگ در اکسل

3- سپس روی دکمه کلیک کرده و از لیست باز شده روی گزینه Filter by Color کلیک نموده و رنگ مورد نظرتان برای فیلتر کردن را انتخاب کنید.

پس از اینکار لیست مورد نظر براساس رنگی که انتخاب کردید فیلتر می‌شود.

شمارش سلول ها با توجه به رنگ در اکسل

4- پس از فیلتر کردن با انتخاب سلول‌های فیلتر شده می‌توانید تعداد و مجموع آن‌ها را در نوار وضعیت پائین اکسل مشاهده کنید و یا اینکه با استفاده از توابع AGGREGATE یا SUBTOTAL تعداد و یا مجموع آن‌ها را محاسبه کنید.

روش سوم: با استفاده از جستجو بر اساس رنگ

1- ابتدا محدوده‌‌ی مورد نظرتان را انتخاب کنید. سپس کلید Ctrl + F را بفشارید تا پنجره Find and Replace باز شود.‌

شمارش سلول ها با توجه به رنگ در اکسل

2- روی تب Find کلیک کنید. روی دکمه Options کلیک کرده و گزینه Format را انتخاب کنید و جزییات جستجو را بر اساس رنگ انتخاب نمایید.

شمارش سلول ها با توجه به رنگ در اکسل
شمارش سلول ها با توجه به رنگ در اکسل

3- سپس روی دکمه Find All کلیک کنید تا نتیجه جستجو در قسمت پائین پنجره جستجو نمایش داده شود.

اکنون می‌توانید همه نتایج جستجو را انتخاب کنید (Ctrl + A) و تعداد آن را در پائین پنجره مشاهده کنید.

شمارش سلول ها با توجه به رنگ در اکسل

روش چهارم: با استفاده از توابع get.cell و Countif

1- به تب Formulas رفته و روی گزینه Define Name کلیک کنید

شمارش سلول ها با توجه به رنگ در اکسل

2- در جدول باز شده موارد زیر را تکمیل نمایید.

شمارش سلول ها با توجه به رنگ در اکسل

Name: GetColor
Scope: Workbook
Refers to:

=GET.CELL(38,Sheet1!$D3)

3- در فرمول بالا به جای Sheet1!$D3 که دومین جز فرمول می باشد آدرس سلول اول ستونی که میخواهید فرمول برای آن بکار رود را جایگزین نمایید.

4- اکنون در ستون جدید فرمول زیر را وارد نمایید تا کد رنگ مربوط به سلول را برای شما نشان دهد.

=GetColor
شمارش سلول ها با توجه به رنگ در اکسل

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

شمارش سلول ها با توجه به رنگ در اکسل

5- اکنون برای شماره سلول ها با رنگ خاص در زیر جدول رنگ یک سلول را به رنگی که میخواهید شمارش کنید تغییر دهید ولی توجه داشته باشید این سلول باید در همان ستونی باشد که در جدول رنگ بندی را انجام داده اید
فرمول زیر را در سلول مقابل آن قرار دهید

=COUNTIF(Table1[Column1],GetColor)
شمارش سلول ها با توجه به رنگ در اکسل

6- به جای محدوده داده شده محدود کد رنگ ها را انخاب نمایید. با تغییر رنگ سلول فرمول تعداد رنگ موجود را خواهد شمرد.

شمارش سلول ها با توجه به رنگ در اکسل

این تابع به طور خودکار آپدیت نمی شود و شما باید از کلید های ترکیبی Ctrl+Alt+F9 برای آپدیت استفاده کنید.

0 پاسخ

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *