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

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

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

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

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

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

Как вручную удалить повторяющиеся строки
Удаление дубликатов в «умной» таблице
Убираем повторы, копируя уникальные записи в другое место
Формулы для удаления дубликатов
Формулы для поиска дубликатов в столбце
Удаление дублирующихся строк при помощи формул
Универсальный инструмент для поиска и удаления дубликатов в Excel

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

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

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

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

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

Для этого выполните следующие действия.

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

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

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

Важное замечание. Повторяющиеся значения определяются по тому, что отображается в ячейке, а не по тому, что в ней записано на самом деле. Представим, что в A1 и A2 содержится одна и та же дата. Одна из них представлена в формате 15.05.2020, а другая отформатирована в формате 15 май 2020. При поиске повторяющихся значений Excel считает, что это не одно и то же. Аналогично значения, которые отформатированы по-разному, считаются разными, поэтому $1 209,32 — это совсем не одно и то же, что 1209,32.

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

Удаление дубликатов в «умной таблице».

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

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

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

Но, в отличие от ранее рассмотренного инструмента удаления, операцию можно отменить, если что-то пошло не так.

Избавьтесь от повторов, скопировав уникальные записи в другое место.

Еще один способ удалить повторы — это выбрать все уникальные записи и скопировать их на другой лист или в другую книгу. Подробные шаги следуют ниже.

Выберите диапазон или всю таблицу, которую вы хотите обработать (1).
Перейдите на вкладку «Данные» (2) и нажмите кнопку «Фильтр — Дополнительно» (3-4).
В диалоговом окне «Расширенный фильтр» (5) выполните следующие действия:
- Выберите переключатель скопировать в другое место (6).
- Убедитесь, что в списке диапазонов указан правильный диапазон. Это должен быть диапазон из шага 1.
- В поле «Поместить результат в…» (7) введите диапазон, в который вы хотите скопировать уникальные записи (на самом деле достаточно указать его верхнюю левую ячейку).
- Выберите только уникальные записи (8).
Наконец, нажмите кнопку ОК, и уникальные значения будут скопированы в новое место:

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

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

Как убрать дубликаты строк с помощью формул.

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

Преимущество этого подхода заключается в универсальности: он позволяет вам:

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

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

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

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

Добавляем еще одну колонку, в которой запишем формулу.

Повторы наименований товаров, без учета первого вхождения:

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

Как видите, когда значение встречается впервые (к примеру, в B4), оно рассматривается как вполне обычное. А вот второе его появление (в B7) уже считается повтором.

Отмечаем все повторы вместе с первым появлением:

=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$17; $B2)>1; «Дубликат»; «Уникальный»)

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

Ну а теперь, чтобы убрать ненужное, устанавливаем фильтр и в столбце H и оставляем только «Дубликат». После чего строки, оставшиеся на экране, просто удаляем.

Вот небольшая пошаговая инструкция.

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

Формулы для поиска повторяющихся строк.

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

Отмечаем при помощи формулы неуникальные строчки, кроме 1- ^го вхождения:

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

В результате видим 2 повтора.

Теперь самый простой вариант действий – устанавливаем фильтр по столбцу H и слову «Дубликат». После этого просто удаляем сразу все отфильтрованные строки.

Если нам нужно исключить все повторяющиеся строки вместе с их первым появлением:

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

Далее вновь устанавливаем фильтр и действуем аналогично описанному выше.

Насколько удобен этот метод – судить вам.

Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

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

Он безупречно работает во всех операционных системах и во всех версиях Microsoft Excel 2019 — 2003.

Как избавиться от дубликатов в Excel в 2 клика мышки.

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

Выберите любую ячейку в таблице, c которой вы хотите работать, и нажмите Quick Dedupe на вкладке Ablebits Data.

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

Поскольку моя цель – просто выделить повторяющиеся данные, я выбрал «Закрасить цветом».

Помимо выделения цветом, вам доступны и другие операции:

Удалить дубликаты
Выбрать дубликаты
Указать их в столбце статуса
Копировать дубликаты на новый лист
Переместить на новый лист

Нажимаем кнопку OK и оцениваем получившийся результат:

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

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

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

Больше возможностей для поиска дубликатов при помощи Duplicate Remover.

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

Удаление дубликатов в Excel — обычная операция. Однако в каждом конкретном случае может быть ряд особенностей. В то время как инструмент Quick Dedupe фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для работы с дубликатами и уникальными значениями.

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

Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.
В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:

Все ваши данные будут автоматически выделены.
Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы выбираем первые 3 столбца:

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

Чтобы более наглядно увидеть результат, отметим параметр «Закрасить цветом» (Fill with color) и нажимаем Готово.

Мастеру Duplicate Remover требуется совсем немного времени, чтобы проанализировать вашу таблицу и показать результат:

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

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

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

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

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

Еще на эту же тему:

Источник

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

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

Чтобы отфильтровать уникальные значения, щелкните Ссылки > сортировки & фильтр > Расширенные.
Чтобы удалить повторяющиеся значения, выберите в > в >удалить дубликаты.
Чтобы выделить уникальные или повторяющиеся значения, используйте команду Условное форматирование в группе Стиль на вкладке Главная.

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

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

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

Сделайте следующее:

Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
Щелкните > дополнительные данные (в группе Фильтр & сортировки).
Во всплывающее окно Расширенный фильтр сделайте следующее:

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

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

Чтобы скопировать результаты фильтра в другое место:

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

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

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

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

Сделайте следующее:

Вы выберите диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
На вкладке Данные нажмите кнопку Удалить дубликаты (в группе Инструменты для работы с данными).
Выполните одно или несколько из указанных ниже действий.
- В области Столбцывыберите один или несколько столбцов.
- Чтобы быстро выбрать все столбцы, нажмите кнопку Выбрать все.
- Чтобы быстро очистить все столбцы, нажмите кнопку Отклоните все.
  
  Если диапазон ячеек или таблицы содержит много столбцов и нужно выбрать только несколько столбцов, вам может быть проще нажать кнопку Отобрать все,а затем в столбцах выберите эти столбцы.
  
  Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не выбраны все столбцы. Например, если выбрать Столбец1 и Столбец2, но не Столбец3, то ключом, используемым для поиска дубликатов, будет значение BOTH Column1 & Column2. Если в этих столбцах найдено повторяющиеся записи, удаляется вся строка, включая другие столбцы в таблице или диапазоне.
Нажмите кнопкуОК, и появится сообщение, в которое будет указано, сколько повторяюных значений было удалено или сколько уникальных значений осталось. Нажмите кнопку ОК, чтобы отклонять это сообщение.
Чтобы отменить изменение, нажмите кнопку Отменить (или нажмите клавиши CTRL+Z на клавиатуре).

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

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

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

Сделайте следующее:

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

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

Сделайте следующее:

Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
На вкладке Главная в группе Стили щелкните стрелку для команды Условное форматирование ивыберите управление правилами, чтобы отобразить всплывающее окно Диспетчер правил условного форматирования.
Выполните одно из следующих действий:
- Чтобы добавить условное форматирование, нажмите кнопку Новое правило, чтобы отобразить всплывающее окно Новое правило форматирования.
- Чтобы изменить условное форматирование, начните с того, что в списке Показать правила форматирования для выбран соответствующий лист или таблица. При необходимости выберите другой диапазон ячеек, нажав кнопку Свернуть во всплывающее окно Применяется к временно скрыть его. Выберите новый диапазон ячеек на этом сайте, а затем снова разширив всплывающее окно, . Выберите правило и нажмите кнопку Изменить правило, чтобы отобразить всплывающее окно Изменение правила форматирования.
В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.
В списке Форматировать все выберите изменить описание правила, выберите уникальный или дубликат.
Нажмите кнопку Формат, чтобы отобразить всплывающее окно Формат ячеек.
Выберите формат числа, шрифта, границы или заливки, который вы хотите применить, когда значение ячейки удовлетворяет условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы отображаются на панели предварительного просмотра.

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

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

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

