تابع XLookup در اکسل – آموزش کامل با مثال

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

افزودن تابع XLOOKUP به اکسل

تابع XLOOKUP شکل پیشرفته تابع VLOOKUP است که شرکت مایکروسافت برای برطرف کردن محدودیت‌های توابع قدیمی جستجو در نسخه‌های جدید اکسل ۲۰۲۱ و آفیس ۳۶۵ به‌صورت پیش‌فرض قرار داده است. بنابراین در این نسخه‌ها نیازی به اضافه کردن آن در اکسل نداریم. کاربران نسخه‌های قدیمی‌تر اکسل می‌توانند از سایر توابع جستجو مانند تابع VLOOKUP، تابع HLOOKUP، ترکیب دو تابع MATCH و INDEX یا ابزارهای پیشرفته Power Query استفاده کنند.

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

شکل کلی تابع XLOOKUP در اکسل

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

آرگومان‌های تابع XLOOKUP

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

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

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

1. Lookup_value (اجباری): عبارتی که می‌خواهیم آن را در جدول جستجو کنیم.
2. Lookup_array (اجباری): محدوده یا آرایه سلول که جستجو را در آن انجام می‌دهیم.
3. Return_array (اجباری):محدوده‌ای که فرمول، مقدار متناظر آرایه جستجو را از آن برمی‌گرداند.
4. If_not_found (اختیاری): متن دلخواهی که به‌عنوان خروجی جستجو در صورت پیدا نشدن مقدار مورد نظر نمایش داده می‌شود. در صورت تعریف نکردن این آرگومان، فرمول پیام خطای #N/A را باز می‌گرداند.
5. Match_mode (اختیاری): در این آرگومان نوع تطابق عبارت هنگام جستجو را با اعداد زیر تعریف می‌کنیم.

به موارد زیر توجه کنید:

• «۰» : حالت پیش‌فرض فرمول عدد صفر است که در آن جستجو با روش تطابق دقیق انجام می‌شود. اگر تابع عبارت را پیدا نکند، خروجی فرمول پیام خطای #N/A خواهد بود.
• «۱-»: تطابق دقیق یا نزدیک‌ترین مقدار کوچک‌تر. اگر عبارت دقیق پیدا نشود، تابع اولین مقدار کوچک را به‌عنوان نتیجه جستجو در نظر می‌گیرد.
• «۱»: تطابق دقیق یا نزدیک‌ترین مقدار بزرگ‌تر. اگر عبارت دقیق پیدا نشود، تابع اولین مقدار بزرگ‌تر را به‌عنوان نتیجه جستجو در نظر می‌گیرد.
• «۲»: تطابق بخشی از عبارت با استفاده از کاراکترهای وایلدکارت
  • Search_mode (اختیاری): این آرگومان نشان‌دهنده جهت جستجو است و با سه عدد به شکل زیر مشخص می‌شود.
• «۱»: جستجو از اولین سلول به آخرین سلول
• «۱-»: جستجوی معکوس از آخرین سلول به اولین سلول
• «۲»: جستجوی دودویی (Binary Search) در داده‌های مرتب‌ شده از مقدار کوچک به بزرگ به‌صورت صعودی
• «۲-»: جستجوی دودویی در داده‌های مرتب شده از مقدار بزرگ به کوچک به‌صورت نزولی

البته جستجوی دودویی برای کاربران حرفه‌ای اکسل است و نوشتن فرمول به این شکل سرعت جستجو را بیشتر می‌کند. در این حالت مرتب بودن داده‌ها برای دریافت نتیجه درست بسیار اهمیت دارد.

روش‌های اصلی جستجو با تابع XLOOKUP در اکسل

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

1. جستجو با تطبیق دقیق عبارت
2. جستجوی تقریبی عبارت
3. جستجوی بخشی از عبارت
4. جستجو در جهت افقی و عمودی
5. جستجو در جهت چپ

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

فیلم آموزش ساخت فرمول های پیچیده با ترکیب توابع مختلف در اکسل (رایگان) در فرادرس

کلیک کنید

جستجو با تطبیق دقیق عبارت

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

=XLOOKUP(B7,A2:A5,C2:C5)

در این فرمول ابتدا تابع XLOOKUP عبارت داخل سلول B7 یعنی «ماوس گیمینگ» را در محدوده سلول A2 تا A5 جستجو می‌کند، سپس مقدار مربوط به مبلغ فروش آن را در محدوده سلول‌های C2 تا C5 به‌عنوان نتیجه فرمول در سلول B8 نشان می‌دهد.

جستجوی تقریبی عبارت

برای جستجو با تطابق نزدیک، عدد «۱-» را در آرگومان match_mode فرمول تابع XLOOKUP می‌نویسیم. به‌عنوان مثال، در جدول زیر مقدار تخفیف کالاهای یک فروشگاه بر حسب تعداد فروش هر کالا تعریف شده‌اند. حال اگر بخواهیم میزان تخفیف برای فروش ۲۸ عدد کالا را در جدول پیدا کنیم، فرمول به‌صورت زیر خواهد بود.

=XLOOKUP(D4,A6:A6,B2:B6,,-1)

در این فرمول تابع XLOOKUP، مقدار عددی سلول D4 را در محدوده سلول‌های A2 تا A6 جستجو می‌کند. از‌آنجا‌که عدد ۲۸ به مقدار عددی ۲۵ در جدول نزدیک‌تر است، با جستجوی میزان تخفیف در سلول‌های B2 تا B6 مقدار مربوط به این عدد را به‌عنوان نتیجه در سلول E4 نشان می‌دهد.

جستجوی بخشی از عبارت

با استفاده از تابع XLOOKUP در اکسل و وایلدکارت‌ها می‌توانیم بخشی از عبارت مورد نظر را در جدول جستجو کنیم. برای این کار در فرمول، آرگومان match_mode را برابر عدد ۲ در نظر می‌گیریم. به‌عنوان مثال در جدول زیر می‌خواهیم مشخصات باتری گوشی موبایلی که عبارت «آیفون X» بخشی از نام آن است را پیدا کنیم.

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

=XLOOKUP("*"&E4&"*", A2:A8, B2:B8, ,2)

در این فرمول تابع XLOOKUP ابتدا عبارت داخل سلول E4 در محدوده سلول‌های A2 تا A8 را به شکل تطابق جزئی با وایلدکارت «*» جستجو می‌کند. سپس مقدار متناظر آن در محدوده ستون B2 تا B8 را به‌عنوان نتیجه نشان می‌دهد.

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

جستجو در جهت افقی و عمودی

در نسخه‌های قدیمی اکسل برای جستجوی عمودی در ستون‌ها از تابع VLOOKUP و جستجوی افقی در ردیف‌ها از تابع HLOOKUP استفاده می‌کردیم. اما تابع XLOOKUP هر دو قابلیت جستجوی افقی و عمودی را دارد. برای این کار کافی است با توجه به جهت جستجو، در فرمول ردیف یا ستون را وارد کنیم. به‌عنوان مثال برای جستجوی عمودی در جدول زیر فرمول را به این شکل می‌نویسیم.

=XLOOKUP(E2, A2:A5, B2:B5)

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

=XLOOKUP(B5, B1:E1, B2:E2)

جستجو در جهت چپ

برای جستجو در جهت‌های مختلف چپ یا راست، بالا یا پایین در نسخه‌های قدیمی اکسل ترکیب تابع INDEX و تابع MATCH تنها گزینه بود. اما با تابع XLOOKUP به‌راحتی می‌توانیم در هر جهتی جستجوی خود را انجام دهیم. به‌عنوان مثال اگر در جدول مثال قبل یک ستون به‌نام «ردیف» اضافه کنیم، برای پیدا کردن ردیف کالای «ماوس پد» فرمول زیر را می‌نویسیم.

=XLOOKUP(F2, B2:B5, A2:A5)

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

یادگیری حرفه‌ای توابع جستجوی اکسل در فرادرس

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

• فیلم آموزش استفاده از توابع و فرمول‌نویسی اکسل در فرادرس
• فیلم آموزش رایگان ساخت فرمول‌های پیچیده با ترکیب توابع مختلف اکسل در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس

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

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

جستجوی پیشرفته با تابع XLOOKUP در اکسل

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

فیلم آموزش اکسل – ابزارهای کاربردی Excel + گواهینامه در فرادرس

کلیک کنید

جستجوی دو طرفه

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

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

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

=XLOOKUP(H6,B3:E3,XLOOKUP(H5,A4:A8,B4:E8))

ابتدا تابع XLOOKUP داخلی، عبارت سلول H5 یعنی «شیشه» را در محدوده ستون A4 تا A8 پیدا می‌کند. سپس سطر متناظر آن در محدوده B4 تا E8 را برمی‌گرداند. نتیجه این عملیات آرایه اعداد شامل ۱۵، ۱۷، ۱۹ و ۲۲ است. در مرحله بعد تابع XLOOKUP دوم، مقدار نوشته شده در سلول H6 را در محدوده ستونی B3 تا E3 پیدا می‌کند. سپس از آرایه اعداد به‌دست آمده در قبل مقدار متناظر با گروه «ب» و جنس «شیشه» یعنی عدد ۱۷ را برمی‌گرداند.

جستجو با استفاده از عبارت‌های باقاعده

«عبارت‌های باقاعده یا منظم» (Regular Expression|Regex) در اکسل کاراکترهایی برای شناسایی یا جستجوی الگوهای خاص در رشته‌های متنی هستند. پیدا کردن کلمات مربوط به شماره تلفن‌ها در یک متن، جستجوی ایمیل‌هایی که ساختار اشتباه دارند یا جستجوی کلمات با یک حرف مشخص در ابتدای آن نمونه‌هایی از کاربردهای عبارت‌های باقاعده است. البته این کاراکترها فقط در نسخه اکسل ۳۶۵ وجود دارند، اما با توجه به کاربرد بسیار آن یک نمونه جستجوی کلمات با عبارت‌های منظم را همراه تابع XLOOKUP بررسی می‌کنیم.

فیلم پرکاربردترین توابع اکسل چیست؟ + معرفی و نحوه استفاده (فیلم آموزش رایگان) در فرادرس

کلیک کنید

به‌عنوان مثال، در جدول زیر می‌خواهیم قیمت صحیح محصول را با توجه به کد موجود در سلول F4 از بین کدهای ستون B پیدا کنیم. اما همان‌طور که مشخص است، سه حرف اول برخی از کدهای محصول حرف‌های بزرگ انگلیسی و سه حرف انتهایی آن‌ها نیز حروف بزرگ است. ضمن اینکه بین هر کد هم دو یا چند عدد وجود دارد. در این حالت استفاده از وایلدکارت‌ها به ما کمکی نمی‌کنند،‌ چون مثلا عدد ۵۶ در چند کد تکرار شده است و تطابق درست، سخت خواهد بود. اما عبارت‌های با قاعده این مشکل را حل می‌کنند.

برای این کار مراحل زیر را انجام می‌دهیم.

1. عدد ۳ را در آرگومان match_mode فرمول تابع XLOOKUP قرار می‌دهیم.
2. در آرگومان lookup_value یک الگوی باقاعده مانند "[A-Z]{3}56[A-Z]{2,3}") وارد می‌کنیم.

فرمول نهایی برای پیدا کردن کد به شکل زیر است.

=XLOOKUP("[A-Z]{3}"&F4&"[A-Z]{2,3}",B5:B16,C5:C16,,3)

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

• با سه حرف بزرگ انگلیسی شروع می‌‌شوند. (عبارت [A-Z]{3} در فرمول)
• با دو یا سه حرف بزرگ انگلیسی تمام می‌شوند. ( عبارت [A-Z]{2,3} در فرمول)
• عبارت &F4& در فرمول نشان‌دهنده مقدار سلول F4 است.

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

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

جستجوی چند شرطی ساده

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

XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (…), return_array)

در این فرمول نتیجه بررسی هر شرط به‌صورت مقادیر «True» یا «False» است. هنگام انجام عملیات ضرب، هر آرایه «True» با عدد یک و هر آرایه «False» با عدد صفر جایگزین می‌شود. بنابراین فقط در صورت برقراری همه شرط‌ها، تابع XLOOKUP آن ردیف را برای محاسبه در نظر می‌گیرد و مقدار مربوط به همان ردیف را به‌عنوان نتیجه نشان می‌دهد.

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

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

=XLOOKUP(1, (A2:A10=A13) * (B2:B10=B13) * (C2:C10=C13), D2:D10)

در این فرمول سه شرط تاریخ فروش، نام فروشنده و نوع کالا وجود دارد. مقادیر هر یک از این شرط‌ها به ترتیب در سلول‌های A2 تا A10، سلول‌های B2 تا B10 و سلول‌های C2 تا C10 از جدول قرار گرفته‌اند. اگر جستجو در این سلول‌ها با مقادیر موجود در سلول‌های G1، G2 و G3 منطبق باشد، خروجی فرمول در سلول D13 نمایش داده می‌شود.

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

