Excel подсчет уникальных значений в столбце

Вытягивание уникальных значений из диапазона была и остается одной из старейших тем обсуждений в среде Excel. Хотя в программе реализовано несколько эффективных возможностей идентификации повторяющихся значений, а статей на эту тему написано еще больше, сегодня мы поговорим о возможности создания в Excel динамического списка уникальных значений с помощью формул массивов.

Предположим, у вас есть файл со списком сотрудников, находящийся в колонке А, и вам необходимо извлечь из этого списка не повторяющиеся значения и поместить в колонку В. В таком случае нам бы помог фильтр уникальных значений, но как назло первоначальный список постоянно меняется, и каждый раз фильтровать по уникальным значениям — дело неблагородное. Выходом из ситуации будет создание формулы массива, который пересчитывается каждый раз, когда первоначальный список претерпевает изменения.

Формула массива будет выглядеть следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС(Список;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(B$1:B1; Список));СЧЁТЕСЛИ(Список;»<«& Список);0));»»)

В данном случае Список — это именованный диапазон ячеек А2:А100.

Скопируйте формулу в ячейку B2, нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы превратить формулу в формулу массивов, и протяните ее до ячейки B20.

Давайте разберемся, как работает формула:

1. СЧЁТЕСЛИ(Список;»<«& Список) — возвращает массив (1) с количеством сотрудников, которые меньше сотрудника, находящегося в текущей ячейке. Т.е. массив будет иметь вид {5; 20; 19; 21; 12 …}, например, Дима Билан имеет значение 5, это значит, что перед ним имеется еще 4 сотрудника, которые расположены раньше по алфавиту, Филипп Киркоров = 20, значит перед ним имеется еще 19 сотрудников расположенных ближе к букве А. Таким образом, мы получаем что-то наподобие отсортированного листа.
2. СЧЁТЕСЛИ(B$1:B…; Список) — возвращает массив (2) с единицами для тех позиций, которые уже имеются на отсортированном листе. Например, в ячейке B2 будет массив {0;0;0;0;0…}, а в ячейке B8 — {0;1;0;0;0; … ;0;1} — так как Валерий Леонтьев встречается два раза.
3. СУММ — суммирует значения, которые уже были представлены на отсортированном списке.
4. ПОИСКПОЗ — ищет сумму встречающихся значений массива (2) в массиве (1)
5. ЕСЛИОШИБКА — скрывает #Н/А, когда расчеты достигают конца списка.

Долго думал, как можно было бы проще объяснить работу формулы, но ничего путного в голову так и не пришло. Чтобы лучше понять, как работает формула, можно разобрать ее по частям и понять, как отрабатывает и какой результат возвращает та или иная часть формулы массива.

Скачать файл с примером можно по ссылке.

Вам также могут быть интересны следующие статьи