تابع VlookUp پیشرفته

تابع 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 قرار دارند.

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

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

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

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

در این فرمول،

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

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

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

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

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

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

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

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

در این فرمول،

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

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

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

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

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

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

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

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

در این فرمول،

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

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

نکات:

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

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

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

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

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

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

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

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

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

در این فرمول،

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

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

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

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

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

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

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

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

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

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

در این فرمول،

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