Удаление одинаковых значений таблицы в Excel
Повторяющиеся значения в Excel: как удалить дубликаты, или просто найти и выделить
Доброго времени суток!
С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.
И в любом относительно большом документе встречаются повторяющиеся строки, особенно, если вы его собрали из несколько других таблиц. Эти дубли могут очень мешать дальнейшему редактированию таблицы, а потому их нужно либо выделить, либо вообще удалить…
Собственно, ко мне ни раз и ни два обращались с подобными вопросами, и я решил оформить работу с дубликатами в отдельную небольшую статью (которую вы сейчас читаете). Приведу самые часто-встречаемые задачи, и покажу их решение.
Примечание: все примеры ниже будут представлены в Office 2016 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще ☻.
♣
Простое авто-удаление дублей строк
Представим одну из самых популярных задач: есть список строк с ФИО людей, в строках много совпадений, нужно оставить только уникальные значения (кстати, это просто пример, все совпадения с реальными людьми — случайны).
Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где «руки» будут бессильны…!).
Пример задачи в Excel
Чтобы в Excel удалить повторяющиеся строки, необходимо:
- выделить свою табличку (строки и столбцы) с данными;
Выделение таблицы в Excel
- далее перейти в раздел «Данные» и нажать по инструменту «Удалить дубликаты» (см. скрин ниже);
Данные — удалить дубликаты // Excel
- после чего появится окно с настройками: если у вас есть заголовки в таблице — рекомендую поставить галочку «Мои данные содержат заголовки». После — нажмите кнопку OK;
Удаление (выделение заголовков)
- в результате увидите сообщение: сколько строк было удалено, и сколько уникальных осталось. Пример выполненной задачи представлен ниже.
Лишние строки удалены!
♣
Копирование уникальных строк (данных) в новое место
Если вы не хотите трогать и изменять текущие данные (текущую таблицу) — то можно просто скопировать только уникальные строки (значения) из нее в новую таблицу. Это очень удобно, когда вы хотите наглядно сравнить, что было, и что получилось (а также волнуетесь за старые данные, которые еще могут пригодиться).
Как это делается:
- сначала необходимо выделить табличку, затем перейти в раздел «Данные» и нажать на кнопку «Дополнительно» (подраздел «Сортировка и фильтр», см. скриншот ниже).
Раздел «Данные» — дополнительно (Excel)
- далее переключите ползунок в режим «Скопировать результат в другое место», в строке «Поместить результат в диапазон» — выберите ячейку, где будет начало таблицы; и поставьте галочку напротив пункта «Только уникальные значения»; нажмите OK. Пример представлен на скриншоте ниже;
Скопировать только уникальные значения в другое место
- после чего вы увидите, как появились данные в выбранном вами месте, причем, строки останутся только уникальные. Дальше можно с ними работать в обычном режиме…
Было — стало
♣
Поиск и выделение повторяющихся значений
В тех случаях, когда вам не нужно удалить дубли или копировать уникальные значения в новое место — можно просто найти и подсветить их (причем, выделить можно как уникальные значения, так и повторяющиеся). Обычно, это необходимо делать, когда предстоит дальнейшая корректировка строк (их удобно и быстро можно найти).
Как выделить повторяющийся строки:
- сначала также выделяете все свои строки (пример ниже);
Выделение строк
- далее необходимо открыть раздел «» в верхнем меню Excel, выбрать подраздел «Условное форматирование», затем «Правила выделения ячеек», «Повторяющиеся значения» (пример представлен на скриншоте ниже);
Повторяющиеся значения // Раздел «» в Excel
- после чего, вам останется выбрать какие строки вы будете выделять (уникальные или повторяющиеся), и цвет выделения (по умолчанию — красно-розовый).
Выбираем цвет, как выделим строки
♣
Надеюсь, информация будет полезна для работы с большими таблицами.
На этом сегодня всё, всем удачной работы!
RSS (как читать Rss)
Полезный софт:
- МОНТАЖ
Отличное ПО для начала создания своих собственных видеороликов (все действия идут по шагам!). сделает даже новичок!
- Ускоритель компьютера
Программа для очистки Windows от мусора (ускоряет систему, удаляет мусор, оптимизирует реестр).
Как удалить дубликаты строк в Excel из таблицы? Как найти и удалить повторяющиеся значения в редакторе Excel?
Подробное руководство по удалению дубликатов из таблиц Excel несколькими способами.
Редактор Excel, который входит в пакет офисных приложений Microsoft Office, на сегодняшний день считается лучшим в своем роде инструментом для работы с различного рода таблицами. Его использую как в домашних условиях, так и в организациях, занимающихся торговлей, аудитом и другого рода деятельностью.
Чаще всего в Excel обрабатываются таблицы крайне больших объемов и порой пользователю необходимо в них найти и удалить повторяющиеся значения.
Поиск и удаление дубликатов вручную в таких таблицах может занять огромное количество времени и даже если оно у пользователя есть, то нет гарантии, что он не пропустит какое-то одно повторяющиеся значение, которое потом будет практически невозможно отыскать.
Но к счастью редактор Excel имеет обширный функционал, благодаря которому пользователи могут значительно облегчить себе работу. В нашей статье Вы найдете несколько простых способов того, как найти и удалить дублирующиеся значения из таблицы в Excel.
Изображение 1. Удаление одинаковых строчек в Excel.
Перед тем, как перейти к ознакомлению с поиском и удалением строчек, столбиков и значений в ячейках, настоятельно рекомендуется предварительно создать тестовую таблицу или копию имеющегося рабочего проекта, чтобы избежать потери ценных данных и не испортить оригинальный формат в случае какой-либо ошибки. После создания тестовой таблицы или копии имеющейся работы откройте их в редакторе Microsoft Excel и переходите к следующему этапу статьи.
Поиск и удаление дублирующихся значений в таблице Excel: первый способ
Шаг 1.
- После того, как Вы открыли тестовую таблицу в редакторе, зажмите левую клавишу мыши и выделите столбцы и строки, где требуется удалить повторы.
- Если это необходимо, то выделите всю таблицу таким же образом или с помощью комбинации клавиш «Ctrl+A».
Изображение 2. Выделение рабочей области.
Шаг 2.
- После выделения необходимых ячеек в верхней части окна редактора на панели инструментов раскройте вкладку «Данные» и в разделе «Работа с данными» щелкните по кнопке «Удалить дубликаты».
Изображение 3. Выбор инструмента для работы с таблицей.
Шаг 3.
- В открывшемся на экране окошке следует выделить галочками столбики, из которых требуется удалить повторяющиеся значения. Если в окне отображается большое количество элементов, для быстроты Вы можете использовать кнопки быстрого выделения.
- Для завершения щелкните по кнопке «ОК».
Изображение 4. Выбор столбцов и запуск процесса фильтрации.
Шаг 4.
- Какое-то время редактор будет искать и удалять имеющиеся в выделенных столбцах дубли. Как только процесс будет завершен, на экране отобразится окошко с уведомлением о том, сколько дублирующихся значений было удалено и сколько уникальных осталось.
Изображение 5. Окно с конечным результатом.
Шаг 5.
- Проверьте документ и удостоверьтесь, что все получилось так, как именно Вам хотелось. Если же Вы допустили ошибку и удалили не те значения, то отменить последнее действие можно путем нажатия на кнопку обратной стрелочки в верхней части окна редактора.
Изображение 6. Отмена последнего сделанного действия.
Поиск и удаление дублирующихся значений в таблице Excel: второй способ
Шаг 1.
- Выберите редактируемую таблицу, в верхней части основного окна редактора раскройте вкладку «Данные» и на панели инструментов в разделе «Сортировка и данные» кликните по кнопке «Дополнительно».
Изображение 7. Переход к дополнительным фильтрам данных.
Шаг 2.
- Если Вам требуется сделать отдельную таблицу, в которой будут содержаться исключительно ячейки с уникальными значениями исходной таблицы, то в открывшемся окошке «Расширенный фильтр» отметьте маркером строчку «Скопировать результат в другое место», после чего с правой стороны от поля «Поместить результат в диапазон» щелкните по кнопке выбора места.
Изображение 8. Настройка фильтра данных.
Шаг 3.
- На следующем этапе Вам потребуется выбрать область свободного рабочего пространства, куда Вы хотите добавить отфильтрованную таблицу. После выбора области необходимо закрыть поле с кодом ячейки и переключиться обратно на «Расширенный фильтр».
Изображение 9. Выбор места для вставки таблицы.
Шаг 4.
- Далее Вам потребуется отметить маркером строчку «Только уникальные записи» для извлечения их из исходной таблицы и щелкнуть по кнопке «ОК».
Изображение 10. Завершение настройки фильтра и начало процесса его работы.
Шаг 5.
- Как только фильтрация будет завершена, в указанной области отобразится версия начальной таблицы исключительно с уникальными значениями.
Изображение 11. Полученный конечный результат.
Шаг 6.
- Если Вы не хотите создавать копию таблицы на отдельной рабочей области в Excel, то в окошке «Расширенный фильтр» вместо строчки «Скопировать результат в другое место» отметьте маркером строчку «Фильтровать список на месте».
- Далее следует активировать настройку фильтра «Только уникальные записи» и щелкнуть по кнопке «ОК». В таком случае в Вашей таблице останутся столбцы и строки исключительно с уникальными значениями без создания копии.
Изображение 12. Удаление дубликатов без создания копии таблицы.
Поиск и удаление дублирующихся значений в таблице Excel: третий способ
Шаг 1.
- На главной вкладке редактора Excel необходимо перейти в раздел «Стили» и там раскрыть контекстное меню «Условное форматирование».
- Далее наведите курсор на пункт «Правила выделения ячеек» и в следующем списке выберите строчку «Повторяющиеся значения».
Изображение 13. Переход к инструменту стилизации ячеек.
Шаг 2.
- Перед Вами появится небольшое окошко, где в строчке «Значения с» необходимо выбрать наиболее подходящий цвет для подсвечивания ячеек с повторяющимися значениями.
- Выберите подходящую цветовую схему и щелкните по кнопке «ОК».
Изображение 14. Настройка стиля дублирующихся ячеек.
Шаг 3.
- Ваша таблица примет примерно такое же обличие, как на скриншоте ниже. Вручную удалите все подсвеченные дубли, выделяя их по одному или группами.
Изображение 15. Ручное удаление подкрашенных ячеек с дублями.
ВИДЕО: Как удалить повторяющие строки в Excel?
Как удалить дубликаты в Excel, найти повторяющиеся строки, убрать или выделить цветом одинаковые значения в Эксель
В работе с данными периодически возникает необходимость извлечения уникальных записей.
Для этого пользователю Excel доступно множество способов удаления повторяющихся значений и фильтрации с возможностью скрытия или отображения.
Каждый метод можно применять в повседневной работе или при обработке сложных статистических данных. При этом специальных знаний не требуется, достаточно минимальных знаний табличного процессора Excel.
Как удалить дубликаты в Excel
Для быстрого решения этой задачи пользователю доступна стандартная функция «Удаление дубликатов». В этом случае операцию можно провести только по выбранным полям.
Для того чтобы удалит дубликаты строк, необходимо:
- Выделить ячейки с нужной информацией.
- Перейти на закладку «Данные».
- Нажать «Удалить дубликаты». Появится информационное окно с указанием выделенного столбца.
- После выполнения, появится информация о количестве найденных и оставшихся элементов. В результате останутся только не повторяющиеся элементы.
Удаление дубликатов в умной таблице
В этом методе используется конструктор Excel (расширенный режим с дополнительными возможностями). Конструктор активируется при выделении любой ячейки табличной области. Как и в первом случае, после применения производится удаление дублированных значений.
Для тех, кто не знает: умная таблица – это вид форматирования, после использования которого все ячейки таблицы принимаю определенные свойства. При этом Excel рассматривает подобную таблицу, как единое целое, а не как набор ячеек и элементов.
Чтобы удалить повторяющиеся строки в такой таблице, следует:
- Установить курсор на любую ячейку области с записями.
- Перейти в «Конструктор».
- Выполнить операцию «Удалить дубликаты» (при необходимости откорректировать столбцы).
- Появится сообщение о количестве найденных и оставшихся дубликатов.
Используем фильтрацию
С помощью фильтрации, можно скрыть дублированные данные, при этом значения не удаляются — их можно вернуть в любой момент.
Как ускорить процесс завершения работы Windows
Чтобы провести форматирование и найти повторяющиеся значения таким образом следует:
- Определить необходимую область.
- На закладке «Данные» установить фильтр.
- В том же блоке сортировки и фильтрации нажать «Дополнительно» для запуска расширенного фильтра. Проверить выбранный диапазон и установить галку «Только уникальные записи».
- После выполнения процедуры, дубли будут скрыты, а фильтрации уже не будет.
- Для возврата скрытых значений, нужно нажать кнопку «Фильтр».
Условное форматирование
Этим способом можно воспользоваться для поиска одинаковых записей в ячейках. Для удобства можно настроить цвет выделения. Для реализации нужно применить предустановленное правило выделения — «Повторяющиеся значения…».
Чтобы найти дубликаты в Excel, необходимо:
- Задать область поиска.
- На главной закладке, в разделе «Условное форматирование», выбрать правило.
- Задать цвет и тип данных — уникальные или повторяющиеся.
- В результате будут выделены все повторы.
Использование формулы
С помощью встроенной функции «ПОИСКПОЗ» можно найти повторяющиеся элементы в настраиваемом диапазоне. «ПОИСКПОЗ» — возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.
Чтобы выделить повторяющиеся строки в Excel необходимо:
- Выбрать нужные ячейки.
- В разделе «Условное форматирование» создать новое правило.
- Выбрать «Использовать формулу для определения форматируемых ячеек».
- Вставить следующую формулу «=ПОИСКПОЗ(B3;$B:$B;0)СТРОКА(B3)» и задать формат (цвет, шрифт и т.д.).
Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».
- Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.
Копирование уникальных строк в новое расположение
Если требуется сохранить текущую табличную область без изменений, тогда можно применить этот способ копирования.
Виртуальная машина VMware Workstation: установка
Для выполнения, потребуется:
- Указать необходимый диапазон.
- На закладке «Данные», в разделе «Сортировка и фильтр», выбрать «Дополнительно». Установить – «скопировать результат в другое место», «только уникальные записи» и задать адреса ячеек нового места.
- Не повторяющиеся элементы будут скопированы в новое месторасположение.
Используем сводные таблицы
Для группировки дублированных элементов можно использовать сводную таблицу (более простое понятие — обобщенная).
Для работы этого способа потребуется:
- Создать новый столбец со значением счетчика «1». Выбрать всю область.
- Используя закладку «Вставка», создать новую сводную таблицу.
- Перейти на новый лист и заполнить поля «Названия строк» и «Значения». Дубли будут подсчитаны мгновенно и отображаться будут на новом листе.
Кроме этого можно ознакомиться с видео по удалению дублей в Excel.
Удаление дубликатов в Google таблицах
В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):
- используя сводные таблицы (подсчет дублированных значений);
- с помощью функции unique (вывод результата из массива);
- используя сторонние расширения;
Пример использования функции «unique»:
Для вывода уникальных записей, требуется применить формулу «=unique(диапазон проверки)»:
Удалить дубликаты в Excel
В данной статье мы разберем, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016.
Вы узнаете несколько разных способов как найти и удалить дубликаты в Excel с или без первых вхождений, удалить дубликаты строк, как удалить дубликаты в столбце, как обнаружить абсолютные дубликаты и частичные совпадения. В статье рассмотрены следующие пункты:
Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»
Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов – функцию Удалить дубликаты.
Эта функция позволяет находить и удалять абсолютные дубликаты (ячейки или целые строки), а также частично соответствующие записи (строки, которые имеют одинаковые значения в указанном столбце или столбцах). Разберем на примере, как пошагово использовать функцию Удалить дубликаты в Excel.
Примечание. Поскольку функция Удалить дубликаты навсегда удаляет идентичные записи, рекомендуется создать копию исходных данных перед удалением повторяющихся строк.
- Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A.
- Далее перейдите на вкладку «ДАННЫЕ» —> группа «Работа с данными» и нажмите кнопку «Удалить дубликаты».
Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel
- Откроется диалоговое окно «Удалить дубликаты». Выберите столбцы для проверки дубликатов и нажмите «ОК».
- Чтобы удалить дубликаты строк, имеющие полностью одинаковые значения во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на изображении ниже.
- Чтобы удалить частичные дубликаты на основе одного или нескольких ключевых столбцов, выберите только соответствующие столбцы. Если в вашей таблице много столбцов, лучше сперва нажать кнопку «Снять выделение», а затем выбрать столбцы, которые вы хотите проверить на предмет дубликатов.
- Если в вашей таблице нет заголовков, уберите флаг с поля «Мои данные содержат заголовки» в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.
Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов
Готово! Все дубликаты строк в выбранном диапазоне удалены, и отображается сообщение, указывающее, сколько было удалено дубликатов записей и сколько уникальных значений осталось.
Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов
Функция Удалить дубликаты в Excel удаляет 2-ой и все последующие дубликаты экземпляров, оставляя все уникальные строки и первые экземпляры одинаковых записей. Если вы хотите удалить дубликаты строк, включая первые вхождения, т.е.
если вы ходите удалить все дублирующие ячейки.
Или в другом случае, если есть два или более дубликата строк, и первый из них вы хотите оставить, а все последующие дубликаты удалить, то используйте одно из следующих решений описанных в этом разделе.
Удалить дубликаты, скопировав уникальные записи в другое место
Другой способ удалить дубликаты в Excel — это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.
- Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
- Перейдите во вкладку «ДАННЫЕ» —> группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно».
Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов
- В диалоговом окне «Расширенный фильтр» выполните следующие действия:
- Выберите пункт «скопировать результат в другое место».
- Проверьте, отображается ли правильный диапазон в Исходном диапазоне. Это должен быть диапазон, выбранный на шаге 1.
- В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
- Выберите Только уникальные записи
Удалить дубликаты в Excel – Фильтр дубликатов
- Наконец, нажмите «ОК». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:
Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места
Таким образом вы получаете новые данные, на основе указанных, но с удаленными дубликатами.
Обратите внимание, что расширенный фильтр позволяет копировать отфильтрованные значения в другое место только на активном листе.
Удалить дубликаты строк в Excel с помощью формул и фильтра
Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.
Преимуществом этого подхода является универсальность — он позволяет найти и удалить дубликаты в столбце или дублировать строки на основе значений в нескольких столбцах. Недостатком является то, что вам нужно будет запомнить несколько повторяющихся формул.
- В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.
Формулы для поиска дубликатов в 1 столбце
- Дубликаты за исключением 1-го вхождения:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)
- Дубликаты с 1-го вхождения:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; «Дубликат»; «Уникальное»)
Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.
Формулы для поиска дубликатов строк
- Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:
=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)
- Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:
=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)
Где A, B и C — столбцы, подлежащие проверке на дубликаты.
Например, так вы можете идентифицировать дубликаты строк, за исключением 1-го вхождения:
Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев
- Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «ДАННЫЕ», или «Сортировка и фильтр» —> «Фильтр» на вкладке «ГЛАВНАЯ».
Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)
- Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок «Дубликат строки».
Удалить дубликаты в Excel – Фильтр дубликатов строки
- И, наконец, удалите дубликаты строк.
Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку» в контекстном меню.
В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete» на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:
Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк
Ну, теперь вы узнали несколько способов, как удалить дубликаты в Excel. И можете пользоваться одним из них в зависимости от вашей ситуации.
Как удалить дубликаты в Excel
Дубликаты данных в Excel могут приводить к множеству проблем при работе с данными. Не важно, импортируете ли вы данные из какой-либо базы данных, получаете их от коллег или друзей. Чем больше данных в вашем файле, тем сложней найти и удалить дубликаты в Excel.
В этой статье мы подробно рассмотрим эффективные практики по поиску и удалению дубликатов.
Поиск и выделение дубликатов цветом в Excel
Дубликаты в таблицах могу встречаться в разных формах. Это могут быть повторяющиеся значения в одной колонке и в нескольких, а также в одной или нескольких строках.
Поиск и выделение дубликатов цветом в одном столбце в Эксель
Самый простой способ найти и выделить цветом дубликаты в Excel, это использовать условное форматирование.
Как это сделать:
- Выделим область с данными, в которой нам нужно найти дубликаты:
- На вкладке “” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:
- После этого, в выделенной колонке, будут подсвечены цветом дубликаты:
Подсказка: не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).
Поиск и выделение дубликатов цветом в нескольких столбцах в Эксель
Если вам нужно вычислить дубликаты в нескольких столбцах, то процесс по их вычислению такой же как в описанном выше примере. Единственное отличие, что для этого вам нужно выделить уже не одну колонку, а несколько:
- Выделите колонки с данными, в которых нужно найти дубликаты;
- На вкладке “” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом дубликаты:
Поиск и выделение цветом дубликатов строк в Excel
Поиск дубликатов повторяющихся ячеек и целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали дубликаты ячеек, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти дубликаты строк:
- Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения повторяющихся строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15);
- На вкладке “” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
На примере выше, мы выделили строки в созданной вспомогательной колонке.
Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?
Для этого давайте сделаем следующее:
- Также как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:
=A2&B2&C2&D2
Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:
- Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
- Затем, на вкладке “” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
- В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:
=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1
- Не забудьте задать формат найденных дублированных строк.
Эта формула проверяет диапазон данных во вспомогательной колонке и при наличии повторяющихся строк выделяет их цветом в таблице:
Как убрать дубликаты в Excel
Выше мы изучили как найти дубликаты и как их выделить цветом. Ниже вы узнаете как их удалить.
Как удалить дубликаты в одном столбце Эксель
Если ваши данные размещены в одном столбце и вы хотите удалить все дубликаты, то проделайте следующие действия:
- Выделите данные;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки”, если в выделенном вами диапазоне столбца есть заголовок. Также, убедитесь, что в меню “Колонны” выделен необходимый вам столбец:
После этого система удалит все дубликаты в столбце, оставив только уникальные значения.
Совет. Обязательно делайте резервную копию ваших данных перед любыми операциями с удалением дубликатов. Можно также проводить удаление дубликатов на отдельных листах, во избежание случайного удаления данных.
Как удалить дубликаты в нескольких столбцах в Excel
Представим, что у нас есть данные продаж как в таблице ниже:
Желтым цветом выделены строки, в которых имена, регион и сумма продаж одинаковы, но даты разные. Скорее всего, это связано с ошибкой ввода дынных в таблице. Если нам требуется удалить дубликаты данных таблицы в отдельных колонках, то проделайте следующие действия:
- Выделите данные таблицы;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того, чтобы удалить дубликаты во всех столбцах кроме даты, оставьте поле с датой пустым:
После этого система удалит строки с данными, в которых дублируются значения в колонках “Имя”, “Регион”, “Продажи”.
Как удалить дублирующие строки с данными в Эксель
Если вам нужно удалить целые строки дублирующиеся в таблице, проделайте следующие шаги:
- Выделите данные таблицы;
- Перейдите на Панели инструментов во вкладку “Данные” – > раздел “Работа с данными” -> “Удалить дубликаты”:
- В диалоговом окне “Удалить дубликаты” поставьте флажок напротив пункта “Мои данные содержат заголовки” если в выделенном вами диапазоне есть заголовки. Для того чтобы система сравнила внутри таблицы строки, важно убедиться что отмечены все столбцы в диалоговом окне:
Используйте вышеупомянутые методы для очистки данных и избавления от дубликатов.
Как в Excel сравнить два столбца и удалить дубликаты (выделить, раскрасить, переместить) — Microsoft Excel для начинающих
Чтение этой статьи займёт у Вас около 10 минут. В следующие 5 минут Вы сможете легко сравнить два столбца в Excel и узнать о наличии в них дубликатов, удалить их или выделить цветом. Итак, время пошло!
Excel – это очень мощное и действительно крутое приложение для создания и обработки больших массивов данных.
Если у Вас есть несколько рабочих книг с данными (или только одна огромная таблица), то, вероятно, Вы захотите сравнить 2 столбца, найти повторяющиеся значения, а затем совершить с ними какие-либо действия, например, удалить, выделить цветом или очистить содержимое. Столбцы могут находиться в одной таблице, быть смежными или не смежными, могут быть расположены на 2-х разных листах или даже в разных книгах.
Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B. Необходимо сравнить имена в этих двух столбцах и найти повторяющиеся. Как Вы понимаете, это вымышленные данные, взятые исключительно для примера. В реальных таблицах мы имеем дело с тысячами, а то и с десятками тысяч записей.
Вариант А: оба столбца находятся на одном листе. Например, столбец A и столбец B.
Вариант В: Столбцы расположены на разных листах. Например, столбец A на листе Sheet2 и столбец A на листе Sheet3.
В Excel 2013, 2010 и 2007 есть встроенный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в такой ситуации, поскольку не может сравнивать данные в 2 столбцах. Более того, он может только удалить дубликаты. Других вариантов, таких как выделение или изменение цвета, не предусмотрено. И точка!
Далее я покажу Вам возможные пути сравнения двух столбцов в Excel, которые позволят найти и удалить повторяющиеся записи.
Вариант А: оба столбца находятся на одном листе
- В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),»Unique»,»Duplicate»)
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));»Unique»;»Duplicate»)В нашей формуле A1 это первая ячейка первого столбца, который мы собираемся сравнивать.
$B$1 и $B$10000 это адреса первой и последней ячеек второго столбца, с которым будем выполнять сравнение.
Обратите внимание на абсолютные ссылки – буквам столбца и номерам строки предшествует знак доллара ($). Я использую абсолютные ссылки для того, чтобы адреса ячеек оставались неизменными при копировании формул.
Если Вы хотите найти дубликаты в столбце B, поменяйте ссылки, чтобы формула приняла такой вид:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),»Unique»,»Duplicate»)
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));»Unique»;»Duplicate»)Вместо «Unique» и «Duplicate» Вы можете записать собственные метки, например, «Не найдено» и «Найдено«, или оставить только «Duplicate» и ввести символ пробела вместо второго значения. В последнем случае ячейки, для которых дубликаты найдены не будут, останутся пустыми, и, я полагаю, такое представление данных наиболее удобно для дальнейшего анализа.
- Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:Нажмите и, удерживая левую кнопку мыши, протащите границу рамки вниз, выделяя все ячейки, в которые требуется вставить формулу. Когда все необходимые ячейки будут выделены, отпустите кнопку мыши:
Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш.
Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).
- Отлично, теперь все повторяющиеся значения отмечены как «Duplicate«:
Вариант В: два столбца находятся на разных листах (в разных книгах)
- В первой ячейке первого пустого столбца на листе Sheet2 (в нашем случае это столбец В) введите такую формулу:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),»»,»Duplicate»)
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));»»;»Duplicate»)Здесь Sheet3 – это название листа, на котором расположен 2-ой столбец, а $A$1:$A$10000 – это адреса ячеек от 1-ой до последней в этом 2-ом столбце.
- Скопируйте формулу во все ячейки столбца B (как и в варианте А).
- У нас получается вот такой результат:
Обработка найденных дубликатов
Отлично, мы нашли записи в первом столбце, которые также присутствуют во втором столбце. Теперь нам нужно что-то с ними делать. Просматривать все повторяющиеся записи в таблице вручную довольно неэффективно и занимает слишком много времени. Существуют пути получше.
Показать только повторяющиеся строки в столбце А
Если Ваши столбцы не имеют заголовков, то их необходимо добавить. Для этого поместите курсор на число, обозначающее первую строку, при этом он превратится в чёрную стрелку, как показано на рисунке ниже:
Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):
Дайте названия столбцам, например, «Name» и «Duplicate?» Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):
После этого нажмите меленькую серую стрелку рядом с «Duplicate?«, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.Вот и всё, теперь Вы видите только те элементы столбца А, которые дублируются в столбце В. В нашей учебной таблице таких ячеек всего две, но, как Вы понимаете, на практике их встретится намного больше.
Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) >Select & Filter (Сортировка и фильтр) >Clear (Очистить), как показано на снимке экрана ниже:
Изменение цвета или выделение найденных дубликатов
Если пометки «Duplicate» не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…
В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек).
В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый.
Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.
Теперь Вы точно не пропустите ни одной ячейки с дубликатами:
Удаление повторяющихся значений из первого столбца
Отфильтруйте таблицу так, чтобы показаны были только ячейки с повторяющимися значениями, и выделите эти ячейки.
Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):
Нажмите ОК, когда Excel попросит Вас подтвердить, что Вы действительно хотите удалить всю строку листа и после этого очистите фильтр. Как видите, остались только строки с уникальными значениями:
Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее.
Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже.
Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:- Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
- Очистите фильтр.
- Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
- Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
- Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
- Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:
Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.
Оцените качество статьи. Нам важно ваше мнение: