با تعریف و دسته بندی توابع منطقی در اکسل یا Logical Functions در مقاله قبلی آشنا شدیم. یاد گرفتیم که توابع منطقی در اکسل به دو دسته ی کلی تقسیم میشوند، دسته ی اول توابع منطقی هستند که خروجی این توابع عبارت True یا False میباشد و در مقاله ی اول معرفی شدند و دسته ی دوم توابع شرطی میباشند که معروفترین تابع در این دسته بندی تابع شرطی if است که در این مقاله به بررسی آن میپردازیم. تابع if در اکسل که از جمله پر کاربرد ترین و معروفترین توابع اکسل میباشد لذا مطالعه این مقاله به تمام کاربران اکسل توصیه میشود.
به منظور یادآوری، شکل زیر توابع منطقی در اکسل را به صورت خلاصه نمایش میدهد:
تابع if در اکسل
روش کار تابع if در اکسل بسیار ساده میباشد، این تابع همانند تمام توابع دیگر اکسل در یک سلول اکسل نوشته میشود و خروجی این تابع در سلول مورد نظر قرار میگیرد. ساختار تابع if در اکسل به صورتی است که یک شرط در آن چک میشود، شرط یک عبارت منطقی (Boolean) است که دو مقدار بیشتر ندارد یا True و یا False. کاربر در ساختار تابع if در اکسل تعیین میکند که در صورت درست یا نادرست بودن شرط چه خروجی در سلول قرار بگیرد.
ساختار تابع if در اکسل مانند زیر است:
IF (logical_test, [value_if_true], [value_if_false])
همانطور که مشاهد میکنید تابع if در اکسل سه آرگومان دارد، آرگومان اول اجباری و دو آرگومان بعد اختیاری میباشند. آرگومان اول عبارت شرط است و به صورتی نوشته میشود که حاصل آن True یا False باشد. آرگومان دوم مقداری است که در صورت درست بودن شرط در سلول نوشته میشود و آرگومان سوم مقداری است که در صورت نادرست بودن شرط در سلول قرار میگیرد.
در مقاله ی فرمول نویسی در اکسل با عملگرها آشنا شدیم، عملگرهای مقایسه مانند علامت بزرگتر، کوچکتر، مساوی و… همگی میتوانند در آرگومان اول تابع if در اکسل برای نوشتن عبارت شرطی به کار برده شوند زیرا همواره خروجی آنها Boolean خواهد بود، همچنین برای بررسی چند شرط به صورت همزمان در آرگومان اول میتوان از توابع منطقی در اکسل مانند توابع AND، OR و.. که در مقاله ی قبلی معرفی شدند استفاده کنید.
به خاطر داشته باشید آرگومان اول تابع if در اکسل همواره باید به صورتی نوشته شود که خروجی آن True یا False باشد.
آرگومان های دوم و سوم تابع if میتوانند رشته های متنی، توابع، عملیات ریاضی، اعداد و یا هر نوع داده ی دیگر باشند، در توابع if پیچیده تر معمولاً آرگومان های دوم و سوم خود توابعی هستند که در صورت درست یا نادرست بودن شرط، حاصل آنها باید در سلول حاوی تابع if درج گردد.
با توجه به اختیاری بودن آرگومان های دوم و سوم تابع if در اکسل، اگر، به ترتیب، جای هر یک از آرگومان های دوم و سوم در تابع خالی باشد، در صورت درست یا نادرست بودن شرط تابع، خروجی تابع if در برابر با عدد صفر خواهد بود.
ساختار if های تو در تو
یکی از روش های به کار بردن تابع if در اکسل به صورت if های تو در تو یا Nested IF Functions میباشد. مثلاً شما میخواهید فرمول زیر را با استفاده از تابع if در اکسل بنویسید، میخواهیم در نوشتن فرمول زیر از ساختار if های تو در تو استفاده کنیم، به مساله دقت کنید و تلاش کنید آن را بنویسید.
مقدار سلول A13 برابر است با:
- Awful: اگر مقدار سلول A1 از ۰ تا ۲۰ باشد.
- Bad: اگر مقدار سلول A1 از ۲۰ تا ۴۹ باشد.
- Good: اگر مقدار سلول A1 از ۵۰ تا ۸۴ باشد.
- Excellent: اگر مقدار سلول A1 از ۸۵ تا ۱۰۰ باشد.
در مساله ی بالا، در سلول A13 به تناسب اینکه چه عددی بین ۰ تا ۱۰۰ در سلول A1 نوشته شده باشد پیامی درج میگردد. با استفاده از ساختار if های تو در تو میتوان فرمول زیر را نوشت:
IF(A1>=50;IF(A1>=85;”Excellent”;”Good”);IF(A1>=20;”Bad”;”Awful”))
در فرمول بالا بجای آرگومان دوم و سوم از توابع if استفاده شده، حال در فرمول بالا در هریک از آرگومان ها دوم و سوم برای ریز تر کردن بازه ها میتوان مجدداً از ساختارهای if دیگر نیز استفاده کرد و به همین ترتیب تا تعداد ۶۴ تابع if تو در تو میتوان در فرمول نویسی تابع if در اکسل لحاظ نمود.
تابع IFERROR در اکسل
با انواع خطاها در اکسل آشنا شدیم، گاهی در فایل های اکسل به دلیل فرمول نویسی های پیاپی و وابسته به هم، بروز خطاها میتواند مشکل ساز باشد، لذا کاربران نیاز دارند با استفاده از راه هایی پی به وجود خطا در سلول های اکسل خود ببرند. یکی از راه های پیدا کردن خطاها در فایل های اکسل استفاده از تابع IFERROR میباشد. ساختار این تابع به صورت زیر است:
IFERROR (value, value_if_error)
این تابع دارای دو آرگومان اجباری میباشد، آرگومان اول آدرس یک سلول و یا یک فرمول و یا یک عبارت است که میخواهیم بدانیم حاصل آن خطا خواهد بود یا خیر و آرگومان دوم خروجی تابع IFERROR در صورت خطا بودن حاصل آرگومان اول است.
خروجی تابع IFERROR در صورت خطا بودن آرگومان اول برابر با آرگومان دوم میباشد و در صورت خطا نبودن آرگومان اول، خروجی این تابع برابر با حاصل آرگومان اول است. و اگر جای هریک از این دو آرگومان خالی باشد تابع IFERROR آن را رشته ای با طول صفر “” در نظر خواهد گرفت.
خطاهای شناخته شده در اکسل که توسط تابع IFERROR شناسایی میشوند موارد زیر میباشند:
#N/A #VALUE! #REF! #DIV/0! #NUM! #NAME? #NULL!
تابع IFNA در اکسل
این تابع دقیقاً مانند تابع IFERROR عمل میکند با این تفاوت که تنها خطای #N/A مورد بررسی این تابع قرار میگیرد و از بقیه انواع خطاها صرفنظر میکند. ساختار این تابع به صورت زیر است:
IFNA (value, value_if_na)
جناب قدیمی با سلام و سپاس از آموزشهای مفید شما
اشاره کرده اید آرگومانهای دوم و سوم if اختیاری هستند. لطفاً بگوئید مفهموم اختیاری بودن چیست؟ و دانستن این موضوع چقدر در فرمول نویسی می تواند اهمیت داشته باشد؟
سلام، خواهش میکنم، آرگومان های اختیاری آنهایی هستند که اگر نوشته نشوند اشکالی در فرمول رخ نمیدهد، مثلاً در تابع IF اگر شما ارگومان دوم یا سوم را خالی بگذارید همچنان تابع دارای خروجی خواهد بود، ولی اگر آرگومان اول را (که ارگومان اجباری است) خالی بگذارید تابع با خطا مواجه میشود و نمیتواند برای شما خروجی تولید کند، علاوه بر این هر آرگومان اختیاری یک مقدار پیشفرض دارد و در صورتی که آن آرگومان خالی بماند مقدار پیشفرض آن توسط اکسل در محاسبات لحاظ میگردد، لذا از این بابت دارای اهمیت است.
سلام میشه خواهش کنم کمک کنید!
من هرچی فرمول می نویسم نمیدونم کجاش مشکل داره
اگر سلول اف ۵ بزرگ تر مساوی ۱۵ باشد ، ۱۵
و اگر سلول اف ۵ کوچک تر از ۱۵ باشد همان عدد باشد
سلام، “همان عدد” یعنی کدام عدد؟!! اگر سلولی که میخواهید در آن فرمول بنویسید دارای یک عدد است، قاعدتاً آن عدد از بین میرود زیرا در آن سلول فرمول نوشته اید، شما نمیتوانید در یک سلول فرمول نویسی کنید در حالی که آن سلول مقدار دارد! به هر حال، کاری که میخواهید انجام دهید را با جزییات بیشتر توضیح دهید تا بتوانم راهنمای کنم، البته اگر با دقت بیشتر همین مقاله را مطالعه نمایید حتماً خودتان میتوانید این فرمول را بنویسد. موفق باشید. اگر منظور شما از همان عدد، عدد نوشته شده در سلول F5 است میتوانید از فرمول زیر استفاده کنید:
(IF(F5>=15;15;F5
خیلی خیلی ممنون از راهنماییتون
بله منظورم همین بود
گیج شده بودم
لطف کردید
باسلام
درمثال بالا اگرA13 بین ۲۰ و ۴۹ باشد شرط اصلی به چه صورت نوشته میشود؟
ازandاستفاده کردم خطاداد برخی گفتندنسخه اصلی اکسل خطا نمی دهد ممنون میشم فرمول رابنوسید
سلام، به صورت زیر بنویسید:
IF(AND(A1>20,A1<۵۰),"True","False")
جناب قدیمی سلام.
می خوام فرمولی طراحی کنم که اگر مقدار سلول مشخصی (مثلاً f5) اعداد ۸ یا ۱۰ یا ۱۲ باشد، ۶درصد از جمع و اگر ۱۴، ۱۶، ۱۸ باشد، ۷درصد از جمع کل کسر شود. از چه تابعی باید استفاده کنم؟
سلام، باید از تابع IF و ساختار IF های تو در تو استفاده نمایید. لطفاً مقاله را به دقت مطالعه کنید. موفق باشید.
سلام تشکر از سایت خوبتون ….لطفا در مورد این فرمول راهنمایی بفرمایید
میخوام با if فرمولی بنویسم که برای یک ستون داده percentile محاسبه شود سپس اعدادی که بزرگتر یا مساوی ان هستن جدا شده شمارش شودو در چند سطر پایین تر ان ستون کپی شود
سلام، خواهش میکنم، انجام این کار با توابع در اکسل ممکن نیست، زیرا خروجی تمام توابع در اکسل تنها و تنها یک عدد، متن، آدرس میباشد و تابعی نمیتوان نوشت که خروجی ان چند ادرس باشد. برای شمارش کردن تعداد سلول ها میتوان از توابع معرفی شده در سایت استفاده کرد. موفق باشید.
با سلام خدمت شما
آیا میتوان از تابع if در اکسل به نحوی استفاده کرد که تنها آرگمان های اول و دوم فعال باشند؟به این معنا که در صورت برآورده نشدن عبارت منطقی،هیچ مطلب و یا عملی توسط تابع انجام نپذیرد؟
با سپاس
سلام، بله، میتوانید ارگومان سوم را یک رشته خالی تعریف کنید، یعنی “” در اینصورت در سلول چیزی وارد نمیگردد. و یا میتوانید آن را خالی بگذارید، زیرا دو آرگومان دوم و سوم در تابع IF اختیاری هستند. موفق باشید.
سلام ، خسته نباشید
من میخوام فرمولی بنویسم که اگه شرط برقرار بود از یه فرمول ریاضی در یه سلول دیگه استفاده کنه ولی متاسفانه نمیشه
شرط اینه :
T<T0
و فرمول نوشته شده توی سلول دیگه
=(s+1)*(Ts/T)
برای اینکه مقدار متنی داده باشه باید توی فرمول از "x" به عنوان مثال استفاده کرد
اینجا باید چیکار کنم
ممنون میشم راهنمایی کنید
روز خوش
سلام، فرمول نوشته شده باید به صورت آدرس یک سری سلول باشد که پارامترهای مورد نظر در آنها درج شده است مثلاً (A1+1)*(A2/A3) در اینصورت به جواب خواهید رسید. موفق باشید.
سلام خسته نباشید.
من یه سوالی داشتم و چون دانش کافی از اکس ندارم همینجا مطرح میکنم. ممنون میشم جواب بدین.
من تو اکسل دو تا شیت دارم که تو هر ردیف از سلول ها اسامی بطور غیر منظم پر شده. و در یه ستون از هر شیت کد عددی هست که فقط میشه با این کد سلول ها رو بهم مرتبط و منظم کرد. تو یه شیت ادرس هست و تو شیت دیگه اسامی. الان چطوری میشه با جستجوی کدهای برابر این اسامی و ادرس ها تو یه شیت کنار هم قرار داد.
ممنون .
سلام، از توابع موجود در اکسل میتوانید استفاده نمایید. مخصوصاً توابع جستجو و مرجع، بعلاوهف اگر ماکرو نویی در اکسل را فرا بگیرید انجام کارهایی از این دست بسیار ساده خواهد بود. موفق باشید.
سلام و با تشکر از مطالب خوبتون. میخواستم بدونم چه طور میتونم به جای true و false کلمات دیگری قرار بگیرد؟
سلام، مقاله “ترفند های کاربردی در اکسل” را مطالعه نمایید.
سلام خدمت شما
تشکر میکنم از برنامه های آموزشی شما موفق باشید.
سلام، متشکرم، موفق باشید.
سلام ببخشید میخواستم بدونم تو اکسل میشه فرمولی نوشت که: با نوشتن تو یه سلول به طور خودکار یه مقداریکه قبلن تایپ شده توی یه سلول دیگه نشون داده بشه؟ مثلن اگه یه بار تو یه سلول نام و و تو یه سلول نام خانوادگی نوشتیم دفعه بعد وقتی نام رو نوشتیم به طور خودکار نام خانوادگی نوشته بشه؟ بازم ممنون
سلام، خیر، ممکن نیست.
ممنون
Ali:
ببخشید یه سوال داشتم میتونید کمک کنید . وقی ی فرمولی مینویسم مقدار اعداد متغیر وارد میکنیم ی جوابی بهمون میده . بعد مرحله بعد متغیرها تغییر میکنه ی جواب میده . همینجور ادماه داره. دستوری هست جواب هر مرحله که بدست میاد یک جوا نوشته شود . وقتی متغیر ها عوض میکنیم و جواب جدید بدست میاد اونم کنارش نوشته . و همینجور ادامه داشته باشه؟؟؟ یعنی منظورم این اون خروجی که داریم هربار حساب میکنیم چون فرمول تو ورودی ها عوض مکنیم دوباره عوض میشه و باید یاداشت کرد راهی هست خودش یجا یاداشت کن؟
سلام، خیر، این کار در اکسل ممکن نیست
سلام
میخواهم در قسمت نتیجه شرط زیر را لحاظ کنم
اگر وضعیت=عادی و نمره کل>= 10 باشد قبول در غیر اینصورت مردود
لطفا راهنمایی فرمایید.تشکر
سلام میتوانید از نمونه زیر استفاده کنید:
if(A10>=10,”Accept”,”Reject”)1
موفق باشید.
سلام و خسته نباشید میخاستم ببینم چطور میتونم یه تابع شرطی تو در تو طولانی بنویسم من با استفاده از تابع if و and یه تابع نوشتم که ارور داد و گفت خیلی طولانی هست و مجبور شدم بیشترش رو پاک کنم و الان نصفه نیمه شده چکار کنم بتونم کل تابع رو وارد کنم و از چه دستوری باید استفاده کنم ممنون
سلام، پیام خطای تابع به خاطر تعداد کاراکتر زیاد نبوده، لطفاً مجدداً تلاش کنید. موفق باشید. یک سلول میتواند حاوی ۳۲۷۶۷ کارکتر باشد.
سلام من میخوام بجای نوشته یا تورو فالس برام عدد مد نظرمو ضرب کنه.ولی نمیشه باید چیکار کنم؟ فرمولم رو اینطوری نوشتم =IF(0<A2<=360, A2=A2*45, (IF(360<A2<=700, A2=A2*54, (IF(700<A2=1000, A2=A2*66, A2=A2*0))))))) میخوام اگه عدد سلول آ۲ بین ۰تا ۳۶۰ بود در ۴۵ ضرب کنه و بین ۳۶۰تا۷۰۰ در ۵۴ و بین ۷۰۰ تا ۱۰۰۰در ۶۰ و بالای ۱۰۰۰ در ۶۶ و جوابش رو برام تو سلول بنویسه ولی این فرمول که نوشتم جواب تورو یا فالس بهم میده
سلام، شما در فرمول نویسی خود اشتباهی انجام داده اید، در آرگومان دوم تابع IF یک علامت مساوی نوشته اید که این علامت مساوی در اکسل یک عملگر مقایسه ای است که خروجی ان True یا False میباشد. لطفاً فرمول خود را اصلاح کنید. موفق باشید.
خسته نباشین . میخام فرمولی بنویسم که اگر سلول هاپر باشن سه تا شون در هم ضرب شه . اگر دو تاشون پر باشن . اون دوتا ضرب شن . نمیدونم این شرط پر بودن ینی عدد داشتن یا نداشتن رو چه جوری بنویسم
باتشکر
سلام، سلول برابر با “” یعنی سلول خالی، البته از تابع isempty هم میتوانید استفاده کنیدد.
سلام خسته نباشید
استاد یه سوال داشتم از خدمتتون
در اکسل برای یک سلول میخواستم یک فرمول بنویسم بلد نبودم
توضیح : اگر عدد من که در خانه A1 هست پس از تفریق از عدد من که در خانه B3 هست بیشتر از ۲۴ باشد آن را از ۲۴ کم کرده و ۱۰% آن را بنویسد
ممنون میشم اگه برام توضیح بدید
سلام، فرمول زیر را امتحان کنید:
If((A1-B3)>24,0.1*(A1-24),””)
موفق باشید.
باسلام میخوام حقوق تا ۲۳۰۰۰۰۰۰ازمالیات معاف.از۲۳تا۹۲، ۱۰درصدمالیات بده.از ۹۲تا۱۱۵. ۱۵درصد.از ۱۱۵تا ۱۶۱. ۲۵درصد و بیشتراز ۱۶۱ .۳۵درصد مالیات دهد.فرمولش چیه؟لطفا راهنمایی کنید
سلامف باید IF های تو در تو بنویسید، مانند نمونه توضیح داده شده در مقاله انجام دهید، با کمی دقت به راحتی موفق میشوید.
باسلام و خسته نباشید
من یه محدوده ای از بازه های زمانی دارم که میخوام بین دو بازه زمانی خاص نامگذاری کنم. مثلا بین تاریخ ۲۱ مارس – ۲۰ آوریل فروردین نامگذاری شه و بقیه هم بدین ترتیب.
و بصورت if تودرتو میخوام استفاده کنم. با if و and تقریبا میشه. ولی در مرحله تودرتو به مشکل میخوره. ثانیا نمیدونم امکانش در اکسل هست که به سال در فرمول نیازی نباشه. طوری که در فرمول گفته شود “از این ماه و روز تا فلان ماه و روز بشود مثلا فروردین” و سال ذکر نشه.
ممنون میشم راهنمایی کنید
با سپاس
سلام، تاریخ و زمان در اکسل به صورت Serial Number هستند و هر روز و ساعت معادل یک عدد است، بنابراین هر روز و ساعت و ثانیه منحصر به فرد است و نمیتوان اعداد متناظر با ماه مشخص را جدا کرد، برای این کار باید از توابع تاریخ و زمان استفاده کنید، مثلا برای این مورد، تابع Month عدد ماه معادل با هر سریال نامبر را میدهد و میتوانید از آن برای تشخیص ماه ها استفاده نمایید.
در صورتی که میخواهید از فرمولی که مینویسید بهره برداری کنید، توصیه میکنم از Add-ins های تبدیل تاریخ میلادی به شمسی استفاده کنید، تبدیل تاریخ میلادی به شمسی در اکسل نسخه ۲۰۱۶ بدون استفاده از Addins هم ممکن شده است.
موفق باشید.