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

Содержание

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

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

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

  1. Выберите ячейки, которые нужно проверить на наличие повторений. …
  2. На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.
  3. В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.

Как найти одинаковые значения в разных столбцах Excel?

Как найти одинаковые значения в столбце Excel

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

Как найти одинаковые значения в ячейках Excel 2003?

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

Читайте также  Как заменить разделитель в Excel?

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

Как узнать количество уникальных значений в столбце Excel?

Подсчёт уникальных значений в Excel

  1. Воспользуемся функцией COUNTIF (СЧЁТЕСЛИ). Для примера подсчитаем количество чисел 5 в диапазоне A1:A6, используя следующую формулу: …
  2. Чтобы подсчитать уникальные значения, добавим функцию SUM (СУММ), 1/, и заменим 5 на A1:A6. …
  3. Закончим ввод формулы нажатием Ctrl+Shift+Enter.

Как в Excel выделить ячейки цветом по условию?

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

  1. Выделите диапазон ячеек A2:A15 (то есть список номеров клиентов) и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». …
  2. В этот раз в поле ввода введите формулу:
  3. Щелкните на кнопку «Формат» и укажите красный цвет на вкладке «Заливка».

Как посчитать сколько раз повторяется число в Excel?

Чтобы подсчитать, сколько раз в диапазоне встречается отдельное значение, используйте функцию СЧЁТЕСЛИ . Например, чтобы посмотреть, сколько раз значение 70 встречается в диапазоне A2–A40, используйте формулу = СЧЁТЕСЛИ( A2:A40;70) .

Как найти совпадения в таблицах Excel?

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

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

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

Читайте также  Как определить цвет заливки в Excel?

Как сравнить два списка с помощью Впр?

Использование формулы подстановки ВПР

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

Как удалить повторяющиеся строки в Excel 2003?

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

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

Как сгруппировать одинаковые значения в Excel?

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

  1. Перейти к щелчку Данные > Консолидировать, см. …
  2. После завершения настройки нажмите OK, а дубликаты объединяются и суммируются.

Как в Excel сделать поиск по слову?

Для этого можно нажать клавиатурную комбинацию CTRL + F (от англ. Find), откроется окно поиска слов. Для нажатия клавиатурной комбинации, нажмите клавишу клавиатуры CTRL и, удерживая ее, нажмите клавишу F (на английский язык переходить не нужно).

Как в Excel найти уникальные значения?

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

Как посчитать количество значений в столбце Excel?

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

Как посчитать количество слов в столбце Excel?

Посчитать слова внутри одной ячейки вы можете, воспользовавшись формулой «=ДЛСТР(ячейка)-ДЛСТР(ПОДСТАВИТЬ(ячейка;» «;»))+1». Вместо слова «ячейка» необходимо подставить номер ячейки, в которой вы хотите подсчитать количество слов, например, А3.

Поиск одинаковых значений в Excel

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

На рисунке – списки писателей. Алгоритм действий следующий:

  • Выбрать ячейку I3 с записью «С. А. Есенин».
  • Поставить задачу – выделить цветом ячейки с такими же записями.
  • Выделить область поисков.
  • Нажать вкладку «Главная».
  • Далее группа «Стили».
  • Затем «Условное форматирование»;
  • Нажать команду «Равно».

image

  • Появится диалоговое окно:

image

  • В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
  • В правом поле можно выбрать цвет шрифта.
  • Нажать «ОК».

В таблицах отмечены цветом ячейки, значение которых равно заданному.

Читайте также:  Особенности работы приложения Whatsapp на Windows Phone

Решение

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

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

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите: Исходный_список ;
  • в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$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 , где эта функция не используется.

Ищем в таблицах Excel все повторяющиеся значения

Отметим все неуникальные записи в выделенной области. Для этого нужно:

  • Зайти в группу «Стили».
  • Далее «Условное форматирование».
  • Теперь в выпадающем меню выбрать «Правила выделения ячеек».
  • Затем «Повторяющиеся значения».
  • Появится диалоговое окно:
  • Нажать «ОК».

Программа ищет повторения во всех столбцах.

Читайте также:  Развертывание Microsoft Active Directory на Windows Server 2020

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

Удаление одинаковых значений из таблицы Excel

Способ удаления неуникальных записей:

  1. Зайти во вкладку «Данные».
  2. Выделить столбец, в котором следует искать дублирующиеся строки.
  3. Опция «Удалить дубликаты».

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

Список с уникальными значениями:

Расширенный фильтр: оставляем только уникальные записи

Расширенный фильтр – это инструмент для получения упорядоченного списка с уникальными записями.

  • Выбрать вкладку «Данные».
  • Перейти в раздел «Сортировка и фильтр».
  • Нажать команду «Дополнительно»:
  • В появившемся диалоговом окне ставим флажок «Только уникальные записи».
  • Нажать «OK» – уникальный список готов.

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

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

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

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

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

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

Читайте также:  Как отключить instant apps на андроиде 5.1. Instant Apps что это за программа на Андроид? Как активировать функцию Excel выполняет процедуру поиска и удаления дубликатов. После её завершения появляется информационное окно, в котором сообщается, сколько повторных значений было удалено и количество оставшихся уникальных записей. Чтобы закрыть данное окно, жмем кнопку «OK».

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

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

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

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

    Совпадение в столбцах Excel

    Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.

    Задача 1: Есть 6 текстов в 6 ячейках. Необходимо узнать, какие из них уникальные, а какие повторяются.

    Использовать будем Условное форматирование.

    • Выбираем ячейки, которые необходимо сравнить;
    • Во вкладке Главная переходим «Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения»;

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

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

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

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

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

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

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

    Как скачать видеоурок с сайта

    Не забудьте поделиться ссылкой на статью ⇒

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

    В сегодняшнем уроке я расскажу как вставить формулу в Word. На самом деле существует несколько способов это сделать и по возможности я постараюсь рассмотреть их все.

    Читайте также:  Как создать и настроить канал: пошаговая инструкция

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

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

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

    Источник: https://4upc.ru/materials/show/sovpadenie-v-stolbcah-excel

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

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

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

    Для того чтобы удалит дубликаты строк, необходимо:

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

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

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

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

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

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

    Используем фильтрацию

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

      Синий экран смерти: причины возникновения ошибки и его исправление

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

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

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

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

    Чтобы найти дубликаты в Excel, необходимо:

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

    Использование формулы

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

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

    1. Выбрать нужные ячейки.
    2. В разделе «Условное форматирование» создать новое правило.
    3. Выбрать «Использовать формулу для определения форматируемых ячеек».
    4. Вставить следующую формулу «=ПОИСКПОЗ(B3;$B:$B;0)<>СТРОКА(B3)» и задать формат (цвет, шрифт и т.д.).

    Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».

    1. Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.

    Копирование уникальных строк в новое расположение

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

      Несколько вариантов отключения DEP в Windows

    Для выполнения, потребуется:

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

    Используем сводные таблицы

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

    Для работы этого способа потребуется:

    1. Создать новый столбец со значением счетчика «1». Выбрать всю область.
    2. Используя закладку «Вставка», создать новую сводную таблицу.
    3. Перейти на новый лист и заполнить поля «Названия строк» и «Значения». Дубли будут подсчитаны мгновенно и отображаться будут на новом листе.

    Кроме этого можно ознакомиться с видео по удалению дублей в Excel.

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

    В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):

    • используя сводные таблицы (подсчет дублированных значений);
    • с помощью функции unique (вывод результата из массива);
    • используя сторонние расширения;

    Пример использования функции «unique»:

    Для вывода уникальных записей, требуется применить формулу «=unique(диапазон проверки)»:

    Читайте также:

    Блог > Microsoft Office

    Изображение 1. Удаление одинаковых строчек в Excel.

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

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

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

    Шаг 1.

    • После того, как Вы открыли тестовую таблицу в редакторе, зажмите левую клавишу мыши и выделите столбцы и строки, где требуется удалить повторы.
    • Если это необходимо, то выделите всю таблицу таким же образом или с помощью комбинации клавиш «Ctrl+A».

    Изображение 2. Выделение рабочей области.

    Шаг 2.

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

    Изображение 3. Выбор инструмента для работы с таблицей.

    Шаг 3.

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

    Изображение 4. Выбор столбцов и запуск процесса фильтрации.

    Шаг 4.

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

    Изображение 5. Окно с конечным результатом.

    Шаг 5.

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

    Изображение 6. Отмена последнего сделанного действия.

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

    Шаг 1.

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

    Изображение 7. Переход к дополнительным фильтрам данных.

    Шаг 2.

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

    Изображение 8. Настройка фильтра данных.

    Шаг 3.

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

    Изображение 9. Выбор места для вставки таблицы.

    Шаг 4.

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

    Изображение 10. Завершение настройки фильтра и начало процесса его работы.

    Шаг 5.

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

    Изображение 11. Полученный конечный результат.

    Шаг 6.

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

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

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

    Шаг 1.

    • На главной вкладке редактора Excel необходимо перейти в раздел «Стили» и там раскрыть контекстное меню «Условное форматирование».
    • Далее наведите курсор на пункт «Правила выделения ячеек» и в следующем списке выберите строчку «Повторяющиеся значения».

    Изображение 13. Переход к инструменту стилизации ячеек.

    Шаг 2.

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

    Изображение 14. Настройка стиля дублирующихся ячеек.

    Шаг 3.

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

    Изображение 15. Ручное удаление подкрашенных ячеек с дублями.

    ВИДЕО: Как удалить повторяющие строки в Excel?

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

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

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

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

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

    *

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

    Смотрите также: “Как убрать колонтитулы в Экселе”

    Читайте также:  Наложение текста на видео: как создать статичную или анимированную надпись в ролике

    Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

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

      image

    3. Переходим к настройкам параметров удаления дубликатов:
      • Ниже, в основном окне, перечислены названия столбцов, по которым будет осуществляться поиск дубликатов. Система считает совпадением ситуацию, в которой в строках повторяются значения всех выбранных в настройке столбцов. Если убрать часть столбцов из сравнения, повышается вероятность увеличения количества похожих строк.
      • Тщательно все проверяем и нажимаем ОК.

        image

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

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

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

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

      Создаем сводную таблицу Обратите внимание на скриншот, где показан пример такого метода.

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

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

      Метод 2: удаление повторений при помощи “умной таблицы”

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

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

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

        Читайте также:  Как восстановить несохраненные документы Microsoft Word

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

        Другой способ удалить дубликаты в Excel — это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

        1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
        2. Перейдите во вкладку «ДАННЫЕ» —> группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно».
        Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов
        1. В диалоговом окне «Расширенный фильтр» выполните следующие действия:
        • Выберите пункт «скопировать результат в другое место».
        • Проверьте, отображается ли правильный диапазон в Исходном диапазоне. Это должен быть диапазон, выбранный на шаге 1.
        • В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
        • Выберите Только уникальные записи
        Удалить дубликаты в Excel – Фильтр дубликатов
        1. Наконец, нажмите «ОК». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:
        Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

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

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

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

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

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

          Метод 4: условное форматирование

          Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая инструкция”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

          Метод 5: формула для удаления повторяющихся строк

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

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

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

          Давайте посмотрим, как с ней работать на примере нашей таблицы:

          1. Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
          2. В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»»).
          3. Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
            • жмем служебную клавишу F2 на клавиатуре;
            • затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
            • Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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

            Оцените статью
            Рейтинг автора
            5
            Материал подготовил
            Илья Коршунов
            Наш эксперт
            Написано статей
            134
            А как считаете Вы?
            Напишите в комментариях, что вы думаете – согласны
            ли со статьей или есть что добавить?
            Добавить комментарий