Функцията INDEX може да се използва самостоятелно, но влагането на функцията MATCH вътре в нея създава разширено търсене.
Тази вложена функция е по-гъвкава от VLOOKUP и може да дава резултати по-бързо.
Тази статия обяснява как да използвате функциите INDEX и MATCH заедно във всички версии на Excel, включително Excel 2019 и Microsoft 365.
Какво представляват функциите INDEX и MATCH?
INDEX и MATCH са функции за търсене на Excel. Въпреки че са две напълно отделни функции, които могат да се използват самостоятелно, те могат също да се комбинират за създаване на разширени формули.
Функцията INDEX връща стойност или препратка към стойност от определена селекция. Например, може да се използва за намиране на стойността във втория ред на набор от данни или в петия ред и третата колона.
Докато INDEX може много добре да се използва самостоятелно, влагането на MATCH във формулата го прави малко по-полезен. Функцията MATCH търси определен елемент в диапазон от клетки и след това връща относителната позиция на елемента в диапазона. Например, може да се използва, за да се определи, че конкретно име е третият елемент в списък с имена.
Image
ИНДЕКС и MATCH Синтаксис и аргументи
Ето как трябва да бъдат написани и двете функции, за да ги разбере Excel:
=ИНДЕКС(масив, номер_на_ред, [номер_на_колона])
array е диапазонът от клетки, които формулата ще използва. Може да бъде един или повече редове и колони, като A1:D5. Задължително е.
row_num е редът в масива, от който да се върне стойност, като 2 или 18. Изисква се, освен ако не присъства column_num.
column_num е колоната в масива, от която да се върне стойност, като 1 или 9. Не е задължително.
lookup_value е стойността, която искате да съпоставите в lookup_array. Това може да бъде число, текст или логическа стойност, която е въведена ръчно или към която се отнася чрез препратка към клетка. Това е задължително.
lookup_array е диапазонът от клетки, през който да се разглежда. Може да бъде един ред или една колона, като A2:D2 или G1:G45. Това е задължително.
match_type може да бъде -1, 0 или 1. Той указва как lookup_value се съпоставя със стойностите в lookup_array (вижте по-долу). 1 е стойността по подразбиране, ако този аргумент е пропуснат.
Кой тип съвпадение да използвате
Тип съвпадение
Какво прави
Правило
Пример
1
Намира най-голямата стойност, която е по-малка или равна на lookup_value.
Стойностите на lookup_array трябва да бъдат поставени във възходящ ред (напр. -2, -1, 0, 1, 2; или A-Z;, или FALSE, TRUE.
lookup_value е 25, но липсва в lookup_array, така че вместо това се връща позицията на следващото най-малко число, като 22.
0
Намира първата стойност, която е точно равна на lookup_value.
Стойностите на lookup_array могат да бъдат в произволен ред.
търсещата_стойност е 25, така че връща позицията на 25.
-1
Намира най-малката стойност, която е по-голяма или равна на lookup_value.
Стойностите на lookup_array трябва да бъдат поставени в низходящ ред (напр. 2, 1, 0, -1, -2).
lookup_value е 25, но липсва в lookup_array, така че вместо това се връща позицията на следващото по големина число, като 34.
Използвайте 1 или -1 за моменти, когато трябва да извършите приблизително търсене по скала, например когато работите с числа и когато приближенията са подходящи. Но не забравяйте, че ако не посочите match_type, 1 ще бъде по подразбиране, което може да изкриви резултатите, ако наистина искате точно съвпадение.
Примерни формули за INDEX и MATCH
Преди да разгледаме как да комбинираме INDEX и MATCH в една формула, трябва да разберем как тези функции работят сами.
INDEX Примери
=ИНДЕКС(A1:B2, 2, 2)
=ИНДЕКС(A1:B1, 1)
=ИНДЕКС(2:2, 1)=ИНДЕКС(B1:B2; 1)
Image
В този първи пример има четири ИНДЕКС формули, които можем да използваме, за да получим различни стойности:
=ИНДЕКС(A1:B2, 2, 2) преглежда A1:B2, за да намери стойността във втората колона и втория ред, която е Стейси.йени
=INDEX(A1:B1, 1) преглежда A1:B1, за да намери стойността в първата колона, която е Jon.
=INDEX(2:2, 1) преглежда всичко във втория ред, за да намери стойността в първата колона, която е Тим.
=INDEX(B1:B2, 1) преглежда B1:B2, за да намери стойността в първия ред, който е Ейми.
Примери за MATCH
=MATCH("Стейси", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=МАЧ (13, A1:D1, 0)
Image
Ето четири лесни примера за функцията MATCH:
=MATCH("Stacy", A2:D2, 0) търси Stacy в диапазона A2:D2 и връща 3 като резултат.
=MATCH(14, D1:D2) търси 14 в диапазона D1:D2, но тъй като не е намерен в таблицата, MATCH намира следващата най-голяма стойност това е по-малко или равно на 14, което в този случай е 13, което е в позиция 1 на lookup_array.
=MATCH(14, D1:D2, -1) е идентичен с формулата над него, но тъй като масивът не е в низходящ ред, както изисква -1, получаваме грешка.
=MATCH(13, A1:D1, 0) търси 13 в първия ред на листа, което връща 4, тъй като това е четвъртият елемент в този масив.
Примери за INDEX-MATCH
Ето два примера, където можем да комбинираме INDEX и MATCH в една формула:
Намиране на препратка към клетка в таблица
=ИНДЕКС(B2:B5, MATCH(F1, A2:A5))
Image
Този пример влага формулата MATCH във формулата INDEX. Целта е да се идентифицира цвета на артикула чрез номера на артикула.
Ако погледнете изображението, можете да видите в редовете „Разделени“как формулите биха били написани сами по себе си, но тъй като ние ги влагаме, това се случва:
MATCH(F1, A2:A5) търси стойността F1 (8795) в набора от данни A2:A5. Ако преброим колоната, можем да видим, че е 2, така че това е, което функцията MATCH току-що разбра.
Масивът INDEX е B2:B5, тъй като в крайна сметка търсим стойността в тази колона.
Функцията INDEX вече може да бъде пренаписана по този начин, тъй като 2 е това, което MATCH намери: INDEX(B2:B5, 2, [column_num]).
Тъй като column_num не е задължително, можем да го премахнем, за да остане следното: INDEX(B2:B5, 2).
Така сега, това е като нормална ИНДЕКС формула, където намираме стойността на втория елемент в B2:B5, който е червен.
В този пример за MATCH и INDEX правим двупосочно търсене. Идеята е да видим колко пари сме спечелили от зелени артикули през май. Това наистина е подобно на примера по-горе, но в INDEX е вложена допълнителна формула MATCH.
MATCH(G1, A2:A13, 0) е първият решен елемент в тази формула. Той търси G1 (думата "май") в A2:A13, за да получи определена стойност. Не го виждаме тук, но е 5.
MATCH(G2, B1:E1, 0) е втората формула MATCH и тя наистина е подобна на първата, но вместо това търси G2 (думата „Зелено“) в заглавията на колоните при B1:E1. Този се разрешава на 3.
Сега можем да пренапишем формулата ИНДЕКС така, за да визуализираме какво се случва: =ИНДЕКС(B2:E13, 5, 3). Това търси в цялата таблица, B2:E13, за петия ред и третата колона, което връща $180.
Правила за СЪВПАДЕНИЕ и ИНДЕКС
Има няколко неща, които трябва да имате предвид, когато пишете формули с тези функции:
MATCH не е чувствителен към главни и малки букви, така че главните и малките букви се третират по един и същ начин при съвпадение на текстови стойности.
MATCH връща N/A по множество причини: ако match_type е 0 и lookup_value не е намерено, ако match_type е -1 и lookup_array не е в низходящ ред, ако match_type е 1 и lookup_array не е във възходящ ред и ако lookup_array не е единичен ред или колона.
Можете да използвате заместващ знак в аргумента lookup_value, ако match_type е 0 и lookup_value е текстов низ. Въпросителен знак съответства на всеки отделен знак, а звездичка съответства на всяка последователност от знаци (напр.например, =MATCH("Jo", 1:1, 0)). За да използвате MATCH, за да намерите действителен въпросителен знак или звездичка, първо въведете ~.
INDEX връща REF! ако row_num и column_num не сочат към клетка в масива.
Свързани функции на Excel
Функцията MATCH е подобна на LOOKUP, но MATCH връща позицията на елемента вместо самия елемент.
VLOOKUP е друга функция за търсене, която можете да използвате в Excel, но за разлика от MATCH, която изисква INDEX за разширени търсения, формулите VLOOKUP се нуждаят само от тази една функция.
Функцията ISBLANK на Microsoft Excel може да бъде чудесен инструмент за намиране на дупки във вашата база данни. Ето как да го използвате максимално, с условно форматиране и не
Microsoft Excel предоставя функции не само за числа, но и за текст. Функцията MID на Excel ще ви помогне да получите текст от средата на съдържанието на клетките. Ето как да го използвате
Научете как да използвате функцията ДАТА във вашите формули на Excel. Функцията ДАТА на Excel може да се използва за комбиниране на стойности за създаване на дата
Използвайте функцията MATCH на Excel, за да намерите първата относителна позиция на конкретни стойности в списък или таблица с данни. Актуализиран, за да включва Excel 2019
