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

Фильтрация или удаление повторяющихся значений

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

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

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

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

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

Удаление повторяющихся значений

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

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

1. Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
2. На вкладке Данные в разделе Работа с данными нажмите кнопку Удалить дубликаты.
3. Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Удалить дубликаты.

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

Применение условного форматирования к уникальным или повторяющимся значениям

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

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

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Создать правило.
3. В списке Стиль выберите пункт Классический, а затем в списке Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
4. В списке значения в выбранном диапазоне выберите пункт уникальные или повторяющиеся.
5. В списке Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.

Изменение правил расширенного условного форматирования

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Управление правилами.
3. Убедитесь, что в списке Показать правила форматирования для выбран соответствующий лист или таблица.
4. Выберите правило и нажмите кнопку Изменить правило.
5. Выберите нужные параметры и нажмите кнопку ОК.

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

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

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

Удаление повторяющихся значений

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

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

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

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

Применение условного форматирования к уникальным или повторяющимся значениям

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в разделе Формат щелкните стрелку рядом с элементом Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт Повторяющиеся значения.
3. Выберите нужные параметры и нажмите кнопку ОК.

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Создать правило.
3. Во всплывающем меню Стиль выберите пункт Классический, а затем во всплывающем меню Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
4. В меню значения в выбранном диапазоне выберите уникальные или повторяющиеся.
5. В меню Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.

Изменение правил расширенного условного форматирования

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.
3. Убедитесь, что в меню Показать правила форматирования для выбран соответствующий лист или таблица.
4. Выберите правило и нажмите кнопку Изменить правило.
5. Выберите нужные параметры и нажмите кнопку ОК.

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

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

=INDIRECT(TEXT(MIN(IF(( $A$2:$C$11 <>"")\*(COUNTIF( $E$1:E1 , $A$2:$C$11 )=0),ROW( $2:$11 )\*100+COLUMN( $A:$C ),7^8)),"R0C00"),)&"" 

Эта конкретная формула находит уникальные значения в диапазоне ячеек A2:C11 и возвращает список уникальных значений в столбце E .

В следующем примере показано, как использовать эту формулу на практике.

Пример: поиск уникальных значений из нескольких столбцов в Excel

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

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

=INDIRECT(TEXT(MIN(IF(( $A$2:$C$11 <>"")\*(COUNTIF( $E$1:E1 , $A$2:$C$11 )=0),ROW( $2:$11 )\*100+COLUMN( $A:$C ),7^8)),"R0C00"),)&"" 

Мы можем ввести эту формулу в ячейку E2 и нажать Ctrl+Shift+Enter :

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

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

Примечание.Чтобы использовать эту формулу с большим количеством столбцов, просто измените диапазон ячеек A2:C11 в формуле, чтобы включить больше столбцов.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

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

Начиная с 2007-й версии функция удаления дубликатов является стандартной — найти ее можно на вкладке Данные — Удаление дубликатов (Data — Remove Duplicates) : В открывшемся окне нужно с помощью флажков задать те столбцы, по которым необходимо обеспечивать уникальность. Т.е. если включить все флажки, то будут удалены только полностью совпадающие строки. Если включить только флажок заказчик, то останется только по одной строке для каждого заказчика и т.д.

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

• Выделяем наш список компаний в Исходный диапазон (List Range) .
• Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
• Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :

Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.

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

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)

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

=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)

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

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

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

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

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

