نحوه استفاده از XLookup در اکسل

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

چندین توابع شناخته شده وجود دارد که به شما کمک می کند تا منطبقات خاص را در یک مجموعه داده جستجو کنید، مانند VLookup.

اما توابعی مانند VLookup محدودیت های خاصی داشتند، بنابراین مایکروسافت اخیراً تابع XLookup را در اکسل معرفی کرد. این روش قوی تری برای یافتن مقادیر خاص در یک مجموعه داده ارائه می دهد.

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

XLookup در اکسل چه می کند؟

مزایای XLookup در اکسل

XLookup آرگومان های مورد نیاز

آرگومان های اختیاری برای XLookup

نحوه استفاده از XLookup در اکسل

بهترین روش ها برای XLookup در اکسل

XLookup در اکسل چه می کند؟

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

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

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

سایر توابع جستجو مانند VLookup (جستجوی عمودی) یا HLookup (جستجوی افقی) دارای محدودیت هایی هستند، مانند جستجو فقط از راست به چپ. کاربران باید داده ها را دوباره مرتب کنند یا راه حل های پیچیده ای برای کاهش این موضوع پیدا کنند.

XLookup یک راه حل بسیار انعطاف پذیرتر است که به شما امکان می دهد مطابقت های جزئی را دریافت کنید، از بیش از یک معیار جستجو و استفاده از پرس و جوهای تودرتو استفاده کنید.

این آموزش XLookup Excel را بررسی کنید تا آن را در عمل ببینید:

مزایای XLookup در اکسل

مزیت اصلی XLookup در اکسل صرفه جویی در زمان است. شما می توانید بدون دستکاری موقعیت داده ها به نتیجه جستجو برسید.

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

وقتی صحبت از مقایسه با سایر توابع جستجو می شود، انعطاف پذیری XLookup کلیدی است. به عنوان مثال، می توانید از حروف عام برای جستجوی موارد منطبق استفاده کنید.

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

آرگومان‌های XLookup نیز ساده‌تر از VLookup هستند و استفاده از آن را سریع‌تر و آسان‌تر می‌کنند. به عنوان مثال، XLookup به طور پیش فرض مطابقت دقیق دارد، در حالی که شما باید این را در آرگومان VLookup خود مشخص کنید.

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

XLookup آرگومان های مورد نیاز

تابع XLookup شما به سه آرگومان نیاز دارد:

• Lookup_value: این مقداری است که در یک آرایه جستجو می کنید.
• Lookup_array: این محدوده سلول هایی است که می خواهید مقدار بازگشتی در آن نمایش داده شود.
• returned_array: این محدوده سلول هایی است که می خواهید تابع به دنبال مقدار باشد.

بنابراین یک نسخه ساده از XLookup به شکل زیر است:

=XLOOKUP(مقدار_ جستجو، آرایه_جستجو، آرایه_بازگشت)

آرگومان های اختیاری برای XLookup

تابع XLookup همچنین چندین آرگومان اختیاری دارد که می توانید برای سناریوهای پیچیده تر یا محدود کردن جستجوی خود از آنها استفاده کنید:

• Match_mode: این نوع تطابق مورد استفاده را مشخص می‌کند، مانند تطابق دقیق یا تطابق با حروف عام برای دریافت منطبقات جزئی.
• Search_mode: این مشخص می کند که آیا تابع شما باید از چپ به راست جستجو کند یا برعکس.
• If_not_found: این آرگومان به تابع می گوید که در صورت عدم وجود تطابق چه مقداری را برگرداند.

با گنجاندن آرگومان های اختیاری، تابع XLookup به شکل زیر است:

=XLOOKUP(مقدار_ جستجو، آرایه_جستجو، آرایه_بازگشت، [if_not_found]، [match_mode]، [search_mode])

نحوه استفاده از XLookup در اکسل

1. اکسل و برگه داده ای را که می خواهید از تابع XLookup برای آن استفاده کنید باز کنید.

2. سلولی را که می خواهید فرمول XLookup Excel را در آن جایگذاری کنید، انتخاب کنید.

3. “=”” را در این سلول تایپ کنید و سپس “XLookup” را تایپ کنید. روی گزینه “XLookup” که در منوی کشویی ظاهر می شود کلیک کنید.

4. یک براکت باز کننده به طور خودکار ایجاد می شود. پس از پرانتز باز، آرگومان های مورد نیاز را به ترتیب صحیح وارد کنید: lookup_value، lookup_array و return_array.

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

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

6. هنگامی که آرگومان های XLookup شما کامل شد، قبل از فشردن enter، یک بند بسته اضافه کنید.

7. نتایج XLookup شما باید در سلولی که تابع را وارد کرده اید ظاهر شود.

در این مثال اکسل XLookup، ما از XLookup برای پیدا کردن نمره یک دانش آموز خاص استفاده کردیم. بنابراین lookup_value نام دانش آموز (“Ruben Pugh”) بود.

lookup_array لیستی از نام دانش آموزان در ستون A بود. در نهایت، lookup_return لیست نمرات دانش آموزان در ستون C بود. با این فرمول، تابع مقدار نمره دانش آموز را برمی گرداند: C-.

اما چگونه می توان از XLookup با آرگومان های اختیاری استفاده کرد؟

با استفاده از مجموعه داده‌های مشابه بالا، فرض کنید می‌خواهیم میزان حضور دانش‌آموزی با نام خانوادگی «اسمیت» را پیدا کنیم.

در اینجا ما به اکسل می گوییم که اگر نمی تواند مقدار را بازیابی کند، پیام “Not Found” را برگرداند، که نشان می دهد دانش آموز “Smith” در این لیست نیست:

حال، فرض کنید می‌خواهیم مشخص کنیم که آیا برخی از دانش‌آموزان 70 درصد حضور داشتند یا خیر. اما اگر 70 درصد در مجموعه داده‌ها وجود نداشته باشد، ما همچنین می‌خواهیم نزدیک‌ترین نرخ حضور و غیاب بعدی را بدانیم.

اگر 70 وجود نداشته باشد، از مقدار “1” در آرگومان match_mode استفاده می کنیم تا بزرگترین عنصر بعدی را پیدا کنیم:

از آنجایی که هیچ دانش آموزی 70% حضور ندارد، XLookup “75” را که بالاترین مقدار بعدی است، برگردانده است.

بهترین روش ها برای XLookup در اکسل

از مراجع تشریحی استفاده کنید

ترجیحاً به جای محدوده‌های عمومی مانند A1:A12 از مراجع توصیفی برای سلول‌هایی که استفاده می‌کنید استفاده کنید.

هنگامی که زمان تنظیم فرمول خود فرا می رسد (که اغلب با عملکردی مانند XLookup انجام می دهید)، ارجاعات توصیفی درک آنچه را که در ابتدا از فرمول استفاده کرده اید آسان می کند.

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

در صورت امکان از یک تطابق دقیق استفاده کنید

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

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

محدوده آرگومان Keep به همان اندازه است

هنگام جمع آوری آرگومان های خود، مطمئن شوید که از همان تعداد سلول برای آرایه return_array و آرایه look_up استفاده می کنید. در غیر این صورت، با خطا مواجه می شوید و اکسل فقط مقدار #VALUE را در سلول برمی گرداند.

فرمول خود را تست کنید

رفع مشکلات با یک XLookup ساده کار سختی نیست. اما اگر از توابع تو در تو یا XLookup در ارتباط با فرمول های دیگر استفاده می کنید، مطمئن شوید که همه چیز را در طول مسیر آزمایش کرده اید.

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

برای شروع آماده می شویم

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

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