Формула Excel: список наиболее часто встречающихся чисел -

Общая формула

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

Резюме

Чтобы перечислить наиболее часто встречающиеся числа в столбце (т. Е. Наиболее часто встречающиеся, вторые по частоте, третьи по частоте и т. Д.), Вы можете составить формулу массива, основанную на четырех функциях Excel: ЕСЛИ, РЕЖИМ, ПОИСКПОЗ и ЕЧИСЛО. В показанном примере формула в D5:

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

где «данные» - именованный диапазон B5: B16. Затем формула копируется в строки ниже D5 для вывода желаемого списка наиболее часто используемых чисел.

Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.

Объяснение

Ядром этой формулы является функция MODE, которая возвращает наиболее часто встречающееся число в диапазоне или массиве. Остальная часть формулы просто создает отфильтрованный массив, который РЕЖИМ будет использовать в каждой строке. Расширяющийся диапазон $ D $ 4: D4 исключает числа, уже выведенные в $ D $ 4: D4.

Работаем изнутри:

  1. Функция ПОИСКПОЗ используется сначала для проверки всех чисел в названном диапазоне "данные" на соответствие существующим числам в расширяющемся диапазоне $ D $ 4: D4
  2. ISNUMBER преобразует совпадающие значения в ИСТИНА, а несовпадающие значения в ЛОЖЬ.
  3. 1-ЧИСЛО переворачивает массив, а математическая операция выводит единицы и нули
  4. IF использует вывод массива из # 3 выше для фильтрации исходного списка значений, исключая числа уже в $ D $ 4: D4
  5. Функция РЕЖИМ возвращает наиболее частое число в выводе массива на шаге 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

Обработка ошибок

Функция РЕЖИМ вернет ошибку # Н / Д при отсутствии режима. При копировании формулы в последующие строки вы, вероятно, столкнетесь с ошибкой # N / A. Чтобы перехватить эту ошибку и вернуть вместо нее пустую строку (""), вы можете использовать ЕСЛИОШИБКА следующим образом:

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

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