تابع VLOOKUP
تابع VLOOKUP در سری توابع LOOKUP & REFERENCE اکسل قرار دارد.
شکل کلی این تابع به صورت:
=VLOOKUP(LOOKUP_Value,table_array,col_index_mum, [range_Lookup]))
می باشد.
به زبان ساده معنی عبارت بالا این است: Lookup_Valueبگرد به دنبال مقداری که من در سلول … وارد کرده ام. (آدرس سلول به جای Lookup نوشته شود)
table_array جدولی که مقدار وارد شده در بالا در آنجا قرار دارد.
col_index_num شماره ستونی که مقدار آن باید مقابل Lookup_Value نوشته شود.
Range_Lookup (مقدار 0 و 1) دقت جستجو را مشخص می کند. مقدار صفر جستجوی دقیق و مقدار یک جستجوی تقریبی را فراهم می آورد.
در این پست کاربرد این تابع را با ذکر یک مثال توضیح میدهم.
دوستان عزیزی که مسئولیت تهیه صورت وضعیت یک شرکت را به عهده دارند به خوبی می دانند که بدون استفاده از تابع VLOOKUP، وارد کردن شرح، واحد و قیمت آیتمهای فهرست بهاء کاری طاقت فرسا و همراه با اشتباهات زیاد خواهد بود.
اما با استفاده از این تابع و فقط با وارد کردن ردیف آیتم مورد نظر، سایر پارامترهای دیگر از قبیل شرح آیتم، واحد، قیمت واحد به صورت اتوماتیک درج خواهند شد.
در این مثال فرض ما بر این است که کاربرگهایی که ما روی آنها کار می کنیم (مانند برگ ریز متره، خلاصه متره و …) دارای جهت راست به چپ هستند (از سربرگ PAGE LAYOUT) گزینه Sheet Right – to- Left را فعال می کنیم.)
در این مثال من یک کاربرگ را به عنوان DATA BASE که نمونه ای از فهرست بهای رشته برق می باشد در قالب اکسل تهیه کرده ام.
همانطور که در شکل می بینید این نمونه در محدوده A2:D10 قرار گرفته است. ابتدا بهتر است که این محدوده را نامگذاری کنیم. برای این کار از سربرگ FORMULAS آیتم Name Manager را انتخاب کرده و از پنجره باز شده New را کلیک می کنیم. پنجره New Name نمایان می شود. در قسمت Name نام دلخواه (فارسی یا انگلیسی) را بدون فاصله تایپ می کنیم. (برای نامهای طولانی می توان از _ بین کلمات استفاده نمود) و سپس در قسمت Refers to: با کلیک بر روی فلش کوچک قرمز رنگ کناری محدوده ای را که قصد نامگذاری آن را داریم انتخاب کرده و سپس بر روی OK کلیک می کنیم.
شکلهای زیر نحوه کار را نماش می دهند.
تابع VLOOKUP
تابع VLOOKUP
در این مثال من دو کاربرگ را تهیه کرده ام. همانطور که قبلاً اشاره شده کاربرگ DATA BASE را به اسم “برق” و کاربرگ دیگر را به نام “صورت وضعیت” نامگذاری کرده ام.
حال میخواهم در کاربرگ صورت وضعیت از تابع VLOOKUP استفاده کرده و با درج شماره ردیف فهرست از کاربرگ برق، شرح، واحد و قیمت آیتمها به صورت خودکار در کاربرگ “صورت وضعیت قرار بگیرند.
در قدم اول قصد دارم شرح آیتم در برگ صورت وضعیت در زیر عنوان “شرح” قرار بگیرد. برای این منظور در سلول B8 کاربرگ “صورت وضعیت” فرمول را به شرح زیر وارد می کنم.
=VLOOKUP(A8,فهرست,2,0)
A8: محلی که باید جستجو شود.
فهرست: جدولی که مقدار مورد جستجو در آنجا قرار دارد
2: ستون دوم از جدول فهرست
0: مقدار جستجو دقیق باشد. شرح نزدیک به آیتم قرار داده نشود.
برای آنکه مقدار واحد نیز درج شود عیناً فرمول فوق را تکرار کرده و فقط به جای عدد “2” باید عدد “3” را وارد کنیم. چونکه شرح واحد در ستون سوم (از سمت راست) جدول “فهرست (کاربرگ برق) قرار دارد.
شکل زیر فرمول و نتیجه کار را نشان می دهد.
تابع VLOOKUP
تذکر: اگر مقدار LOOKUP (در این مثال یعنی ستون A) یافت نشود آنوقت خطای
#NA (Not Available)
نمایش داده می شود.
تذکر: در ردیفهای فهرست قبل از بعضی از ردیفها مقدار “0” درج شده است. اما اکسل آن را نادیده میگیرد. اگر شماره آیتم درست وارد شود و دوباره خطای #NA نمایش داده شود بهتر است که از Format Cell نوع داده سلول را Text در نظر بیگیریم.
تذکر: تابع HLOOKUP نیز مانند VLOOKUP عمل می کند . اما به جای جستجوی عمودی، جستجوی افقی را انجام میدهد.
آموزش تصویری تابع VLOOKUP را در اینجا ببینید
چطور میشه یک استرینگ را وارد کنیم و ردیف یا ایندکس رو دریافت کنیم؟
با سپاس