В Excel точното извличане на конкретни данни често е честа необходимост. Докато функциите INDEX и MATCH имат своите собствени силни страни, комбинирането им отключва мощен набор от инструменти за търсене на данни. Заедно те улесняват набор от възможности за търсене, от основни хоризонтални и вертикални търсения до по-усъвършенствани функционалности като двупосочни, чувствителни към главни и малки букви и многокритериални търсения. Предлагайки подобрени възможности в сравнение с VLOOKUP, сдвояването на INDEX и MATCH позволява по-широка гама от опции за търсене на данни. В този урок нека навлезем в дълбочината на възможностите, които те могат да постигнат заедно.

• функция ИНДЕКС
• Функция МАЧ
• Комбинирайте INDEX и MATCH

• Двупосочно търсене
• Ляво търсене
• Чувствително към малки и големи букви
• Най-близкото съвпадение
• Търсене с множество критерии
• Търсене в множество колони
• Търсене на първа стойност, която не е празна
• Търсене на първа числова стойност
• Търсене на MAX или MIN асоциации
• Съвет: Персонализирайте свои собствени съобщения за грешка #N/A

Как да използвате INDEX и MATCH в Excel

Преди да използваме функциите INDEX и MATCH, нека се уверим, че знаем как INDEX и MATCH могат да ни помогнат първо да търсим стойности.

Как да използвате функцията INDEX в Excel

- INDEX функцията в Excel връща стойността на дадено място в определен диапазон. Синтаксисът на функцията INDEX е както следва:

• масив (задължително) се отнася до диапазона, от който искате да върнете стойността.
• номер_ред (задължително, освен ако колона_номер присъства) се отнася до номера на реда на масива.
• колона_номер (по избор, но задължително, ако номер_ред е пропуснат) се отнася до номера на колоната на масива.

Например да знам резултатът на Джеф- 6студент в списъка, можете да използвате функцията INDEX по следния начин:

=INDEX(C2:C11,6)

√ Забележка: Диапазонът C2:C11 е мястото, където са изброени резултатите, докато числото 6 намира резултата от изпита на 6ти ученик.

Ето нека направим малък тест. За формулата =ИНДЕКС(A1:C1,2;XNUMX), каква стойност ще върне? --- Да, ще се върне Дата на раждане- 2рата стойност в дадения ред.

Сега трябва да знаем, че функцията INDEX може да работи перфектно с хоризонтални или вертикални диапазони. Но какво ще стане, ако имаме нужда да върне стойност в по-голям диапазон с няколко реда и колони? Е, в този случай трябва да приложим както номер на ред, така и номер на колона. Например, за да разберете Резултатът на Джеф в диапазона на таблицата, вместо в една колона, можем да намерим резултата му с a ред номер 6 и колона номер 3 в клетки през A2 до C11 като този:

=INDEX(A2:C11,6,3)

Въпреки това, за наистина голяма база данни с множество редове и колони, със сигурност не е удобно да прилагаме формулата с точен номер на ред и номер на колона. И това е, когато трябва да комбинираме използването на функцията MATCH.

невероятно! Използване на ефективни раздели в Excel като Chrome, Edge, Firefox и Safari!
Спестете 50% от времето си и намалете хилядите кликвания на мишката за вас всеки ден!

Как да използвате функцията MATCH в Excel

Функцията MATCH в Excel връща числова стойност, местоположението на конкретен елемент в дадения диапазон. Синтаксисът на функцията MATCH е както следва:

• търсена_стойност (задължително) се отнася до стойността за съвпадение в търсене_масив.
• търсене_масив (задължително) се отнася до диапазона от клетки, където искате MATCH да търси.
• съвпадение_тип (по избор): 1, 0 or -1.
  • 1 (по подразбиране), MATCH ще намери най-голямата стойност, която е по-малка или равна на търсена_стойност. Стойностите в търсене_масив трябва да бъдат поставени във възходящ ред.
  • 0, MATCH ще намери първата стойност, която е точно равна на търсена_стойност. Стойностите в търсене_масив може да бъде в произволен ред. (За случаите, когато типът на съвпадението е зададен на 0, можете да използвате заместващи знаци.)
  • -1, MATCH ще намери най-малката стойност, която е по-голяма или равна на търсена_стойност. Стойностите в търсене_масив трябва да бъдат поставени в низходящ ред.

Например да знам позицията на Вера в списъка с имена, можете да използвате формулата MATCH по следния начин:

=MATCH("Vera",A2:A11,0)

√ Забележка: Резултатът “4” показва, че името “Vera” е на 4-та позиция в списъка.

След като вече знаем за основните употреби на функциите INDEX и MATCH в Excel, нека запретнем ръкави и да се подготвим да комбинираме двете функции.

Как да комбинирате INDEX и MATCH в Excel

