تابع VLOOKUP در اکسل و کاربردهای آن — راهنمای جامع

تابع «VLOOKUP» در «مایکروسافت اکسل» (Microsoft Excel) یکی از پرکاربردترین توابع موجود است و اتفاقا یکی از توابعی است که کمتر کسی درک درستی از عملکرد آن دارد. در این مطلب می‌خواهیم با استفاده از مثال‌های واقعی، تابع «VLOOKUP» را توضیح دهیم. در طی این آموزش یک قالب فاکتورگیری برای یک شرکت فرضی ایجاد خواهیم کرد. همانطور که گفته شد، «VLOOKUP» یک «تابع» است. در طی این مطلب فرض را بر این می‌گیریم که شما درک کافی از توابع در اکسل دارید و با توابع اولیه نظیر «SUM»، ‏«AVERAGE» و «TODAY» آشنا هستید. VLOOKUP، یک تابع پیشرفته در اکسل است. شما می‌توانید با مشاهده مجموعه فیلم‌های آموزش اکسل پیشرفته فرادرس، بر روی این تابع و دیگر ابزارهای پیشرفته اکسل، به راحتی تسلط پیدا کنید.

تابع VLOOKUP

در ساده‌ترین حالت استفاده، «VLOOKUP» یک تابع مربوط به پایگاه داده یا «database» است، به این معنی که این تابع با جداول پایگاه داده سروکار خواهد داشت یا به زبانی ساده‌تر، با لیستی از محتوا در کاربرگ‌های اکسل کار می‌کند. این محتوا می‌توانند لیستی از کارمندان، محصولات، مشتری‌ها یا هر چیز دیگری باشند.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

در تصویر زیر یک مثال از لیست (یا دیتابیس) را مشاهده می‌کنید. در این مثال یک لیست از محصولات یک شرکت فرضی قرار گرفته است:

این گونه لیست‌ها معمولا برای هر آیتم یک شناسه‌ی منحصر به فرد دارند. در این لیست، شناسه منحصر به فرد ما ستون «Item Code» است. توجه داشته باشید که تابع «VLOOKUP» تنها در زمانی کار می‌کند که لیست یا پایگاه داده شما یک ستون شناسه منحصر به فرد (که از آن به عنوان «کلید» یا «ID» هم یاد می‌شود) داشته باشد. همچنین این ستون حتما باید اولین ستون موجود در جدول شما باشد. مثالی که در بالا مشاهده می‌کنید این شرایط را دارد.

سخت‌ترین بخش استفاده از تابع «VLOOKUP» درک دقیق کاربرد آن است. این تابع را به شکل زیر تعریف می‌کنیم:

VLOOKUP تابعی است که بر اساس مقدار شناسه منحصر به فرد ارائه شده، اطلاعات را از یک پایگاه داده یا لیست دریافت می‌کند.

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

یک صفحه گسترده به عنوان مثال درست می‌کنیم. این صفحه گسترده یک قالب فاکتور است که می‌توان همیشه در یک شرکت فرضی از آن استفاده کرد. اولین کاری که باید بکنیم، اجرای اکسل و ساخت یک فاکتور خالی به شکل زیر است:

عملکرد این کاربرگ این گونه خواهد بود که کاربر تعدادی کد آیتم را در ستون A وارد می‌کند و سیستم توضیحات و قیمت هر آیتم را از پایگاه داده دریافت می‌کند. در نهایت از این داده‌ها برای محاسبه مبلغ کل استفاده می‌شود. همچنین برای سادگی کار، دیتابیس محصولات را در یک کاربرگ مجزا در همان کاربرگ یا «Workbook» قرار می‌دهیم.

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

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

همانطور که در بالا مشاهده کردید، پایگاه داده محصولات ما به شکل زیر است:

برای امتحان کردن فرمول VLOOKUP، ابتدا یک کد آیتم صحیح در سلول A11 فاکتور خود می‌نویسیم.

سپس سلولی که می‌خواهیم توضیحات محصول در آن نمایش داده شود را انتخاب می‌کنیم. جالب است بدانید این گام همان بخشی است که اکثر کاربران به اشتباه متوجه آن می‌شوند. در این گام می‌خواهیم که توضیحات مربوط به کد آیتم موجود در سلول A11 را دریافت کرده و نمایش دهیم. این توضیحات باید در سلول B11 نمایش داده شوند، در نتیجه فرمول VLOOKUP را نیز باید در سلول B11 بنویسیم.

حالا نیاز به پیدا کردن تابع VLOOKUP در لیست توابع اکسل داریم تا بتوانیم به کمک اکسل این تابع را پیاده‌سازی کنیم.

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

کلیک کنید

برای این کار باید از داخل زبانه «Formulas» گزینه‌ی «Insert Fucntion» را بزنید:

یک کادر باز خواهد شد که امکان انتخاب تمام توابع موجود در اکسل را به ما می‌دهد.

برای پیدا کردن تابع مورد نظر خود، می‌توانیم عبارت «lookup» را جست‌وجو کنیم. سیستم یک لیست از تمام توابع مربوط به «lookup» را به ما نمایش می‌دهد. VLOOKUP دومین گزینه در این بخش خواهد بود. آن را انتخاب کرده و بر روی OK کلیک کنید.

کادر «Function Arguments» باز خواهد شد و لیست تمام آرگومان‌هایی (یا پارامترهایی) که برای پیاده‌سازی فرمول VLOOKUP نیاز داریم را به ما نمایش می‌دهد. در واقع این کادر سوال‌های زیر را از ما می‌پرسد:

1. شناسه منحصر به فردی که به دنبال آن هستید چیست؟
2. دیتابیس شما در کجا قرار دارد؟
3. کدام اطلاعات مربوط به شناسه منحصر به فرد خود را می‌خواهید از دیتابیس به دست بیاورید؟

سه آرگومان اول با رنگ پر رنگ‌تر نمایش داده شده‌اند؛ این مساله به این خاطر است که پر کردن این موارد اجباری است. آرگومان چهارم که کم رنگ است اختیاری بوده و می‌توانید آن را وارد نکنید:

ما این آرگومان‌ها را به ترتیب از بالا به پایین پر می‌کنیم.

اولین آرگومانی که باید پر کنیم، آرگومان «Lookup_value» است. تابع ما نیاز دارد تا بداند باید به دنبال کدام شناسه منحصر به فرد بگردد (که در مثال ما همان کد آیتم است) که بتواند توضیحات آن را برگرداند. در این بخش باید کد آیتمی که قبلتر وارد کرده بودیم را بنویسیم (کدی که در A11 نوشتیم).

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

بر روی آیکون موجود در سمت راست آرگومان اول کلیک کنید:

سپس یکبار بر روی سلولی که شامل کد آیتم ما می‌شود (سلول A11) کلیک کرده و کلید «Enter» را فشار دهید:

مقدار «A11» در آرگومان اول قرار می‌گیرد.

حال باید مقدار آرگومان «Table_array» را وارد کنیم. به عبارتی دیگر، باید به تابع VLOOKUP بگوییم که در کدام لیست یا دیتابیس به دنبال مقدار مورد نظر ما بگردد. بر روی آیکون موجود در سمت راست آرگومان دوم کلیک کنید: