Polytech-soft.com

ПК журнал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

  1. Выделите диапазон A1:C10.
  2. На вкладке Главная (Home) нажмите Условное форматирование >Правила выделения ячеек (Conditional Formatting > Highlight Cells Rules) и выберите Повторяющиеся значения (Duplicate Values).
  3. Определите стиль форматирования и нажмите ОК.Результат: Excel выделил повторяющиеся имена.

Примечание: Если в первом выпадающем списке Вы выберите вместо Повторяющиеся (Duplicate) пункт Уникальные (Unique), то Excel выделит только уникальные имена.

Как видите, Excel выделяет дубликаты (Juliet, Delta), значения, встречающиеся трижды (Sierra), четырежды (если есть) и т.д. Следуйте инструкции ниже, чтобы выделить только те значения, которые встречающиеся трижды:

  1. Сперва удалите предыдущее правило условного форматирования .
  2. Выделите диапазон A1:C10.
  3. На вкладке Главная (Home) выберите команду Условное форматирование >Создать правило (Conditional Formatting > New Rule).
  4. Нажмите на Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format).
  5. Введите следующую формулу:

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

  • Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество значений в диапазоне A1:C10, которые равны значению в ячейке A1.
  • Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует ячейку.
  • Поскольку прежде, чем нажать кнопку Условное форматирование (Conditional Formatting), мы выбрали диапазон A1:C10, Excel автоматически скопирует формулы в остальные ячейки. Таким образом, ячейка A2 содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка A3:

=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.

  • Обратите внимание, что мы создали абсолютную ссылку – $A$1:$C$10.
  • Примечание: Вы можете использовать любую формулу, которая вам нравится. Например, чтобы выделить значения, встречающиеся более 3-х раз, используйте эту формулу:

    Макрос для выделения дубликатов разными цветами

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

    Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
    В этом случае, без макросов не обойтись.

    Ниже приведён макрос, который как раз и решает эту задачу
    (достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)

    • 58635 просмотров

    Комментарии

    Евгений, там много в коде переделывать надо. Это только если под заказ (платно)

    Отличный инструмент, спасибо!
    Подскажите, что нужно изменить в этом макросе, чтоб закрашивались только те ячейки, которые повторяются не меньше 4-х (5-и, . 8-и) раз? Готов каждый раз лазить в макрос и менять на нужное кол-во, только подскажите что и где? (я не спец по макросам, к сожалению).

    Добрый день .Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99

    4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
    А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в каторых совпадают 4 цифры и более?
    За раннее спасибо!

    Александр, тут макрос нужно писать.
    Можем сделать под заказ

    Добрый день . Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. Но некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99

    4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
    А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в которых совпадают 4 цифры и более?
    За раннее спасибо!

    а где макрос? не могу скачать

    Всё можно
    Но это другом макрос нужен. Можем сделать под заказ

    Подскажите, а можно ли сделать? Есть 2 столбца (первый составлен из второго с удалением дублей), мне нужно найти все дубли во втором и только то что дублируется окрасить в цвет и в первом и и во втором в один цвет?

    Спасибо, супер макрос, очень помог

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Поиск и удаление дубликатов в Excel: 5 методов

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

    В данной статье мы рассмотрим методы поиска и удаления дублирующихся данных (дубликатов), в частности, строк в 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: формула для удаления повторяющихся строк

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

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

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

    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 предлагает несколько инструментов для нахождения и удаления строк или ячеек с одинаковыми данными. Каждый из описанных методов специфичен и имеет свои ограничения. К универсальным варианту мы, пожалуй, отнесем использование “умной таблицы” и функции “Удалить дубликаты”. В целом, для выполнения поставленной задачи необходимо руководствоваться как особенностями структуры таблицы, так и преследуемыми целями и видением конечного результата.

    Читать еще:  Как сделать доллар в excel
    Ссылка на основную публикацию
    ВсеИнструменты 220 Вольт
    Adblock
    detector