جستجو و فراخوانی داده در ردیف با تابع Hlookup
جستجو و فراخوانی با تابع Hlookup در اکسل
جستجو و فراخوانی در اکسل، از مباحث حرفه ای و کاربردی به شمار می ره. به همین دلیل هست که یک دسته از توابع در اکسل به این موضوع مهم اختصاص داده شده. دسته توابع Lookup & Reference، همه توابع مربوط به جستجو و فراخوانی و ارجاع را در خود جای داده است. توابعی که در این دسته هستند بسیار بسیار توابع مهم و پرکاربردی هستن. مخصوصا که این توابع هنگامی که با یکدیگر و سایر توابع ترکیب می شوند نتایج فوق العاده ای خلق می کنند. توابع Index ،Match ،Offset ،Vlookup در مقلات قبلی آموزش داده شد، در این آموزش تابع Hlookup در اکسل رو توضیح میدم.
تابع Vlookup و Hlookup خیلی مشابه هستن و هر دو آرگومان های یکسانی دارن، تنها تفاوت این دو تابع د رجهت جستجو است. یعنی تابع Vlookup آرگومان های این تابع دقیقا مشابه تابع Vlookup هست. تفاوتی که این دو تابع با هم دارن، جهت جستجوی آنها می باشد. یعنی Vlookup بصورت عمودی (در ستون) جستجو رو انجام میده و Hlookup بصورت افقی (در ردیف).
آرگومان های تابع Hlookup
این تابع شامل چهار آرگومان به شرح زیر است:
Lookup_Value : عبارت یا سلی که میخواهیم جستجو کنیم.
Table_Array : جدولی که جستجو در آن انجام میشود.
Row_index_Num: شماره ردیفی از جدول است که میخواهیم برگردانده شود.(خروجی تابع)
Range_Lookup : تعیین میکند که بصورت دقیق جستجو کند یا تخمینی.
پس همونطور که توضیح داده شد، تابع Hlookup در ردیف اول Table مورد نظر جستجو رو انجام میده، به محض پیدا کردن داده مورد جستجو، داده مورد نرظ در ردیف دلخواه رو برمیگردونه.
همه شرایطی که برای تابع Vlookup حاکمه، برای تابع Hlookup هم برقرار هست. در این خصوص به دو نکته ای که در اموزش Vlookup شرح داده شده دقت کنید.
مثال: در جدول زیر میخوایم نام شهر مورد نظر رو سرچ کنیم و جمعیت مربوط به اون شهر رو فراخوانی کنیم. به شکل 1 دقت کنید:
شکل 1- جستجو بصورت افقی در دیتابیس با استفاده از تابع Hlookup در اکسل
آرگومان اول: موردی است که به جستجوی آن پرداختیم. در اینجا شهر Lookup-Value ما خواهد بود که در سل A4 نوشته شده است.
=HLOOKUP(A4,A1:F2,2,0)
آرگومان دوم: محدوده ای است که جستجو در آن انجام می شود. در اینجا محدوده A1:F2، Table_Array ما خواهد بود.
=HLOOKUP(A4,A1:F2,2,0)
آرگومان سوم: این آرگومان از جنس عدد است و تعیین می کند که چندمین ردیف از محدوده جستجو را برگرداند. در اینجا میخواهیم جمعیت مربوط به شهر انتخابی فراخوانی شود. پس باید ببینیم جمعیت، چندمین ردیف از محدوده جستجو است. در اینجا جمعیت، ردیف دوم از Table_Array است.
=HLOOKUP(A4,A1:F2,2,0)
آرگومان چهارم: مقدار ۰ جستجوی دقیق و عدد ۱ جستجو تخمینی را انجام میدهد. باید به این نکته اشاره کنم که مقدار ۱ کاربردهای خاصی برای برخی مسائل دارد و اغلب اوقات ما آرگومان چهارم را ۰ قرار می دهیم زیرا به دنبال جواب دقیق هستیم.
=HLOOKUP(A4,A1:F2,2,)
جستجوی تخمینی در Hlookup
همونطور که گفته شد، آرگومان آخر این تابع، جستجوی دقیق و تخمینی رو انجام میده. برای جستجوی دقیق، مثال زده شد. برای جستجوی تخمینی هم مثال ارائه میکنیم. حتما مثال جستجوی تخمینی در Vlookup رو مطالعه کنید.
فرض کنید مطابق شکل 2، جدولی داریم که دمای هوا و میزان رطوبت در روزهای مختلف ثبت شده و ما میخوایم میزان رطوبت در تاریخ مورد نظر رو فراخوانی کنیم. اما تاریخ مورد نظر در داده های جدول موجود نیست.
شکل 2- جستجوی تخمینی با استفاده از تابع Hlookup در اکسل
همونطور که میبینید، تاریخ 24 فوریه در داده های جدول موجود نیست. برای اینکه جستجو انجام بشه و نزدیکترین داده رو پیدا کنه، آرگومان آخر رو 1 یا True میزنیم. با توجه به مثالی که برای Vlookup زده شد و همینطور این مثال، می بینید که این حالت فقط در جستجوی اعداد مورد استفاده است.
تاریخ میلادی از جنس عدد هست. مثلا تاریخ 24 فوریه معادل عدد 43155 است. حتما پست مربوط به منطق تاریخ در اکسل رو مطالعه کنید.
*منطق جستجو در حالت تخمینی چی هست؟؟
بزرگترین عدد قبل از عدد مورد جستجو رو معیار جستجو قرار میده. در مثال بالا بزرگترین تاریخ قبل از 24 فوریه، 16 فوریه است که این تابع 16 فوریه رو پیدا کرده و میزان رطوبت مربوط به روز 16 فوریه یعنی 90% رو نمایش میده.
سوال: اگه بخوایم طوری فرمول نویسی کنیم که با انتخاب رطوبت، رطوبت رو بده و با انتخاب دما، دما، چکار باید کرد؟ بعبارتی، طوری فرمول نویسی کنیم که شماره ردیف Row-Index مورد نظر خودش تغییر کنه؟
جواب رو در ادامه در کامنت ثبت کنید.
برچسب:تابع Hlookup, متوسط
17 نظر
با سلام و عرض ادب
سئوالی داشتم 40 شیت مربوط به مرخصی(استحقاقی، استعلاجی،ساعتی) کارمندان برای هر کارمند یک شیت درست کردم
می خواهیم مرخصی های فصل تابستان را از هر شیت بگیرم و در جدولی در یک شیت دیگر فراخوانی کنم لطفا تابعی که بتواند این کار رو انجام بده رو نام ببرید
ممنونم
درود بر شما
جوابتون بستگی به ساختار فایل و نحوه ورود داده داره
همینطوری نمیشه راهی پیشنهاد داد
اگر یک فایل جمع بندی چند شیت داشته باشیم که اسم و مشخصات قطعات موجود در تجهیزات کل کارخانه در یک شیت و در شیت های دیگر سوابق تعمیر و اسم قطعات جدید هر تجهیز بعد از تعمیر را داشته باشیم چطور میتوانیم در شیت کلی با تابع لوک اپ یا ایندکس بگیم اسم اخرین قطعه جایگزین شده در تجهیز رو نشون بده
(یعنی لیست تعمیرات ما مثلا ردیف1 طی تعمیر در روز 17 فروردین کلید “ط” رو گذاشته چند وقت بعد در0 2 فروردین کلید “ب” رو گذاشته” میخوایم تولیست جمع بندی کلید موجود در تجهیز کلید “ب” نشون داده بشه
درود بر شما
خیلی بستگی به ساختار فایلتون داره، که داده ها چطور چیده سده باشن.
بصورت کلی، اگر داده ها شماره ردیف دارن، میتونید بزرگترین شماره ردیف رو ملاک قرار بدید با dmax و بعد match کنید و نتیجه رو بذارید داخل index
این حالت غیر آرایه ای هست.
حالت آرایه ای هم که میتونید اول با if ردیف های تجهیز مورد نظر رو پیدا کنید و بعد بزرگترین row رو match کنید و بذارید داخل index
با سلام من نتونستم فرمول رو ثبت کنم براتون فایل رو ایمیل کردم شما زحمتش رو بکشید با تشکر و سپاس
درود
فایل رو به گروه تلگرامی بفرستید تا اونجا راهنمایی بشید….
لینک گروه در فوتر صفحه اصلی سایت موجود هست
با سلام و عرض خسته نباشید
می خواستم از بین 2000000 عدد که از ( 0 تا 40) بطور نامرتب و بصورت عمودی در سلول جا گرفتن اعدادی منحصر به فردی را جتسجو کنم که داری شرایط خاص باشند رو جستجو کرد ؟ یعنی اگر سلول اول 5 سلول دوم 3 سول سوم 32 باشد تا الی آخر من می خواهم چهار عدد منحصر بفرد که پشت سر هم تکرار شده رو از بین این اعداد مشخص کنم مثلا ( اعداد 27 و 27 و 35 و 28 ) لطفا راهنمایی بفرمایید
درود بر شما
از این فرمول استفاده کنید:
این فرمول بصورت آرایه هست و باید با Ctrl+shift+enter ثبت بشه
نکته اول: در ستون کنار اعداد، اختلاف ها رو بدست بیارید. یعنی اگر در ستون A اعدا نوشته شده، در ستون B بنویسید: A2-A3 و درگ کنید تا اختلاف ها محاسبه بشه.
بعد هم فرمول بالا رو بنویسید.
جهت آشنای با تابع Offset پست زیر رو بخونید:
https://excelpedia.net/offset-function/
جهت آشنایی با فمرول نویسی آرایه ای هم پست زیر:
https://excelpedia.net/array-formula/
با سلام
دو سوال داشتم.
ا. آیا قالب آماده اکسل سراغ دارید که بتوان به عنوان to do list حرفه ای از آن استفاده کرد.
۲. من یک فایل اکسل با چهار ستون دارم که اطلاعات به صورت نامرتب زیر هم درج شده اند. به این صورت که
ستون یک= نام افراد (مثلا ۲۰ نفر)
ستون دوم = عنوان فعالیت/ کاری که به آنها محول شده است( کارهای مختلف و غیر تکراری)
ستون سوم= تاریخی که کار به افراد محول شده است.
ستون چهارم = شرکت /اداره ای که فعالیت مد نظر به آن مرتبط است.
میخواهم به شکل تفکیک شده در یک شیت مجزا
نام هر فرد با کارهایی که به او محول شده است مرتب شده بیاید.
لطفا راهنمایی کنید . با تشکر و سپاس بیکران
سلام
1- منظورتون از حرفه ای مشخص نیست. قالب های آماده بسیاری وجود داره که برای کارهای مختلف ایجاد شده که هر مورد برای کار مورد نظر میتونه حرفه ای باشه. در آینده این قالب ها و فایل های آماده رو هم در سایت قرار میدیم.
2- بهترین ابزاری برای حل این مسئله استفاده از پیوت تیبل هست.
در قسمت شماره سلول فراخوانی شده ار شرطif استفاده می کنیم بطوری که خروجی شرط با توجه به نوع جستجو ( دمای هوا – رطوبت ) 2 یا 3 باشد
احسنت
این یک راه هست که کاملا هم درست ج میده. ساختارش میشه این…
اما یک راه بهینه تر هم هست، برای جدول های بزرگتر که نوشتن If مشکل میشه…. اونم استفاده از تابع Match هست. چون خروجی این تابع عدده و مکان یک سلول رو در یک محدوده میده، خروجی مد نظر ما رو میسازه.برای این تابع آموزش زیر رو ببینید:
https://excelpedia.net/match-function/
میتونیم بعد از نام گذاری محدوده ها به جای آرگومان Row_index_Num از اسم همون محدوده استفاده کنیم
مثلا توی مثالی که توضیح دادید سلولهای b2 تا f2 را دمای هوا و b3 تا f3 را رطوبت نامگذاری میکنیم. بعد به جای آرگومان سوم اسم رطوبت یا دمای هوا را قرار میدیم.
سلام
سؤالی که پرسیدید نامفهومه. اگه میشه واضحتر بیان کنید
درود بر شما
ببینید در تعیین Row_Index تا الان بصورت دستی شماره ثبت کردیم. مثلا گفتیم 2
حالا سوال اینه که چکار کنیم که این شماره بصورت خودکار ایجا دبشه. یعنی اگر در یک سلول انتخاب کردیم دمای هوا، Row-Index بشه 2 و اگه انتخاب کردیم رطوبت، Row-Index بشه 3
امیدوارم واضح شده باشه
میتونیم با نامگذاری محدوده ها این سؤال را حل کنیم
مثلا توی مثالی که خودتون بیان کردید سلولهای B2 تا F2 را دمای هوا و B3 تا F3 را رطوبت نامگذاری میکنیم. بعد به جای آرگومان سوم اسم محدوده را مینویسیم.
محدوده نامگذاری رو چطور فراخوان یکنیم؟ مهم داینامیک بودن هست. چون در واقع با این کار که شما فرمودید، بجای شماره 2 محدوده نامگذاری دما و بجای شماره 3 محدوده نامگذاری رطوبت داریم.
باز هم بحث فراخوانیش میممونه
راهنمایی اینکه، تابعی پیدا کنید که مکان سلول رو در یک محدوده به ما بده (عدد) که بتونیم در آرگومان سوم تابع Hlookup بذاریم.