Как удалить повторяющиеся значения в 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. В диалоговом окне Создать правило форматирования выберите нужные параметры и нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Часто вам может понадобиться удалить повторяющиеся строки на основе двух столбцов в Excel.

К счастью, это легко сделать с помощью функции « Удалить дубликаты » на вкладке « Данные ».

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

Пример: удаление дубликатов на основе двух столбцов в Excel

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

Обратите внимание, что в обоих столбцах есть несколько дубликатов.

• Есть несколько игроков, у которых есть команда A и позиция Guard .
• Есть несколько игроков, у которых есть команда A и позиция Forward .

Чтобы удалить эти строки, которые имеют дубликаты в двух столбцах, нам нужно выделить диапазон ячеек A1: B16 , а затем щелкнуть вкладку « Данные » на верхней ленте, а затем нажать « Удалить дубликаты »:

В появившемся новом окне убедитесь, что установлен флажок рядом с Мои данные имеют заголовки и убедитесь, что оба поля рядом с Команда и Позиция отмечены:

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

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

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

• Есть только одна строка, где Team равна A , а Position равна Guard .
• Есть только одна строка, где Team равна A , а Position равна Forward .

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

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

Удаление дубликатов в Excel с помощью таблиц

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

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

Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.

Устранение дубликатов на основе значений колонки с текстом:

1. Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
2. Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
3. В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.

Альтернативные способы удаления дубликатов

В Excel существуют и другие средства для работы с дублированными значениями. Например:

1. «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
2. «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
3. «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
4. Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
5. Формула в массиве: 1;0;1);0));»»)’ >. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13). Обратите внимание, что формула отображается в фигурных скобках <>, а значит она выполняется в массиве. Поэтому ее нужно вводить комбинацией горячих клавиш CTRL+SHIFT+Enter.

Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Удаление повторяющихся строк в Эксель: самые быстрые методы

Чтобы избежать ошибок при работе с таблицами, надо уметь удалять дубликаты в Excel. Для этого существует несколько способов.

Редактор таблиц MS Excel применяют в разных целях — для составления отчетов и планов, хранения данных о товарах и ценах. Если документ одновременно редактируют несколько человек или он создавался из нескольких файлов, не исключена вероятность повторения и отдельных элементов, и целых строк. Из-за такого удвоения могут появляться проблемы — неправильно считаются суммы и количество предметов.

Функция удаления дубликатов

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

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

Порядок действия для устранения повторов в таблице следующий:

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

С помощью такой методики можно удалить те строки, которые полностью совпадают друг с другом. Обычно это происходит при копировании информации из 2-3 и более файлов в одну таблицу. Но иногда возникает необходимость удалить дубли в Экселе, где информация совпадает только частично. Это может быть, например, каталог товаров, где есть одни и те же наименования с отличающимися ценами.

Простое удаление не позволит устранить повторы, если стоимость будет другой. Избежать ошибки можно, выбрав при настройке удаления дубликатов только те столбцы, которые будут сравниваться. Например, «Название» и «Марка» — но без «Цены» и «Количества».

Применение расширенного фильтра

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

Чтобы использовать фильтр, необходимо:

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

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

Выделение повторов

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

• Выделить таблицу, из которой удаляются повторы.
• Перейти на главной вкладке в группу стилей и выбрать условное форматирование.
• Перейти к правилам выделения ячеек и выбрать пункт «Повторяющиеся значения».
• Указать цвет текста и заливки, которыми будут выделены повторяющиеся сведения. Лучше всего сделать выделение заметно отличающимся от основного текста — так будет проще не пропустить дубликаты для удаления вручную.

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

Создание сводной таблицы

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

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

• Добавить дополнительный столбец, назвав его, например, «Счетчик», и заполнить его единицами до конца таблицы.
• На вкладке «Вставка» в группе таблиц выбрать «Сводную таблицу» и установить галочки так, чтобы она оказалась на новом листе.
• Перетащить мышкой названия всех столбцов, кроме последнего, в поле названий.
• Перетащить «Счетчик» в поле значений.

Теперь у пользователя получится сводная таблица, в которой все дубликаты будут сгруппированы вместе. Узнать о том, что среди информации есть повторы, можно по цифрам в последнем столбце. Если там будет не единица — значит, есть дубликаты.

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

• Как сделать рамку в Ворде: 2 простых способа
• Как отключить спящий режим и гибернацию в Windows 10