تابع IFNA

تابع IFNA خطای نوع #N/A را به دام می‌اندازد و به سایر انواع خطا کاری ندارد.

◀️ در اینجا عبارت N/A به معنی Not Available (به معنی در دسترس نبودن، وجود نداشتن یا پیدا نشدن) است.

✅ کاری که IFNA انجام می‌دهد این است:

1. یک ورودی را دریافت می‌کند: مثلا یک فرمول یا آدرس سلول.
2. اگر در ورودی خطای #N/A وجود نداشت، همان را در خروجی قرار می‌دهد.
3. اما اگر ورودی خطای #N/A داشت، مقداری که شما تعیین کرده‌اید (متن، عدد یا فرمول دیگر) را برمی‌گرداند.

✅ به کمک این تابع می‌توانید از بروز خطای #N/A (مثلاً هنگام جستجوی یک مقدار به کمک توابع اکسل در یک جدول) جلوگیری کنید و یا برای اطلاع کاربر پیام دلخواهی به جای خطای #N/A نمایش دهید.

در ادامه بیشتر با این تابع آشنا می‌شوید:

نحوه نوشتن تابع IFNA

تابع IFNA به شکل زیر است:

=IFNA(value,value_if_na)

• value: مقدار اصلی (مقدار ثابت، مرجع، آرایه، متغیر یا فرمولی) که وجود خطا در آن بررسی می‌شود (که اگر خطا نداشت همان مقدار اصلی در خروجی قرار می‌گیرد).
• value_if_na: مقداری جایگزین که می‌خواهید در صورت وقوع خطای #N/A، به جای ورودی خطا نمایش داده شود.

اگر ورودی‌ها و خروجی تابع IFNA را به شکل یک تابع ریاضی ببینیم، می‌توانیم نمودار زیر را رسم کنیم:

توجه داشته باشید:

• معمولاً معنی خطای #N/A به این معنی است که اکسل مقداری را پیدا نمی‌کند یا آن مقدار در دسترس نیست.
• معمولاً این پیدا نکردن در حین فرمول‌نویسی رخ می‌دهد. در واقع این خطا در حین پردازش داده‌ها در اکسل و توسط فرمول‌های شما رخ می‌دهد و معمولاً این طور است که داده‌ها ذاتاً این خطا را ندارند.
• توجه داشته باشید که خطای #N/A به معنای وجود مشکل اساسی در فرمول‌ها یا مدل شما هم نیست، بلکه ممکن است صرفاً یک اطلاع‌رسانی از سمت اکسل باشد؛ یعنی مثل این است که اکسل به شا بگوید: "آنچه به دنبالش هستید یافت نمی‌شود".
• توجه داشته باشید که تابع IFNA صرفاً در مورد خطای #N/A است و سایر خطاها را نادیده می‌گیرد.
• در واقع برخلاف IFERROR که همه خطاها را به دام می‌اندازد، تابع IFNA خطاهای جدی‌تر و اساسی تر را پنهان نمی‌کند و این موضوع کمک می‌کند که زودتر و بهتر متوجه خطاهای پنهان فرمول‌نویسی و اشکالات احتمالی مدل‌های خود شوید.
• برخی از سایر خطاها که معروف هستند، این موارد هستند:
  • #VALUE! وقتی رخ می‌دهد که ورودی‌های فرمول اشتباه وارد شده‌اند.
    
  • #REF! وقتی رخ می‌دهد که ورودی‌ها از جنس ارجاع هستند ولی نوع ارجاع اشتباه است.
    
  • #DIV/0! وقتی رخ می‌دهد که خطای تقسیم عدد بر صفر در فرمول شما وجود داشته باشد.
    
  • #NAME? وقتی رخ می‌دهد که اشتباه در نوشتن نام تابع و فرمول‌نویسی داشته باشید.
    

در ادامه ابتدا نحوه وارد کردن اطلاعات در تابع IFNA را خواهید دید و سپس مثال‌هایی ازترکیب این تابع با سایر توابع را ملاحظه خواهید کرد:

ورود اطلاعات در تابع IFNA

ورودی‌های تابع IFNA می‌توانند شکل‌های مختلفی داشته باشند که در اینجا به آن می‌پردازیم.

مقادیر ثابت

ورودی‌های تابع IFNA می‌تواند مقادیر ثابت باشد. در اینجا 2 مثال را بررسی می‌کنیم که البته کاربرد خاصی ندارند و صرفاً جنبه آموزشی دارند.

1️⃣ مقادیر ثابت عددی

(صرفاً برای یادگیری) فرمول زیر را در نظر بگیرید:

=IFNA(1,2)

? مسیر ذهنی زیر را در نظر بگیرید:

1. در فرمول بالا هر دو ورودی به صورت مقدار ثابت است.
2. چون مقدار ورودی اول (مقدار 1) خطایی به همراه ندارد،
3. بنابراین تابع IFNA به ورودی دوم (عدد 2) نگاه نمی‌کند و همان ورودی اول را در خروجی برمی‌گرداند.
4. بنابراین خروجی تابع بالا عدد 1 است.

حالا بیایید یک مثال دیگر را بررسی کنیم:

2️⃣ مقادیر ثابت متنی و منطقی

(صرفاً برای یادگیری) تابع زیر را در نظر بگیرید:

=IFNA(FALSE,"Text")

در فرمول بالا، ? مسیر ذهنی زیر را دنبال کنید:

1. تابع IFNA در ورودی اول به دنبال خطا از نوع #N/A می‌گردد.
2. اما محتوای ورودی اول خطا ندارد بلکه فقط یک عبارت منطقی است.
3. پس IFNA به ورودی دوم اصلاً کاری ندارد و همان ورودی اول یعنی عبارت منطقی FALSE را مستقیماً در خروجی می‌نویسد.

ارجاع به سلول

فرض کنید در سلول A1 فرمولی نوشته باشید. در این صورت می‌توانید با تابع زیر خطای #N/A را در مورد این سلول به دام بیندازید:

=IFNA(A1,1)

در تابع بالا، ? مسیر ذهنی زیر را دنبال کنید:

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

فرمول‌ها

ورودی‌های تابع IFNA می‌تواند فرمول باشد. در اینجا 2 مثال را بررسی می‌کنیم که باز هم جنبه آموزشی دارند!

1️⃣ تقسیم اعداد

اتابع زیر را در نظر بگیرید:

=IFNA(1/10,10/0)

? مسیر ذهنی زیر را دنبال کنید:

1. هر دو ورودی تابع IFNA از فرمول تشکیل شده‌اند.
2. تابع IFNA به ورودی اول نگاه می‌کند و چون خطایی در آن نمی‌بیند، بنابراین همان را در خروجی قرار می‌دهد ( مقدار 0.1).
3. توجه کنید که با اینکه ورودی دوم دارای خطای تقسیم بر صفر است، اما اساساً توسط تابع IFNA بررسی نمی‌شود.

بیایید یک مثال دیگر بررسی کنیم:

2️⃣ ترکیب تابع NA و تابع IFNA

✅ تابعی به نام NA در اکسل وجود دارد که کار آن تولید خطای #N/A است. در اینجا فرض کنید می‌خواهیم دو تابع NA و IFNA را با هم ترکیب کنیم:

=IFNA(NA() , "Error")

? مسیر ذهنی زیر را دنبال کنید:

1. ورودی اول تابع IFNA، تابع NA است. این تابع خطای #N/A را تولید می‌کند.
2. پس ورودی اول تابع IFNA با خطای #N/A مواجه خواهد شد.
3. حالا تابع IFNA، ورودی دوم خود (یعنی "Error") را به عنوان خروجی برمی‌گرداند.

به عبارت دیگر، نتیجه فرمول بالا همیشه ورودی دوم یا "Error" خواهد بود.

ارجاع به محدوده‌ها

تابع IFNA از محدوده‌ها و آرایه‌ها پشتیبانی می‌کند.

مثلاً اگر در ورودی اول تابع IFNA یک محدوده مثل A1:A10 را وارد کنید، آنگاه تابع IFNA آن را به شکل یک آرایه متشکل از 10 عنصر می‌بیند و وجود خطای #N/A را در همه آن 10 سلول‌ها تک تک بررسی می‌کند.

در همین رابطه، تابع زیر را در نظر بگیرید:

=IFNA (A1:A10,"Error")

در فرمول بالا، ? مسیر ذهنی زیر را دنبال کنید:

1. تابع IFNA ناحیه A1:A10 را همزمان دریافت می‌کند و آن را به شکل یک آرایه با 10 عضو می‌بیند.
2. حالا تابع IFNA به صورت تک تک وجود خطا را در هر کدام از این 10 ورودی بررسی می‌کند و یک آرایه می‌سازد.
3. (بسته به ورژن و نحوه نوشتن فرمول) اکسل خروجی تابع IFNA (که آرایه است) را در 10 سلول از کاربرگ نشان می‌دهد.

✅ فرمول بالا در واقع یک پردازش آرایه‌ای است:

1. چون قرار است تابع IFNA برای هر یک از 10 ورودی، یک جواب جداگانه به ما بدهد.
2. در واقع ما برای نمایش کامل نتیجه فرمول قبل در محیط کاربرگ، به 10 سلول نیاز داریم.
3. در ورژن‌های جدید اکسل (نسخه 2021 به بعد)، اکسل از Dynamic Arrays یا آرایه‌های پویا پشتیبانی می‌کند. بنابراین به طور خودکار ناحیه مناسب برای نمایش این سلول‌ها را در نظر می‌گیرد و نتیجه را نشان می‌دهد.
4. در ورژن‌های قبلی، باید فرمول آرایه‌ای بنویسید. یعنی ناحیه را انتخاب کنید، سپس برای یک سلول فرمول‌نویسی را انجام دهید و سپس با فشار دادن همزمان کلیدهای Ctrl و Alt و Enter، به اکسل بگویید که شما در حال نوشتن یک فرمول آرایه‌ای هستید. سپس اکسل خروجی را در سلول‌های کاربرگ قرار می‌دهد.

نوشتن مستقیم آرایه‌های ثابت

همچنین در ورودی تابع IFNA می‌توانید از آرایه‌های ثابت هم استفاده کنید:

=IFNA ({1,2,3},"Error")

در فرمول بالا، ? مسیر ذهنی زیر را در نظر بگیرید:

1. ورودی اول یک آرایه است و تابع IFNA برای هر یک از عناصر این آرایه اجرا خواهد شد.
2. بنابراین خروجی تابع IFNA در نهایت به صورت یک آرایه خواهد بود.
3. اما وقتی تابع IFNA در تمام عناصر آرایه یعنی {1,2,3}، به دنبای خطای #N/A می‌گردد، خطایی پیدا نمی‌کند.
4. بنابراین همان آرایه را به خروجی خواهد فرستاد و نتیجه تابع همان آرایه است.

در ورژن‌های جدید اکسل که از Dynamic Array پشتیبانی می‌کنند، اکسل خودبه‌خود 3 سلول را برای نمایش آرایه فوق در نظر خواهد گرفت.

توجه کنید که در مثال قبل اگر مثلاً دوم آرایه دارای خطا می‌بود، خروجی نهایی تابع IFNA به صورت زیر تغییر می‌کرد:

{1,"Error",3}

ارجاع به متغیرها

این امکان وجود دارد که در ورودی‎های تابع IFNA از متغیرهای تعریف شده در محیط اکسل استفاده کنید. تعریف متغیر را می‌توانید به کمک ابزار Name Manager در تب Formula انجام دهید و سپس نام متغیرها را خواهید توانست داخل فرمول‌ها به‌کار ببرید.

برای مثال فرض کنید دو متغیر در محیط اکسل تعریف کرده‌اید:

◽ یک متغیر با نام Input که ورودی اول IFNA را در خود دارد.

◽ یک متغیر با NA_Message که یک پیام متنی است و قرار است در ورودی دوم تابع IFNA استفاده شود.

حالا می‌توانید این دو متغیر را به جای ورودی‌های تابع IFNA استفاده کنید:

=IFNA (Input , NA_Message)

عملکرد IFNA در مواجهه با انواع خطاها

همان طور که گفتیم، تابع IFNA مربوط به تله انداختن خطای #N/A (یا همان خطای عدم دسترسی یا وجود نداشتن یک مقدار) است.

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

? متن زیر را با تمرکز و به آرامی بخوانید! ?

چون تابع IFNA دو ورودی دارد، بیایید دو حالت زیر را بررسی کنیم:

1️⃣ اگر ورودی اول IFNA دارای خطا باشد:

1. اگر خطای ورودی اول از نوع #N/A باشد، (طبق تعریف) تابع IFNA آن خطا را به دام می‌اندازد و به ورودی دوم مراجعه می‌کند (حالا خروجی نهایی تابع به ورودی دوم وابسته است)
2. اگر خطای ورودی اول غیر از نوع #N/A باشد، تابع IFNA نمی‌تواند آن را به تله بیندازد. پس همان خطای ورودی اول را در خروجی نشان می‌دهد.

2️⃣ اگر ورودی دوم IFNA دارای خطا باشد:

1. اگر تابع IFNA به ورودی دوم نیاز پیدا نکند، اصلاً به ورودی دوم نگاه نمی‌کند و مهم نیست که خطا دارد یا ندارد!
2. اگر تابع IFNA به ورودی دوم مراجعه کند و خطایی ببیند (هر نوع خطایی)، تابع IFNA خطای ورودی دوم را مدیریت نخواهد کرد و شما در خروجی همان خطا را خواهید دید.

✅ طبق آنچه گفته شد:

? تابع IFNA فقط خطای #N/A ورودی دوم را مدیریت می‌کند.

? تابع IFNA در ورودی دوم مدیریت خطا ندارد.

? تا زمانی که از ورودی دوم استفاده نشود (نیاز نباشد)، در خروجی وجود خطا در ورودی دوم را متوجه نمی‌شوید.

⬅️حالا بیایید از طریق یک مثال اثر وجود خطا در ورودی‌های تابع IFNA را بررسی کنیم.

برای نمونه تابع زیر را در نظر بگیرید که در ورودی اول آن مقدار سلول A1 بر صفر تقسیم می‌شود و در ورودی دوم، مقدار "Err" قرار گرفته است:

=IFNA(A1/0,"Err")

? مسیر ذهنی زیر را در نظر بگیرید:

1. ورودی دوم تابع IFNA عبارت متنی "Err" است و ورودی دوم خطایی ندارد. پس در این مثال فقط بررسی خطا در ورودی اول مطرح است.
2. اگر ورودی اول دارای خطای #N/A باشد، آنگاه خروجی IFNA مقدار "Err" است. برای این موضوع لازم است که محتوای سلول A1 به هر دلیلی دارای خطای #N/A باشد.
3. اگر ورودی اول دارای خطایی غیر از #N/A باشد، IFNA آن را مدیریت نمی‌کند و همان خطا را در خروجی برمی‌گرداند.

بیایید مورد سوم را بیشتر بررسی کنیم. یعنی حالتی که خطایی غیر #N/A رخ می‌دهد.

همان مثال را دوباره اینجا می‌آوریم:

=IFNA(A1/0,"Err")

حالا 3️⃣ سه سناریوی ? زیر را به عنوان نمونه در نظر بگیرید:

• فرض کنید که A1 مقداری عددی داشته باشد، آنگاه فرمول A1/0 با خطای تقسیم بر صفر #DIV/0! مواجه می‌شود. اما تابع IFNA نمی‌تواند این خطا به تله بیندازد. بنابراین همان خطای تقسیم بر صفر در خروجی دیده خواهد شد.
• فرض کنید که A1 مقداری متنی داشته باشد، آنگاه چون عمل تقسیم نمی‌تواند برای متن تعریف شود، اکسل خطای #Value! می‌دهد. اما تابع IFNA نمی‌تواند این نوع خطا را به دام بیندازد. در نتیجه همان خطا به خروجی می‌رود.
• فرض کنید که A1 یک فرمول اشتباه داشته باشد، مثلاً فرض کنید در A1 به جای نام تابع SUM عبارت SAM نوشته شده باشد، در این صورت اکسل خطای #Name? می‌دهد که به معنی تشخیص ندادن نام تابع است. اما IFNA نمی‌تواند این خطا را به دام بیندازد، بنابراین همان خطا به خروجی می‌رود.

استفاده از IFNA در ترکیب با سایر توابع

✅ معمولاً استفاده اصلی تابع IFNA در ترکیب با سایر توابع اکسل است. در اینجا دو نمونه می‌آوریم:

ترکیب تابع IFNA با تابع MODE

