Удаление циклических строк в Эксель: самые резвые способы

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

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

Смотрите также: “Как убрать колонтитулы в Экселе”

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

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

Вкладка Данные в таблице Эксель

Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заглавия” – он должен быть отмечен галочкой.

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

Иной метод удалить дубликаты в Excel — это разделение неповторимых значений и копирование их на иной лист либо в избранный спектр на текущем листе. Разберем этот метод.

Изберите спектр либо всю таблицу, которую вы желаете удалить дубликаты.
Перейдите во вкладку «ДАННЫЕ» —> группа «Сортировка и фильтр» и нажмите клавишу «Добавочно».

Удалить дубликаты в Excel – Внедрение доп фильтра для удаления дубликатов

В диалоговом окне «Расширенный фильтр» сделайте последующие деяния:

Изберите пункт «скопировать итог в другое пространство».
Проверьте, отображается ли верный спектр в Начальном спектре. Это должен быть спектр, избранный на шаге 1.
В поле Поместить итог в спектр введите спектр, в котором вы желаете скопировать неповторимые значения (по сути довольно избрать верхнюю левую ячейку спектра предназначения).
Изберите Лишь неповторимые записи

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

В конце концов, нажмите «ОК». Excel удалит дубликаты и скопирует неповторимые значения в новое обозначенное пространство:

Удалить дубликаты в Excel – Неповторимые записи, скопированные из другого места

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

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

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

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

ставим курсор на произвольную ячейку таблицы;

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

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

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

Как обычно, выделяем все ячейки таблицы.
Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” отыскиваем клавишу “Фильтр” (иконка припоминает воронку) и кликаем на нее.

как и в прошлом методе, проверяем адресок спектр ячеек таблицы;

Способ 4: условное форматирование

Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая {инструкция}”

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

Выделяем все ячейки нашей таблицы.
Во вкладке “Основная” кликаем по кнопочке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
Раскроется список, в котором избираем группу “Правила выделения ячеек“, а снутри нее – пункт “Повторяющиеся значения“.

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

Как сопоставить два столбца в Excel на совпадения

Представим, что наша таблица состоит из 2-ух столбцов с данными. Нам необходимо найти повторяющиеся значения в первом и втором столбцах. Для решения задачки нам посодействуют функции ЕСЛИ и СЧЁТЕСЛИ .

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула инспектирует значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы сможете указать в формуле точный спектр (к примеру, $B2:$B10 ). Это дозволит убыстрить работу формулы.

Способ 5: формула для удаления циклических строк

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

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

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

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

ставим курсор в конец строки формул (необходимо убедиться, что это, вправду, конец строки, потому что в неких вариантах длинноватая формула не помещается в границах одной строки);

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

Как сопоставить два столбца в Excel по строчкам

Сравнивая два столбца с данными нередко нужно ассоциировать данные в каждой отдельной строке на совпадения либо различия. Создать таковой анализ мы можем при помощи функции ЕСЛИ . Разглядим как это работает на примерах ниже.

Пример 1. Как сопоставить два столбца на совпадения и различия в одной строке

Для того, чтоб сопоставить данные в каждой строке 2-ух столбцов в Excel напишем ординарную формулу ЕСЛИ . Вставлять формулу следует в каждую строчку в примыкающем столбце, рядом с таблицей, в которой расположены главные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на другие строки.

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

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

Мы можем уместить проверку на совпадения и различия меж 2-мя столбцами в одной строке в одной формуле:

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

Пример результата вычислений может смотреться так:

Для того чтоб сопоставить данные в 2-ух столбцах одной строки с учетом регистра следует употреблять формулу:

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Неповторимое”)

Как удалить повторяющиеся строки и абзацы в Word

Как и гласил в посте Как отыскать и поменять текст в Ms Office Word, Open Office Writer, Libre Office, сейчас попробуем поработать с огромным количеством инфы. Бывает, что из массива текста необходимо удалить повторяющиеся строки и абзацы. Будем оставлять самое основное и удалять избыточное в весьма большенном тексте в Word.

Как удалить повторяющиеся строки и абзацы Word

Текст, который мы будем «чистить»:

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

Направьте внимание, что длина удаляемого текста различается. Для выделения строки независимо от количества знаков жмем Ctrl+Shift+стрелка вниз. Под спойлером в конце поста вы увидите все команды выделения текста при помощи клавиатуры. А на данный момент переводим курсор в начало текста, перебегаем в меню «Макросы» и включаем запись макроса.

