اگرچه HLOOKUP به اندازه VLOOKUP استفاده نمی شود، اما همچنان یک تابع ارزشمند برای افزودن به جعبه ابزار اکسل است.
تابع HLOOKUP اکسل یک ابزار ارزشمند برای استخراج داده ها است. با این حال، به دلیل مقایسه نامطلوب با VLOOKUP و XLOOKUP، اغلب نادیده گرفته می شود. در حالی که ساختار و استفاده از آن شبیه به VLOOKUP و XLOOKUP است، زمانی که شما نیاز به تجزیه و تحلیل داده های مرتب شده به روشی خاص دارید، جایی دارد.
با HLOOKUP، می توانید داده های افقی را بدون جابجایی آن ها تجزیه و تحلیل کنید. این ویژگی آن را به یک ابزار مفید تبدیل می کند و شما باید آن را در زرادخانه توابع LOOKUP خود قرار دهید.
آرگومان های HLOOKUP اکسل
تابع HLOOKUP اکسل از چهار آرگومان تشکیل شده است. با این حال، آرگومان چهارم اختیاری است. اینها دلایل و کارهایی است که انجام می دهند:
- lookup_value: این مقداری است که می خواهید تابع در ردیف اول آرایه داده شما پیدا کند. این می تواند یک مقدار عددی، یک مرجع به سلول دیگر یا یک مقدار رشته باشد.
- table_array: به مجموعه سلول هایی که می خواهید جستجو کنید اشاره دارد. سطر اول این آرایه جایی است که HLOOKUP برای آرگومان اول مطابقت را جستجو می کند. بقیه اطلاعاتی را نشان می دهد که می خواهید با نتایج خود وارد کنید.
- row_index_num: این ردیف حاوی مقداری است که می خواهید برگردانید.
- range_lookup (اختیاری): این آرگومان می تواند TRUE یا FALSE باشد. اگر TRUE (یا حذف شود)، HLOOKUP ستونی را با نزدیکترین تطابق برمی گرداند. اما اگر FALSE باشد، فقط مطابقت های دقیق را برمی گرداند.
اگر آرگومان range_lookup درست باشد یا گنجانده نشود، ممکن است این تابع نتایج غیرمنتظره ای را برگرداند. به احتمال زیاد می خواهید این آرگومان را روی FALSE تنظیم کنید تا از مطابقت های نادرست جلوگیری کنید. همین خطا در عملکرد VLOOKUP نیز یک مشکل رایج است.
علاوه بر این، اگر range_lookup را روی TRUE تنظیم کنید یا آن را حذف کنید، باید ردیف اول داده های خود را بر اساس حروف الفبا یا به ترتیب صعودی مرتب کنید.
HLOOKUP، VLOOKUP و XLOOKUP اکسل چگونه متفاوت هستند؟
HLOOKUP اکسل از نظر ساختار و استفاده بسیار شبیه به دو توابع جستجوی دیگر اکسل، VLOOKUP و XLOOKUP است.
تفاوت اصلی بین HLOOKUP و VLOOKUP در جهت گیری داده های جستجو شده است. VLOOKUP فرض می‌کند که داده‌هایی که بررسی کردید به‌صورت افقی سازماندهی شده‌اند، جایی که هر ردیف در جدول نشان‌دهنده یک رکورد است.
HLOOKUP، از طرف دیگر، فرض می کند که داده های شما به صورت عمودی مرتب شده اند. هر ستون نشان دهنده یک رکورد جداگانه است و فیلدهای روی رکوردها بر اساس ردیف سازماندهی می شوند.
XLOOKUP جدیدترین تابع LOOKUP اکسل است. اگرچه شبیه به VLOOKUP است، اما می تواند داده های عمودی و افقی را تجزیه و تحلیل کند. همچنین می تواند به جای محدودیت مرجع تک سلولی HLOOKUP و VLOOKUP، آرایه ای از سلول ها را بر اساس مکان تطابق برگرداند.
به طور کلی، این تفاوت ها موارد استفاده برای HLOOKUP را تا حدودی محدود می کند. در بیشتر موقعیت ها، داده ها به صورت افقی مرتب می شوند، که VLOOKUP و XLOOKUP را بسیار کاربردی تر می کند.
نمونه هایی از HLOOKUP اکسل
برای اینکه به شما نشان دهیم HLOOKUP چگونه کار می کند، به یک مثال نگاه می کنیم:
در اینجا ما یک دفتر اصلی فروش داریم که به صورت افقی مرتب شده است. این برگه شناسه تراکنش، تاریخ تراکنش، مبلغ تراکنش و شناسه مشتری منحصر به فرد را نشان می دهد.
در زیر سوابق، یک ورودی برای شناسه تراکنش داریم. هنگامی که یک شناسه تراکنش معتبر را تایپ می کنید، مبلغ تراکنش و شناسه مشتری از جدول بالا کشیده می شود.
در سلول B7 می توانید از تابع زیر برای دریافت مبلغ تراکنش استفاده کنید:
=HLOOKUP(B6, B1:H4, 3, FALSE)
در سلول B8 می توانید از تابع زیر برای دریافت شناسه مشتری استفاده کنید:
=HLOOKUP(B6, B1:H4, 4, FALSE)
در تابع اول، row_index_num را روی 3 قرار دادیم. این به این معنی است که ردیف سوم در ستون را که با مقدار وارد شده به عنوان اولین آرگومان مطابقت دارد، می کشد که به B6 اشاره می کند. در تابع دوم، آرگومان سوم با 4 عوض می‌شود، که به آن اجازه می‌دهد ردیف چهارم را به جای آن بکشد.
در هر دو مورد، استدلال نهایی، FALSE، گنجانده شده است. این بدان معنی است که تابع HLOOKUP فقط یک تطابق دقیق را می پذیرد. اگر شماره تراکنش وارد شده در جدول نباشد، هر دو تابع با خطا مواجه خواهند شد.
ساده ترین راه برای جلوگیری از این مشکل این است که هر دو تابع را در یک تابع IF قرار دهید و از تابع ISERROR برای تعیین معتبر بودن تابع استفاده کنید. بسته بندی تابع به شما امکان می دهد یک مقدار پیش فرض را وارد کنید که اگر شناسه تراکنش در جدول قرار نداشته باشد نشان داده می شود.
حال دو تابع در B7 و B8 به شرح زیر است:
=IF(ISERROR(HLOOKUP(B6, B1:H4, 3, FALSE)), "Transaction Not Found", HLOOKUP(B6, B1:H4, 3, FALSE)
=IF(ISERROR(HLOOKUP(B6, B1:H4, 4, FALSE)), "Transaction Not Found", HLOOKUP(B6, B1:H4, 4, FALSE))
استفاده از IF و ISERROR به صفحه‌گسترده اجازه می‌دهد تا اگر شناسه تراکنش مشخص‌شده پیدا نشد، پیام مفیدی ارائه دهد. این دو تابع یکی از ساده‌ترین راه‌حل‌هایی هستند که می‌توانید در اکسل استفاده کنید، زمانی که فرمول‌های شما ممکن است منجر به خطا شود.
اگر آرگومان نهایی را از هر دو عبارت در این مورد حذف کنید، می‌توانید ببینید وقتی جستجو را به تطابقات دقیق محدود نکنید چه اتفاقی می‌افتد:
در مثال بالا، حتی اگر شناسه تراکنش 445880 وجود نداشته باشد، یک مبلغ و شناسه مشتری همچنان برگردانده می شود. بدون اینکه آخرین آرگومان روی false تنظیم شده باشد، جستجوی مورد استفاده در عوض نزدیکترین تطابق بعدی را که از مقدار وارد شده تجاوز نمی کند، برمی گرداند.
در این مورد، توابع مقدار و شناسه تراکنش 445879 را از آنجایی که نزدیکترین تطابق بود، استخراج کردند.
وقتی از جستجوی غیردقیق استفاده می شود، نزدیکترین تطابق نمی تواند از مقدار جستجو شده بیشتر باشد. به این معنی که اگر شناسه تراکنش وارد شده کمتر از تراکنش های موجود در جدول باشد، هیچ تراکنشی پیدا نمی شود.
از آنجایی که اختیاری است، خالی ماندن آرگومان نهایی خطای بسیاری از کاربران است. به این ترتیب، آخرین استدلال همیشه باید به عنوان نادرست فهرست شود، مگر اینکه مطمئن باشید که وضعیت شما منطبق‌های غیردقیق را می‌طلبد.
چرا باید HLOOKUP را بدانید و از آن استفاده کنید
HLOOKUP یکی از کم استفاده ترین توابع در خانواده تابع LOOKUP اکسل است. در مقایسه با XLOOKUP و VLOOKUP، تابع HLOOKUP نیاز دارد که داده ها به روشی خاص و غیر معمول مرتب شوند.
الزامات داده خاص، همراه با سردرگمی آرگومان چهارم، اغلب به این معنی است که HLOOKUP کمتر از ارزش گذاری شده است. با این حال، این ابزار در شرایط خاص، ابزار ایده‌آلی است – می‌تواند به شما کمک کند تا داده‌های بسیار مورد نیاز را از مجموعه‌های داده‌ای که به‌طور نامناسب مرتب شده‌اند، جایی که هیچ‌یک از خواهر و برادرهایش کار نمی‌کنند، بیرون بکشید.
با دانستن HLOOKUP، می‌توانید در زمان و تلاش خود صرفه‌جویی کنید، و آن را به ابزاری ارزشمند برای مجموعه ابزار اکسل خود تبدیل کنید، حتی اگر معمولاً استفاده نمی‌شود.