Article #34143

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

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

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

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

• Как найти уникальные значения с помощью формул.
• Фильтр по уникальным данным.
• Выделение и условное форматирование.
• Быстрый и простой способ — это Duplicate Remover.

Как найти уникальные значения при помощи формул.

Самый простой способ сделать это — использовать функции ЕСЛИ и СЧЁТЕСЛИ. В зависимости от типа данных, которые вы хотите найти, могут быть разные варианты формулы, как показано в примерах ниже.

Как найти уникальные значения в столбце.

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

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

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2) = 1, «Уникальный»; «»)

Чтобы определить разные значения:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2; $ A2) = 1, «Разное»; «»)

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

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

Уникальные строки в таблице.

Точно так же вы можете найти уникальные строки в электронной таблице Excel, исследуя записи не в одном, а в двух или более столбцах. В этом случае для оценки значений необходимо использовать СЧЁТЕСЛИ вместо СЧЁТЕСЛИ (в одной формуле можно обработать до 127 пар диапазон / критерий).

Формула получения уникальных строк:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2, $ B $ 2: $ B $ 10, $ B2) = 1, «Уникальный»; «»)

Формула для поиска разных строк:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2) = 1, «Разное»; «»)
 

В нашем случае уникальная комбинация Имя + Фамилия встречается 2 раза. И в списке 6 человек, трое из которых — дубликаты.

Как найти уникальные записи с учетом регистра?

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

Найдите уникальные значения с учетом регистра :

{= IF (SUM ((- EXACT ($ A $ 2: $ A $ 10, A2))) = 1; «Уникальный»;»»)}

Ищите разные значения с учетом регистра :

{= IF (SUM ((- EXACT ($ A $ 2: $ A2, $ A2))) = 1; «Разное»;»»)}

Поскольку обе они являются формулами массива, обязательно нажмите Ctrl + Shift + Enter, чтобы правильно их написать.

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

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

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

1. Примените одну из приведенных выше формул для определения уникальных или различных ячеек или строк.
2. Выберите диапазон и нажмите кнопку «Фильтр» на вкладке «Данные». 
3. Щелкните стрелку фильтра в заголовке столбца, содержащего формулу, и выберите то, что вы хотите просмотреть:

Как выбрать уникальные из фильтра.

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

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

Чтобы выбрать уникальные значения без заголовков столбцов, отфильтруйте их, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.

Примечание. В некоторых редких случаях, особенно в очень больших книгах, с помощью сочетаний клавиш, рекомендованных выше, можно выбрать как видимые, так и невидимые ячейки. Чтобы исправить это, сначала нажмите Ctrl + A или Ctrl + Shift + End, затем нажмите Alt +; для выбора только видимых ячеек, игнорируя скрытые строки.

Если вам трудно запомнить такое количество комбинаций, используйте этот визуальный метод: выделите весь список, затем перейдите на главную> Найти и выбрать> Выбрать группу ячеек и выберите «Только видимые ячейки».

Как скопировать уникальные значения в другое место?

Чтобы скопировать список в новое место, сделайте следующее:

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

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

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

Самый простой и быстрый способ выделить уникальные значения в Excel — применить встроенное правило условного форматирования:

1. Выберите столбец данных, в котором вы хотите выделить уникальные.
2. На вкладке Главная в группе Стили щелкните Условное форматирование> Правила выделения ячеек> Повторяющиеся значения…

1. В диалоговом окне «Повторяющиеся значения» выберите «Уникальный» в левом поле и выберите желаемое форматирование в правом поле, затем нажмите «ОК» .

Совет. Если вас не устраивает какой-либо из предопределенных форматов, нажмите «Пользовательский формат…» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.

Совет. Если вас не устраивает какой-либо из предопределенных форматов, нажмите «Пользовательский формат…» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.

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

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

Как создать правило для условного форматирования уникальных значений?

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

Чтобы создать правило условного форматирования на основе формулы, выполните следующие действия:

1. Перейдите на вкладку «Главная» и нажмите «Условное форматирование»> «Новое правило»> «Использовать формулу», чтобы использовать формулу для определения ячеек для форматирования .
2. Введите формулу в поле «Значения формата …».
3. Нажмите кнопку «Форматировать» и выберите нужный цвет заливки и / или цвет шрифта.
4. Наконец, нажмите OK, чтобы применить правило.

Более подробные инструкции см. В статье: Как создать правила условного форматирования Excel на основе другого значения ячейки .
Теперь поговорим о том, какие формулы использовать и в каких случаях.

Выделяем цветом отдельные уникальные значения.

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

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2) = 1

Где A2 — первая, а A10 — последняя ячейка в диапазоне.

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

= СЧЁТЕСЛИ ($ A $ 2: $ A2; $ A2) = 1

Где A2 — верхняя ячейка диапазона.

Как выделить строку с уникальным значением в одном столбце.

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

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

Как видите, формула

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2) = 1

то же самое, что и раньше, но выбирается вся строка в диапазоне.

Или вы можете использовать следующее выражение:

= СУММ (H ($ A2 & $ B2 = $ A $ 2: $ A $ 10 и $ B $ 2: $ B $ 10))

Результат будет таким же.

Как выделить уникальные строки?

Если вы хотите выделить строки на основе значений в двух или более столбцах, используйте функцию СЧЁТЕСЛИ, которая позволяет указать несколько критериев в одной формуле.

Чтобы выделить уникальные строки:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 10; $ A2; $ B $ 2: $ B $ 10, $ B2) = 1

Чтобы выделить разные строки:

= СЧЁТЕСЛИ ($ A $ 2: $ A2; $ A2; $ B $ 2: $ B2, $ B2) = 1

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

Как вы только что видели, Microsoft Excel предлагает несколько полезных функций, которые могут помочь вам идентифицировать и выделять уникальные значения на ваших листах. 

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