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

Кроха сын к отцу пришел, и спросила кроха…

Нет, не так. На самом деле подошел сотрудник и сказал — а не поставить ли нам эксель 2010? По опыту знаю, что ему требуется пару раз в день заполнять небольшую таблицу, ничего архисложного. Поэтому сразу возник логичный вопрос — а тебе зачем? На что вполне логичный ответ — а там можно одной командой дублирующиеся ячейки удалить. Угу. То есть 3-4 т.р. за то, чтобы дубли удалить. А надо сказать, я вообще очень плохо отношусь к неоправданным расходам в бизнесе. Одно дело, когда что-то требуется для непосредственного выполнения какой-либо функции, которую ни в чем другом выполнить нельзя. Или занимает столько времени, что дешевле оптимизировать, или написать под это специальную программу — вот сейчас, например, пишем за полторы штуки баксов одну такую. А другое дело, когда кто-то хочет на 10 минут подольше посидеть во вконтакте в рабочее время, и просто ленится разобраться, как пару кнопок нажать.

Ну ладно, сейчас расскажу, как удалить дубликаты в excel 2003, и можно идти дальше придумывать, зачем еще 2010-й тебе может понадобиться (не, для чего он нужен мне — я прекрасно знаю:-)).

Самый простой способ а) — как удалить повторяющиеся значения excel:

1. Берем, выделяем диапазон ячеек с дублями, нажимаем на Данные -> Фильтр -> Расширенный фильтр…

3. Получаем результат, который можно сделать Ctrl+C — Ctrl+V на нужное место/лист.

Теперь вариант B), для тех, кто не боится сложностей 🙂

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

3. Сортируем список по столбцу B, скажем, по возрастанию.

4. В ячейку C2 вставляем формулу =ЕСЛИ(B2=B1;0;1), которая сравнивает каждое значение с предыдущим. Если строка — дубль, то ей будет присвоено значение 0, если нет — то 1. Ну, конечно, значения B2 и B1 — это на моем примере, все зависит, сколько столбцов в таблице.

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

6. С полученным результатом делаем Ctrl+C, идем в Правка -> Специальная вставка

7. В открывшемся диалоге выбираем — Вставить Значения

8. Теперь выделяем первые три ячейки в строке 2, с зажатым шифтом щелкаем на нижней границе выделения, таким образом — выделив все с A2 по С12. Жмем Данные ->Сортировка, сортируем по столбцу С, по убыванию (это важно — отсортировать именно по убыванию! Если бы мы дублям назначили 1, а не 0 — то надо было бы отсортировывать наоборот, по возрастанию). Скриншот приводить не буду, поскольку абсолютно аналогично шагам 2 и 3.

9. Выделяем столбец С, нажимаем Ctrl-F, вводим в форму поиска 0, и ищем в этом столбце первую по порядку ячейку с нулем.

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

11. Удаляем значения из столбца С — он тоже свою роль сыграл.

12. Выделяем целиком столбцы А и B, жмем Данные ->Сортировка, и сортируем по столбцу А (в моем случае — по номерам) по возрастанию.

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

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

Самым простым способом, как строки, является использование соответствующего инструмента, располагающегося в ленте.

Итак, вам необходимо:

1. Зажав левую кнопку мыши, выделить необходимый диапазон ячеек, в пределах которого будет поиск и автоматическое удаление повторяющихся строк.
2. Перейти во вкладку "Данные".
3. Нажать по инструменту "Удалить дубликаты", что находится в группе "Работа с данными".
4. В появившемся окне поставить галочку рядом с "Мои данные содержат заголовки".
5. Установить галочки рядом с названием тех столбцов, в которых будет проводиться поиск. Возьмите во внимание, что если установлены все галочки, то дубликатом будут считаться строки, которые полностью повторяются во всех столбцах. Таким образом, если вы хотите удалить дубликаты с одного столбца, то необходимо оставить лишь одну галочку рядом с его наименованием.
6. Нажать "ОК".

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

Способ 2: использование "умной таблицы"

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

Для удаления повторов в Excel, вам нужно сделать следующее:

1. Как и в предыдущем способе, сначала выделить диапазон ячеек, где необходимо удалить дубликаты.
2. Нажать кнопку "Форматировать как таблицу", что располагается на вкладке "Главная" в группе инструментов "Стили".
3. Выбрать из выпадающего меню понравившейся стиль (любой).
4. В появившемся окне необходимо подтвердить указанный ранее диапазон ячеек, а если он не совпадает, то переназначить. Также установите галочку рядом с "Таблица с заголовками», если она такова, по итогу нажмите кнопку "ОК".
5. "Умная таблица" создана, но это еще не все. Теперь вам необходимо выделить любую ячейку таблицы, чтобы во вкладках появился пункт "Конструктор" и непосредственно перейти в эту вкладку.
6. В ленте инструментов нужно нажать кнопку "Удалить дубликаты".

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

Заключение

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

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

Microsoft Excel – это одна из самых популярных программ для работы с числами и таблицами

Второй подход заключается в использовании расширенного фильтра и работает как на новых версиях Microsoft Excel, так и на старых, включая 2003. Тут придётся понажимать несколько больше, однако, такой подход в разы более эффективен. Итак, сначала нужно открыть таблицу, затем перейдите во вкладку «Данные», в блоке «Сортировка и фильтр» выберите пункт «Дополнительно». Перед вами откроется окно, в котором нужно будет задать диапазон (только в том случае, если у вас есть разрывы, в противном случае ничего задавать не нужно) и поставить птичку на пункте «Только уникальные записи». Если вы хотите просто скрыть дубликаты, чтобы иметь возможность ещё поработать с ними в дальнейшем, то выберите фильтрование списка на месте, если же вы укажете «скопировать результат в другое место», то повторяющиеся элементы будут удалены. После нажатия «ОК» таблица будет отфильтрована программой в соответствии с выбранными вами параметрами. Теперь выделите цветом уникальные элементы и нажмите «Очистить». В результате вы увидите все дубликаты, так как они не будут отмечены цветом. Воспользовавшись автофильтром, можно будет получить полный перечень неуникальных значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Супербыстрый способ удаления дубликатов записей

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

Для этого достаточно:

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

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

Диалоговое окно удаления дубликатов предельно простое.

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

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

Результат проделанной работы:

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

Удаление дубликатов с помощью запросов (Power Query)

В 2016й версии Excel Power Query или создание запросов изначально входит в базовую поставку, а для версий 2013, 2010 нужно будет самостоятельно установить это бесплатную надстройку.

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

С помощью фильтра можно скрыть пустые строки (они здесь помечены как «null») и отсортировать, при необходимости, отобранные значения, потом выбрать команду «Закрыть и загрузить/ Закрыть и загрузить в…»

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

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