Моля, вижте примера по-долу, за да разберете как можем да комбинираме функциите INDEX и MATCH:

Да намеря Резултатът на Евелин, със знанието, че оценките от изпитите са в 3rd колона, можем използвайте функцията MATCH, за да определите автоматично позицията на реда без да е необходимо ръчно да го броите. Впоследствие можем да използваме функцията INDEX, за да извлечем стойност в пресечната точка на идентифицирания ред и 3-тата колона:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Е, тъй като формулата може да изглежда малко сложна, нека преминем през всяка част от нея.

- INDEX формулата съдържа три аргумента:

• номер_ред: MATCH("Евелин";A2:A11,0;XNUMX) предоставя INDEX с позицията на реда на стойността "Евелин“ в диапазона A2: A11, кое е 5.
• колона_номер: 3 уточнява 3rd колона за INDEX, за да намерите резултата в масива.
• масив: A2:C11 инструктира INDEX да върне съвпадащата стойност в пресечната точка на посочения ред и колона, в рамките на диапазона, обхващащ от A2 до C11. Накрая получаваме резултата 90.

В горната формула използвахме твърдо кодирана стойност, "Евелин". На практика обаче твърдо кодираните стойности са непрактични, тъй като биха изисквали промяна всеки път, когато се стремим да търсим различни данни, като резултата за друг ученик. В такива сценарии можем да използваме препратки към клетки, за да създадем динамични формули. Например в този случай ще го направя променете "Evelyn" на F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Примери за формули INDEX и MATCH

В тази част ще говорим за различни обстоятелства за използване на функциите INDEX и MATCH за посрещане на различни нужди.

INDEX и MATCH за прилагане на двупосочно търсене

В предишния пример знаехме номера на колоната и използвахме формула MATCH, за да намерим номера на реда. Но какво ще стане, ако не сме сигурни и за номера на колоната?

В такива случаи можем да извършим двупосочно търсене, известно още като търсене в матрица, като използваме две функции MATCH: едната за намиране на номера на реда, а другата за определяне на номера на колоната. Например да знам Резултатът на Евелин, трябва да използваме формулата:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX и MATCH, за да приложите ляво търсене

Сега нека разгледаме сценарий, при който трябва да определите класа на Евелин. Може да сте забелязали, че колоната за клас е позиционирана отляво на колоната с име, ситуация, която надхвърля възможностите на друга мощна функция за търсене на Excel, VLOOKUP.

Всъщност възможността за извършване на търсене отляво е един от аспектите, при които комбинацията от INDEX и MATCH засенчва VLOOKUP.

Да намеря Класът на Евелин, използвайте следната формула, за да потърсете Евелин в B2:B11 намлява извлечете съответната стойност от A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX и MATCH за прилагане на търсене, чувствително към главни и малки букви

Функциите MATCH по своята същност са нечувствителни към главни и малки букви. И все пак, когато изисквате вашата формула да прави разлика между главни и малки букви, можете да я подобрите, като включите ТОЧНО функция. Като комбинирате функцията MATCH с EXACT във формула INDEX, след това можете ефективно да извършите търсене, чувствително към главни и малки букви, както е показано по-долу:

• масив се отнася до диапазона, от който искате да върнете стойността.
• търсена_стойност се отнася до стойността за съвпадение, като се има предвид регистърът на знаците, в търсене_масив.
• търсене_масив се отнася до диапазона от клетки, с които искате да сравните MATCH търсена_стойност.

Например да знам Резултатът от изпита на ДЖИМИ, използвайте следната формула:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете, освен в Excel 365 и Excel 2021.

INDEX и MATCH, за да намерите най-близкото съвпадение

В Excel може да срещнете ситуации, в които трябва да намерите най-близкото или най-близкото съвпадение на конкретна стойност в набор от данни. В такива сценарии използването на комбинация от функции INDEX и MATCH, заедно с функциите ABS и MIN, може да бъде изключително полезно.

• масив се отнася до диапазона, от който искате да върнете стойността.
• търсене_масив се отнася до диапазона от стойности, където искате да намерите най-близкото съвпадение търсена_стойност.
• търсена_стойност препраща към стойността, за да намери нейното най-близко съвпадение.

Например, за да разберете чийто резултат е най-близо до 85, използвайте следната формула, за да потърсете най-близкия резултат до 85 в C2:C11 намлява извлечете съответната стойност от A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете, освен в Excel 365 и Excel 2021.

невероятно! Използване на ефективни раздели в Excel като Chrome, Edge, Firefox и Safari!
Спестете 50% от времето си и намалете хилядите кликвания на мишката за вас всеки ден!

INDEX и MATCH, за да приложите търсене с множество критерии

За да намерите стойност, която отговаря на множество условия, което изисква търсене в две или повече колони, използвайте следната формула. Формулата ви позволява да извършите търсене по множество критерии, като посочите различни условия в различни колони, което ви помага да намерите желаната стойност, която отговаря на всички посочени критерии.

√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете. След това в лентата за формули ще се покажат чифт къдрави скоби.

• масив се отнася до диапазона, от който искате да върнете стойността.
• (търсена_стойност=търсен_масив) представлява едно условие. Това условие проверява дали конкретно търсена_стойност съответства на стойностите в търсене_масив.

Например, за да намерите оценка на Коко от клас А, чиято рождена дата е 7/2/2008, можете да използвате следната формула:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX и MATCH, за да приложите търсене в множество колони

Представете си сценарий, при който имате работа с множество колони с данни. Първата колона действа като ключ за класифициране на данните в другите колони. За да определите категорията или класификацията за конкретен запис, ще трябва да извършите търсене в колоните с данни и да го свържете със съответния ключ в референтната колона.

Например, в таблицата по-долу, как можем да свържем ученика Шон със съответния му клас, като използваме ИНДЕКС и СЪВПАДЕНИЕ? Е, можете да го постигнете с формула, но формулата е доста обширна и може да бъде предизвикателство за разбиране, камо ли запомняне и въвеждане.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Ето къде Kutools за Excel's Индексиране и съпоставяне на множество колони функцията е полезна. Той опростява процеса, като прави бързо и лесно съпоставянето на конкретни записи със съответните им категории. За да отключите този мощен инструмент и без усилие да сравните Шон с неговия клас, просто изтеглете и инсталирайте добавката Kutools за Excelи след това направете следното:

1. Изберете целевата клетка, където искате да покажете съответстващия клас.
2. От Kutools кликнете върху Помощник за формула > Търсене и справка > Индексиране и съпоставяне на множество колони.
3. 
3. В изскачащия диалогов прозорец направете следното:
   1. Щракнете върху 1-ви бутона до Lookup_col за да изберете колоната, съдържаща ключовата информация, която искате да върнете, т.е. имената на класовете. (Тук можете да изберете само една колона.)
   2. Щракнете върху 2-ро бутона до Table_rng за да изберете клетките, които да съответстват на стойностите в избраните Lookup_col, т.е. имената на учениците.
   3. Щракнете върху 3-то бутона до Търсене_стойност за да изберете клетката, съдържаща името на ученика, което искате да съпоставите с неговия клас, в този случай Шон.
   4. Кликнете OK.
   5. 

Резултат

Kutools автоматично генерира формулата и веднага ще видите името на класа на Шон, показано в целевата клетка.

INDEX и MATCH за търсене на първата непразна стойност

За да извлечете първата непразна стойност, игнорирайки грешките, от колона или ред, можете да използвате формула, базирана на функциите INDEX и MATCH. Ако обаче не искате да игнорирате грешките от вашия диапазон, добавете функцията ISBLANK.

• Вземете първата непразна стойност в колона или ред, като игнорирате грешки:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Вземете първата непразна стойност в колона или ред, включително грешки:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX и MATCH за търсене на първата числова стойност

За да извлечете първата цифрова стойност от колона или ред, използвайте формулата, базирана на функциите INDEX, MATCH и ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX и MATCH за търсене на MAX или MIN асоциации

Ако трябва да извлечете стойност, свързана с максималната или минималната стойност в рамките на диапазон, можете да използвате функцията MAX или MIN заедно с функциите INDEX и MATCH.

• INDEX и MATCH за извличане на стойност, свързана с максималната стойност:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX и MATCH за извличане на стойност, свързана с минималната стойност:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• В горните формули има два аргумента:
• • масив се отнася до диапазона, от който искате да върнете свързаната информация.
  • търсене_масив представлява набор от стойности, които трябва да бъдат изследвани или търсени за конкретни критерии, т.е. максимални или минимални стойности.

Например, ако искате да определите който има най-висок резултат, използвайте следната формула:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Съвет: Персонализирайте свои собствени съобщения за грешка #N/A

Когато работите с функциите INDEX и MATCH на Excel, може да срещнете грешка #N/A, когато няма съответстващ резултат. Например в таблицата по-долу, когато се опитвате да намерите резултата на ученичка на име Саманта, се появява грешка #N/A, тъй като тя не присъства в набора от данни.

За да направите вашите електронни таблици по-удобни за потребителя, можете да персонализирате това съобщение за грешка, като обвиете вашата формула INDEX MATCH във функцията IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

По-горе е цялото подходящо съдържание, свързано с функциите INDEX и MATCH в Excel. Надявам се да намерите урока за полезен. Ако искате да проучите още съвети и трикове за Excel, моля, натиснете тук за достъп до нашата обширна колекция от над хиляди уроци.