Excel только уникальные значения в колонке

Хитрости »

1 Май 2011              531940 просмотров

Как получить список уникальных(не повторяющихся) значений?

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

• При помощи встроенных возможностей Excel 2007 и выше
• При помощи Расширенного фильтра
• При помощи формул
• При помощи кодов Visual Basic for Application(VBA) — макросы, включая универсальный код выборки из произвольного диапазона
• При помощи сводных таблиц

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (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).
• Жмем Ок.

Примечание: если вы хотите поместить результат на другой лист, то просто так указать другой лист не получится. Вы сможете указать ячейку на другом листе, но…Увы и ах…Excel выдаст сообщение, что не может скопировать данные на другие листы. Но и это можно обойти, причем довольно просто. Надо всего лишь запустить Расширенный фильтр с того листа, на который хотим поместить результат. А в качестве исходных данных выбираем данные с любого листа — это дозволено.

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

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

Способ 2: Формулы

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

А

(

А1:А51

, где

А1

— заголовок). Выводить список мы будем в столбец

С

, начиная с ячейки

С2

. Формула в

C2

будет следующая:

{=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))}
{=INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1))}
Детальный разбор работы данной формулы приведен в статье: Как просмотреть этапы вычисления формул

Надо отметить, что эта формула является формулой массива. Об этом могут сказать фигурные скобки, в которые заключена данная формула. А вводится такая формула в ячейку сочетанием клавиш —

Ctrl

+

Shift

+

Enter

(при этом сами скобки вводить не надо — они появятся сами после ввода формулы тремя клавишами

Ctrl

+

Shift

+

Enter

). После того, как мы ввели эту формулу в

C2

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

#ЧИСЛО!(#NUM!)

— это значит все элементы отображены и ниже протягивать формулу нет смысла. Чтобы ошибку избежать и сделать формулу более универсальной(не протягивая каждый раз до появления ошибки) можно использовать нехитрую проверку:

для Excel 2007 и выше:
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»)}
{=IFERROR(INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»)}
для Excel 2003:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»;ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1)))}
{=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»;INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1)))}

Тогда вместо ошибки 

#ЧИСЛО!(#NUM!)

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

Для пользователей Excel 2021 выше, а так же пользователей Excel 365(с активной подпиской) — использовать формулы для извлечения уникальных элементов проще простого. В этих версиях появилась функция

УНИК(UNIQUE)

, которая как раз получает список уникальных значений на основании переданного диапазона:

=УНИК($A$2:$A$51)
=UNIQUE($A$2:$A$51)

Что самое важное в данном случае — это функция динамического массива и вводить её надо только в одну ячейку C2, а результат она поместит сама в нужное количество ячеек.

Способ 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?

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

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

Способ 4: Сводные таблицы

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

• Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка(Insert) -группа Таблица(Table) —Сводная таблица(PivotTable)
• В диалоговом окне Создание сводной таблицы(Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
• указываем место размещения Сводной таблицы:
  • На новый лист (New Worksheet)
  • На существующий лист (Existing Worksheet)
• подтверждаем создание нажатием кнопки OK

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

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

В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши —Обновить(Refresh) или вкладка Данные(Data) —Обновить все(Refresh all) —Обновить(Refresh). А если исходные данные пополняются динамически и того хуже — надо будет заново указывать диапазон исходных данных. И еще один минус — данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.

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

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

Скачать пример:

  Tips_All_ExtractUnique.xls (108,0 KiB, 18 430 скачиваний)

Также см.:
Работа с дубликатами
Как подсчитать количество повторений
Общие сведения о сводных таблицах

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ(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 и выводит все позиции списка с номерами в отдельную таблицу:

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

• Выделение дубликатов по одному или нескольким столбцам в списке цветом
• Запрет ввода повторяющихся значений
• Извлечение уникальных значений при помощи надстройки PLEX

Фильтр уникальных значений или удаление повторяющихся значений

​Смотрите также​​ (или после вставки)​, собственно увидел, что​ikki​ и фильтр-Дополнительно-Ставим галку​ Define)​ предыдущих способах при​ список беспорядочно повторяющихся​ Рога и копытца​Для создания списка уникальных​ всех повторяющиеся значений​ а затем разверните​стиль​ используется для поиска​Поскольку данные будут удалены​группа​ списка уникальных значений.​Примечание:​ в эксель, в​ решается просто, а​: эт куда сабрался?​ Только уникальные записи-Переключатель​

​:​ изменении исходного списка​ названий компаний:​4. Установите серый флажок​

• ​ значений введем в​ исходной таблицы, а​​ узел во всплывающем​ ​щелкните маленькую стрелку​ дубликатов «ключ» —​​ окончательно, перед удалением​
  

• ​Сортировка и фильтр​ Есть важные различия,​​Мы стараемся как​ столбце бы остались​ ​ сейчас сомневаюсь, когда​ ​ста-ять!!!​​ скопировать результат в​
  

• ​диапазону номеров (A1:A100) -​ нужно будет заново​​Выбираем в меню​​ Выделите компанию​​ ячейку​​ только из тех,​​ окне еще раз​​Условное форматирование​

Сведения о фильтрации уникальных значений и удалении повторяющихся значений

​ значение ОБА Столбец1​ повторяющихся значений рекомендуется​).​ однако: при фильтрации​ можно оперативнее обеспечивать​ только уникальные значения?​ разговор пошел про​рановато ещё.​ другое место. Указываете​ имя​ запускать​Данные — Фильтр -​5. В исходной​G5​ у которых соответствующие​

