Создание списка уникальных значений в Excel с помощью формул массивов

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

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

99-1-список сотрудников

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

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

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

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

99-2-Уникальные значения Excel

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

  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. ЕСЛИОШИБКА – скрывает #Н/А, когда расчеты достигают конца списка.

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

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


4 комментария

  1. Pingback: 11 Ноября 2015 - Excel-дайджест - Excel для экономиста

  2. У вас описание так сформулировано, что я подумал, что вы ищите слова, которые ни разу не повторились в оригинальном перечне. А оказалось, вы составляете список значений избавленный от дублей. Т.е. список без повторов. Я уже порывался написать, что формула не работает, пока не понял, про что эта статья.

  3. Двухуровневый выпадающий список в Excel . Создание двухуровнего выпадающего списка — задача довольно простая если знать, как к Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *