تفاوت های XLOOKUP در مقابل VLOOKUP در اکسل

وبلاگ دسته‌بندی نشده تفاوت های XLOOKUP در مقابل VLOOKUP در اکسل

تفاوت XLOOKUP و VLOOKUP

XLOOKUP با امکان جستجو در سمت چپ و راست، با قابلیت تغییر بیشتر، کاهش خطاهای فرمول، و داشتن ویژگی‌های پیشرفته مانند مدیریت خطای سفارشی و مطابقت دقیق به صورت پیش‌فرض،نسبت VLOOKUP برتری دارد. برای مقایسه دقیق تر XLOOKUP و VLOOKUP به ادامه مطلب مراجعه کنید.

توابع XLOOKUP و VLOOKUP در اکسل چیست؟

توابع XLOOKUP و VLOOKUP در اکسل برای یافتن، یا ‘lookup’، یک مقدار از یک جدول یا یک لیست و سپس برگرداندن یک نتیجه مرتبط استفاده می شوند. در این منبع، XLOOKUP در مقابل VLOOKUP را با مثال هایی از نحوه استفاده از هر کدام مورد بحث قرار خواهیم داد.
در تصویر زیر سمت چپ مجموعه داده ای دارید که در آن اعضا کارگران کارگاه وجود دارند، به نام مجموعه دادهای ثبت کارگاه. این مجموعه داده برخی از اطلاعات مربوط به اعضا را ندارد، به عنوان مثال، قسمت “نام خانوادگی”.
اطلاعات مربوط به اعضا را می توان در مجموعه داده دیگری به نام مجموعه داده اعضا یافت و می توان از آنجا برای تکمیل فیلدهای مورد نیاز در مجموعه داده ثبت نام کارگاه بازیابی کرد.
برای به دست آوردن اطلاعات از یک مجموعه داده و تکمیل مجموعه داده دیگری با استفاده از آن، معمولاً از VLOOKUP استفاده می کنید. اگر به Microsoft 365 دسترسی دارید، می‌توانید XLOOKUP را برای انجام کار در نظر بگیرید.

نحوه جستجوی جزئیات اعضا، در مجموعه داده برای XLOOKUP و VLOOKUP کمی متفاوت است. بیایید نگاهی به روش ها و تفاوت های هر یک بیندازیم.

روش ها

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

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]=

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=

جدول مقایسه VLOOKUP و XLOOKUP

	VLOOKUP	XLOOKUP
پیش‌فرض مطابقت دقیق	ن	ص
مقدار را به سمت راست جستجو برگردانید	ص	ص
مقدار را به سمت چپ جستجو برگردانید	ن	ص
جستجو در ردیف	ن	ص
برگرداندن بیش از یک مقدار	ن	ص
ستون جستجو باید مرتب شود	فقط مطابقت تقریبی	فقط جستجوی باینری
از بالا به پایین جستجو کردن	ص	ص
از پایین به بالا جستجو کردن	ن	ص
جستجوی باینری	ن	ص
اگر مقدار جستجو پیدا نشد، دادن پیام سفارشی	ن	ص
جستجوی حروف عامیانه	ص	ص
جستجوی دقیق	ص	ص
جستجوی تقریبی مقدار کوچکتر بعدی را برمی گرداند	ص	ص
جستجوی تقریبی مقدار بزرگتر بعدی را برمی گرداند	ن	ص
اگر مقادیر جستجو مرتب نشده باشند، ممکن است مقدار نادرستی را برگرداند؟	ص- تقریبی	ن- تقریبی، ص- باینری
افزودن ستون های جدید به جدول جستجو ممکن است فرمول را خراب کند	ص	ن

ارزش جستوجو

مقداری که می خواهید در لیست جدید پیدا کنید (به عنوان مثال شناسه عضو). هر دو VLOOKUP و XLOOKUP معمولاً یک مقدار واحد را جستجو می کنند.

مقایسه آرگومان VLOOKUP و XLOOKUP: لیست جدول و لیست جستجو

این نشان دهنده بزرگترین تفاوت بین VLOOKUP و XLOOKUP است و در مثال بالا مجموعه داده اعضا را مشاهده کردید که در آن شما می توانید هم عضو (مقدار جستجو) و هم نام یا نام خانوادگی عضو (مقدار برگشتی) را پیدا کنید.
فرمول VLOOKUP از table_array استفاده می‌کند ، این ناحیه ستونی را برای مقدار جستجو نشان می‌دهد و دنبال می کند تا به مقدار بازگشتی برسد. آرگومان بعدی با نشان دادن شماره ستون مربوطه از لیست جدول که مقدار بازگشتی را در خود جای داده است، به این لیست باز می گردد.
اکسل طوری برنامه ریزی شده است که مقدار جستجو را در سمت چپ ترین ستون این محدوده جستجو کند و مقداری را در سمت راست آن بازگرداند که با شماره ستون آرگومان بعدی نشان داده شده است.
XLOOKUP آرایه جستجو و آرایه بازگشتی را به دو آرگومان جدا می کند. آرایه جستجو باید مقداری را که اکسل جستجو می کند در خود جای دهد و آرگومان آرایه بازگشتی مقداری را که قرار است برگردانده شود را در خود جای دهد.
جداسازی آرایه ها XLOOKUP را بسیار انعطاف پذیرتر از VLOOKUP می کند . آرایه جستجو اکنون می تواند در سمت چپ یا راست ستون مقدار بازگشتی باشد و XLOOKUP می تواند به عنوان یک VLOOKUP یا یک HLOOKUP عمل کند .

