اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

توابع اکسل

  • خانه
  • بلاگ
  • توابع اکسل
  • جستجو و فراخوانی داده در ردیف با تابع Hlookup

جستجو و فراخوانی داده در ردیف با تابع Hlookup

  • ارسال شده توسط حسنا خاکزاد
  • دسته بندی توابع اکسل
  • تاریخ ۱۴ بهمن ۱۳۹۶
  • نظرات ۱۷ دیدگاه ها
تابع hlookup در اکسل
4.8 / 5 ( 13 امتیاز )

جستجو و فراخوانی با تابع 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 دقت کنید:

جستجو بصورت افقی در دیتابیس با استفاده از تابع Hlookup در اکسل

شکل 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، جدولی داریم که دمای هوا و میزان رطوبت در روزهای مختلف ثبت شده و ما میخوایم میزان رطوبت در تاریخ مورد نظر رو فراخوانی کنیم. اما تاریخ مورد نظر در داده های جدول موجود نیست.

جستجوی تخمینی با استفاده از تابع Hlookup در اکسل

شکل 2- جستجوی تخمینی با استفاده از تابع Hlookup در اکسل

همونطور که میبینید، تاریخ 24 فوریه در داده های جدول موجود نیست. برای اینکه جستجو انجام بشه و نزدیکترین داده رو پیدا کنه، آرگومان آخر رو 1 یا True میزنیم. با توجه به مثالی که برای Vlookup زده شد و همینطور این مثال، می بینید که این حالت فقط در جستجوی اعداد مورد استفاده است.

نکته:
تاریخ میلادی از جنس عدد هست. مثلا تاریخ 24 فوریه معادل عدد 43155 است. حتما پست مربوط به منطق تاریخ در اکسل رو مطالعه کنید.

 

*منطق جستجو در حالت تخمینی چی هست؟؟

بزرگترین عدد قبل از عدد مورد جستجو رو معیار جستجو قرار میده. در مثال بالا بزرگترین تاریخ قبل از 24 فوریه، 16 فوریه است که این تابع 16 فوریه رو پیدا کرده و میزان رطوبت مربوط به روز 16 فوریه یعنی 90% رو نمایش میده.

سوال: اگه بخوایم طوری فرمول نویسی کنیم که با انتخاب رطوبت، رطوبت رو بده و با انتخاب دما، دما، چکار باید کرد؟ بعبارتی، طوری فرمول نویسی کنیم که شماره ردیف Row-Index مورد نظر خودش تغییر کنه؟

جواب رو در ادامه در کامنت ثبت کنید.

 

برچسب:تابع Hlookup, متوسط

  • اشتراک گذاری:
profile avatar
حسنا خاکزاد
فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

مطلب قبلی

تابع InputBox ابزاری برای تعامل با کاربر
۱۴ بهمن ۱۳۹۶

مطلب بعدی

ساخت جملات داینامیک در اکسل
۱۴ بهمن ۱۳۹۶

ممکن است همچنین دوست داشته باشید

