آموزش کامل تابع HLOOKUP در اکسل با مثال

معرفی تابع HLOOKUP

تابع HLOOKUP یک مقدار (مقدار معلوم) را در سطر اول یک محدوده‌ی مشخص‌شده جستجو می‌کند و نتیجه‌ای (مقدار مجهول) را از همان ستون، در سطری که شما تعیین می‌کنید، به عنوان خروجی برمی‌گرداند. حرف H در ابتدای نام این تابع از کلمه‌ی Horizontal به معنی «افقی» گرفته شده است؛ یعنی این تابع برای جدول‌هایی ساخته شده که عنوان‌هایشان در یک سطر و به‌صورت افقی چیده شده‌اند، مثل گزارش‌های بودجه و صورت‌های مالی که ماه‌های سال در ستون‌ها قرار دارند. تابع HLOOKUP مانند VLOOKUP امکان جستجوی دقیق و یا جستجوی بازه‌ای (غیر دقیق) را در محدوده‌ی مورد نظر دارد.

املا / نوشتار

HLOOKUP(Lookup_valueمقدار معلوم, Table_arrayجدول جستجو و نتیجه, Row_index_numشماره سطر مجهول, [Range_lookup] جستجوی بازه‌ای)

ورودی ها

ورودی آخر تابع HLOOKUP یکی از دو حالت زیر را می‌پذیرد:

نوع خروجی

آزاد هر نوعی از داده وابسته به مقدار مجهول مورد نظر برگردانده می شود.

نحوه کارکرد

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

نکته: نوع ورودی جستجو و نوع داده‌های سطر اول باید یکی باشند (مثلاً اگر ماه‌ها در سطر اول به‌صورت عدد ذخیره شده باشند، مقدار معلوم هم باید عدد باشد نه متن) اما نوع خروجی آزاد است و وابسته به شماره سطر مجهول می‌باشد.

نکته: شماره سطر در ورودی سوم، شماره سطر داخل محدوده‌ی انتخاب‌شده است و ربطی به شماره سطر شیت ندارد. اگر محدوده از سطر 10 شیت شروع شود، سطر 10 شیت در واقع سطر شماره 1 محدوده است.

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

خطاها

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

!VALUE#اگر ورودی سوم تابع HLOOKUP عدد صفر یا منفی باشد این تابع خطای !VALUE# برمی‌گرداند.

!REF#اگر مقداری که در ورودی سوم داده می‌شود بیشتر از تعداد سطرهای ورودی دوم باشد این تابع خطای !REF# برمی‌گرداند.

?NAME# زمانی که نام تابع را اشتباه وارد کنید یا نام محدوده‌ها تعریف نشده باشند این خطا رخ می‌دهد.

!SPILL# در اکسل 2021 و Microsoft 365، اگر ورودی سوم به‌جای یک عدد، چند عدد (آرایه) باشد و سلول‌های محل نمایش نتیجه خالی نباشند، این خطا رخ می‌دهد.

نکته: خطرناک‌ترین اشتباه در کار با HLOOKUP خطا نیست، بلکه نتیجه‌ی اشتباه است. اگر ورودی آخر را خالی بگذارید و سطر اول مرتب نباشد، تابع بدون هیچ پیام خطایی یک عدد نادرست برمی‌گرداند. در گزارش‌های مالی این موضوع می‌تواند به عدد غلط در بودجه یا ذخیره‌ها منجر شود.

نسخه و ملاحظات

