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

Выделить повторяющиеся значения в excel

​господа, будьте бдительны​Hugo​ счета у которых​ сравнения количества найденных​ значения в столбце​​ Способу 2. А​​ список (в нашем​ выбрали диапазон​(Duplicate Values).​– идеально подходит,​Когда завершите с настройками,​H​(Дополнительно). Этим инструментом​

• ​Если нажать одну из​
• ​ если Вы вооружены​

​ В Excel не поддерживается​ значений в списке.​ при работе с​

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

​: Нда, что-то у​ тет дублеров. ​ значений в диапазоне​ Excel.​ именно:​ примере — диапазон​A1:C10​Определите стиль форматирования и​ когда в данных​ нажмите​, а остальные скроет.​

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

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

​ с числом 1.​​Пример дневного журнала заказов​​в Excel 2003 и​​ А2:A10), и идем​​, Excel автоматически скопирует​​ нажмите​​ присутствуют несколько категорий,​​ОК​​ Таким образом, мы​

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

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

​ на товары:​​ старше — выбрать​​ в меню​ формулы в остальные​ОК​ которые, возможно, Вам​. Excel покажет информационное​ нашли дубликаты данных​ немного настроить, прежде​

​ которое содержит всю​ способов справиться с​ отчета сводной таблицы.​

​ в определенном диапазоне.​​ я погарячился с​Я всёж больше​Выкладываю рабочий файл​ больше чем одно​Чтобы проверить содержит ли​

​ в меню​​Формат — Условное форматирование​​ ячейки. Таким образом,​​.​​ понадобится разделить, просуммировать​​ окно с результатом​​ (по одному столбцу),​ чем использовать. Ваши​ информацию по данному​ ней. Когда я​

​На вкладке​ Выделять повторяющиеся значения​ выводами?​ макросам доверяю.​IgorGo, спасибо за​

​ значение, значит формула​​ журнал заказов возможные​​Формат — Условное форматирование​​(Format — Conditional Formatting)​​ ячейка​

​Результат: Excel выделил повторяющиеся​​ или удалить. Создание​​ работы функции (пример​

3 способа как находить дубликаты в Excel

​ показав только домашние​ данные должны быть​ столбцу. Выберите любой​ впервые задумался об​Главная​ будем с помощью​IgorGo​Если нужно выяснить​ помощь, Ваш файл​ возвращает значение ИСТЕНА​ дубликаты, будем анализировать​ — Формула (Format​.​A2​ имена.​ подразделов – самое​ на рисунке ниже),​ встречи:​ организованы так, как​ элемент из этого​

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

• ​ достаточно быстро придумал​Условное форматирование​
• ​ Файл примера).​ формул СЧЕТЕСЛИ -​
• ​ этим универсальным файлом​ посмотреть, сейчас буду​

Поиск дубликатов при помощи встроенных фильтров Excel

​ ячейке применяется условное​ – столбец B:​ — Formula)​ списка вариант условия​A3​Если в первом​ расширенного фильтра.​ нужно нажать​ для нахождения дубликатов,​ т.е. как база​ отобразит данные в​ пару способов как​>​выделите диапазон содержащий список​ все корректно закрашивается.​ их выбрать:​​alexsampler​​ форматирование.​Выделите диапазон B2:B9 и​​в Excel 2007 и​​Формула (Formula)​:​ выпадающем списке Вы​Удаление дубликатов​ОК​ который может помочь​

​ данных.​ соответствии с Вашим​ найти дубликаты, а​Правила выделения ячеек​ значений, например,​_Boroda_​http://www.planetaexcel.ru/docs/forum_upload/post_289049.xls​: Я уже усомнился​alexsampler​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​ новее — нажать​и вводим такую​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​ выберите вместо​уменьшит объём данных​. Excel автоматически ликвидирует​​ сохранить время и​​Перед тем как использовать​ выбором. Это быстрый​ немного поразмыслив, обнаружил​>​А3:А16​: У меня оказывается​Там всего один​ в своей и​: Добрый день!​ форматирование»-«Создать правило».​

​ на вкладке​ проверку:​Обратите внимание, что мы​Повторяющиеся​ до минимума. Я​ строки с дублирующимися​ получить необходимую информацию​ расширенный фильтр, Вы​ способ подвести итог​ ещё несколько способов.​Повторяющиеся значения​;​​ пример не приложился.​​ повтор (если не​​ без того скудной​​Excel 2003​

​Вберете «Использовать формулу для​Главная (Home)​=СЧЁТЕСЛИ($A:$A;A2)>1​ создали абсолютную ссылку​(Duplicate) пункт​ пользуюсь этим способом,​ значениями, оставив Вам​ достаточно быстро. Нужно​ должны настроить для​ или увидеть объём​ Итак, давайте для​.​вызовите Условное форматирование (Главная/​ А я и​

Расширенный фильтр для поиска дубликатов в Excel

​ считать 0.00 -​​ сообразительности, но ваш​​Есть такая задача​ определения форматируемых ячеек».​​кнопку​​в английском Excel это​ –​Уникальные​​ когда мне нужно​​ только уникальные значения​ помнить, что критерий​ него критерий. Посмотрите​ выбранных данных. Вы​ начала рассмотрим пару​В поле рядом с​ Стили/ Условное форматирование/​ не проверил.​ 16)​ файл у меня​

​ – найти повторяющиеся​Чтобы найти повторяющиеся значения​Условное форматирование — Создать​ будет соответственно =COUNTIF($A:$A;A2)>1​$A$1:$C$10​(Unique), то Excel​ сделать список всех​ в столбцах, которые​ должен быть размещён​​ на рисунок ниже,​​ можете убрать галочку​ простых, а затем​ оператором​ Правила выделения ячеек/​Ну да ладно,​IgorGo​ не работает.​ значения 20-тизначных счетов​ в столбце Excel,​ правило (Conditional Formatting​Эта простая функция ищет​.​ выделит только уникальные​ уникальных значений одного​ Вы выбрали. Кстати,​

​ в ячейке отдельно​ на нем виден​ с пункта​ перейдём к более​​значения с​​ Повторяющиеся значения…);​ сейчас новый подход​:​Скопировал один счет​ в столбце и​

​ в поле ввода​ — New Rule)​ сколько раз содержимое​Примечание:​ имена.​ из столбцов, которые​​ этот инструмент присутствует​​ от списка данных,​ список с данными,​​Select All​​ сложным способам.​​выберите форматирование для​​нажмите ОК.​ придумался:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>АВ​​ и повторил вместо​​ выделить все повторяющиеся​ введите формулу: =СЧЁТЕСЛИ($B$2:$B$9;​и выбрать тип​​ текущей ячейки встречается​​Вы можете использовать​​Как видите, Excel выделяет​​ в дальнейшем использую​ в Excel 2007​ чтобы Вы могли​ а справа в​(Выделить все), а​Первый шаг – Вам​

​ применения к повторяющимся​Усложним задачу. Теперь будем​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(—(F$1:F$56&»a»=F1&»a»))>1​130114810100000000039​ других — ни​ красным цветом.​ B2)>1.​ правила​ в столбце А.​ любую формулу, которая​ дубликаты (Juliet, Delta),​ для вертикального поиска​ и более новых​ найти его и​ столбце​ затем выбрать один​ необходимо привести данные​ значениям и нажмите​ выделять дубликаты только​IgorGo​​230114810100000000042​​ каких движений.​​И еще вопрос​​Нажмите на кнопку «Формат»​​Использовать формулу для опеределения​​ Если это количество​

Встроенный инструмент для удаления дубликатов в Excel

