Vlookup من را از محدودیت های سفت و سخت و نحو کاملاً ناخوشایند خود ناامید کرده است. اما ، نه دیگر. من یک fucntion excel پیدا کردم که از هر جهت به جستجوی می پردازد و کنترل تطبیق دقیقی را ارائه می دهد.
Vlookup من را از محدودیت های سفت و سخت و نحو کاملاً ناخوشایند خود ناامید کرده است. اما ، نه دیگر. من یک fucntion excel پیدا کردم که از هر جهت به جستجوی می پردازد و کنترل تطبیق دقیقی را ارائه می دهد.
Xmatch در هر جهت که می خواهید کار می کند
بزرگترین ناامیدی از Vlookup نیاز سرسخت چپ به راست آن است. اگر ستون جستجوی شما سمت چپ در محدوده شما نیست ، گیر کرده اید. شما باید داده های خود را دوباره تنظیم کنید یا یک راه حل پیدا کنید. این می تواند فقط برای خوشحال کردن Vlookup ، صفحات گسترده را برای بازسازی صفحات پخش کند.
عملکرد Xmatch اکسل این سردرد را از بین می برد. بر خلاف ساختار سفت و سخت Vlookup ، Xmatch هر آرایه ای را از هر جهت جستجو می کند. می توانید داده ها را در سمت چپ ستون جستجوی خود پیدا کنید. همچنین می توانید به صورت عمودی در یک ستون یا به صورت افقی در یک ردیف جستجو کنید. Xmatch هر دو را با زحمت کنترل می کند.
در اینجا نحو اساسی وجود دارد:
= Xmatch (lookup_value ، lookup_array ، [match_mode] ، [search_mode])
بیایید این پارامترها را تجزیه کنیم:
- lookup_value: آنچه در جستجوی آن هستید.
- lookup_array: دامنه ای که در آن جستجو می کنید.
- MATCH_MODE: مسابقه شما چقدر دقیق باید باشد (0 برای دقیق ، -1 برای کوچکترین یا کوچکترین بعدی ، 1 برای دقیق یا بعدی بعدی).
- SEARCH_MODE: کدام جهت برای جستجو (1 برای اولین بار تا آخرین ، -1 برای اولین بار تا اول ، 2 برای جستجوی باینری).
در اینجا یک مثال عملی از یک پایگاه داده کارمند آورده شده است. بگویید شما باید پیدا کنید که بخش “کریستن تیت” در کدام بخش کار می کند ، اما نام کارمندان در ستون D است در حالی که بخش ها در ستون B هستند.
اما می توانید از عملکرد XMATCH برای بازگشت موقعیت خود در ستون Name همانطور که در فرمول زیر نشان داده شده است استفاده کنید:
= xMatch (“کریستن تیت” ، D: D ، 0)
این گردش کار من را تغییر داد زیرا دیگر نیازی به شمارش ستون ها ندارم. با استفاده از Vlookup در صفحه گسترده اکسل ، شما دائماً برای تعیین شماره شاخص ستون حساب می کنید. اگر ستون جدیدی را به داده های خود اضافه کنید ، ناگهان فرمول های شما می شکنند زیرا شماره شاخص ها تغییر می کنند.
Xmatch به شما کنترل بهتری بر تطبیق می دهد
گزینه های تطبیق VLookup محدود به یک مسابقه دقیق یا یک مسابقه تقریبی است – این آن است. اگر داده های شما کاملاً تمیز نیست ، شما قبل از اینکه حتی بتوانید جستجوی خود را شروع کنید ، وقت خود را صرف تهیه یک برگه Excel کثیف می کنید.
Xmatch با پارامتر match_mode خود این کار را آسان می کند. درست مانند پارامتر دروغین Vlookup ، آن را برای مسابقات دقیق تنظیم کنید. اما اینجا جایی است که جالب می شود. می توانید از -1 برای یافتن مسابقه دقیق یا کوچکترین مقدار بعدی و 1 برای مسابقه دقیق یا بزرگترین مقدار بعدی استفاده کنید.
حقوق را در مجموعه داده های کارمند در نظر بگیرید. برای پیدا کردن کارمند که نزدیکترین درآمد به اما بیش از 75000 دلار نیست ، استفاده می کنید:
= XMATCH (75000 ، H: H ، -1)
این فرمول موقعیت بالاترین حقوق را از ستون H برمی گرداند که از ارزش هدف شما تجاوز نمی کند – چیزی که Vlookup با آن تلاش می کند مگر اینکه داده های شما کاملاً مرتب شده باشد.
پارامتر Search_Mode لایه دیگری از کنترل را اضافه می کند. در حالی که 1 از اول تا آخر (پیش فرض) جستجو می کند ، -1 از آخرین تا اول جستجو می کند. این پارامتر وقتی مقادیر تکراری دارید و به جدیدترین ورودی نیاز دارید.
به عنوان مثال ، اگر “جان اسمیت” چندین بار در ستون D یک مجموعه داده ظاهر شود ، می توانیم از فرمول زیر برای یافتن آخرین وقوع وی استفاده کنیم.
= Xmatch (“جان اسمیت” ، D: D ، 0 ، -1)
درک این پارامترها به شما کمک می کند تا به طور مؤثرتری به جستجوی داده ها نزدیک شوید. این سطح از کنترل به معنی ستون های کمکی کمتر و دستکاری داده های کمتر است. فرمول های شما قوی تر می شوند و صفحات گسترده شما تمیز تر می شوند.
جفت Xmatch کاملاً با فهرست
Xmatch هنگام ترکیب آن با عملکرد شاخص ، مفیدتر است. در حالی که Xmatch موقعیت را پیدا می کند ، Index مقدار واقعی را از آن موقعیت بازیابی می کند. این یکی از کارکردهای مفید اکسل برای یافتن سریع داده ها است ، اما در کنار هم ، آنها ترکیبی از جستجوی انعطاف پذیر تر ایجاد می کنند.
در اینجا نحو اساسی هنگام ترکیب هر دو وجود دارد:
= index (Return_array ، Xmatch (lookup_value ، lookup_array ، [match_mode]))
این جفت شدن کابوس شمارش ستون Vlookup را از بین می برد. به جای یادآوری این حقوق ، ستون هشتم است ، شما به سادگی ستون حقوق را مستقیماً مشخص می کنید. از این رو ، هنگام اضافه کردن یا حذف ستون ها ، فرمول های شکسته را دریافت نمی کنید.
بیایید بگوییم که شما باید بخش کریستن تیت را از داده های کارمند با Index و Xmatch پیدا کنید:
= فهرست (R: R ، Xmatch (“Kristen Tate” ، D: D ، 0)
فرمول فوق به طور طبیعی خوانده می شود و یک مقدار از ستون R را در موقعیتی که “کریستن تیت” در ستون D ظاهر می شود ، برمی گرداند.
این ترکیب همچنین از جستجوی پیچیده برخوردار است ، مانند زمانی که به حقوق کارمند در بخش فروش با بالاترین شناسه کارمند نیاز دارید:
= فهرست (A: A ، XMATCH (حداکثر (if (r: r = “فروش” ، h: h)) ، if (r: r = “sales” ، h: h) ، 0))
این فرمول آرایه حداکثر شناسه کارمند را در فروش می یابد ، سپس حقوق آن شخص را برمی گرداند. اگر می خواهید این کار را با VLookup امتحان کنید ، به چندین ستون یاور و سایر راه حل ها نیاز دارید.
Xmatch کاملاً جایگزین VLOOKUP در گردش کار من شده است. انعطاف پذیری جهت دار ، کنترل دقیق تطبیق و ادغام آسان شاخص ، آن را به عملکرد جستجوی مورد نیاز من تبدیل می کند. هنگامی که این سطح کنترل را تجربه کردید ، بازگشت به Vlookup عملی نیست.