فهرست مطالب:

• تابع COUNTIF گوگل شیتس چیست؟
• ساختار تابع COUNTIF گوگل شیتس
• تابع COUNTIF در عمل
  • شمارش سلول ها با متن و اعداد (exact match)
  • تابع COUNTIF گوگل شیتس و کاراکترهای ویلدکارد (wild card) تطبیق جزئی (partial match)
  • تابع COUNTIF برای کوچکتر از، بزرگتر از، مساوی با
  • xn 4  تابع COUNTIF با چند شرط – COUNTIFS
  • شمارش با چند شرط – منطق AND
  • شمارش با چند شرط – منطق OR
  • شمارش سلول های خالی و غیرخالی

اگر با فرمول نویسی آشنا نیستی حتما سری به دو مطلب زیر بزن خالی از لطف نیست : )

فرمول نویسی در گوگل شیتس (قسمت اول)

ایجاد و ویرایش فرمول ها (قسمت دوم فرمول نویسی گوگل شیتس)

تابع COUNTIF گوگل شیتس چیست؟

این تابع به ما کمک میکند که تعداد دفعاتی که یک مقدار خاص (یک مقدار با شرط خاص) در یک محدوده داده ظاهر میشود را بشماریم.

ساختار تابع COUNTIF گوگل شیتس

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

=COUNTIF(range;criterion)

range:  محدوده ای که میخواهیم یک مقدار خاص را در آن بشماریم. (اجباری)

criterion یا شرط جستجو : مقداری برای یافتن و شمارش در محدوده داده های نشان داده شده در آگومان اول. (اجباری)

تابع COUNTIF در عمل

شاید فکر کنید این تابع آنقدر ساده است که حتی تابع هم حساب نمیشود. اما در ادامه با ما همراه باش تا شگفت زده بشی.

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

وقتش رسیده که باهم یک سری فرمول بسازیم.

شمارش سلول ها با متن و اعداد (exact match)

بیایید فرض کنیم که شرکت شما انواع مختلفی از شکلات را به چندین مشتری در مناطق مختلف میفروشد و تعداد زیادی کارمند در آنجا کار میکنند.

فروش شما شبیه به تصویر زیر است:

بیا با یک مثال مقدماتی شروع کنیم.

ما نیاز داریم که میزان  “milk chocolate” فروخته شده را بشماریم. توی سلول مورد نظر کلیک کن و علامت مساوی (=) رو تایپ کن. گوگل شیتس متوجه میشود که شما میخواهید یک فرمول بنویسید. به محض اینکه حرف C را تایپ کنید توابعی که با C شروع میشوند برای شما نمایش داده میشود. تابع countif را انتخاب کنید.

اولین آرگومان را D6:D16 تعریف کرده ایم. لازم نیست این محدوده رو تایپ کنی. کافیه که با موس این محدوده رو انتخاب کنی. سپس جدا کننده رو تایپ کن و حالا میریم سراغ آرگومان دوم.

دومین آرگومان مقداری هست که ما میخواهیم توی محدوده دنبالش بگردیم. اسم شکلات یک متن هست پس اون رو داخل دابل کوتیشن قرار میدیم. “milk chocolate” . (این یک قرار داده که متن رو توی فرمول های گوگل شیتس داخل دابل کوتیشن قرار بدیم.)

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

