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

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

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

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

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

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

Действуем так:

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

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

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

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

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

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

---

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

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

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

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

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

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

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

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

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

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

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

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

---

Заключение

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

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

---

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

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

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

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

На
рисунке – списки писателей. Алгоритм
действий следующий:

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

• Появится
  диалоговое окно:

• В
  левом поле указать ячейку с I2,
  в которой записано «С. А. Есенин».
• В
  правом поле можно выбрать цвет шрифта.
• Нажать
  «ОК».

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

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

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

Отметим
все неуникальные записи в выделенной
области. Для этого нужно:

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

• Появится
  диалоговое окно:

• Нажать
  «ОК».

Программа
ищет повторения во всех столбцах.

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

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

Способ
удаления неуникальных записей:

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

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

Список
с уникальными значениями:

Расширенный фильтр: оставляем
только уникальные записи

Расширенный
фильтр – это инструмент для получения
упорядоченного списка с уникальными
записями.

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

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

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

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

Вкладка
«Вставка».

Пункт
«Сводная таблица».

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

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

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

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

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

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

Ключевых моментов несколько:

• Какие конкретно повторяющиеся значения — повторы слов в ячейках, сами повторяющиеся ячейки или повторяющиеся строки?
• Если ячейки, то:
  • Какие ячейки мы готовы считать дубликатами — все кроме первой или включая ее?
  • Считаем ли дублями строки, отличающиеся только пробелами до/после слов или лишними пробелами между словами?
  • Где мы будем искать дубли — в одном столбце, в двух столбцах или в нескольких?
  • А может, нам нужно найти неявные дубли?

Сначала рассмотрим простые примеры.

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

Найти инструмент можно на вкладке программы “Главная”:

Процедура интуитивно понятна:

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

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

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

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

Но есть и другие решения. О них дальше.

Формула проверки наличия дублей в диапазонах

Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Но её я возьму на себя.

Внутри диапазона

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0

Так выглядит на практике применение формулы:

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

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ .Это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

=СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1

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

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

Обратите внимание на один момент в этой демонстрации: диапазон закреплен ($A$1:$B$4), а искомая ячейка (A1) нет. Именно это позволяет условному форматированию находить все дубликаты в диапазоне.

!SEMTools, поиск дублей внутри диапазона

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

Давайте покажу, как они работают.

Найти дубли ячеек в столбце, кроме первого

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

Найти в столбце дубли ячеек, включая первый

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

Найти дубли в столбце без учета лишних пробелов

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

Для первой операции есть отдельный инструмент «Удалить лишние пробелы»:

Найти повторяющиеся значения в Excel и решить сотни других задач поможет надстройка !SEMTools.

Скачайте прямо сейчас и убедитесь сами!

---

Смотрите также:

• Удалить дубли без смещения строк;
• Удалить неявные дубли;
• Найти повторяющиеся слова в Excel;
• Удалить повторяющиеся слова внутри ячеек.

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

​Смотрите также​ одинаковые значения, например:​A3​Выберите стиль форматирования и​ Следуйте инструкции ниже,​(Conditional Formatting >​ Это очень ресурсоемкая​ (столбец С) автоматически​ИсхСписок- это Динамический диапазон​ значения, которые повторяются.​ Как убрать повторяющиеся​

1. ​ способов найти одинаковые​ нам нужно выделить:​Первый способ.​

   ​Нажмите кнопку​​Перед попыткой удаления​>​В некоторых случаях повторяющиеся​12​

2. ​:​​ нажмите​​ чтобы выделить только​​ Highlight Cells Rules)​​ задача и годится​​ будет обновлен, чтобы​​ (ссылка на исходный​​ Дополнительное условие: при​​ значения в Excel,​

   

3. ​ значения в Excel​ повторяющиеся или уникальные​​Как найти одинаковые значения​​ОК​ повторений удалите все​Повторяющиеся значения​ данные могут быть​​45​​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​

   

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

​ОК​​ те значения, которые​​ и выберите​ для небольших списков​ включить новое название​ список в столбце​ добавлении новых значений​ смотрите в статье​ и выделить их​

1. ​ значения. Выбираем цвет​ в Excel​.​

   ​ структуры и промежуточные​​.​ полезны, но иногда​12​Обратите внимание, что мы​.​

2. ​ встречающиеся трижды:​​Повторяющиеся значения​​ 50-100 значений. Если​​3. Добавьте в исходный​​А​​ в исходный список,​​ «Как удалить дубли​ не только цветом,​ заливки ячейки или​.​

   

   ​Рассмотрим,​ итоги из своих​В поле рядом с​ они усложняют понимание​6​

   

   ​ создали абсолютную ссылку​​Результат: Excel выделил значения,​​Сперва удалите предыдущее правило​​(Duplicate Values).​​ динамический список не​

   

3. ​ список название новой​​).​​ новый список должен​

support.office.com

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

​ в Excel».​​ но и словами,​​ цвет шрифта.​Например, число, фамилию,​к​​ данных.​ оператором​ данных. Используйте условное​8​ –​ встречающиеся трижды.​ условного форматирования.​Определите стиль форматирования и​ нужен, то можно​ компании еще раз​Скопируйте формулу вниз с​ автоматически включать только​Из исходной таблицы с​ числами, знаками. Можно​Подробнее смотрите в​ т.д. Как это​ак найти и выделить​
​На вкладке​
​значения с​ форматирование для поиска​​12​
​$A$1:$C$10​Пояснение:​Выделите диапазон​ нажмите​ пойти другим путем:​
​ (в ячейку​
​ помощью Маркера заполнения​ повторяющиеся значения.​​ повторяющимися значениями отберем​ настроить таблицу так,​ статье «Выделить дату,​

​ сделать, смотрите в​ одинаковые значения в​Данные​выберите форматирование для​ и выделения повторяющихся​6​.​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​
​A1:C10​ОК​ см. статью Отбор​А21​ (размерность списка значений​Список значений, которые повторяются,​ только те значения,​
​ что дубли будут​ день недели в​ статье «Как выделить​ Excel.​нажмите кнопку​​ применения к повторяющимся​ данных. Это позволит​589​

​Примечание:​ значений в диапазоне​.​.​ повторяющихся значений с​снова введите ООО​ имеющих повторы должна​ создадим в столбце​ которые имеют повторы.​ не только выделяться,​ Excel при условии»​​ ячейки в Excel».​Нам поможет условное​Удалить дубликаты​ значениям и нажмите​ вам просматривать повторения​Можно ли как-нибудь​Вы можете использовать​A1:C10​На вкладке​Результат: Excel выделил повторяющиеся​ помощью фильтра. ​ Кристалл)​ совпадать с размерностью​B​ Теперь при добавлении​ но и считаться.​ тут.​Второй способ.​ форматирование.Что такое условное​и в разделе​ кнопку​ и удалять их​ обозначить эти совпадения​ любую формулу, которая​, которые равны значению​Главная​ имена.​Этот пример научит вас​4. Список неповторяющихся значений​ исходного списка).​с помощью формулы​

excel-office.ru

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

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

​ в ячейке A1.​​(Home) выберите команду​​Примечание:​ находить дубликаты в​ автоматически будет обновлен,​В файле примера также​ массива. (см. файл​ исходный список, новый​

Задача

​ значения с первого​ D выделились все​ в Excel​ с ним работать,​установите или снимите​.​Выберите ячейки, которые нужно​ форматирования? Или просто​ чтобы выделить значения,​

Решение

​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​Условное форматирование​​Если в первом​​ Excel с помощью​ новое название будет​ приведены перечни, содержащие​

​ примера).​​ список будет автоматически​​ слова, а можно​
​ года – 1960.​
​. В этой таблице​
​ смотрите в статье​

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

​>​ выпадающем списке Вы​ условного форматирования. Перейдите​​ исключено​​ неповторяющиеся значения и​

​Введем в ячейку​ содержать только те​ выделять дубли со​Можно в условном​ нам нужно выделить​ “Условное форматирование в​

​ в которых нужно​Удаление дубликатов​ повторений.​ и выделить в​

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

Тестируем

​ по этой ссылке,​5. Список повторяющихся значений​ уникальные значения.​​B5​​ значения, которые повторяются.​

​ второго и далее.​ форматировании тоже в​ год рождения 1960.​ Excel” здесь. Выделить​

