Excel найти дубли в столбце

Как исключить значения в одном списке из другого в Excel?

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

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

Для этого вы можете применить следующие формулы. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку, которая находится рядом с первой ячейкой списка, который вы хотите удалить, затем введите формулу. = СЧЁТЕСЛИ ($ D $ 2: $ D $ 6; A2) в панель формул, а затем нажмите Enter ключ. Смотрите скриншот:

Внимание: В формуле $ D $ 2: $ D $ 6 — это список, на основе которого вы удаляете значения, A2 — это первая ячейка списка, который вы собираетесь удалить. Пожалуйста, измените их по своему усмотрению

2. Продолжая выбирать ячейку результата, перетащите маркер заполнения вниз, пока он не достигнет последней ячейки списка. Смотрите скриншот:

3. Продолжайте выбирать список результатов, затем щелкните Данные > Сортировка от А до Я.

Затем вы можете увидеть, что список отсортирован, как показано на скриншоте ниже.

4. Теперь выберите все строки имен с результатом 1, щелкните правой кнопкой мыши выбранный диапазон и нажмите Удалить чтобы удалить его.

Теперь вы исключили значения из одного списка на основе другого.

Внимание: Если «список для удаления» находится в диапазоне A2: A6 другого листа, такого как Sheet2, примените эту формулу = IF (ISERROR (VLOOKUP (A2; Sheet2! $ A $ 2: $ A $ 6,1; FALSE)), «Сохранить», «Удалить») получить все Сохранить и Удалить результатов, отсортируйте список результатов от A до Z, а затем вручную удалите все строки имен, содержащие результат удаления на текущем листе. Этот раздел будет рекомендовать Выберите одинаковые и разные ячейки полезности Kutools for Excel чтобы решить эту проблему

Пожалуйста, сделайте следующее

Этот раздел будет рекомендовать Выберите одинаковые и разные ячейки полезности Kutools for Excel чтобы решить эту проблему. Пожалуйста, сделайте следующее.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Нажмите Kutools > Выберите > Выберите одинаковые и разные ячейки. Смотрите скриншот:

2. в Выберите одинаковые и разные ячейки диалоговое окно, вам необходимо:

• 2.1 Выберите список, из которого вы удалите значения в Найдите значения в коробка;
• 2.2 Выберите список, значения которого вы удалите, на основе Согласно информации коробка;
• 2.3 выберите Однокамерная вариант в на основании раздел;
• 2.4 Щелкните значок OK кнопка. Смотрите скриншот:

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

4. Теперь значения в столбце A выбираются, если они существуют в столбце D. Вы можете нажать кнопку Удалить клавишу, чтобы удалить их вручную.

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

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

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

В одном столбце

Условное форматирование – это наиболее простой способ определить, где находятся дубликаты в Excel и выделить их. Что нужно сделать для этого?

1. Найти ту область поиска дубликатов и выделить ее.

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

3. Далее появляется окно, в котором нужно выбрать пункт «Повторяющиеся» и нажать на клавишу ОК.

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

В нескольких столбцах

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

Последовательность действий, в целом, следующая:

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

3. Далее снова выбираем пункт «Повторяющиеся» в появившемся окошке, а в списке справа выбираем цвет заливки. После этого кликаем по «ОК» и радуемся жизни.

Дубликаты строк

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

Посмотрите на эти две таблицы.

1718

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

Итак, что нужно сделать для поиска повторяющихся значений в рядах?

1. Создаем еще одну колонку в правой части по отношению к таблице с исходной информацией. В нем записывается формула, которая выводит объединенную информацию со всех ячеек, входящих в состав строки. =A2&B2&C2&D2

2. После этого мы увидим информацию, которая была объединена.

3. После этого следует выбрать дополнительную колонку (а именно, те ячейки, которые содержат объединенные данные).
4. Далее переходим на «Главная», а затем снова выбираем пункт «Повторяющиеся значения» аналогично описанному выше. 
5. Далее появится диалоговое окно, где снова выбираем пункт «Повторяющиеся», а в правом перечне находим цвет, с использованием которого будет осуществляться выделение. 

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

