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

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

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

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

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

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

تابع IF در اکسل

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

در حقیقت تابع 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 به بعد کپی کنیم.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• همینطور برای گروه A و B نیز شکل تابع تو در تو IF به صورت زیر خواهد بود.

نتیجه اجرای این فرمول چند شرطی در اکسل را در تصویر ۶ مشاهده می‌کنید. مشخص است که کنترل این تعداد شرط و بررسی عملکرد آن‌ها به سختی صورت می‌گیرد.

واضح است که با توجه به افزایش شرط و محدودیت‌ها در جدول ۱، تعداد توابع IF به کار رفته نیز بیشتر خواهد شد. همانطور که مشاهده کردید، فرمول یاد شده، امتیازات (Score) را به پنج درجه (Grade) تقسیم و تفکیک کرد.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

نکته: متاسفانه در نسخه‌های قبل از اکسل ۲۰۰۷، امکان ترکیب هفت تابع تو در تو وجود دارد. بنابراین اگر طبقه‌ها، بیش از ۸ گروه باشند، با ترکیب توابع IF به شکل تو در تو (Nested Function)، نمی‌توان فرمولی نوشت. این محدودیت برای اکسل‌های جدیدتر از ۲۰۰۷، ترکیب ۶۴ تابع تو در تو است.

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

نکته: اگر فرمول نوشته شده در نوار فرمول طولانی شود، با بزرگتر کردن نوار فرمول می‌توانید خوانایی محاسبات نوشته شده را افزایش دهید. کافی است نشانگر را در مرز نوار فرمول و کاربرگ قرار دهید و در زمانی که نشانگر به شکل یک فرش دو طرفه بالا و پایین ( $$\Updownarrow$$ ) درآمد، با کشیدن و رها کردن، ارتفاع نوار فرمول را بیشتر کنید.

یکی از روش‌های رفع محدودیت‌ها و پیچیدگی ترکیب چندین IF، استفاده از ترکیب IF با توابع AND و OR است. در ادامه متن به ترکیب این توابع با IF خواهیم پرداخت.

ترکیب AND و OR با تابع IF

برای ایجاد یک فرمول چند شرطی در اکسل می‌توان توابع شرطی دیگر نظیر AND، OR و حتی NOT را با تابع IF ترکیب کرد. برای آشنایی با نحوه کار به یک مثال دیگر می‌پردازیم.

فرض کنید مطابق با تصویر ۸، می‌خواهیم در ستون Flag، سطرهایی که مقادیر ستون Color و Size آن‌ها به ترتیب برابر با "red" و "small" هست با علامت "x" مشخص شوند.

به این منظور فرمول زیر را در سلول D4 قرار داده‌ایم.

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

نکته: البته این کار را هم با ترکیب دو تابع IF‌ و به کمک فرمول زیر می‌توانستیم انجام دهیم.

حتی می‌توان شرط‌ها را بیشتر نیز کرد. در نظر بگیرید که قرار است برای سطرهایی که ستون Color آن‌ها برابر با "red" یا "blue" بوده و اندازه آن‌ها "small" است مقدار "x" ثبت شود.

ترکیب توابع OR و AND با تابع IF به شکل زیر، محاسبات مربوطه را انجام می‌دهد.

فرمول چند شرطی در اکسل با تابع VLookup

زمانی که یک فرمول چند شرطی در اکسل قرار است مقادیر مشخصی را به ازاء هر شرط در سلول قرار دهد، استفاده از تابع VLookup ساده‌تر از ترکیب چندین IF به شکل تو در تو است.

برای نشان دادن نحوه کار از یک مثال کمک می‌گیریم. کاربرگی که در تصویر ۹ دیده می‌شود را در نظر بگیرید. قرار است به ازاء هر رنگ تعیین شده، مقدار امتیاز آن از جدول استخراج شده و نمایش داده شود.

با استفاده از تابع VLookup، این کار در سلول E4 به سادگی و راحتی صورت می‌گیرد. فرمول محاسباتی برای سلول E4 به شکل زیر نوشته می‌شود.

نتیجه این محاسبه در تصویر 10، دیده می‌شود.

توجه داشته باشید که مقادیر مربوط به رنگ‌ها در سلول E4، باید دقیق مطابق مقادیر ستون B نوشته شده باشند، در غیر اینصورت، اکسل قادر به شناسایی آن‌ها نبوده و نتیجه به شکل نادرست نمایش داده خواهد شد.

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

