Перейти к содержимому

Как найти в двух таблицах одинаковые значения

  • автор:

Как в эксель найти повторяющиеся строки

Дубли в Microsoft Excel

​Смотрите также​Условное форматирование​Нажмите на кнопку формат,​ очереди сложить значение​ всегда повторение свидетельствует​);»»)​ непосредственно перейти в​ в которых будет​.​Скопировав слово, которое необходимо​ фильтр, необходимо выделить​ качестве примера возьмем​Условное форматирование​ кнопку​«Условное форматирование»​ удаления дубликатов, работа​

​Excel выполняет процедуру поиска​При работе с таблицей​

Поиск и удаление

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

Способ 1: простое удаление повторяющихся строк

​ найти, воспользуйтесь сочетанием​ всю таблицу, как​ следующую таблицу с​>​F2​

    ​, расположенную в блоке​ с которым была​​ и удаления дубликатов.​​ или базой данных​​Формат. (Format)​​ заливки для ячеек,​ выделенных строк.​ данных. Иногда несколько​​ENTER​​В ленте инструментов нужно​

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

Окно удаления дубликатов в Microsoft Excel

Информационное окно в Microsoft Excel

Способ 2: удаление дубликатов в «умной таблице»

​ Все дальнейшие действия​ в котором сообщается,​ когда некоторые строки​

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

Создание умной таблицы в Microsoft Excel

Подтверждение диапазона для создание умной таблицы в Microsoft Excel

Переход к удалению дубликатов в Microsoft Excel

​ строк. Для этого​значения с​После этих действий в​и​

​ универсальным и функциональным​​ записей. Чтобы закрыть​ же, при наличии​

Способ 3: применение сортировки

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

    ​ представлено в первом​ вы хотите удалить​​(Удалить). Не стоит​​Найти​​ команд​​ выберите любую ячейку​выберите форматирование для​​ столбце​​«Повторяющиеся значения…»​

Включение фильтра в Microsoft Excel

Переход в раздел Дополнительно в Microsoft Excel

Окно расширенного фильтра в Microsoft Excel

​ себе работу с​).​ последующие действия проводите​ столбца, то необходимо​Delete​Ctrl+V​​(Сортировка и фильтр)​​ затем выделите всю​

Включение показа дублей в Microsoft Excel

​ значениям и нажмите​​отобразятся повторяющиеся значения.​

Способ 4: условное форматирование

​Открывается окно настройки форматирования.​Урок:​«OK»​ формулах. Давайте разберемся,​ Например, имеется вот​

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

Переход к условному форматипррованию в Microsoft Excel

Настройка форматирования в Microsoft Excel

​ Excel​Дубликаты можно удалить из​ Microsoft Excel отыскать​ ФИО в трех​ что некоторые элементы​Как только при сравнении​

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

​ большинства пользователей. К​​ изменения –​

Способ 5: применение формулы

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

​ ячеек, а не​(Параметры), чтобы открыть​

    ​ ниже. Если Вы​Перейдите на вкладку​

Столбец для дубликатов в Microsoft Excel

Формула в Microsoft Excel

Выделение сторлбца в Microsoft Excel

​Выделяем весь табличный диапазон.​Скачать последнюю версию​​ — подсветить совпадающие​​ Хотелось бы видеть​

Отображение дубликатов в Microsoft Excel

​ более одинаковых строк)​Чтобы найти объединить и​ исходного списка).​ строки с повторяющимися​ на кнопку, начнется​Сделав это, Вы можете​ Установите флажок у​ вызовите раскрывающееся меню​(Данные), а затем​повторяющиеся данные удаляются​

​ дублей, но не​ параметре выделения можно,​ только скрывает повторные​Находясь во вкладке​ Excel​ ФИО, имея ввиду​ эти повторы явно,​ это приводит к​ выделить одинаковые строки​В файле примера также​ значениями. Как можно​ поиск дубликатов выделенной​ заметить, что все​ пункта​Data​ нажмите команду​ безвозвратно. Чтобы случайно​ их удаление. Поэтому​ как оставить настройки​ записи в таблице.​«Главная»​Найти и удалить значения​ совпадение сразу по​ т.е. подсветить дублирующие​ суммированию с помощью​

​ в Excel следует​

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

