Excel: как удалить повторяющиеся строки на основе двух столбцов
Часто вам может понадобиться удалить повторяющиеся строки на основе двух столбцов в Excel.
К счастью, это легко сделать с помощью функции « Удалить дубликаты » на вкладке « Данные ».
В следующем примере показано, как использовать эту функцию на практике.
Пример: удаление дубликатов на основе двух столбцов в Excel
Предположим, у нас есть следующий набор данных, который показывает название команды и положение различных баскетболистов:
Обратите внимание, что в обоих столбцах есть несколько дубликатов.
- Есть несколько игроков, у которых есть команда A и позиция Guard .
- Есть несколько игроков, у которых есть команда A и позиция Forward .
Чтобы удалить эти строки, которые имеют дубликаты в двух столбцах, нам нужно выделить диапазон ячеек A1: B16 , а затем щелкнуть вкладку « Данные » на верхней ленте, а затем нажать « Удалить дубликаты »:
В появившемся новом окне убедитесь, что установлен флажок рядом с Мои данные имеют заголовки и убедитесь, что оба поля рядом с Команда и Позиция отмечены:
Как только вы нажмете OK , строки с повторяющимися значениями в обоих столбцах будут автоматически удалены:
Excel сообщает нам, что найдено и удалено 6 повторяющихся строк и что осталось 9 уникальных строк.
Обратите внимание, что ни в одной из оставшихся строк нет повторяющихся значений в обоих столбцах.
- Есть только одна строка, где Team равна A , а Position равна Guard .
- Есть только одна строка, где Team равна A , а Position равна Forward .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Сохранение или удаление повторяющихся строк (Power Query)
При формировании данных распространенной задачей является сохранение или удаление повторяющихся строк. Результаты основаны на столбцах, выбранных в качестве сравнения для определения повторяющихся значений, на основе выбранных данных.
Удаление повторяющихся строк
- Чтобы открыть запрос, найдите ранее загруженную из Редактор Power Query, выделите ячейку в данных, а затем выберите запрос>изменить. Дополнительные сведения см. в статье «Создание, загрузка и изменение запроса в Excel».
- Выберите столбец, щелкнув заголовок столбца. Чтобы выбрать несколько столбцов, нажмите клавиши SHIFT+ЩЕЛЧОК или CTRL+ Щелкните каждый последующий столбец. Столбцы могут быть непрерывными или неуявными.
- Выберите «Главная >удалить строки>удалить дубликаты.
Сохранение повторяющихся строк
- Чтобы открыть запрос, найдите ранее загруженную из Редактор Power Query, выделите ячейку в данных, а затем выберите запрос>изменить. Дополнительные сведения см. в статье «Создание, загрузка и изменение запроса в Excel».
- Выберите столбец, щелкнув заголовок столбца. Чтобы выбрать несколько столбцов непрерывно или неопределяемо, нажмите клавиши SHIFT+CLICK или CTRL+Click для каждого последующего столбца.
- Выберите home >keep rows >KeepDuplicates (Сохранить повторяющиеся строки).
Удаление дубликатов в Excel с помощью таблиц
Возможности функций авто-таблицы позволяют сравнивать значения и устранять их дубликаты. Сразу стоит отметить, что одинаковые числовые значения с разным форматом ячеек в Excel воспринимаются как разные. Рассмотрим это правило на конкретном примере при удалении дубликатов.
Как удалить дубликаты в Excel
Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.
Устранение дубликатов на основе значений колонки с текстом:
- Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
- Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
- В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».
Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.
Альтернативные способы удаления дубликатов
В Excel существуют и другие средства для работы с дублированными значениями. Например:
- «Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
- «Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
- «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
- Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
- Формула в массиве: 1;0;1);0));»»)’ >. Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13). Обратите внимание, что формула отображается в фигурных скобках <>, а значит она выполняется в массиве. Поэтому ее нужно вводить комбинацией горячих клавиш CTRL+SHIFT+Enter.
Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Фильтрация или удаление повторяющихся значений
Фильтрация уникальных значений и удаление повторяющихся — это две тесно связанные друг с другом задачи, поскольку в результате их выполнения отображается список уникальных значений. Однако между этими двумя задачами существует важное различие. При фильтрации уникальных значений повторяющиеся значения временно скрываются, тогда как в ходе удаления повторяющихся значений они удаляются без возможности восстановления. Значение считается повторяющимся, если все значения в одной строке полностью совпадают со значениями в другой. Повторяющиеся значения определяются значением, которое отображается в ячейке, а не тем, которое в ней хранится. Например, если в различных ячейках содержатся одинаковые значения даты в разных форматах («08.12.2010» и «8 дек 2010»), они считаются уникальными. Рекомендуется сначала отфильтровать уникальные значения или применить к ним условное форматирование, чтобы перед удалением повторяющихся значений убедиться в том, что будет получен ожидаемый результат.
Примечание: Если формулы в ячейках разные, а значения одинаковые, такие значения считаются повторяющимися. Например, если в ячейке A1 содержится формула =2-1, а в ячейке A2 — формула =3-2 и к ячейкам применено одинаковое форматирование, такие значения считаются повторяющимися. Одинаковые значения, к которым применены различные числовые форматы, не считаются повторяющимися. Например, если значение в ячейке A1 имеет формат 1,00, а в ячейке A2 — формат 1, эти значения не являются повторяющимися.
Фильтрация уникальных значений
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
- Выполните одно из указанных ниже действий.
| Задача | Необходимые действия |
|---|---|
| Фильтрация диапазона ячеек или таблицы на месте | Выделите диапазон ячеек и щелкните Фильтровать список на месте. |
| Копирование результатов фильтрации в другое место | Выделите диапазон ячеек, щелкните Скопировать результат в другое место, а затем в поле Копировать в введите ссылку на ячейку. |
Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.
Дополнительные параметры
Удаление повторяющихся значений
При удалении повторяющихся значений данные удаляются только из выделенного диапазона ячеек или таблицы. Любые другие значения, которые находятся за пределами этого диапазона ячеек или таблицы, не изменяются и не перемещаются. Так как данные удаляются без возможности восстановления, перед удалением повторяющихся записей рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или в другую книгу.
Примечание: Нельзя удалить повторяющиеся значения, если выделенные фрагмент содержит структурированные данные или промежуточные итоги. Перед удалением повторяющихся значений нужно удалить структуру и промежуточные итоги.
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Работа с данными нажмите кнопку Удалить дубликаты.
- Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Удалить дубликаты.
Совет: Если в диапазоне ячеек или таблице содержится много столбцов, а нужно выбрать только несколько из них, снимите флажок Выделить все и выделите только нужные столбцы.
Применение условного форматирования к уникальным или повторяющимся значениям
Для наглядного отображения уникальных или повторяющихся значений к ним можно применить условное форматирование. Например, выделение повторяющихся данных определенным цветом помогает найти и (при необходимости) удалить их.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование, наведите указатель на пункт Правила выделения ячеек и выберите Повторяющиеся значения.
- В диалоговом окне Создать правило форматирования выберите нужные параметры и нажмите кнопку ОК.
Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям
Вы можете создать правило для выделения уникальных или повторяющихся значений на листе определенным цветом. Это особенно полезно, когда в данных содержится несколько наборов повторяющихся значений.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Создать правило.
- В списке Стиль выберите пункт Классический, а затем в списке Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
- В списке значения в выбранном диапазоне выберите пункт уникальные или повторяющиеся.
- В списке Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.
Изменение правил расширенного условного форматирования
Вы можете отредактировать существующее правило, чтобы изменить условное форматирование, применяемое к уникальным или повторяющимся данным.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в группе Стили щелкните Условное форматирование и выберите пункт Управление правилами.
- Убедитесь, что в списке Показать правила форматирования для выбран соответствующий лист или таблица.
- Выберите правило и нажмите кнопку Изменить правило.
- Выберите нужные параметры и нажмите кнопку ОК.
Фильтрация уникальных значений
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Сортировка и фильтр щелкните стрелку рядом с элементом Фильтр и выберите пункт Расширенный фильтр.
- Выполните одно из указанных ниже действий.
| Задача | Необходимые действия |
|---|---|
| Фильтрация диапазона ячеек или таблицы на месте | Выделите диапазон ячеек и щелкните Фильтровать список на месте. |
| Копирование результатов фильтрации в другое место | Выделите диапазон ячеек, щелкните Скопировать результат в другое место, а затем в поле Копировать в введите ссылку на ячейку. |
Примечание: При копировании результатов фильтрации в другое место будут скопированы уникальные значения из выбранного диапазона. Исходные данные при этом не изменятся.
Дополнительные параметры
Удаление повторяющихся значений
При удалении повторяющихся значений данные удаляются только из выделенного диапазона ячеек или таблицы. Любые другие значения, которые находятся за пределами этого диапазона ячеек или таблицы, не изменяются и не перемещаются. Так как данные удаляются без возможности восстановления, перед удалением повторяющихся записей рекомендуется скопировать исходный диапазон ячеек или таблицу на другой лист или в другую книгу.
Примечание: Нельзя удалить повторяющиеся значения, если выделенные фрагмент содержит структурированные данные или промежуточные итоги. Перед удалением повторяющихся значений нужно удалить структуру и промежуточные итоги.
- Выделите диапазон ячеек или убедитесь в том, что активная ячейка находится в таблице.
- На вкладке Данные в разделе Сервис нажмите кнопку Удалить дубликаты.
- Установите один или несколько флажков, соответствующих столбцам таблицы, и нажмите кнопку Удалить дубликаты. Появится либо сообщение о том, сколько повторяющихся значений было удалено и сколько уникальных осталось, либо сообщение о том, что ни одного повторяющегося значения не было удалено.
Совет: Если в диапазоне ячеек или таблице содержится много столбцов, а нужно выбрать только несколько из них, снимите флажок Выделить все и выделите только нужные столбцы.
Применение условного форматирования к уникальным или повторяющимся значениям
Для наглядного отображения уникальных или повторяющихся значений к ним можно применить условное форматирование. Например, выделение повторяющихся данных определенным цветом помогает найти и (при необходимости) удалить их.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с элементом Условное форматирование, выберите пункт Правила выделения ячеек, а затем — пункт Повторяющиеся значения.
- Выберите нужные параметры и нажмите кнопку ОК.
Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям
Вы можете создать правило для выделения уникальных или повторяющихся значений на листе определенным цветом. Это особенно полезно, когда в данных содержится несколько наборов повторяющихся значений.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Создать правило.
- Во всплывающем меню Стиль выберите пункт Классический, а затем во всплывающем меню Форматировать только первые или последние значения выберите пункт Форматировать только уникальные или повторяющиеся значения.
- В меню значения в выбранном диапазоне выберите уникальные или повторяющиеся.
- В меню Форматировать с помощью выберите нужный вариант форматирования уникальных или повторяющихся значений.
Изменение правил расширенного условного форматирования
Вы можете отредактировать существующее правило, чтобы изменить условное форматирование, применяемое к уникальным или повторяющимся данным.
- Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
- На вкладке Главная в разделе Формат щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.
- Убедитесь, что в меню Показать правила форматирования для выбран соответствующий лист или таблица.
- Выберите правило и нажмите кнопку Изменить правило.
- Выберите нужные параметры и нажмите кнопку ОК.