آموزش تابع look up در اکسل(100% کاربردی بهمراه ویدیو رایگان+نکات ویژه)
آموزش تابع look up در اکسل ، شامل دو تابع Hlookup وVlookupمی باشد:
تابع LOOKUP در اکسل چه کاری انجام می دهد ؟
تابع LOOKUP امکان جستجو در ستون ها و ردیف های اکسل را میسر می کند و می تواند مقادیر متناظر با ستون یا ردیف خاصی را نمایش دهد .
تابع جستجو Vlookup(جستجوی ستونی سلول ها در اکسل)
تابع جستجو Hlookup(جستجوی افقی سلول ها در اکسل )
آموزش تابع VLOOKUP
امکان جستجوی ستونی در نرم افزار اکسل را فراهم می کند .
و همچنين کاربرد ديگر تابع جستجوي Vlookup اين است که اگر براي محاسبات ، لازم باشد از if تو در تو استفاده کنيم و تعداد if ها خيلي زياد شود فرمول پيچيده و تغيير و درک آن سخت مي شود .
براي مثال براي محاسبه ماليات برحقوق پرسنل مختلف با کمک if تو در تو زياد ، فرمول پيچيده و سخت به نظر مي رسد اما مي توانيم با استفاده از تابع Vlookup محاسبات قابل درک تر و ساده تري داشته باشيم
این تابع 4 آرگومان بصورت ذیل دارد :
(lookup_value;table_array;col_index_num;[range_lookup])vlookup
برای درک بهتر مطلب با یک مثال ، آرگومان های تابع و کار با تابع را توضیح می دهیم .
می خواهیم با وارد کردن کد دانش آموزی در سلول خاص ، معدل دانش آموز در سلول مجاور جستجو و نمایش داده شود .
lookup_value:اولین آرگومان این تابع است و مقادیری را که در ستون ها می خواهیم جستجو کنیم را شامل می شود .
- این آرگومان مقادیر متنی ، عددی ،True و False را می تواند شامل شود.
- درمثال بالا با وارد کردن کد دانش آموز می خواهیم معدل دانش آموز جستجو و نمایش داده شود .
- پس دراین مثال آرگومان اول این تابع شامل مقدار کد دانش آموزی می شود.
- برای مثال می توانیم کد دانش آموزی 1005 را به عنوان آرگومان اول وارد و یا آدرس سلولی که این عدد در آن قرار دارد یعنی I9 را به عنوان آرگومان اول وارد کنیم .
Table_array:محدوده ی جدول مورد جستجو تابع VLOOKUP می باشد .که حتما باید شامل ، ردیف مقدار مورد جستجوی lookup_value و ردیف مقدار متناظر باشد.
- در مثال بالا محدوده مورد جستجو کل جدول می باشد که از سلول A1 شروع و تا سلول D8 ادامه دارد .
- و شامل مقدار lookup_value یعنی کد دانش آموزی که در ستون اول است و ستون متناظر آن ، که بعد از جستجو باید نمایش داده شود یعنی معدل می باشد.
Col_index_num: شماره ستونی از جدول مورد جستجو را نمایش می دهد که می خواهیم بر اساس آرگومان اول به ما نشان دهد .
- در مثال بالا با ورود کد دانش آموزی باید معدل نشان داده شود.
- معدل در ستون چهارم جدول فوق قراردارد پس مقدار این آرگومان 4 می باشد .
Range_lookup: این تابع دو مقدار را می گیرد :
- مقدار False: یا مقدار صفر به این معنی است که این تابع دقیقا مقدار آرگومان اول یعنی lookup_value را جستجو می کند و در صورت یافتن نمایش می دهد اگر این مقدار را نیافت خطای N/A# را نمایش می دهد.
- در مثال فوق با ورود کد دانش آموزی 1005 در سلول I9 و یا نوشتن مقدار1005 به عنوان آرگومان اول تابع و انتخاب گزینه False برای آرگومان آخر (Range_lookup)
- طبق شکل فوق ،دقیقا کد دانش آموزی 1005 جستجو و براساس آن مقدار متناظر ش یعنی معدل 14.35 نمایش داده می شود.
- اما اگر مانند شکل ذیل در ستون اول مقدار1005 را به 1010 تغییر دهیم تابع vlookup نمی تواند دقیقا مقدار 1005 را پیدا کند و خطای N/A# می دهد .
- مقدارTrue :یا مقدار یک که مقدار پیش فرض تابع VLOOKUP می باشد یعنی اگر هیچ مقداری را برای آرگومان آخر در نظر نگیریم خود تابع مقدار True یا یک را در نظر می گیرد .
- مقدار True یا یک به این معنی می باشد که اگر تابع VLOOKUP نتوانست دقیقا مقدار مورد جستجوی آرگومان اول را بیابد.
- نزدیکترین مقدار به این آرگومان (یعنی مقداری که در ستون قبل از 1005قرار دارد )یافت می شود(مقدار1004) و مقدار متناظرش 17.35 نمایش داده می شود .
در مثال فوق اگر در آرگومان آخر (Range_lookup) مقدار True را انتخاب کنیم اگر1005 را پیدا نکند مقدار قبل از آن یعنی 1004 را می یابد و بر اساس آن معدل17.35 را نمایش می دهد .
چند نکته مهم در مورد تابع VLOOKUP
- آرگومان اول (lookup_value) این تابع مقداری است که مورد جستجو قرار می گیرد و باید همیشه در ستون اول جدول جستجو قرار گیرد .
برای مثال اگر با ورود نام ، بخواهیم معدل دانش آموزان نمایش داده شود مانند شکل زیر اولین ستون جدول باید حتما نام باشد.
- برای اینکه در جستجو مقادیر درست نمایش داده شود مقادیر ستون اول باید بصورت صعودی یا نزولی مرتب شوند.
در مثال بالا از تب Data گزینه Sort را انتخاب می کنیم و داده های ستون اول را بصورت صعودی مرتب می کنیم و بصورت شکل زیر داده ها صعودی مرتب می شوند.
آموزش تابع Hlookup
این تابع امکان جستجوی افقی مقادیر در اکسل را فراهم می کند.
برای مثال جدولی از اطلاعات دانش آموزان داریم که با ورود کد دانش آموزی در سلول ،می خواهیم نام خانوادگی دانش آموزان در سلول مجاور نمایش داده شود .
تابع Hlookup دارای چهار آرگومان است .
Hlookup(lookup_value;table_array;row_index_num;[range_lookup])
lookup_value: اولین آرگومان این تابع است و با ورودی این آرگومان می توانیم مقادیری را در ردیف جستجو کنیم.
- در مثال فوق کد دانش آموزی 1003 مقدار این آرگومان است که می تواند این مقدار را بطور دستی وارد یا ادرس خانه شامل این عدد یعنی سلول C6 را به عنوان آرگومان اول انتخاب کنیم .
- مقادیر متنی ،عددی ، True و False می تواند به عنوان ورودی این آرگومان استفاده شود .
table_array: این آرگومان محدوده مورد جستجو را مشخص می کند که حتما باید شامل ردیف مقدار مورد جستجوی lookup_value و ردیف مقدار متناظر باشد.
- در این مثال محدوده مورد جستجو شامل A1 تا F4 است.
- باید حتما شامل مقدار lookup_value یعنی کد دانش آموزی و ردیف متناظر آن ، که بعد از جستجو باید نمایش داده شود یعنی نام خانوادگی می باشد.
row_index_num: شماره ردیف متناظر آرگومان اول است که می خواهیم بعد از جستجو نشان داده شود .
- در مثال فوق مقدار متناظری که بعد از جستجوی در آرگومان اول(کد دانش آموزی) باید نمایش داده شود ، نام خانوادگی دانش آموز می باشد .
- نام خانوادگی دانش آموز در ردیف سوم قرار گرفته پس مقدار ورودی این آرگومان 3 می باشد .
range_lookup: این آرگومان اختیاری است و دو مقدار را به عنوان ورودی می گیرد
- مقدار False یا صفر به این معنی است ، که این تابع دقیقا مقدار آرگومان اول یعنی lookup_value را جستجو می کند و در صورت یافتن مقدار متناظرش را نمایش می دهد.
- اگر این مقدار را نیافت خطای N/A# را نمایش می دهد.
در مثال بالا کد دانش آموزی 1003 را در سلول C6 وارد می کنیم و در سلول C7 تابع Hlookup را می نویسیم مقدار 1003 در ردیف اول جستجو می شود در صورت یافتن این مقدار ،تابع مقدار متناظر آن یعنی نام خانوادگی قاسمی را نمایش می دهد .
در صورتی که کد دانش آموزی 1003 در ردیف مورد جستجو یافت نشود خطای N/A# می دهد .
- مقدار True یا یک که مقدار پیش فرض این آرگومان می باشد یعنی درصورتی که برای این آرگومان مقداری را وارد نکنیم خود تابع مقدار یک یا True را در نظر می گیرد.
- مقدار True یا یک به این معنی می باشد که اگر تابع Hlookup نتوانست دقیقا مقدار مورد جستجو را در ردیف بیابد نزدیکترین مقدار به این آرگومان پیدا می شود و مقدار متناظرش نمایش داده می شود .(مقدار که در ردیف قبل از مقدار مورد جستجو می باشد )
در مثال فوق اگر مقدار 1003 را در ردیف اول حذف و مقدار 1010 را وارد کنیم تابع Hlookup نمی تواند مقدار 1003 را بیابد در نتیجه مقدار نزدیک به این عدد یعنی 1002 را می یابد که مقداریست که قبل از این عدد قرار گرفته و مقدار متناظر با آن یعنی نام خانوادگی منوچهری را نمایش می دهد .
چند نکته مهم در مورد تابع HLOOKUP
- آرگومان اول (lookup_value) این تابع مقداری است که مورد جستجو قرار می گیرد و باید همیشه در ردیف اول جدول جستجو قرار گیرد .
برای مثال اگر با ورود نام ، بخواهیم معدل دانش آموزان نمایش داده شود مانند شکل زیر اولین ردیف جدول باید حتما نام باشد.
- برای اینکه در جستجو مقادیر درست نمایش داده شود مقادیر ردیف اول باید بصورت صعودی یا نزولی مرتب شوند.
در مثال بالا از تب Home گزینه Sort & Filter را انتخاب می کنیم و داده های ردیف اول را بصورت صعودی مرتب می کنیم .