چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

ما قبلا برای مبتدیان نحوه استفاده از توابع اولیه VLOOKUP را توضیح داده ایم (به انگلیسی VLOOKUP، مخفف عبارت "عمودی تابع جستجو" است). و به کاربران با تجربه چندین فرمول پیچیده تر نشان داده شد.

و در این مقاله سعی می کنیم در مورد روش دیگری از کار با جستجوی عمودی اطلاعاتی ارائه کنیم.

ممکن است تعجب کنید: "چرا این لازم است؟". و این برای نشان دادن تمام روش های جستجوی ممکن ضروری است. علاوه بر این، محدودیت های متعدد VLOOKUP اغلب از دستیابی به نتیجه مطلوب جلوگیری می کند. از این نظر، INDEX( ) MATCH( ) بسیار کاربردی تر و متنوع تر است و محدودیت های کمتری نیز دارند.

مبانی INDEX MATCH

از آنجایی که هدف این راهنما این است که نشان دهد این ویژگی چقدر خوب است، ما بیایید به اطلاعات اولیه در مورد اصول عملکرد آن نگاه کنیم. و نمونه هایی را نشان خواهیم داد و همچنین در نظر می گیریم که چرا بهتر از VLOOKUP ().

نحو و استفاده از تابع INDEX

این تابع به یافتن مقدار مورد نظر در بین مناطق جستجوی مشخص شده بر اساس شماره ستون یا خط کمک می کند. نحو:

=INDEX(آرایه، شماره ردیف، شماره ستون):

  • آرایه - منطقه ای که جستجو در آن انجام می شود.
  • شماره خط - شماره خطی که باید در آرایه مشخص شده جستجو شود. اگر شماره ردیف ناشناخته است، شماره ستون باید مشخص شود.
  • شماره ستون - تعداد ستونی که در آرایه مشخص شده یافت می شود. اگر مقدار ناشناخته باشد، شماره خط مورد نیاز است.

مثالی از یک فرمول ساده:

=INDEX(A1:S10,2,3)

این تابع در محدوده A1 تا C10 جستجو می کند. اعداد نشان می دهد که از کدام ردیف (2) و ستون (3) مقدار مورد نظر را نشان می دهد. نتیجه سلول C2 خواهد بود.

خیلی ساده، درست است؟ اما وقتی با اسناد واقعی کار می کنید، بعید است که اطلاعاتی در مورد شماره ستون یا سلول داشته باشید. تابع MATCH() برای همین است.

نحو و استفاده از تابع MATCH

تابع MATCH() مقدار مورد نظر را جستجو می کند و عدد تقریبی آن را در ناحیه جستجوی مشخص شده نشان می دهد.

سینتکس searchpos() به شکل زیر است:

=MATCH (مقدار جستجو، آرایه برای جستجو، نوع مطابقت)

  • مقدار جستجو - عدد یا متنی که باید پیدا شود.
  • آرایه جستجو شده - منطقه ای که جستجو در آن انجام می شود.
  • نوع تطبیق - مشخص می کند که آیا باید به دنبال مقدار دقیق یا نزدیک ترین مقادیر به آن بود:
    • 1 (یا بدون مقدار مشخص شده) - بزرگترین مقدار را که برابر یا کمتر از مقدار مشخص شده است را برمی گرداند.
    • 0 - مطابقت دقیق با مقدار جستجو شده را نشان می دهد. در ترکیب INDEX() MATCH() تقریباً همیشه به یک تطابق دقیق نیاز دارید، بنابراین ما 0 می نویسیم.
    • -1 – کوچکترین مقداری را که بزرگتر یا مساوی مقدار مشخص شده در فرمول است را نشان می دهد. مرتب سازی به ترتیب نزولی انجام می شود.

به عنوان مثال، در محدوده B1:B3 نیویورک، پاریس، لندن ثبت شده است. فرمول زیر عدد 3 را نشان می دهد زیرا لندن سومین لیست است:

=EXPOSE(London,B1:B3,0)

نحوه کار با تابع INDEX MATCH 

احتمالاً قبلاً شروع به درک اصلی کرده اید که کار مشترک این توابع بر اساس آن ساخته می شود. به طور خلاصه، پس INDEX() مقدار مورد نظر را در بین سطرها و ستون های مشخص شده جستجو می کند. و MATCH() اعداد این مقادیر را نشان می دهد:

=INDEX(ستونی که مقدار از آن برگردانده می شود، MATCH(مقدار برای جستجو، ستون برای جستجو، 0))

هنوز در درک نحوه عملکرد آن مشکل دارید؟ شاید مثالی بهتر توضیح دهد. فرض کنید لیستی از پایتخت های جهان و جمعیت آنها دارید:

برای اینکه اندازه جمعیت یک پایتخت خاص، به عنوان مثال، پایتخت ژاپن را بفهمیم، از فرمول زیر استفاده می کنیم:

=INDEX(C2:C10، MATCH(ژاپن، A2:A10,0،XNUMX))

شرح:

  • تابع MATCH() به دنبال مقدار – "Japan" در آرایه A2:A10 می گردد و عدد 3 را برمی گرداند، زیرا ژاپن سومین مقدار در لیست است. 
  • این رقم بهشماره خط” در فرمول INDEX() و به تابع می گوید که مقداری را از این ردیف چاپ کند.

بنابراین فرمول فوق به فرمول استاندارد تبدیل می شود INDEX(C2:C10,3). فرمول از سلول های C2 تا C10 جستجو می کند و داده ها را از سلول سوم در این محدوده، یعنی C4 برمی گرداند، زیرا شمارش معکوس از ردیف دوم شروع می شود.

آیا نمی خواهید نام شهر را در فرمول تجویز کنید؟ سپس آن را در هر سلولی بنویسید، مثلاً F1، و از آن به عنوان مرجع در فرمول MATCH() استفاده کنید. و در نهایت با فرمول جستجوی پویا مواجه می شوید:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

مهم! تعداد خطوط در صف INDEX() باید با تعداد سطرهای موجود در آن یکسان باشد آرایه در نظر گرفته شده است در MATCH()، در غیر این صورت نتیجه اشتباهی خواهید گرفت.

یک دقیقه صبر کنید، چرا فقط از فرمول VLOOKUP() استفاده نکنید؟

=VLOOKUP(F1, A2:C10, 3, False)

 اتلاف وقت برای کشف این همه پیچیدگی INDEX MATCH چه فایده ای دارد؟

در این مورد، مهم نیست که از کدام تابع استفاده کنید. این فقط یک مثال برای درک چگونگی کارکرد توابع INDEX() و MATCH() است. مثال‌های دیگر نشان می‌دهند که این توابع در شرایطی که VLOOKUP بدون قدرت است، چه کارایی دارند. 

INDEX MATCH یا VLOOKUP

هنگام تصمیم گیری برای استفاده از فرمول جستجو، بسیاری موافق هستند که INDEX() و MATCH() بسیار برتر از VLOOKUP هستند. با این حال، بسیاری از مردم هنوز از VLOOKUP() استفاده می کنند. اولا، VLOOKUP() ساده تر است، و ثانیا، کاربران به طور کامل تمام مزایای کار با INDEX() و MATCH() را درک نمی کنند. بدون این دانش، هیچ کس موافقت نمی کند که وقت خود را صرف مطالعه یک سیستم پیچیده کند.

در اینجا مزایای کلیدی INDEX() و MATCH() نسبت به VLOOKUP():

 

  • از راست به چپ جستجو کنید. VLOOKUP() نمی تواند از راست به چپ جستجو کند، بنابراین مقادیری که به دنبال آن هستید باید همیشه در سمت چپ ترین ستون های جدول باشند. اما INDEX() و MATCH() می توانند بدون مشکل این کار را انجام دهند. این مقاله به شما می گوید که در عمل چگونه به نظر می رسد: چگونه مقدار مورد نظر را در سمت چپ پیدا کنید.

 

  1. اضافه یا حذف ایمن ستون ها. فرمول VLOOKUP () نتایج نادرستی را هنگام حذف یا اضافه کردن ستون نشان می دهد زیرا VLOOKUP() برای موفقیت به شماره ستون دقیق نیاز دارد. طبیعتاً وقتی ستون ها اضافه یا حذف می شوند، تعداد آنها نیز تغییر می کند. 

و در فرمول های INDEX() و MATCH()، یک محدوده از ستون ها مشخص شده است، نه ستون های جداگانه. در نتیجه، می توانید با خیال راحت ستون ها را بدون نیاز به به روز رسانی فرمول هر بار اضافه و حذف کنید.

  1. هیچ محدودیتی در حجم جستجو وجود ندارد. هنگام استفاده از VLOOKUP()، تعداد کل معیارهای جستجو نباید از 255 کاراکتر تجاوز کند وگرنه یک #VALUE دریافت خواهید کرد! بنابراین اگر داده های شما حاوی تعداد زیادی کاراکتر است، INDEX() و MATCH() بهترین گزینه هستند.
  2. سرعت پردازش بالا اگر میزهای شما نسبتا کوچک هستند، بعید است که تفاوتی را متوجه شوید. اما، اگر جدول شامل صدها یا هزاران ردیف باشد، و بر این اساس، صدها و هزاران فرمول وجود داشته باشد، INDEX () و MATCH () بسیار سریعتر از VLOOKUP (). واقعیت این است که اکسل به جای پردازش کل جدول، تنها ستون های مشخص شده در فرمول را پردازش می کند. 

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