Как записать макрос

Когда будете задавать имя макросу, не оставляйте пробелы – Word выдаст ошибку. Назначаем его для клавиши либо клавиатуры. Я больше люблю работать с клавиатурой и избрал потому клавиши.

Настраиваем макрос. Здесь просто жмем хоть какое сочетание кнопок. Если оно уже присвоено, то о этом покажется информация.

Я присвоил макросу клавиатурную комбинацию Ctrl+G. Эта композиция не употребляется в обычном режиме редактирования и является вольной. Не переписывайте композиции, которые нередко используете .

Запомнили число и нажали Ctrl+Z – отменили подмену текста на звёздочку. Сейчас я понимаю, что в примере у меня 24 вхождения. Это я должен надавить 24 раза сочетание Ctrl+G для того, чтоб отформатировать текст. Будем упрощать это дело.

Жмем Alt+ F 11. Мы попали в редактор макросов. Это большая и непростая тема, здесь самый реальный язык программирования, но у нас всё будет просто, я вас уверяю.

Вписываем как на картинке две строки в начало и слово в конце.

Dim i As Integer

For i = 0 To 23

Что это означает? Мы обозначили i — числовой переменной и присвоили значения от 0 до 23, опосля окончания первого цикла подмены число i возрастет на 1 и макрос будет опять повторяться (Next) до того времени, пока не достигнет конца (23).

Другими словами эти строки дают команду опосля нажатия Ctrl+G повторить макрос «Удаляем3строки» 23 раза. Вот текст моего примера макроса.

Пример макроса для удаления трёх строк

Dim i As Integer

Selection.MoveDown Unit:=wdLine, Count:=1

Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend

Selection.Delete Unit:=wdCharacter, Count:=1

Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend

Selection.Delete Unit:=wdCharacter, Count:=1

Selection.MoveDown Unit:=wdParagraph, Count:=1, Extend:=wdExtend

Selection.Delete Unit:=wdCharacter, Count:=1

Selection.MoveDown Unit:=wdLine, Count:=1

Сохраняем макрос и закрываем редактор.

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

Тогда можно временно поменять постоянное выражение на хоть какой знак либо уменьшить шрифт на шаге работы макроса и тому схожее.

Возвратились в редактор, нажали Ctrl+G, и текст мигом принял новейший вид. Всё вышло. За несколько минут обработали достаточно большенный текст в автоматическом режиме.

Команды управления для макросов

Выделение текста при помощи клавиатуры.Выделение частей в основном тексте документа

Выделяемый элемент	Действие
Один символ на право	Нажмите клавиши SHIFT+СТРЕЛКА ВПРАВО
Один символ на лево	Нажмите клавиши SHIFT+СТРЕЛКА ВЛЕВО
Слово от начала к концу	Установите курсор в начало слова и нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВПРАВО.
Слово от конца к началу	Установите курсор в конец слова и нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВЛЕВО.
Строчка от начала к концу	Нажмите кнопку HOME, а потом нажмите клавиши SHIFT+END.
Строчка от конца к началу	Нажмите кнопку END, а потом нажмите клавиши SHIFT+HOME.
Одна строчка вниз	Нажмите кнопку END, а потом нажмите клавиши SHIFT+СТРЕЛКА ВНИЗ.
Одна строчка ввысь	Нажмите кнопку HOME, а потом нажмите клавиши SHIFT+СТРЕЛКА ВВЕРХ.
Абзац от начала к концу	Установите курсор в начало абзаца и нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВНИЗ.
Абзац от конца к началу	Установите курсор в конец абзаца и нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВВЕРХ.
Документ от конца к началу	Установите курсор в конец документа и нажмите клавиши CTRL+SHIFT+HOME.
Документ от начала к концу	Установите курсор в начало документа и нажмите клавиши CTRL+SHIFT+END.
От начала до конца окна	Установите курсор в начало окна и нажмите клавиши ALT+CTRL+SHIFT+PAGE DOWN.
Весь документ	Нажмите клавиши CTRL+A.
Вертикальный блок текста	Нажмите клавиши CTRL+SHIFT+F8, а потом используйте клавиши со стрелками. Для выхода из режима выделения нажмите кнопку ESC.
Ближний символ	Нажмите кнопку F8 для входа в режим выделения, а потом нажмите кнопку СТРЕЛКА ВЛЕВО либо СТРЕЛКА ВПРАВО; для выхода из режима выделения нажмите кнопку ESC.
Слово, предложение, абзац либо документ	Нажмите кнопку F8 для входа в режим выделения, а потом нажмите кнопку F8 один раз, чтоб выделить слово, дважды, чтоб выделить предложение, трижды, чтоб выделить абзац либо четыре раза, чтоб выделить документ. Для выхода из режима выделения нажмите кнопку ESC.

