Формула на Excel: Вземете n-то съвпадение -

Съдържание

Обща формула

=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)

Обобщение

За да получите позицията на n-то съвпадение (например 2-ра съвпадаща стойност, 3-та съвпадаща стойност и т.н.), можете да използвате формула, базирана на функцията SMALL. В показания пример формулата в G5 е:

=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)

Тази формула връща позицията на втората поява на "червено" в списъка.

Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.

Обяснение

Тази формула използва посочения диапазон "списък", който е диапазонът B5: B11.

Ядрото на тази формула е функцията SMALL, която просто връща n-тата най-малка стойност в списък със стойности, които съответстват на номера на редове. Номерата на редовете са „филтрирани“ от израза IF, който прилага логиката за съвпадение. Работейки отвътре навън, IF сравнява всички стойности в посочения диапазон "списък" със стойността в B5, което създава масив като този:

(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)

"Стойността, ако е вярно" е набор от относителни номера на редове, създадени от този код:

ROW(list)-MIN(ROW(list))+1

Резултатът е масив като този:

(1;2;3;4;5;6;7)

Вижте тази страница за пълно обяснение.

С логически тест, който връща масив от резултати, функцията IF действа като филтър - оцеляват само номера на редове, които съответстват на съвпадение, а останалите връщат FALSE. Резултатът, върнат от IF, изглежда така:

(1;FALSE;FALSE;FALSE;5;FALSE;7)

Числата 1, 5 и 7 съответстват на местоположението на "червено" в списъка.

И накрая, SMALL връща n-тия най-малък елемент в този списък, игнорирайки FALSE стойности. В примера F5 съдържа 2, така че SMALL връща втората най-малка стойност: 5.