تابع VLOOKUP در اکسل

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

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

VLOOKUP چیست؟

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

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

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

در اکسل ۳۶۵ و اکسل ۲۰۲۱ می‌توانید از تابع XLOOKUP استفاده کنید که جانشین قدرتمندتر و انعطاف‌پذیرتری برای VLOOKUP است.

سینتکس تابع VLOOKUP در اکسل

ساختار یا سینتکس تابع VLOOKUP به‌شکل زیر است:

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

که در آن:

•       lookup_value (ضروری): مقداری است که باید جستجو شود. این مقدار می‌تواند یک عدد، تاریخ، متن، مرجع سلول یا مقداری باشد که توسط تابع دیگری بازگردانده شده است. برخلاف اعداد و مراجع سلولی، مقادیر متنی باید همیشه در «گیومه» قرار بگیرند.
•       table_array (ضروری): محدوده‌ای از سلول‌ها که در آن به‌دنبال مقدار جستجو می‌گردید و از آن مقدار مرتبط را بازیابی می‌کنید. تابع VLOOKUP همیشه در ستون اول محدوده جستجو می‌کند که ممکن است شامل مقادیر متنی، اعداد، تاریخ‌ها و مقادیر منطقی باشد.
•       col_index_num (ضروری): شماره ستونی که مقدار باید از آن بازگردانده شود. شمارش از چپ‌ترین ستون در محدوده شروع می‌شود که شماره ۱ است.
•       range_lookup (اختیاری): تعیین می‌کند که آیا به‌دنبال تطابق تقریبی یا دقیق هستید:
  • TRUE یا خالی (پیش‌فرض): تطابق تقریبی. اگر تطابق دقیقی یافت نشود، فرمول به‌دنبال بزرگ‌ترین مقداری می‌گردد که کمتر از مقدار جستجو باشد. نیاز به مرتب‌سازی ستون جستجو به‌ترتیب صعودی دارد.
  • FALSE: تطابق دقیق. فرمول به‌دنبال مقداری دقیقاً برابر با مقدار جستجو می‌گردد. اگر تطابق دقیقی یافت نشود، مقدار #N/A بازگردانده می‌شود.

فرمول ساده VLOOKUP

در این بخش از آموزش VLOOKUP در اکسل، یک مثال از فرمول VLOOKUP را در ساده‌ترین شکل آن آورده‌ایم:

=VLOOKUP(“lion”, A2:B11, 2, FALSE)

•       آرگومان اول (lookup_value): به وضوح نشان می‌دهد که فرمول به‌دنبال کلمه “lion” می‌گردد.
•       آرگومان دوم (table_array): محدوده A2:B۱۱ است. با توجه به اینکه جستجو در چپ‌ترین ستون انجام می‌شود، می‌توانید فرمول را این‌گونه بخوانید: به‌دنبال “lion” در محدوده A2:A۱۱ بگردید.
•       آرگومان سوم (col_index_num): شماره ۲ است، به این معنی که می‌خواهیم مقدار مرتبط را از ستون B که دومین ستون در محدوده است، بازگردانیم.
•       آرگومان چهارم (range_lookup): مقدار FALSE است که نشان می‌دهد به‌دنبال تطابق دقیق هستیم.

با توجه به تمام آرگومان‌ها می‌توانید فرمول را به این شکل بخوانید: به‌دنبال “lion” در A2:A۱۱ بگرد، یک تطابق دقیق پیدا کن و مقداری از ستون B در همان ردیف بازگردان.

برای راحتی می‌توانید مقدار موردنظر را در یک سلول وارد کنید، مثلاً E۱ و متن ثابت را با مرجع سلول جایگزین کنید تا فرمول به‌دنبال هر مقداری که در E۱ وارد می‌کنید، بگردد:

=VLOOKUP(E1, A2:B11, 2, FALSE)

سپس سعی کنید به‌شکل زیر به آن نگاه کنید:

آموزش VLOOKUP در اکسل

هنگام استفاده از فرمول‌های VLOOKUP در ورک‌شیت‌های واقعی، قانون اصلی این است: محدوده جدول را با مراجع مطلق (مانند $A$2:$C$۱۱) قفل کنید تا از تغییر آن هنگام کپی‌کردن فرمول به سلول‌های دیگر جلوگیری کنید.

مرجع مطلق یا آدرس دهی مطلق طبق آنچه که در آموزش آدرس دهی در اکسل وب‌سایت ۲۴ وب لرن منتشر شده است، نوعی آدرس دهی است که در آن شما یک سلول یا محدوده خاص را «قفل» می‌کنید تا حتی با کپی‌کردن یا جابه‌جایی فرمول تغییر نکند. برای ایجاد یک آدرس مطلق، از علامت دلار ($) قبل‌از حرف ستون و/یا شماره سطر استفاده می‌کنید.

مقدار جستجو در اکثر موارد باید یک مرجع نسبی (مانند E۲) باشد یا می‌توانید فقط مختصات ستون را قفل کنید ($E۲). هنگامی که فرمول به پایین ستون کپی می‌شود، مرجع به‌طور خودکار برای هر ردیف تنظیم می‌شود.

برای دیدن نحوه عملکرد آن، مثال زیر را درنظر بگیرید. یک ستون دیگر به جدول نمونه اضافه کرده‌ایم که حیوانات را براساس سرعت رتبه‌بندی می‌کند (ستون A) و می‌خواهیم سریع‌ترین دونده‌های جهان را پیدا کنیم. برای این کار، رتبه‌های جستجو را در برخی سلول‌ها وارد کنید (E2:E۴ در تصویر زیر) و از فرمول‌های زیر استفاده کنید:

برای استخراج نام حیوانات از ستون B:

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)

برای استخراج سرعت از ستون C:

=VLOOKUP($E2, $A$2:$C$11, 3, FALSE)

فرمول‌های بالا را در سلول‌های F۲ و G۲ وارد کنید، آن‌ها را انتخاب کنید و فرمول‌ها را به ردیف‌های پایین‌تر بکشید:

اگر فرمول را در یک ردیف پایین‌تر بررسی کنید، متوجه خواهید شد که مرجع مقدار جستجو برای آن ردیف خاص تنظیم شده است؛ درحالی‌که محدوده جدول بدون تغییر باقی مانده است:

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

۵ نکته مهم درباره VLOOKUP در اکسل

نکات مهمی که باید درنظر بگیرید، عبارت‌اند از:

1. تابع VLOOKUP نمی‌تواند به‌سمت چپ نگاه کند. این تابع همیشه در چپ‌ترین ستون محدوده جدول جستجو می‌کند و مقداری را از ستونی در سمت راست بازمی‌گرداند. اگر نیاز به استخراج مقادیر از سمت چپ دارید، از ترکیب INDEX MATCH (یا INDEX XMATCH در اکسل ۳۶۵) استفاده کنید که به موقعیت ستون‌های جستجو و بازگشت اهمیتی نمی‌دهد.
2. تابع VLOOKUP به حروف بزرگ و کوچک حساس نیست؛ به این معنی که حروف بزرگ و کوچک به‌عنوان معادل درنظر گرفته می‌شوند. برای تمایز بین حروف بزرگ و کوچک از فرمول‌های VLOOKUP حساس به حروف استفاده کنید.
3. به اهمیت پارامتر آخر توجه کنید. از TRUE برای تطابق تقریبی و FALSE برای تطابق دقیق استفاده کنید.
4. هنگام جستجوی تطابق تقریبی مطمئن شوید که داده‌های ستون جستجو به ترتیب صعودی مرتب شده‌اند.
5. اگر مقدار جستجو یافت نشود، خطای #N/A بازگردانده می‌شود.

نحوه جستجوی VLOOKUP از یک شیت دیگر در اکسل

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

برای جستجوی VLOOKUP از یک شیت دیگر، نام شیت را به‌همراه علامت تعجب (!) در آرگومان table_array قبل‌از مرجع محدوده وارد کنید. به‌عنوان‌مثال، برای جستجو در محدوده A2:B۱۰ در شیت Sheet۲، از این فرمول استفاده کنید:

=VLOOKUP(“Product1”, Sheet2!A2:B10, 2)

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

به‌عنوان‌مثال، این‌گونه می‌توانید مقدار A۲ را در محدوده A2:A۹ در شیت Prices جستجو کنید و مقدار مرتبط را از ستون C بازگردانید:

=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)

به چند نکته زیر توجه کنید:

•       اگر نام شیت شامل فاصله یا کاراکترهای غیرالفبایی باشد، باید آن را در علامت تک‌کوتیشن (‘) قرار دهید، مانند ‘Price list’!$A$2:$C$۹.
•       درصورتی‌که از فرمول VLOOKUP برای چند سلول استفاده می‌کنید، به یاد داشته باشید که table_array را با علامت $ قفل کنید، مانند $A$2:$C$۹.

نحوه جستجوی VLOOKUP از یک فایل دیگر در اکسل

برای جستجوی VLOOKUP از یک فایل دیگر در اکسل، نام فایل را در کروشه ([]) قبل‌از نام شیت وارد کنید.

به‌عنوان‌مثال، فرمول جستجوی مقدار A۲ در شیت Prices در فایل Price_List.xlsx به‌صورت زیر است:

=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)

اگر نام فایل یا شیت شامل فاصله یا کاراکترهای غیرالفبایی باشد، باید آن‌ها را در تک‌کوتیشن (‘) قرار دهید، مانند:

=VLOOKUP(A2, ‘[Price List.xlsx]Prices’!$A$2:$C$9, 3, FALSE)

ساده‌ترین روش برای ساخت فرمول VLOOKUP که به یک فایل دیگر اشاره دارد، به‌صورت زیر است:

1. هر دو فایل را باز کنید.
2. فرمول خود را شروع کنید، به فایل دیگر بروید و محدوده جدول را با ماوس انتخاب کنید.
3. بقیه آرگومان‌ها را وارد کنید و کلید Enter را بزنید تا فرمول کامل شود.

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

پس‌از بستن فایل حاوی جدول جستجو، فرمول VLOOKUP همچنان کار خواهد کرد؛ اما اکنون مسیر کامل فایل بسته را نمایش می‌دهد:

چگونه از یک کاربر مبتدی به یک متخصص Excel تبدیل شوید؟

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

این دوره با بیش‌از ۹ ساعت محتوای آموزشی و ۱۸ ویدئوی باکیفیت به شما کمک می‌کند تا از یک کاربر مبتدی به یک کاربر حرفه‌ای تبدیل شوید و در شغل فعلی خود پیشرفت قابل‌توجهی کنید. یکی از مزایای بزرگ این دوره، دسترسی مادام‌العمر به تمام فایل‌ها و به‌روزرسانی‌ها است؛ بنابراین می‌توانید هر زمان که نیاز داشتید، به محتوای دوره دسترسی داشته باشید. همچنین با پشتیبانی دائم، هیچ‌گاه در مسیر یادگیری تنها نخواهید بود.

و حالا بهترین بخش این است که شما می‌توانید این دوره را با تخفیف ویژه و قیمت شگفت‌انگیز ۱۹۹,۰۰۰ تومان (به‌جای ۴۹۹,۰۰۰ تومان) خریداری کنید. این فرصت استثنایی را از دست ندهید. همین حالا از صفحه محصول دوره جامع اکسل برای شرکت وب‌سایت ۲۴ وب لرن دیدن کنید و با ثبت‌نام در دوره، قدم بزرگی به سوی ارتقای مهارت‌های خود بردارید.

سخن پایانی

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

سوالات متداول

•       آیا VLOOKUP فقط در یک جدول قابل استفاده است؟

خیر، VLOOKUP می‌تواند داده‌ها را از برگه‌ها و جداول مختلف جمع‌آوری کند.

•       تفاوت VLOOKUP با HLOOKUP چیست؟

VLOOKUP به صورت عمودی و HLOOKUP به صورت افقی در جداول جستجو می‌کند.

•       چگونه می‌توان خطاهای N/A# را در VLOOKUP برطرف کرد؟

استفاده از ترکیب فرمول‌های IFERROR یا IFNA می‌تواند به مدیریت خطاهای جستجو کمک کند.