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

- Обработка: Выбираем Скопировать результат в другое место
(Copy to another location) . - Исходный диапазон
(List range) : Выбираем диапазон с данными(в нашем случае это ).А1:А51 - Диапазон критериев
(Criteria range) : в данном случае оставляем пустым. - Поместить результат в диапазон
(Copy to) : указываем первую ячейку для вывода данных - любую пустую(на картинке - ).E2 - Ставим галочку Только уникальные записи
(Unique records only) . - Жмем Ок.
Примечание: если вы хотите поместить результат на другой лист, то просто так указать другой лист не получится. Вы сможете указать ячейку на другом листе, но...Увы и ах...Excel выдаст сообщение, что не может скопировать данные на другие листы. Но и это можно обойти, причем довольно просто. Надо всего лишь запустить Расширенный фильтр с того листа, на который хотим поместить результат. А в качестве исходных данных выбираем данные с любого листа - это дозволено.
Так же можно не выносить результат в другие ячейки, а отфильтровать данные на месте. Данные от этого никак не пострадают - это будет обычная фильтрация данных.
Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте
Этот способ сложнее в понимании для неопытных пользователей, но зато он создает список уникальных значений, не изменяя при этом исходные данные. Ну и он более динамичен: если изменить данные в исходной таблице, то изменится и результат. Иногда это бывает полезно. Попытаюсь объяснить на пальцах что и к чему: допустим, список с данными у Вас расположен в столбце
Надо отметить, что эта формула является формулой массива. Об этом могут сказать фигурные скобки, в которые заключена данная формула. А вводится такая формула в ячейку сочетанием клавиш -
Тогда вместо ошибки
Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье: Как в ячейке с формулой вместо ошибки показать 0
Для пользователей Excel 2021 выше, а так же пользователей Excel 365(с активной подпиской) - использовать формулы для извлечения уникальных элементов проще простого. В этих версиях появилась функция
Что самое важное в данном случае - это функция динамического массива и вводить её надо только в одну ячейку C2, а результат она поместит сама в нужное количество ячеек.
Данный подход потребует разрешения макросов и базовых знаний о работе с ними. Если не уверены в своих знаниях для начала рекомендую прочитать эти статьи:
- Что такое макрос и где его искать?
к статье приложен видеоурок - Что такое модуль? Какие бывают модули?
потребуется, чтобы понять куда вставлять приведенные ниже коды
Оба приведенных ниже кода следует помещать в стандартный модуль. Макросы должны быть разрешены.
Исходные данные оставим в том же порядке - список с данными расположен в столбце "А"(А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:C30").Value |
Или другой столбец:
Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value |
здесь отдельно стоит обратить внимание то, что в данном случае помимо изменения
Код ниже можно применять для любых диапазонов. Достаточно запустить его, указать диапазон со значениями для отбора только неповторяющихся(допускается выделение более одного столбца) и ячейку для вывода результата. Указанные ячейки будут просмотрены, из них будут отобраны только уникальные значения(пустые ячейки при этом пропускаются) и результирующий список будет записан, начиная с указанной ячейки.
Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next 'запрашиваем адрес ячеек для выбора уникальных значений Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений", "Запрос данных", "A2:A51", Type:=8) If rVals Is Nothing Then 'если нажата кнопка Отмена Exit Sub End If 'если указана только одна ячейка - нет смысла выбирать If rVals.Count = 1 Then MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation, "www.excel-vba.ru" Exit Sub End If 'отсекаем пустые строки и столбцы вне рабочего диапазона Set rVals = Intersect(rVals, rVals.Parent.UsedRange) 'если указаны только пустые ячейки вне рабочего диапазона If rVals Is Nothing Then MsgBox "Недостаточно данных для выбора значений", vbInformation, "www.excel-vba.ru" Exit Sub End If avVals = rVals.Value 'запрашиваем ячейку для вывода результата Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then 'если нажата кнопка Отмена Exit Sub End If 'определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) 'при помощи объекта Коллекции(Collection) 'отбираем только уникальные записи, 'т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then 'пропускаем пустые ячейки .Add x, CStr(x) 'если добавляемый элемент уже есть в Коллекции - возникнет ошибка 'если же ошибки нет - такое значение еще не внесено, 'добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else 'обязательно очищаем объект Ошибки Err.Clear End If End If Next End With 'записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub |
Несколько нестандартный способ извлечения уникальных значений.
- Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка
(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable) - В диалоговом окне Создание сводной таблицы
(Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных) - указываем место размещения Сводной таблицы:
- На новый лист
(New Worksheet) - На существующий лист
(Existing Worksheet)
- На новый лист
- подтверждаем создание нажатием кнопки OK
Т.к. сводные таблицы при обработке данных, которые помещаются в область строк или столбцов, отбирают из них только уникальные значения для последующего анализа, то от нас ровным счетом ничего не требуется, кроме как создать сводную таблицу и поместить в область строк или столбцов данные нужного столбца.
- выделил диапазон
на листе Извлечение по критериюA1:B51 - вызвал меню вставки сводной таблицы: вкладка Вставка
(Insert) -группа Таблица(Table) -Сводная таблица(PivotTable)
выбрал вставить на новый лист(New Worksheet) - назвал этот лист Уникальные сводной таблицей
- поле Данные поместил в область строк
- поле ФИО в область фильтра. Почему? Чтобы удобно было выбирать одно или несколько ФИО и в сводной отображался бы список уникальных месяцев только для выбранных фамилий
В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши -Обновить
Чтобы лучше понимать все действия и научиться обращаться со сводными таблицами настоятельно рекомендую ознакомиться со статьей Общие сведения о сводных таблицах - к ней приложен видеоурок, в котором я наглядно демонстрирую простоту и удобство работы с основными возможностями сводных таблиц.
В приложенном примере помимо описанных приемов, записана чуть более сложная вариация извлечения уникальных элементов формулой и кодом, а именно: извлечение уникальных элементов по критерию. О чем речь: если в одном столбце фамилии, а во втором(
Tips_All_ExtractUnique.xls (108,0 KiB, 18 810 скачиваний)
Также см.:
Работа с дубликатами
Как подсчитать количество повторений
Общие сведения о сводных таблицах
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Создаете список уникальных позиций(как не скажу, т.к. неясно что это), но данная статья поможет, думаю в этом нет проблем.http://www.excel-vba.ru/chto-umeet-excel/kak-najti-znachenie-v-drugoj-tablice-ili-sila-vpr/
Далее к этим позициям притягиваете данные функциями из этой статьи:
ВПР и ПОИСКПОЗ всегда возвращают первое найденное.
Послушайте, а можно как-нибудь формулу с ИНДЕКСОМ внедрить в список проверки, чтобы там получились только уникальные значения? Или же как-нибудь иначе его туда добавить (формульно, не макросам)?
Никак. Формируйте формулой список на отдельном листе, а уже оттуда в вып.список.
Очень жаль. В LibreOffice такая возможность есть (там в вариантах проверки есть пункт "диапазон ячеек", отсеивает дубликаты и еще по алфавиту выстраивает), а в MS почему-то до сих пор нет.
@Дмитрий(Админ)
Среди ответов на вопрос о получении неповторяющегося списка нашел такой рабочий (проверил) ответ. Причем формула - на массив. Можно Вас попросить прокомментировать разницу и что удобнее применять - вариант приведенный Вами с статье или нижеуказанный:
Вопрос - excel как получить список неповторяющихся значений?
Ответ DV-68 - Например такой формулой (в ячейку K8, диапазон данных в I8:I20):
=ИНДЕКС(I$8:I$20;ПОИСКПОЗ(;ИНДЕКС(СЧЁТЕСЛИ(K$7:K7;I$8:I$20););))
Самому для меня в таких вложенных многократно (особенно в массивах) формулах мне пока сложно, а тупо копипастить идею - не хочется.
отлично работает, только у меня макрос выкинул ноли когда вместо "" поставил 0@Дмитрий(Админ)
Женя, а про какой макрос речь? В приведенном в статье коде некуда подставить 0. Если речь про формулу - так все правильно тогда. Там "" ставится специально, чтобы визуально ячейка была пустой.
Здравствуйте! Есть таблица с тремя столбцами: отправитель, получатель, кол-во накладных. Как удалить повторяющиеся сочетания отправителя и получателя, с одновременным суммированием по полю кол-во накладных? Заранее благодарен.
Создать сводную таблицу, перетащить нужные поля в нее (создав заголовки столбцов в исходной таблице)Скрыть ненужные
данные. Преимущество, что если исходный диапазон задать
с запасом, то потом не нужно обновлять данные, они сами
обновятся при введении новых записей.
Здравствуйте! Подскажите пожалуйста, есть ли возможность дополнить Ваш код, чтоб отбирать уникальные по первым двум символам в ячейке?
Евгений, попробуйте так:
спасибо! получается, но у меня числа и вместо 0100, 2000, 3000 код VBA выдает результат 1, 20, 30
Евгений, код здесь ни при чем. Скорее всего у Вас ведущие нули заданы форматом ячеек. Перейдите в любую ячейку и посмотрите в строку формул(там отображается реальное значение). Уверен, Вы увидите там число без ведущих нулей.
да форматом заданы нули выше я описал реальный результат, а видимый 0010, 0020, 0030
я Вас понял! тогда мне нужны уникальные по последним с третьего символа справа
заменить Left(vItem,2)
на mid(vItem,3,2)
во всех случаях считает с лева на право, мне нужно из чисел 0123, 0156, 0253, 1478, 2398 получить 0100, 0200, 1400, 2300
решение нашел сделал вспомогательную колонку где свои числа разделил на сто, по которой делал отбор