اساطیر زبان

تابع VLOOKUP در اکسل یک تابع قدرتمند است که برای جستجوی یک مقدار در محدوده داده ها مورد استفاده قرار می گیرد و مقدار متناظر را از همان سطری که ستون آن در تابع مشخص شده برمی گرداند. در این آموزش شیوه استفاده از تابع VLOOKUP را همراه با مثال های مختلف توضیح خواهیم داد.

ویدیوی آموزشی (آموزش کامل تابع در ۳ دقیقه!)

 

دانلود ویدیو 

اگر ویدیوی بالا کافی نبود یا به هر دلیل دیگری، به خواندن ادامه دهید.

لیست همه دروس کاربردی اکسل ما

ساختار تابع به صورت زیر است:

word image 24

تابع VLOOKUP

ساختار:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

آرگومان های تابع:

lookup_value: مقدار مورد نظر برای جستجو می باشد. این مقدار باید در ستون اول محدوده table_array باشد.

table_array: محدوده یا جدول داده برای جستجو است. ستون مقدار مورد جستجو و ستون مقدار نتیجه در آن قرار دارند.

col_index_num: شماره ستونی است که مقدار متناظر (تطبیقی) از آن برگردانده می شود. شماره ستون ها از ستون سمت چپ محدوده با شماره ۱ شروع می شود. (البته وقتی که تنظیمات صفحه از چپ به راست باشد.)

[range_lookup]: آرگومان اختیاری است. یک مقدار منطقی است که تعیین می کند تابع VLOOKUP مقدار متناظر دقیق را برگرداند یا یک مقدار تقریبی.

  • مقدار تقریبی (۱ / TRUE): اگر مقدار دقیق پیدا نشود، فرمول نزدیکترین مقدار متناظر را جستجو می کند و بزرگترین مقدار کوچکتر از مقدار جستجو را برمی گرداند. در این حالت باید ستون مورد جستجو را به ترتیب صعودی مرتب کنید.

=VLOOKUP(lookup_value, table_array, col_index, TRUE)

=VLOOKUP(lookup_value, table_array, col_index, 1)

  • مقدار دقیق (۰ / FALSE): این مورد برای جستجوی مقدار دقیق برابر با مقدار جستجو استفاده می شود. اگر تابع مقدار دقیق را پیدا نکند، خطای #N/A را برمی گرداند.

=VLOOKUP(lookup_value, table_array, col_index, FALSE)

=VLOOKUP(lookup_value, table_array, col_index, 0)

سایر نکات:

۱٫ تابع Vlookup مقدار را از چپ به راست (در صفحات با جهت چپ به راست) جستجو می کند.

۲٫ اگر چندین مقدار بر اساس مقدار جستجو وجود داشته باشد، فقط اولین مقدار تطبیق یافته را برمی گرداند.

۳- اگر مقدار مورد جستجو در ستون سمت چپ پیدا نشود، خطای #N/A را برمی گرداند.

نکته: در این آموزش فرض بر این است که جهت محتوای صفحه از چپ به راست است و شروع محتوا از چپ می باشد. برای محتوای راست به چپ مانند زبان فارسی سمت راست شروع می باشد.

مثال های تابع VLOOKUP

تطبیق دقیق با Vlookup

اگر می خواهید تابع Vlookup، تطبیق یا جستجوی دقیق را انجام دهد، کافیست آخرین آرگومان آن را FALSE قرار دهید.

در مثال زیر، داده های مرتبط با نمرات داش آموزان آورده شده است. مقادیر مرتبط با شناسه دانش آموز، نام، نمره ریاضی و نمره شیمی به ترتیب در ستون های ID، Name، Math و Chemistry قرار دارند.

word image 25

برای به دست آوردن نمرات ریاضی متناظر بر اساس شماره شناسه خاص، مراحل زیر را دنبال کنید:

فرمول زیر را در یک سلول خالی برای محاسبه وارد کنید.

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

برای اعمال این فرمول روی بقیه سلول های ستون، روی گوشه سمت راست- پایین سلول فعلی حرکت کنید تا شکل آن به (+) تغییر پیدا کند سپس آن را به سمت پایین بکشید.

word image 26

در این فرمول،

  • مقدار سلول F2 مقدار مورد جستجو است که تابع VLOOKUP مقدار متناظر با آن را برمی گرداند.
  • A2:D7 محدوده مورد جستجو است.
  • عدد ۳ شماره ستونی است که مقدار متناظر از آن برگردانده می شود.
  • FALSE نیز تعیین می کند که تابع مقدار دقیق را جستجو کند.
  • اگر مقدار تعیین شده در محدوده داده ها پیدا نشود، خطای #N/A را نمایش می دهد.

تطبیق تقریبی با Vlookup

تطبیق تقریبی برای جستجوی مقادیر بین یک محدوده مفید است. اگر مقدار تطبیق دقیقی پیدا نشود، تابع Vlookup یک مقدار تقریبی برمی گرداند که بزرگترین مقداری است که از مقدار جستجو کوچکتر می باشد.

در مثال زیر، داده های مرتبط با تعداد سفارشات در ستون Orders و تخفیف مطابق با آن در ستون Discount قرار دارند.

فرض کنید سفارش داده شده در ستون Orders موجود نیست، چگونه می توان نزدیکترین تخفیف را در ستون B به دست آورد؟

word image 27

فرمول زیر را در سلول مورد نظر وارد کنید:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

سپس برای اعمال فرمول روی بقیه سلول ها، دستگیره گوشه سمت راست پایین سلول را روی بقیه سلول ها بکشید. مقدار تطبیق تقریبی با توجه به مقادیر داده شده محاسبه می شود:

word image 28

در این فرمول،

  • D2 مقداری است که داده تقریبی نسبت به آن محاسبه می شود.
  • A2:B9 محدوده داده مورد جستجو می باشد.
  • عدد ۲ شماره ستونی است که مقدار تقریبی از آن بازگشت داده می شود.
  • مقدار TRUE نیز به تطبیق تقریبی اشاره می کند.

تطبیق تقریبی بزرگترین مقدار کوچکتر از مقدار جستجوی خاص را برمی گرداند و برای استفاده از تابع Vlookup برای مقدار تقریبی باید محدوده داده را به ترتیب صعودی مرتب کنید در غیر این صورت نتیجه اشتباهی را برمی گرداند.

استفاده از کاراکترهای جایگزین برای تطبیق های جزئی در تابع Vlookup

می توانید از کاراکترهای جایگزین (wildcards) در تابع Vlookup استفاده کنید. این باعث می شود تا تطبیق جزئی روی مقدار جستجو انجام شود. به عنوان مثال می توانید از Vlookup برای به دست آوردن مقدار متناظر بر اساس بخشی از مقدار جستجو استفاده کنید.

فرض کنید می خواهیم مقدار score (امتیاز) را بر اساس نام کوچک در Name (نه نام کامل) به دست آوریم. چگونه می توان این کار را در اکسل انجام داد ؟

word image 29

تاع Vlookup عادی به درستی کار نمی کند، باید متن یا آدرس سلول حاوی کاراکتر جایگزین را اضافه کنید. فرمول زیر را در یک سلول خالی وارد کنید:

=VLOOKUP(E2&”*”, $A$2:$C$11, 3, FALSE)

این فرمول را روی بقیه سلول های بکشید تا مقدار score برای آنها نیز محاسبه شود. مانند آنچه در تصویر زیر نشان داده شده است:

word image 30

در این فرمول،

  • “*”&E2 مقدار جستجو است؛ مقدار سلول E2 و کاراکتر جایگزین *. (رشته “*” بیانگر یک کاراکتر یا هر کاراکتری است)
  • A2:C11 محدوده جستجو است.
  • عدد ۳ نشان دهنده ستون حاوی مقدار بازگشتی است.

هنگام استفاده از تابع Vlookup همراه با کاراکترهای جایگزین باید حالت تطبیق (یعنی آخرین آرگومان تابع) را FALSE یا ۰ تنظیم کنید.

نکات:

۱- برای جستجو و پیدا کردن مقادیری ​​که با یک مقدار مشخص خاتمه می یابند، فرمول زیر را استفاده کنید:

=VLOOKUP(“*”&E2, $A$2:$C$11, 3, FALSE)

word image 31

۲- برای جستجو و پیدا کردن مقدار مطابق بر اساس بخشی از رشته متن، بدون توجه به اینکه متن مشخص شده در کجای رشته متنی باشد، کافیست دو کاراکتر * را در ابتدا و انتهای متن یا آدرس سلول قرار دهید:

word image 32

جستجوی مقادیر از کاربرگ دیگر

گاهی اوقات مجبور می شوید با بیش از یک کاربرگ (worksheet) کار کنید. می توانید از تابع Vlookup برای جستجوی داده ها از یک صفحه دیگر استفاده کرد.