Хорошо, предположим, перед нами стоит задача выбрать те строки, которые располагаются в исходном диапазоне, а не по вспомогательной колонке? Чтобы это сделать, нужно предпринять следующие действия:

1. Аналогично предыдущему примеру, делаем вспомогательную колонну, где записываем формулу объединения предыдущих столбцов. =A2&B2&C2&D2

2. Далее мы получаем все содержащиеся в строке значения, указанные в соответствующих ячейках каждой из строк.

3. После этого осуществляем выделение всей содержащиеся информации, не включая дополнительный столбец. В случае с нами это такой диапазон: A2:D15. После этого переходим на вкладку «Главная» и выбираем пункт «Условное форматирование» – создать правило (видим, что последовательность немного другая).

4. Далее нас интересует пункт «Использовать формулу для определения форматируемых ячеек», после чего вставляем в поле «Форматировать значения, для которых следующая формула является истинной», такую формулу. =СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

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

23

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

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

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

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

Формулы для поиска дубликатов в 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 – Формула для идентификации дубликатов строк за исключением первых случаев

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

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

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

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

1. И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete» на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

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

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

Метод 3: использование фильтра

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

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

1. Первым делом следует выделить полностью таблицу, в которой вы собираетесь провести манипуляции по удалению дубликатов.
2. Теперь перейдите в раздел «Данные» и сразу перейдите в подраздел «Фильтр».

Выделяем диапазон таблицы и используем фильтр

1. Явным признаком того, что фильтр был активирован, является наличие в шапке таблицы специальных стрелок, после этого вам будет достаточно воспользоваться ими и указать информацию касательно дубликатов (к примеру, слово или обозначение в поиске).

Таким образом можно сразу отфильтровать все дубликаты и произвести дополнительные манипуляции с ними.

Расширенный фильтр для поиска дубликатов в Excel

Имеется еще дополнительный способ использования фильтров в программе Excel, для этого вам понадобится:

1. Выполнить все действия прошлого метода.
2. В окне инструментария воспользоваться значком «Дополнительно», который находится около того самого фильтра.

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

После использования данного значка вам достаточно будет обратить внимание на окно дополнительных настроек. Этот расширенный инструментарий позволит ознакомиться с первоначальной информацией:
поначалу следует проверить указанный диапазон таблицы, чтобы он совпадал с тем, что вы отмечали;
обязательно отметьте пункт «Только уникальные записи»;
как только все будет готово, остается лишь нажать на кнопку «ОК».. Проверяем и подтверждаем установки фильтра

Проверяем и подтверждаем установки фильтра

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

Проверяем дополнительную информацию после фильтрации

Как найти одинаковые строки в Excel и выделить их цветом

​ с которым была​.​ и ликвидация дубликатов​Pelena​If s1 =​ что примерно то​ Workbook​ втором столбце найти​ новее — нажать​ текущей ячейки встречается​ например – зеленый.​ совпадают одинаковые значения​ выделения она оставалась​Перед попыткой удаления​ создание «умной таблицы».​ Общий вид данной​Найти повторяющиеся ячейки можно​ подробно расписана при​Дубликаты можно удалить из​ – это звенья​: Это я и​

Как объединить одинаковые строки одним цветом?

​ s Then​ же самое, только​Set WB =​ нужное значение. А​ на вкладке​

1. ​ в столбце А.​ И нажмите на​ (находятся две и​ активной как показано​ повторений удалите все​ При использовании данного​ формулы будет выглядеть​ также при помощи​ описании первого способа.​ диапазона ячеек, создав​
2. ​ одного процесса.​ мела в виду,​With r1: Range(.Cells(i1,​ добавить копирование, но​ ActiveWorkbook​
3. ​ дальше условное форматирование​Главная (Home)​
4. ​ Если это количество​ всех открытых окнах​ более одинаковых строк)​ ниже на рисунке.​ структуры и промежуточные​ способа можно максимально​ следующим образом:​

​ условного форматирования таблицы.​ Все дальнейшие действия​ умную таблицу.​Проще всего удалить дубликаты​

Как выбрать строки по условию?

​ 1), .Cells(i1, 7)).Copy:​ в VBA пока​n = Cells(Rows.Count,​ для подсветки.​кнопку​ повторений больше 1,​

​ кнопку ОК.​ это приводит к​ И выберите инструмент:​ итоги из своих​ точно и удобно​=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;;​ Правда, удалять их​ производятся в точно​

​Выделяем весь табличный диапазон.​ – это воспользоваться​ (как обозначено в​ End With​ не силен (только-​

​ 1).End(xlUp).Row: Set r​drugojandrew​Условное форматирование — Создать​ т.е. у элемента​

​Все транзакции, проводимые во​ суммированию с помощью​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​ данных.​ настроить поиск дубликатов.​ адрес_столбца;)>1;0;1);0));»»)​ придется другим инструментом.​ таком же порядке.​Находясь во вкладке​ специальной кнопкой на​ теме) и «скопировать​

​With r: Range(.Cells(i,​ только начинаю что-то​ = Range(«a1:e» &​: Здравствуйте.​ правило (Conditional Formatting​ есть дубликаты, то​ вторник или в​ функции =СУММ() числа​В появившемся окне «Создание​На вкладке​ К тому же,​Создаем отдельный столбец, куда​

​Выделяем область таблицы. Находясь​Этот способ является наиболее​«Главная»​ ленте, предназначенной для​ значения» — это​ 1), .Cells(i, 7)).PasteSpecial​ делать) и у​ n): m =​Нигде не могу​

​ — New Rule)​ срабатывает заливка ячейки.​ среду выделены цветом.​ 1 указанного во​ правила форматирования» выберите​Данные​ их удаление происходит​ будут выводиться дубликаты.​ во вкладке​ универсальным и функциональным​

Как найти и выделить дни недели в датах?

​жмем на кнопку​ этих целей.​ не одно и​ xlPasteFormats: End With​ самого реализовать не​ r.Value​ найти решения подобной​и выбрать тип​ Для выбора цвета​Допустим, что у нас​ втором аргументе функции​ опцию: «Использовать формулу​нажмите кнопку​ моментально.​Вводим формулу по указанному​

​ в данной статье.​, расположенную на ленте​ Переходим во вкладку​

exceltable.com>

Поиск дублей в Excel

Спросите у SEO-шника без чего он, как без рук! Он наверняка ответит: без Excel! Эксель – лучший друг и помощник и для специалиста в SEO, и для вебмастера.

Одна из задач, которую тебе точно придётся решать при работе с большими массивами данных – это поиск дублей в Excel. Не вариант проверять тысячи ячеек руками – угробишь на это часы и выйдешь с работы, пошатываясь, будто пьяный. Я предложу тебе 2 способа, как выполнить эту работу в десяток раз быстрее. Они дают немного разные результаты, но в равной степени просты.

Как в Эксель найти повторяющиеся значения?

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

• Наша цель – найти повторы в столбцах Excel и выделить их цветом.
• Действуем так:

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

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

Нажмите «ОК», и вы обнаружите: одинаковые ячейки в двух столбиках теперь выделены! Как видите, это вопрос 30 секунд.

Описанный вариант – самый удобный для пользователей Эксель версий 2013 и 2016.

Как вычислить повторы при помощи сводных таблиц

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

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

1. Должно получиться так:

А на листе сформируется сама сводка – уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

Заключение

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

Хотя на самом деле функционал программы Эксель настолько широк, что можно не только подсветить повторяющиеся значения в столбике, но и автоматически их все удалить. Я знаю, как это делается, но сейчас вам не скажу. Теперь на сайте есть отдельная статья об удалении повторяющихся строк в Excel – там и смотрите ????.

Помогли ли тебе мои методы работы с данными? Или ты знаешь лучше? Поделись своим мнением в х!

Кликни на звездочку, чтобы оценить его!

Средний рейтинг 3.6 / 5. Итог: 5

Использование формул массива вместе с ВПР.

Здесь все гораздо сложнее. Вновь вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Прайс1» и «Прайс2».

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

Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.

Результаты сравнения таблиц вынесем также на отдельный лист «Сравнение».

В ячейке A5 запишем формулу

Поскольку это формула массива, то не забудьте завершить ее ввод комбинацией клавиш Ctrl+Shift+Enter.

В  результате получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований товаров.

Рассмотрим процесс пошагово. Формула последовательно берет значения из списка наименований. Затем при помощи функции СЧЕТЕСЛИ определяется количество совпадений с каждым из значений в ячейках, находящихся выше этого значения. Если результат СЧЕТЕСЛИ равен нулю, значит это наименование ранее не встречалось и можно его занести в список.

Функция ПОИСКПОЗ вычисляет номер позиции этого  уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, по номеру позиции извлекает значение из массива и записывает его в ячейку.

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

Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается пустая строка “”.

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

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

Запишем в В5:

Не забудьте, что это тоже формула массива (Ctrl+Shift+Enter).

Можно для наглядности выделить несовпадения цветом, используя условное форматирование.

Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст содержит…

Ну и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.

Заменим в нашей формуле значение «Есть» на функцию ВПР:

В итоге наше формула преобразуется к виду:

Аналогично в С5 :

Напомним, что на листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.

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

Примеры использования функции ВПР:

голоса

Рейтинг статьи

Как сравнить две таблицы при помощи формулы ВПР.

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

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

Для наглядности расположим обе таблицы на одном листе.

Формула

берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «». 

Таким образом, в ячейке J3 будет указана старая цена, если ее удастся найти, а также если она не равна новой.

Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —  

а в K3 – его новая цена:  

Ну а далее в L3 просто найдем разность K3-J3.

Таким образом, в отчёте сравнения мы видим только несовпадения значений второй таблицы по сравнению с первой.

И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.

В ячейке B2 запишем формулу

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

Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными  —

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

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

​ }.​​ по количеству проданных​ решить, используя одну​ использовать при поиске​Чтобы избежать дублированных заказов,​СОВЕТ:​​ENTER​​ строки по полному​​ окне выбираем, что​​ =ПРОСМОТР(2;1/(A1&B1=Лист2!A$1:A$99&Лист2!B$1:B$99);Лист2!A$1:A$99) =ПРОСМОТР(2;1/(A1&B1=Лист2!A$1:A$99&Лист2!B$1:B$99);Лист2!B$1:B$99) Вставить​​Продлеваете ее до​​Выделяем (для примера​​ найти аналогичные совпадения​​ ввести число 5000​​ следующего завершающего примера.​ полюбуйтесь на результат.​​ ячейку D2 введите​В ячейку C2 формула​​ товаров за три​ стандартную функцию. Да,​​ одинаковых значений в​ можно использовать условное​

​Созданный список повторяющихся значений​​нужно нажать​ совпадению, удалить ячейки​ нам нужно выделить:​​ и протянуть вниз​ конца первой таблицы.​ — диапазон А2:A10),​

CyberForum.ru>

​ в огромном списке​

• Excel найти минимальное значение в ряду чисел
• Как в excel найти значения в таблице
• Найти среднее значение в excel формула
• Как в excel сравнить значения в столбцах excel
• В excel найти значение в массиве
• Эксель найти повторяющиеся значения в столбце
• Как найти дубли в excel в столбце
• Excel как найти значение в таблице
• В excel найти одинаковые ячейки
• Как в эксель найти одинаковые значения в одном столбце
• Excel найти все значения удовлетворяющие условию
• Excel последнее значение в столбце

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

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

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

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

Кликните правой кнопкой мыши и в контекстном меню выберите 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 при помощи формул – это не так уж сложно.

Метод 5: формула для удаления повторяющихся строк

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

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

Давайте посмотрим, как с ней работать на примере нашей таблицы:

1. Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
2. В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»») .
3. Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
   • ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
   • жмем служебную клавишу F2 на клавиатуре;
   • затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
4. Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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