در نرم‌افزار Excel مایکروسافت می‌توان برخی سلول‌ها را رنگی کرد تا تشخیص محتوا ساده شود. اکسل سلول‌ها را با تابع count شمارش میکند ولیکن چگونه می‌توان سلول‌هایی که رنگی هستند را شمارش کرد؟ تابعی برای این کار وجود ندارد اما با کمی دستکاری و استفاده از امکانات موجود، می‌توان سلول‌های رنگی را نیز شمرد.
در این مقاله به روش شمارش سلول‌های رنگی در نرم‌افزار اکسل می‌پردازیم. با ما باشید.
چگونه سلول‌های رنگی را در اکسل شمارش کنیم؟
در این مطلب آموزشی می‌خواهیم با استفاده از VBA و همین‌طور بدون آن، تعداد سلول‌های رنگی در یک ناحیه را شماره کنیم و به این منظور دو روش جالب را بررسی می‌کنیم.
- استفاده از Filter و SUBTOTAL در اکسل
- ایجاد یک تابع کاستوم که به کمک VBA نوشته شده است
شمارش سلول‌های رنگی در اکسل با فیلتر و تابع SUBTOTAL
ناحیه‌ای شامل سلول‌های رنگی را در نظر بگیرید. ابتدا در سلولی که قرار است تعداد سلول‌های رنگی آن ستون را نمایش دهد، کلیک کرده و فرمول زیر را وارد کنید.
توجه: که برای فرمول نوشتن در سلول‌های اکسل، همیشه یک = را تایپ کرده و سپس دستور خود را می‌نویسیم.
عدد ۱۰۲ که آرگومان اول در دستور زیر است، در حقیقت برای شمارش سلول‌هایی که در حاض حاضر دیده می‌شوند، به کار می‌رود و این یعنی اگر به روشی، سلولی را پنهان کنیم، در شمارش لحاظ نمی‌شود و این کار را در ادامه با استفاده از فیلترها انجام می‌دهیم.
=SUBTOTAL(102,E1:E20)
همان‌طور که در تصویر زیر می‌بینید، تعداد سلول‌ها از سلول E1 الی E20 در سلولی که فرمول فوق را وارد کرده‌ایم، ذکر شده است. اگر ناحیه‌ی دیگری مدنظرتان است، به جای E1 و E20 نام سلول شروع و پایانی آن بخش را وارد نمایید.
اکنون هدرها را انتخاب کنید. سپس در منوی بالای اکسل مسیر Data > Sort and Filter > Filter را طی کنید تا ابزار فیلتر فعال شده و روی تمام هدرهایی که انتخاب کرده‌اید، اعمال شود. اکنون اگر روی یکی از هدرها کلیک کنید، می‌توانید از منوی کرکره‌ای که نمایش داده میشود، گزینه‌ی Filter by Color یا فیلتر کردن بر اساس رنگ‌ها را انتخاب کنید. قدم بعدی این است که رنگ‌های استفاده شده در ناحیه‌ی موردنظر خود را یکی‌یکی انتخاب کنید تا صرفاً سلول‌های رنگی نمایش داده شوند و بقیه‌ی سلول‌ها، ناپدید شوند.
به محض کلیک کردن روی رنگی مثل نارنجی، فقط سلول‌های نارنجی شمارش می‌شوند و عدد ۴ را خواهید دید. با کلیک کردن روی رنگ سبز و نمایان شدن سلول‌های سبز، تعدادشان به عدد ۴ اضافه میشود و به این ترتیب می‌توانید شمارش دقیقی از سلول‌های رنگی داشته باشید.
شمارش سلول‌های رنگی در اکسل با استفاده از تابع کاستوم
در اکسل می‌توان از ویژوآل بیسیک برای برنامه‌نویسی و ایجاد توابع کاستم (به اشتباه کاستوم) استفاده کرد که آن را به نرم‌افزار قدرتمند تبدیل میکند.
برای اضافه کردن تابع جدید، روی نام شیت فعال کلیک کنید و گزینه‌ی View Code را انتخاب کنید. با کلید ترکیبی Alt + F11 نیز پنجره‌ی کدنویسی نمایان میشود.
در این پنجره در سمت چپ روی شیتی که سلول‌های رنگی را شامل دربرگرفته، راست‌کلیک کنید و Insert و سپس Module را انتخاب کنید.
یک ماژول جدید ایجاد میشود و کمی پایین‌تر در پوشه‌ی Modules قرار می‌گیرد. روی نام آن که ماژول۱ است دبل‌کلیک کنید و کد زیر را در پنجره‌ی سمت چپ پیست کنید:
'Code created by Sumit Bansal from https://trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function
اکنون می‌توانید پنجره‌ی ویژوآل بیسک را ببندید و به شیت خود در اکسل برگردید. حال روی سلولی که قرار است تعداد سلول‌های سبز یا ... را نمایش دهد، کلیک کنید و سپس Shift + F3 را فشار دهید تا پنجره‌ی اضافه کردن تابع نمایان شود. از منوی کرکره‌ای، User Defined را انتخاب کنید. تنها تابع موجود، تابعی است که اخیراً اضافه کرده‌اید. روی آن کلیک کرده و سپس روی دکمه‌ی OK کلیک کنید.
در پنجره‌ی تابع GetColorCount، روی فیلد بالایی کلیک کرده و سپس روی سلول ابتدایی محدوده‌ی موردنظر خود کلیک کرده و درگ کنید تا به سلول انتهای محدود برسید.
اکنون روی فیلد دوم کلیک کنید و سپس روی یکی از سلول‌های رنگی کلیک کنید. در نهایت روی OK کلیک کنید تا تابع در سلول انتخابی، ثبت شود. در این سلول تعداد سلول‌های هم‌رنگ با سلولی که به عنوان مرجع مشخص شده، ذکر میشود. برای هر رنگ می‌بایست یک سلول شمارش تعداد در نظر بگیرید. مثل نمونه‌ای که در تصویر مشاهده می‌کنید.
در نهایت با استفاده از تابع SUM و انتخاب کردن دو سلول شمارش رنگ‌های سبز و نارنجی، مجموع سلول‌های رنگی مشخص میشود. به این منظور در دستور زیر به جای I3 و I4 نام سلول‌های شمارش تعداد خود را قرار دهید:
=sum(I3;I4;I5)
در صورت نیاز می‌توانید نام سلول‌های بیشتری را در این فرمول اضافه کنید. کافی است بعد از آخرین سلول ; و نام سلول بعدی را اضافه کنید.
توجه کنید که در صورت بسیار بزرگ بودن یک صفحه‌ی اکسل، می‌توانید تابع شمارش خانه‌های رنگی را به صورت دستی تایپ کنید. به عنوان مثال اگر منظور شما این باشد که جدولی با شروع از خانه‌ی دوم در ستون اول تا خانه‌ی ۱۰۰۰ در ستون چهارم بررسی شده و تعداد خانه‌های رنگی شمارش شود، می‌توانید فرمول زیر را تایپ کنید:
=GetColorCount(A2:D1000,D2)
و همان‌طور که در تصویر مشاهده می‌کنید، در مثال ما خروجی این تابع به سرعت محاسبه میشود و سلول‌های آبی‌رنگ در هر چهار ستون را نمایش می‌دهد:
trumpexcelسیاره‌ی آی‌تی
بسیار عالی بود به من خیلی کمک کرد. ممنون از شما. فقط یه سوال داشتم از حضورتون، اونجایی که اینسرت فانکشنوو زدیم و تابع رو آوردیم، توی قسمتی که می خوایم محدوده سلول ها رو انتخاب کنیم چطور باید بگیم کل اکسلو انتخاب کنیم؟ یعنی بهش بگیم تو کل اکسل دنبال رنگ مورد نظر بگرده؟
سلام.
رنج رو میتونید به صورت Start:End مشخص کنید. مثلاً برای یه جدول خیلی بزرگ اکسل میشه نوشت A1:F1000. یعنی از اولین سطر و ستون الی ستون F و ردیف ۱۰۰۰ام.
بسیار عالی،سپاسگزارم.
خیلی عالی . متشکرم از مطلب خوبتون. بسیار کاربردی و عالی بود.
با سپاس.
در صورتیکه فرمول را در یک سل نوشته و تعداد سلهای رنگی محاسبه شده باشد، وقتی که در محدوده محاسبه مجدداً یک یا چند سل را رنگی نمایید به طور اتومات عدد شمارنده اضافه نمی شود ( برخلاف سایر فرمولهای اکسل همچون جمع و… ) و فقط در صورتیکه فرمول را مجدداً رفرش نمایید عدد مربوط به تعداد سلهای رنگی آبدیت میشود، چنانچه راهکاری جهت رفع این نقص وجود دارد ممنون میشوم راهنمایی بفرمایید.
سلام
بله منم تست کردم این مشکل رو داره. بررسی میکنم ببینم راهی پیدا میکنم براش یا نه.
من سرچ کردم یه سری روش گفته بودن که هیچ کدوم جواب نداد. تنها راهی که پیدا کردم اینه که Ctrl + Alt + F9 رو بزنید تا محاسبات از نو انجام بشه در صفحه‌ی فعلی.
با سلام و عرض وقت بخیر بنده سه تا جدول رو از از شیت های مختلف ولوکاپ کردم و ستون های مربوط به هم رو جلوی هم گذاشته ام و بعد از آن کل شیت رو به table تبدیل کردم ولی بعضی از ستون هام با وجود این که همه سلول های زیرش عدد می باشد برای سورت کردن گزینه sort smallest to largest یا sort largest to smallest نمیاد و بر اساس sort A to Z یا sort Z to A فیلتر میکند با وجود اینکه اون ستون رو همه رو از general به number تبدیل کرده ام ممنون میشم اگر راهنمایی بفرمایید
سلام
وقتی به Table تبدیل می‌کنیم، توی گوگل شیتس که گاهی مشکل Sort داره. اکسل رو یادم نیست مشکل داشت یا نه چون کلاً به Table خیلی تبدیل نمی‌کردم.
اگر داده‌های فایل حالت شخصی نداره، برام ایمیل کنید تست کنم ببینم راهی به ذهنم میرسه.
alireza1411@gmail.com
سلام خیلی مچکرم بابت تابع کاستوم سوالم این هست که این تابع تا چند نوع رنگ مختلف رو میتونه شناسایی کنه؟
سلام
این مطلب رو خیلی وقت پیش خودم نوشته بودم و تا جایی که یادم هست توش رنگ رو به شکل کد Hex می‌دادیم. بنابراین تعداد رنگ‌ها میشه ۱۶ میلیون. برای هر رنگ اصلی، ۰ تا ۲۵۵ میشه عدد داد و لذا ۲۵۶ در ۲۵۶ در ۲۵۶ حالت مختلف رنگ میتونیم داشته باشیم.
فایل اکسلم انگار خیلی سنگین شد تو سیستم مدیرمم باز نمیشه حجمش 215KB هست ولی به سختی باز میشه
سلام بسیار عالی فقط وقتی فایل رو سیوش میکنم پیام میده اگه سیو بشه فرمول برای آینده عمل نخواهد کرد و اگه رنگی کم وزیادبشه خطا می ده و با رفرش هم درست نمیشه آیا راهی داره؟