Сделайте следующее:

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

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

Нужна дополнительная помощь?

Источник

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

Пусть в столбце

А

имеется список с

повторяющимися

значениями, например список с названиями компаний.

Задача

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

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

с помощью Условного форматирования

Решение

Для начала создадим

Динамический диапазон

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

Динамический диапазон

и нижеследующие формулы не придется модифицировать.

Для создания

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

:

на вкладке

Формулы

в группе

Определенные имена

выберите команду

Присвоить имя

;
в поле

Имя

введите:

Исходный_список

;
в поле

Диапазон

введите формулу

=СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
нажмите ОК.

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

B

с помощью

формулы массива

(см.

файл примера

). Для этого введите следующую формулу в ячейку

B5

:

=ЕСЛИОШИБКА(ИНДЕКС(Исходный_список; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B$4:B4;Исходный_список);0));»»)

После ввода формулы вместо

ENTER

нужно нажать

CTRL + SHIFT + ENTER

. Затем нужно скопировать формулу вниз, например, с помощью

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

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

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

Здесь использование функции

СЧЁТЕСЛИ()

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

Исходный_список

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

B4:B4

(

B4:B5

и т.д.), и 1 если найдено. Например, в ячейке

B5

формулой

СЧЁТЕСЛИ(B$4:B5;Исходный_список)

возвращается массив {1:0:0:0:0:0:0:1:0:0:0:0:1:1:0}. Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» (

B5

). Массив легко увидеть с помощью

клавиши

F9

(выделите в

Строке формул

выражение

СЧЁТЕСЛИ(B$4:B5;Исходный_список)

, нажмите

F9

: вместо формулы отобразится ее результат);
ПОИСКПОЗ()

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

B5

);
ИНДЕКС()

– восстанавливает значение по его позиции в диапазоне

Исходный_список

;
ЕСЛИОШИБКА()

подавляет ошибку, возникающую, когда функция

ПОИСКПОЗ()

пытается в массиве нулей и единиц, возвращенном

СЧЁТЕСЛИ()

, найти 0, которого нет (ситуация возникает в ячейке

B12

, когда все уникальные значения уже извлечены из исходного списка).

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

Примечание

. Функция

ЕСЛИОШИБКА()

будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью

про функцию

ЕСЛИОШИБКА()

. В файле примера имеется лист

Для 2003

, где эта функция не используется.

Решение для списков с пустыми ячейками

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

формулу массива

(см. лист

с пропусками

файла примера

):

=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19; ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0) );»»)

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

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

формул массива

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

файле примера

).

СОВЕТ:

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

Расширенного фильтра

(см. статью

Отбор уникальных строк с помощью Расширенного фильтра

Сводных таблиц

или через меню

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

уникальных

значений автоматически обновлялся.

СОВЕТ2

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

Выпадающий список

, необходимо учитывать, что вышеуказанные формулы возвращают значение

Пустой текст «»

, который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции

СЧЕТЗ()

нужно использовать СЧЕТЕСЛИ() со специальными аргументами

). Например, см. статью

Динамический выпадающий список в MS EXCE

Примечание

: В статье

Восстанавливаем последовательности из списка без повторов в MS EXCEL

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

Источник

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

Смотрите также описание поля в ячейку А1 - только строки с стрелку, как показано при копировании формул. создания и обработки повторяющиеся строки в удалить повторяющиеся строки.Удалить дубликатыВ спискеПримечание: рекомендуется применять болееФильтр включен, о чем ленте в блокеУсловное форматирование нажмите кнопкуПримечание: списке Форматировать все относительно. Таким образом уникальными значениями: на рисунке ниже:

Если Вы хотите найти больших массивов данных. небольших таблицах. Мы Этот процесс может

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

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

в виде перевернутых«Работа с данными»Элемент правила выделения ячеек. можно оперативнее обеспечивать или повторяющиеся. производиться для каждой на одном листе и в контекстномB есть несколько рабочихFind and Replace монотонную и трудоемкую том, сколько повторяющихсяуникальные

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

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

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

повторяющиеся

диапазона. Исходные данные предназначенных для поиска нужно его настроить. Если у вас
., а затем нажмите языке. Эта страница окна Формат ячеек. в этом окне вплотную друг к(Вставить):
вид: огромная таблица), то, все продукты Microsoft задачу. Сегодня мы

 осталось, либо сообщение. при этом не

и удаления дублей. Кликаем по кнопке таблица с шапкой

Введите значения, которые выкнопку ОК

переведена автоматически, поэтомуВыберите формат чисел, перейти еще на
другу (не смежные),Дайте названия столбцам, например,=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),»Unique»,»Duplicate») вероятно, Вы захотите
Office. Для начала разберем несколько удобных о том, чтоВ списке изменятся. У каждого из«Дополнительно» (а в подавляющем
хотите использовать и. ее текст может шрифта, границы или вкладку

то процесс удаления «=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));»Unique»;»Duplicate»)

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

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

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

выберите нужный вариант

Только уникальные записи особенности. Например, условное в той же и есть), то
Расширенное форматирование скопирует на новое грамматические ошибки. Для применить к ячейкам,(Error Alert) сложнее. Мы не» и «
Unique а затем совершить
- планируется работать. в Excel. В удалено. форматирования уникальных или
- и нажмите кнопку форматирование предполагает поиск группе инструментов около параметра
- Выполните следующие действия. место. нас важно, чтобы значение которых отвечает
  
  и ввести текст, можем удалить всюDuplicate?» и « с ними какие-либоОткрыв таблицу, выберите ячейку, качестве примера возьмемСовет: повторяющихся значений.ОК
  
   дублей только по«Сортировка и фильтр»«Мои данные содержат заголовки»Выделите одну или несколькоПри удалении повторяющихся значений эта статья была условию, а затем который будет появляться строку с повторяющимися» Затем откройте вкладкуDuplicate действия, например, удалить, содержимое которой требуется следующую таблицу с Если в диапазоне ячеекВы можете отредактировать существующее. каждой ячейке в.
должна стоять галочка. ячеек в диапазоне, на значения в вам полезна. Просим нажмите кнопку ОК. при попытке ввести значениями, поскольку такData» Вы можете записать выделить цветом или найти и заменить,
данными: или таблице содержится правило, чтобы изменитьПри удалении повторяющихся значений

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

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

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

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

 Устанавливаем в нем

окна расположен список

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

 эффект. Другие значения

помогла ли она

отображаются в поле жмем ОК и столбца тоже. Итак,(Фильтр):
Не найдено одной таблице, быть нужную ячейку и находить и удалять несколько из них, повторяющимся данным. ячеек или таблицы. только искать, но«Только уникальные записи» будет проводиться проверка.Главная вне диапазона ячеек
вам, с помощью Предварительный просмотр.
- наслаждаемся реакцией окружающих чтобы оставить толькоПосле этого нажмите меленькую» и « смежными или не нажмите сочетание клавиш дубликаты строк. Начнем
- снимите флажокВыделите одну или несколько Любые другие значения, и удалять повторяющиеся. Все остальные настройки Строка будет считатьсяв группе или таблице не кнопок внизу страницы. К началу страницы :) уникальные записи в серую стрелку рядомНайдено смежными, могут бытьCtrl+C с поиска повторяющихсяВыделить все ячеек в диапазоне, которые находятся за значения. Наиболее универсальный оставляем по умолчанию. дублем только вСтили
будет изменить или Для удобства такжев столбце присутствуют повторяющиесяПлюс этого способа - столбце с «
«, или оставить только расположены на 2-х. строк. Для этогои выделите только таблице или отчете пределами этого диапазона вариант – это
После этого кликаем случае, если данныещелкните стрелку для переместить. При удалении приводим ссылку на значения, как их
в простоте реализации,АDuplicate? « разных листах илиСкопировав слово, которое необходимо выберите любую ячейку нужные столбцы. сводной таблицы. ячеек или таблицы, создание «умной таблицы». по кнопке всех столбцов, выделенныхУсловного форматирования

support.office.com

Поиск и удаление дубликатов в Microsoft Excel

повторяющихся данных, хранящихся оригинал (на английском убрать? версия экселя а минус -, сделайте следующее:«, чтобы раскрыть менюDuplicate даже в разных найти, воспользуйтесь сочетанием в таблице, аДля наглядного отображения уникальныхНа вкладке не изменяются и При использовании данного«OK» галочкой, совпадут. То

и выберите пункт в первое значение

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

языке) . 2003 в том, чтоОтфильтруйте таблицу так, чтобы фильтра; снимите галочки» и ввести символ книгах.Ctrl+H

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

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

. есть, если выУправление правилами в списке, ноВ Excel существует несколько1 подобную защиту легко отображались только дублирующиеся со всех элементов пробела вместо второго
Представьте, что у нас, чтобы вызвать диалоговое таблицу, нажав к ним можнов группе как данные удаляются точно и удобноПосле этого, повторяющиеся записи снимете галочку с, чтобы открыть других идентичных значений способов фильтр уникальных1 отключить в том значения, и выделите этого списка, кроме значения. В последнем есть 2 столбца окноCtrl+A применить условное форматирование.Стили без возможности восстановления, настроить поиск дубликатов. будут скрыты. Но названия какого-то столбца, всплывающее окно удаляются. значений — или
2 же диалоговом окне эти ячейки. КликнитеDuplicate случае ячейки, для с именами людейFind and Replace. Например, выделение повторяющихсящелкните перед удалением повторяющихся К тому же, их показ можно

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

то тем самымДиспетчер правил условного форматированияПоскольку данные будут удалены

удаление повторяющихся значений:
3 или скопировав и по ним правой, и нажмите которых дубликаты найдены – 5 имён(Поиск и замена).Перейдите на вкладку данных определенным цветомУсловное форматирование
записей рекомендуется скопировать их удаление происходит в любой момент расширяете вероятность признания. окончательно, перед удалениемЧтобы фильтр уникальных значений,4 вставив в наш кнопкой мыши иОК не будут, останутся в столбце Вставьте скопированное словоDate помогает найти ии выберите пункт исходный диапазон ячеек моментально. включить повторным нажатием записи повторной. ПослеВыполните одно из действий,
повторяющихся значений рекомендуется нажмите кнопку4 диапазон ячейки с в контекстном меню. пустыми, и, яA с поле(Данные), а затем (при необходимости) удалитьУправление правилами или таблицу наАвтор: Максим Тютюшев на кнопку того, как все указанных ниже. скопировать исходный диапазонданных >5
дубликатами. Против лома выберитеВот и всё, теперь полагаю, такое представлениеи 3 имениНайти нажмите команду их.

. другой лист илиФильтрация уникальных значений и«Фильтр»

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

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

Сортировка и фильтр >5 нет приема. ДляClear contents Вы видите только

данных наиболее удобно в столбце, нажавRemove DuplicatesВыделите одну или несколькоУбедитесь, что в списке в другую книгу. удаление повторяющихся —.
жмем на кнопку нажмите кнопку в другой лист Дополнительно5 предотвращения таких террористических(Очистить содержимое). те элементы столбца для дальнейшего анализа.BCtrl+V(Удалить дубликаты), как ячеек в диапазоне,
Показать правила форматирования дляПримечание: это две тесноУрок:«OK»Создать правило или книгу..6 действий пользователя придется

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

Нельзя удалить повторяющиеся значения, связанные друг с

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

Расширенный фильтр в Excel.для отображения воВыполните следующие действия.Чтобы удалить повторяющиеся значения,

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

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

АВC повторяющиеся. Как Вы(Параметры), чтобы открыть(Удалить дубликаты). МожноГлавная кнопку или промежуточные итоги.

выполнения отображается список условного форматирования таблицы.

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

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

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

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

лист или таблицаНа вкладкеУдалить повторения быть аналогично) можно

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

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

Откройте вкладку

lumpics.ru

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

на практике ихA имеем дело с(Ячейка целиком). Это в пунктеУсловное форматированиеОКВыделите диапазон ячеек или уникальных значений повторяющиеся«Главная» количество оставшихся уникальныхизменения условного форматирования,Удалить повторения повторяющиеся значения, команда фильтра: Данные -Имеется большой диапозонData встретится намного больше.. Для этого наведите тысячами, а то необходимо сделать, посколькуMy data has headers, выберите пункт. убедитесь в том, значения временно скрываются,, жмем на кнопку записей. Чтобы закрыть начинается. При необходимости(в группеУсловного форматирования Фильтр — Стандартный поиска (1 столбец,(Данные) и нажмитеЧтобы снова отобразить все указатель мыши на и с десятками в некоторых ячейках(Мои данные содержатПравила выделения ячеекВыделите диапазон ячеек или что активная ячейка

 тогда как в«Условное форматирование» данное окно, жмем выберите другой диапазонРабота с даннымив группе фильтр ( в 7500 ячеек) .Sort A to Z строки столбца правый нижний угол тысяч записей. искомые слова находятся заголовки)., а затем — убедитесь в том, находится в таблице. ходе удаления повторяющихся, расположенную в блоке кнопку ячеек, нажав кнопку).стиль меню фильтра уже) Нужно убрать из(Сортировка от АА

ячейкиВариант А:

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

вместе с другимиВ нашем примере нет пункт что активная ячейка
На вкладке значений они удаляются настроек«OK»СвернутьВыполните одно или несколькона вкладке «

Детали — Поставить этого столбца значения,

 до Я). В

, кликните символ фильтра

C1оба столбца находятся

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

Данные без возможности восстановления.

«Стили».во всплывающем окне следующих действий.Главная галку «без повторений», которые повторяются 10 открывшемся диалоговом окне

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

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

Дополнительные параметры

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

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

 скрывает повторяющиеся значения, и оставить соответственноContinue with the current, который теперь выглядит показано на картинкеA ячейки, которые требуется флажок. Сделав это, нажмите кнопку

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

Сортировка и фильтрУдалить дубликаты полностью совпадают со«Правила выделения»Выделяем весь табличный диапазон. ячеек на листе, столбцов. являются две сходныеДмитрий близнов менее 10 раз.

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

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

