فرمول hlookup در اکسل
همانطور که احتمالاً می دانیداکسل برای جستجوی یک مقدار، سه فرمول LOOKUP ،VLOOKUP و HLOOKUP را در اختیار کاربران قرار می دهد. قبلا با فرمول VLOOKUP در اکسل آشنا شدید. در این آموزش قصد داریم به بررسی فرمول HLOOKUP بپردازیم.
فرمول HLOOKUP مخفف “Horizontal lookup” است و همانطور که از نامش پیداست جستجو را به صورت افقی انجام می دهد.
در ابتدا بیایید به ساختار و آرگومان های این تابع نگاهی بیندازیم:
برای درک بهتر آرگومان های این تابع ادامه آموزش را با یک مثال ساده دنبال می کنیم تا به شما کمک کند از HLOOKUP در اکسل بصورت کارآمدتر استفاده کنید.
داده های جدول زیر را در نظر بگیرید. می خواهیم با توجه به تعرفه بالای جدول، رتبه هر شخص را از A تا F مشخص کنیم. تابع HLOOKUP به راحتی این کار را انجام می دهد.
در ادامه، یک راهنمای پنج مرحله ای برای انجام این مثال و معرفی کامل آرگومان های این تابع آورده شده است.
۱- درج فرمول HLOOKUP در اکسل
برای دسترسی به تابع HLOOKUP در سلول G6 علامت = و سپس چند حرف از این تابع را تایپ می کنیم. سپس با نشان کردن تابع مورد نظر و فشردن کلید Tab فرمول را آماده دریافت آرگومان ها می کنیم.
همچنین می توانیم در تب Formulas، گروه Lookup & Reference را انتخاب و روی HLOOKUP کلیک کنیم. با این کار پنجرهای برای وارد کردن آرگومان ها ظاهر می شود.
۲- آرگومان اول (lookup_value)
این پارامتر مقداری را که قرار است در محدوده موردنظر ما جستجو شود تعیین می کند. ما در این مثال می خواهیم رتبه را برای امتیاز ۹۲ بدست آوریم که یک عدد بین ۸۰ تا ۱۰۰ است. بنابراین مقداری که ما می خواهیم بر اساس آن جستجو رو انجام بدهیم سلول F6 است. پس پارامتر اول ما برابر F6 هست.
۳- آرگومان دوم (table_array)
در این پارامتر باید بازه ای را که قرار است جستجو را در آن انجام دهیم تعریف کنیم. این بازه باید هم شامل سطری باشد که می خواهیم جستجو را در آن انجام بدهیم (در مثال ما سطر “رتبه”) و هم سطری که به دنبال نتایج متناظر جستجو از آن هستیم (سطر مربوط “امتیاز”). پس پارامتر دوم ما برابر B2:G3 است.
۴- آرگومان سوم (row_index_num)
بعد از مشخص کردن مقدار مورد جستجو و جدول، فرمول می پرسد که مجهول ما کجاست؟ کافی است که جای سطر مجهول یعنی “رتبه” را به آن بگوییم. یعنی بگوییم در این جدول یعنی (B2:G3) دومین سطر حاوی مقداری است که ما آن را لازم داریم. پس در این مثال عدد ۲ (یعنی دومین ردیف داده) آرگومان سوم ما است.
توجه داشته باشید که Hlookup از اولین سطر جدول داده (نه اولین سطر اکسل) کار جستوجو را انجام می دهد.
۵- آرگومان چهارم (Range_Lookup)
چهارمین پارامتر تعیین می کند که آیا شما به دنبال تطابق دقیق هستید یا تطابق بازه ای. برای پیدا کردن تطابق دقیق (Exact Match)، صفر یا FALSE را وارد کنید. برای پیدا کردن تطابق بازه ای (Approximate Match) هر چیزی غیر از صفر یا TRUE را وارد کنید.
نکته: این پارامتر اختیاری است و اگر وارد نشود، به طور پیش فرض TRUE در نظر گرفته می شود.
در این مثال ما تطابق بازه ای را انتخاب می کنیم. منطق تطابق بازه ای به این صورت است که بزرگترین عدد قبل از عدد مورد جستجو را معیار جستجو قرار می دهد.
بنابراین شکل نهایی فرمول HLOOKUP به صورت زیر خواهد شد:
در نهایت با فشردن کلید Enter مشخص می شود که رتبه نفر اول B است.
اکنون با دوبار کلیک کردن روی نقطه مشخص شده در تصویر بالا یا Drag کردن آن و استفاده از قابلیت Fill Sequence، می توانیم به سادگی رتبه همه افراد را مشاهده کنیم.
خطاهای تابع HLOOKUP
خطای N/A#: این خطا به معنای موجود نبودن (Not Available) یا پیدا نکردن است و زمانی نمایش داده می شود که مقداری که در حال جست و جوی آن هستیم، موجود نباشد.
خطای !REF#: این خطا به معنای اشتباه وارد شدن رفرنس (Reference) می باشد و زمانی نمایش داده می شود که آرگومان سوم تابع Hlookup عددی بزرگ تر از تعداد سطرهای جدول داده باشد.
خطای !VALUE#: این خطا برای زمانی است که در آرگومان سوم عددی منفی وارد کنیم.
اکنون باید درک کرده باشید که دو فرمول HLOOKUP و VLOOKUP در اکسل یک کار را انجام می دهند با این تفاوت که فرمول HLOOKUP به صورت ردیفی جستجو می کند و جواب را در ستون متناظر پیدا می کند، در صورتی که فرمول VLOOKUP به صورت ستونی جستجو می کند و جواب را در سطر متناظر آن پیدا می کند.
درباره آمنه نوروزیان
دانش آموخته علم ژنتیک و بیوتکنولوژی دانشگاه تهران، علاقه مند به زیست محاسباتی و یادگیری مطالب جدید
نوشته های بیشتر از آمنه نوروزیانمطالب زیر را حتما مطالعه کنید
جستجوی چند شرطی در اکسل
قبلا با توابع جستجو مانند Vlookup آشنا شدیم. همانطور که به خاطر دارید تابع Vlookup به نحوی طراحی شده است که صرفا قادر است یک مقدار یا عبارت را جستجو کرده و مقدار مربوط به آن را در ردیف دلخواه برگرداند. اما بسیار اتفاق می افتد که نیاز است به جای جستجوی یک مقدار ( یک شرط)، چندین مقدار را جستجو کنیم. ّا ما همراه باشید تا به چگونگی جستجوی چند شرطی در اکسل پی ببریم.
مقایسه دو ستون در اکسل
یکی از پرتکرارترین سوال های کاربران اکسل این است که «چگونه دو ستون را در اکسل مقایسه کنیم؟» مقایسه دو ستون در اکسل بسته به نیاز کاربران به روش های مختلفی انجام می شود. در این مطلب قصد داریم به بررسی این روش ها بپردازیم.
کاربرد تابع offset در اکسل
در این قسمت از مطلب آموزشی قصد داریم با یکی از پرکاردترین توابع جستجوی اکسل به نام تابع Offset آشنا شویم. این تابع در ایجاد نمودارهای پویا و محدوده های داینامیک کاربرد دارد که می تواند در تهیه نرم افزار، گزارش گیری حرفه ای و تهیه داشبوردهای مدیریتی مورد استفاده قرار گیرد.
کاربرد علامت دلار در اکسل
ممکن است برخی از کاربران علامت $ یعنی دلار در اکسل را فقط یک واحد پولی تلقی کنند. در این مطلب قصد داریم به سایر کاربردهای شگفت انگیز علامت دلار در اکسل اشاره کنیم. با ما همراه باشید.
کاربرد علامت تعجب در اکسل
در این مطلب به بررسی کاربرد های علامت تعجب (Exclamation mark) در آدرس دهی در فرمول ها و نمودار ها و نام گذاری محدوده ها می پردازیم.
if های تو در تو در اکسل
برای تحلیل قدرتمند داده، ممکن است نیاز باشد که شرط های چندگانه ای را به صورت همزمان مورد ارزیابی قرار دهید، به این معنی که با استفاده از آزمون های منطقی پیچیده از چندین تابع IF در یک فرمول استفاده کنید. این IFهای تو در تو اکسل امکان بررسی چندین شرط را فراهم می کند.