توجه داشته باشید که اگر رنگ وارد شده در سلول E3 را رنگ‌های موجود در لیست ستون B،‌ مطابقت نداشته باشد، تابع VLookup پیغام خطای !N/A# و فرمول IF تو در تو، مقدار FALSE را نشان خواهند داد.

فرمول چند شرطی در اکسل با تابع Choose

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

به تصویر 11 توجه کنید. همانطور که می‌بینید ابتدا تابع WEEKDAY مقدار تاریخ که در سلول B4 قرار گرفته را دریافت کرده و شماره روز در هفته را مشخص می‌کند. برای مثال روز یکشنبه، اولین روز هفته در تقویم میلادی است در نتیجه اگر تاریخ سلول B4 مربوط به روز یکشنبه باشد، تابع WEEKDAY مقدار ۱ را برمی‌گرداند. به همین ترتیب مقادیر ۱ تا ۷ توسط این تابع قابل محاسبه است.

به کمک تابع Choose هر یک از مقادیر ۱ تا ۷ براساس حرف اول روز هفته (M- Monday تا F-Friday) درون سلول‌های ستون C قرار می‌گیرد.

به این ترتیب به جای به کارگیری چندین تابع IF، فقط یک تابع Choose برای کدگذاری مقادیر صحیح و مرتب شده، به کار می‌رود.

فرمول چند شرطی در اکسل با تابع SWITCH

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

=SWITCH(expression, value1 , result1 , ...,value126, result126, default_result)

پارامترهای این تابع به صورت زیر هستند.

• expression: مقدار یا آدرسی از سلول‌ها که باید براساس آن، تصمیم‌گیری شود.
• value: مقداری یا آدرسی از سلول‌ها که باید با مقدار value to switch، مطابقت داده شود. این پارامتر می‌تواند از ۱ تا ۱۲۶ حالت مختلف داشته باشد. توجه داشته باشید که این پارامتر با پارامتر بعدی به صورت زوج در فرمول ظاهر خواهند شد.
• result: مقداری یا آدرس سلولی که در صورت مطابقت با پارامتر قبلی در سلول وابسته ظاهر می‌شود. این پارامتر نیز حداکثر دارای ۱۲۶ حالت مختلف خواهد داشت.
• default_result: این پارامتر در صورت محقق نشدن شرط‌های قبلی (در پارامتر Value to match) مقداری را برای سلول وابسته تعیین می‌کند. این پارامتر اختیاری است و در صورتی که مشخص نشده باشد، در صورت صدق نکردن هیچ یک از شرط‌ها، تابع SWITCH، پیغام خطای !N/A# را نشان خواهد داد.

همانطور که می‌بینید، این تابع می‌تواند از ۱ تا ۱۲۶ شرط را مورد بررسی قرار دهد. هر گاه مقدار expression با یکی از مقادیر مربوط به پارامتر value، مطابقت پیدا کند، مقدار متناظر از قسمت result نمایش داده می‌شود.

در حقیقت تابع SWITCH یک مقدار (پارامتر اول) را با یک لیست از مقادیر (پارامترهای value) مقایسه کرده و در صورت مطابقت، نتیجه (پارامتر result) را در سلول وابسته قرار می‌دهد. برای روشن شدن موضوع به یک مثال می‌پردازیم. فرض کنید که در جدول F4 تا G7، رتبه‌ها (Rating) و نام رتبه (Key) را مشخص کرده‌ایم. می‌خواهیم برای افراد مختلف که در جدول سمت چپ قرار دارند، با توجه به رتبه هر یک از آن‌ها که در ستون C نوشته شده، نام رتبه را در ستون D، مشخص و نمایش دهیم.

همانطور که در تصویر 12 مشاهده می‌کنید، سلول وابسته D5 است. پارامتر اول (که با آدرس C5 مشخص شده) نیز رتبه نفر اول (Jim) است. تابع نوشته شده برای مشخص کردن نام رتبه این فرد به صورت زیر است:

همانطور که می‌بینید اگر کسی در هیچ یک از گروه‌ها قرار نگیرد و رتبه‌ای بیشتر از ۳ داشته باشد (۴ و مقادیر بیشتر)، عبارت ?? برایش در ستون D ثبت خواهد شد.

