Как в Excel сопоставить два столбца и удалить дубликаты (выделить, раскрасить, переместить)

Как в Excel сопоставить два столбца и удалить дубликаты (выделить, раскрасить, переместить)

Чтение данной для нас статьи займёт у Вас около 10 минут. В последующие 5 минут Вы можете просто сопоставить два столбца в Excel и выяснить о наличии в их дубликатов, удалить их либо выделить цветом. Итак, время пошло!

Excel – это весьма массивное и вправду крутое приложение для сотворения и обработки огромных массивов данных. Если у Вас есть несколько рабочих книжек с данными (либо лишь одна большущая таблица), то, возможно, Вы возжелаете сопоставить 2 столбца, отыскать повторяющиеся значения, а потом совершить с ними какие-либо деяния, к примеру, удалить, выделить цветом либо очистить содержимое. Столбцы могут находиться в одной таблице, быть смежными либо не смежными, могут быть размещены на 2-х различных листах либо даже в различных книжках.

Представьте, что у нас есть 2 столбца с именами людей – 5 имён в столбце A и 3 имени в столбце B. Нужно сопоставить имена в этих 2-ух столбцах и отыскать повторяющиеся. Как Вы осознаете, это измышленные данные, взятые только для примера. В настоящих таблицах мы имеем дело с тыщами, а то и с десятками тыщ записей.

Вариант А: оба столбца находятся на одном листе. К примеру, столбец A и столбец B.

Вариант В: Столбцы размещены на различных листах. К примеру, столбец A на листе Sheet2 и столбец A на листе Sheet3.

В Excel 2013, 2010 и 2007 есть интегрированный инструмент Remove Duplicate (Удалить дубликаты), но он бессилен в таковой ситуации, так как не может ассоциировать данные в 2 столбцах. Наиболее того, он может лишь удалить дубликаты. Остальных вариантов, таковых как выделение либо изменение цвета, не предвидено. И точка!

Дальше я покажу Для вас вероятные пути сопоставления 2-ух столбцов в Excel, которые дозволят отыскать и удалить повторяющиеся записи.

• Сравниваем 2 столбца и отыскиваем дубликаты с помощью формул

Сравниваем 2 столбца в Excel и находим повторяющиеся записи с помощью формул

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

1. В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:

В нашей формуле A1 это 1-ая ячейка первого столбца, который мы собираемся ассоциировать. $B$1 и $B$10000 это адреса первой и крайней ячеек второго столбца, с которым будем делать сопоставление. Направьте внимание на абсолютные ссылки – буковкам столбца и номерам строчки предшествует символ бакса ($). Я использую абсолютные ссылки для того, чтоб адреса ячеек оставались постоянными при копировании формул.

Если Вы желаете отыскать дубликаты в столбце B, поменяйте ссылки, чтоб формула приняла таковой вид:

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

1. Непревзойденно, сейчас все повторяющиеся значения отмечены как “Duplicate“:

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

1. В первой ячейке первого пустого столбца на листе Sheet2 (в нашем случае это столбец В) введите такую формулу:

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

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

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

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

Кликните правой клавишей мыши и в контекстном меню изберите 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. Вот и всё, сейчас столбец А содержит лишь неповторимые данные, которых нет в столбце В:

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

Как удалить дубликаты в Excel – все доступные способы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтоб отыскать дубликаты в Excel, нужно:

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

Внедрение формулы

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

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

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

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

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

Копирование неповторимых строк в новое размещение

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

Для выполнения, будет нужно:

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

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

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

Для работы этого метода будет нужно:

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

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

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

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

• используя сводные таблицы (подсчет дублированных значений);
• при помощи функцииunique (вывод результата из массива);
• используя посторонние расширения;

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

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

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

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

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

Выделяем цветом дубликаты в таблице

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

Открывает вкладку «Основная», в разделе «Стили» избираем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».

Раскрылось окно, в котором есть два пт: что выделить – неповторимые либо повторяющиеся значения, и, как их обозначить – в какую цветовую палитру выкрасить ячейки. И, естественно же, клавиша «ОК».

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

Поглядите на мой итог. Правда таковой метод имеет значимый недочет: нет подборки, выделяет все, что встречается наиболее 1-го раза.

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

Метод, быть может, очевидный, но действующий. Воспользуемся функцией «Поиск».

Открывает вкладку «Основная» — раздел «Редактирование» — «Отыскать и выделить» (CTRL+F). alt=»11″ width=»300″ height=»34″ />

В окне в поле «Отыскать» набираем, что мы отыскиваем. Потом нажимаем по кнопочке «Отыскать все», жмем сочетание кнопок CTRL+A, чтоб избрать все поисковые результаты, и выделяю их цветом. Так же их можно удалить, а не выделять.

Удаляем все схожие значения в Excel при помощи расширенного фильтра

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

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

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

Очередной метод стремительно удалить дубли в таблице

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

Сейчас откройте вкладку «Данные», раздел «Работа с данными», «Удалить дубликаты». alt=»7″ width=»300″ height=»84″ />

Расставим нужные галочки. Мне нужен поиск по двум столбцам, поэтому оставляю, как есть, и жму на клавишу «ОК».

На этом способ завершился. Вот мой итог его работы.

Эти способы были испытаны в различных версиях Эксель 2007, 2010, 2013, 2016, 2019.

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

Незначительно юмора:

Оскорбление 21 века — «По для тебя фотошоп рыдает».

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

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

Метод 1: обычное удаление дубликатов

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

Итак, для вас нужно:

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

Как лишь вы нажмете на клавишу, начнется поиск дубликатов выделенной области, а потом и их удаление. По итогу покажется окно, в котором будет продемонстрирован отчет. Сейчас вы понимаете, как в Excel удалить повторяющиеся строчки, но только 1-ый метод, на очереди 2-ой.

Метод 2: внедрение «умной таблицы»

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

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

1. Как и в прошлом методе, поначалу выделить спектр ячеек, где нужно удалить дубликаты.
2. Надавить клавишу «Форматировать как таблицу», что размещается на вкладке «Основная» в группе инструментов «Стили».
3. Избрать из выпадающего меню понравившейся стиль (хоть какой).
4. В показавшемся окне нужно подтвердить обозначенный ранее спектр ячеек, а если он не совпадает, то переназначить. Также установите галочку рядом с «Таблица с заголовками», если она такая, по итогу нажмите клавишу «ОК».
5. «Умная таблица» сотворена, но это еще не все. Сейчас для вас нужно выделить всякую ячейку таблицы, чтоб во вкладках возник пункт «Конструктор» и конкретно перейти в эту вкладку.
6. В ленте инструментов необходимо надавить клавишу «Удалить дубликаты».

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

Заключение

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