تابع VLOOKUP در اکسل: آشنایی با جزییات و نحوه کارکرد

vlookup-function-in-excel

تابع vlookup در اکسل، به جست و جو در نخستین ستون جدول lookup (جدول مورد جستجو) پرداخته و پس از یافتن مقدار نخستین آرگومان تابع، مقدار متناظرش (از لحاظ ردیفی) در ستون مشخص شده ی دیگر جدول (در آرگومان سوم) را باز می گرداند.

جدول lookup به صورت عمودی چیده شده است (که V گویای آن در نام تابع است). سینتکس این تابع، به شکل روبه رو است:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

آرگومان های تابع VLOOKUP به این شکل است:

  • Lookup_value (مقدار lookup): مقداری که باید از نخستین ستون جدول lookup یافت شود.
  • Table_array (جدول lookup): بازه ای که دربردارنده ی جدول مورد جست وجوی lookup است.
  • Col_index_num (شماره ستون محل استخراج نتیجه): شماره ستونی از جدول، که باید مقدار سلول متناظر در ردیف مقدار یافت شده در ستون ۱ در آن جای دارد، به عنوان خروجی تابع، برگشت داده شود.
  • Range_lookup: (اختیاری): اگر این آرگومان را TRUE گذاشته یا خالی رها کنید، یک تطبیق تقریبی ازگردانده می شود. (یعنی اگر تطبیق دقیقی در ستون ۱ یافت نشود [دقیقا همان مقدار یافت نشود]، بزرگترین مقدار بعدی که کمتر از آرگومان ۱ است بازگردانده می شود، یعنی شبیه ترین مقدار به مقدار lookup_value). اگر FALSE را در این آرگومان وارد کنید، تابع به دنبال دقیقا همان عبارت آرگومان lookup_value می گردد نه چیزی کمتر و نه بیشتر (تطبیق دقیق). اگر تابع نتواند چیزی در ستون ۱ بیابد، خروجی آن #N/A خواهد شد.
اگر آرگومان range_lookup را روی TRUE تنظیم کنید یا خالی رها سازید، نخستین ستون جدول lookup را باید به ترتیب افزایشی (ascending) مرتب سازید. چنانچه مقدار lookup در آرگومان ۱ کوچکتر از کمترین مقدار ستون ۱ جدول lookup باشد، تابع VLOOKUP خروجی #N/A را می دهد. اگر آرگومان آخر را روی FALSE بگذارید، ستون ۱ جدول نباید ترتیبی افزایشی داشته باشد. اگر تطبیقی دقیق یافت نشود، خروجی تابع، #N/A خواهد بود. و چنانچه نخستین آرگومان VLOOKUP، یعنی مقدار lookup، متن باشد و آرگومان آخر، یعنی range_lookup روی FALSE تنظیم شده باشد، در آرگومان مقدار lookup می توان از کاراکترهای * و ؟ استفاده کرد.

یک مثال درباره تابع vlookup

یکی از مرسوم ترین کاربردهای فرمول lookup، در برنامه ریزی نرخ مالیات بر درآمد است با استفاده از اکسل است (شکل زیر). برنامه ریزی نرخ مالیات، نرخ های مالیات درآمد سطوح گوناگون درآمدی را نشان می دهد(فایل این مثال را می توانید از انتهای متن دانلود کنید). 

آشنایی با جزییات و نحوه کارکرد تابع VLOOKUP

فرمول زیر که در سلول B3 وارد شده است، نرخ مالیات مربوط به درآمد موجود در سلول B2 را بازمی گرداند:

=VLOOKUP(B2,D2:F7,3)

جدول lookup در بازه ای که دارای سه ستون است (D2:F7) خانه گزیده است. چون آخرین آرگومان تابع VLOOKUP در این فرمول، عدد ۳ است، در حقیقت آرگومان چهارم آن خالی رها شده، و این، آرگومان سوم تابع می باشد؛ به این معنا که باید مقدار متناظر در ستون سوم جدول lookup برگردانده شود.

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

اجازه دهید با اعداد روی شکل صحبت کنیم.

جدول lookup که در شکل می بینید، دارای ۳ ستون است. ستون ۱و۲ آن که ترتیبی افزایشی دارد، حدود درآمد را نشان می دهد. مثلا ردیف ۲ می گوید، درآمدی که بزرگ تر یا مساوی ۰ و کوچک تر از ۲۶۵۰ دلار باشد، مالیاتی برابر با ۱۵% دارد.

آرگومان ۱ تابع، مقدار ۴۵٫۵۰۰ است  (B2). ستون ۱ جدول نیز به ترتیب افزایشی چیدمان شده است و می بینید که دارای این مقدار نیست. بزرگ ترین عددی از این ستون درآمد که از خود آن کوچک تر باشد، مقدار درآمد ۲۷٫۳۰۱ است که در ردیف ۴ مستقر شده است. محل تقاطع این ردیف با ستون ۳ جدول (مالیات که آرگومان ۳ تابع است)، عدد ۳۱% است که خروجی تابع می باشد. با نگاه سطحی نیز می توان فهمید که عدد ۴۵٫۵۰۰ میان اعداد ۲۷٫۳۰۱ و ۵۸٫۵۰۰ است، یعنی ردیف ۴ کاربرگ. اما در جدول های بزرگ به ویژه پایگاه داده ای، به صورت چشمی نمی توان این کار را کرد و نیاز به خودکارسازی است.

اکنون که زیبایی تابع VLOOKUP در اکسل را درک نمودید، توان انجام هر کاری را با این تابع خواهید داشت. یعنی جدولی دارید که قرار است مقدار متناظر با یکی از مقادیر ستون ۱ آن را که در ستونی دیگر جا خوش کرده، شناسایی نمایید. مقدار مورد جست و جوی lookup را در سلولی خارج از جدول به عنوان نقطه ای ثابت برای آرگومان ۱ آن وارد می کنید (در اینجا B2). فرمول را در سلولی دیگر در کنار آن می نویسید. خروجی را در محل تابع خواهید دید. برای فراموش نکردن آن در مراجعه های بعدی، کنار این دو، توضیحات هر یک را وارد می کنید. در اینجا، Enter Income می گوید ” درآمد را وارد کن” و عبارت The Tax Rate Is: می گوید “نرخ مالیات این درآمد:”

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

دانلود فایل اکسل این مثال

دانلود

ویدوئوی آموزشی کار با تابع vlookup در نرم افزار اکسل

پست های مرتبط

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

*