آموزش رفع ارور #N/A در اکسل (حل مشکل فرمول)

خطاهای #N/A، #VALUE و #NAME ازجمله ارورهایی ⚠️هستند که شاید در زمان استفاده حرفه‌ای از برنامه اکسل با آن مواجه شوید.

این مسئله عمدتا به دلیل مشکل در بخش VLOOKUP و کار نکردن آن ایجاد میشود و حل این مورد ممکن است برای برخی افراد پیچیده باشد.  به همین دلیل در این مطلب از حرف اضافه به روش عیب یابی این خطا و آموزش رفع ارور #N/A در اکسل با روش حل مشکل فرمول میپردازیم.

رفع ارور N A در اکسل

Fixing the NA Error on Excel 

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

برای کسب اطلاعات بیشتر درباره این موضوع بهتر است ابتدا روش کار با فرمول و تابع VLOOKUP اکسل را یاد بگیرید و از نکات مرتبط با آن آگاه شوید.

روش سریع رفع ارور #N/A در اکسل

بر اساس مطلب سایت مایکروسافت، ارور #N/A  به طور معمول زمانی نمایش داده میشود که سیستم قادر به پیدا کردن مقدار مورد نظر بر اساس فرمول نیست. این مشکل عموما برای توابع XLOOKUP، VLOOKUP، HLOOKUP، LOOKUP یا MATCH که مقدار جستجو شده در داده‌های منبع آنها وجود ندارد نمایش داده میشود. به طور مثال فرمول نمایش داده شده در تصویر زیر با فرمول =VLOOKUP(D2,$D$6:$E$8,2,FALSE)، اگر مقدار موز (Banana) در محدوده منبع موجود نباشد، خطای #N/A  نمایش داده میشود.

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

=IFERROR(FORMULA(),0)

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

=IFERROR(FORMULA(),"پیغام خطا")

در این فرمول از علامت “” برای مقدار هیچ استفاده کنید.

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

بررسی دلایل ایجاد ارور #N/A

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

۱- نادرست بودن نوع مقادیر

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

برای رفع این مشکل ابتدا لازم است مطمئن شوید که انواع داده‌ها به شکل یکسانی هستند. سپس یک سلول یا محدوده‌ای از سلول‌ها را انتخاب کنید و روی آنها کلیک راست کنید. در اینجا لازم است به مسیر Format Cells > Number بروید یا کلید Ctrl+1 را بزنید. سپس در پنجره باز شده به تب Number بروید و نوع داده را به مقدار مناسب تغییر دهید. اگر مقدار موردنظرتان به شکل عددی است، میتوانید این بخش را روی حالت Number قرار دهید یا برای مقدار متنی Text را انتخاب کنید. سپس روی OK بزنید.

این نکته را به خاطر داشته باشید که اگر میخواهید فرمت یک ستون را به طور کامل تغییر دهید، لازم است ابتدا قالب موردنظرتان را انتخاب کنید و به مسیر Data > Text to Columns > Finish بروید.

۲- وجود فاصله اضافه بین سلول‌ها

گاهی مواقع وجود فاصله‌های اضافه در داده‌های میتواند باعث پیدا نشدن نتیجه در VLOOKUP و در نتیجه عدم نمایش n a در اکسل شود. برای حل این مشکل میتوانید از تابع TRIM استفاده کنید تا فضاهای خالی در ابتدا یا انتهای متن حذف شود. به طور مثال در نمونه زیر TRIM داخل VLOOKUP قرار گرفته است تا فواصل اضافه در محدوده A2:A7 حذف شود.

=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)

توجه کنید که این فرمول باید به صورت آرایه‌ای ( Dynamic array formulas) وارد شود. اگر در نسخه‌های قدیمی اکسل برای تایید فرمول باید از Ctrl + Shift + Enter استفاده کنید. در ورژن‌های جدیدتر Microsoft 365، باید فرمول‌ها را در سلول بالای سمت چپ محدوده خروجی وارد کنید و سپس Enter را فشار دهید تا فرمول تایید شود.

۳- استفاده از روش تطابق تقریبی در برابر تطابق دقیق (TRUE/FALSE)

به طور پیشفرض توابعی که اطلاعات را در جدول جستجو میکنند باید به ترتیب صعودی مرتب شوند. با این وجود توابع VLOOKUP و HLOOKUP دارای قابلیتی به نام range_lookup هستند که تعیین میکند که آیا باید مقدار دقیق جستجو شود یا مقدار تقریبی نیز قابل قبول است. برای پیدا کردن مقدار دقیق، باید range_lookup را برابر FALSE قرار دهید. برخی مواقع در صورت تنظیم این بخش روی TRUE جستجو به صورت تطبیقی تقریبی است و به همین دلیل خطا#n/a در اکسل نمایش داده میشود.

همچنین اگر از تابع MATCH استفاده می کنید، میتوانید مقدار آرگومان match_type را تغییر دهید تا نوع جستجو را مشخص کنید. برای پیدا کردن مقادیر دقیق لازم است مقدار match_type را برابر ۰ قرار دهید.

۴- عدم مطابقت فرمول آرایه با محدوده ارجاع داده شده

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

=SUM(IF(A2:A11=D2,B2:B5))

