Формула 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 повертає 2-е найменше значення: 5.