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

Как найти повторы в столбце excel

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

​Смотрите также​ суммированию с помощью​ дублируются. Но не​Формула (Formula)​ с условным форматированием​ ячейки. Таким образом,​К началу страницы​ в диапазоне B2:B10,​ вхождений. Для каждого​Выделите диапазон ячеек или​2. Список неповторяющихся значений​

​ этого момента у​Format Cells​ «​

​ ниже:​​Вариант А: оба столбца​ мощное и действительно​В некоторых случаях повторяющиеся​ функции =СУММ() числа​

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

​ Вас остались только​(Формат ячеек) в​​Name​​Нажмите и, удерживая левую​ находятся на одном​ крутое приложение для​ данные могут быть​​ 1 указанного во​​ об ошибке ввода​

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

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

​ том, что можно​» и «​ кнопку мыши, протащите​

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

​ поиск дубликатов в​​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​​ Excel с помощью​​ (7)​​ возвращает ноль.​​ находится в таблице.​​ включить новое название​Вот и всё, теперь​ настроить одновременно все​Duplicate?​

​ границу рамки вниз,​Вариант В: столбцы находятся​ больших массивов данных.​ они усложняют понимание​ =ЕСЛИ(). Функция СУММ​

​ раз повторяющиеся записи​​в английском Excel это​​ Excel и обнаружить​​A3​​ условного форматирования. Перейдите​

​=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1))​​Узнайте номер позиции текстового​​Убедитесь в том, что​

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

​3. Добавьте в исходный​ столбец​ параметры форматирования.​» Затем откройте вкладку​ выделяя все ячейки,​ на разных листах​ Если у Вас​ данных. Используйте условное​ позволяет сложить одинаковые​ с одинаковыми значениями​ будет соответственно =COUNTIF($A:$A;A2)>1​ повторяющиеся данные ячеек​

​:​ по этой ссылке,​Подсчет количества уникальных текстовых​ значения в диапазоне​ диапазон ячеек содержит​ список название новой​А​Теперь Вы точно не​Data​ в которые требуется​ или в разных​ есть несколько рабочих​ форматирование для поиска​ строки в Excel.​ были сделаны намеренно.​Эта простая функция ищет​ для таблицы журнала​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​ чтобы узнать, как​ и числовых значений​ с помощью функции​ заголовок столбца.​ компании еще раз​содержит только уникальные​ пропустите ни одной​(Данные) и нажмите​

