Как пронумеровать повторяющиеся значения в excel

6 простых способов сделать автоматическую нумерацию в Excel — инструкция и видео

Быстро пронумеровать список фамилий, расставить людей по рейтингу и присвоить участникам викторины порядковый номер — задачи, которые можно автоматизировать. Зачем проставлять номера каждому участнику списка отдельно, если можно воспользоваться силой Excel и сделать всё быстро, и без мучений.

Способ 1 — простая нумерация

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

Если строк много, можно воспользоваться быстрым способом. Наводим мышку на правый нижний угол ячейки и делаем двойной клик по крестику — это сразу заполнит весь диапазон до последней строки.

Как это работает. Так как в первую ячейку мы поставили единицу, а во вторую — двойку, Excel счёл это как последовательность. Скорее всего, следующее число будет 3, за ним 4, а потом 5 и так далее.

Способ 2 — автоматическая последовательная нумерация

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

Далее точно так же тянем всё вниз и заполняем диапазон.

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

Как это работает. Наша формула образно звучит так: «Возьми ячейку над текущей и увеличь результат на единицу». А так как формула тянется вниз, то всё значение каждой ячейки увеличивается на один — и происходит автоматическая нумерация.

Способ 3 — через функцию =СТРОКА()

Если ваш список увеличивается строго вниз, то способ через функцию СТРОКА будет оптимальным.

Функция =СТРОКА() возвращает порядковый номер строки для текущей ячейки, начиная со строки №1

Зная это, мы можем найти закономерность и здесь. Введём формулу в ячейку E2:

Как видите, результат работы функции — это число 2. Строка вторая, значит в результате будет 2.

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

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

Как это работает. Механика функции простыми словами: «у ячейки с таким адресом вот такой номер строки». И нам, чтобы начать список с первого пункта, нужно этот номер строки уменьшить на количество строк шапки нашей таблицы. Так мы и получаем желаемый результат.

Способ 4 — нумерация через инструмент «Прогрессия»

Нумеровать можно не только формулами и арифметическими действиями, но и встроенными инструментами. Нам нужен инструмент «Прогрессия».

Арифметическая прогрессия — это последовательность чисел, где каждое следующее число больше предыдущего на какой-то шаг

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

Вводим в ячейку E1 число 1, выделяем эту единицу и весь диапазон, который нужно пронумеровать, а затем переходим на вкладку «Главная» → область «Редактирование» → кнопка «Заполнить» → «Прогрессия…»

Открывается диалоговое окно, в котором мы выбираем расположение «По столбцам», поскольку нумеруем вниз. Затем выбираем тип «Арифметическая» и ставим шаг — 1.

Если мы знаем, сколько всего людей в списке, то мы можем поставить предельное значение, например, 25, и тогда инструмент остановит свою работу, как только дойдёт до определённого числа. Нажимаем «Ок» и видим как Excel делает автоматическую нумерацию строк.

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

Способ 5 — функция МАКС()

Иногда бывает, что список людей идёт с разрывами и нам нужно продолжать нумерацию после пропуска. В этом случае нам подойдёт способ через функцию МАКС и работает он следующим образом.

Функция МАКС ищет наибольшее число в указанном диапазоне

В первую ячейку ставим число 1, а во вторую вводим формулу: =МАКС($E$2:E2)+1

Начало диапазона мы указали строго от $E$2, то есть первая граница не меняется ни при каких условиях. Концом диапазона будет последняя ячейка после протягивания. При протягивании такой формуле в строке 26 мы получим формулу: =МАКС($E$2:E25)+1

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

При этом формулу можно просто скопировать и вставить в любом месте сверху и снизу нашего списка нумерации и все числа автоматически пересчитаются.

Как это работает. Так как диапазон увеличивается на единицу =МАКС() + 1, то у нас каждая ячейка будет больше другой и функция МАКС будет выбирать её.

Способ 6 — адаптивная автоматическая нумерация через =СЧЁТЗ() и =ЕСЛИ()

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

Функция СЧЁТЗ считает количество заполненных ячеек в диапазоне
Функция ЕСЛИ проверяет ячейку на соответствие какому-то условию. Если условие истинно, выполняется одно действие. Если условие ложно, выполняется другое действие

Итак, формула: =ЕСЛИ(B2=»»;»»;СЧЁТЗ($B$2:B2))

Вставляем формулу в ячейку B2, тянем вниз и формула автоматически пропускает пустые строки. Заметьте, что в части формулы, где СЧЁТЗ, мы закрепили первую часть, потому что мы не хотим, чтобы диапазон состоял из одной строки. Нам нужно, чтобы он постоянно увеличивался.

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

Как это работает. Функция ЕСЛИ отвечает за такое условие: если ячейка B2, где у нас лежит фамилия, пустая, то так и оставить её пустой, иначе — посчитать общее количество заполненных ячеек.

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

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

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

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

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

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

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

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

Щелкаем ОК,

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

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

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

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

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

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

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

1. выделить свою табличку (строки и столбцы) с данными;
   Выделение таблицы в Excel
2. далее перейти в раздел «Данные» и нажать по инструменту «Удалить дубликаты» (см. скрин ниже);
   Данные — удалить дубликаты // Excel
3. после чего появится окно с настройками: если у вас есть заголовки в таблице — рекомендую поставить галочку «Мои данные содержат заголовки». После — нажмите кнопку OK;
   Удаление (выделение заголовков)
4. в результате увидите сообщение: сколько строк было удалено, и сколько уникальных осталось. Пример выполненной задачи представлен ниже.
   Лишние строки удалены!

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

Выберите любую ячейку в таблице, перейдите по вкладке Данные

в группу
Сортировка и фильтр
, щелкните по кнопке
Дополнительно.
В появившемся диалоговом окне Расширенный фильтр,

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

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

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

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

1. сначала необходимо выделить табличку, затем перейти в раздел «Данные» и нажать на кнопку «Дополнительно»(подраздел «Сортировка и фильтр», см. скриншот ниже);
   Раздел «Данные» — дополнительно (Excel)
2. далее переключите ползунок в режим «Скопировать результат в другое место», в строке «Поместить результат в диапазон» — выберите ячейку, где будет начало таблицы; и поставьте галочку напротив пункта «Только уникальные значения»; нажмите OK. Пример представлен на скриншоте ниже;
   Скопировать только уникальные значения в другое место
3. после чего вы увидите, как появились данные в выбранном вами месте, причем, строки останутся только уникальные. Дальше можно с ними работать в обычном режиме…
   Было — стало

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

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

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

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

Как вычислить повторы при помощи сводных таблиц

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

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

Должно получиться так:

А на листе сформируется сама сводка – уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

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

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

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

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

Posted On 09.12.2017

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

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

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

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

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

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

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

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

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

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

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

Если Вам нужно найти и посчитать количество повторяющихся значений в Excel, создадим для этого сводную таблицу Excel. Добавляем в исходную таблицу столбец «Код» и заполняем его «1»: ставим 1, 1 в первых двух ячейка, выделяем их и протягиваем вниз. Когда будут найдены дубликаты для строк, каждый раз значение в столбце «Код» будет увеличиваться на единицу.

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

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

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

Справой стороны перетаскиваем первые три заголовка таблицы в область «Названия строк», а поле «Код» перетаскиваем в область «Значения».

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

Для удобства, выделим все значения в столбце «Сумма по полю Код», и отсортируем их в порядке убывания.

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

Поделитесь статьёй с друзьями:

Автор: Аня Каминская| Дата: 2016-05-03| Просмотров:(30118)| Комментов:(0)

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

— Простите, вы не видели тут моего близнеца?
— Вы уже спрашивали.
Допустим, что у нас имеется длинный список чего-либо и мы предполагаем, что некоторые элементы этого списка повторяются более 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 — это первая ячейка с городом в этом столбце после шапки. Не забудьте задать цвет, нажав кнопку
Формат
.

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

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

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

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

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

Подскажите, пожалуйста, каким образом можно реализовать следующую выборку. У меня есть, к примеру 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 подсвечивал только уникальные значения, а не частично похожие. Ссылка

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