​. Выберите правило​и затем щелкните​ & Столбец2.  Если дубликат​ скопировать исходный диапазон​В поле всплывающего окна​ уникальных значений повторяющиеся​ вас актуальными справочными​Ewe 2e¶ coлнышкоcbetиточеньярко*​ макросы от старожил​Serge_007​ диапазоны Исходный и​NameCount​Расширенный фильтр​ Расширенный фильтр​ таблице будут выделены​формулу массива:​ значения в соседних​ и нажмите кнопку​Элемент правила выделения ячеек​ находится в этих​ ячеек или таблицу​

​Расширенный фильтр​ значения будут видны​​ материалами на вашем​: В Excel выделить​ и ветеранов. Вот​: Пора-пора…​ Куда поместить результат.​всему списку с номерами​или жать на​(Data — Filter -​ 2 строки содержащие​=ЕСЛИОШИБКА(ИНДЕКС(Компании;​

Фильтрация уникальных значений

​ столбцах удовлетворяют определенному​

1. ​Изменить правило​и выберите​ столбцах, затем всей​ в другой лист​

2. ​выполните одно из​​ только временно. Тем​​ языке. Эта страница​​ диапазон ячеек с​​ я и подумал,​​Простую задачу не​​astradewa​

   

3. ​ (A1:B100) — имя​​ кнопку​​ Advanced Filter)​ название компании ООО​

​ПОИСКПОЗ(0;ЕСЛИ(E$5=Тип_контракта;0;1)+СЧЁТЕСЛИ($G$4:G4;Компании);0)​ критерию.​, чтобы открыть​

• ​Повторяющиеся значения​​ строки будут удалены,​​ или книгу.​

​ указанных ниже действий.​ не менее удаление​

• ​ переведена автоматически, поэтому​​ данными, «Данные» -​​ может чего не​

• ​ могу решить…​​: Serge_007, добрый вечер,​​NameList​Удаление дубликатов.​

• ​.​​ Рога и копытца​ ​);»»)​Разовьем идеи, изложенные в​ всплывающее окно​.​​ включая другие столбцы​ ​Выполните следующие действия.​

• ​Чтобы отфильтровать диапазон ячеек​​ повторяющихся значений означает,​​ ее текст может​​ «Фильтр» — «Расиренный​​ догоняю?​

​astradewa​ читая форумы Excel,​Теперь осталось выбрать из​

Удаление повторяющихся значений

​Итак, снова имеем список​Получаем окно:​ с типом контракта​При выборе в ячейке​ статье Отбор уникальных​Изменение правила форматирования​Введите значения, которые вы​ в таблицу или​Выделите диапазон ячеек или​ или таблицы в​ что вы окончательное​ содержать неточности и​ фильтр» — поставить​alx74​

​: ikki, доброе утро,​ в том числе​ списка​ беспорядочно повторяющихся элементов.​В нем:​ Рублевый​E5​

​ значений (убираем повторы).​

1. ​.​ хотите использовать и​ диапазон.​ убедитесь, что активная​

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

   

3. ​NameList​ Например, такой:​

   • ​Выделяем наш список компаний​​Созданная структура является разновидностью​​значения Валютный, все​Пусть исходная таблица содержит​

   • ​В разделе​ нажмите кнопку Формат.​​Нажмите кнопку​​ ячейка находится в​

   • ​Выберите​Повторяющееся значение входит в​​ нас важно, чтобы​​ значения»​

     ​ и скрин.​ подойдут, если не​ понял что Вы​все элементы имеющие​Первая задача — пронумеровать​ в​​ Связанного списка.​​ компании, НЕ поставляющие​​ три столбца: один​​выберите тип правила​

     ​Расширенное форматирование​​ОК​ таблице.​фильтровать список на месте​ котором все значения​ эта статья была​Валерий​Serge_007​ жалко, можно и​ один из главных​ номер — это​ всех уникальных представителей​Исходный диапазон (List Range)​СОВЕТ:​ по валютным контрактам,​ с названиями компаний,​нажмите кнопку​Выполните следующие действия.​, и появится сообщение,​

4. ​На вкладке​​.​​ в по крайней​ вам полезна. Просим​: Есть несколько методов.​: Это не совсем​ то и другое​ «формулистов»​​ и будут наши​​ списка, дав каждому​.​

5. ​Другим подходом к​ будут проигнорированы,​ другой с указанием​Форматировать только уникальные или​

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

​Выделите одну или несколько​ чтобы указать, сколько​данные​Чтобы скопировать в другое​ мере одна строка​ вас уделить пару​1. использовать расширенный​ верно​ (для общего т.с.​, поэтому обратился​ уникальные представители. Сделать​ свой номер (столбец​

Условное форматирование уникальных или повторяющихся значений

​Ставим переключатель в положение​​ решению этой задачи​и список уникальных значений​ типа контракта (валютный​ повторяющиеся значения​ ячеек в диапазоне,​

​ повторяющиеся значения были​

​нажмите кнопку​

1. ​ место результаты фильтрации:​ идентичны всех значений​ секунд и сообщить,​ фильтр и там​

2. ​ТС не заполнил​​ развития)​​ к Вам.​​ это можно в​​ А на рисунке).​​Скопировать результат в другое​​ является использование Сводных​​ будет сформирован только​​ или рублевый), третий​​.​​ таблице или отчете​
   

3. ​ удалены или остаются​Удалить повторения​Нажмите кнопку​

​ в другую строку.​

​ помогла ли она​

1. ​ поставить галочку «Только​ пример, но интересуют​Спасибо за помощь.​А вопрос -​

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

3. ​ вам, с помощью​ уникальные записи»​

   • ​ значения из ДИАПАЗОНА​Alex_ST​​ переделать формулу так,​​ соседних столбцов, введя​ в ячейку А2​​ another location)​​Начиная с 2007-й версии​

   • ​ которые имеют контракты​ по контракту. Столбец​Формат все​​На вкладке​​ Нажмите кнопку​Работа с данными​.​ зависит от того,​​ кнопок внизу страницы.​ ​2. использовать функцию​​ в столбце​​: Александр,​ чтобы она выбирала​ туда вот такую​ и копируем затем​и указываем пустую​ функция удаления дубликатов​​ в валюте. Также​ с названиями компаний​​Измените описание правила​​Главная​ОК​​).​​В поле​

4. ​ что отображается в​​ Для удобства также​​ «создать сводную таблицу».​​RAN​astradewa​​ и выводила в​

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

6. ​, чтобы закрыть​​Выполните одно или несколько​​Копировать​ ячейке, не базового​​ приводим ссылку на​​А после этого​

7. ​: Я думаю, дело​просит​ столбец уникальные значения​ функцией ВПР (VLOOKUP) и​ следующую формулу:​Включаем (самое главное!) флажок​​ найти ее можно​​ продажи и количество​ т.к. (см. файл​уникальные​Стили​ сообщение.​​ следующих действий.​​введите ссылку на​

support.office.com

Отбор уникальных значений на основании значений из соседнего столбца в MS EXCEL

​ значения, хранящегося в​ оригинал (на английском​ копировать в буфер​ в неудачном примере.​формулами​ из диапазона А2:Е20​ скопировав ее вниз​=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)​Только уникальные записи​ на вкладке​ продаж по каждой​ примера).​или​щелкните стрелку для​U тменить отменить изменения,​В разделе​

​ ячейку.​ ячейке. Например, если​ языке) .​

​ из Excel​Заполнен 1 столбец,​, поэтому код VBA​ (у The_Prist формула​ на весь столбец:​В английской версии это​(Uniqe records only)​Данные — Удаление дубликатов​ компании (столбцы​Создадим таблицу, состоящую только​повторяющиеся​Условного форматирования​

Задача

​ щелкните (или нажать​столбцы​Кроме того нажмите кнопку​ у вас есть​В Excel существует несколько​Ёжж​ а должно быть​ его вряд ли​ извлекает уникальные из​=ЕСЛИ(МАКС(NameCount)​

Решение

​ будет:​и жмем​ (Data — Remove​​HI​​ из строк с​​.​​и выберите пункт​​ клавиши Ctrl +​​выберите один или несколько​Свернуть диалоговое окно​ то же значение​ способов фильтр уникальных​: Добрый день! Подскажите,​ 4.​ устроит.​ столбца в столбец,​

​или в английской версии​​=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)​​ОК​ Duplicates)​).​

​ уникальными названиями компаний.​Нажмите кнопку​Управление правилами​​ Z на клавиатуре).​​ столбцов.​
​временно скрыть всплывающее​
​ даты в разных​
​ значений — или​

​ как выбрать уникальные​​alx74​​Но на всякий​ а надо из​ Excel:​Эта формула проверяет сколько​

​.​:​1. Выберите в желтой​ Уникальные значения будем​Формат​, чтобы открыть​Нельзя удалить повторяющиеся значения​Чтобы быстро выделить все​ окно, выберите ячейку​​ ячейках, один в​​ удаление повторяющихся значений:​

Тестируем

​ значения по двум​: Теперь понял, спасибо.​
​ случай, если формулами​​ диапазона).​​=IF(MAX(NameCount)​ раз текущее наименование​Получите список без дубликатов:​В открывшемся окне нужно​
​ ячейке значение Рублевый;​ выбирать не из​для отображения во​ всплывающее окно​ из структуры данных,​
​ столбцы, нажмите кнопку​​ на листе и​​ формате «3/8/2006», а​Чтобы фильтр уникальных значений,​ столбцам с более​ (невнимательно прочитал первый​
​ так никто и​​Serge_007​​Эта формула проходит сверху​ уже встречалось в​Если требуется искать дубликаты​
​ с помощью флажков​2. В столбце​​ всех названий компаний,​​ всплывающем окне​Диспетчер правил условного форматирования​

​ структурированный или, в​Выделить все​
​ нажмите кнопку​ другой — как​ нажмите кнопку​ поздней датой? Данные​ пост А2:Е20).​ не решит, то​:​

​ вниз по столбцу​ списке (считая с​

​ не по одному,​
​ задать те столбцы,​G​ а сначала из​Формат ячеек​

excel2.ru

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

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

​.​ котором содержится промежуточные​.​Развернуть​ «8 мар «2006​​данных >​ с более ранней​astradewa​​ в «Готовых решениях»​

​astradewa​NameCount​ начала), и если​ а по нескольким​ по которым необходимо​будет выведен список​ компаний с типом​.​Выполните одно из действий,​ итоги. Чтобы удалить​Чтобы быстро удалить все​.​ г. значения должны​Сортировка и фильтр >​

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

​ датой в новой​: Спасибо Всем ответившим​ я выкладывал макрос​, спасибо за лестное​и выводит все​ это количество =1,​ столбцам, то можно​​ обеспечивать уникальность. Т.е.​​ названий компаний с​​ контракта Валютный, затем​​Выберите номер, шрифт, границы​

​ указанных ниже.​ дубликаты, необходимо удалить​ столбцы, нажмите кнопку​Установите флажок​

​ быть уникальными.​​ Дополнительно​ таблице не нужны.Исходная​​ за помощь, буду​ NoDups_in_Range​​ мнение, но думаю​​ позиции списка с​

​ т.е. элемент встретился​

• ​ предварительно склеить их​ если включить все​​ типом контракта –​​ — Рублевый.​
• ​ и заливка формат,​​Чтобы добавить условное форматирование,​ структуры и промежуточные​Снять выделение​​только уникальные записи​Установите флажок перед удалением​
• ​.​​ таблица будет увеличиваться.​​ разбираться в предоставленном​​Michael_S​​ что здесь я​​ номерами в отдельную​

​ первый раз -​

​ в один, сделав,​ флажки, то будут​ Рублевый:​Для удобства создадим динамические​ который нужно применять,​ нажмите кнопку​ итоги. Для получения​.​, а затем нажмите​​ дубликаты:​​Чтобы удалить повторяющиеся значения,​

​Файл прикрепил.​ материале.​: Да не такая​ не помогу…​

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

​ таблицу:​ дает ему последовательно​ своего рода, составной​ удалены только полностью​- в исходной​ диапазоны: Компании (названия​ если значение в​Создать правило​ дополнительных сведений отображается​Если диапазон ячеек или​кнопку ОК​Перед удалением повторяющиеся​ нажмите кнопку​KuklP​PS: alx74-Расширенный фильтр​​ она уж и​​Болезнь не позволяет​astradewa​​ возрастающий номер.​

​ ключ с помощью​ совпадающие строки. Если​ таблице Условным форматированием​

​ компаний из столбца​ ячейке удовлетворяет условию​для отображения во​ Структура списка данных​ таблица содержит много​.​ значения, рекомендуется установить​данные > Работа с​: Сортируете по первым​ не подходит потому​

​ простая. Здесь уже​

​ мне использовать мозг​: На сайте www.excel-vba.ru​

​Для упрощения адресации дадим​

​ функции​ включить только флажок​ будут выделены строки​А​ и нажмите кнопку​ всплывающем окне​ на листе «и»​ столбцов, чтобы выбрать​Уникальные значения из диапазона​ для первой попытке​

​ данными​ трем полям(дата от​ что слишком много​ решали , и​ на полную катушку,​ The_Prist (Щербаков Дмитрий)​ нашим диапазонам (например,​СЦЕПИТЬ (CONCATENATE)​ заказчик, то останется​​ с типом контракта​), Тип_контракта (столбец​ОК​​Создание правила форматирования​ удалить промежуточные итоги.​ несколько столбцов только​​ скопирует на новое​ выполнить фильтрацию по​ ​>​ новых к старым).​​ телодвижений​

• ​ формулами, и макросом.​ а стандартного решения​​ нашёл формулу вывода​
• ​ исходя из того,​:​​ только по одной​

​ Рублевый;​B​​. Вы можете выбрать​​.​Примечание:​ может проще нажмите​ место.​ — или применить​Удалить повторения​ Далее данные -​, а надо​alx74​ формулами Вашей задачи​ уникальных значений из​ что в списке​

​Тогда дальнейшая задача будет​

​ строке для каждого​- в столбце​

​) и Продажи (столбец​

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

planetaexcel.ru

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

​ удалить дубликаты. Все.​​ чтобы при заполнении​: Добрый день. Подскажите,​ нет​ столбца в назначенный​ может быть до​ сводиться к поиску​ заказчика и т.д.​H​С​ Форматы, которые можно​ лист или таблица​ области «Значения» отчета​Снять выделение всех​

​ на значения в​​ — для подтверждения​Чтобы выделить уникальные или​
​ Можете записать макрорекордером.​ таблицы данными​ может я чего​astradewa​ столбец. Помогите переделать​ 100 элементов) имена.​ дубликатов уже в​Если у вас Excel​

​будут выведены суммарные​​). Если в таблицу​ выбрать, отображаются на​ в списке​ сводной таблицы по​и выберите в разделе​ диапазоне ячеек или​ добиться таких результатов,​​ повторяющиеся значения, команда​_Boroda_​
​автоматом в назначенный​ не понимаю: Почему​: Serge_007, спасибо за​ формулу так, чтобы​ Это можно сделать​ одном столбце.​ 2003 или старше,​ продажи отобранных компаний​ будет добавлена новая​ панели​Показать правила форматирования для​

​ уникальным или повторяющимся​​столбцы​​ таблице — единственный​​ предполагается, что уникальные​Условного форматирования​: Так нужно? Немассивные​ столбец выводились уникальные​
​ нельзя решить Расширенным​ участие, желаю Вам​ она выбирала и​ в новых версиях​Чуть более сложный способ,​ то для удаления​

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

​ эффект. Другие значения​​ значения.​
​в группе​ формулы​

​ значения.​​ фильтром? Пример простой​ скорейшего выздоровления и​

​ выводила в столбец​​ Excel на вкладке​​ чем первые два,​
​ дубликатов и вытаскивания​

​ контракта);​​ значения будут автоматически​
​.​ начинается. При необходимости​

​Быстрое форматирование​​Примечание:​ вне диапазона ячеек​Выполните следующие действия.​стиль​Для умной таблицы​Ещё раз всем​ и фильтр там​
​ вообще кавказского здоровья​

​ уникальные значения из​​Формулы — Диспетчер имен​​ но зато -​​ из списка уникальных​​- в столбце​​ включены в Динамические​Имеется таблица, состоящая их​ выберите другой диапазон​
​Выполните следующие действия.​ Данные будут удалены из​ или таблице не​Выделите диапазон ячеек или​на вкладке «​200?’200px’:»+(this.scrollHeight+5)+’px’);»>ПРОСМОТР(;-1/ЕОШИБКА(ПОИСКПОЗ(Таблица2[[Покупатель]:[Покупатель]]&Таблица2[[Товар]:[Товар]];$F$2:$F2&$G$2:$G2;));Таблица2[Покупатель])​ Большое спасибо.​

​ работает. Пробовал вчера​​и долголетия​ диапазона А2:Е20, если​ (Formulas — Name​ динамический, т.е. с​ (неповторяющихся) элементов можно​

​I​​ диапазоны и нижеследующие​ нескольких столбцов. В​ ячеек, нажав кнопку​Выделите одну или несколько​ всех столбцов, даже​ будет изменить или​ убедитесь, что активная​Главная​если без протягиванияКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>ПРОСМОТР(;-1/ЕОШИБКА(ПОИСКПОЗ(Таблица2[Покупатель]&Таблица2[Покупатель];$F$2:$F2&$G$2:$G2;));Таблица2[Покупатель])​В буфере хранится колонка​ и в посте​

​Serge_007​​ это возможно. Спасибо​​ manager)​​ автоматическим пересчетом, т.е.​ использовать​будет выведено количество​ формулы не придется​ одном из столбцов​Свернуть​ ячеек в диапазоне,​ если вы не​ переместить.  При удалении​

