توابع شرطی در نرمافزار Excel شامل موارد سادهای مثل IF و AND و OR و موارد خاصتر مثل XOR و NOT و توابعی مثل COUNTIF و SUMIF که عمل شمارش و جمع زدن را با بررسی شرط انجام میدهند، میشود. در صورت آشنایی با توانمندیهای تابع سادهای مثل IF میتوانید فرمولهای شرطی را سادهتر و کوتاهتر بنویسید.
در این مقاله با چند نکته و مثال در استفاده از تابع IF و در ادامه توابع XOR و NOT در خدمت شما هستیم. با سیارهی آیتی همراه شوید.
مثالهایی از انواع کاربرد IF در اکسل
تابع IF اصلیترین تابع امور شرطی در اکسل است و لذا اگر با شیوههای مختلف استفاده از آن آشنا شوید، استفاده کردن از دیگر توابع شرطی سادهتر میشود. قبلاً به معرفی این تابع و روش کار با آن پرداختیم:
در این مقاله میخواهیم با نکات و مثالهای بیشتر، تابع IF را کاملتر درک کنیم. تابع IF در حالت کلی سینتکسی به صورت زیر دارد:
=IF(logical_test, [value_if_true], [value_if_false])
سه آرگومان این تابع به ترتیب موارد زیر است:
- logical_test: تست و شرط منطقی است که میبایست بررسی شود و نتیجهی آن صحیح یا غلط است.
- value_if_true: مقدار خروجی در صورت صحیح بودن شرط است.
- value_if_false: مقدار خروجی در صورت نادرست بودن شرط است.
انواع عمل مقایسهای در تابع IF و توابع شرطی
در نوشتن شرط میتوان از اعمال مقایسهای مختلفی استفاده کرد:
- سادهترین عمل مقایسه، = است که تساوی دو سمت علامت تساوی بررسی میشود.
- علامت < و > که به معنی بزرگتر و کوچکتر هستند، حالتهای سادهی بعدی هستند.
- برای نوشتن حالت بزرگتر یا مساوی، میبایست از علامت <= استفاده کرد.
- برای نوشتن حالت مخالف که برعکس حالت مساوی است، از علامت <> استفاده میشود.
و خلاصهای از این حالتها در یک جدول:
مثال اول از کاربرد IF: شرط بررسی عبارتها
تابع IF صرفاً برای چک کردن اعداد و ارقام کاربرد ندارد بلکه میتوانید عبارتها را نیز با هم مقایسه کنید. به عنوان مثال فرض کنید که میخواهیم در صورت تکمیل شدن کاری یا به عبارت دیگر Completed شدن کاری، در سلول روبروی آن کلمهی Yes درج شود و در غیر این صورت کلمهی No چاپ شود. برای این کار میتوان از IF استفاده کرد و مقدار سلول را با عبارت Completed مقایسه کرد. در صورت صحیح بودن شرط، Yes چاپ میشود.
بنابراین فرمول سادهای برای این مثال در سلولهای ستون C قرار میگیرد و مقدار سلولهای ستون B را با Completed قیاس میکند:
=IF(B2="Completed","No","Yes")
دقت کنید که جملاتی که باید چاپ شود و همینطور عبارتی که باید تست شود، بین دو دابلکوتیشن یا " قرار گرفته است.
این فرمول با روش کلیک و درگ از طریق مربع کوچک سمت راست و پایین سلول، در سلولهای بعدی کپی میشود.
و نتیجهی استفاده از این فرمول در ستون C را مشاهده میکنید:
توجه کنید که تابع IF به بزرگی و کوچکی حروف حساس نیست. لذا میتوانید کلمهی Completed را به صورت completed نیز وارد کنید.
استفاده از IF برای مقایسه اعداد در Excel
مثال بعدی ما این است که اگر مقدار سلولهای ردیف B که نمرهی دانشآموزان است، بزرگتر یا مساوی ۷۵ باشد، شخص قبول شده و امتحان را Pass کرده و در غیر این صورت نتیجه Fail یا مردود است. بنابراین فرمول به صورت زیر خواهد بود:
=IF(B2>=75,"Pass","Fail")
همانطور که مشاهده میکنید برای شرط عددی، عدد ۷۵ بین علامتهای " قرار نمیگیرد.
علامت بزرگتر یا مساوی در این مثال استفاده شده است. در نهایت نتیجه به صورت زیر خواهد بود:
و اما به جای اینکه IF پس از بررسی شرط، مقداری را چاپ کند، میتوانید با این تابع، محاسبهای انجام بدهید و نتیجه را چاپ کنید.
به یک مثال ساده توجه کنید: اگر میزان خرید کالا بیش از ۳۰۰۰ دلار باشد، مقدار چاپ شده معادل مبلغ خرید با ۱۰ درصد تخفیف است و در غیر این صورت تخفیفی وجود ندارد. برای این مسأله فرمول زیر مناسب است:
=IF(B2>=3000,B2*90%,B2)
به عبارت دیگر اگر مقدار سلول B2 بزرگتر از 3000 باشد، در سلول C2 که کنار آن است، ۳۰۰۰ در ۹۰ درصد چاپ میشود و در غیر این صورت سلول C2 مساوی B2 میشود. نتیجه را بررسی کنید:
به همین صورت میتوان در یک تابع IF از یک IF دیگر استفاده کرد و با این روش چند IF را به صورت تودرتو تعریف و استفاده کرد. برای بررسی مثالهای پیچیدهتر در این زمینه، به مقالهای که قبلاً منتشر کردیم مراجعه فرمایید.
استفاده از IF برای مقایسهی تاریخها در اکسل
با استفاده از تابع شرطی IF میتوانید تاریخها را نیز به سادگی مقایسه کنید. به عنوان مثال اگر تاریخ تایپ شده در سلولی، بزرگتر از تاریخ تحویل کار باشد، عبارت منقضی شده یا Overdue چاپ شود یا میزان تأخیر با تفریق تاریخها محاسبه و چاپ شود.
توجه کنید که روز فعلی با استفاده از تابع امروز یا TODAY قابل محاسبه است. این تابع تاریخ را با توجه به تاریخ تنظیم شده در کامپیوتر محاسبه میکند، لذا باید تاریخ را صحیح تنظیم کنید.
=TODAY()
فرمول زیر تاریخ تحویل را با تاریخ امروز مقایسه میکند. اگر تاریخ روز فعلی بزرگتر از تاریخ تحویل باشد، زمان تحویل منقضی شده و در غیر این صورت تفاضل چاپ میشود.
=IF(B2<TODAY(),"Overdue",B2-TODAY())
و نتیجه به این صورت خواهد بود:
معرفی تابع XOR و آموزش استفاده از آن در اکسل
تابع OR معادل یا است. تابع XOR که اولین بار در Excel 2013 اضافه شده است و به این صورت عمل میکند:
- در صورت صحیح بودن فقط یکی از ۲ شرط، مقدار TRUE یا صحیح را برمیگرداند.
- اگر هر دو شرط غلط یا هر دو صحیح باشند، FALSE یا نادرست، خروجی این تابع خواهد بود.
تفاوت این تابع با تابع OR در مورد دو شرط در این حالت است که همهی شروط صحیح باشند: OR زمانی که همهی شروط صحیح باشند، صحیح یا TRUE را برمیگرداند اما XOR غلط را برمیگرداند. در سایر حالتها این دو تابع یکسان هستند.
سینتکس کلی به صورت زیر است و در واقع XOR بیش از دو شرط نیز میپذیرد:
=XOR(logical1, [logical2] ...)
تابع XOR در صورت افزایش تعداد شرطها، وضعیت پیچیدهای پیدا میکند:
- زمانی که تعداد شرطهای صحیح، فرد باشد، مقدار خروجی TRUE است.
- اگر تعداد شروط صحیح زوج باشد یا تمام شرایط غلط باشند، خروجی تابع FALSE میشود.
و مثالی از کاربرد تابع XOR: فرض کنید برای رتبه دادن به فروشندگان، اگر یک فروشنده در دو نیمهی سال، بیش از ۳۰۰۰ دلار فروش ثبت کند، درجهی Gold به آن تعلق میگیرد و اگر در یکی از نیمههای سال بیش از ۳۰۰۰ دلار فروش ثبت کند، درجهی Silver برای وی در نظر گرفته میشود. حالت سوم این است که در هیچ یک از نیمههای اول و دوم سال، فروش بالای ۳۰۰۰ ثبت نکرده باشد که در این صورت درجهای اختصاص پیدا نمیکند.
برای این مثال، استفاده از تابع XOR بسیار ساده است. اگر فروش نیمههای سال در ستون B و C ثبت شده باشد، فرمول موردنیاز برای تخصیص مدال نقره یا Silver، این گونه است:
=IF(XOR(B2>=3000,C2>=3000),"Yes","No")
برای مدال طلا نیز کافی است از تابع AND استفاده شود:
=IF(AND(B2>=3000,C2>=3000),"Yes","No")
و نتیجه را در تصویر زیر مشاهده میکنید:
آشنایی با تابع NOT و کاربرد آن در اکسل
تابع NOT روش کار سادهای دارد: اگر شرط بررسی شده صحیح باشد، خروجی FALSE یا نادرست است و در صورت برقرار نبودن شرط، نتیجه TRUE یا درست خواهد بود. در واقع این تابع برعکس تابع IF عمل میکند.
=NOT(logical)
و یک مثال ساده از کاربرد NOT: فرض کنید که در سلولهای ستون B محل دفاتر ذکر شده و میخواهیم در ستون C، برای دفاتر واقع در لندن، کلمهی No چاپ شود و در غیر این صورت کلمهی Yes چاپ شود. برای این کار از فرمول زیر استفاده میشود:
=IF(NOT(B2="London"),"Yes","No")
همانطور که میبینید شرط موردبررسی در تابع IF، خروجی تابع NOT است. نتیجه را در تصویر زیر بررسی کنید:
به جای فرمول قبلی و کار با تابع NOT میتوان مستقیماً از IF استفاده و در واقع عبارت مخالف که <> است، استفاده کرد:
=IF(B2<>"London","Yes","No")
و نتیجه یکسان خواهد بود.
اما گاهی استفاده از تابع NOT به مراتب سادهتر از IF و حتی ضروری است. به عنوان مثال تابع ISTEXT بررسی میکند که آیا محتوای یک سلول، متن است یا خیر. در صورت وجود متن، TRUE و در غیر این صورت FALSE برگردانده میشود. تابع NOT خروجی این دستور را معکوس میکند که کاربردهای زیادی دارد.
به عنوان مثال فرض کنید میزان فروش توسط فروشندگان در ستون B ذکر شده باشد و برای کسی که فروشی نداشته از کلمهی None استفاده شده است. حال اگر بخواهیم فرمولی بنویسیم که در صورت مثبت بودن فروش، ۵ درصد از فروش به فروشنده تعلق بگیرد و برای کسی که فروشی ثبت نکرده، عددی محاسبه نشود، میتوان از NOT و ISTEXT استفاده کرد. در فرمول زیر اگر مقدار سلول B2 متنی باشد، شرط برقرار نیست و خروجی تابع IF عدد ۰ میشود و در غیر این صورت، خروجی ISTEXT عبارت FALSE خواهد بود که NOT آن را به TRUE تبدیل میکند و لذا ۵ درصد فروش محاسبه و چاپ میشود.
=IF(NOT(ISTEXT(B2)),B2*5%,0)
نتیجه را در تصویر زیر مشاهده میکنید:
howtogeekسیارهی آیتی
میخوام توی اکسل فرمول بدم
مثلا سلول A1=256 و B1=20 و C1=1000
میخوام فرمول بنویسم که اگر A1 بزرگتر از 256 بود اونوقت بگه B1 ضربدر C1 شود ولی اگر B1 بزرگتر از 20 بود حاصلضرب بزرگتر از 20000 نشود
ممنون میشم راهنمایی بفرمایید
سلام
الان نوشتم تست نکردم. یه چیز تو این مایهها میشه، ترکیب تابع if و and:
سلام
در صورتی که شرط درست بود و بخوام بگم محتوای سلول x رو کپی کن تو سلول y چی باید بنویسم؟
سلام
خب آرگومان اول if که میشه شرط. تو سلول y از if استفاده کنید، شرط رو بنویسید، آرگومان دوم هم بزارید آدرس سلول X و آرگومان سوم هم خالی یا 0