Excel vba поиск уникальных значений в столбце

Обновлено: 07.07.2024

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись - т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA) и сводных таблиц. В этой статье рассмотрим каждый из вариантов.

при помощи встроенных возможностей Excel 2007 и выше
В Excel 2007 и 2010 это сделать проще простого - есть специальная команда, которая так и называется - Удалить дубликаты (Remove Duplicates) . Расположена она на вкладке Данные (Data) подраздел Работа с данными (Data tools)

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (Data) -Удалить дубликаты (Remove Duplicates) .

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

Появится окно с параметрами удаления дубликатов

Ставите галочки напротив тех столбцов, дубликаты в которых надо удалить и жмете Ок. Если в выделенном диапазоне так же расположены заголовки данных, то лучше поставить флаг Мои данные содержат заголовки, чтобы случайно не удалить данные в таблице(если они вдруг полностью совпадают со значением в заголовке).

Способ 1: Расширенный фильтр
В случае с Excel 2003 все посложнее. Там нет такого инструмента, как Удалить дубликаты. Но зато есть такой замечательный инструмент, как Расширенный фильтр. В 2003 этот инструмент можно найти в Данные -Фильтр -Расширенный фильтр. Прелесть этого метода в том, с его помощью можно не портить исходные данные, а создать список в другом диапазоне. В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на вкладке Данные (Data) , группа Сортировка и фильтр (Sort & Filter) - Дополнительно (Advanced)
Как его использовать: запускаем указанный инструмент - появляется диалоговое окно:

Обработка: Выбираем Скопировать результат в другое место (Copy to another location) .
Исходный диапазон (List range) : Выбираем диапазон с данными(в нашем случае это А1:А51 ).
Диапазон критериев (Criteria range) : в данном случае оставляем пустым.
Поместить результат в диапазон (Copy to) : указываем первую ячейку для вывода данных - любую пустую(на картинке - E2 ).
Ставим галочку Только уникальные записи (Unique records only) .
Жмем Ок.

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

Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте (Filter the list, in-place) .

Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье: Как в ячейке с формулой вместо ошибки показать 0

Способ 3: код VBA
Данный подход потребует разрешения макросов и базовых знаний о работе с ними. Если не уверены в своих знаниях для начала рекомендую прочитать эти статьи:

к статье приложен видеоурок потребуется, чтобы понять куда вставлять приведенные ниже коды

Оба приведенных ниже кода следует помещать в стандартный модуль. Макросы должны быть разрешены.

Исходные данные оставим в том же порядке - список с данными расположен в столбце " А "( А1:А51 , где А1 - заголовок). Только выводить список мы будем не в столбец С , а в столбец Е , начиная с ячейки Е2 :

