چگونه از VLOOKUP برای جستجو در بازه های اعداد مرتب استفاده کنیم؟

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

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

پیش بینی وضعیت بورس در روزهای آینده + آموزش ویدیویی دقیق ترین پیش بینی و تحلیل آماری بورس در سایت AmarBourse.ir

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 یا شماره ی ستون به این معنی است که آنچه به دنبال آن هستید در کدام ستون قرار گرفته است. شمارش از ستون اول جدول و از عدد 1 شروع می شود. بنابراین در مثال ما قیمت ستون شماره ی 4 خواهد بود.
• Range lookup مقدار اضافی است که اگر True باشد، مقادیر مشابه نیز به عنوان نتیجه ی جستجو معرفی می شوند و اگر False  باشد، صرفاً مقدار خاصی معرفی می شود.

اما به سوالات قبلی و روابطی که برای یافتن پاسخ باید استفاده کرد بپردازیم تا کاربری عملی تابع VLookup کاملاً روشن شود.

• مقدار مرجع: برای یافتن پاسخ این سوال که قیمت هر واحد شیر چقدر است، با توجه به اینکه شیر در ردیف 8 قرار گرفته، سلول مرجع B8 خواهد بود.
• آرایه ی جدول: آرایه ی جستجو شامل تمام ستون های B و C و D و E و F می شود. بنابراین عبارت B:F که به معنی ستون های B الی F است را به عنوان آرایه ی جدول تایپ می کنیم.
• شماره ی ستون قیمت در جدول، عدد 4 است و به عبارت دیگر چهارمین ستون جدول است.
• مقدار دقیق موردنظرمان است لذا آخرین پارامتر تابع را TRUE تعریف می کنیم.

بنابراین فرمول نهایی به این صورت است:

=VLOOKUP(B8,B:F,4,TRUE)

این فرمول را در یکی از سلول های خارج از محدوده ی جدول تایپ کنید:

و حاصل محاسبه عدد 10 خواهد بود.

به این ترتیب می توانید پاسخ سوال بعدی را به سادگی مشخص کنید:  نوع محصول گندم چیست؟ برای این سوال از فرمول زیر استفاده می شود:

=VLOOKUP(B12,B:F,3,TRUE)

و حاصل با توجه به داده های جدول، کلمه ی Fruits  خواهد بود.

کاربرد پارامتر آخر VLookup برای یافتن نتایج تقریبی

فرض کنید جدولی دارید که در آن در ستون اول، امتیاز 85 موجود نیست و رابطه ای نوشته اید که در جدول مقدار مرجع که امتیاز است را جستجو می کند و رتبه که یک حرف الفبا مثل A تا F است را برمی گرداند.

در تصویر زیر امتیازی که در سلول F3 ذکر شده، در جدول رتبه بندی جستجو می شود و گرید مربوطه در سلول F4 درج می شود. با توجه به اینکه ممکن است عدد تایپ شده در سلول F3 در جدول وجود نداشته باشد، می بایست پارامتر آخر را TRUE انتخاب کنید تا یافتن نتیجه ی تقریبی فعال شود.

با فعال کردن نتیجه ی تقریبی، بزرگ ترین امتیاز که کمتر از 85 است، در جدول پیدا می شود که در مثال ما 80 است و در نتیجه گرید B به عنوان خروجی VLookup ذکر می شود.

استفاده از VLookup در فرمول های پیچیده و کاربردی صفحات اکسل

زمانی که می خواهید در دیتابیسی که به صورت جدولی عظیم است، در مورد چیزی جستجو کنید، به جای روش دستی یا روش استفاده از کلید Ctrl + F برای باز کردن ابزار جستجو، می توانید از تابع VLookup استفاده کنید و اگر کمی حوصله داشته باشید، می توانید پارامترهای این تابع را از سلول های مختلف استخراج کنید.

در مثال ما به عنوان مثال به جای وارد کردن نام محصول یا سلول آن، می توانید یک منوی کرکره ای در یک سلول قرار دهید تا کاربر نام محصول را ساده و سریع انتخاب کنید.

برای هر مشخصه مثل قیمت و نوع محصول و غیره نیز یک سلول در نظر بگیرید و منوی کرکره ای در آن تعریف کنید.

برای اطلاعات بیشتر در مورد روش ایجاد منوی کرکره ای در اکسل، به مقاله ای که قبلاً در سری آموزش های اکسل منتشر شده، توجه فرمایید:

آموزش ایجاد لیست کشویی در اکسل

در پایان توجه شما را به مقاله ی دیگری در معرفی تابع VLookup جلب می کنیم:

آشنایی با فرمت شرطی یا Conditional Formatting و VLookup در اکسل

سیاره ی  آی تی