این تابع نسبتاً قدیمی بوده و در تمام نسخه‌های اکسل دیده می‌شود. همانطور که در بخش توضیحات مشخص شد، این تابع فقط در سطر اول محدوده جستجو می‌کند و امکان برگرداندن اطلاعات سطرهای بالاتر از سطر اول را ندارد. همچنین ورودی آخر این تابع بطور پیش‌فرض روی حالت تقریبی قرار دارد که اگر فراموش شود، کاربران را با نتایج اشتباه مواجه می‌کند. مشکل دیگر این است که شماره سطر به‌صورت یک عدد ثابت در فرمول نوشته می‌شود؛ پس اگر بعداً سطری به وسط جدول اضافه شود، فرمول بدون هیچ خطایی از سطر اشتباه خروجی می‌گیرد. برای حل این مشکل می‌توان شماره سطر را با تابع MATCH محاسبه کرد (مثال دوم). راهکار جایگزین این تابع استفاده از تابع XLOOKUP است که از اکسل 2021 به بعد در دسترس می‌باشد و ترکیب INDEX و MATCH نیز در همه‌ی نسخه‌ها این محدودیت‌ها را ندارد.

منابع و لینک ها

لینک آموزش تابع HLOOKUP در سایت W3Schools

لینک آموزش تابع HLOOKUP در سایت Microsoft

لینک آموزش تابع HLOOKUP در سایت Exceljet

مثال

مثال 1: پیدا کردن بودجه‌ی یک هزینه در ماه مشخص

فرض کنید واحد مالی یک شرکت، بودجه‌ی هزینه‌های عملیاتی سال را مطابق با تصویر ذیل تنظیم کرده است. در این جدول ماه‌های سال در سطر اول (سلول‌های B1 تا M1) و سرفصل‌های هزینه در ستون A قرار دارند. می‌خواهیم با وارد کردن نام یک ماه در سلول P2، بودجه‌ی هزینه‌ی تبلیغات آن ماه را در سلول P3 بدست آوریم. طبیعی است که با یک جدول کوچک می‌توان عدد را با چشم پیدا کرد اما در عمل جدول‌های بودجه سرفصل‌ها و مراکز هزینه‌ی زیادی دارند و ما می‌خواهیم با تغییر نام ماه (سلول P2) به سرعت بودجه‌ی ماه دیگر را ببینیم. از آنجا که در این جدول عنوان‌ها (ماه‌ها) بصورت افقی چیده شده‌اند، تابع مناسب برای این کار HLOOKUP است.

در چنین شرایطی می‌توانیم فرمول زیر را در سلول P3 بنویسیم:

=HLOOKUP(P2, A1:M7, 5, FALSE)

در تصویر فوق و در ورودی اول تابع HLOOKUP مقدار معلوم که همان سلول P2 است و حاوی عبارت «مهر» می‌باشد داده شده است. سپس در ورودی دوم محدوده‌ی جستجو به شکلی انتخاب شده که سطر اول آن حاوی نام ماه‌ها باشد، چرا که تابع HLOOKUP فقط می‌تواند مقدار معلوم را در اولین سطر محدوده جستجو کند. با توجه به اینکه ما به دنبال بودجه‌ی تبلیغات هستیم و محدوده را از سطر 1 تا 7 انتخاب کرده‌ایم، همانطور که در شماره‌گذاری سطرها مشاهده می‌کنید عدد 5 را به عنوان شماره سطر مجهول به ورودی سوم تابع داده‌ایم. نکته‌ی پایانی اینکه چون جستجوی مورد نظر از نوع دقیق است و می‌خواهیم دقیقاً ماهی که در P2 نوشته شده را پیدا کنیم، در ورودی آخر تابع مقدار FALSE را قرار می‌دهیم.

نکته: در اکسل یک نوع داده به نام مقدار منطقی یا همان Logical داریم. مقدار منطقی یا 0 است یا 1 که اگر بخواهیم به جای این اعداد عنوان مناسبی استفاده کنیم، به جای 0 مقدار FALSE و به جای 1 هر مقدار غیر صفری منجمله TRUE را می‌توان استفاده کرد. در حالتی که بخواهیم تابع HLOOKUP را در حالت غیردقیق استفاده کنیم می‌توانیم ورودی آخر را وارد نکنیم یا در آن هر عددی غیر از صفر و یا مقدار TRUE را وارد کنیم.

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

مثال 2: جستجوی دوبعدی با ترکیب HLOOKUP و MATCH

