یکی از توابع مفید اکسل برای جستجو کردن در جدوال، VLookup نام دارد. معمولاً در جستجو کردن در بانک داده یا به زبان دیگر دیتابیس بزرگ از این دستور استفاده می‌شود اما استفاده از آن در ساخت فرم‌های ساده که به جدول‌های نسبتاً کوچک مربوط می‌شوند هم امور را ساده می‌کند. با استفاده از VLookup می‌توانید مشخصه‌های یک محصول، شخص یا هر موضوع دیگری را که در ستون‌های مختلف یک جدول ذکر شده، به راحتی پیدا کنید.
در ادامه به معرفی VLookup و روش استفاده از آن در نرم‌افزار Excel مایکروسافت می‌پردازیم. با سیاره‌ی آی‌تی همراه شوید تا یکی دیگر از توابع پرکاربرد و معروف اکسل را بشناسیم.
VLookup در اکسل چیست و چه کاری انجام می‌دهد؟
تابع VLookup در نرم‌افزار Excel مایکروسافت برای پیدا کردن مقدار موجود در ردیف‌های جدول اکسل کاربرد دارد. به عبارت دیگر اگر تعدادی ردیف داشته باشید، با مشخص کردن مقداری که در ستون اول و یکی از ردیف‌ها موجود است، مقداری در همان ردیف اما ستون دیگر را پیدا می‌کنید.
یک مثال ساده کاربری گسترده‌ی این تابع را روشن می‌کند. فرض کنید جدولی در اکسل تهیه کرده‌اید که در ستون اول آن نام محصولات خوراکی ذکر شده و در ستون‌های بعدی مشخصاتی مثل شماره‌ی هر محصول، نوع محصول، قیمت محصول و موجودی انبار ذکر شده است.
برای رسیدن به پاسخ سوالات زیر می‌توانید از تابع VLookup اکسل استفاده کنید:
کد محصولی به اسم نارنگی چیست؟
نوع محصولی به اسم گندم چیست؟
موجودی سیب در انبار چقدر است؟
قیمت هر واحد شیر چقدر است؟
در واقع کد محصول دومین ستون جدول فوق است، نوع محصول سومین ستون و همین‌طور موجود و قیمت در ستون‌های بعدی قرار دارند. با VLookup می‌توان نام محصول را مشخص کرد و مشخصه‌های موجود روبروی آن را استخراج کرد و این کار در فرمول‌نویسی و ساخت فرم‌های جالب در Excel مجموعه‌ی آفیس ضروری است.
آشنایی با روش استفاده تابع VLOOKUP
اگر به توضیحات تابع VLookup در اکسل رجوع کنیم، سینتکس کلی استفاده از آن به صورت زیر است:
=VLOOKUP(Lookup value,Table array,Column index number,[range_lookup])
و اما پارامترهای داخل پرانتز به ترتیب عبارتند از:
- Lookup value یا مقدار مرجع که به دنبال اطلاعات بیشتر در مورد آن هستید. در مثال ما نام محصول مثل سیب و گندم است.
- Table array یا آرایه‌ی جدول که محدوده‌ی ستون‌هایی است که می‌خواهید جستجو در آن صورت بگیرد. توجه کنید که محدوده‌ی جدول جستجو می‌بایست از ستونی شروع شود که مقدار مرجع در آن قرار گرفته است. در مثال ما آرایه‌ی جدول از ستون نام محصول شروع می‌شود.
- Column index number یا شماره‌ی ستون به این معنی است که آنچه به دنبال آن هستید در کدام ستون قرار گرفته است. شمارش از ستون اول جدول و از عدد ۱ شروع می‌شود. بنابراین در مثال ما قیمت ستون شماره‌ی ۴ خواهد بود.
- Range lookup مقدار اضافی است که اگر True باشد، مقادیر مشابه نیز به عنوان نتیجه‌ی جستجو معرفی می‌شوند و اگر False باشد، صرفاً مقدار خاصی معرفی می‌شود.
اما به سوالات قبلی و روابطی که برای یافتن پاسخ باید استفاده کرد بپردازیم تا کاربری عملی تابع VLookup کاملاً روشن شود.
- مقدار مرجع: برای یافتن پاسخ این سوال که قیمت هر واحد شیر چقدر است، با توجه به اینکه شیر در ردیف ۸ قرار گرفته، سلول مرجع B8 خواهد بود.
- آرایه‌ی جدول: آرایه‌ی جستجو شامل تمام ستون‌های B و C و D و E و F می‌شود. بنابراین عبارت B:F که به معنی ستون‌های B الی F است را به عنوان آرایه‌ی جدول تایپ می‌کنیم.
- شماره‌ی ستون قیمت در جدول، عدد ۴ است و به عبارت دیگر چهارمین ستون جدول است.
- مقدار دقیق موردنظرمان است لذا آخرین پارامتر تابع را TRUE تعریف می‌کنیم.
بنابراین فرمول نهایی به این صورت است:
=VLOOKUP(B8,B:F,4,TRUE)
این فرمول را در یکی از سلول‌های خارج از محدوده‌ی جدول تایپ کنید:
و حاصل محاسبه عدد ۱۰ خواهد بود.
به این ترتیب می‌توانید پاسخ سوال بعدی را به سادگی مشخص کنید: نوع محصول گندم چیست؟ برای این سوال از فرمول زیر استفاده می‌شود:
=VLOOKUP(B12,B:F,3,TRUE)
و حاصل با توجه به داده‌های جدول، کلمه‌ی Fruits خواهد بود.
کاربرد پارامتر آخر VLookup برای یافتن نتایج تقریبی
فرض کنید جدولی دارید که در آن در ستون اول، امتیاز ۸۵ موجود نیست و رابطه‌ای نوشته‌اید که در جدول مقدار مرجع که امتیاز است را جستجو می‌کند و رتبه که یک حرف الفبا مثل A تا F است را برمی‌گرداند.
در تصویر زیر امتیازی که در سلول F3 ذکر شده، در جدول رتبه‌بندی جستجو می‌شود و گرید مربوطه در سلول F4 درج می‌شود. با توجه به اینکه ممکن است عدد تایپ شده در سلول F3 در جدول وجود نداشته باشد، می‌بایست پارامتر آخر را TRUE انتخاب کنید تا یافتن نتیجه‌ی تقریبی فعال شود.
با فعال کردن نتیجه‌ی تقریبی، بزرگ‌ترین امتیاز که کمتر از ۸۵ است، در جدول پیدا می‌شود که در مثال ما ۸۰ است و در نتیجه گرید B به عنوان خروجی VLookup ذکر می‌شود.
استفاده از VLookup در فرمول‌های پیچیده و کاربردی صفحات اکسل
زمانی که می‌خواهید در دیتابیسی که به صورت جدولی عظیم است، در مورد چیزی جستجو کنید، به جای روش دستی یا روش استفاده از کلید Ctrl + F برای باز کردن ابزار جستجو، می‌توانید از تابع VLookup استفاده کنید و اگر کمی حوصله داشته باشید، می‌توانید پارامترهای این تابع را از سلول‌های مختلف استخراج کنید.
در مثال ما به عنوان مثال به جای وارد کردن نام محصول یا سلول آن، می‌توانید یک منوی کرکره‌ای در یک سلول قرار دهید تا کاربر نام محصول را ساده و سریع انتخاب کنید.
برای هر مشخصه مثل قیمت و نوع محصول و غیره نیز یک سلول در نظر بگیرید و منوی کرکره‌ای در آن تعریف کنید.
برای اطلاعات بیشتر در مورد روش ایجاد منوی کرکره‌ای در اکسل، به مقاله‌ای که قبلاً در سری آموزش‌های اکسل منتشر شده، توجه فرمایید:
در پایان توجه شما را به مقاله‌ی دیگری در معرفی تابع VLookup جلب می‌کنیم:
سیاره‌ی آی‌تی