Формула Excel: Найпоширеніший текст із критеріями -

Зміст

Загальна формула

=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))

Резюме

Щоб знайти найбільш часто зустрічається текст у діапазоні на основі критеріїв, які ви надаєте, ви можете використовувати формулу масиву на основі декількох функцій Excel INDEX, MATCH, MODE та IF. У наведеному прикладі формула в G5 має вигляд:

=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))

де "постачальник" - іменований діапазон C5: C15, а "клієнт" - іменований діапазон B5: B15.

Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.

Пояснення

Працюючи зсередини, ми використовуємо функцію MATCH, щоб зіставити текстовий діапазон із самим собою, надаючи MATCH однаковий діапазон для значення підстановки та масиву підстановки, з нулем для типу збігу:

MATCH(supplier,supplier,0)

Оскільки значення пошуку є масивом з 10 значеннями, MATCH повертає масив з 10 результатів:

(1;1;3;3;5;1;7;3;1;5;5)

Кожен елемент у цьому масиві являє собою першу позицію, на якій ім’я постачальника відображається в даних. Цей масив подається у функцію IF, яка використовується для фільтрування результатів лише для клієнта A:

IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))

IF повертає відфільтрований масив до функції MODE:

(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)

Зверніть увагу, що в масиві залишаються лише позиції, пов’язані з клієнтом А. MODE ігнорує значення FALSE і повертає найбільш часто зустрічається число функції INDEX як номер рядка: