Вытягивание уникальных значений из диапазона была и остается одной из старейших тем обсуждений в среде Excel. Хотя в программе реализовано несколько эффективных возможностей идентификации повторяющихся значений, а статей на эту тему написано еще больше, сегодня мы поговорим о возможности создания в Excel динамического списка уникальных значений с помощью формул массивов.
Предположим, у вас есть файл со списком сотрудников, находящийся в колонке А, и вам необходимо извлечь из этого списка не повторяющиеся значения и поместить в колонку В. В таком случае нам бы помог фильтр уникальных значений, но как назло первоначальный список постоянно меняется, и каждый раз фильтровать по уникальным значениям – дело неблагородное. Выходом из ситуации будет создание формулы массива, который пересчитывается каждый раз, когда первоначальный список претерпевает изменения.
Формула массива будет выглядеть следующим образом:
=ЕСЛИОШИБКА(ИНДЕКС(Список;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(B$1:B1; Список));СЧЁТЕСЛИ(Список;»<«& Список);0));»»)
В данном случае Список – это именованный диапазон ячеек А2:А100.
Скопируйте формулу в ячейку B2, нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы превратить формулу в формулу массивов, и протяните ее до ячейки B20.
Давайте разберемся, как работает формула:
- СЧЁТЕСЛИ(Список;»<«& Список) – возвращает массив (1) с количеством сотрудников, которые меньше сотрудника, находящегося в текущей ячейке. Т.е. массив будет иметь вид {5; 20; 19; 21; 12 …}, например, Дима Билан имеет значение 5, это значит, что перед ним имеется еще 4 сотрудника, которые расположены раньше по алфавиту, Филипп Киркоров = 20, значит перед ним имеется еще 19 сотрудников расположенных ближе к букве А. Таким образом, мы получаем что-то наподобие отсортированного листа.
- СЧЁТЕСЛИ(B$1:B…; Список) – возвращает массив (2) с единицами для тех позиций, которые уже имеются на отсортированном листе. Например, в ячейке B2 будет массив {0;0;0;0;0…}, а в ячейке B8 — {0;1;0;0;0; … ;0;1} – так как Валерий Леонтьев встречается два раза.
- СУММ – суммирует значения, которые уже были представлены на отсортированном списке.
- ПОИСКПОЗ – ищет сумму встречающихся значений массива (2) в массиве (1)
- ЕСЛИОШИБКА – скрывает #Н/А, когда расчеты достигают конца списка.
Долго думал, как можно было бы проще объяснить работу формулы, но ничего путного в голову так и не пришло. Чтобы лучше понять, как работает формула, можно разобрать ее по частям и понять, как отрабатывает и какой результат возвращает та или иная часть формулы массива.
Скачать файл с примером можно по ссылке.
Pingback: 11 Ноября 2015 - Excel-дайджест - Excel для экономиста
У вас описание так сформулировано, что я подумал, что вы ищите слова, которые ни разу не повторились в оригинальном перечне. А оказалось, вы составляете список значений избавленный от дублей. Т.е. список без повторов. Я уже порывался написать, что формула не работает, пока не понял, про что эта статья.
Двухуровневый выпадающий список в Excel . Создание двухуровнего выпадающего списка — задача довольно простая если знать, как к Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы.
Добрый день. Очень нужная формула но с цифрами не работает.
Спасибо большое за полезную формулу!
Подскажите, пожалуйста, как можно доработать данную формулу так, чтобы она считала слова в разных регистрах разными, а не дублями одного и того же?
Формула не рабочая
Все работает, кавычки замените!
Подскажите, что надо изменить что бы работала для горизонтальных строк данных, вместо вертикальных
А если нужно создать список уникальных ячеек с диапазона, например B2:H15 ?
формула массива — это фантастика. Большое спасибо!
Я уже очень долго ищу решение для поставленной задачи и только у вас наконец удалось найти подходящую формулу.
Правда я всё равно не понимаю как она работает. При вычислении кусков формулы через F9 она выдаёт ошибки.
Кроме того, мне на листе приходится использовать несколько таких формул. После того как я заполнил несколько столбцов оказалось, что значения в этих ячейках по непонятным причинам пересчитываются, то пропадают, то меняются местами. И видно, что эти вычисления занимают существенное время.
Может быть эту формулу можно упростить? И наверно как-то привязать к первому значению.
извините, но формула не рабочая: вручную вначале набирала, потом в соседний столбец копировала ее, в конце концов скачала заявленный файл — и ничего!!!!!
Формула не работает у меня. Нули во всех ячейках. Кавычки заменил.
Согласен — 0 везде, при этом в файле демонстрационном как-то считает
Работает скрипт. Обязательно должен быть заголовок столбца иначе не работает
Не работает.
Перерыла много статей на эту тему, но эта статья лучшая! Формула работает!!! Да еще по алфавиту сортирует!!! (У кого не работает — сделайте пару действий:
1. СЧЁТЕСЛИ(B$1:B1; Список) — уберите лишний пробел перед именем диапазона Список;
2. Замените кавычки » на «;
ну и по мелочи… имя диапазона и ссылку на заголовок того столбца, где будет результат.
Я Нуб, поэтому так подробно)) А за помощь — ОГРОМНОЕ спасибо!!!
0 выдает, если в списке есть незаполненные строки. Скажите, пожалуйста, как можно адаптировать формулу, чтобы она считала массив и с пустыми строками?
Спасибо, формула супер, очень помогла
Кто-нибудь может скинуть Excel файл с рабочей этой формулой??
Всё работает. Нужно заменить кавычки « на «, убрать лишний пробел перед именем диапазона Список, выделить диапазон А2:А100 и задать ему имя Список.
Попробовал обработать массив на 10 000 строк и компьютер виснет на пару минут пока обработает массив. При этом если просто удалять дубликаты и выстроить от большего к меньшему в ручном режиме занимает пару секунд и ничего не зависает. В чём может быть дело?
Спасибо!