عبارات شرطی (IF) در اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش گام به گام)

همه برنامه‌نویسان از اهمیت و کاربرد «عبارات شرطی» (IF statement) در زبان‌های نوشتاری آگاه هستند. اما آیا می‌دانستید امکان استفاده از همین منطق کدنویسی در یک «سلول» (cell) اکسل نیز وجود دارد؟

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

اگر در ابتدا این موضوع کمی پیچیده به نظر می‌رسد، نگران نباشید. در ادامه به روشی خلاقانه، راه‌های استفاده از عبارات شرطی را فرا خواهید گرفت. بگذارید با بیان یک نمونه، کاربرد چنین دستوراتی را روشن کنیم.

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

کلیک کنید

فرض کنید می‌خواهید اعداد زوج و فرد ورودی را با یکدیگر به صورت جداگانه جمع کنید. کارکتر A را برای جمع اعداد زوج و کارکتر B را برای جمع اعداد فرد، فعلا برابر با صفر، در نظر می‌گیریم. با استفاده از عبارات شرطی از نرم‌افزار می‌خواهید اگر عدد ورودی زوج بود، مقدار عدد را با مقدار قبلی A جمع کند. به همین شکل اگر ورودی عددی فرد بود، این مقدار به مقدار قبلی B اضافه شود. در نهایت تمام اعداد زوج با همدیگر در کارکتر A و تمام اعداد فرد در B جمع می‌شوند. این اگرها همان عبارات شرطی ‌هستند.

عبارت شرطی در اکسل چگونه است؟

اکثر افراد هنگامی که در مورد IF statement می‌شنوند، سریعا به یاد نرم‌افزار «ویژوال بیسیک» (Visual Basic) یا زبان‌های دیگر می‌افتند. چراکه عموما از این منطق در چنین زبان‌های کدنویسی استفاده می‌گردد. با این حال همین منطق را می‌توان در سلول‌های اکسل نیز به کار برد.

هنگامی که در سلول عبارت «)IF=» را بنویسید، خروجی به صورت پیش‌فرض برابر با «درست» (TRUE) یا «غلط» (FALSE) خواهد بود. با این حال با تنظیمات دلخواه می‌توانید خروجی را متناسب با نیاز تغییر دهید. توجه کنید هنگامی که شروع به نوشتن دستوری در اکسل می‌کنید، این برنامه به صورت خودکار قالب درست وارد کردن دستور را به شما نشان می‌دهد.

اساس کار یک تابع شرطی چیست؟

ابتدا بیایید به «تابع شرطی» (IF function) نگاهی بیندازیم. در «صفحه‌گسترده» (spreadsheet) شکل بالا، اطلاعات چهار نوع فعالیت مربوط به نگهداری و تعمیر خودرو وارد شده است. هر کدام از اقدامات شامل «تعویض روغن» (oil change)، تعمیر خودرو (car repair)، ثبت‌نام (registration) یا «تجدید بیمه‌ی خودرو» (insurance renewal) انجام شود، در جدول ثبت می‌گردد.

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

کلیک کنید

حال می‌خواهیم اگر ستون مربوط به Repaired با YES پر شود، در ستون Event Type، کلمه‌ی REPAIR وارد شود. در غیر این صورت باید عبارت NON-REPAIR وارد گردد. چنین منطقی بسیار ساده است و به شکل زیر نوشته می‌شود.

1=IF(C2="YES","Repair","Non-Repair")

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

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

عبارات شرطی IF و AND

دقیقا مشابه زبان‌های برنامه‌نویسی رایج، به منظور بررسی دو یا سه شرط که به یکدیگر وابسته‌اند، نیاز به منطق AND دارید. در اکسل نیز این امکان به شکل مشابه وجود دارد.

ابتدا دو نوع اتفاق جدید با مفهوم «برنامه‌ریزی شده» (Planned) و «بدون برنامه قبلی» (Unplanned) تعریف می‌کنیم.

برای نمونه تنها بر ستون Oil Change تمرکز می‌نماییم. فرض کنید طبق برنامه، روغن خودرو در روز دوم هر ماه تعویض می‌گردد. هر گونه تعویض روغن در غیر از این روز به عنوان Unplanned در نظر گرفته می‌شود.

جهت تشخیص این موضوع به یک دستور AND به شکل زیر نیازمندیم.

1=IF(AND(DAY(A2)=2,B2="YES"),"Planned","Unplanned")

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

