هدف آموزش هدف آموزش
تابع XLookup در اکسل به صورت پیشرفته با کاربردهای شگفت انگیز و کاربردی آن
در ویدیوی زیر به طور کامل تابع XLookup در اکسل به صورت پیشرفته با کاربردهای شگفت انگیز آن آموزش داده شده که می توانید نکات مهم و کاربردی آن را بیاموزید. همچنین توصیه می کنیم متن صفحه را با دقت مطالعه و نکات ارزشمند آن را بیاموزید.
هر زمان که بخواهید در اکسل جستجو کنید، از کدام تابع استفاده می کنید؟ آیا این یک VLOOKUP است یا مدل افقی آن HLOOKUP ؟
در یک مورد پیچیده تر، آیا به ترکیب متعارف INDEX MATCH تکیه می کنید یا کار را به Power Query منتقل می کنید؟ خبر خوب این است که شما دیگر انتخابی ندارید . همه این روش ها راه را برای جانشین قدرتمندتر و همه کاره تر، تابع XLOOKUP در اکسل باز می کنند.
چگونه XLOOKUP در اکسل بهتر است؟ از بسیاری جهات! می تواند به صورت عمودی و افقی، به سمت چپ و بالا نگاه کند، با چندین شرط جستجو کند، و حتی یک ستون یا ردیف کامل از داده ها، نه فقط یک مقدار را برگرداند.
مایکروسافت بیش از ۳ دهه طول کشیده است، اما در نهایت آنها موفق به طراحی یک عملکرد قوی شده اند که بر بسیاری از خطاها و ضعف های ناامید کننده VLOOKUP غلبه می کند.
اما نکته خیلی مهمی هست که فعلا: تابع XLOOKUP در اکسل فقط برای Microsoft365 ، Excel 2021 به بعد و اکسل برای وب در دسترس است. مهم است که توجه داشته باشید که XLOOKUP سازگار با نسخه های قبل نیست، به این معنی که در نسخه های قبلی کار نخواهد کرد.
تابع XLOOKUP در اکسل یک محدوده یا یک آرایه را برای یک مقدار مشخص جستجو می کند و مقدار مربوطه را از ستون دیگری برمی گرداند. می‌تواند هم به صورت عمودی و هم به صورت افقی جستجو کند و یک تطابق دقیق (پیش‌فرض)، تقریبی (نزدیک‌ترین) یا تطابق عام (جزئی) انجام دهد.
مدل کلی استفاده از تابع XLOOKUP به شرح زیر است:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
3 آرگومان اول مورد نیاز و سه آرگومان آخر اختیاری هستند.
Lookup_value – مقداری که باید جستجو شود.
Lookup_array – محدوده یا آرایه ای که در آن جستجو می شود.
Return_array – محدوده یا آرایه ای که از آن مقادیر برمی گردند.
If_not_found [اختیاری] – مقداری که در صورت پیدا نشدن مطابقت باید برگردانده شود. اگر حذف شود، یک خطای #N/A برگردانده می شود.
Match_mode [اختیاری] – نوع مطابقت برای انجام:
۰ یا حذف شده (پیش فرض) – مطابقت دقیق. اگر پیدا نشد، یک خطای #N/A برگردانده می شود.
منفی۱- مطابقت دقیق یا بعدی کوچکتر. اگر مطابقت دقیق پیدا نشد، مقدار کوچکتر بعدی برگردانده می شود.
۱ – مطابقت دقیق یا بزرگتر بعدی. اگر مطابقت دقیق پیدا نشد، مقدار بزرگتر بعدی برگردانده می شود.
۲ – تطبیق کاراکترهای عام.
Search_mode [اختیاری] – جهت جستجو:
۱ یا حذف شده (پیش فرض) – برای جستجو از اول تا آخر.
منفی۱ – برای جستجو به ترتیب معکوس، از آخرین به اول.
۲ – جستجوی دودویی بر روی داده های مرتب شده صعودی.
منفی۲ – جستجوی دودویی بر روی داده ها مرتب شده به صورت نزولی.
جستجوی باینری یا دودویی:
به گفته مایکروسافت، جستجوی باینری برای کاربران پیشرفته گنجانده شده است. این یک الگوریتم ویژه است که موقعیت یک “مقدار جستجو” را در یک آرایه یا محدوده مرتب شده با مقایسه آن با عنصر میانی محدوده پیدا می کند. جستجوی دودویی بسیار سریعتر از جستجوی معمولی است، اما فقط روی داده های مرتب شده به درستی کار می کند.
شکل ساده استفاده از تابع XLookup در اکسل
برای به دست آوردن درک مفهومی تر ، بیایید یک فرمول XLOOKUP در ساده ترین شکل آن برای انجام جستجوی دقیق ایجاد کنیم. برای این کار فقط به ۳ آرگومان اول نیاز داریم.
فرض کنید، شما یک جدول خلاصه با اطلاعاتی در مورد کدهای تدارکاتی و نام قطعه کالاها دارید. شما می خواهید با ورود کد تدارکاتی خاص را در i7 بدست آورید (lookup_value). با ستون نام کد تدارکاتی در B2:B13 (lookup_array) و نام قطعات در D2:D13 (return_array)، فرمول به شرح زیر است:
=XLOOKUP(I7, B2:B13, D2:D13,”پیدا نشد”)
مقدار i7 را در B2:B13 جستجو کنید و مقداری از D2:D13 را در همان ردیف برگردانید.
نکته: تابع ToCol ، باعث می شود در حالی که از جدول استفاده نمی کنیم ، محدوده ای از سلول ها را به عنوان داینامیک در نظر بگیرد که بتوان در انتهای ستون ، مقادیر را اضافه کرد.
=XLOOKUP(I7, ToCol(B2:B40), ToCol(D2:D40),”پیدا نشد”)
ویژگی های قدرتمند تابع XLookup در اکسل که از VLookup بسیار بهتر هست:
XLOOKUP یک تابع قدرتمند است که در بسیاری از جنبه ها از VLOOKUP پیشی می گیرد. قابلیت ها، تطبیق پذیری و کارایی بیشتری را ارائه می دهد. در اینجا تعداد ویژگی برتر وجود دارد که آن را به عنوان بهترین تابع جستجوی نهایی در اکسل تبدیل می کند:
- جستجوی عمودی و افقی تابع XLOOKUP که نام خود را به دلیل توانایی آن در جستجوی عمودی و افقی دریافت کرده است.
- به هر جهتی نگاه کنید تابع XLookup جستجو را انجام می دهد. راست، چپ، پایین یا بالا. در حالی که VLOOKUP فقط می تواند در سمت چپ ترین ستون و HLOOKUP در بالاترین ردیف جستجو کند، XLOOKUP چنین محدودیتی ندارد.
- تطابق جزئی با حروف عام(* و ؟): هنگامی که شما فقط بخشی از مقدار جستجو را می دانید، نه همه آن را، که می توان از تابع XLookup استفاده نمود.
- به ترتیب معکوس جستجو کنید. یعنی از آخر به اول. پیش از این، برای دریافت آخرین مقدار، باید ترتیب داده‌های منبع خود را برعکس می‌کردید. اکنون، شما به سادگی آرگومان search_mode را روی منفی ۱ قرار دهید تا فرمول Xlookup خود را مجبور کنید از آخرین سطر جستجو کند و آخرین مطابقت را برگرداند.
- چندین مقدار را برگردانید. با دستکاری آرگومان return_array، می توانید یک سطر یا ستون کامل از داده های مربوط به مقدار جستجوی خود را در نتیجه نمایش دهید.
- جستجو با شرط های متعدد : XLOOKUP انجام جستجو با شرطهای متعدد را ممکن می سازد.
- XLOOKUP این قابلیت را در آرگومان if_not_found گنجانده است که به شما امکان می دهد اگر مطابقت معتبری پیدا نشد، متن “عدم پیدا شدن” را خروجی بگیرید.
- درج/حذف ستون: یکی از آزاردهنده‌ترین مسائل VLOOKUP این است که اضافه کردن یا حذف ستون‌ها ، یک فرمول را تخریب می کند زیرا ستون بازگشتی با شماره آن مشخص می‌شود.
با XLOOKUP، شما محدوده بازگشتی را تعیین می کنیم ، نه یک عدد را، به این معنی که می توانید هر تعداد ستون را که نیاز دارید وارد و حذف کنید.
- عملکرد بهتر تابع XLookup در اکسل: VLOOKUP می تواند شیت های ما را کم سرعت کند زیرا کل جدول را در محاسبات شامل می شود که منجر به پردازش سلول های بسیار بیشتر از آنچه واقعاً نیاز است می شود. XLOOKUP تنها محدوده های جستجو و خروجی را که واقعاً به آنها وابسته است، کنترل می‌کند.
نکته مهم: تابع XLookup در اکسل انعطاف بسیار بالایی داشته باشد و اجازه می دهد تا ستون های جستجو و خروجی به طور جداگانه مشخص شوند. این جداسازی انعطاف پذیری استثنایی را ایجاد می کند . ستون جستجو را می توان در سمت چپ یا راست ستون مقدار بازگشتی قرار داد.
مثال‌های زیر مفیدترین ویژگی‌های تابع XLOOKUP در اکسل را در عمل نشان می‌دهند. علاوه بر این، چند کاربرد شگفت انگیز دیگر خواهید دید که مهارت های جستجوی اکسل شما را به سطح جدیدی می برد.
مثال دوم : نمایش همه ستون ها در خروجی تابع XLookup
در این مثال نام خریدار را جستجو کرده و براساس آن ، اطلاعات سطر پیدا شده را نمایش می دهیم.
حال همانطور که در تصویر مشخص هست ، هم اطلاعات سطر نتیجه یا خروجی را به صورت ردیفی و هم به صورت زیر هم نمایش داده ایم.
فرمول های نوشته شده با استفاده از تابع XLookup در اکسل به صورت زیر می باشد:
=XLOOKUP(G1,A5:A12,A5:D12, (“پیدا نشد“)
=TRANSPOSE(XLOOKUP(G1,A5:A12,A5:D12,”پیدا نشد“)
مثال سوم: تابع XLOOKUP در اکسل با تطابق دقیق و تقریبی
مطابقت جستجو توسط آرگومان پنجم به نام match_mode کنترل می شود. به طور پیش فرض، یک تطابق دقیق یا Exact Match انجام می شود.
لطفاً توجه داشته باشید که حتی وقتی یک تطابق تقریبی را انتخاب می‌کنید (پارامتر مطابقت روی ۱ یا منفی ۱ تنظیم شده است)، این تابع همچنان ابتدا یک مطابقت دقیق را جستجو می‌کند. تفاوت در این است که اگر مقدار جستجوی دقیقی پیدا نشود، چه چیزی را برمی گرداند.
آرگومان Match_mode:
0 یا حذف شده : مطابقت دقیق. اگر پیدا نشد – خطای #N/A.
منفی ۱ : مطابقت دقیق؛ اگر پیدا نشد – مورد کوچکتر بعدی.
یک یا ۱ : مطابقت دقیق: اگر پیدا نشد – مورد بزرگتر بعدی.
مطابقت دقیق تابع XLOOKUP در اکسل:
این گزینه ای است که احتمالاً ۹۹٪ مواقعی که در اکسل جستجو می کنید از آن استفاده می کنید. از آنجایی که تطابق دقیق، رفتار پیش‌فرض XLOOKUP است، می‌توانید match_mode را حذف کنید و تنها ۳ آرگومان مورد نیاز اول را ارائه کنید.
با این حال، در برخی شرایط، “تطابق دقیق” کار نخواهد کرد. یک حالت زمانی است که جدول جستجوی شما حاوی تمام مقادیر نیست، بلکه حاوی «مقادیر عطف» یا «محدوده‌هایی» هست.
در مثال زیر و تصویر زیر این مورد را کاملا بررسی کرده ایم.
جدول منبع ما شامل دو ستون نمره و گرید است. که شامل مقادیر محدوده ای است و اکثر موارد مطابقت دقیق در آن وجود ندارد.
حال می خواهیم برای همه اشخاص ، گرید آن را از روی جدول منبع بدست بیاوریم.
با توجه به نمره هر شخص ، گرید آن را با تابع XLookup فرمول نویسی کرده ایم.
و با توجه به پارامتر آخر که -۱ در نظر گرفته شده ، و مقدار نمره هر شخص ، نزدیک ترین و کمترین نمره از جدول اصلی جستجو و گرید آن استخراج می شود.
=XLOOKUP(F2,$A$2:$A$7,$B$2:$B$7,-پیدا نشد“,۱”)
مثال چهارم: تابع XLookup در اکسل با کاراکترهای خاص * و ؟
برای انجام جستجوی با استفاده از کاراکترهای خاص ، آرگومان match_mode را روی ۲ تنظیم کنید، که به تابع XLOOKUP دستور می‌دهد تا کاراکترهای عام ( * و ؟ ) را پردازش کند:
یک ستاره (*) – هر دنباله ای از کاراکترها را نشان می دهد.
علامت سوال (؟) – نشان دهنده یک کاراکتر می باشد.
برای اینکه ببینید چگونه کار می کند، لطفاً مثال زیر را در نظر بگیرید. در ستون C، تعدادی متقاضی داریم و در ستون B ، نام مراکز آنها می باشد. می خواهیم با وارد کردن بخشی از نام متقاضی ، به صورت اتومات تابع XLookup ، نام مرکز را تشخیص داده و نمایش دهد.
در این مثال ، از کاراکترهای خاص مثل * و ? در دستور XLookup استفاده می کنیم. نکته مهم این هست که پارامتر Match Mode را باید حتما روی ۲ قرار دهیم.
خروجی تابع ، را طوری تنظیم کرده ایم که یکبار فقط نام مرکز را در خروجی نمایش دهد و بار دیگر طوری تنظیم کرده ایم که کل محدوده سطر را در نتیجه نمایش دهد.
=XLOOKUP(“*” &F2& “*”,Table1[نام متقاضی],Table1[نام مرکز] ,”پیدا نشد“)
=XLOOKUP(“*” &F2& “*”,Table1[نام متقاضی],Table1,”پیدا نشد“)
مثال پنجم: XLOOKUP در اکسل و جستجو به ترتیب معکوس از آخر به اول برای دریافت آخرین مورد
در صورتی که جدول شما حاوی چندین مورد از مقدار جستجو باشد، ممکن است گاهی لازم باشد آخرین مورد پیدا شده یا مطابقت را برگردانید. برای انجام آن، فرمول Xlookup خود را برای جستجو به ترتیب معکوس پیکربندی کنید.
جهت جستجو با آرگومان ۶ با نام search_mode کنترل می شود:
1 (پیش‌فرض) : جستجو از اولین تا آخرین مقدار، یعنی از بالا به پایین .
منفی ۱ : به ترتیب معکوس از آخرین تا اولین مقدار جستجو می کند.
به عنوان مثال، اجازه دهید آخرین فروش انجام شده توسط یک فروشنده خاص را برگردانیم. برای این کار، سه آرگومان مورد نیاز اول (H2 برای lookup_value، A2:A14 برای lookup_array و B2:F14 برای return_array) را کنار هم قرار داده و منفی ۱ را در آرگومان پنجم قرار می دهیم:
=TRANSPOSE(XLOOKUP(H2,A2:A14,B2:F14,پیدا نشد”, ,-۱″)
مثال ششم: XLOOKUP در اکسل با شرط های متعدد
یکی دیگر از مزایای بزرگ XLOOKUP این است که محدوده ها را به صورت ستونی یا بخش بخش شده مدیریت می کند. با توجه به این توانایی، می توانید چندین شرط را مستقیماً در آرگومان lookup_array استفاده کنید:
این فرمول چگونه کار می کند: نتیجه هر شرط ، آرایه ای از مقادیر TRUE و FALSE است. ضرب آرایه ها TRUE و FALSE را به ترتیب به ۱ و ۰ تبدیل می کند و آرایه جستجوی نهایی را تولید می کند. همانطور که می دانید، ضرب در ۰ همیشه صفر را به دست می دهد، بنابراین در آرایه جستجو، فقط مواردی که همه شرط ها را دارند با ۱ نشان داده می شوند. و چون مقدار جستجوی ما “۱” است، اکسل عدد “۱” را در lookup_array به عنوان اولین پارامتر می گیرد ( first match) و مقدار را از return_array در همان موقعیت برمی گرداند.
=XLOOKUP(1,(A2:A6=J2)*(B2:B6=J3)*(C2:C6=J4),F2:F6,”پیدا نشد“)
مثال هفتم: XLOOKUP در اکسل به صورت دوتایی / تو در تو
برای یافتن یک مقدار در تقاطع یک سطر و ستون خاص، به اصطلاح جستجوی دوگانه یا جستجوی ماتریسی را انجام دهید. XLOOKUPدر اکسل نیز می تواند این کار را انجام دهد. شما به سادگی یک تابع را در یک تابع دیگر قرار می دهید:
در این مثال ، با استفاده از تابع XLookup بیرونی ، سال مورد نظر را پیدا کرده و سپس با تابع داخلی ، ارزیابی مورد نظر را جستجو و تقاطع این دو سطر و ستون ، به عنوان مقدار خروجی در نظر گرفته می شود.
برای این کار، مقادیر جستجو را در G1 (سال) و G2 (نوع ارزیابی) وارد می کنیم و Xlookup دو طرفه را با فرمول زیر انجام می دهیم:
=XLOOKUP(G1,A2:A8,XLOOKUP(G2,B1:D1,B2:D8))
این فرمول چگونه کار می کند: این فرمول بر اساس توانایی تابع XLOOKUP برای برگرداندن یک سطر یا ستون می باشد. تابع داخلی ، مقدار جستجوی خود را بررسی می کند و یک ستون یا ردیف از داده های مرتبط را برمی گرداند. آن محدوده به تابع خارجی به عنوان آرایه بازگشتی می رود.
همچنین در صورت مفید بودن آموزش بالا ، آموزش تابع Filter در اکسل با عملکرد شگفت انگیز و پیشرفته با مثال های مهم را می توانید در زیر مشاهده نمایید. این آموزش خارق العاده شیوه فیلتر کردن داده ها را با فرمول نویسی به صورت پیشرفته می آموزید.
اگر آموزش بالا برای شما مفید و موثر بوده ، توصیه می کنیم آموزش جداسازی کلمات یک متن در اکسل به صورت شگفت انگیز و پیشرفته با تابع TextSplit را با مثال های متعدد و کاربردی در زیر مشاهده نمایید.
همچنین توصیه می کنیم آموزش زیر هم قرار دادن کلمات یک متن در اکسل در یک سلول با استفاده از توابع مهم TextJoin و TextSplit را در زیر مشاهده نمایید.
تهیه مجموعه کامل و تخصصی اکسل سایت با ۷۰ درصد تخفیف به مدت محدود(کلیک نمایید)
