تابع VLOOKUP در اکسل :Microsoft Excel دارای تعداد زیادی عملکرد مفید و کاربردی است که بسیاری از آنها معمولا توسط کاربران عادی استفاده نمی‌شوند.
تابع VLOOKUP در اکسل بدون شک یکی پرکاربردترین عملکرد اکسل است. در هر مصاحبه شغلی که به مهارت اکسل نیاز داشته باشد، سوال آیا می‌دانید چگونه از عملکرد VLOOKUP استفاده کنید یکی از پرتکرارترین سوال‌ها است. عملکرد VLOOKUP در میان توابع اکسل در دسته جستجو قرار می‌گیرد.
V در VLOOKUP مخفف vertical است. این تابع برای جستجوی داده‌هایی استفاده می‌شود که به صورت عمودی ساختار یافته‌اند. برای جستجوی افقی، تابعی به نام HLOOKUP وجود دارد. گرچه استفاده از VLOOKUP آسان است اما محدودیت‌ها و پس زمینه‌های خاص خود را دارد. ما در مورد همه موارد از جمله نقاط قوت و ضعف عملکرد صحبت خواهیم کرد.
چرا ما به تابع ویلوکاپ در اکسل نیاز داریم؟
تصور کنید هزاران ردیف داده از کارمندان در Excel دارید و رئیس شما به طور تصادفی از شما حقوق و اطلاعات یکی از کارکنان را می‌پرسد یا بدتر از آن، او لیستی از صد کارمند را به شما می‌دهد و از شما می‌خواهد مشخصات این کارمندان را برایش ارسال کنید.
شما چه کاری انجام خواهید داد؟ مسلما پیدا کردن این اطلاعات به صورتی دستی نه عملی است نه هوشمندانه. پس راه حل درست چیست؟ اجازه دهید عملکرد VLOOKUP اکسل را به شما معرفی کنم.
بخش‌های مختلف تابع Vlookup در اکسل
تابع VLOOKUP در اکسل اولین مطابقت مقدار داده‌ها با نام مقدار جستجو را در یک جدول داده جستجو می‌کند و مقدار را از فهرست ستون داده شده بازیابی می‌کند. مطابقت می‌تواند تقریبی یا دقیق باشد. فرمول این تابع به شکل زیر است:
=VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])
=VLOOKUP(جستجوی تقریبی،شماره ستون، محدوده جستجو ، مقدار مورد نظر برای جستجو)
Lookup_value: این بخش از دستور VLOOKUP از موارد اجباری است و پارامتری را که می‌خواهیم درمحدوده مورد نظر جستجو شود را مشخص می‌کند.
Table_array: جدولی که میخواهید از آن به دنبال مقدار جستجو بگردید.
col_index_number: این پارامتر، مشخص می‌کند که بعد از پیدا کردن مقدار مورد نظر، نتیجه تابع از چه ستونی در جدول آرایه‌ای استخراج شود.
[range_lookup]: وارد کردن این پارامتر کاملا اخیاری است. اگر این مقدار رو برابر TRUE قرار بدهید به این معنی است که، مطابقت تقریبی را انتخاب کرده‌اید.در این حالت اگر مقداری که سرچ کرده‌اید در محدوده مورد نظر یافت نشد، کردهو تابع برای شما بزرگترین مقداری که در محدوده وجود دارد و کوچک‌تر از مقدار مورد نظر شما است را به عنوان نتیجه جستجو، نشان می‌دهد.اگر این مقدار را بروی FALSE قرار دهید به این معنی است که مطابقت دقیق را در نظر دارید. در این حالت تابع خطای #REF را نشان می‌دهد.
چگونه از تابع VLOOKUP (ویلوکاپ)استفاده کنیم؟
برای مثال اگر شما نام یک محصول را می‌دانید و میخواهید به سرعت قیمت آن را تعیین کنید، می‌توانید به راحتی نام محصول را در Excel وارد کنید و VLOOKUP قیمت آن را برای شما به راحتی پیدا می‌کند.
با این حال، برای یک کاربر تازه کار اکسل، استفاده از VLOOKUP می‌تواند یک روند سخت و گیج‌کننده داشته باشد، شما می‌توانید فقط با مرور، آموزش گام به گام که در ادامه آورده شده است، نحوه استفاده از VLOOKUP در اکسل را بیاموزید:
- روی سلولی که میخواهید فرمول VLOOKUP در آن محاسبه شود کلیک کنید.
- روی “فرمول” در بالای صفحه کلیک کنید
- روی «جستجو و مرجع» در قسمت روبان کلیک کنید.
- روی “VLOOKUP” در پایین منوی کشویی کلیک کنید.
- اولین پارامتری که باید وارد کنید مقدار جستجو است، روی آیکون کنار مستطیل آن کلیک کرده سپس بر روی سلولی که به دنبال داده های آن هستید را کلیک کرده و «Enter» را فشار دهید.
- مرحله بعدی مشخص کردن محدوده داده‌هایی است که میخواهید VLOOKUP برای جستجوی خود استفاده کند، در کادر table_array مشخص کنید. می‌توانید کل جدول (به استثنای سرصفحه‌ها) را انتخاب کنید.
- شماره ستونی را که VLOOKUP باید اطلاعات مورد نظر مارا از آن استخراج کند را در قسمت col_index_num ، مشخص کنید.
نکته: در اکسل ستون‌ها با حروف مشخص می‌شوند، شماره ستون در اینجا اشاره به این دارد که ستون مورد نظر ما چندمینستون در محدوده انتخابی ما است.
- با وارد کردن FALSE (مطابقت دقیق) یا TRUE (مطابقت تقریبی) در کادر range_lookup مشخص کنید که آیا به یک مطابقت دقیق نیاز دارید یا خیر.
- روی “تأیید” در پایین پنجره بازشده کلیک کنید.
چگونه می‌توان ازVLOOKUP در برگه‌های مختلف اکسل استفاده کرد؟
با استفاده از VLOOKUP، نه تنها می‌توانید مقادیر فردی را جستجو کنید، بلکه می‌توانید دو صفحه کار را در یکی ترکیب کنید. به عنوان مثال، اگر یک صفحه کار با نام و شماره تلفن و یک صفحه دیگر با نام و آدرس ایمیل دارید، می‌توانید با استفاده از VLOOKUP آدرس‌های ایمیل را در کنار نام‌ها و شماره تلفن‌ها قرار دهید.
با استفاده از تابع زیر انجام این کار بسیار ساده است:
=VLOOKUP(LOOKUP_VALUE,SHEEETNAME!TABALE_ARRAY,COL_INDEX,1/0)
=VLOOKUP(جستجوی تقریبی،شماره ستون، نام برگه‌ها محدوده جستجو ، مقدار مورد نظر برای جستجو)
همانطور که ملاحظه می‌کنید این تابع با تابع اصلی VLOOKUP تنها در نام ورق تفاوت دارد. پس باید تنها نام برگه را قبل از جدول مورد جستجوبا یک علامت تعجب بنویسد.
تابع Vlookup یکی از به‌دردبخورترین توابع اکسل است و دانستن آن مسیر میانبری را برای شما در استفاده از نرم‌افزار اکسل باز می‌کند و در وقت شما صرفه‌جویی می‌کند.
بعید است به مصاحبه‌ی شغلی دعوت شوید و اکسل یکی از سوالات اصلی آن مصاحبه‌ی شغلی نباشد. به همین دلیل، یادگیری اکسل از چیزی که فکرش را هم بکنید اهمیت بیشتری دارد. در ادامه با آموزش سایر توابع نرم افزار اکسل در مکتب‌خونه همراه باشید.
مثال های تابع VLOOKUP
تابع VLOOKUP در اکسل برای جستجوی مقدار خاصی در یک ستون و بازگرداندن مقدار مربوطه از ستون دیگر استفاده می‌شود. مثلاً، اگر لیستی از محصولات و قیمت‌های آنها را داشته باشید، می‌توانید با استفاده از VLOOKUP قیمت یک محصول خاص را پیدا کنید.
استفاده از کاراکترهای جایگزین برای تطبیق های جزئی در تابع Vlookup
در برخی موارد، می‌توانید از کاراکترهای جایگزین مانند `*` (ستاره) یا `?` (علامت سوال) برای تطبیق جزئی در VLOOKUP استفاده کنید. مثلاً، اگر به دنبال کلمه‌ای هستید که با “آب” شروع می‌شود، می‌توانید از `آب*` استفاده کنید.
حساسیت به حروف در Vlookup
تابع VLOOKUP به طور پیش‌فرض به حروف بزرگ و کوچک حساس نیست. یعنی “Apple” و “apple” را یکسان در نظر می‌گیرد. اگر نیاز به حساسیت به حروف دارید، باید از توابع دیگر مانند `EXACT` استفاده کنید.
درک رفتار تابع VLOOKUP
VLOOKUP مقدار مورد نظر را در اولین ستون از محدوده مشخص شده جستجو می‌کند و اگر مقدار را پیدا کند، مقدار مربوطه از ستون تعیین شده را برمی‌گرداند. اگر مقدار پیدا نشود، خطای `#N/A` برگردانده می‌شود.
تفسیر واژه «جدول» در VLOOKUP
در VLOOKUP، «جدول» به محدوده‌ای از داده‌ها اشاره می‌کند که شامل ستون جستجو و ستون‌های مربوطه است. این محدوده باید شامل ستونی باشد که مقدار جستجو در آن قرار دارد و ستون‌هایی که مقادیر بازگردانده شده از آنها استخراج می‌شود.
آرگومانهای تابع VLOOKUP اکسل
تابع VLOOKUP چهار آرگومان دارد: مقدار جستجو، محدوده جدول، شماره ستون بازگردانده شده و نوع تطبیق (دقیق یا تقریبی). نوع تطبیق معمولاً `FALSE` برای تطبیق دقیق و `TRUE` برای تطبیق تقریبی استفاده می‌شود.
نکات و قوانین حاکم بر تابع VLOOKUP اکسل
VLOOKUP همیشه اولین ستون در محدوده جدول را جستجو می‌کند. اگر مقدار جستجو در ستون اول نباشد، نتیجه‌ای بازگردانده نمی‌شود. همچنین، اگر نوع تطبیق `TRUE` باشد، ستون جستجو باید به ترتیب صعودی مرتب شده باشد.
مثال ساده از تابع VLOOKUP
فرض کنید یک جدول دارید که در ستون اول نام محصولات و در ستون دوم قیمت‌ها قرار دارد. با استفاده از `=VLOOKUP(“سیب”, A2:B10, 2, FALSE)` می‌توانید قیمت سیب را پیدا کنید.
استفاده از تابع VLOOKUP با دو شرط
VLOOKUP به طور مستقیم از دو شرط پشتیبانی نمی‌کند، اما می‌توانید با ایجاد یک ستون ترکیبی از دو شرط و استفاده از آن در VLOOKUP، این کار را انجام دهید. مثلاً، `=VLOOKUP(A2&B2, C2:D10, 2, FALSE)`.
نمایش نتایج تکراری
اگر چندین مقدار مشابه در ستون جستجو وجود داشته باشد، VLOOKUP فقط اولین مقدار مطابقت داده شده را برمی‌گرداند. برای نمایش تمام نتایج تکراری، باید از روش‌های دیگر مانند فیلتر کردن یا استفاده از توابع پیشرفته‌تر استفاده کنید.
استفاده از VLOOKUP برای نمایش داده ستون قبلی
VLOOKUP نمی‌تواند به طور مستقیم داده‌ها را از ستون‌های قبل از ستون جستجو برگرداند. برای این کار، باید ساختار جدول خود را تغییر دهید یا از توابع دیگر مانند `INDEX` و `MATCH` استفاده کنید.
اهمیت آرگومان آخر VLOOKUP و نمایش نتیجه تقریبی
آرگومان آخر VLOOKUP تعیین می‌کند که آیا تطبیق باید دقیق (`FALSE`) یا تقریبی (`TRUE`) باشد. تطبیق تقریبی معمولاً برای مقادیر عددی و زمانی استفاده می‌شود که داده‌ها به ترتیب صعودی مرتب شده‌اند.
نکات مهم تابع VLOOKUP اکسل
- ستون جستجو همیشه اولین ستون است: تابع VLOOKUP فقط می‌تواند مقادیر را در اولین ستون محدوده جدول جستجو کند. اگر مقدار مورد نظر در ستون‌های دیگر باشد، VLOOKUP نمی‌تواند آن را پیدا کند.
- حساسیت به حروف بزرگ و کوچک ندارد: ولوکاپ به طور پیش‌فرض به حروف بزرگ و کوچک حساس نیست. برای انجام جستجوی حساس به حروف، باید از ترکیب توابع دیگر مانند EXACT یا INDEX و MATCH استفاده کنید.
- نتیجه تقریبی نیاز به مرتب‌سازی داده‌ها دارد: اگر از تطبیق تقریبی (آرگومان آخر TRUE) استفاده می‌کنید، داده‌های ستون جستجو باید به ترتیب صعودی مرتب شده باشند، در غیر این صورت ممکن است نتایج نادرست برگردانده شود.
- فقط اولین تطابق را برمی‌گرداند: اگر چندین مقدار مشابه در ستون جستجو وجود داشته باشد، VLOOKUP فقط اولین مقدار مطابقت داده شده را برمی‌گرداند و بقیه را نادیده می‌گیرد.
- خطای #N/A به معنای عدم یافتن مقدار است: اگر VLOOKUP نتواند مقدار مورد نظر را در ستون جستجو پیدا کند، خطای #N/A برگردانده می‌شود. این خطا نشان‌دهنده این است که مقدار جستجو در محدوده جدول وجود ندارد.
آموزش اکسل با مکتب خونه
اگر به دنبال افزایش مهارت‌های خود در اکسل هستید، دوره‌های آموزشی مکتب خونه بهترین گزینه برای شماست. در این دوره‌ها، از سطح مقدماتی تا پیشرفته، با جدیدترین تکنیک‌ها و ترفندهای اکسل آشنا می‌شوید و می‌توانید به راحتی پروژه‌های خود را انجام دهید. همین حالا در دوره‌های آموزش اکسل مکتب خونه ثبت نام کنید و به جمع متخصصان این نرم‌افزار پرکاربرد بپیوندید.
