آموزش countif در اکسل به زبان ساده

اگر برایتان پیش آمده که در نرم‌افزار اکسل بخواهید تعداد چند سلول که از شرط خاصی پیروی می‌کنند را بشمارید، بهتر است تا با تابع countif آشنا شوید. اگر می‌خواهید بدانید که تابع countifs در اکسل چیست، این مطلب را از دست ندهید. در این مطلب ضمن معرفی این تابع، به آموزش countif در اکسل به زبان ساده و به‌صورت مرحله به مرحله پرداخته‌ایم.

تابع countif در اکسل چیست؟

تابع countif (بخوانید کانت ایف) از دو تابع count و if ساخته شده است. تابع count، یک تابع شمارنده یا فرمول شمارش تعداد رکورد است. در اکسل شمارش سلول‌ها با تابع count مقدور است. خروجی این تابع، یک عدد است که به‌عنوان «تعداد رکوردها» برگردانده می‌شود. تابع if نیز یک تابع شرطی است که برقرار بودن یک شرط خاص را بررسی می‌کند. پس می‌توان گفت که تابع countif، به شمارش سلول‌هایی می‌پردازد که شرط خاص در آن‌ها برقرار شده باشد. اگر تعداد شرط‌ها، بیش‌تر از یک گزینه باشد، باید از تابع countifs استفاده کنید. در ادامه، به چگونگی استفاده از این توابع پرداخته‌ایم.

دوره پیشنهادی: آموزش معتبرسازی داده‌ها در اکسل 2016

فرمول تابع countif در اکسل

همان‌طور که در بخش قبل شرح دادیم، تابع countif باید شرط خاصی را دریافت کند. پس قطعاً یکی از آرگومان‌های این تابع، criteria یا شرط خاص است. این شرط می‌تواند یک متن یا عدد مشخص، یک عملگر منطقی و یا کاراکتر wild card باشد. آرگومان دیگر تابع countif، range یا محدوده‌ی سلول‌هایی است که می‌خواهیم عمل شمارش در آن‌جا انجام شود. به عبارتی، جست‌وجو توسط تابع countif، تنها در سلول‌هایی انجام می‌شود که آدرس آن‌ها در آرگومان ورودی تابع، درج شده باشد.

بنابر آن‌چه که در این بخش بیان کردیم، شکل ظاهری تابع countif، به صورت COUNTIF (Range , criteria) نوشته می‌شود. برای تابع countifs چون چند شرط ورودی دریافت می‌کند، شکل آرگومان‌ها به صورت COUNTIFS (Range1 , criteria 1 , Range۲ , criteria ۲) خواهد بود. مثال‌هایی که در ادامه ذکر شده‌اند؛ درک مطلب را ساده‌تر می‌کنند.

کاربرد تابع countif

تابع countif در اکسل، کاربردهای فراوانی دارد. به عنوان مثال، در پایگاه داده‌های انبار کالا در یک فروشگاه، این توابع، کاربردهای زیادی دارد. شما می‌توانید موجودی کالاها را برحسب رنگ، مدل، نام کالا و … محاسبه کنید. مثلاً اگر یک انبار کالاهای بهداشتی داشته باشید، می‌توانید محاسبه کنید که از خمیردندان برند «الف» چه تعداد در انبار موجود است. یا این‌که شوینده‌های لباس (مشکین‌شوی، نرم‌کننده و …) از برند «الف» چه تعدادی در انبار موجود است. این محاسبات، برای سفارش‌های بعدی و یا شارژ مجدد کالاها، ضرورت دارد. کافی است تا کار با توابع countif و countifs را یاد داشته باشید.

پایگاه داده‌های اطلاعات دانش‌آموزان، انبارهای کالا،‌ سفارشات اینترنتی، هزینه‌ها و درآمدهای شرکت و … برخی از مثال‌های کاربرد توابع countif  و countifs خواهند بود. خلاصه این‌که هرجا که بخواهید روی داده‌های خود، عمل شمارش همراه با اعمال شرط یا شرایط، انجام دهید؛ می‌توانید از این توابع استفاده کنید.

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

عملگرهای منطقی در شرط تابع countif

پیش‌تر بیان کردیم که برای شرط (criteria) در تابع countif، می‌توان از اپراتورها یا عملگرهای منطقی استفاده کرد. در این بخش، اشاره‌ی مختصری به این عملگرها کرده‌ایم.