نمونه های فرمول 

ما قبلاً به سودمندی این توابع پی برده ایم، بنابراین می توانیم به جالب ترین بخش برویم: کاربرد دانش در عمل.

فرمول جستجو از راست به چپ

همانطور که قبلا ذکر شد، VLOOKUP نمی تواند این شکل از جستجو را انجام دهد. بنابراین، اگر مقادیر مورد نظر در ستون سمت چپ نباشد، VLOOKUP() نتیجه ای ایجاد نمی کند. توابع INDEX() و MATCH() تطبیق پذیرتر هستند و مکان مقادیر نقش زیادی برای کارکرد آنها ندارد.

به عنوان مثال، یک ستون رتبه در سمت چپ جدول خود اضافه می کنیم و سعی می کنیم بفهمیم که پایتخت کشور ما از نظر جمعیت چه رتبه ای را اشغال می کند.

در سلول G1 مقداری را که باید پیدا شود می نویسیم و سپس از فرمول زیر برای جستجو در محدوده C1:C10 استفاده می کنیم و مقدار مربوطه را از A2:A10 برمی گردانیم:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

سریع. اگر قصد دارید از این فرمول برای چندین سلول استفاده کنید، مطمئن شوید که محدوده ها را با استفاده از آدرس دهی مطلق (مثلاً $A$2: $A$10 و $C$2: 4C$10).

شاخص بیشتر در معرض بیشتر در معرض  برای جستجو در ستون ها و ردیف ها

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

پیچیده به نظر می رسد، اما فرمول چنین محاسباتی مشابه فرمول استاندارد INDEX() MATCH() است، تنها با یک تفاوت: فرمول MATCH() باید دو بار استفاده شود. بار اول برای به دست آوردن شماره ردیف و بار دوم برای دریافت شماره ستون:

=INDEX(آرایه، MATCH(مقدار جستجوی عمودی، ستون جستجو، 0)، MATCH(مقدار جستجوی افقی، ردیف جستجو، 0))

بیایید به جدول زیر نگاه کنیم و سعی کنیم یک فرمول بسازیم INDEX() EXPRESS() EXPRESS() به منظور نمایش جمعیتی در یک کشور خاص برای یک سال انتخاب شده.

کشور هدف در سلول G1 (جستجوی عمودی) و سال هدف در سلول G2 (جستجوی افقی) است. فرمول به صورت زیر خواهد بود:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

این فرمول چگونه کار می کند

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

  • MATCH(G1,A2:A11,0) - یک مقدار (G1) را در محدوده A2:A11 جستجو می کند و تعداد این مقدار را نشان می دهد، در مورد ما 2 است.
  • SEARCH(G2,B1:D1,0) – به دنبال مقدار (G2) در محدوده B1:D1 می گردد. در این مورد، نتیجه 3 بود.

اعداد سطر و ستون یافت شده به مقدار مربوطه در فرمول INDEX() ارسال می شوند:

=INDEX(B2:D11,2,3)

در نتیجه، مقداری داریم که در یک سلول در محل تقاطع 2 سطر و 3 ستون در محدوده B2:D11 قرار دارد. و فرمول مقدار مورد نظر را نشان می دهد که در سلول D3 است.

جستجو بر اساس شرایط متعدد با INDEX و MATCH

اگر راهنمای ما برای VLOOKUP() را خوانده باشید، احتمالا چندین فرمول جستجو را امتحان کرده اید. اما این روش جستجو دارای یک محدودیت قابل توجه است - نیاز به اضافه کردن یک ستون کمکی.

اما خبر خوب این است که با INDEX() و MATCH() می توانید چندین شرط را بدون نیاز به ویرایش یا تغییر کاربرگ خود جستجو کنید.

در اینجا فرمول کلی جستجوی چند شرطی برای INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

یادداشت: این فرمول باید همراه با میانبر صفحه کلید استفاده شود CTRL+SHIFT+ENTER.

فرض کنید باید ارزش مورد نظر خود را بر اساس 2 شرط پیدا کنید: خریدار и تولید - محصول.

این به فرمول زیر نیاز دارد:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

در این فرمول، C2:C10 محدوده ای است که جستجو در آن انجام می شود. F1 - این شرایط A2:A10 — محدوده ای برای مقایسه شرایط است، F2 - شرط 2، V2: V10 - محدوده برای مقایسه شرایط 2.

فراموش نکنید که ترکیب را در پایان کار با فرمول فشار دهید CTRL+SHIFT+ENTER – اکسل به طور خودکار فرمول را با پرانتزهای فرفری می بندد، همانطور که در مثال نشان داده شده است:

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

اگر نمی خواهید از فرمول آرایه ای برای کار خود استفاده کنید، سپس INDEX() دیگری را به فرمول اضافه کنید و ENTER را فشار دهید، مانند مثال زیر می شود:

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

این فرمول ها چگونه کار می کنند

این فرمول مانند فرمول استاندارد INDEX() MATCH() عمل می کند. برای جستجوی چند شرط، شما به سادگی چندین شرط False و True ایجاد می کنید که بیانگر شرایط فردی صحیح و نادرست است. و سپس این شرایط برای تمام عناصر متناظر آرایه اعمال می شود. این فرمول آرگومان های False و True را به ترتیب به 0 و 1 تبدیل می کند و آرایه ای را خروجی می دهد که در آن 1 مقادیر تطبیقی ​​است که در رشته پیدا شده است. MATCH() اولین مقداری را پیدا می کند که با 1 مطابقت دارد و آن را به فرمول INDEX() منتقل می کند. و به نوبه خود مقدار مورد نظر را در خط مشخص شده از ستون مورد نظر برمی گرداند.

فرمول بدون آرایه به توانایی INDEX() برای مدیریت آنها به تنهایی بستگی دارد. دومین INDEX() در فرمول با falsy (0) مطابقت دارد، بنابراین کل آرایه با آن مقادیر را به فرمول MATCH() منتقل می کند. 

این یک توضیح نسبتا طولانی از منطق پشت این فرمول است. برای اطلاعات بیشتر مقاله "INDEX MATCH با شرایط متعدد'.

AVERAGE، MAX و MIN در INDEX و MATCH

اکسل توابع خاص خود را برای یافتن میانگین ها، حداکثرها و حداقل ها دارد. اما اگر بخواهید از سلول مرتبط با آن مقادیر داده دریافت کنید، چه؟ در این مورد AVERAGE، MAX و MIN باید همراه با INDEX و MATCH استفاده شوند.

INDEX MATCH و MAX

برای پیدا کردن بزرگترین مقدار در ستون D و نمایش آن در ستون C، از فرمول استفاده کنید: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH و MIN

برای پیدا کردن کوچکترین مقدار در ستون D و نمایش آن در ستون C، از فرمول زیر استفاده کنید:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX و SERPENT

برای پیدا کردن مقدار متوسط ​​در ستون D و نمایش این مقدار در C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

بسته به نحوه نوشتن داده های شما، آرگومان سوم MATCH() 1، 0 یا -1 است:

  • اگر ستون ها به ترتیب صعودی مرتب شده اند، 1 را تنظیم کنید (سپس فرمول حداکثر مقدار را محاسبه می کند که کمتر یا مساوی با مقدار متوسط ​​است).
  • اگر مرتب سازی نزولی باشد، آنگاه -1 (فرمول حداقل مقداری را که بزرگتر یا مساوی با میانگین است به دست می دهد).
  • اگر آرایه جستجو حاوی مقداری است که دقیقاً برابر با میانگین است، آن را روی 0 قرار دهید. 

 در مثال ما، جمعیت به ترتیب نزولی مرتب شده است، بنابراین ما -1 را قرار می دهیم. و نتیجه توکیو است، زیرا ارزش جمعیت (13,189) نزدیکترین مقدار به میانگین (000) است.

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

VLOOKUP() همچنین می تواند چنین محاسباتی را انجام دهد، اما فقط به عنوان فرمول آرایه: VLOOKUP با AVERAGE، MIN و MAX.

INDEX MATCH و ESND/IFERROR

احتمالاً قبلاً متوجه شده اید که اگر فرمول نتواند مقدار مورد نظر را پیدا کند، خطا می دهد # N / A. می توانید پیام خطای استاندارد را با چیزی آموزنده تر جایگزین کنید. به عنوان مثال، آرگومان را در فرمول تنظیم کنید در XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

با این فرمول اگر داده ای را وارد کنید که در جدول نیست، فرم پیغام مشخص شده را به شما می دهد.

چرا INDEX و MATCH بهتر از VLOOKUP در اکسل هستند

اگر می خواهید همه خطاها را بگیرید، به جز برای در XNUMX می تواند مورد استفاده قرار گیرد IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0))، "مشکلی پیش آمد!")

اما به یاد داشته باشید که پنهان کردن خطاها به این روش ایده خوبی نیست، زیرا خطاهای استاندارد موارد نقض فرمول را گزارش می دهند.

امیدواریم راهنمای ما برای استفاده از تابع INDEX MATCH() مفید بوده باشد.

پاسخ دهید