Формула Excel: Знайдіть найчастіший текст із критеріями

У деяких випадках вам може знадобитися знайти текст, який найчастіше з’являється на основі критерію в Excel. Цей підручник представляє формулу масиву для обробки цієї роботи та дає пояснення щодо аргументів цієї формули.

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

=INDEX(rng_1,MODE(IF(rng_2=criteria,MATCH(rng_1,rng_1,0))))

Аргументи

Rng_1: the range of cells that you want to find the most frequent text.

Rng_2: the range of cells that contain the criteria you want to use.

Criteria: the condition you want to find text based on.

Повернене значення

Ця формула повертає найбільш частий текст із певним критерієм.

Як працює ця формула

Приклад: Існує ряд клітинок із переліком продуктів, інструментів та користувачів, тепер ви хочете знайти найбільш часто використовуваний інструмент кожного продукту. Будь ласка, використовуйте формулу нижче в комірці G3:

=INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0))))

прес Shift + Ctrl + Enter клавіші разом, щоб отримати правильний результат. Потім перетягніть маркер заповнення вниз, щоб застосувати цю формулу.

Пояснення

MATCH($C$3:$C$12,$C$3:$C$12,0): MATCH функція повертає розташування значення_значення у рядку або стовпці. Тут формула повертає результат масиву {1;2;3;4;2;1;7;8;9;7}, який знаходить позицію кожного тексту в діапазоні $ C $ 3: $ C $ 12.

IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)): IF функція використовується для встановлення умови. Тут ця формула розглядається як IF($B$3:$B$12=”KTE”,{1;2;3;4;2;1;7;8;9;7}) , а результат масиву повертається ={1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}.

MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0))): Функція MODE знаходить найчастіший текст у діапазоні. Тут ця формула знайде найчастіше число в результаті масиву функції IF, яке можна розглянути як MODE({1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}) і повертає 1.

INDEX function: ІНДЕКС функція повертає значення в таблиці або масиві на основі заданого розташування. Ось формула. INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) буде зменшено до INDEX($C$3:$C$12,1).

Зауваження

Якщо найчастіше є два або більше тексти, формула повертає значення, яке з’являється спочатку.

Зразок файлу

Клацніть, щоб завантажити зразок файлу

---

Відносні формули

• Перевірте, чи містить комірка певний текст
  Щоб перевірити, чи містить комірка деякі тексти в діапазоні A, але не містить тексти в діапазоні B, можна скористатися формулою масиву, яка поєднує в програмі Excel функцію COUNT, SEARCH та AND.
• Перевірте, чи містить комірка одне з кількох значень, але виключіть інші значення
  Цей підручник надасть формулу для швидкого вирішення завдання, яка перевіряє, чи містить комірка одне з речей, але виключаючи інші значення в Excel, та пояснює аргументи формули.
• Перевірте, чи містить комірка одну з речей
  Припустимо, що в Excel у стовпці E є список значень, ви хочете перевірити, чи містять клітинки в стовпці B всі значення в стовпці E, і повернути TRUE або FALSE.
• Перевірте, чи містить комірка номер
  Іноді вам може знадобитися перевірити, чи містить комірка числові символи. Цей підручник містить формулу, яка поверне TRUE, якщо комірка містить число, FALSE, якщо комірка не містить число.