تکنیک های کار با تابع Countif
تابع countif یکی از توابع محاسباتی در محیط برنامه اکسل است که برای شمارش تعداد سلول‌هایی که شرایط مشخصی را برآورده می‌کنند، استفاده می‌شود.
فرمول استفاده شده برای این تابع به صورت زیر است:
فرمول تابع countif
=COUNTIF(range, criteria)
در این فرمول از آموزش اکسل، range محدوده‌ای از سلول‌ها است که می‌خواهیم تعدادی از آن‌ها را شمارش کنیم و criteria شرطی است که می‌خواهیم روی آن‌ها تست کنیم. اگر شرط درست باشد، آن سلول شمرده خواهد شد.
ویژگی های تابع countif
1- شمارش تعداد مقادیر در یک محدوده‌ی مشخص
2- امکان شمارش بر اساس مقدار مورد نظر (شمارش مقادیر مساوی، بزرگتر یا کوچکتر از مقدار مورد نظر و …)
3-امکان استفاده از عملگرهای مختلف برای تعیین شرط (مثلا استفاده از عملگر “=” برای شمارش سلول‌هایی که مقدار آن‌ها برابر با مقدار مورد نظر است و استفاده از عملگر “<” برای شمارش سلول‌هایی که مقدار آن‌ها کوچکتر از مقدار مورد نظر است)
4- قابلیت استفاده از چندین شرط با استفاده از تابع COUNTIFS
به کمک این تابع می‌توان به سادگی تعداد موارد مورد نظر در یک محدوده‌ی مشخص را شمرده و از آن در محاسبات و تحلیل داده‌ها استفاده کرد. همچنین، به کمک شرایط مختلف قابل تنظیم در این تابع، می‌توان به صورت دقیق تر موارد مورد نظر را شمرده و تحلیل دقیق‌تری از داده‌ها داشت.
مثال های تابع countif
مثال اول : اگر بخواهیم تعداد سلول‌هایی را که مقدارشان بزرگتر از 10 است، در محدوده A1:A10 را بشماریم، فرمول زیر را در یک سلول جدید قرار می‌دهیم:
COUNTIF(A1:A10, “>10”)
در اینجا، range برابر با A1:A10 و criteria برابر با “>10” است. به این معنی که می‌خواهیم تعداد سلول‌هایی که مقدارشان بزرگتر از 10 است را در این محدوده شمرده کنیم.
مثال دوم: چند راه برای شمارش سلولهای حاوی متن، سلولهای خالی و سلولهای غیر خالی را با استفاده یکی از فرمول و توابع اکسلی به نام تابع Countif داریم.
شمارش تمام سلول های غیر خالی در تابع Countif
شمارش سلول های حاوی متن در تابع Countif
شمارش سلولهای خالی در تابع Countif
مثال سوم :
برای محدوده (range) می‌توانید یک محدوده سلول‌ها یا یک آرایه باشد. مثلاً می‌توانید محدوده A1:A10 یا B1:C5 را به عنوان محدوده ورودی در نظر بگیرید.
برای شرط (criteria) می‌توانید از اپراتور‌های مقایسه‌ای مختلفی مانند >، >=، <، <=، =، <> استفاده کنید. همچنین می‌توانید از عبارت‌های مقایسه‌ای مانند “بزرگتر از”، “کوچکتر مساوی با”، “نامساوی با” و … نیز استفاده کنید.
در صورتی که محدوده‌ی انتخاب شده شامل مقادیر متنی باشد، شرط مورد نظر باید بین علامت دو بار کوتاه (”) قرار گیرد. برای مثال، اگر می‌خواهید تعداد سلول‌هایی که شامل عبارت “خانه” هستند را بشمارید، می‌توانید از فرمول زیر استفاده کنید:
(A1:A10, “‘*خانه*'”)COUNTIF
در این مثال، بین دو علامت تک کوتاه (”)، عبارت “خانه” قرار گرفته است. همچنین، با استفاده از علامت ‘*’ قبل و بعد از عبارت “خانه”، می‌توانید بازه‌ی حاوی هر کلمه‌ای که شامل عبارت “خانه” باشد را شامل شود.
مثال چهارم :
اگر محدوده‌ی انتخاب شده شامل محدوده‌هایی باشد که شامل مقادیر خالی (مثلاً سلولهای خالی یا با فرمول‌هایی که به مقدار خالی تبدیل می‌شوند)، در شرط مورد نظر باید از علامت “=” استفاده کنید. برای مثال، اگر می‌خواهید تعداد سلولهای خالی را در محدوده A1:A10 بشمارید، می‌توانید از فرمول زیر استفاده کنید:
COUNTIF(A1:A10, “=”)
در این فرمول، شرط برابر با “=” است که به معنی “مقدار سلول برابر با خالی است” است.
مثال پنجم :
شما می‌توانید از چندین شرط در یک فرمول COUNTIF استفاده کنید. برای این کار می‌توانید از عملگرهای منطقی مانند “AND” و “OR” استفاده کنید. برای مثال، اگر می‌خواهید تعداد سلول‌هایی را که بزرگتر از 10 و کوچکتر از 20 هستند، در محدوده A1:A10 بشمارید، می‌توانید از فرمول زیر استفاده کنید:
COUNTIF(A1:A10, “>10”) – COUNTIF(A1:A10, “>20”) + COUNTIF(A1:A10, “=10”) + COUNTIF(A1:A10, “=20”)
در اینجا، از چندین تابع COUNTIF برای شمارش سلول‌هایی استفاده شده است که شرایط مختلفی را برآورده می‌کنند و سپس با استفاده از عملگرهای منطقی “AND” و “OR”، تعداد سلولهای مورد نظر شمرده می‌شود.
مثال ششم :
فرمول COUNTIF می‌تواند در محاسبات شما مفید باشد. به عنوان مثال، اگر می‌خواهید میانگین اعداد موجود در ستون A را محاسبه کنید، می‌توانید از فرمول زیر استفاده کنید:
SUM(A1:A10)/COUNTIF(A1:A10, “<>”””””)
در اینجا، ابتدا مجموع اعداد ستون A با استفاده از تابع SUM محاسبه می‌شود، سپس تعداد سلول‌هایی که خالی نیستند با استفاده از تابع COUNTIF شمرده می‌شوند. در نهایت، مجموع اعداد ستون A تقسیم بر تعداد سلول‌هایی که دارای مقدار نیستند (یعنی سلولهای خالی نیستند)، میانگین محاسبه شده است.
مثال هفتم :
تابع COUNTIF به صورت خودکار مقادیر متنی را به مقدار صفر تبدیل می‌کند. بنابراین، اگر می‌خواهید تعداد سلول‌هایی را که مقدار آن‌ها برابر با یک متن خاص (مثلاً “خیر”) است را شمرده و در نتیجه آن‌ها را از تعداد کل سلول‌ها کم کنید، می‌توانید از فرمول زیر استفاده کنید:
در این فرمول، تابع COUNTBLANK برای شمردن تعداد سلولهای خالی استفاده شده است. همچنین، از تابع COUNTIF برای شمردن تعداد سلول‌هایی استفاده شده است که مقدار آن‌ها برابر با “خیر” است، و تعداد سلول‌هایی که دارای مقدار نیستند (یعنی سلولهای خالی نیستند)، و تعداد سلول‌هایی که مقدار آن‌ها بیشتر از صفر است. در نهایت، با از کم کردن تعداد سلول‌هایی که مقدار آن‌ها با فرمول گفته شده، تعداد سلول‌هایی که مقدار آن‌ها برابر با “خیر” است، به همراه سلولهای خالی شمرده می‌شود و سپس از تعداد کل سلولهای موجود در محدوده A1:A10 کم می‌شود.
=COUNTIF(A1:A10, "خیر") + COUNTBLANK(A1:A10) - COUNTIF(A1:A10, "<>""""") - COUNTIF(A1:A10, ">0")
مثال هشتم :
برای اعمال چند شرط به فرمول COUNTIF، می‌توانید از فرمول SUMPRODUCT استفاده کنید. به عنوان مثال، اگر می‌خواهید تعداد سلول‌هایی را که مقدار آن‌ها برابر با یک متن خاص (مثلاً “خیر”) و در همان زمان مقدار ستون B آن‌ها بیشتر از 10 است را شمرده و در نهایت آن‌ها را با تعداد کل سلول‌ها که دارای مقدار هستند، تقسیم کنید، می‌توانید از فرمول زیر استفاده کنید:
در این فرمول، تابع SUMPRODUCT برای ضرب عناصر دو محدوده استفاده شده است. در اینجا، اولین محدوده برای ستون A (مقادیر متنی) و دومین محدوده برای ستون B (مقادیر عددی) استفاده شده است. هر عنصر این دو محدوده به طور جداگانه با یکدیگر ضرب می‌شوند و سپس تمامی عناصر به هم جمع می‌شوند. در نهایت، نتیجه به تعداد کل سلول‌هایی که دارای مقدار هستند تقسیم می‌شود تا درصد مورد نظر محاسبه شود.
نکته مهم در استفاده از فرمول COUNTIF، تفاوت بین استفاده از علامت “=” و “<>” در آن است. علامت “=” برای مقایسه مقادیر دقیق استفاده می‌شود، در حالی که علامت “<>” برای مقایسه مقادیر غیر دقیق (مثلاً مقادیری که با یک متن خاص شروع نمی‌شوند) استفاده می‌شود.
=SUMPRODUCT((A1:A10="خیر")*(B1:B10>10))/COUNTA(A1:A10)
خطا های تابع countif
در صورت استفاده نادرست از تابع COUNTIF، ممکن است با خطاهایی روبرو شوید.
برخی از خطاهای متداول این تابع عبارتند از:
#VALUE!: این خطا بیانگر این است که اگر شما مقدار غیرمجازی را به عنوان شرط در تابع COUNTIF استفاده کنید، این خطا به شما نمایش داده می‌شود. برای رفع این خطا، می‌توانید شرط خود را بررسی کنید و مطمئن شوید که مقدار شرط درست است.
#NAME?: این خطا به شما نشان می‌دهد که اسم تابع را به درستی ننوشته‌اید. در صورتی که این خطا به شما نشان داده می‌شود، باید اسم تابع را چک کرده و اگر اشتباه است، درست کنید.
#REF!: این خطا بیانگر این است که محدوده‌ای که در تابع COUNTIF استفاده کرده‌اید، نامعتبر است. به این معنا که سلول‌هایی که در محدوده تعیین شده قرار ندارند یا حذف شده‌اند. در این صورت، می‌توانید محدوده را دوباره بررسی کنید و مطمئن شوید که محدوده صحیح است.
#DIV/0!: این خطا به شما نشان می‌دهد که تابع COUNTIF برای شمارش تعداد مورد نظر، با مشکل روبرو شده است. این مشکل معمولا به دلیل تقسیم بر صفر بودن تعداد مورد نظر اتفاق می‌افتد. در صورتی که این خطا به شما نمایش داده شده‌است، می‌توانید شرط خود را بررسی کنید و مطمئن شوید که درست است
2 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام
من یه لیست نام وارد کردم و وقتی از اون لیست انتخاب میکنم جلوش تعدادش رو هم وارد میکنم
حالا میخوام پایین لیست که چند تا از این ردیف ها هست یه شمارش گر بندازم که ببنیم مثلا انبه چندتا تو لیستم هست . ولی مشکل اینجاست که چون تعداد جلوش اومده با این تابع countif فقط تعداد کلمه های انبه رو میشماره و دیگه ضرب در تعداد رو نمیدونم چجوری توش وارد کنم تا ضربدر تعدادی که جلوش هست بکنه بعد تعداد رو به من بگه. ممنون میشم تو این امر بهم کمک کنید