آموزش تابع IFERROR در اکسل – به زبان ساده + مثال کاربردی

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

سینتکس تابع IFERROR در اکسل و استفاده پایه‌ای از آن

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

فرمول این تابع را در ادامه می‌بینید:

1IFERROR(value, value_if_error)

• در این فرمول، آرگومان «Value»، ضروری است و مقدار موردنظر برای بررسی خطا را مشخص می‌کند. این آرگومان می‌تواند یک فرمول، عبارت، مقدار یا ارجاع سلولی در اکسل باشد.
• آرگومان ضروری «Value_if_error» نیز مقداری است که کاربر مشخص می‌کند در صورت بروز خطا، در خروجی نشان داده شود. به جای این آرگومان می‌توانید از یک رشته خالی (سلول خالی)، پیغام متنی، مقدار عددی یا حتی فرمول یا محاسبه دیگر نیز استفاده کنید.

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

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

استفاده از تابع IFERROR و رشته خالی

برای نمایش یک سلول خالی هنگام بروز خطا در هر فرمولی می‌توانید به جای آرگومان ‌Value_if_error، یک رشته خالی قرار دهید و فرمول زیر را بنویسید:

1=IFERROR(A2/B2, "")

نحوه نمایش پیام متنی شخصی سازی شده هنگام بروز خطا در اکسل

به کمک تابع IFERROR در اکسل همچنین می‌توانید پیامی کاملا شخصی سازی شده، در هنگام بروز خطا نمایش دهید.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

کافی است آرگومان دوم فرمول تابع را با پیام موردنظر داخل «علامت نقل قول»‌ ("") پر کنید و فرمول را به شکل زیر بنویسید:

1=IFERROR(A2/B2, "خطا در محاسبه")

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

در ادامه نکاتی را بررسی می‌کنیم که بهتر است پیش از کار کردن با این تابع از آن‌ها اطلاع داشته باشید.

فیلم آموزش اکسل – ابزارهای کاربردی Excel

کلیک کنید

• تابع IFERROR هر نوع خطایی در اکسل از جمله «DIV/0» ،«N/A» ،«NAME» ،«NULL» ،«NUM»، «REF» و «VALUE» را مدیریت می‌کند.
• بر اساس مقدار آرگومان Value_if_error، این تابع می‌تواند خطاها را با پیغام‌های متنی شخصی سازی شده، عدد، تاریخ، مقادیر منطقی، نتیجه یک فرمول دیگر یا حتی یک رشته خالی جایگزین کند.
• اگر مقدار آرگومان Value، یک سلول خالی باشد، فرمول با آن مانند یک رشته خالی برخورد خواهد کرد و آن را خطا در نظر نمی‌گیرد.
• تابع IFERROR از نسخه اکسل 2007 معرفی شده است و در تمام نسخه‌های پس از آن قابلیت استفاده دارد.
• برای مدیریت خطا در اکسل 2003 و نسخه‌های قدیمی‌تر می‌توانید از ترکیب دو تابع «ISERROR» و «IF» کمک بگیرید.

خطای Name در اکسل چیست؟

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

خطای تقسیم بر صفر در اکسل چیست؟

زمانی خطای DOV/0 در اکسل نشان داده می‌شود که قصد داشته باشید عددی را بر صفر تقسیم کنید. این کار به طور منطقی امکان پذیر نیست و مثلا نمی‌شود 5 مقدار را بر هیچ مقدار تقسیم کرد. برای رفع این خطا باید سلول موردنظر برای تقسیم در اکسل را بازبینی کرده و از وجود عدد صحیح در آن اطمینان حاصل کنید.

مثال‌های استفاده از تابع IFERROR

در ادامه مثال‌های مختلفی را می‌بینیم که نحوه استفاده از تابع IFERROR در اکسل را راحت‌تر توضیح می‌دهند.

ترکیب تابع IFERROR و تابع Vlookup

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

1IFERROR(VLOOKUP(…),"Not found")

اگر مقدار مورد جست‌وجو در جدول موردنظر نباشد، یک فرمول معمولی Vlookup، خطای «N/A#» را نشان می‌دهد.

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

1=IFERROR(VLOOKUP(A2, 'Lookup table'!$A$2:$B$4, 2,FALSE), "Not found")

نتیجه را می‌توانید در تصویر زیر ببینید:

اگر می‌خواهید فقط خطاهای N/A# را گیر بیندازید و باقی را نه، می‌توانید از تابع «IFNA» نیز به جای IFERROR کمک بگیرید.

خطای ‌Value در تابع Vlookup چیست؟

بروز خطای Value در تابع Vlookup معمولا به این معنی است که فرمول تابع به نوعی نادرست است. در بیشتر مواقع خطا به دلیل وجود اشکال در سلول ارجاعی به عنوان مقدار جست‌وجو، بروز می‌کند. حداکثر تعداد مقادیر برای جست‌وجو در تابع Vlookup، برابر 255 حرف است.

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

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

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

فرض کنید چند گزارش فروش از شعبات مختلف شرکت خود در دست دارید، و می‌خواهید مقادیر مرتبط با شناسه کالای خاصی را به‌دست بیاورید. با در نظر گرفتن سلول «A2« به عنوان مقدار موردنظر جست‌وجوی Vlookup و محدوده «A2:B5» به عنوان محدوده‌های مورد جست‌وجو در سه صفحه کاری (گزارش 1، گزارش 2 و گزارش 3)، فرمول شما به شکل زیر نوشته خواهد شد:

1=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 در فرمول‌های آرایه

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

