تابع HLOOKUP

تابع HLOOKUP

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

قاعده کلی تابع HLOOKUP به شکل زیر هست:

(جستجوی تقریبی [اختیاری]، شماره سطر، محدوده جستجو، مقدار موردنظر برای جستجو) HLOOKUP

برای اینکه با نحوه کاربرد این تابع به صورت عملی آشنا بشیم از یک مثال استفاده میکنیم. جدول زیر رو در نظر بگیرید

A B C D E F
1 تاریخ 1392/2/13 1392/2/14 1392/2/15 1392/2/16 1392/2/17
2 میزان تولید 1000 1300 1240 800 700
3
4 تاریخ میزان تولید
5

اگر بخوایم فرمولی بنویسیم که با توجه به تاریخ وارد شده در سلول A5، مقدار تولید متناظر اون تاریخ رو در سلول B5 نمایش بده می تونیم از تایع HLOOKUP به شکل زیر استفاده کنیم:

مقدار موردنظر برای جستجو:  این پارامتر مقداری رو که قراره در محدوده موردنظر ما جستجو بشه تعیین میکنه. ما در این مثال میخوایم میزان تولید رو برای تاریخی که در سلول A5 وارد کردیم بدست بیاریم. بنابراین مقداری که ما میخوایم بر اساس اون جستجو رو انجام بدیم سلول A5 هست. پس پارامتر اول ما برابر A5 هست.

محدوده جستجو: در این پارامتر ما بازه ای رو که در اون قراره جستجو رو انجام بدیم تعریف میکنیم. این بازه باید هم شامل سطری باشه که ما قراره در اون جستجو رو انجام بدیم (در مثال ما سطر تاریخ) و هم سطری که به نتایج متناطر جستجو از اون هستیم (سطر مربوط به میزان تولید). پس پارامتر دوم ما برابر A1:F2 هست.

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

جستجوی تقریبی: این پارامتر در تابع HLOOKUP اختیاری هست. اگر این مقدار رو برابر TRUE قرار بدید به این معنی هست که اگر مقدار موردنظر برای جستجو در محدوده جستجو وجود نداشت بزرگترین مقدار موجود در محدوده جستجو که کوچکتر از مقدار موردنظر برای جستجو هست به عنوان نتیجه جستجو انتخاب بشه. به عنوان مثال در جدول بالا بزرگترین تاریخ موجود در جدول برابر 1393/2/17 هست. حالا اگر جستجوی تقریبی رو برابر TRUE قرار بدیم و پارامتر مقدار موردنظر برای جستجو رو 1393/2/20 تعیین کنیم، تابع HLOOUP نتیجه رو برای بزرگترین مقدار موجود در محدوده جستجو که که کوچکتر از 1393/2/20 هست (1393/2/17) نشون میده. اگر در این حالت پارامتر جستجوی تقریبی رو برابر  FALSE قرار بدیم تابع خطای !REF# رو برمیگردنه.

خب پس فرمول تابع HLOOKUP در مثال بالا به شکل زیر تکمیل میشه:

