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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

2. Использование расширенного фильтра для удаления дубликатов

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

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

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

3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

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

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

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

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

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

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

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

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



Альтернативные способы удаления дубликатов

В Excel существуют и другие средства для работы с дублированными значениями. Например:

Каждый инструмент обладает своими преимуществами и недостатками. Но эффективнее всех использовать для удаления дубликатов – таблицу (как описано выше). Там весь процесс происходит поэтапно с максимальным контролем данных. Это дает возможность получить качественный результат.

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

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

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

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

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

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

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

Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где "руки" будут бессильны...!).

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

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

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

Как это делается:

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

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

Всем добрый вечер! Случалось ли Вам когда нибудь работать с данными в excel строковое значение которых переваливает за пару десятков тысяч? А вот мне человеку который создает и продвигает сайты и интернет магазины приходится сталкиваться достаточно часто, особенно когда дело касается загрузки и выгрузки данных от поставщика на сайт интернет магазина. Данная заметка родилась не на пустом месте, а прямо так сказать с пылу жару! Сегодня делал загрузку на свой тематике (см портфолио) и после того как скачал прайс от поставщика и открыв его в excel (перед загрузкой на сайт я сначала все сверяю, на случай ошибок и случайных изменений столбцов со стороны поставщика) и увидел что из 25 тыс строк более 6-8 тыс являются дубликатами, зачем и почему так делает поставщик мы сейчас обсуждать не будем, на это не хочется тратить не сил, ни времени, а просто понимаем, что так сделал программист и по другому это делать нельзя!

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

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

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

В данной статье будет представлено два варианта решения проблемы.

1 Вариант — Стандартная функция в эксель — Удалить дубликаты

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

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

2 Вариант — Пометить дубликаты строк в Лож или Истина

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

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

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

и нажимаем энтер, и у нас в этой ячейки в зависимости от данных должно появится значение Лож или Истина

ЛОЖ — если А1 не будет равно А2

Истина — если А1 будет ровно А2

если применить этот вариант на столбце Пример2, то как вы поняли везде будет значение Лож

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

3 Вариант — Удалить дубликаты в столбе

Вот этот вариант уже более сложный, но он решит вашу проблему на все 100% и сразу ответит на все вопросы.

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

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

ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;»»;A2)

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

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

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

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

Нет похожих записей.