Извлечение уникальных значений из диапазона ячеек или массива

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

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

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

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

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

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

Для создания Динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
• в поле Имя введите: Исходный_список;
• в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
• нажмите ОК.

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

После ввода формулы вместо ENTER нужно нажать CTRL + SHIFT + ENTER. Затем нужно скопировать формулу вниз, например, с помощью Маркера заполнения. Чтобы все значения исходного списка были гарантировано отображены в списке уникальных значений, необходимо сделать размер списка уникальных значений равным размеру исходного списка (на тот случай, когда все значения исходного списка не повторяются). В случае наличия в исходном списке большого количества повторяющихся значений, список уникальных значений можно сделать меньшего размера, удалив лишние формулы, чтобы исключить ненужные вычисления, тормозящие пересчет листа.

Разберем работу формулу подробнее:

• Здесь использование функции СЧЁТЕСЛИ() не совсем обычно: в качестве критерия (второй аргумент) указано не одно значение, а целый массив Исходный_список , поэтому функция возвращает не одно значение, а целый массив нулей и единиц. Возвращается 0, если значение из исходного списка не найдено в диапазоне B4:B4(B4:B5 и т.д.), и 1 если найдено. Например, в ячейке B5 формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается массив . Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» (B5). Массив легко увидеть с помощью клавиши F9 (выделите в Строке формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список) , нажмите F9: вместо формулы отобразится ее результат);
• ПОИСКПОЗ() – возвращает позицию первого нуля в массиве из предыдущего шага. Первый нуль соответствует значению еще не найденному в исходном списке (т.е. значению «ОАО Уважаемая компания» для формулы в ячейке B5);
• ИНДЕКС() – восстанавливает значение по его позиции в диапазоне Исходный_список ;
• ЕСЛИОШИБКА() подавляет ошибку, возникающую, когда функция ПОИСКПОЗ() пытается в массиве нулей и единиц, возвращенном СЧЁТЕСЛИ() , найти 0, которого нет (ситуация возникает в ячейке B12, когда все уникальные значения уже извлечены из исходного списка).

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

Примечание. Функция ЕСЛИОШИБКА() будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() . В файле примера имеется лист Для 2003, где эта функция не используется.

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

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

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

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

1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.

2. В меню редактора перейдите на вкладку «Данные».

3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».

4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».

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

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

1. Кликните по редактируемой таблице.

2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».

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

1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».

2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».

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

4. Кликните окошко «Только уникальные записи» и нажмите «OK».

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

Чтобы отредактировать документ без создания копий:

• в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
• кликом мышки включите надстройку «Только уникальные записи»;
• клацните «OK».

1. Кликните вкладку «Главная».

2. В разделе «Стили» откройте выпадающее подменю «Условное форматирование».

3. Откройте: Правила выделения ячеек → Повторяющиеся значения…

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

5. Удалите выборочно или группами обозначенные дубли через контекстное меню Excel (клик правой кнопкой → Удалить).

Комфортной вам работы в Excel!

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

​ что равно 2​: Еще вопрос, в​ не так делаю. ​ ячейки на дубль​ 100 столбцов на​: Спасибо. Я возможно​ последовательности до конца​ промежуточные итоги, необходимо​ убрать повторяющиеся значения,​ Excel, существует соответствующая​ список команд, из​ который уже существует​.​ есть еще 3-й​ дубликатов.​

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

​ Обо всем этом​Как выделить повторяющиеся значения​ но для вашей​ Вашем файле есть​ Все по образцу,​(B2=$E$13) проверка значения​ 100 строк спец​ плохо объяснил условие.​ таблицы. Для этого:​ убрать их перед​ и открываем в​

​ команда меню. Она​ которых нас интересует​ в списке, обе​Преобразовав обычный диапазон в​ пол), и этот​Хотите ли Вы, чтобы​ и другом читайте​ в Excel​ таблицы это первая​ пояснение «задаем столбцы»​ не работает. Что-то​ ячейки на условие​ вставка форматы ОК​

​ В каждой колонке​

1. ​​ тем, как удалять​ программе вкладку «Данные».​
2. ​ находится на вкладке​ пункт «Правила выделения​ ячейки будут выделяться​ таблицу, Ваш список​ набор значений определен.​ данные автоматически проверялись​ в статье «Как​
3. ​. В этой таблице​ колонка по этому​ и напротив цифры​ упускаю.​ по которому ищем​ ( но вначале​

​ должны искаться дубли​Выделите диапазон ячеек A2:C19​ дубликаты в столбце​ Здесь находим блок​ «Данные» в блоке​

​ ячеек».​ ярким цветом и​ будет автоматически отформатирован,​ Вы можете вводить​ на наличие дубликатов​

Как можно использовать сводную таблицу.

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

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

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

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

1. Когда откроется сводная таблица, расположите области строк, столбцов и значений так, как вам нужно. Если у вас нет большого опыта работы со сводными таблицами Excel, могут оказаться полезными следующие подробные рекомендации: Создание сводной таблицы в Excel.
2. Переместите поле, количество уникальных элементов которого вы хотите вычислить ( поле « Товар» в этом примере), в область « Значения» , щелкните его и выберите «Параметры значения поля…» из раскрывающегося меню.
3. Откроется диалоговое окно , прокрутите вниз до операции «Число разных элементов» , которая является самым последним пунктом в списке, выберите ее и нажмите OK .

Вы также можете дать собственное имя своему счетчику, если хотите.

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

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

Благодарю вас за чтение и надеюсь увидеть вас снова. Пожалуйста, не переключайтесь!

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

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

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

Как посчитать количество повторяющихся значений в Excel? – Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…

Как убрать повторяющиеся значения в Excel? – В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…

6 способов создать список уникальных значений в Excel

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

Список уникальных значений возможно создать 6-ю способами:

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

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

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

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

Создать список уникальных значений с помощью расширенного фильтра

Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте.

«Расширенный фильтр»Скопировать результат в другое место»«Исходный диапазон»«Диапазон критериев»«Поместить результат в диапазон»«Только уникальные записи»«Ок»
«Фильтровать список на месте»

Внимание! Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)

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

=ЕСЛИ(МАКС(A1:A100)

Использование итерации

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

Код показан ниже:

a = 

b = []

for i in a:
    # Add to the new list
    # only if not present
    if i not in b:
        b.append(i)

print(b)

Выход

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

a = 
b = []

print(b)

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

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

Решение без формул массива

Для отбора уникальных значений можно обойтись без использования формул массива . Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист «Без CSE» в файле примера ).

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

СОВЕТ2 : Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список , необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст «» , который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами ). Например, см. статью Динамический выпадающий список в MS EXCE L.

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

• Как протабулировать функцию в excel

    

• Построить график экспоненты в excel

    

• Программы для смартфона lenovo

    

• Булево значение в 1с что это

    

• Word постоянно просит пересохранить документ

Решение без формул массива

Для отбора уникальных значений можно обойтись без использования формул массива . Для этого создайте дополнительный служебный столбец для промежуточных вычислений (см. лист «Без CSE» в файле примера ).

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

СОВЕТ2 : Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список , необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст «» , который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами ). Например, см. статью Динамический выпадающий список в MS EXCE L.

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

голоса

Рейтинг статьи

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.

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

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

Формулы для поиска дубликатов в 1 столбце

Дубликаты за исключением 1-го вхождения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)

Дубликаты с 1-го вхождения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; «Дубликат»; «Уникальное»)

Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, подлежащие проверке на дубликаты.

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

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев

2. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку «Фильтр» на вкладке «ДАННЫЕ», или «Сортировка и фильтр» —> «Фильтр» на вкладке «ГЛАВНАЯ».

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

3. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок «Дубликат строки».

Удалить дубликаты в Excel – Фильтр дубликатов строки

4. И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите «Удалить строку» в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей «Delete» на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

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

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

Воспользуемся примером с должностями, когда мы делали выпадающие списки (https://zen.yandex.ru/media/id/5f24070dd98a994308ce5e45/kak-sdelat-vypadaiuscii-spisok-v-excel-chast-2-5f318e6a268a8b3aa0499c21 )

Выбрав таблицу, нажме на кнопку Удалить дубликаты (Remove Duplicates) в разделе Данные (Data)

Если выбрать в окне удаления дубликатов только столбец “Отдел”, удалится 5 строк с дубликатами. Если выбрать “Должность” и “Отдел”, будут удалены только две строки, где сочетание отдела и должности повторяется. Если к выбору добавить еще колонку “Имя” или “Фамилия” – не удалится ничего, так как все сочетания уникальны.

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

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

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

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

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

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

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

Выделим область с данными, в которой нам нужно найти дубликаты:

На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”:

Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены дублирующие значения. Нажмите кнопку “ОК”:

После этого, в выделенной колонке, будут подсвечены цветом дубликаты:

Подсказка: не забудьте проверить данные вашей таблицы на наличие лишних пробелов. Для этого лучше использовать функцию TRIM (СЖПРОБЕЛЫ).

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

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

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

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

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

Обратите внимание на две таблицы ниже:

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

Рассмотрим как найти дубликаты строк:

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

=A2&B2&C2&D2

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

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

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

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого давайте сделаем следующее:

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

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

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

В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

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

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