Text-Functions-Cover
توابع ویرایش متن انگلیسی در اکسل
۱۷ مرداد, ۱۳۹۹
Replace Function Cover-min
آموزش کار با تابع Replace
۱۲ فروردین, ۱۳۹۹
Mid Function Cover-min
تابع Mid و چند کاربرد در اکسل
۱ دی, ۱۳۹۸

    17 نظر

  1. profile avatar
    mirzaei
    ۵ آذر ۱۳۹۹
    پاسخ

    با سلام و عرض ادب
    سئوالی داشتم 40 شیت مربوط به مرخصی(استحقاقی، استعلاجی،ساعتی) کارمندان برای هر کارمند یک شیت درست کردم
    می خواهیم مرخصی های فصل تابستان را از هر شیت بگیرم و در جدولی در یک شیت دیگر فراخوانی کنم لطفا تابعی که بتواند این کار رو انجام بده رو نام ببرید
    ممنونم

    • profile avatar
      حسنا خاکزاد
      ۵ آذر ۱۳۹۹
      پاسخ

      درود بر شما
      جوابتون بستگی به ساختار فایل و نحوه ورود داده داره
      همینطوری نمیشه راهی پیشنهاد داد

  2. profile avatar
    mas
    ۱۷ فروردین ۱۳۹۸
    پاسخ

    اگر یک فایل جمع بندی چند شیت داشته باشیم که اسم و مشخصات قطعات موجود در تجهیزات کل کارخانه در یک شیت و در شیت های دیگر سوابق تعمیر و اسم قطعات جدید هر تجهیز بعد از تعمیر را داشته باشیم چطور میتوانیم در شیت کلی با تابع لوک اپ یا ایندکس بگیم اسم اخرین قطعه جایگزین شده در تجهیز رو نشون بده
    (یعنی لیست تعمیرات ما مثلا ردیف1 طی تعمیر در روز 17 فروردین کلید “ط” رو گذاشته چند وقت بعد در0 2 فروردین کلید “ب” رو گذاشته” میخوایم تولیست جمع بندی کلید موجود در تجهیز کلید “ب” نشون داده بشه

    • profile avatar
      حسنا خاکزاد
      ۱۷ فروردین ۱۳۹۸
      پاسخ

      درود بر شما
      خیلی بستگی به ساختار فایلتون داره، که داده ها چطور چیده سده باشن.
      بصورت کلی، اگر داده ها شماره ردیف دارن، میتونید بزرگترین شماره ردیف رو ملاک قرار بدید با dmax و بعد match کنید و نتیجه رو بذارید داخل index
      این حالت غیر آرایه ای هست.
      حالت آرایه ای هم که میتونید اول با if ردیف های تجهیز مورد نظر رو پیدا کنید و بعد بزرگترین row رو match کنید و بذارید داخل index

  3. profile avatar
    مجتبی خادمی
    ۶ اسفند ۱۳۹۶
    پاسخ

    با سلام من نتونستم فرمول رو ثبت کنم براتون فایل رو ایمیل کردم شما زحمتش رو بکشید با تشکر و سپاس

    • profile avatar
      حسنا خاکزاد
      ۶ اسفند ۱۳۹۶
      پاسخ

      درود
      فایل رو به گروه تلگرامی بفرستید تا اونجا راهنمایی بشید….

      لینک گروه در فوتر صفحه اصلی سایت موجود هست

  4. profile avatar
    مجتبی خادمی
    ۵ اسفند ۱۳۹۶
    پاسخ

    با سلام و عرض خسته نباشید
    می خواستم از بین 2000000 عدد که از ( 0 تا 40) بطور نامرتب و بصورت عمودی در سلول جا گرفتن اعدادی منحصر به فردی را جتسجو کنم که داری شرایط خاص باشند رو جستجو کرد ؟ یعنی اگر سلول اول 5 سلول دوم 3 سول سوم 32 باشد تا الی آخر من می خواهم چهار عدد منحصر بفرد که پشت سر هم تکرار شده رو از بین این اعداد مشخص کنم مثلا ( اعداد 27 و 27 و 35 و 28 ) لطفا راهنمایی بفرمایید

    • profile avatar
      حسنا خاکزاد
      ۵ اسفند ۱۳۹۶
      پاسخ

      درود بر شما

      از این فرمول استفاده کنید:

      1
      =IF(SUM((OFFSET(B2,0,0,4,1)={27;27;35;28})*1)=4,1,"")

      این فرمول بصورت آرایه هست و باید با Ctrl+shift+enter ثبت بشه

      نکته اول: در ستون کنار اعداد، اختلاف ها رو بدست بیارید. یعنی اگر در ستون A اعدا نوشته شده، در ستون B بنویسید: A2-A3 و درگ کنید تا اختلاف ها محاسبه بشه.
      بعد هم فرمول بالا رو بنویسید.

      جهت آشنای با تابع Offset پست زیر رو بخونید:
      https://excelpedia.net/offset-function/

      جهت آشنایی با فمرول نویسی آرایه ای هم پست زیر:
      https://excelpedia.net/array-formula/

  5. profile avatar
    محمد
    ۱۷ بهمن ۱۳۹۶
    پاسخ

    با سلام
    دو سوال داشتم.
    ا. آیا قالب آماده اکسل سراغ دارید که بتوان به عنوان to do list حرفه ای از آن استفاده کرد.
    ۲. من یک فایل اکسل با چهار ستون دارم که اطلاعات به صورت نامرتب زیر هم درج شده اند. به این صورت که
    ستون یک= نام افراد (مثلا ۲۰ نفر)
    ستون دوم = عنوان فعالیت/ کاری که به آنها محول شده است( کارهای مختلف و غیر تکراری)
    ستون سوم= تاریخی که کار به افراد محول شده است.
    ستون چهارم = شرکت /اداره ای که فعالیت مد نظر به آن مرتبط است.

    میخواهم به شکل تفکیک شده در یک شیت مجزا
    نام هر فرد با کارهایی که به او محول شده است مرتب شده بیاید.
    لطفا راهنمایی کنید . با تشکر و سپاس بیکران

    • profile avatar
      سامان چراغی
      ۱۷ بهمن ۱۳۹۶
      پاسخ

      سلام
      1- منظورتون از حرفه ای مشخص نیست. قالب های آماده بسیاری وجود داره که برای کارهای مختلف ایجاد شده که هر مورد برای کار مورد نظر میتونه حرفه ای باشه. در آینده این قالب ها و فایل های آماده رو هم در سایت قرار میدیم.
      2- بهترین ابزاری برای حل این مسئله استفاده از پیوت تیبل هست.

  6. profile avatar
    نوید محبی
    ۱۶ بهمن ۱۳۹۶
    پاسخ

    در قسمت شماره سلول فراخوانی شده ار شرطif استفاده می کنیم بطوری که خروجی شرط با توجه به نوع جستجو ( دمای هوا – رطوبت ) 2 یا 3 باشد

    • profile avatar
      حسنا خاکزاد
      ۱۶ بهمن ۱۳۹۶
      پاسخ

      احسنت
      این یک راه هست که کاملا هم درست ج میده. ساختارش میشه این…

      1
      =Hlookup(lookup value, table Array, if(    ,2,3),False)

      اما یک راه بهینه تر هم هست، برای جدول های بزرگتر که نوشتن If مشکل میشه…. اونم استفاده از تابع Match هست. چون خروجی این تابع عدده و مکان یک سلول رو در یک محدوده میده، خروجی مد نظر ما رو میسازه.برای این تابع آموزش زیر رو ببینید:

      https://excelpedia.net/match-function/

  7. profile avatar
    مهدی
    ۱۵ بهمن ۱۳۹۶
    پاسخ

    میتونیم بعد از نام گذاری محدوده ها به جای آرگومان Row_index_Num از اسم همون محدوده استفاده کنیم
    مثلا توی مثالی که توضیح دادید سلولهای b2 تا f2 را دمای هوا و b3 تا f3 را رطوبت نامگذاری میکنیم. بعد به جای آرگومان سوم اسم رطوبت یا دمای هوا را قرار میدیم.

  8. profile avatar
    مهدی
    ۱۴ بهمن ۱۳۹۶
    پاسخ

    سلام
    سؤالی که پرسیدید نامفهومه. اگه میشه واضح‌تر بیان کنید

    • profile avatar
      حسنا خاکزاد
      ۱۴ بهمن ۱۳۹۶
      پاسخ

      درود بر شما

      ببینید در تعیین Row_Index تا الان بصورت دستی شماره ثبت کردیم. مثلا گفتیم 2
      حالا سوال اینه که چکار کنیم که این شماره بصورت خودکار ایجا دبشه. یعنی اگر در یک سلول انتخاب کردیم دمای هوا، Row-Index بشه 2 و اگه انتخاب کردیم رطوبت، Row-Index بشه 3

      امیدوارم واضح شده باشه

      • profile avatar
        مهدی
        ۱۵ بهمن ۱۳۹۶
        پاسخ

        میتونیم با نامگذاری محدوده ها این سؤال را حل کنیم
        مثلا توی مثالی که خودتون بیان کردید سلولهای B2 تا F2 را دمای هوا و B3 تا F3 را رطوبت نامگذاری میکنیم. بعد به جای آرگومان سوم اسم محدوده را مینویسیم.

        • profile avatar
          حسنا خاکزاد
          ۱۶ بهمن ۱۳۹۶
          پاسخ

          محدوده نامگذاری رو چطور فراخوان یکنیم؟ مهم داینامیک بودن هست. چون در واقع با این کار که شما فرمودید، بجای شماره 2 محدوده نامگذاری دما و بجای شماره 3 محدوده نامگذاری رطوبت داریم.
          باز هم بحث فراخوانیش میممونه

          راهنمایی اینکه، تابعی پیدا کنید که مکان سلول رو در یک محدوده به ما بده (عدد) که بتونیم در آرگومان سوم تابع Hlookup بذاریم.

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

از طریق ایمیل، مطالب آموزشی درباره اکسل و برنامه نویسی وی بی و سایر رویدادهای مهم را دریافت خواهید کرد.
  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

آخرین مطالب

نصب Power Query
فعال کردن Power Query در ورژن های مختلف اکسل
۰۷اسفند۱۳۹۹
3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل
۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل
۰۱اسفند۱۳۹۹
مدیریت لینک
مدیریت لینک ها در اکسل
۲۷بهمن۱۳۹۹
جستجو پیشرفته با Wild Card
جستجو پیشرفته در اکسل با استفاده از Wildcard
۲۲بهمن۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما