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

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

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

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

آرگومان‌های تابع vlookup در اکسل

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

• lookup_value: در اینجا باید مقدار مورد نظر برای جست‌جو را وارد کنیم و باید این مقدار در ستون اول محدوده table_array وجود داشته باشد.
• table_array: این آرگومان همان جدول داده و یا محدوده برای جستجو است و دو ستون، مقدار مورد جست‌جو و مقدار نتیجه در آن قرار دارند.
• آرگومان دیگر در آموزش تابع vlookup در اکسل col_index_num است: شماره ستونی که مقدار متناظر یا تطبیقی از آن بر‌گردانده می‌شود در اینجا مشخص می‌شود. در صفحه زمانی که تنظیمات صفحه از چپ به راست باشد از ستون سمت چپ محدوده شماره ستون‌ها با 1 شروع می‌شود.
• [range_lookup]: این آرگومان اجباری نیست و از طریق یک مقدار منطقی مشخص می‌کند که مقدار دقیق یا مقدار تقریبی توسط تابع بر‌گردانده شود. اگر یک مقدار تقریبی توسط تابع مورد جست‌جو باشد این آرگومان با 1 یا True جاگذاری می‌شود و تابع نزدیک‌ترین مقدار را جست‌جو می‌کند. اگر بخواهیم تابع مقدار دقیق را برگرداند این آرگومان را با 0 یا False جاگذاری می‌کنیم.

مثال ‌جستجوی دقیق با تابع vlookup در اکسل

برای انجام جستجو یا تطبیق دقیق با استفاده از تابع VLOOKUP در اکسل، باید در بخش [range_lookup] مقدار False را وارد نمایید. در یک مثال که داده‌های مربوط به نمرات دانش‌آموزان را شامل می‌شود، ستون‌های Math، Name، ID و Chemistry به ترتیب نمره ریاضی، نام، شناسه دانش‌آموز و نمره شیمی را شامل می‌شود. اگر می‌خواهید بر اساس شماره شناسه خاص، نمرات ریاضی را پیدا کنید، باید مراحل زیر را دنبال کنید.

برای محاسبه از فرمول =VLOOKUP(G3,$B$2:$E$8,3,FALSE) در یک سلول خالی استفاده کنید. برای اعمال این فرمول بر روی سلول‌های دیگر در ستون، به راحتی می‌توانید از قابلیت کپی و Paste در اکسل استفاده کنید و یا کافیست روی گوشه پایین سمت راست سلول فعلی حرکت کنید تا فرمول به شکل + تغییر کند، سپس آنرا به سمت پایین حرکت دهید تا فرمول در سلول‌های پایین‌تر نیز تعمیم داده شود. در این فرمول، G3 مقداری است که برای جستجو استفاده می‌شود و تابع مقدار متناظر با آن را از ستون سوم (ریاضی) محدوده جستجوی B2:E8 برمی‌گرداند.

عدد ستون که مقدار متناظر با آن برگردانده می‌شود، سه است. برای اطمینان از بازگشت مقدار دقیق، مقدار FALSE را وارد کرده‌ایم، همانطور که پیش‌تر اشاره شده است، اگر مقدار مورد نظر در محدوده داده‌ها یافت نشود، تابع خطای “#N/A” را نمایش می‌دهد.

اگر تابع VLOOKUP نتوانست مقداری که اطمینان داریم وجود دارد را پیدا کند، چند دلیل می‌تواند وجود داشته باشد. این ممکن است به دلیل وجود یک یا چند فاصله خالی در داخل سلول باشد که با استفاده از تابع TRIM باید آن‌ها را حذف کنید. باید در نظر داشت که عدد 1 با متن “1” یکسان نیست؛ ممکن است شما در تابع عدد یک را نوشته باشید، اما در جدول، آنها به عنوان متن در نظر گرفته شود. همچنین، بررسی صحت وجود حروف “ی” و “ک” را نیز انجام دهید.

مثال ‌جستجوی تقریبی با vlookup در اکسل

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

فرمول =VLOOKUP(E4,$B$3:$C$9,2,TRUE) را در سلول مورد نظر وارد کنید و سپس آن را به سایر سلول‌ها تعمیم دهید. با توجه به مقادیر داده شده، این فرمول مقدار تطبیقی را محاسبه می‌کند. سلول E4 مقداری را به ما می‌دهد که بر اساس آن، مقدار تقریبی محاسبه می‌شود. محدوده‌ی داده‌هایی که مورد جست‌وجو قرار می‌گیرد توسط محدوده B3:C9 مشخص می‌شود. شماره ستونی که مقدار تقریبی از آن برگشت داده می‌شود توسط عدد 2 مشخص می‌شود و مقدار TRUE تطبیق تقریبی را مشخص می‌کند.

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