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

تابع COUNTIF در اکسل این کار را ساده می‌کند. با این تابع می‌توانید تعداد سلول‌هایی را که یک شرط مشخص دارند بشمارید؛ برای مثال، تعداد دفعاتی که نام یک مشتری در فهرست فاکتورها ثبت شده است. در این مقاله، روش استفاده از COUNTIF و COUNTIFS را با مثال‌های کاربردی حسابداری یاد می‌گیرید.

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

تابع COUNTIF تعداد سلول‌هایی را می‌شمارد که با یک شرط مشخص مطابقت دارند. این تابع برای شمارش نام مشتریان، شماره فاکتورها، وضعیت پرداخت و سایر داده‌های متنی یا عددی کاربرد دارد.

ساختار تابع به شکل زیر است:

=COUNTIF(range, criteria)

معنی آرگومان‌ها:

• range: محدوده سلول‌هایی که می‌خواهید بررسی کنید.
• criteria: شرطی که سلول‌ها باید داشته باشند؛ مانند نام مشتری، عدد یا یک شرط مقایسه‌ای.

برای مثال، اگر نام مشتریان در سلول‌های A2 تا A100 ثبت شده باشد، می‌توانید تعداد دفعات ثبت نام «شرکت آریا» را با فرمول زیر بشمارید:

=COUNTIF(A2:A100,"شرکت آریا")

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

مثال اول: شمارش تعداد فاکتورهای هر مشتری

فرض کنید اطلاعات فاکتورهای فروش شما به شکل زیر است:

در این جدول، شماره فاکتور در ستون A، نام مشتری در ستون B و مبلغ فاکتور در ستون C قرار دارد.

برای شمارش تعداد فاکتورهای شرکت آریا، فرمول زیر را وارد کنید:

=COUNTIF(B2:B7,"شرکت آریا")

نتیجه: ۳ فاکتور

زیرا نام شرکت آریا در سه ردیف ثبت شده است.

برای شمارش فاکتورهای فروشگاه سپهر نیز می‌توانید بنویسید:

=COUNTIF(B2:B7,"فروشگاه سپهر")

نتیجه: ۲ فاکتور

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

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

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

برای مثال، نام مشتریان را در ستون E وارد کنید و در سلول F2 فرمول زیر را بنویسید:

=COUNTIF($B$2:$B$100,E2)

سپس فرمول را به ردیف‌های پایین کپی کنید.

در این فرمول:

• $B$2:$B$100 محدوده نام مشتریان است.
• E2 نام مشتری‌ای است که می‌خواهید تعداد فاکتورهایش را بشمارید.
• علامت $ باعث می‌شود هنگام کپی‌کردن فرمول، محدوده اصلی ثابت بماند.

نمونه گزارش خروجی:

این جدول می‌تواند بخشی از گزارش فروش ماهانه یا داشبورد مدیریتی شما باشد. اگر فاکتورهای جدید اضافه می‌کنید، محدوده فرمول را متناسب با داده‌ها گسترش دهید یا اطلاعات را به جدول استاندارد اکسل (Table) تبدیل کنید.

مثال سوم: شمارش فاکتورهای یک مشتری با استفاده از سلول انتخابی

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

فرض کنید نام مشتری در سلول E2 قرار دارد. فرمول زیر را در F2 وارد کنید:

=COUNTIF(B2:B100,E2)

حالا با تغییر نام مشتری در E2، تعداد فاکتورهای مشتری جدید نمایش داده می‌شود.

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

مثال چهارم: شمارش فاکتورهای پرداخت‌نشده

فرض کنید وضعیت پرداخت هر فاکتور در ستون D ثبت شده است و مقادیر آن شامل «پرداخت‌شده» و «پرداخت‌نشده» می‌شود.

برای شمارش فاکتورهای پرداخت‌نشده، از فرمول زیر استفاده کنید:

=COUNTIF(D2:D100,"پرداخت‌نشده")

این فرمول تعداد ردیف‌هایی را می‌شمارد که وضعیت آن‌ها دقیقاً با عبارت «پرداخت‌نشده» مطابقت دارد.

اگر بخواهید تعداد فاکتورهای پرداخت‌شده را محاسبه کنید:

=COUNTIF(D2:D100,"پرداخت‌شده")

توجه کنید که این فرمول تعداد فاکتورها یا ردیف‌های دارای وضعیت موردنظر را می‌شمارد، نه مجموع مبلغ آن‌ها. برای جمع مبلغ فاکتورهای پرداخت‌نشده می‌توانید از تابع SUMIF استفاده کنید.

مثال پنجم: شمارش فاکتورهای بزرگ‌تر از یک مبلغ مشخص

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

اگر مبلغ فاکتورها در ستون C قرار دارد، برای شمارش فاکتورهای بیشتر از ۱۰,۰۰۰,۰۰۰ بنویسید:

=COUNTIF(C2:C100,">10000000")

برای شمارش فاکتورهای مساوی یا بیشتر از ۱۰,۰۰۰,۰۰۰ نیز از فرمول زیر استفاده کنید:

=COUNTIF(C2:C100,">=10000000")

اگر مبلغ موردنظر در سلول E2 نوشته شده باشد، می‌توانید شرط را به آن سلول متصل کنید:

=COUNTIF(C2:C100,">"&E2)

با تغییر مبلغ در E2، تعداد فاکتورهای بالاتر از آن نیز به‌صورت خودکار تغییر می‌کند.

تابع COUNTIFS چیست و چه تفاوتی با COUNTIF دارد؟

تابع COUNTIFS برای شمارش سلول‌ها یا ردیف‌هایی استفاده می‌شود که چند شرط را به‌صورت هم‌زمان دارند. برای مثال، ممکن است بخواهید تعداد فاکتورهای شرکت آریا را فقط در ماه مهر بشمارید.

ساختار تابع به این صورت است:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

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

مثال ششم: شمارش فاکتورهای یک مشتری در یک ماه مشخص

فرض کنید:

• ستون B: نام مشتری
• ستون C: ماه صدور فاکتور

برای شمارش فاکتورهای شرکت آریا در ماه مهر بنویسید:

=COUNTIFS(B2:B100,"شرکت آریا",C2:C100,"مهر")

این فرمول فقط ردیف‌هایی را می‌شمارد که نام مشتری آن‌ها «شرکت آریا» و ماه آن‌ها «مهر» باشد.

اگر نام مشتری در E2 و ماه در F2 قرار دارد، فرمول را به این شکل بنویسید:

=COUNTIFS(B2:B100,E2,C2:C100,F2)

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

مثال هفتم: شمارش فاکتورهای یک مشتری با وضعیت پرداخت مشخص

فرض کنید نام مشتری در ستون B و وضعیت پرداخت در ستون D قرار دارد. برای شمارش فاکتورهای پرداخت‌نشده شرکت آریا بنویسید:

=COUNTIFS(B2:B100,"شرکت آریا",D2:D100,"پرداخت‌نشده")

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

تفاوت COUNTIF، COUNTIFS و COUNTA

برای مثال، اگر شماره فاکتورها در محدوده A2:A100 باشند، فرمول زیر تعداد سلول‌های غیرخالی را محاسبه می‌کند:

=COUNTA(A2:A100)

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

خطاهای رایج در شمارش فاکتورها با COUNTIF

چرا COUNTIF عدد صفر نمایش می‌دهد؟

دلایل احتمالی عبارت‌اند از:

• نام مشتری در شرط با متن ثبت‌شده در جدول مطابقت ندارد.
• در نام‌ها فاصله اضافی وجود دارد.
• محدوده اشتباهی انتخاب شده است.
• داده‌ها در ستون دیگری قرار دارند.

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

