نحوه استفاده از 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: این محدوده سلول هایی است که می خواهید مقدار بازگشتی در آن نمایش داده شود.
• Return_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 شما کامل شد، قبل از زدن اینتر یک پرانتز بسته اضافه کنید.

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 انجام می دهید)، ارجاعات توصیفی درک آنچه را که در ابتدا از فرمول برای انجام آن استفاده می کردید آسان تر می کند.

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

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

استفاده از پیش‌فرض مطابقت دقیق در فرمول (بر خلاف کاراکترهای عام) به شما کمک می‌کند تا مقادیر ناخواسته را در بازگشت دریافت نکنید.

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

محدوده های استدلال را در یک اندازه نگه دارید

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

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

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

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

شروع شدن

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

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