گذاشتن شرط در اکسل با تابع IF بسیار پر کاربرد است و اتفاقا اکثر کاربران تازه کار اکسل با آن کمی مشکل دارند. به نظر من مشکل اصلی آنها در یک چیز است، آنهم نبودن درس منطق در دوران تحصیل و امیدوارم که روزی در دبیرستان‌ها برای همه رشته‌ها کمی اصول این درس زیبا، آموزش داده شود.
جملات شرطی چه در اکسل باشد و چه در زبان روزمره دارای سه قسمت است که با حوصله می خواهم تک به تک این قسمت ها را برای شما شرح دهم:
قسمت اول تابع IF اکسل - مقایسه
در هر جمله شرطی ساده ما معمولا در ابتدا یک مقایسه می کنیم به عنوان مثال:
* می گوییم که اگر معدل تو بالای 18 شود. (معدل با عدد 18 مقایسه می شود.)
* می گوییم که اگر هوا بارانی بود . (وضعیت هوا با بارانی بودن مقایسه می شود)
* اگر بعد از ساعت 8 کارت بزنی (ساعت ورود با عدد 8 صبح مقایسه می شود)
* اگر حقوق تو بالای 1500000 تومان باشد ( مبلغ حقوق شما با مبنای 15000000 مقایسه می شود)
در همه مثال های بالا می بینید که چیزی با چیزی مقایسه می شود. بنابراین اولین قسمت یک جمله شرطی یا تابع IF در اکسل ، یک مقایسه خواهد بود که حاصل آن True یا False خواهد شد.
قسمت دوم تابع IF اکسل - یک اتفاق
قسمت دوم جملات شرطی به ما می گوید که اگر پاسخ قسمت اول "درست از آب درآمد" یا به زبان دقیق‌تر True شد، باید چه اتفاقی باید بیافتد. و معمولا در فارسی از واژه "آنگاه" ، "اونوقت" و سپس بیان آن "اتفاق" استفاده می کنیم:
* که اگر معدل تو بالای 18 شود آونوقت "برایت پلی استیشن می خرم"
* می گوییم که اگر هوا بارانی بود آنگاه "چترت را ببر"
* اگر بعد از ساعت 8 کارت بزنی آنگاه "کسر کار خواهی خورد"
* اگر حقوق تو بالای 1500000 تومان باشد اونوقت "باید مالیات بدهی"
قسمت سوم تابع IF اکسل - یک اتفاق
قسمت سوم تابع IF در اکسل یا هر جمله شرطی به ما می گوید که اگر پاسخ مقایسه "درست از آب در نیامد" یا به زبان دقیق‌تر False شد، چه اتفاقی باید بیافتد. معمولا در فارسی این قسمت بعد از واژه "وگرنه" و "در غیر اینصورت" بیان می شود:
* که اگر معدل تو بالای 18 شود آونوقت "برایت پلی استیشن می خرم" وگرنه "دوچرخه ات رو هم می گیرم"
* می گوییم که اگر هوا بارانی بود آنگاه "چترت را ببر" در غیراینصورت "کلاه تابستانیت را ببر"
* اگر بعد از ساعت 8 کارت بزنی آنگاه "کسر کار خواهی خورد" وگرنه "مشکلی نیست"
* اگر حقوق تو بالای 1500000 تومان باشد اونوقت "باید مالیات بدهی" و در غیر اینصورت "مشمول مالیات نیستی"
خلاصه و جمع بندی
پس یک جمله شرطی ساده در اکسل و هر زبان برنامه نویسی دیگری دارای سه قسمت است :
1) مقایسه
2) اتفاقی (یا کار یا محاسبه ای) که اگر پاسخ مقایسه درست از آب درآمد، باید رخ دهد.
3) اتفاقی (یا کار یا محاسبه ای) که اگر پاسخ مقایسه درست از آب در نیامد، باید رخ دهد.
عکس زیر اینها را به صورت خلاصه مثال هوای بارانی به شما نشان می دهد. یعنی
اگر «هوا بارانی بود» آنگاه «چتر ببر» وگرنه «کلاهت را بردار»
شکل کلی تابع IF در اکسل
تابع IF در اکسل سه ورودی دارد و ورودی ها با علامت کاما از هم جدا می شوند:
IF( ... , ... , ....)حال می‌دانیم که باید در قسمت اول IF باید "یک مقایسه" بنویسیم و به جای نوشتن «آنگاه» و «وگرنه» در فرمول IF کافی است که یک کاما بگذارید و خود اکسل منظور شما را می‌فهمد.
بنابراین شکل کلی تابع IF اکسل اینگونه خواهد شد:
IF( Yek moghayese , har formuli , har formuli)مثال ۱)
فرمولی می‌نویسیم که عبارت اگر «هوا بارانی بود» آنگاه «چتر ببر» وگرنه «کلاهت را بردار» را در اکسل شبیه سازی کند:
=IF( hava = 🌧 , ☂ , 👒 )مثال ۲)
مثلا می خواهیم بدانیم که آیا حقوق شما مشمول مالیات هستید یا خیر. برای اینکار باید حقوق شما را در سلول A1 اکسل می نویسیم و با حداقل حقوق تصویبی برای مالیات مقایسه کنیم بنابراین قسمت اول IF به شکل زیر خواهد شد:
IF( A1 > 15000000 , ... , ...)در قسمت دوم IF هر فرمولی و یا هر محاسبه ای و یا هر چیزی که اکسل می فهمد 😉 را آن بنویسید. و این مقدار وقتی محاسبه می شود که پاسخ شرط شما "TRUE" شود و اگر پاسخ شرط شما FALSE شود، این محاسبه هرگز انجام نمی شود.
در این مثال ما 10% مبلغ حقوق را به عنوان مالیات محاسبه می کنیم و اگر حقوق بیش از 15000000 باشد، این محاسبه انجام می شود و به عنوان پاسخ فرمول IF نمایش داده می شود.
IF( A1 > 15000000 , A1*10% , ...)قسمت سوم IF ، می تواند دقیقا مانند قسمت دوم هر فرمول اکسلی باشد، و اگر پاسخ شرط شما FALSE شود، این محاسبه انجام می شود و به خروجی IF نمایش داده می شود .
در این مثال می خواهیم که مالیات حقوق زیر 15000000 عدد صفر باشد.
IF( A1 > 15000000 , A1*10% , 0 )به همین سادگی در اکسل یک IF نوشته می شود.
و می توانیم این فرمول را اینگونه بخوانیم:
"اگر مبلغ حقوق (یا سلول A1) بیشتر از 15000000 بود آنگاه باید 10% مبلغ حقوق را مالیات بدهی و گرنه مالیاتت صفر خواهد شد".
مثال ۳)
در جلوی نام پروژه‌هایی که درصد پیشرفت آنها ٪ ۱۰۰ شده است، علامت تیک بگذارید.
=IF( A1 = 100%, "✔", "")مشاهده می‌کنید که می‌توانیم از هر ایموجی در فرمول IF استفاده کنیم و اگر بخواهیم که خروجی تابع هیچ باشد، باید از "" استفاده کنیم.
مثال ۴)
گاهی در اکسل می‌خواهیم که نتیجه محاسبه را محدود کنیم تا از یک مقداری بیشتر و یا کمتر نشود. برای مثال می‌خواهیم فرمول محاسبه عیدی را در اکسل بنویسیم.
می‌دانیم که عیدی دو برابر حقوق است به شرط آنکه از سه برابر حداقل حقوق سال مربوطه بیشتر نباشد.اگر سه برابر حقوق پایه را مبلغ ۱۲ میلیون تومان در نظر بگیرید و بنابراین هیچ کسی نباید بیش از ۱۲ میلیون تومان عیدی بگیرد. اگر در سلول A1 حقوق پایه کاوه وارد شده باشد، فرمول عیدی کاوه در اکسل اینگونه خواهد شد:
=IF( A1 * 2 > 12000000, 12000000, A1 * 2)مثال ۵)
یکی از نکته‌های مهم تابع IF اکسل، اضافه کردن شرط‌های زمان و تاریخ میلادی در آن است.
فرض کنیم که می‌خواهیم اگر کسی بعد از ساعت ۸ صبح ورودش ثبت شد، برای او تاخیر در ورود (Late) بزنیم و گرنه بزنیم On Time. برای نوشتن ساعت 8:00 در هر فرمولی اکسلی باید از تابع TIMEVALUE استفاده کرد:
=IF( A1 > TIMEVALUE("8:00"), "Late", "On Time")
نکته 2: فقط خروجی تابع IF می تواند یک مقدار باشد و نمی تواند رنگ و ... باشد. اگر بخواهید که سلول ها را بر اساس یک شرط رنگی کنید، از ابزار Conditional Formatting اکسل استفاده نمایید.
نکته 3: اگر زبان کامپیوتر شما در CONTROL PANEL بر روی فارسی باشد، شما باید در فرمول ها به جای علامت کاما از علامت سمی کالن ";" استفاده نمایید.
شما هم تجربه یا دیدگاه خود را بنویسید:
سلام من میخوام فرمولی بنویسم که:اگرموجودی چیزی کمترازرقم خاصی شد آلارم خاصی دریافت کنم مثلا رنگی براش تعریف کنم
در همین سایت مقاله «رنگی کردن سلولها» را با Conditional Formatting بخوانید.
میخواستم در یک سلول اگر عدد 1 امد مقدار 2000 اگر 2 امد مقدار 1000 و اگر مقدار 3 تایپ شد مقدار 3000 را نشان دهد
این تابع با IF به شکل زیر می شود:
اما معمولا این مثالهای ساده را که از ۱ و ۲ و ۳ و ... است را با تابع CHOOSE اینگونه می‌نویسیم:
و در نسخه های جدید اکسل می‌توانیم از IFS, SWITCH هم برای نوشتن این سوال استفاده کنیم.
باسلام و خسته نباشین. من تابع if میخام که اگه ساعت ورود پرسنل یک اداره از ۷.۱۵ دیرتر بود تاخیر در ورود. و اگر از ساعت ۲.۱۵ زودتر بود تاجیل در خروج. و از ساعت ۲.۱۵ بیشتر بود اضافه کار برام محاسبه کنه ممنونم
این سوال مشهوری است و مثال ۵ همین مقاله اضافه شد.
سلام. ممنون از توضیح خوب و دقیق
من همین کار ها رو کردم ولی کلا ارور فرمول میده. در قسمت true یا false نمیشه فرمول گذاشت؟
احتمالا به جای علامت کاما در فرمولها باید علامت ; و یا ؛ بگذارید.
سلام و درود خدمت شما
میبخشین اگه بخوام بنویسم داده های خروجی من اگه بزرگتر و مساوی یک شد برام نشون بده رو چحوری فرمولشو بنویسم؟ ممنون میشم اگه کمکم کنین
خیلی عالی بود ممنون.
سلام
من محاسباتی دارم به این صورت که در چند شیت مختلف دو عدد بر هم تقسیم میشن و در نهایت جواب اعداد به دست آمده در همه ی شیت ها در شیت دیگه ای(به نام شیت میانگین) ازشون میانگین گرفته میشه. برای اینکه جواب تقسیم هادر شیت آخر(شیت میانگین) فراخوان بشه از تابع vlookup استفاده کردم ولی مشکلی که دارم این هست که اگه در یکی از شیت ها مقدار صورت، مخرج و یا هر دو صفر باشه، تقسیم به ما جوابی نمیده و در نهایت در شیت آخر که همه ی اعداد فراخوان و از آن ها میانگین گرفته میشه دیگه جوابی به من نمیده(به خاطر غیرقابل محاسبه بودن یکی از تقسیم ها) برای این مشکل چیکار باید کرد؟
ممنون
سلام من دوتا sheetدارم میخوام وقتی داخل f3از sheetاول عدد ۱ وارد
در sheet دوم اگر در خانه F3 عدد ۱ وارد شد پیغام خطا بدهد.
با سلام ممنون از مطلب مفیدتون. اگر بخواهیم حقوق بیشتر از ۳۰،۰۰۰و کارکرد بالا ۵۵ساعت را ۵٪مالیات درغیر اینطورت۳٪مالیات حساب کنیم چطوری میتونیم فرمول نویسی کنیم،؟ممنون میشم پاسخ بدید.
این مقاله با همین قصد نوشته شده است !
با عرض سلام و وقت بخیر
من می خواستم فرمولی در اکسل بنویسم اگر راهنماییم کنید ممنون میشم:
ستون هایی رو با استفاده از دیتا ولیدیشن نامگذاری کردم
حالا میخوام مثلا اگر مقدار ستون یک برابر با نام شخص و مقدار ستون 2 برابر با عملی که انجام شده باشد در ستون 4 مجموع اعداد ستون سه را نشان دهد
اصلا چنین امکانی وجود دارد؟؟؟؟
باسلام
در کدنویسی vbaاگر برای یک سلول شرط گذاشتیم و شرط آن سلول رعایت نشد .پیغامی میدهد چه کدی باید بنویسم که اگر سراغ سلول بعدی رفتیم دیگه پیغام مربوط به سلول قبلی نمایش داده نشه؟
باسلام
در کدنویسی vbaاگر برای یک سلول شرط گذاشتیم و شرط آن سلول رعایت نشد .پیغامی میدهد چه کدی باید بنویسم که اگر سراغ سلول بعدی رفتیم دیگه پیغام مربوط به سلول قبلی نمایش داده نشه؟
من دارم یک جدول اززشیابی را پر میکنم به این شکل که دارم توی یک جدول اسامی و معیار که اسمش را میگذارم جدول یک عددهای ارزشگذاری را وارد میکنم
و با استفاده از فرمول vlookup اعداد وارد شده در جدول یک توی جدول دو که جدول نهایی هم هست مینشیند.
حالا مشکلم اینه که توی جدول دو وقتی برای یک نفر عدد گذاری میکنم مثلا میدم 6 (مثلا یعنی عالی)
در تمام سلهای مربوط به اون رکوردچون فرمول vlookup یکسان هست پس در زیر تمام خونه های اون اعم از عالی تا ضعیف عدد 6 درج میشه
درصورتیکه میخام وقتی نمره به یک نفر 6 میدهم توی یک خانه ای خونه های بعدی که مثلا خیلی خوب تا ضعیف هستند دیگه چیزی درج نشه توش.
میخاستم کمکم کنید.
با سلام
2 تا سلول دارم A و B در سلول A هر بار عددی وارد میکنم . میخوام سلول B همیشه کمترین عدد وارد شده در سلول A رو بعنوان کمترین عدد وارد شده داشته باشه . یعنی اگه عدد جدید وارد شده در سلول A بزرگتر از خودش باشه تغییری نکنه اما اگه عدد جدید کوچکتر بود در B ذخیره بشه .
ممنون میشم راهنمایی بفرمایید .
سلام
من میخوام فرمول شرطی برای تاریخ بنویسم که اگه تاریخهای موجود در جدول، بعدتر از تاریخ30-6-1399 بودبرام ستاره دار بشه
شما باید / بهتر است تاریخ را به صورت یک عدد یعنی 13990630 بنویسید و سپس به راحتی می توانید if را بنویسید.
برای حذف علامت - در بین تاریخ ها از ابزار Replace اکسل استفاده کنید تا همه به صورت عدد شوند. البته باید توجه داشته باشید که باید ماه و روز به صورت 2 رقمی باشند.
سلام
حقوق و دستمزد درست کنم. به این صورت که؟
شماره ۱: حقوق ثابت ۲۵۰۰۰۰۰ تومان
تبصره ۱٫۱: درصورت محقق شدن ۴۰الی۵۰ درصد از تارگت ، جقوق ثابت نصف می گردد.
تبصره ۱٫۲: درصورت محقق شدن زیر ۴۰ درصد از تارگت ، حقوق ثابت ۳ درصد محاسبه می گردد.
شماره ۲: پورسانت به صورت پلکانی
تبصره ۲٫۱: درصورت محقق شدن پورسانت بین ۰تا ۵۰ درصد تارگت ، ۱درصد
تبصره ۲٫۲: درصورت محقق شدن پورسانت بین ۵۱تا ۷۰ درصد تارگت ، ۱٫۵درصد
تبصره ۲٫۳: درصورت محقق شدن پورسانت بین ۷۱تا ۱۰۰ درصد تارگت ، ۲درصد
تبصره ۲٫۴: درصورت محقق شدن پورسانت بیشتر از تارگت ، ۳درصد
شماره ۳: پاداش فصلی به صورت پلکانی
تبصره ۳٫۱: از جمع تارگت سه ماه یک فصل درصورت محقق شدن یک سوم آن ، ۰٫۵ درصد
تبصره ۳٫۲: از جمع تارگت سه ماه یک فصل درصورت محقق شدن دو سوم آن ، ۰٫۷۵ درصد
تبصره ۳٫۳: از جمع تارگت سه ماه یک فصل درصورت محقق شدن کل تارگت ، ۱ درصد
تبصره ۳٫۴: از جمع تارگت سه ماه یک فصل درصورت محقق شدن بیشتر از تارگت ، ۱٫۵ درص
اینکار را بهتر است با تابع vlookup انجام دهید. مقاله های vlookup که در همین سایت است را با دقت بخوانید. دقیقا همین مساله حل شده است
با سلام و تشکر.خیلی ممنون از لطفتون و توضیحات شفاف و کافی و قابل قبولتون.
استفاده کردم و مشکل چند ساله ام بابت توابع شرطی حل شد
با سلام
بر اساس عبارت نوشته شدە در یک سلول، عددی متناظر با آن را کە در لیستی دیگر قرار دارد انتخاب کند. مثلن با دیدن عبارت"C/30"، عدد ٣٠ را نمایش دهد.
سلام / مقاله VLOOKUP را در همین سایت مطالعه کنید.
با سلام
میخام یه فرمولی بنویسم که اگر عددی که توی اون سلول نوشته بالاتر از 1 بود یه جمعی رو انجام بده مثلا دوتا سلول قبلی رو جمع کنه و 1 رو ازش کم کنه
لطفا مقاله را حداقل یکبار بخوانید. همه چیز همین جا توضیح داده شده است !!
با سلام ، من میخواستم بیشتر از یک if تعریف کنم فرضا بگم اگر 6 بود بنویس 7.30 اگر 12 بود بنویس 13.30 و اگر18 بود بنویس 19.30 این رو چطور میتونم بنویسم
=IF(A1 = 6, 7.30, IF(A1 = 12, 13.30, IF(A1 = 18, 19.30, "error")))
البته در نسخه 2019 تابع IFS داریم که کار کمی ساده تر می شود:
=IFS(A1 = 6, 7.30, A1 = 12, 13.30, A1 = 18, 19.30)
و یا از تابع SWITCH استفاده کنید: (در اکسل 2019)
=SWITCH(A1, 6, 7.30, 12, 13.30, 18, 19.30, "error")
توجه مهم: اگر تعداد حالت های شما بیشتر باشد، باید / بهتر است که از vlookup استفاده کنید.
باسلام
فرمولیمیخوام که به این شکل باشه
اگر سلول فلان زیر صفر بود نشون بده بدهکار و اگر بالای صفر بود نشون بده بستانکار
باید بتوانید این فرمول را خودتان پس از خواندن این مقاله بنویسید. فقط یک نکته را مد نظر داشته باشید، بهتر است سوالتان را اینگونه در ذهنتان مطرح کنید:
اگر سلول فلان زیر صفر بود نشون بده بدهکار وگرنه نشون بده بستانکار
مفید خلاصه و ساده بیان شد
ممنون بابت مطلب
با عرض سلام و ادب
از آموزش کامل شما در این مبحث تشکر می کنم
در صورت امکان مبحث تغییر رنگ سلول را بر اساس شرط نیز آموزش دهید.
همچنین آموزش جدا کردن جزء صحیح یک عدد اعشاری
خیلی خیلی ممنونم
کتاب Conditional Formatting که در سایت است را ببیند.
اگر جمله شرط مقایسه زمان داشته باشد مثلا بزرگتر از ساعت ۸، زمان را چطور باید وارد کنیم؟!
در مقاله تاریخ و زمان در اکسل این مورد دقیقا توضیح داده شده است اما به صورت خلاصه باید بنویسید:
=IF( A1 > TIMEVALUE("8:30"), "ok", "NOT")
سلام ببخشید از کدام تابع میتوانم استفاده کنم که مثلاً اگر در سلول a1 یکی از گزاره های a یا aa یا ab و... بود عنوان حرف کلاس a ، اگر b یا bb یاbc بود گزاره کلاس b و اگر c یا ca یا ccو ... بود گزاره کلاس c بود و چند شرط دیگه
آیا میشه از اکسل استفاده کرد
سلام / مقاله VLOOKUP را در همین سایت مطالعه کنید.
سلام من واسه پایانامم نیاز به کمک دارم
میخوام مثلا بین A1 تا A90 بگرده هر چند تا 5 بود تعدادشو بهم بگه. یه دنیا ممنون میشم راهنمایی کنید
باسلام من در اکسل یه لیستی دارم که میخوام از اون لیست تعداد سلولهایی که شامل کلمه فروش هستند رو بهم بگه.ممنون میشم اگه راهنماییم کنین
یه سوالی دارم
آیا میشه تابع شرطی بنویسیم که اگر در یک سلول دیگه فلان اتفاق بیوفته رنگ سلولی که توش هستیم مثلا أبی یا رزرد بشه ؟
کتاب Conditional Formatting که در سایت است را ببینید.
مطلب آموزشی شما بسیار شیوا ، روان و فوق العاده ساده و حرفه ای بیان شده است و فقط به یک بار خواندن ومطالعه نیاز دارد . سپاس از شما
بسمه تعالی
سلام
چقدر قشنگ و قابل فهم آموزش دادید. تنها سایتی هست که بین هزاران سایت آموزشی به این قشنگی و مفهومی آموزش داده.
خیلی عالی توضیح داده بودید ممنون
با سلام
چطور میشه حروفات فارسی را ارزش گذاری کرد؟
مثلا س = 10 و ل=5 و الف=2 و م=3 که اگه سلام نوشته شد خودش جمع بزنه بنویسه 20.
مثل حروف ابجد
با سلام من دنبال تابع میگردم که در یک ستون اعداد دارم اگر در عدد سلول برابر ان قسمت از ستون بود درست بگه وگرنه غلط بگه
این یک IF بسیار ساده است که در این مقاله دقیقا آن را آموزش داده ایم.
با سلام
می خواهم در جدولی موجودی انبارم کمتر از 2عدد شد آلارم دریافت کنم.امکانش هست توضیح دهید