Как найти, посчитать и убрать повторяющиеся значения в Эксель

Найти и выделить дубликаты в документе можно, используя условное форматирование в Эксель. Выделите весь диапазон данных в нужной таблице. На вкладке «Главная» кликните на кнопочку «Условное форматирование» , выберите из меню «Правила выделения ячеек» – «Повторяющиеся значения» .

Повторяющиеся значения в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Свежие статьи

В Excel существуют и другие средства для работы с дублированными значениями. Например:

«Данные»-«Удалить дубликаты» — Инструмент удаляет повторяющиеся строки на листе Excel.
«Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только уникальные записи». Инструмент скрывает повторяющиеся строки листа.
«Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Повторяющиеся значения». Инструмент подсвечивает дублирующие данные цветом.
Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет одинаковые значения чисел количества в столбце B. Формулу следует протянуть маркером (скопировать) вдоль всех значений. Так же следует проконтролировать, чтобы был одинаковый формат отображения всех исходных чисел в столбце A.
Формула в массиве: 1;0;1);0));»»)’ Формула ищет одинаковые наименования в диапазоне A2:A13 и выводит их в отдельный список столбца B (формулу нужно скопировать в диапазон B2:B13)

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

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

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

— Простите, вы не видели тут моего близнеца?
— Вы уже спрашивали.
Допустим, что у нас имеется длинный список чего-либо и мы предполагаем, что некоторые элементы этого списка повторяются более 1 раза. Хотелось бы видеть эти повторы явно, т.е. подсветить дубликаты цветом. Сделать это в Excel можно несколькими разными способами.

Способ 1. Повторяющиеся ячейки

Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек — Повторяющиеся значения (Highlight Cell Rules — Duplicate Values):

В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т.д.)

Способ 2. Выделение всей строки

Если хочется выделить цветом не одиночные ячейки, а сразу строки целиком, то придется создавать правило условного форматирования с формулой. Для этого выделяем все данные в таблице и выбираем Главная — Условное форматирование — Создать правило — Использовать формулу для выделения форматируемых ячеек (Home — Conditional formatting — Create rule — Use a formula to determine which cells to format)

, а затем вводим формулу:

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

  • $A$2:$A$20 — столбец в данных, в котором мы проверяем уникальность
  • $A2 — ссылка на первую ячейку столбца

Способ 3. Нет ключевого столбца

Усложним задачу. Допустим, нам нужно искать и подсвечивать повторы не по одному столбцу, а по нескольким. Например, имеется вот такая таблица с ФИО в трех колонках:

Исходные данные

Задача все та же — подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам — имени, фамилии и отчества одновременно.

Самым простым решением будет, конечно, добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), чтобы собрать ФИО в одну ячейку:

Сцепка ФИО

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

Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:

Формула массива в условном форматировании

Ссылки по теме

  • Сравнение двух диапазонов данных, поиск различий и совпадений
  • Извлечение уникальных элементов из диапазона

А как быть, если идентичные дубликаты нужно сгруппировать? То есть Медведевых выделить одним цветом, а Ермаковых другим? Этот пример хорош тем, что он небольшой и всё видно наглядно, а если в таблице более тысячи строк, то на глаз прикинуть несколько десятков идентичных дубликатов и выделить нужные не так-то просто. Есть ли какое-то решение? Ссылка

Я бы отфильтровал дубликаты по цвету, а потом отсортировал — получите на экране только повторяющиеся элементы сгруппированные «по одинаковости». Родитель Ссылка

Николай, нашёл дубликаты по способу номер 1 (Excel 2007) из двух колонок. Так получилось, что правая колонка состоит из 2000 ячеек, и мне нужно было найти эти 2000 дубликатов в левой колонке состоящей из 27000 ячеек. Нашёл, обозначил цветом. Теперь нажимаю на фильтр над колонкой из 27000 ячеек (чтобы собрать воедино цветные ячейки) и у меня даже не вылазиет контекстное меню для выбора критериев фильтрации — Excel начинает в прямом смысле слова тупить, пока не нажмёшь клавишу . Вот такая проблема. Родитель Ссылка

Нашёл ответ сам. Нужно было всего лишь подождать, поскольку столбец длиный он долго собирал в контекстное меню параметры фиольтрации. Спасибо. Всё работает. Родитель Ссылка

Та же самая проблема, что у вас на маке. На винде все нормально работает, сразу открывается. Хотя, мак мощнее. В чем может быть проблема? Разве это нормально, что так долго открывается фильтр?)) Если пытаться открыть соседний столбец, где не выделено цветом, то все мгновенно открывается. Родитель Ссылка

Дело не в мощи процессора, а в самом ПО. Excel для Mac традиционно отстает по возможностям от Excel для Windows — так было всегда, к сожалению. Родитель Ссылка

Николай, вот сделал специально для такой задачи макрос. Родитель Ссылка

Спасибо огромное, Николай! Очень нужный макрос. Экономит кучу времени, которое можно тратить на велопрогулки. Как Вы всё успеваете, даже ума не приложу! И надстройку допилить, и книгу написать, и работа, и дела домашние делать. Книгу жду с нетерпением (шпаргалка с приёмами и горячими клавишами прочно обосновалась на столе в течение нескольких месяцев). Вашим успехам радуюсь от души. Желаю написать многотомный бестселлер, перевести на несколько языков, создать свою корпорацию, вырастить несколько детей, находясь в гармонии с самим собой. Виртуально жму Вам руку и желаю всяческих успехов. Родитель Ссылка

Шикарный макрос. Нельзя ли его немного оттюнинговать, чтобы на слишком темной заливке менялся бы цвет надписи(хотя бы)? А то если заливает тёмно-синим, то черный шрифт значения в ячейке не видать совсем. И ваще, хорошо бы заливать не стандартными цветами(они жёсткие — по «глазам бьют» ), а более мягкими из палитры themes/web. Спасибо. Родитель Ссылка

Добрый день, подскажите, пожалуйста, возможно ли такое сравнение? знаяения в одном столбце (можно и разделить, т.е. данные в скобках вынести во второй столбец), н-р. Аптека 1 (ООО Фарм) Аптека 1 (ОАО Годовалов) Аптека 2 (ООО Фарм) ООО Фарм нужно оставить только одну строку с ООО Фарм, а строки Аптека 1 (ООО Фарм) и Аптека 2 (ООО Фарм) удалить (выделить…) Ссылка

Елена, посмотрите статью про удаление дубликатов Родитель Ссылка

  1. Добрый день! Подскажите, а если мне нужно найденные одинаковые дубликаты просуммировать в месте и в отдельный столбец уже вывести результаты без дубликатов. Как это можно правильно реализовать?

Ссылка
Николай Павлов

Александр, вам нужны сводные таблицы Родитель Ссылка

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

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

Решение найдено! В условном форматировании, на основе формулы создать правило и ввести формулу =СЧЁТЕСЛИ($AH$7:$AH7;AH7)>1 , адаптируйте для своих значений. Меняет формат каждого следующего повтора, крорме первого. Ссылка

Добрый вечер! Подскажите, а как выделить цветом ВСЮ СТРОКУ? Есть большая таблица данных, в которой например 8-й столбец содержит названия городов. Нужно выделить цветом всю строку таблицы данных, если это, предположим, Москва или С-Петербург. Заранее спасибо. Ссылка

Выделить всю таблицу, открыть Главная — Условное форматирование — Создать правило — Использовать формулу

и ввести примерно так:

=$D2=»Москва»

Предполагаю, что города у вас в столбце D и D2 — это первая ячейка с городом в этом столбце после шапки. Не забудьте задать цвет, нажав кнопку
Формат
.

Николай! Большое Вам спасибо за помощь с моим вопросом и за замечательный сайт!! Успехов в Новом Году. Родитель Ссылка

Подскажите пожалуйста , почему могут не выделяться дубликаты при условном форматировании? Значения ячеек абсолютно одинаковые? Ссылка

А они точно дубликаты? Пробелов или русская-эс-вместо-английская-си там точно нет? Родитель Ссылка

да Николай , даже бывает копируешь содержимое ячейки , а строка всё равно не выделяется. Родитель Ссылка