در مثال قبل شماره سطر (عدد 5) را به‌صورت ثابت در فرمول نوشتیم. این روش دو ایراد دارد: اول اینکه فقط بودجه‌ی تبلیغات را برمی‌گرداند و دوم اینکه اگر بعداً سرفصل جدیدی مثل «هزینه‌ی نرم‌افزار» به وسط جدول اضافه شود، تبلیغات به سطر 6 می‌رود و فرمول بدون هیچ خطایی بودجه‌ی سرفصل دیگری را نشان می‌دهد. برای حل این مشکل، فرض کنید در سلول P4 نام سرفصل هزینه را وارد می‌کنیم و می‌خواهیم بودجه‌ی هر سرفصل در هر ماه را بدست آوریم. در این حالت شماره سطر را با تابع MATCH محاسبه می‌کنیم:

=HLOOKUP(P2, $A$1:$M$7, MATCH(P4, $A$1:$A$7, 0), FALSE)

[تصویر: همان جدول بودجه. O2 «ماه» و P2 «آبان»، O4 «سرفصل هزینه» و P4 «اجاره» (ترجیحاً با لیست کشویی Data Validation)، O3 «بودجه» و در P3 فرمول بالا و نتیجه‌ی آن. سلول I3 در جدول (اجاره‌ی آبان) و P3 با رنگ زرد مشخص شده‌اند — نام فایل پیشنهادی: hlookup_3_match.jpg — alt پیشنهادی: جستجوی دوبعدی با ترکیب توابع hlookup و match در اکسل]

تابع MATCH نام سرفصل («اجاره») را در ستون A جستجو می‌کند و جایگاه آن را برمی‌گرداند که در اینجا عدد 3 است. این عدد مستقیماً به عنوان ورودی سوم به HLOOKUP داده می‌شود. چون محدوده‌ی MATCH (یعنی A1:A7) دقیقاً از همان سطری شروع می‌شود که محدوده‌ی HLOOKUP شروع شده، شماره‌ای که MATCH برمی‌گرداند با شماره سطر داخل محدوده‌ی HLOOKUP یکی است. حالا با تغییر هر کدام از سلول‌های P2 یا P4، فرمول بودجه‌ی تقاطع ماه و سرفصل را نشان می‌دهد و اضافه شدن سطر جدید به جدول هم نتیجه را خراب نمی‌کند.

نکته: محدوده‌ی MATCH باید دقیقاً از سطر اول محدوده‌ی HLOOKUP شروع شود. اگر یکی از A1 و دیگری از A2 شروع شود، نتیجه یک سطر جابجا می‌شود.

مثال 3: محاسبه‌ی ذخیره‌ی مطالبات مشکوک‌الوصول (جستجوی بازه‌ای)

یکی از کاربردهای مهم HLOOKUP در حسابداری، جستجوی بازه‌ای است. فرض کنید سیاست شرکت برای محاسبه‌ی ذخیره‌ی مطالبات مشکوک‌الوصول بر اساس سن مطالبات به این شکل است: مطالبات تا 30 روز بدون ذخیره، 31 تا 90 روز 5 درصد، 91 تا 180 روز 20 درصد، 181 تا 365 روز 50 درصد و بیشتر از یک سال 100 درصد. این نرخ‌ها را در یک جدول افقی کوچک در سلول‌های A1 تا F2 وارد می‌کنیم. در سطر اول ابتدای هر بازه (0، 31، 91، 181 و 366) و در سطر دوم نرخ ذخیره‌ی همان بازه قرار دارد. حالا برای فهرست مشتریان که از سطر 5 شروع می‌شود، در سلول D6 فرمول زیر را می‌نویسیم و تا انتهای فهرست کپی می‌کنیم:

=HLOOKUP(C6, $B$1:$F$2, 2, TRUE)

