صفر تا صد آموزش تابع IF در اکسل با 4 مثال کاربردی
تعریف تابع IF
اگر دقت کنید ما در همه جنبه های زندگی نیاز به انتخاب بین دو یا چند چیز داریم و این انتخاب معمولا وابسته به اگرهایی هست که آن را تحت تأثیر قرار می دهد.
لذا از این جهت اگرهای زیادی در زندگی وجود دارد که نتیجه آن منجر به انتخاب بین دو یا چند چیز می شود، معادل این موضوع در تحلیل داده هادر بحث آموزش اکسل نیز وجود دارد و اکسل به عنوان یک ابزار تحلیل داده این اگرها را با تابع IF در اکسل برای ما پیاده می کند.
گاهی شرط های ما بسیار ساده هستند و گاهی شرط ها چندین جنبه را بررسی می کنند .
به عنوان مثال یک شرط ساده در نمره دانش آموزان وجود دارد که اگر نمره بزرگتر یا مساوی 10 باشد معادل قبولی است و در غیر این صورت مردود تلقی می شود.
دقت کنید که در خصوص اهمیت تابع IF همین بس که در اکثر نرم افزارها و تمامی زبان های برنامه نویسی این تابع با ساختار تقریبا مشابه وجود دارد و در نتیجه یادگیری آن بسیار حائز اهمیت است.
فهرست مطالب
نحوه نوشتن توابع:
برای نوشتن هر تابعی دو حالت وجود دارد که هرکدام مزایا و معایبی دارد:
حالت اول : استفاده از پنجره Function Arguments
از سه طریق می توانید این پنجره را باز کنید.
a. روی گزینه fx کنار فرمولا بار کلیک کنید.
b. بعد از وارد کردن علامت = و نوشتن تابع مورد نظر و باز کردن پرانتز Shift+F3 را بزنید.
c. بعد از وارد کردن علامت = و نوشتن تابع مورد نظر و باز کردن پرانتز Ctrl+A را بزنید.
مزایا
– برای افراد مبتدی استفاده از این پنجره جهت فرمول نویسی کار را راحت تر می کند چرا که نیازی نیست جداکننده بین هر آرگومان را تایپ کنند و یا اینکه اگر در یکی از قسمت ها متنی وارد کردند بطور خودکار داخل دبل کوتیشن قرار می گیرد.
– روی هر آرگومان که قرار می گیرید توضیحات لازم در پایین این پنجره نمایش داده می شود و همینطور محدودیت ها و مقدار پیش فرض را در توضیحات مربوطه نمایش می دهد.
– نتیجه هر قسمت را جلوی آن می توان دید و همینطور نتیجه کل فرمول نیز در این پنجره نمایان می شود.
– در پایین پنجره با زدن گزینه Help on this function می توانید مقاله مربوط به تابع مورد نظر را در سایت مایکروسافت ملاحظه کنید.
معایب
– در مواقعی که قصد نوشتن فرمولهای ترکیبی داریم بیشتر ازین که کار ما را ساده تر کند دست و پا گیر است و گاهی فهم فرمول را سخت تر می کند.
– سرعت نوشتن فرمول از طریق این پنجره کمتر از حالتی است که فرمول را مستقیما وارد می کنیم.
– استفاده از صفحه کلید برای انتخاب محدوده مورد نظر را سخت تر می کند.
حالت دوم : نوشتن فرمول بدون باز کردن پنجره Function Arguments
مستقیما فرمول را درون سلول وارد می کنید.
مزایا
– سرعت نوشتن فرمول زیادتر است و براحتی می توانید با صفحه کلید محدوده مورد نظر را انتخاب کنید.
– درک فرمول ها راحت تر است و از این جهت که می توان فرمول را در چند خط نوشت فرمول بیشتر قابل درک است.
– اگر روی نام تابع در کادر نمایش داده شده زیر آن کلیک کنید توضیحات سایت مایکروسافت در سمت راست صفحه اکسل نمایش داده می شود و در صورت وجود فیلم آموزشی، آن را نیز خواهید دید.
معایب
– مقدار پیش فرض هر آرگومان و توضیحات هر قسمت را نمی توانید ببینید.
– نتیجه هر آرگومان و نتیجه کل فرمول را نمی توانید ببینید مگر اینکه هر قسمت را انتخاب کنید و کلید F9 را بزنید.
– برای وارد کردن متن درون فرمول خودتان باید علامت دبل کوتیشن ” را وارد کنید.
نکته : به طور کلی پیشنهاد میشود که در مراحل اولیه که تسلط کافی روی فرمول ندارید از پنجره Function Arguments راهنمایی بگیرید ولی به مرور عادت کنید فرمول ها را بدون استفاده از این پنجره بنویسید تا در نوشتن فرمول های ترکیبی آزادی عمل و سرعت عمل بیشتری داشته باشید.
نوشتن تابع IF
یکی از توابع و فرمول های پر کاربرد اکسل تابع IF میباشد که بیایید به ساختار ساده این تابع در اکسل نگاهی بیندازیم و با قسمت های مختلف آن در حالت ساده آشنا شویم.
IF(logical_test,[value_if_true],[value_if_false])
logical_test: در این قسمت شرط مورد نظر ما قرار می گیرد که می تواند یک مقدار ثابت یا یک مقایسه یا یک یا چند فرمول ترکیبی باشد ولی عموما یک مقایسه هست و در نتیجه از عملگرهای مقایسه (مانند = ، > ، < ، => ، =< ، <>) استفاده می شود.
دقت کنید که خروجی هر شرط/مقایسه در اکسل در صورتی که برقرار باشد مساوی True خواهد بود و در صورت برقرار نبودن شرط/مقایسه خروجی آن False می شود.
نکته برای حرفه ای ها: از آنجایی که عبارت False در اکسل مساوی عدد صفر است اگر در این قسمت شرطی را بنویسیم که خروجی آن عدد صفر باشد در نتیجه مانند آن است که شرط برقرار نیست یعنی اگر فرمولی بنویسیم که عددی بجز عدد صفر را خروجی بدهد(همه اعداد منفی و مثبت) معادل برقرار بودن شرط است.
value_if_true: اکسل پس از بررسی شرط ما در قسمت قبل، در صورت برقرار بودن شرط این قسمت را اجرا خواهد کرد و در این قسمت میتوانیم یک متن یا عدد یا فرمول دلخواه را قرار دهیم.
به عنوان مثال اگر فرض کنیم مقایسه/شرط ما در قسمت قبل، خروجی True یا هر عددی بجز عدد صفر بوده است، هر داده ای که در این قسمت قرار می دهیم نمایش داده خواهد شد و دیگر اکسل به قسمت بعد نمیرود و هر داده ای که در قسمت بعد قرار بگیرد بررسی نخواهد شد.
value_if_false: این قسمت فقط در صورتی که شرط ما برقرار نباشد بررسی خواهد شد و مانند قسمت قبل می تواند عدد، متن یا یک فرمول باشد.
مثال اول :
IF( True , “Yes”)=
تعجب کردید؟ بله این فرمول درست است و نتیجه کلمه Yes می شود!
خب الان احتمالا چندین سوال در ذهن شما شکل می گیرد.
چرا قسمت اول هیچ شرطی ننوشتید؟
چون فرقی ندارد که عبارت True نتیجه یک مقایسه باشد یا خودتان آن را بنویسید و همینطور با توجه به نکته حرفه ای که گفته بودیم فرمول های زیر هم نتیجه یکسانی با فرمول فوق خواهد داشت (اعداد غیر صفر معادل True است(:
IF(1,”Yes”)=
IF(-1,”Yes”)=
چرا قسمت قسمت دوم کلمه Yes داخل علامت دبل کوتیشن ” وارد شده است؟ بطور کلی در اکسل در هر تابعی اگر بخواهید عبارت متنی بنویسید باید حتما داخل علامت دبل کوتیشن قرار بگیرد.
چرا قسمت سوم فرمول را وارد نکردید؟ همانطور که در آرگومان های تابع مشاهده کردید قسمت دوم و سوم داخل علامت براکت قرار گرفته است و در توابع اکسل این به معنی اختیاری بودن آن آرگومان است و در نتیجه چون اختیاری بود یعنی اکسل مقدار پیش فرض برای آن دارد و مقدار پیش فرض عبارت False هست.
اگر در فرمول فوق، شرط ما برقرار نبود یا قسمت اول را عبارت FALSE یا عدد صفر وارد می کردیم نتیجه فرمول چی می شد؟ از آنجایی که پیش فرض آرگومان سوم عبارت FALSE است در نتیجه اگر شرط ما برقرار نباشد چون قسمت آخر را وارد نکردیم همان عبارت پیش فرض یعنی کلمه FALSE نمایش داده می شود.
IF(False,”Yes”)=
IF(0, “Yes”)=
IF(1 , ,”No”)=
بله همانطور که در قسمت قبل دیدیم قسمت آخر فرمول اختیاری بود با توجه به اینکه قسمت دوم هم داخل براکت بوده این قسمت را هم میتوانیم خالی بگذاریم
و چون عدد 1 در قسمت شرط معادل True هست و باید سیستم قسمت دوم را نمایش دهد چون مقداری وارد نکردیم طبق توضیحات قبلی که برگرفته از سایت مایکروسافت می باشد اصولا باید عبارت پیش فرض آن یعنی TRUE را نمایش دهد، اما اگر این فرمول را بنویسید می بینید که نتیجه آن مساوی عدد صفر می شود!
این بار دوباره یک چرا مطرح می شود که خود حاوی یک نکته مهم است.
چرا نتیجه فرمول عبارت TRUE نشد؟ چون یک منطقی در فرمول نویسی اکسل وجود دارد که اگر جایگاه آرگومانی را داشته باشیم ولی چیزی در آن قسمت وارد نکنیم انگار عدد صفر را وارد کرده ایم.
پس با توضیح فوق به این فکر کنید که جواب فرمول زیر چه می شود؟
( , )IF=
بله اگر توضیحات را با دقت خوانده باشید شما هم با من هم نظر هستید که جواب فرمول فوق عبارت FALSE می شود.
چرا؟ چون قسمت جایگاه شرط خالی گذاشته شده پس صفر در نظر گرفته می شود و از آنجایی که صفر معادل False بود قسمت سوم تابع باید اجرا شود که ما چون قسمت سوم را بطور کلی وارد نکرده ایم مقدار پیش فرض آن یعنی عبارت FALSE نمایش داده خواهد شد.
بطور خلاصه تا اینجا نکات زیر را یاد گرفتید:
– قسمت دوم و سوم تابع IF اختیاری است اما بطور همزمان نمیتوانید هر دو را وارد نکنید.
– در فرمول های اکسل عدد صفر معادل False و هر عددی بجز صفر معادل True است.
– اگر آرگومانی را وارد نکنیم ولی جایگاه آن را نگه داریم (یعنی جداکننده ای که برای رسیدن به آن آرگومان لازم است، را وارد کنیم) اکسل آن قسمت را، عدد صفر در نظر خواهد گرفت یعنی دو فرمول زیر از نظر اکسل یکسان است.
( , , )IF=
IF(0,0,0)=
خب حالا با درک عمیقی که از قسمت های مختلف این تابع دارید برایتان مثالهای بیشتری فراهم کردیم.
مثال دوم:
فرض کنید میخواهید فرمولی بنویسید که اگر در سلول A1 هر نمره ای وارد شد در سلول B1 مشخص کند که نمره قبولی است یا مردود.
IF(A1>=10=
“قبول”,
“مردود”)
چرا و چطور در سه خط نوشته شد؟
یکی از روشهایی که باعث میشه خیلی راحت تر قسمت های فرمول را درک کنید این است که در خطوط مجزا قرار دهید کافیه آرگومان اول را که نوشتید و جداکننده را وارد کردید با زدن کلید Alt+Enter به خط بعد بروید و بعد با زدن چندبار اسپیس قسمت قسمت دوم فرمول را وارد کنید و همین کار را برای خط بعد انجام دهید.
نکته: اسپیس اضافه بین هر آرگومان، تغییری در فرمول ایجاد نمی کند.
مثال سوم :
یک مثال کاربردی که در خصوص تابع IF می توان گفت در خصوص مقایسه مبلغ بودجه شده و مبلغ واقعی هست که در ادامه فرمولی نوشتیم که در صورتی که مبلغ واقعی بیشتر از مبلغ بودجه شده بود عبارت Over Budget نوشته شود و در غیر اینصورت عبارت Within Budget ظاهر شود.
IF(C2>B2=
” Over Budget”,
” Within Budget”)
مثال چهارم :
در ادامه مثال قبل با کمک تابع IF می توان مبلغی که بیشتر از مبلغ بودجه شده بوده را جداگانه بدست آورد.
IF(C2>B2,C2-B2,0)=
بر اساس شکل, اگر C2 بزرگتر از B2 باشد, اکسل باید هر دو سلول را از هم تفریق کند, در غیر این صورت باید داده عددی صفر (0) را قرار دهد.
نکته: قسمت سوم تابع یعنی عدد 0 را می توانید وارد نکنید و با توجه به توضیحات قبلی اگر فرمول را به صورت زیر بنویسید جواب یکسانی دریافت خواهید کرد.
IF(C2>B2=
(,C2-B2
همانطور که در مثال های بالا مشاهده کردید, تابع IF بوسیله در قسمت اول شرط را با استفاده از عملگرهای مقایسه ای ایجاد کرد.
انواع عملگرهای مقایسه ای:
بطور کلی هرجا قصد تصمیم گیری بین دو یا چند مورد با در نظر گرفتن شرط یا شرط های مورد نظر را دارید احتمالا تابع IF راهکار مناسبی باشد که البته اگر تعداد شرط ها بیشتر از چند مورد شد باید دقت کنید که گاهی ممکن است با توابع دیگری سریع تر به نتیجه یکسان برسید.
مثال پنجم :
اگر با تابع Vlookup آشنا باشید، می دانید که در قسمت دوم این تابع حتما باید داده مورد جستجوی ما در اولین ستون جدول انتخابی باشد و ستون نتیجه پس از آن باشد،
لذا گاهی اوقات که ستون نتیجه ما قبل از ستونی که داده lookup_value در آن قرار دارد تنظیم شده و نمی خواهیم جای ستون ها را عوض کنیم لازم است یک جدول مجازی در حافظه فرمول ایجاد کنیم که ترتیب ستون ها را تغییر دهیم.
یکی از روش هایی که میتوان این کار را انجام داد استفاده از تابع If بصورت آرایه ای می باشد.
طبق توضیحاتی که قبلا گفتیم اگر در قسمت اول تابع If عدد 1 بگذاریم قسمت دوم تابع اجرا می شود
و اگر عدد صفر بگذاریم قسمت سوم تابع اجرا می شود اما اگر هر دوی این اعداد را بصورت آرایه ای بگذاریم یعنی بصورت {1,0} ، در نتیجه تابع IF هردو قسمت را کنار هم قرار خواهد داد
و اگر قسمت اول را یک ستون از جدول انتخاب کنیم و قسمت دوم را ستون دیگری را انتخاب کنیم توانستیم جدولی متشکل از دو ستون و به ترتیب دلخواه ایجاد کنیم که این جدول می تواند در قسمت دوم تابع Vlookup استفاده شود.
برای اینکه از حالت تئوری خارج شویم و موضوع برایتان ساده تر شود یک مثال ساده می زنم.
فرض کنید یک جدول با نام Table1 به شرح ذیل دارید.
نتیجه فرمول زیر چه خواهد شد؟
If(1,Table1[Product])=
بله درست است جواب فرمول بالا ستون Product جدول Table1 را به شما خواهد داد.
حالا به جواب فرمول زیر فکر کنید.
If(0,,Table1[Category])=
بله باز هم درست حدس زدید، جواب فرمول بالا ستون Category جدول Table1 را به شما خواهد داد.
(البته دقت کنید اگر آفیس 2021 یا آفیس 365 را ندارید از آنجایی که قابلیت Spill Array را ورژن های قدیمی تر ندارد، نتیجه فرمول فوق را نمی توانید ببینید ولی این به معنی این نیست که نتوانید از فرمول فوق استفاده کنید)
خب حالا مطمئنا جواب فرمول زیر رو دیگه میدونید.
If({1,0}, Table1[Product],Table1[Category])=
بله جواب یک جدول دو ستونی است که ستون اول آن Product و ستون دوم Category را نمایش خواهد داد.
پس به این صورت توانستید در حافظه فرمول، ترتیب ستون ها را تغییر دهید و این روش می تواند محدودیت ویلوکاپ را که حتما باید ستون جستجو اولین ستون باشد را برای شما برطرف کند.
یعنی اگر از ما خواسته شود با جستجوی نام محصول گروه آن را بدست آوریم ابتدا با فرمول فوق می توانیم جدولی که ستون اول نام محصول و ستون دوم گروه آن است بدست آورده و این جدول را در قسمت دوم تابع Vlookup قرار دهیم.
Vlookup(“Shorts”, If({1,0}, Table1[Product],Table1[Category]) , 2 , 0)
به عبارتی دیگر از این پس با کمک تابع If محدودیت تابع ویلوکاپ که ستون های پشت ستون جستجو را نمی توانست بیاورد را نخواهید داشت.
نکته:
نکته 1 : اگر بجای {1,0} عبارت {0,1} بزنید جای ستون ها عوض خواهد شد.
نکته2: اگر ورژن آفیس 2021 یا آفیس 365 را دارید در نتیجه تابع Xlookup را خواهید داشت و این تابع محدودیت تابع ویلوکاپ را ندارد.
نکته3: با ترکیب تابع Index , Match که در مقاله جداگانه به آن پرداختیم محدودیت تابع Vlookup را نخواهید داشت.
10 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
واقعا آموزش هاتون نظیر نداره، اینکه بدونیم چه نکته هایی داخل یه تابع که ما همیشه ازش براحتی استفاده و عبور میکنیم هست و چقدر میتونه کاربردی باشه و کار رو راحت کنه و در عین حال برای افرادی که نمیخوایم از کارمون سر دربیارن ? واقعا مفید و عالیه
بخش آرگومان ها و اینکه چه جایگزین هایی میتونه داشته باشه عالی بود
خدا قوت
ممنون از نظر موشکافانه و مفصل شما.
عالی بود جناب بنی اسدی
ممنونم.
استاد فوق العاده صریح و عالی راجع به تابع if توضیح دادید و نکات و مثال کاربردی که برای این تابع پرکارد در اکسل همراهش زدید عالی تر تشکر از شما
ممنون از شما که مقاله رو بطور کامل مطالعه کردید
سلام استاد گرامي بسيار عالي بود
ممنون از شما
مهندس جان خسته نباشید
نکته هایی که در رابطه با مقادیر پیش فرض در تابع IF بود بسیار برام جدید و جذاب بود. واقا آموزش هاتون پر از نکته است. ایشالا همیشه موفق باشید.
سوال دارم
تابعی بنویسید که شرط زیر را بررسی کند
بررسی کند سلول a برابر سلولb
اگر صحیح است سلول c= a
اگر صحیح نیست به مقدار سلول c دست نزند
;if (a=b
c=a;
(