В этой статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel.
Ранее мы рассматривали различные способы подсчета уникальных значений в Excel. Но иногда вы просто хотите посмотреть на уникальные или разные значения в столбце, не пересчитывая их. Но прежде чем мы продолжим, давайте убедимся, что вы понимаете, о чем мы говорим. Нравится,
Теперь давайте рассмотрим передовые методы управления уникальными и отличными значениями в таблицах Excel.
Самый простой способ сделать это — использовать функции ЕСЛИ и СЧЁТЕСЛИ. В зависимости от типа данных, которые вы хотите найти, могут быть разные варианты формулы, как показано в примерах ниже.
Чтобы найти разные или уникальные значения в списке, используйте одну из следующих формул, где 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, чтобы правильно их написать.
При обнаружении уникальных или различных значений их можно легко отфильтровать, выбрать или скопировать, как описано ниже.
Чтобы увидеть в списке только уникальные или уникальные значения, отфильтруйте их, выполнив следующие действия.
Если у вас относительно небольшой список уникальных, вы можете просто выбрать их обычным способом с помощью мыши, удерживая нажатой клавишу Ctrl. Если отфильтрованный список содержит сотни или тысячи строк, вы можете использовать один из следующих методов для экономии времени.
Чтобы быстро выбрать весь результирующий список, включая заголовки столбцов, отфильтровать по уникальным значениям, щелкните ячейку в результирующем списке, затем нажмите Ctrl + A.
Чтобы выбрать уникальные значения без заголовков столбцов, отфильтруйте их, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.
Примечание. В некоторых редких случаях, особенно в очень больших книгах, с помощью сочетаний клавиш, рекомендованных выше, можно выбрать как видимые, так и невидимые ячейки. Чтобы исправить это, сначала нажмите Ctrl + A или Ctrl + Shift + End, затем нажмите Alt +; для выбора только видимых ячеек, игнорируя скрытые строки.
Если вам трудно запомнить такое количество комбинаций, используйте этот визуальный метод: выделите весь список, затем перейдите на главную> Найти и выбрать> Выбрать группу ячеек и выберите «Только видимые ячейки».
Чтобы скопировать список в новое место, сделайте следующее:
Всякий раз, когда вам нужно выделить что-то в Excel на основе определенного условия, сразу переходите к функции условного форматирования. Более подробная информация и примеры представлены ниже.
Самый простой и быстрый способ выделить уникальные значения в Excel — применить встроенное правило условного форматирования:
Совет. Если вас не устраивает какой-либо из предопределенных форматов, нажмите «Пользовательский формат…» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.
Совет. Если вас не устраивает какой-либо из предопределенных форматов, нажмите «Пользовательский формат…» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.
Как видите, выделение уникальных значений в Excel — самая простая задача, которую вы можете себе представить. Однако встроенное правило Excel работает только для элементов, которые появляются в списке только один раз. Если вам нужно выделить разные значения — уникальные и первые вхождения дубликатов — вам нужно будет создать собственное правило на основе формулы.
Вам также потребуется создать настраиваемое правило для выделения уникальных строк на основе значений в одном или нескольких столбцах.
Чтобы выделить уникальные или разные значения в столбце, выберите диапазон ячеек без заголовка столбца (вы же не хотите, чтобы заголовок выделялся, не так ли?) Затем создайте правило условного форматирования с помощью формулы.
Чтобы создать правило условного форматирования на основе формулы, выполните следующие действия:
Более подробные инструкции см. В статье: Как создать правила условного форматирования 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 это не проблема
