آموزش تابع XLOOKUP در اکسل به زبان ساده

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

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

XLOOKUP در اکسل

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

آموزش اکسل مقدماتی

کلیک کنید

XLOOKUP به عنوان یک ابزار جستجوی سفارشی عمل می‌کند و با نیازهای انواع مختلف داده‌ها در صفحات گسترده اکسل سفارشی سازگار است. XLOOKUP در اکسل در اصل نوعی تابع نسبتاً جدید است که تنها از سال ۲۰۲۰ برای کاربران مایکروسافت ۳۶۵ در دسترس قرار گرفته است. با این حال، به سرعت به یکی از بهترین ویژگی‌های اکسل برای تجزیه و تحلیل داده‌ها تبدیل شده است.

آموزش XLOOKUP در اکسل

در این مطلب از مجله مکتوب موارد زیر را یاد خواهیم گرفت:

• تابع XLOOKUP چیست و چگونه کار می‌کند؟
• تابع XLOOKUP چگونه در مایکروسافت اکسل کار می‌کند؟
• مواردی که قبل از استفاده از تابع XLOOKUP در اکسل باید در نظر بگیرید

گام اول. تابع XLOOKUP چیست و چگونه کار می‌کند؟

اکسل از قبل ابزارهای متعددی برای جستجوی داده‌ها دارد، از قوانین قالب‌بندی شرطی گرفته تا ابزار جستجو و انتخاب داخلی. با این حال، این ویژگی‌ها واقعاً صفحه گسترده شما را به روشی معنادار تغییر نمی‌دهند. برای مثال، نمی‌توانید به راحتی یک سلول قالب‌بندی شده بر اساس رنگ را به طور خودکار در فرمول دیگری ارجاع دهید. اگر داده‌هایی دارید که باید به سرعت در آن‌ها جستجو کنید، با معیارهای جستجوی سفارشی که باید مرتباً تغییر دهید، چه؟

VLOOKUP و HLOOKUP – راه‌حل‌های احتمالی

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

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

تفاوت vlookup و xlookup چیست؟

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

راه‌حل XLOOKUP

شما می‌توانید برخی از این محدودیت‌ها را با توابع دیگر (مانند INDEX و MATCH) دور بزنید؛ اما این فرآیند کارآمد نیست. XLOOKUP راه‌حلی برای این مشکل است و به شما امکان می‌دهد داده‌ها را در ستون‌ها و ردیف‌ها، هم در سمت چپ و هم در سمت راست مقدار جستجوی خود جستجو کنید.

آموزش اکسل برای کسب وکار مقدماتی

کلیک کنید

این تابع می‌تواند مطابقت‌های تقریبی، مقادیر جستجوی متعدد، پرس و جوهای تودرتو (با استفاده از چندین تابع XLOOKUP در یک فرمول واحد)، پیام‌های خطای سفارشی و موارد دیگر را مدیریت کند. XLOOKUP (در حال حاضر) فقط برای مشترکین مایکروسافت ۳۶۵ در دسترس است، اما برای جستجوهای داده‌ای اولیه و پیچیده، یک تغییر دهنده مطلق بازی است.

گام دوم. تابع XLOOKUP چگونه در مایکروسافت اکسل کار می‌کند؟

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

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

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

این موارد شامل lookup، lookup_array، return_array، not_found (اختیاری)، match_mode (اختیاری) و search_mode (اختیاری) است. برای ایجاد یک فرمول XLOOKUP به ساختار زیر نیاز است:

=XLOOKUP(lookup, lookup_array, return_array, not_found, match_mode, search_mode)

آرگومان‌های مورد نیاز (lookup، lookup_array، return_array)

در این مثال، lookup به مقدار جستجویی که به دنبال آن هستیم اشاره دارد. برای مثال، اگر به دنبال شماره شناسه هستیم، از خود مقدار (مثلاً ۱۰۱) یا یک مرجع سلول حاوی آن مقدار (مثلاً E۳) استفاده می‌کنیم.

