در دنیای دادهمحور امروز، تسلط بر ابزارهای تحلیل داده مانند Microsoft Excel به یکی از مهارتهای ضروری برای افراد و سازمانها تبدیل شده است. یکی از چالشهای رایج هنگام کار با دادهها، شمارش سریع و دقیق مقادیر مختلف در میان حجم زیادی از اطلاعات است. انجام این کار بهصورت دستی علاوه بر زمانبر بودن، احتمال بروز خطا را نیز افزایش میدهد.
توابع شمارشی اکسل این مشکل را بهسادگی برطرف میکنند. با استفاده از این توابع میتوان تنها در چند ثانیه تعداد سلولهای دارای عدد، متن، سلولهای خالی یا حتی دادههایی که شرایط خاصی دارند را محاسبه کرد و گزارشهای دقیقتری تهیه نمود.
در این مقاله از مجموعه آموزش اکسل، با ۵ تابع اصلی شمارش سلولها در اکسل آشنا میشوید و کاربرد هر یک را همراه با مثالهای عملی یاد خواهید گرفت. در پایان نیز خواهید دانست که در هر موقعیت، کدام تابع بهترین انتخاب است تا بتوانید سرعت و دقت تحلیل دادههای خود را به شکل قابل توجهی افزایش دهید.
فلسفه مدیریت داده و اهمیت استراتژیک شمارش سلولها در اکسل
شمارش سلولها در اکسل تنها یک عملیات ساده نیست، بلکه یکی از ابزارهای مهم برای تحلیل داده و تصمیمگیری در سازمانها به شمار میرود. بسیاری از گزارشهای مدیریتی، داشبوردها و تحلیلهای کسبوکار بر پایه شمارش صحیح دادهها ساخته میشوند. بنابراین انتخاب تابع مناسب برای شمارش، تأثیر مستقیمی بر دقت گزارشها و کیفیت تصمیمات مدیریتی خواهد داشت.
برای مثال، یک کسبوکار میتواند با استفاده از توابع شمارشی، تعداد مشتریان فعال در هر ماه، تعداد سفارشهای ثبتشده، تعداد محصولات موجود یا ناموجود در انبار و حتی تعداد سفارشهایی را که مبلغ آنها بیش از ۵ میلیون تومان است، بهسرعت محاسبه کند. این اطلاعات در برنامهریزی فروش، مدیریت موجودی و ارزیابی عملکرد واحدهای مختلف نقش بسیار مهمی دارند.
اهمیت دقت در شمارش دادهها
هرچند شمارش سلولها در ظاهر ساده به نظر میرسد، اما انتخاب نادرست تابع میتواند نتایج گمراهکنندهای ایجاد کند. برای نمونه، تابع COUNT فقط سلولهای دارای مقدار عددی را میشمارد و سلولهای متنی را نادیده میگیرد. از طرف دیگر، تابع COUNTBLANK تنها سلولهای واقعاً خالی را تشخیص میدهد و ممکن است سلولهایی که تنها شامل یک فاصله (Space) هستند را خالی محسوب نکند.
به همین دلیل، تفاوت میان یک کاربر عادی و یک تحلیلگر حرفهای اکسل، در شناخت دقیق رفتار این توابع و انتخاب صحیح آنها در شرایط مختلف است.
فرض کنید مدیر فروش قصد دارد پاداش کارکنان را بر اساس تعداد سفارشهای ثبتشده محاسبه کند. اگر برای شمارش سفارشها بدون بررسی کیفیت دادهها از تابع COUNTA استفاده شود، ممکن است سلولهایی که شامل متنهای غیرضروری، مقادیر خطا یا اطلاعات ناقص هستند نیز در شمارش لحاظ شوند. چنین اشتباهی میتواند باعث ارائه گزارش نادرست، محاسبه اشتباه پاداشها و در نهایت ایجاد نارضایتی در تیم شود.
آشنایی با جعبهابزار شمارش در اکسل
در ادامه این مقاله، با مهمترین توابع شمارشی اکسل آشنا خواهید شد؛ توابعی که پایه و اساس بسیاری از گزارشها، داشبوردها و تحلیلهای حرفهای را تشکیل میدهند.
هدف ما تنها معرفی فرمولها نیست، بلکه میخواهیم بدانید هر تابع دقیقاً چه نوع دادهای را میشمارد، چه محدودیتهایی دارد و در چه شرایطی باید از آن استفاده کنید. با یادگیری این نکات، میتوانید با اطمینان بیشتری دادههای خود را تحلیل کرده و گزارشهایی دقیقتر و حرفهایتر تهیه کنید.
آشنایی با تابع COUNT در اکسل (شمارش سلولهای عددی)
تابع COUNT یکی از پرکاربردترین توابع شمارشی در اکسل است که برای شمارش سلولهای حاوی مقادیر عددی استفاده میشود. هر زمان بخواهید بدانید در یک محدوده چند سلول دارای عدد هستند، این تابع بهترین انتخاب خواهد بود.
نکته مهم این است که تابع COUNT فقط سلولهایی را میشمارد که مقدار آنها از نوع عدد باشد. از آنجایی که اکسل تاریخ و زمان را بهصورت عدد ذخیره میکند، این مقادیر نیز توسط تابع COUNT شمارش میشوند. اما اگر تاریخ یا زمان بهصورت متن وارد شده باشند، این تابع آنها را نادیده میگیرد.
ساختار تابع COUNT
آرگومانهای تابع:
- value1: محدوده یا اولین مقداری که باید بررسی شود.
- value2: (اختیاری) محدوده یا مقدار دوم برای شمارش.
نکات مهم درباره تابع COUNT
هنگام استفاده از تابع COUNT باید به این نکته توجه داشته باشید که این تابع تنها مقادیر عددی را شمارش میکند و موارد زیر را نادیده میگیرد:
- سلولهای متنی
- سلولهای خالی
- مقادیر منطقی (TRUE و FALSE)
- مقادیر خطا مانند #N/A یا #VALUE!
به همین دلیل، اگر هدف شما شمارش تمام سلولهای پرشده باشد، بهتر است از تابع COUNTA استفاده کنید.
مثال کاربردی از تابع COUNT
فرض کنید در ستون مبلغ فروش، بیشتر سلولها شامل مقادیر عددی هستند، اما در برخی ردیفها بهجای مبلغ، عبارت “ناموجود” وارد شده است.
اگر از تابع زیر استفاده کنید:
اکسل فقط سلولهایی را که دارای عدد هستند شمارش میکند و سلولهایی که عبارت “ناموجود” در آنها نوشته شده است، در نتیجه نهایی لحاظ نخواهند شد.
بنابراین اگر قصد دارید تعداد مبالغ ثبتشده را محاسبه کنید، تابع COUNT انتخاب مناسبی است. اما اگر هدف شما شمارش تمام ردیفهای دارای اطلاعات باشد، باید از تابع COUNTA استفاده کنید.
نمونه فرمول:
اگر دادههای شما در محدوده A2:A10 قرار داشته باشد، فرمول به شکل زیر خواهد بود:
شمارش سلول ها در اکسل در تابع COUNTA به جز سلول های خالی
اگر شما تابع COUNT را در اکسل به عنوان نگهبان اعداد در نظر بگیرید تابع COUNTA را می توانید به عنوان شناسنده حضور معرفی نمایید. هنگامی که نیاز دارید بدانید چند سلول فارغ از اینکه دارای عدد، متن و یا فرمول حاوی محتوا هستند، می توانید از تابع COUNTA برای این منظور استفاده نمایید. این تابع هر سلولی که خالی نباشد را شمارش می کند. در نتیجه در این رابطه با محدودیتی مواجه نیست. ساختار این تابع به شکل زیر است:
نکته مهمی که کاربران در زمان استفاده از این تابع در اکسل باید به آن توجه نمایند، این است که حرف اختصاری A که در نام تابع قید شده در واقع مخفف کلمه All می باشد. کلمه All نشان دهنده شمارش تمام مقادیر غیر خالی در یک محدوده داده ای خاص می باشد. در نهایت ما از این تابع می توانیم در جهت شمارش تعداد کل ردیف های پر شده در دیتابیس مشتریان بهره ببریم. بنابراین تابع COUNTA قادر می باشد تمام سلول هایی که به نحوی دارای داده ای خاص هستند را شمارش کند. این سلول ها عموما حاوی متن، عدد، تاریخ، خطا و حتی سلول های دارای یک نشانه Space می باشند.
چالش حرفه ای در زمان کاربرد تابع شمارشی در اکسل
بسیاری از کاربران از تابع COUNTA برای پیدا کردن تعداد کل مشتریان استفاده می کنند. اما یک مشکل بزرگ در این باره وجود دارد، در واقع اگر یک سلول حاوی یک فاصله باشد از نظر کاربر به عنوان یک سلول خالی در نظر گرفته می شود. اما از نظر این تابع آن سلول پر است و اینجاست که شما به عنوان یک متخصص باید بدانید که خروجی این تابع ممکن است بزرگتر از واقعیت باشد.
مثال:
فرض کنید در محدوده A1:A6 از یک کاربرگ اکسل، مقادیر 10، Ali، 0، یک سلول خالی، Hello و FALSE بهترتیب در سلولهای A1 تا A6 قرار گرفته باشند.
فرمول این تابع به شکل روبرو خواهد بود:
کاربرد تابع COUNTBLANK در شناسایی فضای خالی یا حفره های اطلاعاتی
تابع COUNTBLANK یکی از توابعی می باشد که در تحلیل داده های اکسل به کمک کاربران می آید. به خاطر داشته باشید در کسب و کارها گاهی شناسایی آنچه موجود نیست اهمیت بیشتری نسبت به بررسی دارایی ها دارد. از این تابع به منظور شمارش تعداد سلول های خالی در یک محدوده مشخص استفاده می کنند.
کاربرد تابع COUNTBLANK در مدیریت کیفیت
در مدیریت کیفیت از این تابع به منظور بررسی فرم های ناقص مشتریان بهره می برند. برای مدیریت کیفیت داده ها دانستن تعداد سلول های خالی امری الزامی می باشد. تابع COUNTBLANK نیز یکی از مهم ترین توابعی است که در اکسل این وظیفه را بر عهده دارد. فرض نمایید یک فرم ثبت اطلاعات مشتری در دست دارید. برای اینکه بدانید فرم ها چقدر کامل و کاربردی هستند می توانید از تابع COUNTBLANK بر روی ستون های حیاتی مانند شماره تماس ها و یا ایمیل ها استفاده نمایید. اگر تعداد COUNTBLANK در ستون تماس بالا بود به این معنی است که تیم فروش در جمع آوری اطلاعات ناقص عمل کرده است.
ساختار تابع:
نکته مهمی که در زمان کاربرد این تابع در اکسل باید در نظر گرفته شود این مورد می باشد که این تابع فقط قادر به شمارش سلول هایی است که هیچ گونه داده و یا فاصله ای در آن منظور نشده است.کاربرد حرفه ای این تابع نیز به منظور پیدا نمودن ردیف های نقص در فرم های ثبت نام و یا پرسش نامه های تحقیقاتی و اداری می باشد.
مثال برای نوع کاربرد تابع COUNTBLANK
اگر شما در محدوده A1:A6 شاهد حضور مقادیر زیر باشید ساختار تابع به چه صورت خواهد بود:
فرض کنید محدوده A1:A6 شامل مقادیر زیر باشد: سلول A1 خالی، A2 برابر با 10، A3 خالی، A4 شامل متن All، A5 خالی و A6 برابر با 25 باشد. در این حالت، با استفاده از فرمول COUNTBLANK(A1:A6)= تعداد سلولهای خالی این محدوده محاسبه میشود. از آنجا که سلولهای A1، A3 و A5 خالی هستند، نتیجه این فرمول 3 خواهد بود.
فرمول تعریف شده برای این تابع به شکل زیر خواهد بود:
قدرت شرط ها برای ورود به دنیای تحلیل هوشمند
اگر توابع قبل را ابزارهای اندازه گیری بدانیم توابع شرطی را باید مغز متفکر نرم افزار اکسل دانست. در دنیای واقعی ما هیچ وقت نمی خواهیم فقط بدانیم که چه تعداد عدد در لیست وجود دارد؟ ما هیچ وقت نمی خواهیم بدانیم چند فروش در منطقه جنوب داشتیم؟ یا چند مشتری از مشتریان وفادار ما در ماه گذشته خرید کرده اند؟ اینجاست که قدرت COUNTIF و COUNTIFS آشکار خواهد شد.
تابع COUNTIF در اکسل که به منظور شمارش شرطی استفاده می شود
زمانی که برای شمارش، معیار و یا شرط خاصی مد نظر ندارید، می توانید از تابع COUNTIF برای این منظور استفاده نمایید. تابع COUNTIF به شما اجازه می دهد یک محدوده ای را انتخاب و سپس یک شرط برای آن تعیین کنید. در این شرایط اکسل در کل این محدوده می چرخد و فقط سلول هایی را شمارش می نماید که با شرط شما همخوانی دارند. برای مثال در شرایطی که اقدام به شمارش نمرات بالای 15 در یک محدوده داده ای خاص می نماید قادر به مدیریت این فرایند با استفاده از تابع COUNTIF خواهید بود. ساختار تابع COUNTIF به شرح زیر می باشد:
نکته مهمی که نیاز است تا کاربران در زمان استفاده از این تابع به آن توجه نمایند، این است که تمام شرط های منظور شده در این توابع باید داخل کوتیشن (” “) قرار گیرند. برای نمونه در مثال بررسی نمرات بالای 15 در محدوده ای خاص، باید این شرط به این صورت (“>15”) نگارش شود.
در این مثال، از فرمول زیر برای شمارش مشتریان شهر «تهران» استفاده شده است:
کار با اعداد و عملگرهای مقایسه ای
بسیاری از کاربران بر این باورند که تابع COUNTIF در اکسل فقط برای متن قابل کاربرد است. اما قدرت واقعی این تابع در کار با عملگرهای ریاضی است. برای استفاده از این عملگرها باید آن ها را داخل کوتیشن قرار دهیم.
بررسی سناریوی مدیریت انبار به کمک تابع COUNTIF
فرض کنید میخواهید بدانید چند قلم کالا در انبار دارید که موجودی آنها “کمتر از ۱۰ عدد” است (تا بتوانید سفارش جدید دهید). ساختار تابع در این شرایط به شکل زیر خواهد بود.
در این شرایط اکسل به جای شمارش سلول ها در اکسل در قالب همه کالاهای موجود، فقط آنهایی را شمارش می نماید که در وضعیت کمبود هستند.
لیست عملگرهای کاربردی در اکسل
- “>50” : بزرگتر از ۵۰
- “<=100” : کوچکتر یا مساوی ۱۰۰
- “<>0” : هر چیزی که صفر نباشد (بسیار کاربردی برای حذف داده های بی اثر)
شمارش سلول ها در اکسل با متن خاص
شمارش سلولهایی که با حرف A شروع میشوند:
تابع COUNTIFS در اکسل به منظور شمارش چند شرطی
در دنیای واقعی شرایط هیچ وقت تک بعدی نیست و شما ممکن است عوامل متعددی را در نظر داشته باشید. در این شرایط عموما کاربران حرفه ای اکسل از تابع COUNTIFS برای مدیریت فرایندها بهره می برند. تابع COUNTIFS قدرتمندترین ابزار خانواده توابع شمارشی به شمار می رود. تابع COUNTIFS به کاربر این اجازه را می دهد تا چندین شرط را به صورت همزمان در زمان شمارش سلول ها در اکسل اعمال نماید. به خاطر داشته باشید در زمان کاربرد این تابع هر چقدر تعداد توابع افزایش یابد دقت تحلیل های شما نیز بالاتر خواهد رفت.
ساختار این تابع
نکته مهم در زمان کاربرد این تابع این است که تمامی محدوده ها در این تابع باید ابعاد یکسانی داشته باشند تا تابع دچار خطا نشود. کاربرد پیشرفته ای که برای این تابع قید شده به منظور شمارش تعداد فروش های موفق( شرط اول) در بازه زمانی مشخص ( شرط دوم) برای یک محصول خاص (شرط سوم) می باشد. در نتیجه همانگونه که مشاهده می نماید در این حالت کاربر به طور همزمان سه شرط متفاوت را در یک تابع اعمال نموده است.
مثال اول:
فرض کنید جدولی دارید با ستون های ارائه شده در زیر:
- ستون A: نام کارمند
- ستون B: منطقه جغرافیایی
- ستون C: مبلغ فروش
هدف: میخواهیم با مطالعه این داده ها بدانیم “احمدی” در منطقه “شمال” چند بار فروشی انجام داده که مبلغ آن “بیشتر از ۲ میلیون تومان” بوده است. در نهایت فرمول نهایی برای این مثال به شکل زیر خواهد بود:
تحلیل عملکرد این فرمول
- شمارش سلول ها در اکسل ابتدا در ستون A صورت می گیرد و فقط ردیفهایی که “احمدی” هستند را علامت میزند.
- سپس اکسل در همان ردیف ها، نگاه می کند که آیا در ستون B کلمه “شمال” وجود دارد یا از این کلمه استفاده نشده است.
- در نهایت، فقط ردیف هایی که هر دو شرط بالا را داشتند بررسی می شوند، تا با شمارش سلول ها در اکسل این موضوع را بررسی نماید که مبلغ در ستون C بیشتر از ۲ میلیون هست یا مقدار واقعی با این مقدار متفاوت می باشد. بنابراین می توان گفت، این قدرت شمارش سلول ها در اکسل می باشد که به کاربر این امکان را می دهد تا از میان هزاران داده در کمترین زمان داده های مورد نظر خود را استخراج کند.
مثال دوم:
اگر ستون A شامل نمره و ستون B شامل جنسیت باشد ساختار تابع COUNTIFS با وجود دو شرط فوق به شکل زیر خواهد بود.
این فرمول فقط ردیف هایی را شمارش می کند که هم برای آنها نمرات بالای 10 منظور شده است و هم جنسیت آنها دختر می باشد.
بخش تمرین های کاربردی
کاربران باید به خاطر داشته باشند که برای تثبیت یادگیری باید این سه تمرین را در فایل اکسل خود پیاده سازی کنند:
تمرین ۱: تحلیل کارنامه دانش آموزان
سوالی که در این رابطه می توانیم مطرح نماییم این است که چند دانش آموز در کلاس الف یا همان شرط اول موفق به کسب معدل بالای 17 شده اند.
تمرین 2: مربوط به بررسی مدیریت موجودی یک انبار است
در این بین سوالی که ممکن است برای مخاطب پیش آید این موضوع است که کالاهایی که موجودی آن ها صفر است( یعنی سلول ها خالی هستند یا مقدار آنها صفر منظور شده است) را مشخص نمایید.
تمرین 3: نیز به منظور پاک سازی داده ها در نظر گرفته می شود:
در اینگونه تمرین ها برای مثال چند سلول از لیست ایمیل های پر شده را مورد بررسی قرار می دهیم که چه تعداد نفرات ایمیل خود را ثبت نموده اند:
شناخت مشکلاتی که در زمان شمارش سلول ها در اکسل با آنها مواجه می شویم
حتی افراد حرفه ای نیز گاهی ممکن است در این بخش دچار خطا شوند. برای اینکه شما بتوانید داده های کاربردی را در زمان شمارش سلول ها در اکسل به دست آورید، بهتر است با مشکلاتی که در این مسیر شما را تهدید می کنند آشنا شوید.
تضاد در محدوده
یکی از شایع ترین خطاها این است که محدوده اول و دوم با همدیگر یک اندازه نباشند. مثلا محدوده اول را از ردیف 1 تا 100 انتخاب کنید( A1:A100) اما محدوده دوم را از ردیف 2 تا 1000(B2:B1000) انتخاب نمایید. در این شرایط اکسل به کاربر خطای #VALUE! را خواهد داد. در نتیجه کاربران باید همیشه دقت کنند که تمام محدوده ها باید دقیقا از یک ردیف شروع و در یک ردیف تمام شوند.
فراموش کردن استفاده از کوتیشن در عبارات متنی و منطقی
به خاطر داشته در صورتی که در یک فرمول نیاز به نگارش کوتیشن باشد و شما نگارش آن را فراموش کرده باشید اکسل فرمول شما را به عنوان یک دستور ریاضی اشتباه در نظر می گیرد و خطا می دهد. در نتیجه کاربران در هر جایی که از عملگرهای مقایسه ای استفاده می نمایند باید حتما از کوتیشن نیز استفاده کنند.
اشکال در اعداد ذخیره شده به عنوان متن
اگر در ستون مبلغ، عددی وجود داشته باشد، که اکسل آن را متن تشخیص دهد تابع COUNTIFS آن را نادیده می گیرد. در نتیجه همیشه قبل از تحلیل داده ها با استفاده از تابع ISNUMBER سلامت داده ها را بررسی نمایید.
