تابع if در اکسل

تابع if در اکسل، ساختار و کاربرد

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

ساختار تابع if در اکسل به صورت زیر است:

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

همانطور که می بینید این فرمول 3 آرگومان دارد، ولی صرفا آرگومان اول ضروری است و دو آرگومان بعدی اختیاری هستند.

logical_test: (آزمون منطقی): یک عبارت یا مقدار منطقی که میتواند صحیح یا غلط باشد، در این آرگومان میتوان متن، عدد، تاریخ، یا هر اپراتور(عملگر) مقایسه ای دیگری را قرار دهید.

برای مثال، تست منطقی شما میتواند به صورت زیر باشد:

B1="فروخته شده"

B1<12/1/2014

B1=10

B1>10

value_if_true: (مقدار، در صورت صحیح بودن شرط)

مقداری که در صورت صحیح بودن عبارت منطقی می بایست برگردانده شود.( اختیاری)

به عنوان مثال فرمول زیر در صورتی که مقدار سلول B1 بزرگتر از 10 باشد؛ عبارات “خوب” را بر میگرداند.

=IF(B1>10, "خوب")

value_if_false: (مقدار، در صورت غلط بودن شرط)

مقداری که در صورت غلط بودن شرط می بایست برگردانده شود.(اختیاری)

برای مثال، در صورتی که “بد” را بعنوان پارامتر سوم به فرمول بالا اضافه کنید، این فرمول در صورتی که مقدار سلول B1 بزرگتر از 10 باشد مقدار “خوب” را برمیگرداند، در غیر این صورت عبارت”بد” برگردانده می شود.

=IF(B1>10,"خوب","بد")

تابع if در اکسل

تابع if در اکسل اکسل: مواردی که می بایست به یاد داشت!

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

1. در صورتی که مقدارVALUE_IF_TRUE مقدار دهی نشود.

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

=IF(B1>10, ,"بد")

در صورتی که میخواهید فرمول شما در صورت صحیح بون شرط هیچ مقداری را برنگرداند، از یک دابل کوتیشن(“”) در قسمت پارامتر دوم استفاده کنید

 =IF(B1>10,"","بد")

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

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

2.در صورتی که مقدار VALUE_IF_FALSE در تابع if حذف گردد.

در صورتی که مشخص نکنید در صورت اشتباه بودن شرط تابع if در اکسل چه مقداری را بازگرداند، و شرط به کار رفته نیز صحیح نباشد،( یعنی بعد از VALUE_IF_TRUE پرانتز بسته شود)، تابع if عبارت FALSE را برمیگرداند. کمی غیر منتظره میرسد ، اینطور نیست؟ نمونه ای از چنین فرمول هایی در زیر آمده است:

=IF(B1>10,"خوب")

در صورتی که بعد از VALUE_IF_TRUE یک کاما قرار دهید تابع مقدار 0 را برمیگرداند که باز هم معنی دار نخواهد بود.

در اینجا باز هم منطقی ترین کار این است که به جای مقدار VALUE_IF_FALSE از (“”) استفاده شود.

=IF(B1>10,"خوب","")

3. استفاده از تابع if در اکسل برای نشان دادن TRUE  یا FALSE

در صورتی که میخواهید تابع if در اکسل پس از ارزیابی شرط ارائه شده از مقادیر TRUE و FALSE استفاده کند، مقدار TRUE را به جای VALUE_IF_TRUE قرار دهید. برای مثال:

=IF(B1>10,TRUE,FALSE)

یا

=IF(B1>10,TRUE)

توجه:

در صورتی که میخواهید TRUE  و FALSE گفته شده در بالا توسط دیگر فرمول های موجود در اکسل خوانده و شناخته شود مطمئن شوید که آنها را میان “” قرار نداده اید. همچنین اگر میخواهید صرفاً از عبارت های TRUE  و FALSE به عنوان متن معمولی استفاده کنید، آنها را میان “” قرار دهید. در این صورت مقدار برگردانده شده در سمت چپ قرار میگیرد و فرمت آن GENERAL در نظر گرفته میشود. هیچ تابع اکسلی نیست که متن های “TRUE” و “FALSE” را به عنوان مقادیرمنطقی تشخیص دهد.

4. استفاده از if برای انجام عملیات ریاضی و برگرداندن نتایج

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

نمونه یک تابع if

 =IF(A1>B1,C3*10,C3*5)

فرمول مقادیر سلول های A1وB1 را مورد ارزیابی قرار میدهد،در صورتی که A1 بزرگتر باشد، مقدار C3 را در10 ضرب میکند در غیر این صورت در 5 ضرب میکند.

نمونه دو تابع if

=IF(A1<>B1,SUM(A1:D1),"")

در این صورت تابع مقادیر A1 و B1 را مورد مقایسه قرار میدهد در صورتی که مساوی نباشد، مجموع A1:D1 را برمیگرداند و در غیر این صورت مقدار خالی را برمیگرداند.

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

حال که با تابع if آشنا هستید، چند مثال در این زمینه ببینید و با نحوه استفاده از if در کاربرگ آشنا شوید.

نمونه های تابع if برای اعداد: کوچکتر از، بزرگتر از ، مساوی

استفاده از تابع if، با مقادیر عددی، بر اساس به کارگیری عامل های مقایسه ای بعنوان شرط می باشد. لیست کامل عامل های منطقی را که در فرمول به کار برده شده است در جدول زیر ارائه شده است:

تصویر زیر نمونه ای از فرمول if با عبارت منطقی “بزرگتر مساوی” را نشان میدهد.

 مثال های تابع if در اکسل برای مقادیر متنی (TEXT)

عموماً با استفاده ازعامل های “مساوی” یا ” عدم تساوی” به صورتی که در نمونه های زیر نشان داده شده است، از تابع if برای رشته های متنی استفاده میشود.

مثال 1: فرمول if در اکسل بدون حساسیت به بزرگ یا کوچک بودن حروف، برای رشته های متنی

فرمول if نیز مثل بسیاری از دیگر توابع اکسل، نسبت به بزرگ یا کوچک بودن حروف حساس نیست. برای مثال، در فرمول زیر بر اساس DELIVERY STATUS (ستون C عبارت “YES” یا “NO” برگردانده میشود.)

=IF(C2="DELIVERERD","NO","YES")

این فرمول به فارسی روان به این معنی است که، در صورتی که سلول c2 شامل عبارت “DELIVEREY” بشود “YES” را برمیگرداند و در غیر این صورت “NO” را برخواهد گرداند.

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

همچنین دیگر روشی که میتوانید به همین نتیجه دست یابید استفاده از عامل”<>” و جابجایی VALUE_IF_TRUE و VALUE_IF_FALSE است.

=IF(C2<>"DELIVERED","بله","خیر")

مثال 2: ارزیابی منطقی با حساسیت رو بزرگی و کوچکی حروف

در صورتی که میخواهید ارزیابی منطقی که صورت میگیرد نسبت به حروف بزرگ یا کوچک حساس باشد،  میتوانید از ترکیب IF و EXACT استفاده کنید. در این صورت، اگر رشته مورد نظر دقیقا مشابه رشته موجود در فرمول نباشد عبارت “TRUE” و در غیر اینصورت  عبارت “FALSE”  برگردانده میشود . تابع EXACT فرمت های مختلف را نادیده میگیرد.

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

=IF(EXACT(C2,"DELIVERED"),"خیر","بله")

در این صورت می بایست در ستون c فقط عبارت “DELIVERED” به کار رود در غیر این صورت “مقدار “بله” برگردانده میشود.

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

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

مثال 3: فرمول if برای مقادیر متنی با مطابقت جزئی

در صورتی که میخواهید شرط خود را بر اساس PARTIAL MATCH(مطابقت جزئی) به جای EXACT MATCH(مطابقت دقیق) قرار دهید، راه حل این است که از کاراکترهای WILDCARD(* یا ؟) استفاده کنید. اما این شیوه جواب نخواهد داد، بسیاری از توابع اکسل WILDCARD را قبول میکنند ولی IF  این کاراکترها را شناسایی نمیکند.

راه حل این است که از ترکیبIF با ISNUMBER و SEARCH یا FIND استفاده کنید.

برای مثال، اگر هم برای ستون DELIVERED و هم ستون OUT OF DELIVERERY، اقدام NO قرار گیرد، فرمول زیر میتواند کارامد باشد:

=IF(ISNUMBER(SERACH("deliv",C2)),"بله","خیر")

