Извлечение уникальных элементов из диапазона

378954 10.11.2012 Скачать пример

Способ 1. Штатная функция в Excel 2007

Начиная с 2007-й версии функция удаления дубликатов является стандартной - найти ее можно на вкладке Данные - Удаление дубликатов (Data - Remove Duplicates):

remove-duplicates1.png

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

Способ 2. Расширенный фильтр

Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data).

Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:

uniqe1.gif

Выбираем в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter). Получаем окно:

uniqe2.gif

В нем:

  • Выделяем наш список компаний в Исходный диапазон (List Range).
  • Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
  • Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

uniqe3.gif

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE):

remove-duplicates2.png

Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.

Способ 3. Выборка уникальных записей формулой

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

Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:

uniqe4.gif

Первая задача - пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А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 и выводит все позиции списка с номерами в отдельную таблицу:

uniqe5.gif

Ссылки по теме

 


Последнее решение просто потрясающее!
28.05.2013 17:36:52
А можно ли извлечь уникальных данных формулой, только без участия доп.столбца? А то список то длинный )))
02.06.2013 08:03:55
Только первым способом.
25.07.2013 09:48:09
можно
=ИНДЕКС($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)
25.07.2013 09:58:58
или вот так, чтобы работало со списком, в котором есть пустые строки
=ИНДЕКС($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)
06.02.2014 18:53:16
Не работает формула, ни та ни другая...
20.08.2014 11:16:16
как же так, уважаемый!

в ячейку 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)
08.09.2014 13:40:57
просто в предыдущих двух формулах некоторые знаки заменились смайлами )
27.09.2015 17:30:52
Здравствуйте!
Напишите пожалуйста вашу формулу так чтобы ее было полностью видно.
20.10.2015 16:45:10
Виноват, проверил как следует на своём примере - всё работает! Спасибо!
20.01.2016 00:18:12
В предыдущей формуле лишняя точка с запятой (после последних кавычек), из-за чего Excel ругается. Не знающие новички могут часами искать почему не работает (как я). Вот рабочая формула. Немого переделана, чтобы при протягивании вместо ошибки пустые ячейки показывала

формула массива (так как формула длинная и не влазит в ширину страницы- два пробела поставил, нужно будет убрать)

=ЕСЛИОШИБКА(ИНДЕКС($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);"")
20.01.2016 00:22:54
27.06.2013 08:58:11
Способ 3. Выборка уникальных записей формулой
A2=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"")   
два года этой формулой и пользовался. но на диаппазоне тысяч в 60 строк эта формула кидает Excel в глубокую задумчивость. тут на форуме мы это обсуждали. а если таких столбика с формулами два на листе - то удалить их можно только макросом. даже макросом займёт это несколько минут. потому что выделить столбик и нажать Del - не получится. а вот аналог этого способа для большого количество строк пока не могу найти. может быть счётесли + выборка единиц из этого вспомогательного столбика. потому что нахождение максимума формулой вида A60000 = макс(A$1:A59999)+1 кидает Excel в аут
10.07.2013 14:14:23
А возможно ли сделать отбор строк с уникальными значениями в столбце "А" по условию, допустим "не пустая ячейка" в столбце "В"?
21.07.2013 18:50:58
В некоторых случаях с этой задачей может справиться Сводная таблица (не для удаления дубликатов, а для отбора уникальных данных)
24.07.2013 11:14:44
Где-то слышал, что для VBA можно использовать коллекции.
Ключ коллекции должен быть уникален, иначе возникает ошибка, которую можно обработать. Получается что-то типа
Function Выборка(ДиапазонЕстьДубликаты As Range) As Variant()
Dim Результат() As Variant
Dim Ячейка As Range
Dim КоллекцияБезДубликатов As New Collection
Dim i As Integer, item As Variant
On Error Resume Next
For Each Ячейка In ДиапазонЕстьДубликаты
    'неуникальный ключ даёт ошибку
    If Not IsEmpty(Ячейка) Then КоллекцияБезДубликатов.Add Ячейка.Value, CStr(Ячейка.Value)
Next
On Error GoTo 0
ReDim Результат(1 To КоллекцияБезДубликатов.Count, 1 To 1)
i = 1
For Each item In КоллекцияБезДубликатов
    Результат(i, 1) = item
    i = i + 1
Next
Выборка = Результат
End Function
07.11.2013 22:25:48
Добрый день! Подскажите пжл как сделать так, чтобы в столбце остались только уникальные ячейки, а те хоторые дубликаты не полностью удалялить, а оставить их пустыми.
Моя проблемма заключается в том, что когда я извлекаю дубликаты, ячейки удаляются, и идет несоответствие со слобцом который рядом.
13.01.2015 21:41:48
например так:
Sub CleanDuplicates()
' убирает лишние цифры из первого столбца, первая строка должна быть заполнена
Dim i As Integer
Dim intLastRow As Integer
intLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