فرض کنید دو کاربرگ دارید، مانند تصویر زیر. برای جستجوی و بازگشت داده های مربوطه از یک کاربرگ دیگر، مراحل زیر را دنبال کنید:

word image 33

۱- فرمول زیر را در سلول خالی مورد نظر وارد کنید:

=VLOOKUP(A2,’Data sheet’!$A$2:$C$15,3,0)

۲- با استفاده از دستگیره گوشه راست- پایین سلول، فرمول را روی بقیه سلول ها بکشید تا نتایج متناظر را مشاهده کنید:

word image 34

در این فرمول،

  • A2 مقدار جستجو را نشان می دهد.
  • Data sheet نام صفحه یا کاربرگی است که اطلاعات از آن جستجو می شود، (اگر نام برگه حاوی کاراکترهایی مانند کاما، نقطه یا فاصله باشد باید آن را در کوتیشن قرار دهید در غیر این صورت می توانید نام را به طور مستقیم استفاده کنید، مانند:

=VLOOKUP(A2,Datasheet!$A$2:$C$15,3,0) );

  • A2:C15 محدوده ای است که داده ها در آن جستجو می شود.
  • عدد ۳ شماره ستون حاوی داده های برگشتی متناظر است.

جستجوی مقادیر از یک کتاب کار دیگر

در این قسمت، شیوه جستجو و بازگشت مقادیر تطبیقی از یک کتاب کار دیگر (workbook) با استفاده از تابع Vlookup را توضیح خواهیم داد.

در مثال زیر، کتاب کار اول (product list) شامل لیست های محصول و هزینه (cost و product) است. حالا می خواهید هزینه مربوطه را در کتاب کار دوم بر اساس نوع محصول به دست آورید.

word image 35

برای به دست آوردن هزینه تقریبی از کتاب کار دیگر،

  • ابتدا هر دو کتاب کار را باز کنید.
  • فرمول زیر را در سلول مورد نظر برای محاسبه نتیجه اعمال کنید:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1′!$A$2:$B$6,2,0)

  • سپس این فرمول را بکشید و روی بقیه سلول ها کپی کنید:

word image 36

در این فرمول،

  • B2 مقدار جستجو را نشان می دهد.
  • [Product list.xlsx]Sheet1 نام کتاب کار و صفحه کاری است که اطلاعات از آن جستجو می شود، (آدرس کتاب کار در براکت قرار می گیرد و هر دو نام کتاب کار و صفحه در کوتیشن محصور می شوند).
  • A2:B6 محدوده داده های صفحه در کتاب کار دیگر است که داده ها در آن جستجو می شوند.
  • عدد ۲ شماره ستون شامل داده های متناظر بازگشتی است.
  • اگر فایل کتاب کار حاوی داده مورد جستجو بسته باشد، مسیر کامل فایل کتاب کار به فرم زیر نوشته می شود:

word image 37

حساسیت به حروف در Vlookup

به طور پیشفرض، جستجو در تابع Vlookup حساس به حروف (Case Sensitive) نیست، یعنی حروف کوچک و بزرگ برای آن یکسان می باشد.

گاهی اوقات ممکنه بخواهید یک جستجوی حساس به حروف را انجام دهید، برای این کار می توانید از دو فرمول دیگر استفاده کنید:

  • فرمول Index، Match و Exact
  • فرمول Lookup و Exact

مثال زیر را در نظر بگیرید، محدوده داده ها در ستون ID (ستون A) شامل رشته های متنی حاوی حروف بزرگ و کوچک است، فرض کنید می خواهید نمره درس Math (ریاضیات) مرتبط به ID داده شده را به دست آورید.

word image 38

فرمول ۱: استفاده از توابع EXACT، INDEX و MATCH

فرمول آرایه ای زیر را در سلول خالی مورد نظر برای محاسبه نتیجه وارد کنید:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

حالا کلیدهای Ctrl + Shift + Enter را همزمان فشار دهید تا نتیجه به دست آید، سپس سلول حاوی فرمول را انتخاب کرده و دستگیره آن را به سمت پایین بکشید تا فرمول روی بقیه سلول ها اعمال شود:

word image 39

در این فرمول،

  • محدوده A2:A10 شامل مقادیر خاصی است که می خواهید در آن جستجو کنید.
  • F2 مقدار مورد جستجو است.
  • C2:C10 ستونی است که مقدار متناظر از آن برگشت داده می شود.
  • اگر چند تطبیق یا نتیجه پیدا شود، این فرمول همیشه اولین مقدار تطبیق را برمی گرداند.

