تابع HLOOKUP یکی از توابع جستجو و مرجع (Lookup & Reference) اکسل می باشد. در این آموزش پیشرفته اکسل با قسمت های تشکیل دهنده و کاربرد این تابع آشنا می شویم.
ورودی های تابع HLOOKUP اکسل
آرگومان های تابع HLOOKUP اکسل در پنجره Function Arguments این تابع در تصویر زیر نمایش داده شده است.
تابع HLOOKUP چهار آرگومان به نام های Row_index_num، Table_array، Lookup_value و Range_lookup دارد.
کارکرد تابع
HLOOKUP مخفف horizontal lookup و به معنای جستجوی افقی می باشد. تابع HLOOKUP در ردیف اول یک جدول یا آرایه (Table_array) مقداری (Lookup_value) را جستجو می کند و در صورتی که آن را پیدا کند مقدار متناظر آن را در ردیفی که مشخص می کنید (Row_index_num) بعنوان مقدار بازگشتی برمی گرداند.
شکل فرمولی تابع HLOOKUP اکسل
=HLOOKUP(Lookup_value, Table_array, Row_index_num, [Range_lookup])
- Lookup_value : اجباری؛ مقداری است که می خواهید در سطر اول یک جدول جستجو نمایید. Lookup_value می تواند یک مقدار ثابت، یک ارجاع و یا یک رشته متنی باشد.
- Table_array : اجباری؛ جدول اطلاعاتی است که می خواهید در آن جستجو را انجام دهید. table_array می تواند یک ارجاع به یک محدوده سلولی، یک محدوده نامی (range name) و یا یک آرایه ثابت باشد.
- مقادیر سطر اول table_array می تواند متن، عدد یا مقادیر منطقی (true یا false) باشد.
- اگر range_lookup مقدار TRUE داشته باشد، مقادیری که در سطر اول table_array قرار گرفته اند باید بصورت صعودی (ascending) مرتب شده باشند. (بعنوان مثال
...، -2، -1، 0، 1، 2، ...یاA-ZیاFALSE, TRUE) در غیراینصورت HLOOKUP ممکن است که اطلاعات درستی به شما ندهد. اگر range_lookup مقدار FALSE داشته باشد، نیازی نیست table_array مرتب شود. - حروف بزرگ و حروف کوچک یکسان هستند.
- داده های range_lookup را از چپ به راست و صعودی (ascending) مرتب نمایید.
- Row_index_num : اجباری؛ شماره ردیفی در جدول Table_array می باشد که اگر در ردیف اول جدول مقدار Lookup_value پیدا شود، مقدار بازگشتی تابع از آن انتخاب خواهد شد. اگر یک باشد به معنای ردیف اول، اگر دو باشد ردیف دوم و به همین ترتیب. اگر کمتر از یک باشد مقدار بازگشتی تابع خطای
#VALUE!خواهد بود. اگر بزرگتر از تعداد ردیف های Table_array باشد، مقدار بازگشتی تابع خطای#REF!خواهد بود. - Range_lookup : اختیاری؛ یک مقدار منطقی (TRUE یا FALSE) است که مشخص می نماید که شما می خواهید تابع به هنگام جستجو یک تطبیق دقیق یا یک تطبیق تقریبی انجام دهد. اگر TRUE یا نادیده گرفته شده باشد، یک مقدار تقریبی برگردانده می شود. بعبارت دیگر اگر تابع یک تطبیق دقیق پیدا نکند، بزرگترین مقداری که کمتر از lookup_value باشد برگردانده می شود. اگر FALSE باشد HLOOKUP یک دقیقاً مقدار Lookup_value را جستجو می نماید و در صورتی که آن را پیدا نکند مقدار بازگشتی تابع
#N/Aخواهد بود.
ملاحظات
- اگر HLOOKUP مقدار Lookup_value را پیدا نکند و Range_lookup مقدار TRUE داشته باشد، تابع بالاترین مقداری که کمتر از Lookup_value باشد برمی گرداند.
- اگر Lookup_value کمتر از کوچکترین مقدار در سطر اول Table_array باشد، مقدار بازگشتی تابع
#N/Aخواهد بود. - اگر Range_lookup مقدار FALSE داشته باشد و Lookup_value متن باشد، می توانید در Lookup_value از فرانویسه ها (wildcard characters) استفاده نمایید.
مثال
در مثال زیر با کاربرد تابع HLOOKUP در اکسل آشنا شوید. داده های جدول زیر را در سلول A1 اکسل کپی نمایید. فایل اکسل مثال را می توانید از لینک زیر پایین همین صفحه دریافت نمایید.
| Axles | Bearings | Bolts |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| فرمول | توضیحات | نتیجه |
|---|---|---|
=HLOOKUP("Axles", A1:C4, 2, TRUE) | Axles را در سطر ۱ جستجو می نماید و مقدار متناظر آن در سطر دوم همان ستون برمی گرداند. | 4 |
=HLOOKUP("Bearings", A1:C4, 3, FALSE) | Bearings را در سطر ۱ جستجو می نماید و مقدار متناظر آن در سطر سوم همان ستون برمی گرداند. | 7 |
=HLOOKUP("B", A1:C4, 3, TRUE) | B را در سطر ۱ جستجو می نماید و مقدار متناظر آن در سطر سوم همان ستون برمی گرداند. بدلیل اینکه جستجوی تابع تقریبی تعیین شده است بزرگترین مقداری که در سطر ۱ کمتر از B باشد جستجو می شود و بنابراین A در Axles توسط تابع انتخاب می شود. | 5 |
=HLOOKUP("Bolts", C1:E4, 4) | Bolts را در سطر ۱ جستجو می نماید و مقدار متناظر آن در سطر چهارم همان ستون برمی گرداند. | 11 |
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) | آرایه ثابت {1,2,3;”a”,”b”,”c”;”d”,”e”,”f”} سه سطر دارد که آنها با ; (semicolon) از یکدیگر جدا شده اند. تابع مقدار ۳ را در سطر اول آرایه (یعنی 1,2,3) جستجو می نماید. با توجه به اینکه ۳ در ستون سوم سطر اول می باشد، ستون سوم انتخاب می شود و در دومین سطر (یعنی “a”,”b”,”c” ) نیز ستون سوم یعنی c مقدار بازگشتی تابع می باشد. | c |
بیشتر بخوانید
تابع Concat اکسل | جمع کردن کلمات و رشته ها در اکسل
تابع CODE اکسل | پیدا کردن کد یک نویسه (کاراکتر) در اکسل
تابع CHAR اکسل | تبدیل کد عددی نویسه (کاراکتر کد) به نویسه در اکسل
تابع ADDRESS اکسل | تبدیل شماره سطر و ستون یک سلول به آدرس در اکسل
تابع INDEX اکسل | پیدا کردن داده ها در یک جدول با استفاده از شماره سطر و ستون