n = Cells(2, 1).Value
For i = 1 To intLastRow
    If Cells(i, 1) <> "" Then
        If Cells(i, 1) = n Then
            Cells(i, 1) = ""
        Else
            n = Cells(i, 1).Value
        End If
    End If
Next i

End Sub
 
10.12.2013 02:11:54
Я так понимаю, третий вариант в Таблице реализовать нельзя напрямую?
У меня получился только как - сначала преобразую таблицу в диапазон, вставляю формулу, потом снова преобразую в таблицу
04.01.2014 12:19:33
Почему нельзя? Только вместо адресов ячеек нужно будет прописывать правильные ссылки на ячейки и столбцы Таблицы. Это же обычная формула, а не формула массива, которые в Таблицах не работают.
09.01.2014 11:24:16
Подскажите, а можно ли использовать третий способ, если список расположен на нескольких страницах?
15.07.2014 16:35:43
Лучше собрать их на один лист сначала.
23.03.2014 14:20:20
Всё есть про уникальные значения, а что если нужно извлечь в отдельный столбец именно повторяющиеся значения, оставив без изменения исходный диапазон. И еще сосчитать сколько раз повторяется каждый из элементов, скажем цифрой в следующем столбце.
15.12.2014 00:03:33
Добрый вечер,
что я делаю не так в третьем примере ?
Ругается при вводе описанной выше формулы

=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;"")
15.12.2014 08:17:21
Если у вас англоязычный Excel, то (возможно) аргументы функций могут разделяться не точкой с запятой, а просто запятыми (это зависит от региональных настроек Windows).
Либо проблема глубже, но без файла - не понять.
15.12.2014 22:13:58
Спасибо, замена на запятые помогла :)
15.01.2015 20:35:06
Желаю здравствовать.
Вопрос такой: есть столбец в 4 тысячи строк, примерно. Количество повторений в нем достаточно велико.
Так вот, как удалить дубликаты так, чтобы сам диапазон не смещался? То есть, чтобы остались пустые ячейки на месте дубликатов?
03.02.2015 12:17:07
Вторую английскую формулу надо исправить
12.03.2015 08:30:59
Уважаемые, здравствуйте!
Подскажите пожалуйста решение данной проблемы:
есть файл с двумя закладками "Export Products Sheet (2)"-основные данные с перечнем ассортимента продукции (более 15 000) и "Export Groups Sheet" с основными двумя колонками "Номер группы" и "Название группы". Задача: отобразить в первой закладке, в ячейках столбца "Р" (это номер группы) ссылаясь на сопоставление данных столбца "Х" (индификатор группы) со второй закладкой столбца "В" (название группы) в результате чего в первой закладке в ячейках столбца "Р" будут отображаться данные второй закладки ячеек столбца "А". названия значений в ячейках столбца "Х" идентичны значениям ячеек столбца "В".(текстовые к примеру Aveo).
С одной ячейкой я справился с помощью формулы: =ЕСЛИ(X2='Export Groups Sheet'!B4;'Export Groups Sheet'!A4)
Но со всем массивом не смог.(
Помогите, если не трудно.
12.03.2015 13:22:06
День добрый!
А как получить в сухом остатке строки изначально уникальные, т.е. представленные в единственном экземпляре? Рецепт актуален и для Excel 2003.
14.03.2015 22:15:49
Спасибо, уже не требуется. Excel могуч (а с PLEX'ом так вообще), но это не значит, что всё нужно делать только при помощи него.
Идею уже не новую (и ранее благополучно прошляпленную) я нашел здесь:
g "uniq -u" site:ru-board.com
Нужна утилита uniq из набора UnxUtils или UnxUpdates. Добавляем путь к ней в PATH.
Затем
cd /d D:\Desktop
sort src.txt | uniq -u > target.txt
Можно затем и
excel.exe /e target.txt  
Привык тащить/делать подобное на рабочий стол. Путь к папке, где лежит excel.exe у меня тоже в PATH.
Годится и для csv.
Ключ -i может быть полезен.
А без ключа -u будет другой коленкор (то, что Excel считает уникальным)

Поясню:
D:\Desktop>type 1.txt
22
11
33
55
44
33
22
D:\Desktop>sort 1.txt | uniq
11
22
33
44
55

D:\Desktop>sort 1.txt | uniq -u
11
44
55
Простите если это было банальностью.
Если чем нарушил правила форума нещадно вымарывайте.
26.03.2015 16:13:50
Подскажите пожалуйста, как сделать чтобы формула =ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2)) стала рабочей если диапазон откуда извлекаются уникальные значения был на одном листе, а сами уникальные значения появлялись на другом листе. Причем таблица из которой извлекаются значения является динамической и ей присвоено имя .
21.05.2015 20:51:15
Простите, ну что то не работает способ 3 ... ошибка #Н/Д Скажите почему?
07.07.2015 08:33:31
Спасибо. Сделал отчет за 5 минут из массива данных более 20000 строк.

