خطای #N/A در اکسل به همراه مثال vlookup

در بسیاری از موارد پیش می آید که پس از نوشتن تابع جستجو مرجع مانند vlookup و hlookup و lookup و match این توابع به درستی انتظارات شما را برآورده نمی کند و خطای #N/A را برای شما باز می گرداند. قبل از اینکه به بررسی دلایلی که باعث به وجود آمدن خطای #N/A در تابع vlooup و سایر توابع می شود و راه حل های آن، بهتر است به بررسی خود خطای #N/A بپردازیم. به یاد داشته باشید راه های ارائه شده در این مطلب در هر تابعی که خطای #N/A را به شما نمایش می دهد این خطا را حل می کند.

پیشنهاد مطالعه: 

تابع xlookup در اکسل قابلیتی را ایجاد کرده است که می توانید به راحتی مقدار مورد نظر را به جای خطای #N/A جایگزین کنید، مطلب زیر رو حتما مطالعه کنید:

تابع xlookup در اکسل (تایع جدید در اکسل 2021)

 

 

معنی خطای #N/A در اکسل چیست؟

به زمان ساده یعنی «نمی توانم پیدا کنم!». na سر واژه های کلمه not available به معنی عدم دسترسی است. زمانی که این خطای ایجاد می شود اکسل به شما می گوید مقدار مورد نظر در بازه تعریف شده وجود ندارد. اما جالب اینجاست که در بسیاری از موارد شما مقدار مورد نظر خود را در بازه تعریف شده می بینید ولی باز هم این خطا نمایش داده می شود. در ادامه به بررسی حالت هایی می پردازیم که این خطا ایجاد می شود.

فایل اکسل مربوط به این آموزش و مثال ها را از اینجا دانلود کنید:

[email-download download_id=”5960” contact_form_id=”4438”]

در اینجا ما سه مشکل رایج را بحث کردیم. اگر شما دلیل دیگری برای این خطا پیدا کردید در قسمت نظرات برای ما ارسال کنید تا این مطلب را کامل تر کنیم.

دلیل اول ایجاد خطای #N/A در اکسل : عدم استفاده از تطابق دقیق (exact match)

در برخی از مواقع خطای #N/A به دلیل عدم آشنایی کافی کاربر با توابع اکسل ایجاد می شود. عمده ترین دلیل عدم آشنایی با حالت های approximate match (تطابق تقریبی) و exact match (تطابق دقیق) در توابع رخ می دهد. مثلا در تابع vlookup اگر آرگون چهارم که نشان دهنده حالت تطابق است را به درستی تعیین نکنید تابع مقدار اشتباه را برای شما نمایش می دهد یا با خطای #N/A مواجه می شود.

اما حالت های تطابق به چه معنی هستند و چگونه باید از آنها استفاده کرد؟ در تابع vlookup در حالت پیشفرض حالت جستجو از نوع تطابق تقریبی است، در صورتی که در اکثر مواقع کاربران نیاز دارند که حالت جستجو را از نوع تطابق دقیق (exact match) قرار دهند. برای این کار کافی است که آرگون آخر تابع vlookup را مقدار exact match انتخاب کنید یا مقدار 0 یا false را تایپ کنید.

نکته: اگر میخواهید از حالت تطابق تقریبی استفاده کنید حتما داده های شما باید به صورت صعودی مرتب شده باشند و تابع vlookup در این حالت مقدار دقیق یا کوچکتر از مقدار مورد نظر را نمایش می دهد.

خواهید دید که در این صورت تابع vlookup مقدار صحیح را برای شما نمایش خواهد داد.

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

تابع vlookup در اکسل | مثال ها و کاربردها

تابع mathc در اکسل | مثال ها و کاربردها

معرفی تابع match در اکسل و ترکیب با vlookup

دلیل دوم ایجاد خطای #N/A در اکسل: وجود کاراکترهای پنهان در سلول ها

یکی از عمده کاراکترهای پنهایی که در سلول ها و جود دارد و دیده نمی شود «فاصله» یا همان space است. در مثال زیر در هر دو سلول کلمه kaar2.ir تایپ شده است. اما زمانی که در سلول دیگری این دو سلول را مساوی هم قرار می دهیم (=B3=C3) مقدار FALSE یا همان عدم مساوی بودن را نمایش می دهد.

موضوع خیلی ساده است. ممکن است در ابتدا و انتهای یکی از سلول های یک یا چندین «فاصله» وجود داشته باشد. در این مثال در انتهای کلمه در سلول C3 یک فاصله اضافه تایپ شده است.

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

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

اما اگر به داخل سلول های خود بروید خواهید دید که مقدارهای موجود در ستون A دارای «فاصله های اضافه» هستند. اگر نمی خواهید داده های ستون A را تغییر دهید کافی است که یک ستون کمکی بین ستون A و B ایجاد کنید و از تابع trim استفاده کنید. سپس بازه جدول تابع vlookup را ستون B و C تعیین کنید.

خواهید که در این صورت مقدار صحیح نمایش داده خواهد شد.

اگر سایر کاراکترهای پنهان مانند نیم فاصله های اضافه و سایر کاراکترها در متن شما موجود باشد باید به صورت دستی یا با استفاده از ctrl+H یا همان جایگزینی (reaplace) اقدام به حذف آنها کنید.

دلیل سوم ایجاد خطای #N/A در اکسل : عدم تطابق کاراکترها مخصوصاً حرف «ی» و «ک»

اگر شما فایل داده خود را از سیستم های ERP یا حسابداری یا CRM یا سایر نرم افزارها خروجی گرفته باشید یا در سایر سیستم ها تایپ شده باشد، به دلیل تفاوت در کد حرف ممکن است در ظاهر کاراکترها یکی باشند ولی در باطن یکی از نوع فارسی و یکی از نوع عربی باشد. (به صورت تخصصی در مورد این موضوع بحث نمی کنیم.) اما اگر در برخی از سلول ها این مشکل وجود داشت و دو مشکل بالا را بررسی کردید، این بار نوبت بررسی تک تک کاراکترها است. ابتدا حروف «ی» و «ک» را در یکی از سلول ها را پاک کنید و با سیستم خود دوباره تایپ کنید. اگر مشکل حل نشد سایر کاراکترها را یکی یکی پاک و تایپ کنید تا کاراکتری که با سیستم شما مطابق نیست را پیدا کنید.

در اکثر مواقع ممکن است در فایل شما حرف «ي» عربی که عمدتا با کلیدهای shift+x تایپ می شود به جای «ی» فارسی وجود داشته باشد. در این صورت باز هم اکسل دو مقدار را نمی تواند یکسان در نظر بگیرد.

برای حل این مشکل کافی است به صورت دستی یا با استفاده از ctrl+h تمام حروف «ي» عربی را با «ی» سیستم خود جایگزین کنید، یا برعکس.