برای یافتن آن مقادیر، باید محدوده‌های سلول را برای lookup_array و return_array شناسایی کنیم. در این مثال، lookup_array ستون یا ردیفی است که حاوی مقدار lookup است (مثلاً A۲:A۱۰ برای شماره‌های شناسه در ستون A).

return_array ستون یا ردیف جداگانه‌ای است که حاوی داده‌هایی است که می‌خواهید برگردانید. برای مثال، اگر شماره‌های شناسه را جستجو می‌کنیم، اما می‌خواهیم به جای آن نام کارمند را برگردانیم، به محدوده سلول صحیح حاوی آن نام‌ها (مثلاً B۲:B۱۰ برای نام کارمندان در ستون B) نیاز داریم.

آرگومان‌های اختیاری (not_found، match_mode، search_mode)

اگر می‌خواهید وقتی هیچ نتیجه‌ای مطابقت ندارد، یک مقدار سفارشی برگردانید، باید یک آرگومان not_found اضافه کنید. این می‌تواند یک عدد، رشته متن (در علامت نقل قول)، مقدار بولی (TRUE یا FALSE) یا یک مرجع سلول (مثلاً ۱، “یافت نشد” یا G۱) باشد. اگر این مورد را ارائه نکنید، اکسل یک خطای #N/A برای مقادیر غیر منطبق برمی‌گرداند.

XLOOKUP در اکسل به طور پیش فرض، مطابقت‌های دقیق با مقدار lookup را جستجو می‌کند. اگر می‌خواهید این را تغییر دهید، می‌توانید آرگومان اختیاری match_mode را در فرمول XLOOKUP خود ارائه دهید. این یک مقدار عددی است، با ۰ برای مطابقت‌های دقیق، -۱ برای یک مطابقت دقیق (یا مقدار بعدی زیر آن)، ۱ برای یک مطابقت دقیق (یا مقدار بعدی بالای آن) و ۲ برای مقادیر wildcard استفاده می‌شود.

اگر ۲ را برای match_mode انتخاب کنید، می‌توانید از عملگرهای wildcard مانند ستاره (*)، علامت سؤال (?) یا تیلدا (~) با مقادیر lookup استفاده کنید. اکسل اولین مطابقت موجود با مقدار lookup را به عنوان نتیجه برمی‌گرداند.

در نهایت، می‌توانید ترتیب جستجو برای مقادیر برگشتی را با استفاده از آرگومان search_mode تعیین کنید. XLOOKUP بلافاصله با اولین مقدار به طور پیش فرض مطابقت می‌کند (مقدار ۱ برای search_mode، اگر می‌خواهید یا نیاز به تعیین آن دارید)، یا می‌توانید این را معکوس کرده تا از آخرین مقدار منطبق با -۱ استفاده کنید.

همچنین می‌توانید از جستجوهای دودویی به ترتیب صعودی با ۲ یا نزولی با -۲ استفاده کنید. یک جستجوی دودویی اساساً نوعی جستجوی بازگشتی سریع با استفاده از یک مجموعه داده مرتب شده است، بنابراین باید مطمئن شوید که

گام سوم: تعیین مجموعه داده با استفاده از lookup_array و return_array

هنگامی که مقدار lookup (مقدار جستجوی شما، یا یک مرجع سلول حاوی آن) را وارد کردید، باید محدوده‌های سلول lookup_array و return_array خود را مشخص کنید.

آموزش توابع و فرمول ها در اکسل

کلیک کنید

lookup_array ردیف یا ستونی است که احتمالاً حاوی پرس و جوی جستجوی شما (یا نزدیک‌ترین نتیجه تقریبی) است. return_array ردیف یا ستونی است که می‌خواهید به عنوان نتیجه ببینید. این می‌تواند سلولی در ستون دیگری در همان خط باشد (برای مثال، مطابقت شماره‌های شناسه با نقش‌های شغلی در صفحه گسترده کارمند نمونه ما).

برای مثال ما، استفاده از =XLOOKUP(E3,A2:A10,D2:D10) به این معنی است که lookup_array مورد استفاده محدوده سلول عمودی A۲:A۱۰ است، در حالی که return_array محدوده سلول عمودی D۲:D۱۰ است.

در این مرحله، اگر به اضافه کردن یک پیام خطای سفارشی، استفاده از نوع جستجوی متفاوت (مثلاً مطابقت تقریبی یا wildcard) یا تغییر ترتیب مقدار جستجو علاقه ندارید، می‌توانید فرمول خود را با یک پرانتز بسته ببندید. در غیر این صورت، با پیروی از مراحل زیر، به اضافه کردن آرگومان‌های اختیاری به فرمول خود ادامه دهید.

گام چهارم (اختیاری): اضافه کردن یک پیام خطای سفارشی با استفاده از not_found

اگر سعی می‌کنید یک مطابقت دقیق برای مقدار lookup خود پیدا کنید، اما نمی‌توان آن را در محدوده lookup_array شما یافت، اکسل یک پاسخ خطای #N/A برمی‌گرداند. برای غلبه بر این مشکل، باید یک آرگومان اختیاری not_found به فرمول XLOOKUP خود اضافه کنید.

آرگومان not_found می‌تواند یک مقدار عددی (مانند ۱)، یک رشته متن در علامت نقل قول (مانند “یافت نشد”)، یک مقدار بولی (TRUE یا FALSE) یا یک مرجع سلول (مانند A۲) باشد.

برای مثال، =XLOOKUP(E3,A2:A10,D2:D10,”not found”) تضمین می‌کند که اگر یک مطابقت دقیق با مقدار در E۳ یافت نشد، اکسل پیام یافت نشد را برمی‌گرداند. از آنجا که شماره‌های شناسه در محدوده سلول A۲ تا A۱۰ از ۱۰۱ تا ۱۰۹ هستند و مقدار lookup در سلول E۳ ۱۱۰ است، اکسل در نتیجه یافت نشد را برمی‌گرداند.

از آنجا که هر آرگومان به صورت متوالی در نظر گرفته می‌شود، اگر قصد دارید آرگومان‌های match_mode و search_mode بیشتری را در زیر به فرمول خود اضافه کنید، باید یک آرگومان not_found سفارشی اضافه کنید.

گام پنجم (اختیاری): تنظیم مطابقت‌های دقیق، تقریبی یا wildcard با استفاده از match_mode

در حالی که XLOOKUP در اکسل به طور پیش فرض برای جستجوی مطابقت‌های دقیق طراحی شده است، این مورد می‌تواند با اضافه کردن یک مقدار آرگومان اختیاری match_mode به فرمول شما تغییر کند.

چهار مقدار ممکن وجود دارد: ۰ برای یک مطابقت دقیق (مقدار پیش فرض، اگر اضافه نشود)، -۱ برای یک مطابقت دقیق یا مقدار بعدی زیر آن، ۱ برای یک مطابقت دقیق یا مقدار بعدی بالای آن، یا ۲ برای مطابقت‌های جزئی با استفاده از ستاره‌ها، تیلداها یا علامت سؤال wildcard.

با استفاده از صفحه گسترده مثال ما، فرمول جزئی =XLOOKUP(E3,B2:B10,C2:C10,”not found”,۲) تضمین می‌کند که XLOOKUP یک پرس و جوی wildcard را پشتیبانی می‌کند. در این مثال، XLOOKUP نزدیک‌ترین مقدار منطبق با پرس و جوی wildcard (ji*) را در محدوده lookup_array B۲:B۱۰ پیدا می‌کند، سپس مقدار منطبق در همان ردیف را از محدوده return_array C۲:C۱۰ (یک آدرس ایمیل) برمی‌گرداند.