[تصویر: در بالای شیت جدول نرخ ذخیره: A1 «سن مطالبات (روز)» و B1 تا F1 اعداد 0، 31، 91، 181، 366؛ A2 «نرخ ذخیره» و B2 تا F2 به ترتیب 0%، 5%، 20%، 50%، 100%. از سطر 5 فهرست مشتریان با سرستون‌های «نام مشتری»، «مانده مطالبات»، «سن مطالبات (روز)»، «نرخ ذخیره»، «مبلغ ذخیره». نمونه‌ی داده‌ها: شرکت آرین با سن 12 روز، فروشگاه نیک‌پخش با سن 45 روز، بازرگانی سپهر با سن 120 روز (نرخ 20%)، شرکت پارس‌تجارت با سن 240 روز و آقای رضایی با سن 400 روز. ردیف بازرگانی سپهر و سلول D3 جدول نرخ با رنگ زرد مشخص شده‌اند — نام فایل پیشنهادی: hlookup_4_range_lookup.jpg — alt پیشنهادی: محاسبه ذخیره مطالبات مشکوک الوصول با جستجوی بازه ای تابع hlookup]

برای مشتری «بازرگانی سپهر» که سن مطالباتش 120 روز است، عدد 120 در سطر اول وجود ندارد. چون ورودی آخر TRUE است، تابع بزرگترین عددی را که از 120 کوچکتر است، یعنی 91، انتخاب می‌کند و نرخ زیر آن یعنی 20 درصد را برمی‌گرداند. در ستون بعدی هم با ضرب مانده‌ی مطالبات در این نرخ (=B6*D6) مبلغ ذخیره بدست می‌آید.

نکته: در جستجوی بازه‌ای سطر اول باید به‌صورت صعودی مرتب باشد و بهتر است اولین مقدار آن صفر باشد. اگر جدول از 31 شروع شود، مطالبات زیر 31 روز خطای N/A# می‌گیرند، چون از کوچکترین مقدار سطر اول هم کوچکترند.

نکته: نرخ‌های این مثال فرضی هستند و هر شرکت بر اساس تجربه‌ی وصول مطالبات و سیاست‌های خود نرخ‌های متفاوتی تعیین می‌کند. مزیت این روش آن است که با تغییر اعداد جدول نرخ، همه‌ی محاسبات ذخیره خودبه‌خود به‌روز می‌شوند.

مثال 4: مقایسه‌ی بودجه با عملکرد واقعی

در بسیاری از شرکت‌ها جدول بودجه افقی (ماه‌ها در ستون‌ها) تنظیم می‌شود اما هزینه‌های واقعی که از نرم‌افزار حسابداری خارج می‌شوند عمودی هستند (هر ماه در یک سطر). فرض کنید در شیت «عملکرد» ستون A نام ماه و ستون B هزینه‌ی واقعی تبلیغات هر ماه است. برای قرار دادن بودجه‌ی همان ماه در کنار عدد واقعی، در سلول C2 فرمول زیر را می‌نویسیم و تا انتهای ستون کپی می‌کنیم:

=HLOOKUP(A2, بودجه!$A$1:$M$7, 5, FALSE)

سپس در ستون D انحراف بودجه (=B2-C2) و در ستون E درصد انحراف (=D2/C2) را محاسبه می‌کنیم. به این ترتیب یک گزارش «بودجه در برابر عملکرد» ساخته می‌شود که با اضافه شدن هر ماه جدید فقط کافی است فرمول‌ها را یک سطر پایین‌تر کپی کنیم.

[تصویر: شیت «عملکرد» با سرستون‌های «ماه»، «هزینه واقعی تبلیغات»، «بودجه»، «انحراف»، «درصد انحراف» برای ماه‌های فروردین تا مهر. ستون بودجه با فرمول بالا پر شده و انحراف‌های مثبت و منفی با قالب‌بندی شرطی سبز و قرمز شده‌اند. نوار فرمول، فرمول سلول C2 را نشان می‌دهد — نام فایل پیشنهادی: hlookup_5_budget_vs_actual.jpg — alt پیشنهادی: گزارش مقایسه بودجه و عملکرد با تابع hlookup در اکسل]

