توابع چند شرطی در اکسل از جمله توابع پرکاربرد و مفید این نرم‌افزار هستند که به شما امکان می‌دهند بر اساس یک یا چند شرط، محاسبات، تحلیل‌ها و عملیات‌های مختلفی را انجام دهید. در این مقاله قصد داریم انواع توابع شرطی در اکسل و نحوه فرمول‌نویسی و استفاده از این توابع را از صفر تا صد آموزش دهیم و با مثالهای عملی نشان دهیم که چگونه می‌توانید با استفاده از این توابع، کارایی و سرعت خود را در کار با اکسل افزایش دهید.
تابع چند شرطی IF
تابع IF یکی از ساده‌ترین توابع چند شرطی در اکسل است که بر اساس یک شرط، یکی از دو مقدار را نمایش می‌دهد. برای مثال، اگر بخواهیم در یک ستون، بر اساس نمره دانش‌آموزان، قبول یا مردود بودن آن‌ها را نشان دهیم، می‌توانیم از تابع IF استفاده کنیم. یکی از مثالهای تابع if در اکسل را با هم ببینیم:
فرض کنید که نمره دانش‌آموزان در ستون B واقع شده‌اند. در این صورت، می‌توانیم در ستون C، فرمول زیر را وارد کنیم.
- =IF(B2>=50,”مردود”,”قبول”)
این فرمول بررسی می‌کند که آیا نمره دانش‌آموز در سلول B2 بزرگتر یا مساوی 50 است یا خیر. اگر بله، مقدار “قبول” را نمایش می‌دهد و اگر خیر، مقدار “مردود” را نمایش می‌دهد. این فرمول را می‌توانیم برای تمام سطرهای دیگر کپی کنیم تا برای هر دانش‌آموز، وضعیت قبولی یا مردودی را مشخص کنیم.
حالا برای اعمال این فرمول در بقیه سطر‌ها، علامت مثبت را مثل تصویر زیر به پایین می‌کشیم. در صورت لزوم این کار را برای تمامی توابع انجام دهید.
اما گاهی اوقات، یک شرط کافی نیست و ممکن است بخواهیم بر اساس چند شرط، مقدار یا عملی را انجام دهیم. در این صورت، می‌توانیم از توابع چند شرطی پیشرفته‌تری مانند IFS، AND، OR، NOT، IFERROR، IFNA، COUNTIF، COUNTIFS، SUMIF، SUMIFS، AVERAGEIF، AVERAGEIFS، تابع between در اکسل و غیره استفاده کنیم.
یا در صورتی که لازم باشد، داده‌ها به صورت قدرتمندتری تحلیل شوند از شرط‌های چند‌گانه به صورت همزمان استفاده می‌شود. یعنی با استفاده از آزمون‌های منطقی پیچیده، چند تابع IF را در یک فرمول جای می‌دهند که به اصطلاح به آن if تو در تو می‌گویند. در ادامه به بررسی و آموزش چند تابع مهم می‌پردازیم.
پیشنهاد مطالعه: آموزش گام به گام تکنیک های حرفه ای ایجاد آلارم در اکسل
تابع IFS در اکسل
تابع IFS یکی از توابع چند شرطی در اکسل است که بر اساس چند شرط، یکی از چند مقدار را نمایش می‌دهد. این تابع از چپ به راست شروع به بررسی شرط‌ها می‌کند و اولین شرطی که صحیح باشد، مقدار مربوط به آن را برمی‌گرداند. اگر هیچ شرطی صحیح نباشد، مقدار #N/A را نمایش می‌دهد. برای مثال، اگر بخواهیم بر اساس نمره دانش‌آموزان، رتبه آن‌ها را نشان دهیم، می‌توانیم از تابع IFS استفاده کنیم.
فرض کنید که نمره دانش‌آموزان در ستون B و جمع نمرات در ستون C واقع شده‌اند. در این صورت، می‌توانیم در ستون E، فرمول زیر را وارد کنیم.
- =IFS(B2>=18,“عالی”,B2>=15,“خوب”,B2>=10,“متوسط”,B2<10,“ضعیف”)
این فرمول بررسی می‌کند که آیا نمره دانش‌آموز در سلول E2 بزرگتر یا مساوی 70 است یا خیر. اگر بله، مقدار “عالی” را نمایش می‌دهد. اگر خیر، حالا بررسی می‌کند که آیا نمره دانش‌آموز بزرگتر یا مساوی 60 است یا خیر. اگر بله، مقدار “خوب” را نمایش می‌دهد. اگر خیر، بررسی می‌کند که آیا نمره دانش‌آموز بزرگتر یا مساوی 40 است یا خیر. اگر بله، مقدار “رضایت‌بخش” را نمایش می‌دهد. اگر خیر، مقدار “ضعیف” را نمایش می‌دهد.
تابع چند شرطی در اکسل AND
تابع AND یکی از توابع چند شرطی در اکسل است که بررسی می‌کند که آیا تمام شرط‌های ورودی، صحیح هستند یا خیر. فرمول این تابع به شکل زیر است:
- =AND(شرط2,شرط 1,…)
این تابع شرطی در اکسل، اگر تمام شرط‌های ورودی صحیح باشند، مقدار TRUE را نشان می‌دهد و اگر حتی یک شرطِ ورودی غلط باشد، مقدار FALSE را به ما می‌دهد. برای مثال، اگر بخواهیم بررسی کنیم که آیا یک دانش‌آموز هم در درس ریاضی و هم در درس فیزیک و هم عربی قبول شده است یا خیر، می‌توانیم از تابع AND استفاده کنیم.
فرض کنید که نمره ریاضی دانش‌آموزان در ستون B و نمره فیزیک آن‌ها در ستون C واقع شده‌اند. در این صورت، می‌توانیم در ستون D، فرمول زیر را وارد کنیم:
- =AND(B2>=50,C2>=50,D2>=50)
این فرمول بررسی می‌کند که آیا نمره ریاضی و نمره فیزیک دانش‌آموز در سلول‌های B2 و C2 و D2 بزرگتر یا مساوی 50 هستند یا خیر. اگر هر سه شرط صحیح باشند، مقدار TRUE را نمایش می‌دهد و اگر حتی یکی از شرط‌ها غلط باشد، مقدار FALSE را نمایش می‌دهد. این فرمول را میتوان برای تمام سطرهای دیگر نیز کپی کرد تا برای هر دانش‌آموز، وضعیت قبولی در هر دو درس مشخص شود.
تابع OR
یکی دیگر از توابع چند شرطی در اکسل، تابع OR است که بررسی می‌کند آیا حداقل یکی از شرط‌های ورودی صحیح است یا خیر. فرمول این تابع به شکل زیر است:
- =OR(شرط1، شرط 2، …)
این تابع اگر حداقل یکی از شرط‌های ورودی صحیح باشد، مقدار TRUE را برمی‌گرداند و اگر همه شرط‌های ورودی غلط باشند، مقدار FALSE را برمی‌گرداند. برای مثال، اگر بخواهیم بررسی کنیم که آیا یک دانش‌آموز در حداقل یکی از دروس ریاضی یا فیزیک قبول شده است یا خیر، می‌توانیم از تابع OR استفاده کنیم. فرض کنید که نمره ریاضی دانش‌آموزان در ستون B و نمره فیزیک آن‌ها در ستون C واقع شده‌اند. در این صورت، می‌توانیم در ستون D، فرمول زیر را وارد کنیم:
- =OR(B2>=10، C2>=10)
این فرمول بررسی می‌کند که آیا نمره ریاضی یا نمره فیزیک دانش‌آموز در سلول‌های B2 و C2 بزرگتر یا مساوی 10 هستند یا خیر. اگر حداقل یکی از شرط‌ها صحیح باشد، مقدار TRUE را نمایش می‌دهد و اگر هر دو شرط غلط باشند، مقدار FALSE را نمایش می‌دهد.
تابع NOT
تابع NOT یکی دیگر از توابع چند شرطی در اکسل است که مقدار منطقی ورودی را برعکس می‌کند. فرمول این تابع به شکل زیر است:
- =NOT(شرط)
این تابع اگر شرط ورودی صحیح باشد، مقدار FALSE را برمی‌گرداند و اگر شرط ورودی غلط باشد، مقدار TRUE را نمایش می‌دهد. برای مثال، اگر بخواهیم بررسی کنیم که دانش‌آموز در کدام‌یک از دروس ریاضی یا فیزیک قبول شده یا خیر، می‌توانیم از تابع NOT با ترکیب تابع OR استفاده کنیم. فرض کنید که نمره ریاضی دانش‌آموزان در ستون B و نمره فیزیک آن‌ها در ستون C واقع شده‌اند. در این صورت، می‌توانیم در ستون D، فرمول زیر را وارد کنیم:
- =NOT(OR(B2>=10,C2>=10))
این فرمول بررسی می‌کند که آیا نمره ریاضی یا نمره فیزیک دانش‌آموز در سلول‌های B2 و C2 بزرگتر یا مساوی 10 هستند یا خیر. اگر حداقل یکی از شرط‌ها صحیح باشد، مقدار FALSE و اگر هر دو شرط غلط باشند، مقدار TRUE را نمایش می‌دهد.
تابع COUNTIF
تابع COUNTIF نیز از توابع چند شرطی در اکسل است که شمارش و انتخاب سلولها با شرط در اکسل را برآورده می‌کند. در فرمول این تابع فقط یک شرط بررسی می‌شود؛ به شکل زیر:
- =COUNTIF(محدوده,شرط)
این تابع در یک محدوده از سلولها، شرط ورودی را بررسی می‌کند و تعداد سلول‌هایی را نشان می‌دهد که آن شرط را برآورده می‌کنند. برای مثال، اگر بخواهیم تعداد دانش‌آموزانی را بشماریم که در درس ریاضی قبول شده‌اند، می‌توانیم از تابع COUNTIF استفاده کنیم. فرض کنید که نمره ریاضی دانش‌آموزان در ستون B واقع شده‌اند. در این صورت، می‌توانیم در یک سلول خالی، فرمول زیر را وارد کنیم:
- =COUNTIF(B2:B20,“>=10”)
این فرمول در محدوده B2 تا B20، شرط “>=10” را بررسی می‌کند و تعداد سلول‌هایی را برمی‌گرداند که نمره آن‌ها بزرگتر یا مساوی 10 است.
تابع COUNTIFS
تابع COUNTIFS نیز از توابع چند شرطی در اکسل است که مثل تابع قبلی تعداد سلولها را می‌شمارد با این تفاوت که باید چند شرط را برآورده کند.
فرمول این تابع به شکل زیر است:
- =COUNTIFS(شرط2,محدوده2,شرط1,محدوده1,…)
این تابع در چند محدوده از سلولها، شرط‌های ورودی را بررسی می‌کند و تعداد سلول‌هایی را برمی‌گرداند که تمام شرط‌ها را برآورده می‌کنند. برای مثال، اگر بخواهیم تعداد دانش‌آموزانی را بشماریم که در هر دو درسِ ریاضی و فیزیک قبول شده‌اند، می‌توانیم از تابع COUNTIFS استفاده کنیم. فرض کنید که نمره ریاضی دانش‌آموزان در ستون B و نمره فیزیک آن‌ها در ستون C واقع شده‌اند. در این صورت، می‌توانیم در یک سلول خالی، فرمول زیر را وارد کنیم:
- =COUNTIFS(B2:B20,“>=10”,C2:C20,“>=10”)
این فرمول در محدوده‌های B2 تا B20 و C2 تا C20، شرط‌های “>=10” را بررسی می‌کند و تعداد سلول‌هایی را برمی‌گرداند که نمره آن‌ها در هر دو درس بزرگتر یا مساوی 10 است.
تابع VLOOKUP
تابع VLOOKUP یکی از توابع چند شرطی در اکسل است که مقدار یک سلول را بر اساس یک مقدار کلیدی در یک جدول جستجو می‌کند. فرمول این تابع به شکل زیر است:
- =VLOOKUP(مقدار,جدول,شماره_ستون,[محدوده])
این تابع در یک جدول، مقدار ورودی را در ستون اول جدول جستجو می‌کند و اگر مقدار مطابقت داشته باشد، مقدار سلولی را برمی‌گرداند که در همان سطر و در شماره ستون مشخص شده قرار دارد. اگر مقدار مطابقت نداشته باشد، مقدار #N/A را نمایش می‌دهد. اگر محدوده ورودی TRUE باشد، مقدار تقریبی را جستجو می‌کند و اگر FALSE باشد، مقدار دقیق را جستجو می‌کند. برای مثال، اگر بخواهیم نام دانش‌آموزی را بر اساس کد دانش‌آموزی در یک جدول پیدا کنیم، می‌توانیم از تابع VLOOKUP استفاده کنیم. فرض کنید که کد و نام دانش‌آموزان در جدول A2:B20 واقع شده‌اند. در این صورت، می‌توانیم در یک سلول خالی، فرمول زیر را وارد کنیم:
- =VLOOKUP(123,A2:B20,2,FALSE)
این فرمول در جدول A2:B20، مقدار 123 را در ستون A جستجو می‌کند و اگر مقدار مطابقت داشته باشد، مقدار سلولی را برمی‌گرداند که در همان سطر و در ستون B قرار دارد. اگر مقدار مطابقت نداشته باشد، مقدار #N/A را نمایش می‌دهد.
پیشنهاد مطالعه: آموزش Data analysis در اکسل با کمک افزونه Analysis toolpak
تابع چند شرطی در اکسل MATCH
تابع MATCH یکی از توابع چند شرطی در اکسل است که موقعیت یک مقدار در یک محدوده از سلولها را پیدا می‌کند. فرمول این تابع به شکل زیر است:
- =MATCH(مقدار,محدوده,[نوع])
این تابع در یک محدوده از سلولها، مقدار ورودی را جستجو می‌کند و اگر مقدار مطابقت داشته باشد، شماره موقعیت آن را برمی‌گرداند. اگر مقدار مطابقت نداشته باشد، مقدار #N/A را نمایش می‌دهد. اگر نوع ورودی 1 باشد، مقدار کمتر یا مساوی را جستجو می‌کند و محدوده باید به صورت صعودی مرتب شده باشد. اگر نوع ورودی 0 باشد، مقدار دقیق را جستجو می‌کند و محدوده می‌تواند به هر ترتیبی باشد. اگر نوع ورودی -1 باشد، مقدار بزرگتر یا مساوی را جستجو می‌کند و محدوده باید به صورت نزولی مرتب شده باشد. برای مثال، اگر بخواهیم موقعیت کد دانش‌آموزی 123 را در یک جدول پیدا کنیم، می‌توانیم از تابع MATCH استفاده کنیم. فرض کنید که کد و نام دانش‌آموزان در جدول A2:B20 واقع شده‌اند. در این صورت، می‌توانیم در یک سلول خالی، فرمول زیر را وارد کنیم:
- =MATCH(123,A2:A20,0)
این فرمول در محدوده A2 تا A20، مقدار 123 را جستجو می‌کند و اگر مقدار مطابقت داشته باشد، شماره موقعیت آن را برمی‌گرداند. اگر مقدار مطابقت نداشته باشد، مقدار #N/A را نمایش می‌دهد.
تابع INDEX
تابع INDEX یکی از توابع چند شرطی در اکسل است که مقدار یک سلول را بر اساس شماره سطر و ستون در یک محدوده از سلولها برمی‌گرداند. فرمول این تابع به شکل زیر است:
- =INDEX(محدوده، شماره_سطر، [شماره_ستون])
این تابع در یک محدوده از سلولها، شماره سطر و شماره ستون ورودی را در نظر می‌گیرد و مقدار سلولی را برمی‌گرداند که در آن موقعیت قرار دارد. اگر شماره ستون وارد نشود، مقدار سلولی را برمی‌گرداند که در شماره سطر ورودی و در ستون اول محدوده قرار دارد. برای مثال، اگر بخواهیم نام دانش‌آموزی را بر اساس شماره سطر و ستون در یک جدول پیدا کنیم، می‌توانیم از تابع INDEX استفاده کنیم. فرض کنید که کد و نام دانش‌آموزان در جدول A2:B20 واقع شده‌اند. در این صورت، می‌توانیم در یک سلول خالی، فرمول زیر را وارد کنیم:
- =INDEX(A2:B20,3,2)
این فرمول در محدوده A2 تا B20، شماره سطر 3 و شماره ستون 2 را در نظر می‌گیرد و مقدار سلولی را برمی‌گرداند که در آن موقعیت قرار دارد. در این مثال، مقدار سلول B4 را برمی‌گرداند که نام دانش‌آموز سوم است. این فرمول را می‌توانیم در هر سلول خالی دیگری کپی کنیم تا نام دانش‌آموزی را بر اساس شماره سطر و ستون پیدا کنیم.
توابع شرطی تاریخ در اکسل
توابع شرطی تاریخ در اکسل، توابعی هستند که بر اساس یک یا چند شرط مربوط به تاریخ، مقدار یا عملی را برگردانند. برای استفاده از این توابع چند شرطی در اکسل، باید تاریخ‌ها را به صورت استاندارد وارد کنیم. اکسل تاریخ‌ها را به صورت اعداد تعریف می‌کند که نشان دهنده تعداد روزهای گذشته از تاریخ 1 ژانویه 1900 هستند. برای مثال، تاریخ 1 ژانویه 2023 را به صورت عدد 44576 تعریف می‌کند. برای وارد کردن تاریخ به صورت استاندارد، می‌توانیم از فرمت‌های زیر استفاده کنیم:
- روز/ماه/سال
- ماه/روز/سال
- سال/ماه/روز
برای مثال، تاریخ 1 ژانویه 2023 را می‌توانیم به صورت 1/1/2023 یا 1/1/2023 یا 2023/1/1 وارد کنیم. اکسل این تاریخ را به صورت یک تاریخ شناسایی می‌کند و می‌توانیم با آن محاسبات و تحلیل‌های مختلفی را انجام دهیم.
برخی از توابع شرطی تاریخ در اکسل عبارتند از:
- تابع DATE: این تابع یک تاریخ را بر اساس سال، ماه و روز ورودی برمی‌گرداند. فرمول این تابع به شکل زیر است:
=DATE(سال,ماه,روز)
برای مثال، اگر بخواهیم تاریخ 1 ژانویه 2023 را با استفاده از این تابع بسازیم، می‌توانیم فرمول زیر را وارد کنیم:
=DATE(2023,1,1)
این فرمول تاریخ 1 ژانویه 2023 را برمی‌گرداند.
- تابع TODAY: این تابع تاریخ امروز را برمی‌گرداند. فرمول این تابع به شکل زیر است:
=TODAY()
این تابع هیچ ورودی نمی‌گیرد و تاریخ امروز را بر اساس تنظیمات سیستم شما برمی‌گرداند. برای مثال، اگر امروز 27 نوامبر 2023 باشد، این تابع تاریخ 27/11/2023 را برمی‌گرداند.
- تابع EDATE: این تابع یک تاریخ را بر اساس یک تاریخ شروع و یک تعداد ماه ورودی برمی‌گرداند. فرمول این تابع به شکل زیر است:
=EDATE(تاریخ_شروع,تعداد_ماه)
این تابع تاریخ شروع را در نظر می‌گیرد و تعداد ماه ورودی را به آن اضافه می‌کند و تاریخ نهایی را برمی‌گرداند. برای مثال، اگر بخواهیم تاریخ 6 ماه بعد از 1 ژانویه 2023 را با استفاده از این تابع بدست آوریم، می‌توانیم فرمول زیر را وارد کنیم:
=EDATE(1/1/2023,6)
این فرمول تاریخ 1 ژانویه 2023 را در نظر می‌گیرد و 6 ماه را به آن اضافه می‌کند و تاریخ 1 ژوئیه 2023 را برمی‌گرداند.
- تابع EOMONTH: این تابع تاریخ آخرین روز یک ماه را بر اساس یک تاریخ شروع و یک تعداد ماه ورودی برمی‌گرداند. فرمول این تابع به شکل زیر است:
=EOMONTH(تاریخ_شروع,تعداد_ماه)
این تابع، تاریخ شروع را در نظر می‌گیرد و تعداد ماه ورودی را به آن اضافه می‌کند و تاریخ آخرین روز ماه مربوطه را برمی‌گرداند. برای مثال، اگر بخواهیم تاریخ آخرین روز ماه 6 ماه بعد از 1 ژانویه 2023 را با استفاده از این تابع بدست آوریم، می‌توانیم فرمول زیر را وارد کنیم:
=EOMONTH(1/1/2023,6)
این فرمول تاریخ 1 ژانویه 2023 را در نظر می‌گیرد و 6 ماه را به آن اضافه می‌کند و تاریخ 31 ژوئیه 2023 را برمی‌گرداند.
- تابع DATEDIF: این تابع تفاوت بین دو تاریخ را بر اساس یک واحد زمانی ورودی برمی‌گرداند. فرمول این تابع به شکل زیر است:
=DATEDIF(تاریخ_شروع,تاریخ_پایان,واحد)
این تابع تاریخ شروع و تاریخ پایان را در نظر می‌گیرد و تفاوت بین آن‌ها را بر اساس واحد زمانی ورودی برمی‌گرداند. واحد زمانی می‌تواند یکی از مقادیر زیر باشد:
- Y: تفاوت سال
- M: تفاوت ماه
- D: تفاوت روز
- MD: تفاوت روز بدون در نظر گرفتن ماه و سال
- YM: تفاوت ماه بدون در نظر گرفتن سال
- YD: تفاوت روز بدون در نظر گرفتن سال
برای مثال، اگر بخواهیم تفاوت ماه بین دو تاریخ 1 ژانویه 2023 و 1 ژوئیه 2023 را با استفاده از این تابع بدست آوریم، می‌توانیم فرمول زیر را وارد کنیم:
- =DATEDIF(1/1/2023,1/7/2023,“M”)
این فرمول تاریخ 1 ژانویه 2023 و تاریخ 1 ژوئیه 2023 را در نظر می‌گیرد و تفاوت ماه بین آن‌ها را برمی‌گرداند. در این مثال، مقدار 6 را برمی‌گرداند.
سخن پایانی در مورد توابع اکسل
در این مقاله، به شما نحوه استفاده از توابع چند شرطی در اکسل (مثل توابع دو شرطی در اکسل و توابع سه شرطی در اکسل) را از صفر تا صد آموزش دادیم و با مثالهای عملی نشان دادیم که چگونه می‌توانید با استفاده فرمول نویسي و این توابع، کارایی و سرعت خود را در کار با اکسل افزایش دهید. امیدواریم که این مقاله برای شما مفید و جذاب بوده باشد. البته توابع اکسل بسیار بیشتر از آنچه هست که در این مقاله به آن پرداخته شد، در بسیاری از موارد حتی میتوان چند فرمول را با هم ترکیب کرد.
آموزش اکسل با مکتب خونه
اکسل یک نرم‌افزار قدرتمند برای کار با داده‌ها و انجام محاسبات است که در بسیاری از زمینه‌های کاری و تحصیلی کاربرد دارد. با اکسل می‌توانید جداول، نمودارها، فرمول‌ها، توابع، ماکروها و بسیاری موارد دیگر را ایجاد و ویرایش کنید. این نرم‌افزار به شما کمک می‌کند تا داده‌های خود را به راحتی مدیریت، تجزیه و تحلیل و ارائه کنید به خصوص با توابع چند شرطی در اکسل و ترکیب دستور if با آن‌ها در سازمان‌های خاص.
پس چنانچه به صورت حرفه‌ای می‌خواهید اکسل را یاد بگیرید و از تمام قابلیت‌های آن بهره‌مند شوید، پیشنهاد می‌کنیم از پکیج های آموزشی اکسل مکتب خونه استفاده کنید. مکتب خونه به شما امکان می‌دهد تا با هزینه اندک و زمان کم، اکسل را از مبتدی تا پیشرفته آموزش ببینید.
