خبر و ترفند روز

خبر و ترفند های روز را اینجا بخوانید!

5 خطای رایج VLOOKUP اکسل و نحوه جلوگیری از آنها

ارشیا یوسفی ادیب ۳ فروردین, ۱۴۰۲ 5 min read

آیا می خواهید VLOOKUP های اکسل خود را بدون خطا بسازید؟ بیاموزید که چگونه از اشتباهات اصلی اجتناب کنید و مهارت های تجزیه و تحلیل داده های خود را به راحتی بهبود بخشید.

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

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

1. جستجوی مقدار در ستون اشتباه

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

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

جدول با داده‌های کنار آن یک فرمول VLOOKUP است که خطای #N/A را برمی‌گرداند

نحوه رفع این خطا:

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

هنگام ایجاد جداول در اکسل برای VLOOKUP، مطمئن شوید که مقدار جستجو را در سمت چپ مقداری که می خواهید برگردانید قرار دهید. ساده ترین راه برای انجام این کار این است که آن را به ستون اول جدول تبدیل کنید.

جدول اکسل با تعداد کارمندان بر اساس کشور و شهر. برچسب ها ستون 1 را به عنوان مقدار جستجو و ستون 3 را به عنوان مقدار بازگشتی نشان می دهند

اکنون، فرمول شما باید مقداری را برگرداند:

فرمول VLOOKUP یک عدد را به جای خطای #N/A برمی گرداند

2. قالب بندی اعداد نادرست

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

مطلب مرتبط:   نحوه استفاده از تابع TEXTSPLIT در مایکروسافت اکسل

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

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

نحوه رفع این خطا:

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

  1. ستون خود را با مقدار جستجو انتخاب کنید. یک راه سریع برای انتخاب تمام داده ها، کلیک بر روی اولین سلول در ستون مورد نظر است. در مرحله بعد، در حالی که کلید CTRL را پایین نگه داشته اید، روی کلید فلش پایین کلیک کنید.
  2. پس از انتخاب تمام داده های ستون جدول، روی Error Indicator کلیک کرده و Convert to Number را انتخاب کنید.
  3. این خطا در فرمول شما اکنون باید از بین رفته باشد.

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

3. مقدار اشتباهی که به دلیل مقادیر مشابه برگشت داده شده است

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

نحوه رفع این خطا:

با به روز رسانی فرمول خود با این مراحل به راحتی این مشکل را حل کنید:

  1. با تابع VLOOKUP روی اولین سلول ستون خود کلیک کنید.
  2. با کلیک بر روی دکمه fx فرمول را مشاهده کنید.
  3. در کادر آخر، Range_Lookup، مطمئن شوید که 0 داشته باشد تا آرگومان FALSE باشد. اگر 1 یا خالی باشد، روی TRUE تنظیم می شود. True به این معنی است که اگر نتواند مقدار دقیق را پیدا کند، مقدار را برای نزدیکترین مطابقت برمی گرداند. اگر False باشد، فرمول فقط مطابقت دقیق یا یک خطا را برمی گرداند.
مطلب مرتبط:   توابع IF و AND/OR را برای منطق پیچیده تر در اکسل ترکیب کنید

پنجره فرمول VLOOKUP با مقدار جستجوی محدوده روی false تنظیم شده است

4. قفل نشدن محدوده داده

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

به عنوان مثال، اگر جدولی دارید که فروش ها را بر اساس شناسه کارمند نشان می دهد و جدول دیگری که نام و شماره شناسه کارمندان را نشان می دهد، می توانید از این ابزار برای نشان دادن فروش بر اساس نام کارمند با جستجوی شماره شناسه استفاده کنید.

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

نحوه رفع این خطا:

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

  1. با تابع VLOOKUP روی اولین سلول ستون خود کلیک کنید.
  2. با کلیک بر روی دکمه fx فرمول را مشاهده کنید.
  3. اگر از جدول استفاده می کنید، Table_Array شما باید Table x[#All] باشد (همانطور که در زیر مشاهده می کنید).
  4. اگر از یک محدوده معمولی استفاده می‌کنید یا ستون کامل را نمی‌خواهید، مطمئن شوید که $ را قبل از حرف و دوباره قبل از تعداد سلول‌هایی که می‌خواهید با آن‌ها شروع و با آن‌ها خاتمه دهید قرار دهید.
  5. هر یک از این روش ها برای رفع خطای شما کار می کند.

پنجره بازشو تابع VLOOKUP با جدول[#همه] در کادر آرایه جدولپنجره بازشو تابع VLOOKUP نشان می دهد که محدوده با افزودن $ در آرایه جدول قفل شده است

5. فرمول SUM به دلیل مقادیر #N/A کار نمی کند

حتی زمانی که فرمول شما به درستی کار می کند، اگر مقدار در جدول وجود نداشته باشد، ممکن است مقادیر #NA همچنان ظاهر شوند. این کاملاً رایج است و می تواند با سایر فرمول های اعمال شده در ستون شما تداخل داشته باشد.

مطلب مرتبط:   نحوه جدا کردن نام و نام خانوادگی از ستون ها در اکسل

یکی از پرکاربردترین توابع فرمول SUM است که نمی تواند مقادیر غیر عددی در محدوده ای که می خواهید با هم جمع کنید وجود داشته باشد را محاسبه کند.

نحوه رفع این خطا:

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

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

استفاده از SUMIF به جای فرمول SUM:

  1. به جای استفاده از تابع SUM، =SUMIF را تایپ کنید
  2. بر روی دکمه fx کلیک کنید.
  3. در بخش Range، از بخشی که می‌خواهید مطابق با معیارها باشد استفاده کنید.
  4. در زیر معیارها، این آرگومان را وارد کنید تا ورودی های #N/A از معادله حذف شوند. “<>#N/A”.
  5. در قسمت Sum Range، محدوده ای را که می خواهید اضافه کنید، قرار دهید. در این مورد، ستون با VLOOKUP شما.
  6. فرمول باید به شکل زیر باشد:

جعبه راه‌اندازی Sumif Formula با معیارهایی که روی مقادیر #NA حذف شده استفرمول Sumif و مقدار بازگشتی 39

با استفاده از تابع IFERROR در مقابل فرمول VLOOKUP:

  1. با تابع VLOOKUP روی اولین سلول ستون خود کلیک کنید.
  2. در تب فرمول، IFERROR( را بین علامت مساوی و V تایپ کنید.
  3. بعد از معادله VLOOKUP تایپ کنید، 0)
  4. فرمول باید به شکل زیر باشد:
  5. حالا به جای #N/A، اگر مقدار وجود نداشته باشد، 0 را برمی گردانید.

نوار فرمول اکسل با IFERROR در مقابل فرمول VLOOKUPنوار فرمول اکسل با فرمول VLOOKUP در تابع IFERRORتابع Sum Excel که مقدار مورد انتظار را نشان می دهد، در سمت چپ جدولی با داده ها و در سمت راست جدولی برای VLOOKUP قرار دارد.

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

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

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

Tags: