پث‌وایز

آموزش اکسل و گوگل شیتدرس ۵ از ۱۲

تابع IF، COUNTIF و SUMIF در اکسل: فرمول‌های شرطی

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

چیزی که از این درس می‌بری

  • فرمول =IF(test, if true, if false) بین دو تا جواب انتخاب می‌کنه؛ حواست به فرق >= و > باشه.
  • تابع COUNTIF ردیف‌هایی رو که شرط دارن می‌شماره؛ تابع SUMIF ستون اون ردیف‌ها رو جمع می‌زنه.
  • شرط‌ها داخل گیومه میان: مثل "Food"، ">1000" و "<>Paid".
فهرست این درس۴
  1. اگه این شد اون، وگرنه اون‌یکی
  2. فقط ردیف‌هایی رو بشمار که شرط رو دارن
  3. فقط ردیف‌هایی رو جمع بزن که شرط رو دارن
  4. مرور درس

۰۱تابع IF

اگه این شد اون، وگرنه اون‌یکی

ساختارش اینه: =IF(test, answer if true, answer if false): اول شرط، بعد جواب اگه درست بود، و آخرش جواب اگه غلط بود. شرط یه مقایسه‌ست: = (مساوی)، <> (نامساوی)، >، <، >=، <=. جواب‌های متنی هم داخل گیومه میان.

فرمول =IF(B2>=50, "Pass", "Fail") برای ۵۰ به بالا Pass و برای کمتر از اون Fail نشون می‌ده.

مثال

B2 = 72   B3 = 41   B4 = 50

=IF(B2>=50, "Pass", "Fail")
=IF(B3>=50, "Pass", "Fail")
=IF(B4>=50, "Pass", "Fail")
=IF(B4>50,  "Pass", "Fail")

خروجی

Pass
Fail
Pass
Fail

خط آخر نشون می‌ده چرا فرق >= و > مهمه: دقیقاً ۵۰ با >= قبول می‌شه ولی با > رد می‌شه.

خودت رو امتحان کنسؤال ۱ از ۴

ارسال برای سفارش‌های ۵۰۰،۰۰۰ تومان به بالا رایگانه، وگرنه ۶۰،۰۰۰ تومانه. جمع سفارش توی C2 هست. کدوم فرمول هزینه‌ی ارسال رو نشون می‌ده؟

  1. =IF(C2>500000, 0, 60000)
  2. =IF(C2>=500000, 0, 60000)
  3. =IF(C2>=500000, 60000, 0)
  4. =IF(C2>=500000, "free", 60000)
جواب رو ببین

=IF(C2>=500000, 0, 60000)

درسته. «۵۰۰،۰۰۰ به بالا» یعنی >=، و نوشتن عدد 0 ستون رو عددی نگه می‌داره تا بعداً بشه جمعش زد.

۰۲تابع COUNTIF

فقط ردیف‌هایی رو بشمار که شرط رو دارن

فرمول =COUNTIF(range, condition) سلول‌هایی رو می‌شماره که شرط رو دارن. شرط متنیه و داخل گیومه میاد: مثل "Food"، ">1000" یا "<>Paid".

فرمول =COUNTIF(C2:C200, "Food") می‌شماره چند تا از هزینه‌ها توی دسته‌ی Food هستن.

۰۳تابع SUMIF

فقط ردیف‌هایی رو جمع بزن که شرط رو دارن

فرمول =SUMIF(range to check, condition, range to add) اول محدوده‌ی اول رو چک می‌کنه و برای هر ردیفی که با شرط بخونه، عدد همون ردیف از محدوده‌ی سوم رو جمع می‌زنه.

فرمول =SUMIF(C2:C200, "Food", D2:D200) مبالغ ستون D رو فقط برای ردیف‌هایی جمع می‌زنه که دسته‌بندیشون توی ستون C کلمه‌ی Food باشه.

خودت رو امتحان کنسؤال ۲ از ۴

هر سؤال رو به فرمول مناسبش وصل کن

  • چند تا سفارش کنسل شدن؟
  • جمع کل هزینه‌ی حمل‌ونقل چقدر شد؟
  • این فاکتور درشته؟
  • چند تا فروش بالای یه میلیون داشتیم؟

=COUNTIF(D2:D99, ">1000000")=COUNTIF(E2:E99, "Cancelled")=IF(D2>5000000, "Large", "Normal")=SUMIF(C2:C99, "Transport", D2:D99)

جواب رو ببین
  • چند تا سفارش کنسل شدن؟ → =COUNTIF(E2:E99, "Cancelled")
  • جمع کل هزینه‌ی حمل‌ونقل چقدر شد؟ → =SUMIF(C2:C99, "Transport", D2:D99)
  • این فاکتور درشته؟ → =IF(D2>5000000, "Large", "Normal")
  • چند تا فروش بالای یه میلیون داشتیم؟ → =COUNTIF(D2:D99, ">1000000")

خودت رو امتحان کنسؤال ۳ از ۴

فرمول =SUMIF(C2:C50, "Food", D2:D50) ردیف‌هایی رو که دسته‌بندیشون با یه فاصله‌ی اضافه در آخر مثل "food " تایپ شده هم جمع می‌زنه.

  1. درست
  2. نادرست
جواب رو ببین

نادرست

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

خودت رو امتحان کنسؤال ۴ از ۴

حمید جمع کل فاکتورهایی رو می‌خواد که وضعیتشون Paid (پرداخت‌شده) نیست. وضعیت توی ستون E و مبلغ‌ها توی ستون D هستن. کدوم فرمول درسته؟

  1. =SUMIF(E2:E80, "Paid", D2:D80)
  2. =COUNTIF(E2:E80, "<>Paid")
  3. =SUMIF(E2:E80, "<>Paid", D2:D80)
  4. =SUMIF(D2:D80, "<>Paid", E2:E80)
جواب رو ببین

=SUMIF(E2:E80, "<>Paid", D2:D80)

درسته. علامت <> یعنی «نامساوی»، ستون وضعیت (E) چک می‌شه و ستون مبلغ (D) جمع زده می‌شه.

۰۴جمع‌بندی

مرور درس

  • فرمول =IF(test, if true, if false) بین دو تا جواب انتخاب می‌کنه؛ حواست به فرق >= و > باشه.
  • تابع COUNTIF ردیف‌هایی رو که شرط دارن می‌شماره؛ تابع SUMIF ستون اون ردیف‌ها رو جمع می‌زنه.
  • شرط‌ها داخل گیومه میان: مثل "Food"، ">1000" و "<>Paid".
  • شرط رو توی یه سلول بنویس و محدوده‌ها رو قفل کن تا برای هر دسته یه جمع کل داشته باشی.

به سبک خودت یادش بگیر.

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

Cafe Bazaar Myket توی اپ شخصی‌سازیش کن