آموزش تابع IFERROR در اکسل؛ جلوگیری از خطا با مثال‌های کاربردی

تابع IFERROR در اکسل چیست و چه کاربردی دارد؟

تابع IFERROR در اکسل را می توانیم به عنوان یکی از توابع کاربردی و منطقی معرفی نماییم. این تابع به کاربر این امکان را می دهد که به جای نمایش خطاهای آزار دهنده مانندN/A ، #DIV/0 ! و # VALUE ! در فرایند گزارش نویسی معادلات را به شکلی دقیق تر اجرا نماید. در نتیجه می توان گفت که تابع IFERROR در اکسل دقیقا برای مقابله با این مشکل طراحی و ارائه شده است. در نتیجه این تابع به شما این امکان را می دهد تا از بروز خطاها پیشگیری نمایید و معادلات را به شکلی اصولی هدایت نمایید.

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

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

سینتکس تابع IFERROR

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

آرگومان Value در تابع IFERROR

در ابتدا باید به این موضوع اشاره نماییم که فرمول و یا عبارتی که قصد داریم به بررسی بروز خطا در آن بپردازیم همین عبارت می باشد.

آرگومان Value_if_error در تابع IFERROR

مقداری که اگر در فرمول اول ایجاد خطا کند به جای آن به نمایش گذاشته می شود. این مقدار می تواند در قالب یک متن ( مثل ناموجود) و یا یک عدد مثل صفر به نمایش درآید. گاهی نیز کاربر برای نمایش این عبارت از سلول خالی بهره می برد.

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

برای درک بهتر تابع IFERROR در اکسل، ابتدا یک مثال ساده را بررسی کنیم. فرض کنید در سلول A1 قیمت یک کالا و در سلول B1 تعداد فروش آن کالا قرار دارد و می‌خواهیم با ضرب این دو مقدار، مبلغ فروش را محاسبه کنیم.

فرمول معمولی به شکل زیر است:

اگر مقادیر موجود در سلول‌ها معتبر باشند، اکسل نتیجه محاسبه را نمایش می‌دهد. اما اگر یکی از سلول‌ها حاوی مقدار نامعتبر باشد، ممکن است فرمول با خطا مواجه شود؛ برای مثال، اگر B1 به جای عدد شامل یک متن باشد، خطای #VALUE! نمایش داده می‌شود.

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

در این فرمول، اگر محاسبه A1*B1 بدون خطا انجام شود، نتیجه واقعی محاسبه نمایش داده می‌شود؛ اما اگر فرمول با هر یک از خطاهای اکسل مواجه شود، مقدار 0 به عنوان نتیجه جایگزین نمایش داده خواهد شد.

بنابراین ساختار کلی تابع به این صورت است:

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

کاربردهای واقعی تابع IFERROR در محیط کار

در محیط کار، گزارش‌های اکسل معمولاً شامل فرمول‌های زیادی هستند و ممکن است به دلایل مختلف با خطا مواجه شوند. نمایش خطاهایی مثل #DIV/0!، #VALUE! یا #N/A علاوه بر اینکه ظاهر گزارش را نامناسب می‌کند، می‌تواند خوانایی و استفاده از گزارش را هم دشوار کند.

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

برای مثال، فرض کنید در یک گزارش فروش می‌خواهیم میانگین مقادیر موجود در محدوده B2:B10 را محاسبه کنیم. اگر این محدوده شامل هیچ مقدار عددی نباشد، تابع AVERAGE با خطای #DIV/0! مواجه می‌شود.

فرمول معمولی:

خروجی در صورت نبود مقدار عددی، خطای #DIV/0! خواهد بود:

حالا می‌توانیم همین فرمول را با تابع IFERROR ترکیب کنیم:

در این حالت، اگر محاسبه میانگین با خطا مواجه شود، به جای نمایش #DIV/0!، مقدار 0 نمایش داده می‌شود.

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

ترکیب IFERROR با VLOOKUP

فرض نمایید لیستی از قیمت محصولات در اختیار دارید و می خواهید در جدول دیگری با وارد نمودن کد کالا و یا قیمت به آن محصول دسترسی یابید. در این شرایط اگر کدی را وارد نمایید که در لیست اصلی حضور ندارد خروجی نمایش داده شده به جای داده مورد نظر ما به شکل #N/A خواهد بود. این خطا باعث می شود که اگر شما بخواهید مجموع قیمت ها را حساب نمایید کل نتیجه نهایی با خطا مواجه خواهد شد. در نهایت می توان گفت رایج ترین ترکیب استفاده شده توسط کاربران ترکیب تابع IFERROR با تابع عمودی VLOOKUP می باشد. هر بار که مقدار جستجوی مورد نظر در جدول یافت نشود باز هم ما با خطای فوق مواجه می گردیم.

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

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

ترکیب IFERROR با XLOOKUP

در اکسل ارائه شده در 2021 و 365 ما شاهد بودیم که تابع مدرن XLOOKUP جایگزین مناسبی برای VLOOKUP بود. این تابع در واقع مجهز به آرگومان های داخلی برای نمایش خطاها می باشد. در نهایت ترکیب تابع XLOOKUP و IFERROR در اکسل را می توانیم به عنوان هوشمندانه ترین سیستم مدیریت برای خطا در اکسل معرفی نمود. البته این تابع خود مجهز به ویژگی های داخلی مدیریت خطا می باشد که بسیار کارآمد است. به خاطر داشته باشید که از ترکیب تابع IFERROR با XLOOKUP می توان به شکل سنتی استفاده نمود. در این روش شما ابتدا جستجو را انجام خواهید داد و در صورتی که اکسل با خطای #N/A مواجه شود تابع IFERROR موضوع را درک و در نهایت با نمایش پیام مناسب مشکل را مرتفع می کند. فرمول تابع در این شرایط به شکل زیر خواهد بود:

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

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

ترکیب IFERROR با INDEX و MATCH

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

در این تابع اگر عبارت MATCH قادر به یافتن مقدار نباشد ما با خطای # N/A مواجه خواهیم شد. در نهایت ترکیب این دو تابع با یکدیگر سبب می شود تا پیام دلخواه کاربر در گزارش نمایش داده شود.

جلوگیری از نمایش خطای تقسیم بر صفر

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

نمایش سلول خالی به جای خطا

اگر شما نمی خواهید هیچ پیامی در عبارت نمایش داده شود تنها کافی است آرگومان دوم را با یک رشته خالی “” جایگزین نمایید.

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

تفاوت IFERROR و IFNA در اکسل؛ کدام را استفاده کنیم؟

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

چه زمانی IFNA بهتر از IFERROR است؟

بهترین زمان استفاده از تابع IFNA زمانی است که از توابعی مانند VLOOKUP استفاده می نماییم. اگر مقدار پیدا نشود اکسل اقدام به نمایش خطای #N/A خواهد نمود. برای مثال ممکن است اگر تنها از تابع IFERROR استفاده نمایید خطاهای مهم محاسباتی در پشت یک پیام سفارشی نادیده گرفته شود. در این حالت ممکن است شما متوجه اشتباه بودن فرمول خود نشوید. در نهایت اگر شما در اکسل خطایی مانند خطای تایپی دارید مایل خواهید بود که این موارد برای شما به نمایش گذاشته شود. در این شرایط بهترین انتخاب برای ما کاربرد تابع IFNA است.

اشتباهات رایج در استفاده از IFERROR

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

• پنهان کردن خطاهای واقعی

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

• فراموش کردن علامت نقل قول برای متن

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

روش اشتباه:

روش صحیح:

دلیل این موضوع این است که اکسل عبارت بدون دابل‌کوت را به عنوان نام یا ارجاع در نظر می‌گیرد، نه یک متن ساده.

• استفاده از IFERROR در جاهایی که نیازی به آن نیست

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

• استفاده از IFERROR در فرمول‌های آرایه‌ای

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

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

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

• آیا IFERROR همه انواع خطا در اکسل را مدیریت می کند؟

بله تابع IFERROR در اکسل قادر به بررسی هر نوع پیام خطایی است. در این بین تمام خطاهای # N/A ، #VALUE! ، #REF! ، #DIV/0! ، #NUM! ، #NAME ? و # NULL ! به آرگومان دوم تبدیل می گردند.

• تفاوت IFERROR و ISERROR چیست؟

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

• آیا IFERROR روی اکسل موبایل کار می کند؟

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

• چطور به جای خطا سلول خالی نمایش بدیم؟

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

• آیا می توان IFERROR را تودرتو استفاده کرد؟

بله ما می توانیم چند تابع IFERROR را در هم ادغام کنیم تا اولویت های مختلف برای مدیریت خطا ایجاد نماییم.

جمع بندی

تابع IFERROR در اکسل یکی از آن ابزارهایی است که می تواند به کاربر در مدیریت فضای گزارش ها کمک نماید. در واقع این تابع می تواند فضای بهم ریخته اکسل را به فضایی مرتب تبدیل نماید. با کمک این تابع ما می توانیم فرمول های جستجو مانندVLOOKUP ، XLOOKUP و INDEX-MATCH را ایمن کنیم. همچنین این تابع می تواند از بروز خطای تقسیم بر صفر اجتناب کند و به جای ایجاد پیام های فنی و غیر قابل فهم از پیام های واضح با ساختار گویش انسانی برای انتقال مفهوم بهره ببرد. اما مهم ترین درسی که کاربران در این مقاله خواهند آموخت این مورد است که از این تابع به شکل عاقلانه باید استفاده نمایید.

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