Функция удалить дубликаты excel

Функция удалить дубликаты excel

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

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

Способ 1. Ищем повторения: текст по столбцам и формула массива

Это не самый удобный и быстрый, но зато самый простой вариант решения задачи "на коленке". Выделим исходный список и разобъем его на столбцы по пробелам с помощью команды Данные — Текст по столбцам (Data — Text to columns) . В открывшемся окне трёхшагового Мастера выберем формат По разделителю (By delimiter) на первом шаге и поставим флажок Пробел (Space) на втором:

Если в исходных данных могут быть лишние пробелы, то лучше включить и опцию Считать последовательные разделители одним (Treat consecutive delimiters as one) — это избавит нас от лишних столбцов.

На третьем шаге в поле Поместить в зададим пустую ячейку рядом с таблицей, чтобы результаты не затёрли нам исходные данные и нажмём на Готово (Finish) :

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

В английской версии это будет =SUMPRODUCT(N(COUNTIF(B2:G2,B2:G2)>1))

Давайте разберём логику её работы на примере первой строки.

  1. Сначала мы с помощью формулы СЧЁТЕСЛИ( B2:G2 ; B2:G2 ) вычисляем по очереди количество вхождений каждого имени в диапазон B2:G2 и получаем на выходе массив <1,2,1,2,1>, т.к. Иван встречается в первой строке 1 раз, Елена — 2 раза, Сергей — 1 и т.д.
  2. Проверяем с помощью СЧЁТЕСЛИ(B2:G2;B2:G2) >1 какие из полученных чисел больше единицы, т.е. где у нас повторы. На выходе эта формула выдаст нам массив результатов проверки в виде .
  3. Переводим логические значения ЛОЖЬ и ИСТИНА в более удобные для подсчета 0 и 1, соответственно, с помощью функции Ч. На выходе получаем массив <0,1,0,1,0>.
  4. Суммируем все элементы получившегося массива функцией СУММПРОИЗВ. Можно было бы использовать и обычную функцию СУММ, но тогда пришлось бы жать вместо привычного Enter сочетание клавиш Ctrl + Shift + Enter , чтобы ввести формулу как формулу массива.

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

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

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

Если дубликаты нужно именно наглядно показать, то удобнее будет использовать для этого специальный макрос. Откроем редактор Visual Basic одноимённой кнопкой на вкладке Разработчик (Developer — Visual Basic) или сочетанием клавиш Alt + F11 . Вставим в книгу новый пустой модуль через меню Insert — Module и скопируем туда вот такой код:

Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:

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

Способ 3. Выводим повторы в соседний столбец

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

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

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

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

Способ 5. Удаление повторов внутри ячейки через Power Query

Этот способ использует бесплатную надстройку Excel для обработки данных под названием Power Query. Для Excel 2010-2013 скачать её можно с сайта Microsoft, а в Excel 2016-2019 она уже встроена по умолчанию. Огромным плюсом этого варианта является возможность автоматического обновления — если в будущем исходные данные изменятся, то нам не придется заново проделывать всю обработку (как в Способе 1) или запускать макрос (как в Способе 4) — достаточно будет просто обновить созданный запрос.

Сначала наши данные нужно загрузить в Power Query. Проще всего для этого превратить нашу таблицу в "умную" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home — Format as Table) , а затем нажать кнопку Из таблицы/диапазона (From table/range) на вкладке Power Query (если у вас Excel 2010-2013) или на вкладке Данные (если у вас Excel 2016 или новее):

Читайте также:  Выбрать дату и время

Поверх окна Excel откроется окно редактора запросов Power Query с загруженными туда нашими данными:

Дальше делаем следующую цепочку действий:

Удаляем ненужный пока шаг Измененный тип (Changed Type) справа в панели применённых шагов с помощью крестика слева от шага.

Чтобы можно было потом идентифицировать принадлежность каждого имени к исходной строке — добавляем столбец с нумерацией строк на вкладке Добавление столбца — Столбец индекса — От 1 (Add Column — Index Column — From 1) :

Выделяем столбец с именами и жмём на вкладке Преобразование — Разделить столбец — По разделителю (Transform — Split Column — By delimiter) , а в открывшемся окне выбираем деление по каждому пробелу и — главное — деление на строки, а не на столбцы в расширенных параметрах:

После нажатия на ОК увидим следующее:

Теперь выделяем оба столбца (удерживая клавишу Ctrl или Shift ) и удаляем дубликаты через Главная — Удалить строки — Удалить дубликаты (Home — Remove Rows — Remove Duplicates) .

Осталось собрать всё обратно в ячейки 🙂 Для этого выделим столбец Индекс и используем команду Группировать по на вкладке Преобразование (Transform — Group By) со следующими параметрами:

После нажатия на ОК наши имена сгруппируются во вложенные таблицы, имитирующие начальные ячейки — только уже без повторов. Увидеть содержимое свёрнутых таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table (но не в слово Table!):

Осталось вытащить все имена из первой колонки каждой таблицы и склеить их через пробел. Это можно сделать с помощью небольшой формулы на встроенном в Power Query языке М. Выберем на вкладке Добавление столбца команду Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):

Здесь выражение [Ячейки][Имена] извлекает содержимое столбца Имена из каждой таблицы в колонке Ячейки, а функция Text.Combine склеивает затем их все через заданный разделитель (пробел). После нажатия на ОК мы, наконец, увидим желаемое:

Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close & Load — Close & Load to..) :

Задача решена! Если в будущем данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

          Читайте также:  Как найти телефон без imei

          Заключение

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

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

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

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

          Перейдите на вкладку Date (Данные), а затем нажмите команду Remove Duplicates (Удалить дубликаты), как показано ниже.

          Появится небольшое диалоговое окно Remove Duplicates (Удалить дубликаты). Можно заметить, что выделение первой строки снимается автоматически. Причиной тому является флажок, установленный в пункте My data has headers (Мои данные содержат заголовки).

          В нашем примере нет заголовков, поскольку таблица начинается с 1-й строки. Поэтому снимем флажок. Сделав это, Вы заметите, что вся таблица снова выделена, а раздел Columns (Колонны) изменится с dulpicate на Column A, B и С.

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

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

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

          Затем перейдите на вкладку Data (Данные), в группе команд Sort & Filter (Сортировка и фильтр) нажмите команду Advanced (Дополнительно), как показано ниже. Если Вы используете Excel 2003, вызовите раскрывающееся меню Data (Данные), выберите Filters (Фильтры), а затем Advanced Filters (Расширенные фильтры).

          Теперь необходимо установить флажок Unique records only (Только уникальные записи).

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

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

          Этот метод удобно применять, когда необходимо найти повторяющиеся строки в небольших таблицах. Мы будем использовать инструмент Find and Replace (Поиск и замена), который встроен во все продукты Microsoft Office. Для начала необходимо открыть таблицу Excel, с которой планируется работать.

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

          Скопировав слово, которое необходимо найти, воспользуйтесь сочетанием Ctrl+H, чтобы вызвать диалоговое окно Find and Replace (Поиск и замена). Вставьте скопированное слово с поле Найти, нажав Ctrl+V.

          Нажмите кнопку Options (Параметры), чтобы открыть дополнительный список опций. Установите флажок у пункта Match entire cell contents (Ячейка целиком). Это необходимо сделать, поскольку в некоторых ячейках искомые слова находятся вместе с другими словами. Если не выбрать эту опцию, можно непреднамеренно удалить ячейки, которые требуется оставить. Убедитесь, что все остальные настройки соответствуют указанным на рисунке ниже.

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

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

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

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

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

          Сделав это, Вы можете заметить, что все оставшиеся строки имеют уникальные значения.

          Ссылка на основную публикацию
          Форд экоспорт белый фото
          Компания Форд славится тем, что каждое обновление их машин несет в себе кучу перемен. Не стал исключением и недорогой городской...
          Установка и настройка ip камеры
          Системы видеонаблюдения используются давно. Старые аналоговые решения были дороги и громоздки. Они требовали большого количества дорогостоящего оборудования, квалифицированных специалистов и...
          Установка и настройка операционной системы windows
          Наши услуги УСТАНОВКА ОПЕРАЦИОННЫХ СИСТЕМ Определения: Операционная система (сокращенно ОС) – комплекс взаимосвязанных программ, предназначенных для управления ресурсами компьютера и...
          Форм факторы корпусов пк размеры
          Главная FAQ Железо Типы компьютерных корпусов Типы компьютерных корпусов Говоря слово "компьютер" многие подразумевают системный блок компьютера, и в принципе...
          Adblock detector