همان‌طور که مشاهده می‌کنید، دستور به خوبی کار می‌کند. هر زمان که با برنامه‌ی قبلی و در تاریخ دوم هر ماه روغن تعویض گردد، نتیجه در ستون Planned ثبت می‌شود. با این حال اگر ستون Oil Change دارای سلول خالی باشد، ستون‌های نتیجه نیز باید خالی بماند.

به این منظور باید از دستور دیگری استفاده کنیم که در ادامه توضیح داده می‌شود.

عبارات شرطی داخلی

جهت اصلاح نتایج به مقدار مورد نظر، باید تابع شرطی دیگری داخل تابع شرطی اصلی جایگذاری شود. انتظار داریم اگر سلولی در ستون Oil Change خالی باشد، سلول متناظر در ستون نتایج نیز خالی بماند.

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

کلیک کنید

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

1=IF(ISBLANK(B2),"",IF(AND(DAY(A2)=2,B2="YES"),"Planned","Unplanned"))

شاید این عبارت کمی پیچیده به نظر برسد. با این حال اگر با دقت نگاه کنید، به راحتی آن را درک خواهید کرد. اولین IF statement بررسی می‌کند که آیا سلول ستون B خالی است یا نه. اگر این سلول خالی باشد نتیجه‌ی نهایی خالی خواهد بود.

اگر سلول خالی نباشد، عبارت شرطی مشابهی مانند مثال قبل در قسمت False اولین عبارت شرطی وارد می‌کنیم. به این شکل تنها در صورتی که واقعا روغن خودرو تعویض شده باشد، سلول متناظر مورد بررسی قرار می‌گیرد و در غیر این صورت خالی باقی خواهد ماند.

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

عبارت OR

حال می‌خواهیم کمی سطح دستورات را بالا ببریم. فرض کنید مایلیم اگر تعویض روغن، تعمیر، ثبت‌نام و تجدید بیمه هم‌زمان انجام شوند، نتیجه برابر با «تعمیر و نگه‌داری سالانه» (Yearly Maintenance) و در غیر این‌ صورت برابر با «تعمیر و نگه‌داری معمولی» (Routine Maintenance) طبقه‌بندی گردد. در نگاه اول این کار به نظر سخت می‌رسد اما با منطق درستی از عبارات شرطی، مشکلی نخواهیم داشت.

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

1=IF(OR(B2="YES",C2="YES"),IF(OR(D2="YES",E2="YES"),"Yearly Maintenance","Routine Maintenance"),"")

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

امکان تحلیل‌های پیچیده تنها با توابع شرطی داخلی بسیار شگفت‌انگیز است.

نتایج بر اساس بازه‌ای از مقادیر ورودی

گاهی مایلیم بازه‌ای از مقادیر را به یک نتیجه‌ی متنی تبدیل کنیم. برای نمونه شاید بخواهیم بازه‌ی دمایی ۰ تا ۵۰ درجه‌ی فارنهایت را «سرد»، همچنین ۵۰ تا ۸۰ درجه را «گرم» و بالاتر از ۸۰ درجه را «داغ» در نظر بگیریم.

به علاوه احتمالا معلم‌ها همیشه به چنین منطقی نیاز داشته باشند. در مثال زیر امکان تغییر بازه‌ای از مقادیر را به یک نتیجه‌ی متنی بررسی می‌کنیم.

بگذارید به شکل زیر این بازه‌ها را تعریف کنیم.

• نمره‌ی ۹۰ تا ۱۰۰ را به عنوان A
• نمره‌ی ۸۰ تا ۹۰ را به عنوان B
• نمره‌ی ۷۰ تا ۸۰ را به عنوان C
• نمره‌ی ۶۰ تا ۷۰ را به عنوان D
• و در نهایت نمره‌ی کمتر از ۶۰ را به عنوان F در نظر می‌گیریم.

به شکل زیر از چند عبارت شرطی داخلی استفاده می‌کنیم.

1=IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","F"))))

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

نتایج مورد نظر به شکل زیر در می‌آید.

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

اگر علاقه دارید در این رابطه بیشتر یاد بگیرید، به مطالب زیر مراجعه کنید.

• آموزش آشنایی با اکسل (Microsoft Office Excel 2016) (رایگان)
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• آموزش داشبوردهای مدیریتی اکسل (Excel)
• آموزش ایجاد و مدیریت کاربرگ در اکسل – (رایگان)
• آموزش ارسال ایمیل از اکسل با استفاده از اسکریپت‌های ویژوال بیسیک
• چگونه کلمه عبور فایل برنامه‌نویسی‌شده در اکسل (VBA) را بازیابی کنیم؟

^^