به عنوان یک مطابقت wildcard، این اولین و نزدیک‌ترین تقریب خواهد بود. اگر می‌خواهید ترتیب جستجو را تغییر دهید (به عنوان مثال، با استفاده از آخرین مقدار ممکن، به جای اولین)، باید در مرحله بعد یک آرگومان search_mode اضافه کنید.

گام ششم (اختیاری): تعیین ترتیب بازگشت مقدار با استفاده از search_mode

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

برای مثال، در یک پرس و جوی wildcard ساده که J* مقدار lookup است، آرایه‌ای حاوی نام‌های James، Jack، Jim، Jess، Julia و Jane به این معنی است که ۱۰۱ مقداری است که معمولاً برگردانده می‌شود، زیرا James اولین مقدار در یک پرس و جو است که در آن همه مقادیر به طور بالقوه مطابقت دارند. اگر می‌خواهید این رفتار را تغییر دهید تا به جای آن آخرین مقدار ممکن را پیدا کنید، باید آرگومان search_mode را تغییر دهید.

چهار مقدار ممکن برای یک آرگومان search_mode وجود دارد: ۱ اولین مقدار ممکن را جستجو می‌کند (این گزینه پیش فرض است) در حالی که ۱- این را معکوس می‌کند تا آخرین مقدار ممکن را جستجو کند. از طرف دیگر، می‌توانید یک جستجوی دودویی به ترتیب صعودی با ۲ یا جستجوی دودویی معکوس به ترتیب نزولی با ۲- انجام دهید.

جستجوهای دودویی می‌توانند سریع‌تر باشند و تعداد جستجوها را در یک آرایه داده برای یافتن مقدار صحیح کاهش دهند (و در نتیجه تأثیر کلی حافظه پرس و جو را، به ویژه با مجموعه‌های داده بزرگتر، کاهش دهند)، اما قبل از استفاده از آن، باید داده‌های خود را مرتب کنید.

اکثر کاربران احتمالاً می‌خواهند به ۱ یا -۱ برای search_mode پایبند باشند؛ اما اگر با مقدار زیادی داده کار می‌کنید، ۲ یا -۲ تأثیر را کاهش می‌دهد.

برای مثال، در فرمول =XLOOKUP(E3,A2:A10,D2:D10,”not found”,-1,1)، اکسل مطابقت دقیق یا مقدار بعدی و کوچکترین مقدار منطبق با مقدار lookup در سلول E۲ را پیدا می‌کند و آن را برمی‌گرداند. با تنظیم search_mode به ۱، اکسل به ترتیب صعودی جستجو می‌کند و اولین مقدار منطبق را در محدوده برمی‌گرداند.

در این مثال، با تنظیم مقدار در E۲ به ۱۱۰ و بدون هیچ مقداری که مطابقت دقیق ارائه می‌دهد، نتیجه بعدی و کوچکترین، شماره شناسه ۱۰۹ است.

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

سخن نهایی

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

آموزش معتبر سازی داده در اکسل

کلیک کنید

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

سؤالات متداول

آیا XLOOKUP در همه نسخه‌های اکسل موجود است؟

• خیر، XLOOKUP فقط در نسخه‌های جدیدتر اکسل، به ویژه برای مشترکین مایکروسافت ۳۶۵ در دسترس است.

آیا می‌توان از XLOOKUP برای جستجوی داده‌ها در فایل‌های اکسل مختلف استفاده کرد؟

• بله اما هر دو فایل باید هم‌زمان باز باشند. در غیر این صورت، با خطای #REF مواجه خواهید شد.

آیا XLOOKUP در اکسل می‌تواند چندین نتیجه را برگرداند؟

• بله با استفاده از فرمول‌های پیچیده‌تر و تودرتو، می‌توان XLOOKUP را برای برگرداندن چندین نتیجه پیکربندی کرد.

آیا استفاده از XLOOKUP پیچیده است؟

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