تابع HLOOKUP جزو توابع جستوجوی اکسل است. این تابع یک مقدار را از اولین سطر یک جدول جستوجو میکند و جواب مربوطه را از یک سطر معین دیگر برمیگرداند.
◄ Hlookup معادل Horizontal lookup به معنای جستجوی افقی است.
آرگومانها یا پارامترهای ورودی
آرگومان اول (lookup_value): مقدار یا ارزشی که قرار است جستوجو شود و میتواند به صورت عدد، متن، تاریخ، آدرس سلول و … باشد.
آرگومان دوم (table_array): این آرگومان بیانگر محل جستوجو است و باید بصورت یک جدول باشد که حکم دیتابیس را دارد. مقدار آرگومان اول از درون این جدول جستوجو میشود.
آرگومان سوم (row_index_num): در این آرگومان باید عدد بنویسید. این عدد بیانگر شماره سطری است که میخواهید جواب از آن سطر نمایش داده شود.
آرگومان چهارم ([range_lookup]): این آرگومان اختیاری و تعیین میکند تابع در صورتی که مقدار دقیق آرگومان اول (lookup_value) را نیافت، چه چیزی را برگرداند. این آرگومان دو گزینه دارد و میتواند TRUE یا FALSE باشد:
- TRUE: با انتخاب این گزینه تطبیق تقریبی انجام میشود؛ یعنی در صورتی که مقدار دقیق آرگومان اول (lookup_value) از اولین سطر جدول یافت نشد، اکسل نزدیکترین حد پایین مقدار مورد نظر را نمایش میدهد (به جای واژه True از عدد یک نیز میتوانید استفاده کنید).
توجه: در این حالت حتماً باید سطر اول جدول صعودی باشد.
- FALSE: با انتخاب این گزینه تطبیق کامل انجام میشود، یعنی در صورتی که مقدار دقیق آرگومان اول (lookup_value) از اولین سطر جدول یافت نشد، دیگر بحث تقریب در کار نیست و تابع یک خطا برمیگرداند (به جای واژه False از عدد صفر نیز میتوانید استفاده کنید).
توجه مهم:
تمام نکات مربوط به تابع Hlookup همانند تابع Vlookup میباشد با این تفاوت که جای سطر و ستون عوض میشود. تابع Vlookup براساس ستون و تابع Hlookup براساس سطر کار میکند.
برای آشنایی بیشتر با تابع Vlookup اینجا کلیک کنید
نمایی از تابع Hlookup به صورت شماتیک
مثالها
پیدا کردن نام براساس کد پرسنلی
با توجه به شکل زیر میخواهیم فرمولی در سلول J2 بنویسیم که براساس کد پرسنلی واردشده در سلول H2، نام شخص مربوطه را بازگرداند.
فرمول سلول J2:
=Hlookup( H2 , C1:G4 , 2 , 0 )
ترجمهی فارسی تابع فوق:
اکسل برو محتویات سلول H2 را (که در آن مقدار 1201 وجود دارد) بگرد از اولین سطر جدول ناحیه C1:G4 و جواب را از دومین سطر این جدول برای ما نمایش بده!
زمانی که مقدار مورد جستجو از اولین سطر یافت شد، اکسل متناظر با این مقدار را از سطر دوم برای ما نمایش میدهد.
اگر همین فرمول را برای بدست آوردن سمّت بنویسیم به صورت زیر خواهد بود:
=Hlookup( H2 , C1:G4 , 3 , 0 )
توجه: سایر مثال های تابع Hlookup را میتوانید در انتهای صفحه دانلود نمایید.
نکات مهم تابع Hlookup
نکته 1) Hlookup از اولین سطر جدول داده (نه اولین سطر اکسل) کار جستوجو را انجام میدهد.
همانطور که در شکل بالا میبینید اکسل سطر 2 را به عنوان اولین سطر برای Hlookup درنظر میگیرد نه سطر 1.
نکته 2) Hlookup مقدار تکراری را پیدا نمیکند؛ در صورت تکراری بودن، اولین مقدار را نمایش میدهد.
همانطور که در شکل بالا میبینید کد پرسنلی 1202 در سطر اول تکرار شده است بنابراین انتظار داریم اکسل هنگام جستوجوی 1202، اولین مقدار را که مربوط به شخص اول (رضا) است برای ما نمایش دهد نه شخص بعدی (محسن).
پس توجه داشته باشید از این تابع برای زمانی استفاده کنید که عبارت مورد جستوجوی شما یونیک یا به عبارت دیگر منحصربهفرد باشد مانند: کد پرسنلی، کد ملی، کد محصول و … .
نکته 3) بزرگ یا کوچک بودن حروف در جستوجوی این تابع مهم نیستند. مانند: “Reza” = “reza”
نکته 4) تابع Hlookup از وایلدکاردها (Wildcard) نیز پشتیبانی میکند. مانند جستوجوی واژه “محمد*” به جای “محمد”.
نکته 5) در صورتی که آرگومان آخر را وارد نکنید و تابع را با 3 آرگومان بنویسید، اکسل با فرض True بودن آرگومان آخر تابع Hlookup را اجرا میکند، پس توجه داشتە باشید در اکثر مواقع آرگومان آخر عدد صفر یا واژه False نوشته شود.
نکته 6) اگر عبارتی که میخواهید جستوجو کنید به صورت عددی باشد، توجه داشته باشید آن را داخل دابلکوتیشن ننویسید.
زمانی که عددی داخل دابلکوتیشن نوشته میشود، آن عدد حالت متنی به خود میگیرد و از حالت عدد خارج میشود. در شکل بالا حالت الف درست است و حالت ب اشتباه میباشد.
نکته 7) درصورتی که جدول اکسل شما خروجی یک نرمافزار مانند همکاران سیستم و … باشد به حروف “ی” و “ک” توجه کنید؛ معمولاً در خروجی نرمافزارها این حروف به صورت عربی نوشته میشود به خاطر همین تابع Hlookup نمیتواند عبارت مورد جستوجو را بیابد.
یکی از مشکلاتی که کاربران فارسی زبان در اوایل شروع استفاده از کامپیوترهای شخصی داشتند، نبود فونت فارسی روی سیستمها و به طور کلی قابل استفاده نبودن خط فارسی بود. اولین بار بعضیها اقدام به فارسیسازی با استفاده از فونتهای عربی نمودند و به همین دلیل اکثر قریب به اتفاق سیستمها و برنامهها برگردان از عربی بود… .
یکی از تفاوتهای خط عربی با فارسی نداشتن چهار حرف ( گ چ پ ژ ) در عربی است و همه این چهار حرف هم در آن موقع به خط فارسی اضافه شد ولی کسانی که این کار را انجام میدادند گاهی اوقات فراموش میکردند که خط عربی دو تفاوت دیگر هم با خط فارسی دارد و آن در حروف ( ی و ک ) است. ی و ک عربی به این صورت است: ي و ك
برای مشاهده ویدئوی ي و ك عربی اینجا کلیک کنید.
خطاهای تابع Hlookup
خطای N/A#: این خطا به معنای موجود نبودن (Not Available) یا پیدا نکردن است و زمانی نمایش داده میشود که مقداری که در حال جستوجوی آن هستیم، موجود نباشد.
در شکل بالا چون مقدار 1205 در جدول داده ها یافت نشد، تابع Hlookup خطای N/A# را نمایش میدهد.
خطای !REF#: این خطا به معنای اشتباه وارد شدن رفرنس (Reference) میباشد و زمانی نمایش داده میشود که آرگومان سوم تابع Hlookup عددی بزرگتر از تعداد سطرهای جدول داده باشد.
در شکل بالا چون عددی که در آرگومان سوم (عدد 5) وارد شده بزرگتر از تعداد سطرهای جدول (4 سطر) است، Hlookup خطای رفرنس را نمایش میدهد.
خطای !VALUE#: این خطا برای زمانی است که در آرگومان سوم عددی منفی وارد کنید.
تفاوت دو تابع HLOOKUP و VLOOKUP:
در تابع HLOOKUP به صورت ردیفی جست و جو میکنیم و جواب را در ستون متناظر پیدا میکنیم اما در تابع VLOOKUP به صورت متنی جست و جو میکنیم و جواب را در ردیف متناظر آن پیدا میکنیم.در واقع این دو تابع یک کار را انجام میدهند و این شما هستید که بر اساس نیاز و ترکیب توابع با یکدیگر از این توابع استفاده خواهید کرد.
عالیی بود مث همیشه
عالی بود ممنون
دست مریزاد.
آموزش فوق العاده بود🙏🙏🙏
مثل همیشه خیلی عالی و مفید-خدا قوت