Извлечение уникальных элементов из диапазона
Способ 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
=ИНДЕКС($B$2:$B$25;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0)=СТРОКА($1:$24);ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0);"";);СТРОКА()-1);1)
=ИНДЕКС($B$2:$B$25;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0);"";)=СТРОКА($1:$24);ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0);"";);СТРОКА()-1);1)
в ячейку N2, например:
формула массива
=ИНДЕКС($B$2:$B$25;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0);"")=СТРОКА($1:$24);ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0);"");СТРОКА()-1);1)
Напишите пожалуйста вашу формулу так чтобы ее было полностью видно.
формула массива (так как формула длинная и не влазит в ширину страницы- два пробела поставил, нужно будет убрать)
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$25;НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($B$2:$B$25)=ЛОЖЬ; ЕСЛИ(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0)=СТРОКА($1:$24);ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;
0);"");"");СТРОКА()-1);1);"")
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$25;
НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0)=СТРОКА()-1;
СТРОКА()-1;"");"");СТРОКА()-1));"")
Решение сложных задач посредством формул достаточно трудно воспринимается со стороны, так как формула может содержать до 5 и более уровней вложения. Сам не раз, пытаясь разобраться в чьей-то формуле тратил немало времени на ее разбор, но оно того стоит. Лучше разобраться в алгоритме работы формулы, нежели просто вставить готовую в свой документ, тогда работа формулы для изучающего ее упрощается.
P.S. При вставке формулы нужно удалить лишние пробелы.
Ключ коллекции должен быть уникален, иначе возникает ошибка, которую можно обработать. Получается что-то типа
Моя проблемма заключается в том, что когда я извлекаю дубликаты, ячейки удаляются, и идет несоответствие со слобцом который рядом.
У меня получился только как - сначала преобразую таблицу в диапазон, вставляю формулу, потом снова преобразую в таблицу
что я делаю не так в третьем примере ?
Ругается при вводе описанной выше формулы
=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;"")
Либо проблема глубже, но без файла - не понять.
Вопрос такой: есть столбец в 4 тысячи строк, примерно. Количество повторений в нем достаточно велико.
Так вот, как удалить дубликаты так, чтобы сам диапазон не смещался? То есть, чтобы остались пустые ячейки на месте дубликатов?
Подскажите пожалуйста решение данной проблемы:
есть файл с двумя закладками "Export Products Sheet (2)"-основные данные с перечнем ассортимента продукции (более 15 000) и "Export Groups Sheet" с основными двумя колонками "Номер группы" и "Название группы". Задача: отобразить в первой закладке, в ячейках столбца "Р" (это номер группы) ссылаясь на сопоставление данных столбца "Х" (индификатор группы) со второй закладкой столбца "В" (название группы) в результате чего в первой закладке в ячейках столбца "Р" будут отображаться данные второй закладки ячеек столбца "А". названия значений в ячейках столбца "Х" идентичны значениям ячеек столбца "В".(текстовые к примеру Aveo).
С одной ячейкой я справился с помощью формулы: =ЕСЛИ(X2='Export Groups Sheet'!B4;'Export Groups Sheet'!A4)
Но со всем массивом не смог.(
Помогите, если не трудно.
А как получить в сухом остатке строки изначально уникальные, т.е. представленные в единственном экземпляре? Рецепт актуален и для Excel 2003.
Идею уже не новую (и ранее благополучно прошляпленную) я нашел здесь:
g "uniq -u" site:ru-board.com
Нужна утилита uniq из набора UnxUtils или UnxUpdates. Добавляем путь к ней в PATH.
Затем
Годится и для csv.
Ключ -i может быть полезен.
А без ключа -u будет другой коленкор (то, что Excel считает уникальным)
Поясню:
Если чем нарушил правила форума нещадно вымарывайте.
Подскажите пожалуйста, как сделать чтобы формула =ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2)) стала рабочей если диапазон откуда извлекаются уникальные значения был на одном листе, а сами уникальные значения появлялись на другом листе. Причем таблица из которой извлекаются значения является динамической и ей присвоено имя
---------------------
Да хоть на другом листе, хоть на листе другого файла. Самое главное при назначении диапазонов NameCount и NameList - начинайте делать их в целевом листе (куда будете собирать уникальные значения), переходя при указании данных на лист из которого берете данные.
Необходимо вместо формулы =ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"";) написать в A2 значение "1"(без формул), а дальше формула выглядит: ЕСЛИ(СЧЁТЕСЛИ(B$1:B3;B3)=1;B2+1;B2). Так получается, может не так красиво, так как столбец будет выглядеть:1,2,2,2,2,2,2,3,3,3,3,3.... но вторая формула будет работать, так как ВПР подтягивает в таком случае, по первому вхождению значения(что нам и надо).
Также, может МАКС(Nameout) внести в какую-либо ячейку, и постоянно на неё ссылаться? Я точно не знаю, но так может быть быстрее (чем в каждой строке заново рассчитывать).
Взял за основу Ваш 3-й вариант. Вот что получилось:
=ИНДЕКС(A$1:A$10000;ЕСЛИ(СЧЁТЕСЛИ(A$1:A2;A2)=1;СТРОКА(A2);""))
Работает по тому же принципу. То есть, сначала СЧЁТЕСЛИ проверяет сколько раз встречался пункт и если он уникален (встречался =1 раз), то формула ЕСЛИ выдает номер строки уникального значения. А ИНДЕКС выдает значение находящееся в диапазоне А1:А10000 на нужной строке.
Интересно почитать комментарии.
В общем вот.
Вариант 1.
=ЕСЛИ(ЕОШИБКА(ИНДЕКС(А$1:А$10000;ЕСЛИ(СЧЁТЕСЛИ(А$1:А4;А4)=1;СТРОКА(А4);"";)));"-";ИНДЕКС(А$1:А$10000;ЕСЛИ(СЧЁТЕСЛИ(А$1:А4;А4)=1;СТРОКА(А4);"";)))
Здесь есть два жирных минуса:
- она НЕ выносит отдельно уникальные значения, она просто заменяет не уникальные на "-"
- она не подойдет для работы с данными, т.к. при изменении первоначального списка (к примеру, фильтрация по алфавиту) конечный результат тоже будет меняться
Вариант 2=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(ИНДЕКС(A$1:B10000;ПОИСКПОЗ(СТРОКА(1:1);A:A;0);3);$C$1:$C1;0));ИНДЕКС(A$1:B10000;ПОИСКПОЗ(СТРОКА(1:1);A:A;0);3);0)
Этой формулой проблема с изменением списка уников решена. Но сохранилась замена не уникальных (теперь на 0) и появилось 2 условия использования:
- исходный список должен быть нумерованным
- и список с результатами обязательно должен начинаться со второй строки (в данном случае с С2)
Ладно, в общем буду пока думать над этой задачей. Вот так и учусь excel'ю потихонькуПодскажите, пожалуйста, как можно создать уникальный список из диапазона в 128 столбцов и 11000 строк, когда во всех ячейках значения в виде больничных кодов, то есть нет заголовков и других критериев (не считая пустых ячеек)?
Буду премного благодарна.
2)=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);A:B;2;0);"";)
Третий способ очень хорош, но я столкнулся с проблемой: если диапазон содержит пустые ячейки и одновременно нули, то после первого нуля формула присваивает номера пустым строкам до тех пор, пока не встретится второй нуль
Вот так можно от этого избавиться
В ячейку А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 работает?
Пытался оптимизировать одну из формул тут в комментариях:
Получил вот это:
Формула даже без преобразования в массив выдаёт в современном экселе список уникальных значений в любом месте.
Но у меня проблеме следующего характера: я эту формулу делаю именованной "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 - база данных, откуда откуда формируется уникальный список и колонка с условием.