نکته: این تابع نیز در نسخه‌های جدید اکسل (2019 به بعد یا Office 365) ظاهر شده و قابل استفاده است.

به نظر می‌رسد تابع SWITCH پیشرفته‌تر از تابع Choose است و می‌توان با آن، بسته به نوع مقدار پارامتر اول، نسبت به انتخاب مقدار جدید برحسب پارامترهای دوم و سوم و ...، سلول وابسته را مقدار دهی کرد.

یکی از معایب استفاده از تابع SWITCH، مطابقت مقدار دقیق برحسب شرط است. به این ترتیب نمی‌توان در پارامتر expression از گزاره شرطی به صورت < یا > استفاده کنید. ولی البته برای انجام این گونه مقایسه‌ها، گزاره‌های مقایسه‌ای را باید در پارامترهای value قرار دهید.

برای مثال حالتی را در نظر بگیرید که می‌خواهیم با توجه به مقدار سلول A1 که امتیاز یک ورزشکار را مشخص می‌کند، تعیین کنیم آیا باید مدال طلا (Gold) یا نقره (Silver) یا برنز (Bronze) به او اهدا شود.

فرمول و تابع SWITCH در این حالت به صورت زیر نوشته می‌شود.

نکته: به مقدار پارامتر اول (expression) که مقدار TRUE دارد در فرمول بالا توجه کنید. مقداری که باید در مورد آن تصمیم گیری شود در سلول A1 نوشته شده است.

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

توجه داشته باشید که تابع IFS محدودیت گفته شده را نسبت به تابع SWITCH ندارد.

فرمول چند شرطی در اکسل با تابع IFS

اگر از اکسل 2019 یا آفیس ۳۶۵ استفاده می‌کنید، دسترسی شما به تابع IFS وجود دارد. به کمک این تابع می‌توانید به ازاء هر شرطی که معرفی می‌کنید، یک نوع محاسبه را انجام دهید. هر یک از شرط‌ها در تابع IFS به صورت پارامتر معرفی می‌شوند و محاسبه مربوط به آن نیز پارامتر بعدی را تشکیل می‌دهد. به این ترتیب حداکثر 127 شرط، ممکن است با یکدیگر ترکیب شوند.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

کاربرگی که در تصویر 13 دیده می‌شود را در نظر بگیرید.

می‌خواهیم به ازاء هر یک از سطوح مشخص شده در سلول‌های G5 تا H9، برای هر یک از افراد، براساس مقادیر Score، امتیاز یا رتبه (Grade) را استخراج کنیم. فرمولی که برای انجام این کار احتیاج داریم بسیار ساده بوده و تقریبا شبیه تابع Choose یا SWITCH است. باز هم حاصل به کارگیری تابع IFS در اینجا، ایجاد گروه یا کدگذاری افراد برحسب مقادیر یک متغیر عددی بود.

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

نکته: به یاد داشته باشید که به کارگیری توابعی که فقط در نسخه‌های جدید اکسل امکان‌پذیر است در هنگام نمایش گاربرگ‌ها در نسخه‌های قدیمی‌تر با محدودیت روبرو می‌شود. اگر چنین فایلی را در نسخه‌های قدیمی اکسل باز کنید، نتیجه تا زمانی که محاسبات به روزآوری نشوند نشان داده شده ولی با تغییر یک سلول، خطای !Name# در سلول وابسته، ظاهر خواهد شد.

خلاصه و جمع‌بندی

همانطور که در این متن از مجله فرادرس خواندید، به کارگیری فرمول چند شرطی در اکسل به روش‌های مختلفی صورت می‌گیرد. شاید به کارگیری توابع Choose و VLookup برای مواقعی که محدوده مقادیر مشخص و کم باشد، مناسب‌ترین گزینه باشند. اگر هدف طبقه‌بندی و ایجاد متغیرهای کیفی از روی متغیرهای کمی است، شاید استفاده از SWITCH و Choose، مناسب باشد ولی متاسفانه در بعضی از موارد محاسبات چند شرطی در اکسل پیچیده‌تر از جستجوی ساده هستند. بنابراین بهترین راه‌کار شاید استفاده از تابع IFS باشد که در نسخه‌های جدید اکسل (نسخه 2019 به بعد) معرفی شده است. در این متن نحوه استفاده از این تابع نیز مورد بررسی قرار گرفت.