Подскажите пожалуйста, как сделать чтобы формула =ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2)) стала рабочей если диапазон откуда извлекаются уникальные значения был на одном листе, а сами уникальные значения появлялись на другом листе. Причем таблица из которой извлекаются значения является динамической и ей присвоено имя
---------------------
Да хоть на другом листе, хоть на листе другого файла. Самое главное при назначении диапазонов NameCount и NameList - начинайте делать их в целевом листе (куда будете собирать уникальные значения), переходя при указании данных на лист из которого берете данные.
25.08.2015 08:57:05
До варианта с формулами дошёл сам. Но, его можно сделать более легким для расчёта.
Необходимо вместо формулы =ЕСЛИ(СЧЁТЕСЛИ(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) внести в какую-либо ячейку, и постоянно на неё ссылаться? Я точно не знаю, но так может быть быстрее (чем в каждой строке заново рассчитывать).
29.08.2015 22:20:44
можно ли изменить способ номер 3 таким образом что бы при внесении нового значения в середину списка это же значение отображалось внизу второго списка?? например: спсиок н 1: 1,2,6,4,5,7,8,9,10 список н 2: 1,2,3,4,5,6,7,8,9.
06.10.2015 11:25:16
Стояла задача добиться тоже результата, но только одной формулой.
Взял за основу Ваш 3-й вариант. Вот что получилось:

=ИНДЕКС(A$1:A$10000;ЕСЛИ(СЧЁТЕСЛИ(A$1:A2;A2)=1;СТРОКА(A2);""))

Работает по тому же принципу. То есть, сначала СЧЁТЕСЛИ проверяет сколько раз встречался пункт и если он уникален (встречался =1 раз), то формула ЕСЛИ выдает номер строки уникального значения. А ИНДЕКС выдает значение находящееся в диапазоне А1:А10000 на нужной строке.

Интересно почитать комментарии.
06.10.2015 15:41:19
Стыдно признать, но предыдущая формула была не доработана. :oops:

В общем вот.

Вариант 1.

=ЕСЛИ(ЕОШИБКА(ИНДЕКС(А$1:А$10000;ЕСЛИ(СЧЁТЕСЛИ(А$1:А4;А4)=1;СТРОКА(А4);"";)));"-";ИНДЕКС(А$1:А$10000;ЕСЛИ(СЧЁТЕСЛИ(А$1:А4;А4)=1;СТРОКА(А4);"";)))

Здесь есть два жирных минуса:
  1. она НЕ выносит отдельно уникальные значения, она просто заменяет не уникальные на "-"
  2. она не подойдет для работы с данными, т.к. при изменении первоначального списка (к примеру, фильтрация по алфавиту) конечный результат тоже будет меняться
Вариант 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 условия использования:
  1. исходный список должен быть нумерованным
  2. и список с результатами обязательно должен начинаться со второй строки (в данном случае с С2)
Ладно, в общем буду пока думать над этой задачей. Вот так и учусь excel'ю потихоньку ;)
10.12.2015 18:31:08
Здравствуйте, знатоки!
Подскажите, пожалуйста, как можно создать уникальный список из диапазона в 128 столбцов и 11000 строк, когда во всех ячейках значения в виде больничных кодов, то есть нет заголовков и других критериев (не считая пустых ячеек)?
Буду премного благодарна.
11.03.2016 16:42:01
1)=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"";)
2)=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);A:B;2;0);"";)
23.06.2016 16:53:36
3 способ просто шикарен! Огромное спасибо.:)
Третий способ очень хорош, но я столкнулся с проблемой: если диапазон содержит пустые ячейки и одновременно нули, то после первого нуля формула присваивает номера пустым строкам до тех пор, пока не встретится второй нуль. Пример
10.08.2016 08:45:23
Здравствуйте,помогите пожалуйста,разобраться,начну сначала. Мне необходимо в таблице по дислокации жилых домов отсортировать данные. имеется колонка со списком домов по адресам и колонка со списком адресов контейнерных площадок у этих домов. Некоторыми контейнерными площадками пользуются несколько домов. Мне необходимо в таблице оставить только те дома,у которых есть своя контейнерная площадка. Я воспользовалась способом №1,так как я еще только познаю возможности exel,он мне показался наиболее понятным. Но я столкнулась с такой проблемой что при использовании этого способа,в колонке контейнерных площадок все же остались несколько повторяющихся адресов,а некоторые адреса в колонке жилых домов не совпадают с адресами в колонке контейнерных площадок. он отсортировывает без разбора,если адрес 2 й раз по списку,убирает его,а мне нужно чтобы оставались только те контейнерные площадки с адресом совпадающим с адресом жилого дома. Возможно ли это выполнить?
16.11.2016 16:05:15
А у меня первый шаг получился, а дальше никак, НД выдает и все, помогите пожалуйста