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

15376 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

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

 


Страницы: 1  2  
Александр Скакальский
17.04.2013 14:38:47
Последнее решение просто потрясающее!
Ссылка
Bomond
28.05.2013 17:36:52
А можно ли извлечь уникальных данных формулой, только без участия доп.столбца? А то список то длинный )))
Ссылка
Николай Павлов
02.06.2013 08:03:55
Только первым способом.
Родитель Ссылка
futurama3000
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)
Родитель Ссылка
futurama3000
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)
Родитель Ссылка
ran-nn
06.02.2014 18:53:16
Не работает формула, ни та ни другая...
Родитель Ссылка
futurama3000
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
Здравствуйте!
Напишите пожалуйста вашу формулу так чтобы ее было полностью видно.
Родитель Ссылка
ran-nn
20.10.2015 16:45:10
Виноват, проверил как следует на своём примере - всё работает! Спасибо!
Родитель Ссылка
Sam D.
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);"")
Родитель Ссылка
Юрик Закирьянов
21.09.2018 10:57:15
Честно говоря, я эту формулу не понимаю вообще (хотя немного разбираюсь в EXCEL)... Не могли ли бы Вы пояснить что да как, т.к. я ввожу эту формулу (естественно со своими ячейками), и совсем в другом месте (т.е. строка другая и столбец), но формула ее то считает, но не целиком, то вообще пустые ячейки (где должны выдаваться значения) показывает...
Родитель Ссылка
berloga_
03.03.2023 12:15:45
Поэкспериментировав с вашей формулой пришел к выводу, что ее можно упростить, заменив на


=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$25;
НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0)=СТРОКА()-1;
СТРОКА()-1;"");"");СТРОКА()-1));"")


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

P.S. При вставке формулы нужно удалить лишние пробелы.
Родитель Ссылка
Stanislav S
01.07.2024 13:05:24
Ошибка в вашей формуле.

Правильно:

=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$25;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ($B$2:$B$25;$B$2:$B$25;0)=СТРОКА()-1; СТРОКА()-1;"");"");1));"")
Родитель Ссылка
Sam D.
20.01.2016 00:22:54
.
Родитель Ссылка
KUDRIN
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
Добрый день! Подскажите пжл как сделать так, чтобы в столбце остались только уникальные ячейки, а те хоторые дубликаты не полностью удалялить, а оставить их пустыми.
Моя проблемма заключается в том, что когда я извлекаю дубликаты, ячейки удаляются, и идет несоответствие со слобцом который рядом.
Ссылка
ixet
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
Всё есть про уникальные значения, а что если нужно извлечь в отдельный столбец именно повторяющиеся значения, оставив без изменения исходный диапазон. И еще сосчитать сколько раз повторяется каждый из элементов, скажем цифрой в следующем столбце.
Ссылка
Jabrail
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).
Либо проблема глубже, но без файла - не понять.
Родитель Ссылка
Jabrail
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.
Ссылка
lesnik911
06.10.2015 11:25:16
Стояла задача добиться тоже результата, но только одной формулой.
Взял за основу Ваш 3-й вариант. Вот что получилось:

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

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

Интересно почитать комментарии.
Ссылка
lesnik911
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'ю потихоньку ;)
Родитель Ссылка
playlife
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 способ просто шикарен! Огромное спасибо.:)
Ссылка
Страницы: 1  2  
Наверх