تفاوت های XLOOKUP در مقابل VLOOKUP در اکسل
تفاوت XLOOKUP و VLOOKUP
XLOOKUP نسبت به VLOOKUP پیشرفته‌تر است، چون میتوان داده‌ها را هم از سمت چپ و هم از سمت راست جستجو کرد. این ویژگی باعث افزایش انعطاف‌پذیری می‌شود و همچنین خطاهای فرمول را کاهش می‌دهد. علاوه بر این، XLOOKUP ویژگی‌های پیشرفته‌ای مانند مدیریت خطای سفارشی و جستجوی دقیق به‌صورت پیش‌فرض را شامل می‌شود.
برای مقایسه کامل‌تر XLOOKUP و VLOOKUP، ادامه مطلب را بخوانید.
توابع XLOOKUP و VLOOKUP در اکسل چیست؟
توابع XLOOKUP و VLOOKUP در اکسل برای جستجو و پیدا کردن مقادیر از یک جدول یا لیست استفاده می‌شوند. این توابع به شما کمک می‌کنند تا یک مقدار را جستجو کرده و نتیجه‌ای مرتبط با آن را برگردانید.
در این مقاله، به مقایسه 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 استفاده می‌شود.
این ناحیه ستونی، مقدار جستجو را مشخص می‌کند و سپس مقدار بازگشتی را پیدا می‌کند. آرگومان بعدی، شماره ستون مربوطه را تعیین می‌کند که مقدار بازگشتی در آن قرار دارد.
به‌طور پیش‌فرض، اکسل مقدار جستجو را در سمت چپ‌ترین ستون table_array جستجو می‌کند و مقداری را از سمت راست آن بازمی‌گرداند. شماره ستون در آرگومان بعدی، مشخص می‌کند که مقدار بازگشتی در کدام ستون قرار دارد.
اما در XLOOKUP، آرایه جستجو و آرایه بازگشتی به دو آرگومان جداگانه تقسیم می‌شوند.
آرایه جستجو مقداری را که اکسل باید جستجو کند در خود دارد و آرگومان آرایه بازگشتی مقداری را که باید برگرداند، در خود جای می‌دهد.
این جداسازی آرایه‌ها باعث می‌شود که XLOOKUP بسیار انعطاف‌پذیرتر از VLOOKUP باشد.
اکنون آرایه جستجو می‌تواند هم در سمت چپ و هم در سمت راست ستون مقدار بازگشتی قرار بگیرد. علاوه بر این، XLOOKUP می‌تواند هم به‌عنوان VLOOKUP و هم به‌عنوان HLOOKUP عمل کند.
مقایسه آرگومان VLOOKUP و XLOOKUP - شماره فهرست ستون در مقابل آرایه بازگشتی
در VLOOKUP، این آرگومان یک عدد است که به ستون خاصی در جدول اشاره می‌کند.
این ستون به آرایه جدول (آرگومان قبلی که کل محدوده جستجو و مقدار برگشتی را مشخص می‌کند) ارجاع داده می‌شود. اگر این شماره ستون به صورت دستی (هاردکد) وارد شود، ممکن است باعث بروز مشکل در فرمول شود. این یکی از مزایای مهم XLOOKUP به شمار می‌آید.
در XLOOKUP، به جای یک عدد، یک محدوده کامل از مقادیر برای این آرگومان نیاز است تا آرایه‌ای که ممکن است مقدار برگشتی در آن قرار گیرد، مشخص شود. این محدوده باید از نظر طول و موقعیت با آرایه جستجو (آرگومان قبلی) تراز باشد تا از بروز خطای #VALUE جلوگیری شود.
آرایه برگشتی در XLOOKUP می‌تواند بیش از یک ستون را به عنوان مقادیر بازگشتی برگرداند،
که این ویژگی به شما این امکان را می‌دهد تا چندین مقدار مرتبط با مقدار جستجو را بازگردانید. این قابلیت نسبت به VLOOKUP که نیاز به استفاده از دو فرمول مجزا دارد، برتری دارد.
دو آرگومان محدوده جستجو و محدوده برگشتی بزرگترین مزایای XLOOKUP را نشان می‌دهند:
- آرایه جستجو می‌تواند در سمت راست یا چپ آرایه برگشتی قرار گیرد، که انعطاف‌پذیری بیشتری در انتخاب محدوده‌ها فراهم می‌کند.
- شکستن XLOOKUP سخت‌تر است زیرا هر دو آرایه جستجو و برگشتی نسبی هستند و در فرمول‌ها به صورت ثابت کدگذاری نمی‌شوند.
این ویژگی‌ها باعث شده است که کاربران از XLOOKUP بسیار هیجان‌زده شوند و بخواهند آن را جایگزین VLOOKUP کنند. با این حال، اگر شماره ستون را در VLOOKUP رمزگذاری نکنید و این استدلال را از طریق فرمول‌ها به نتیجه برسانید، VLOOKUP همچنان مزیتی دارد که ممکن است تکرار آن با XLOOKUP به تنهایی دشوار باشد.
استدلال های اختیاری
اگر پیدا نشد (فقط در XLOOKUP)
اگر XLOOKUP نتواند مقداری را پیدا کند، یک پیام پیش‌فرض یا سفارشی را برمی‌گرداند. برای مثال، ممکن است پیامی مانند «عضوی مورد نظر شما در پایگاه داده ما ثبت نشده است» نمایش داده شود.
اما در VLOOKUP، اگر مقدار یافت نشود، به طور پیش‌فرض خطای #N/A (موجود نیست) را برمی‌گرداند. این خطا ممکن است باعث شود شما فکر کنید که اشتباهی رخ داده است و VLOOKUP کار نمی‌کند، مگر اینکه از IFERROR استفاده کنید تا این خطا را مدیریت کنید.
مقایسه آرگومان VLOOKUP در مقابل XLOOKUP-جستجوی محدوده در مقابل حالت مطابقت
در VLOOKUP، امکان استفاده از تطابق دقیق (FALSE) یا تطابق تقریبی (TRUE) وجود دارد. این امکانات مشابه در XLOOKUP نیز موجود است. در مقایسه، به این صورت خواهد بود:
گزینه های برنامه ریزی شده XLOOKUP | گزینه های VLOOKUP که عملکرد مشابهی دارند |
|---|---|
| دقیق (0) – پیش فرض | دقیق (1) |
| تقریبی (مقدار دقیق یا مقدار کوچکتر از آن) (-1) | تقریبی – پیش فرض |
| تقریبی (مقدار دقیق یا مقدار بزرگتر از آن) (1) | هیچ کدام |
| حروف عام (?*~) (2) | مطابقت دقیق (1) با استفاده از همان کاراکترهای عام |
XLOOKUP به طور پیش‌فرض از تطابق دقیق استفاده می‌کند. در مقابل، در VLOOKUP، آرگومان جستجوی محدوده به طور پیش‌فرض تطابق تقریبی را اعمال می‌کند. از آنجایی که معمولاً از تطابق دقیق استفاده می‌شود، این ویژگی باعث می‌شود XLOOKUP مؤثرتر باشد.
علاوه بر این، XLOOKUP گزینه‌ای اضافی برای تطابق تقریبی دارد که در آن مقدار بزرگتر بعدی بازگردانده می‌شود.
جستجو
حالت جستجو در VLOOKUP و 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 در مجموعه داده‌ها موجود نباشد، اکسل خطای #N/A (یعنی “مقدار پیدا نشد”) را نمایش می‌دهد.
VLOOKUP
در VLOOKUP، اگر مقدار جستجو یافت نشود، خطای #N/A را نشان می دهد که به معنای “در دسترس نبودن” است.
XLOOKUP
در XLOOKUP، اگر مشابه آن دقیق پیدا نشود، به طور پیش فرض هیچ مقداری نشان نمی دهد. اما شما می‌توانید یک پیام سفارشی برای نمایش در این موارد تنظیم کنید.
مثال 3 - یک مقدار در سمت چپ پیدا کنید
در این مثال، هدف ما این است که اکسل مقدار 4 را در ستونی به نام 99 پیدا کند و تعداد مرتبط را به سمت چپ بازگرداند. از آنجا که VLOOKUP نمی‌تواند این کار را به راحتی انجام دهد، می‌توانیم از روش جایگزین فهرست و مطابقت (List & Match) استفاده کنیم.
در مقایسه با روش‌های قدیمی‌تر، XLOOKUP ساده‌تر و مؤثرتر است.
برای انجام این کار، در سلول D4 عبارت زیر را وارد کنید:
XLOOKUP(A4,$J$4:$J$14,$I$4:$I$14)=
این مثال لیستی از سفارشات مشتریان را نشان می‌دهد. اگر بخواهیم بدانیم که مجموع آخرین سفارش یک مشتری خاص چقدر بوده است، می‌توانیم از عملکرد جدید XLOOKUP برای جستجو از آخر به اول استفاده کنیم.
مثال 4 - آخرین رخداد موجود یک مقدار را بیابید
