آموزش رایگان و صد در صد کاربردی تابع شرطی IF|آکادمي استاد آموز

مثال دوم :در شکل زیر لیست حقوق افراد  و درصد مالیات  نمایش داده شده است  افرادی که حقوق بالای 2000000 تومان دریافت می کنند 20% مالیات از حقوقشان کسر می شود می خواهیم حقوق دریافتی را با تابع If بررسی کنیم .

روش کار با تابع if

ابتدا روی خانه ی D4 که مربوط به  مالیات حقوق مهدی است  کلیک می کنیم .

از تب Formulas روی گزینه Insert function  کلیک می کنیم.

تابع If  را از لیست توابع موجود در لیست  انتخاب می کنیم

اگر تابع If در لیست وجود نداشت از قسمت بالای پنجره، (Search for a function) نام تابع If  را می نویسیم

و روی دکمه Go  کلیک می کنیم تا تابع به لیست اضافه شود.

سپس تابع if را انتخاب و  ok  می کنیم .

پنجره آرگومان های تابع باز می شود .

در قسمت آرگومان اول (logical test ) :

• شرط تابع را وارد می کنیم .پس عبارت  B4>2000000 می نویسیم.
• زیرا شرط تابع این بود که افرادی که حقوق بالای دو میلیون تومان دارند باید 20% مالیات از حقوقشان کسر شود .
• بنابراین  برای اولین شخص یعنی مهدی ابتدا مالیات را محاسبه و بعد به بقیه خانه ها با auto fill گسترش می دهیم .

در قسمت آرگومان دوم (Value_if_true) :

• اگر شرط برقرار  و درست باشد تعیین می کنیم چه اتفاقی باید بیفتد.
• اگر افراد حقوق بالای 2000000 تومان بگیرند باید 20% مالیات بدهند .پس در آرگومان دوم  مبلغ مالیات را تعیین می کنیم.
• برای اینکه مشخص کنیم 20% مالیات چند تومان می شود .آدرس خانه حقوق را در آدرس خانه درصد مالیات  ضرب می کنیم (B4*C4)

 در آرگومان سوم (Value_if_false):

• اگر شرط برقرار نبود تعیین می کنیم چه اتفاقی بیافتد .
• اگر افراد حقوق زیر دو میلیون تومان بگیرند شامل مالیات نمی شوند.
•   پس مقدار صفر را در این قسمت وارد می کنیم . و روی OK  کلیک می کنیم.

و با استفاده از خصوصیت Auto fill مقدار مبلغ مالیات را برای بقیه افراد بدست می آوریم .

  در پایان فرمول مثال فوق بشکل زیر نمایش داده می شود .

IF(B4>2000000;B4*C4;0)=

اگر روی سلولی که فرمول تابع  if را نوشتیم مثل D4 کلیک کنیم . و در نوار فرمول روی fx کلیک کنیم می توانیم آرگوما نهای تابع را مجدد ببینیم .

و در آخر برای محاسبه حقوق دریافتی مهدی،آدرس خانه حقوق را از آدرس خانه مبلغ مالیات کم می کنیم و می نویسم .

B4-D4=

و سپس با استفاده از Auto Fill محاسبه حقوق دریافتی را برای بقیه افراد  بدست می آوریم .

استفاده از تابع If بهمراه تابع And و OR

مثال 3:  افرادی که مشمول پرداخت مالیات می شوند  که حقوق بالای دو میلیون تومان و مبلغ مالیات بزرگتر از صفر باشد و کسانی که این شروط را دارند در ستون E، مشمول مالیات و در غیر این صورت خالی نمایش داده شود

در سلول E4 کلیک می کنیم .عملگر مساوی را تایپ و تابع if  را می نویسیم پرانتز را باز می کنیم در قسمت logical_test از تابع and بصورت زیر استفاده می کنیم .

logical_test:

• در این قسمت دو تا شرط باید بررسی شود هم حقوق باید بزرگتر از2000000 باشد و هم مبلغ مالیات بزرگتر از صفر باشد.
• چون دو تا شرط داریم که هردو باید درست باشد تا آرگومان دوم اجرا شود پس از تابع and استفاده می کنیم
• شرط حقوق بزرگتر از دو میلیون  را بصورت   ( B4>2000000) و مبلغ مالیات هم بزرگتر از صفر را بصورت ( D4>0) می نویسیم
  

AND(B4>2000000; D4>0)

value_if_true:

• در این قسمت اگر هر دو شرط تابع and  برقرار و درست باشد مقدار این آرگومان اجرا می شود
• مقدار این آرگومان “مشمول مالیات” است .

value_if_False:

• اگر یک از شرط های مثال فوق درست نباشد مقدار این تابع نمایش داده می شود .
• مقدار این تابع می تواند خالی بگذاریم که در این صورت اگر یکی از شروط برقرار نباشد مقدار False نمایش داده می شود.
• در این مثال مقدار این آرگومان را خالی ” ” می گذاریم.

فرمول تابع If همراه and  در پایان بصورت زیر می شود .

(“”;” مشمول مالیات”;AND(B4>2000000; D4>0))IF=

مثال3:افرادی که حقوق کوچکتر مساوی 2000000 یا مبلغ مالیات برابر صفر دارند مشمول مالیات نمی باشند و در غیراین صورت مشمول مالیات نمایش داده شود

در سلول E4 کلیک می کنیم .عملگر مساوی را تایپ و تابع if  را می نویسیم پرانتز را باز می کنیم در قسمت logical_test از تابع OR بصورت زیر استفاده می کنیم .

logical_test:

• در این قسمت یکی از شرط ها درست باشد کافی است یعنی یا حقوق باید کوچکتر مساوی 2000000 باشد یا مبلغ مالیات مساوی صفر باشد.
• پس می توانیم از تابع OR استفاده کنیم .
  
