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

  • تعریف و کاربرد تابع شرطی  If

  • حل مثال های کاربردی و متنوع از تابع IF

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

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

  • تابع IFERROR و IFNA

تعریف و کاربرد تابع شرطی  If

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

تابع If می تواند دارای یک شرط و چندین شرط داخلی تر (IF های تودرتو ) باشد.

اگر نیاز به استفاده همزمان تابع IF و میانگین داشتیم از تابع  Averageif و تابع Averageifs می توانیم استفاده کنیم .

برای استفاده  تابغ IF و مجموع ، از تابع Sumif و تابع Sumifs   استفاده  می کنیم .

و برای استفاده تابع IF  و تعداد می توانیم از توابع  Countif و Countifs را بکارببریم.

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

تابع If در اکسل دارای  3 آرگومان ورودی است .

 اولین آرگومان، شامل شرط منطقی است.

از دو آرگومان بعدی ، فقط یکی از آنها اجرا می شود .

  • اگر شرط درست بود آرگومان دوم  اجرا می شود .
  • اگر شرط نادرست بود آرگومان سوم تابع IF اجرا می گردد .

برای مثال اگر دانش آموز نمره 10 یا بزرگتر از 10 بگیرد قبول می شود.

و اگر نمره کمتر از 10 بگیرد مردود می گردد.

در این مثال شرط تابع “10=<” است که اگر درست باشد مقدار”قبول” برای دانش آموز نمایش داده می شود . اگر شرط نادرست باشد یعنی دانش آموز نمره زیر 10 گرفته که در این حالت مقدار “مردود”  نشان داده می شود .

ساختار تابع شرطی IF

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

(در صورت ناصحیح بود شرط;اگر شرط تابع درست بود;شرط تابع )IF=

logical_test:

  • شرط تابع if  در این قسمت نوشته می شود .
  • شرط می تواند شامل عملگر های مقایسه ای بزرگتر(<) ، بزرگتر مساوی (=<) ، کوچکتر (>)،کوچکتر مساوی (=>)،مخالف (<>) ، مساوی (=)
  • شرط می تواند شامل متن، عبارات منطقی ، عدد و تابع باشد.

 برای مثال:

   (بزرگتر از10)                       10<

(بزرگتر مساوی مقدار آدرس خانه B4)                    B3 =<

(کوچکتر از تاریخ)         1398/02/05 >

(مخالف علی)                “علی”<>

and(D3>2 ;B2<3)

value_if_true:

  • اگر شرط تابع درست بود مقدار این آرگومان اجرا و نمایش داده می شود.
  • این آرگومان اختیاری است و می تواند مقداری نداشته باشد.
  • مقدار این آرگومان می تواند عدد ، متن ، تابع  و … باشد .

برای مثال:

(مقدار 5 )                       5

( مقدار آدرس خانه B3)                    B3

 “مردود”

if(and(D3>2 ;B2<3);3;””)

value_if_false:

  • اگر شرط تابع صحیح نبود مقدار این آرگومان اجرا و نمایش داده می شود.
  • مقدار این آرگومان می تواند متن ، عدد ،تابع و… باشد .
  •  این آرگومان اختیاری است و می تواند مقدار خالی بگیرد و در صورت ناصحیح بودن شرط ، مقدار False برمی گرداند.

برای مثال:

(مقدار 15)                       15

( مقدار آدرس خانه B6)                    B6

 “قبول”

if(OR(D3>2 ;B2<3);3;)

حل مثال از تابع if

حل مثال از تابع if

برای مثال فرض کنید نمرات دانش آموزان در جدولی مطابق شکل فوق نوشته شده است می خواهیم افرادی که نمره بزرگتر مساوی 10 کسب کرده اند در ستون وضعیت قبولی ، قبول  و کسانی که نمره کمتر از 10 کسب کرده اند  مردود نمایش داده شوند.

روی سلول C4 کلیک می کنیم عملگر مساوی را می نویسیم .

سپس If را تایپ می کنیم  .

از نوار فرمول در بالای صفحه روی علامت fx کلیک می کنیم   تا پنجره آرگومان های تابع If باشود .

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

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

ابتدا شرط تابع را می نویسیم.

آرگومان های تابع شرطی if

آرگومان های تابع شرطی if

برای نوشتن شرط ابتدا نمره مهدی  را بررسی می کنیم که در سلول B4 قرار دارد سپس با خصوصیت Auto fill فرمول را به بقیه خانه ها گسترش می دهیم .

پس در قسمت شرط عبارت 10=< B4 را تایپ می کنیم .

ودر قسمت value_if_true :

مقداری را می نویسیم که در صورت درست بودن شرط باید اجراشود . آن مقدار  “قبول” است .

 در قسمت  value_if_false  :

مقداری که در صورت ناصحیح بودن شرط قرار است نمایش داده شود را وارد می کنیم و آن ، مقدار “مردود” است.

در مثال فوق فرهاد و مینا وضعیت قبولی شان مردود و بقیه دانش آموزان قبول شده اند .

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

(“مردود”;”قبول “;B4>=10)IF=

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

sample-of-if

حل مثال از تابع 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 کلیک کنیم می توانیم آرگوما نهای تابع را مجدد ببینیم .

آرگومان های تابع شرطی if

آرگومان های تابع شرطی if

 

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

B4-D4=

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

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

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

حل مثال کاربرد تابع if همراه and

حل مثال کاربرد تابع if همراه and

در سلول 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=

حل مثال کاربرد تابع if همراه and

حل مثال کاربرد تابع if همراه and

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

حل مثال کاربرد تابع if همراه and

حل مثال کاربرد تابع if همراه and

در سلول 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 های تودر تو)

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

حل مثال از تابع if های تودرتو

حل مثال از تابع if های تودرتو

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

  • نمره کوچکتر از 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:

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