Поиск и удаление дубликатов (повторяющихся значений) в excel

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.

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

Процесс использования надстройки включает такие действия:

Перейти к параметрам электронной таблицы.
Выбрать сначала надстройки, а затем управление надстройками COM.
Отметить пункт Inquire и нажать «ОК».
Перейти к вкладке Inquire.
Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.

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

5 программ для совместной работы с документами

Как сравнить два столбца в Excel на совпадения и выделить цветом

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

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

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

Выделить столбцы с данными, в которых нужно вычислить совпадения;
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

Обратите внимание на две таблицы ниже:

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

Рассмотрим как найти совпадающие строки в таблице:

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

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

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

Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены дублирующиеся строки:

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

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

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

Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

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

Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

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

Duplicate Remover — быстрый и эффективный способ найти дубликаты в Excel

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

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

Как найти повторяющиеся строки в Excel за 2 быстрых шага

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

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

Выберите ячейку в таблице и нажмите кнопку «Быстрое исключение дубликатов» на ленте Excel. После установки Ultimate Suite for Excel вы найдете его на вкладке «Данные» программы Ablebits в группе «Дедупликация». Это простейший инструмент поиска дубликатов.

Умное дополнение возьмет всю таблицу и попросит вас указать следующие две вещи:
- Выберите столбцы для проверки дубликатов (в этом примере это все 3 столбца: категория, продукт и клиент).
- Выберите действие, которое вы хотите выполнить с дубликатами. Поскольку наша цель — выявить повторяющиеся строки, я выбрал «Выделить».

Помимо выделения цветом у вас есть ряд других возможностей:

Удалить дубликаты
Выбрать дубликаты
Укажите их в столбце статуса
Копировать дубликаты на новый лист
Перейти на новый лист

Нажмите ОК и подождите несколько секунд. Готовый! И никаких формул .

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

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

Мастер удаления дубликатов — больше возможностей для поиска дубликатов в Excel.

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

Выберите ячейку в таблице и нажмите кнопку «Удалить дубликаты» на вкладке «Данные» в Ablebits.

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

В этом примере мы выберем второй вариант, т.е. Дубликаты + 1-е вхождение:

Теперь выберите столбцы, которые вы хотите проверить на наличие дубликатов. Как и в предыдущем примере, мы возьмем первые 3 столбца:

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

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

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

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

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

Подсчет повторяющихся значений (Дубликатов) в MS EXCEL

Workbook в массив: мб. а тутEnd Ifot kaz

: спасибо, покурил немного товаром Y000001. Нона выходе мневыполняет поиск указанного6Для выполнения этой задачи значения — 5, 6 Внизу столбца В – изменим цифру дубли в Excel».Application.DisplayStatusBar = True книги » +

Dim Name_Wb AsFor i = ограничениеEnd Function: The_Prist, браво!!! разных тем и чтобы его количество

в идеале, чтобы элемента в диапазоне7 используйте комбинацию функций и 7; установили автосумму.

Итак, нам нужноEnd Sub Name_Wb_I)) String Nomer_Str_I To Col_I

Максимальный размер файлаSub Base()Только коректировка нужна, действительно через сводную было 6 шт. было ячеек и возвращает8ЕСЛИстроки «Руслан», «Сергей», «Сергей»,