فرمول ۲: استفاده از توابع Lookup و Exact

فرمول زیر را در سلول خالی مورد نظر برای محاسبه نتیجه وارد کنید:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

سپس دستگیره سلول حاوی فرمول را به سمت پایین بکشید تا فرمول روی بقیه سلول ها نیز اعمال شود، مقدار متناظر برای مقدار جستجو با حروف بزرگ به دست می آید. به شکل زیر نگاه کنید:

word image 40

در این فرمول،

  • محدوده A2:A10 ستون حاوی مقادیر خاص است که در آن جستجو می شود.
  • F2 مقدار مورد جستجو است.
  • C2:C10 ستونی است که نتیجه از آن برگشت داده می شود.
  • اگر چند تطبیق یا نتیجه پیدا شود، این فرمول همیشه آخرین مقدار تطبیق را برمی گرداند.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

تفاوت پک های حرفه ای ما با آموزشهای رایگان: بطور خلاصه از زمین تا آسمان! 1-پکها، جدیدترین نسخه نرم افزارها را آموزش می دهند با قابلیت های بسیار بیشتر. 2-پکها توسط متخصص آن نرم افزار، به صورت کاملا پروژه محور و با حل چالش هایی که در مسیر کار عملی و حرفه ای با آن روبرو می شوید تهیه شده اند و بعد از استفاده، کاملا برای بازار کار آماده اید! 3- متد این پکها کاملا کار شده و تا ماهها، در ذهن تان ماندگارند و یادگیری بسیار سریعتر و کاملتری خواهید داشت. آموزشهای رایگان فقط دستورات نرم افزارها را (آنهم ناقص) بیان می کنند و تازه برای ورود به بازار باید ماهها تجربه عملی هم کسب کنید !!

turned_in

چرب زبان

با این اپلیکیشن ساده، هر زبانی رو فقط با 5 دقیقه در روز، توی 80 روز مثل بلبل حرف بزن! بهترین متد روز، تقویت حافظه، آموزش تصویری. یادگیری زبان کلید یادگیری هر مهارتی در قرن 21 !


حتما بخوانید!

7 دیدگاه. ارسال دیدگاه جدید

  • سلام من وقتی فرمول را میکشم رو بقیه سلول ها تا کپی کند جاهای جدول عوض میکنه ینی شماره سلول هایی ک انتخاب کردم که ازونجا سرچ کنه به ترتیب میاد پایین چیکار کنم که در فرمول بقیه سلول ها ثابت بمونه و فقط بخش اول بع ترتیب بیاد

    پاسخ
  • سلام.
    برای ثابت شدن حروف و اعداد ، آنها را بین دو علامت دلار قرار دهید.

    پاسخ
  • سلام چطوری میشه فايل ویلوکاپ شده که دارای فیلتر هست رو سورت کنيم و داده ها بهم نریزه و غلط نشه
    ممنون

    پاسخ
  • سلام
    من به ازای یک اسم چندتا تاریخ متفات دارم، میخوام در حالت جستجو مثلا تاریخ اول رو رد کنه و تاریخ دوم را به عنوان نتیجه بهم نشون بده، چجوری این کار رو با استفاده از Vlookup انجام بدم؟ در ضمن راهکار نمایش داده های تکرار ی رو میدونم ولی می خوام فقط یک نتیجه که اون هم نتیجه اول نیست رو بهم نشون بده

    پاسخ
    • سلام. توی محدود جستجوی جدولتون محدوده تاریخ اول رو درنظر نگیرید. محدوده‌ی تاریخ دوم رو بیارید توی فرمول.

      پاسخ
  • خیلی ممنون

    پاسخ
  • سلام
    مطلب عالی بود.
    بنده جدولی دارم که مثلا برای یک کالا در دو تاریخ مختلف قیمت تعیین شده و می‌خوام اونا رو در دو سطر جداگانه بنویسم و جداگانه مواردش رو حساب کنه و بده. یعنی مثلاً قیمت کتاب در فروردین و رو جدا و در اردیبهشت جدا برام تو فاکتور بیاره چطور میشه از این تابع استفاده کرد؟

    پاسخ

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این فیلد را پر کنید
این فیلد را پر کنید
لطفاً یک نشانی ایمیل معتبر بنویسید.

فهرست