В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в 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 человек, из которых трое дублируются.
Как найти уникальные записи с учетом регистра?
Если вы работаете с набором данных, где важен регистр букв, вам понадобится немного более сложная формула массива.
Поиск уникальных значений с учетом регистра :
{=ЕСЛИ(СУММ((--СОВПАД($A$2:$A$10;A2)))=1;"Уникальный";"")}
Поиск различных значений с учетом регистра :
{=ЕСЛИ(СУММ((--СОВПАД($A$2:$A2;$A2)))=1;"Различный";"")}
Поскольку обе они являются формулами массива, обязательно нажмите Ctrl + Shift + Enter, чтобы правильно их записать.
Когда уникальные или различные значения найдены, вы можете легко отфильтровать, выбрать или скопировать их, как будет описано ниже.
Фильтр для уникальных значений.
Чтобы просмотреть только уникальные или различные значения в списке, отфильтруйте их, выполнив следующие действия.
- Примените одну из приведенных выше формул для определения уникальных или различных ячеек или строк.
- Выберите диапазон и нажмите кнопку «Фильтр» на вкладке «Данные».
- Щелкните стрелку фильтрации в заголовке столбца, содержащего формулу, и выберите то, что хотите просмотреть:
Как выбрать уникальные из фильтра.
Если у вас относительно небольшой список уникальных, вы можете просто выбрать их обычным способом с помощью мыши при нажатой клавише Ctrl. Если отфильтрованный список содержит сотни или тысячи строк, то для экономии времени вы можете использовать один из следующих способов.
Чтобы быстро выбрать весь получившийся список, включая заголовки столбцов, отфильтруйте уникальные значения, щелкните любую ячейку в получившемся списке, а затем нажмите Ctrl + A.
Чтобы выбрать уникальные значения без заголовков столбцов, отфильтруйте их, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.
Примечание. В некоторых редких случаях, в основном в очень больших книгах, рекомендованные выше комбинации клавиш могут выбирать как видимые, так и невидимые ячейки. Чтобы исправить это, нажмите сначала либо Ctrl + A или же Ctrl + Shift + End, а затем нажмите Alt +; для выбора только видимых ячеек, игнорируя скрытые строки.
Если вам сложно запомнить такое количество комбинаций, используйте этот визуальный способ: выделите весь список, затем перейдите на вкладку «Главная» > «Найти и выделить» > «Выделить группу ячеек» и выберите «Только видимые ячейки».
Как скопировать уникальные значения в другое место?
Чтобы скопировать список на новое место, сделайте следующее:
- Выберите отфильтрованные значения с помощью мыши или вышеупомянутых комбинаций клавиш.
- Нажмите
Ctrl + Cдля копирования выбранных значений. - Выберите верхнюю левую ячейку в целевом диапазоне (она может находиться на том же или другом листе) и нажмите Ctrl + V , чтобы вставить данные.
Выделение цветом уникальных значений в столбце.
Всякий раз, когда вам нужно выделить что-либо в 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
та же самая, что и раньше, но строка в диапазоне выделена вся.
А можно использовать и такое выражение:
=СУММ(Ч($A2&$B2=$A$2:$A$10&$B$2:$B$10))<2
Результат будет таким же.
Как выделить уникальные строки?
Если вы хотите выделить строки на основе значений в двух или более столбцах, используйте функцию СЧЁТЕСЛИМН, которая позволяет указать несколько критериев в одной формуле.
Чтобы выделить уникальные строки:
=СЧЁТЕСЛИМН($A$2:$A$10;$A2; $B$2:$B$10;$B2)=1
Чтобы выделить различные строки:
=СЧЁТЕСЛИМН($A$2:$A2;$A2; $B$2:$B2;$B2)=1
Быстрый и простой способ найти и выделить уникальные значения
Как вы только что видели, Microsoft Excel предоставляет довольно много полезных функций, которые могут помочь вам идентифицировать и выделять уникальные значения на ваших листах.
Однако все эти решения сложно назвать интуитивно понятными и простыми в использовании, поскольку они требуют запоминания нескольких различных формул. Конечно, для профессионалов Excel в этом нет ничего страшного :)
Для тех пользователей Excel, которые хотят сэкономить свое время и силы, позвольте мне показать быстрый и простой способ поиска уникальных значений в Excel.
В этом последнем разделе нашего сегодняшнего руководства мы собираемся использовать надстройку Duplicate Remover для Excel. Пожалуйста, пусть вас не смущает название инструмента. Помимо повторяющихся записей, он может отлично обрабатывать уникальные и различные записи.
Давайте посмотрим.
- Выберите любую ячейку в таблице, в которой вы хотите найти уникальные значения, и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.
Мастер запустится, и вся таблица будет выбрана автоматически. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.
Совет. При первом использовании надстройки имеет смысл на всякий случай установить флажок Создать резервную копию (Create a backup copy).
- В зависимости от вашей цели выберите один из следующих вариантов и нажмите Далее :
- Уникальные
- Уникальные + 1е вхождения (различные)
- Выберите один или несколько столбцов, в которых вы хотите проверить значения.
В этом примере мы хотим найти уникальные сочетания Заказчик + Товар на основе значений в двух столбцах. Их и выбираем при помощи галочки. - Выберите один или несколько столбцов, в которых вы хотите проверить значения.
Если у вашей таблицы есть заголовки, обязательно установите флажок Mytable has headers. И если в вашей таблице могут встретиться пустые ячейки, то убедитесь, что установлен флажок Skipempty cells. Оба параметра находятся в верхней части диалогового окна и обычно выбираются по умолчанию.
Если вдруг в наших записях случайно появились лишние пробелы, то, думаю, стоит их игнорировать. Поэтому отмечаем также Ignore extra spaces.
Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не активируем опцию Case-sensitive match.
- Выберите одно из следующих действий, которые нужно выполнить с найденными значениями:
- Выделить цветом.
- Выбрать и выделить.
- Отметить в колонке статуса.
- Копировать в другое место.
Если вы выберете опцию Select values, то все найденные значения окажутся выделенными, как будто вы кликали на них мышкой при нажатой клавише Ctrl. Пока они выделены, вы можете изменить их цвет фона и шрифта, границы и т.д. К сожалению, скопировать либо переместить их никуда не получится, так как такую операцию не поддерживает Excel.
В нашем случае чтобы найти уникальные значения, вполне достаточно будет просто выделить их цветом. Поэтому выберем Highlight with color.
Нажмите кнопку «Готово» и получите результат:
Вот как вы можете находить, выбирать и выделять уникальные значения в Excel с помощью надстройки Duplicate Remover. Это действительно просто, не правда ли?
Я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover и множество других инструментов, которые помогут сэкономить вам кучу времени при работе в Excel.