​ приведены перечни, содержащие​ заметить, в этом​ области, а затем​ оставшиеся строки имеют​Match entire cell contents​(Данные), выберите​Remove Duplicates​ не потерять необходимые​ рекомендуется применять более​ по умолчанию, так​Выделяем таблицу. Переходим во​

​жмем на кнопку​ таблицы, которые дублируются,​ всем трем столбцам​

​ ячейки цветом, например​​ функции =СУММ() числа​ выполнить несколько шагов​ неповторяющиеся значения и​ нет ничего сложного,​

​ и их удаление.​​ уникальные значения.​​(Ячейка целиком). Это​​Filters​​(Удалить дубликаты), как​​ сведения, перед удалением​​ простые и функциональные​​ и выбрать любой​​ вкладку​

Правила выделения ячеек

​«Форматировать как таблицу»​ возможно разными способами.​​ — имени, фамилии​​ так: ​ 1 указанного во​ простых действий:​ уникальные значения.​​ а пользуясь инструкцией,​​ По итогу появится​

Диалоговое окно

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

​Урок подготовлен для Вас​​ необходимо сделать, поскольку​​(Фильтры), а затем​ показано ниже.​ повторяющихся данных рекомендуется​ решения, описанные ранее.​ подходящий для вас​«Данные»​, расположенную на ленте​

​ В каждом из​ и отчества одновременно.​В последних версиях Excel​

​ втором аргументе функции​​Выделите весь диапазон данных​С помощью Условного форматирования​ так и вовсе​ окно, в котором​ командой сайта office-guru.ru​

​ в некоторых ячейках​​Advanced Filters​​Появится небольшое диалоговое окно​​ скопировать исходные данные​​Как видим, в Экселе​​ цвет, после этого​​. Жмем на кнопку​ в блоке инструментов​ этих вариантов поиск​Самым простым решением будет​

Удалить повторения

​ начиная с 2007​ =ЕСЛИ(). Функция СУММ​ табличной части A2:F18.​ в исходном списке​ провернуть данную операцию​

Выделенные повторяющиеся значения

​ будет продемонстрирован отчет.​​Источник: http://www.howtogeek.com/198052/how-to-remove-duplicate-rows-in-excel/​​ искомые слова находятся​​(Расширенные фильтры).​​Remove Duplicates​

Диалоговое окно

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

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

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

​«Фильтр»​«Стили»​ и ликвидация дубликатов​ добавить дополнительный служебный​ года функция подсветки​ позволяет сложить одинаковые​ Начинайте выделять значения​ можно выделить повторяющиеся​ можно за несколько​ Теперь вы знаете,​Перевел: Антон Андронов​ вместе с другими​Теперь необходимо установить флажок​(Удалить дубликаты). Можно​Выделите диапазон ячеек с​ предназначенных для поиска​«OK»​

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

Вариант 1: Команда Удалить дубликаты в Excel

​, расположенную в блоке​. В появившемся списке​ – это звенья​ столбец (его потом​ дубликатов является стандартной.​ строки в Excel.​ из ячейки A2,​ значения.​ секунд. Пример был​ как в Excel​​Автор: Антон Андронов​​ словами. Если не​

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

​Unique records only​​ заметить, что выделение​​ повторяющимися значениями, который​ и удаления дублей.​​.​​ настроек​ выбираем любой понравившийся​

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

​ одного процесса.​​ можно скрыть) с​​Выделяем все ячейки с​Если строка встречается в​ так чтобы после​1. Добавьте в исходный​ приведен в 2016-й​ удалить повторяющиеся строки,​​Когда работаешь с огромным​​ выбрать эту опцию,​(Только уникальные записи).​

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

​ первой строки снимается​ нужно удалить.​ У каждого из​После этого произойдет выделение​«Сортировка и фильтр»​ стиль.​Проще всего удалить дубликаты​ текстовой функцией СЦЕПИТЬ​​ данными и на​​ таблице только один​​ выделения она оставалась​​ список название новой​​ версии программы, однако​​ но лишь первый​​ объемом данных в​​ можно непреднамеренно удалить​​После нажатия​​ автоматически. Причиной тому​

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