• در قسمت شرط تابع Or را به این صورت می نویسیم.
  

OR(B4<=2000000; D4=0)

value_if_true:

• در این قسمت اگر یکی از شرط های  تابع OR  برقرار و درست باشد مقدار این آرگومان اجرا می شود
• مقدار این آرگومان “مشمول مالیات نمی باشد” است .

value_if_False:

• اگر هر دو شرط های مثال فوق درست نباشد مقدار این تابع نمایش داده می شود .
• در این مثال مقدار این آرگومان را “مشمول مالیات”می گذاریم.

فرمول تابع If همراه OR در پایان بصورت زیر می شود .

(“مشمول مالیات”;” مشمول مالیات نمی باشد”;OR(B4<=2000000; D4=0))IF=

تعریف و کاربرد تابع چندشرطی IF(IF های تودر تو)

در تابع چند شرطی If ، می توانیم همزمان چندین شرط را بررسی کنیم. در این حالت در تابع If می توانیم if های متعدد بنویسیم .

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

حل مثال از تابع IF  تودرتو

برای مثال لیستی از نمرات دانش آموزان داریم ،می خواهیم افرادی که نمره زیر 10 کسب کرده اند “بد”، افرادی که بین 10تا 15 نمره گرفته اند “متوسط” و کسانی که نمره بالای 15 گرفته اند “خوب”نمایش داده شود .

شروط مساله بصورت خلاصه در متن زیر آورده شده  است.

• نمره کوچکتر از 10————->بد
• نمره بین 10 تا 15————–>متوسط
• نمره بزرگتر از 15————–>خوب

روش حل مساله با If های تودرتو

ابتدا وضعیت نمره مهدی که اولین فرد است را بررسی می کنیم و با خصوصیت  Auto fill وضعیت نمره بقیه افراد هم بدست می آوریم .

بنابریان روی سلول C4 می کنیم تا تابع if را در این خانه بنویسیم.

عملگر مساوی را تایپ می کنیم تابع If را می نویسیم پرانتز را باز می کنیم

و در قسمت شرط تابع ، ( logical_test ) :

• اولین شرط تابع را می نویسیم .
• افرادی که نمره کوچکتر از 10 گرفته اند پس روی آدرس نمره مربوط به مهدی کلیک می کنیم و می نویسیم( 10>B4 )

در قسمت  Value_If_true:

• اگر شرط درست باشد این قسمت اجرا می شود .
• یعنی اگر نمره کوچکتر از 10(10>)باشد عبارت “بد”نمایش داده می شود.
• سپس جداکننده “;” را تایپ می کنیم .

در قسمت Value_If_false:

• گر شرط درست نباشد این قسمت اجرا می شود
• اگر نمره کوچکتر از 10 نباشد پس می تواند بین 10تا 15 باشد
• پس در این قسمت می توانیم if  دیگری را بنویسیم که با تابع And نمره بزرگتر مساوی 10 و کوچکتر از15 را بررسی می کند.

IF(AND(B4>=10;B4<15)=

مجدد شرط فوق بررسی می شود و در صورت صحیح بودن

درقسمت :Value_If_true شرط دوم :

اگر شرط فوق درست بود (یعنی نمره بین 10 تا 15 بود )عبارت “متوسط” نشان داده می شود.

در قسمت  Value_If_true شرط دوم:

• اگر نمره بین 10 تا 12 نبود حتما نمره بزرگتر از 15 است .
• پس عبارت “خوب ” را در این قسمت می نویسیم.
• سپس پرانتز را می بندیم و اینتر از کی بورد را می زنیم .

در پایان فرمول فوق بصورت زیر تکمیل می شود

((“خوب”;”متوسط”; AND(B4>=10;B4<15))IF;”بد”;B4<10)IF=

تابع IFERROR و IFNA

تابع IFERROR هر گونه خطایی را در اکسل شناسای می کند .

اما تابع   IFNA فقط برای شناسایی خطای N/A# مورد استفاده قرار می گیرد .

و اگر فرمولی را بنویسیم که خطاهای فوق را بدهد با کمک فرمول های IFERROR و IFNA می توانیم بجای نمایش خطا ، پیام خطای خودمان را نمایش دهیم.

ساختار تابع IFERROR

IFERROR(value;Value_if_Error)=

value:

آدرس خانه ای که دارای خطا است.

Value_if_Error:

پیام خطایی که می خواهیم نمایش دهد .

برای مثال اگر عددی را بر صفر تقسیم کنیم اکسل پیام خطای !DIV/0 # را نمایش می دهد .

با استفاده از تابع IFERROR می توانیم بجای نمایش این خطا در خانه اکسل ، پیام ما را نمایش بدهد .

• در خانه دلخواه مثل C5 عددی را وارد می کنیم در خانه دیگری مثل C6 ، عدد صفر را می نویسیم .
• سلول F6 را انتخاب می کنیم و فرمول (  C5/C6=)   را تایپ می کنیم.
• بدلیل تقسیم عدد بر صفر ارور !DIV/0 #  در خانه F6 نمایش داده می شود .

حال با کمک تابع  IFERROR  می خواهیم بجای نمایش !DIV/0 # پیام خطای خودمان نشان داده شود.

پس فرمول زیر را می نویسیم.

(“ارور تقسیم بر صفر”;C5/C6)IFERROR

ساختار تابع  IFNA

در توابع VLOOKUP و HLOOKUP که بیشتر خطای  N/A# روی می دهد می توان از تابع  IFNA استفاده کرد.

IFNA(Value;Value_if_na)=

Value:

آدرس سلولی که خطای N/A# می دهد .

Value_if_na:

پیامی که می خواهیم بجای این خطا نشان داده شود .