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

Способ 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);"")
21.09.2018 10:57:15
Честно говоря, я эту формулу не понимаю вообще (хотя немного разбираюсь в EXCEL)... Не могли ли бы Вы пояснить что да как, т.к. я ввожу эту формулу (естественно со своими ячейками), и совсем в другом месте (т.е. строка другая и столбец), но формула ее то считает, но не целиком, то вообще пустые ячейки (где должны выдаваться значения) показывает...
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. При вставке формулы нужно удалить лишние пробелы.
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
А у меня первый шаг получился, а дальше никак, НД выдает и все, помогите пожалуйста
04.05.2017 18:46:17
Коллега писал вот это:
Третий способ очень хорош, но я столкнулся с проблемой: если диапазон содержит пустые ячейки и одновременно нули, то после первого нуля формула присваивает номера пустым строкам до тех пор, пока не встретится второй нуль
Вот так можно от этого избавиться
В ячейку А2 пишем эту формулу и протягиваем до конца массива
ЕСЛИ(ИЛИ(B2=0;B2="");"";ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"";))
Восхитительная функция Агрегат
=ИНДЕКС($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)))
11.04.2019 13:53:16
Эта формула восхитительна и единственная смогла корректно  заработать.
Друзья, можно сюда добавить сортировку по убыванию количества совпадений? Понятно, что это можно использовать костыли.. А в рамках одной  формулы это возможно? пробовал прикрутить Макс + счет, но во всех значениях массива оставалось первое максимальное значение.
12.04.2019 13:51:13
Решил вопрос этим топиком
На выходе у меня появился документ со списком уникальных значений с автоматической сортировкой по количеству повторений.
Петров 15
Иванов 12
Жуков 7
...
14.08.2017 10:28:17
Помогите пожпалуйста,интересует 3 способ,уже всю голову сломал не получается,нужно сделать динамический список с количеством повторяющих имен ввиде таблицы,т.е. У меня есть люди которые обращались в профсоюз с просьбами (это лист1), мне надо на лист4 вынести ввиде таблицы этот список фамилий с количеством обращений,чтобы видеть актив,кто сколько раз обращался
17.08.2017 07:30:13
Алексей, посмотрите видео про создание сводной таблицы - это то, что вам нужно.
16.08.2018 09:49:33
Добрый день Николай, подскажите пожалуйста в 3 варианте у вас не дописана формула для англ.версии,
перевела ее, IF(MAX(NameCount)<ROW(1:1);"";VLOOKUP(ROW(1:1);NameList;2))
но проблема почему-то осталась, пишет ошибка
24.08.2017 23:31:06
А как быть с двумерной таблицей. Есть сетка записи: по вертикали - время, по горизонтали допустим - залы.
В ячейку вписываются Фамилии/номера клиентов. Они могут дублироваться  и в строке и в столбце.
Как вывести столбцом весь перечень уникальных значений? Желательно "на лету" без макрасов.
Спасибо!
20.11.2017 16:15:28
Допишите английскую формулу в третьем способе. Перевел ее самостоятельно- не работает =IF(MAX(TaskNumber)<ROW(22:22);"";VLOOKUP(ROW(22:22);TaskName;2))
16.08.2018 09:38:36
Добрый день, а вы нашли верную формулу для английской версии?
12.12.2017 10:44:06
Добрый день. Все примеры приведены с данными начиная с первой строки. Что надо исправить в формуле Sam D. чтобы выводились уникальные данные из списка начинающегося не с первой строки? Стоит переместить список с данными хотя бы на одну строку, как список уникальных становится пустым. Так и смог понять, что надо исправить в формуле для избежания этого.


Updt Получилось, но логику и принцип работы так и не уловил.
21.12.2017 06:37:31
Хотел спросить, а как сделать наоборот, имеется к примеру список (все уникальные), а проверить надо 2 список, но там нет 1 или 2, 3 уникальных, надо их вытащить в отдельном списке, например дежурство, в этом месяце не участвуют вот эти ФИО, в этом месяце не задействованы вот эти значения из таблицы 1 и т.д.
очень кстати полезная штука будет, особенно если можно без всяких макросов и ВБА :D
24.01.2018 14:57:11
Добрый день!
Подскажите по способу №3 - как сделать, чтобы номер проставлялся не в первом повторяющемся значении, а в последнем?
К примеру на цифре 2:
1Орион
Ланта
Ланта
2Ланта
3Прод-сервис
10.05.2018 19:18:45
 Здравствуйте.

Третий способ отлично работает, но есть проблема, не уверен, может только у меня так.

Суть проблемы, то что формула не извлекает по настоящему "уникальные данные". Прописав пример, у меня получилось, что выборка идёт по позициям которые встречаются минимум дважды начиная со второго аргумента. Загвоздка в том, что у меня в списках есть как и повторяющиеся аргументы так и "уникальные".

Собственно вопрос:

Каким образом можно модифицировать формулу что бы она извлекала все значения?
09.06.2018 13:12:11
Добрый день!!! А возможно пойти от обратного, т.е. искать дубликаты  и выдавать информацию с номером либо номерами строк, где они встречаются. Например, у меня столбец с номерами телефонов в огромном количестве. Условным форматированием была настроена подсветка номеров, которые встречаются в столбце более 2-х раз. С ней я могу определить уникальный звонок от повторного. Теперь зная что звонок повторный мне нужно понять когда он был сделан ранее. Возможно ли такое? Желательно чтобы инфа выдавалась ли выплывающим окном либо как то рядом, чтобы не плодить лишние строки, так как эта процедура делается постоянно. Спасибо
22.06.2018 14:20:59
Добрый день. Не понял "СТРОКА(1:1)"  Как она действует в формуле??:(
23.09.2018 22:19:27
Доброго времени суток!
Прошу подсказать можно ли удалить не все дубликаты, а положим первые 4?

Спасибо!
16.02.2019 11:14:22
Добрый день,
Подскажите, пожалуйста, в одном из приведенных примеров Вы склеили ячейки в одну. И потом отфильтровали полученный список. А можно ли полученный список обратно "расклеить", и если можно то как?

Заранее спасибо!
04.04.2019 11:43:14
Здравствуйте, дорогие товарищи.

Николай, спасибо за статью.

Не раз встречал способы решения данного вопроса. Есть даже скачанные примеры. Но ваш "подход для чайников" как всегда особняком.

Я немного упростил его, для тех кто не умеет именовать диапазоны, потому что в способе 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
30.08.2020 21:42:36
В 365 появилась функция Unique  УНИК, которая позволяет это сделать. Но они в попытках поиметь рынок опять поимеют себя, т.к. функция для обычных пользователей не доступна. В гугл таблицах уже давно эта команда работает и не надо изобретать ракету, чтобы забить гвоздь.
15.09.2020 21:21:46
Добры вечер всем. Друзья, можно сюда добавить сортировку по убыванию количества совпадений в рамках одной  формулы ? это возможно? пробовал прикрутить Макс + счет, но во всех значениях массива оставалось первое максимальное значение.
14.09.2022 12:56:34
Вариант 3 не рабочий, постоянно 0 выдает
=ЕСЛИ(СЧЁТЕСЛИ(W$1:W5;W5)=1;МАКС(V$1:V5)+1;"") или он только для столбцов A и B работает?
28.02.2023 12:35:45
а подскажите есть ли возможность вывода уникальных значений из выделенного диапазона столбца, которые встречаются только в выделенном диапазоне и не встречаются за его границами?
28.08.2023 18:03:09
Здравствуйте.
Пытался оптимизировать одну из формул тут в комментариях:
Получил вот это:
=ИНДЕКС(список;НАИМЕНЬШИЙ(
ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(список;список;0);"")= 
СТРОКА(ДВССЫЛ("$1:$"&СУММ(СЧЁТЗ(список);СЧИТАТЬПУСТОТЫ(список))));ПОИСКПОЗ(список;список;0);"");
СТРОКА(ДВССЫЛ("$1:$"&СЧЁТ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(список;список;0);"")= 
СТРОКА(ДВССЫЛ("$1:$"&СУММ(СЧЁТЗ(список);СЧИТАТЬПУСТОТЫ(список))));ПОИСКПОЗ(список;список;0);""))))
);1) 
Здесь "Список" - это именованный диапазон из одного столбца и кучи строк.
Формула даже без преобразования в массив выдаёт в современном экселе список уникальных значений в любом месте.
Но у меня проблеме следующего характера: я эту формулу делаю именованной "Ctrl+F3" и хочу сделать выпадающий список по ней, но там только одно значение. Почему Excel на листе сам заполняет несколько строк уникальными значениями, т.е. список в уме он держит, но не может превратить это в раскрывающийся список.
16.01.2024 20:08:58
Спасибо автору!
Очень полезно.
Мне помогла такая конструкция формулы, доделал под себя
=ЕСЛИОШИБКА(ИНДЕКС('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", то перейти с проверки следующей строки на уникальность, а значение этой ячейки не учитывать.
24.01.2024 11:39:25
Добрейший денечек.
Скажите, пожалуйста, как в 3-й вариант добавить отбор по условию?
Есть лва листа:
1 - куда выводится уникальный список и содержится условие в ячейке, которое является динамическим;
2 - база данных, откуда откуда формируется уникальный список и колонка с условием.
Наверх