Формула Excel: Перерахуйте найчастіше зустрічаються числа -

Зміст

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

(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))

Резюме

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

(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))

де "дані" - названий діапазон B5: B16. Потім формула копіюється в рядки нижче D5, щоб вивести бажаний список найбільш частих чисел.

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

Пояснення

Ядром цієї формули є функція MODE, яка повертає найбільш часто зустрічається число в діапазоні або масиві. Решта формули просто створює відфільтрований масив для MODE для використання в кожному рядку. Діапазон, що розширюється, $ D $ 4: D4 працює, щоб виключити числа, які вже виведені в $ D $ 4: D4.

Робота зсередини:

  1. Функція MATCH використовується спочатку перевірити всі числа в названому діапазоні "дані" щодо існуючих чисел у діапазоні, що розширюється $ D $ 4: D4
  2. ISNUMBER перетворює зіставлені значення в TRUE, а невідповідні значення в FALSE
  3. 1-NUMBER змінює масив, а математична операція виводить одиниці та нулі
  4. IF використовує вихідний масив №3 вище для фільтрації вихідного списку значень, виключаючи числа, що вже є в $ D $ 4: D4
  5. Функція MODE повертає найчастіше число у виведенні масиву на кроці # 4

У комірці D5 фільтрування не відбувається, і вихід кожного кроку вище виглядає так:

(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93

У комірці D6, з 93 вже в D5, вихід виглядає так:

(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95

Помилки обробки

Функція MODE повертає помилку # N / A, коли немає режиму. Під час копіювання формули в наступні рядки, швидше за все, ви потрапите на помилку # N / A. Щоб затримати цю помилку і замість цього повернути порожній рядок (""), ви можете використовувати IFERROR, як це:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")

Цікаві статті...