значениями в другой.иНаходясь во вкладке а затем разверните
Чтобы быстро выделить все задачи, поскольку цель: руками не пробовал? (Макросы, формулы быть указанного выделения) и выберите кнопку мыши, протащите. соответствуют указанным на выделена, а разделВы можете создать правило с элементом
Установите один или несколько Повторяющиеся значения определяются«Повторяющиеся значения…»«Главная» узел во всплывающем столбцы, нажмите кнопку

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

— для представленияAndrey krasnikov может, если да, нажмите кнопкуSelect all границу рамки вниз,Вариант В: рисунке ниже.

Columns для выделения уникальныхФильтр флажков, соответствующих столбцам
значением, которое отображается.жмем на кнопку окне еще разВыделить все списка уникальных значений.: Как нужно сделать, то какие?)Sort
(Выделить все). Либо выделяя все ячейки,Столбцы расположены наТеперь необходимо ввести значение(Колонны) изменится с или повторяющихся значенийи выберите пункт таблицы, и нажмите в ячейке, аОткрывается окно настройки форматирования.«Форматировать как таблицу». Выберите правило
. Есть важные различия, вы руками забейтеM(Сортировка): Вы можете сделать в которые требуется
разных листах. Например, в полеdulpicate на листе определеннымРасширенный фильтр

Изменение правил расширенного условного форматирования

кнопку не тем, которое Первый параметр в, расположенную на ленте и нажмите кнопку

Чтобы быстро удалить все однако: при фильтрации 1 2 3: Условное форматирование уникальных
Удалите столбец с формулой, то же самое вставить формулу. Когда столбецReplace withна цветом. Это особенно.Удалить дубликаты
в ней хранится. нём оставляем без в блоке инструментовИзменить правило
столбцы, нажмите кнопку уникальных значений повторяющиеся 4 последовательно в или повторяющихся значений
он Вам больше через Ленту, нажав все необходимые ячейкиA

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

(Заменить на). ВColumn A полезно, когда вВыполните одно из следующих
. Например, если в изменения –«Стили», чтобы открытьСнять выделение значения будут видны ячейках, потом выделитеПримечание. Условное форматирование не понадобится, с

Data будут выделены, отпустите

на листе

 данном примере мы

, данных содержится несколько

действий:Совет: различных ячейках содержатся«Повторяющиеся»

. В появившемся списке всплывающее окно

. только временно. Тем эту область и полей в области этого момента у(Данные) > кнопку мыши:Sheet2

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

Изменение правила форматированияЕсли диапазон ячеек или не менее удаление тяните до конца «Значения» отчета сводной

Дополнительные параметры

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

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

(Сортировка и фильтр)В больших таблицахA. Введя нужное значение,С ячеек в диапазоне,Фильтрация диапазона ячеек или много столбцов, а («08.12.2010» и «8

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

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

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

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

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

(Заменить все). таблица, нажмитеНа вкладке щелкните
снимите флажок сначала отфильтровать уникальные подходящий для вас для формирования «умнойФорматировать только уникальные или кнопку котором все значения как надо последовательно.Выберите одну илисодержит только уникальные на снимке экрана ячейкуВ Excel 2013, 2010
Можно заметить, что всеOKГлавнаяФильтровать список на месте

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

Выделить все значения или применить цвет, после этого таблицы». Если вы повторяющиеся значенияСнять выделение всех в по крайнейАкимов глеб

несколько ячеек в данные, которых нет ниже:C1
и 2007 есть значения, чтобы удалить дубликаты.в разделе.и выделите только к ним условное жмем на кнопку выделили все правильно,.
и выберите в разделе мере одна строка: Уточни — во области, таблице или в столбцеЕсли пометки «и нажмите встроенный инструментduplicate В нашем случаеФорматКопирование результатов фильтрации в
нужные столбцы. форматирование, чтобы перед«OK» то можно подтверждать,В спискестолбцы идентичны всех значений
всем столбце или отчете сводной таблицы.ВDuplicateCtrl+C

Изменение правил расширенного условного форматирования

Remove Duplicateв ячейках таблицы, все строки сщелкните стрелку рядом другое место

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

support.office.com

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

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

Вариант 1: Команда Удалить дубликаты в Excel

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

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

Ctrl+Shift+End не может сравнивать1 удалении отобразится воСоздать правило, а затем в Например, выделение повторяющихсяПримечание: вы потом при внимание на то,или

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

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

Вариант 2: Расширенный фильтр

помогает найти и разные, а значения вручную стандартным способом.«Таблица с заголовками». на этом этапе. ячейке. Например, если освободившимися ячйками - ячеек. уж сложно. способом… в столбе С) он может только в тексте. Теперь

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

выберите пункт ячейку. их.

считаются повторяющимися. Например, применением условного форматирования её нет, тоФормат Столбец1 и Столбец2, то же значение на что-то. значения. командой сайта office-guru.ru дубликаты, как показаноCtrl+V вариантов, таких как определить строки, которые дубликаты, являетсяКлассическийПримечание:Выделите одну или несколько если в ячейке производится не по следует поставить. Последля отображения во

Вариант 3: Замена

но не Столбец3 даты в разныхКороче — ставьВведите необходимые значенияИсточник: https://www.ablebits.com/office-addins-blog/2013/09/05/compare-two-columns-remove-duplicates/ выше, выделите все(чтобы вставить формулу выделение или изменение имеют повторяющиеся значения.Расширенный фильтр, а затем во При копировании результатов фильтрации ячеек в диапазоне,

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

отфильтрованные ячейки и во все выделенные цвета, не предусмотрено.Чтобы оставить один из. Этот метод также всплывающем меню в другое место таблице или отчете=2-1 а по каждой настройки завершены, жмитеФормат ячеек дубликатов «ключ» —

формате «3/8/2006», аHadidje rayРасширенное форматированиеАвтор: Антон Андронов нажмите ячейки). И точка! дубликатов, достаточно вставить применим к ExcelФорматировать только первые или будут скопированы уникальные сводной таблицы., а в ячейке ячейке в частности, на кнопку. значение ОБА Столбец1 другой — как: Выделить столбец -сортировка.Выберите одну или

Простая задача: есть диапазонCtrl+1Отлично, теперь все повторяющиесяДалее я покажу Вам исходный текст обратно 2003. Чтобы применить последние значения значения из выбранногоНа вкладке A2 — формула поэтому не для

«OK»Выберите номер, шрифт, границы & Столбец2. Если дубликат «8 мар «2006 фильтр-сортировать от А несколько ячеек в ячеек (допустим А1:А10),, чтобы открыть диалоговое значения отмечены как возможные пути сравнения в строку, которая фильтр, необходимо выделитьвыберите пункт диапазона. Исходные данные

Главная=3-2 всех случаев он. «Умная таблица» создана. и заливка формат, находится в этих г. значения должны до Я- услов.

области, таблице или куда пользователь вводит окно « двух столбцов в

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

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

изменятся.Стили
применено одинаковое форматирование,
Урок:

— это только

office-guru.ru

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

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

Установите флажокщелкните такие значения считаютсяУсловное форматирование в Excel один шаг для ячейке удовлетворяет условию включая другие столбцы дубликаты:Рустам хасанов страница в группе всех введенных значений, качестве примера, давайтеВ первой ячейке первого повторяющиеся записи. 1-й строке таблицы.Ctrl+AВ менюТолько уникальные записиУсловное форматирование повторяющимися. Одинаковые значения,Кроме того, найти дубликаты решения нашей главной и нажмите кнопку в таблицу илиПеред удалением повторяющиеся: если цифры проще

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

 ищем дубликаты при содержимым, поочередно выделитеЗатем перейдите на вкладкувыберитеОК пункт различные числовые форматы, с использованием сразу

 дубликатов. Кликаем по. Вы можете выбратьНажмите кнопку для первой попытке ячейку 1 там Условное форматирование, а оно уже присутствует с дубликатами наSheet2 помощи формул их, удерживая клавишуData

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

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

диапазона. При этом Форматы, которые можно, и появится сообщение,
- — или применить черный квадратик зажимаешь Управление правилами.
- было введено ранее. можете изменить цвет это столбец В) находятся на одном
.
- командповторяющиеся
- данные удаляются толькоПовторяющиеся значения
- в ячейке A1 производить поиск дубликатов

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

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

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

заливки при помощи введите такую формулу: листеВыбрав все строки, которыеSort & Filter. из выделенного диапазона. имеет формат по конкретному столбцу. вкладок панели повторяющиеся значения были — для подтверждения вниз и вместо Диспетчер правил условного жмем кнопку инструмента=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),»»,»Duplicate»)Вариант В: столбцы находятся необходимо удалить, щелкните(Сортировка и фильтр)

В меню ячеек или таблицы.В диалоговом окне1,00 Общий вид данной«Работа с таблицами»

предварительного просмотра
удалены или остаются

добиться таких результатов, будет форматирования.Проверка данных (Data Validation)Fill=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));»»;»Duplicate») на разных листах правой кнопкой мыши нажмите командуФорматировать с помощью Любые другие значения,Создать правило форматирования, а в ячейке формулы будет выглядеть. Находясь во вкладке. количества уникальных значений. предполагается, что уникальные1 1Выполните одно изна вкладке(Цвет заливки) наЗдесь
или в разных по заголовку любойAdvancedвыберите нужный вариант которые находятся завыберите нужные параметры A2 — формат следующим образом:«Конструктор»При работе с таблицей Нажмите кнопку значения.1 2 указанных ниже действий.Данные (Data) вкладкеSheet3 книгах из выделенных строк(Дополнительно), как показано форматирования уникальных или пределами этого диапазона и нажмите кнопку1=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;;кликаем по кнопке или базой данных

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

— Excel 2003(Главная), но преимущество листа, на которомПоказать только повторяющиеся строки меню нажмите

Вариант В: два столбца находятся на разных листах (в разных книгах)

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

убедитесь, что активнаяАЛЕКСАНДР1986 Создать правило. и ранее - диалогового окна расположен 2-ой столбец, в столбце АDelete вызовите раскрывающееся меню правило, чтобы изменить не перемещаются. Так
Вы можете создать правилоБолее новые версии будут выводиться дубликаты. ленте в блоке когда некоторые строки
U тменить отменить изменения, ячейка находится в

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

: всем добрый деньПоявится диалоговое окно открываем менюFormat Cells аИзменить цвет или выделить(Удалить). Не стоитData условное форматирование, применяемое как данные удаляются для выделения уникальных Office 2011 

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

Вводим формулу по указанному инструментов повторяются. Это ещё щелкните (или нажать таблице.есть желтая таблица Новое правило форматирования.Данные — Проверка(Формат ячеек) в$A$1:$A$10000

найденные дубликаты нажимать клавишу(Данные), выберите к уникальным или без возможности восстановления,

или повторяющихся значенийВыделите диапазон ячеек или выше шаблону в«Сервис» больше увеличивает массив клавиши Ctrl +Нажмите кнопку не подскажите какДля изменения условного(Data — Validation)

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

Z на клавиатуре).данные > Дополнительно из нее получить форматирования выполните указанные. настроить одновременно все ячеек от 1-ой столбцана клавиатуре, поскольку(Фильтры), а затемВыделите одну или несколько записей рекомендуется скопировать цветом. Это особенно

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

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

Убедитесь, что вПараметры (Settings)Теперь Вы точно не этом 2-ом столбце. (в нашем примере удалится только содержимое(Расширенные фильтры). таблице или отчете или таблицу на

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

запишем вот такую строки полностью.Unique records only

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

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

 описании первого способа. как в программе итоги. Чтобы удалить). повторами необходимый лист или(Allow)Отфильтруйте таблицу так, чтобыB формулу:Сделав это, Вы можете

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

 таблица.выбираем вариант показаны были только(как и в=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),»Unique»,»Duplicate») заметить, что всеПосле нажатияв разделе Нельзя удалить повторяющиеся значения, таблице или отчетенажмите кнопку дубликатов, кроме шапки. производятся в точно и удалить повторяющиеся структуры и промежуточныеРасширенный фильтр: а вкладка данныеТакже можно изменитьДругой ячейки с повторяющимися

варианте А).=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));»Unique»;»Duplicate») оставшиеся строки имеютOKФормат если выделенные фрагмент сводной таблицы.Дополнительно Устанавливаем курсор в таком же порядке.
строки.
итоги. Для получениявыполните одно из удалить дубликаты не диапазон ячеек. Для(Custom) значениями, и выделитеУ нас получается вот
В нашей формуле уникальные значения.в документе будутщелкните стрелку рядом содержит структурированные данныеНа вкладке. конец строки формул.Этот способ является наиболееСкачать последнюю версию дополнительных сведений отображается указанных ниже действий. прокатит? этого нажмите кнопкуи вводим следующую
эти ячейки. такой результат:A1Урок подготовлен для Вас удалены все дубликаты, с кнопкой
или промежуточные итоги.ГлавнаяВыполните одно из указанных Нажимаем на клавиатуре универсальным и функциональным Excel Структура списка данныхЧтобы отфильтровать диапазон ячеек

_Boroda_ Свернуть диалоговое окно формулу в строкуЕсли 2 столбца, которыеОтлично, мы нашли записиэто первая ячейка

командой сайта office-guru.ru за исключением одной
Условное форматирование
Перед удалением повторяющихся

в группе

office-guru.ru

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

ниже действий. кнопку из всех описанныхНайти и удалить значения на листе «и» или таблицы в: Вариант. Формула массива в поле ОтноситсяФормула (Formula) Вы сравниваете, находятся в первом столбце,

первого столбца, которыйИсточник: http://www.howtogeek.com/198052/how-to-remove-duplicate-rows-in-excel/ записи. В нашеми выберите пункт значений нужно удалитьСтилиЗадачаF2 в данной статье. таблицы, которые дублируются,  удалить промежуточные итоги. программе:200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(C$2:C$25;ПОИСКПОЗ(;СЧЁТЕСЛИ(G$1:G1;C$2:C$25);));»») к, чтобы временно: на разных листах  которые также присутствуют мы собираемся сравнивать.Перевел: Антон Андронов  примере осталось двеУправление правилами структуру и промежуточныещелкнитеНеобходимые действия

. Затем набираем комбинацию

Урок: возможно разными способами.

Примечание:Выберитеgling скрыть диалоговое окно,=СЧЁТЕСЛИ($A$1:$A$10;A1), то есть в во втором столбце.$B$1Автор: Антон Андронов записи, поскольку первый. итоги.Условное форматированиеФильтрация диапазона ячеек или клавишКак сделать таблицу в В каждом из Условное форматирование полей вфильтровать список на месте: Здравствуйте. Выделить диапазон—Вкладка затем выберите новыйили в английской версии разных таблицах, кликните Теперь нам нужно иЧтение этой статьи займёт дубликат был обнаруженУбедитесь, что в менюВыделите диапазон ячеек или

и выберите пункт таблицы на местеCtrl+Shift+Enter Excel

этих вариантов поиск области «Значения» отчета. данные—Удалить дубликаты—ОК диапазон ячеек на =COUNTIF($A$1:$A$10;A1) правой кнопкой мыши что-то с ними$B$10000 у Вас около в строке 1.Показать правила форматирования для убедитесь в том,Создать правилоВыделите диапазон ячеек и. Это обусловлено особенностямиДанный способ является не и ликвидация дубликатов сводной таблицы по

Чтобы скопировать в другоекитин листе и нажмите

planetaexcel.ru

Как можно исключить ячейки которые повторяются более 10 раз в excel?

Смысл этой формулы прост выделенный диапазон и делать. Просматривать всеэто адреса первой 10 минут. В Этот метод автоматическивыбран соответствующий лист что активная ячейка. щелкните применения формул к совсем удалением дубликатов, – это звенья

 уникальным или повторяющимся место результаты фильтрации:: и еще одна
кнопку Развернуть диалоговое — она подсчитывает в контекстном меню повторяющиеся записи в и последней ячеек следующие 5 минут
определяет заголовки в
или таблица. находится в таблице.В спискеФильтровать список на месте
массивам. так как сортировка одного процесса. значениям невозможно.Нажмите кнопку формула массива окно.
количество ячеек в выберите
таблице вручную довольно второго столбца, с
Вы сможете легко
таблице. Если ВыВыберите правило и нажмитеНа вкладкеСтиль
.После этих действий в только скрывает повторныеПроще всего удалить дубликатыБыстрое форматированиеКопировать в другое место200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$25;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ($C$2:$C$25;$C$2:$C$25;0)=СТРОКА($C$2:$C$25)-1;СТРОКА($C$2:$C$25)-1);СТРОКА(A1)));»»)
Выберите правило, а диапазоне A1:A10 равныхDelete Row
неэффективно и занимает которым будем выполнять
сравнить два столбца хотите удалить первую кнопку
Данныевыберите пункт
Копирование результатов фильтрации в столбце записи в таблице.
– это воспользоватьсяВыполните следующие действия..Pelena затем нажмите кнопку
содержимому ячейки А1.(Удалить строку): слишком много времени. сравнение. Обратите внимание в Excel и строку, Вам придетсяИзменить правилов разделеКлассический другое место«Дубликаты»Выделяем таблицу. Переходим во
специальной кнопкой наВыделите одну или несколькоВ поле
: Почему в теме Изменить правило.
Ввод будет разрешенНажмите Существуют пути получше. на абсолютные ссылки узнать о наличии
удалить ее вручную..Сервис, а затем вВыделите диапазон ячеек, щелкните
отобразятся повторяющиеся значения. вкладку ленте, предназначенной для
ячеек в диапазоне,Копировать написано «удалить всеПоявится диалоговое окно только в теОКЕсли Ваши столбцы не
– буквам столбца в них дубликатов, Когда в 1-йВыберите нужные параметры и
нажмите кнопку

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

спискеСкопировать результат в другоеНо, данный способ все-таки«Данные»
этих целей.
таблице или отчете
введите ссылку на
НЕ повторяющиеся»? %)
Изменение правила форматирования.
ячейки, где полученное
, когда Excel попросит
имеют заголовков, то
и номерам строки
удалить их или

 строке будут заголовки, нажмите кнопкуУдалить дубликатыФорматировать только первые или место слишком сложен для. Жмем на кнопкуВыделяем весь табличный диапазон. сводной таблицы. ячейку.(Невнимательность_ТС) * (невнимательность_отвечающих)В разделе Выберите число меньше или Вас подтвердить, что их необходимо добавить. предшествует знак доллара выделить цветом. Итак,

 а не дубликаты,ОК

. последние значения, а затем в большинства пользователей. К«Фильтр» Переходим во вкладкуНа вкладкеКроме того нажмите кнопку = все_нормально тип правила выберите равно 1. Причем Вы действительно хотите Для этого поместите

 ($). Я использую время пошло! останется только одна.Установите один или нескольковыберите пункт поле
тому же, он, расположенную в блоке«Данные»ГлавнаяСвернуть диалоговое окно
Минус на минус пункт Форматировать только

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

Работая с Microsoft Excel флажков, соответствующих столбцамФорматировать только уникальные илиКопировать в предполагает только поиск настроек. Жмем на кнопкув группевременно скрыть всплывающее
…
уникальные или повторяющиеся
(абсолютными ссылками со
листа и после

удалить все повторяющиеся значения (Формулы/Formulas)

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

«Удалить дубликаты»стиль окно, выберите ячейкуАЛЕКСАНДР1986

 значения. знаками $), а
этого очистите фильтр.

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

 когда необходимо найти ситуация, когда необходимо кнопку.

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

 ссылка на текущую Как видите, остались

excelworld.ru

превратится в чёрную

Источник

Хитрости »

1 Май 2011 532147 просмотров

Как получить список уникальных(не повторяющихся) значений?

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись — т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA) и сводных таблиц. В этой статье рассмотрим каждый из вариантов.

При помощи встроенных возможностей Excel 2007 и выше
При помощи Расширенного фильтра
При помощи формул
При помощи кодов Visual Basic for Application(VBA) — макросы, включая универсальный код выборки из произвольного диапазона
При помощи сводных таблиц

при помощи встроенных возможностей Excel 2007 и выше

В Excel 2007 и 2010 это сделать проще простого — есть специальная команда, которая так и называется — Удалить дубликаты (Remove Duplicates). Расположена она на вкладке Данные (Data) подраздел Работа с данными (Data tools)

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (Data) —Удалить дубликаты (Remove Duplicates).

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

Появится окно с параметрами удаления дубликатов

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

Способ 1: Расширенный фильтр

В случае с Excel 2003 все посложнее. Там нет такого инструмента, как Удалить дубликаты. Но зато есть такой замечательный инструмент, как Расширенный фильтр. В 2003 этот инструмент можно найти в Данные —Фильтр —Расширенный фильтр. Прелесть этого метода в том, с его помощью можно не портить исходные данные, а создать список в другом диапазоне.

В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на вкладке Данные (Data), группа Сортировка и фильтр (Sort & Filter) — Дополнительно (Advanced)

Как его использовать: запускаем указанный инструмент — появляется диалоговое окно:

Обработка: Выбираем Скопировать результат в другое место (Copy to another location).
Исходный диапазон (List range): Выбираем диапазон с данными(в нашем случае это А1:А51).
Диапазон критериев (Criteria range): в данном случае оставляем пустым.
Поместить результат в диапазон (Copy to): указываем первую ячейку для вывода данных — любую пустую(на картинке — E2).
Ставим галочку Только уникальные записи (Unique records only).
Жмем Ок.

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

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

Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте (Filter the list, in-place).

Способ 2: Формулы

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

(

А1:А51

, где

А1

— заголовок). Выводить список мы будем в столбец

, начиная с ячейки

С2

. Формула в

будет следующая:

{=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))}
{=INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1))}
Детальный разбор работы данной формулы приведен в статье: Как просмотреть этапы вычисления формул

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

Ctrl

Shift

Enter

(при этом сами скобки вводить не надо — они появятся сами после ввода формулы тремя клавишами

Ctrl

Shift

Enter

). После того, как мы ввели эту формулу в

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

#ЧИСЛО!(#NUM!)

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

