0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро перейти к следующему уникальному значению в столбце Excel?

Как быстро перейти к следующему уникальному значению в столбце Excel?

Фильтрация уникальных значений и удаление повторяющихся значений — две тесно связанные задачи, поскольку в результате их выполнения получается список уникальных значений. Однако между этими двумя задачами есть важное различие. При фильтрации уникальных значений повторяющиеся значения временно скрываются, а при удалении повторяющихся значений они удаляются без возможности восстановления. Значение считается повторяющимся, если все значения в строке в точности совпадают со значениями в другой строке. Повторяющиеся значения определяются значением, отображаемым в ячейке, а не значением, хранящимся в ячейке. Например, если разные ячейки содержат одинаковые значения даты в разных форматах («08.12.2010» и «8 декабря 2010»), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование перед удалением дубликатов, чтобы убедиться, что вы получите ожидаемый результат.

Примечание: Если формулы в ячейках разные, а значения одинаковые, эти значения считаются повторяющимися. Например, если ячейка A1 содержит формулу =2-1, а ячейка A2 содержит формулу =3-2, и к ячейкам было применено одинаковое форматирование, эти значения считаются дубликатами. Одинаковые значения с разным числовым форматированием не считаются дубликатами. Например, если значение в ячейке A1 отформатировано как 1.00, а значение в ячейке A2 отформатировано как 1, эти значения не являются дубликатами.

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

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

На вкладке Данные в группе Сортировка и фильтр нажмите на кнопку Дополнительно.

Кнопка "Дополнительно"

Сделайте одно из следующих действий

Необходимые действия

Фильтруйте диапазон ячеек или таблицу

Выберите диапазон ячеек и нажмите кнопку Filter list (Фильтровать список).

Копирование результатов фильтра в другое место

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

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

Нажмите OK, чтобы установить флажок Только уникальные записи.

Дополнительные параметры

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

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

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

Нажмите Удалить дубликаты на вкладке Данные в разделе «Обработка данных».

Кнопка "Удалить дубликаты"

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

Совет: Если диапазон ячеек или таблица содержит много столбцов, и вы хотите выбрать только некоторые из них, снимите флажок Выделить все и выберите только нужные столбцы.

Читайте так же:
Как ввести одни и те же данные / формулу в несколько ячеек одновременно в Excel?

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

Выберите одну или несколько ячеек в интервальном отчете, таблице или сводной таблице.

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

В диалоговом окне Создание правила форматирования выберите необходимые параметры и нажмите OK.

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

Выделите одну или несколько ячеек в интервальном отчете, таблице или итоговой таблице.

На вкладке Главная в группе Стили нажмите Условное форматирование и выберите Создать правило.

В списке Стиль должно быть установлено значение Классический, а в списке Форматирование одиночных или повторных значений — Форматирование одиночных или повторных значений.

Выберите Unique или Repeated из списка всех значений в выбранном диапазоне.

В списке Формат с помощью выберите нужную опцию.

Редактирование существующего правила для изменения условного форматирования, применяемого для уникальных или повторяющихся данных.

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

Выберите Управление правилами в группе Условное форматирование на вкладке Главная в группе Стили.

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

Чтобы отредактировать правило, выберите кнопку Редактировать правило.

Выберите необходимые параметры и нажмите OK.

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

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

В разделе Сортировка и фильтрация на вкладке Данные нажмите стрелку рядом с Фильтр и выберите Расширенный фильтр.

Сделайте одно из следующих действий.

Необходимые меры

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

Отфильтруйте список, выделив диапазон ячеек и нажав Фильтровать список на месте.

Копировать результаты фильтрации в другое место

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

Примечание: При копировании результатов фильтрации в другое место будут скопированы отдельные значения из выбранного диапазона. Исходные данные не будут изменены.

Нажав OK, вы будете видеть только уникальные записи.

Дополнительные параметры

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

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

Читайте так же:
Как автоматически копировать и вставлять ячейку в текущий лист или с одного листа на другой в Excel?

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

В разделе Услуги на вкладке Данные щелкните Удалить дубликаты.

Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Remove Duplicates (Удалить дубликаты).

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

Совет: Если диапазон ячеек или таблица содержат несколько столбцов, и вы хотите выбрать только некоторые из них, снимите флажок Выбрать все и выберите только нужные столбцы.

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

Затем выберите ячейки в диапазоне, таблице или сводной таблице отчета, которые необходимо просмотреть.

На вкладке Главная в разделе Формат нажмите стрелку рядом с Условное форматирование, выберите Правила выбора ячеек, а затем выберите Повторяющиеся значения.

Нажав OK, вы можете выбрать нужные вам параметры.

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

Выделение одной или нескольких ячеек в интервальном отчете, таблице или итоговой таблице.

Выберите кнопку Условное форматирование на вкладке Формат, нажмите на стрелку рядом с ней, а затем выберите пункт Создать правило.

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

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

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

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

Выберите диапазон, таблицу или сводный отчет по таблице.

На вкладке Главная в разделе Формат нажмите стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.

В меню Показать правила форматирования для соответствующего форматирования выберите нужный вам лист или таблицу.

VBA Excel. Отбор уникальных значений с помощью Collection

При написании макросов для манипулирования данными в Excel VBA иногда возникает необходимость выбрать уникальные значения из списка повторяющихся элементов. Для этого можно использовать следующий код:

Теперь уникальные значения выбраны. Коллекция заполнена уникальными элементами.

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

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

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

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

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

При необходимости сортируем полученный список в столбце "В":

Он также может отображать количество найденных уникальных элементов, при условии, конечно, что элемент управления Label1 был добавлен в UserForm1 :

Если вам нужно заполнить ListBox или ComboBox отсортированным списком, вы можете добавить его элементы из листа Excel после сортировки, как это демонстрирует Range(Cells(1, 2), Cells(i, 2)).

Читайте так же:
Как быстро отсортировать IP-адрес от меньшего к большему в Excel?

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

Если вы хотите увидеть, как работает этот код, вы можете скачать демонстрационный файл.

Узнайте, как удалить повторяющиеся значения из диапазона ячеек с помощью Range в VBA Excel. Используя объект Dictionary, удалите дублирующиеся значения с помощью метода RemoveDuplicates.

28 комментариев для “VBA Excel. Отбор уникальных значений с помощью Collection”

Такое возможно?

у меня не получается, помогите пожалуйста, заранее спасибо!
С приветом Холмурод.

Привет, Холмурод. В примере указан диапазон из столбца «B»: Range(Cells(1, 2), Cells(i, 2)). Замените его на диапазон из столбца «A»: Range(Cells(1, 1), Cells(i, 1)).

Привет Евгений.
в ListBox1 список покажет но Sort Key1:=Range(«A30») в ячейке A30 ничего нет. Сделал вот так:

Holmurod, вы хотите, чтобы список, вставленный в рабочий лист, начинался с ячейки A30? Если да, то вывод на рабочий лист должен начинаться с ячейки 30:

И указать диапазон, начиная с ячейки 30, для сортировки

Евгений огромное Вам спасибо.
просто получилось.

Добрый день,
В приведенном примере создаётся коллекция уникальных значений. Все работает. Только я не пойму чем, в какой строке определяется уникальность значений этих значений? Где происходит сравнение ? Ведь, наверное, в коллекцию должны записаться все элементы из Range(«A1:A20») ?

Привет, Антон.
Уникальность значений проверяется в строке добавления очередного элемента в коллекцию:

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

Простое и элегантное решение поиска уникальных значений!
Спасибо!

Добрый день!
Странно у меня не срабатывает данный код и выдает ошибку «This key already associated with element of this collection»

Привет, Камалджан!
Строка

Должен предшествовать строке

Чтобы избежать ошибок типа «This key is already associated with an item in this collection», как в исходном коде из данной статьи

Евгений,
Снимаю шляпу…..
Тонко придумано.
Спасибо

Антон, это решение я нашел у Джона Уокенбаха в его книге «Excel 2010: Профессиональное программирование VBA».

Евгений, хорошо было бы привести пример со словарем в дополнение к коллекции.
И рассказать как-нибудь про метод Range.RemoveDuplicates

Привет, Фарин!
Согласен с вашими пожеланиями, принял их к сведению.

Добрый день!
Скажите пожалуйста, чувствителен ли данный метод добавления уникальных значений к регистру?
Я делаю выборку уникальных значений по полю, и значения «Сибирь» и «сибирь» почему то присваиваются одному значению «сибирь».
Может быть чувствительность как то отключается/включается?
Подскажите пожалуйста )

Читайте так же:
Как автоматически пронумеровать столбец в Excel?

Добрый день, Андрей!
Ключи объекта Collection нечувствительны к регистру. Если вы хотите отобрать уникальные значения с учетом регистра, используйте код с объектом Dictionary.

Евгений, спасибо, что перезвонили! Взял на вооружение )

Привет, очень интересный метод, но работает ли он только на простом списке? Т.е. можно ли применить этот метод к таблице с 2-3 столбцами, где только один столбец проверяется на уникальность?

Здравствуйте, Вольдемар!
Можно удалить из таблицы строки с неуникальными значениями в одном столбце, проверяя уникальность значений снизу вверх:

Да, это неплохая идея, спасибо Евгений, я сам об этом не подумал.

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

I = 10 — 1 означает, что столбец будет заполнен с 10-й строки выражения. Если заполнение должно начинаться с первой ячейки столбца, это выражение следует исключить из процедуры.

В выражении Worksheets(«Лист1»).Cells(i, «D») = myElement :

  • «Лист1» – наименование ярлыка листа, в ячейки которого будут вставляться уникальные значения;
  • «D» – буквенное обозначение столбца, который будет заполняться (можно заменить на числовое значение: 1 вместо «A», 2 вместо «B» и т.д.).

Здравствуйте, помогите пожалуйста, мне нужен код для получения уникального списка по критерию в соседнем столбце

Добрый день. Помогите пож. «Играюсь» с Collection. При попытке вывести кол-во членов коллекции через Collection.Count Программа выдает кол-во строк указанного диапазона(( , а не кол-во знаков в диапазоне. И только повтором типа myCollection.Add CStr(myCell.Value), CStr(myCell.Value) MsgBox выдает кол-во членов коллекции корректно. Почему??((

Здравствуйте, Дитрий!
Пример кода, пожалуйста.

Евгений, добрый день! Только начинаю изучать VBA. Вопросов. Постараюсь в будущем по пустякам не отвлекать).
По существу дела:

Хочется узнать:
1. Почему только повторение CStr(c.Value) дает кол-во уникальных знаков, в противном случае дает значение 20 (я почему жду в результате кол-во всех знаков, но без учета пустых ячеек);
2. К моему удивлению макрос выдает данные только с типом возвращ. значений CStr;
3. И если не сложно подсказать куда двигаться: Возможно ли в коллекцию отбирать уже данные с нужными параметрами или для этого нужно использовать другие инструменты?
За ранее, огромное спс.

Добрый день, Дитрий

1. Первое выражение CStr(c.Value) определяет элемент для записи в коллекцию, второе — ключ для добавления. Ключ в коллекции не может быть неуникальным, поэтому если он уже есть в коллекции, генерируется ошибка, и цикл переходит к обработке следующей ячейки. Чтобы программа не останавливалась при возникновении ошибки, перед циклом добавляется следующая строка: On Error Resume Next .

Если элементы коллекции не преобразованы в текст, они могут содержать различные типы данных: MyColl. Добавить c. Значение .

3. Я использую коллекции только для отбора уникальных значений, в остальных случаях — массивы. Копирование значений из диапазона ячеек в массив и обратно.

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

Начиная с версии 2007 функция удаления дубликатов является стандартной — вы найдете ее на вкладке Данные — Удалить дубликаты:

Читайте так же:
Как в Excel автоматически вставлять знак равенства в ячейки с числами?

remove-duplicates1.png

В открывшемся окне с помощью флажков укажите столбцы, уникальность которых должна быть гарантирована. Другими словами, если выбраны все поля, будут удалены только те строки, которые полностью совпадают. Если выбрано только поле «клиент», то для каждого клиента останется только одна строка и т.д.

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

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

Предположим, у нас есть список произвольно повторяющихся названий компаний:

Выберите в меню Данные — Фильтр — Расширенный фильтр. Открывается окно:

  • Выберите наш список компаний в диапазоне списка.
  • Установите переключатель в положение Копировать в другое место и укажите пустую ячейку.
  • Включите (это самое важное!) флажок Только записи Uniqe и нажмите OK.

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

Функция CONCATENATE может использоваться для объединения нескольких столбцов в один, создавая своего рода составной ключ при поиске дубликатов:

remove-duplicates2.png

Далее мы будем искать дубликаты, уже имеющиеся в одном столбце.

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

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

Итак, у нас снова есть список случайно повторяющихся элементов. Например, это:

Первая задача — пронумеровать все уникальные члены списка, присвоив каждому свой номер (столбец A на рисунке). Следующую формулу можно поместить в ячейку A2, а затем скопировать ее до самого низа:

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

Считая от самого начала списка, эта формула определяет, сколько раз элемент уже появлялся в списке, и если это значение = 1, это означает, что элемент уже появлялся однажды. элемент встретился впервые, она присваивает ему возрастающее число.

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

  • диапазону номеров (A1:A100) — имя NameCount
  • всему списку с номерами (A1:B100) — имя NameList

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

=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1);NameList;2))

или в английской версии Excel:

=IF(MAX(NameCount)

Следуя этой формуле сверху вниз в колонке NameCount, все элементы списка с номерами будут отображаться отдельно:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector