ترکیب VLOOKUP MATCH

0
100557 این مطلب رو نوشت. 31 اوت 2014

سلام

1. تصور کنید یک جدول با تعداد زیادی ستون داریم به نظر شما به بهتر نیست به جای شمارش کردن ستونها یافتن شماره ستون مورد نظر را به اکسل محول کنیم.
2. اگر شماره ستونی که می‌خواهید با استفاده از VLOOKUP ارزش آن را برگردانید تغییر کند "جابجا شود" VLOOKUP کماکان اطلاعات مربوط را برگشت خواهد داد؟
ترکیب VLOOKUP و MATCH یکی ترکیبهای محبوب و متداول برای جستجو در کاربرگهای اکسل است که در این پست سعی می‌شود به نحو مناسب آموزش داده شود. لازم است یادآوری کنم پیش فرض من برای ارائه این آموزش این است که شما درک صحیحی از دو فرمول VLOOKUP و MATCH دارید و می‌توانید به نحو مناسب از آنها در انجام کارهایتان استفاده کنید. در غیر این صورت یادگیری را پیش از مطالعه این مطالب توصیه می‌نماییم.
بیان مسئله
در بسیار از مواقع نیاز می‌شود از یکی از کاربرگهای اکسل اطلاعاتی را استخراج کرده و در کاربرگ دیگری استفاده نماییم. در چنین شرایطی فرمول VLOOKUP پرکاربرد ترین فرمولی است که کاربران از آن استفاده می‌کنند اما یک مشکل در استفاده از این تابع ممکن است است پیش بیاید و آن این است که اگر دو کاربرگ یادشده در دو workbook باشند برای حذف و اضافه کردن ستونها در workbook اول بای workbook دوم باز باشد در غیر این صورت متاسفانه فرمول VLOOKUP استفاده شده در کاربرگ دوم متناسب با تغییراتی که در کاربرگ اول داده‌اید تغییر نخواهد کرد و در نتیجه اطلاعاتی که فرمول VLOOKUP در کاربرگ دوم برگشت می‌دهد نامناسب و نامربوط خواهد بود. (این مشکل همین امروز حین تهیه کاربرگ تلفیق برای خودم پیش آمد و اصلاح آن حدوداً 4 ساعت طول کشید). برای همین تصمیم گرفتم از این به بعد در حین استفاده از VLOOKUP کمی حرفه‌ای تر برخورد کنم.
هدف
استفاده از فرمول MATCH در آرگیومت "argument" سوم فرمول VLOOKUP جهت یافتن شماره ستون مورد نظر در جدول اولیه به صورت هوشمند. ساختار فرمول VLOOKUP به یاد بیاورید:

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

در حقیقت قصد داریم ارگیومت colindexnum را با استفاده از فرمول MATCH مشخص کنیم البته قبل از پرداختن به ادامه بحث اجازه دهید ساختار فرمول MATCH را یادآوری کنم:

= MATCH(lookup_value, lookup_array, [match_type])

اگر بخواهیم این دو فرمول را به گونه9ای با هم ترکیب کنیم که با استفاده از MATCH آرگیومت colindexnum به صورت هوشمند مشخص شود نتیجه ترکیب به صورت زیر خواهد بود:

= VLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, [match_type]), [range_lookup])

آن بخش از سطر بالا که با رنگ آبی نوشته شده است فرمول MATCH است که در آرگیومت colindexnum فرمول VLOOKUP نوشته شده است.
برای استفاده از فرمول VLOOKUP به یک ارزش برای جستجو "lookupvalue" و یک محدوده مشخص "tablearray" و نیز شماره ستون "colindexnum" مورد نظر از محدوده مشخص شده نیاز داریم. لطفا به تصویر زیر نگاه کنید:

در این تصویر ارزش مورد جستجو عدد 5 محدوده مورد جستجو B6:F14 است و ستونی که قصد داریم ارزش آن را برگشت دهیم ستون چهارم است که State نام دارد. حال به تصویر زیر دقت نمایید.

در تصویر فوق آنچه که قرار است انجام دهیم رنگ شده اما فرمولها درج نشده است. برای ترکیب فرمولها با من همراه شوید:
از آنجایی که نابرده رنچ گنجی بدست نخواهد آمد توصیه می‌کنم یک فایل مشابه با فایلی که در تصویر مشاهده می‌نمایید ایجاد نمایید. و مراحل زیر را انجام دهید
گام اول
گام اول نوشتن فرمول VLOOKUP است، پس لطفا فرمول VLOOKUP را نوشته ارزشی را که قصد داریم جستجو را بر اساس آن انجام دهیم مشخص کنید در کاربرگ من ارزش مورد جستجو در سلول B3 قرار دارد. و محدوده B6:F14 جدولی است که می‌خواهم در آن جستجو کنم لذا این محدوده را به عنوان آرگیومنت دوم یا tablearray مشخص می‌کنم.

گام دوم
حال نوبت استفاده از فرمول MATCH است برای این منظور فرمول MATCH را در آرگیومنت سوم از فرمول VLOOKUP می‌نویسیم.


گام سوم
با توجه به ساختار فرمول MATCH اولین آرگیومت آن را که همان ارزشی است که می‌خواهیم با استفاده از فرمول MATCH شماره ستون آن را پیدا کنیم تکمیل می‌کنیم، پیشتر عنوان شد که قصد داریم ستون چهارم را برگشت دهیم که نام آن State است برای این منظور من روی سلول E6 کلیک کردم شما نیز می‌توانید اینچنین کنید لیکن صحیح تر آن است که مقدار مورد جستجو را به صورت مستقل در یک سلول دیگر و یا به صورت مستقیم در فرمول MATCH بنویسیم.

گام چهارم
برای برداشتن چهارمین گام کافی است محدوده ای را که قرار است فرمول MATCH عبارت State را در آن جستجو کند مشخص نمایید. این محدوده برای مثال من B6:F6. است.

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

نکته: به خاطر داشته باشید عدم تکمیل آرگیومت‌های [match
type] و [range_lookup] هموراه سودمند نیست و خیلی از مواقع باید با استفاده از پارامتر مناسب تکمیل شود که در پست مربوط به آموزش فرمولهای فوق به تفصیل توضیح داده شده است لذا در اینجا در مورد آنها بحث نگردیده است.
نتیجه گامهایی که برداشتم به صورت زیر است.

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

منبع

آخرین ویرایش 05 سپتامبر 2014

samir karamkhani

1 نظرات
0
100557 این مطلب رو نوشت. 05 سپتامبر 2014

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

samir karamkhani


261 نفر از 625 عضو ما 1162 بار در مباحثه ها نظر ارسال کرده اند.