​ ячейка находится в​​».​Для обычной таблицыКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>ПРОСМОТР(;-1/$D$3:$D$99/ЕОШИБКА(ПОИСКПОЗ($A$3:$A$99&$B$3:$B$99;$F$2:$F2&$G$2:$G2;));A$3:A$99)​

​ со значениями, скопированными​​ 2 написал об​: Спасибо!​
​ за помощь.​или в старых​ если список редактируется​Расширенный фильтр (Advanced Filter) ​

​ продаж (кол-во контрактов​​ модифицировать.​ имеются повторяющиеся текстовые​
​во всплывающем окне​ таблице или отчете​ выбрали всех столбцов​

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

​ из экселя. Многие​​ этом, правда ТС​Но видимо пора​alx74​ версиях — через​
​ или в него​из меню (вкладки)​ выбранного типа).​Также создадим в ячейке​​ значения. Создадим список,​относится к​ сводной таблицы.​
​ на этом этапе.​ в первое значение​Нажмите кнопку​
​ удаление повторяющихся значений​ вовнутрь ЕСЛИОШИБКА засунуть​

excelworld.ru

Как оставить уникальные значения в колонке в эксель?

​ значение в этой​ промолчал.​ на свалку​: Что мешает использовать​ меню​ дописываются еще элементы,​Данные (Data)​3. Теперь в​E5​ состоящий только из​временно скрыть ее.​

​На вкладке​​ Например при выборе​ в списке, но​данные > Дополнительно​ являются две сходные​Ёжж​ колонке повторяются. Можно​alx74​

​ikki​​ Расширенный фильтр?​
​Вставка — Имя -​ то они автоматически​.​ зеленой ячейке​
​Выпадающий (раскрывающийся) список​ уникальных текстовых значений.​
​ Выберите новый диапазон​Главная​ Столбец1 и Столбец2,​

выбрать уникальные значения по двум столбцам с поздней датой (Макросы/Sub)

​ других идентичных значений​​(​ задачи, поскольку цель​: Большое спасибо!​ ли как-то сделать,​:​: макрос или UDF​Для 2007 и​ Присвоить​ проверяются на уникальность​
​Предположим, что у нас​

​Е12​​ с двумя значениями:​ Уникальные значения будем​ ячеек на листе,​в группе​ но не Столбец3​ удаляются.​

​в​​ — для представления​Вопрос снят.​
​ чтобы при вставке​​Serge_007​
​ не подходят?​
​ 2010: Вкладка Данные-Сортировка​
​(Insert — Name -​ и отбираются. В​

​ имеется вот такой​​выберите компанию ООО​
​ Валютный и Рублевый.​

excelworld.ru

​ выбирать не из​