در این مقاله، به دنبال یادگیری ترکیب و کاربرد تابع IF در اکسل هستیم. سپس به مثال هایی در این باره میپردازیم. این مثال ها هم در مورد مبتدیان و هم حرفه ای های اکسل کمک کننده خواهد بود.
اگر با تابع if در اکسل آشنا هستید مطالعه این مطالب را به شما پیشنهاد می کنیم:
فیلم آموزشی تابع if در اکسل
اگر از آن دسته افرادی هستید که با دیدن فیلم آموزشی مطالب را بهتر درک میکنید. قبل از مطالعه ادامه مطلب، فیلم آموزشی زیر را پیشنهاد میکنیم. با دیدن این فیلم آموزشی با تابع 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 در اکسل: مواردی که می بایست به یاد داشت!
اگرچه دو پارامتر آخر تابع اختیاری هستند، ولی در صورتی که منطق فرمول خود را ندانید، نتایج غیرمشخصی بدست خواهید آورد.
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
مقدار 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(A2>5,"OK",) |
در صورتی که مقدار موجود در سلول A2 بزگتر از 5 باشد مقدار OK را برمیگرداند در غیر این صورت 0 برگردانده می شود. |
کوچکتر از | > |
=IF(A2<5,"OK",) |
در صورتی که مقدار موجود در سلول A2 کوچکتر از 5 باشد مقدار OK را برمیگرداند در غیر این صورت 0 برگردانده می شود. |
مساوی با | = |
=IF(A2=5,"OK","WRONG NUMBER") |
در صورتی که مقدار موجود در سلول A2 مساوی 5 باشد مقدار OK را برمیگرداند در غیر این صورت WRONG NUMBER برگردانده می شود. |
عدم تساوی | <> |
=IF(A2<>5,"WRONG NUMBER,"OK") |
در صورتی که مقدار موجود در سلول A2 برابر 5 نباشد، فرمول مقدار WRONG NUMBER را برمیگرداند، در غیر این صورت “OK” را برمیگرداند. |
بزرگتر یا مساوی | >= |
=IF(A2>=5,"OK","POOR") |
در صورتی که مقدار سلول A2 بزرگتر مساوی 5 باشد، تابع عبارت OK را برمیگرداند در غیر این صورت POOR برگردانده میشود. |
کوچکتر یا مساوی | <= |
=IF(A2<=5,"OK","") |
در صورتی که مقدار سلول A2 بزرگتر مساوی 5 باشد، تابع عبارت OK را برمیگرداند در غیر این صورت مقدار خالی”” برگردانده میشود. |
تصویر زیر نمونه ای از فرمول 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، عبارت «خیر» در نظر گرفته شود، فرمول زیر میتواند کارامد باشد:
=IF(ISNUMBER(SERACH("deliv",C2)),"بله","خیر")
ما از فرمول SERACH برای شناسایی مواردی که از لحاظ حروف بزرگ و کوچک متفاوت هستند؛ استفاده کردیم. در صورتی که میخواهید مطابقت کامل صورت گیرد، کافیست در این فرمول Find را جایگزین SERACH کنید:
=IF(ISNUMBER(find(متن مورد نظر",جایی که باید جستجو شود" )),"value_if_true","value_if_false")
=IF(ISNUMBER(Find("delivered",C2)),"بله","خیر")
تابع 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<Today(),"به زودی فرا میرسد","تمام شده")
تابع 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 استفاده کنید.
- میتوانید از عبارت های منطقی =””(مساوی با خالی) یا <>””( مساوی با خالی نیست) استفاده کنید.
جدول زیر تفاوت این دو روش را نشان میدهد و فرمول هایی را نیز در این باره ارائه میکند:
آزمون منطقی | توصیف | نمونه فرمول | |
سلول خالی | =”” | در صورت خالی بودن سلول مورد نظر و نیز همچنین وجود رشته های با طول صفر، TRUE را برمیگرداند و در غیر این صورت FALSE را برمیگرداند. |
=IF(A1="",0,1) در صورتی که سلول خالی باشد 0 را برمیگرداند در غیر این صورت 1. |
ISBLANK() | در صورتی که یک سلول مطلقاً خالی باشد TRUE را برمیگرداند- هیچ فرمول، یا رشته خالی که توسط یک فرمول دیگر ایجاد شده باشد وجود نداشته باشد.
در غیر این صورت FALSE را برمیگرداند. |
=IF(ISBLANK(A1),0,1) نتایج شبیه به فرمول بالا را نشان میدهد ولی سلول های حاوی رشته با طول 0 را خالی در نظر نمی گیرد. در صورتی که A1 شامل یک رشته خالی باشد 1 را برمیگرداند. |
|
سلول های غیر خالی | <>”” | در صورتی که یک سلول شامل داده باشد TRUE را برمیگرداند در غیراین صورت FALSE.
سلول های با رشته به طول صفر،خالی در نظر گرفته میشود. |
=IF(A1<>"",1,0) در صورتی که A1 غیر خالی باشد 1 را برمیگرداند در غیر این صورت 0 را برمیگرداند. در صورتی که A1 شامل یک رشته خالی باشد 0 را برمیگرداند. |
ISBLANK()=FALSE | در صورتی که سلول خالی نباشد، TRUE را برمیگرداند و در غیر این صورت FALSE برگردانده میشود. سلول های با رشته به طول صفر،غیرخالی در نظر گرفته میشود. |
=IF(ISBLANK(A1)=FALSE,0,1) مثل فرمول بالا کار میکند ولی اگر A1 خالی باشد 1 را برمیگرداند. |
نمونه های بعدی آزمون های منطقی خالی و غیر خالی را به صورت عملی نشان میدهد:
فرض کنید در صورتی که یک بازی مشخص انجام شده باشد یک تاریخ در ستون C خوهید داشت.بنابراین میتوانید ازفرمول های زیر برای نشان دادن بازی های انجام شده خود استفاده کنید:
=IF($C2<>"","تمام شده","")
=IF(ISBLANK($C2)=FALSE,"تمام شده","")
کلام آخر
امیدواریم که نمونه های بالا، درک مشخصی از تابع IF در اکسل برای شما ایجاد کرده باشد. درعمل، شما به تابعIF نیاز دارید تا شرط های چندگانه را بررسی کند. در مقاله بعدی به شما نشان خواهیم داد که چگونه این کار را انجام دهید.علاوه بر این، تابع If تو در تو در اکسل، فرمول آرایه ای IF و توابع IFNA و IFERROR را بررسی خواهیم کرد.
از این که این متن را خواندید از شما متشکریم. در این مقاله، به دنبال یادگیری ترکیب و کاربرد تابع IF در اکسل هستیم. سپس به مثال هایی در این باره میپردازیم. این مثال ها هم در مورد مبتدیان و هم حرفه ای های اکسل کمک کننده خواهد بود.
اگر با تابع if در اکسل آشنا هستید مطالعه این مطالب را به شما پیشنهاد می کنیم:
فیلم آموزشی تابع if در اکسل
اگر از آن دسته افرادی هستید که با دیدن فیلم آموزشی مطالب را بهتر درک میکنید. قبل از مطالعه ادامه مطلب، فیلم آموزشی زیر را پیشنهاد میکنیم. با دیدن این فیلم آموزشی با تابع 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 در اکسل: مواردی که می بایست به یاد داشت!
اگرچه دو پارامتر آخر تابع اختیاری هستند، ولی در صورتی که منطق فرمول خود را ندانید، نتایج غیرمشخصی بدست خواهید آورد.
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
مقدار 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(A2>5,"OK",) |
در صورتی که مقدار موجود در سلول A2 بزگتر از 5 باشد مقدار OK را برمیگرداند در غیر این صورت 0 برگردانده می شود. |
کوچکتر از | > |
=IF(A2<5,"OK",) |
در صورتی که مقدار موجود در سلول A2 کوچکتر از 5 باشد مقدار OK را برمیگرداند در غیر این صورت 0 برگردانده می شود. |
مساوی با | = |
=IF(A2=5,"OK","WRONG NUMBER") |
در صورتی که مقدار موجود در سلول A2 مساوی 5 باشد مقدار OK را برمیگرداند در غیر این صورت WRONG NUMBER برگردانده می شود. |
عدم تساوی | <> |
=IF(A2<>5,"WRONG NUMBER,"OK") |
در صورتی که مقدار موجود در سلول A2 برابر 5 نباشد، فرمول مقدار WRONG NUMBER را برمیگرداند، در غیر این صورت “OK” را برمیگرداند. |
بزرگتر یا مساوی | >= |
=IF(A2>=5,"OK","POOR") |
در صورتی که مقدار سلول A2 بزرگتر مساوی 5 باشد، تابع عبارت OK را برمیگرداند در غیر این صورت POOR برگردانده میشود. |
کوچکتر یا مساوی | <= |
=IF(A2<=5,"OK","") |
در صورتی که مقدار سلول A2 بزرگتر مساوی 5 باشد، تابع عبارت OK را برمیگرداند در غیر این صورت مقدار خالی”” برگردانده میشود. |
تصویر زیر نمونه ای از فرمول 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، عبارت «خیر» در نظر گرفته شود، فرمول زیر میتواند کارامد باشد:
=IF(ISNUMBER(SERACH("deliv",C2)),"بله","خیر")
ما از فرمول SERACH برای شناسایی مواردی که از لحاظ حروف بزرگ و کوچک متفاوت هستند؛ استفاده کردیم. در صورتی که میخواهید مطابقت کامل صورت گیرد، کافیست در این فرمول Find را جایگزین SERACH کنید:
=IF(ISNUMBER(find(متن مورد نظر",جایی که باید جستجو شود" )),"value_if_true","value_if_false")
=IF(ISNUMBER(Find("delivered",C2)),"بله","خیر")
تابع 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<Today(),"به زودی فرا میرسد","تمام شده")
تابع 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 استفاده کنید.
- میتوانید از عبارت های منطقی =””(مساوی با خالی) یا <>””( مساوی با خالی نیست) استفاده کنید.
جدول زیر تفاوت این دو روش را نشان میدهد و فرمول هایی را نیز در این باره ارائه میکند:
آزمون منطقی | توصیف | نمونه فرمول | |
سلول خالی | =”” | در صورت خالی بودن سلول مورد نظر و نیز همچنین وجود رشته های با طول صفر، TRUE را برمیگرداند و در غیر این صورت FALSE را برمیگرداند. |
=IF(A1="",0,1) در صورتی که سلول خالی باشد 0 را برمیگرداند در غیر این صورت 1. |
ISBLANK() | در صورتی که یک سلول مطلقاً خالی باشد TRUE را برمیگرداند- هیچ فرمول، یا رشته خالی که توسط یک فرمول دیگر ایجاد شده باشد وجود نداشته باشد.
در غیر این صورت FALSE را برمیگرداند. |
=IF(ISBLANK(A1),0,1) نتایج شبیه به فرمول بالا را نشان میدهد ولی سلول های حاوی رشته با طول 0 را خالی در نظر نمی گیرد. در صورتی که A1 شامل یک رشته خالی باشد 1 را برمیگرداند. |
|
سلول های غیر خالی | <>”” | در صورتی که یک سلول شامل داده باشد TRUE را برمیگرداند در غیراین صورت FALSE.
سلول های با رشته به طول صفر،خالی در نظر گرفته میشود. |
=IF(A1<>"",1,0) در صورتی که A1 غیر خالی باشد 1 را برمیگرداند در غیر این صورت 0 را برمیگرداند. در صورتی که A1 شامل یک رشته خالی باشد 0 را برمیگرداند. |
ISBLANK()=FALSE | در صورتی که سلول خالی نباشد، TRUE را برمیگرداند و در غیر این صورت FALSE برگردانده میشود. سلول های با رشته به طول صفر،غیرخالی در نظر گرفته میشود. |
=IF(ISBLANK(A1)=FALSE,0,1) مثل فرمول بالا کار میکند ولی اگر A1 خالی باشد 1 را برمیگرداند. |
نمونه های بعدی آزمون های منطقی خالی و غیر خالی را به صورت عملی نشان میدهد:
فرض کنید در صورتی که یک بازی مشخص انجام شده باشد یک تاریخ در ستون C خوهید داشت.بنابراین میتوانید ازفرمول های زیر برای نشان دادن بازی های انجام شده خود استفاده کنید:
=IF($C2<>"","تمام شده","")
=IF(ISBLANK($C2)=FALSE,"تمام شده","")
کلام آخر
امیدواریم که نمونه های بالا، درک مشخصی از تابع IF در اکسل برای شما ایجاد کرده باشد. درعمل، شما به تابعIF نیاز دارید تا شرط های چندگانه را بررسی کند. در مقاله بعدی به شما نشان خواهیم داد که چگونه این کار را انجام دهید.علاوه بر این، تابع If تو در تو در اکسل، فرمول آرایه ای IF و توابع IFNA و IFERROR را بررسی خواهیم کرد.
از این که این متن را خواندید از شما متشکریم.
سلام من 5 تا ستون دارم پذیرنده کد ملی پایانه تعداد تراکنش مبلغ تراکنش 12345 12181986 254856 1 156,145,845 12345 12181986 254857 5 5,416,516,516 12347 12181986 254858 63 651,651,651 12348 12181986 254859 42 651,651,854 12349 12181990 254860 58 21,321 12350 12181990 254861 65 68,498 12351 12181992 254862 42 52,313,215,456 12351 12181992 254863 58 321,321,321 12353 12181992 254864 65554 848,784 12354 12181992 254865 2111 2,121,654 12355 12181996 254866 45 12,151,654 12356 12181996 254867 10 1,216,354,684 12357 12181998 254868 0 21,215,484 12358 12181998 254869 0 2,151,454,851 12358 12181998 254870 22 2,154,851,854 12358 12181998 254871 55 84 12358 12181998 254872 66 845,454 12362… مطالعه بیشتر »
از فیلتر پیشرفته در اکسل استفاده کنید.
سلام، برقراری شرط با وردو یک کلمه مشخص،
مثلا در صورت ورود هر جمله ای که در آن کلمه « روز » وجود داشت ok نوشته شود
از تابع search یا تابع find باید توی قسمت شرط استفاده کنید.
با سلام
می خواهم تا سلول قبلی عدد وارد نکنید در سلو کنار چیزی یا عددی نتوانی بنویسی / چه کار باید کرد؟
باید از ابزار data validation در اکسل استفاده کنید.
میخواهم در یک ستون که لیست اسامی است، اسم هر نفر چند بار آمده و بعدش اسم نفر بعدی شروع شده و آن هم چند بار تکرار شده، میخوام فرمت اولین سلول که اسم نفر بعدی شروع می شود، رنگش تغییر کند، لطفا راهنمایی کنید.
سلام
باید از فرمت دهی شرطی استفاده کنید. آموزش های مربوط به فرمت دهی شرطی رو از لینک زیر ببینید:
آموزش اکسل از صفر
این فیلمها رو دیدم و خودم این فرمت دهی شکلی رو تا حدودی بلد هستم ولی من می خواهم مثلا در لیست زیر من می خواهم اولین جایی که اسم «حسینی» و یا «محمدی» و یا هر اسم دیگه ای باشه با رنگ متفاوت باشه.
این رو چطوری میشه حل کرد لطفا راهنمای کنید.
اکبری
اکبری
اکبری
حسینی
حسینی
حسینی
محمدی
محمدی
محمدی
اگر بخواید از رنگ متفاوت استفاده کنید از فرمت دهی شرطی نمی تونید و باید از برنامه نویسی VBA استفاده کنید. اگر با vba و ضبط ماکرو آشنا نیستید آموزش ها زیر رو ببینید: آموزش برنامه نویسی Vba در اکسل روش کار هم به این صورت هست که اول یک ماکرو ضبط کنید تا ببینید کد تغییر رنگ پس زمینه سلول چی هست. بعد یک حلقه بنویسید و مقدار سلول جاری رو با بالایی با استفاده از if مقایسه کنید. اگر مقدار ها برابر نبود، رنگ پس زمینه رو تغییر بدید. (می تونید به جای کد رنگ با استفاده از… مطالعه بیشتر »
چقد شما خوبید
باسلام
من یک سری داده در سه ستون دارم که میخواهم براساس مقادیر مختلف ستون اول و ستون دوم مقدار متناظر آن در ستون سوم را نشان دهد. چه تابعی مناسب است؟ من مقادیر ستون اول و دوم را بصورت لیست با دیتا ولیدیشن مرتب کرده ام ولی تابع مناسب را بلد نیستم
تابع if و and را استفاده کردم فقط برای یک سطر محاسبه میکند.
سلام باید از تابع vlookup استفاده کنید. البته باید دقیق تر و با مثال مسئله رو توضیح بدید. مثلا توی ستون اول و دوم چی هست؟ آیا مقدارهای تکراری از ترکیب ستون اول و دوم ایجاد میشه یا نه؟ و …
ولی به صورت کلی این مدل مسئله ها با تابع vlookup قابل حل هست.
باسلام و تشکر. مقادیر تکراری ایجاد نمیشه.هر سطر نشانگر یک حالت است. عناصر ستون اول و دوم در واقع از یک ماتریس استخراج شدن. مثلا سطر اول ماتریس 1 و ستون اول ماتریس 1 بود و داخل ماتریس نرخ مربوط به این درایه ماتریس بود من حالتهای مختلف ممکن از ماتریس را در سطرهای اکسل و با دو ستون وارد کردم و در ستون سوم اکسل مقادیر نرخ های یا همان درایه های ماتریس هستند الان تابعی که با گرفتن اعضای سطر و ستون ماتریس یا همان مقادیر ستون اول و دوم اکسل در هر سطر، مقدار نرخ را برگرداند… مطالعه بیشتر »
اگر ماتریس موجود هست نیاز به این ندارید که تبدیل به سطرش کنید. می تونید از ترکیب تابع index و match استفاده کنید.
اما اگر میخواید روی سطرها این کار رو بکنید و مقدار تکراری ندارید، باید از تابع vlookup استفاده کنید.
اول کار با این تابع رو یاد بگیرید، بعد اگر نتونستید مشکل رو حل کنید توی واتس آپ پیام بدید تا فرمول رو با هم بررسی کنیم.
میخوام توی اکسل فرمول بدم
مثلا سلول A1=256 و B1=20 و C1=1000
میخوام فرمول بنویسم که اگر A1 بزرگتر از 256 بود اونوقت بگه B1 ضربدر C1 شود ولی اگر B1 بزرگتر از 20 بود حاصلضرب بزرگتر از 20000 نشود
ممنون میشم راهنمایی بفرمایید
سوالاتی که می پرسید ترکیب تابع if و and هست. مطالب زیر رو مطالعه کنید تا نحوه نوشتن رو یاد بگیرید:
تابع if در اکسل
ترکیب تابع if و and در اکسل
سلام
میخوام توی اکسل فرمول بدم که مثلا اگر سلول A1 بزرگتر از 250 بود اونوقت سلول B1 در C1 ضرب شود به شرطی که نتیجه حاصل ضرب از عدد 1200 بیشتر نباشد
میشه راهنمایی بفرمایید
سلام
باید از تابع and استفاده کنید و هر دو شرط رو بررسی کنید.
سلام وقتتون بخیر.واقعا ممنونم از آموزشهای عالی تون.
خواهش میکنم. موفق باشید.
سلام
میشه فرمولی برام بنویسید که
مثلا مقدار عددی که در خانهA1 وارد می کنیم, اگر بزرگتر یا مساوی عدد 10 بود داخل همان خانه عدد 10 تایپ شود و بیشتر از ده تایپ نشه و اگر زیر ده بود هر عددی بود همن عدد تایپ بشه
سلام شما با توابع نمی تونید در یک سلول مقدار رو بگیرید و همزمان روی اون پردازش انجام بدید و خروجی رو در همان سلول قرار بدید. فرمول نویسی در یک سلول و از مقدارهای سلولهای دیگر اتفاق می افته. برای اینکار شما باید از قواعد برنامه نویسی vba و ماکرونویسی استفاده کنید. با برنامه نویسی Vba شما می تونید بر روی ورودی سلول ها کنترل داشته باشید و بعد از وارد شدن مقدار، خروجی مورد نظر رو هم وارد کنید. برای آشنایی با برنامه نویسی Vba آموزش های زیر رو ببینید. آموزش رایگان برنامه نویسی vba و ماکرونویسی در… مطالعه بیشتر »