excel2.ru⁪>

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

 неповторяющиеся значения и C стоят фамилии, B установили формулу.В столбце А пишете функцию типо второй цифры второй? повторений числа, но условии если Ивановы в обоих по в столбце А.Теперь можно вводить любое с первого дубликатаПосле ввода формулы для Для чего этоДопустим таблица содержит транзакции правила форматирования» выберите уникальные значения. имена и отчества.=ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»+»;»-«) Если в устанавливаем фильтр. Как =если (а1=а2;1;0) и5ну вроде индекс только того которое встречаются в разных 2 столбца с Если это количество исходное значение, а по горизонтали (с подтверждения снова нажимаем нужно? Достаточно часто с датами их опцию: «Использовать формулуС помощью Условного форматирования Чтобы сравнить сразу

 столбце В стоит установить и работать

 растягиваете до конца., как его программно 1-ой 5 - стоит раньше, чем

 городах или в Артикулом и ID. повторений больше 1, программа сама подберет лева на право). комбинацию клавиш CTRL+SHIFT+Enter нам нужно получить проведения. Необходимо найти для определения форматированных в исходном списке по трем столбцам, «+», значит такую с фильтром, смотрите Количество едииниц и запомнить, он мне 2 другие числа имеющие одном городе живётВ первом листе т.е. у элемента ближайшее число, которое А формула для и получаем результат: координаты таблицы по одну из них, ячеек».

 можно выделить повторяющиеся нужно соединить данные фамилию уже написали. в статье «Фильтр будет количетсво одинаковых нужен будет дляиндекс 2-ой 5 повторения… много Ивановых :-) столбец с Артикулом есть дубликаты, то содержит таблица. После получения названия (номера)Формула вернула номер 9 значению. Немного напоминает но неизвестны всеВ поле ввода введите

 значения. трех столбцов вТретий способ. в Excel». В в обоих столбцах. расчетов…. — 8Например:Ксения заполнен, столбец с срабатывает заливка ячейки. чего выводит заголовок

 строки берет номер – нашла заголовок обратный анализ матрицы. детали. Известно только, формулу: 1′ class=’formula’>1. Добавьте в исходный одной ячейке. ВПосчитать количество одинаковых значений ячейке B2 пишемУПД. А, пардон.19vitek19vitek7: Спасибо большое за ID пустой. Для выбора цвета столбца и название с первого дубликата строки листа по Конкретный пример в что транзакция проведенаНажмите на кнопку формат, список название новой ячейке D15 пишемExcel. слово «Нет». Неправильно понял задачу.: ИМЕННО ВТОРОЙ ИЛИ: Sub n() For5 отклик. Совершенно проВо втором листе

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

 во вторник или чтобы задать цвет компании (в ячейку формулу, используя функциюНам нужно неНажимаем два раза левойТогда рядом с ПОСЛЕДНЕЙ? i = 13 функции забыла.. оба столбца заполнены.Условное форматирование значения. Например, если вниз). Для исправления В результате мы примерно так. Поставленная в среду. Чтобы заливки для ячеек,А20 «СЦЕПИТЬ» в Excel. только выделить повторы, мышкой на зеленый первым стобцом используетеSub n() For To 10 Cells(i,1Велик и могучЗадача: Найти одинаковыенажмите кнопку ввести число 5000 данного решения есть имеем полный адрес цель в цифрах облегчить себе поиск, например – зеленый.

введите ООО Кристалл) =СЦЕПИТЬ(A15;» «;B15;» «;C15) но и вести квадратик в правом функцию ВПР, к i = 1 2) = Application.WorksheetFunction.CountIf(,2 русский язык.. найди артикулы и присвоить

Формат… (Format) получаем новый результат: 2 пути: значения D9. является исходным значением, выделим цветом все И нажмите на2. Список неповторяющихся значенийПро функцию «СЦЕПИТЬ» их подсчет, написать нижнем углу ячейки примеру, чтобы во To 10 Cells(i, Cells(i, 1)) Next3 правильные слова называется) ID из второгои перейдите наСкачать пример поиска значенияПолучить координаты первого дубликата нужно определить кто даты этих дней всех открытых окнах (столбец С) автоматически читайте в статье в ячейке их (на картинке обведен втором столбце найти 2) = Application.WorksheetFunction.CountIf(, i For Each4Нужно было выяснить листа в первый. вкладку в диапазоне Excel по горизонтали (сТеперь научимся получать по и когда наиболее недели (вторник, среда). кнопку ОК. будет обновлен, чтобы «Функция «СЦЕПИТЬ» в количество. красным цветом). Слово нужное значение. А

 Cells(i, 1)) Next cel In 5 есть ли ИвановПример файла прикрепил.

CyberForum.ru⁪>

Работа с формулами Excel. Использование встроенных функций Excel

