توابع VLOOKUP و HLOOKUP در اکسل

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

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

به عنوان یادآوری، شکل زیر خلاصه ای از توابع جستجو و مرجع در اکسل را نشان میدهد:

فهرست توابع جستجو و مرجع در اکسل

توابع جستجو در اکسل

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

توابع VLOOUP و HLOOKUP در اکسل

تابع LOOKUP در اکسل

این تابع دو فرم آرایه ای و Vector دارد، فرم Vector این تابع مورد نظر ما میباشد. در اکسل به یک محدوده از سلول ها که شامل تنها یک سطر یا یک ستون باشد Vector میگوییم مثلاً محدوده های A1:A88 یا A1:M1 هر دو Vector هستند. تابع LOOKUP یک عبارت را در یک Vector جستجو میکند و در صورت پیدا کردن آن عبارت محتوای سلول هم موقعیت با سلول پیدا شده در سطر یا ستون مجاور Vector جستجو شده را به عنوان خروجی به کاربر میدهد. سینتکس تابع LOOKUP به صورت زیر است:

LOOKUP (lookup_value, lookup_vector, result_vector)

  • آرگومان اول، که یک آرگومان اجباری میباشد، در واقع عبارت مورد نظر برای جستجو است. این آرگومان میتواند عدد، رشته متنی، 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 در اکسل

دقت داشته باشید که لزومی ندارد دو محدوده lookup_vector و  result_vecto مانند مثال بالا، حتماً در جوار یکدیگر قرار داشته باشند، بلکه صرفاً هم اندازه بودن این دو محدوده (Vector) برای بدست آوردن جواب کافیست.

توابع VLOOUP و HLOOKUP در اکسل

تابع 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# خواهد بود.

توابع VLOOUP و HLOOKUP در اکسل

آرگومان چهارم یک آرگومان اختیاری و بسیار مهم است، این آرگومان Boolean میباشد و میتواند True یا False باشد. اگر این آرگومان True یا ۱ باشد یا نادیده گرفته شود، در اینصورت تابع VLOOKUP رفتار زیر را انجام میدهد:

  • اولاً، داده های Vector مورد جستجو (ستون اول) باید همانند آنچه در تابع LOOKUP وجود داشت به صورت صعودی مرتب شده باشند تا مطمئن باشیم که خروجی تابع قابل اعتماد است.
  • دوماً، تابع در ستون اول به دنبال عبارت مورد جستجو میگردد و در صورت نیافتن آن عبارت، دقیقاً مانند آنچه در تابع LOOKUP دیدیم، بزرگترین مقدار کوچکتر از عبارت مورد جستجو را به عنوان پاسخ میپذیرد.
  • سوماً، مجدداً مشابه تابع LOOKUP، اگر تابع VLOOKUP نتواند عبارت مورد جستجو را در ستون اول (Vector جستجو شده) پیدا کند، آخرین (بزرگترین) مقدار در آن Vector که برابر یا کوچکتر از عبارت مورد جستجو میباشد را به عنوان نتیجه ی جستجو میپذیرد (به صعودی بودن داده ها دقت کنید).

حال اگر آرگومان چهارم False باشد، False برای تابع به معنی Exact Match است یعنی کاربر به دنبال داده ای دقیقاً منطبق بر عبارت مورد جستجو میگردد و در این حالت تابع تنها داده ای را به عنوان پاسخ میپذیرد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.

در صورتی که آرگومان چهارم False باشد نیازی به چینش صعودی داده های ستون مورد جستجو نیست در صورت وجود داشتن چند جواب، اولین مورد پیدا شده به عنوان جواب پذیرفته میشود.

آرگومان چهارم هرچه که باشد (True یا False)، مانند تابع LOOKUP، اگر عبارت مورد جستجو، کوچکتر از کوچکترین عضو ستون مورد جستجو (Vector) باشد خروجی تابع خطای N/A# است.

توابع VLOOUP و HLOOKUP در اکسل

تابع HLOOKUP در اکسل