مقایسه آرگومان VLOOKUP و XLOOKUP - شماره فهرست ستون در مقابل آرایه بازگشتی

این آرگومان محدوده ای را نشان می دهد که مقدار برگشتی را می توان یافت. در مثال ما، این نام کوچک عضو خواهد بود.
در VLOOKUP این یک عدد واحد است که به آرایه جدول (آگومان قبلی که کل محدوده ای را نشان می دهد که مقدار جستجو و مقدار بازگشتی را می توان پیدا کرد) اشاره می کند.
اگر این شماره ستون در آرگومان هاردکد شده باشد، VLOOKUP به راحتی شکسته می شود، که یک مزیت مهم برای XLOOKUP است.
XLOOKUP به یک محدوده کامل از مقادیر برای این آرگومان نیاز دارد تا آرایه ای را که ممکن است مقدار بازگشتی در آن پیدا شود نشان دهد. این محدوده باید از نظر طول و موقعیت با آرایه جستجو (آگومان قبلی) تراز باشد تا از یک #VALUE جلوگیری شود.

آرایه برگشتی در XLOOKUP همچنین می‌تواند بیش از یک ستون بازگشتی را در آرایه بازگشتی جای دهد، که بیش از یک مقدار مربوط به مقدار جستجو را برمی‌گرداند. شما باید از دو VLOOKUP برای مدیریت یکسان استفاده کنید.
دو آرگومان گذشته (تفکیک محدوده ها) بزرگترین مزایای XLOOKUP را نشان می دهد:
آرایه جستجو می تواند در سمت راست یا چپ آرایه برگشتی باشد
شکستن XLOOKUP سخت تر است زیرا دو آرایه (جستجو و بازگشت) نسبی هستند و در فرمول کدگذاری نشده اند.
اینجاست که کاربران در مورد XLOOKUP هیجان زده می شوند و آماده هستند تا فوراً VLOOKUP را از بین ببرند. اما، اگر شماره ستون را در VLOOKUP رمزگذاری نکنید، اما موفق شوید این استدلال را نتیجه یک فرمول کنید، VLOOKUP مزیتی را ارائه می دهد که ممکن است تکرار آن با XLOOKUP به تنهایی دشوار باشد.

استدلال های اختیاری
اگر یافت نشد ( فقط XLOOKUP )
اگر XLOOKUP نتواند مقداری را پیدا کند، یک پیام پیش‌فرض یا سفارشی را برمی‌گرداند. پیام ممکن است به این صورت باشد: «عضوی مورد نظر شما در پایگاه داده ما ثبت نشده است.»
مگر اینکه یک IFERROR اضافه کنید ، VLOOKUP #N/A معمول خود را برمی گرداند! خطای (در دسترس نیست)، که ممکن است باعث شود فکر کنید کار اشتباهی انجام داده اید و VLOOKUP کار نمی کند.

مقایسه آرگومان VLOOKUP در مقابل XLOOKUP-جستجوی محدوده در مقابل حالت مطابقت

تطابق دقیق (FALSE) و تقریبی (TRUE) در VLOOKUP امکانات مشابهی در XLOOKUP دارند. در مقایسه، به این صورت خواهد بود:

گزینه های برنامه ریزی شده XLOOKUP	گزینه های VLOOKUP که عملکرد مشابهی دارند
دقیق (0) – پیش فرض	دقیق (1)
تقریبی (مقدار دقیق یا کوچکتر بعدی) (-1)	تقریبی – پیش فرض
تقریبی (دقیق یا مقدار بعدی بزرگتر) (1)	هیچ کدام
حروف عام (?*~) (2)	مطابقت دقیق (1) با استفاده از همان کاراکترهای عام

XLOOKUP به طور پیش‌فرض یک تطابق دقیق دارد ، در حالی که آرگومان جستجوی محدوده در VLOOKUP یک تطابق تقریبی را پیش‌فرض می‌کند. از آنجایی که اغلب از یک تطابق دقیق استفاده می شود، این تنظیم XLOOKUP را موثرتر می کند. علاوه بر این، XLOOKUP یک گزینه اضافی از یک مطابقت تقریبی ارائه می دهد که مقدار بزرگتر بعدی را برمی گرداند.

جستجو

حالت جستجو
VLOOKUP به گونه ای برنامه ریزی شده است که از بالا به پایین (اول تا آخر) جستجو کند و اولین مورد مطابق را برای مطابقت های دقیق و تقریبی برمی گرداند.
XLOOKUP دارای چهار گزینه جستجوی ممکن است:
اولین تا آخرین (1) – پیش فرض
آخرین تا اول (-1)
جستجوی دودویی – مرتب سازی صعودی را برای محدوده جستجو در نظر بگیرید (2)
جستجوی دودویی – مرتب سازی نزولی را برای محدوده جستجو (-2) فرض کنید
روش‌های جستجو قابلیت‌های اضافی را برای XLOOKUP باز می‌کنند – می‌توانید آخرین مورد از یک مورد را در یک محدوده جستجو کنید. روش جستجوی دودویی در برگه‌هایی با مقادیر زیاد داده سریع‌تر است، اما متکی بر این واقعیت است که داده‌ها در محدوده جستجو مرتب شده‌اند.
VLOOKUP از اول تا آخر جستجو می کند و تنها با استفاده از مانورهای جدی در اکسل می تواند از آخرین به اول نگاه کند.

مرتب سازی محدوده جستجو
اگر محدوده جستجو به ترتیب صعودی مرتب نشده باشد، جستجوی تقریبی VLOOKUP (TRUE) مقادیر نادرستی را برمی‌گرداند.
XLOOKUP با مرتب‌سازی زمانی که از آخرین به اول جستجو می‌کند یا زمانی که از جستجوی باینری استفاده می‌کند، مهار می‌شود.

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

سازگاری با نسخه های قدیمی اکسل

VLOOKUP هیچ مشکلی در سازگاری با نسخه های قدیمی اکسل ندارد . XLOOKUP فقط در Microsoft 365 و نسخه آنلاین – Excel برای وب در دسترس است
اگر می‌خواهید یک کاربرگ را با XLOOKUPها با همکاران به اشتراک بگذارید، مطمئن شوید که فعلاً به Microsoft 365 نیز دسترسی دارند.

عیب یابی

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

پیام های خطا

خطای VLOOKUP:
#N/A – مقدار جستجو شده در دسترس نیست.
مثال: شماره عضو در مجموعه داده اعضا یافت نمی شود.
#REF – مقدار بازگشتی خارج از آرایه جدول تعریف شده است.
مثال: شما پنج ستون را انتخاب کرده اید و از ستون 6 مقدار بازگشتی می خواهید.
خطای XLOOKUP:
#N/A – مقدار جستجو شده در دسترس نیست.
مثال: شماره عضو در مجموعه داده اعضا یافت نمی شود. (همانند VLOOKUP.)
#REF – کتاب کار دیگری که در فرمول ذکر شده باز نیست.
#VALUE – ابعاد آرایه جستجو و آرایه برگشتی از نظر موقعیت و طول تراز نیستند.
مثال: آرایه جستجو پنج ردیف و آرایه برگشتی چهار ردیف است یا آرایه جستجو در یک ردیف و آرایه برگشتی در یک ستون است.
برای بهتر متوجه شدن خطا ها فایما در این مقاله به ده تا از رایج ترین خطا ها اشاره کرده و هرکدام را به صورت کامل توضیح داده

زمان استفاده از XLOOKUP یا VLOOKUP
XLOOKUP را می توان برای کارهای مشابه VLOOKUP استفاده کرد و سپس چند کاربرد اضافی دارد. شاید بهتر باشد نمونه هایی را به شما نشان دهم.

مثال 1 - مقداری را پیدا کنید که وجود داشته باشد و منحصر به فرد باشد

مثال اول به دنبال شماره عضوی است که (تطابق دقیق) در مجموعه داده اعضا وجود دارد و منحصر به فرد است – مقداری که انتظار دارید به راحتی آن را پیدا کنید.

در سلول B4 وارد می کنیم:

VLOOKUP(A4,$J$4:$L$14,2,FALSE)=

XLOOKUP(A4,$J$4:$J$14,$K$4:$L$14,"Membership number not found")=

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

مثال 2 - مقداری را پیدا کنید که وجود ندارد

وقتی از اکسل خواسته می شود مقداری را پیدا کند که در مجموعه داده وجود ندارد چه اتفاقی می افتد ؟ در مثال زیر، شناسه عضو 11111 وجود ندارد.

VLOOKUP
VLOOKUP دقیق یک #N/A را برمی‌گرداند – که به معنای در دسترس نیست.

XLOOKUP
تطابق دقیق XLOOKUP فقط چیزی را پیدا نمی کند، همانطور که انتظار می رود، و یک پیام سفارشی برمی گرداند.

مثال 3 - یک مقدار در سمت چپ پیدا کنید

در این مثال، اکسل باید به دنبال مقدار 4 در ستونی به نام «99» باشد و «تعداد» مرتبط را به سمت چپ برگرداند.
VLOOKUP نمی تواند مطابقت داشته باشد، بنابراین می توان از یک فهرست و مطابقت جایگزین استفاده کرد.
XLOOKUP در مقایسه نسبتاً ساده است.

در سلول D4 وارد می کنیم:

XLOOKUP(A4,$J$4:$J$14,$I$4:$I$14)=

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