=XLOOKUP(1, (A2:A10<=A13) * (B2:B10=B13) * (C2:C10=C13), D2:D10)

جستجوی چند شرطی پیچیده

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

1. عبارت ستون شماره حساب با حرف «x» شروع شود.
2. مقدار سلول منطقه بانکی کاملا برابر کلمه «East» باشد.
3. ماه افتتاح حساب «April» نباشد.

همان‌طور که می‌بینیم، شرط اول و سوم کمی پیچیده‌ هستند و در فرمول‌نویسی از تابع LEFT برای پیدا کردن شماره حساب‌های شروع شده با حرف «x» همراه با ترکیب تابع MONTH و تابع NOT برای حذف جستجوی ماه «April» استفاده می‌کنیم. در این صورت فرمول به‌صورت زیر در می‌آید.

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

همان‌طور که در تصویر زیر مشخص است، تابع XLOOKUP فقط در صورت صحیح بودن همه شرط‌ها مقدار متناظر را به‌عنوان نتیجه نشان می‌دهد.

جستجوی سریع دودویی

تابع XLOOKUP یک حالت جستجوی دودویی (Binary Search Mode) دارد که عملیات جستجو را سریع‌تر می‌کند. این نوع جستجو نیاز به دقت بالا در مرتب‌سازی داده‌ها دارد و قبل از هر کاری لازم است داده‌های جدول را حتما به ترتیب صعودی یا نزولی مرتب کنیم. در این حالت اگر داده‌ها را صعودی (کوچک به بزرگ) مرتب کرده باشیم، مقدار آرگومان search_mode را عدد ۲ و در حالت نزولی (مرتب سازی از بزرگ به کوچک) برابر عدد ۲- قرار می‌دهیم. بنابراین دو فرمول زیر را خواهیم داشت.

=XLOOKUP(A1,lookup_array,return_array,,0,2) // binary search A-Z =XLOOKUP(A1,lookup_array,return_array,,0,-2) // binary search Z-A

جستجوی حساس به حروف بزرگ و کوچک انگلیسی

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

XLOOKUP(TRUE, EXACT(lookup_value, lookup_array), return_array)

در این فرمول، ابتدا تابع EXACT عبارت جستجو را با هر یک از عبارت‌های موجود در محدوده جستجو، مقایسه می‌کند. سپس در صورت یکسان بودن حرف به حرف آن از نظر یکی بودن حروف بزرگ و کوچک، مقدار «TRUE» و در غیر این صورت مقدار «FALSE» را به‌عنوان نتیجه باز می‌گرداند. تابع XLOOKUP در صورت دریافت نتیجه «TRUE» مقدار متناظر آن را از جدول به‌عنوان نتیجه نهایی نشان می‌دهد.

به‌عنوان مثال برای جستجوی کالایی با نام نوشته شده در سلول E1 در محدوده سلول‌های B2 تا B7 فرمول زیر را می‌نویسیم.

=XLOOKUP(TRUE, EXACT(E2, A2:A7), B2:B7)

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

تعریف نوع نمایش جستجو با تابع XLOOKUP در اکسل

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

نمایش مشخصات جستجو در یک ردیف

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

=XLOOKUP(A12,A2:A9,B2:D9)

برای این کار کافی است فرمول را در سلول B12 وارد کنیم. بعد از زدن دکمه اینتر تابع XLOOKUP ابتدا کد ۸۴۱ را در کلیه سطر و ستون‌های جدول جستجو می‌کند و بعد از پیدا کردن آن، به‌صورت خودکار عبارت‌های متناظر با آن را به ترتیب در سلول‌های D12 ،C12 ،A12 وارد می‌کند.

نمایش جستجو در یک ستون

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

=TRANSPOSE(XLOOKUP(B11,A2:A9,B2:D9))

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

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

فیلم نمایش عمودی و افقی داده ها در اکسل با تابع TRANSPOSE + مثال کاربردی (رایگان) در فرادرس

کلیک کنید

نمایش مقادیر سلول‌های دلخواه

