«توابع شرطی در اکسل» (Conditional Functions) دسته‌ای از پرکاربردترین توابع اکسل هستند که محاسبات را با توجه به برقرار بودن یا نبودن یک یا چند شرط تعریف شده در فرمول، انجام می‌دهند. هر یک از این توابع شرطی با توجه به نیاز محاسبه‌ای برای تحلیل داده‌های مالی، طبقه‌بندی اطلاعات و تصمیم‌گیری خودکار به‌خصوص برای تعداد داده‌های زیاد در جدول اکسل بسیار کاربرد دارند. در این مطلب از مجله فرادرس نحوه فرمول‌نویسی با ۱۴ تابع مختلف تک شرطی و چند شرطی اکسل را همراه مثال‌های ساده یاد می‌گیریم.

انواع توابع شرطی در اکسل

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

1. توابع تک شرطی اکسل
2. توابع چند شرطی اکسل

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

البته استفاده از توابع AND ،OR و NOT در ترکیب با توابع شرطی قابلیت‌های فراوانی را برای کاربران فراهم می‌کنند که در ادامه بحث هر یک از آن‌ها را همراه مثال‌های ساده توضیح می‌دهیم.

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

توابع تک شرطی در اکسل

«توابع تک شرطی اکسل» (Single Condition Functions) فقط یک شرط مشخص در فرمول را بررسی می‌کنند و با توجه به درست یا نادرست بودن شرط، نتیجه تعریف شده را نمایش می‌دهند. از این دسته توابع در فرمول‌نویسی‌ها و محاسبات ساده استفاده می‌کنیم.

تابع IF در اکسل

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

=IF(condition, value_if_true, value_if_false)

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

• Condition : شرط یا عبارت منطقی که تابع، درست یا غلط بودن آن را بررسی می‌کند.
• Value_if_true: مقداری که فرمول در صورت صحیح بودن شرط به‌عنوان نتیجه نشان می‌دهد.
• Value_if_false: مقداری که فرمول در صورت غلط بودن شرط به‌عنوان نتیجه نشان می‌دهد.

مثال

نمرات دانش‌آموزان یک کلاس را در یک جدول فرضی اکسل داریم. برای مشخص کردن «مردود» یا «قبول» بودن هر دانش‌آموز با توجه به نمرات آن‌ها فرمول =IF(B2:B6>=10,”قبول”,”مردود”) را می‌نویسیم. در این فرمول اگر نمره‌های دانش‌آموزان در محدوده سلول B2 تا B6 بالاتر از «۱۰» باشد، نتیجه ارزیابی در ستون C جدول با عبارت «قبول» و در غیر این صورت با عبارت «مردود» مشخص می‌شود.

هنگام فرمول‌نویسی با تابع IF می‌توانیم از عملگرهای محاسباتی < ، > ، <> ، = ، <= و >= برای تعریف شرط نیز استفاده کنیم.

تابع IFERROR در اکسل

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

=IFERROR(value, value_if_error)

در این ساختار آرگومان value مقداری مورد نظر برای بررسی خطا و آرگومان value_if_error عبارتی است که در صورت ایجاد خطا به‌عنوان خروجی نمایش داده می‌شود.

مثال

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

این پیام‌های خطا در تعداد بالای داده‌ها باعث سردرگمی می‌شود، بنابراین با فرمول =IFERROR(B2/C2, “خطای محاسباتی”) مواردی که به اشتباه در جدول وارد شده‌اند را در نتیجه نهایی با پیام «خطای محاسباتی» نمایش می‌دهیم. به این شکل، حسابدار شرکت می‌تواند بعد از بررسی دوباره اعداد، اشتباهات را اصلاح کند.

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

تابع COUNTIF در اکسل

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

=COUNTIF(cell_range_to_count, criteria)

در این ساختار، آرگومان cell_range_to_count محدوده‌ای از سلول‌ها است که شمارش در آن‌ها انجام می‌گیرد و آرگومان دوم criteria شرط مورد نظری است که می‌خواهیم شمارش با توجه به آن انجام گیرد. این شرط را می‌توانیم به شکل‌های مختلفی مانند موارد زیر تعریف کنیم.

•  یک عدد: به‌عنوان مثال در فرمول =COUNTIF(A1:A10,10) فقط سلول‌هایی که مقدار آن‌ها برابر «۱۰» است، شمرده می‌شوند.
•  آدرس یک سلول: به‌عنوان مثال در فرمول =(COUNTIF(A1:A10,B5) سلول‌هایی که مقدار آن‌ها برابر مقدار سلول B5 است شمرده می‌شوند.
•  عبارت متنی: به‌عنوان مثال در فرمول =COUNTIF(A1:A10,”Ali”) سلول‌هایی که متن داخل آن‌ها با عبارت «Ali» تطابق دارد، در شمارش به‌حساب می آیند.
•  همراه عملگرهای محاسباتی: به‌عنوان مثال در فرمول COUNTIF(A1:A10,”>20″) سلول‌هایی که مقدار آن‌ها بزرگ‌تر از عدد «۲۰» است، شمارش می‌شوند.
•  همراه عملگر محاسباتی نابرابر: به‌عنوان مثال در فرمول COUNTIF(A1:A10,”<>Ali”) فقط سلول‌هایی که متن داخل آن‌ها با عبارت «Ali» مشابه نیست، برای شمارش در نظر گرفته می‌شوند.

مثال

در جدول فرضی زیر می‌خواهیم تعداد فاکتورهای بالای «۲۰۰» میلیون تومان را بشماریم. با نوشتن فرمول =COUNTIF(B2:B7,”>200″) در صورت بزرگ‌تر بودن عدد مبلغ فروش در محدوده سلول B2 تا B7 آن مقدار در شمارش لحاظ می‌شود. بنابراین نتیجه نهایی به شکل تصویر زیر خواهد بود.

تابع SUMIF در اکسل

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

=SUMIF(criteria_range, criteria, [sum_range])

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

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

مثال

در جدول مربوط به مبالغ فروش چند فاکتور یک فروشگاه، این‌بار می‌خواهیم مجموع مبلغ فاکتورهایی که بیش از «۲۰۰» میلیون تومان هستند را حساب کنیم. بنابراین به راحتی با نوشتن فرمول =SUMIF(B2:B7, “>200”) جمع مبالغ را محاسبه می‌کنیم.

تابع AVERAGEIF در اکسل

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

=AVERAGEIF(criteria_range, criteria, [average_range])

تعریف آرگومان‌ها در این ساختار به شرح زیر هستند.

• criteria_range : محدوده‌ای که شرط را در آن‌جا بررسی می‌کنیم.
• criteria : شرطی که برای بررسی تابع در محدوده تعیین شده از جدول در نظر داریم.
• average_range :محدوده‌ای که در نظر داریم میانگین اعداد آن همراه با شرط مشخص محاسبه شود. این آرگومان اختیاری است و در صورت تعریف نکردن آن در فرمول، تابع به‌صورت خودکار آرگومان criteria_range را برای محاسبه در نظر می‌گیرد.

مثال

در جدول فرضی زیر می‌خواهیم میانگین مبلغ فروش فردی به‌نام «علی» را محاسبه کنیم. با نوشتن فرمول =AVERAGEIF(A2:A7, “علی”, B2:B7) محاسبه را انجام می‌دهیم.

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

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

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

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

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

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

در بسیاری موارد لازم است محاسبه‌ها را با تعریف چند شرط مختلف در اکسل انجام دهیم، بنابراین استفاده از توابع تک شرطی جوابگو نیست. برای این منظور با فرمول‌نویسی‌های کمی پیچیده‌تر از «توابع چند شرطی اکسل» (Multiple Conditions Functions) استفاده می‌کنیم.

تابع IF تو در تو در اکسل

در فرمول تابع IF تو در تو یا (Nested IF) یک تابع IF دیگر به عنوان آرگومان‌های value_if_true یا value_if_false قرار می‌گیرد. با این تابع می‌توانیم تا «۶۴» مرتبه تابع IF را به‌صورت تو در تو استفاده کنیم. البته در عمل، استفاده بیش از «۱۰» تابع به‌دلیل بالا رفتن احتمال خطا و پایین آمدن خوانایی فرمول پیشنهاد نمی‌شود. نمونه ساده ساختار فرمول به شکل زیر است.

=IF(condition1, value_if_true, IF(condition2, value_if_true, value_if_false))

مثال

در جدول فرضی زیر با استفاده از تابع IF تو در تو می‌توانیم محاسبات جذابی را انجام دهیم. اگر بخواهیم نتیجه فعالیت دانش‌آموزانی که نمره بالاتر یا مساوی «۱۸» گرفته است، با عبارت «عالی»، نتیجه دانش‌آموزان دارای نمره بالاتر یا مساوی «۱۴» با عبارت «خوب»، نتیجه دانش‌آموزان با نمره بالاتر یا برابر با «۱۰» با عبارت «قابل قبول» و دانش‌آموزی که نمره پایین تر از «۱۰» دارد با عبارت «مردود» مشخص شود، فرمول زیر را می‌نویسیم.

=IF(B2:B6>=18, "عالی", IF(B2:B6>=14, "خوب", IF(B2:B6>=10, "قابل قبول", "مردود"))) 

برای مطالعه مثال‌های بیشتر در مطلب زیر به‌طور کامل تابع IF تو در تو و سایر فرمول‌های شرطی اکسل را توضیح داده‌ایم.

تابع IFS در اکسل

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

ساختار کلی تابع IFS به شکل زیر است.

=IFS(logical-test1, value-if-true1, logical-test2, value-if-true2, …)

در این ساختار هر یک از آرگومان‌های logical-test نشان‌دهنده شرط مورد نظر و آرگومان‌های value-if-true نتیجه نهایی در صورت درست بودن شرط هستند.

مثال

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

=IF(B2:B6>=18, "عالی", IF(B2:B6>=14, "خوب", IF(B2:B6>=10, "قابل قبول", "مردود"))) 

اما با استفاده از تابع IFS فرمول به شکل ساده زیر تبدیل می‌شود.

=IFS(B2:B6>=18, "عالی", B2:B6>=14, "خوب", B2:B6>=10, "قابل قبول", B2:B6<10, "مردود")

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

تابع SWITCH در اکسل

تابع SWITCH در اکسل یکی دیگر از توابع چند شرطی است که از آن برای مقایسه یک مقدار ثابت با چندشرط مختلف و بازگرداندن نتیجه با مقدار تطبیق‌یافته استفاده می‌کنیم. این تابع از نسخه ۲۰۱۹ به بعد اضافه شده است و بر خلاف دو تابع IF تو در تو و تابع IFS فقط برای نوشتن فرمول‌های شرطی با عملگر مساوی کاربرد دارد و شرط‌های بزرگتر یا کوچک‌تر را نمی‌توانیم در فرمول استفاده کنیم.

ساختار کلی فرمول تابع SWITCH به شکل زیر است.

=SWITCH(expression, value1, result1, value2, result2, ..., default_result)

در این ساختار تعریف هر یک از آرگومان‌ها عبارتند از:

• expression : مقدار یا عبارتی که می‌خواهیم با حالت‌های مختلف مقایسه کنیم.
• value1 ، value2 و ..: مقادیری که عبارت مورد نظر برای مقایسه ممکن است با آن‌ها برابر باشد.
• result1 ، result2 و…: عبارت‌هایی که در صورت برقراری هر یک از شرط‌ها به‌عنوان نتیجه نمایش داده می‌شود.
• default_result : نتیجه‌ای که در صورت برابر نبودن هیچ کدام از مقادیر نمایش داده می‌شود.

مثال

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

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

=SWITCH(B2,1, "کد نامشخص", "تحویل داده شد", 4, "در حال ارسال",3, "تایید شده" ,2, "در انتظار تایید")

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

تابع COUNTIFS در اکسل

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

ساختار کلی فرمول تابع COUNTIFS به‌صورت زیر است.

=COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

در این فرمول هر یک از آرگومان‌های cell_range_to_count_1 تا cell_range_to_count_n به‌ترتیب محدوده سلول‌های اول تا n‌ام و آرگومان‌های criteria_1 تا criteria_n شرط‌های تعریف شده در هر محدوده هستند. البته همه محدوده‌های تعریف شده در فرمول از لحاظ تعداد سطر و ستون یکسان هستند.

مثال

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

=COUNTIFS(A2:A8, "علی", B2:B8, "فروردین" )

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

تابع SUMIFS در اکسل

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

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

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

• sum_range محدوده‌ای از جدول که باید مجموع مقادیر آن محاسبه شود.
• criteria_range1 و مشابه آن محدوده‌ای از سلول‌ها است که شرط اول تا n‌ام در آن بررسی می‌شود.
• criteria1 تا criterian شرط اول تا nام مورد نظر برای انجام عملیات است.

نکته مهم در مورد تابع SUMIFS این است که آرگومان sum_range به‌عنوان اولین بخش فرمول قرار می‌گیرد و نوشتن آن اجباری است. اما در تابع SUMIF این آرگومان اختیاری است و در انتهای فرمول قرار دارد.

مثال

در یک جدول فرضی مبلغ فروخته شده توسط فروشندگان مختلف شرکت بر حسب ماه را در اختیار داریم. حال اگر بخواهیم مجموع فروش فردی به‌نام «علی» در ماه فروردین را حساب کنیم، فرمول زیر را می‌نویسیم.

=SUMIFS(C2:C8, A2:A8, "علی", B2:B8, "فروردین" )

در این فرمول عبارت C2:C8 معادل آرگومان sum_range ، محدوده سلول‌های A2:A8 معادل آرگومان criteria_range1 و عبارت «علی» شرط اول فرمول است. به همین ترتیب B2:B8 معادل آرگومان criteria_range2 و عبارت «فروردین» شرط دوم فرمول است.

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

تابع AVERAGEIFS در اکسل

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

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

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

• average_range محدوده سلول‌هایی که میانگین در آن محاسبه می‌شود.
• criteria_range1 محدوده‌ای که اولین شرط در آن بررسی می‌شود.
• criteria1 شرطی که در محدوده criteria_range1 باید برقرار باشد.
• به همین ترتیب سایر آرگومان‌ها مانند criteria_range2 و criteria2 مربوط به سایر شرط‌ها مانند شرط دوم، سوم و بقیه موارد است.

باید توجه داشته باشیم که در این فرمول بر خلاف تابع AVERAGEIF، نوشتن آرگومان average_range اجباری است.

مثال

جدولی از نمرات دانشجویان را در دو رشته مختلف داریم. برای محاسبه میانگین نمرات دانشجویان دختر در رشته مهندسی شیمی از فرمول =AVERAGEIFS(C2:C9, B2:B9, “دختر”, A2:A9, “مهندسی شیمی”) استفاده می‌کنیم. نتیجه محاسبه به شکل تصویر زیر است.

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

تابع MAXIFS در اکسل

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

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

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

• max_range : محدوده‌ای که بیشترین مقدار در آن محاسبه می‌شود.
• criteria_range1 : محدوده‌ای که اولین شرط در آن بررسی می‌شود.
• criteria1 : اولین شرط که مربوط به محدوده criteria_range1 است.

به همین ترتیب نیز سایر شرط‌ها و محدوده‌های مربوط به‌صورت آرگومان‌های بعدی در فرمول اضافه می‌شوند.

مثال

میزان فروش محصولات شعبه‌هایی از یک فروشگاه زنجیره‌ای در شهرهای مختلف را در اختیار داریم. برای پیدا کردن بیشترین مقدار فروش محصول «لپ‌تاپ» در شهر «تهران» از فرمول =MAXIFS(C2:C7, B2:B7, “تهران”, A2:A7, “لپ‌تاپ”) استفاده می‌کنیم. در نتیجه به‌راحتی مطابق فرمول محاسبه انجام می‌شود.

در مطلب زیر مثال‌های بیشتری از نحوه کار با تابع MAXIFS را توضیح داده‌ایم.

تابع MINIFS در اکسل

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

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

در این ساختار نیز همانند تابع MINIFS با تفاوتی اندک، آرگومان min_range محدوده‌‌ مورد نظر برای جستجوی کوچک‌ترین مقدار، criteria_range1 محدوده سلول‌ها مربوط به اولین شرط و criteria1 اولین شرط فرمول است. بر حسب نیاز در آرگومان‌های بعدی نیز محدوده‌ها و شرط‌های بعدی تعریف می‌شوند.

مثال

در یک شرکت حمل و نقل، اطلاعات مربوط به زمان تحویل کالا و وضعیت آن‌ها بر حسب شهر در یک جدول قرار دارد. حال اگر بخواهیم کمترین زمان تحویل برای کالاهایی که در وضعیت «تحویل شده» قرار دارند را محاسبه کنیم، فرمول =MINIFS(D2:D9, C2:C9, “تهران”, B2:B9, “تحویل ‌شده” نتیجه دلخواه را خواهد داد.

استفاده از عملگرهای منطقی با توابع شرطی در اکسل

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

تابع AND در زمانی کاربرد دارد که بخواهیم نتیجه محاسبه فقط در حالت صحیح بودن هر دو شرط نمایش داده شود. در نقطه مقابل تابع OR در صورت برقرار بودن فقط یک شرط، نتیجه را نشان می‌دهد. همچنین، تابع NOT نتیجه منطقی یک شرط را برعکس می‌کند.

مثال

حسابدار یک شرکت فرضی می‌خواهد بررسی کند که فاکتورهایش قابل پرداخت است یا خیر. او برای این‌کار فاکتورهای خود را در یک جدول با دو حالت «وضعیت تایید» و «وضعیت پرداخت» تقسیم‌بندی کرده است.

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

• اگر یکی از حالت‌ها برقرار باشد، فاکتور «قابل بررسی» است.
• اگر هر دو حالت برقرار باشد، فاکتور «قابل پرداخت» است.
• اگر فاکتور تایید نشده باشد، ولی پرداخت شده باشد، فاکتور صادر شود.

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

=IF(OR(A2="Yes", B2="Yes"), "قابل بررسی", "قابل بررسی نیست")

=IF(AND(A2="Yes", B2="Yes"), "قابل پرداخت", "قابل پرداخت نیست")

=IF(AND(NOT(B2="بله"), C2="بله"), ", "قابل پرداخت ")

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

جمع‌بندی پایانی

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