INDEX и MATCH с множество масиви
Да приемем, че имате няколко таблици с едни и същи надписи, както е показано по-долу, търсенето на стойности, които отговарят на зададените критерии от тези таблици, може да е трудна работа за вас. В този урок ще говорим за това как да търсите стойност в множество масиви, диапазони или групи чрез съвпадение на конкретни критерии с INDEX, MATCH намлява ИЗБИРАМ функции.
Как да търсите стойност в множество масиви?
За да познаете лидери на различни групи, които принадлежат към различни отдели, можете първо да използвате функцията CHOOSE, за да насочите таблицата, от която да върнете името на лидера. След това функцията MATCH ще открие позицията на лидера в таблицата, към която той/тя принадлежи. И накрая, функцията INDEX ще извлече лидера въз основа на информацията за позицията плюс конкретната колона, където са изброени имената на лидерите.
Общ синтаксис
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- номер_масив: Числото CHOOSE се използва за обозначаване на масив от списъка масив1, масив2,... за връщане на резултата от.
- масив1, масив2,…: Масивите, от които да се върне резултатът. Тук се отнася за трите таблици.
- търсена_стойност: Стойността, използвана от формулата за комбинация за намиране на позицията на съответния лидер. Тук се отнася за дадената група.
- търсене_масив: Диапазонът от клетки, където търсена_стойност е в списъка. Тук се отнася за обхвата на групата. Забележка: Можете да използвате диапазона на групата от всеки отдел, тъй като всички те са еднакви и ние просто трябва да получим номера на позицията.
- колона_номер: Колоната, която посочвате, от която искате да извлечете данни.
За да познаете лидер на група D, която принадлежи към отдел А, моля, копирайте или въведете формулата по-долу в клетка G5 и натиснете Въведете за да получите резултата:
=ИНДЕКС(ИЗБЕРЕ(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),СЪВПАДА(F5,$B$5:$B$8,0),2)
√ Забележка: Знаците за долар ($) по-горе показват абсолютни препратки, което означава, че диапазоните на името и класа във формулата няма да се променят, когато преместите или копирате формулата в други клетки. След като въведете формулата, плъзнете манипулатора за попълване надолу, за да приложите формулата към клетките по-долу, и след това променете номер_масив съответно.
Обяснение на формулата
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Функцията CHOOSE връща 1st масив от трите масива, изброени във формулата. Така че ще се върне $B$5:$C$8, т.е диапазон от данни на отдел А.
- MATCH(F5;$B$5:$B$8,0;XNUMX): Типът_съвпадение 0 принуждава функцията MATCH да върне позицията на първото съвпадение на Група Г, стойността в клетката F5, в масива $B$5:$B$8, кое е 4.
- ИНДЕКС(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5;$B$5:$B$8,0;XNUMX),2) = ИНДЕКС($B$5:$C$8,4,2): Функцията INDEX извлича стойността в пресечната точка на 4ред и 2рата колона от диапазона $B$5:$C$8, кое е Емили.
За да избегнете промяната номер_масив във формулата всеки път, когато я копирате, можете да използвате помощната колона, колоната D. Формулата ще бъде така:
=ИНДЕКС(ИЗБЕРЕ(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),СЪВПАДА(F5,$B$5:$B$8,0),2)
√ Забележка: Числата 1, 2, 3 в помощната колона посочете масив1, масив2, масив3 във функцията CHOOSE.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Функцията CHOOSE връща стойност от списъка със стойностен аргумент въз основа на дадения индексен номер. Например CHOOSE(3,”Apple”,”Peach”,”Orange”) връща оранжево, номерът на индекса е 3, а оранжевото е третата стойност след номера на индекса във функцията.
Свързани формули
Търсете стойности от друг работен лист или работна книга
Ако знаете как да използвате функцията VLOOKUP за търсене на стойности в работен лист, стойностите на vlookup от друг работен лист или работна книга няма да представляват проблем за вас.
Vlookup с динамично име на лист
В много случаи може да се наложи да съберете данни в множество работни листове за обобщение. С комбинацията от функцията VLOOKUP и функцията INDIRECT можете да създадете формула за търсене на конкретни стойности в работни листове с име на динамичен лист.
Търсене по множество критерии с INDEX и MATCH
Когато работите с голяма база данни в електронна таблица на Excel с няколко колони и надписи на редове, винаги е трудно да намерите нещо, което отговаря на множество критерии. В този случай можете да използвате формула за масив с функциите INDEX и MATCH.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
