همه چیز در مورد تابع if و ifs اکسل به همراه مثال

[cris]

تابع IF و IFS از مهمترین و ساده ترین توابع اکسل هستند و می خواهیم این دو تابع را به زبان ساده و کامل توضیح دهیم !!



تابع IF و IFS از مهمترین و ساده ترین توابع اکسل هستند و می خواهیم این دو تابع را به زبان ساده و کامل توضیح دهیم !!

تابع IF:
به تصویر زیر دقت کنید:
آرگومانهای تابع IF تابع IF از سه آرگومان تشکیل شده است :

نکته : آرگومانهایی که پررنگ تر نوشته شده اند باید حتما دارای مقدار باشند.
۱- Logical_test
در این باکس باید شرط خود را بنویسید و مثلا اگر میخواهید مقدار موجود در سلول A1 را بررسی کنید که ببینید از عدد ۵ بزرگتر است یا نه باید بنویسید:

A1>5

۲- Value_if_true

کاری را که میخواهید اگر شرط برقرار بود انجام شود را در این قسمت وارد کنید . مثلا اگر می خواهید در صورتی که عدد موجود در سلول A1 بزرگتر از ۵ بود نوشته شود “بزرگتر” باید همین کلمه عبارت را در آرگومان دوم را بنوسید

۳-Value_if_false

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

=IF(A1>5,”بزرگتر”,”کوچکتر”)

که تصویر تابع هم به شکل زیر می شود:

شکل تکمیل شده تابع IF

حالا تصور کنید می خواهید شروط بیشتری را مورد بررسی قرار دهید یعنی چی ؟ مثلا می خواید تو سلول B1 فرمولی بنویسید که اگر عدد موجود در سلول A1 برابر ۵ بود نوشته شود “مساوی” اگر بزرگتر از ۵ بود نوشته شود “بزرگتر” اگر کوچکتر از ۵ بود نوشته شود “کوچکتر”

خب مشکلی وجود دارد تابع IF در شکل معمول فقط دو شرط را قبول می کند . راه حل چیست ؟

در ورژنهای قبلی اکسل باید از IFهای تودر تو استفاده می کردید و در واقع فرمول را اینچنین می نوشتید:

=IF(A1>5,”بزرگتر”,IF(A1<5,”کوچکتر”,”مساوی”))

IFهای تو در توی بیشتری هم می توان به همین روش استفاده کرد ولی خبر خوب این است که در ورژنهای جدید اکسل می توان از تابع IFS برای حل این مشکل استفاده کرد و تابع فوق را به شکل زیر نوشت:

[Ahmad1587]

تابع if یکی از پرکاربردترین توابع در نرم افزار اکسل می باشد. If یک تابع ساده ای است که کاربران خیلی آن را دوست دارند زیرا به آنها قدرتی می دهد تا فایل اکسل واکنش گرایی داشته باشند. با تابع if شما می توانید زندگی را به صفحه گسترده تان بیاورید.
اما یک تابع if همیشه منجر به تابع if دیگری خواهد شد.
آیا if های تو در تو بد است؟ آیا گاهی اوقات به آنها نیاز است؟ جایگزین آن چی هست؟
این مقاله را بخوانید تا جواب این سوال ها و سوالات دیگر را بدانید.


1- ساختار تابع if

قبل از اینکه درباره ی if های تو در تو صحبت کنیم ، اجازه دهید تا به طور سریع و مختصر ساختار تابع if را بررسی نماییم.

=IF (logical_test, [value_if_true], [value_if_false])

تابع if یک آزمونی را برگزار می نماید و با توجه به نتیجه آن خروجی متناسب را نمایش می دهد. این خروجی بستگی به درست یا نادرست بودن آزمون دارد.
آرگومان دوم و سوم چون در داخل براکت قرار دارند لذا اختیاری می باشند. یعنی اینکه اگر مقداری هم اختیار نشود نرم افزار خطا نخواهد داد. با این وجود برای آنکه محاسبات مد نظر شما اجرا شود باید مقادیری برای آنها در نظر بگیرید.


برای توضیحات بیشتر، از تابع if برای چک کردن قبولی استفاده کرده ایم. نمره قبولی حداقل 65 در نظر گرفته ایم.

تابع if در اکسلسلول D2 در این مثال شامل این فرمول می باشد:

=IF(C2>=65,”قبول”)

این فرمول را می شود به این صورت ترجمه خودمانی کرد: اگر نمره داخل سلول C2 حداقل 65 بود ؛ “قبول” را برگردان.
اگر نمره وارده کمتر از 65 باشد تابع مقدار False را برمی گرداند، چون ما برای حالت false تابع مقداری در نظر نگرفتیم. برای آنکه “مردود” را برای حالت نمره کمتر از 65 نمایش دهد می توانیم عبارت “مردود” را در آرگومان سوم آن بنویسیم. در این حالت تابع به این شکل خواهد گشت:

=IF(C3>=65,”قبول”,”مردود”)

2- معنی تو در تو چیست؟

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

برای مثال؛ تابع Today در تابع Month قرار دارد، یا به عبارتی دیگر تابع Today آرگومانی از تابع Month خواهد بود.

=Month (Today ())

خروجی تابع Today تاریخ امروز خواهد بود، از طرفی Month یک سریال عددی تاریخ را دریافت می کند و در قبال آن عددی را به عنوان ماه نمایش می دهد.این سریال عددی یا همان تاریخ را تابع Today به تابع Month می دهد.
3- یک مثال ساده از If تو در تو

می آییم و یک if را به جای یکی از آرگومان های تابع if اصلی استفاده می کنیم.
برای مثال؛ در ادامه مثال قبلی ، بررسی موضوع قبولی یا مردودی را در گرو تکمیل نمودن مقدار آن سلول قرار می دهیم. یعنی اینکه تا سلول تکمیل نشود و عددی در آن قرار نگیرد موضوع قبولی یا مردودی مورد بررسی قرار نمی گیرد.
لذا فرمول را به این نحو می نویسیم:
=IF(C3=””,”تکمیل نشده”,IF(C3>=65,”قبول”,”مردود”))
به زبان ساده این فرمول بیان می کند که؛ اگر در سلول مد نظر مقدار وجود نداشته باشد مقدار خروجی اش True خواهد بود که در این صورت آرگومان دوم تابع نمایش داده خواهد شد. در صورتی هم که مقداری در سلول وجود داشته باشد آنگاه روند بررسی قبولی یا مردودی انجام خواهد گرفت.
4- توابع If تو در تو برای چند بازه ای ها

اغلب در if های تو در تو شما با چند بازه روبرو هستید. یعنی اینکه ابتدا باید شما بازه اول را بررسی کنید و سپس بازه بعدی تا به مطلوب تان برسید. مثل بررسی نمرات، هزینه های خرید، نرخ مالیات و … که یک مقدار عددی وارد می کنیم، با بررسی بازه های مختلف مقدار مدنظر نشان داده می شود.
برای آن که فرمول مان خوب باشد یا اینکه از نظر خوانایی سخت نباشد می آییم بازه های آن را مرتب می کنیم. یعنی اینکه دسته بندی داده ها را از بزرگ به کوچک یا از کوچک به بزرگ مرتب می کنیم.
برای مثال؛ نمرات را در جدول زیر به این صورت دسته بندی می کنیم.


if های تو در توبا این تعیین وضعیت موضوع شفاف می گردد که باید به ترتیب بازه ها را مورد بررسی قرار دهیم. ابتدا بازه اول را بررسی می کنیم، اگر در این بازه قرار داشت پس نمره بازه اول را نمایش می دهد و به همین ترتیب تا بازه آخر ادامه پیدا می کند. ما می توانیم اولین بخش تابع if را بنویسیم:

=IF(C5<64,”F”)

این فرمول نمره “F” را مورد بررسی قرار می دهد؛ بعد از آن می رسیم به”D” ، لازم هست تا یک وضعیت دیگر را اضافه نماییم:

=IF(C5<64,”F”,IF(C5<73,”D”))

به سادگی نمره “D” را مورد بررسی قرار دادیم. یک تابع If دیگر در ارگومان False تابع اول قرار دادیم. به این معنی که تابع اول بازه اول را بررسی می کند اگر مقدار وارد شده در این بازه نباشد پس خروجی False خواهد داشت و آرگومان سوم آن باید انجام شود. در آرگومان سوم از تابع if دیگری استفاده کردیم تا بازه دوم را بررسی کند. دلیل اینکه به این راحتی می توانیم بازه ها را بررسی کنیم مرتب کردن داده ها و بازه ها می باشد.

به همین ترتیب بازه های مختلف را با توابع if تکمیل می کنیم:
=IF(C5<64,”F”,IF(C5<73,”D”,IF(C5<85,”C”)))
=IF(C5<64,”F”,IF(C5<73,”D”,IF(C5<85,”C”,IF(C5<95,” B”,”A”))))

خروجی نهایی این فرمول نویسی به شکل زیر خواهد شد:

تابع if در اکسل


5- نمودار منطقی if های تو در تو
بسیاری از فرمول ها از داخل به خارج حل می شوند. دلیل آن این است که خروجی توابع درونی تر ، ورودی توابع بیرونی هستند.
همچنین If های تو در تو نیز از این جریان منطقی تبعیت می کند. چون در حقیقت if های بیرونی دروازه ای برای if های درونی هستند. یعنی اینکه زمانی توابع بیرونی اجرا می شوند که توابع درونی محاسبه شده باشند.
به طور مثال؛ برای مثال نمرات، دیاگرام آن به صورت زیر می تواند باشد.
دیاگرام تابع if





6- مشاهده روند منطقی محاسبات توابع
در سیستم عامل های ویندوز ، کاربر می تواند روند محاسبات مرحله به مرحله نرم افزار اکسل را مشاهده نمایید. این امکان زمانی بیشتر قابل لمس خواهد بود که فرمول نویسی پیچیده باشد یا اینکه خروجی مطلوب توسط فرمول نمایش داده نمی شود.
این امکان نرم افزار اکسل را می توان در Formulas > Evaluate Formula مشاهده نمایید.



7- بررسی سریع نتیجه
وقتی که قسمتی از فرمول را در فرمول بار انتخاب کردید می توانید با زدن کلید F9 از اکسل بخواهید تا فقط همان قسمت را محاسبه نمایید و خروجی آن را نیز نمایش دهد.
این یک روش قدرتمند برای تایید آنچه که فرمول باید انجام دهد می باشد. یک تکنیک سریع آن است تا در نوار راهنمای فرمول آرگومان مد نظر را انتخاب کنیم سپس با زدن کلید F9 آن قسمت را محاسبه و چک نماییم.
ترفند کلید F9 در اکسل


8- محدودیت هایifهای تو در تو
اکسل برای if های تو در تو محدودیت هایی در نظر گرفته است. تا اکسل 2007 تا 7 سطح اجازه استفاده از توابع تو در تو بود. در نسخه های بالاتر از 2007 تا 64 سطح این امکان گنجانده شده است.
منظور از سطح یعنی اینکه تابعی در تابع دیگر مورد استفاده قرار بگیرد.
اگرچه این امکان قرار داده شده است ولی شما مجبور به استفاده از تمام سطح های آن نیستید. معمولا هر چه تعداد سطح ها افزایش یابد فهم آن سخت تر و احتمال خطا زیاد می شود.



9- بستن پرانتزها به صورت حرفه ای

یکی از شانس هایی که در if های تو در تو وجود دارد تطابق یا “balancing” پرانتزها می باشد. وقتی پرانتزها به درستی تکمیل نشده باشد فرمولتان ناقص خواهد بود.
خوشبختانه ، اکسل یک جفت ابزاری را برای کمک به کاربران ایجاد کرده است تا در هنگام فرمول نویسی مطمئن شوند پرانتزها تطابق دارند.
اول؛ زمانی که بیشتر از یک پرانتز داشته باشید ، پرانتزها به صورت رنگی کد گذاری می شوند تا پرانتز باز شده با بسته شده به یک رنگ نمایش داده شوند.
دوم ( امکان بهتر)؛ وقتی یک پرانتز را می بندید اکسل جفت آن را برجسته می کند. همچنین زمانی که با جهت ها در فرمول جابجا می شوید با رسیدن به هر پرانتز اگر جفتی داشته باشد ، جفتش به صورت برجسته نمایش داده می شود.
لذا اگر هر کدام از حالات بیان شده نبودند پس نقصی در پرانتز هایمان وجود دارد.



10- رفیق همیشگی

وقتی در حال حرکت یا ویرایش فرمول هستید، برای توابع نوار راهنمایشان (Screen Tip)نمایش داده می شود. با آن حرکت و انتخاب آرگومان ها راحت تر می شود.
کافی است بر روی آرگومان مد نظر در نوار راهنما کلیک نمایید تا آن آرگومان در فرمول انتخاب گردد.

نوار راهنما در فرمول نویسی اکسل

11- متن با عدد فرق می کند!!
شاید موضوع بدیهی به نظر برسد ولی یادآوری سریعی درباره این موضوع داشته باشیم بد نیست. وقتی از IFهای تودرتو استفاده می کنید حواستان باشد تا مقدار عددی را با متنی تطابق ندهید. به مثال زیر توجه نمایید:

=IF(A1=”100″,”قبول”,”مردود”)

در این فرمول قرار است تا با توجه به مقدار سلول A1 قبولی یا مردودی مورد بررسی قرار بگیرد. حال آیا این فرمول صحیح می باشد؟
باید گفت که خروجی آرگومان اول آن در هر حالتی (حتی اگر نمره قبولی را دریافت کند) False خواهد بود. زیرا مقدار “100” با عدد 100 یکسان نیست. حالت درست آن به این صورت می باشد:

=IF(A1=100,”قبول”,”مردود”)

12- افزایش خوانایی

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

تابع if در اکسل

حالا با استفاده از کلید ترکیبی Alt+Enter قبل از تایپ آرگومان سوم تابع if ، تابع بعدی را می توانیم در خط بعدی بنویسیم. با این حال ویرایش آن خیلی راحت خواهد گشت:

فرمول نویسی در اکسل

13- افزایش عملکرد توابع if

از یک طرف If های تو در تو خیلی قدرتمند هستند، اما با اضافه کردن سطح بیشتر خیلی سریع بغرنج می شوند. یکی از راه هایی که می توانیم از افزایش سطح های آن جلوگیری نماییم ، ترکیب توابع And و OR در تابع IF می باشد. این توابع خروجی True/False که ورودی تابع IF می باشد.

برای مثال؛ در مثال زیر قرار است تا در ستون D ردیف هایی که رنگ آنها “قرمز” و سایزشان “کوچک” می باشد علامت * نشان دهد.

فرمول نویسی در اکسل

می توانیم این کار را با if های تو در تو انجام دهیم:

=IF(A6=”قرمز”,IF(B6=”کوچک”,”*”,””),””)

می شود این فرمول را به نحو زیر نیز نوشت:

=IF(AND(A6=”قرمز”,B6=”کوچک”),”*”,””)

حتی میتوان این فرمول را با OR گسترش داد تا دو رنگ “قرمز” یا“آبی” و سایز “کوچک” را مشخص نماید.

=IF(AND(OR(A4=”قرمز”,A4=”آبی”),B4=”کوچک”),”*”,””)

این کار را می توان با if های تو در تو نیز نوشت ولی خیلی طولانی و پیچیده خواهد شد. این موضوع را می توانید امتحان کنید.
14- تابع Max

گاهی اوقات می توانید با توابع Max و Min کارهای خارق العاده ای انجام دهید تا از تابع if استفاده نکنید.
برای مثال؛ در محاسباتی که انجام داده اید باید این موضوع را بررسی نمایید تا اگر محاسبات انجام گرفته کمتر از صفر می باشد صفر نمایش دهد و اگر بزرگتر از صفر بود مقدار آن را نمایش دهد. به عنوان دیگر خروجی نمی تواند منفی باشد.
با تابع Max می توان این کار را بدون تابع if انجام داد:
=MAX(A,0)
این تکنیک فقط اعداد مثبت را نمایش می دهد و برای اعداد منفی خروجی به صورت صفر نمایش می دهد.

15- از عبارت های منطقی استفاده کنید.

در نرم افزار اکسل True به صورت عدد 1 و False به صورت عدد صفر می باشد. پس می توان بدون استفاده از تابع if محاسبات را انجام داد.
دو حالت زیر را مشاهده نمایید:

=IF(E3=”red”,100,IF(E3=”blue”,200,IF(E3=”green”,30 0,IF(E3=”orange”,400,500))))=(E3=”red”)*100+(E3=”blue”)*200+(E3=”green”)*300+( E3=”orange”)*400+(E3=”purple”)*500

در فرمول دوم بدون استفاده از تابع if مواردی که باید بررسی شود را انجام داده ایم.

وبینار فرمول نویسی در اکسل را از دست ندهید.

[ali65e]

سلام
اساتید بزرگوار اکسلی دارم که در یک سلولش اختلاف دو سلول محاسبه می شود .در بعضی مواقع که دانش اموزم غایب است فرمول اختلاف ارور می دهد.نیازمند فرمول ایفی هستم که اگر در سلول غایب نوشته بود ء سلول اختلاف خالی گذاشته شود

[misammisam]

سلام
اساتید بزرگوار اکسلی دارم که در یک سلولش اختلاف دو سلول محاسبه می شود .در بعضی مواقع که دانش اموزم غایب است فرمول اختلاف ارور می دهد.نیازمند فرمول ایفی هستم که اگر در سلول غایب نوشته بود ء سلول اختلاف خالی گذاشته شود

=iferror(b1-a1;"")