Читайте также:  Формула ПРЕДСКАЗ для прогноза продаж с учетом сезонности в Excel

Здравствуйте. удалось ди вам найти решение данной проблемы? то же самое дубликаты не выделяются, мне кажется если они созданы в разных программах. Пыталась объединить два файла в один копированием, все равно не выделяются. Родитель Ссылка

Подскажите, пожалуйста, каким образом можно реализовать следующую выборку. У меня есть, к примеру 30 столбцов с e-mail адресами Мне нужно отфильтровать те e-mail’ы, которые встречаются во всех

30 столбцах. Т.е. если e-mail есть только в одном или двух или 29 столбцах — он не подходит. А если он есть в 30 — это то что нужно. Спасибо! Ссылка

С ходу вариант: сделать рядом с таблицей справа еще одну таблицу из 30 столбцов, где в каждом столбце проверять с помощью функции СЧЁТЕСЛИ наличие в нем определенного email (будет либо 0, либо 1). Затем суммировать все единицы еще в одном столбце. Там где сумма =30 — ваш email. Родитель Ссылка

Подскажите, пожалуйста, как можно реализовать вот такое действие: «Когда данные при вводе в ячейку ФИО повторяются, в соседней ячейке писалось «Повторно», а уникальные — «Впервые». Спасибо. Ссылка

Проверять формулой СЧЁТЕСЛИ не было ли раньше уже введенного значения. Затем при помощи функции ЕСЛИ выводить «повторно» или «впервые» в зависимости от результатов проверки. Точнее сказать не могу не видя вашего файла. Родитель Ссылка

Спасибо большое, Николай. Я написала через функцию ЕСЛИ, вот какая формула получилась: =ЕСЛИ(СЧЁТЕСЛИ(E$1:E2;E2)>1;»Повторно»;»Впервые»). А я хотела попробовать через макрос, например как у вас тут на сайте приведен пример с автоматической вставкой ДАТЫ в соседнюю ячейку, я хотела так же и тут попробовать, ну ничего не вышло. Лучше же конечно, чтобы всё автоматически работало, а формулу надо постоянно копировать вниз. Родитель Ссылка

Добрый день! Скажите, а возможен поиск не 100%-ых дубликатов, а например 60% (и можно-ли менять этот показатель на 70-80-90% и т.д.)? Проблема в том, что у меня 5 списков наименований (

10.000 каждый), которые создавались пятью разными людьми. Эти 5 человек по разному описывали один и тот-же товар Пример: — молоко — «молоко» — молоко. — молако — малако Мне нужно соединить эти 5 списков в одну базу и найти дубликаты, но так как совпадение в моём случае не 100%, то и выделения тоже не происходит. Есть ли какое-то решение? Ссылка

Вот здесь описано возможное решение: Нечеткое сравнение строк Родитель Ссылка

странно, у меня почему-то вариант с использованием функции «сцепить» не работает самое интересное то, что пример я скачал, удаляю правила из ячеек, затем снова добавляю — и не работает. Ссылка

Руслан, посмотрите внимательно на доллары в адресах ячеек в моем примере и у вас — закрепление ссылок должно быть по-другому. Родитель Ссылка

Да, спасибо, разобрался Родитель Ссылка

Добрый день! Задача из той же серии: есть 2 столбца со значениями. Мне необходимо выделить значения, которые повторяются в двух столбцах, при этом в первом столбце также есть одинаковые значения, соответственно их выделять не нужно. Ссылка

А если сначала удалить дубликаты в первом столбце, а потом применить способ из статьи? Родитель Ссылка

Дело в том что некоторые дубликаты в первом столбце могут попадаться и во втором. А задача состоит именно в том, чтобы найти повторения в двух столбцах(значений в каждом столбце по несколько тысяч). Родитель Ссылка

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

А есть возможность подсветка дублирующихся ячеек через формулу?* Ссылка

В смысле «через формулу»? Как формула может изменять цвет ячеек? Родитель Ссылка

Добрый день! Подскажите как установить счетчик повтора значения в ячейке. Например в формуле =ЕСЛИ(СЧЁТЕСЛИ(E$1:E2;E2)>1;»Повторно»;»Впервые»;) вместо «повторно» указывалось количество повторов (для втрого раза — 2, для третьего повтора -3 и т.д., а вместо «Впервые» — 1). Разобрался (помог поиск по форуму): =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$16;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Спасибо за сайт Ссылка

Здравствуйте! У меня стоит MS Office 2007. Пару лет назад при очередном обновлении Excel возникла проблема с правильным отображением дубликатов некоторых текстовых значений. С тех пор проблема не исчезла и наблюдается во всех последующих версиях Excel (проверяла), хотя до злополучного обновления всё работало нормально. Причем проблема эта наблюдается как в условном форматировании ячеек при попытке выделить повторяющиеся значения, так и в формулах, где идет проверка на совпадение значений. А теперь суть: Привожу для наглядности пример. Ячейки А1:Е1 В отформатированы как текстовые, кроме того с условным форматированием повторяющихся значений. Четыре ячейки выделены как повторяющиеся. Excel воспринимает как одинаковые пары значений 1-02, 2-15 и 1-01, 1-01. Кроме того приведена формула на поиск соответствия в этом диапазоне текстовому значению 1-15. Формула выдает значение «Истина» (то есть подходящих ячеек в диапазоне как минимум должно быть две), при этом такого значения в диапазоне нет вообще! Причины такого поведения я раскрыла. Они наглядно отражены в таблице (колонки G:H). Колонка G отформатирована как текст, колонка H отформатирована как числа. Значения же в них вводились одинаковые, но в колонке H эти значения преобразовывались в числа, которые для значений 1-01 и 1-15 оказались одинаковыми, что тут же отразилось в поведении условного форматирования. Вот почему и формула выдает Истину, принимая значения 1-01 в строке равной значению 1-15. Но ведь это неправильное поведение! Можно ли как-то решить эту проблему?


Ссылка

Юлия, «числа-как-текст» и «числа-как-числа» — это две большие разницы в Excel (у них разные внутренние коды). Чтобы корректно их сравнивать, нужно либо преобразование псевдочисел в числа (это можно сделать, например, с помощью макроса в PLEX), либо чисел в текстовый формат. Родитель Ссылка

Николай, как я и говорила ранее, я отформатировала ячейки в диапазоне как текст. То есть я преобразовала эти псевдочисла в текстовой формат, разве нет? Что я еще должна была сделать, что бы Excel воспринимал значения 1-02, 6-15, 1-01 и проч. именно как текст?


Родитель Ссылка

Ответа не дождалась, но, думаю, проблема всё-таки в некорретной работе текстового форматирования. Некорректно она работает и в формульной части. Так в справке к функции «Текст» имеем следующее: » Функция ТЕКСТ

преобразует число в форматированный текст, и результат больше не может быть использован в вычислениях в качестве числа.». Простой пример: в ячейку А1 заношу любое числовое значение, например, 402. В ячейку А2 заношу простую формулу, призванную преобразить число в текст, который далее больше нельзя будет использовать при вычислениях: =ТЕКСТ(A1;»0″ ) Получаем результат 402, сдвинутый к левому краю ячейки, что вроде бы должно нас убедить, что это теперь текст. Далее в ячейку А3 ввожу формулу: =A2+2 и в результате, вуаля, получаем 407! Как я уже говорила, такая политика в форматировании появилась пару лет назад и сразу во всех вариантах Офиса. До тех пор текст это был именно текст, как бы он не выглядел. Отсюда и проблемы. Правильно ли я понимаю, что в настоящее время решить эту ситуацию никак нельзя?


Родитель Ссылка

День добрый! Может кто сталкивался и знает, что с этим делать. Есть таблица, в одном столбце действует правило выделение дубликатов. Когда в ячейке этого столбца вводишь число, которое точно в этом столбце есть, то ячейка выделяется, т.е. все работает, как и должно. Но если копируешь строку из этой таблицы, и вставляешь ниже (чтобы не заполнять заново другие ячейки), то правило работать перестает. Вот так правило выглядит до вставки новой строки — А вот как правило начинает выглядеть после вставки строки — Я так понимаю, что почему-то правило начинает ограничиваться новой строкой, но почему? И если правило применяется до 940 строки, а новая строка как раз имеет этот номер, то почему оно в ней не меняет цвет, хоть там и дубль? Если просто вставить пустую строку, то все ок, правило не меняется. Ссылка

