توابع XLOOKUP و VLOOKUP اکسل یک مقدار را در یک جدول یا یک لیست پیدا کرده و نتایج مرتبط را برمی گرداند. در اینجا نحوه تفاوت آنها و اینکه کدام یک بهتر عمل می کند، آمده است.
توابع XLOOKUP و VLOOKUP اکسل برای تجزیه و تحلیل سریع مجموعه های بزرگ داده استفاده می شود. VLOOKUP تابعی است که سال ها در اکسل استفاده می شود. با این حال، یک تابع جدیدتر وجود دارد که باید از آن استفاده کنید به نام XLOOKUP.
هر دو مزایا و محدودیت های خود را دارند. ما اینجا هستیم تا تفاوت ها را از بین ببریم و به شما کمک کنیم تصمیم بگیرید کدام یک برای پروژه شما مناسب تر است.
VLOOKUP چگونه کار می کند؟
VLOOKUP مخفف عبارت Vertical Look-up است. این یک تابع اکسل است که جستجوهای عمودی را برای یک مقدار در سمت چپ ترین ستون یک مجموعه داده انجام می دهد و سپس یک مقدار منحصر به فرد را از ستون دیگری در همان ردیف برمی گرداند. سینتکس تابع VLOOKUP به شرح زیر است:
=VLOOKUP(مقدار_جستجو، آرایه_جدول، col_index_num، [range_lookup])
بیایید به یک مثال نگاه کنیم. فرض کنید فهرستی از مدل‌های دستگاه‌های هوشمند در مجموعه داده‌های خود دارید و باید اطلاعات خاصی در مورد آنها تهیه کنید.
می توانید از تابع VLOOKUP برای جمع آوری اطلاعات اضافی در مورد پردازنده ها و قیمت آنها استفاده کنید.
در اینجا یک راهنمای دقیق در مورد نحوه استفاده از VLOOKUP در اکسل برای استخراج اطلاعات خاص از یک مجموعه داده است. اگرچه VLOOKUP یکی از بهترین توابع در اکسل است و می تواند به شما در ساده کردن مجموعه داده های بزرگ کمک کند، اما چند محدودیت دارد. به عنوان مثال، داده ها باید به ترتیب صعودی مرتب شوند تا فرمول کار کند.
XLOOKUP چگونه کار می کند؟
XLOOKUP به شما امکان می دهد برای جستجوی یک مقدار در یک صفحه گسترده، جستجوهای افقی و عمودی انجام دهید. تابع اساساً جستجوی آرایه را انجام می دهد. اگر مطابقت یافت شود، آیتم مربوطه را از محدوده داده دوم (return_array) برمی گرداند.
نسبت به سایر گزینه های جستجو موثرتر است زیرا به طور پیش فرض مطابقت دقیقی دارد. می‌توانید از آرگومان‌های اختیاری در آرگومان ([match_mode]) استفاده کنید تا نوع مقادیر تقریبی تطبیق مورد نیاز خود را مشخص کنید – یک تطابق دقیق یا یک مقدار پایین‌تر یا بالاتر بعدی، یا یک تطابق کاراکترهای عام.
می توان از آن برای اهداف مختلفی از جمله تحلیل مالی، تحلیل بازاریابی و فروش و … استفاده کرد. سینتکس تابع XLOOKUP به شرح زیر است:
=XLOOKUP(مقدار_جستجو، آرایه_جستجو، آرایه_بازگشت، [اگر_پیدا نشد]، [حالت_مطابق]، [حالت_جستجو])
راهنمای زیر شما را با اصول اولیه آشنا می کند و به شما کمک می کند تا نحوه استفاده از تابع XLOOKUP در کتاب کار خود را درک کنید. هر دوی این توابع به حداقل سه آرگومان نیاز دارند.
تفاوت های کلیدی بین XLOOKUP و VLOOKUP
در حالی که شباهت های زیادی بین XLOOKUP و VLOOKUP وجود دارد، درک تفاوت ها بسیار مهم است.
1. شماره فهرست ستون
هنگام اجرای VLOOKUP، برای بررسی و تعیین شماره فهرست دقیق ستون، باید به جدول منبع بروید.
VLOOKUP برای برگرداندن نتایج صحیح به پارامتر شماره شاخص ستون نیاز دارد. استفاده از یک عدد شاخص ستون نادرست می‌تواند منجر به خروجی متفاوتی از کاربرگ یا داده‌های شما شود، چه به صورت مقدار نادرست یا یک خطای “#REF”.
در مثال بالا، فرض کنید باید جزئیات پردازنده دستگاه، Apple iPhone 12 را استخراج کنید. ما از مجموعه داده دوم برای همه نمونه ها استفاده خواهیم کرد.
بر این اساس، باید مقدار جستجو، یعنی دستگاه Apple iPhone 12 را در ستون J وارد کنیم، که اتفاقاً سمت چپ ترین ستون در مجموعه داده مثال ما است، و آرایه جدول مربوطه (ستون های J و K). مقدار باید از ستون دوم آرایه جدول، یعنی ستون جستجوی K بازیابی شود، بنابراین، شماره فهرست ستون 2 خواهد بود.
XLOOKUP یک تابع پویا است که آرگومان شماره شاخص ستون را حذف می کند، به این معنی که شما نیازی به نگرانی در مورد شمارش تعداد ستون های یک جدول ندارید. شما فقط باید ستون های جستجو و جستجو را مشخص کنید، که باعث صرفه جویی در وقت و تلاش شما می شود.
2. استحکام و انعطاف پذیری
هنگامی که مدل خود را ایجاد کردید، باید بتوانید به سرعت جدول منبع را با داده های تازه به روز کنید بدون اینکه نگران شکستن فرمول های خود باشید. با VLOOKUP، اضافه کردن یا جابجایی ستون ها به این معنی است که باید کل فرمول را دوباره انجام دهید.
XLOOKUP یک عملکرد قوی تر است که به شما امکان می دهد بدون نگرانی در مورد خطا، ستون ها را اضافه یا حذف کنید. فقط مطمئن شوید که ستون یا ردیفی را خارج از آرایه جستجو وارد نکنید، در این صورت تابع یک خطا برمی گرداند.
3. پیام های خطای سفارشی
عملکرد XLOOKUP برای استفاده راحت است. این به شما امکان می دهد یک پیام متنی خطای سفارشی را در آرگومان [if_not_found] وارد کنید که اگر مقدار جستجو پیدا نشد، نشان داده می شود.
VLOOKUP عملکرد مشابهی را ارائه نمی دهد. در مواردی که هیچ منطبقی پیدا نمی شود، ممکن است لازم باشد از توابع دیگری مانند IFERROR به عنوان راه حل استفاده کنید.
چرا XLOOKUP در مقایسه با VLOOKUP بهتر است؟
اگرچه VLOOKUP کاربردهای خود را دارد، اما از نظر عملکرد محدودتر از XLOOKUP است. این امر XLOOKUP را به انتخاب ارجح برای کار بر روی مجموعه داده های بزرگتر تبدیل می کند. نتایج سریعتر و کمتر مستعد خطا هستند. بیایید درک کنیم که چرا XLOOKUP در مقایسه با VLOOKUP بهتر کار می کند.
1. XLOOKUP به محدوده داده های خاص محدود نمی شود
XLOOKUP بسیار انعطاف پذیرتر از VLOOKUP است، که می تواند مقادیر را فقط در سمت چپ ترین ستون جدول جستجو کند و مقادیر را از ستون های مربوطه در سمت راست برگرداند، همانطور که در مثال بالا دیدیم. در مقابل، مدل XLOOKUP به مراحل ساده‌تری نیاز دارد و می‌تواند مقادیر را برای هر ستون در هر جهت برگرداند.
2. XLOOKUP به طور پیش فرض مطابقت دارد
به طور پیش فرض، تابع VLOOKUP در اکسل فرض می کند که یک مطابقت تقریبی را جستجو می کند. با این حال، اگر مطابقت دقیق برای کاربرگ شما مورد نیاز است، باید “FALSE” را در آرگومان range_lookup وارد کنید.
تابع XLOOKUP مقدار جستجو را در محدوده مشخص شده جستجو می کند که به طور پیش فرض دقیقاً مطابقت دارد. اگر تطابق دقیقی پیدا نشد، می‌توانید آرگومان را برای جستجوی یک تطابق تقریبی که ممکن است کمتر یا بیشتر از مقدار جستجو باشد، تغییر دهید.
برای انجام این کار، می‌توانید آرگومان [match_mode] اختیاری را روی 0 = تطابق دقیق (پیش‌فرض)، -1 = تطابق دقیق یا کوچک‌ترین بعدی، 1 = تطابق دقیق یا بزرگ‌ترین بعدی، یا 2 = تطابق علامت عام تنظیم کنید.
3. XLOOKUP می تواند درج یا حذف ستون را مدیریت کند
VLOOKUP یک برنامه عالی برای یافتن داده های خاص از صفحه گسترده یا جدول دیگری است. با این حال، این بیشتر یک اندازه گیری ثابت است – اگر نیاز به به روز رسانی منبع داده یا ایجاد تغییرات یا ترتیب مجدد ستون ها داشته باشید، فرمول شکسته می شود و در نهایت تابع VLOOKUP را از دست می دهید.
از سوی دیگر، XLOOKUP یک معیار پویاتر است که می تواند تغییرات ستون را مدیریت کند و همچنان نتایج جستجوی دقیق را برگرداند. به جای آرگومان آرایه جدول و شماره شاخص ستون در VLOOKUP، پارامترهای خاص تری مانند آرایه جستجو و آرایه بازگشتی ارائه می شود.
4. XLOOKUP نگاه های عمودی و افقی را انجام می دهد
تابع XLOOKUP می تواند داده ها را در هر دو آرایه افقی و عمودی پیدا کند، برخلاف VLOOKUP، که فقط می تواند جستجوی عمودی داده ها را انجام دهد.
5. XLOOKUP مطابقت های تقریبی دقیق تری را ارائه می دهد
اگر به دنبال موردی هستید که به آنچه می خواهید نزدیک باشد، XLOOKUP نتایج دقیق تری به شما ارائه می دهد. نتایج دقیق تر و مثبت کاذب کمتری ارائه می دهد.
با این حال، با VLOOKUP، اگر می‌خواهید یک تطابق تقریبی از صفحه‌گسترده خود پیدا کنید، این تابع فقط مقدار کوچکتر بعدی مرجع تطبیق را برمی‌گرداند. اما XLOOKUP به شما امکان می دهد تطابق تقریبی بهتری انجام دهید و مقادیر بزرگتر یا کوچکتر بعدی را نیز از جدول جستجوی خود برگردانید.
6. XLOOKUP به مراجع سلولی کمتری نیاز دارد
در حین استفاده از XLOOKUP، تنها چیزی که نیاز دارید این است که دو محدوده خاص از داده ها را انتخاب کنید – یک آرایه یا محدوده از جایی که می خواهید یک مقدار را جستجو کنید (lookup_array)، و یک آرایه/محدوده که می خواهید مقدار را از آن بازیابی کنید (return_array).
از سوی دیگر، تابع VLOOKUP از شما می‌خواهد تا با استفاده از پارامتر table_array، طیف وسیعی از داده‌ها را انتخاب کنید. این می تواند یک مشکل با مجموعه داده های پیچیده باشد زیرا اگر صفحه گسترده شما بزرگ باشد، مرتب کردن مجدد یا انتخاب کل محدوده سلولی که می خواهید مقداری را از آن برگردانید ممکن است دشوار باشد. علاوه بر این، برای برگرداندن یک تطابق تقریبی با استفاده از VLOOKUP، داده‌های جدول همیشه باید به ترتیب صعودی مرتب شوند.
VLOOKUP یا XLOOKUP: کدام یک برای شما مناسب است؟
بنابراین، آیا XLOOKUP بهتر از VLOOKUP است؟ در یک مقایسه مستقیم، پاسخ باید مثبت باشد. با این حال، ممکن است برای همه کاربران به این سادگی نباشد. به عنوان مثال، اگر به طور خاص در آن مهارت دارید یا زمانی که برای یک عملکرد خاص مناسب برای استفاده خود استفاده می کنید، ممکن است یک تابع قدیمی تر را ترجیح دهید.
برای مجموعه داده‌های بزرگتر، XLOOKUP به دلیل افزایش عملکرد، احتمالاً گزینه بهتری برای شما است. VLOOKUP چندین محدودیت دارد که استفاده از آن را برای داده‌های پیچیده دشوار می‌کند – و در نتیجه، XLOOKUP انعطاف‌پذیری بسیار بیشتری دارد و آن را برای اکثر کاربران صفحه‌گسترده انتخاب بهتری می‌کند.