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

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2023-12-17 06:46

موارد مهم

• عملکرد VLOOKUP در اکسل برای یافتن یک مقدار در صفحه گسترده استفاده می شود.
• نحو و آرگومان‌ها =VLOOKUP هستند (مقدار_جستجو، جدول_جستجو، شماره_ستون، [تطابق_تقریبی])

این مقاله نحوه استفاده از تابع VLOOKUP را در همه نسخه‌های اکسل، از جمله Excel 2019 و Microsoft 365 توضیح می‌دهد.

عملکرد VLOOKUP چیست؟

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

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

نحو و آرگومان های تابع VLOOKUP

چهار بخش ممکن برای این تابع وجود دارد:

=VLOOKUP(جستجوی_مقدار، جدول_جستجو، شماره_ستون، [تطابق_تقریبی])

• search_value مقداری است که شما به دنبال آن هستید. باید در ستون اول lookup_table باشد.
• lookup_table محدوده ای است که در آن جستجو می کنید. این شامل search_value می‌شود.
• column_number عددی است که نشان دهنده تعداد ستون های lookup_table است، از سمت چپ، باید ستونی باشد که VLOOKUP مقدار را از آن برمی گرداند.
• تطابق_تقریبی اختیاری است و می تواند درست یا نادرست باشد. تعیین می کند که یک تطابق دقیق یا تقریبی پیدا شود. وقتی حذف شد، پیش‌فرض TRUE است، به این معنی که مطابقت تقریبی پیدا می‌کند.

مثالهای تابع VLOOKUP

در اینجا چند نمونه وجود دارد که عملکرد VLOOKUP را در عمل نشان می دهد:

مقدار کنار یک کلمه را از جدول بیابید

=VLOOKUP("لیموها"، A2:B5، 2)





این یک مثال ساده از عملکرد VLOOKUP است که در آن باید از لیستی از چندین مورد، تعداد لیموهای موجود در انبار را پیدا کنیم. محدوده مورد نظر ما A2:B5 است و عددی که باید بکشیم در ستون 2 است زیرا "در انبار" دومین ستون از محدوده ما است. نتیجه اینجا 22 است.
شماره کارمند را با استفاده از نام آنها پیدا کنید
=VLOOKUP(A8, B2:D7, 3)
=VLOOKUP(A9, A2:D7, 2)





در اینجا دو مثال وجود دارد که تابع VLOOKUP را کمی متفاوت می نویسیم. آنها هر دو از مجموعه داده های مشابهی استفاده می کنند، اما از آنجایی که ما اطلاعات را از دو ستون مجزا، 3 و 2 استخراج می کنیم، این تمایز را در انتهای فرمول انجام می دهیم - اولین مورد موقعیت شخص را در A8 (فینلی) می گیرد در حالی که فرمول دوم نامی را برمی گرداند که با شماره کارمند در A9 (819868) مطابقت دارد.از آنجایی که فرمول ها سلول های مرجع هستند و یک رشته متن خاص نیستند، می توانیم نقل قول ها را کنار بگذاریم.
استفاده از بیانیه IF با VLOOKUP
=IF(VLOOKUP(A2, Sheet4!A2:B5, 2)>10, "No", "Yes")





VLOOKUP همچنین می تواند با دیگر توابع اکسل ترکیب شود و از داده های برگه های دیگر استفاده کند. ما در این مثال هر دو را انجام می دهیم تا مشخص کنیم که آیا باید تعداد بیشتری از آیتم را در ستون A سفارش دهیم یا خیر. از تابع IF استفاده می کنیم تا اگر مقدار موقعیت 2 در Sheet4!A2:B5 بزرگتر از 10 باشد، می نویسیم No. برای نشان دادن اینکه ما نیازی به سفارش بیشتر نداریم.
نزدیکترین عدد را در جدول پیدا کنید
=VLOOKUP(D2, $A$2:$B$6, 2)





در این مثال آخر، ما از VLOOKUP برای تعیین درصد تخفیفی که باید برای سفارش‌های عمده کفش استفاده شود، استفاده می‌کنیم. تخفیفی که ما به دنبال آن هستیم در ستون D است، محدوده ای که شامل اطلاعات تخفیف است A2:B6 است و در آن محدوده ستون 2 است که شامل تخفیف است.از آنجایی که VLOOKUP نیازی به یافتن تطابق دقیق ندارد، approximate_match برای نشان دادن TRUE خالی می ماند. اگر مطابقت دقیقی پیدا نشد، تابع از مقدار کوچکتر بعدی استفاده می کند.
می بینید که در مثال اول از 60 سفارش، تخفیف در جدول سمت چپ یافت نمی شود، بنابراین از مقدار کوچکتر بعدی 50 استفاده می شود که 75٪ تخفیف است. ستون F قیمت نهایی در هنگام محاسبه تخفیف است.

خطاها و قوانین VLOOKUP
در اینجا مواردی وجود دارد که باید هنگام استفاده از تابع VLOOKUP در اکسل به خاطر بسپارید:

اگر search_value یک رشته متنی است، باید در گیومه احاطه شود.
اگر VLOOKUP نتواند نتیجه ای پیدا کند Excel NO MATCH را برمی گرداند.
Excel NO MATCH را برمی گرداند اگر عددی در جدول_جستجو وجود نداشته باشد که بزرگتر یا برابر با search_value باشد.
Excel REF را برمی گرداند! اگر column_number بیشتر از تعداد ستون های lookup_table باشد.
search_value همیشه در سمت چپ صفحه lookup_table قرار دارد و هنگام تعیین شماره ستون در موقعیت 1 قرار دارد.
اگر FALSE را برای approximate_match مشخص کنید و مطابقت دقیقی پیدا نشد، VLOOKUP N/A را برمی گرداند.
اگر برای approximate_match TRUE را مشخص کنید و مطابقت دقیقی پیدا نشد، مقدار کوچکتر بعدی برگردانده می شود.
جدول مرتب نشده باید از FALSE برای تطابق_تقریبی استفاده کند تا اولین مطابقت دقیق برگردانده شود.
اگر تقریبی_تطبیق درست است یا حذف شده است، ستون اول باید بر اساس حروف الفبا یا عدد مرتب شود. اگر مرتب نشده باشد، اکسل ممکن است مقدار غیرمنتظره ای را برگرداند.
استفاده از ارجاعات سلول مطلق به شما امکان می دهد فرمول ها را بدون تغییر جدول_جستجو به صورت خودکار پر کنید.


کارکردهای دیگر مانند VLOOKUP
VLOOKUP جستجوهای عمودی را انجام می دهد، به این معنی که اطلاعات را با شمارش ستون ها بازیابی می کند. اگر داده ها به صورت افقی سازماندهی شده اند و می خواهید ردیف ها را برای بازیابی مقدار معکوس شمارش کنید، می توانید از تابع HLOOKUP استفاده کنید.
عملکرد XLOOKUP مشابه است اما در هر جهتی کار می کند.