Извлечение уникальных элементов из диапазона
Способ 1. Штатная функция в Excel 2007
Начиная с 2007-й версии функция удаления дубликатов является стандартной - найти ее можно на вкладке Данные - Удаление дубликатов (Data - Remove Duplicates):
В открывшемся окне нужно с помощью флажков задать те столбцы, по которым необходимо обеспечивать уникальность. Т.е. если включить все флажки, то будут удалены только полностью совпадающие строки. Если включить только флажок заказчик, то останется только по одной строке для каждого заказчика и т.д.
Способ 2. Расширенный фильтр
Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data).
Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:
Выбираем в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter). Получаем окно:
В нем:
- Выделяем наш список компаний в Исходный диапазон (List Range).
- Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
- Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.
Получите список без дубликатов:
Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE):
Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.
Способ 3. Выборка уникальных записей формулой
Чуть более сложный способ, чем первые два, но зато - динамический, т.е. с автоматическим пересчетом, т.е. если список редактируется или в него дописываются еще элементы, то они автоматически проверяются на уникальность и отбираются. В предыдущих способах при изменении исходного списка нужно будет заново запускать Расширенный фильтр или жать на кнопку Удаление дубликатов.
Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:
Первая задача - пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"")
В английской версии это будет:
=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;"")
Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз - дает ему последовательно возрастающий номер.
Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы - Диспетчер имен (Formulas - Name manager) или в старых версиях - через меню Вставка - Имя - Присвоить (Insert - Name - Define):
- диапазону номеров (A1:A100) - имя NameCount
- всему списку с номерами (A1:B100) - имя NameList
Теперь осталось выбрать из списка NameList все элементы имеющие номер - это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:
=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2))
или в английской версии Excel:
=IF(MAX(NameCount)
Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:
Ссылки по теме
- Выделение дубликатов по одному или нескольким столбцам в списке цветом
- Запрет ввода повторяющихся значений
- Извлечение уникальных значений при помощи надстройки PLEX
Третий способ очень хорош, но я столкнулся с проблемой: если диапазон содержит пустые ячейки и одновременно нули, то после первого нуля формула присваивает номера пустым строкам до тех пор, пока не встретится второй нуль
Вот так можно от этого избавиться
В ячейку А2 пишем эту формулу и протягиваем до конца массива
=ИНДЕКС($B$2:$B$17;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$17)-СТРОКА($B$2)+1)/(ПОИСКПОЗ($B$2:$B$17;$B$2:$B$17;0)=СТРОКА($B$2:$B$17)-СТРОКА($B$2)+1);ЧСТРОК($B$2:$B2)))
Друзья, можно сюда добавить сортировку по убыванию количества совпадений? Понятно, что это можно использовать костыли.. А в рамках одной формулы это возможно? пробовал прикрутить Макс + счет, но во всех значениях массива оставалось первое максимальное значение.
На выходе у меня появился документ со списком уникальных значений с автоматической сортировкой по количеству повторений.
Петров 15
Иванов 12
Жуков 7
...
перевела ее, IF(MAX(NameCount)<ROW(1:1);"";VLOOKUP(ROW(1:1);NameList;2))
но проблема почему-то осталась, пишет ошибка
В ячейку вписываются Фамилии/номера клиентов. Они могут дублироваться и в строке и в столбце.
Как вывести столбцом весь перечень уникальных значений? Желательно "на лету" без макрасов.
Спасибо!
Updt Получилось, но логику и принцип работы так и не уловил.
очень кстати полезная штука будет, особенно если можно без всяких макросов и ВБА
Подскажите по способу №3 - как сделать, чтобы номер проставлялся не в первом повторяющемся значении, а в последнем?
К примеру на цифре 2:
Третий способ отлично работает, но есть проблема, не уверен, может только у меня так.
Суть проблемы, то что формула не извлекает по настоящему "уникальные данные". Прописав пример, у меня получилось, что выборка идёт по позициям которые встречаются минимум дважды начиная со второго аргумента. Загвоздка в том, что у меня в списках есть как и повторяющиеся аргументы так и "уникальные".
Собственно вопрос:
Каким образом можно модифицировать формулу что бы она извлекала все значения?
Прошу подсказать можно ли удалить не все дубликаты, а положим первые 4?
Спасибо!
Подскажите, пожалуйста, в одном из приведенных примеров Вы склеили ячейки в одну. И потом отфильтровали полученный список. А можно ли полученный список обратно "расклеить", и если можно то как?
Заранее спасибо!
Николай, спасибо за статью.
Не раз встречал способы решения данного вопроса. Есть даже скачанные примеры. Но ваш "подход для чайников" как всегда особняком.
Я немного упростил его, для тех кто не умеет именовать диапазоны, потому что в способе 3 это не совсем подробно описано именование диапазонов. А там ещё добавлены формулы под каждый:
NameCount, диапазон =СМЕЩ('Способ 3'!$B$1;0;0;СЧЁТЗ('Способ 3'!$B:$B);1)
NameList, диапазон =СМЕЩ('Способ 3'!$B$1;0;0;СЧЁТЗ('Способ 3'!$B:$B);2)
UniqueNames, диапазон =СМЕЩ('Способ 3'!$E$2;0;0;МАКС('Способ 3'!$B:$B);1)
Не знаю как прикрепить пример в комментарии.
нумерация списка - столбец А, список с повторяющимися значениями столбец B, уникальный список в столбце F начиная с 1й строки
В моем "способе 4" для нумерации списка используется формула из вашего способа 3
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"")
В столбец F
Для первого значения уникального списка вставляется формула:
=ВПР(СТРОКА(1:1);A:B;2)
начиная со второй строки уникального списка:
=ЕСЛИ(ВПР(СТРОКА(2:2);A:B;2)=F1;"";ЕСЛИ(F1="";"";ВПР(СТРОКА(2:2);A:B;2)))
Единственный минус - то что используется три формулы вместо двух. Но зато не надо именовать столбцы, и добавлять к их именованию формулы, не описанные в способе №3
=ЕСЛИ(СЧЁТЕСЛИ(W$1:W5;W5)=1;МАКС(V$1:V5)+1;"") или он только для столбцов A и B работает?
Пытался оптимизировать одну из формул тут в комментариях:
Получил вот это:
=ИНДЕКС(список;НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(список;список;0);"")= СТРОКА(ДВССЫЛ("$1:$"&СУММ(СЧЁТЗ(список);СЧИТАТЬПУСТОТЫ(список))));ПОИСКПОЗ(список;список;0);""); СТРОКА(ДВССЫЛ("$1:$"&СЧЁТ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(список;список;0);"")= СТРОКА(ДВССЫЛ("$1:$"&СУММ(СЧЁТЗ(список);СЧИТАТЬПУСТОТЫ(список))));ПОИСКПОЗ(список;список;0);"")))) );1)Формула даже без преобразования в массив выдаёт в современном экселе список уникальных значений в любом месте.
Но у меня проблеме следующего характера: я эту формулу делаю именованной "Ctrl+F3" и хочу сделать выпадающий список по ней, но там только одно значение. Почему Excel на листе сам заполняет несколько строк уникальными значениями, т.е. список в уме он держит, но не может превратить это в раскрывающийся список.
Очень полезно.
Мне помогла такая конструкция формулы, доделал под себя
=ЕСЛИОШИБКА(ИНДЕКС('1'!$H$2:$H$999;ПОИСКПОЗ(0;СЧЁТЕСЛИМН($B$16:B18;'1'!$H$2:$H$999);0));"";)
Исходные данные на листе "1" в диапазоне H$2:$H$999.
Проверку на уникальность ведем в этом диапазоне $B$16:B18
$B$16 - первая строка выдачи выборки (на одну выше, чем выдача результатов )
***Не могу допилить формулу, чтоб для выдачи соответствовало еще одно условие, например, ячейка A1 содержала текст "qwerty"
То есть, если ячейка A1 содержит "qwerty", тогда проводить проверку уникальности, если ячейка A1 не содержит "qwerty", то перейти с проверки следующей строки на уникальность, а значение этой ячейки не учитывать.
Скажите, пожалуйста, как в 3-й вариант добавить отбор по условию?
Есть лва листа:
1 - куда выводится уникальный список и содержится условие в ячейке, которое является динамическим;
2 - база данных, откуда откуда формируется уникальный список и колонка с условием.