آموزش تابع IF در اکسل(تصویری + فیلم آموزشی)

تابع IF در اکسل

این تابع در بخش توابع LOGICAL زبانه FORMULAS قرار دارد. این تابع یک تابع منطقی است که یک یا چند شرط را بررسی می کند، اگر همه شرط ها برقرار بود، عبارتی را که مشخص شده و اگر حداقل یکی از شرط ها برقرار نبود عبارت مشخص شده دیگری را نمایش میدهد. برای دسترسی به این تابع همانند تصویر زیر عمل کنید.

مثال ۱ برای تابع IF

نمره یک دانشجو در محیط اکسل بصورت زیر در دسترس است.

می خواهیم فرمولی در سلول ۲C داشته باشیم که نمره دانشجو را بررسی کند. اگر نمره دانشجو کوچکتر از ۱۱ باشد عبارت “مردود” و اگر بزرگتر و مساوی ۱۱ باشد عبارت “قبول” را نمایش دهد. برای این کار در سلول ۲C تابع IF را فرامیخوانیم. در تصویر زیر پنجره تابع IF را مشاهده میکنید.

در کادر LOGICAL_TEST باید عبارت شرط را وارد کرد. در این مثال عبارت شرط را میتوان B2<10 در نظر گرفت، یعنی اگر مقدار سلول B2 کوچکتر از ۱۱ باشد. برای این کار در این کادر ابتدا روی سلول B2 کلیک کرده و سپس عبارت “۱۰>” را مینویسیم.

حالا در کادر VALUE_IF_TRUE مقداری را که در صورت صحت شرط باید نمایش داده شود را وارد کنید. در این مثال اگر شرط برقرار باشد، یعنی نمره دانشجو کوچکتر از ۱۱ باشد، باید عبارت “مردود” نمایش داده شود. بنابراین در این کادر عبارت “مردود” را مینویسیم.

در کادر VALUE_IF_FALSE مقداری را که در صورت عدم صحت شرط باید نمایش داده شود را وارد کنید. در این مثال اگر شرط برقرار نباشد، یعنی نمره دانشجو بزرگتر یا مساوی با ۱۱ باشد، باید عبارت “قبول” نمایش داده شود. بنابراین در این کادر عبارت “قبول” را مینویسیم.

شکل نهایی این تابع بصورت زیر است. در پایان روی OK کلیک کنید.

حالا در سلول C2 متناسب با نمره وارد شده در سلول B2 عبارت مربوطه را مشاهده میکنیم.

حال اگر در سلول B2 عدد ۷ را بنویسیم، خواهیم دید وضعیت دانشجو در سلول C2 به مردود تغییر پیدا خواهد کرد.

آموزش مرتبط : آموزش برقرار کردن ارتباط بین ورد و اکسل | Excel & Word

مثال ۲ برای فرمول IF در اکسل

فروش ماهیانه یک فروشگاه در یک سال بصورت زیر است. ابتدا در سلول D1 میانگین فروش ماهانه و سپس با استفاده از تابع IF در ستون C در مقابل فروش هر ماه فرمولی تهیه کنید که اگر فروش هر ماه کمتر از میانگین باشد، عبارت “ضعیف” و در غیر اینصورت عبارت “مناسب” را نمایش دهد.

برای محاسبه میانگین در سلول D1 از بخش AUTO SUM فرمول AVERAGE را انتخاب میکنیم.

زمانیکه فرمول میانگین بصورت زیر نمایان شده، با استفاده از ماوس محدوده مورد نظر(B1 تا B12) را با ماوس انتخاب کرده و در پایان کلید ENTER را بفشارید.

میانگین فروش ماهانه در این سال در سلول D1 نمایش داده شده است.

حالا در سلول C1 تابع IF را فرامیخوانیم. در کادر LOGICAL_TEST باید شرطی نوشته شود به این صورت که اگر عدد موجود در سلول B1 کوچکتر از عدد موجود در سلول D1 باشد که این شرط بصورت B1<D1 نوشته میشود. دقت کنید بجای نوشتن عدد میانگین، آدرس سلول آن را در تابع قرار میدهیم.

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

حالا در کادر VALUE_IF_TRUE مقداری را که در صورت صحت شرط باید نمایش داده شود را وارد کنید. در این مثال اگر شرط برقرار باشد، یعنی فروش هر ماه کوچکتر از D1 باشد، باید عبارت “ضعیف” نمایش داده شود. بنابراین در این کادر عبارت “ضعیف” را مینویسیم.

در کادر VALUE_IF_FALSE مقداری را که در صورت عدم صحت شرط باید نمایش داده شود را وارد کنید. در این مثال اگر شرط برقرار نباشد، یعنی فروش ماهانه بزرگتر یا مساوی با D1 باشد، باید عبارت “مناسب” نمایش داده شود. بنابراین در این کادر عبارت “مناسب” را مینویسیم.

بعد از کلیک روی گزینه OK حاصل بصورت زیر خواهد بود.

حالا با یک دبل کلیک روی مربع کوچک گوشه کادر سلول C1 فرمول این سلول برای سایر سلولها کپی میشود و شکل نهایی جدول بدست  می آید.

بخوانید : آموزش تصویری کشیدن جدول در اکسل

مثال واقعی برای تابع IF

فرض کنید قانون مالیات بر درآمد بصورت زیر است.
تا مبلغ ۹۰۰۰۰۰۰ ریال معاف، از ۹۰۰۰۰۰۰ ریال تا ۱۵۰۰۰۰۰۰ ریال ۱۱ درصد نسبت به مازاد ۹۰۰۰۰۰۰ ریال و بیش از ۱۵۰۰۰۰۰۰ ریال ۲۰ درصد نسبت به مازاد ۱۵۰۰۰۰۰۰ ریال. در سلول A2 فرمولی بنویسید که مقدار مالیات مبلغ ورودی در سلول A1 را محاسبه کند.
برای این کار سلول A2 تابع IF را فرامیخوانیم و در کادر شرط، شرط اول که مبلغ درآمد کوچکتر و مساوی ۹۰۰۰۰۰۰ ریال است را بصورت A1<=9000000 را مینویسیم.

در کادر دوم که عبارت صحت شرط را باید بنویسیم، چون مبالغ کمتر از ۹۰۰۰۰۰۰ ریال معاف از مالیات هستند ،عدد صفر را مینویسیم.

در کادر سوم باید مقداری را که در صورت برقرار نبودن شرط نمایش داده شود را وارد کنیم. چون بیش از یک حالت در این کادر وجود دارد (مبلغ درآمد میتواند بیش از ۹۰۰۰۰۰۰ ریال و یا بیش از ۱۵۰۰۰۰۰۰ ریال باشد که محاسبات آنها تفاوت دارد)، در این کادر کلیک کرده و سپس همانند تصویر زیر یک بار دیگر تابع IF را در این کادر فرامیخوانیم.

حالا در ادامه نوشتن این فرمول، یک کادر IF خالی باز میشود. در کادر شرط، باید ادامه شرطها را بنویسیم .حالا در این کادر برای حالت درآمد بین ۹۰۰۰۰۰۰ ریال و ۱۵۰۰۰۰۰۰ ریال فرمول نویسی میکنیم. چون در این حالت دو شرط داریم(A1 بزرگتر از ۹۰۰۰۰۰۰ و کوچکتر از ۱۵۰۰۰۰۰۰) در کادر اول تابع IF همانند روش قبل یک تابع AND فرامیخوانیم.
حالا تابع AND بصورت زیر باز میشود. دقت کنید این تابع ها در همان تابع IF اولیه ایجاد شده اند.

در کادر LOGICAL1 شرط اول یعنی A1>9000000 و در کادر LOGICAL2 شرط دوم یعنی ۱۵۰۰۰۰۰۰=>A1 را مینویسیم.

در این زمان قبل از کلیک بر گزینه OK باید به نوشتن ادامه فرمول در توابع IF قبلی بپردازیم. برای این کار در نوار فرمول روی تابع IF دوم کلیک میکنیم.

حال در کادر VALUE_IF_TRUE تابع IF دوم، عبارتی را که در صورت برقراری این شرط باید نمایش داده شود را مینویسیم. طبق قانون مالیات اگر درآمد بین ۹۰۰۰۰۰۰ ریال و ۱۵۰۰۰۰۰۰ ریال باشد مبلغ مالیات بصورت زیر محاسبه میشود(A1 مبلغ درآمد است.)

(A1 – 9000000)×۱۰%

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

در کادر VALUE_IF_FALSE این تابع حالت آخر قانون (درآمد بیش از ۱۵۰۰۰۰۰۰ ریال) باید نوشته شود. فرمول محاسبه مالیات این درآمد بصورت زیر است(A1 مبلغ درآمد است.)

(۶۰۰۰۰۰۰×۱۰%)+((A1-15000000)×۲۰%)

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

حالا با کلیک بر گزینه OK مبلغ مالیات در سلول A2 نمایش داده میشود. اگر درآمد شخص در سلول A1 مبلغ ۱۱۰۰۰۰۰۰ ریال باشد مالیات وی بصورت زیر است.

حال اگر مبلغ درآمد وی به ۱۸۰۰۰۰۰۰ ریال تغییر یابد، مبلغ مالیات بصورت زیر است.

فرمول موجود در سلول A2 که در نوار فرمول وجود دارد بصورت زیر است:

=IF(A1<=9000000,0,IF(AND(A1>9000000,A1<=15000000),(A1-9000000)*10%,(6000000*10%)+((A1-15000000)))*20%)))

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