Выделение частей таблицы

Выделяемый элемент	Действие
Содержимое последующей ячейки	Нажмите кнопку TAB.
Содержимое предшествующей ячейки	Нажмите клавиши SHIFT+TAB.
Содержимое нескольких смежных ячеек	При нажатой клавише SHIFT пару раз нажмите подобающую кнопку со стрелкой, пока не будет выделено содержимое всех подходящих ячеек.
Содержимое столбца	Выделите верхнюю либо нижнюю ячейку столбца, нажмите кнопку SHIFT и, удерживая ее, пару раз нажмите кнопку СТРЕЛКА ВВЕРХ либо СТРЕЛКА ВНИЗ, пока не будет выделено содержимое столбца.
Содержимое таблицы полностью	Щелкните в любом месте таблицы, а потом нажмите клавиши ALT+5 на цифровой клавиатуре (при выключенном индикаторе NUM LOCK).

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

Как убрать повторяющиеся значения в Excel?

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

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

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

ставим курсор на произвольную ячейку таблицы;

Как соединить схожие строки одним цветом?

Чтоб отыскать соединить и выделить схожие строки в Excel следует выполнить пару шажков обычных действий:

Выделите весь спектр данных табличной части A2:F18. Начинайте выделять значения из ячейки A2, так чтоб опосля выделения она оставалась активной как показано ниже на рисунке. И изберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Сделать правило».
В показавшемся окне «Создание правила форматирования» изберите опцию: «Применять формулу для определения форматированных ячеек».
В поле ввода введите формулу:
Нажмите на клавишу формат, чтоб задать цвет заливки для ячеек, к примеру – зеленоватый. И нажмите на всех открытых окнах клавишу ОК.

В итоге выделились все строки, которые повторяются в таблице хотя-бы 1 раз.

Как избрать строки по условию?

Форматирование для строки будет использовано лишь в том случаи если формула возвращает значения ИСТИНА. Принцип деяния формулы последующий:

1-ая функция =СЦЕПИТЬ() складывает в один ряд все знаки из лишь одной строки таблицы. При определении условия форматирования все ссылки указываем на первую строчку таблицы.

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

2-ая функция =СЦЕПИТЬ() по очереди сложить значение ячеек со всех выделенных строк.

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

Как лишь при сопоставлении совпадают схожие значения (находятся две и наиболее схожих строк) это приводит к суммированию при помощи функции =СУММ() числа 1 обозначенного во 2-м аргументе функции =ЕСЛИ(). Функция СУММ дозволяет сложить схожие строки в Excel.

Если строчка встречается в таблице лишь один раз, то функция =СУММ() возвратит значение 1, а целая формула возвращает – ЛОЖЬ (ведь 1 не является больше чем 1).

Если строчка встречается в таблице 2 и наиболее раза формула будет возвращать значение ИСТИНА и для проверяемой строки присвоится новейший формат, обозначенный юзером в параметрах правила (заливка ячеек зеленоватым цветом).

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

Как обычно, выделяем все ячейки таблицы.
Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” отыскиваем клавишу “Фильтр” (иконка припоминает воронку) и кликаем на нее.

как и в прошлом методе, проверяем адресок спектр ячеек таблицы;

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

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

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

Сделайте умную таблицу (CTRL+T) с циклическими значениями как на рисунке:
Щелкните по таблице и изберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
В показавшемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Стоимость».

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

Способ 4: условное форматирование

Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая {инструкция}”

Выделяем все ячейки нашей таблицы.
Во вкладке “Основная” кликаем по кнопочке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
Раскроется список, в котором избираем группу “Правила выделения ячеек“, а снутри нее – пункт “Повторяющиеся значения“.

Duplicate Remover — резвый и действенный метод отыскать дубликаты в Excel

Сейчас, когда вы понимаете, как употреблять формулы для поиска циклических значений в Excel, разрешите мне показать для вас очередной резвый, действенный и без всяких формул метод: инструмент Duplicate Remover для Excel.

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

Как отыскать повторяющиеся строки в Excel за 2 стремительных шага

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