آموزش فارسی رایگان توابع VLOOKUP و HLOOKUP در اکسل ،تابع (VERTICAL LOOKUP) از پرکاربردترین و مهترین توابع اکسل بوده که یک جستجوی عمودی انجام داده و مقدار مبتنی بر یک انطباق و یا مقدار دقیق را بر می گرداند.این تابع با جداولی که ماهیت دیتابیسی دارند کار می کند. کسانی که در واحدهای برنامه ریزی شرکتها کار می کنند با این تابع زیاد سروکار دارند.
شکل زیر خلاصه ای از توابع جستجو و مرجع در اکسل را نشان میدهد:
آموزش فارسی رایگان توابع VLOOKUP و HLOOKUP در اکسل
توابع جستجو در اکسل
این دسته از توابع به منظور جستجو در بانک های اطلاعاتی مورد استفاده قرار میگیرند، منظور از جستجو صرفاً پیدا کردن یک عبارت نیست، توابع VLOOKUP و HLOOKUP در اکسل در واقع به کاربران در تکمیل بانک های اطلاعاتی و استفاده از آنها بسیار کمک میکنند، با استفاده از این توابع انجام بسیاری از کارها در اکسل راحت میشود.
تابع LOOKUP در اکسل
این تابع دو فرم آرایه ای و Vector دارد، فرم Vector این تابع مورد نظر ما میباشد. در اکسل به یک محدوده از سلول ها که شامل تنها یک سطر یا یک ستون باشد Vector میگوییم مثلاً محدوده های A1:A88 یا A1:M1 هر دو Vector هستند. تابع LOOKUP یک عبارت را در یک Vector جستجو میکند و در صورت پیدا کردن آن عبارت محتوای سلول هم موقعیت با سلول پیدا شده در سطر یا ستون مجاور Vector جستجو شده را به عنوان خروجی به کاربر میدهد.
سینتکس تابع LOOKUP به صورت زیر است:
LOOKUP (lookup_value, lookup_vector, result_vector)
آموزش فارسی رایگان توابع VLOOKUP و HLOOKUP در اکسل
آرگومان اول، که یک آرگومان اجباری میباشد، در واقع عبارت مورد نظر برای جستجو است. این آرگومان میتواند عدد، رشته متنی، Logical Values (شامل صفر و یک یا True و False) یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
آرگومان دوم، Vector محل جستجو میباشد که باز یک آرگومان اجباری است. این آرگومان یک محدوده از اکسل شامل یک سطر یا یک ستون میباشد که قرار است عبارت آرگومان اول در آن جستجو شود. سلول های محدوده ی Vector هم میتوانند حاوی اعداد یا رشته های متنی یا Logical Values (شامل صفر و یک یا True و False) باشند.
نکته ی بسیار مهم “Vector حتماً باید به صورت صعودی مرتب (Sort) شده باشد (صعودی مانند : از اعداد منفی به مثبت یا کوچکتر به بزرگتر یا از حرف A تا حرف Z یا False به True). در غیر اینصورت ممکن است تابع LOOKUP جواب صحیح را به ما ندهد”
آرگومان سوم، یک Vector مانند آرگومان اول و به همان اندازه میباشد، مثلاً اگر آرگومان دوم (lookup_vector) یک Vector افقی با ۵۵ سلول باشد، آرگومان سوم (result_vector) هم باید یک Vector افقی با ۵۵ سلول باشد. در واقع پس از یافتن عبارت مورد جستجو در Vector اول (lookup_vector)، تابع LOOKUP محتوای سلول هم تراز با سلول حاوی عبارت جستجو در result_vector را به عنوان خروجی ارائه میدهد.
اگر تابع LOOKUP نتواند عبارت مورد جستجو را در lookup_vector پیدا کند، آخرین (بزرگترین) مقدار در lookup_vector را که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن lookup_vector دقت کنید). و اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو lookup_vector باشد خروجی تابع خطای N/A# میباشد.
دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و result_vecto حتماً در کنار یکدیگر قرار داشته باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) برای بدست آوردن جواب کافیست.
آموزش فارسی رایگان توابع VLOOKUP و HLOOKUP در اکسل
تابع VLOOKUP در اکسل
تابع VLOOKUP یا Vertical LOOKUP مانند تابع LOOKUP عمل میکند، اگر منطق تابع LOOKUP و طریقه ی کار کردن با آن را بدانید درک VLOOKUP برای شما آسان تر خواهد بود، لذا حتماً بخش تابع LOOKUP را قبل از این قسمت بخوانید.
تابع VLOOKUP میتواند یک عبارت مورد نظر را در اولین ستون یک محدوده (سمت چپ ترین ستون یک محدوده در نوشتار چپ به راست) جستجو کند و پس از پیدا کردن عبارت مورد نظر محتوای سلول هم تراز (هم ردیف) با سلول پیدا شده در هریک از ستون های مجاور با ستون مورد جستجو را به عنوان خروجی اعلام نماید. در واقع کلمه Verticalدر نام این تابع به جستجو در ستون (یا Vector عمودی) اشاره میکند.
ساختار این تابع به صورت زیر است:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
تابع VLOOKUP چهار آرگومان دارد، سه آرگومان اول اجباری و آرگومان آخر اختیاری میباشد.
آرگومان اول عبارتی است که کاربر میخواهد جستجو کند، این آرگومان همانند آنچه در تابع LOOKUP وجود داشت، میتواند عدد، رشته متنی، آدرس سلول و یا یک مقدار منطقی (Logical Value) باشد.
آرگومان دوم یک محدوده از اکسل میباشد، تمام جدول داده ها به عنوان این آرگومان به تابع معرفی میگردد، بطور مثال محدوده ی A2:C10 به عنوان آرگومان دوم قرار میگیرد، همینطور میتوان نام محدوده را به عنوان آرگومان دوم نوشت.
همانند تابع LOOKUP، جستجو در تابع VLOOKUP تنها در یک ستون (ستون اول داده ها) یا به عبارتی در Vector اول صورت میگیرد، هر محدوده ای که به عنوان آرگومان دوم (table_array) به تابع معرفی شود، ستون اول (سمت چپ ترین ستون در چینش چپ به راست) به عنوان Vector محل جستجو برای عبارت مورد نظر استفاده میشود.
آرگومان سوم col_index_num، یک عدد میباشد و شماره ستون داده ی مورد نظر برای استخراج از جدول است، ستون شماره ۱ همان ستون یا Vector جستجو شده و ستون شماره ۲ ستون مجاور میباشد و به همین ترتیب.
اگر آرگومان سوم تابع VLOOKUP کمتر از یک باشد خروجی تابع خطای !VALUE# و اگر این عدد بزرگتر از تعداد کل ستون ها باشد خروجی تابع خطای !REF# خواهد بود.
آرگومان چهارم یک آرگومان اختیاری و بسیار مهم است، این آرگومان Boolean میباشد و میتواند True یا False باشد. اگر این آرگومان True یا ۱ باشد یا نادیده گرفته شود.
آموزش فارسی رایگان توابع VLOOKUP و HLOOKUP در اکسل
در اینصورت تابع VLOOKUP رفتار زیر را انجام میدهد:
اولاً، داده های Vector مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
دوماً، تابع در ستون اول به دنبال عبارت مورد جستجو میگردد و در صورت نیافتن آن عبارت، دقیقاً مانند آنچه در تابع LOOKUP دیدیم، بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ میپذیرد.
سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول (Vector جستجو شده) پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن داده ها دقت کنید).
حال اگر آرگومان چهارم False باشد، False برای تابع به معنی Exact Match است یعنی کاربر به دنبال داده ای دقیقاً منطبق بر عبارت مورد جستجو میگردد و در این حالت تابع تنها داده ای را به عنوان پاسخ میپذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.
در صورتی که آرگومان چهارم False باشد نیازی به چینش صعودی داده های ستون مورد جستجو نیست در صورت وجود داشتن چند جواب، اولین مورد پیدا شده به عنوان جواب پذیرفته میشود.
آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.
آموزش فارسی رایگان توابع VLOOKUP و HLOOKUP در اکسل
تابع HLOOKUP در اکسل
تابع HLOOKUP یا Horizontal LOOKUP، در ساختار و روش عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد و تنها تفاوت آنها در افقی و عمودی بودن داده ها میباشد، تابع HLOOKUP برای جدول های افقی کاربرد دارد و سطر اول داده ها را به عنوان Vectorجستجو میکند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.
پایگاه دانش، علیرضا مقربی