​Совет:​ них есть свои​​ ячеек с повторяющимися​​.​Затем открывается небольшое окошко,​ – это воспользоваться​ (CONCATENATE), чтобы собрать​ вкладке​ раз, то функция​ активной как показано​ компании (в ячейку​

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

Вариант 2: Расширенный фильтр

​ удалить повторяющиеся строки​ способ, на очереди​ программе «Эксель», то​ ячейки, которые требуется​​OK​​ является флажок, установленный​Перед попыткой удаления​ особенности. Например, условное​ значениями. Эти ячейки​Фильтр включен, о чем​ в котором нужно​ специальной кнопкой на​​ ФИО в одну​​Главная (Home)​

​ =СУММ() вернет значение​​ ниже на рисунке.​​А20​ в Excel 2010​​ второй.​​ ненароком очень просто​ оставить. Убедитесь, что​​в документе будут​​ в пункте​ повторений удалите все​ форматирование предполагает поиск​ вы потом при​​ говорят появившиеся пиктограммы​​ подтвердить выбранный диапазон​​ ленте, предназначенной для​​ ячейку:​​жмем кнопку​​ 1, а целая​

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

​ И выберите инструмент:​​введите ООО Кристалл)​​ и других версиях​

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

​Дубликаты можно удалить подобным​​ можно совершить ошибку​​ все остальные настройки​ удалены все дубликаты,​My data has headers​ структуры и промежуточные​ дублей только по​ желании сможете удалить​ в виде перевернутых​ для формирования «умной​ этих целей.​Имея такой столбец мы,​Условное форматирование (Conditional Formatting)​ формула возвращает –​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​2. Список неповторяющихся значений​ можно точно также.​ способом, который имеет​ и вписать одни​ соответствуют указанным на​ за исключением одной​

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

Вариант 3: Замена

​(Мои данные содержат​ итоги из своих​ каждой ячейке в​ вручную стандартным способом.​ треугольников в названиях​​ таблицы». Если вы​​Выделяем весь табличный диапазон.​ фактически, сводим задачу​, затем выбираем​ ЛОЖЬ (ведь 1​В появившемся окне «Создание​ (столбец С) автоматически​Автор: Вадим Матузок​

​ место быть в​ и те же​ рисунке ниже.​ записи. В нашем​ заголовки).​ данных.​ отдельности. К тому​​Внимание! Поиск дублей с​​ столбцов. Теперь нам​

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

​ выделили все правильно,​ Переходим во вкладку​​ к предыдущему способу.​​Правила выделения ячеек -​ не является больше​​ правила форматирования» выберите​​ будет обновлен, чтобы​Из исходной таблицы с​ этой статье. На​​ данные повторно. Так​​Теперь необходимо ввести значение​​ примере осталось две​​В нашем примере нет​

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

​На вкладке​​ же, не все​​ применением условного форматирования​ нужно его настроить.​ то можно подтверждать,​«Данные»​​ Для выделения совпадающих​​ Повторяющиеся значения (Highlight​ чем 1).​ опцию: «Использовать формулу​ включить новое название​ повторяющимися значениями отберем​ сей раз, в​ возникают дубликаты. Это,​ в поле​ записи, поскольку первый​ заголовков, поскольку таблица​Данные​ инструменты могут не​ производится не по​

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

​ Кликаем по кнопке​ если допустили ошибку,​​. Жмем на кнопку​​ ФИО теперь можно​ Cell Rules -​Если строка встречается в​​ для определения форматированных​​3. Добавьте в исходный​ только те значения,​​ рассказе, как в​​ конечно же, повышает​

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

​Replace with​ дубликат был обнаружен​​ начинается с 1-й​​нажмите кнопку​ только искать, но​​ строке в целом,​​«Дополнительно»​​ то в этом​​«Удалить дубликаты»​ выделить все три​ Duplicate Values)​ таблице 2 и​ ячеек».​ список название новой​

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

​ которые имеют повторы.​ Excel удалить повторяющиеся​ объем всей суммарной​(Заменить на). В​ в строке 1.​ строки. Поэтому снимем​Удалить дубликаты​ и удалять повторяющиеся​

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

​ а по каждой​, расположенной рядом все​ окне следует исправить.​​. Она располагается на​​ столбца с данными​

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

​:​ более раза формула​В поле ввода введите​ компании еще раз​ Теперь при добавлении​ строки, будет использоваться​ информации, а в​​ данном примере мы​​ Этот метод автоматически​ флажок. Сделав это,​​и в разделе​​ значения. Наиболее универсальный​ ячейке в частности,​ в той же​ Важно также обратить​ ленте в блоке​

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