در اکسل تابع MODE عددی را که بیشترین تکرار را در میان یک سری از اعداد دارد، پیدا می‌کند. به این عدد مد یا نما می‌گویند.

البته در ورژن‌های جدید اکسل می‌توانید از تابع MODE.SNGL استفاده کنید.

✏️ در مثال زیر، عدد 1 بیشتر از بقیه تکرار شده است. پس خروجی تابع زیر برابر 1 است:

=MODE(1,1,1,2,9)

✏️ اما خروجی تابع زیر خطای #N/A است. چون اکسل نمی‌تواند عددی را پیدا کند که بیشترین تکرار را داشته باشد:

=MODE(1,2,3)

✅ حالا که کار با تابع MODE را می‌شناسید، فرمول زیر را در نظر بگیرید:

=IFNA( MODE(1,2,3), "No-Mode")

چون ورودی اول دارای خطا است، پس تابع IFNA ورودی دوم را برمی‌گرداند که همان عبارت متنی "No-Mode" است.

ترکیب تابع IFNA با توابع جستجو

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

? اما یکی از خطاهایی که این توابع ممکن است به شما بدهند، خطای #N/A است.

✅ در واقع معمولاً وقتی توابع جستجو نتوانند چیزی را پیدا کنند، خطای #N/A می‌دهند. در این حالت برای مدیریت خطا می‌توانید از IFNA استفاد کنید.

مثلاً تابع MATCH در اکسل برای پیدا کردن موقعیت نسبی یک مقدار خاص در یک آرایه یا محدوده از سلول‌ها استفاده می‌شود.

البته در اینجا نمی‌خواهیم تابع MATCH را توضیح دهیم و فقط می‌خواهیم نشان دهیم که این تابع می‌تواند با IFNA ترکیب شود.

صرفاً برای آشنایی با تابع MATCH، مثال زیر را در نظر بگیرید:

=MATCH("B",{"A","B","C"})

? تابع بالا، متن "B" را در آرایه {"A","B","C"} جستجو می‌کند. چون "B" در جایگاه دوم از لیست قرار گرفته است، پس تابع بالا مقدار 2 را برمی‌گرداند.

اما حالا تابع زیر را در نظر بگیرید:

=MATCH("X",{"A","B","C"})

? مقدار X در آرایه وجود ندارد، پس خروجی تابع بالا خطای #N/A است.

✅✅ حالا قاعدتاً می‌توانید حدس بزنید که خروجی تابع زیر چیست:

=IFNA( MATCH("X",{"A","B","C"}), "Not-Found")

? چون ورودی اول تابع IFNA خطای #N/A دارد، پس تابع IFNA به ورودی دوم خود نگاه می‌کند و مقدار متنی "Not-Found" را برمی‌گرداند.

?? از تابع IFNA می‌توان برای مدیریت خطای #N/A در توابع جستجو مانند VLOOKUP، MATCH و HLOOKUP استفاده کرد.

جمع‌بندی

• تابع IFNA در اکسل برای به دام انداختن و مدیریت خطای #N/A است. مانند تابع IFERROR، این تابع نیز کمک می‌کند که فرمول‌نویسی و مدل‌سازی‌های داشته باشیم که در مقابل خطاهای ممکن منعطف باشند.
• اما بر خلاف IFERROR، تابع IFNA همه خطاها را از دید شما پنهان نمی‌کند.
• ورودی‌های تابع می‌تواند مقدار ثابت، ارجاع به سلول، ناحیه، آرایه‌ها، متغیرها و فرمول‌های دیگر باشد.
• تابع IFNA فقط حالتی را مدیریت می‌کند که ورودی اول آن خطای #N/A دارد. اما اگر ورودی اول خطای دیگری داشته باشد همان را به خروجی منتقل می‌کند.
• وجود خطا در ورودی دوم تابع IFNA فقط موقعی بررسی می‌شود که به ورودی دوم نیاز باشد (ورودی اول خطای #N/A داده باشد).
• از جمله کاربردهای تابع IFNA، ترکیب آن با توابع جستجو در اکسل است.
• مانند IFERROR، در مورد IFNA هم این ما هستیم که باید خطاهای ممکن در مسئله یا فرمول‌نویسی خود را پیش‌بینی کنیم تا بتوانیم به شکل موثری از این توابع استفاده کنیم.

مهدی ملک