Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись - т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами 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).
  • Жмем Ок.

Примечание: если вы хотите поместить результат на другой лист, то просто так указать другой лист не получится. Вы сможете указать ячейку на другом листе, но...Увы и ах...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. После того, как мы ввели эту формулу в C2 мы её должны скопировать и вставить в несколько строк так, чтобы точно отобразить все уникальные элементы. Как только формула в нижних ячейках вернет #ЧИСЛО! - это значит все элементы отображены и ниже протягивать формулу нет смысла. Чтобы ошибку избежать и сделать формулу более универсальной(не протягивая каждый раз до появления ошибки) можно использовать нехитрую проверку:
для 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



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

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

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

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

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

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

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



 
Способ 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 (107,5 KiB, 9 840 скачиваний)

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


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

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

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 101 коммент.
  1. Евгений:

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

  2. Евгений:

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

  3. Юрий:

    Дмитрий воспользовался Вашим кодом для получения уникальных значений . Как получить список из диапазона в моем случае обрабатывается диапазон A2:C. Я прописал код ,а результата нет?
    For Each vItem In Range("A2:C", Cells(Rows.Count, 1).End(xlUp)).Selection.Value

    • Юрий, откуда Selection взялось там, где быть не должно?
      Для диапазона столбца А и С:

  4. Денни:

    Спасибо за макрос, но у меня не получается осуществить его авто выполнение. Все расчёты у меня на втором листе, а на первом следующий код:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then Extract_Unique
    End Sub

    по моему замыслу этот макрос запускает Ваш макрос, когда кликаешь на третью колонку первого листа, но этого не происходит - код выдаёт ошибку. подскажите что нужно исправить в коде?

    • Советую обратиться в форум. Т.к. для определения проблемы надо видеть файл, а не три строки Вашего кода. Здесь выложить файл нет возможности - на форуме есть. Сам код выглядит очень даже верно и должен работать. Где ошибка и какая - не видать...

  5. Виктор:

    Дмитрий,
    Скажите пожалуйста, как отправить второй макрос (по критерию) отбирать данные на другом листе?
    Sub Extract_Unique_for_Criteria()
    Dim rCell As Range, avArr, li As Long, vCriteria
    ReDim avArr(1 To Rows.Count, 1 To 1)
    'Запоминаем критерий
    vCriteria = [A5].Value
    With New Collection
    On Error Resume Next
    For Each rCell In Range(Sheets("Испытания").Range("A23"), Cells(Rows.Count, 2).End(xlUp))
    If rCell.Offset(, 1).Value = vCriteria Then
    'Cells(Rows.Count, 2).End(xlUp) - определяет последнюю заполненную ячейку в столбце B
    .Add rCell.Value, CStr(rCell.Value)
    If Err = 0 Then
    li = li + 1: avArr(li, 1) = rCell.Value
    Else: Err.Clear
    End If
    End If
    Next
    End With
    If li Then [C5].Resize(li).Value = avArr
    End Sub

    Спасибо

  6. Елена:

    подскажите пожалуйста, как написать в коде, что список лежит не в какой то колонке а в определенном месте - указать лист, и выложить его нужно тоже не просто в колонку, а в другой лист в колонку?

  7. dr.tores:

    Подскажите, пожалуйста, как формула =ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));"") работает с массивом, имеющим пустые ячейки.
    Бился всю ночь - не смог найти решения. Может быть есть подобная, но с учётом отбора только заполненных ячеек?

    • С пустыми работает на равне с другими. Пустая ячейка является значением 0 или строкой нулевой длины(""). Именно эти значения по одному и будут вставлены в результирующий список. Чтобы пустые ячейки пропускались необходимо эту часть формулы:
      СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0
      записать так:
      (СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0)*($A$2:$A$51<>"")

  8. Виктор:

    1. В прилагаемом примере на листе "Простое извлечение" в ячейках Д2 и Д3 не формулы массива, в отличии от остальных в диапазоне Д2-Д17.
    2. Всего уникальных значений 12, а строк выделено 16 - это баг или фича?

    • Виктор, да на листе ошибка. Конечно же, должны быть формулы массива.
      Выделено 16 намеренно, чтобы показать какой результат будет у формулы после выбора всех уникальных. В первом столбце - без обхода ошибки, во втором - с обходом.

      P.S. Файл перезалил - теперь формулы массива везде, где должны быть.

  9. Софья:

    Здравствуйте, подскажите, пожалуйста, можно ли, используя код VBA (первый из приведенных в статье), получить уникальные значения по сочетаниям данных из двух столбцов (D и N), а затем перенести данные, так же в 2 столбца на другой лист...
    Указывала
    For Each vItem In Sheets("Stock").Range(Sheets("Stock").Range("D21:N21"), Sheets("Stock").Cells(Sheets("Stock").Rows.Count, 14).End(xlUp)).Value
    и
    If li Then [H4;J4].Resize(li).Value = avArr
    Так тоже писала
    For Each vItem In Sheets("Stock").Range(Sheets("Stock").Range("D21,N21"), Sheets("Stock").Cells(Sheets("Stock").Rows.Count, 14).End(xlUp)).Value
    Результатом получаю одинаковые столбцы H и J на активном листе, с уникальными значениями отдельно по каждому столбцу, который вошел в указанный диапазон (что, естественно, вполне логично)...
    Но вот как указать несвязанный диапазон? Не могу найти на просторах интернета и в ссылках, которые Вы рекомендуете к просмотру.
    Борюсь не один час уже с этим... Буду очень благодарна за помощь.

  10. Александр:

    Добрый день,
    Спасибо за макросы, Ваш сайт выручал неоднократно! По поводу "Извлечение по критерию" макрос в примере ссылается на ячейку D1 а описание написано C2.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти