فرمول vlookup در اکسل
زمان تقریبی مطالعه این مطلب و فیلم مربوطه: 20 دقیقه
سطح: پیشرفته
تابع vlookup برای جستجوی عمودی یک مقدار (Lookup_Value) در اولین ستون یک ناحیه داده ای (Table_Array) و بازگرداندن مقداری در ستون دیگر آن (col_index_num) بکار می رود. مثلا وقتی می خواهیم کد یک کالا را وارد کنیم و اکسل نام این کالا را از طریق کد وارده شده، در یک جدول دیگر یافته و نمایش دهد از فرمول vlookup استفاده می کنیم.
فرمول VLookup معروف ترین و مورد توجه ترین فرمول اکسل در ایران (و شاید جهان!)
چه زمانی لازم است تا از فرمول VLookup استفاده کنیم؟
هرگاه یک مقدار معلوم و یک یا چند مقدار مجهول دارید اما کلیه مقادیر معلوم و مجهول شما در یک جدول یا شیت یا فایل دیگری بصورت کامل قرار دارند (مثلا شماره پرسنلی چند نفر معلوم است و نام و نام خانوادگی آن ها مجهول است ولی یک جدول کامل اطلاعات پرسنل را در اختیار دارید) در این حالت شما یک جستجوی عمودی دارید که می شود همان Vertical Lookup.
چون ما معمولا جدول ها را بصورت سطری پُر می کنیم این فرمول کاربرد بیشتری دارد درحالیکه فرمول HLookup نیز در اکسل برای جستجو های افقی وجود دارد.
در اکثر مصاحبه های شغلی از این فرمول سوال شده حتی در برخی از موارد از مصاحبه شونده خواسته شده تا این فرمول را روی کاغذ بنویسد و توضیح دهد.
ایرادات و نواقص این فرمول که البته همگی قابل رفع هستند به شرح ذیل می باشند:
1- اگر چند مقدار معلوم داشتیم چه کنیم؟ (وی لوکاپ چند شرطی) مثلا نام و نام خانوادگی را داریم و به دنبال تاریخ تولد پرسنل می گردیم؟ اینجا نمیتوان از نام خانوادگی به تنهایی استفاده کرد چون ممکن است اطلاعات فردی دیگری با همین نام خانوادگی را پیدا کند.
2- چه کنیم تا وی لوکاپ بعد از یافتن اولین مقدار جستجو را متوقف نکند؟ مثلا یک شماره پرسنلی را بدهیم و تمام تاریخ های دریافت وجه یا دریافت وام این فرد را پیدا کند.
3- وردی آخر وی لوکاپ چه فایده ای دارد؟
4- چگونه به جای IF های تو در تو و پیچیده از یک وی لوکاپ ساده استفاده کنیم؟
در حقیقت از تابع Vlookup هنگامی استفاده می شود که بخواهیم یک جستجوی عمودی بر روی اولین ستون یک ناحیه از جدول پایه انجام دهیم و در ستونی دیگر از سطری که نتیجه پیدا شد مقداری را استخراج کنیم:
برای یادگیری دقیق و کامل فرمول vlookup ویدئوی زیر را تماشا کنید. هرگونه سوالی داشتید در پایین همین مطلب مطرح کنید.
توجه داشته باشید که ورودی آخر این فرمول Range_Lookup در دو مقدار 0 , 1 یا همان True , False فرمول را در دو حالت دقیق یا تقریبی قرار می دهد. یعنی اگر برابر مقدار True یا عدد 1 باشد (یا خالی گذاشت شود) مقادیر عددی تقریبی را پیدا می کند. برای اطلاعات بیشتر فایل handbook را دانلود کنید.
فرمول دیگری هم برای جستجوی های افقی وجود دارد که نام آن HLookup است اما چون اکثرا جداول ما در حالت سطری پر می شوند و جداول عمودی داریم فرمول VLookup معروف و پرکاربرد شده است.
کاربردهای فرمول VLookup
یکی از مهمترین کاربردهای فرمول vlookup در اکسل استفاده از این فرمول در مغایرت گیری می باشد.
دانلود فایل اسلاید ارائه این ویدئو دانلود فایل اکسل این فرمول دانلود فایل PDF English این فرمول
سلام استاد
بینظیر بود خسته نباشید
سلام.
واقعا سایت قشنگیه
دستتون درد نکنه
عالی بود , و خیلی شیوا ممنون
سلام
ممنون عالی بود.
ولی چطور میشه از این فرمول استفاده کرد ولی داده تکراری برام نیاره؟ یعنی اگر چند تا از کلمه مورد نظر که دارم سرچ میکنم در ستون اول باشه و اون در هر بار جستجو داده ای که قبلا پیدا کرده رو نیاره؟
اگر سوال شما به شکل زیر مطرح شود که:
آیا vlookup می تواند سایر اقلام داده ای که در ستون اول سرچ می کند را نیز پیدا کند یعنی با پیدا کردن اولین مقدار تطابق یافته کار را تمام نکند؟
پاسخ مثبت است. اینکار کاملا پیشرفته بوده و با کمک روش آرایه ای و فرمول های match و index و small و row انجام می شود.
این فیلم برای من من خیلی خیلی با ارزش بود. خیلی کارم رو ساده کرد. واقعا از شما ممنونم.
یا سوال هم دارم. فیلمهای آموزشی مربوط به سایر توابع یا سایر مباحث مربوط به تابع vlookup رو چطور میشه تهیه کرد؟
خواهش می کنم. در بخش تماس درخواست خود را ارسال کنید.
سلام و خسته نباشید .ممنون بابت این کلیپ آموزشی پرکاربرد
ادامه این آموزش هارو برامون میزارید ؟
سلام. به امید خدا. مورد خاصی مد نظر شماست؟
سلام و خسته نباشید خیلی عالی بود ممنون واقعا
واقعا قشنگ و بی نقص توضیح دادین مشکل بزرگی از من حل شد واقعا سپاس گذارم
سلام خسته نباشید… در مورد اینکه چه کنیم تا وی لوکاپ بعد از یافتن اولین مقدار جستجو را متوقف نکند فیلم آموزشی دارید؟
سلام. چنین مواردی در کلاس اکسل پیشرفته آموزش داده می شود. البته می توانید در سایت های خارجی آموزش آن را پیدا کنید.
سلام.من یه راهنمایی می خواستم.من تو اکسل با استفاده از ,Vlook و یا Match ,Index یه داده را فراخوانی می کنم.چطوری میتونم چند تا جواب داشته باشم؟ خیلی جستجو کردم.همیشه اولین جوابو بهم میده این فرمول ها.من شاید چند تا جواب بخوام.
برای انجام این کار که تحت عنوان VLookup Next Result شناخته می شود از ترکیب فرمول های IF, Row, Isnumber, Search, Index, IFError, Small استفاده می کنند تا چنین چیزی پیاده سازی شود. با احترام ما چنین مواردی را در کلاس آموزش می دهیم چون توضیحات خیلی طولانی دارد اما امکان اینکه در اینترنت آن را بپدا کنید کاملا وجود دارد.
سلام
شما كجا تدريس مي كنيد
چطوري ميشه شمارو پيدا كردممنون ميشم جواب بديد
اطلاعات تماس در بخش تماس سایت هست.
اطلاعات کلاس ها هم در منوی دوره های حضوری بطور کامل هست.
سلام استاد گرامی
سوالی که برام در دستور vlookup پیش اومده اینه که ما تو سلول اول وقتی اسم رو مختصر بنویسیم یا نامش رو کامل ندونیم که چیه، در نهایت اون طرف نتیجهای بهمون نامیده.
راهحلی وجود داره براش؟؟؟؟
میشه راهنمایی بفرمایید؟؟؟؟
بله در حالت عادی فرمول vlookup به دنبال مقدار مورد نظر شما هست و اگر پیدا نشود نتیجه ای نخواهد داد اما می توانید اسم مورد نظرتان را بین دو * قرار دهید تا اگر یافت شد (حتی اگر بخشی از اسم در مقصد حاوی اسم مورد نظر شما بود) نتیجه دهد.