• > عملگر« بزرگ‌تر از». این عملگر زمانی استفاده می‌شود که می‌خواهید شرط را در صورت بزرگ‌تر بودن از مقدار مشخص، تعیین کنید.
• > عملگر« کوچک‌تر از». این عملگر زمانی استفاده می‌شود که می‌خواهید شرط را در صورت کوچک‌تر بودن از مقدار مشخص، تعیین کنید.
• = عملگر«مساوی با». این عملگر زمانی استفاده می‌شود که می‌خواهید شرط را در صورت برابر بودن با مقدار مشخص، تعیین کنید.
• <= عملگر« بزرگ‌تر مساوی». این عملگر زمانی استفاده می‌شود که می‌خواهید شرط را در صورت بزرگ‌تر بودن از مقدار مشخص یا برابر بودن با آن، تعیین کنید.
• >= عملگر« کوچک‌تر مساوی». این عملگر زمانی استفاده می‌شود که می‌خواهید شرط را در صورت کوچک‌تر بودن از مقدار مشخص یا برابر بودن با آن، تعیین کنید.

Wild card چیست؟

موضوع دیگری که در بخش شرط در تابع countif، استفاده می‌شود؛ Wild card است. Wild card‌ها کاراکترهایی هستند که در هنگام جست‌وجو در یک پایگاه داده، به کار برده می‌شوند. این کاراکترها، از نوشتن چند گزینه، جلوگیری می‌کنند و به‌نوعی، در اختصارنویسی کاربرد دارند.

دوره پیشنهادی : آموزش اکسل برای مارکترها

Wild cardهایی که در تابع countif در اکسل، کاربرد دارند، * ـ یا ؟ هستند. در ادامه،‌ کاربرد هریک را توضیح داده‌ایم.

• از کاراکتر * زمانی استفاده می‌شود که بخواهیم شرط جست‌وجو را طوری بنویسیم که رشته‌ای از حروف، بدون محدودیت باشند. به عنوان مثال، زمانی که جست‌وجو تنها به کاراکتر اول یا آخر یک رشته محدود می‌شود، می‌توانیم از * استفاده کنیم. به این صورت که اگر بنویسیم: «م*»، جست‌وجو به رشته‌هایی محدود می‌شود که با م آغاز می‌شوند و در ادامه، هر کاراکتر دیگری می‌توانند داشته باشند. «مینا»، «مریم»، «مهسا»، «مرتضی» و … برخی از نتایج چنین شرطی روی سلول‌های اسامی خواهند بود.
• از کاراکتر ـ زمانی استفاده می‌شود که بخواهیم جست‌وجو را به محدوده‌ای از کاراکترها محدود کنیم. به عنوان مثال وقتی می‌نویسیم: « م ]ا ـ د[ ر »، واژه‌هایی نظیر «مار» و «متر» در این جست‌وجو برگردانده می‌شود اما واژه‌ای مانند «مور» به دلیل نبودن «و» در محدوده‌ی «الف» تا «د»، برگردانده نخواهد شد.
• از کاراکتر ؟ زمانی استفاده می‌شود که تنها یک کاراکتر، بدون محدودیت باشد. به عنوان مثال، اگر بنویسیم «م؟ر»، واژه‌های سه حرفی که حرف اولشان «میم» و حرف آخرشان «ر» هست، برگردانده خواهد شد.
• از کاراکتر ~ زمانی استفاده می‌شود که بخواهیم کاراکتر بعد از آن، به عنوان یک نویسه‌ی معمولی در نظر گرفته بشود. کاراکترهایی مانند *، ؟، ـ و … نویسه‌های معنی‌دار هستند. اما زمانی که بخواهیم * واقعاً ستاره باشد، باید قبل از * از نماد ~ استفاده کنیم.

آموزش تصویری تابع countif در اکسل

در ادامه، می‌خواهیم نحوه‌ی کار با تابع countif و countifs در اکسل را با تصویر و به صورت گام‌به‌گام، آموزش دهیم.

گام اول: آماده‌سازی شیت اکسل

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

گام دوم: فراخوانی تابع countif

اکنون باید یاد داشته باشیم که چطور از تابع countif استفاده کنیم. برای فراخوانی تابع countif یا countifs در اکسل، راه‌های مختلفی وجود دارد. شما می‌توانید این کار را با نوشتن فرمول countif (یا فرمول countifs)، و وارد کردن آرگومان‌ها به صورت دستی انجام دهید. فرمول این توابع نیز بسیار شبیه با فرمول count در اکسل است. می‌نویسیم:

• =COUNTIF(Range,criteria)

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

هم‌چنین می‌توانید از روش دوم برای فراخوانی تابع countif  استفاده کنید. برای این منظور ابتدا روی سلولی که می‌خواهید نتیجه‌ی تابع countif روی آن نشان داده شود، کلیک کنید. سپس از مسیر فرمول، پنجره‌ی insert function را باز کنید. در این پنجره، لیستی از توابع آماری به شما نشان داده می‌شود. گزینه‌ی countif را پیدا کرده و سپس دکمه‌ی OK را بزنید )تابع COUNTIFS نیز در این لیست قرار دارد). در پنجره‌ی باز شده، می‌توانید آرگومان‌های تابع countif را به صورت مجزا ببینید.

گام سوم: تنظیم آرگومان‌های ورودی تابع countif در اکسل

گام نهایی برای آموزش countif در اکسل، تنظیم آرگومان‌های تابع است. این‌جا دیگر فرقی ندارد که تابع countif را از کدام مسیر فراخوانی کرده باشید. برای این منظور باید لیست سلول‌هایی که می‌خواهید در آن جست‌وجو انجام شود و البته شرط یا شروط مورد نظر برای جست‌وجو را تعیین کنید.

فرض می‌کنیم که می‌خواهیم جست‌وجو را در ستون نام خانوادگی دانش‌آموزان انجام دهیم.

بنابراین آرگومان اول به صورت B1:B۱۰ نوشته می‌شود. چراکه نام‌ خانوادگی دانش‌آموزان در ستون دوم یعنی ‌B نوشته شده است.

اگر بخواهیم تعداد سلول‌هایی نشان داده شوند که نام‌خانوادگی آن‌ها با حرف «س» شروع شود، می‌نویسیم:

• =COUNTIF(B1:B5, “س*”)

توجه داشته باشید که آرگومان اول، نشان می‌دهد که جست‌وجو در چه سلول‌هایی باید انجام شود. در این مثال چون هدف،‌ جست‌وجو براساس نام خانوادگی دانش‌آموزان بود، سلول‌های B1 تا B۱۰ انتخاب شدند. آرگومان دوم، نشان دهنده‌ی شرط جست‌وجو است. برای این مثال، ضروری است تا از Wild card استفاده کنیم. عبارت «س*» نشان می‌دهد که حرف اول رشته باید «سین» باشد و تعداد و بقیه‌ی حروف، اهمیتی ندارد. برای این حالت،‌ پاسخ تابع countif به صورت زیر خواهد بود:

چون این مثال،‌خیلی ساده است و تعداد سطرهای کاربرگ تنها ۱۰ سطر است، می‌توانیم خودمان هم پاسخ را ارزیابی کنیم. با مشاهده‌ی لیست نام خانوادگی دانش‌آموزان (ستون دوم)،‌ می‌بینیم که تنها دو دانش‌آموز «ستایش» و «سلحشور» هستند که نام خانوادگی آن‌ها براساس الگوی «س*» است.

پیشنهاد مطالعه: آموزش لینک کردن در اکسل

استفاده از تابع countifs در اکسل

همان‌طور که گفتیم، اگر شرط برای جست‌وجو در بین سلول‌های شیت اکسل، بیش‌تر از یک گزینه‌ باشد، می‌بایست از تابع countifs استفاده کنیم. این تابع به شما این امکان را می‌دهد تا تعداد شرط‌های بیش‌تری را در جست‌وجوی خود دخیل کنید. به مثال زیر، توجه کنید. فرض کنید کاربرگ اکسل قبلی را داریم.

می‌خواهیم تعداد دانش‌آموزانی را داشته باشیم که معدل آن‌ها بالای ۱۹ بوده و شهر محل تولدشان، شهر «بیرجند» باشد. در این مسأله، ما دو شرط داریم. پس ضروری است تا از تابع countifs استفاده کنیم. می‌نویسیم:

• =COUNTIFS(D1:D۱۰, >19, C1:C10, “بیرجند”)

می‌بینید که دو ستون مربوط به شهر تولد و معدل پایه‌ی قبل، رنگی شده‌اند. چرا که در تابع countifs به این ستون‌ها اشاره کرده‌ایم. خواهیم داشت:

ملاحظه می‌شود که تعداد دانش‌آموزانی که معدل بالای ۱۹ داشته باشند و شهر تولد آن‌ها بیرجند باشد،‌ تنها یک نفر (سارا احمدی) است.

مثال کاربردی از countif و countifs در اکسل

فرض می‌کنیم صاحب یک فروشگاه اینترنتی هستیم. اگر برای ثبت سفارش مشتریان، از نرم‌افزار اکسل استفاده کنیم، می‌توانیم کاربرگ را به‌صورت زیر طراحی کنیم.

دوره پیشنهادی : دوره آموزش مصورسازی داده‌ ها با اکسل

در این‌ کاربرگ، سطرها به سفارش‌های مشتریان اختصاص داده شده است. برای هر سطر، اطلاعاتی نظیر نام و نام خانوادگی مشتری، استان، شهر،‌ آدرس، کد پستی، تاریخ سفارش، کالای سفارش داده شده، وضعیت سفارش، کد رهگیری مرسوله را ثبت می‌کنیم. به عنوان مثال، تصور می‌کنیم که این اطلاعات برای ۵ مشتری، به صورت زیر باشد:

اکنون می‌خواهیم تعداد سفارشات را به تفکیک استان، وضعیت سفارش و تاریخ سفارش داشته باشیم.

اگر بخواهیم تعداد سفارشات استان خراسان رضوی، خراسان جنوبی و خراسان شمالی را محاسبه کنیم، می‌توانیم از تابع countif استفاده کنیم و بنویسیم:

• =COUNTIF(C۲:C5,”خراسان*”)

C۲:C5 به سلول‌های ستون سوم، یعنی استان سفارش اشاره می‌کند. چون می‌خواهیم سفارشاتی را پیدا کنیم که مربوط به استان خراسان شمالی، خراسان جنوبی یا خراسان رضوی هستند؛ کافی است تا آن‌هایی را جدا کنیم که با نام «خراسان» شروع می‌شوند. برای این منظور از Wild card استفاده می‌کنیم و می‌نویسیم: «خراسان*». داریم:

حاصل این تابع برابر با عدد ۲ خواهد بود.

اکنون فرض می‌کنیم که برای این سفارشات، به دنبال تعداد سفارش‌های در انتظار ارسال هستیم، پس باید از تابع countifs در اکسل استفاده کنیم.

به این صورت که می‌نویسیم:

• =COUNTIFS(C2:C5,”خراسان*”,I2:I5, “در انتظار ارسال”)

بخش اول این تابع، مشابه با تابع COUNTIF در بالا پر شده است. برای بخش دوم، I2:I5 آدرس سلول‌های مربوط به ستون نهم، یعنی وضعیت سفارش است. چون به دنبال سفارش‌های «در انتظار ارسال» هستیم، آن را به‌صورت “در انتظار ارسال” نوشته‌ایم. ممکن است شما از کدهای ۱، ۲ و … برای تعیین وضعیت سفارش استفاده کنید که در این صورت کد مورد نظر برای سفارشات در انتظار ارسال را درج می‌کنید. داریم:

بدین ترتیب، مدیریت سفارشات بسیار ساده‌تر خواهد شد.

پیشنهاد مطالعه: آموزش Data analysis در اکسل با کمک افزونه Analysis toolpak

کلام نهایی!

در این مطلب، شما را با تابع countif و countifs در اکسل آشنا کردیم. این توابع برای شمارش سلول‌هایی که از شرط یا شرایطی خاص پیروی می‌کنند، به کار می‌روند. این موضوع، کاربرد بسیار زیادی در بیش‌تر کاربرگ‌های اکسل دارد. به منظور تسهیل آموزش countif در اکسل، در این مطلب مثال‌های کاربردی و ساده‌ای از واقعیت زدیم. توجه داشته باشید که زمانی که شرط‌های جست‌وجو بیش‌تر از یک شرط باشد، باید از تابع countifs برای شمارش آیتم‌ها استفاده کنید. به عبارت دیگر تابع countif در اکسل، تنها یک شرط را به‌عنوان ورودی می‌پذیرد.

در این مطلب هم‌چنین با نکات مهم برای درج شرایط در آرگومان توابع countif و countifs آشنا شدید. در آرگومان شرط این توابع، می‌توانید از عملگرهای منطقی، wild cardها و یا نوشته یا رقم خاص، استفاده کنید. مثال‌های این مطلب، برای آموزش و یادگیری چگونگی استفاده از این موارد، کمک‌کننده است. شما می‌توانید موارد متعدد دیگر مانند ترکیب تابع countif و vlookup را نیز روی سلول‌های کاربرگ اکسل خود پیاده‌سازی نمایید. ترکیب توابع در اکسل، منجر به اجرای کوئری‌ها و جست‌وجوهای پیشرفته‌تر روی داده‌ها خواهد شد.

دوره پیشنهادی : آموزش اکسل از صفر

آموزش اکسل در مکتب‌خونه

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

آموزش‌ها در مکتب‌خونه ساده و با ذکر مثال‌های عملی همراه است. این‌طور مطالب بهتر در ذهن شما تثبیت خواهد شد. شما می‌توانید در پایان شرکت در دوره‌های آموزشی مکتب‌خونه، برای اخذ گواهی‌نامه‌ی این آکادمی نیز درخواست دهید. این گواهی‌نامه، سند معتبری برای ارتقای رزومه‌ی شما خواهد بود. این گواهی‌نامه، نشان می‌دهد که شما یک دوره‌ی کامل را در زمینه‌ی مهارت مربوطه در مکتب‌خانه، گذرانده‌اید.