برای اصلاح فرمول و رفع ارور #N/A در اکسل ، محدوده‌ی B2:B5 باید به B2:B11 تغییر کند تا با محدوده‌ی اول هماهنگ شود.

=SUM(IF(A2:A11=D2,B2:B11))

لازم است بدانید که قابلیت فرمول‌های آرایه‌ای یا  Array Formulas در نسخه‌های جدید Microsoft 365 به صورت پویا عمل میکند. بنابراین میتوان فرمول را در سلول بالا و چپ محدوده خروجی وارد کرد و دکمه اینتر را فشار داد. اما در نسخه‌های قدیمی‌تر اکسل برای تایید فرمول آرایه باید Ctrl + Shift + Enter را فشار دهید تا اکسل فرمول را داخل {} قرار دهد.

۵- رفع ارور #N/A در اکسل با بررسی وارد کردن دستی مقدار در سلول‌ها

در صورتی که مقدار #N/A یا NA() را به صورت دستی در سلول‌ها وارد کرده باشید، فرمول‌هایی که به این سلول‌ها ارجاع میدهد. قادر به محاسبه مقدار نخواهند بود و خطای #N/A را نمایش میدهند. به طور مثال در نمونه تصویر زیر ماه‌های May تا December مقدار #N/A دارند، بنابراین فرمول SUM نمیتواند مقدار کل را محاسبه کند و به‌ جای آن، مقدار #N/A نمایش داده میشود.

۶- خطای کمبود آرگومان در فرمول‌ها

در این حالت برخی اوقات ممکن است فرمول استفاده شده توسط کاربر یک یا چند آرگومان مورد نیاز را نداشته باشد. برای حل این مشکل لازم است دوباره فرمول استفاده شده را بررسی کنید و در صورت نیاز تمامی آرگومان‌های لازم را در فرمول وارد نمایید. برای این مسئله ممکن است نیاز به رفتن ویژوال بیسیک (VBE) برای بررسی عملکرد داشته باشید. جهت دسترسی به بخش VBE میتوانید از طریق بخش Developer یا ALT+F11 اقدام کنید.

۷- رفع ارور #N/A در اکسل، با بررسی عدم دسترسی به تابع تعریف‌ شده

گاهی ممکن است برنامه اکسل نتواند تابع تعریف شده کاربر را پیدا کند. برای رفع این مشکل، بررسی کنید که فایل کاری (Workbook) حاوی این تابع باز باشد و تابع به درستی کار کند.

۸- قرارگیری ستون آرایه‌ها و نتایج را بررسی کنید

یکی از محدودیتی‌های مهمی که در VLOOKUP ایجاد میشود، آن است که نمیتواند از سمت چپ جستجو کند. به همین خاطر ستون جستجو باید همیشه در سمت چپ‌ترین ستون آرایه جدول قرار بگیرد. اما ممکن است برخی اوقات این مسئله فراموش شود و خطای n/a در اکسل ایجاد شود. برای حل این مسئله اگر امکان تغییر دادن ساختار داده‌ها را ندارید تا ستون جستجو را در سمت چپ قرار دهید، میتوانید به جای از توابع INDEX و MATCH به‌ صورت ترکیبی استفاده کنید. در اینجا شما میتوانید یک نمونه از فرمول را ببینید.

در صورت نیاز میتوانید سایر راهکارها برای رفع مشکل VALUE و نوشتن فرمول در اکسل را نیز امتحان کنید.

۱۰- رفع ارور #N/A در اکسل با اصلاح فرمت ستون به حالت عددی

یکی دیگر از راهکارهایی که میتوان برای رفع ارور #N/A در اکسل از آن استفاده کرد، اطمینان از وارد شدن اعداد به شکلی درست است. برخی اوقات ممکن است اعداد در جدول اصلی با فرمت متن قرار داده شده باشد. این مشکل اغلب زمانی اتفاق می‌افتد که داده‌ها را از یک منبع خارجی وارد کنید یا برای نمایش داده شدن صفر ابتدایی از آپاستروف یا علامت (‘) استفاده کرده باشید. برای رفع این مشکل میتوانید همه اعداد یا جداول مشکل دار را انتخاب کنید و آیکون ارور بزنید، سپس از منوی باز شده گزینه Convert to Number را انتخاب کنید.

۱۱- بررسی گم شدن داده‌ها

یکی دیگر از دلایلی که میتواند باعث خطای n/a# اکسل شود، گم شدن یا از دست رفتن برخی از اطلاعات است. این مسئله همچنین میتواند به دلیل خالی ماندن برخی از سلول‌های مرجع نیز ایجاد شود. برای بررسی اینکه ببینید آیا واقعاً این اتفاق افتاده است یا خیر میتوانید از فرمول از IFNA به شکل زیر استفاده کنید:

=IFNA(VLOOKUP(A2, B2:C10, 2, FALSE), "Data Missing")

استفاده از #N/A در نمودارها

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

سوالات متداول

جمع بندی

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

نظرات و پیشنهادات شما؟

آیا شما هم تا به حال در زمان استفاده از برنامه اکسل با ارور #N/A  مواجه شده‌اید؟ بنظر شما نحوه رفع خطای N A در اکسل به چه صورت میتواند موثرتر باشد؟ اگر در این زمینه نظر یا تجربه‌ای دارید، لطفاً آن را با ما و سایر خوانندگان این مطلب به اشتراک بگذارید.