=countif(D6:D16;”Milk Chocolate”

نیجه 3 خواهد بود. سه فروش از این شکلات داشته ایم.

نکته: توجه داشته باشید که تابع COUNTIF با یک سلول یا ستون های کنار هم کار میکند. به عبارت دیگر نمیتوانید چند سلول یا ستون و ردیف جداگانه در این فرمول استفاده کنید. به مثال زیر برای درک بیشتر این موضوع توجه کنید.

فرمول های غلط:

=COUNTIF(C6:C16; D6:D16;"Milk Chocolate")

=COUNTIF(D6; D8;D10;D12;D14;"Milk Chocolate")

فرمول های درست:

=COUNTIF(C6:D16;"Milk Chocolate")

=COUNTIF(D6;"Milk Chocolate") + COUNTIF(D8;"Milk Chocolate") + COUNTIF(D10;"Milk Chocolate") + COUNTIF(D12;"Milk Chocolate") + COUNTIF(D14;"Milk Chocolate")

شاید متوجه شده باشید که نوشتن چندباره شرط (ارگومان دوم) در فرمول ها واقعا کار راحتی نیست. بهتر است به جای نوشتن مستقیم آن در فرمول، از سلول مرجع استفاده کنید. بریم با سلول مرجع فرمول رو بنویسیم؟؟

میخواهیم فروش های West را بشماریم. برای این منظور از سلول مرجع در تابع COUNTIF استفاده میکنیم. به این ترتیب فرمول به شکل زیر خواهد بود:

=COUNTIF(C6:C16;A3)

A3 آدرس سلولی است که مقدار متنی West در آن قرار گرفته است. همانطور که میبینید ویرایش کردن آن در فرمول بسیار است.

البته که میتونیم در مورد اعداد هم این مورد صدق میکنه. یعنی هم میتونیم مستقیم خود عدد رو به عنوان شرط قرار بدیم یا اینکه میتونیم آدرس سلول رو قرار بدیم.

=COUNTIF(E7:E17;125)

=COUNTIF(E7:E17;A3)

تابع COUNTIF گوگل شیتس و کاراکترهای ویلدکارد (wild card) تطبیق جزئی (partial match)

یکی از ویژگی های جالب این تابع این است که میتواند تمام سلول یا قسمت هایی از محتوای سلول را بشمارد. برای این منظور باید از کاراکترهای “؟” ، “*” استفاده کنیم.

علامت ? و تابع countif

به عنوان مثال برای شمارش فروش در برخی از مناطق خاص میتوانیم از بخشی از نام آنها استفاده کنیم: مثلا “?est” را در سلول B3 وارد کنید. در اینجا علامت سؤال جایگزین یک کاراکتر میشود. به این ترتیب ما به دنبال کلمات چهار حرفی هستیم که با est پایان می یابند.

فرمول زیر را در سلول B3 بنویسید:

=countif(C7:C17;A3)

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

=countif(C7:C17;”?est)

به این ترتیب نتیجه فرمول 5 خواهد بود. به معنای این که 5 منطقه وجود دارد که با est تمام میشوند.

خب حالا بیاین یک فرمول دیگه توی سلول B4 وارد کنیم.

=countif(C7:C17;A4)

چه اتفاقی میفته وقتی در قسمت شرط به  جای یک علامت سؤال از دو علامت سؤال استفاده کنیم؟ “?st” . این به این معناست که این بار ما به دنبال کلمات 4 حرفی هستیم که با “st” تمام شوند. با توجه به اینکه  هر دو منطقه “west” و “east” با st تمام میشوند. اینبار فرمول 9 منطقه برای فروش را برمیگرداند.

به تصویر زیر دقت کنید.

علامت *  و تابع countif

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

“*chocolate” این شرط تمام محصولاتی که با “chocolate” تمام میشوند را میشمارد.

“chocolate*” این شرط تمام محصولاتی که با “chocolate” شروع میشوند را میشمارد.

حتما حدس زدید که اگر “*chocolate*” را وارد کنیم، فرمول تمام محصولاتی که شامل “chocolate” باشند را میشمارد.

نکته: اگر میخواهید کلماتی را بشمارید که شامل علامت ستاره (*) و علامت سؤال (?) هستند، قبل از آنها علامت tilde را که به صورت (~) است؛ بنویسید. به عنوان مثال، اگر بخواهیم مقادیری که شامل “?” هستند را پیدا کنید، فرمول زیر را بنویسید:

=countif(D7:D15;”*~?*”)

تابع COUNTIF گوگل شیتس برای کوچکتر از، بزرگتر از، مساوی با

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

برای این منظور ما از عملگرهای ریاضی مربوطه استفاده میکنیم: “=”، “>”، “<“، “>=”، “<=” و “<>”.

در جدول زیر هر کدام از این موارد را به همراه مثال توضیح داده ایم:

=countif(F9:F19,”>100”)

=countif(F9:F19,”<100”)

=countif(F9:F19,”=100”)

=countif(F9:F19,”<>100)

=countif(F9:F19,”>=100”)

=countif(F9:F19,”<=100”)

نکته: حتما عدد و عملگر ریاضی را در داخل دابل کوتیشن قرار دهید.

در صورتی که در قسمت شرط از سلول مرجع استفاده کرده اید، برای تغییر فرمول کافی است محتوای سلول مرجع را تغییر دهید.

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

=countif(F9:F19,A3)

ایجاد شرطهای پیچیده تر با علامت &

به عنوان مثال، B4 حاوی فرمولی است که تعداد مقادیر بزرگتر یا مساوی 100 را در محدوده E9:E19 می شمارد:

=COUNTIF(E9:E19;”>=”&A4)

B5 نیز شرطی شبیه قبلی دارد؛ اما ما هم برای عدد و هم برای عملگر از سلول مرجع استفاده کرده ایم:

=COUNTIF(E9:D19,A6&A5)

تابع COUNTIF گوگل شیتس با چند شرط – COUNTIFS

بعضی اوقات لازم است تعداد مقادیری که حداقل یکی از شرطها برایشان برقرار است (منطق OR) یا چندین شرط برایشان برقرار است (منطق AND) را شمارش کنید. برای این کار  دو روش وجود دارد:

• همزمان از چند تابع COUNTIF در یک سلول استفاده کنید.
• از تابع COUNTIFS که جایگزین روش قبل است استفاده کنید.

شمارش با چند شرط – منطق AND

پیشنهاد من این هست که از تابع COUNTIFS استفاده کنید:

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

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

مثال: بیایید تعداد فروش بین 200 تا 400 رو حساب کنیم:

=COUNTIFS(F8:F18,">=200",F8:F18,"<=400")

شمارش با چند شرط – منطق OR

وقتی کافی هست که حداقل یک شرط در مورد عدد برقرار باشد از چندین تابع COUNTIF استفاده کن.

مثال 1: COUNTIF+COUNTIF

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

=COUNTIF(D7:D17,"*Milk*") + COUNTIF(D7:D17,"*Dark*")

من از ستاره استفاده میکنم.  که مطمئن باشم کلمات Milk و Dark در سلول باشند. (فرقی نمیکند ابتدا، انتها، یا وسط سلول.)

مثال 2: countif-countif

حالا میخواهم تعداد فروش کل بین 200  و 400 را شمارش کنم.

ابتدا تعداد کل فروش های زیر 400 رو حساب میکنم، سپس تعداد فروش های زیر 200 رو حساب میکنم و در نهایت از فرمول زیر استفاده میکنم:

=COUNTIF(F7:F17,"<=400") - COUNTIF(F7:F17,"<=200")

فرمول تعداد فروش های بیشتر از 200 و کمتر از 400 رو برمیگرداند.

شما میتوانید از سلول های مرجع A3 و A4 برای کوتاه شدن فرمول خود استفاده کنید، نتیجه تغییری نخواهد کرد:

=COUNTIF(F7:F17, A4) - COUNTIF(F7:F17, A3)

شمارش سلول های خالی و غیرخالی

با کمک countif میتوان تعداد سلول های خالی و غیر خالی را نیز در برخی محدوده ها شمارش کرد. فرض کنید ما در مقابل محصولاتی که با موفقیت فروخته ایم Paid، اگر مشتری کالا را رد کرده باشد عدد صفر و اگر معامله بسته نشده باشد، سلول را خالی میگذاریم.

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

=COUNTIF(F7:F15,"<>")

یا

=COUNTIF(F7:F15,A3)

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

=COUNTIF(F7:F15,"")

یا

=COUNTIF(F7:F15,A4)

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

=COUNTIF(F7:F15,"*")

یا

=COUNTIF(F7:F15,A5)

به تصویر زیر، فرمول ها و نتایج دقت کنید:

با توجه به نتایج، 4 معامله بسته شده، 3 معامله پرداخت شده، 5 معامله بسته نشده است.