​ и создать новое​В появившемся затем окне​ будет возвращать значение​ формулу: 1′ >​

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

​ (в ячейку​ новых значений в​
​ «умная таблица».​
​ некоторых случаях сбивает​

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

​ определяет заголовки в​ Вы заметите, что​Столбцы​ вариант – это​ поэтому не для​ группе инструментов​ внимание на то,​ инструментов​ правило форматирования, аналогичное​ можно задать желаемое​ ИСТИНА и для​Нажмите на кнопку формат,​А21​ исходный список, новый​Для удаления повторов в​ формулы, которые должны​1​ таблице. Если Вы​ вся таблица снова​установите или снимите​ создание «умной таблицы».​ всех случаев он​

 как в excel удалить повторяющиеся строки

Способ 1: стандартное удаление дубликатов

​«Сортировка и фильтр»​ чтобы около параметра​«Работа с данными»​ Способу 2. А​ форматирование (заливку, цвет​

​ проверяемой строки присвоится​

  1. ​ чтобы задать цвет​снова введите ООО​ список будет автоматически​ Excel, вам нужно​ подводить итоги и​. Введя нужное значение,​
  2. ​ хотите удалить первую​
  3. ​ выделена, а раздел​ флажки, соответствующие столбцам,​ При использовании данного​ является подходящим.​
  4. ​.​«Таблица с заголовками»​.​ именно:​
  5. ​ шрифта и т.д.)​ новый формат, указанный​ заливки для ячеек,​ Кристалл)​ содержать только те​ сделать следующее:​ рассчитывать различные переменные.​ нажмите​ строку, Вам придется​Columns​ в которых нужно​ способа можно максимально​Урок:​Открывается окно расширенного фильтра.​стояла галочка. Если​Открывается окно удаление дубликатов.​в Excel 2003 и​
  6. ​В более древних версиях​

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

​ пользователем в параметрах​ например – зеленый.​4. Список неповторяющихся значений​ значения, которые повторяются.​Как и в предыдущем​ Вручную найти дубликаты​Replace All​ удалить ее вручную.​(Колонны) изменится с​ удалить повторения.​ точно и удобно​Условное форматирование в Excel​ Устанавливаем в нем​ её нет, то​

Способ 2: использование «умной таблицы»

​ Если у вас​ старше — выбрать​ Excel придется чуточку​ правила (заливка ячеек​ И нажмите на​ автоматически будет обновлен,​Пусть в столбце​ способе, сначала выделить​ можно, но зачем,​

​(Заменить все).​ Когда в 1-й​dulpicate​

  1. ​Например, на данном листе​ настроить поиск дубликатов.​Кроме того, найти дубликаты​ галочку напротив параметра​
  2. ​ следует поставить. После​ таблица с шапкой​ в меню​ сложнее. Выделяем весь​ зеленым цветом).​
  3. ​ всех открытых окнах​ новое название будет​
  4. ​А​ диапазон ячеек, где​ если есть несколько​Можно заметить, что все​ строке будут заголовки,​на​ в столбце «Январь»​ К тому же,​ можно применив формулу​«Только уникальные записи»​ того, как все​
  5. ​ (а в подавляющем​Формат — Условное форматирование​ список (в нашем​Допустим таблица содержит транзакции​ кнопку ОК.​ исключено​имеется список с​ необходимо удалить дубликаты.​ способов, как в​
  6. ​ значения​ а не дубликаты,​Column A​

удалить повторяющиеся строки excel 2010

​ содержатся сведения о​ их удаление происходит​ с использованием сразу​. Все остальные настройки​ настройки завершены, жмите​ большинстве всегда так​ — Формула (Format​ примере — диапазон​ с датами их​

Заключение

​В результате выделились все​5. Список повторяющихся значений​ повторяющимися значениями, например​Нажать кнопку «Форматировать как​ Excel удалить повторяющиеся​duplicate​ останется только одна​,​ ценах, которые нужно​ моментально.​ нескольких функций. С​ оставляем по умолчанию.​ на кнопку​ и есть), то​ — Conditional Formatting​ А2:A10), и идем​ проведения. Необходимо найти​ строки, которые повторяются​

​ (столбец B) автоматически​

Отбор повторяющихся значений в MS EXCEL

​ список с названиями​ таблицу», что располагается​ строки автоматически.​в ячейках таблицы,​ копия существующих повторов.​B​ сохранить.​Автор: Максим Тютюшев​ её помощью можно​ После этого кликаем​

​«OK»​​ около параметра​​ — Formula)​ в меню​ одну из них,​ в таблице хотя-бы​ будет обновлен, чтобы​ компаний. В некоторых​

Задача

​ на вкладке «Главная»​Самым простым способом, как​ будут заменены на​Этот метод удобно применять,​и​Поэтому флажок​В некоторых случаях повторяющиеся​ производить поиск дубликатов​ по кнопке​

Решение

​. «Умная таблица» создана.​«Мои данные содержат заголовки»​​в Excel 2007 и​​Формат — Условное форматирование​ но неизвестны все​ 1 раз.​

​ включить новое название.​​ ячейках исходного списка​​ в группе инструментов​
​ в Excel удалить​
​1​
​ когда необходимо найти​

​С​​Январь​​ данные могут быть​​ по конкретному столбцу.​«OK»​​Но создание «умной таблицы»​

​должна стоять галочка.​ новее — нажать​(Format — Conditional Formatting)​​ детали. Известно только,​​​

​СОВЕТ:​ имеются повторы.​ «Стили».​ повторяющиеся строки, является​. Мы использовали значение​ повторяющиеся строки в​

​.​в поле​ полезны, но иногда​ Общий вид данной​

​.​ — это только​ В основном поле​ на вкладке​

Тестируем

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

​Выбрать из выпадающего меню​ использование соответствующего инструмента,​1​ небольших таблицах. Мы​

​Теперь, когда выделена вся​Удаление дубликатов​ они усложняют понимание​ формулы будет выглядеть​​После этого, повторяющиеся записи​​ один шаг для​ окна расположен список​

​Главная (Home)​Выбираем из выпадающего​ во вторник или​ применено только в​

​ является динамическим, т.е.​ содержит только те​ понравившейся стиль (любой).​ располагающегося в ленте.​

​, так как оно​

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

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

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

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

​ и выделения повторяющихся​ адрес_столбца;)>1;0;1);0));»»)​ в любой момент​ дубликатов. Кликаем по​ Строка будет считаться​

  1. ​ правило (Conditional Formatting​и вводим такую​ выделим цветом все​ ИСТИНА. Принцип действия​ список, новый список​ добавлении новых значений​ диапазон ячеек, а​ выделить необходимый диапазон​ Вы можете визуально​ который встроен во​Создать правило1.
  2. ​ В нашем случае​.​ данных. Это позволит​Создаем отдельный столбец, куда​ включить повторным нажатием​СЦЕПИТЬ.
  3. ​ любой ячейке табличного​ дублем только в​Зеленая заливка.
  4. ​ — New Rule)​ проверку:​ даты этих дней​ формулы следующий:​ будет автоматически обновляться.​ в исходный список,​ если он не​

​ ячеек, в пределах​ определить строки, которые​ все продукты Microsoft​ все строки с​

​Работая с Microsoft Excel​

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

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

​=СЧЁТЕСЛИ($A:$A;A2)>1​ недели (вторник, среда).​Первая функция =СЦЕПИТЬ() складывает​ Это очень ресурсоемкая​ новый список должен​ совпадает, то переназначить.​ которого будет поиск​ имеют повторяющиеся значения.​

​ Office. Для начала​ повторяющимися данными удалятся,​ очень часто возникает​ и удалять их​Вводим формулу по указанному​

​«Фильтр»​ появляется дополнительная группа​ всех столбцов, выделенных​ правила​

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

​ по мере необходимости.​ выше шаблону в​.​ вкладок​ галочкой, совпадут. То​Использовать формулу для опеределения​ будет соответственно =COUNTIF($A:$A;A2)>1​ использовать условное форматирование.​ все символы из​ для небольших списков​ повторяющиеся значения.​ рядом с «Таблица​

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

​ есть, если вы​ форматируемых ячеек (Use​Эта простая функция ищет​Выделите диапазон данных в​ только одной строки​ 50-100 значений. Если​Список значений, которые повторяются,​ с заголовками», если​Перейти во вкладку «Данные».​ исходный текст обратно​

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

​ планируется работать.​ удалении отобразится во​ Этот процесс может​ проверить на наличие​ нового столбца. В​Расширенный фильтр в Excel​. Находясь во вкладке​ снимете галочку с​ a formula to​ сколько раз содержимое​ таблице A2:B11 и​ таблицы. При определении​ динамический список не​ создадим в столбце​ она такова, по​

  1. ​Нажать по инструменту «Удалить​ в строку, которая​Открыв таблицу, выберите ячейку,​ всплывающем диалоговом окне.​Создать правило2.
  2. ​ превратиться в бессмысленную,​ повторений.​ нашем конкретном случае​Найти повторяющиеся ячейки можно​«Конструктор»​Использовать формулу.
  3. ​ названия какого-то столбца,​ determine which cell​Зеленый фон.
  4. ​ текущей ячейки встречается​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​ условия форматирования все​ нужен, то можно​B​ итогу нажмите кнопку​ дубликаты», что находится​

​ была заменена. В​ содержимое которой требуется​Вторым инструментом Excel, с​

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

​ монотонную и трудоемкую​Примечание:​ формула будет иметь​ также при помощи​кликаем по кнопке​ то тем самым​ to format)​ в столбце А.​ форматирование»-«Создать правило».​ ссылки указываем на​ пойти другим путем:​с помощью формулы​

Как в эксель найти повторяющиеся строки

Способ 1. Если у вас Excel 2007 или новее

​ «ОК».​ в группе «Работа​ нашем случае мы​ найти и заменить,​

​ помощью которого можно​ работу, однако, существует​ В Excel не поддерживается​​ следующий вид:​​ условного форматирования таблицы.​​«Удалить дубликаты»​​ расширяете вероятность признания​​Затем ввести формулу проверки​ Если это количество​В появившемся окне «Создание​ первую строку таблицы.​​ см. статью Отбор​

Как в эксель найти повторяющиеся строки

​ массива. (см. файл​»Умная таблица» создана, но​ с данными».​ восстановим значения в​

Способ 2. Если у вас Excel 2003 и старше

​ и скопируйте ее.​ найти и удалить​ ряд способов упростить​ выделение повторяющихся значений​=ЕСЛИОШИБКА(ИНДЕКС(A8:A15;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E7:$E$7;A8:A15)+ЕСЛИ(СЧЁТЕСЛИ(A8:A15;A8:A15)>1;0;1);0));»»)​ Правда, удалять их​, которая расположена на​​ записи повторной. После​ ​ количества совпадений и​​ повторений больше 1,​​ правила форматирования» выберите​Абсолютные и относительные адреса​​ повторяющихся значений с​​ примера).​ это еще не​

​В появившемся окне поставить​

​ 1-й строке таблицы.​ Для этого выделите​

​ дубликаты, является​ задачу. Сегодня мы​ в области «Значения»​Выделяем весь столбец для​ придется другим инструментом.​ ленте в блоке​ того, как все​ задать цвет с​ т.е. у элемента​ опцию: «Использовать формулу​ ссылок в аргументах​​ помощью фильтра. ​​Введем в ячейку​​ все. Теперь вам​​ галочку рядом с​Определив строки с повторяющимся​​ нужную ячейку и​​Расширенный фильтр​

Способ 3. Если много столбцов

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

Как в эксель найти повторяющиеся строки

​ функций позволяют нам​Достаточно часто рабочие таблицы​B5​ необходимо выделить любую​ «Мои данные содержат​ содержимым, поочередно выделите​ нажмите сочетание клавиш​

​. Этот метод также​ методов нахождения и​На вкладке​ Устанавливаем курсор в​ во вкладке​«Сервис»​ жмем на кнопку​Формат (Format)​

Как в эксель найти повторяющиеся строки

​ срабатывает заливка ячейки.​ ячеек».​ распространять формулу на​ Excel содержат повторяющиеся​формулу массива:​ ячейку таблицы, чтобы​ заголовки».​ их, удерживая клавишу​Ctrl+C​ применим к Excel​ удаления повторяющихся строк​

  • ​Главная​ конец строки формул.​«Главная»​​.​«OK»​- все, как​ Для выбора цвета​
  • ​В поле ввода введите​ все строки таблицы.​ записи, которые многократно​​=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;​​ во вкладках появился​​Установить галочки рядом с​Ctrl​.​​ 2003. Чтобы применить​ в Excel. В​​выберите​ Нажимаем на клавиатуре​, жмем на кнопку​После этого, открывается окно​.​

