INDEX и MATCH в множество колони
За да търсите стойност чрез съвпадение в множество колони, формула за масив, базирана на INDEX намлява MATCH функции, които включва MMULT, ТРАНЗОЗИРАНЕ намлява КОЛОНА ще ти направи услуга.
Как да търсите стойност чрез съвпадение в множество колони?
За да попълните съответния клас на всеки ученик както е показано в горната таблица, където информацията е посочена в множество колони, можете първо да използвате трика на функцията MMULT, TRANSPOSE и COLUMN, за да създадете матричен масив. Тогава функцията MATCH ще ви даде позицията на вашата търсена стойност, която ще бъде подадена към INDEX, за да извлече стойността, която търсите в масива.
Общ синтаксис
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете.
- върнат_обхват: Диапазонът, от който искате формулата да върне информацията за класа. Тук се отнася до обхвата на класа.
- търсена_стойност: Стойността, използвана от формулата за намиране на съответната информация за класа. Тук се отнася до даденото име.
- търсене_масив: Диапазонът от клетки, където търсена_стойност е в списъка; Диапазонът със стойностите за сравнение с търсена_стойност. Тук се отнася до диапазона от имена.
- match_type 0: Принуждава MATCH да намери първата стойност, която е точно равна на търсена_стойност.
За да намерите класа на Джими, моля, копирайте или въведете формулата по-долу в клетка H5 и натиснете Ctrl + Превключване + Въведете за да получите резултата:
=ИНДЕКС($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),ТРАНСПОНИРАНЕ(КОЛОНА($C$5:$E$7)^0)),0)))
√ Забележка: Знаците за долар ($) по-горе показват абсолютни препратки, което означава, че диапазоните на името и класа във формулата няма да се променят, когато преместите или копирате формулата в други клетки. Имайте предвид, че не трябва да добавяте знаци за долар към препратката към клетката, която представлява търсената стойност, тъй като искате тя да бъде относителна, когато я копирате в други клетки. След като въведете формулата, плъзнете манипулатора за попълване надолу, за да приложите формулата към клетките по-долу.
Обяснение на формулата
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- ---($C$5:$E$7=G5): Този сегмент проверява всяка стойност в диапазона $C$5:$E$7 ако са равни на стойността в клетката G5, и генерира TRUE и FALSE масив по следния начин:
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
След това двойното отрицание ще преобразува TRUE и FALSE в 1 и 0, за да се получи масив като този:
{1,0,0;0,0,0;0,0,0}. - КОЛОНА($C$5:$E$7): Функцията COLUMN връща номерата на колоните за диапазона $C$5:$E$7 в масив като този: 3,4,5 {}.
- ТРАНСПОНИРАНЕ (КОЛОНА($C$5:$E$7)^0) = ТРАНСПОНИРАНЕ (3,4,5 {}^0): След повишаване на степента до 0, всички числа в масива {3,4,5} ще бъдат преобразувани в 1: {1,1,1}. След това функцията TRANSPOSE преобразува масива от колони в масив от редове по следния начин: {1;1;1}.
- MMULT(---($C$5:$E$7=G5),ТРАНСПОНИРАНЕ (КОЛОНА($C$5:$E$7)^0)) = MMULT({1,0,0;0,0,0;0,0,0},{1;1;1}): Функцията MMULT връща матричното произведение на двата масива по следния начин: {1;0;0}.
- MATCH(1,MMULT(---($C$5:$E$7=G5),ТРАНСПОНИРАНЕ (КОЛОНА($C$5:$E$7)^0)), 0) = MATCH(1,{1;0;0}, 0): Типът_съвпадение 0 принуждава функцията MATCH да върне позицията на първото съвпадение на 1 в масива {1;0;0}, кое е 1.
- ИНДЕКС($B$5:$B$7,(МАЧ(1,MMULT(---($C$5:$E$7=G5),ТРАНСПОНИРАНЕ (КОЛОНА($C$5:$E$7)^0)),0))) = ИНДЕКС($B$5:$B$7,1): Функцията INDEX връща 1st стойност в диапазона на класа $B$5:$B$7, кое е A.
За лесно търсене на стойност чрез съвпадение в множество колони можете също да използвате нашата професионална добавка за Excel Kutools за Excel. Вижте инструкциите тук, за да изпълните мисията.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Функцията MMULT на Excel връща матричното произведение на два масива. Резултатът от масива има същия брой редове като array1 и същия брой колони като array2.
Функцията TRANSPOSE на Excel завърта ориентацията на диапазон или масив. Например, може да завърти таблица, която е подредена хоризонтално в редове към вертикално в колони или обратно.
Функцията COLUMN връща номера на колоната, чиято формула се появява, или връща номера на колоната на дадена препратка. Например формула =COLUMN(BD) връща 56.
Свързани формули
Търсене по множество критерии с INDEX и MATCH
Когато работите с голяма база данни в електронна таблица на Excel с няколко колони и надписи на редове, винаги е трудно да намерите нещо, което отговаря на множество критерии. В този случай можете да използвате формула за масив с функциите INDEX и MATCH.
Двупосочно търсене с INDEX и MATCH
За да търсим нещо както в редове, така и в колони в Excel или да кажем да търсим стойност в пресечната точка на конкретния ред и колона, можем да използваме помощта на функциите INDEX и MATCH.
Търсете стойност на най-близкото съвпадение с множество критерии
В някои случаи може да се наложи да търсите най-близката или приблизителна стойност на съвпадение въз основа на повече от един критерий. С комбинацията от функции INDEX, MATCH и IF можете бързо да го направите в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