بعضی مواقع می‌خواهیم فقط نتیجه چند سلول را بعد از جستجوی عبارت دلخواه نشان دهیم. بر خلاف دو فرمول قبلی، در این نوع نمایش لازم است با قرار دادن تابع FILTER در آرگومان return_array فرمول را تغییر دهیم. در این آرگومان با توجه به تعداد سلول‌ها، آرایه‌ای عددی را تعریف می‌کنیم که در آن سلول‌های مورد نظر برای نمایش در نتیجه نهایی با عدد یک و بقیه با عدد صفر مشخص هستند.

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

=XLOOKUP(A12,A2:A9, FILTER(B2:D9, {0,1,1}))

در مطلب زیر نحوه فرمول‌نویسی با تابع FILTER را توضیح داده‌ایم.

نمایش کامل مشخصات یک ستون

در این حالت کل ستون داده مرتبط با مقدار جستجو به عنوان نتیجه نمایش داده می‌شود. به‌عنوان مثال در جدول قبلی اگر بخواهیم همه مقادیر مربوط به ستون «نام خانوادگی» را نمایش دهیم، مقدار سلولF1 را به‌عنوان آرگومان lookup_value ، محدوده سلول‌های A1 تا D1 یا تیترهای اصلی جدول را به‌عنوان آرگومان lookup_array و محدوده سلول‌های A2 تا D9 یا کل داده‌های جدول را به عنوان آرگومان return_array تعریف می‌کنیم. در این صورت فرمول به شکل زیر در می‌آید.

=XLOOKUP(F1, A1:D1, A2:D9)

نمایش هم‌زمان مشخصات چند ستون

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

به‌عنوان مثال در جدول داده‌های قبلی برای نمایش مشخصات ستون‌های مربوط به سه کد شناسایی ۸۴۱، ۸۸۶ و ۶۲۲ فرمول زیر را می‌نویسیم.

=CHOOSEROWS(B2:D9, XMATCH(A12:A14, A2:A9))

در مطلب زیر به‌طور کامل تابع XMATCH را توضیح داده‌ایم.

پیام‌های خطا هنگام کار با تابع XLOOKUP

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

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

نتیجه اشتباه

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

پیام خطای NA#

این پیام نشان می‌دهد که تابع XLOOKUP مقدار یا عبارت مورد نظر برای جستجو را پیدا نکرده است. اگر مطمئن هستیم که حداقل یک عبارت در جدول وجود دارد، برای رفع خطا و پیدا کردن مشکل فرمول‌نویسی را به شکل تطابق تقریبی عبارت تغییر می‌دهیم. در غیر این صورت بهتر است برای رفع سردرگمی، عبارت دلخواهی مانند «مقداری پیدا نشد» یا « No match is found» به عنوان آخرین آرگومان در فرمول اضافه کنیم. به‌عنوان مثال نمونه فرمول در یک جدول فرضی را می‌توانیم به شکل زیر تعریف کنیم.

=XLOOKUP(E1, A2:A6, B2:B6, "No match is found")

به این شکل اگر تابع مقداری را در جدول پیدا نکند، به‌جای پیام خطای #NA این عبارت‌ را نشان می‌دهد.

خطای VALUE#

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

خطای REF#

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

تفاوت تابع XLOOKUP با تابع VLOOKUP در اکسل

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

۱. جستجوی عمودی و افقی

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

۲. جستجو در جهت‌های مختلف

با تابع VLOOKUP فقط می‌توانیم جستجو را در ستون سمت راست مقدار مورد نظر انجام دهیم. در حالی‌که تابع XLOOKUP محدودیتی در جهت جستجو ندارد.

۳. جستجوی دقیق به‌صورت پیش‌فرض

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

۴. جستجوی بخشی از عبارت

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

۵. جستجو از انتها به ابتدا

در سایر توابع برای پیدا کردن آخرین مقدار منطبق با مقدار مورد نظر لازم است ترتیب داده‌های جدول را معکوس کنیم. اما در تابع XLOOKUP با تنظیم آرگومان search_mode روی عدد ۱- جستجو از قسمت انتهایی انجام می‌گیرد.

۶. بازگرداندن چند مقدار

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

۷. تعریف چند شرط

از آنجا که تابع XLOOKUP به‌صورت پیش‌فرض از آرایه‌ها پشتیبانی می‌کند، انجام جستجوی شرطی با آن بسیار ساده است.

۸. مدیریت خطا

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

۹. به‌هم نریختن فرمول بعد از حذف یا اضافه کردن ستون‌

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

۱۰. عملکرد بهتر

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

جمع‌بندی پایانی

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