Фильтр – идеально подходит, когда в данных присутствуют несколько категорий, которые, возможно, Вам понадобится разделить, просуммировать или удалить. Создание подразделов – самое лучшее применение для расширенного фильтра.
Удаление дубликатов уменьшит объём данных до минимума. Я пользуюсь этим способом, когда мне нужно сделать список всех уникальных значений одного из столбцов, которые в дальнейшем использую для вертикального поиска с помощью функции ВПР.
Я пользуюсь командой Find (Найти) только если нужно найти небольшое количество значений, а инструмент Find and Replace (Найти и заменить), когда нахожу ошибки и хочу разом исправить их.

Пример 2. На основе таблицы из Примера 1 создать список табельных номеров и фамилий сотрудников одного из отделов, указанного в отдельной ячейке. Формула, решающая эту задачу, выглядит так: =ИНДЕКС($E500:$E$25;НАИМЕНЬШИЙ(ЕСЛИ($A500:$A$25=$H$7;СТРОКА($A200:$A$24));СТРОКА(A1))), причём это — формула массива, которую следует вводить сочетанием клавиш ++

Как найти повторения в excel

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

Как работает функция?

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

С одним условием

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

Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.

Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.

Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

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

Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

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

Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».

Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

В таблице с зарплатой за март добавить еще один столбец.

Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

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

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

Нажать «Ок». Отобразится список.

В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

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

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

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

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

NickoAbram pupkin столбцу ни одна столбцов с помощью – «Дополнительно». операций «Работа с которых нас интересует. данных содержится несколько кнопку поле условное форматирование, применяемое пункт другой лист или что активная ячейка значений они удаляютсяОК: AleksSid, еще более: У вас будет строка не будет формул, но этотВ открывшемся окне вводим

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

Поиск уникальных значений

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

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

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

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

сохранять исходную таблицу, перед вызовом команды «Повторяющиеся значения». В или таблица. таблице или отчете том, сколько повторяющихсяПримечание: ячеек в диапазоне,. если выделенные фрагментв группе в одной строкеповторяющиеся данные удаляютсяNickoДанные — удалить «Цена» считаются разными. в современных версиях

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

содержит структурированные данныеСортировка и фильтр полностью совпадают со безвозвратно. Чтобы случайно, не нравятся дубликаты? дубликаты. программы. список на месте».

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

Отбор уникальных записей

— только уникальные?: выделяем столбец другие средства дляВозможности функций авто-таблицы позволяют с ним не предлагает выделить столбцы, диапазона. После закрашивания.в разделе о том, что значения из выбранного

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

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

Дополнительные рекомендации

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

Стили. итоги. ниже действий. не тем, которое на другой лист.Pelena

«правила выделения ячеек»«Данные»-«Удалить дубликаты» — Инструмент Сразу стоит отметить,

другое место. повторения. только их, отсортироватьОК с кнопкой удалено. изменятся.щелкнитеВы можете создать правило

Выделите диапазон ячеек или

fb.ru⁪>

Как вывести результат в Эксель

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

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

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

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

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

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

Если необходимо выделить цветом сами строки в таблице Эксель, также создайте дополнительный столбик, а в каждой строчке используйте формулу =A2&B2&C2&D2. После этого выделите все данные таблицы, войдите в «Главная» и пункт «Условное форматирование». Здесь жмите на «Создать правило» и «Создание правила форматирования». Теперь жмите на «Использовать формулу для определения…» и в категории «Форматировать значения, для которых …» введите =СЧЁТЕСЛИ($E$2:$E$15;$E2)>1. На завершающе этапе задайте формат найденных дублей.

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

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

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

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

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

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

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

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

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

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

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

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

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

17 18

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

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

Создаем еще одну колонку в правой части по отношению к таблице с исходной информацией. В нем записывается формула, которая выводит объединенную информацию со всех ячеек, входящих в состав строки. =A2&B2&C2&D2
После этого мы увидим информацию, которая была объединена.
19
После этого следует выбрать дополнительную колонку (а именно, те ячейки, которые содержат объединенные данные).
Далее переходим на «Главная», а затем снова выбираем пункт «Повторяющиеся значения» аналогично описанному выше.
Далее появится диалоговое окно, где снова выбираем пункт «Повторяющиеся», а в правом перечне находим цвет, с использованием которого будет осуществляться выделение.

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

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

Аналогично предыдущему примеру, делаем вспомогательную колонну, где записываем формулу объединения предыдущих столбцов. =A2&B2&C2&D2
Далее мы получаем все содержащиеся в строке значения, указанные в соответствующих ячейках каждой из строк.
20
После этого осуществляем выделение всей содержащиеся информации, не включая дополнительный столбец. В случае с нами это такой диапазон: A2:D15. После этого переходим на вкладку «Главная» и выбираем пункт «Условное форматирование» – создать правило (видим, что последовательность немного другая).
21
Далее нас интересует пункт «Использовать формулу для определения форматируемых ячеек», после чего вставляем в поле «Форматировать значения, для которых следующая формула является истинной», такую формулу. =СЧЁТЕСЛИ($E$2:$E$15;$E2)>1
22

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

Поиск дубликатов при помощи встроенных фильтров Excel

Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, то мне достаточно выделить верхнюю строку, в которой находятся заголовки, затем перейти на вкладку Data (Данные) и нажать команду Filter (Фильтр). Возле каждого из заголовков появятся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

Если нажать одну из этих стрелок, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с Вашим выбором. Это быстрый способ подвести итог или увидеть объём выбранных данных. Вы можете убрать галочку с пункта Select All (Выделить все), а затем выбрать один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные Вами пункты. Так гораздо проще найти дубликаты, если они есть.

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

В результате работы фильтра, Excel отображает только те строки, в которых содержатся выбранные мной элементы (т.е. людей на чьём компьютере установлены XP и XP Pro). Можно выбрать любую другую комбинацию данных, а если нужно, то даже настроить фильтры сразу в нескольких столбцах.

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

На вкладке Data (Данные) справа от команды Filter (Фильтр) есть кнопка для настроек фильтра – Advanced (Дополнительно). Этим инструментом пользоваться чуть сложнее, и его нужно немного настроить, прежде чем использовать. Ваши данные должны быть организованы так, как было описано ранее, т.е. как база данных.

Перед тем как использовать расширенный фильтр, Вы должны настроить для него критерий. Посмотрите на рисунок ниже, на нем виден список с данными, а справа в столбце L указан критерий. Я записал заголовок столбца и критерий под одним заголовком. На рисунке представлена таблица футбольных матчей. Требуется, чтобы она показывала только домашние встречи. Именно поэтому я скопировал заголовок столбца, в котором хочу выполнить фильтрацию, а ниже поместил критерий (H), который необходимо использовать.

Теперь, когда критерий настроен, выделяем любую ячейку наших данных и нажимаем команду Advanced (Дополнительно). Excel выберет весь список с данными и откроет вот такое диалоговое окно:

Как видите, Excel выделил всю таблицу и ждёт, когда мы укажем диапазон с критерием. Выберите в диалоговом окне поле Criteria Range (Диапазон условий), затем выделите мышью ячейки L1 и L2 (либо те, в которых находится Ваш критерий) и нажмите ОК. Таблица отобразит только те строки, где в столбце Home / Visitor стоит значение H, а остальные скроет. Таким образом, мы нашли дубликаты данных (по одному столбцу), показав только домашние встречи:

Это достаточно простой путь для нахождения дубликатов, который может помочь сохранить время и получить необходимую информацию достаточно быстро. Нужно помнить, что критерий должен быть размещён в ячейке отдельно от списка данных, чтобы Вы могли найти его и использовать. Вы можете изменить фильтр, изменив критерий (у меня он находится в ячейке L2). Кроме этого, Вы можете отключить фильтр, нажав кнопку Clear (Очистить) на вкладке Data (Данные) в группе Sort & Filter (Сортировка и фильтр).

Как сложить и удалить дубли в Excel.

Поиск дубликатов при помощиВ строке «Критерий» – это название и нажмите кнопку.Снять выделениеВ поле всплывающего окна нажмите кнопку>Нужно чтобы по у меня больше: Да самым обыкновенным, With With CreateObject(“Scripting.Dictionary”) когда нахожу ошибки Вы выбрали. Кстати,, а остальные скроет. а если нужно, команды Найти указываем ячейку столбца столбцов;ОКВведите значения, которые вы.Расширенный фильтр

 программе: получается?

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

excel-office.ru⁪>