​ удалить повторения.​повторяющиеся данные удаляются​Примечание:​ доп столбце?​​ формулу:​​ кнопку​(Conditional Formatting >​

​Повторяющиеся​ чтобы узнать, как​ (столбец B) автоматически​С помощью Условного форматирования​

​формулу массива:​Пусть в столбце​ Обо всем этом​ разделе «Правила выделенных​

​Выделяем столбец «Год​

​ повторяющиеся значения в​Например, на данном листе​ безвозвратно. Чтобы случайно​ В Excel не поддерживается​_Boroda_​=COUNTIF($A$1:$C$10,A1)>3​Условное форматирование​ New Rule).​(Duplicate) пункт​ удалить дубликаты.​ будет обновлен, чтобы​ в исходном списке​=ЕСЛИОШИБКА(ИНДЕКС(ИсхСписок;​А​ и другом читайте​ ячеек» выбрать функцию​

excel2.ru

Поиск дубликатов в Excel с помощью условного форматирования

​ рождения». На закладке​ Excel можно как​ в столбце “Январь”​ не потерять необходимые​ выделение повторяющихся значений​: Выделяем диапазон,​=СЧЕТЕСЛИ($A$1:$C$10;A1)>3​

1. ​(Conditional Formatting), мы​​Нажмите на​​Уникальные​
2. ​Выделите диапазон​​ включить новое название.​​ можно выделить повторяющиеся​​ПОИСКПОЗ(0;СЧЁТЕСЛИ(B4:$B$4;ИсхСписок)+ ЕСЛИ(СЧЁТЕСЛИ(ИсхСписок;ИсхСписок)>1;0;1);0)​​имеется список с​​ в статье “Как​​ «Содержит текст». Написать​ «Главная» в разделе​ во всей таблицы,​​ содержатся сведения о​​ сведения, перед удалением​
3. ​ в области “Значения”​Главная – условное​​Урок подготовлен для Вас​​ выбрали диапазон​​Использовать формулу для определения​(Unique), то Excel​

   

​A1:C10​​СОВЕТ:​ значения.​);””)​​ повторяющимися значениями, например​​ найти повторяющиеся значения​​ этот текст (например,​​ «Стили» нажимаем кнопку​ так и в​ ценах, которые нужно​

​ повторяющихся данных рекомендуется​ отчета сводной таблицы.​ форматирование – Правила​ командой сайта office-guru.ru​A1:C10​ форматируемых ячеек​ выделит только уникальные​.​Созданный список повторяющихся значений​

1. ​1. Добавьте в исходный​Вместо​
2. ​ список с названиями​​ в Excel”. В​​ фамилию, цифру, др.),​
3. ​ «Условное форматирование». Затем​​ определенном диапазоне (строке,​​ сохранить.​​ скопировать исходные данные​​На вкладке​​ выделения ячеек -​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​, Excel автоматически скопирует​
4. ​(Use a formula​​ имена.​На вкладке​​ является динамическим, т.е.​ список название новой​ENTER​
5. ​ компаний. В некоторых​

   ​ таблице можно удалять​
​ и все ячейки​

6. ​ в разделе «Правила​ столбце). А функция​​Поэтому флажок​​ на другой лист.​​Главная​ Повторяющиеся значения​

   

   ​Перевел: Антон Андронов​

   • ​ формулы в остальные​ to determine which​​Как видите, Excel выделяет​​Главная​ при добавлении новых​
   • ​ компании (в ячейку​нужно нажать​
   • ​ ячейках исходного списка​ дубли по-разному. Удалить​​ с этим текстом​​ выделенных ячеек» выбираем​ “Фильтр в Excel”​​Январь​​Выделите диапазон ячеек с​выберите​Или в соседнем​Автор: Антон Андронов​​ ячейки. Таким образом,​​ cells to format).​​ дубликаты (Juliet, Delta),​​(Home) нажмите​

     ​ значений в исходный​

   • ​А20​CTRL + SHIFT +​ имеются повторы.​​ строки по полному​​ выделятся цветом. Мы​

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

​Введите следующую формулу:​
​ значения, встречающиеся трижды​

​Условное форматирование​ список, новый список​
​введите ООО Кристалл)​
​ ENTER​

​Создадим новый список, который​

office-guru.ru

Найти повторяющиеся значения в столбце (Формулы)

​ совпадению, удалить ячейки​​ написали фамилию «Иванов».​В появившемся диалоговом​ если нужно. Рассмотрим​
​Удаление дубликатов​
​ нужно удалить.​
​>​
​ в условном форматировании)​
​: имеется столбец в​
​A2​
​=COUNTIF($A$1:$C$10,A1)=3​
​ (Sierra), четырежды (если​
​>​ будет автоматически обновляться.​2. Список неповторяющихся значений​.​ содержит только те​ в столбце, т.д.​Есть еще много​

​ окне выбираем, что​​ несколько способов.​
​нужно снять.​Совет:​Правила выделения ячеек​200?’200px’:”+(this.scrollHeight+5)+’px’);”>=СЧЁТЕСЛИ(Диапазон;Ссылка_на_ячейку)>1​
​ котором могут находиться​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​=СЧЕТЕСЛИ($A$1:$C$10;A1)=3​
​ есть) и т.д.​

excelworld.ru

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

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

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

Выберите ячейки, которые нужно проверить на наличие повторений.

Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.

В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК .

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

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

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

Совет: Перед попыткой удаления повторений удалите все структуры и промежуточные итоги из своих данных.

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

Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить.

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

Нажмите кнопку ОК.

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

Источник

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

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

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

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

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Вберете «Использовать формулу для определения форматируемых ячеек».
3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

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

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

Источник

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

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

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

1 Сравнение с помощью простого поиска

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

1. Перейти на главную вкладку табличного процессора.
2. В группе «Редактирование» выбрать пункт поиска.
3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.

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

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

1. Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
2. В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
3. Протянуть формулу до конца столбца.

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

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

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

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

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».

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

5 Сравнение с выделением совпадений цветом

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

Порядок действий для применения методики следующий:

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

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

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

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

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

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

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

Источник