​ вам нравится. Например,​ значения, встречающиеся трижды​​ с помощью функции​​ версиях.​ использовать. Вы можете​L​ или несколько нужных​ к такому формату,​ кнопку​ если установлен Флажок​: сравнение происходит нормально​​3​​При открытии файла​ – можно ли​ и выберите желаемую​ форматируемых ячеек (Use​​ повторений больше 1,​​ чтобы выделить значения,​

​ (Sierra), четырежды (если​ ВПР.​Если Вам нужно найти​ изменить фильтр, изменив​указан критерий. Я​ элементов. Excel покажет​ который позволяет легко​​ОК​​ «Выделить дубликаты» (ячейка​​ и без присоединения​​4ЛОЖЬ=B2=B1​ выскакивает ошибка о​ выделять разные пары,​ заливку ячеек, чтобы​ a formula to​

​ т.е. у элемента​ встречающиеся более 3-х​​ есть) и т.д.​​Я пользуюсь командой​ в Excel небольшое​ критерий (у меня​ записал заголовок столбца​ только те строки,​ оперировать ими и​​.​​B1​ «а»​51=сумм(—(B1:B2=B2))​ циклической ссылке​ разными цветами.​ выделить дубликаты цветом.​ determine which cell​ есть дубликаты, то​ раз, используйте эту​ Следуйте инструкции ниже,​

Поиск дубликатов при помощи команды Найти

​Find​ количество дублирующихся значений,​ он находится в​ и критерий под​ которые содержат выбранные​ изменять их. Создание​При использовании функции​​)​​это =СУММ(—(F$1:F$56=F2))>1 дает​​62=СЧЁТЕСЛИ(B1:B2;B2)​​Подскажите где туплю​И как сделать,​ Например, зеленый. И​ to format)​ срабатывает заливка ячейки.​ формулу:​ чтобы выделить только​(Найти) только если​ Вы можете сделать​ ячейке L2). Кроме​

​ одним заголовком. На​ Вами пункты. Так​ заголовков в верхней​Удаление дубликатов​выделите диапазон содержащий список​ тот же результат​при работе с​alexsampler​ что бы в​ нажмите ОК на​Затем ввести формулу проверки​ Для выбора цвета​=COUNTIF($A$1:$C$10,A1)>3​ те значения, которые​

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

В заключение

​ значений, например,​Татьяна​ данной задачей выяснилось. ​: в моем примере​

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

​ рамкой. Они окрасились​ OK!​ значений в столбце.​ помощью кнопки​нажмите кнопку​ командой сайта office-guru.ru​ условного форматирования.​Find and Replace​Hom​

​Clear​ только домашние встречи.​
​После настройки фильтра Вы​
​ организовать данные в​

​ сведения, перед удалением​

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

