آموزش ترکیب if و and در اکسل – ساخت شرط های چندگانه از صفر تا صد
ترکیب IF و AND در اکسل یکی از روشهای فرمولنویسی برای ساخت شرط چندگانه است که با استفاده از آن میتوانیم همزمان درستی یا نادرستی چند شرط را بررسی کنیم. در این حالت اگر همه شرطها درست باشند، یک عبارت یا نتیجه دلخواه بهعنوان خروجی نمایش داده میشود. در فرمول کلی این ترکیب، ابتدا تابع AND همه شرطها را کنترل میکند و در نهایت تابع IF با توجه به خروجیهای درست یا نادرست شرطها، نتیجه نهایی را نمایش میدهد. برای آشنایی بیشتر با نحوه فرمولنویسی و ساخت شرطهای چندگانه با ترکیب IF و AND، در این مطلب از مجله فرادرس روش کار را همراه هفت مثال مختلف یاد میگیریم.
- اجزای اصلی ساخت شرطهای چندگانه با ترکیب IF و AND را خواهید شناخت.
- روش فرمولنویسی با ترکیب IF و AND را یاد خواهید گرفت.
- نحوه کار با ترکیب چند شرطی IF و AND را همراه هفت مثال مختلف را میآموزید.
- با مسیر یادگیری سایر توابع چند شرطی اکسل در فرادرس آشنا خواهید شد.
ساختار فرمول ترکیب IF و AND در اکسل
برای درک بهتر فرمول ترکیب IF و AND در اکسل ابتدا لازم است با ساختار فرمول تابع IF و تابع AND آشنا باشیم. زیرا به این شکل نتیجه صحیحتری از ساخت شرطهای چندگانه ساده تا پیچیده در اکسل بهدست میآوریم. هر چند در ادامه مطلب این موارد را یاد خواهیم گرفت، اما قبل از ورود به بحث اصلی برای کسب مهارت بیشتر پیشنهاد میکنیم بهعنوان مسیر تکمیلی فیلم آموزش استفاده از توابع و فرمولنویسی در اکسل در فرادرس را با تمرکز بر درس دوم آن مشاهده کنید.
فرمول تابع IF در اکسل
ساختار فرمول تابع IF بهعنوان سادهترین تابع شرطی در اکسل به شکل زیر است.
=IF(logical_test, value_if_true, [value_if_false])در این ساختار هر آرگومان بهصورت زیر تعریف میشود.
- logical_test (اجباری): یک عبارت منطقی که تابع درستی یا نادرستی آن را بررسی میکند.
- value_if_true (اجباری): عدد، متن یا تابع دیگر که اگر شرط برقرار باشد، بهعنوان خروجی فرمول نمایش داده میشود.
- value_if_false (اختیاری): عدد، متن یا تابع دیگر که اگر شرط برقرار نباشد، به عنوان خروجی فرمول نمایش داده میشود.
فرمول تابع AND در اکسل
ساختار فرمول تابع AND نیز به شکل زیر است.
=AND(logical1, [logical2], …)که در این ساختار هر یک از آرگومانهای logical1 و logical2 شرطهای تعریف شده در فرمول هستند که تابع AND زمانی که همه این شرطها برقرار باشد، مقدار «True» را باز میگرداند.
فرمول ترکیب IF و AND در اکسل
فرمول اصلی ترکیب IF و AND با یکی کردن دو ساختار تابع IF و تابع AND به صورت زیر تعریف میشود.
=IF(AND(logical1, [logical2], …), value_if_true, [value_if_false])در این ساختار، تابع AND بهجای آرگومان logical_test در فرمول تابع IF قرار میگیرد. بنابراین فقط زمانیکه تمام شرطهای داخل AND برقرار باشند، فرمول نتیجه مشخص شده در value_if_true را برمیگرداند. به همین ترتیب حتی اگر یکی از شرطها برقرار نباشند، نتیجه نهایی فرمول عبارت مشخص شده در آرگومان value_if_false خواهد بود.
یادگیری سایر توابع چند شرطی همراه با فرادرس
توابع شرطی اکسل بسیار متنوع هستند و توابع ترکیبی دیگری مانند تابع IFS، تابع SUMIF، تابع COUNTIFS نیز هستند که در صورت فرمولنویسی صحیح میتوانیم محاسبات شرطی دیگری را با آنها انجام دهیم. اما از آنجا که جستجوی مطالب مربوط به هر یک از این توابع در مراجع مختلف، ممکن است وقتگیر باشد، مجموعه فیلمهای آموزشی فرادرس راهنمای جامع و یکپارچهای در این مسیر هستند.
در صورت علاقهمندی به دسترسی سریعتر،فیلمهای آموزشی منتخب زیر پیشنهاد اول یادگیری هستند.
- فیلم آموزش استفاده از توابع و فرمولنویسی اکسل همراه گواهینامه در فرادرس
- فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
- فیلم رایگان آموزش تابع SUMIFS در فرادرس
- فیلم رایگان آموزش نکات کاربردی فرمولنویسی اکسل در فرادرس
همچنین در دو مجموعه فیلم آموزش زیر نیز امکان انتخاب موارد بیشتری وجود دارد.
مثال محاسبه چند شرطی با ترکیب IF و AND
برای یادگیری بهتر روش ساخت فرمولهای چند شرطی با ترکیب IF و AND در اکسل و ترفندهای کار با آن، هشت مثال و کاربرد مختلف این ترکیب را توضیح میدهیم.
مثال اول: کاربرد ساده فرمول چند شرطی
در یک کار تحقیقاتی، فهرستی فرضی از افراد و اطلاعات تغذیهای آنها را در اختیار داریم که در آن وضعیت گیاهخوار بودن و حساسیت غذایی هر نفر با توجه به نوع تغذیه آورده شده است. حال میخواهیم افرادی را که از نظر تغذیهای گیاهخوار هستند و در عین حال حساسیت غذایی هم دارند، مشخص کنیم. طبق این اطلاعات، فرمول شرطی مانند تصویر زیر برای ردیف اول به شکل =IF(AND(D2=”بله”,E2=”بله”),”خیر”,”بله”) خواهد بود. بعد از اجرای فرمول برای سایر ردیفها، نتیجه نهایی در صورت درست بودن هر دو شرط، با رنگ نارنجی مشخص شده است.
مثال دوم: استفاده از عملگرهای منطقی
با استفاده از عملگرهای منطقی کوچکتر (>)، بزرگتر (<)، ترکیب مساوی یا کمتر از (=>) و مساوی یا بزرگتر از (=<) نیز میتوانیم عبارتهای چند شرطی مختلفی را بسازیم. بهعنوان مثال اگر در جدول قبلی بخواهیم افراد بزرگتر از ۱۹ سال و کوچکتر از ۲۲ سال را شناسایی کنیم، با استفاده از این عملگرهای منطقی، فرمول چند شرطی برای ردیف اول به شکل تصویر زیر در میآید که بعد از کپی کردن آن در سایر ردیفها نتیجه نهایی مشخص میشود.
همچنین برای مشخص کردن افرادی که سن آنها بزرگتر یا مساوی ۲۰ و کوچکتر یا مساوی ۲۱ است، فرمول با استفاده از عملگرهای منطقی به شکل تصویر زیر در میآید.
مثال سوم: محاسبات پویا با فرمول چندشرطی
در این کاربرد ترکیب IF و AND، بهجای استفاده از عبارتهای ثابت متنی یا عددی بهعنوان خروجی، میتوانیم فرمولهای محاسباتی دلخواه خود را جایگزین آرگومان value_if_true و value_if_false کنیم. در این حالت با توجه به نوع محاسبه، یک فرمول را بهجای این عبارتها قرار میدهیم. برای درک بهتر، نحوه انجام کار را با یک مثال توضیح میدهیم.
فرض میکنیم یک فروشگاه لوازم خانگی برای نمایندگان فروش خود سیستم پاداش تعریف کرده است. به این شکل که برای سفارشهای نهایی شده با مبلغ فروش حداقل ۱۰۰ میلیون تومان، پنج درصد فروش بهعنوان پاداش به نماینده تعلق میگیرد. در این حالت آرگومان value_if_true یک فرمول محاسباتی است. بنابراین نتیجه با فرمول چند شرطی را به شکل تصویر زیر مینویسیم.
در این مثال، به نمایندگانی که مبلغ فروش آنها پایینتر از ۱۰۰ میلیون تومان است، پاداشی تعلق نمیگیرد. حال اگر بخواهیم برای این دسته هم یک سیستم پاداش، بهعنوان مثال ۱ درصد میزان فروش، تعریف کنیم، آرگومان value_if_false را نیز به شکل فرمول B2*1% تعریف میکنیم. در این حالت نتیجه به شکل تصویر زیر در میآید.
مثال چهارم: محاسبه با تعریف بیشتر از دو شرط
در تمام مثالهای قبلی، فقط دو شرط را در فرمول استفاده کردیم، اما در ساختار ترکیب IF و AND با توجه به نسخه نرمافزاری اکسل بیشتر از دو شرط را نیز میتوانیم تعریف کنیم. در اکسل نسخه ۲۰۰۷ به بالا وارد کردن ۲۵۵ آرگومان شرطی مجاز است، اگر طول کل فرمول از ۸۱۹۲ کاراکتر بیشتر نشود. به این شکل انجام محاسبات پیچیدهتر شرطی با ترکیب IF و AND در اکسل بسیار ساده خواهد بود.
بهعنوان مثال، در فروشگاه لوازم خانگی مورد اشاره قبلی میخواهیم سفارشهای نهایی شده با مبلغ برابر یا بیشتر از ۱۰۰ میلیون تومان که در ماه نهم میلادی تحویل شدهاند را مشخص کنیم. در این حالت نتیجه فرمول با استفاده از تابع MONTH برای ردیف اول به شکل تصویر زیر خواهد بود که بعد از کپی کردن آن در سایر ردیفها نتیجه نهایی به دست میآید.
مثال پنجم: تعریف شرطهای متنی انگلیسی حساس به بزرگ یا کوچک بودن حروف
فرمول IF و AND در اکسل نسبت به بزرگ یا کوچک بودن حروف انگلیسی حساس نیست. اما اگر دادههایی در جدول داریم که در تعریف شرط میخواهیم این حساسیت در نظر گرفته شود، لازم است آن شرطها را به شکل جداگانه داخل تابع EXACT قرار دهیم. در این حالت ساختار کلی فرمول به شکل زیر در میآید.
=IF(AND(EXACT(cell,"condition1"), EXACT(cell,"condition2")), value_if_true, value_if_false)بهعنوان مثال، در جدول زیر میخواهیم نمایندگانی به اسم «Ali» را که مبلغ فروش آنها بیشتر از ۱۰۰ میلیون تومان است، مشخص کنیم. همانطور که در تصویر میبینیم، چند نماینده فروش با نام «Ali» یا «ali» وجود دارند که تنها تفاوت اسم آنها در بزرگ یا کوچک بودن حرف «A» است. بنابراین اگر فقط بخواهیم نمایندگانی که اسم آنها دقیقا «Ali» است را پیدا کنیم، فرمول برای ردیف اول به شکل زیر خواهد بود که بعد از کپی کردن آن در سایر ردیفها نتیجه نهایی مشخص میشود.
برای اینکه بتوانیم نام فرد را بدون تغییر فرمول بهراحتی تغییر دهیم، نام نماینده و شرط مربوط به مبلغ فروش را بهصورت جداگانه در دو سلول مینویسیم. در این حالت فرمول به شکل تصویر زیر در میآید که در آن برای قفل کردن سلول های مرجع، آنها را داخل علامت «$» مینویسیم. به این صورت فرمول موقع کپی کردن در ردیفهای دیگر تغییر نمیکند.
مثال ششم: فرمولنویسی ترکیب IF و AND با تابع OR
در فرمولنویسی با ترکیب IF و AND در اکسل به جز عدد و متن میتوانیم توابع مختلف را نیز در آرگومانهای شرطی وارد کنیم. یکی از موارد پرکاربرد، استفاده از تابع OR در این ساختار است. در این حالت زمانیکه میخواهیم در صورت برقراری حداقل یک شرط، نتیجه مشخصی را بهدست آوریم، تابع OR را در کنار تابع AND استفاده میکنیم.
بهعنوان مثال در جدول زیر میخواهیم اطلاعات دو نماینده به نامهای «علی» و «رضا» را که مبلغ فروش بالاتر از ۱۰۰ میلیون تومان دارند، مشخص کنیم. در این حالت ابتدا تابع OR با جستجو در ستون B نام نمایندگانی که «علی» یا «رضا» است را مشخص میکند. سپس تابع AND درست بودن شرط دوم یعنی مبلغ فروش بالای ۱۰۰ میلیون را بررسی میکند. در نهایت تابع IF زمانیکه هر دو شرط برقرار باشند، خروجی را نشان میدهد.
برای بررسی مثالهای بیشتر در مورد فرمولنویسی با تابع OR، در مطلب زیر نکات تکمیلی را بهطور کامل توضیح دادهایم.
مثال هفتم: استفاده از تابع شرطی IF تودرتو همراه تابع AND
در مواقعی که تعداد دادههای جدول اکسل زیاد باشد، امکان دارد نیاز به بررسی چند شرط همزمان با تابع AND داشته باشیم. در این حالت فرمول کلاسیک IF تودرتو را همراه چند شرط با تابع AND استفاده میکنیم. بنابراین فرمول کلی به شکل زیر در میآید.
=IF(AND(…), output1, IF(AND(…), output2, IF(AND(…), output3, output4)))در این فرمول تابع AND هر مجموعه شرط را از نظر صحیح بودن بهصورت جداگانه بررسی میکند. اگر مجموعه شرط اول برقرار باشد، اولین نتیجه طبق تعریف فرمول برگردانده میشود. در غیر این صورت، فرمول به مجموعه شرط بعدی میرود و این روند ادامه پیدا میکند.
بهعنوان مثال یک شرکت حمل و نقل فرضی میخواهد خدمات خود را بر اساس هزینه و زمان تحویل کالا به شکل زیر رتبهبندی کند.
- عالی: هزینه حمل کمتر از ۲۰ میلیون تومان با زمان تحویل کمتر از سه روز
- ضعیف: هزینه حمل بیشتر از ۳۰ میلیون تومان و زمان تحویل بیشتر از ۵ روز
- متوسط: این مورد شامل حالتهای دیگر میشود.
برای این کار مطابق تصویر، فرمول را به شکل زیر مینویسیم.
در این فرمول ابتدا اولین تابع IF برقرار بودن شرط مربوط به هزینه و زمان تحویل را از نظر تطبیق با رتبه «ضعیف» بررسی میکند. اگر این شرط برقرار نبود، دومین تابع IF تطابق با رتبه «عالی» را بررسی میکند. در نهایت اگر هیچ یک از این دو شرط برقرار نبودند، خروجی «متوسط» به عنوان نتیجه نمایش داده میشود.
در صورت علاقهمندی به یادگیری تکمیلی سایر توابع چند شرطی اکسل، در مطلب زیر از مجله فرادرس مثالهای بیشتری را توضیح دادهایم.
نکات کاربردی هنگام استفاده از ترکیب IF و AND
با رعایت نکات فرمولنویسی ترکیب IF و AND در اکسل، معمولا به مشکل خاصی برخورد نمیکنیم. اما توجه به نکات زیر در انتخاب بهترین فرمول چند شرطی برای محاسبات و ورود اطلاعات در این ترکیب کار را سادهتر میکند.
- ترکیب IF و AND جایگزین بهتری برای «تابع IF تودرتو» (Nested IF) کلاسیک است. البته در برخی مواقع مجبور به استفاده از تابع IF تودرتو هستیم، اما هر زمان که بین این دو تابع امکان انتخاب داشتیم، ترکیب IF و AND اولویت اول است.
- ترکیب IF و AND فقط در مواردی کاربرد دارد که تمام شرطها برقرار باشند. بنابراین، اگر بخواهیم در صورت برقراری فقط یک شرط، نتیجه خاصی از فرمول را بهدست آوریم، به جای تابع AND، تابع OR را همراه تابع IF استفاده میکنیم.
جمعبندی پایانی
با استفاده از ترکیب IF و AND در اکسل شرطهای چندگانه مختلفی را میتوانیم تعریف کنیم، به شکلی که در صورت برقرار بودن همه شرطها، خروجی فرمول بهدست میآید. در این مطلب از مجله فرادرس فرمولنویسی با این ترکیب را یاد گرفتیم و هشت مثال از کاربردهای متنوع آن را بررسی کردیم. از آنجا که ترکیب IF و AND یکی از توابع اکسل با فرمولنویسی چند شرطی است، برای آشنایی بیشتر با سایر توابع مشابه و بررسی تفاوتهای آنها علاقهمندان میتوانند درس دوم از فیلم آموزش استفاده از توابع و فرمولنویسی اکسل در فرادرس را مشاهده کنند.
