تابع VLOOKUP در لیبره آفیس کالک
VLOOKUP نوعی تابع جستجو در لیبره آفیس کالک یا هر برنامه صفحه گسترده محبوب مانند مایکروسافت اکسل است. این مخفف عبارت vertical lookup است و به صورت عمودی جستجو میکند، یعنی از بالا به پایین در جدول، از این رو نام آن است.
به طور خلاصه، این تابع کلید شما را در یک لیست/جدول جستجو میکند و یک مقدار مرتبط با آن مقدار مطابق را برمیگرداند.
به عنوان مثال، من مجموعه دادههای زیر را از کارکنان یک سازمان دارم. چگونه میتوانم آدرس ایمیل یک شناسه خاص را در این جدول پیدا کنم؟
سینتکس
=VLOOKUP(search_key, table_array, column number of table array to be returned, search_type)
search_key: مقداری که میخواهید جستجو کنید
table_array: جایی که میخواهید جستجو کنید
column number: اگر پیدا شد کدام ستون از مقدار را میخواهید برگردانید
search_type: مقدار 0=تطابق دقیق. مقدار 1=تطابق تقریبی; پیشفرض 1 است (تقریباً مطابقت).
مثال
در مثال بالا، اگر بخواهم آدرس ایمیل Adrian را که شناسه او 572 است، پیدا کنم، باید از فرمول زیر استفاده کنم:
=VLOOKUP(C6,B11:F16,4,0)
توضیح: فرمول بالا C6، یعنی 572 را در محدوده B11:F16 جستجو میکند و ستون شماره 4 را از محدوده برمیگرداند. نوع تطبیق مطابقت دقیق است. بنابراین مقدار آن صفر است.
مرجع مطلق و نسبی در VLOOKUP
در مثال بالا، میتوانید ببینید که مرجع محدوده به صورت B11:F16 استفاده شده است. به این یک مرجع نسبی میگویند.
اگر مجموعهای از مقادیر را با مرجع نسبی جستجو کنید و دسته سلول را بکشید تا بقیه ردیفها پر شود، B11:F16 افزایش مییابد. یعنی جستجوی زیر با B12:F17 اتفاق میافتد.
اگر table_array برای هر مقدار کلید تغییر کند، خوب نیست.
بنابراین، همیشه از مرجع مطلق با علامت $ استفاده کنید یا برای تبدیل آن، F4 را فشار دهید. در نهایت، فرمول مناسب این میشود.
=VLOOKUP(C6,$B$11:$F$16,4,0)
VLOOKUP فقط سمت راست را بررسی میکند
این تابع همیشه به سمت راست جستجو میکند و از بالا شروع میشود. بنابراین، شما باید این را در هنگام آمادهسازی دادههای خود یا نوشتن تابع در نظر داشته باشید. در اینجا نحوه تجسم گردش کار کلی VLOOKUP آورده شده است.
کلید همیشه باید اولین ستون آرایه جدول باشد
یکی از جوانب مهم VLOOKUP، زمینه کلیدی است. ستون اول جدول جستجو (table_array) همیشه باید حاوی مقادیر فیلد کلید شما باشد.
برای مثال بالا، اگر قسمت ID شما بعد از ایمیل یا نام ظاهر شود، VLOOKUP کار نخواهد کرد.
مطابقت تقریبی و دقیق
چهارمین پارامتر VLOOKUP، wither TRUE یا FALSE است. این نشاندهنده موارد زیر است.
- 0 یا FALSE: مطابقت دقیق، یعنی کل رشته باید مطابقت داشته باشد و مقادیر برگردانده شوند.
- 1 یا TRUE: مطابقت تقریبی
بیایید مثال زیر را در نظر بگیریم، جایی که من به دنبال درصد کمیسیون برای طیف وسیعی از ارقام فروش هستم.
اگر بخواهم کمیسیون فروش 550 دلار را بدانم؟ چون 550 تو جدول نیست!
در اینجا، شما به تطابق تقریبی، یعنی آرگومان نهایی به عنوان 1 نیاز دارید.
بنابراین، اگر فرمول زیر را قرار دهم، میتوانم کمیسیون 11٪ را برای 550 دلار دریافت کنم.
=VLOOKUP(C6,C11:D16,2,1)
توضیح: VLOOKUP سعی میکند بفهمد که آیا مطابقت دقیقی برای 550 وجود دارد یا خیر. و در جدول وجود ندارد. در مرحله بعد سعی می کند یک قدم به عقب برگردد و نزدیکترین مطابقت را که 544 است برمیگرداند و در نهایت 11% را برمیگرداند.
در اینجا چند نکته وجود دارد که باید در اینجا به خاطر بسپارید:
- هنگام استفاده از یک تطابق تقریبی، مطمئن شوید که ستون کلید table_array مرتب شده است.
- ترتیب مرتبسازی باید صعودی باشد.
- اگر مرتب نشده باشد، VLOOKUP ممکن است یک خطای #N/A یا نتیجه نادرست به شما بدهد.
- این پارامتر اختیاری است. اگر این مورد را در VLOOKUP حذف کنید، پیشفرض به عنوان 1 در نظر گرفته میشود، یعنی مطابقت تقریبی.
بنابراین، از این تطابق تقریبی با احتیاط استفاده کنید.
VLOOKUP فقط مطابقت اول را برمیگرداند
همانطور که در مثال بالا میبینید، زمانی که دو مقدار از تطابق تقریبی وجود داشته باشد، فقط اولین مورد برگردانده میشود.
این همچنین در مورد تطابق دقیق نیز صدق میکند.
در مثال اول، اگر نام خانوادگی Steve را به Patel تغییر دهم و دقیقاً مطابقت را امتحان کنیم، VLOOKUP چه چیزی را باید برگرداند؟
=VLOOKUP(C6,$D$11:$E$16,2,0)
همانطور که میبینید، دو همخوانی وجود دارد که در زیر مشخص شده است. در این سناریو، VLOOKUP اولین تطابق دقیق را برمیگرداند.
مطابقت Wildcard در VLOOKUP
VLOOKUP در لیبره آفیس کالک همچنین امکان جستجوی wildcard با “*” را فراهم می کند. شما می توانید “*” را در search_key مشخص کنید. هنگامی که از یک wildcard استفاده می کنید، به یاد داشته باشید که همیشه باید از نوع تطبیق دقیق، یعنی FALSE، به عنوان آرگومان نهایی استفاده کنید.
=VLOOKUP("Pate" & "*",$D$11:$E$16,2,0)
بنابراین، اگر از فرمول بالا استفاده کنید، دقیقاً همان نتیجه مثال قبلی را برمیگرداند.
خطاهای VLOOKUP و #N/A در لیبره آفیس کالک
اگر از این تابع استفاده کنید، مطمئناً در نتیجه VLOOKUP با خطای #N/A مواجه خواهید شد. #N/A به این معنی است که مطابقت پیدا نشد.
اگر نمیتوانید خطای #N/A را حل کنید، در اینجا برخی از دلایلی وجود دارد که باید بدانید.
- مقدار search_key یا Look-up در جدول (ستون اول table_array) وجود ندارد.
- ممکن است search_key اشتباه املایی داشته باشد یا ممکن است حاوی فضای اضافی باشد. ممکن است بخواهید با استفاده از تابع trim فضا را حذف کنید.
- با دقت search_type را بررسی کنید تا ببینید آیا دقیق یا تقریبی را مشخص کردهاید.
- table_array به درستی ذکر نشده است. مراقب باشید که آیا با مرجع نسبی و مرجع مطلق قفل شده است.
دیدگاهتان را بنویسید