Когда работаешь с огромным объемом данных в программе "Эксель", то ненароком очень просто можно совершить ошибку и вписать одни и те же данные повторно. Так возникают дубликаты. Это, конечно же, повышает объем всей суммарной информации, а в некоторых случаях сбивает формулы, которые должны подводить итоги и рассчитывать различные переменные. Вручную найти дубликаты можно, но зачем, если есть несколько способов, как в Excel удалить повторяющиеся строки автоматически.
Способ 1: стандартное удаление дубликатов
Самым простым способом, как строки, является использование соответствующего инструмента, располагающегося в ленте.
Итак, вам необходимо:
- Зажав левую кнопку мыши, выделить необходимый диапазон ячеек, в пределах которого будет поиск и автоматическое удаление повторяющихся строк.
- Перейти во вкладку "Данные".
- Нажать по инструменту "Удалить дубликаты", что находится в группе "Работа с данными".
- В появившемся окне поставить галочку рядом с "Мои данные содержат заголовки".
- Установить галочки рядом с названием тех столбцов, в которых будет проводиться поиск. Возьмите во внимание, что если установлены все галочки, то дубликатом будут считаться строки, которые полностью повторяются во всех столбцах. Таким образом, если вы хотите удалить дубликаты с одного столбца, то необходимо оставить лишь одну галочку рядом с его наименованием.
- Нажать "ОК".
Как только вы нажмете на кнопку, начнется поиск дубликатов выделенной области, а затем и их удаление. По итогу появится окно, в котором будет продемонстрирован отчет. Теперь вы знаете, как в Excel удалить повторяющиеся строки, но лишь первый способ, на очереди второй.
Способ 2: использование "умной таблицы"
Дубликаты можно удалить подобным способом, который имеет место быть в этой статье. На сей раз, в рассказе, как в Excel удалить повторяющиеся строки, будет использоваться "умная таблица".
Для удаления повторов в Excel, вам нужно сделать следующее:
- Как и в предыдущем способе, сначала выделить диапазон ячеек, где необходимо удалить дубликаты.
- Нажать кнопку "Форматировать как таблицу", что располагается на вкладке "Главная" в группе инструментов "Стили".
- Выбрать из выпадающего меню понравившейся стиль (любой).
- В появившемся окне необходимо подтвердить указанный ранее диапазон ячеек, а если он не совпадает, то переназначить. Также установите галочку рядом с "Таблица с заголовками», если она такова, по итогу нажмите кнопку "ОК".
- "Умная таблица" создана, но это еще не все. Теперь вам необходимо выделить любую ячейку таблицы, чтобы во вкладках появился пункт "Конструктор" и непосредственно перейти в эту вкладку.
- В ленте инструментов нужно нажать кнопку "Удалить дубликаты".
После этого появится окно удаления повторяющихся строк. Оно будет аналогично тому, что было представлено в первом способе, поэтому все последующие действия проводите в соответствии с первой инструкцией.
Заключение
Вот мы и разобрали два способа, как в Excel удалить строки с повторяющимися значениями. Как можно заметить, в этом нет ничего сложного, а пользуясь инструкцией, так и вовсе провернуть данную операцию можно за несколько секунд. Пример был приведен в 2016-й версии программы, однако удалить повторяющиеся строки в Excel 2010 и других версиях можно точно также.
Кроха сын к отцу пришел, и спросила кроха…
Нет, не так. На самом деле подошел сотрудник и сказал — а не поставить ли нам эксель 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, жмем Данные ->Сортировка, и сортируем по столбцу А (в моем случае — по номерам) по возрастанию.
Электронные таблицы MS Excel используются для анализа содержащихся в них данных. Информация анализируется в исходном виде, но чаще она требует предварительного редактирования. В статье мы рассмотрим, как избавиться от лишних, повторяющихся данных, если они затрудняют обработку файла.
Поиск повторяющихся значений
Рассмотрим пример. Магазин, занимающийся продажей косметики, регулярно получает прайс-листы от компаний-поставщиков в формате Excel. Предположим для удобства, что наименования товаров во всех компаниях одинаковые. Для того чтобы сделать заказ, мы хотим прежде всего определить, в какой из фирм какую продукцию выгоднее заказывать.
Для этого нужно найти одинаковые записи и сравнить цены в них. Пока мы не будем удалять дубликаты в Excel, а только найдем одни и те же товары для анализа стоимости. Для этого воспользуемся форматированием ячеек по условию. Выделяем столбец с наименованием товаров и открываем панель инструментов «Стили» вкладки «Главная». По кнопке «Условное форматирование» открывается выпадающий список команд, из которых нас интересует пункт «Правила выделения ячеек».
Нужное нам правило - «Повторяющиеся значения». В открывшемся окне указываем цвет, которым будут отмечены повторяющиеся элементы диапазона. После закрашивания ячеек с помощью фильтра можно отобрать только их, отсортировать по наименованию продукции, произвести анализ цен и удалить дубликаты в Excel.
Поиск уникальных значений
Описанный выше способ подходит также и для отбора элементов, встречающихся в единственном числе. Для такого выбора сделайте те же действия, что и в предыдущем пункте. Откройте панель команд «Стили» вкладки «Главная» и нажмите кнопку «Условное форматирование». В списке операций выберите «Правила выделения ячеек».
Но теперь в окне настроек фильтра нужно выбрать из выпадающего списка значение не «Повторяющиеся», а «Уникальные». Программа разметит выбранным цветом только те элементы столбца, которые встречаются в единственном экземпляре.
После слияния нескольких прайс-листов в один файл в таблице осталось много повторяющихся записей, которые нужно удалить. Так как список продукции очень длинный, его обработка вручную займет очень много времени и потребует значительных усилий. Гораздо удобнее воспользоваться опцией, которую предлагает программа.
Чтобы удалить дубликаты в Excel, существует соответствующая команда меню. Она находится на вкладке «Данные» в блоке операций «Работа с данными». При нажатии на кнопку открывается диалоговое окно. Если перед вызовом команды на листе не было выделено группы ячеек, программа сразу предлагает выделить столбцы, по которым нужно найти и убрать повторения.
Если же предварительно выделить поля одной колонки, Excel выведет уточняющее сообщение, в котором необходимо будет выбрать, использовать ли в последующей операции только выделенный диапазон или расширить его.
Затем отмечаете столбцы для поиска дубликатов. Функция удобна тем, что позволяет найти как полные соответствия и удалить дубликаты строк Excel, так и совпадения только по отдельным полям.
Например, в нашем случае мы можем искать повторения лишь по наименованиям, кодам, типу и производителю товаров, зная, что цены и поставщики будут разными. Такая обработка позволит оставить в списке уникальные элементы продукции для составления собственного прайс-листа или каталога.
Еще один способ удалить дубликаты в Excel - отбор только уникальных значений выделенного диапазона таблицы. Покажем это на примере. Выделяем группу ячеек, по которым нужно убрать повторяющиеся значения, и открываем в программе вкладку «Данные». Здесь находим блок команд «Сортировка и фильтр» и выбираем одну из них - «Дополнительно».
В открывшемся окне вводим параметры выбора полей. Если нет необходимости сохранять исходную таблицу, выбирайте вариант «Фильтровать список на месте». Но если работа с ним не закончена, лучше перенести результаты фильтра в другое место.
Выбираем группу полей для обработки и отмечаем ячейку, в которую будут помещены отфильтрованные данные. Для того чтобы получить в результате только уникальные вхождения, устанавливаем флажок «Только уникальные записи».
Получаем в итоге из начальных 27 записей только 19 без повторений. Этим способом можно удалить дубликаты в Excel 2003, тогда как предыдущий появился только в версии программы 2007.
Обратите внимание на некоторые советы перед удалением информации из таблиц. Прежде всего, до выполнения таких действий сделайте копию таблицы и проводите операции с ней. Либо оставьте ее как резервную и выполняйте изменения в оригинале. В противном случае вы можете потерять данные вашего файла или изменить формат листа.
Если исходная таблица содержит группировку, суммирование или необходимо убрать их перед тем, как удалять дубликаты в столбце Excel.
Нахождение одинаковых элементов не работает в отчетах сводной таблицы.
Кроме вышеперечисленных, есть возможность убрать повторяющиеся элементы столбцов с помощью формул, но этот метод достаточно трудоемкий и не имеет смысла в использовании в современных версиях программы.
В сегодняшних Excel файлах дубликаты встречаются повсеместно. К примеру, когда вы создаете составную таблицу из других таблиц, вы можете обнаружить в ней повторяющиеся значения, или в файле с общим доступом внесли одинаковые данные два разных пользователя, что привело к задвоению и т.д. Дубликаты могут возникнуть в одном столбце, в нескольких столбцах или даже во всем листе. В Microsoft Excel реализовано несколько инструментов поиска, выделения и, при необходимости, удаления повторяющихся значений. Ниже описаны основные методики определения дубликатов в Excel.
1. Удаление повторяющихся значений в Excel (2007+)
Предположим, у вас имеется таблица, состоящая из трех столбцов, в которой присутствуют одинаковые записи и вам необходимо избавится от них. Выделяем область таблицы, в которой хотите удалить повторяющиеся значения. Вы можете выделить один или несколько столбцов, или всю таблицу целиком. Переходим по вкладке Данные в группу Работа с данными, щелкаем по кнопке Удалить дубликаты.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.
Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице. Если вы выделили не все столбцы для определения дубликатов, строки с повторяющимися значениями также будут удалены.
2. Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.
В появившемся диалоговом окне Расширенный фильтр, необходимо установить переключатель в положение скопировать результат в другое место, в поле Исходный диапазон указать диапазон, в котором находится таблица, в поле Поместить результат в диапазон указать верхнюю левую ячейку будущей отфильтрованной таблицы и установить маркер Только уникальные значения. Щелкаем ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.
3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладкеГлавная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
В появившемся диалоговом окне Повторяющиеся значения, необходимо выбрать формат выделения дубликатов. У меня по умолчанию установлено светло-красная заливка и темно-красный цвет текста. Обратите внимание, в данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому если у вас имеются повторяющиеся значения только в одном столбце, Excel отформатирует их тоже. На примере вы можете увидеть, как Excel залил некоторые ячейки третьего столбца с именами, хотя вся строка данной ячейки таблицы уникальна.
4. Использование сводных таблиц для определения повторяющихся значений
Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названиемСчетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.
Создаем сводную таблицу. В поле Название строк помещаем три первых столбца, в поле Значенияпомещаем столбец со счетчиком. В созданной сводной таблице, записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности, можно отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.
Поиск и удаление вручную повторяющихся значений в таблице Excel, в особенности в больших документах, - весьма рискованное и нецелесообразное занятие. Не исключено, что вы, проверяя зрительно ячейки, можете пропустить дубли. И потом, тратится колоссальное количество времени на удаление каждой ячейки.
В этой статье мы рассмотрим, как можно удалить дубликаты в Excel, используя различные автоматизированные штатные функции.
Совет! Для удобства ознакомления со способами удаления повторяющихся строк, столбцов, значений ячеек, откройте в редакторе черновой шаблон или копию проекта, над которым работаете, чтобы по ошибке в ходе редактирования не потерять ценные данные и не нарушить формат оригинала.
Способ №1
1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.
2. В меню редактора перейдите на вкладку «Данные».
3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».
4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».
5. По завершении очистки строк и столбцов таблицы от дублей появится сообщение о том, сколько осталось уникальных значений.
Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».
Способ №2
1. Кликните по редактируемой таблице.
2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».
Если необходимо создать новую таблицу, содержащую только уникальные ячейки исходника:
1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».
2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».
3. Клацните на свободном рабочем пространстве Excel, куда необходимо будет поместить отфильтрованную таблицу. После щелчка в поле появится код ячейки. Закройте его и перейдите в опции фильтра.
4. Кликните окошко «Только уникальные записи» и нажмите «OK».
5. После выполнения фильтрации в указанном месте появится версия исходной таблицы без повторов.
Чтобы отредактировать документ без создания копий:
- в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
- кликом мышки включите надстройку «Только уникальные записи»;
- клацните «OK».
Loading...