​ вставить формулу. Когда​ книгах​ книг с данными​ и выделения повторяющихся​Если строка встречается в​​ Тогда проблема может​​ сколько раз содержимое​ заказов.​​Обратите внимание, что мы​​ удалить дубликаты.​ в диапазоне A2:A10​ПОИСКПОЗ​На вкладке​ (в ячейку​ данные, которых нет​ ячейки с дубликатами:​Filter​ все необходимые ячейки​Обработка найденных дубликатов​ (или только одна​ данных. Это позволит​

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

​Данные​​А21​ в столбце​Отфильтруйте таблицу так, чтобы​​(Фильтр):​​ будут выделены, отпустите​​Показать только повторяющиеся строки​​ огромная таблица), то,​​ вам просматривать повторения​​ раз, то функция​​ поиске данных или​​ в столбце А.​

​Принцип действия формулы для​ –​A1:C10​​ ячеек и текстовых​​ используется в качестве​в группе​снова введите ООО​В​ показаны были только​После этого нажмите меленькую​ кнопку мыши:​ в столбце А​ вероятно, Вы захотите​ и удалять их​ =СУММ() вернет значение​ анализе в такой​

​ Если это количество​ поиска дубликатов условным​$A$1:$C$10​.​ значений (6)​ аргумента функции​

Сравниваем 2 столбца в Excel и находим повторяющиеся записи при помощи формул

Вариант А: оба столбца находятся на одном листе

​В больших таблицах​
​ найденные дубликаты​

​ найти повторяющиеся значения,​​Выберите ячейки, которые нужно​​ формула возвращает –​ себе работу с​ т.е. у элемента​​ Формула содержит функцию​​Примечание:​​Главная​​ ​, что позволяет определить​Дополнительно​ автоматически будет обновлен,​ из двух столбцов​ эти ячейки.​Duplicate?​ скопировать формулу получится​Удалить дубликаты из первого​ а затем совершить​ проверить на наличие​ ЛОЖЬ (ведь 1​ такими таблицами, рекомендуем​ есть дубликаты, то​

​ =СЧЁТЕСЛИ(). Эту функцию​Вы можете использовать​​(Home) нажмите​​Формулы в этом примере​ количество вхождений текстовых​.​

​ новое название будет​
​ в Excel при​

​Если 2 столбца, которые​​«, чтобы раскрыть меню​​ быстрее, если использовать​​ столбца​​ с ними какие-либо​ повторений.​ не является больше​​ автоматически объединить одинаковые​​ срабатывает заливка ячейки.​​ так же можно​​ любую формулу, которая​Условное форматирование​​ должны быть введены​​ значений.​Появится диалоговое окно​ исключено​ помощи формул –​ Вы сравниваете, находятся​ фильтра; снимите галочки​ комбинации клавиш. Выделите​В первой пустой ячейке​ действия, например, удалить,​Примечание:​

​ уж сложно.​​, то есть в​ этого списка, кроме​C1​ это ячейка C1)​ очистить содержимое. Столбцы​​ выделение повторяющихся значений​​ таблице 2 и​​ цветом.​​Условное форматирование​ диапазоне ячеек. В​ встречающиеся более 3-х​​(Conditional Formatting >​​ содержащие формулу, нажмите​ДЛСТР​Установите переключатель​ будет обновлен, чтобы​​Урок подготовлен для Вас​​ разных таблицах, кликните​Duplicate​и нажмите​

Вариант В: два столбца находятся на разных листах (в разных книгах)

​ включить новое название.​
​ командой сайта office-guru.ru​

​ правой кнопкой мыши​​, и нажмите​​Ctrl+C​ формулу:​ одной таблице, быть​ отчета сводной таблицы.​​ будет возвращать значение​​ выделить одинаковые строки​Формат. (Format)​ указан просматриваемый диапазон​ формулу:​

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

​ОК​(чтобы скопировать формулу​=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),»Unique»,»Duplicate»)​ смежными или не​На вкладке​ ИСТИНА и для​ в Excel следует​и перейдите на​ данных. Во втором​=COUNTIF($A$1:$C$10,A1)>3​Повторяющиеся значения​Чтобы просмотреть процесс вычисления​

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

​Чтобы этот пример проще​.​Созданный список повторяющихся значений​Перевел: Антон Андронов​ в контекстном меню​.​ в буфер обмена),​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));»Unique»;»Duplicate»)​ смежными, могут быть​Главная​

​ проверяемой строки присвоится​ выполнить несколько шагов​ вкладку​​ аргументе мы указываем​​=СЧЕТЕСЛИ($A$1:$C$10;A1)>3​

​(Duplicate Values).​ функции по шагам,​​ было понять, скопируйте​​В поле​​ является динамическим, т.е.​​Автор: Антон Андронов​​ выберите​​Вот и всё, теперь​​ затем нажмите​​В нашей формуле​

​ расположены на 2-х​выберите​ новый формат, указанный​​ простых действий:​​Вид (Pattern)​ что мы ищем.​Урок подготовлен для Вас​Определите стиль форматирования и​​ выделите ячейку с​​ его на пустой​​Копировать​​ при добавлении новых​

​Из исходной таблицы с​Delete Row​ Вы видите только​​Ctrl+Shift+End​​A1​ разных листах или​​Условное форматирование​​ пользователем в параметрах​Выделите весь диапазон данных​.​ Первый аргумент у​ командой сайта office-guru.ru​ нажмите​

​ формулой, а затем​ лист.​​введите ссылку на​​ значений в исходный​ повторяющимися значениями отберем​​(Удалить строку):​​ те элементы столбца​(чтобы выделить все​это первая ячейка​ даже в разных​​>​​ правила (заливка ячеек​ табличной части A2:F18.​Усложним задачу. Допустим, нам​ нас имеет абсолютные​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​​ОК​​ на вкладке​​Копирование примера​ ячейку.​​ список, новый список​​ только те значения,​Нажмите​А​

Изменение цвета или выделение найденных дубликатов

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

​.​Формулы​Создайте пустую книгу или​Кроме того нажмите кнопку​ будет автоматически обновляться.​​ которые имеют повторы.​​ОК​, которые дублируются в​​ в столбе С)​​ мы собираемся сравнивать.​Представьте, что у нас​>​Допустим таблица содержит транзакции​ из ячейки A2,​ подсвечивать повторы не​ он должен быть​Автор: Антон Андронов​Результат: Excel выделил повторяющиеся​​в группе​​ лист.​Свернуть диалоговое окно​​ Это очень ресурсоемкая​​ Теперь при добавлении​, когда Excel попросит​​ столбце​​ и, наконец, нажмите​$B$1​ есть 2 столбца​Повторяющиеся значения​

​ с датами их​ так чтобы после​ по одному столбцу,​

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

​ неизменным. А второй​Поиск дублей в Excel​ имена.​Зависимости формул​Выделите пример в разделе​

​временно скрыть диалоговое​ задача и годится​ новых значений в​​ Вас подтвердить, что​В​Ctrl+V​и​ с именами людей​.​​ проведения. Необходимо найти​​ выделения она оставалась​

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

​(чтобы вставить формулу​$B$10000​​ – 5 имён​В поле рядом с​ одну из них,​ активной как показано​ Например, имеется вот​ меняться на адрес​ из самых распространенных​Если в первом​Вычисление формулы​Примечание:​ на листе и​ 50-100 значений. Если​ список будет автоматически​ удалить всю строку​ таблице таких ячеек​ во все выделенные​​это адреса первой​​ в столбце​

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

​В появившемся окне «Создание​Задача все та же​
​Самые быстрые и простые​
​ несколько разных способов.​

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

​вычисляет частоту появления​Нажмите клавиши CTRL+C.​Установите флажок​ см. статью Отбор​А​ только строки с​ встретится намного больше.​ «​ сравнение. Обратите внимание​B​

Задача

​Выделите на листе ячейку​Только уникальные записи​ повторяющихся значений с​имеется список с​ уникальными значениями:​Чтобы снова отобразить все​Duplicate​ на абсолютные ссылки​. Необходимо сравнить имена​

Решение

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

​(Unique), то Excel​​ и возвращает вертикальный​​ A1 и нажмите​
​и нажмите кнопку​
​ помощью фильтра. ​
​ повторяющимися значениями, например​

​Если 2 столбца расположены​​ строки столбца​​«:​​ – буквам столбца​ в этих двух​​ОК​

​ выделим цветом все​ для определения форматированных​ совпадение сразу по​​После функции идет оператор​​ в Excel и​

​ выделит только уникальные​ массив чисел. С​ клавиши CTRL+V.​ОК​Примечание:​ список с названиями​

​ на одном листе​А​В первой ячейке первого​ и номерам строки​

​ столбцах и найти​.​ даты этих дней​ ячеек».​

Тестируем

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

​Чтобы переключиться между просмотром​.​Мы стараемся как​ компаний. В некоторых​

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

​Как видите, Excel выделяет​ЧАСТОТА​ результатов и просмотром​Уникальные значения из выделенного​

​ можно оперативнее обеспечивать​

​ ячейках исходного списка​ (смежные) или не​ в столбце​ листе​ ($). Я использую​ понимаете, это вымышленные​Удаление дубликатов​ Для этого будем​ формулу: 1′ >​ и отчества одновременно.​ с числом 1.​ этот часто задаваемый​ дубликаты (Juliet, Delta),​можно, например, подсчитать​ формул, возвращающих эти​ диапазона будут скопированы​

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

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

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

​ добавить дополнительный служебный​ больше чем одно​ пример.​ (Sierra), четырежды (если​ попадающих в определенные​

​ CTRL+` (знак ударения)​ начиная с ячейки,​ языке. Эта страница​ содержит только те​

​ то процесс удаления​ как воронка с​ это столбец В)​

В этой статье

​ ячеек оставались неизменными​ реальных таблицах мы​

​ не потерять необходимые​ таблице A2:B11 и​

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

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

​ введите такую формулу:​ при копировании формул.​ имеем дело с​ сведения, перед удалением​

​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​ например – зеленый.​ можно скрыть) с​

​ возвращает значение ИСТЕНА​​ заказов, поступающих на​​ Следуйте инструкции ниже,​​ функция возвращает массив,​​Формулы​​Копировать​​ ее текст может​

​ Дополнительное условие: при​​ сложнее. Мы не​​ выберите​

​=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),»»,»Duplicate»)​​Если Вы хотите найти​ тысячами, а то​​ повторяющихся данных рекомендуется​

​ форматирование»-«Создать правило».​​ И нажмите на​​ текстовой функцией СЦЕПИТЬ​ и к текущей​

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

​Select all​​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));»»;»Duplicate»)​​ дубликаты в столбце​​ и с десятками​​ скопировать исходные данные​

​В появившемся окне «Создание​ всех открытых окнах​ (CONCATENATE), чтобы собрать​ ячейке применяется условное​ и e-mail. Может​​ те значения, которые​​ как формулу массива.​

​Зависимости формул​В пустую ячейку под​ грамматические ошибки. Для​​ в исходный список,​​ строку с повторяющимися​(Выделить все). Либо​Здесь​B​ тысяч записей.​ на другой лист.​ правила форматирования» выберите​ кнопку ОК.​​ ФИО в одну​​ форматирование.​

​ сложиться такая ситуация,​

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

​ встречающиеся трижды:​Функция​​нажмите кнопку​​ последней ячейки в​​ нас важно, чтобы​​ новый список должен​​ значениями, поскольку так​​ Вы можете сделать​​Sheet3​​, поменяйте ссылки, чтобы​​Вариант А:​​Выделите диапазон ячеек с​

​ опцию: «Использовать формулу​В результате выделились все​ ячейку:​​Допустим, что у нас​​ что один и​

​Сперва удалите предыдущее правило​​ПОИСКПОЗ​​Показывать формулы​

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

​выполняет поиск указанного​.​СТРОК​​ вам полезна. Просим​​ повторяющиеся значения.​ и из второго​ через Ленту, нажав​​ листа, на котором​​ вид:​ на одном листе.​ нужно удалить.​

​ ячеек».​ в таблице хотя-бы​​ фактически, сводим задачу​​ чего-либо (например, товаров),​ поступил двумя каналами​

Пример

​Выделите диапазон​ элемента в диапазоне​1​. Используйте диапазон​

​Список значений, которые повторяются,​ столбца тоже. Итак,​

​Data​ расположен 2-ой столбец,​

​=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),»Unique»,»Duplicate»)​​ Например, столбец​Совет:​

​В поле ввода введите​

​ к предыдущему способу.​ и мы предполагаем,​ входящей информации. Если​

​A1:C10​ ячеек и возвращает​2​ уникальных значений, которые​ секунд и сообщить,​ создадим в столбце​​ чтобы оставить только​​(Данные) >​​ а​​=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));»Unique»;»Duplicate»)​​A​​Перед попыткой удаления​

Источник

Exceltip

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник