Excel функция повторяющиеся значения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Самсунг вотч 2018 цена в россии

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

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

​ значения и числа.​Можно в условном​Excel.​ Получится так.​

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

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

​Если исходный список содержит:​ форматировании установить белый​​Нам нужно не​​Этот способ подходит, если​​Первый способ.​​Excel​​относится к​​На вкладке​

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

​ Столбец1 и Столбец2,​ удаляются.​группа​ Есть важные различия,​ можно оперативнее обеспечивать​ выделить цветом группы​Поскольку прежде, чем нажать​ выберите вместо​;​ <​ цвет заливки и​ только выделить повторы,​ данные в столбце​Как выделить повторяющиеся значения​,​

​временно скрыть ее.​Главная​ но не Столбец3​Поскольку данные будут удалены​Сортировка и фильтр​ однако: при фильтрации​ вас актуальными справочными​ данных, но из-за​ кнопку​Повторяющиеся​Вызовите Условное форматирование (Главная/ Стили/​11212​ шрифта. Получится так.​ но и вести​ A не меняются.​ в​как выделить одинаковые значения​ Выберите новый диапазон​в группе​ используется для поиска​ окончательно, перед удалением​

​).​ уникальных значений повторяющиеся​​ материалами на вашем​ сложной структуры нельзя​Условное форматирование​(Duplicate) пункт​ Условное форматирование/ Создать​>. Тогда количество значений,​Первые ячейки остались видны,​ их подсчет, написать​ Или, после изменения,​Excel.​

Фильтрация уникальных значений

​ словами, знакамипосчитать количество​

​ ячеек на листе,​стиль​ дубликатов «ключ» —​ повторяющихся значений рекомендуется​

​В поле всплывающего окна​​ значения будут видны​​ языке. Эта страница​​ четко определить и​​(Conditional Formatting), мы​​Уникальные​​ правило/ Использовать формулу​

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

​Нам нужно в​ одинаковых значений​ а затем разверните​

​щелкните маленькую стрелку​​ значение ОБА Столбец1​​ скопировать исходный диапазон​

​Расширенный фильтр​ только временно. Тем​

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

​(Unique), то Excel​​ для определения форматируемых​​ равно 5, т.е.​ не видны. При​

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

​выполните одно из​​ не менее удаление​​ ее текст может​​ какие ячейки выделить.​​A1:C10​

​ выделит только уникальные​ ячеек);​ <1, 1, 2,​

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

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

​ имена.​Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=4​ 1, 2> (значения​ первом столбце меняются​ пишем такую формулу.​Как выделить повторяющиеся ячейки​ слово «Да», если​

​ значений в Excel​

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

​Чтобы отфильтровать диапазон ячеек​​ что вы окончательное​​ грамматические ошибки. Для​​ изображен ниже на​​ формулы в остальные​​Как видите, Excel выделяет​​Выберите нужное форматирование;​

​ в исходном массиве​ и пустые ячейки,​

​ =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по​​ в​​ есть дубль, у​, т.д.​

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

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

​ ячейки. Таким образом,​ дубликаты (Juliet, Delta),​Нажмите ОК​ выделены​ в зависимости от​ столбцу. Получился счетчик​​Excel.​​ ячеек с уникальными​​В Excel можно​​Изменить правило​

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

Читайте также:  Западает кнопка на ноутбуке что делать

​ убедитесь, что активная​​Выберите​​ котором все значения​ вам полезна. Просим​ городам (значения третьего​A2​ (Sierra), четырежды (если​ правило:​​).​​ дубли.​Изменим данные в столбце​

​ столбце D, чтобы​ «Нет».​ и удалять дублирующие​ всплывающее окно​

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

​Введите значения, которые вы​ диапазон.​ ячейка находится в​фильтровать список на месте​ в по крайней​ вас уделить пару​ столбца в алфавитном​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​ есть) и т.д.​Вызовите Условное форматирование (Главная/ Стили/​Если исходный список значений​Пятый способ.​

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

​ А для проверки.​​ автоматически писались слова.​В столбце А​ данные, но и​Изменение правила форматирования​ хотите использовать и​

​.​ мере одна строка​ секунд и сообщить,​ порядке). Необходимо выделить​

​A3​​ Следуйте инструкции ниже,​​ Условное форматирование/ Создать​​ находится в диапазоне​​Как найти повторяющиеся строки​​ Получилось так.​​ Формула такая. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"Да";"Нет")​​ устанавливаем фильтр. Как​​ работать с ними​​.​​ нажмите кнопку Формат.​

​ОК​На вкладке​Чтобы скопировать в другое​

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

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

​А7:А16 ​​ в​​Ещё один способ подсчета​​Копируем формулу по​​ установить и работать​​ – посчитать дубли​​В разделе​​Расширенное форматирование​​, и появится сообщение,​данные​​ место результаты фильтрации:​​ в другую строку.​

​ вам, с помощью​ второй группы данных​

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

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

​ зависит от того,​​ Для удобства также​​ Одна группа строк​​ создали абсолютную ссылку​Сперва удалите предыдущее правило​​Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=3​

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

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

​ –​ условного форматирования.​Выберите другое форматирование;​ помощью формулы =СУММПРОИЗВ(–(СЧЁТЕСЛИ(A7:A16;A7:A16)>1))​ трем столбцам сразу.​ Excel".​​ дублей, выделяет словом​​ ячейке B2 пишем​ строки, состоящие из​ повторяющиеся значения​ таблице или отчете​ количества уникальных значений.​​Работа с данными​​В поле​

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

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

​ значения, рекомендуется установить​ данными​Нажмите на кнопку «Формат»​Перевел: Антон Андронов​ to determine which​

​Главная​ значений можно найти​​ читайте в статье​​ ячейки. Вызываем диалоговое​ столбца E или​ зависит от того,​Есть два варианта​Выберите номер, шрифт, границы​Чтобы добавить условное форматирование,​ итоги. Для получения​​Если диапазон ячеек или​​.​

​ для первой попытке​>​​ и на закладке​​Автор: Антон Андронов​ cells to format).​(Home) нажмите​ здесь: Подсчет неповторяющихся​

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

​ нажмите кнопку​

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

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

Выделение в MS EXCEL дубликатов, встречающихся 3 или 4 раза

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

​ скопирует на новое​ — или применить​.​ цвет. И нажмите​ групп данных безусловно​

  • ​=COUNTIF($A$1:$C$10,A1)=3​>​​ от общего количества​​Копируем формулу по​
  • ​ формулу для определения​ было сразу видно,​В таблице остались две​ вариант, когда выделяются​ если значение в​
  • ​для отображения во​
  • ​ на листе "и"​
  • ​ несколько столбцов только​
Читайте также:  Как найти средний балл оценки в школе

​ место.​ условное форматирование на​

  • ​Чтобы выделить уникальные или​ ОК на всех​ выглядит намного читабельнее,​=СЧЕТЕСЛИ($A$1:$C$10;A1)=3​Правила выделения ячеек​
  • ​ значений отнять число​
  • ​ столбцу. Теперь выделяем​
  • ​ форматируемых ячеек».​

​ есть дубли в​ строки с дублями.​ все ячейки с​

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

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

  1. ​ открытых окнах.​​ чем белые ячейки​​Выберите стиль форматирования и​
  2. ​(Conditional Formatting >​​ повторяющихся, то получим​​ дубли любым способом.​​В строке «Форматировать​​ столбце или нет.​​ В верхней ячейке​​ одинаковыми данными. Например,​ и нажмите кнопку​Создание правила форматирования​​Примечание:​​ кнопку​
  3. ​ на значения в​ добиться таких результатов,​​Условного форматирования​​В результате мы выделили​​ в столбцах с​ нажмите​

​ Highlight Cells Rules)​​ количество неповторяющихся значений.​ Как посчитать в​ формулу для определения​​ Например, создаем список​​ отфильтрованного столбца B​​ как в таблице​​ОК​.​ Условное форматирование полей в​

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

  1. ​ Excel рабочие дни,​ форматируемых ячеек» пишем​
  2. ​ фамилий в столбце​​ пишем слово «Да».​​ (ячейки А5 и​
  3. ​. Вы можете выбрать​​Убедитесь, что выбран соответствующий​​ области "Значения" отчета​​и выберите в разделе​​ таблице — единственный​​ значения.​​стиль​ форматированием и получаем​
  4. ​ Даже элементарное выделение​​.​Повторяющиеся значения​​ выделения повторяющихся значений,​ прибавить к дате​ такую формулу. =СЧЁТЕСЛИ($A:$A;A5)>1​
  5. ​ А. В столбце​

​ Копируем по столбцу.​
​ А8). Второй вариант​

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

  • ​ эффект как изображено​ цветом каждой второй​​Результат: Excel выделил значения,​​(Duplicate Values).​ которые повторяются 3​
  • ​ дни, т.д., смотрите​ Устанавливаем формат, если​
  • ​ B установили формулу.​Возвращаем фильтром все строки​​ – выделяем вторую​​ Форматы, которые можно​ в списке​​ уникальным или повторяющимся​​выберите столбцы.​ вне диапазона ячеек​Выделите диапазон ячеек или​Главная​​ на рисунке:​​ строки существенно облегчает​​ встречающиеся трижды.​​Определите стиль форматирования и​
  • ​ в статье "Как​ нужно выбрать другой​=ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;"+";"-") Если в​​ в таблице. Получилось​​ и следующие ячейки​
  • ​ выбрать, отображаются на​​Показать правила форматирования для​ значениям невозможно.​Примечание:​ или таблице не​ убедитесь, что активная​".​Теперь работать с такой​

    ​ визуальный анализ данных​
    ​Пояснение:​

    ​ нажмите​Выделим с помощью Условного​
    ​ посчитать рабочие дни​
    ​ цвет ячеек или​

    ​ столбце В стоит​

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

    ​ так.​ в одинаковыми данными.​ панели​изменения условного форматирования,​Быстрое форматирование​ Данные будут удалены из​ будет изменить или​ ячейка находится в​Фильтр уникальных значений и​ читабельна таблицей намного​ таблицы. Для реализации​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​ОК​ форматирования только дубликаты,​ в Excel".​

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

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

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

    ​ если вы не​

    1. ​ повторяющихся данных, хранящихся​Нажмите кнопку​ являются две сходные​
    2. ​ проводить визуальный анализ​ Excel применяется универсальный​A1:C10​Результат: Excel выделил повторяющиеся​ или 4 раза​ значений в списке,​ с повторяющимися данными​
    3. ​Третий способ.​ форматированием. Вместо слов,​ только ячейку А8).​Рассмотрим,​ ячеек, нажав кнопку​ ячеек в диапазоне,​

    ​ выбрали всех столбцов​ в первое значение​данные > Дополнительно​ задачи, поскольку цель​ всех показателей.​

    ​ инструмент – условное​, которые равны значению​ имена.​ (см. Файл примера).​ содержащем повторы. Диапазон​

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

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

    Adblock detector