​вызовите Условное форматирование (Главная/​ ввести, чтобы получить​ имет формат текстовый.​ в красное, но​На всякий случай​Как видно на рисунке​Формат (Format)​

1. ​Формат. (Format)​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​​Выделите диапазон​
2. ​(Найти и заменить),​​e (Главная) и кликните​​(Очистить) на вкладке​​ Именно поэтому я​​ можете удалить дубликаты​​ виде списка. Одним​​ повторяющихся данных рекомендуется​ Стили/ Условное форматирование/​ количество повторяющихся значений?​​ тут две ячейки​​ в столбце они​
3. ​ выкладываю пример​ с условным форматированием​​- все, как​​и перейдите на​​Перевел: Антон Андронов​A1:C10​

​ когда нахожу ошибки​​Find & Select​Data​ скопировал заголовок столбца,​​ строк, подвести промежуточные​​ словом, данные превращаются​​ скопировать исходные данные​​ Создать правило/ Использовать​ Например, в столбце​ с данными и​

​ уникальны (не повторяются)​Спасибо.​ нам удалось легко​ в Способе 2:​ вкладку​Автор: Антон Андронов​.​ и хочу разом​(Найти и выделить).​

1. ​(Данные) в группе​ в котором хочу​
2. ​ итоги или дополнительно​​ в базу, которую​​ на другой лист.​
3. ​ формулу для определения​​ цифры: 7,6,5,4,3,2,1,0-нужно количество​​ три с формулами.​​Но на части​​Serge_007​​ и быстро реализовать​​Поиск дублей в Excel​Вид (Pattern)​
4. ​Допустим, что у нас​​На вкладке​ исправить их.​​ Откроется диалоговое окно,​Sort & Filter​ выполнить фильтрацию, а​
5. ​ отфильтровать данные по​

​ можно сортировать и​
​Выделите диапазон ячеек с​

• ​.​ имеется длинный список​​Главная​​Это далеко не исчерпывающий​ в котором можно​
• ​(Сортировка и фильтр).​ ниже поместил критерий​
• ​ другому столбцу. Вы​ выполнять с ней​​ повторяющимися значениями, который​​введите формулу =И(СЧЁТЕСЛИ($B$3:$B$16;$B3)>1;$B$1)​Заранее Благодарю за​​ 5, 6 в​​ отработало правильно​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИ($F$1:$F$56;F1)>1​ Excel и обнаружить​ из самых распространенных​​Усложним задачу. Допустим, нам​​ чего-либо (например, товаров),​​(Home) выберите команду​​ список методов поиска​

​ ввести любое значение​

​Обратите внимание, что в​​ помощь!​ колонке В приведены​IgorGo​IgorGo​ повторяющиеся данные ячеек​ задач для любого​ нужно искать и​

​ и мы предполагаем,​
​Условное форматирование​

​ дубликатов в Excel.​ для поиска в​
​ функция​
​ использовать.​

Выделение дубликатов цветом

​Поиск дубликатов при помощи​Совет:​ формуле использована относительная​Pelena​ тексты формул, в​: ошибки нет. там​: мудрое решение​ для таблицы журнала​ офисного сотрудника. Для​ подсвечивать повторы не​ что некоторые элементы​>​

Способ 1. Если у вас Excel 2007 или новее

​ Способов много, и​ Вашей таблице. Чтобы​Remove Duplicates​Теперь, когда критерий настроен,​

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

​Создать правило​ это лишь некоторые​ избежать опечаток, Вы​(Удалить дубликаты). Вы​

Способ 2. Если у вас Excel 2003 и старше

​ выделяем любую ячейку​ На примере ниже​Встроенный инструмент для удаления​ повторений удалите все​ ячейкой при вводе​Татьяна​ вычислений этих формул.​​лекарство такое:​ ​(alexsampler)200?’200px’:»+(this.scrollHeight+5)+’px’);»>И еще вопрос​​​​ несколько разных способов.​ а по нескольким.​​ более 1 раза.​​(Conditional Formatting >​ из них, которыми​

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

​ можете выбрать столбец​ наших данных и​

​ у меня выбраны​ дубликатов в Excel​ структуры и промежуточные​ формулы должна быть​, прочтите Правила форума​Результаты в А4,​большая круглая кнопка​ – можно ли​Принцип действия формулы для​ Но как быстро​ Например, имеется вот​​ Хотелось бы видеть​​ New Rule).​​ я пользуюсь регулярно​​ прямо из списка​ с данными и​​ нажимаем команду​​ элементы​

Способ 3. Если много столбцов

​Поиск дубликатов при помощи​ итоги из своих​B3​jakim​ А5 не вызывают​параметры ексель​ выделять разные пары,​ поиска дубликатов условным​ как найти дубликаты​

​ такая таблица с​ эти повторы явно,​Нажмите на​ в своей повседневной​ данных.​ при помощи этой​Advanced​

​XP​ команды Найти​ данных.​(т.е. диапазон нужно​: Предлагаю такую формулу​ сомнений. А вот​формулы​ разными цветами.​

​ форматированием – прост.​ в Excel и​ ФИО в трех​ т.е. подсветить дублирующие​Использовать формулу для определения​ работе.​В случае, когда объём​ команды удалить все​(Дополнительно). Excel выберет​и​Организовав данные в виде​

• ​На вкладке​ выделять сверху вниз).​ УФ​​ в А6 СЧЕТЕСЛИ​в группе параметры​можно. макросом.​ Формула содержит функцию​
• ​ выделить их цветом?​ колонках:​ ячейки цветом, например​​ форматируемых ячеек​​Урок подготовлен для Вас​​ информации очень велик​ дубликаты, оставив только​ весь список с​​XP Pro​ списка, Вы можете​​Данные​ Активная ячейка в​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=AND(F10<>0;COUNTIF(F10:F65;F10)>1;F10<>»0.00″;TYPE(F10)=1)​ вернул 2, т.е.​ вычислений​