(HLOOKUP(A5;A1:F2;2;TRUE

با قرار دادن فرمول بالا در سلول B5، هر تاریخی رو که در سلول A5 وارد کنیم میزان تولید متناطر با اون تاریخ در سلول B5 نمایش داده میشه. به عنوان مثال اگر مقدار سلول A5 رو برابر 1393/2/15 قرار بدیم، عدد 1240 یعنی میزان تولید در تاریخ 1393/2/15 در سلول B5 نمایش داده خواهد شد.

 

40 Comments on “تابع HLOOKUP”

  1. جواد

    با سلام. لطفا در مورد تابع lookup هم توضیحاتی بدهید. مشکلی که من دارم این است که خروجی تابع lookup هرگز مقدار بالای یک ستون را برنمیگرداند و خطا دارد. لطفا راهنمایی نمایید.

  2. مهدی

    سلام
    واقعا متحیر شدم که چنین سایت کاملی وجود داره و من کشفش نکرده بودم!
    واقعا دست مریزاد.
    من با lookup یه مشکل اساسی دارم و به نظرم ربط به فارسی بودن محتوای سلولها داره.
    آیا این تابع با فارسی مشکل داره؟
    فرض کنید محتوای دو ستون به شکل زیر است:
    عمران 1
    تاسيسات مکانيکي ساختمان 2
    معماري 3
    شهر سازي 4

    چرا دستور ساده look up زیر برای من جواب های نادرست میده؟
    =LOOKUP(K2,P17:P20,Q17:Q20)

    با اینکه تو حالت متن ساده و نظرات نمیشه خوب سوال رو مطرح کرد ولی ممنون میشم کمکم کنید.

    1. یاسین اسماعیل پور

      سلام. فایلتو بفرست تا بهتر بتونم کمکت کنم. اینطوری توی نظرات همونطوری که خودت نوشتی راحت نمیشه فهمید مشکل کجاست. در ضمن بهتره به جای تابع lookup از سایر تابع ها استفاده کنی. چون این تابع در نسخه های جدید اکسل با تابع های بهتری جایگزین شده

  3. نیما

    سلام
    من میخوام یک سلول رو تو یک آرایه سرچ کنم و شماره ستونشو بدست بیارم اگه ممکنه راهنمایی کنید

  4. نیما

    سلام خیلی ممنون که حواب منو دادید
    از تابع match استفاده کردم ولی به علت دو بعدی بودن آرایه من جواب نداد
    من شماره ستون آرایه دو بعدی رو که عدد مورد نظرم تو اون ستون هست رو می خوام
    اگه ممکنه دوباره راهنماییم کنید

    1. یاسین اسماعیل پور

      اگر عدد تکراری نداشته باشی میتونی از این فرمول استفاده کنی. ((SUMPRODUCT((A1:E5=x)*COLUMN(A1:E5= با فرض اینکه مقداری که میخوای پیداش کنی x باشه و آرایه دوبعدیت هم A1 تا E5 باشه.

  5. علي محمدشاه

    سلام قربان وقت بخير امكان دارد به سوال من جواب دهيد
    1-دوتا ستون داريم 2-ستون اول 1 تا 10 ستون دوم A تا J روبرو هم
    حالا اگه ستون اوا ماگزيمم بگيريم عدد 10 را نشان ميدهد من مي خام حروف روبروي آنرا هم نشان دهد حالا اگه رديف 5 عدد زياد كرديم خرف متناظر آنرا نشان دهد به همين ترتيب آيا امكان نوشتن خط فرمول برايتان مقدور است خيلي لطف مي فرماييد امتحان دارم گرفتار شدم
    09177132217 pbalim@yahoo.com متشكرم

  6. احمدی

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

  7. maya

    سلام من يكسري اطلاعات در شيتهاي مختلف با يك جدول مشابه دارم ميخوام يكسري اطلاعات از اين شيتها خارج و در جدول ديگري بصورت آماري و سطري وارد شود مثلا يك شماره پرونده را در ساير شيتها در يك سلول مثلا a5جستجو كند در هر شيتي پيدا كرد در همان شيت اطلاعات سطرa10رو وارد شيت جديد در سطر جديد بكنه
    البته مشكل اصلي من در بخش خواندن شيتهاست و نميدونم درسته از فرمول lookup استفاده ميكنم يانه و اگر درسته چكار بايد بكنم كه شيتها خوانده شود و اگر نه از چه فرمولي استفاده كنم؟ ممنون

  8. شادی ایزدفر

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

    1. یاسین اسماعیل پور

      برای اینکار باید از توابعی مثل VLOOKUP و .. استفاده کنی. توضیحش خیلی مفصله دوست من

  9. محمد

    سلام . خسته نباشید
    بنده میخواهم یک تابعی بنویسم که اگر عدد یک سلول از22 کمتر باشد رنگ سلول قرمز ،در صورتی که بین 22 و 25 باشد زرد و در صورتی که بالاتر از 25 باشد رنگ سلول آبی شود (در آن سلول یک تابع درصد گیری در حال حاضر موجود است که با توجه به نتیجه آن می بایست رنگ را تغییر دهد)، یک کمک کامل میخاستم چون از اکسل خیلی نمیدونم .
    خیییییلی ممنون

  10. ت- عابدی

    باسمه تعالی
    یاسین جون سلام عزیزم
    راهنمایی بفرمایید چطوری میتونم با کمک جدولی که بیش از هزار تا اسم و مشخصات داره وقراره به کمک آن نامه ای با یک فرمت برای همه آنها بنویسم از چه تابعی استفاده کنم مثلا جاهای خالی را درنامه زیر کامل کنم و بعد بتونم پرینت انرا در ورد و یا همون اکسل انجام بدم:
    —————————————————————————————————–
    ریاست محترم ……
    بدینوسیله اقای /خانم نام …. نام خانوادگی ….. نام پدر …… با کدملی ……..معرفی میگردد.
    لذا خواهشمند است ……………
    ————————————————————————————————
    ممنون میشم راهنمایی بفرمایید.
    رو ایمیل منتظرم- قربانت

    1. یاسین اسماعیل پور

      سلام دوست من
      شما برای اینکار میتونید از بخش mailings در نرم افزار word استفاده کنید

  11. بهار

    سلام.خوب هستید.خسته نباشید.من ی سری اطلاعاتو از برنامه SAP2000،برای رسم نمودار EXPORT کردم به EXCEL .که حدود 12 فایل (1400ردیف و2 ستونه) هست.ولی برای رسم نمودار من به یکی در میون این ردیف ها نیاز دارم.که چون تعداد خیلی زیاده نمیدونم باید چ کنم.از کس دیگه ای هم ک پرسیدم گفت:
    در یک ستون شماره ردیف ها رو یک در میان وارد کنید که با فرمول اینکار شدنیه و در ستون بعدی با استفاده از index و یا hlookup مقادیر رو فراخوانی کنید و در نهایت نمودار موردنظر رو ترسیم کنید.
    من الان شماره ردیف ها رو یکی در میون واردکردم.ولی نمیدونم چطور باید با HLOOKUP مقادیرو فراخوانی کنم.میشه راهمنماییم کنید.خیلی ب کمکمتون نیاز دارم.مرسی

  12. yaser shams

    استاد میشه داده های یک ستون در یک شیت رو به صورت خودکار تبدیل به سه ستون در شیت دیگر کرد. مثلا ردیف عنوان و مقدار یک سری داده که 90ردیف میشه توی یه شیت دیگه به سه ستون 30ردیفی تبدیل بشه

    1. یاسین اسماعیل پور

      بله میشه با ترکیب تابع index و match اینکار رو میتونید انجام بدید.

  13. سیاوش

    سلام
    من دو تا sheet دارم که تو اولی دو تا ستون دارم .ستون اول کد ملی اشخاص و ستون دوم تلفن همراه اون اشخاص قرار داره.در sheet دومی ستون اول بعضی از اون کد ملی ها هست میخام عمل جستجو طوری صورت بگیره که هر کد ملی دقیقا شماره همراه خودش در sheet اول را پیدا کنه و در ستون دوم sheet دوم قرار بگیره

  14. زمانی

    سلام
    مرسی که اینقده خوب راهنمایی کردید.تشکر
    فقط اینکه من میخوام چندتا مقدار را که در ستون b دارم و نام مقدارها را که در ستون a قرار دارد را جمع کنم و با توجه به اینکه سلول ها به صورت پراکنده هستند و من میخوام اون مقدارها که سلول های نامشان یکی هست باهم جمع کنم باید از چه فرمولی استفاده کنم

  15. asal

    با سلام
    به چه صورت میتونم از دو ستون اسم و شماره حساب با وارد کردن اسم جدید در صورت تکراری بودن اسم وارد شده شماره حساب اون اسم از ستون مربوطه جستجو و در سلول مقابل اسم درج بشه

  16. حامد

    سلام در مثال اول اگر در ردیف سوم مثلا نوع تولید را داشته باشیم و بخواهیم مقدار تولید را بر اساس تاریخ و نو ع تولید lookup نماییم از چه تابعی استفاده می کنیم؟

  17. محمد حسن

    سلام
    از تابع hlookup استفاده کردم ولی جواب خطا #refو در چند گزینه برای جستجو N/A ، راهنمایی کنید

    1. یاسین اسماعیل پور

      سلام دوست عزیز
      لطفاً فایلت رو از بخش تماس برام ارسال کن برات بررسی میکنم

  18. امید

    سلام چگونه می توان در یک بانک اطلاعاتی 5عدد آخرین ورودی یک کد کالا را تنها با زدن کد کالا فراخوان کرد

  19. maryam-l

    سلام. من تابع vlookup رو استفاده کردم. فقط کاری که میخوام انجام بدم دو سطر و دو ستون رو باید همزمان جستجو کنه. و میدونید که این تابع vlookup یک سطر و یک ستون رو میشه انتخاب کرد. میخواستم بدونم چطوری میشه انتخاب یک سطر رو به دو تا ارتقا داد. ممنون

  20. محیا

    سلام و ممنون اط آموزش خوبتون.
    من این کار رو انجام دادم و فقط برای همون تاریخ جواب گرفتم. وقتی تاریخ جدید وارد می کنم حتی با گسترش تابع به ستون های پایینی هم جوابش نمیاد و باید تک تک HLOOK تعریف کنم براشون. چکار باید بکنم؟

  21. zahi

    با سلام
    من یک لیست اسامی تو اکسل دارم میخوام کنارش یک تکست باکس ایجاد کنم که وقتی داخلش یک حرف مثلا م رو میزنم تمام اسامی که با م شروع میشه رو بهم بده میشه راهنماییم کنین

پاسخ دهید

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