Доброго времени суток, повелители формул! Уперся тоже в эту проблему, поиск дубликатов работает нормально, но только до 15 знака, после 15 знака СЧЁТЕСЛИ «видит» нули и воспринимается значение как совпадение, а это ни есть «истина». Использую формулу =СЧЁТЕСЛИМН($B$5:$B$2723;$B5;$E$5:$E$2723;$E5)>1 в условном форматировании, т.е. поиск ведется по двум условиям (хотя это не так важно сейчас). И, таки да, формат ячейки текстовый, потому как значения могут начинаться с нуля, Excel 2021. Есть ли решение этой проблемы? На просторах инета предложений с таким случаем не нашел. У Алекскй Иванов решение вроде есть, но он не отвечает. Да и не правильно это, решение должно быть доступно для всех Родитель Ссылка

Доброго дня! Попробуйте конструкцию: СЧЁТЕСЛИ($A$1:$A$15;»*»&A1) У меня работает как формула условного форматирования, подсвечивает дубликаты на ICC сим-карт (20 цифр как текст). Родитель Ссылка

Антон, доброго времени суток! Работает пока всё отлично и ошибок пока не заметил! Думал уже решения не найду. Антон, преогромнейшее спасибо за подсказку! Я надеюсь, что этот приём пригодится не одному мне. Всех форумчан с Новым Годом. Родитель Ссылка

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

странно, у меня не работает способ №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 , но этот код выделяет только абзацы, а не предложения в текстах. Помогите, пожалуйста Спасибо заранее Ссылка

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

Здравствуйте, помогите, пожалуйста, разобраться со следующей проблемой. На макбуке про 2021 года 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 с решением данной проблемы.

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

Реализация товаров и услуг ПЮ000003136 от 08.02.2021 7:56:31

Реализация товаров и услуг ПЮ000003048 от 06.02.2021 8:19:19

Реализация товаров и услуг ПЮ000002812 от 04.02.2021 23:00:00

Реализация товаров и услуг ПЮ000003000 от 14.10.2022 9:20:28

Реализация товаров и услуг ПЮ000003000 от 06.02.2021 8:19:19

Реализация товаров и услуг ПЮ000002040 от 29.01.2021 7:49:02

мне нужно чтоб подсветился текст Реализация товаров и услуг ПЮ000003000 от Ссылка
Яна Билоха

Добрый вечер! Подскажите, пожалуйста: в меня есть первый столбец с большим количеством повторяющихся значений и есть 100 значений во втором, которые встречаются и в первом столбце. Моя задача -выделить в первом столбце все значения, которые есть во втором. Использовать первый способ не могу, так как он высвечивает всё. Подсвечивать ячейки через создание правила-форматировать только ячейки, которые содержат-получается. Но проделать это точечно очень трудозатратно. А массово не могу, не получается. Подскажите, пожалуйста, как можно это сделать? Заранее спасибо за ответ. Ссылка

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

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

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

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

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

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

Как сравнить два столбца в Excel на совпадения

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Post navigation

Например, зеленый. И нажмите ОК на всех открытых окнах.

поиска одинаковых значений в столбце.

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

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

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

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

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

Поиск дубликатов с !SEMTools

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

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

Найти дубли ячеек внутри диапазона, кроме первого

Найти в диапазоне дубли ячеек, включая первый

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

Пример такой таблицы изображен ниже на рисунке:

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

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

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

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

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

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

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

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

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

• Выделить столбцы с данными, в которых нужно вычислить совпадения;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

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

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

Открывает вкладку «Главная», в разделе «Стили» выбираем «Условное форматирование» — «Правила выделения ячеек» — «Повторяющиеся значения».

Открылось окно, в котором есть два пункта: что выделить – уникальные или повторяющиеся значения, и, как их выделить – в какую цветовую гамму. И, конечно же, кнопка «ОК».

Чтобы поиск был осуществлен не по всей таблице, предварительно выделите один или несколько столбцов.

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

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

Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, то мне достаточно выделить верхнюю строку, в которой находятся заголовки, затем перейти на вкладку Data (Данные) и нажать команду Filter (Фильтр). Возле каждого из заголовков появятся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

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

После настройки фильтра Вы можете удалить дубликаты строк, подвести промежуточные итоги или дополнительно отфильтровать данные по другому столбцу. Вы можете редактировать данные в таблице так, как Вам нужно. На примере ниже у меня выбраны элементы XP и XP Pro.

Удаляем все одинаковые значения в Excel с помощью расширенного фильтра

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

Теперь нужно настроить в этом окне, каким образом будет произведена фильтровка. Можно скопировать результаты фильтра в другое место (ставим галочку и указываем место, куда скопируется результат), либо результат оставить в том же месте. И, обязательно, ставим галочку «Только уникальные значения».

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

Поиск и удаление

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

Способ 1: простое удаление повторяющихся строк

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

1. Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

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

Способ 2: удаление дубликатов в «умной таблице»

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

1. Выделяем весь табличный диапазон.

Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

Но создание «умной таблицы» — это только один шаг для решения нашей главной задачи – удаления дубликатов. Кликаем по любой ячейке табличного диапазона. При этом появляется дополнительная группа вкладок «Работа с таблицами». Находясь во вкладке «Конструктор» кликаем по кнопке «Удалить дубликаты», которая расположена на ленте в блоке инструментов «Сервис».

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

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

Урок: Как сделать таблицу в Excel

Способ 3: применение сортировки

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

1. Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

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

Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».

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

Урок: Расширенный фильтр в Excel

Способ 4: условное форматирование

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

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

Открывается окно настройки форматирования. Первый параметр в нём оставляем без изменения – «Повторяющиеся». А вот в параметре выделения можно, как оставить настройки по умолчанию, так и выбрать любой подходящий для вас цвет, после этого жмем на кнопку «OK».

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

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

Урок: Условное форматирование в Excel

Способ 5: применение формулы

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));»»)

1. Создаем отдельный столбец, куда будут выводиться дубликаты.

Вводим формулу по указанному выше шаблону в первую свободную ячейку нового столбца. В нашем конкретном случае формула будет иметь следующий вид: =ЕСЛИОШИБКА(ИНДЕКС(A8:A15;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E7:$E$7;A8:A15)+ЕСЛИ(СЧЁТЕСЛИ(A8:A15;A8:A15)>1;0;1);0));»»)

Выделяем весь столбец для дубликатов, кроме шапки. Устанавливаем курсор в конец строки формул. Нажимаем на клавиатуре кнопку F2. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это обусловлено особенностями применения формул к массивам.

После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

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

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

Еще один способ быстро удалить дубли в таблице

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

Теперь откройте вкладку «Данные», раздел «Работа с данными», «Удалить дубликаты».

Расставим нужные галочки. Мне нужен поиск по двум столбцам, потому оставляю, как есть, и жму на кнопку «ОК».

На этом метод закончился. Вот мой результат его работы.

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

Сегодня я расскажу Вам о том, как избежать появления дубликатов в столбце данных на листе Excel. Этот приём работает в Microsoft Excel 2013, 2010, 2007 и более ранних версиях.

Мы уже касались этой темы в одной из статей. Поэтому, возможно, Вы уже знаете, как в Excel сделать так, чтобы введённые повторно данные автоматически выделялись цветом в процессе ввода.

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

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

В Excel есть встроенная функция Remove Duplicates (Удалить дубликаты). Вы можете выбрать столбец с данными и при помощи этой команды удалить все дубликаты, оставив только уникальные значения. Воспользоваться инструментом Remove Duplicates (Удалить дубликаты) можно при помощи одноименной кнопки, которую Вы найдёте на вкладке Data (Данные).

Не забудьте выбрать, в каком столбце необходимо оставить только уникальные значения. Если данные не содержат заголовков, то в диалоговом окне будут показаны Column A, Column B (столбец A, столбец B) и так далее, поэтому с заголовками работать гораздо удобнее.

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

Как предотвратить появление дубликатов – 5 простых шагов

В Excel есть инструмент, о котором часто незаслуженно забывают – «Проверка данных». С его помощью можно избежать ошибок, возникающих при вводе. Позже мы обязательно посвятим отдельную статью этому полезному инструменту. А пока, для разогрева, покажем его работу на простом примере

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

1. Просматриваем таблицу и, если необходимо, удаляем все повторяющиеся записи. Для этого сначала выделим дубликаты цветом, а затем, проверив все значения, удалим их вручную.
2. Выделяем весь столбец, в котором хотим избежать появления дубликатов. Для этого щёлкаем мышью по первой ячейке с данными и, удерживая клавишу Shift нажатой, щёлкаем по последней ячейке. Если этот столбец крайний в таблице, как в нашем случае, то можем использовать комбинацию клавиш Ctrl+Shift+End. Самое главное сначала выделите именно первую ячейку с данными.

Замечание: Если данные оформлены, как обычный диапазон, а не как полноценная таблица Excel, то необходимо выделить все ячейки столбца, в том числе пустые. В нашем примере это будет диапазон D2:D1048576.

1. Откройте вкладку Данные (Data) и кликните по иконке Проверка данных (Data Validation), чтобы вызвать диалоговое окно Проверка вводимых значений (Data Validation).
2. На вкладке Параметры (Settings) в выпадающем списке Тип данных (Allow) выберите Другой (Custom) и в поле Формула (Formula) введите такое выражение:
   =СЧЁТЕСЛИ($D:$D;D2)=1 =COUNTIF($D:$D,D2)=1

Здесь $D:$D – это адреса первой и последней ячейки в столбце. Обратите внимание, что мы использовали знак доллара, чтобы записать абсолютную ссылку. D2 – это адрес первой выделенной ячейки столбца, это не абсолютная ссылка.

Эта формула подсчитывает количество повторений значения ячейки D2 в диапазоне D1:D1048576. Если это значение встречается в заданном диапазоне только однажды, тогда всё в порядке. Если значение встречается несколько раз, то Excel покажет сообщение, текст которого мы запишем на вкладке Сообщение об ошибке (Error Alert).

Подсказка: Мы можем искать повторяющиеся значения, записанные не только в текущем, но и в другом столбце. Этот столбец может находиться на другом листе или даже в другой рабочей книге. Таким образом, вводя электронные адреса в столбец, мы можем сравнивать их с адресами, которые занесены в чёрный список и с которыми решено прекратить сотрудничество. Я расскажу подробнее о таком применении инструмента «Проверка данных» в одной из будущих статей.

1. Открываем вкладку Сообщение об ошибке (Error Alert) и заполняем поля Заголовок (Title) и Сообщение (Error message). Именно это сообщение будет показано в Excel при попытке ввести повторяющееся значение в столбец. Постарайтесь доступно пояснять в своём сообщении детали ошибки, чтобы Вам и Вашим коллегам было понятно в чём причина. Иначе по прошествии длительного времени, например, через месяц, Вы можете забыть, что означает данное сообщение.Например, так:Заголовок: Повторяющийся email.
   Сообщение: Введённый Вами адрес email уже используется в данном столбце. Допускается вводить только уникальные адреса email.
2. Нажмите ОК, чтобы закрыть диалоговое окно Проверка вводимых значений (Data validation).

Теперь при попытке ввести в столбец e-mail адрес, который в нём уже существует, будет показано созданное нами сообщение об ошибке. Это сработает, как при создании записи e-mail для нового клиента, так и при попытке изменить e-mail существующего клиента:

Если наша борьба с дубликатами допускает исключения

На шаге 4 в выпадающем списке Вид (Style) выбираем Предупреждение (Warning) или Сообщение (Information). Поведение сообщения об ошибке изменится следующим образом:

Предупреждение: В диалоговом окне будет предложен набор кнопок Да (Yes) / Нет (No) / Отмена (Cancel). Если нажать Да (Yes), то введённое значение будет добавлено в ячейку. Чтобы вернуться к редактированию ячейки, нажмите Нет (No) или Отмена (Cancel). По умолчанию активна кнопка Нет (No).

Сообщение: В диалоговом окне будет предложено нажать кнопку ОК или Отмена (Cancel). По умолчанию активна кнопка ОК – введённое повторяющееся значение останется в ячейке. Если хотите изменить данные в ячейке, нажмите Отмена (Cancel), чтобы вернуться к редактированию.

