-
بکارگیری تابع Hlookup در اکسل
در آموزش های قبلی در مورد تابع Vlookup و کاربرد آن صحبت کردیم و گفتیم که این تابع جزو توابع جستجو می باشد و از طریق آن میتوانیم عملیات جستجوی را بصورت عمودی Vertical انجام و یک سلول را در یک ستون جستجو کرده و مقدار متناظر با آن را در ستون های بعدی پیدا کنیم ، اما در این مطالب عملیات جستجو را بصورت افقی horizontal توسط تابع Hlookup در اکسل انجام خواهیم داد.
-
گرامر تابع Hlookup
HLOOKUP (lookup_value , table_array , row_index , [range-lookup])=
- Lookup_value: مقداری که قصد داریم در اولین سطر جدول جستجو کنیم را در این قسمت قرار میدهیم. این مقدار میتواند از نوع عدد ؛ تاریخ ؛ متن ؛ آدرس سلول و مقدار بازگشتی توسط تابع دیگری باشد. لازم به ذکر است که مقادیر متنی را همیشه باید در داخل ” ” قرار دهیم.
- Table_array: محدوده مورد نظر که میخواهیم جستجو در داخل آن انجام شود که شامل دو یا چند سطر از اطلاعات قرار گرفته در جدول است. تابع HLOOKUP همواره در سطر اول جدول (که میتواند با سطر اول اکسل متفاوت باشد) ، مقدار مورد نظر را جستجو میکند که میتواند دارای متن ، اعداد ، تاریخ و مقادیر منطقی (True ؛ False) باشد.
- row_index_num: شماره سطری که قصد داریم مقدار مورد نظر از آن بازگردانده شود را در این بخش وارد کنید.
- range_lookup: در این قسمت تعیین میکنیم که آیا برای جستجو به مطابقت تقریبی یا دقیق نیاز داریم.
-
- TRUE (مطابقت تقریبی): اگر در هنگام جستجو تطابق دقیقی پیدا نشود ، تابع نزدیکترین مطابقت را جستجو خواهد کرد.
- FALSE (مطابقت دقیق): این فرمول مقداری دقیقاً برابر با مقداری که جستجو می شود را پیدا خواهد کرد.
در این مطلب آموزشی برای درک بهتر شما کاربران عزیز وب سایت مستر پلنر از همان مثالی استفاده خواهیم کرد که در آموزش تابع Vlookup از آن استفاده کردیم .
به تصویر زیر نگاه کنید ، قصد داریم تا از باکس جستجوی تعبیه شده با وارد کردن شناسه مشتری ، سایر اطلاعات وی را شامل نام و نام خانوادگی ، استان و تعداد سفارشات بدست آوریم.
برای انجام اینکار آرگومان Lookup_value سلول A7 خواهد بود و ما بایستی فرمول را در سلول های A8 , A9 , A10 بنویسیم تا اطلاعات مورد نیاز خودمان را از داخل جدول حاوی اطلاعات پیدا کرده و به نمایش درآوریم.
البته بجای نوشتن فرمول در سه سلول میتوانیم یکبار آن را در سلول A8 برای آیتم نام و نام خانوادگی نوشته و از طریق آدرس دهی مطلق آرگومان اول و دوم یعنی Lookup_value وTable_array از طریق کشیدن ، آنرا به سلول های A9 , A10 تعمیم دهیم.
همانطور که در تصویر بالا ملاحظه میکنید ، تابع hlookup را برای آیتم نام و نام خانوادگی مورد استفاده قرار دادیم ، نکته مهمی که در اینجا لازم است به آن اشاره کنیم ، این است که الزامأ جدول شما از سطر و ستون 1 اکسل ممکن است شروع نشود لذا در انتخاب جدول در آرگومان 2 تابع باید به این مسأله توجه داشته باشید ؛ لذا در این مثال آموزشی برای درک بهتر و آسانتر جدول را از سطر و ستون 1 آغاز کردیم.
حالا به راحتی میتوانیم فرمول را به آیتم های بعدی نیز تعمیم دهیم ولی توجه به آرگومان 3 تابع باید داشته باشیم تا سطر صحیح را در این قسمت معرفی کنیم تا مقدار جستجو شده نتیجه را به درستی برگرداند ، به زبان ساده تر برای آیتم استان آرگومان 3 تابع یا همان row_index_num را باید روی 3 قرار دهیم ، چون سطر حاوی استان در جدول سطر 3 است (البته چون در اینجا مبدأ جدول از 1 بوده به این شکل درآمده لذا اگر جدول از محل دیگری شروع شود باید شماره آن سطر را وارد کرد).
HLOOKUP($B$7;$B$1:$K$4;3;FALSE)
-
ترکیب تابع IFERROR و HLOOKUP
در مواقعی که مقدار جستجو شده خالی باشد یا اگر پیدا نشود با خطای N/A# یا همان not available روبرو خواهیم شد که برای نمایش یک پیغام جایگزین در این مواقع میتوانیم از ترکیب تابع iferror و hlookup استفاده کنیم.
برای انجام اینکار مطابق تصویر زیر کل فرمولی را که در مرحله قبل داریم در داخل تابع IFERROR قرار میدهیم تا بتوانیم در صورت پیدا نشدن مقدار مورد جستجو پیام مورد نظر را به نمایش در آوریم ، البته توجه به این نکته لازم است که تابع IFERROR برای نمایش یک پاسخ در هنگام مواجهه با خطاها در اکسل میتواند مورد استفاده قرار گیرد.
(“یافت نشد”,HLOOKUP($B$7;$B$1:$K$4;3;FALSE))IFERROR
