Как найти повторяющиеся значения в 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.

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

Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

1. Полностью выделяем все ячейки таблицы с данными, воспользовавшись, например, зажатой левой кнопкой мыши.
2. Во вкладке “Данные” в разделе инструментов “Работа с данными” находим кнопку “Удалить дубликаты” и кликаем на нее.
3. Переходим к настройкам параметров удаления дубликатов:
   • Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заголовки” – он должен быть отмечен галочкой.
   • Ниже, в основном окне, перечислены названия столбцов, по которым будет осуществляться поиск дубликатов. Система считает совпадением ситуацию, в которой в строках повторяются значения всех выбранных в настройке столбцов.  Если убрать часть столбцов из сравнения, повышается вероятность увеличения количества похожих строк.
   • Тщательно все проверяем и нажимаем ОК.
4. Далее программа Эксель в автоматическом режиме найдет и удалит все дублированные строки.
5. По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Для закрытия окна и завершения работы данной функции нажимаем кнопку OK.

Метод 2: удаление повторений при помощи “умной таблицы”

Еще один способ удаления повторяющихся строк  – использование “умной таблицы“. Давайте рассмотрим алгоритм пошагово.

1. Для начала, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
2. Во вкладке “Главная” находим кнопку “Форматировать как таблицу” (раздел инструментов “Стили“). Кликаем на стрелку вниз справа от названия кнопки и выбираем понравившуюся цветовую схему таблицы.
3. После выбора стиля откроется окно настроек, в котором указывается диапазон для создания “умной таблицы“. Так как ячейки были выделены заранее, то следует просто убедиться, что в окошке указаны верные данные. Если это не так, то вносим исправления, проверяем, чтобы пункт “Таблица с заголовками” был отмечен галочкой и нажимаем ОК. На этом процесс создания “умной таблицы” завершен.
4. Далее приступаем к основной задаче – нахождению задвоенных строк в таблице. Для этого:
   • ставим курсор на произвольную ячейку таблицы;
   • переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
   • в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.
5. Следующие шаги полностью совпадают с описанными в методе выше действиями по удалению дублированных строк.

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

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

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

1. Как обычно, выделяем все ячейки таблицы.
2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
4. В появившемся окне с расширенными настройками:
   • как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
   • отмечаем галочкой пункт “Только уникальные записи“;
   • жмем ОК.
5. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

Метод 4: условное форматирование

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

1. Выделяем все ячейки нашей таблицы.
2. Во вкладке “Главная” кликаем по кнопке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
3. Откроется перечень, в котором выбираем группу “Правила выделения ячеек“, а внутри нее – пункт “Повторяющиеся значения“.
4. Окно настроек форматирования оставляем без изменений. Единственный его параметр, который можно поменять в соответствии с собственными цветовыми предпочтениями – это используемая для заливки выделяемых строк цветовая схема. По готовности нажимаем кнопку ОК.
5. Теперь все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое или удалить строки целиком любым удобным способом.

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

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

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

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));"")

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

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

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

Заключение

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

Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и — вперед!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При работе с большими наборами данных в Excel или объединении нескольких небольших электронных таблиц в большие листы вы можете столкнуться с большим количеством идентичных строк.

И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и более ранних.

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

Поиск повторяющихся значений включая первые вхождения.

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

= СЧЁТЕСЛИ (LA: LA; LA2)> 1

Где A2 — первая ячейка области поиска.

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

Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если есть совпадение. А для значений, которые встречаются только 1 раз, отображается ЛОЖЬ.

Запрос! Если вы ищете повторы в определенной области, а не во всем столбце, укажите желаемый диапазон и «зафиксируйте» его знаками $. Это значительно ускорит расчеты. Например, если вы смотрите в формате A2: A8, используйте

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 8; A2)> 1

Если вас смущают значения ИСТИНА и ЛОЖЬ в столбце статуса и вы не хотите помнить, какое из них означает повторение, а какое уникально, заключите свой СЧЁТЕСЛИ в функцию ЕСЛИ и укажите любые слова, которые должны соответствовать повторяющимся и уникальным:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; A2)> 1, «Дублировать», «Уникальный»)

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

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 17; A2)> 1, «Дублировать»;»»)

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

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

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

Если вам просто нужно указать совпадения, давайте немного изменим:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)> 1, «Дублировать»;»»)

На скриншоте ниже вы можете увидеть эту формулу в действии.

легко понять, что это не означает первое вхождение слова, а отсчет начинается со второго.

Чувствительный к регистру поиск дубликатов

Я хотел бы обратить ваше внимание на то, что, хотя приведенные выше формулы находят 100% дубликатов, есть тонкий момент: они не чувствительны к регистру. Может, для тебя это не важно. Но если abc, abc и abc — три разных параметра в ваших данных, то этот пример для вас.

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

{= ЕСЛИ (СУММ ((- ТОЧНО ($ A $ 2: $ A $ 17, A2)))

Не забывайте, что формулы массива вводятся нажатием Ctrl + Shift + Enter.

Вернувшись к оглавлению, используйте функцию ТОЧНО, чтобы сравнить целевую ячейку со всеми другими ячейками в выбранной области. Результат возвращается как ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из единиц и нулей с помощью оператора (—).

Затем функция СУММ складывает эти числа. И если результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.

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

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

Как извлечь дубликаты из диапазона.

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

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

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

{= ИНДЕКС (МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1; $ A $ 2: $ E $ 11); СТРОКА ($ 1: $ 100)); МАЛЕНЬКИЙ (ЕСЛИОШИБКА (ЕСЛИ (ПОИСК (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11))> 1; $ A $ 2: $ E $ 11); RIGA ($ 1: $ 100)); SMALL (IF (COUNTIF ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1; $ A $ 2: $ E $ 11); LINE ($ 1: $ 100)); 0) = LINE ($ 1: $ 100); LINE ($ 1: $ 100)); «»); STRING () — 1))}

вы можете получить постоянно растущий список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl + Alt + Enter.

Затем перетащите маркер заливки вниз по мере необходимости.

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

= ЕСЛИОШИБКА (ИНДЕКС (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1, $ A $ 2: $ E $ 11), СТРОКА ($ 1: $ 100)); МАЛЕНЬКИЙ (ЕСЛИОШИБКА (ЕСЛИ (ПОИСК (МАЛЫЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11))> 1; $ A $ 2: $ E $ 11); СТРОКА ($ 1: $ 100)); МАЛЕНЬКИЙ (ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ E $ 11; $ A $ 2: $ E $ 11)> 1; $ A $ 2: $ E $ 11); LINE ($ 1: $ 100)); 0) = LINE ($ 1: $ 100); LINE ($ 1: $ 100)); «»); LINE () — 1));»»)

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

Поэтому, если вам нужно поместить его, например, в ячейку K4, замените выражение LINE () — 1 в конце на LINE () — 3.

Обнаружение повторяющихся строк

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

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

Итак, у нас есть данные о товарах и покупателях.

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

Допустим, данные хранятся в столбцах A: C. В ячейку D2 записываем:

= A2, B2 и C2

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

= СЧЁТЕСЛИ (D: D; D2)

Копировать для всех строк данных.

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

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

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

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

Прежде всего, я рекомендую вам отформатировать наши данные как умную таблицу. Напоминаю: Главное меню — Форматировать в виде таблицы.

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

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

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

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

Условное выделение — очень важный инструмент Excel, о котором мы говорили достаточно подробно.

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

Как показано на изображении ниже, выберите «Правила выбора ячеек — Дублировать». Неуникальные данные будут выделены цветом.

Но здесь нельзя исключать первых явлений: все подсвечено.

Но эту проблему можно решить с помощью формулы условного форматирования.

= СЧЁТЕСЛИ ($ B $ 2: $ B2; B2)> 1

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

Чтобы освежить память, вы можете прочитать нашу статью «Как изменить цвет ячейки по значению».

Поиск совпадений при помощи команды «Найти».

Другой простой, но не очень технологичный способ — использовать интегрированный поиск.

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

Затем нажимаем «Найти все» и видим все найденные дубликаты и их расположение, как на изображении ниже.

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

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

Как применить сводную таблицу для поиска дубликатов.

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

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

Создайте новый макет сводной таблицы. И затем мы используем ту же строку и поле значения. В нашем случае — «Товар». Поскольку название продукта является текстовым, Excel по умолчанию использует функцию СЧЁТ для вычисления этих значений, то есть подсчитывает количество. И это то, что нам нужно. Если он больше 1, есть дубликаты.

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

Например, откуда берутся 3 повторяющихся спрайта? Щелкаем на цифре 3 и видим следующее изображение:

Я думаю, что этот метод можно использовать достаточно хорошо. Самое замечательное, что формулы не нужны.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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