توابع Lookup & Reference در اکسل
این دسته از توابع به منظور جستجو یا Lookup در بانک های اطلاعاتی مورد استفاده قرار میگیرند، منظور از جستجو صرفاً پیدا کردن یک عبارت نیست، توابع VLOOKUP و HLOOKUP در اکسل در واقع به کاربران در تکمیل بانک های اطلاعاتی و استفاده از آنها بسیار کمک میکنند، با یاری گرفتن از این توابع انجام بسیاری از کارها در اکسل راحت میشود. تابع VLOOKUP در اکسل به منظور جستجوی یک عبارت در یک جدول و یافتن اطلاعات متناظر با آن به کار برده میشود. تصور کنید فهرستی از پرسنل شرکت خود دارید که شامل مشخصات و شماره تماس است، برای یافتن شماره تماس مربوط به یک شخص با استفاده از نام و نام خانوادگی میتوان از تابع VLOOKUP در اکسل استفاده نمود.
تابع LOOKUP در اکسل
این تابع دو فرم آرایه ای و Vector دارد، فرم Vector این تابع مورد نظر ما میباشد. در اکسل به یک محدوده از سلول ها که شامل تنها یک سطر یا یک ستون باشد Vector میگوییم مثلاً محدوده های A1:A88 یا A1:M1 هر دو Vector هستند. تابع LOOKUP یک عبارت (یک عدد) را در یک Vector جستجو میکند و در صورت پیدا کردن آن عبارت محتوای سلول هم موقعیت با سلول پیدا شده در سطر یا ستون مجاور Vector جستجو شده را به عنوان خروجی به کاربر میدهد. سینتکس تابع LOOKUP به صورت زیر است:
LOOKUP (lookup_value, lookup_vector, result_vector)
- آرگومان اول، که یک آرگومان اجباری میباشد، در واقع عبارت مورد نظر برای جستجو است. این آرگومان میتواند عدد، رشته متنی، Logical Values شامل صفر و یک یا True و False یا آدرس یک سلول حاوی عبارت مورد نظر باشد.
به خاطر داشته باشید که از تابع LOOKUP به خاطر روش عملکرد آن تنها برای جستجوی اعداد استفاده نمایید. در واقع آرگومان اول یک عدد باید باشد علیرغکم اینکه سایر انواع داده ها نیز میتوانند انتخاب شوند.
- آرگومان دوم، Vector محل جستجو میباشد که باز یک آرگومان اجباری است. این آرگومان یک محدوده از اکسل شامل یک سطر یا یک ستون میباشد که قرار است عبارت آرگومان اول در آن جستجو شود. سلول های محدوده Vector هم میتوانند حاوی اعداد یا رشته های متنی یا Logical Values شامل صفر و یک یا True و False باشند.
- مجدداً تاکید میشود تابع LOOKUP برای اعداد مورد استفاده قرار گیرد زیرا به علت روش عملکرد آن به کابردن این تابع برای دادههای غیر عددی پاسخ درست نمیدهد.
نکته بسیار مهم اینست که 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 در اکسل
تابع VLOOKUP در اکسل یا Vertical LOOKUP مانند تابع LOOKUP عمل میکند، اگر منطق تابع LOOKUP و طریقه ی کار کردن با آن را بدانید درک VLOOKUP برای شما آسان تر خواهد بود، لذا حتماً بخش تابع LOOKUP را قبل از این قسمت بخوانید.
تابع VLOOKUP در اکسل میتواند یک عبارت مورد نظر را در اولین ستون یک محدوده (سمت چپ ترین ستون یک محدوده در نوشتار چپ به راست) جستجو کند و پس از پیدا کردن عبارت مورد نظر محتوای سلول هم تراز (هم ردیف) با سلول پیدا شده در هریک از ستون های مجاور با ستون مورد جستجو را به عنوان خروجی اعلام نماید. در واقع کلمه Verticalدر نام این تابع به جستجو در ستون (یا Vector عمودی) اشاره میکند. دقت داشته باشید تابع VLOOKUP عبارت مورد جستجو را تنها در ستون اول محدوده انتخاب شده جستجو میکند لذا باید در انتخاب محدوده دقت نمایید، از طرفی به “چپ به راست” بودن یا “راست به چپ” بودن نیز دقت کنید، زیرا ستون اول از نظر اکسل در چنش “چپ به راست”، سمت چپ ترین و در چینش “راست به چپ” سمت راست ترین ستون است.
برای درک بهتر این تابع، جدول زیر را در نظر بگیرید، ستون اول شماره شناسایی پرسنل، ستون دوم واحد محل کار هر نفر و ستون سوم نام شخص میباشد، میخواهیم در یک سلول فرمولی بنویسیم که با گرفتن شماره شناسایی هر فرد نام آن فرد را به عنوان خروجی بدهد، برای این کار میتوانیم از VLOOKUP کمک بگیریم.
ساختار این تابع به صورت زیر است:
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 رفتار زیر را انجام میدهد:
- اولاً، داده های Vector مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
- دوماً، تابع در ستون اول به دنبال عبارت مورد جستجو میگردد و در صورت نیافتن آن عبارت، دقیقاً مانند آنچه در تابع LOOKUP دیدیم، بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ میپذیرد.
- سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول Vector جستجو شده پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن داده ها دقت کنید).
حال اگر آرگومان چهارم تابع VLOOKUP در اکسل برابر False باشد، False برای تابع به معنی Exact Match است یعنی کاربر به دنبال داده ای دقیقاً منطبق بر عبارت مورد جستجو میگردد و در این حالت تابع تنها داده ای را به عنوان پاسخ میپذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.
در صورتی که آرگومان چهارم تابع VLOOKUP در اکسل False باشد نیازی به چینش صعودی داده های ستون مورد جستجو نیست در صورت وجود داشتن چند جواب، اولین مورد پیدا شده به عنوان جواب پذیرفته میشود.
در جستجوی اعداد آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.
تابع HLOOKUP در اکسل
تابع HLOOKUP یا Horizontal LOOKUP، در ساختار و روش عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد و تنها تفاوت آنها در افقی و عمودی بودن دادهها میباشد، تابع HLOOKUP برای جدول های افقی کاربرد دارد و سطر اول داده ها را به عنوان Vector جستجو میکند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.
