Как найти повторные ячейки в excel - Сайт, где вы сможете решить свои вопросы

Поиск и удаление повторений

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще…Меньше

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

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

Примечание: В Excel не поддерживается выделение повторяющихся значений в области “Значения” отчета сводной таблицы.
На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.
В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.

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

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

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

Например, на данном листе в столбце “Январь” содержатся сведения о ценах, которые нужно сохранить.

Поэтому флажок Январь в поле Удаление дубликатов нужно снять.
Нажмите кнопку ОК.

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

Дополнительные сведения

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

Поиск и удаление повторений

Смотрите также одинаковые значения, например:A3Выберите стиль форматирования и Следуйте инструкции ниже,(Conditional Formatting > Это очень ресурсоемкая (столбец С) автоматическиИсхСписок- это Динамический диапазон значения, которые повторяются. Как убрать повторяющиеся

способов найти одинаковые нам нужно выделить:Первый способ.

Нажмите кнопкуПеред попыткой удаления>В некоторых случаях повторяющиеся12
: нажмите чтобы выделить только Highlight Cells Rules) задача и годится будет обновлен, чтобы (ссылка на исходный Дополнительное условие: при значения в Excel,
значения в Excel повторяющиеся или уникальныеКак найти одинаковые значенияОК повторений удалите всеПовторяющиеся значения данные могут быть45=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.

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

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

значения. Выбираем цвет в Excel.

 структуры и промежуточные. полезны, но иногда12Обратите внимание, что мы.
встречающиеся трижды:Повторяющиеся значения 50-100 значений. Если3. Добавьте в исходныйА в исходный список, «Как удалить дубли не только цветом, заливки ячейки или.

Рассмотрим, итоги из своихВ поле рядом с они усложняют понимание6

создали абсолютную ссылкуРезультат: Excel выделил значения,Сперва удалите предыдущее правило(Duplicate Values). динамический список не
список название новой). новый список должен

support.office.com

Как выделить повторяющиеся значения в Excel.

в Excel». но и словами, цвет шрифта.Например, число, фамилию,к данных. оператором данных. Используйте условное8 – встречающиеся трижды. условного форматирования.Определите стиль форматирования и нужен, то можно компании еще разСкопируйте формулу вниз с автоматически включать толькоИз исходной таблицы с числами, знаками. МожноПодробнее смотрите в т.д. Как этоак найти и выделить
На вкладке
значения с форматирование для поиска12
$A$1:$C$10Пояснение:Выделите диапазон нажмите пойти другим путем:
 (в ячейку
 помощью Маркера заполнения повторяющиеся значения. повторяющимися значениями отберем настроить таблицу так, статье «Выделить дату,

сделать, смотрите в одинаковые значения вДанныевыберите форматирование для и выделения повторяющихся6.Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество
A1:C10ОК см. статью ОтборА21 (размерность списка значенийСписок значений, которые повторяются, только те значения,
что дубли будут день недели в статье «Как выделить Excel.нажмите кнопку применения к повторяющимся данных. Это позволит589

Примечание: значений в диапазоне.. повторяющихся значений сснова введите ООО имеющих повторы должна создадим в столбце которые имеют повторы. не только выделяться, Excel при условии» ячейки в Excel».Нам поможет условноеУдалить дубликаты значениям и нажмите вам просматривать повторенияМожно ли как-нибудьВы можете использоватьA1:C10На вкладкеРезультат: Excel выделил повторяющиеся помощью фильтра. Кристалл) совпадать с размерностьюB Теперь при добавлении но и считаться. тут.Второй способ. форматирование.Что такое условноеи в разделе кнопку и удалять их обозначить эти совпадения любую формулу, которая, которые равны значениюГлавная имена.Этот пример научит вас4. Список неповторяющихся значений исходного списка).с помощью формулы

excel-office.ru

Отбор повторяющихся значений в MS EXCEL

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

в ячейке A1.(Home) выберите командуПримечание: находить дубликаты в автоматически будет обновлен,В файле примера также массива. (см. файл исходный список, новый

Задача

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

Решение

Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматируетУсловное форматированиеЕсли в первом Excel с помощью новое название будет приведены перечни, содержащие

примера). список будет автоматически слова, а можно
года – 1960.
. В этой таблице
смотрите в статье

флажки, соответствующие столбцам,При использовании функции проверить на наличие найти такие строки встречающиеся более 3-х ячейку.

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

Введем в ячейку содержать только те выделять дубли соМожно в условном нам нужно выделить “Условное форматирование в

в которых нужноУдаление дубликатов повторений. и выделить в

раз, используйте этуПоскольку прежде, чем нажатьСоздать правило выберите вместо

Тестируем

по этой ссылке,5. Список повторяющихся значений уникальные значения.B5 значения, которые повторяются.

второго и далее. форматировании тоже в год рождения 1960. Excel” здесь. Выделить

удалить повторения.повторяющиеся данные удаляютсяПримечание: доп столбце? формулу: кнопку(Conditional Formatting >

Повторяющиеся чтобы узнать, как (столбец B) автоматическиС помощью Условного форматирования

формулу массива:Пусть в столбце Обо всем этом разделе «Правила выделенных

Выделяем столбец «Год

повторяющиеся значения вНапример, на данном листе безвозвратно. Чтобы случайно В Excel не поддерживается_Boroda_=COUNTIF($A$1:$C$10,A1)>3Условное форматирование New Rule).(Duplicate) пункт удалить дубликаты. будет обновлен, чтобы в исходном списке=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;А и другом читайте ячеек» выбрать функцию

excel2.ru

Поиск дубликатов в Excel с помощью условного форматирования

рождения». На закладке Excel можно как в столбце “Январь” не потерять необходимые выделение повторяющихся значений: Выделяем диапазон,=СЧЕТЕСЛИ($A$1:$C$10;A1)>3

(Conditional Formatting), мыНажмите наУникальные
Выделите диапазон включить новое название. можно выделить повторяющиесяПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)имеется список с в статье “Как «Содержит текст». Написать «Главная» в разделе во всей таблицы, содержатся сведения о сведения, перед удалением
в области “Значения”Главная – условноеУрок подготовлен для Вас выбрали диапазонИспользовать формулу для определения(Unique), то Excel

A1:C10СОВЕТ: значения.);””) повторяющимися значениями, например найти повторяющиеся значения этот текст (например, «Стили» нажимаем кнопку так и в ценах, которые нужно

повторяющихся данных рекомендуется отчета сводной таблицы. форматирование – Правила командой сайта office-guru.ruA1:C10 форматируемых ячеек выделит только уникальные.Созданный список повторяющихся значений

1. Добавьте в исходныйВместо
список с названиями в Excel”. В фамилию, цифру, др.),
«Условное форматирование». Затем определенном диапазоне (строке, сохранить. скопировать исходные данныеНа вкладке выделения ячеек -Источник: http://www.excel-easy.com/examples/find-duplicates.html, Excel автоматически скопирует
(Use a formula имена.На вкладке является динамическим, т.е. список название новойENTER
компаний. В некоторых
таблице можно удалять
и все ячейки
в разделе «Правила столбце). А функцияПоэтому флажок на другой лист.Главная Повторяющиеся значения

Перевел: Антон Андронов
- формулы в остальные to determine whichКак видите, Excel выделяетГлавная при добавлении новых
- компании (в ячейкунужно нажать
- ячейках исходного списка дубли по-разному. Удалить с этим текстом выделенных ячеек» выбираем “Фильтр в Excel”ЯнварьВыделите диапазон ячеек свыберитеИли в соседнемАвтор: Антон Андронов ячейки. Таким образом, cells to format). дубликаты (Juliet, Delta),(Home) нажмите
  значений в исходный
- А20CTRL + SHIFT + имеются повторы. строки по полному выделятся цветом. Мы

 «Повторяющиеся значения». поможет их скрыть,в поле повторяющимися значениями, которыйУсловное форматирование столбце (или тожеhatter ячейка

Введите следующую формулу:
значения, встречающиеся трижды

Условное форматирование список, новый список
введите ООО Кристалл)
ENTER

Создадим новый список, который

office-guru.ru

Найти повторяющиеся значения в столбце (Формулы)

 совпадению, удалить ячейки написали фамилию «Иванов».В появившемся диалоговом если нужно. Рассмотрим
Удаление дубликатов
нужно удалить.
>
в условном форматировании)
: имеется столбец в
A2
=COUNTIF($A$1:$C$10,A1)=3
(Sierra), четырежды (если
> будет автоматически обновляться.2. Список неповторяющихся значений. содержит только те в столбце, т.д.Есть еще много

 окне выбираем, что несколько способов.
нужно снять.Совет:Правила выделения ячеек200?’200px’:”+(this.scrollHeight+5)+’px’);”>=СЧЁТЕСЛИ(Диапазон;Ссылка_на_ячейку)>1
котором могут находитьсясодержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка=СЧЕТЕСЛИ($A$1:$C$10;A1)=3
есть) и т.д.

excelworld.ru

Правила выделения ячеек

Источник

Содержание

Поиск и удаление
- Способ 1: простое удаление повторяющихся строк
- Способ 2: удаление дубликатов в «умной таблице»
- Способ 3: применение сортировки
- Способ 4: условное форматирование
- Способ 5: применение формулы
Вопросы и ответы

При работе с таблицей или базой данных с большим количеством информации возможна ситуация, когда некоторые строки повторяются. Это ещё больше увеличивает массив данных. К тому же, при наличии дубликатов возможен некорректный подсчет результатов в формулах. Давайте разберемся, как в программе Microsoft Excel отыскать и удалить повторяющиеся строки.

Поиск и удаление

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

Способ 1: простое удаление повторяющихся строк

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

Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

Excel выполняет процедуру поиска и удаления дубликатов. После её завершения появляется информационное окно, в котором сообщается, сколько повторных значений было удалено и количество оставшихся уникальных записей. Чтобы закрыть данное окно, жмем кнопку «OK».

Способ 2: удаление дубликатов в «умной таблице»

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

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

Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

Но создание «умной таблицы» — это только один шаг для решения нашей главной задачи – удаления дубликатов. Кликаем по любой ячейке табличного диапазона. При этом появляется дополнительная группа вкладок «Работа с таблицами». Находясь во вкладке «Конструктор» кликаем по кнопке «Удалить дубликаты», которая расположена на ленте в блоке инструментов «Сервис».

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

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

Урок: Как сделать таблицу в Excel

Способ 3: применение сортировки

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

Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

Фильтр включен, о чем говорят появившиеся пиктограммы в виде перевернутых треугольников в названиях столбцов. Теперь нам нужно его настроить. Кликаем по кнопке «Дополнительно», расположенной рядом все в той же группе инструментов «Сортировка и фильтр».

Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».

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

Урок: Расширенный фильтр в Excel

Способ 4: условное форматирование

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

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

Открывается окно настройки форматирования. Первый параметр в нём оставляем без изменения – «Повторяющиеся». А вот в параметре выделения можно, как оставить настройки по умолчанию, так и выбрать любой подходящий для вас цвет, после этого жмем на кнопку «OK».

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

Внимание! Поиск дублей с применением условного форматирования производится не по строке в целом, а по каждой ячейке в частности, поэтому не для всех случаев он является подходящим.

Урок: Условное форматирование в Excel

Способ 5: применение формулы

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));"")

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

Вводим формулу по указанному выше шаблону в первую свободную ячейку нового столбца. В нашем конкретном случае формула будет иметь следующий вид:
=ЕСЛИОШИБКА(ИНДЕКС(A8:A15;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E7:$E$7;A8:A15)+ЕСЛИ(СЧЁТЕСЛИ(A8:A15;A8:A15)>1;0;1);0));"")