تابع HLOOKUP یا Horizontal LOOKUP، در ساختار و روش عملکرد هیچ تفاوتی با تابع VLOOKUP ندارد و تنها تفاوت آنها در افقی و عمودی بودن داده ها میباشد، تابع HLOOKUP برای جدول های افقی کاربرد دارد و سطر اول داده ها را به عنوان Vectorجستجو میکند. با یادگیری و فهم توابع LOOKUP و VLOOKUP مشکلی در کار با تابع HLOOKUP نخواهید داشت.

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۱۲ نظر

  1. اسماعیل

    سلام واقعا بابت آموزش های با ارزشتون ممنونم فقط میشه لطف کنید آموزش ها رو به صورت pdf بذارید تا بشه راحت دانلود کرد ؟
    ممنون

    1. حامد قدیمی

      سلام، خواهش میکنم، برای پرینت مقالات به صورت PDF میتوانید از قابلیت Browser خود استفاده نمایید. موفق باشید.

  2. وحید

    سلام
    وقتی از تابع VLOOKUP استفاده میکنیم و در قسمت اخر فرمول، ۰ می گذاریم در این حالت تابع تنها داده ای را به عنوان پاسخ به ما میدهد که دقیقاً مانند عبارت مورد جستجو باشد و اگر آن را پیدا نکرد خروجی تابع برابر خطای N/A# خواهد بود.
    حالا اگه بخواهیم به جای نمایش خطا سلول ان را خالی بگذارد باید چکار کنیم !؟
    چون نمایش خطا در سلول باعث می شود مابقی محاسبات که از این ستون فرمول می گیرند دچار مشکل شوند.
    ممنون

    1. حامد قدیمی

      سلام، برای این کار باید از تابعی مانند IFERROR استفاده نمایید. لطفاً توضیحات مربوط به این تابع را در مقاله توابع شرطی مطالعه نمایید. موفق باشید.

  3. شهریار

    ممنون از راهنمایتون
    یه سوال هم داشتم ؟
    مثلا من میخوام از ستون اول دنبال عدد مورد نظر من بگرده و وقتی پیدا کرد تمام اطلاعات سطر مقابلش رو ببره اون سطری که من میخوام ممنون میشم اگه راهنمایی کنید

    1. حامد قدیمی

      خواهش میکنم، شما باید از توابع جستجو و مرجع استفاده کنید. مخصوصاً تابع VLOOKUP برای این کار مناسب است. موفق باشید.

  4. حسن زرشام فر

    با سلام و احترام
    یک جدول اکسل شامل نوع دستگاه و شماره سریال و قیمت موجود است برای تهیه لیست اقلام و مانده گیری مطلوبست نام قطعات بدون تکرار استخراج شود آیا راه حلی وجود دارد ؟

    1. حامد قدیمی

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

  5. آزاده

    ممنون بابت اطلاعات مفیدی که در پیجتون قرار میدید
    اگر در جدول مرجعمون آرگمان اول تکراری داشته باشه چجوری میتونیم با استفاده از vlookup گزارش گیری داشته باشیم که هر یک از تکرارها در سطر مجزا بیاد؟
    مثلا اگر جدول مرجعمان این باشد: که جدول انواع درخواستهای پرسنل هست. و گزارش گیری اینجوری باشه که میخوام بدونم مثلا کد پرسنل ۱۰چند بار مراجعه کرده و هر بار چه درخواستی داشته
    کد پرسنل(آرگمان اول)- تاریخ درخواست-شرح

    1. حامد قدیمی

      سلام، خواهش میکنم، به این نکته حتماً توجه داشته باشید که خروجی هر تابع تنها و تنها یک عبارت است، یعنی خروجی تابعی نمیتواند چند عبارت باشد، مگر اینکه فرمول به صورت ارایه ای و در چند سلول نوشته شود. بنابراین شما باید تابعی بنویسید که تعداد تکرار شدن را استخراج نماید، و تابع VLOOKUP همیشه تنها اولین مورد یافت شده را به عنوان خروجی میپذیرد، از ترکیب توابع INDEX و MATCH میتوانید برای این منظور استفاده کنید. لطفاً امحان کنید. موفق باشید.

  6. sepehr

    سلام
    من یک فایل اکسل دارم که میخام بین دو شیت ارتباط از طریق تابع ایجاد کنم به این صورت که در شیت A یک جدول دارم دارای ۵ ستون که هر ستون به نام یک اپراتور خط می باشد هر اپراتور از هر کد تولید یک تعداد نامشخص در طی روز تولید می کند من میخام زمانی که در شیتAداده ای ثبت میکنم در شیت B به صورت فیش حقوقی ثبت بشه و به من بگه از هر کد چه تعدادی هر نفر زده اند.
    ممنون میشم کمکم کنید.

    1. حامد قدیمی

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

دیدگاهتان را بنویسید؟