تابع VLOOKUP در اکسل
مثال ها، کاربردها و فیلم آموزشی
تابع vlookup از کاربردی ترین توابع اکسل است. تابع vlookup یکی از توابع اکسل جهت جستجو و بازیابی اطلاعات از یک ستون مشخص از یک جدول میباشد. تابع vlookup دارای حالت تقریبی و حالت دقیق و wild cards برای مقادیر مشخص میباشد. V که اولین حرف vlookup است، مخفف کلمه vertial به معنی«عمودی» میباشد یعنی این تابع به صورت عمودی در ستون (یا آرایه) کار جستجو را انجام می دهد.
قبل از شروع آموزش تابع vlookup، بحث را با طرح یک مثال ساده شروع می کنیم. در عکس بالا فرض کنید هر فرد یک سری اطلاعات مانند شماره شناسایی، نام، نام خانوادگی، ایمیل و موقعیت شغلی دارد. شما میخواهید بر اساس شماره شناسه، ایمیل یا سایر اطلاعات فرد را فراخوانی کنید. ساده ترین راه استفاده از تابع vlookup است. در ادامه با هدف تابع vlookup، نحوه استفاده و نکات و مثال های ریز و درشت این تابع آشنا خواهید شد.
هدف تابع vlookup
جستجو یک مقدار مشخص در جدول بر اساس تطابق آن با ستون اول جدول مورد نظر
نتیجه دریافتی از vlookup اکسل
یافتن مقدار مورد نظر از جدول
ترکیب بندی یا اجزای تابع vlookup
=VLOOKUP (lookup_value , table_array , col_index , [range-lookup])
lookup_value : مقداری که از اولین ستون جدول جستجو میشود. این پارامتر میتواند یک آدرس سلول، یک مقدار( متنی، عددی، تاریخ) یا مقدار برگردانده شده توسط یک تابع دیگر باشد. برای مثال:
- فرمول (2,vlookup(40,A2:B15 مقدار 40 را جستجو میکند.
- به همین صورت فرمول (2,vlookup(“Apple”,A2:B15 رشته متنی apple را در محدوده تعریف شده جستجو میکند و مقدار متناظر را برمیگرداند. حتما توجه داشته باشید که برای تعریف رشته های متنی می بایست این مقادیر را در درون “” قرار دهید.
- اما همانطور که گفتیم شما میتوانید از مقادیر سلول های دیگر نیز بعنوان lookup value در فرمول های vlookup خود استفاده کنید. برای مثال فرمول (2,vlookup(c2,A2:B15 مقدار موجود در سلول c2 را در بازه مشخص شده جستجو میکند و مقدار متناظر را برمیگرداند.
table Array: جدولی که مقادیر مورد نظر در آن جستجو میشود و متشکل از دو یا چند ستون است.
تابع vlookup همیشه در اولین ستون از table Array مقدار خواسته شده را جستجو میکند. جدول شما میتواند انواع مختلفی از مقادیر از جمله متن، عدد، تاریخ، مقادیر منطقی را شامل شود. مقادیر موجود در جدول نسیت به بزرگی و کوچکی حروف حساس نیستند به این معنی که حروف کوچک و بزرگ هیچ تفاوتی با هم ندارند و به صورت مشابه در نظر گرفته میشوند.
col-index-num : شماره ستونی که قرار است مقادیر مورد نظر از آن بازیابی شود. همانطور که گفته شد تابع vlookup همیشه مقدار مورد نظر را در اولین ستون جستجو میکند و مقدار متناظر با آن را در ستون دلخواه شما که توسط این پارامتر مشخص میکنید برمیگرداند. همیشه سمت چپ ترین ستون در جدول مشخص شده شماره 1، ستون بعد شماره 2 و … می باشد.
rang-lookup : به صورت پیش فرض TRUE میباشد و نشان دهندة مقدار تقریبی است و کلمه FALSE که نشان دهندة مقدار دقیق است.این پارامتر اختیاری است ولی از اهمیت بسیار فوق العاده ای برخوردار است و در بسیاری از موارد مشکلاتی که برای کاربران در رابطه با تابع vlookup پیش می آید ناشی از بی دقتی در استفاده از این پارامتر است. در ادامه این مطلب توضیحات بیشتری در این زمینه ارائه خواهیم کرد.
نکات کاربردی تابع vlookup
تابع vlookup برای بازیابی داده ها در جدولی است که با اطلاعات آن در ردیف های عمودی قرار گرفته اند که هر ردیف نشان دهنده یک ثبت جدید میباشد. کلمه V در vlookup مخفف vertial به معنی «عمودی» میباشد.
اگر اطلاعات شما به صورت افقی طبقه بندی شده اند باید از تابع HLOOKUP استفاده کنید.
تابع vlookup فقط مقدارهای سمت چپ را میآورد
تابع vlookup جستجوی خود را از داده های ستون
سمت راست شروع کرده و مقادیر موردنظر و جوابهای درخواستی را در ستون سمت چپ (در صورتی که صفحه کاری راست چین باشد) متناظر به نمایش میگذارد.
تابع vlookup داده ها را بر اساس شماره ستون بازیابی مینماید
وقتی که شما از تابع vlookup اکسل استفاده میکنید تصور نمایید که هر ستون جدول شمارهگذاری شده است و از سمت راست هر جدول کار شروع میشود. برای دریافت یک مقدار از ستون خاص، شماره مورد نظر را به عنوان ستون شاخص وارد نمایید.
=VLOOKUP(H3,B4:E13,2,FALSE) // نام
=VLOOKUP(H3,B4:E13,3,FALSE) // نام خانوادگی
=VLOOKUP(H3,B4:E13,4,FALSE) // ایمیل
چند مثال از تابع vlookup در اکسل
نحوه جستجو در یک ورک شیت دیگر با استفاده از vlookup
در عمل به ندرت اتفاق می افتد که شما با استفاده از vlookup یک مقدار را در همان ورک شیت جستجو کنید و اغلب مقادیر خواسته شده در ورک شیت های دیگر قرار دارند. برای جستجو در یک ورک شیت دیگر می بایست نام ورک شیت و یک علامت تعجب(!) را قبل از محدوده مورد نظر وارد کنید. برای مثال (vlookup(40,sheet2!A2:B6,2 . با استفاده از این فرمول table array شما محدوده A2:B6 از شیت 2 قرار می گیرد.
اما شما مجبور نیستید نام شیت را به صورت دستی وارد کنید. کافیست شروع به نوشتن فرمول کنید و به جای نوشتن table array به شیت مورد نظر منتقل شده و محدوده دلخواه را انتخاب کنید. برای مثال در تصویر زیر فرمول نوشته شده مقدار “Product 1” را در ستون A ورک شیت prices جستجو میکند.
نحوه انجام جستجو در یک ورک بوک دیگر با استفاده از تابع Vlookup اکسل
برای انجام جستجو در یک ورک بوک دیگر نیز شبیه به مثال قبل عمل میکنیم با این تفاوت که نام ورک بوک دلخواه را در درون براکت قرار داده و سپس نام ورک شیت( به اضافه علامت تعجب) و آدرس محدوده دلخواه را وارد میکنیم. برای مثال به منظور جستجوی مقدار 40 در شیت 2 ورک بوک numbers از فرمول زیر استفاده میکنیم.
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
در صورتی که نام ورک بوک یا ورک شیت شامل فاصله یا کاراکترهای غیر الفبایی باشد می بایست حتما نام آنها را در درون علامت ‘ ‘ قرار دهید . برای مثال:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2′!A2:B15,2)
اما در اینجا نیز راهی ساده تر از وارد کردن نام های معمولا پیچیده ورک بوک در درون فرمول های vlookup وجود دارد. شبیه به مثال قبل شما میتوانید:
- هر دو ورک بوک را باز کنید. البته این مرحله اجباری نیست ولی پیشنهاد میشود برای ساده تر شدن کار قبل از شروع به نوشتن فرمول vlookup هر دو ورک بوک را باز کنید.
- شروع به نوشتن فرمول خود کنید و به جای نوشتن دستی پارامتر table-array، به ورک بوک مور نظر منتقل شده و محدوده دلخواه را انتخاب کنید.
تابع vlookup شامل حالت تقریبی و حالت دقیق میباشد
این وضعیت را میتوان توسط بخش چهارم فورمول که «range-VLOOKUP» نامیده میشود تنظیم نمود. اگر عبارت «FALS» در این قسمت نوشته شود تابع vlookup دقیقاً کلمه مورد نظر را جستجو خواهد کرد و اگر عبارت «TURE» در این قسمت نوشته شود نتیجه با یک عبارت تقریبی منطبق خواهد شد.
=VLOOKUP(value, table, column) // حالت پیش فرض: تقریبی
=VLOOKUP(value, table, column, TRUE) // تطابق تقریبی
=VLOOKUP(value, table, column, FALSE) // تطابق دقیق
توجه : قسمت «range-lookup» به صورت پیش فرض روی کلمه «TURE» میباشد بنابراین به صورت خودکار در جستجوی خود بر یک مقدار تقریبی منطبق میشود.
مثال 1 : تطبیق دقیق یا مطابقت کامل در تابع vlookup
اگر شما به دنبال یافتن یک مطابقت دقیق هستید تابع vlookup اکسل این قابلیت منحصر به فرد را برای شما ایجاد میکند. به عنوان مثال اسامی فیلمها را در دادههای جدول زیر مشاهده کنید.
فرمول خانه H6 برای جستجوی نام فیلم بر اساس سال به صورت زیر خواهد بود.
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = تطابق دقیق
مثال 2 : مطابقت تقریبی در تابع vlookup اکسل
در این حالت شما در جستجوی بهترین و نزدیکترین مقدار با هدف مورد نظر خود هستید و مقدار دقیق مد نظر شما نیست. به عنوان مثال یافتن نرخ حق کمیسیون بر اساس مقدار فروش ماهانه است. در این حالت شما میخواهید تابع vlookup بهترین نتیجه منطبق با دادههای ورودی را برای شما پیدا نماید. در جدول زیر فرمول خانه D5 نشان دهندة مقدار تقریبی به سمت کمترین مقدار نرخ حق کمیسیون در جدول مورد نظر میباشد.
=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = تطابق تقریبی
نکته : زمانی که از حالت تقریبی vlookup استفاده میکنید دادههای شما باید به صورت صعودی در lookup value ثبت شود.
نکات کاربردی در تابع vlookup اکسل
Range-lookup : بررسی میکند که آیا value ها دقیقاً مطابقت دارند یا خیر. به صورت پیش فرض TURE = با یک مقدار تقریبی
برای دریافت یک مقدار دقیق «rang-vlookup» را روی «FALSE» تنظیم کنید.
وقتی range-lookup که به صورت پیش فرض روی TRUE قرار دارد VLOOKUP نزدیکترین مقدار با ارزش خواسته شده به سمت کمترین مقدار را پیدا میکند و به نمایش میگذارد.
وقتی range-vlookup حذف میشود VLOOKUP یک مقدار نه چندان دقیق را جستجو میکند ولی در صورت وجود مقدار دقیق همان را به نمایش میگذارد.
وقتی range-lookup در حالت پیش فرض TRUE قرار دارد اطمینان حاصل کنید که مقادیر موجود در ردیف اول جدول به صورت صعودی مرتب شدهاند. در غیر اینصورت ممکن است تابع vlookup یک مقدار نادرست یا غیرمنتظره را نمایش دهد. وقتی rang-lookup روی FALSE قرار میگیرد (نیازمند یک تطابق دقیق)، مقادیر در ستون اول جدول نیازی به مرتب بودن ندارند.
استفاده از کاراکترهای wildcard در تابع vlookup
در فرمول های vlookup نیز میتوانید شبیه به هر تابع دیگری در اکسل از کاراکترهای wildcard استفاده کنید. قبلا در مطالب متعددی در مورد کاراکترهای Wildcard اکسل صحبت کرده ایم. در صورتی که مطالب قبل در این زمینه را مطالعه نکرده اید یا با کاراکترهای وایلدکارد در اکسل آشنایی ندارید پیشنهاد میکنیم مطلب زیر را در این باره مطالعه کنید:
معرفی کاراکترهای وایلدکارت در اکسل
شما در موارد متعددی میتوانید از این کاراکترها در فرمول های خود بهره مند شوید. از جمله:
- زمانی که متن دقیق مورد نظر را که میخواهید جستجو کنید نمیدانید.
- زمانی که میخواهیم عبارتی را جستجو کنیم که قسمتی از محتوای یک سلول است. عموما فرمول های Vlookup کل محتوای یک سلول را جستجو میکنند. در این شرایط یکی از بهترین گزینه ها استفاده از کاراکترهای wildcard در ترکیب با تابع vlookup است.
- زمانی که مقدار مورد جستجو دارای فاصله های اضافی در ابتدا یا انتها می باشد .
- …
مثال 1: جستجوی عبارت متنی که با مقدار خاصی شروع میشود یا به پایان میرسد.
فرض کنید میخواهید نام یکی از مشتریان را از لیست زیر پیدا کنید ولی شما نام او را به یاد نمی آورید و صرفا میدانید که نام او به مقدار “ack” شروع میشود. برای انجام این کار میتوانید از کاراکترهای وایلدکارت در ترکیب با تابع vlookup به شکل زیر استفاده کنید:
=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)
زمانی که اطمینان حاصل کردید که نام صحیح را پیدا کرده اید میتوانید از یک فرمول مشابه برای یافتن مقدار پرداخت شده توسط این مشتری استفاده کنید. برای انجا این کار فقط کافیست پارامتر سوم فرمول را به 3 تغییر دهید تا مقدار متناشر با آن مشتری در ستون Sum برگردانده شود.
=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)
در زیر چند نمونه دیگر در این زمینه ارائه شده است:
=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE)
نامی که با مقدار man به پایان میرسند را پیدا میکند.
=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE)
نامی که با ad شروع میشود . با son به پایان میرسد را پیدا میکند.
=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE)
یک نام پنج کاراکتری را پیدا میکند.
تذکر: زمانی که از کاراکترهای وایلدکارد در ترکیب با فرمول vlookup استفاده میکنید بهتر است همیشه پارامتر آخر تابع را false قرار دهید. در صورتی که بیش از یک مقدار با شرط تعیین شده مطابقت داشته باشد، همیشه اولین مقدار مطابق برگردانده میشود.
مثال 2: نوشتن فرمول vlookup بر اساس مقدار یک سلول
در این میخواهیم یک مقدار را که فقز قسمتی از آن را میدانیم، در یک سلول جستجو کنیم . فرض کنید یک کد در ستون A و یک نام مربوط به آن کد در ستون B در اختیار دارید. حال فرض کنید میخواهیم نام مربوط به یک کد را که فقز قسمتی از آن را در اختیار داریم را پیدا کنیم. برای انجام این کار کاراکترهایی را که در اختیار داریم را در سلول C1 (مقدار مورد جستجو) وارد میکنیم و از فرمول زیر برای استخراج نام مربوط به آن کد استفاده میکنیم:
=VLOOKUP(“*”&C1&”*”,$A$2:$B$12,2,FALSE)
توجه داشته باشید که در پارامتر اول، قبل و بعد از آدرس سلول از علامت (&) استفاده کردیم تا رشته های متنی را به هم متصل کند. همانطور که در تصویر زیر مشاهده میکنید، مقدار jeremy hill برگردانده شد. زیرا تنها کد مربوط به این نام است که شامل محتوای سلول C1 می باشد.