ВсёПросто

Удаление одинаковых значений таблицы в Excel

ovdmitjb

Содержание

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

Доброго времени суток!

С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.

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

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

Примечание: все примеры ниже будут представлены в Office 2016 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще ☻.

♣

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

Представим одну из самых популярных задач: есть список строк с ФИО людей, в строках много совпадений, нужно оставить только уникальные значения (кстати, это просто пример, все совпадения с реальными людьми — случайны).

Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где «руки» будут бессильны…!).

Пример задачи в Excel

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

  1. выделить свою табличку (строки и столбцы) с данными;

    Выделение таблицы в Excel

  2. далее перейти в раздел «Данные» и нажать по инструменту «Удалить дубликаты» (см. скрин ниже);

    Данные — удалить дубликаты // Excel

  3. после чего появится окно с настройками: если у вас есть заголовки в таблице — рекомендую поставить галочку «Мои данные содержат заголовки». После — нажмите кнопку OK;

    Удаление (выделение заголовков)

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

    Лишние строки удалены!

♣

Копирование уникальных строк (данных) в новое место

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

Как это делается:

  1. сначала необходимо выделить табличку, затем перейти в раздел «Данные» и нажать на кнопку «Дополнительно» (подраздел «Сортировка и фильтр», см. скриншот ниже).

    Раздел «Данные» — дополнительно (Excel)

  2. далее переключите ползунок в режим «Скопировать результат в другое место», в строке «Поместить результат в диапазон» — выберите ячейку, где будет начало таблицы; и поставьте галочку напротив пункта «Только уникальные значения»; нажмите OK. Пример представлен на скриншоте ниже;

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

  3. после чего вы увидите, как появились данные в выбранном вами месте, причем, строки останутся только уникальные. Дальше можно с ними работать в обычном режиме…

    Было — стало

♣

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

В тех случаях, когда вам не нужно удалить дубли или копировать уникальные значения в новое место — можно просто найти и подсветить их (причем, выделить можно как уникальные значения, так и повторяющиеся). Обычно, это необходимо делать, когда предстоит дальнейшая корректировка строк (их удобно и быстро можно найти).

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

  1. сначала также выделяете все свои строки (пример ниже);

    Выделение строк

  2. далее необходимо открыть раздел «» в верхнем меню Excel, выбрать подраздел «Условное форматирование», затем «Правила выделения ячеек», «Повторяющиеся значения» (пример представлен на скриншоте ниже);

    Повторяющиеся значения // Раздел «» в Excel

  3. после чего, вам останется выбрать какие строки вы будете выделять (уникальные или повторяющиеся), и цвет выделения (по умолчанию — красно-розовый).

    Выбираем цвет, как выделим строки

♣

Надеюсь, информация будет полезна для работы с большими таблицами.

На этом сегодня всё, всем удачной работы!

RSS (как читать Rss)

Полезный софт:

Как удалить дубликаты строк в Excel из таблицы? Как найти и удалить повторяющиеся значения в редакторе Excel?

Подробное руководство по удалению дубликатов из таблиц Excel несколькими способами.

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

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

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

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

Изображение 1. Удаление одинаковых строчек в Excel.

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

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

Шаг 1.

Изображение 2. Выделение рабочей области.

Шаг 2.

Изображение 3. Выбор инструмента для работы с таблицей.

Шаг 3.

Изображение 4. Выбор столбцов и запуск процесса фильтрации.

Шаг 4.

Изображение 5. Окно с конечным результатом.

Шаг 5.

Изображение 6. Отмена последнего сделанного действия.

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

Шаг 1.

Изображение 7. Переход к дополнительным фильтрам данных.

Шаг 2.

Изображение 8. Настройка фильтра данных.

Шаг 3.

Изображение 9. Выбор места для вставки таблицы.

Шаг 4.

Изображение 10. Завершение настройки фильтра и начало процесса его работы.

Шаг 5.

Изображение 11. Полученный конечный результат.

Шаг 6.

Изображение 12. Удаление дубликатов без создания копии таблицы.

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

Шаг 1.

Изображение 13. Переход к инструменту стилизации ячеек.

Шаг 2.

Изображение 14. Настройка стиля дублирующихся ячеек.

Шаг 3.

Изображение 15. Ручное удаление подкрашенных ячеек с дублями.

ВИДЕО: Как удалить повторяющие строки в Excel?

Как удалить дубликаты в Excel, найти повторяющиеся строки, убрать или выделить цветом одинаковые значения в Эксель

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

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

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

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

Для быстрого решения этой задачи пользователю доступна стандартная функция «Удаление дубликатов». В этом случае операцию можно провести только по выбранным полям.

Для того чтобы удалит дубликаты строк, необходимо:

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

Удаление дубликатов в умной таблице

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

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

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

  1. Установить курсор на любую ячейку области с записями.
  2. Перейти в «Конструктор».
  3. Выполнить операцию «Удалить дубликаты» (при необходимости откорректировать столбцы).
  4. Появится сообщение о количестве найденных и оставшихся дубликатов.

Используем фильтрацию

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

  Как ускорить процесс завершения работы Windows

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

  1. Определить необходимую область.
  2. На закладке «Данные» установить фильтр.
  3. В том же блоке сортировки и фильтрации нажать «Дополнительно» для запуска расширенного фильтра. Проверить выбранный диапазон и установить галку «Только уникальные записи».
  4. После выполнения процедуры, дубли будут скрыты, а фильтрации уже не будет.
  5. Для возврата скрытых значений, нужно нажать кнопку «Фильтр».

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

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

Чтобы найти дубликаты в Excel, необходимо:

  1. Задать область поиска.
  2. На главной закладке, в разделе «Условное форматирование», выбрать правило.
  3. Задать цвет и тип данных — уникальные или повторяющиеся.
  4. В результате будут выделены все повторы.

Использование формулы

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

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

  1. Выбрать нужные ячейки.
  2. В разделе «Условное форматирование» создать новое правило.
  3. Выбрать «Использовать формулу для определения форматируемых ячеек».
  4. Вставить следующую формулу «=ПОИСКПОЗ(B3;$B:$B;0)СТРОКА(B3)» и задать формат (цвет, шрифт и т.д.).

Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».

  1. Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.

Копирование уникальных строк в новое расположение

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

  Виртуальная машина VMware Workstation: установка

Для выполнения, потребуется:

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

Используем сводные таблицы

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

Для работы этого способа потребуется:

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

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

Удаление дубликатов в Google таблицах

В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):

Пример использования функции «unique»:

Для вывода уникальных записей, требуется применить формулу «=unique(диапазон проверки)»:

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

В данной статье мы разберем, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016.

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

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов – функцию Удалить дубликаты.

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

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

  1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A.
  2. Далее перейдите на вкладку «ДАННЫЕ» —> группа «Работа с данными» и нажмите кнопку «Удалить дубликаты».

Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel

  1. Откроется диалоговое окно «Удалить дубликаты». Выберите столбцы для проверки дубликатов и нажмите «ОК».

Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов

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

Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов

Функция Удалить дубликаты в Excel удаляет 2-ой и все последующие дубликаты экземпляров, оставляя все уникальные строки и первые экземпляры одинаковых записей. Если вы хотите удалить дубликаты строк, включая первые вхождения, т.е.

если вы ходите удалить все дублирующие ячейки.

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

Удалить дубликаты, скопировав уникальные записи в другое место

Другой способ удалить дубликаты в Excel — это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

  1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
  2. Перейдите во вкладку «ДАННЫЕ» —> группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно».

Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов

  1. В диалоговом окне «Расширенный фильтр» выполните следующие действия:

Удалить дубликаты в Excel – Фильтр дубликатов

  1. Наконец, нажмите «ОК». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:

Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

Таким образом вы получаете новые данные, на основе указанных, но с удаленными дубликатами.

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.

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

  1. В зависимости от вашей задачи используйте одну из следующих формул для поиска дубликатов.

Формулы для поиска дубликатов в 1 столбце

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; «Дубликат»; «Уникальное»)

Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, подлежащие проверке на дубликаты.

Например, так вы можете идентифицировать дубликаты строк, за исключением 1-го вхождения:

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев

  1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «ДАННЫЕ», или «Сортировка и фильтр» —> «Фильтр» на вкладке «ГЛАВНАЯ».

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

  1. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок «Дубликат строки».

Удалить дубликаты в Excel – Фильтр дубликатов строки

  1. И, наконец, удалите дубликаты строк.

    Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку» в контекстном меню.

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

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

Ну, теперь вы узнали несколько способов, как удалить дубликаты в Excel. И можете пользоваться одним из них в зависимости от вашей ситуации.

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

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

В этой статье мы подробно рассмотрим эффективные практики по поиску и удалению дубликатов.

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

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

Поиск и выделение дубликатов цветом в одном столбце в Эксель

Самый простой способ найти и выделить цветом дубликаты в Excel, это использовать условное форматирование.

Как это сделать:

Подсказка: не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).

Поиск и выделение дубликатов цветом в нескольких столбцах в Эксель

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

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

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

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

Рассмотрим как найти дубликаты строк:

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения повторяющихся строк в таблице сделайте следующие шаги:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого давайте сделаем следующее:

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

=СЧЁТЕСЛИ($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, которые позволят найти и удалить повторяющиеся записи.

Вариант А: оба столбца находятся на одном листе

  1. В первой пустой ячейке (в нашем примере это ячейка 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» и ввести символ пробела вместо второго значения. В последнем случае ячейки, для которых дубликаты найдены не будут, останутся пустыми, и, я полагаю, такое представление данных наиболее удобно для дальнейшего анализа.

  2. Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:Нажмите и, удерживая левую кнопку мыши, протащите границу рамки вниз, выделяя все ячейки, в которые требуется вставить формулу. Когда все необходимые ячейки будут выделены, отпустите кнопку мыши:

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

Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

  1. Отлично, теперь все повторяющиеся значения отмечены как «Duplicate«:

Вариант В: два столбца находятся на разных листах (в разных книгах)

  1. В первой ячейке первого пустого столбца на листе 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-ом столбце.

  2. Скопируйте формулу во все ячейки столбца B (как и в варианте А).
  3. У нас получается вот такой результат:

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

Кликните правой кнопкой мыши и в контекстном меню выберите 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 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее.

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

Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:
  1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
  2. Очистите фильтр.
  3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
  4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
  5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
  6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

Оцените качество статьи. Нам важно ваше мнение:

No related posts.