آموزش تصویری تابع vlookup در اکسل با 2 مثال عملی

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

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

آموزش پیشنهادی

دوره جامع آموزش اکسل پیشرفته 2021⚡️ویژه مبتدی تا حرفه‌ای

• فرمول نویسی در اکسل
• کار با پیوت تیبل
• پاورکوئری
• داشبورد سازی
• ضبط ماکرو و VBA نویسی

مشاهده دوره

فایل PDF

جهت دانلود فایل pdf آموزش بالا روی لینک زیر کلیک کنید:

تعریف و کاربرد vlookup در اکسل

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

فرمول VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=

آرگومانهای دستور vlookup در اکسل به شرح زیر است:

• lookup_value:مقدار یا آدرس جستجو
• table_array:این آرگومان محدوده‌ای از جدول است که در آن می‌خواهید جستجو انجام شود. ستون اول این محدوده باید حاوی مقادیری باشد که می‌خواهید به آنها اشاره کنید.
• col_index_num:این آرگومان عددی است که نشان می‌دهد در صورت یافتن مقدار جستجو، مقدار متناظر در کدام ستون از محدوده جدول (table_array) قرار دارد.
• range_lookup: این آرگومان اختیاری است و مشخص می‌کند که جستجو باید دقیقاً (False) یا تقریبی (True) باشد. اگر این آرگومان وجود نداشته باشد، اکسل فرض می‌کند جستجو تقریبی باشد.

راهنمای بکارگیری و آموزش تابع vlookup

تابع VLOOKUP هنگامی استفاده می شود كه بخواهيم يك جستجوی عمودی بر روی اولين ستون يك ناحيه از جدول پايه انجام دهيم و در ستونی ديگر از سطری كه نتيجه پيدا شد، مقداری را استخراج كنيم. این گونه فهرست ها عموما برای هر مورد با شناسه ی منحصر به فرد استفاده می شود. دقت کنید که این ویژگی زمانی قابل به کارگیری است که تابع « VLOOKUP » با نام و یا ID خاص باشد. به عنوان مثال صفحه گسترده ای در برنامه اکسل باز می کنیم. مثلا در فیلم آموزشی ابتدایی صفحه مثالی مربوط به مشخصات دانشجویان یک واحد دانشگاهی آمده است.

• برای تست کردن فرمول VLOOKUP ابتدا یک کد آیتم صحیح را در یک سلول از صفحه گسترده اکسل می نویسیم.
• در مرحله بعد سلولی را که می خواهیم توضیحات محصول در آن نمایش داده شود را انتخاب می نماییم. نکته ای که در اینجا اهمیت دارد، اینست که توضیحات باید در سلول دیگری نوشته شود که اکثر کاربران این مسئله را به اشتباه متوجه می شوند.
• در ادامه باید از بخش  insert قسمت formulas  را انتخاب کنیم.
• سپس روی بخش  insert function کلیک می کنیم.
•  سپس مجدد بخش ‌  lookup را انتخاب می کنیم.
• در این قسمت کادر«Function Arguments» را باز می کنیم و لیست تمامی پارامترهایی که برای پیاده سازی تابع VLOOKUP لازم است را به ما نشان می دهد.

در این قسمت 3 پارامتر موردنیاز از ما پرسیده میشود که شامل موارد زیر خواهد بود:

۱.شناسه منحصر به فردی که به دنبال آن هستید چیست؟

۲.مرکزداده یا دیتابیس شما در کجا قرار دارد؟

۳.کدام اطلاعات مربوط به شناسه مورد نظر را می خواهید از دیتابیس انتخاب کنید؟

پارامترهای پررنگ اجباری و موارد کم رنگ تر اختیاری هستند.

این پارامترها از بالا به پایین تکمیل می شوند. سپس تابع ما باید بداند که به دنبال کدام مشخصه خاص بگردد.

• بروی گزینه اول کادر کلیک کرده و بعد بروی سلول که کد مشخصه ما را در برمی گیرد ،کلیک کرده و کلید Enterرا فشار می دهیم.
• پس از وارد کردن مقدار آن مشخصه در بخش مربوطه، به قسمت «Table_array»مراجعه نموده و به تابع VLOOKUP پمی گوییم که در کدام بخش یا دیتا بیس مقدار موردنظر ما را جستجو کند.
• حالا دیتابیس موردنظرخود را انتخاب و سربرگهای مربوط را انتخاب می کنیم.

