هوش تجاری

تابع VLOOKUP در اکسل چیست و چگونه ایجاد می‌شود؟

تابع VLOOKUP یکی از چندین تابع جستجوی موجود در اکسل (Microsoft Excel) است که به شما امکان می‌دهد با توجه به استدلال‌هایی که استفاده می‌کنید، مطابقت تقریبی یا دقیق را از داده‌های سازمان‌یافته در قالب جدول، تعیین و برگردانید. VLOOKUP با داده‌های ذخیره شده عمودی کار می‌کند.

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

تابع VLOOKUP چیست و چگونه کار می‌کند؟

تعدادی ابزار جستجوی داخلی برای کاربران اکسل وجود دارد. اگر می‌خواهید بر داده خاصی تأکید کنید، می‌توانید قوانین قالب‌بندی مشروط (Conditional Formatting) را به صفحه گسترده خود اضافه کنید. اگر می‌خواهید به سرعت یک عدد یا یک رشته متنی پیدا کنید، می‌توانید از “find” و “select” استفاده کنید.

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

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

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

حتما بخوانید: پیاده سازی هوش تجاری در سازمان‌ها

تابع VLOOKUP در اکسل چگونه کار می‌کند؟

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

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

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

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

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

VLOOKUP(value, table, col_index_num, range_lookup)=

در این مثال، “مقدار” مورد قابل جستجو است که می‌خواهیم در جدول خود پیدا کنیم. به عنوان مثال، ما می‌توانیم به دنبال شماره شناسنامه دانشجویی متناسب (به عنوان مثال ۱۰۱۰) باشیم. اگر می‌خواستیم یک جعبه جستجو با ویرایش آسان ایجاد کنیم، می‌توانیم به مرجع سلولی مراجعه کنیم که حاوی این مقدار است (به عنوان مثال F3).

حتما بخوانید: چگونه تصمیمات داده‌محور باعث رشد کسب و کار می‌شود؟

نحوه نوشتن VLOOKUP

شما باید جدول حاوی داده‌های خود را مشخص کنید. این مسئله به طور معمول یک محدوده سلولی است، مانند A2:D11. هنگامی که VLOOKUP، سطر حاوی معیارهای مقدار جستجوی شما را پیدا کرد، سپس باید داده‌های آن سطر را که دوست دارید برگرداند، شناسایی کنید.

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

اکنون به ستون سمت راست نیاز دارد که به عنوان عدد به مقدار col_index_num نیاز دارد. با شروع شماره ۱ برای اولین ستون جدول خود، باید ستون سمت راست را مشخص کنید (به عنوان مثال، ۳ برای ستون C).

سرانجام، باید مشخص کنید که آیا می‌خواهید VLOOKUP یک مطابقت دقیق (اولین نتیجه مطابقت در یک جدول سازمان یافته و مرتب شده) برگرداند یا در عوض مطابقت تقریبی را برگردانید. برای تنظیم این مورد، باید مقدار range_lookup را به فرمول خود اضافه کنید.

در این قسمت فرمول فقط TRUE یا ۱ (برای محاسبات تقریبی) یا FALSE یا ۰ (برای محاسبات دقیق) در دسترس است. اگر مقدار range_lookup را ارائه ندهید، اکسل بصورت پیش‌فرض از TRUE استفاده می‌کند و در عوض یک مطابقت تقریبی را برمی‌گرداند، که ممکن است برای جستجوی اینچنینی مناسب نباشد.

اگر از مقادیر جستجوی wildcard استفاده می‌کنید، برای بازگرداندن مقدار صحیح، باید range_lookup را روی TRUE تنظیم کنید (برای مطابقت دقیق).

مواردی که قبل از استفاده از تابع VLOOKUP در اکسل باید در نظر گرفت.

قبل از تصمیم به استفاده از تابع VLOOKUP در اکسل، باید موارد زیر را در نظر داشته باشید:

VLOOKUP به دنبال داده در سمت راست هر مقدار جستجو است. تلاش برای یافتن داده‌ها در سمت چپ منجر به خطا می‌شود. با استفاده از توابع INDEX و MATCH یا با استفاده از تابع جدیدتر XLOOKUP می‌توانید، این مشکل را حل کنید.
VLOOKUP یک تابع جستجوی عمودی است. این بدان معناست که داده‌های شما باید در جدولی مرتب شوند که در آن هر سطر دارای اشکال مختلف (اما مرتبط) داده در هر ستون باشد. اگر می‌خواهید داده‌ها را در قالب‌های دیگر جستجو کنید، به جای آن از HLOOKUP،INDEX و MATCH یا XLOOKUP استفاده کنید.
به طور پیش‌فرض، VLOOKUP اولین مطابقت تقریبی را با مقدار جستجو برمی‌گرداند. با تنظیم یک آرگومان range_lookup در فرمول خود، در جایی که TRUE مطابقت تقریبی را بازمی‌گرداند در حالی که FALSE دقیقا مطابقت دارد، می‌توانید این مورد را تغییر دهید. می‌توانید از اعداد (۰ یا ۱) یا متن (FALSE یا TRUE) استفاده کنید.
مطابقت دقیق در فرمول‌های VLOOKUP در واقع به اولین مطابقت دقیق جدول اشاره دارد. این مسئله ممکن است برای جداول داده که در آنها مقدار جستجو چندین نتیجه مطابقت دارد، مشکل‌ساز باشد. بنابراین داده‌های خود را بر این اساس مرتب کنید.
اگر می‌خواهید یک مطابقت تقریبی را جستجو کنید، برای صحت نتایج باید داده‌های شما به ترتیب صعودی مرتب شوند.
دقت کنید که ستون‌های جدید را در جدول داده خود وارد کنید، زیرا این امر می‌تواند مقادیر col_index_num را در فرمول‌های موجود VLOOKUP شما شکسته کند.
از Wildcard می‌توان برای مقادیر جستجوی VLOOKUP با استفاده از ستاره یا علامت سوال استفاده کرد. برای کار صحیح با استفاده از ampersand برای پیوند دادن مقدار جستجو با wildcard در فرمول VLOOKUP خود به عنوان مثال VLOOKUP(value&“*”, table, col_index_num, range_lookup.
VLOOKUP برای جستجوی متن حساس به حروف بزرگ طراحی نشده است و اولین مقدار دقیق یا تقریبی مطابقت را بدون در نظر گرفتن متن مورد استفاده، برمی گرداند.