для Excel 2007 и выше:
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»)}
{=IFERROR(INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»)}
для Excel 2003:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»;ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1)))}
{=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»;INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1)))}

Тогда вместо ошибки

#ЧИСЛО!(#NUM!)

у вас будут пустые ячейки(не совсем пустые, конечно — с формулами :-)).
Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье: Как в ячейке с формулой вместо ошибки показать 0

Для пользователей Excel 2021 выше, а так же пользователей Excel 365(с активной подпиской) — использовать формулы для извлечения уникальных элементов проще простого. В этих версиях появилась функция

УНИК(UNIQUE)

, которая как раз получает список уникальных значений на основании переданного диапазона:

=УНИК($A$2:$A$51)
=UNIQUE($A$2:$A$51)

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

Способ 3: код VBA

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

Что такое макрос и где его искать? к статье приложен видеоурок
Что такое модуль? Какие бывают модули? потребуется, чтобы понять куда вставлять приведенные ниже коды

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

Исходные данные оставим в том же порядке — список с данными расположен в столбце «А«(А1:А51, где А1 — заголовок). Только выводить список мы будем не в столбец С, а в столбец Е, начиная с ячейки Е2:

Sub Extract_Unique()
    Dim vItem, avArr, li As Long
    ReDim avArr(1 To Rows.Count, 1 To 1)
    With New Collection
        On Error Resume Next
        For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
            'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А
            .Add vItem, CStr(vItem)
            If Err = 0 Then
                li = li + 1: avArr(li, 1) = vItem
            Else: Err.Clear
            End If
        Next
    End With
    If li Then [E2].Resize(li).Value = avArr
End Sub

С помощью данного кода можно извлечь уникальные не только из одного столбца, но и из любого диапазона столбцов и строк. Если вместо строки
Range(«A2», Cells(Rows.Count, 1).End(xlUp)).Value
указать Selection.Value, то результатом работы кода будет список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить — вместо [E2] поставить ту, в которой данных нет.
Так же можно указать конкретный диапазон:

Или другой столбец:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value

здесь отдельно стоит обратить внимание то, что в данном случае помимо изменения А2 на С2 изменилась и цифра 1 на 3. Это указание на номер столбца, в котором необходимо определить последнюю заполненную ячейку, чтобы код не просматривал лишние ячейки. Подробнее про это можно прочитать в статье: Как определить последнюю ячейку на листе через VBA?

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

Sub Extract_Unique()
    Dim x, avArr, li As Long
    Dim avVals
    Dim rVals As Range, rResultCell As Range
 
    On Error Resume Next
    'запрашиваем адрес ячеек для выбора уникальных значений
    Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений", "Запрос данных", "A2:A51", Type:=8)
    If rVals Is Nothing Then 'если нажата кнопка Отмена
        Exit Sub
    End If
    'если указана только одна ячейка - нет смысла выбирать
    If rVals.Count = 1 Then
        MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
    'отсекаем пустые строки и столбцы вне рабочего диапазона
    Set rVals = Intersect(rVals, rVals.Parent.UsedRange)
    'если указаны только пустые ячейки вне рабочего диапазона
    If rVals Is Nothing Then
        MsgBox "Недостаточно данных для выбора значений", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
    avVals = rVals.Value
    'запрашиваем ячейку для вывода результата
    Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8)
    If rResultCell Is Nothing Then 'если нажата кнопка Отмена
        Exit Sub
    End If
    'определяем максимально возможную размерность массива для результата
    ReDim avArr(1 To Rows.Count, 1 To 1)
    'при помощи объекта Коллекции(Collection)
    'отбираем только уникальные записи,
    'т.к. Коллекции не могут содержать повторяющиеся значения
    With New Collection
        On Error Resume Next
        For Each x In avVals
            If Len(CStr(x)) Then 'пропускаем пустые ячейки
                .Add x, CStr(x) 'если добавляемый элемент уже есть в Коллекции - возникнет ошибка
                'если же ошибки нет - такое значение еще не внесено,
                'добавляем в результирующий массив
                If Err = 0 Then
                    li = li + 1
                    avArr(li, 1) = x
                Else
                    'обязательно очищаем объект Ошибки
                    Err.Clear
                End If
            End If
        Next
    End With
    'записываем результат на лист, начиная с указанной ячейки
    If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr
End Sub

Способ 4: Сводные таблицы

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

Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка(Insert) -группа Таблица(Table) —Сводная таблица(PivotTable)
В диалоговом окне Создание сводной таблицы(Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
указываем место размещения Сводной таблицы:
- На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
подтверждаем создание нажатием кнопки OK

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

выделил диапазон A1:B51 на листе Извлечение по критерию
вызвал меню вставки сводной таблицы: вкладка Вставка(Insert) -группа Таблица(Table) —Сводная таблица(PivotTable)
выбрал вставить на новый лист(New Worksheet)
назвал этот лист Уникальные сводной таблицей
поле Данные поместил в область строк
поле ФИО в область фильтра. Почему? Чтобы удобно было выбирать одно или несколько ФИО и в сводной отображался бы список уникальных месяцев только для выбранных фамилий

В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши —Обновить(Refresh) или вкладка Данные(Data) —Обновить все(Refresh all) —Обновить(Refresh). А если исходные данные пополняются динамически и того хуже — надо будет заново указывать диапазон исходных данных. И еще один минус — данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.

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

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

Скачать пример:

Tips_All_ExtractUnique.xls (108,0 KiB, 18 431 скачиваний)

Также см.:
Работа с дубликатами
Как подсчитать количество повторений
Общие сведения о сводных таблицах

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

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

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

Как найти и выделить

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

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

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

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

Как удалить

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

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

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

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

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

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

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

Как посчитать

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

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

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

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

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

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

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

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

Загрузка…

Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

Источник

Найти и выделить цветом дубликаты в Excel
Формула проверки наличия дублей в диапазонах
- Внутри диапазона
!SEMTools, поиск дублей внутри диапазона
- Найти дубли ячеек в столбце, кроме первого
- Найти в столбце дубли ячеек, включая первый
- Найти дубли в столбце без учета лишних пробелов

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

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

Ключевых моментов несколько:

Какие конкретно повторяющиеся значения — повторы слов в ячейках, сами повторяющиеся ячейки или повторяющиеся строки?
Если ячейки, то:
- Какие ячейки мы готовы считать дубликатами — все кроме первой или включая ее?
- Считаем ли дублями строки, отличающиеся только пробелами до/после слов или лишними пробелами между словами?
- Где мы будем искать дубли — в одном столбце, в двух столбцах или в нескольких?
- А может, нам нужно найти неявные дубли?

Сначала рассмотрим простые примеры.

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

Найти инструмент можно на вкладке программы “Главная”:

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

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

Процедура интуитивно понятна:

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

Второй шаг процедуры – выбор цветов заливки и текста

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

Неочевидный результат работы условного форматирования с дублями внутри диапазона

Условное форматирование меняет цвет дубликатов внутри столбца

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

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

Но есть и другие решения. О них дальше.

Формула проверки наличия дублей в диапазонах

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

Внутри диапазона

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0

Так выглядит на практике применение формулы:

Формула возвращает ИСТИНА, если в адресованном диапазоне появляется дубликат

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

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ .Это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

=СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1

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

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

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

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

Обратите внимание на один момент в этой демонстрации: диапазон закреплен ($A$1:$B$4), а искомая ячейка (A1) нет. Именно это позволяет условному форматированию находить все дубликаты в диапазоне.

!SEMTools, поиск дублей внутри диапазона

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

Давайте покажу, как они работают.

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

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

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

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

Найти дубли в столбце без учета лишних пробелов

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

Для первой операции есть отдельный инструмент «Удалить лишние пробелы»:

Как найти дубли ячеек, не учитывая лишние пробелы

Найти повторяющиеся значения в Excel и решить сотни других задач поможет надстройка !SEMTools.

Скачайте прямо сейчас и убедитесь сами!

Смотрите также:

Удалить дубли без смещения строк;
Удалить неявные дубли;
Найти повторяющиеся слова в Excel;
Удалить повторяющиеся слова внутри ячеек.

Источник

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

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

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

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

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

Читайте также на Комьюнити: Условное форматирование в Microsoft Excel.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

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

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

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

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

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

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

Источник

Содержание

Как удалить дубликаты в Excel
Удаление дублирующихся строк вручную
Удаление повторений при помощи «умной таблицы»
Применение расширенного фильтра
Как убрать дубликаты строк с помощью формул.
Формулы для поиска повторяющихся строк.
Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

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

Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.

Устранение дубликатов на основе значений колонки с текстом:

Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:

Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».

В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.

Удаление дублирующихся строк вручную

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

Для начала необходимо выделить все ячейки таблицы: зажимаем ЛКМ и выделяем всю область ячеек.
Сверху в панели инструментов нужно выбрать раздел «Данные», чтобы получить доступ ко всем необходимым инструментам.
Внимательно рассматриваем доступные значки и выбираем тот, который имеет два столбца ячеек, раскрашенных в разные цвета. Если навести на этот значок курсор, то высветится наименование «Удалить дубликаты».
Чтобы эффективно использовать все параметры этого раздела, достаточно быть внимательным и не торопиться с установками. К примеру, если таблица имеет «Шапку», то обязательно обратите внимание на пункт «Мои данные содержат заголовки», в нем обязательно должна стоять галочка.

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

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

Указываем нужную информацию в окне работы

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

Подтверждаем полученную информацию

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

Удаление повторений при помощи «умной таблицы»

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

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

Выделяем нужный диапазон таблицы

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

Переходим в панель инструментов для работы со стилем таблицы

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

Проверяем и подтверждаем информацию по диапазону таблицы

Осталось только приступить к поиску и дальнейшему удалению дублированных строк. Чтобы сделать это, необходимо выполнить дополнительные действия:
- поставьте курсор на произвольную ячейку таблицы;
- в верхней панели инструментов нужно выбрать раздел «Конструктор таблиц»;
- ищем значок в виде двух столбцов ячеек с разным цветом, при наведении на которые будет высвечиваться надпись «Удалить дубликаты»;
- выполните действия, которые мы указали в первом методе после использования данного значка.

Удаляем найденные дубликаты

Обратите внимание! Данный метод обладает уникальным свойством – благодаря нему можно будет работать с таблицами разного диапазона без каких-либо ограничений. Любая выделенная область во время работы с Excel будет подвергаться тщательному анализу на дубликаты.

Применение расширенного фильтра

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

Чтобы использовать фильтр, необходимо:

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

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

Как убрать дубликаты строк с помощью формул.

Преимущество этого подхода заключается в универсальности: он позволяет вам:

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

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

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

Добавляем еще одну колонку, в которой запишем формулу.

Повторы наименований товаров, без учета первого вхождения:

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

Отмечаем все повторы вместе с первым появлением:

=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$17; $B2)>1; «Дубликат»; «Уникальный»)

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

Вот небольшая пошаговая инструкция.

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

Формулы для поиска повторяющихся строк.

Отмечаем при помощи формулы неуникальные строчки, кроме 1- го вхождения:

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

В результате видим 2 повтора.

Если нам нужно исключить все повторяющиеся строки вместе с их первым появлением:

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

Далее вновь устанавливаем фильтр и действуем аналогично описанному выше.

Насколько удобен этот метод – судить вам.

Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

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

Он безупречно работает во всех операционных системах и во всех версиях Microsoft Excel 2019 — 2003.

Выберите любую ячейку в таблице, c которой вы хотите работать, и нажмите Quick Dedupe на вкладке Ablebits Data.

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

Поскольку моя цель – просто выделить повторяющиеся данные, я выбрал «Закрасить цветом».

Помимо выделения цветом, вам доступны и другие операции:

Удалить дубликаты
Выбрать дубликаты
Указать их в столбце статуса
Копировать дубликаты на новый лист
Переместить на новый лист

Нажимаем кнопку OK и оцениваем получившийся результат:

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

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

Удаление дубликатов в Excel — обычная операция. Однако в каждом конкретном случае может быть ряд особенностей. В то время как инструмент Quick Dedupe фокусируется на скорости, Duplicate Remover предлагает ряд дополнительных опций для работы с дубликатами и уникальными значениями.

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

Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.
В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:

Все ваши данные будут автоматически выделены.
Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы выбираем первые 3 столбца:

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

Чтобы более наглядно увидеть результат, отметим параметр «Закрасить цветом» (Fill with color) и нажимаем Готово.

Источники

https://exceltut.ru/udalenie-dublikatov-v-excel-s-pomoshhyu-tablits/
https://office-guru.ru/excel/kak-najti-i-udalit-dublikaty-v-excel-5-metodov-poiska-i-udaleniya-dublikatov-v-excel.html
https://ichip.ru/sovety/ekspluataciya/udalenie-povtoryayushchihsya-strok-v-eksel-samye-bystrye-metody-732582
https://mister-office.ru/excel/remove-duplicate-rows-values.html

Источник

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

Удаление дубликатов в «умной таблице».

Избавьтесь от повторов, скопировав уникальные записи в другое место.

Как убрать дубликаты строк с помощью формул.

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

Формулы для поиска повторяющихся строк.

Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

Как избавиться от дубликатов в Excel в 2 клика мышки.

Больше возможностей для поиска дубликатов при помощи Duplicate Remover.

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

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

См. также

Нужна дополнительная помощь?

Задача

Решение

Решение для списков с пустыми ячейками

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

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

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

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

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

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

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

Поиск и удаление дубликатов в Microsoft Excel

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

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

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

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

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

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

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

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

Дополнительные параметры

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

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

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

Изменение правил расширенного условного форматирования

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

Дополнительные параметры

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

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

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

Изменение правил расширенного условного форматирования

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

Вариант 1: Команда Удалить дубликаты в Excel

Вариант 2: Расширенный фильтр

Вариант 3: Замена

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

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

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

Вариант В: два столбца находятся на разных листах (в разных книгах)

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

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

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

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

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

Как можно исключить ячейки которые повторяются более 10 раз в excel?

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

удалить все повторяющиеся значения (Формулы/Formulas)

Как получить список уникальных(не повторяющихся) значений?

Поиск по меткам

Как найти и выделить

Как удалить

Как посчитать

Формула проверки наличия дублей в диапазонах

Внутри диапазона

!SEMTools, поиск дублей внутри диапазона

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

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

Найти дубли в столбце без учета лишних пробелов

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

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

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

Удаление дублирующихся строк вручную

Удаление повторений при помощи «умной таблицы»

Применение расширенного фильтра

Как убрать дубликаты строк с помощью формул.

Формулы для поиска повторяющихся строк.

Duplicate Remover — универсальный инструмент для поиска и удаления дубликатов в Excel.

А вот еще интересные статьи: