فصل ۳: توابع کلیدی — منطقی، شرطی، متنی، تاریخ و گرد کردن

توابع منطقی: IF، IFS، SWITCH، AND/OR و مدیریت خطا با IFERROR و IFNA

تصمیم‌گیری در فرمول

تابع IF(شرط، مقدار اگر درست، مقدار اگر نادرست) ساده‌ترین و پرکاربردترین تابع منطقی است. شرط هر عبارتی است که TRUE یا FALSE بدهد: مقایسه، ISBLANK، ISNUMBER یا ترکیبی از AND و OR. مشکل از جایی شروع می‌شود که شرط‌ها زیاد می‌شوند و IF های تو در تو غیرقابل‌خواندن می‌شوند.

مثال: پورسانت پلکانی نمایندگان فروش

نماینده‌ای که فروش ماهانه‌اش (ستون C، به تومان) از ۵ میلیارد بیشتر باشد ۳٪، از ۲ میلیارد بیشتر ۲٪ و بقیه ۱٪ پورسانت می‌گیرند:

IF تو در تو (همه‌ی نسخه‌ها):
=C2*IF(C2>=5E9, 3%, IF(C2>=2E9, 2%, 1%))

IFS (Excel 2019 به بعد):
=C2*IFS(C2>=5E9, 3%, C2>=2E9, 2%, TRUE, 1%)

SWITCH برای تطبیق دقیق (2019 به بعد):
=SWITCH(D2, "تهران", "منطقه ۱", "اصفهان", "منطقه ۲", "کاشان", "منطقه ۲", "سایر")

SWITCH(TRUE, …) برای بازه‌ها:
=SWITCH(TRUE, C2>=5E9, "طلایی", C2>=2E9, "نقره‌ای", "عادی")

منطق چندشرطی:
=IF(AND(C2>=2E9, E2="نقدی"), "تخفیف ویژه", "")
=IF(OR(D2="تهران", D2="کرج"), "پخش مرکزی", "پخش استانی")

اگر تعداد پله‌ها بیش از سه‌چهار تاست، به‌جای IF یک جدول پورسانت کوچک بسازید و با XLOOKUP تقریبی (فصل ۴) جست‌وجو کنید. تغییر نرخ‌ها آن‌وقت فقط تغییر یک جدول است، نه ویرایش صدها فرمول.

منطق به زبان اعداد

TRUE در محاسبات برابر 1 و FALSE برابر 0 است. پس «ضرب» معادل AND و «جمع» (بزرگ‌تر از صفر) معادل OR است. این ترفند پایه‌ی SUMPRODUCT و FILTER است:

=(C2>=2E9)*(E2="نقدی")        → 1 اگر هر دو شرط برقرار باشد
=((D2="تهران")+(D2="کرج"))>0   → TRUE اگر یکی برقرار باشد

IFERROR در برابر IFNA

تابعچه خطاهایی را می‌پوشاندکِی
IFERROR(x, جایگزین)همه‌ی خطاهافقط وقتی مطمئنید هر خطایی یعنی «نتیجه ندارد»
IFNA(x, جایگزین)فقط #N/Aجست‌وجوها؛ خطاهای واقعی مثل #REF! پنهان نمی‌شوند

IFERROR بی‌محابا خطرناک است: اگر ستونی حذف شود و فرمول #REF! بدهد، IFERROR آن را به صفر یا خالی تبدیل می‌کند و گزارش بی‌صدا غلط می‌شود.

نکته‌هایی که کمتر کسی می‌داند

  • AND و OR روی آرایه «سطر به سطر» کار نمی‌کنند: AND(A2:A10>0) فقط یک TRUE/FALSE برای کل محدوده می‌دهد. در فرمول‌های آرایه‌ای به‌جای آن‌ها از * و + استفاده کنید.
  • سلولی که فرمولش "" برمی‌گرداند خالی به نظر می‌رسد اما ISBLANK برایش FALSE است؛ برای بررسی «خالی‌نما» از A2="" یا LEN(A2)=0 استفاده کنید.
  • IF «تنبل» است: فقط شاخه‌ی انتخاب‌شده محاسبه می‌شود. برای جلوگیری از #DIV/0! بنویسید =IF(B2=0, 0, A2/B2)؛ نیازی به IFERROR نیست.
  • IFS اگر هیچ شرطی برقرار نباشد #N/A می‌دهد؛ همیشه آخرین جفت را TRUE, مقدار پیش‌فرض بگذارید.
  • عدد 5E9 همان پنج میلیارد است؛ نوشتن نمایی در فرمول، خطای شمردن صفرها را از بین می‌برد.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.