نکته: علامت $ در محدوده‌ی جدول بودجه باعث می‌شود هنگام کپی کردن فرمول به سطرهای پایین، محدوده جابجا نشود. فراموش کردن این علامت یکی از رایج‌ترین دلایل خطای N/A# در سطرهای پایین‌تر است.

مثال 5: جلوگیری از نمایش خطای N/A#

اگر کاربر در سلول ورودی نام ماه را اشتباه تایپ کند (مثلاً «مهرماه» به‌جای «مهر») یا قبل و بعد از آن فاصله‌ی اضافی بگذارد، HLOOKUP خطای N/A# برمی‌گرداند و اگر این سلول در جمع گزارش استفاده شود، کل گزارش خطا می‌دهد. با ترکیب تابع IFNA و تابع TRIM می‌توان این مشکل را کنترل کرد:

=IFNA(HLOOKUP(TRIM(P2), $A$1:$M$7, 5, FALSE), “ماه نامعتبر است”)

تابع TRIM فاصله‌های اضافی ابتدا و انتهای متن را حذف می‌کند و تابع IFNA در صورت بروز خطای N/A#، به‌جای خطا عبارت «ماه نامعتبر است» را نمایش می‌دهد. اگر می‌خواهید نتیجه در محاسبات بعدی استفاده شود، می‌توانید به‌جای متن عدد 0 را قرار دهید.

[تصویر: دو حالت کنار هم: در حالت اول P2 حاوی «مهرماه» و P3 خطای N/A# را نشان می‌دهد؛ در حالت دوم همان ورودی با فرمول بالا عبارت «ماه نامعتبر است» را نشان می‌دهد — نام فایل پیشنهادی: hlookup_6_ifna.jpg — alt پیشنهادی: مدیریت خطای N/A در تابع hlookup با تابع ifna]

نکته: بهتر است به‌جای پنهان کردن خطا، جلوی ورود مقدار اشتباه را بگیرید. با استفاده از Data Validation و ساختن یک لیست کشویی از ماه‌ها در سلول P2، کاربر فقط می‌تواند یکی از نام‌های موجود در سطر اول را انتخاب کند.

مثال 6: جستجو با بخشی از عنوان (کاراکترهای عام)

فرض کنید گزارش فروش شعب به‌صورت افقی تنظیم شده و عنوان ستون‌ها نام کامل شعبه‌ها است، مثل «شعبه مرکزی تهران»، «شعبه اصفهان – سپاهان‌شهر»، «شعبه شیراز» و «شعبه تبریز». در سطرهای زیر به ترتیب فروش ناخالص، برگشت از فروش، تخفیفات و فروش خالص قرار دارد. می‌خواهیم کاربر فقط نام شهر را در سلول P2 وارد کند و فروش خالص آن شعبه را ببیند:

=HLOOKUP(“*”&P2&”*”, $B$1:$E$5, 5, FALSE)

علامت * در اکسل به معنی «هر تعداد کاراکتر» است. با چسباندن این علامت به ابتدا و انتهای مقدار P2، فرمول دنبال عنوانی می‌گردد که کلمه‌ی «اصفهان» در هر جای آن آمده باشد. علامت ? هم به معنی «دقیقاً یک کاراکتر» است.

[تصویر: جدول فروش شعب در B1:E5 با ستون A شامل «شرح» (فروش ناخالص، برگشت از فروش، تخفیفات، فروش خالص) و چهار شعبه در B1 تا E1. O2 «شهر»، P2 «اصفهان» و P3 فرمول بالا با نتیجه‌ی فروش خالص شعبه‌ی اصفهان. ستون شعبه‌ی اصفهان و سلول P3 با رنگ زرد مشخص شده‌اند — نام فایل پیشنهادی: hlookup_7_wildcard.jpg — alt پیشنهادی: جستجو با بخشی از عنوان با کاراکترهای عام در تابع hlookup]

نکته: کاراکترهای عام فقط در حالت جستجوی دقیق (FALSE) و فقط برای مقادیر متنی کار می‌کنند. اگر عبارت مورد نظر در چند عنوان تکرار شده باشد، فقط اولین عنوان از سمت چپ پیدا می‌شود. برای جستجوی خود علامت * یا ? به‌عنوان یک کاراکتر معمولی، قبل از آن علامت ~ بگذارید.

مثال 7: برگرداندن چند سطر با یک فرمول (اکسل 2021 و Microsoft 365)

در نسخه‌های جدید اکسل که از آرایه‌های پویا (Dynamic Arrays) پشتیبانی می‌کنند، می‌توان به‌جای یک شماره سطر، چند شماره سطر را به ورودی سوم داد. فرض کنید می‌خواهیم با انتخاب یک ماه، بودجه‌ی همه‌ی سرفصل‌های هزینه‌ی آن ماه را به‌صورت یک ستون در سلول‌های P5 تا P10 ببینیم. کافی است فرمول زیر را فقط در سلول P5 بنویسیم:

=HLOOKUP(P2, $A$1:$M$7, {2;3;4;5;6;7}, FALSE)

آرایه‌ی {2;3;4;5;6;7} به تابع می‌گوید سطرهای 2 تا 7 را برگرداند و نتیجه خودبه‌خود در شش سلول زیر هم پخش (Spill) می‌شود. به‌جای نوشتن اعداد به‌صورت دستی می‌توان از تابع SEQUENCE هم استفاده کرد: SEQUENCE(6, 1, 2). با جمع زدن نتیجه نیز کل بودجه‌ی هزینه‌ی آن ماه بدست می‌آید.

[تصویر: در O5 تا O10 نام سرفصل‌های هزینه و در P5 فرمول بالا که نتایج آن در P5 تا P10 با کادر آبی Spill دیده می‌شود. P2 «آذر» است. در P11 جمع بودجه‌ی ماه — نام فایل پیشنهادی: hlookup_8_multiple_rows.jpg — alt پیشنهادی: برگرداندن چند سطر با یک فرمول hlookup در اکسل 365]

نکته: اگر هر کدام از سلول‌های P6 تا P10 از قبل مقدار داشته باشند، فرمول خطای !SPILL# برمی‌گرداند. در نسخه‌های قدیمی‌تر اکسل این روش کار نمی‌کند و باید برای هر سطر یک فرمول جداگانه نوشت. همچنین جداکننده‌ی آرایه در برخی تنظیمات منطقه‌ای ویندوز ممکن است متفاوت باشد.

کاربردهای تابع HLOOKUP

کاربردهای تابع HLOOKUP بیشتر در جاهایی است که داده‌ها به‌صورت افقی چیده شده‌اند. در گزارش‌های مالی و مدیریتی این حالت بسیار رایج است: جدول‌های بودجه، صورت سود و زیان ماهانه، گزارش‌های مقایسه‌ای سال به سال، جدول نرخ‌ها و تعرفه‌ها و گزارش‌هایی که از نرم‌افزارهای حسابداری با ماه‌ها در ستون خروجی گرفته می‌شوند. در ادامه بخشی از کاربردهای تابع HLOOKUP بصورت فهرست‌وار قید شده‌اند:

1- استخراج اعداد از جدول‌های بودجه و برنامه‌ی ماهانه

2- ساخت گزارش مقایسه‌ی بودجه با عملکرد

3- جستجوی بازه‌ای در جدول نرخ‌ها، پورسانت‌ها و تعرفه‌ها

4- خواندن اطلاعات از صورت‌های مالی چند دوره‌ای

5- انتقال داده از جدول افقی به گزارش عمودی

6- ساخت داشبوردهای ساده با انتخاب دوره از لیست کشویی

7- جستجوی دوبعدی در جدول‌های ماتریسی (همراه با MATCH)

توابع مشابه

VLOOKUP

XLOOKUP

LOOKUP

INDEX

MATCH

XMATCH

توابع مرتبط

IF

IFNA

IFERROR

TRIM

SEQUENCE

TRANSPOSE

INDIRECT

VALUE