پس از فشردن کلید Enter به قسمت بعدی می رویم یعنی«Col_index_num» و این قسمت برای مشخص کردن اطلاعاتی است که می خواهیم بعدا آنها را بکار گیریم. مثلا در مثال فیلم حاضر می خواهیم مشخصات دانشجو را براساس ستون «Description» بدست آوریم. بانگاهی به دیتابیس می فهمیم که دومین ستون دیتابیس ما می باشد .اکنون مقدار مدنظر را در بخش «Col_index_num» وارد می کنیم.

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

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

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

مشکلات تابع vlookup در اکسل

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

۱-خطای N/A

یکی از مهمترین مشکلات برنامه VLOOKUP در اکسل خطای N/A است. اگر شما در محدوده پارامتری LOOKUP_VALUE مقداری وارد نکنید و یا مقادیری که در محدوده جستجو وجود ندارند وارد کنید در چنین حالتی با این خطا رو به رو می شوید.

بهتر است قبل پایان کار ورودی های خود را بررسی کنید تا اطمینان یابید بدرستی وارد شده اند.

۲-مشکل وارد شدن داده عددی به صورت متنی

ازجمله مشکلات مشاهده شده هنگام بکارگیری این تابع،این است که موقع وارد کردن داده های عددی به اشتباه از “” استفاده کنید. در این چنین حالتی اکسل آن را داده متنی فرض می کند و همین باعث می شود که تابع اجرا نشود.

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

۳-خطای VALUE#

ایم خطا زمانی دیده می شود که طول داده از ۲۵۵ کاراکتر کمتر باشد. به منظور برطرف کردن این ایراد باید داده ها کمتر از ۲۵۵ کاراکتر تعریف شوند.

VLOOKUP یا XLOOKUP؟

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

نکاتی برای استفاده از تابع ویلوکاپ در اکسل

در این بخش آموزش تصویری vlookup در اکسل، مواردی که قبل از انتخاب این تابع اهمیت دارد، آمده است.

• جهت جستجو: تابع VLOOKUP در اکسل به دنبال داده در سمت راست هر مقدار جستجو می‌گردد. اگر نیاز به جستجو در سمت چپ داده‌ها دارید، از توابعی مانند INDEX و MATCH یا XLOOKUP استفاده کنید.
• بروزرسانی داده: اگر جدول داده‌ها خود را بروزرسانی کردید و ستون‌ها یا سطرها اضافه یا حذف شده باشند، باید اطمینان حاصل کنید که فرمول‌های VLOOKUP شما با تغییرات هماهنگ شده باشند.
• ساختار داده: VLOOKUP یک تابع جستجوی عمودی است و انتظار دارد که داده‌ها در یک جدول مرتب شده باشند. بنابراین، جدولی که از آن استفاده می‌کنید، باید مرتب شده و هر سطر دارای داده‌های مرتبط در هر ستون باشد.
• تطابق دقیق یا تقریبی: به طور پیش‌فرض، VLOOKUP به دنبال تطابق تقریبی است. برای تطابق دقیق، آرگومان range_lookup را به FALSE تنظیم کنید.
• استفاده از Wildcard: برای جستجوی مقادیر با Wildcard (مثل ستاره یا علامت سوال)، می‌توانید از امپرسند (&) برای پیوند داده جستجویی با Wildcard در فرمول VLOOKUP استفاده کنید.

نحوه استفاده از تابع VLOOKUP در برگه های مختلف اکسل

تابع VLOOKUP می‌تواند در یک برگه (Sheet) یا برگه‌های مختلف اکسل استفاده شود. برای مثال، اگر بخواهید در برگه A از اکسل از تابع VLOOKUP استفاده کنید و جواب را در برگه B بازیابی کنید، می‌توانید فرمول را به صورت زیر وارد کنید:

=VLOOKUP(A1, Sheet1!A:B, 2, FALSE)

نمای کلی تابع vlookup

این تابع موجود در برنامه اکسل بطورعمودی جستجو را باتوجه به شاخص و  range موردنظر انجام می دهد. و بهمین راحتی شما می توانید پارامتری را در فایل اکسل خود با توجه به سطر و ستون پیدا کنید.

در فیلم آموزشی موجود، می توانید نمونه ای از کار با تابع vlookup را مشاهده کنید که در رابطه با طراحی کارت دانشجویی به کمک این تابع است. همچنین برای مشاهده مثال های بیشتر مقاله مربوطه در وبسایت simplilearn را بررسی کنید.

با ثبت نظرات، پیشنهادات، انتقاد ها و همچنین سوال های خود درباره تابع vlookup در بخش نظرات سایت ما را در راستای بهبود کیفیت خدماتمان، یاری نمایید.