=IF(MAX(NameCount)

Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:

Ссылки по теме

• Выделение дубликатов по одному или нескольким столбцам в списке цветом
• Запрет ввода повторяющихся значений
• Извлечение уникальных значений при помощи надстройки PLEX

покупка

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

Например, у меня есть два столбца разной длины, заполненные именами студентов, и теперь я хочу сравнить эти два столбца, чтобы выбрать все значения в столбце A, но не в столбце B, и выбрать все значения в столбце B, но не в столбце A. Это означает выбор уникальных значений между двумя столбцами. Если я сравню их по ячейке, это займет очень много времени. Есть ли хорошие идеи, чтобы быстро узнать все уникальные значения между двумя столбцами в Excel?

• Найдите уникальные / повторяющиеся значения между двумя столбцами с помощью формулы
• Найдите и подсчитайте уникальные/повторяющиеся значения между двумя столбцами по формуле Kutools for Excel
• Найдите уникальные/повторяющиеся значения между двумя столбцами с помощью Kutools for Excel

Найдите уникальные / повторяющиеся значения между двумя столбцами с помощью формулы

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

В пустой ячейке B2 введите эту формулу = ЕСЛИ (ISNA (ВПР (A2; $ C $ 2: $ C $ 13,1; FALSE)); «Да»; «») , а затем перетащите маркер автозаполнения этой ячейки в ячейку B15.

Если уникальные значения остаются только в столбце A, но не в столбце C, они будут отображаться. Да в столбце B; тогда как если ничего не возвращается в столбце B, это означает, что соответствующее значение остается как в столбце A, так и в столбце C. См. снимок экрана:

Заметки:

(1) В приведенной выше формуле A2 это столбец, который вы хотите сравнить. И 2 канадских доллара: 13 канадских долларов это диапазон, с которым вы хотите сравнивать.

(2) Если вы хотите перечислить уникальные значения только в столбце C, но не в столбце A, вы можете применить эту формулу: = ЕСЛИ (ISNA (ВПР (C2; $ A $ 2: $ A $ 15,1; FALSE)); «Да»;.

Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия

Найдите и подсчитайте уникальные/повторяющиеся значения между двумя столбцами с помощью формулы Kutools for Excel

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

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Выберите пустую ячейку рядом с первым столбцом, в нашем случае мы выбираем ячейку B2 и нажимаем Кутулс > Формула Помощник > Считайте, сколько раз появляется слово. Смотрите скриншот:

2. В открывшемся диалоговом окне Formula Helper укажите абсолютный адрес второго столбца ( 2 канадских доллара: 13 канадских долларов в нашем случае) в Текст поле выберите первый элемент первого столбца (A2 в нашем случае) в Word и нажмите OK кнопку.

3. Затем перетащите дескриптор автозаполнения этой ячейки в нужный диапазон.

И затем было отсчитано появление каждого элемента в столбце C. 0 означает, что соответствующий элемент уникален между двумя столбцами. В то время как другие числа, такие как 1, означают, что соответствующее значение существует в столбце C и появляется один раз в столбце C. См. Снимок экрана слева:

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

Найдите уникальные/повторяющиеся значения между двумя столбцами с помощью Kutools for Excel

На самом деле, Kutools for Excel развивает Выберите одинаковые и разные ячейки утилита, помогающая пользователям Excel сравнивать два списка и легко находить / подсчитывать / выделять одинаковые или уникальные значения между ними. Пожалуйста, сделайте следующее:

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

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

(1) Укажите первый столбец, значения которого вы будете проверять на уникальность в Найдите значения в коробка;
(2) Укажите второй столбец, в котором вы будете проверять значения на основе По словам коробка;
(3) В на основании раздел, пожалуйста, проверьте Каждый ряд вариант;
(4) В Найдите раздел, пожалуйста, проверьте Разные ценности вариант;
(5) Необязательно проверять Заполните цвет фона опцию и укажите цвет заливки из раскрывающегося списка внизу;
(6) Щелкните значок Ok кнопку.

Внимание(1) Если два столбца имеют одинаковый заголовок, проверьте У моих данных есть заголовки вариант; (2) Чтобы найти повторяющиеся значения между двумя столбцами, проверьте Те же ценности опцию.

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now

3. Затем все уникальные значения (или повторяющиеся значения) в первом столбце были выбраны и залиты указанным цветом, а в диалоговом окне показано, сколько ячеек было выбрано. Смотрите скриншот:

Если вы хотите выбрать значения в столбце C, но не в столбце A, вам просто нужно поменять местами область действия Диапазон А и Диапазон B.

Демо: найдите уникальные / повторяющиеся значения между двумя столбцами в Excel

Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните свой 30 Бесплатная пробная версия на день без каких-либо ограничений сегодня. Скачать