• مطالب پیشنهادی برای مطالعه:
  • توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی (+ دانلود فیلم آموزش گام به گام)
  • تابع Lookup در اکسل چیست؟ – توضیح کاربرد + مثال
  • فرق فرمول و تابع در اکسل — به زبان ساده + فیلم آموزشی رایگان
  • تابع IF در اکسل — به زبان ساده
  • تابع SUMIF در اکسل — آموزش با مثال‌های کاربردی

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

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

فرمول تا جایی که در ستون مقسوم علیه، سلول خالی یا حاوی صفر نباشد، به درستی کار می‌کند. اگر حتی یک سلول خالی یا دارای صفر وجود داشته باشد، با خطای «DIV/0» مواجه می‌شوید.

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

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

به این ترتیب، فرمول ابتدا سلول‌های ستون B را در سلول‌های ستون C تقسیم می‌کند ( 100/2 ،200/5 و 0/0  )، و آرایه نتایج ( {50 :40: DIV/0}  ) را در خروجی قرار می‌دهد. سپس تابع IFERROR تمام خطاها را گرفته و آن‌ها را با مقدار «0» جایگزین می‌کند. پس از آن نیز تابع SUM، مقادیر موجود در آرایه نتیجه ( {50; 40; 0}  ) را به کمک جمع در اکسل، در نتیجه نهایی ( 50+40=90  ) نشان می‌دهد.

البته توجه داشته باشید که اگر از نسخه‌های اکسل 2019 و قبل‌تر استفاده می‌کنید، برای رسیدن به نتیجه درست با فرمول‌های آرایه باید پس از نوشتن فرمول، دکمه‌های «Ctrl+Shift+Enter» را فشار دهید.

مقایسه تابع IFERROR و ترکیب IF ISERROR

حالا که دیدید نحوه استفاده از تابع IFERROR در اکسل چقدر ساده است، شاید برایتان سوال باشد که چرا برخی کاربران همچنان از ترکیب دو تابع «IF ISERROR» کمک می‌گیرند و آیا استفاده از این ترکیب، مزایای دارد؟‌ جواب خیر است.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

باید بدانید که در نسخه اکسل 2003 و نسخه‌های قبل‌تر از آن، تابع IFERROR هنوز وجود نداشت و استفاده از ترکیب IF ISERROR، تنها راه ممکن برای رفع خطاها محسوب می‌شد. اما در اکسل 2007 و نسخه‌های بالاتر امکان استفاده از این تابع فراهم است.

مثلا شما می‌توانید برای رفع خطاهای Vlookup، هر دو فرمول زیر را استفاده کنید:

• در اکسل 2007 و بالاتر

1IFERROR(VLOOKUP(…), "پیدا نشد")

• در تمام نسخه‌های اکسل

1IF(ISERROR(VLOOKUP(…)), "پیدا نشد", VLOOKUP(…))

دقت کنید که در ترکیب IF ISERROR Vlookup باید دوبار از تابع Vlookup استفاده کرد. به زبان ساده می‌شود این فرمول را اینگونه تفسیر کرد: اگر تابع Vlookup در نتیجه به خطا منجر شد، عبارت «پیدا نشد»، نشان داده شود، در غیر اینصورت نتیجه خود تابع، نشان داده شود.

در ادامه همچنین یک مثال از دنیای واقعی برای درک ترکیب IF ISERROR داریم.

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

تابع IFERROR در مقابل IFNA

تابع IFNA که از نسخه اکسل 2013 معرفی شده است، نیز یکی دیگر از توابع مورد استفاده برای بررسی خطاها محسوب می‌شود و فرمول آن به شکل زیر است:

1IFNA(value, value_if_na)

تفاوت تابع ‌IFNA و IFERROR در این است که اولی فقط خطاهای N/A را برطرف می‌کند، درحالی که دومی برای رفع تمام انواع خطاهای اکسل کاربردی است.

زمانی از تابع IFNA استفاده کنید که برطرف کردن تمام خطاها، کار درستی نباشد. مثلا موقع کار با محدوده‌های عظیم و حساس داده، ممکن است بخواهید از وجود خطا در مجموعه خود مطلع شوید، و در این شرایط، خطاهای استاندارد اکسل که با عبارت «#» شروع می‌شوند، کمک بسیاری به شما خواهند کرد.

در ادامه می‌بینیم که چگونه می‌شود با استفاده از تابع IFNA، خطاهای N/A در شرایط عدم تطابق مقدار Vlookup را رفع کرد، اما سایر خطاها را نشان داد.

فرض کنید مجموعه داده زیر را در احتیار دارید و می‌خواهید مقدار «باقی مانده» را از جدول «جست‌وجو» گرفته و به جدول «خلاصه» بیاورید. اگر تابع IFERROR را بنویسید، به نتیجه بسیار تمیز و مناسبی دست خواهید یافت، که درواقع از نظر تکنیکی غلط است، چون مقداری برای «لیمو» در جدول جست‌وجو وجود ندارد.

در این شرایط برای رفع خطاهای N/A و نمایش خطاهای دیگر (در این مثال خطای DIV/0)، تابع IFNA را وارد عمل می‌کنیم و فرمول آن را می‌نویسیم:

1=IFNA(VLOOKUP(F3,$A$3:$D$6,4,FALSE), "پیدا نشد")

همچنین اگر با اکسل نسخه 2010 و قبل‌تر کار می‌کنید می‌توانید از ترکیب دو تابع IF ISNA، به شکل زیر کمک بگیرید:

1=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 هستند که در مراحل قبلی دیدیم.

همان‌طور که در تصویر زیر می‌بینید، عبارت «پیدا نشد» تنها برای مقداری نمایش داده می‌شود که در جدول جست‌وجو وجود ندارد (هلو). اما برای «لیمو»، همان خطای DIV/0 نشان داده می‌شود که یعنی امکان تقسیم بر صفر وجود ندارد.

سخن پایانی

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

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