شرح تابع HLOOKUP

درجه اهمیت: بسیار کاربردی

تابع HLOOKUP یک ورودی موجود در اولین ردیف از بالای یک جدول یا آرایه را پیدا کرده و مقداری که در ردیف مورد نظر دیگری که از نظر جایگاه ستون با آن مقدار ورودی همتراز باشد را بازمی‌گرداند. این تابع در اکسل در طبقه توابع Lookup/Reference قرار دارد.

نوشتار این تابع به صورت زیر است:

=HLOOKUP( دقت جستجو , شماره ردیف مورد نظر , بازه جدول یا آرایه , مقدار جستجو )

ورودی های این تابع به شرح زیر هستند

مقدار مورد جستجو :   مقداری را که میخواهید جستجو کنید.

بازه جدول یا آرایه :   بازه ای از جدول یا یک آرایه که مقدار مورد جستجو در اولین ردیف از بالای آن باشد (که میخواهیم در این بازه جستجو صورت گیرد).

شماره ردیف مورد نظر :   شماره ردیفی که مقدار مورد نظر ما به عنوان خروجی در آن قرار دارد (این ردیف باید در بازه جدول یا آرایه موجود باشد).

[دقت جستجو]:   وارد کردن این مورد اختیاری است و تعیین می‌کند که در صورتی که مقداری دقیقا مطابق با مقدار مورد جستجو یافت نشد چه مقداری بازگردانده شود. دقت جستجو باید به صورت TRUE یا FALSE وارد ‌شود (در صورت خالی گذاشتن این قسمت به طور پیشفرض TRUE در نظر گرفته می‌شود) که معنای هر کدام داخل این تابع در زیر توضیح داده شده است:

TRUE: اگر تابع نتواند دقیقا مقداری مطابق با مقدار مورد جستجو را پیدا کند، به عنوان خروجی نزدیکترین مقدار کمتر از  مقدار مورد جستجو را بازمی‌گرداند (برای این کار اولین ردیف از بالای جدول یا آرایه باید از چپ به راست افزایشی باشد).

FALSE: : اگر تابع نتواند دقیقا مقداری مطابق با مقدار مورد جستجو را پیدا کند، یک خطا بازگردانده می‌شود.

(برای درک بهتر این ورودی به مثال ۲ مراجعه کنید)

آنچه که تابع باز می گرداند

تابع HLOOKUP یک مقدار را بازمی‌گرداند (که می‌تواند هرگونه دیتا-تایپی نظیر عددی، متنی یا زمانی باشد).

نکات

– عملکرد تابع HLOOKUP به  صورت افقی است. برای استفاده از تابع LOOKUP به صورت عمودی باید از تابع VLOOKUP استفاده کنید.

– همانطور که گفته شد تابع HLOOKUP به عنوان مقدار مورد جستجو، تنها ردیف اول از بالا را مد نظر قرار میدهد و اگر آن مقدار در آن ردیف نباشد با پیغام خطای #N/A مواجه می‌شویم.

– به جای مقدار منطقی FALSE می‌توان صفر وارد کرد (که همان معنی FALSE را می‌دهد)، و به جای TRUE هر عددی غیر صفر می‌تواند قرار گیرد.

برای دانلود مجموعه کامل راهنمای توابع به همراه فایل های مثال در اکسل به لینک کتاب «راهنما و مثال های توابع مایکروسافت اکسل» رجوع شود.

مثال های تابع HLOOKUP

مثال ۱

در مثال زیر نحوه کاربری تابع HLOOKUP را مشاهده می‌کنید.

در مثال بالا میخواهیم نمره زبان انگلیسیِ احمد مظفری را به عنوان خروجی داشته باشیم. برای این کار ابتدا به عنوان “مقدار مورد جستجو” نام وی یا سلول حاوی نام وی را وارد می‌کنیم. سپس به عنوان “بازه جدول یا آرایه” محدوده ای که می‌خواهیم مورد جستجو قرار گیرد را به شکلی که مشاهده می‌کنید وارد می‌کنیم. توجه داشته باشید که این محدوده را باید طوری تنظیم کنیم که ردیف مورد نظر که قرار است از آن خروجی گرفته شود در آن وجود داشته باشد و همچنین ردیفی که نام احمد مظفری (مقدار مورد جستجو) در آن وجو دارد اولین ردیف از بالای جدول باشد. بعد از آن به عنوان “شماره ردیف مورد نظر” به ترتیب از بالا به پایین ردیف هایی که در بازه جدولی که در مرحله قبل تعیین کردیم را می‌شماریم و شماره ردیفی که خروجی مورد نظر ما در آن وجود دارد را وارد میکنیم. در مرحله آخر در قسمت “دقت جستجو” مقدار FALSE را وارد کردیم تا تابع دقیقا مقدار مورد نظر ما را پیدا کند و در صورت عدم وجود آن پیغام خطا دهد (که در مثال بالا مقدار مورد نظر یافت شده که برابر با ۹۴% است). در ادامه همین کار را برای استخراج نمره ادبیات فارسیِ علی بهرامی انجام دادیم.

 

مثال ۲

در مثال بالا مقداری که دقیقا مطابق با مقدار مورد جستجو باشد وجود ندارد. در ردیف ۶ هیچ مقداری برای “دقت جستجو” وارد نشده و به صورت پیشفرض TRUE در نظر گرفته شده است که در نتیجه در قسمت خروجی میوه ای که نزدیکترین قیمت را به مقدار مورد جستجوی ما دارد و در عین حال ارزانتر است بازگردانده شده است. همانطور که مشاهده می‌کنید توت فرنگی به قیمت مورد نظر ما نزدیکتر است، ولی چون قیمت آن بالاتر است به جای آن طالبی بازگرداند شده است که هم نزدیک به قیمت ما باشد (نسبت به لیست ما) و هم ارزانتر باشد. دقت کنید که در این لیست مقادیر ردیف ۲ که بیانگر قیمت است به صورت افزایشی از چپ به راست تنظیم شده تا هنگام استفاده از TRUE با پیغام خطا مواجه نشویم.

در ردیف ۸ مقدار منطقی FALSE وارد شده و از آنجاییکه هیچ مقداری دقیقا برابر با مقدار مورد جستجوی ما یافت نشده است با پیغام خطا مواجه شدیم.

 

برای یادگیری نرم افزار اکسل به صورت کاربردی به دوره «آموزشی اکسل کاربردی» رجوع شود.

برای یادگیری برنامه نویسی به زبان VBA به دوره «آموزش VBA در اکسل» مراجعه نمایید.

خطاهای معمول

#VALUE! :  ۱) شماره ردیف مورد نظر کمتر از ۱ بوده و یا یک مقدار عددی نیست.   ۲) مقدار وارد شده برای قسمت دقت جستجو TRUE یا FALSE نبوده و یا یک مقدار عددی که بیانگر این مقادیر منطقی باشد نیست.

#REF! :   ۱) شماره ردیف مورد نظر فراتر از بازه جدول یا آرایه است.   ۲) سلول هایی که آدرس آنها در تابع وارد شده در صفحه وجود ندارند که معمولا زمانی اتفاق می‌افتد که یک فرمول HLOOKUP از سلولی دیگری کپی شوند.

#N/A :   تابع HLOOKUP نمی‌تواند هیچ مقداری مطابق با مقدار مورد جستجو پیدا کند که این مشکل معمولا به مقدار دقت جستجو مربوط می‌شود. به این صورت که اگر مقدار برای دقت جستجو TRUE یا یک عدد غیر صفر وارد شود و یا به کلی جای آن خالی گذاشته شود و با این خطا مواجه شویم به این دلیل است که: ۱) کوچکترین مقدار موجود در ردیف اول از بالای جدول یا آرایه از مقدار مورد جستجوی ما بیشتر است. (همانطور که گفته شد در حالت TRUE تابع نزدیکترین میزان کوچکتر از مقدار مورد جستجو را بازمی‌گرداند)   ۲) اولین ردیف از بالا به صورت افزایشی از چپ به راست تنظیم نشده است.

حال اگر مقدار وارد شده برای دقت جستجو FALSE یا صفر باشد و با خطای #N/A مواجه شوید به این دلیل است که مقداری دقیقا برابر با مقدار مورد جستجو در اولین ردیفِ بالا از بازه جدول یا آرایه وجود ندارد.