Замечание: Хочу ещё раз обратить Ваше внимание на то обстоятельство, что сообщение о появлении повторяющегося значения будет показано только при попытке ввести это значение в ячейку. Инструмент «Проверка данных» в Excel не обнаружит дубликаты среди уже введённых записей, даже если этих дубликатов сотни!

Урок подготовлен для Вас командой сайта office-guru.ru Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

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

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

1. В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:

В нашей формуле A1 это первая ячейка первого столбца, который мы собираемся сравнивать. $B$1 и $B$10000 это адреса первой и последней ячеек второго столбца, с которым будем выполнять сравнение. Обратите внимание на абсолютные ссылки – буквам столбца и номерам строки предшествует знак доллара ($). Я использую абсолютные ссылки для того, чтобы адреса ячеек оставались неизменными при копировании формул.

Если Вы хотите найти дубликаты в столбце B, поменяйте ссылки, чтобы формула приняла такой вид:

Вместо “Unique” и “Duplicate” Вы можете записать собственные метки, например, “Не найдено” и “Найдено“, или оставить только “Duplicate” и ввести символ пробела вместо второго значения. В последнем случае ячейки, для которых дубликаты найдены не будут, останутся пустыми, и, я полагаю, такое представление данных наиболее удобно для дальнейшего анализа. Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:Нажмите и, удерживая левую кнопку мыши, протащите границу рамки вниз, выделяя все ячейки, в которые требуется вставить формулу. Когда все необходимые ячейки будут выделены, отпустите кнопку мыши:

Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

Отлично, теперь все повторяющиеся значения отмечены как “Duplicate“:

Комментарии

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

Отличный инструмент, спасибо! Подскажите, что нужно изменить в этом макросе, чтоб закрашивались только те ячейки, которые повторяются не меньше 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 небольшое количество дублирующихся значений, Вы можете сделать это при помощи поиска. Зайдите на вкладку Home (Главная) и кликните Find & Select (Найти и выделить). Откроется диалоговое окно, в котором можно ввести любое значение для поиска в Вашей таблице. Чтобы избежать опечаток, Вы можете скопировать значение прямо из списка данных.

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

Если нужно выполнить поиск по всем имеющимся данным, возможно, кнопка Find All (Найти все) окажется для Вас более полезной.

Как найти одинаковые строки в Excel и выделить их цветом

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

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

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

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

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

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

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

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

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

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

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

Еще один способ быстро удалить дубли в таблице

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

Теперь откройте вкладку «Данные», раздел «Работа с данными», «Удалить дубликаты».

Расставим нужные галочки. Мне нужен поиск по двум столбцам, потому оставляю, как есть, и жму на кнопку «ОК».

На этом метод закончился. Вот мой результат его работы.

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

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

Как сравнить два столбца в Excel по строкам

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

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

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

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

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

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

Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Как сравнить два столбца в Excel на совпадения

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

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

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

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

• Выделить столбцы с данными, в которых нужно вычислить совпадения;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

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

Рассмотрим как найти совпадающие строки в таблице:

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

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

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

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

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

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

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

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

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

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

1. В первой пустой ячейке (в нашем примере это ячейка C1) запишем вот такую формулу:

В нашей формуле A1 это первая ячейка первого столбца, который мы собираемся сравнивать. $B$1 и $B$10000 это адреса первой и последней ячеек второго столбца, с которым будем выполнять сравнение. Обратите внимание на абсолютные ссылки – буквам столбца и номерам строки предшествует знак доллара ($). Я использую абсолютные ссылки для того, чтобы адреса ячеек оставались неизменными при копировании формул.

Если Вы хотите найти дубликаты в столбце B, поменяйте ссылки, чтобы формула приняла такой вид:

Вместо “Unique” и “Duplicate” Вы можете записать собственные метки, например, “Не найдено” и “Найдено“, или оставить только “Duplicate” и ввести символ пробела вместо второго значения. В последнем случае ячейки, для которых дубликаты найдены не будут, останутся пустыми, и, я полагаю, такое представление данных наиболее удобно для дальнейшего анализа. Теперь давайте скопируем нашу формулу во все ячейки столбца C, вплоть до самой нижней строки, которая содержит данные в столбце A. Для этого наведите указатель мыши на правый нижний угол ячейки C1, указатель примет форму чёрного перекрестия, как показано на картинке ниже:Нажмите и, удерживая левую кнопку мыши, протащите границу рамки вниз, выделяя все ячейки, в которые требуется вставить формулу. Когда все необходимые ячейки будут выделены, отпустите кнопку мыши:

Подсказка: В больших таблицах скопировать формулу получится быстрее, если использовать комбинации клавиш. Выделите ячейку C1 и нажмите Ctrl+C (чтобы скопировать формулу в буфер обмена), затем нажмите Ctrl+Shift+End (чтобы выделить все не пустые ячейки в столбе С) и, наконец, нажмите Ctrl+V (чтобы вставить формулу во все выделенные ячейки).

Отлично, теперь все повторяющиеся значения отмечены как “Duplicate“:

Поиск и удаление

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

Способ 1: простое удаление повторяющихся строк

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

1. Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

Способ 2: удаление дубликатов в «умной таблице»

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

Выделяем весь табличный диапазон.

Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

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

Способ 3: применение сортировки

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

Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

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

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

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

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

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, “Name” и “Duplicate?” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate?“, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

Вот и всё, теперь Вы видите только те элементы столбца А, которые дублируются в столбце В. В нашей учебной таблице таких ячеек всего две, но, как Вы понимаете, на практике их встретится намного больше.

Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

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

Если пометки “Duplicate” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

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

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

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

Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:

1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
2. Очистите фильтр.
3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):

Как видите, удалить дубликаты из двух столбцов в Excel при помощи формул – это не так уж сложно.

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

Выберите любую ячейку в таблице, перейдите по вкладке Данные

в группу
Сортировка и фильтр
, щелкните по кнопке
Дополнительно.

В появившемся диалоговом окне Расширенный фильтр,

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

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

Нумерация строк в Эксель

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

Нумерация вручную

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

Нумерация при помощи маркера заполнения

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

Маркер заполнения

1. Нужно установить номер в ячейку первой строки, затем ухватить маркер заполнения и при нажатой кнопке Ctrl, оттянуть маркер вниз на все строки которые нужно пронумеровать.
2. Ставим в первую и вторую ячейку последовательно нумерацию, например 1 и 2, и, зацепив мышкой маркер заполнения, тянем его вниз.
3. Нумерация строк при помощи функции СТРОКА. В ячейке с номером 1 вписываем =СТРОКА(A1), далее как в предыдущих способах: захватываем маркер заполнения и перетягиваем его вниз.
4. При помощи формулы СУММА или сложения. В первой ячейке вписываем 1, во второй пишем =, после знака указываем предыдущую ячейку и прибавляем единицу (+1). Должно получиться примерно следующее: =А3+1, далее жмем Enter и маркером заполнения перетаскиваем формулу в конец списка.

Интересно, что при втором способе можно задать шаг нумерации, например проставим номера 1 и сразу 3, то все строки будут пронумерованы 1,3,5… и т.д., т.е. нечетными числами. Или пронумеруем строки через одну, то и нумерация строк продолжится через одну строку.

Отображение или скрытие маркера заполнения

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

Разрешить маркеры…

Как автоматически пронумеровать строки при помощи инструмента Прогрессия

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

Нумерация при помощи прогрессии

Нумерация строк с объединенными ячейками

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

Ячейки объединил, нужно нумеровать как одну строку

Для такого случая есть еще одна функция, но ее написание зависит от количества строк из которых состоит шапка таблицы, например, шапка занимает две строки, тогда в третьей строке (ячейке) пишем =МАКС(А$1:А2)+1. Если нумерация начинается с пятой строки, тогда формула может быть следующей: =МАКС(А$1:А4)+1. Для нумерации строк нужно выделить все ячейки, в которых нужна нумерация, в том числе и объединенные, в строку формул вписываем эту формулу (знак $ можно поставить простым нажатием кнопки F4), затем одновременно нажимаем кнопки Ctrl и Enter.

Формула, чтобы вставить знак $, нажимаем на кнопку F4
Одновременно нажимаем кнопки Ctrl и Enter

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