فرمول چند شرطی در اکسل — به زبان ساده

ایجاد شرط در یک فرمول، قدرت انتخاب نحوه محاسبات را به کاربر می‌دهد. به این ترتیب بسته به اینکه چه شرط یا شرط‌هایی محقق شوند، نوع محاسبه می‌تواند تغییر کند. چنین مسئله‌ای در محاسبات مربوط به مالیات به وفور دیده می‌شود. از طرفی گاهی برای تبدیل متغیرهای کمی به کیفی یا طبقه‌ای و کدگذاری آن‌ها، چنین امری ضروری به نظر می‌رسد. در اکسل و همینطور بسیاری از زبان‌های برنامه‌نویسی، تابع شرطی IF یکی از مشهورترین و پرکاربردترین تابع‌ها برای ایجاد شرط و محاسبات مرتبط با آن است. هر چند ساختار تابع شرطی IF در اکسل، ساده است ولی ترکیب چندین شرط و پیاده‌سازی آن به کمک ترکیب چندین IF کار سختی خواهد بود. اغلب برای ترکیب شرط‌ها از تابع AND ،OR یا Not نیز استفاده می‌کنند. حتی در مواردی نیز می‌توان از تابع VLookup برای انجام این کار استفاده کرد. در این نوشتار از مجله فرادرس سعی داریم نحوه ایجاد فرمول چند شرطی در اکسل را با توابع یا فرمول‌های مختلف شرح دهیم. در این بین به کمک مثال‌هایی نیز به کارگیری چنین فرمول‌هایی مورد ارزیابی قرار خواهد گرفت.

برای آشنایی بیشتر با نحوه فرمول نویسی و کاربرد تابع IF در اکسل، بهتر است نوشتارهای فرمول نویسی در اکسل – آموزش مقدماتی و تابع IF در اکسل — به زبان ساده را مطالعه کنید. همچنین خواندن مطالب آموزش مقدماتی اکسل (Excel) — به زبان ساده و توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی  از مجله فرادرس نیز خالی از لطف نیست.

فرمول چند شرطی در اکسل

در بسیاری از فرمول‌ها، لازم است بسته به موقعیت یا شرط‌های مختلف، محاسبات متفاوتی را اجرا کنید. چنین وضعیتی را با عنوان فرمول چند شرطی (Multiple Condition) می‌شناسیم. نوشتن و ایجاد فرمولی که بتواند چنین کاری را انجام بدهد در بعضی از موارد امری پیچیده شده و به سادگی امکان‌پذیر نخواهد بود. البته راه‌کارهای متفاوتی برای غلبه بر این پیچیدگی ارائه شده است.

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

کلیک کنید

سعی داریم در این نوشتار از مجموعه مقالات فرادرس، با نحوه اجرا و ایجاد فرمول چند شرطی به کمک توابع متنوع اکسل آشنا شویم. هر چند به کارگیری روش‌های مختلفی برای ایجاد شرط در اکسل وجود دارد ولی بیشتر تکیه به تابع IF و البته IFS خواهیم داشت. به این موضوع نیز توجه داشته باشید که به کارگیری توابع دیگر مانند Choose یا SWITCH نیز در بعضی از موارد می‌تواند مفید باشند.

تابع IF در اکسل

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

1=IF(test,[true],[false])

در حقیقت تابع IF یک شرط را سنجیده و براساس صحت یا نادرست بودن آن، محاسباتی را انجام می‌دهد. این گزاره شرطی در پارامتر test قرار گرفته و در صورت صحیح بودن شرط، عملیات بخش true صورت گرفته و در صورت غلط بودن آن، محاسبات بخش false انجام خواهد شد.

برای اینکه بهتر کاربرد تابع IF را درک کنید به مثالی که توسط تصویر ۱، ارائه شده است، توجه کنید. نمرات دانشجویان در این ستون Score درج شده. شرط نیز به این شکل بیان شده که افراد دارای نمره بیش از ۶۵، با مقدار متنی "Pass" در ستون Result مشخص شوند و در صورتی که کمتر از ۶۵ امتیاز کسب کرده باشند، مقدار این سلول برایشان FALSE باشد.

برای مثال همانطور که در تصویر ۱ می‌بینید، مقدار سلول D3 برابر با Pass شده زیرا امتیاز این دانشجو بیش از ۶۵ است. البته دانشجویی به نام Block با نمره ۶۴، مقدار سلول نتیجه برایش FALSE است.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

نکته: اگر در قسمت false تابع IF چیزی ننویسید، اکسل در صورت نادرست بودن شرط، به طور خودکار مقدار سلول را با FALSE جایگزین خواهد کرد.

به جدولی که در ناحیه F3 تا G4 قرار دارد نیز توجه کنید. بعداً از این ناحیه برای تعیین حدود شرط استفاده خواهیم کرد. اگر بخواهیم به جای مقدار FALSE (که یک مقدار منطقی است)، مقدار متنی "Fail" قرار گیرد باید فرمول را برای سلول D3 به شکل زیر بنویسیم. توجه دارید که برای آنکه برای دانشجویان دیگر نیز این فرمول به کار گرفته شود، سلول D3 را باید در سلول‌های D4 به بعد کپی کنیم.

1=IF(C3>=65,"Pass","Fail")

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

تابع IF تو در تو ساده

آنچه به عنوان تابع IF‌ تو در تو می‌شناسیم، در حقیقت به کار بردن دو تابع IF برای بیان شرط است. این بار با توجه به کاربرگ تصویر ۳، می‌خواهیم کسانی که در ستون Score دارای نمره نیستند، مقدار متنی Incomplete در قسمت Result برایشان ثبت شود. به این ترتیب کسانی که دارای نمره هستند با شرط قبلی متن مربوطه برایشان ثبت می‌شود و در غیر اینصورت مقدار "Incomplete" باید در سلول مربوطه قرار گیرد.

کد دستوری ترکیب دو تابع IF در این حالت به صورت زیر نوشته خواهد شد.

1=IF(C3="","Incomplete",IF(C3>=65,"Pass","Fail"))

همانطور که مشاهده می‌کنید، تابع IF دوم، در قسمت FALSE تابع IF اول نوشته شده و به عنوان پارامتر آن تلقی خواهد شد. نتیجه اجرای این فرمول برای سلول C3 در تصویر ۳ قرار گرفته است.

واضح است که به صورت منطقی، ابتدا تابع IF‌ اول محاسبه شده در صورتی که نتیجه FALSE باشد، تابع IF دوم برای افرادی که نمره دارند، در نظر گرفته خواهد شد. ولی می‌دانیم که اکسل از داخلی‌ترین تابع یا پرانتز برای محاسبه استفاده می‌کند. بنابراین منطق محاسباتی اکسل، به این شکل عمل می‌کند که ابتدا کسانی که دارای نمره بیشتر از ۶۵ هستند، عبارت Pass و در غیر اینصورت Fail در سلول وابسته قرار می‌گیرد. سپس برای کسانی که مقداری در سلول C3 ندارند، عبارت Incomplete در سلول وابسته ثبت می‌شود.

فیلم آموزش محاسبات آماری در اکسل Excel در فرادرس

کلیک کنید

این بار می‌خواهیم با استفاده از کران‌هایی، حدود نمره افرادی که قبول شده‌اند را با حروف A تا D تقسیم بنده کرده و افرادی که کمتر از ۶۵ نمره کسب کرده‌اند را با کد F‌، نمایش دهیم. در جدول ۱، این تقسیم‌بندی‌ها را مشاهده می‌کنید.

به منظور روشن‌تر شدن روش نوشتن فرمول چند شرطی در اکسل به کمک تابع IF، یک نمودار گردشی (Flow Chart) برای مسئله بالا تهیه کرده‌ایم که در تصویر ۴، قابل مشاهده است

همانطور که مشخص است هر شرط یا تابع IF توسط یک لوزی، نمایش داده شده و محاسبات مربوط به TRUE و FALSE با مستطیل‌هایی تعیین شده‌اند. در تمامی قسمت‌های شرطی، مقدار FALSE با یک تابع شرطی دیگر جایگزین شده است. البته در آخرین بخش (پایین‌تر شرط)، قسمت FALSE، مقدار Grade را برابر با A‌ قرار داده است. به این ترتیب هر یک از شرط‌ها توانسته‌اند سطرهای جدول ۱ را بازسازی کنند.

حال می‌خواهیم چنین روندی را در اکسل پیاده کنیم. به تصویر ۵ توجه کنید، با توجه به محدودیت‌های ذکر شده مقدارهای A تا D و همچنین F برای هر یک از دانشجویان ثبت شده است. همانطور که می‌بینید اولین محاسبه، گروه F را جدا کرده و محاسباتی بعدی، کدهای درجه (Grade) را مشخص می‌کنند.

بهتر است نحوه نوشتن شرط‌های تو در تو (گزاره چند شرطی) را به کمک تابع IF در این حالت به صورت گام به گام شرح دهیم.

• ابتدا اولین IF را برای تعیین مردودی (درجه F) می‌نویسیم.

1=IF(C5<64,"F")

• مشخص است که این IF کسانی که نمره مردود گرفته‌اند را جدا می‌کند. در بخش بعدی باید گروه دوم را تعیین کنیم. حال IF دوم را هم در قسمت پارامتر سوم تابع IF‌ اول وارد می‌کنیم.

1=IF(C5<64,"F",IF(C5<73,"D"))

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

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

1=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C")))