Николай, здравствуйте. Подскажите пожалуйста, такой момент. В таблице 60 000 строк текстовых фраз, среди которых есть дубликаты. В соседнем столбце через формулу СЧЁТЕСЛИ вывожу сколько раз каждая фраза встречается. Для первой строки проблем никаких нет, формула цифру выдаёт. А вот когда я растягиваю формулу до конца таблицы, то вначале таблицы она ещё срабатывает, то есть выдаёт верную цифру, а в дальнейшем (начиная с середины таблицы и до конца) показывает одну и туже цифру. То есть складывается ощущение, что ексель не справляется с расчетом и выдаёт некую цифру. Что самое интересное, если по этим одинаковым цифрам ещё раз растянуть формулу, то цифры обновятся на корректные. Для информации: вычисление стоит автоматически, ошибок в формуле нет (перепроверил эти моменты по 100 раз). Спасибо. Юрий. Ссылка

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

А есть ли возможность в колонке выделить дубликаты чисел с противоположным знаком? Ссылка

Добрый день! спасибо за мануалы. есть такой вопрос. есть столбец с ссылками и столбец с словом YES и NO фильтрую по слову YES. и теперь здесь нужно найти дубликаты ссылок и дать им значение NO. т.е. только копиям дать значение NO. прошу помочь с этой проблемой. в файле 30 листов по 2500 строк. по одному нереально каждую проверять. Ссылка

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

Есть решение, пишите в личку Родитель Ссылка

Здравствуйте, Николай! Сделал по описанному Вами способу выпадающие списки с накоплением (Фамилия ИО) в расписании занятий (чтобы знать в какое время занят тот или иной человек, на каком занятии, у кого и т.д.). В соседних ячейках время занятий («время от» и «время до»;) Попытался найти способ как выделять цветом одних и тех же людей, занятых в одно и то же время, чтобы случайно не поставить человека на то время, когда он записан на другом занятии, но не смог. Получается, если человек один, но у меня список с накоплением, вот в чем подвох. Когда набирается в списке более одного выделение снимается. Не подскажете, может есть выход? Ссылка

Читайте также:  Работа со сводными таблицами в Excel на примерах

странно, у меня не работает способ №3, может дело в том, что я использую умную таблицу, но почему тогда, когда я прописываю правило в окне «Изменение правила форматирования», нажимаю «ок», и окно не закрывается, убираю знак равно-проходит, но формула в ковычках и не работает. Ссылка

Здравствуйте! Подскажите пожалуйста, можно ли сделать следующее: Есть книга с расписанием, в ней может быть до 6 листов. Можно ли сделать чтобы выделялись повторяющиеся ячейки на всех листах, для столбца «аудитория» но только для каждой строки отдельно (т.е. выделять если повторяется на любом курсе повтор аудитории например в понедельник 1 пара или вторник 5 пара). Шаблон: https://cloud.mail.ru/public/KGek/jPVjxCfXN Ссылка

Добрый день! а можно ли вывести дубликаты (Фамилия и имя), если они в одном столбце, но не в отдельных ячейках, а в одной ячейке указано много фамилий сразу и таких ячеек около 40? Скриншот как столбца https://prntscr.com/gew5wl Ссылка

Ох, ужас какой А на выходе вам нужно что получить? Список всех людей без повторов? Или понимать, кто участвует больше чем в одной конференции? Родитель Ссылка

Специально зарегистрировалась на вашем сайте, уж очень полезный. скажите, пожалуйста, а есть ли возможность сделать так. Например, идут значения в столбце (номера лотов), и чтобы при последующем введении лота Excel как нибудь бы сообщал, что такой лот уже существует. А если бы еще и отсылал на строку с таким номером , то вообще сказка.)) Условное форматирование в принципе работает, то есть при введении существующего лота, excel сразу же закрашивает его цветом. Но хотелось бы узнать есть ли еще какие то способы? Ссылка

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

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

Добрый день! Не могу понять почему знак $ перед буквой ячейки выделяет цветом все три ячейки ФИО, а если без $ то только ячейки в первом столбце? Нашел:) Ссылка

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

Илья, с такими запросами вам на Форум в ветку Работа

Ок, спасибо! Родитель Ссылка

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

222/5657 проверить
222/5650 замена
222/5657 передать

Ссылка
Helin ACAR

Здравствуйте Николай Спасибо, что делитесь этим с нами. Хочу попросить у вас совета или даже помощи. Мы создаем таблицы и в ячейках тексты. В этих текстах бывают повторяющиеся предложения или словосочетания. Нам надо найти и выделить (. Не удалять. ) эти предложения или словосочетания. Ну и потом их мануально изменить. Я не могу составить макрос или правило (кажется макрос будет правильней) для поиска и выделения предложения или скорее лучше будет минимум 3х слов. P.S. я нашла вот это решение https://www.extendoffice.com/documents/word/5450-word-find-duplicate-sentences.html через этот сайт https://otvet.mail.ru/question/217550511 , но этот код выделяет только абзацы, а не предложения в текстах. Помогите, пожалуйста Спасибо заранее Ссылка

С такой задачей лучше на форум в раздел «Работа» — тут нужно специальный макрос писать (не самый простой, ИМХО). Родитель Ссылка

Здравствуйте, помогите, пожалуйста, разобраться со следующей проблемой. На макбуке про 2020 года 2.3 ггц 8 гб озу работаю с файлом эксель. У него 20 тысяч строк и 6 столбцов. Выделяю красным дубли в одном из столбцов, далее пытаюсь открыть фильтр и эксель зависает. Только при нажатии esc несколько раз отвисает. При нажатии на фильтр на соседних столбцах, где нет выделения цветом, все срабатывает мгновенно. При этом на более слабом компе на винде такой проблемы нет. Все почти мгновенно работает. В чем может быть проблема? Ссылка

А Office какой версии? Родитель Ссылка

2019 последней версии. На других маках, даже более слабых, нет проблем с экселем. Родитель Ссылка

Здравствуйте Николай. Огромное спасибо за статью. Не могу разобраться с возникшей проблемой: Решил автоматизировать Способ № 3 (без служебного столбца)

(
с формулойсумм(ч…
и т.д.) В таком виде:

Range(«A2:C30″).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:=»=СУММ(Ч($A2&$B2&$C2=$A$2:$A$30&$B$2:$B$30&$C$2:$C$30))>1» Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).Interior.Color = 49407 Selection.FormatConditions(1).StopIfTrue = False

Однако возникла проблема, после применения данного скрипта — выделения повторяющихся ячеек не происходит, при этом правило создаётся. Стоит зайти в редактирование этого правила (а именно в редактирование формулы) и ничего не изменяя — нажать ок/применить форматирование начинает работать. В чём может быть проблема? Заранее спасибо
UPD:
проблема решилась заменой в формуле макроса
СУММ
на
СУММПРОИЗВ
. За подсказку спасибо пользователю sokol92. Ссылка на форум Планета Excel с решением данной проблемы.

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

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

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

Функция «Найти и выделить»

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

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

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

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

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

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

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

Где A2, B2 и C2 — самые верхние в вашем диапазоне, а 3 — количество колонок для сравнения.

Конечно, можно не ограничиваться сравнением только 3 колонок. Вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбиках.

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

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

2. Как выделить различия.

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

  1. Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.

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

Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если нужно перемещаться снизу вверх, то нажмите и удерживайте SHIFT, и вновь используйте ТАВ — будете двигаться не вниз, а вверх. Вы увидите, как ваше белое пятно перемещается, и соответственно изменяется активный столбец.

  1. На вкладке «Главная» нажмите «Найти и выделить» > « Выделить группу ячеек». Затем выберите «Отличия по строкам» и нажмите «ОК» .
  1. Позиции, значения которых отличаются от ячеек сравнения в каждой строке, выделяются. Если вы хотите закрасить выделенные ячейки каким-либо цветом, просто щелкните значок «Цвет заливки» на ленте и выберите нужный цвет.

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

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».

    =АДРЕС(1;1) – возвращает $A$1.
    =АДРЕС(1;1;4) – возвращает A1.
    =АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
    =АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

    Поиск по цвету

    Инструкция, как переименовать несколько файлов сразу Вам будет интересно: Инструкция, как переименовать несколько файлов сразу

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

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

    • указывающий на повторяющиеся данные;
    • показывает, в какой цвет будет окрашивать обнаруженные дубликаты;
    • кнопка подтверждения.

    Найти повторяющиеся значения в excel

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

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

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

    в группу
    Стили,
    выбираем
    Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
    В появившемся диалоговом окне Повторяющиеся значения,

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

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

    Если вы работаете с табличными документами большого объема (много данных/столбцов), очень сложно держать на контроле достоверность/актуальность всей информации. Поэтому очень часто требуется проанализировать два или более столбцов в документе Эксель на предмет обнаружения повторений. А если пользователь не обладает информацией обо всем функционале программы, у него может логично возникнуть вопрос: как сравнить два столбца в excel?

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

    Типы векселей

    Различия прав владельца оформления задолженности определяют классификацию по следующим типам:

    • именные;
    • ордерные;
    • на предъявителя.

    Документы 1-го типа содержат сведения о лице, которому предоставляется право требовать возврат денег от векселедателя. Во втором случае такое право предоставлено человеку, который в данный момент владеет документом. Его данные не прописаны на бумаге. Ордерное обязательство составляется на имя первого владельца и может передаваться другому лицу внесением передаточной надписи. Продажа и покупка осуществляется с каждым типом этого финансового инструмента. Банковский вексель может быть инкассовым. Тогда фиксируется передаточная надпись в пользу конкретного банка.

    • Корзина из газетных трубочек
    • Половые гормоны ЛГ и ФСГ
    • Как приготовить постное и заварное тесто для вареников с картошкой

    Именной вексель

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

    Вексель на предъявителя

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

    Поиск нестрогого соответствия символов

    Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

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

    Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

    Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

    Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

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

    Доброго времени суток!

    С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.

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

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

    Примечание : все примеры ниже будут представлены в Office 2016/2019 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще.

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

    Представим одну из самых популярных задач: есть список строк с ФИО людей, в строках много совпадений, нужно оставить только уникальные значения (кстати, это просто пример, все совпадения с реальными людьми — случайны) .

    Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где «руки» будут бессильны. ).

    Пример задачи в Excel

    Чтобы в Excel удалить повторяющиеся строки, необходимо:

      выделить свою табличку (строки и столбцы) с данными;

    Выделение таблицы в Excel

    далее перейти в раздел «Данные» и нажать по инструменту «Удалить дубликаты» (см. скрин ниже);

    Данные — удалить дубликаты // Excel

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

    Удаление (выделение заголовков)

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

    Лишние строки удалены!

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

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

    Как это делается:

      сначала необходимо выделить табличку, затем перейти в раздел «Данные» и нажать на кнопку «Дополнительно» (подраздел «Сортировка и фильтр», см. скриншот ниже);

    Раздел «Данные» — дополнительно (Excel)

    далее переключите ползунок в режим «Скопировать результат в другое место» , в строке «Поместить результат в диапазон» — выберите ячейку, где будет начало таблицы; и поставьте галочку напротив пункта «Только уникальные значения» ; нажмите OK. Пример представлен на скриншоте ниже;

    Скопировать только уникальные значения в другое место

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

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

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

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

      сначала также выделяете все свои строки (пример ниже);

    далее необходимо открыть раздел «Главная» в верхнем меню Excel, выбрать подраздел «Условное форматирование», затем «Правила выделения ячеек», «Повторяющиеся значения» (пример представлен на скриншоте ниже);

    Повторяющиеся значения // Раздел «Главная» в Excel

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

    Выбираем цвет, как выделим строки

    Надеюсь, информация будет полезна для работы с большими таблицами.

Ссылка на основную публикацию