چرا تعداد فاکتورها بیشتر از مقدار واقعی است؟

ممکن است یک شماره فاکتور چند بار در جدول ثبت شده باشد یا یک فاکتور در چند ردیف جزئیات داشته باشد. COUNTIF تعداد ردیف‌های مطابق شرط را می‌شمارد، نه تعداد شناسه‌های یکتا را.

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

آیا COUNTIF به بزرگی و کوچکی حروف حساس است؟

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

چرا با اضافه‌شدن فاکتور جدید، تعداد به‌روز نمی‌شود؟

اگر فرمول محدوده‌ای مانند B2:B100 را بررسی کند، داده‌های بعد از ردیف ۱۰۰ را نمی‌شمارد. محدوده را گسترش دهید یا اطلاعات را به Table تبدیل کنید تا فرمول‌ها بهتر با اضافه‌شدن داده‌ها سازگار شوند.

نکات مهم برای گزارش‌گیری حرفه‌ای

برای اینکه گزارش شمارش فاکتورها دقیق‌تر و نگهداری آن آسان‌تر باشد، این نکات را رعایت کنید:

1. نام مشتریان را یکدست ثبت کنید. برای یک مشتری از چند شکل نوشتاری مختلف استفاده نکنید.
2. برای هر فاکتور شناسه مشخص داشته باشید. شماره فاکتور باید طبق رویه ثبت اطلاعات شما قابل تشخیص و پیگیری باشد.
3. محدوده‌ها را کنترل کنید. مطمئن شوید همه فاکتورهای موردنیاز داخل محدوده فرمول قرار دارند.
4. برای گزارش‌های چندشرطی از COUNTIFS استفاده کنید. این تابع امکان ترکیب شرط‌هایی مانند مشتری، ماه و وضعیت پرداخت را فراهم می‌کند.
5. نتیجه را با نمونه‌ای از داده‌ها تطبیق دهید. برای گزارش‌های مهم مالی، چند ردیف را دستی بررسی کنید تا مطمئن شوید منطق شمارش با ساختار فایل شما هماهنگ است.

پرسش‌های متداول

چگونه تعداد فاکتورهای یک مشتری را در اکسل بشمارم؟

اگر نام مشتری در ستون B ثبت شده است، از فرمول =COUNTIF(B2:B100,"شرکت آریا") استفاده کنید و نام و محدوده را با داده‌های فایل خود تطبیق دهید.

چطور تعداد فاکتورهای هر مشتری را در یک جدول نمایش دهم؟

نام مشتریان را در یک ستون خلاصه قرار دهید و در ستون کناری از فرمولی مانند =COUNTIF($B$2:$B$100,E2) استفاده کنید؛ سپس فرمول را برای سایر مشتریان کپی کنید.

چگونه تعداد فاکتورهای یک مشتری در یک ماه را بشمارم؟

اگر نام مشتری در ستون B و ماه در ستون C باشد، از COUNTIFS استفاده کنید؛ برای نمونه: =COUNTIFS(B2:B100,"شرکت آریا",C2:C100,"مهر").

آیا COUNTIF مبلغ فاکتورها را هم جمع می‌کند؟

خیر. COUNTIF تعداد سلول‌های مطابق شرط را می‌شمارد. برای جمع مبلغ‌ها بر اساس شرط، از SUMIF و برای جمع بر اساس چند شرط، از SUMIFS استفاده کنید.

آیا COUNTIF تعداد فاکتورهای یکتا را می‌شمارد؟

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

جمع‌بندی

تابع COUNTIF راهی ساده برای شمارش فاکتورهای هر مشتری، تعداد فاکتورهای پرداخت‌شده یا پرداخت‌نشده و تعداد مبالغی است که شرط مشخصی دارند. اگر لازم باشد چند شرط هم‌زمان بررسی شود، COUNTIFS انتخاب مناسب‌تری است.

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