آموزش تابع iferror در اکسل با مثال (به زبان ساده)

آموزش امروز ما نشان می دهد که چگونه از تابع IFERROR در اکسل برای گرفتن خطاها و جایگزینی آنها با یک سلول خالی، یک مقدار دیگر یا یک پیام سفارشی استفاده کنید. با استفاده از این راهنما شما یاد می گیرید که چطور از تابع IFERROR با Vlookup و Index Match استفاده کنید. علاوه بر این ما نحوه مقایسه این تابع با IF ISERROR و IFNA . را هم بررسی می کنیم.

ارشمیدس در جایی گفته است: «جایی را برای ایستادن به من بده و من زمین را حرکت خواهم داد.» شما به عنوان یک کاربر اکسل می توانید بگویید: «فرمولی به من بدهید و من خطای آن را کشف می کنم». در این آموزش، ما با نگاهی به چگونگی بازگرداندن خطاها در اکسل، نحوه جلوگیری از بروز آنها برای تمیز نگه داشتن کاربرگ ها و شفافیت فرمول ها را یاد می گیریم.

نکته: در انتهای همین مطلب ویدیوی آموزشی تابع iferror نیز وجود دارد. اگر آموزش گام به گام زیر را دوست نداشتید می توانید از طریق ویدیو یاد بگیرید

کاربردهای اساسی (نحو) این تابع

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

نحو تابع IFERROR در اکسل به شرح زیر است:

IFERROR(value, value_if_error)

که:

Value (لازم) – آنچه که برای خطاها چک می‌شود. این می تواند یک فرمول، عبارت، مقدار یا مرجع سلول باشد.
Value_if_error(الزامی) چیزی که باید در صورت مشاهده خطا بازگرداند. این می تواند یک رشته خالی (سلول خالی)، پیام متنی، مقدار عددی، فرمول یا محاسبه دیگری باشد.
به عنوان مثال، هنگام تقسیم دو ستون اعداد، اگر یکی از ستونها حاوی سلولهای خالی، صفر یا متن باشد، ممکن است یک دسته از خطاهای مختلف را دریافت کنید.

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

اگر خطا داد، پس خالی بشود

یک رشته خالی (“) به آرگومان value_if_error ارائه دهید تا در صورت مشاهده خطا، یک سلول خالی را برگرداند:

=IFERROR(A2/B2, “”)

اگر خطا داد، پیامی به شما نشان داده می شود.

همچنین می توانید پیام خود را به جای علامت گذاری استاندارد خطای اکسل نمایش دهید:

=IFERROR(A2/B2, “Error in calculation”)

۵ نکته ای که باید در مورد تابع IFERROR در اکسل بدانید

۱- تابع IFERROR در اکسل همه خطاها از جمله !DIV/0!، #N/A، #NAME?، #NULL!، #NUM!، #REF# و !VALU# را کنترل می کند.

۲- بسته به محتویات آرگومان value_if_error،IFERROR می تواند خطاها را با پیام متنی سفارشی، شماره، تاریخ یا مقدار منطقی، نتیجه فرمول دیگر یا رشته خالی (سلول خالی) جایگزین کند.

۳- اگر آرگومان مقدار یک سلول خالی باشد، به عنوان یک رشته خالی (“”) رفتار می شود اما خطا نیست.

۴- IFERROR در اکسل ۲۰۰۷ معرفی شد و در تمام نسخه های بعدی اکسل ۲۰۱۰، اکسل ۲۰۱۳ و اکسل ۲۰۱۶ موجود است.

۵- برای به دام انداختن خطاها در اکسل ۲۰۰۳ و نسخه های قبلی، از تابع ISERROR در ترکیب با IF استفاده کنید، همانطور که در این مثال نشان داده شده است.

مثال های فرمول IFERROR

مثال های زیر نحوه استفاده از IFERROR در اکسل در ترکیب با سایر توابع برای انجام کارهای پیچیده تر را نشان می دهد.

IFERROR اکسل با Vlookup

یکی از رایج ترین کاربردهای تابع IFERROR این است که به کاربران بگویید مقداری که آنها جستجو می کنند در مجموعه داده وجود ندارد. برای این، شما یک فرمول VLOOKUP را در IFERROR مانند این جمع بندی می کنید:

IFERROR(VLOOKUP(…),”Not found”)

اگر مقدار جستجو در جدول مورد نظر شما نباشد، یک فرمول منظم Vlookup خطای #N/A را برمی گرداند:

برای ذهن کاربران خود، VLOOKUP را در IFERROR قرار دهید و یک پیام مفید و کاربرپسندتر را نمایش دهید:

=IFERROR(VLOOKUP(A2, ‘Lookup table’!$A$2:$B$4, 2,FALSE), “Not found”)

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

اگر می خواهید فقط خطاهای N/A# را به دام بیندازید، اما نه همه خطاها، به جای IFERROR از تابع IFNA استفاده کنید.
برای مثال های بیشتر فرمول Excel IFERROR VLOOKUP، لطفاً این آموزش ها را ببینید:

توابع IFERROR تو در تو برای انجام Vlookup های متوالی در اکسل

در شرایطی که شما نیاز به انجام چندین Vlookup دارید بر اساس موفقیت یا عدم موفقیت Vlookup قبلی، می توانید دو یا چند تابع IFERROR را در یکدیگر لانه کنید.

با فرض اینکه شما تعدادی گزارش فروش از شعب منطقه ای شرکت خود دارید و می خواهید برای شناسه سفارش خاصی مبلغی دریافت کنید. با A2 به عنوان مقدار جستجو در ورق فعلی، و A2 B5 به عنوان دامنه جستجو در ۳ صفحه جستجو (گزارش ۱، گزارش ۲ و گزارش ۳)، فرمول به شرح زیر است:

=IFERROR(VLOOKUP(A2,’Report 1′!A2:B5,2,0),IFERROR(VLOOKUP(A2,’Report 2′!A2:B5,2,0),IFERROR(VLOOKUP(A2,’Report 3′!A2:B5,2,0),”not found”)))

نتیجه تقریباً به صورت زیر خواهد شد:

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

IFERROR در فرمول های آرایه ای

همانطور که احتمالاً می دانید، فرمول های آرایه در اکسل برای انجام چندین محاسبه در یک فرمول واحد است. اگر فرمول آرایه یا عبارتی را ارائه دهید که منجر به آرایه ای در آرگومان مقدار تابع IFERROR شود، می تواند آرایه ای از مقادیر را برای هر سلول در محدوده مشخص شده بازگرداند. مثال زیر جزئیات را نشان می دهد.

فرض کنیم شما در ستون B مجموع و در ستون C قیمت دارید و می خواهید مقدار کل را محاسبه کنید. این را می توان با استفاده از فرمول آرایه زیر انجام داد، که هر سلول را در محدوده B2:B4 به سلول مربوطه از محدوده C2:C4 تقسیم می کند، و سپس نتایج را جمع می کند:

=SUM($B$2:$B$4/$C$2:$C$4)

این فرمول تا زمانی خوب کار می کند که محدوده تقسیم کننده صفر یا سلول خالی نداشته باشد. اگر حداقل یک مقدار ۰ یا یک سلول خالی وجود دارد،#DIV/0! خطا برگردانده می شود:

برای رفع این خطا، به سادگی تقسیم را در تابع IFERROR انجام دهید:

=SUM(IFERROR($B$2:$B$4/$C$2:$C$4,0))

کاری که فرمول انجام می دهد این است که مقداری را در ستون B بر مقدار ستون C در هر سطر تقسیم کند (۲/۱۰۰، ۵/۲۰۰ و ۰/۰) و آرایه نتایج را برگرداند {۵۰؛ ۴۰؛#DIV/0! }عملکرد IFERROR همه خطاهای #DIV/0! را می گیرد و آنها را با صفر جایگزین می کند. و سپس، تابع SUM مقادیر موجود در آرایه حاصل را جمع می کند {۵۰؛ ۴۰؛ ۰} و نتیجه نهایی را خارج می کند (۹۰=۴۰+۵۰).

نکته: لطفاً به یاد داشته باشید که فرمول های آرایه باید با فشار دادن میانبر Ctrl+Shift+Enter تکمیل شوند.

IFERROR در مقایسه با IF ISERROR

اکنون که می دانید استفاده از تابع IFERROR در اکسل چقدر آسان است، ممکن است تعجب کنید که چرا برخی هنوز به استفاده از ترکیب IF ISERROR متمایل هستند. آیا مزیتی نسبت به IFERROR دارد؟ هیچ یک. در روزهای بد اکسل یعنی سال ۲۰۰۳ و قبل تر، زمانی که IFERROR وجود نداشت، IF ISERROR تنها راه ممکن برای به دام انداختن خطاها بود. در اکسل ۲۰۰۷ و بعد از آن، این روش کمی پیچیده تر برای دستیابی به همان نتیجه است.

به عنوان مثال، برای گرفتن خطاهای Vlookup، می توانید از فرمول های زیر استفاده کنید.
در اکسل ۲۰۰۷- اکسل ۲۰۱۶:

IFERROR(VLOOKUP(…), “Not found”)

در تمامی ورژن ها:

IF(ISERROR(VLOOKUP(…)), “Not found”, VLOOKUP(…))

توجه داشته باشید که در فرمول IF ISERROR VLOOKUP، شما باید دو بار Vlookup کنید. در انگلیسی ساده، فرمول را می توان به صورت زیر خواند اگر Vlookup منجر به خطا شد، «Not found» را برگردانید، در غیر این صورت نتیجه Vlookup را خارج کنید.

و در اینجا یک مثال واقعی از فرمول Excel If Iserror Vlookup آورده شده است:

=IF(ISERROR(VLOOKUP(D2, A2:B5,2,FALSE)),”Not found”, VLOOKUP(D2, A2:B5,2,FALSE ))

۹ روش رند کردن (گردکردن) در اکسل (ساده ترین متدها)

IFERROR در مقایسه با IFNA

IFNA که با اکسل ۲۰۱۳ معرفی شده است، یکی از تابع های دیگر برای بررسی فرمول خطاها است. نحو آن شبیه به IFERROR است:

IFNA(value, value_if_na)

از چه لحاظ IFNA با IFERROR متفاوت است؟ تابع IFNA فقط خطای N ​​/A# را نمی گیرد در حالی که IFERROR همه نوع خطا را کنترل می کند.
در چه شرایطی ممکن است بخواهید از IFNA استفاده کنید؟ وقتی غیر منطقی است که همه خطاها را پنهان کنیم. به عنوان مثال، هنگام کار با داده های مهم یا حساس، ممکن است بخواهید در مورد خطاهای احتمالی مجموعه داده هایتان هشدار داده شود و پیام های خطای استاندارد اکسل با علامت «#» می تواند شاخص های دیداری واضحی باشد.

بیایید ببینیم که چگونه می توانید فرمولی ایجاد کنید که به جای خطای N/A، پیام «Not found» نمایش داده شود، این حالت وقتی ظاهر نمی شود که مقدار جستجو در مجموعه داده وجود ندارد، اما توجه شما را به سایر خطاهای اکسل جلب می کند.

با فرض اینکه می خواهید تعداد را از جدول مراجعه به جدول خلاصه همانطور که در تصویر زیر نشان داده شده است بکشید. استفاده از فرمول Excel Iferror Vlookup نتیجه دلپذیری از نظر زیبایی ایجاد می کند که از نظر فنی نادرست است زیرا لیموها در جدول جستجو وجود دارند:

برای گرفتن #N/A اما نمایش خطای #DIV/0، از تابع IFNA در Excel 2013 و Excel 2016 استفاده کنید:

=IFNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE), “Not found”)

یا، ترکیب IF ISNA در اکسل ۲۰۱۰ و نسخه های قبلی:

=IF(ISNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE)),”Not found”, VLOOKUP(F3,$A$3:$D$6,4,FALSE))

نحو فرمولهای IFNA VLOOKUP و IF ISNA VLOOKUP مشابه فرمولهای IFERROR VLOOKUP و IF ISERROR VLOOKUP است که قبلاً بحث شد.
همانطور که در تصویر زیر نشان داده شده است، فرمول Ifna Vlookup فقط برای موردی که در جدول جستجو وجود ندارد (هلو) «Not found» را برمی گرداند. برای لیمو، DIV/0 را نشان می دهد نشان می دهد که جدول جستجوی ما دارای خطای تقسیم بر صفر است:

بهترین روشها برای استفاده از IFERROR در اکسل

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

۱- خطاها را بدون دلیل به دام نیندازید.

۲- کوچکترین قسمت ممکن از فرمول را در IFERROR جمع کنید.

۳- برای مدیریت فقط خطاهای خاص، از یک تابع مدیریت خطا با دامنه کوچکتر استفاده کنید:
IFNA* یا IF ISNA فقط خطاهای N/A# را می گیرد.
ISERR* برای گرفتن همه خطاها به جز /N#.

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

ویدیوی آموزشی

۳ روش ایجاد لیست کشویی در اکسل (گام به گام) +فیلم