تابع 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 بگوییم که در کدام لیست یا دیتابیس به دنبال مقدار مورد نظر ما بگردد. بر روی آیکون موجود در سمت راست آرگومان دوم کلیک کنید:

حال دیتابیس یا لیستی که می‌خواهید را پیدا کرده و تمام آن را انتخاب نمایید (به غیر از سربرگ ستون‌ها). در مثال ما، دیتابیس در یک کاربرگ مجزا قرار دارد، در نتیجه ابتدا آن را باز می‌کنیم:

سپس تمام دیتابیس خود را به غیر از سربرگ آن انتخاب می‌نماییم:

در نهایت کلید «Enter» را فشار می‌دهیم. محدوده سلول‌هایی که دیتابیس ما را مشخص می‌کنند به طور خودکار در آرگومان دوم قرار خواهند گرفت (در مثال ما این مقدار «Product Database’!A2:‎D7» است).

حال به سراغ آرگومان سوم، یعنی «Col_index_num» می‌رویم. از این آرگومان برای مشخص کردن اطلاعاتی که می‌خواهیم استفاده می‌کنیم. در این مثال، می‌خواهیم مقدار ستون «Description» را به دست بیاوریم. اگر به کاربرگ دیتابیس نگاهی بیندازید مشاهده خواهید کرد که ستون «Description» دومین ستون دیتابیس ما است. پس ما هم مقدار «2» را در آرگومان «Col_index_num» وارد می‌کنیم:

نکته مهمی که باید به آن توجه کنید این است که دلیل وارد کردن عدد «2» این نیست که ستون مورد نظر ما در ستون «B» قرار دارد. اگر دیتابیس ما از ستون «K» شروع می‌شد، باز هم ما عدد 2 را در این بخش وارد می‌کردیم، چراکه ستون «Description» ستون دومی است که در لیست مجموعه ستون‌های وارد شده در آرگومان «Table_array» قرار دارد.

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

کلیک کنید

در نهایت باید تصمیم بگیریم که آیا برای آرگومان آخر نیز مقداری می‌خواهیم وارد کنیم یا خیر. این آرگومان نیازمند یک مقدار «True» یا «False» به معنای صحیح یا غلط است. زمانی که از VLOOKUP در هنگام کار با دیتابیس استفاده می‌کنید، باید بر اساس توضیحات زیر تصمیم به مقداردهی این آرگومان بگیرید:

اگر ستون اول دیتابیس شما (ستونی که به عنوان شناسه منحصر به فرد استفاده شده است) به ترتیب حروف الفبا یا عدد و به صورت صعودی مرتب شده است، می‌توانید مقدار «true» را در این بخش وارد نموده یا آن را خالی رها کنید.

اگر ستون اول دیتابیس مرتب نشده باشد یا به صورت نزولی مرتب شده باشد، باید مقدار «false» را برای این آرگومان استفاده نمایید.

از آنجایی که ستون اول دیتابیس ما مرتب نشده است، مقدار «false» را در آن وارد می‌کنیم.

فرمول ما آماده است. تمامی اطلاعات مورد نیاز برای به دست آوردن اطلاعات مورد نیاز خود را در تابع VLOOKUP وارد کردیم. بر روی گزینه‌ی «OK» کلیک کنید. مشاهده می‌کنید که توضیحات مربوط به آیتم «R992545» در سلول B11 به نمایش در آمده است.

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

اگر یک کد آیتم دیگر را در سلول A11 وارد کنیم، می‌توانیم قدرت تابع VLOOKUP را مشاهده نماییم. توضیحات ما تغییر کرده و به توضیحات آیتم جدید تبدیل خواهد شد.

همچنین می‌توانیم گام‌هایی مشابه را برای نمایش مبلغ در ستون «E11» دنبال کنیم. توجه داشته باشید که فرمول جدید ما باید در سلول E11 ساخته شود. نتیجه‌ی آن به شکل زیر خواهد بود:

فرمول جدید ما نیز همانند زیر است:

توجه کنید که تنها تفاوتی که در بین این دو فرمول وجود دارد این است که مقدار آرگومان «Col_index_num» را از 2 به 3 تغییر داده‌ایم، چراکه می‌خواستیم مقدار ستون سوم دیتابیس را نمایش دهیم.

اگر قصد خرید دو عدد از این آیتم را داشته باشیم، مقدار 2 را در سلول «D11» وارد می‌کنیم، سپس فرمول زیر را در سلول «F11» می‌نویسیم تا قیمت کل به دست بیاید:

1=D11*E11

نتیجه به شکل زیر خواهد بود:

تکمیل قالب فاکتور

تا اینجای کار اطلاعات زیادی راجع به VLOOKUP به دست آورده‌ایم. نکته‌ی مهمی که باید به یاد داشته باشید این است که VLOOKUP در شرایط دیگری به غیر از کار با دیتابیس‌ها نیز کاربرد دارد.

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

کلیک کنید

با این حال قالب فاکتور ما هنوز کامل نیست و برای تکمیل آن باید کارهای زیر را انجام دهیم:

1. مقدارهای آزمایشی را از سلول‌های A11 و D11 حذف می‌کنیم. با این کار فرمول VLOOKUP یک خطا به ما نمایش می‌دهد:
   
   این مساله را با استفاده از توابع «IF» و «ISBLANK» حل می‌کنیم. در واقع فرمول خود را از:

   1=VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)

   به فرمول زیر تغییر می‌دهیم:

   1=IF(ISBLANK(A11),””,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
2. این فرمول را به سلول‌های B11، ‏E11،‏ F11 و ستون‌های مشابه در سایر سطرهای فاکتور کپی می‌کنیم. توجه داشته باشید که زمانی که این کار را انجام می‌دهیم، دیگر فرمول‌های ما به جدول دیتابیس صحیح اشاره نخواهند کرد. برای رفع این مشکل می‌توانیم از ارجاع‌های مطلق استفاده کنیم. همچنین می‌توانیم یک نام برای محدوده‌ی دیتابیس خود در نظر گرفته و از آن برای ارجاع استفاده نماییم. با این کار فرمول ما از:

   1=IF(ISBLANK(A11),””,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))

   به فرمول زیر تغییر می‌کند:

   1=IF(ISBLANK(A11),””,VLOOKUP(A11,Products,2,FALSE))

   حالا می‌توانیم فرمول را به سایر سطرهای خود نیز کپی کنیم.

3. می‌توانیم سلول‌های خود را قفل کنیم تا کسی به طور اشتباه فرمول‌های ما را در هنگام پر کردن فاکتور تغییر ندهد.
4. این فایل را به عنوان یک قالب ذخیره می‌کنیم تا هر کسی در شرکت بتواند از آن استفاده کند.

فیلم آموزش گوگل شیت – اسپرید شیت گوگل Google Sheets – مقدماتی در فرادرس

کلیک کنید

حتی می‌توان یک دیتابیس برای کاربران ایجاد کرد، سپس با وارد کردن شناسه کاربری در سلول F5، مشخصات کاربر از جمله نام و آدرس وی را در سلول‌های B6 تا B8 قرار داد. این کار را می‌توانید به عنوان تمرین انجام دهید. قالب تکمیل شده را می‌توانید از این لینک دانلود کنید.