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

Работая с большими объемами данных нам часто нужно составить список уникальных значений — артикулы или названия товаров, должности, города — да мало ли еще!

Информация

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

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

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

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

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

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

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

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

Например:

Есть Должно получиться
строка1 строка2 строка4 строка3 строка4 строка1 строка5 строка1 строка2 строка4 строка3 строка5

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

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

Выделяем список (столбец) ➤ переходим во вкладку Date (Данные), нажимаем команду Remove Duplicates (Удалить дубликаты) ➤ в открывшемся диалоговом окне «Remove Duplicates» (Удалить дубликаты) снимаем флажок My data has headers (Мои данные содержат заголовки) ➤ нажимаем OK. Все повторяющиеся строки будут удалены, кроме первой (оригинала).

Читайте также:  Что такое прокси-сервер и для чего он нужен?

Фильтровать дубликаты в LibreOffice

Выделяем нужные ячейки (столбец целиком) ➤ Открываем меню Date (Данные) ➤ «Ещё фильтры»«Стандартный фильтр…» ➤ В открывшемся диалоговом окне в поле «Имя поля» выбираем необходимый столбец ➤ В поле «Условие» устанавливаем знак равно «=» ➤ В поле «Значение» указываем «Не пусто» ➤ Внизу в разделе «Параметры» обязательно отмечаем флажок «Без повторений» ➤ Жмем OK. В столбце останутся только единичные экземпляры и можно их скопировать.

Путь к стандартному фильтру в LibreOfficeОкно стандартного фильтра в Libre Office

Обратите внимание, что номера у строк в таблице отображаются прежние.

Google-Таблицы

Google Таблицы не имеют встроенных функций удаления дублей, поэтому можно использовать установить дополнение «Remove Duplicates». Установить это расширение можно бесплатно.

После установки расширения заходим в Гугл-таблицу, выделяем столбец (строки) где нужно почистить от дублей ➤ нажимаем в меню «Дополнения»Remove Duplicates (Удалить дубликаты) ➤ Find duplicates or uniques rows (Найти дубликаты или уникальные) ➤ В открывшемся окне на 1-м шаге нажимаем «Next» ➤ во 2-ом шаге выбираем «Duplicates» и далее ➤ пропускаем 3-й шаг далее ➤ и на 4-ом шаге выбираем, что делать с найденными дублями: перенести (Move), копировать (Copy) в другое место, очистить (Clear) или удалить (Delete). Выбираем «Delete rows within selection» (Удалить выделенные строки). Всё, готово.

Remove Dublicate шаг 1Remove Dublicate шаг 4

NotePad++

Для удаления ненужных дублей строк в NotePad++ необходимо установить плагин TextFX если не установлен.

Удаление дублей строк плагином TextFX в NotePad++

Удаление повторяющихся строк: Переходим в документ со списком и выделяем (Ctrl+A) ➤ нажимаем в меню TextFXTextFX Tools ➤ проверяем отмечена ли функция Sort outputs only UNIQUE lines (Сортировать вывод только по УНИКАЛЬНЫМ строкам), ➤ если да, то сразу выбираем Sort lines case insensitive (Сортировка строк без учета регистра).

Удаление дублей онлайн

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

Читайте также:  Все html теги с описанием — справочник со списком кодов

Как удалить дубликаты в excel без сдвига ячеек?

При заполнении таблицы, особенно текстовыми данными, например список людей или что-то подобное, может возникнуть ситуация, когда вы запишите подряд несколько одинаковых значений. Хорошо, если таблица маленькая, то вы сразу обнаружите дубликаты. А если она большая? Сразу и не заметить. Но если вы заметили дубликат и

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

В новых версиях excel имеется полезная кнопка удалить дубликаты. Найти ее можно во вкладке «Данные».

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

Они разбросаны по столбцу и удалить их вручную сложно. Ставим курсор в этот столбец и нажимаем на кнопку «удалить дубликаты«.

Как удалить дубликаты в excel без сдвига ячеек?

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

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

Как удалить дубликаты в excel без сдвига ячеек?

Теперь нажимаем ОК и видим, что в столбце удалены дубликаты, а уникальные данные остались.

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

Как удалить дубликаты в excel без сдвига ячеек?

Четвертый способ — формулы

Формула, извлекающая уникальные значения.

Четвертый способ — использовать формулы для работы с массивами INDEX (ИНДЕКС), MATCH (ПОИСКПОЗ), COUNTIF (СЧЁТЕСЛИ), IFERROR (ЕСЛИОШИБКА) а также SMALL (НАИМЕНЬШИЙ), ISTEXT (ЕТЕКСТ), ISNUMBER (ЕЧИСЛО).

Вот эта формула: =IFERROR(IFERROR(SMALL(IF((COUNTIF($D$2:D2; Table2[Отдел])=0)*ISNUMBER(Table2[Отдел]);Table2[Отдел]; "A"); 1); INDEX(Table2[Отдел]; MATCH(SMALL(IF(ISTEXT(Table2[Отдел])*(COUNTIF(D2:$D$2; Table2[Отдел])=0); COUNTIF(Table2[Отдел]; "<"&Table2[Отдел]); ""); 1); IF(ISTEXT(Table2[Отдел]); COUNTIF(Table2[Отдел]; "<"&Table2[Отдел]); ""); 0)));"")

Необходимые пояснения:

  1. Формула должна начинаться как минимум, с 2 строки в столбце (в примере выше — с третьей), а ячейка над формулой должна либо содержать значение, которого нет в списке, либо должна быть пустой.
  2. Table2[Отдел] — это именнованный диапазон данных. Если вы используете таблицу — у вас будет похожее название. Если нет — нужно указать, например $A$1:$A$100.
  3. Это формула массива, о чем говорят фигурные скобки вокруг формулы. Вводить их вручную бесполезно, нужно в строке с формулой нажать Ctrl + Shift + Enter.
  4. Формула должна быть протянута на столько ячеек, сколько уникальных значений мы допускаем максимально.
  5. Подробно и в деталях, как работает эта формула я разобрал в статье -ispolzovanie-formul-v-excel-kak-vybrat-iz-spiska-unikalnye-znacheniia-bez-makrosov-5f33e5e9f13d89681bf68520 , здесь лишь используется усовершенствованный вариант, который, помимо отбора уникальных значений, еще и сортирует данные.
  6. Сортировка выполняется при помощи тех же функций INDEX, MATCH, COUNTIF, но немного по-разному для текста и чисел (поэтому и используются IF, ISTEXT и ISNUMBER. Также есть функция ISERROR, которая фильтрует ошибки, что позволяет иметь пустые строки в исходных данных.

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

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

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

Как обычно, прикладываю ссылку на файл Excel:

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

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

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

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

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

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

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

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

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