Выделяем весь столбец для дубликатов, кроме шапки. Устанавливаем курсор в конец строки формул. Нажимаем на клавиатуре кнопку F2. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это обусловлено особенностями применения формул к массивам.

После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

Но, данный способ все-таки слишком сложен для большинства пользователей. К тому же, он предполагает только поиск дублей, но не их удаление. Поэтому рекомендуется применять более простые и функциональные решения, описанные ранее.

Как видим, в Экселе есть множество инструментов предназначенных для поиска и удаления дублей. У каждого из них есть свои особенности. Например, условное форматирование предполагает поиск дублей только по каждой ячейке в отдельности. К тому же, не все инструменты могут не только искать, но и удалять повторяющиеся значения. Наиболее универсальный вариант – это создание «умной таблицы». При использовании данного способа можно максимально точно и удобно настроить поиск дубликатов. К тому же, их удаление происходит моментально.

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Найти и выделить цветом дубликаты в Excel
Формула проверки наличия дублей в диапазонах
- Внутри диапазона
!SEMTools, поиск дублей внутри диапазона
- Найти дубли ячеек в столбце, кроме первого
- Найти в столбце дубли ячеек, включая первый
- Найти дубли в столбце без учета лишних пробелов

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

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

Ключевых моментов несколько:

Какие конкретно повторяющиеся значения — повторы слов в ячейках, сами повторяющиеся ячейки или повторяющиеся строки?
Если ячейки, то:
- Какие ячейки мы готовы считать дубликатами — все кроме первой или включая ее?
- Считаем ли дублями строки, отличающиеся только пробелами до/после слов или лишними пробелами между словами?
- Где мы будем искать дубли — в одном столбце, в двух столбцах или в нескольких?
- А может, нам нужно найти неявные дубли?

Сначала рассмотрим простые примеры.

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

Найти инструмент можно на вкладке программы “Главная”:

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

Вызов процедуры условного форматирования для подсветки повторяющихся значений

Процедура интуитивно понятна:

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

Второй шаг процедуры – выбор цветов заливки и текста

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

Неочевидный результат работы условного форматирования с дублями внутри диапазона

Условное форматирование меняет цвет дубликатов внутри столбца

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

Как избежать подобной ситуации, если хочется найти именно дубли в другом столбце? Простейшее решение: удалить дубли внутри каждого столбца перед применением условного форматирования.

Но есть и другие решения. О них дальше.

Формула проверки наличия дублей в диапазонах

Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Но её я возьму на себя.

Внутри диапазона

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0

Так выглядит на практике применение формулы:

Формула возвращает ИСТИНА, если в адресованном диапазоне появляется дубликат

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

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ .Это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

=СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1

Эта формула слегка отличается, так как проверяет встречаемость в диапазоне значения одной ячейки.

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

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

Применение формулы для условного форматирования и поиска неявных дубликатов

Обратите внимание на один момент в этой демонстрации: диапазон закреплен ($A$1:$B$4), а искомая ячейка (A1) нет. Именно это позволяет условному форматированию находить все дубликаты в диапазоне.

!SEMTools, поиск дублей внутри диапазона

Когда-то я потратил немало времени, пользуясь перечисленными выше методами поиска повторяющихся значений. Все они мне не нравились. Причина была одна: это попросту медленно. Поэтому я решил сделать отдельные процедуры для поиска и удаления дубликатов в Excel в своей надстройке.

Давайте покажу, как они работают.

Найти дубли ячеек в столбце, кроме первого

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

Найти в столбце дубли ячеек, включая первый

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

Найти дубли в столбце без учета лишних пробелов

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

Для первой операции есть отдельный инструмент «Удалить лишние пробелы»:

Как найти дубли ячеек, не учитывая лишние пробелы

Найти повторяющиеся значения в Excel и решить сотни других задач поможет надстройка !SEMTools.

Скачайте прямо сейчас и убедитесь сами!

Смотрите также:

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

Источник

При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста…

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