Извлечете n-то съвпадение с INDEX
За да намерите n-то съвпадение на стойност от диапазон и да извлечете съответните данни, можете да използвате формула, базирана на INDEX, РЕД, МАЛЪК и IF функции.
Как да извлека информация, свързана с n-то съвпадение на стойност с INDEX?
За да получите информация за 3-тия мач на Юсейн Болт в таблицата, както е показано по-горе, можете да използвате функциите ROW и IF заедно, за да получите масив, който се състои от номерата на редовете на всички стойности на Юсейн Болт. След това използвайте функцията SAMLL, за да извлечете номера на реда на n-тата съответстваща стойност, която сте посочили. Накрая подайте резултата на функцията INDEX, за да получите съответната информация на същия ред.
Общ синтаксис
=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))
√ Забележка: Това е формула за масив, която изисква да въведете с Ctrl + Превключване + Въведете.
- върнат_обхват: Диапазонът, от който да се върне информацията, съответстваща на n-то съвпадение. Тук се отнася за годината или времевия диапазон.
- търсене_масив: Диапазонът, от който да се търси позицията на реда на n-то съвпадение. Тук се отнася за обхвата на победителя.
- търсена_стойност: Стойността, която посочихте за търсене на нейното n-то съвпадение. Тук се има предвид Юсейн Болт.
- n: Посочете n-то съвпадение. За да намерите първото съвпадение на стойност, задайте n като 1; за намиране на 2-ро съвпадение, набор n като 2 г.
За да получите информация за 3-тия мач на Юсейн Болт, моля, копирайте или въведете формулите по-долу в клетките G8 и G9 и натиснете Ctrl + Превключване + Въведете за да получите резултатите:
Година (клетка G8)
=ИНДЕКС(B6: B12,МАЛКИ(АКО(C6:C12=G5,РЕД(C6:C12-РЕД(ИНДЕКС(C6:C12,1,1))+1),G6))
Време (клетка G9)
=ИНДЕКС(D6: D12,МАЛКИ(АКО(C6:C12=G5,РЕД(C6:C12-РЕД(ИНДЕКС(C6:C12,1,1))+1),G6))
Обяснение на формулата
Тук използваме формулата по-долу като пример:
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))
- РЕД(C6:C12): Функцията ROW връща номерата на редовете на всяка клетка в диапазона C6:C12 в масив като този: {6;7;8;9;10;11;12}.
- РЕД(ИНДЕКС(C6:C12,1,1;XNUMX;XNUMX)): Функциите INDEX и ROW връщат номера на първия ред от диапазона за търсене C6:C12 в този работен лист на Excel, който е 6.
- IF(C6:C12=G5,РЕД(C6:C12)-РЕД(ИНДЕКС(C6:C12,1,1;XNUMX;XNUMX))+ 1) = IF(C6:C12=G5,{6;7;8;9;10;11;12}-6+ 1): Функцията IF проверява дали всяка стойност в диапазона C6:C12 съответства на стойността в клетката G5, Юсейн Болт. Ако да, функцията ще върне резултата от „съответния му номер на ред-6+1“; В противен случай върнете FALSE. Така че резултатите ще бъдат в масив като този: {FALSE;2;3;4;FALSE;FALSE;FALSE}.
- МАЛЪК(IF(C6:C12=G5,РЕД(C6:C12)-РЕД(ИНДЕКС(C6:C12,1,1;XNUMX;XNUMX))+ 1),G6) = МАЛЪК({FALSE;2;3;4;FALSE;FALSE;FALSE},G6): Фрагментът връща 3rd (стойност в клетка G6) най-малката стойност от масива, която е 4.
- ИНДЕКС(B6:B12,МАЛЪК(IF(C6:C12=G5,РЕД(C6:C12)-РЕД(ИНДЕКС(C6:C12,1,1;XNUMX;XNUMX))+ 1),G6)) = ИНДЕКС(B6:B12,4) Функцията INDEX връща 4та стойност в годишния диапазон B6: B12, кое е Пекин 2008.
Свързани функции
Функцията ИНДЕКС на Excel връща показаната стойност въз основа на дадена позиция от диапазон или масив.
Функцията IF е една от най-простите и полезни функции в работната книга на Excel. Той извършва прост логически тест, който в зависимост от резултата от сравнението, и връща една стойност, ако резултатът е TRUE, или друга стойност, ако резултатът е FALSE.
Функцията Excel Row връща номера на реда на справка.
Свързани формули
Извлечете n-то съвпадение с VLOOKUP
Да предположим, че имате списък със стойности и съответната им информация, за да извлечете свързаната информация за n-то съвпадение на стойност, можете да използвате формула, базирана на функцията VLOOKUP.
Извлечете информация, свързана с най-ниските n стойности
За да извлечете данни, които съответстват на най-малката, 2-рата най-малка или n-тата най-малка стойност в списък, таблица или ред в Excel, можете да използвате формула ИНДЕКС и МАЧ заедно с функцията SMALL.
Получете информация, съответстваща на максималната стойност
За да извлечете данни, които съответстват на максималната стойност в списък, таблица или ред в Excel, можете да използвате формула INDEX и MATCH заедно с функцията MAX.
Получете информация, съответстваща на минималната стойност
За да извлечете данни, които съответстват на минималната стойност в таблица, можете да използвате формула INDEX и MATCH заедно с функцията MIN.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