ما از فرمول SERACH به این دلیل در فرمول بالا استفاده کردیم که، تا مواردی که از لحاظ حروف بزرگ و کوچک متفاوت هستند مورد شناسایی قرار گیرند. در صورتی که مطابقت کامل ایجاد کنید، فقط کافیست در این فرمول Find را جایگزین since  کنید:

=IF(ISNUMBER(find("متن مورد نظر",جایی که باید جستجو شود)),"value_if_true","value_if_false")

تابع if در اکسل: فرمول های حاوی تاریخ

در نگاه اول ممکن است به نظر برسد که فرمول های IF حاوی تاریخ هم مثل نمونه های قبلی است که حاوی مقادیر عددی بود.ولی اینگونه نیست.

برخلاف دیگر توابع در اکسل، IF مقادیر تاریخ را شناسایی نمیکند و آن را بعنوان یک رشته متنی در نظر میگیرد. و به همین دلیل است که شما نمیتوانید تاریخ خود را به این گونه وارد فرمول کنید >”11/19/2017″ .

مثال 1: فرمول IF برای تاریخ همراه با تابع DATEVALUE

به منظور اینکه تابع IF شما تاریخ را در فرمول شناسایی کند، می بایست آن را با تابع DATEVALUE به کار گیرید. مثل

=DATEVALUE("11/19/2017")

فرمول کامل در زیر آمده است:

=IF(C2<DATEVALUE("11/19/2017″),"به زودی فرا میرسد","تمام شده")

همانطور که در تصویر زیر نیز نشان داده شده است، این فرمول، تاریخ را در ستونC ارزیابی میکند و در صورتی که یک بازی قبل از 11 نوامبر انجام شده باشد “تمام شده” را بر میگرداند و در غیر این صورت “به زودی فرا میرسد” را نشان میدهد.

مثال2: تابع IF در اکسل همراه با تابع ()TODAY

زمانی که میخواهید  شرط خود را در فرمول بر اساس تاریخ امروز قرار دهید، میتوانید در قسمت آزمون منطقی(LOGICAL TEST) از تابع TODAY استفاده کنید.

=IF(C2<DATEVALUE("11/19/2017″),"به زودی فرا میرسد","تمام شده")

تابعIF در اکسل میتواند آزمونهای منطقی بسیار پیچیده تری را شناسایی کند. یکی از این نمونه ها در زیرآمده است.

مثال 3: فرمول های پیشرفته IF برای تاریخ های گذشته و آینده

فرض کنید میخواهید زمان هایی را در تابع خود در نظر بگیرید که مربوط به 30 روز بعد است، در این مورد، در این صورت میتوانید در قسمت آرگومان آزمون منطقی خود (logical_test)  از این عبارت استفاده کنید: A2-TODAY()>30.

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

=IF(A2-TODAY()>30,"تاریخ آینده","")

همچنین در مورد تاریخ هایی که در بیشتر از 30 روز قبل اتفاق می افتد میتوانید از فرمول زیر استفاده کنید:

=IF(TODAY()-A2>30,"تاریخ گذشته","")

در صورتی که میخواهید هر دو نشانه را در ستون های خود داشته باشید میتوانید بنویسید:

=IF(A2-TODAY()>30,"تاریخ آینده",=IF(TODAY()-A2>30,"تاریخ گذشته",""))

تابع IF در اکسل برای سلول های خالی و غیر خالی

در صورتی که میخواهید به نحوی داده های خود را بر اساس سلول های خالی یا غیر خالی نشانه گذاری کنید،میتوانید :

• از فرمول IF همراه با IFBLANK استفاده کنید.
• میتوانید از عبارت های منطقی =””(مساوی با خالی) یا <>””( مساوی با خالی نیست) استفاده کنید.

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

نمونه های بعدی آزمون های منطقی خالی و غیر خالی را به صورت عملی نشان میدهد:

فرض کنید در صورتی که یک بازی مشخص انجام شده باشد یک تاریخ در ستون  cخواهید داشت، بنابراین میتوانید از یکی از فرمول های زیر برای نشان دادن بازی های انجام شده خود استفاده کنید:

=IF($C2<>"","تمام شده","")

=IF(ISBLANK($C2)=FALSE,"تمام شده","")

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