​условное форматирование только​ =СЧЁТЕСЛИ(). Эту функцию​ Для ответа на​Задача все та же​​ так: ​​(Use a formula​ командой сайта office-guru.ru​

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

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

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

​ to determine which​Источник: https://www.ablebits.com/office-addins-blog/2011/06/07/find-duplicates-excel/​ работу поиска, выделите​ инструментом​ вот такое диалоговое​В результате работы фильтра,​ различные фильтры. В​Удалить дубликаты​ белая и ее​ формат счетов на​ в диапазоне В1:В2​ — птица​ (см.вложение)​ использовать при поиске​ вопрос рассмотрим конкретный​ ФИО, имея ввиду​

​ начиная с 2007​ cells to format).​Перевел: Антон Андронов​ строку или столбец,​Remove Duplicates​ окно:​

​ Excel отображает только​ зависимости от набора​

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

1. ​Quote​ одинаковых значений в​ пример.​
2. ​ совпадение сразу по​ года функция подсветки​
3. ​Введите следующую формулу:​Автор: Антон Андронов​ в котором нужно​(Удалить дубликаты) можно​Как видите, Excel выделил​
4. ​ те строки, в​ данных, который у​Столбцы​ поле Имя.​Pelena​ как в В2.​ — 1​

​(alexsampler)200?’200px’:»+(this.scrollHeight+5)+’px’);»>И как сделать,​ диапазоне ячеек. В​

​Допустим мы занимаемся регистрацией​ всем трем столбцам​ дубликатов является стандартной.​=COUNTIF($A$1:$C$10,A1)=3​Этот пример научит вас​ искать, и только​ при помощи одноименной​ всю таблицу и​ которых содержатся выбранные​

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

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

​ фирму через факс​ и отчества одновременно.​ данными и на​

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

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

​: Не сочтите меня​​ данных выскакивало сообщение​
​ данных. Во втором​
​ и e-mail. Может​Самым простым решением будет​ вкладке​ нажмите​ условного форматирования. Перейдите​ не сделать, Excel​
​Data​ критерием. Выберите в​ компьютере установлены XP​ нескольким столбцам. Поскольку​
​ удалить повторения.​ выделение повторяющихся значений​ дату топика​ эксель сравнивает текстовое​ идиотом, но после​
​ OK!​ аргументе мы указываем​
​ сложиться такая ситуация,​

​ добавить дополнительный служебный​​Главная (Home)​
​ОК​

​ по этой ссылке,​​ будет искать по​
​(Данные).​​ диалоговом окне поле​ и XP Pro).​ я использую Office​Например, на данном листе​
​ исчезнет.​
​RAN​ значение «30114810100000000042» (именно​ всех исправлений -​
​и снова макросом.​​ что мы ищем.​ что один и​ столбец (его потом​жмем кнопку​.​
​ чтобы узнать, как​ всем имеющимся данным​Не забудьте выбрать, в​Criteria Range​ Можно выбрать любую​ 2010, то мне​

​ в столбце «Январь»​​В некоторых случаях повторяющиеся​
​: Контора пишет.​
​ текст, а не​ изменений ни каких. ​ или. добавил строку​ Первый аргумент у​ тот же заказ​ можно скрыть) с​Условное форматирование (Conditional Formatting)​Результат: Excel выделил значения,​
​ удалить дубликаты.​ и находить не​
​ каком столбце необходимо​
​(Диапазон условий), затем​ другую комбинацию данных,​ достаточно выделить верхнюю​ содержатся сведения о​

​ данные могут быть​​pool​ число 30114810100000000042) с​Подскажите, что по​ над списком, если​ нас имеет абсолютные​ поступил двумя каналами​
​ текстовой функцией СЦЕПИТЬ​, затем выбираем​ встречающиеся трижды.​Выделите диапазон​
​ нужные результаты.​ оставить только уникальные​ выделите мышью ячейки​
​ а если нужно,​

​ строку, в которой​​ ценах, которые нужно​ полезны, но иногда​: Подскажите пожалуйста, есть​ диапазоном В1:В2 и​ вашему должно произойти.​ повторов нет, то​
​ ссылки, так как​ входящей информации. Если​ (CONCATENATE), чтобы собрать​

​Правила выделения ячеек -​​Пояснение:​A1:C10​
​Если нужно выполнить поиск​
​ значения. Если данные​
​L1​
​ то даже настроить​
​ находятся заголовки, затем​ сохранить.​
​ они усложняют понимание​ БД с номерами​
​ возвращает результат 2. ​Я сделал несколько​
​ почти так и​

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

​ и тот же​​ ячейку:​ Cell Rules -​ значений в диапазоне​
​На вкладке​ данным, возможно, кнопка​
​ то в диалоговом​L2​ нескольких столбцах.​Data​
​Январь​
​ форматирование для поиска​ чтобы при добавлении​ пожалуйста, у себя.​ перемен не происходит​

​alexsampler​​ аргумент наоборот, должен​ ​ заказ, могут возникнуть​
​Имея такой столбец мы,​
​ Duplicate Values)​
​A1:C10​
​Главная​
​Find All​
​ окне будут показаны​
​(либо те, в​На вкладке​
​(Данные) и нажать​в поле​ и выделения повторяющихся​ нового, если он​вывод у меня​На всякий случай​: ALARM. ​ меняться на адрес​ определенные проблемы для​ фактически, сводим задачу​:​, которые равны значению​
​(Home) нажмите​(Найти все) окажется​Column A​ которых находится Ваш​Data​ команду​Удаление дубликатов​ данных. Это позволит​ уже существует, баз​ один: в данной​
​ повторюсь — мне​Serge_007​ каждой ячейки просматриваемого​ фирмы. Ниже рассмотрим​ к предыдущему способу.​В появившемся затем окне​ в ячейке A1.​Условное форматирование​
​ для Вас более​,​
​ критерий) и нажмите​(Данные) справа от​Filter​нужно снять.​ вам просматривать повторения​ реагировала, ну как-то​ ситуации эксель решил​ нужно чтобы в​
​Помогите пожалуйста, на​ диапазона, потому имеет​ решение средствами условного​ Для выделения совпадающих​ можно задать желаемое​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​

​>​​ полезной.​Column B​ОК​

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

​ форматирования.​​ ФИО теперь можно​ форматирование (заливку, цвет​ ячейку.​
​Правила выделения ячеек​Все три метода просты​

​(столбец A, столбец​​. Таблица отобразит только​Filter​ из заголовков появятся​ОК​ по мере необходимости.​ существующий номер?​ числа, а за​
​ столбце F появляются​ форматирование сработало, а​

​Самые быстрые и простые​​Чтобы избежать дублированных заказов,​​ выделить все три​​ шрифта и т.д.)​

​Поскольку прежде, чем нажать​​(Conditional Formatting >​ в использовании и​
​ B) и так​
​ те строки, где​(Фильтр) есть кнопка​ направленные вниз треугольные​

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

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

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

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

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

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

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

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

1. Выделите диапазон ячеек A2:C19 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся диалоговом окне выделите опцию: «Использовать формулу для определения форматируемых ячеек», а в поле ввода введите следующую формулу: $C3:$C20)+0);2)’ >
3. Нажмите на кнопку «Формат» и на закладке заливка укажите зеленый цвет. И нажмите ОК на всех открытых окнах.

В результате мы выделили целые строки условным форматированием и получаем эффект как изображено на рисунке:

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

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

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

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

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

В этом случае, без макросов не обойтись. Нажимаем сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставляем новый пустой модуль через меню Insert — Module и копируем туда код этого макроса:

Sub ВыделитьДубликатыРазнымиЦветами()

On Error Resume Next

‘ массив цветов, используемых для заливки ячеек-дубликатов

Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _

9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213)

Dim coll As New Collection, dupes As New Collection, _

cols As New Collection, ra As Range, cell As Range, n&

Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange)

If Err Then Exit Sub

ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False

For Each cell In ra.Cells ‘ запонимаем значение дубликатов в коллекции dupes

Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value)

If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value)

Next cell

For i& = 1 To dupes.Count ‘ заполняем коллекцию cols цветами для разных дубликатов

n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1

Next

For Each cell In ra.Cells ‘ окрашиваем ячейки, если для её значения назначен цвет

cell.Interior.color = cols(CStr(cell.Value))

Next cell

Application.ScreenUpdating = True

End Sub

Теперь можно выделить любой диапазон с данными на листе и запустить макрос с помощью сочетания клавиш Alt+F8 или через кнопку Макросы (Macros) на вкладке Разработчик (Developer).

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

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

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

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

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

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Круто. Вот VBA все таки понятный язык. Меня тут подрядили для опенофис макросы написать, я уже запарился пока редактор макросов нашел. А в экселе все просто и понятно. Красота.

Спасибо тебе огромное!

Хорошо то как.
Цветов бы побольше, а то повторяются быстро.

а для google exel такого макроса нет?

Раз уж тут интересные фишки в Excel рассказывают новичкам, хочу спросить.

Есть файлы xml, из элементов которых нужно вытащить информацию и составить из них таблицу.

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

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

Импорт XML тоже не подходит, т.к. с ним удобно работать, если файл один.

Вопрос такой от новичка: какие порекомендовали бы ресурсы для изучения макросов на данном направлении, в частности, по преобразованию xml документов в DomDocument и последующего извлечения информации оттуда?

Читать ещё на Пикабу

EXCEL — ЭТИ СТРАШНЫЕ МАКРОСЫ – НАЧАЛО⁠ ⁠

Я решил с двух ног ворваться в тему макросов.

Кто-то про них слышал, кто-то даже видел, отдельные сверхразумы их даже использовали. Сегодня будет ознакомительный пост: что это вообще такое и как с этим начать работать. Обратите внимание – этот пост тех, кто не знает, что такое макросы и никогда с ними не работал

Первым делом нужно включить вкладку «Разработчик». По умолчанию в Excel ее спрятали, чтобы не взорвать мозг юзерам. Идем в Параметры -> Настройка ленты -> Основные вкладки -> Разработчик (поставить галочку).

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

Давайте теперь что-то сделаем. На пример поменяем заливку ячейки А1, в ячейку A2 напишем значение «Мама, я программист», а в ячейке А3 пропишем формулу текущей даты «=Сегодня()»

Останавливаем запись макроса. Нажимаем иконку «Макросы», выбираем наш макрос как мы его обозвали, нажимаем кнопку «изменить».

Появляется окно Microsoft Visual Basic for Applications. Кстати оно также вызывается комбинацией клавиш (Alt + F11) У меня почему-то вызывается только левым Altом, а правым нет, видимо намекая на то что для написания макросов лучше иметь 2 руки (хотя я и одной нажать могу). Появился редактор языка VBA – это язык, который написан специально под офис чтобы на нем писать макросы. В основном окне видим саму эту запись, которую автоматически сделал Excel.

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(«A2»).Select
ActiveCell.FormulaR1C1 = «Мама, я программист»
Range(«A3»).Select
ActiveCell.FormulaR1C1 = «=TODAY()»
Range(«A4»).Select
End Sub

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

Теперь давайте разбираться что делает этот макрос

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

(Весь этот кусок от начала говорит нам о том, что с тем элементом что был выделен ранее происходит некоторое дерьмо, в том числе изменение цвета. Вот там, где Color = 255. Все остальное это параметры заливки, которые по итогу не менялись, но макрорекордер решил их тоже записать, на всякий. Это связано с внутренними особенностями работы excel как я понял. Вообще привыкайте к тому что макрорекордер пишет много того что потом вообще можно удалить. Конструкция With – End With позволяет делать несколько действий с одним объектом, на пример выше берется объект Selection.Interior, то есть фон выбранной области и ряду параметров этой заливки назначаются конкретные значения. То есть With нужен для облегчения записи кода, чтобы Selection.Interior не писать вначале каждой строчки.

Range(«A2»).Select –выделяем ячейку «A2»
ActiveCell.FormulaR1C1 = «Мама, я программист» – пишем в ячейку значение
Range(«A3»).Select – выделяем ячейку «А3»
ActiveCell.FormulaR1C1 = «=TODAY()» –пишем в ячейку формулу
Range(«A4»).Select – зачем то выделяем ячейку А4.
End Sub

Теперь при создании нового листа и запуске этого макроса он будет делать все то же самое.

Тут стоит понимать, что половину того что записал макрос можно опустить, так как нам важен результат, а не путь по которому к этому результату пришли, а макрорекордер записывает именно путь. На пример вместо всей конструкции With можно записать

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

Range(“A2”).Value = ”Мама, я программист”

или писать формулу как в третей ячейке

С формулами и значениями лично мне не понятно, как excel их интерпретирует, но в макрорекордре он записывает любой ввод в ячейку как ввод формулы. Благо лично у меня при написании макросов не возникает необходимости писать формулы в ячейки. На пример вместо вставки формулы как это было выше можно написать Range(“A3”).Value = Date(), тогда макрос вставит сразу текущую дату в ячейку как значение.

Опытные макроделы пишут макросы сразу без их записи макрорекордером, но это полезный инструмент для самостоятельного изучения при написании макросов: если не знаешь, что как делается в VBА то запускаешь и делаешь, потом смотришь что он там написал.

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

Sub Colorization()
‘начало нашего макроса и его название
Dim x As Integer
‘объявляем переменную х типа интеджер, это тип для целых чисел от -32 768 до 32 767 (2 байта),
‘она нам нужна для перебора ячеек
For x = 1 To ActiveSheet.UsedRange.Rows.Count
‘перебираем х от 1 до конца использованной части листа, то есть не весь лист, а там где есть данные.
‘Тут цикл For повторяется от этой строки до строки Next x, которая прописана ниже
If Cells(x, 1).Value = «красный» Then Cells(x, 1).Interior.Color = RGB(255, 0, 0)
‘если значение в ячейке равно «красный» то закрашиваем ячейку в красный цвет. Функция If выполняет часть
‘после Then если условие между If и Then верно. Так как у нас необходимое действие занимает одну
‘строку можно писать в таком виде, если же действий несколько применяется конструкция:
‘If … Then
‘…
‘…
‘End If
If Cells(x, 1).Value = «зеленый» Then Cells(x, 1).Interior.Color = RGB(0, 255, 0)
‘как выше только в зеленый цвет
If Cells(x, 1).Value = «синий» Then Cells(x, 1).Interior.Color = RGB(0, 0, 255)
‘в синий цвет
Next x ‘берем следующее значение х, конец цикла For, который мы начали выше
End Sub ‘конец макроса
Как работает этот макрос: берет первый столбец, сначала 1 ячейку, смотрит что в ней написано, и если это равно «красный», «зеленый» или «синий», то красит фон ячейки в этот цвет, если нет по пропускает. Потом берет вторую и т. д. до конца активной части текущего листа.
Для проверки работы макроса нам нужен лист, где в первом столбце будут случайным образом прописаны цвета «красный», «зеленый», «синий». Запускаем макрос – когда он отработает ячейки будут раскрашены:

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

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

Итак, на этом пока все. Надеюсь теперь те, кто никогда не видел макросов получат о них начальное представление. Дальше буду писать про более практичное применение.