Sub Extract_Unique() Dim vItem, avArr, li As Long ReDim avArr(1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value 'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А .Add vItem, CStr(vItem) If Err = 0 Then li = li + 1: avArr(li, 1) = vItem Else: Err.Clear End If Next End With If li Then [E2].Resize(li).Value = avArr End Sub

С помощью данного кода можно извлечь уникальные не только из одного столбца, но и из любого диапазона столбцов и строк. Если вместо строки
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
указать Selection.Value , то результатом работы кода будет список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить - вместо [E2] поставить ту, в которой данных нет.
Так же можно указать конкретный диапазон:

Или другой столбец:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value

здесь отдельно стоит обратить внимание то, что в данном случае помимо изменения А2 на С2 изменилась и цифра 1 на 3. Это указание на номер столбца, в котором необходимо определить последнюю заполненную ячейку, чтобы код не просматривал лишние ячейки. Подробнее про это можно прочитать в статье: Как определить последнюю ячейку на листе через VBA?

Универсальный код выбора уникальных значений
Код ниже можно применять для любых диапазонов. Достаточно запустить его, указать диапазон со значениями для отбора только неповторяющихся(допускается выделение более одного столбца) и ячейку для вывода результата. Указанные ячейки будут просмотрены, из них будут отобраны только уникальные значения(пустые ячейки при этом пропускаются) и результирующий список будет записан, начиная с указанной ячейки.

Способ 4: Сводные таблицы
Несколько нестандартный способ извлечения уникальных значений.

Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка (Insert) -группа Таблица (Table) -Сводная таблица (PivotTable)
В диалоговом окне Создание сводной таблицы (Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
указываем место размещения Сводной таблицы:
- На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
Т.к. сводные таблицы при обработке данных, которые помещаются в область строк или столбцов, отбирают из них только уникальные значения для последующего анализа, то от нас ровным счетом ничего не требуется, кроме как создать сводную таблицу и поместить в область строк или столбцов данные нужного столбца.
На примере приложенного к статье файла я :
- выделил диапазон A1:B51 на листе Извлечение по критерию
- вызвал меню вставки сводной таблицы: вкладка Вставка (Insert) -группа Таблица (Table) -Сводная таблица (PivotTable)
  выбрал вставить на новый лист (New Worksheet)
- назвал этот лист Уникальные сводной таблицей
- поле Данные поместил в область строк
- поле ФИО в область фильтра. Почему? Чтобы удобно было выбирать одно или несколько ФИО и в сводной отображался бы список уникальных месяцев только для выбранных фамилий
В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши -Обновить (Refresh) или вкладка Данные (Data) -Обновить все (Refresh all) -Обновить (Refresh) . А если исходные данные пополняются динамически и того хуже - надо будет заново указывать диапазон исходных данных. И еще один минус - данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.

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

В приложенном примере помимо описанных приемов, записана чуть более сложная вариация извлечения уникальных элементов формулой и кодом, а именно: извлечение уникальных элементов по критерию. О чем речь: если в одном столбце фамилии, а во втором( В ) некие данные(в файле это месяцы) и требуется извлечь уникальные значения столбца В только для выбранной фамилии. Примеры подобных извлечений уникальных расположены на листе Извлечение по критерию.

При написании макросов для работы с данными в VBA Excel иногда возникает необходимость отбора уникальных значений из списка с повторяющимися элементами. Для этого можно воспользоваться следующим кодом:
'myRange - диапазон ячеек, заполненный исходным списком элементов 'myElement - элемент коллекции (должен быть типа "Variant") Dim myRange As Range , myCell As Range , myCollection As New Collection , _ 'присваиваем переменной myRange диапазон ячеек с исходным списком элементов myCollection . Add CStr ( myCell . Value ) , CStr ( myCell . Value )
На этом отбор уникальных значений завершен. Коллекция заполнена уникальными элементами.

Добавление уникальных элементов в ListBox

Теперь можно добавить уникальные значения в ListBox, если перед этим создать форму UserForm1 и на нее добавить элемент управления ListBox1:

ListBox заполнен уникальными значениями из коллекции. Другие способы заполнения ListBox и ComboBox смотрите здесь.

Запись уникальных значений на рабочий лист

А так можно добавить уникальные элементы в ячейки столбца «В» активного рабочего листа:

При необходимости сортируем полученный список в столбце "В":
Range ( Cells ( 1 , 2 ) , Cells ( i , 2 ) ) . Sort Key1 : = Range ( "B1" ) , Order1 : = xlAscending , _ Header : = xlGuess , OrderCustom : = 1 , MatchCase : = False , Orientation : = xlTopToBottom
А также можно отобразить количество найденных уникальных элементов, если, конечно, на форму UserForm1 добавлен элемент управления Label1:
UserForm1 . Label1 . Caption = "Уникальных элементов: " & myCollection . Count
Если вам необходимо в ListBox или ComboBox загрузить отсортированный список, его элементы можно добавить с листа Excel после сортировки, в данном примере из диапазона Range(Cells(1, 2), Cells(i, 2)).

Обратите внимание, что в представленном коде VBA Excel для отбора уникальных значений из списка, выгрузки их в ListBox и записи на рабочий лист идет сплошная нумерация от Sub ОтборУникальных() и до End Sub.

Для наглядного ознакомления с работой представленного кода вы можете скачать демонстрационный файл.

Функция UniqueValuesFromArray позволяет найти в указанном столбце двумерного массива все уникальные значения, и получить новый массив, содержащий все найденные уникальные значения.
Это может пригодиться, если надо, к примеру, заполнить ComboBox на форме возможными вариантами значений из базы данных:

Код самой функции:

Во вложении - пример использования этой функции в макросе (вывод уникальных записей в другой столбец листа), и пользовательская функция Уникальные - для использования в формулах листа Excel.

Макрос и дополнительная функция из файла во вложении:

Комментарии

Благодарю за Вашу работу. Столкнулся с проблемой выборки дубликатов из массива около 80000 строк и записью строки из 6 значений другого массива на лист ошибок. Решил вопрос доработкой функции UniqueValuesFromArray плюс быстрый поиск ArraySearchResults. Временные показатели очень порадовали 40 секунд вместо 10 минут перебора и выборки из другого массива. Прошу дополнить страницу или создать новую с функцией DoubleValuesFromArray (Поиск уникальных ПОВТОРЯЮЩИХСЯ значений в массиве)

Еще раз спасибо за Вашу работу. Выручали не раз.

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

Это потому что вы в другой файл перенесли только часть кода (а код функции UniqueValuesFromArray забыли скопировать)

Добрый день!
На родном файле макрос работает отлично, на другом, с тем же расширением не работает, выдает ошибку Sub or Function not defined на UniqueValuesFromArray. В чем может быть причина?
Расширения обоих файлов поменял на .xlsm
Спасибо!

РАЗОБРАЛСЯ.
СПАСИБО БОЛЬШОЕ ЗА ФУНКЦИЮ1111111

Нет.
выглядит так:
А B
1 текст 1 текст 1
2 текст 2 текст 2
3 текст 3 текст 3
4 текст 4 текст 4
5 текст 5 текст 5
6 текст 6 текст 6
7 текст 7 текст 7
.
33 текст 33 текст 33
34 текст 34 текст 34
35 текст 35 текст 1
36 текст 36 текст 1
37 текст 37 текст 1
и так делее
Т.е. в столбце А я вставил только уникальные значения до строки 80 (текст 80), но на 35 строке функция перестает работать как-будто, может я что не так сделал?
Помогите пожалуйста, я могу это сделать формулами, ног проблема в нагрузке, в моем файле просматриваемый массив имеет длину 2000 строк, excel умирает на расчетах.(((((

Видимо, 36-е значение (которое «первое уникальное») - только с виду похоже на первое
(например, есть лишний пробел, или одна русская буква заменена похожей английской)

Добрый день.
Сразу скажу, что в макросах не че не понимаю.
Мне очень понравилась ваша функция, но появилась проблема.
Функция выбирает 35 уникальных значений, далее возвращает первое уникальное.
В чем проблема?

В статье написано:

пользовательская функция - для использования в качестве формулы массива

выделяете НЕСКОЛЬКО ячеек, вводите эту формулу, и завершаете ввод нажатием Ctrl + Shift + Enter (вместо обычного Enter)
Тогда будет выводиться несколько уникальных значений

А вообще, уникальные можно и формулами вывести, без макросов.

Доброго времени суток. Макрос у меня заработал отлично а вот функция "Уникальные" почему то работает только в файле примере Unique.xls. Ситуация такая: все как положено добавил функцию в модуль, но она на отрез отказывается корректно работать, вместо уникальных значений выдает первое попавшееся значение причем оно возвращается во всех ячейках где прописана функция. Помогите пожалуйста разобраться.

Не знаю, увидите ли вы, но спасибо!
И автору тоже!

Добрый вечер. Разбираю макрос, который здесь представлен. С VBA не работала. Можно ли усложнить функцию вывода уникальных: вывод за период временной с исключением каких-то уникальных значений, т.е. возможно это значение встречается в этот период несколько раз, но выводить его не надо, так как не интересно. Буду очень благодарна за помощь

Добрый день. Разбираю сейчас Ваш макрос. Не сильна в них. Прошу помочь. В целом все работает отлично, но не могу понять как поменять его, чтобы новый массив уникальных вставал на другой (заданный) лист и не столбцом, а строкой. Буду очень признательна за помощь.

Тут самое главное, вот это: On Error Resume Next: Dim coll As New Collection
то есть при ошибке продолжить заполнение коллекции, а так как в коллекцию добавляется ключ такой же как и само значение
txt$ = Trim(cell): If Len(txt$) Then coll.Add txt$, txt$ 'коллекция.добавить значение, ключ
то в коллекции автоматически остается только один уникальный ключ, по определению, и вместе с ним одно уникальное значение

Владимир, ваш вопрос не имеет никакого отношения к теме статьи
В вашем случае, макросы не нужны, - достаточно одной формулы
Обратитесь на любой форум по Excel, прикрепив пример файла, - вам помогут с формулой.

Здравствуйте!
Давно пытаюсь найти решение подобной задачи. Знаний к сожалению не хватает. И вот нашёл Ваш пример. У меня задача хоть и подобная но масштабы больше. В одной книге есть 2 листа:
1 лист (таблица заказов) имеет 159 столбцов с данными, 165 столбец с датой. Каждый столбец с данными пронумерован № продукции 0001,0002,0003, и т.д.0159.
2 лист (отчёт) В отчёте 2 столбец №-ра продукции построчно и 4 столбец для копирования данных продукции с 1 листа.
И вот надо решить задачу: как выбрать продукцию с 1 листа и скопировать во 2 лист по определённой дате заданной во 2 листе.
Вроде бы кажется, что всё просто, но ничего не получается. Прошу Вас подскажите как всё это решить.

Спасибо большое! Действительно быстро работает.
Единственное, что добавил - пропуск пустых ячеек:
For i = LBound(arr) To UBound(arr)
If Trim(arr(i, col)) = "" Then GoTo 1
txt$ = Trim(arr(i, col)): coll.Add txt$, txt$
1: Next i

Function UniqueValuesFromArray может возвращать массив размерностью исходного массива, а не размерностью N * 1?

В дополнительном столбце, при помощи формулы типа =СЦЕПИТЬ(A1;"//";B1), объедините значения этих 2 столбцов,
и потом по этому доп.столбцу отбирайте уникальные

Подскажите пожалуйста, как изменить ваши процедуры, чтобы можно было выбрать уникальные значения не одного столбца, а нескольких. Например в Стобце A значения а,в,а,в. В столбце B в соседних ячейках значения с,д,с,е. Нужно чтобы макрос вдавал массив: В первом столбце: а,в,в. В соседнем с,д,е.

Спасибо! Очень помогло.

Спасибо за функцию.
Работает отлично.
Это уже вторая Ваша функция в течение двух дней, которую я применил в своей работе.
Завтра еще третью буду пробовать - поиск в массиве :)

Еще раз большое спасибо!

Спасибо. Буду изучать.

Так сделать можно, но.
одно дело - выбрать уникальные значения (тут всё ясно, вариантов особо нет),
и совсем другое - выбрать строки с уникальными значениями в каком-то столбце.

К примеру, есть у нас 3 строки с одинаковыми значениями в 1-м столбце.
Какую из этих трёх строк выводить в результат? Первую, третью, вторую?

Пример её использования для вашего случая:

Извините за надоедливость ))
Возни еще один вопрос.

Сейчас выборка уникальный происходит только по столбцу "A".
Возможно ли сделать выборку уникальных по столбцу "A", но с условием, чтобы в диапазон фильтрования попадали также столбцы B и C ?

Т.е. чтобы был аналог функционалу Excel 2007: Выделяю столбцы A,B,C => Данные => Удалить дубликаты => В качестве столбца, по которому будет происходить удаление дубликатов выбираю только столбец A.

Как результат: уникальные значения будут отобраны по столбцу "А", но соответствующие записи из столбцов B и C будут также сохранены.

Вроде разобрался:
Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Range(Sheets("данные").Range("A1"), Sheets("данные").Range("A" & Rows.Count).End(xlUp))

Подскажите, пожалуйста. Сейчас макрос срабатывает в случае если активен лист с массивом.
Как правильно добавить название листа в запись, чтобы активация листа с массивом не была обязательной?

Я пробовал вот так, но макрос выдает ошибку:

Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Sheets("данные").Range([A1], Sheets("данные").Range("A" & Rows.Count).End(xlUp))

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

Отличный пример использования коллекций для создания массива уникальных записей. Давно использую коллекции, но до этого сам не додумался. И работает быстро ))

Начиная с 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 и копируем затем вниз до упора следующую формулу:

В английской версии это будет:

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =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 и выводит все позиции списка с номерами в отдельную таблицу:

Начиная с 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 и копируем затем вниз до упора следующую формулу:

В английской версии это будет:

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =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 и выводит все позиции списка с номерами в отдельную таблицу:

Читайте также:

Excel vba поиск уникальных значений в столбце

Добавление уникальных элементов в ListBox

Запись уникальных значений на рабочий лист

Комментарии

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

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

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

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