​ в Способе 2:​ выделения в окне​ формулу:​Вторая функция =СЦЕПИТЬ() по​​ дублируются. Но не​​ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)​ пункт «Конструктор» и​

Как в Excel найти повторяющиеся и одинаковые значения

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

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

Поиск одинаковых значений в Excel

  • Выбрать ячейку I3 с записью «С. А. Есенин».
  • Поставить задачу – выделить цветом ячейки с такими же записями.
  • Выделить область поисков.
  • Нажать вкладку «Главная».
  • Далее группа «Стили».
  • Затем «Условное форматирование»;
  • Нажать команду «Равно».
  • Появится диалоговое окно:
  • В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
  • В правом поле можно выбрать цвет шрифта.
  • Нажать «ОК».

как выделить повторяющиеся значения в excel разными цветами

Ищем в таблицах Excel все повторяющиеся значения

  • Зайти в группу «Стили».
  • Далее «Условное форматирование».
  • Теперь в выпадающем меню выбрать «Правила выделения ячеек».
  • Затем «Повторяющиеся значения».
  • Появится диалоговое окно:
  • Нажать «ОК».

как в excel найти повторяющиеся строки

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

  1. Зайти во вкладку «Данные».
  2. Выделить столбец, в котором следует искать дублирующиеся строки.
  3. Опция «Удалить дубликаты».

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

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

  • Выбрать вкладку «Данные».
  • Перейти в раздел «Сортировка и фильтр».
  • Нажать команду «Дополнительно»:
  • В появившемся диалоговом окне ставим флажок «Только уникальные записи».
  • Нажать «OK» – уникальный список готов.

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

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

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

Поиск совпадений в двух списках

Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов — подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:

Исходные списки для сравнения

Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен — Создать (Formulas — Name Manager — Create) . Также можно превратить таблицы в «умные» с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table) .

Подсчет количества совпадений

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

Количество совпадений формулой

В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))

Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек.

Во-первых, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации:

=СЧЁТЕСЛИ( Где_искать ; Что_искать )

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

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

Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции — суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl + Shift + Enter . Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.

Вывод списка совпадений формулой массива

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

Вывод совпадений в двух списках формулой массива

В английской версии это будет, соответственно:

Логика работы этой формулы следующая:

  • фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
  • фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
  • и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент

Возникающие на избыточных ячейках ошибки #Н/Д можно дополнительно перехватить и заменить на пробелы или пустые строки «» с помощью функции ЕСЛИОШИБКА (IFERROR) .

Вывод списка совпадений с помощью слияния запросов Power Query

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

Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table) :

Загрузка списков в Power Query

Excel превратит нашу таблицу в «умную» и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить — Закрыть и загрузить в. (Close & Load To. ) и выбрать в появившемся окне Только создать подключение (Create only connection) :

Закрыть и загрузить вТолько подключение

Затем повторяем то же самое со вторым диапазоном.

И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные — Объединить запросы — Объединить (Get Data — Merge Queries — Merge) :

Объединение запросов в Power Query

В открывшемся окне делаем три вещи:

  1. выбираем наши таблицы из выпадающих списков
  2. выделяем столбцы, по которым идет сравнение
  3. выбираем Тип соединения = Внутреннее (Inner Join)

Слияние для выявления совпадающих строк

После нажатия на ОК на экране останутся только совпадающие строки:

Результат слияния

Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :

Выгрузка результатов на лист

Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 .

Макрос для вывода списка совпадений

Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer) . Если ее не видно, то отобразить ее можно через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .

В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert — Module и затем скопировать туда код нашего макроса:

Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl , оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F8 . Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:

Макрос поиска совпадений в двух списках

Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

повторяющиеся значения excel лого

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

1. Удаление повторяющихся значений в Excel (2007+)

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

удалить-дубликаты excel

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

удалить-дубликаты excel

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2. Использование расширенного фильтра для удаления дубликатов

Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

расширенный фильтр excel

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

расширенный фильтр excel

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

расширенный фильтр excel

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

повторяющиеся значения excel

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

повторяющиеся значения excel

4. Использование сводных таблиц для определения повторяющихся значений

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

сводная таблица excel

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *