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

покупка

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

• Скрыть строки, если они дублируются с помощью функции расширенного фильтра
• Скрыть строки, если они дублируются с Kutools for Excel

Скрыть строки, если они дублируются с помощью функции расширенного фильтра

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

1. Выберите диапазон, в котором вы скроете повторяющиеся строки, и нажмите кнопку Данные > Фильтр.

2. В открывшемся диалоговом окне Advanced Filter установите флажок Только уникальные записи и нажмите OK кнопку.

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

Скрыть строки, если они дублируются с Kutools for Excel

Иногда вам может потребоваться скрыть все повторяющиеся строки, включая первую (или исключая первую). В этом случае вы можете попробовать Kutools for Excel’s Выберите повторяющиеся и уникальные ячейки утилита в Excel.

Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональной 30-дневной БЕСПЛАТНОЙ пробной версией без необходимости использования кредитной карты! Скачать сейчас!

Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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

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

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

3. Щелкните правой кнопкой мыши заголовок выбранной строки и выберите Спрятать из контекстного меню.

Затем вы увидите, что все повторяющиеся строки скрыты сразу, как показано на следующих снимках экрана:

Выберите Дубликаты (кроме 1-го) можно получить результат:

Выберите Все дубликаты (включая 1-й), вы можете получить результат:

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональной 30-дневной БЕСПЛАТНОЙ пробной версией без необходимости использования кредитной карты! Get It Now

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

В общем, мы можем легко удалить дубликаты из списка с помощью Данные > Удалить дубликаты в Excel. Но как выбрать повторяющиеся / уникальные значения или подсчитать их из столбца? А что, если выбрать или подсчитать дубликаты / уникальные, кроме первого дубликата? Попробуйте Kutools для Excel Выберите повторяющиеся и уникальные ячейки утилита!

Получите 30-дневную бесплатную пробную версию прямо сейчас!

Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональной 30-дневной БЕСПЛАТНОЙ пробной версией без необходимости использования кредитной карты! Get It Now

Демо: скрыть строки, если они дублируются с Kutools for Excel

Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните 30-дневную бесплатную пробную версию без ограничений сегодня. Скачать

Как удалить дубликаты в Microsoft Excel

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

Как определить дубликаты в таблице

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

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

Это лишь один из примеров условного форматирования в Microsoft Excel. Если вас заинтересовала эта тема, читайте другую мою статью, где рассказано все о данной функции.

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Удаление дубликатов в таблице Excel

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

1. Выделите необходимые ячейки, перейдите на вкладку «Данные» и разверните меню «Работа с данными».
2. Щелкните по кнопке «Удалить дубликаты».
3. Вы выбрали только один столбец, поэтому в появившемся окне просто нажмите «ОК».
4. Отобразится уведомление с информацией о найденных и удаленных повторяющихся значений. Далее покажется, сколько уникальных значений осталось.
5. Выделение условного форматирования пропало – значит, повторы удалены и остались только уникальные строки.

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

1. Вы видите два столбца с днями недели и пометкой «Рабочий» или «Нерабочий» день. Некоторые значения полностью повторяются, например «Понедельник — Рабочий», но есть и «Понедельник — Нерабочий». Так вот удаление дубликатов сработает только в первом случае.
2. Выделите два столбца и нажмите кнопку «Удалить дубликаты».
3. Подтвердите выбор в новом окне.
4. Ознакомьтесь с информацией об удаленных и оставшихся значениях.
5. Теперь обратите внимание на то, какие строки были удалены.

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

Повторяющиеся строки в столбцах MS Excel и как с ними бороться

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

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

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

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

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

ИТОГ: Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице.

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

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

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

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

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

3. Условное форматирование в Excel

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

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

ИТОГ: Для больших массивов информации данный метод не дает четкой картины. Все фамилии авторов книг, у которых есть повторения, выделены одинаковым цветом.

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

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

Не забудьте нажать на командную кнопку ОК и продолжить формирование сводной таблицы на новом листе. В окне Список полей сводной таблицы установите флажки во всех полях, так как указано на рисунке. При этом выбранные наименования полей появятся в окнах Название строк и å Значения. Перетащите поле п/п в столбец Названия строк.

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

Материал подготовлен Л.А. Шутилиной, методистом ГМЦ ДОгМ

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

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

• Чтобы отфильтровать уникальные значения, щелкните Данные >Сортировка & Фильтр >Дополнительно.
• Чтобы удалить повторяющиеся значения, щелкните Data > Data Tools>Remove Duplicates (Удалить дубликаты).
• Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная .

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

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

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

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

Фильтрация уникальных значений

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. Щелкните Данные > Дополнительно (в группе Фильтр & сортировки ).

Важно: Вставьте текст оповещения здесь.

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

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

• Щелкните Фильтровать список на месте.

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

• Установите переключатель скопировать результат в другое место.
• В поле Копировать введите ссылку на ячейку.
• Кроме того, нажмите кнопку Свернуть диалоговое окно , чтобы временно скрыть всплывающее окно, выберите ячейку на листе и нажмите кнопку Развернуть.
• Установите флажок Только уникальные записи и нажмите кнопку ОК.

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

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

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

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

1. Выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
2. На вкладке Данные щелкните Удалить дубликаты (в группе Инструменты для работы с данными ).
3. Выполните одно или несколько из указанных ниже действий.
4. В разделе Столбцы выберите один или несколько столбцов.
5. Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
6. Чтобы быстро очистить все столбцы, щелкните Отменить выбор всех. Если диапазон ячеек или таблицы содержит много столбцов и требуется выбрать только несколько столбцов, может оказаться проще нажать кнопку Отменить выбор всех, а затем в разделе Столбцы выберите эти столбцы.

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. Если в этих столбцах найден дубликат, будет удалена вся строка, включая другие столбцы в таблице или диапазоне.

Проблемы при удалении дубликатов из контурных или промежуточных данных

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

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

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

Быстрое форматирование

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

Важно: Вставьте текст оповещения здесь.

Введите нужные значения, а затем выберите формат.

Расширенное форматирование

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

Важно: Вставьте текст оповещения здесь.

Выполните одно из следующих действий:

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

В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.

В списке Форматировать всев разделе Изменение описания правила выберите уникальное или повторяющееся.

Щелкните Формат , чтобы открыть всплывающее окно Формат ячеек .

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

В Excel в Интернете можно удалить повторяющиеся значения.

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

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

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

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

Примечание: Данные будут удалены из всех столбцов, даже если на этом шаге не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключ, используемый для поиска дубликатов, будет значением ОБА столбца & Column2. При обнаружении дубликата в столбцах Column1 и Column2 будет удалена вся строка, включая данные из Столбца 3.

Примечание: Если вы хотите вернуть данные, просто нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.