آموزش تابع شرطی IF به بیانی دیگر

این تابع جزء مهمترین و پرکاربردترین توابع در اکسل است که بیانگر جملات شرطی است. مثلا:

• اگر درس بخوانی قبول می‌شوی در غیر این صورت رد می‌شوی.
• اگر نمره شخص بالای ۱۰ شد قبول است در غیر این صورت رد است.
• اگر تعداد تولید بیشتر از ۱۰۰۰ قطعه در روز بود خوب است در غیر این صورت بد است.

Logical-test: شرط مورد نظر

Value-if-true: جواب شرط در صورت درست بودن شرط

Value-if-false: جواب شرط در صورت غلط بودن شرط

 < Control Panel > Region and Language > Additional Settings… > List Separator

مثال ۴ برای تابع IF : جمعیت شناسی

فرمولی در سلول I3 بنویسید تا بر اساس جمعیت وارد شده در سلول H3 پرجمعیت یا کم جمعیت بودن خانوار را مشخص کند.

 :فرمول سلول I3
IF(H3>4,C3,C4)=

اگر جمعیت وارد شده در سلول H3 یک عدد منفی بود فرمول قبلی چگونه اصلاح می‌شود؟ (مثلا واژه no را نشان دهد)

:فرمول سلول I3
IF(H3>=0,”no”,IF(H3<=4.C4,C3))=

سوال : چگونه میتوان یک رشته متنی (مانند سود نداریم-علی-فاصله – *- و …) را در داخل یه تابع نوشت؟

اکسل نسبت به متنها بیگانه است و آنها را به عنوان خطا تشخیص میدهد. برای وارد کردن یک متن در یک تابع مورد نظر باید داخل دابل کوتیشن نوشته شود. (“سود” – “نداریم” – “على” – ” ” – “*” – “!” و…)

مثال ۵ برای تابع IF : حقوق ناخالص

فرمولی در سلول C3 بنویسید بگونه ای که تا ۲ میلیون معاف از مالیات باشد و بیشتر از آن ۱۰% مابه التفاوت را به عنوان مالیات در نظر بگیرد.

:فرمول سلول C3
IF(B3>=2000000,0,(B3-2000000)*10%)=

:فرمول سلول D3
B3-C3=

مثال ۶ برای آموزش تابع IF سقف اضافه کاری

باتوجه به جدول زیر فرمولی در سلول G2 و G3 بنویسید که شرط زیر را داشته باشد:

“میزان اضافه کاری با احتساب ضریب ۱/۴ نباید بیشتر از سقف کاری باشد”

: راه اول
IF(D2<B2,D2*E2*0.9,B2*E2*0.9)=

: راه دوم
IF(D2<B2,D2,B2)*E2*0.9=

: راه سوم
MIN(D2,B2)*E2*0.9=

مثال ۷ برای آموزش تابع IF : تخفیف

فرمولی در سلول I3 بنویسید تا براساس تناژ وارد شده در سلول H3 درصد تخفیف را مشخص کند.

: راه اول
IF(H3>=7,C3,IF(H3<=4,C5,C4)))=

: راه دوم
IF(H3<=4,C5,IF(H3>=7,C3,C4)))=

: راه سوم
IF(H3>=7,C3,IF(4<H3<7,C4,C5)))=

مثال ۸ برای آموزش تابع IF : ارزیابی عملکرد مشخص کردن نفر اول و آخر

در سلول G2 فرمولی بنویسید که نفر اول و آخر را مشخص کند.

=IF(F2=MAX($F$2:$F$8),”نفراول”,IF(F2=MIN($F$2:$F$8),”نفر آخر”,””))

مثال ۹ برای آموزش تابع IF : نمره قبولی

الف) در سلول E2 فرمولی بنویسید که اگر نمره سلول C2 بزرگتر مساوی ۲۰ و نمره سلول D2 بزرگتر مساوی ۳۰ باشد واژه pass و در غیر اینصورت واژه fail را نمایش دهد.

  =IF(AND(C2>=20,D2>=30),”PASS”,”FAIL”)

ب) در سلول E2 فرمولی بنویسید که اگر نمره سلول C2 بزرگتر مساوی ۲۰ یا نمره سلول D2 بزرگتر مساوی ۳۰ باشد واژه pass و در غیر اینصورت واژه fail را نمایش دهد.

  =IF(OR(C2>=20,D2>=30),”PASS”,”FAIL”)

مثال ۱۰ برای آموزش تابع IF : تخفیف تعدادی کم

در سلول C6 فرمولی بنویسید که بر اساس تعداد وارد شده در سلول C4 تخفیف مناسب را از جدول تخفیفات محاسبه کند.

 

=IF(AND(C4>0,C4<=10),F5,IF(AND(C4>=11,C4<=15),F6,IF(AND(C4>=16,C4<=20)F7,F8)))

مثال ۱۱ برای آموزش تابع IF : طبقات هزینه

در سلول C2 فرمولی بنویسید و به سمت پایین کپی کنید که نشان دهد یک سوم هزینه ها “کم”، یک سوم هزینه ها متوسط و یک سوم آنها “زیاد” است.

=IF(B2<=1/3*MAX($B$2:$B$17),”کم”,IF(B2>=2/3*MAX$B$2:$B$17),”متوسط”,”زیاد”))

مثال ۱۲ برای آموزش تابع IF : تغییرات هزینه

در سلول C3 فرمولی بنویسید که تغییرات هزینه هر ماه را با ماه قبل مقایسه کند و اگر روند صعودی بود با فلش رو به بالا و اگر نزولی بود با فلش رو به پایین نشان دهد.

فونت ستون C را باید روی Wingdins 3 قرار دهید.

=IF(B3=B2,””,IF(B3>B2,”P”,”Q”))

مثال ۱۳ برای آموزش تابع IF : مغایرت زمان ورود و خروج بارگیری محصول

محصولات یک شرکت به دو صورت فله” و “گونی” بفروش میرسد مدت زمان بارگیری محصولات بصورت گونی حداکثر ۲ ساعت و فله حداکثر ۱ ساعت است با توجه به شکل زیر در سلول Q3 فرمولی بنویسید که اگر زمان ورود و خروج محاسبه شده در سلول P3 بیشتر از حداکثر مقدار هر محصول باشد با عدد یا ستاره یا تیک یا هرچیز دیگر نشان دهد.

=IF(ADN(TRIM(RIGHT(C3,4))=”گونی”,F2<=$B$1),0,IF(AND(TRIM(RIGHT(C3,4))=”فله”.F3<=$A$1),0,1))

مثال ۱۴ برای آموزش تابع IF : شماره تلفن ۳ ستاره

با توجه به شکل زیر اگر شماره تلفن وارد شده در ستون A درست نبود (تعداد ارقام آن کم یا زیاد باشد)، اکسل سه ستاره (***) در سلول جلوی آن نشان دهد. اما در صورتی که شماره تلفن وارد شده درست باشد به ستون دوم یعنی B نگاه کنید. اگر در ستون B حرف yes نوشته شده بود، اکسل شماره تلفن مورد نظر را با حذف فقط صفر اول نشان دهد، اما در صورتی که no نوشته شده بود اکسل شماره تلفن مورد نظر را با هم حذف صفر اول و هم حذف خط تیره ها نشان دهد.

=IF(OR(LEN(A1)<>13,”***”,IF(B1=”YES”,RIGHT(A1,12),”!”))

مثال ۱۵ برای آموزش تابع IF شماره حساب برعکس

زمانی که شماره حساب ها از Word به Excel کپی میشود بصورت برعکس این عمل صورت میگیرد. در سلول C2 فرمولی بنویسید و آن را کپی کنید تا این مشکل را حل نماید.

 

مثال ۱۶ برای آموزش تابع IF : هزینه بودجه شد

در سلول G3 و ۱۳ فرمولی بنویسید که اگر اختلاف وارد شده در ستون E مثبت بود به تعداد آن مربع یا ستاره نشان دهد. حال توجه داشته باشید اگر اختلاف مثبت بود مربع یا ستاره در ستون G و اگر اختلاف منفی بود در ستون I نشان داده شود.

 

 

مثال ۱۷ برای آموزش تابع IF : شماره عابر کارت

فرمولی در سلول B1 بنویسید که شماره عابر کارت وارد شده در سلول A1 را ۴ ،رقم ۴ رقم با خط تیره از هم جدا کند.

 

 

مثال ۱۸ برای آموزش تابع IF : کد ضمانت نامه

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

 

ب) این مسئله را با ابزار Find & Select در اکسل حل کنید؟

 

مثال ۱۹ برای آموزش تابع IF : برنامه آموزش

الف) در سلول H2 فرمولی بنویسید که واژه “مجازی” را با واژه “ارشد” در سلول A1 عوض کند.
ب) در سلول ۱۲ فرمولی بنویسید که واژه دکتر” را به اول نام و نام خانوادگی استاد .بچسباند و در صورتی نام استاد موجود نباشد ۳ ستاره (***) نشان دهد.
ج) در سلول ۱۲ فرمولی بنویسید زمان شروع و پایان کلاس را کنار هم و با یک خط تیره وسط آن نشان دهد.

 

امیدواریم از این مطلب در سایت صنایع سافت که درباره آموزش تابع IF در اکسل بود، لذت برده باشید. هر سوال و نظری دارید برای ما کامنت بذارید، کمتر از یک روز پاسخ داده می‌شه 😉