Формула на 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)),"")

Интересни статии...