Как сделать дубликаты данных в ячейках в Excel

Обновлено: 05.07.2024

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

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

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

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

Пример 1. Выделите повторяющиеся ячейки, включая первые вхождения

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

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

Чтобы применить собственное форматирование к дубликатам, нажмите Пользовательский формат… (последний элемент в раскрывающемся списке), а затем выберите нужный Шрифт, Границу и Параметры Заполнить.
Чтобы выделить уникальные ячейки, выберите «Уникальные» в левом поле.

Пример 2. Выделите повторяющиеся ячейки, кроме первых вхождений

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

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

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

=ЕСЛИ(СТОЛБЦЫ($B2:B2)>1, СЧЁТЕСЛИ(A$2:$B$7,B2),0) + СЧЁТЕСЛИ(B$2:B2,B2)>1

Намного больше вариантов использования и примеров можно найти в этом руководстве: Как выделить дубликаты в Excel.

Как найти повторяющиеся ячейки в Excel с помощью формул

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

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

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

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

Чтобы найти повторяющиеся ячейки, включая первые экземпляры, вы сравниваете целевую ячейку (A2) со всеми другими ячейками в диапазоне $A$2:$A$10 (обратите внимание, что мы блокируем диапазон абсолютными ссылками), и если их несколько найдена ячейка, содержащая одинаковое значение, пометьте целевую ячейку как «Дубликат».

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10, A2)>1, "Дубликат", "")

Эта формула помещается в ячейку B2, а затем вы копируете ее в столько ячеек, сколько элементов в списке.

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2, $A2)>1, "Дубликат", "")

При копировании в ячейки ниже ссылка на диапазон увеличивается на 1. Таким образом, формула в ячейке B2 сравнивает значение в ячейке A2 только с самой этой ячейкой. В ячейке B3 диапазон расширяется до $A$2:$A3, поэтому значение в ячейке A3 также сравнивается с ячейкой выше и так далее.

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

Дополнительные примеры формул см. в разделе Как найти дубликаты в Excel.

Как удалить дубликаты в Excel

Как вы, наверное, знаете, все модемные версии Excel оснащены инструментом Удалить дубликаты, который работает со следующими оговорками:

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

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

Выберите набор данных, который вы хотите дедуплицировать.
На вкладке Данные в группе Инструменты данных нажмите Удалить дубликаты.
В диалоговом окне Удалить дубликаты выберите столбцы для проверки на дубликаты и нажмите ОК.

В приведенном ниже примере мы хотим проверить первые четыре столбца на наличие дубликатов, поэтому выбираем их. Столбец Комментарии не очень важен и поэтому не выбран.

Исходя из значений в выбранных столбцах, Excel обнаружил и удалил две повторяющиеся записи (для Кадена и Итана). Первые экземпляры этих записей сохраняются.

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

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

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

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

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

Позвольте представить вам наше недавнее дополнение к набору инструментов Ablebits Duplicate Remover — надстройка Find Duplicate Cells.

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

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

Помните громоздкую формулу условного форматирования для достижения того же эффекта? ;)

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

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

Хорошей новостью является то, что все эти инструменты включены в Ultimate Suite, и вы можете попробовать любой из них в своих рабочих листах прямо сейчас — ссылка для скачивания находится прямо ниже.

Параметр «Повторяющиеся значения» в продолжении меню «Выделение правил ячеек» в Excel 2016 позволяет выделить повторяющиеся значения в выбранном диапазоне ячеек.

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

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

Нажмите кнопку «Условное форматирование» в группе «Стили» на вкладке «Главная» ленты; затем выберите «Правила выделения ячеек» → «Повторяющиеся значения» в раскрывающемся меню.

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

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

Если вы определяете собственный формат, а не выбираете один из предустановленных форматов, используйте параметры на вкладках «Число», «Шрифт», «Граница» и «Заливка» диалогового окна «Формат ячеек», чтобы указать все применяемые форматы, а затем нажмите Нажмите кнопку "ОК", чтобы закрыть диалоговое окно "Формат ячеек" и вернуться в диалоговое окно "Сравнить столбцы" (где "Пользовательский формат" отображается в третьем раскрывающемся списке).

Нажмите "ОК", чтобы закрыть диалоговое окно "Повторяющиеся значения".

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

Об этой статье

Эта статья взята из книги:

Об авторе книги:

Грег Харви, доктор философии, президент Mind Over Media, LLC и автор бестселлеров по Excel, в том числе всех выпусков Excel для чайников и Excel Workbook For Dummies. . Он начал обучать бизнес-пользователей работе с компьютерами еще в 1980-х годах и с тех пор посвятил себя обучению.

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

Объединить повторяющиеся строки и суммировать значения с помощью функции Consolidate

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

<р>1. Щелкните ячейку, в которой вы хотите найти результат на текущем листе.

<р>2. Нажмите Данные > Консолидировать, см. снимок экрана:

<р>3. В диалоговом окне «Консолидация»:

<р>4. После завершения настроек нажмите OK, и дубликаты будут объединены и суммированы. Смотрите скриншот:

Примечание. Если в диапазоне нет строки заголовка, снимите флажок Верхняя строка в параметре Использовать метки в.

Объединить повторяющиеся строки и суммировать/усреднить соответствующие значения в другом столбце

Kutools for Excel's Advanced Combibe Rows помогает вам объединить несколько повторяющихся строк в одну запись на основе ключевого столбца, а также может применять некоторые вычисления, такие как сумма, среднее значение, количество и т. д., для других столбцов. Нажмите, чтобы скачать Kutools for Excel!

Объединить повторяющиеся строки и суммировать значения с помощью кода VBA

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

<р>1. Удерживая нажатыми клавиши ALT + F11, откроется окно Microsoft Visual Basic для приложений.

<р>2. Нажмите «Вставить» > «Модуль» и вставьте следующий код в окно модуля.

Код VBA: объединение повторяющихся строк и суммирование значений

<р>3. Затем нажмите клавишу F5, чтобы запустить этот код, и вам нужно выбрать диапазон, который вы хотите объединить, в появившемся окне подсказки. Смотрите скриншот:

<р>4. Затем нажмите OK, повторяющиеся строки будут объединены, а значения суммированы.

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

Объедините повторяющиеся строки и просуммируйте значения с помощью Kutools for Excel

Здесь я представляю удобный инструмент - Kutools for Excel для вас, его расширенные ряды комбинирования также могут быстро решить эту проблему.

После установки Kutools for Excel сделайте следующее:

<р>1. Выберите нужный диапазон и нажмите Kutools > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншот:

<р>2. В диалоговом окне «Расширенные строки объединения» установите флажок «Мои данные имеют заголовки», если в вашем диапазоне есть заголовки, и выберите имя столбца, дубликаты которого вы хотите объединить, и нажмите «Первичный ключ», см. снимок экрана:

<р>3.Затем выберите имя столбца, в котором вы хотите суммировать значения, и нажмите «Рассчитать»> «Сумма» или другие вычисления, которые вам нужны. Смотрите скриншот:

<р>4. Нажмите «ОК», чтобы закрыть диалоговое окно, после чего вы увидите, что дубликаты объединены, а соответствующие данные в другом столбце суммируются. Смотрите скриншоты:

Объедините соответствующие строки на основе повторяющихся значений в другом столбце с помощью Kutools for Excel

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

<р>1. Выберите диапазон данных, который вы хотите использовать, а затем нажмите Kutools > Слияние и разделение > Расширенные строки комбинирования, чтобы включить диалоговое окно «Расширенные строки комбинирования».

<р>2. В диалоговом окне «Расширенные строки объединения» щелкните имя столбца, на основе которого вы хотите объединить другие данные, и нажмите «Первичный ключ», см. снимок экрана:

<р>3. Затем щелкните имя другого столбца, данные которого вы хотите объединить, и нажмите «Объединить», чтобы выбрать разделитель для разделения объединенных значений, см. снимок экрана:

<р>4. Затем нажмите «ОК», все значения с одной и той же ячейкой в столбце А были объединены вместе, см. скриншоты:

Объедините повторяющиеся строки и суммируйте значения с помощью Kutools for Excel

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

Мастер объединения дубликатов — это надстройка для Microsoft Excel, специально разработанная для объединения данных из повторяющихся строк в одну.

Видео: как работать со слиянием дубликатов

Прежде чем начать

Обратите внимание на флажок Создать резервную копию этого листа. Мы рекомендуем оставить этот параметр выбранным, поскольку Excel не позволяет отменить изменения, внесенные надстройками.

Обратите внимание, что скрытые строки по-прежнему обрабатываются надстройкой.

Имейте в виду, что инструмент вставляет результаты значений, а не формулы.

Как использовать слияние дубликатов

Начать объединение дубликатов

На вкладке Ablebits Data в группе Объединить щелкните значок Объединить дубликаты:

Шаг 1. Выберите таблицу

На первом этапе надстройка выбирает весь диапазон с вашими данными:

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

Не забудьте установить флажок Создать резервную копию этого листа, чтобы получить копию своих данных.

Нажмите "Далее".

Шаг 2. Выберите ключевые столбцы с повторяющимися записями

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

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

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

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

Нажмите кнопку "Далее".

Шаг 3. Выберите столбцы со значениями для объединения

На этом шаге выберите столбцы с записями для объединения:

Установите флажки рядом со столбцами с данными, которые нужно объединить, и воспользуйтесь дополнительными параметрами:

Отметьте Удалить повторяющиеся значения, если записи, которые нужно объединить, могут содержать идентичные элементы, но вы хотите оставить в результатах только уникальные значения:
Выберите параметр Пропускать пустые ячейки, чтобы не добавлять дополнительные разделители к пустым ячейкам.
Чтобы отсортировать объединенные значения, выберите параметр Сортировать объединенные значения.

Примечание. Инструмент использует стандартные функции Excel (СУММ, СРЗНАЧ, СЧЁТ, СЧЕТЧИК, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛОН.П, СТАНДОТКЛОН.С), поэтому, если вы столкнулись с определёнными трудностями, обратитесь к соответствующему разделу здесь.

Например, если это ваш стол:

На шаге 2 мы проверяем столбцы A и B, а на шаге 3 — столбец C.

Совет. Если вам нужно изменить настройки на предыдущих шагах, нажмите кнопку «Назад» и внесите необходимые исправления.

Нажмите "Готово", чтобы объединить дубликаты в выбранных столбцах:

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

Ultimate Suite для Excel

Этот инструмент является частью пакета Ablebits Ultimate Suite, который включает более 70 профессиональных инструментов и более 300 решений для повседневных задач.

Читайте также: