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

Обновлено: 03.07.2024

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ПОВТОР в Excel для заполнения ячейки определенным количеством вхождений текстовой строки. С помощью данной функции возвращается текстовая строка заданное пользователем количество символов раз.

Генерация звездочек пароля с функцией ПОВТОР в Excel

Пример 1. Веб-сайт использует таблицу Excel в качестве базы данных для хранения логина и пароля пользователей. Если сайт «запомнил» пользователя в форме входа, то поля Логин и Пароль заполняются автоматически. В целях сохранения конфиденциальности, вместо самого пароля требуется выводить определенное количество символов «*», соответствующее длине пароля.

Внесем исходные данные в таблицу:

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

«*» - символы, которые будут отображаться определенное число раз;
ДЛСТР(B3) – длина исходной строки, содержащей пароль.

Аналогично преобразуем остальные пароли. В результате получим:

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

Скрытие номера кредитной карты функцией ПОВТОР в Excel

Пример 2. Номер кредитной карты – это 12-значное число, распределенное на 4 подгруппы, например: 0000-0000-0000-0000. На сайтах обычно отображают лишь последнюю подгруппу (****-****-****-0000). Реализовать подобную запись номеров кредитных карт в Excel.

Исходная таблица данных:

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

В данном случае функция ПОВТОР возвращает текстовую строку, состоящую из трех последовательных текстовых строк «****-», а к возвращаемому ей результату благодаря символу & (амперсанд) будут добавлены 4 последних символа из текста, содержащегося в ячейке B3.

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

Добавление повторяющихся символов к зачиню ячейки Excel

Пример 3. Приложение обрабатывает данные, полученные из таблицы Excel. Для корректной работы требуется, чтобы каждое поле содержало 20 символов. Было решено выполнять проверку длины каждых текстовых данных, содержащихся в Excel, и увеличивать их длину до 20 символов путем добавления символов «.». Рассмотрим, как это было реализовано.

В ячейке B2 записана следующая формула:

Данная запись имеет следующий смысл:

Если изначальный текст содержит более 20 символов, будет возвращено значение null (понятный для приложения тип данных, обозначающий нулевую ссылку на объект);
Если длина изначального текста более менее 20 символов, к текущему тексту будет добавлено требуемое количество точек «.» (вычисляется на основании записи 20-ДЛСТР(A3)).
Иначе (если количество символов равно 20), будет возвращена ссылка на ячейку с исходным текстом.

Адаптируем формулу для остальных ячеек и в итоге получим:

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

Функция ПОВТОР в Excel и особенности ее использования

Функция ПОВТОР имеет следующий синтаксис:

Оба аргумента являются обязательными для заполнения. Описание аргументов:

Например, у вас есть список значений в столбце A, и вы хотите повторить значения определенное количество раз в зависимости от количества раз в столбце B, как показано на скриншоте слева, как вы могли бы добиться этого в Excel?

Повторите значения ячеек X раз с формулой и вспомогательным столбцом
Повторить значения ячеек X раз с кодом VBA
Копируйте и вставляйте значения ячеек X раз с потрясающей функцией

Повторите значения ячеек X раз с формулой и вспомогательным столбцом

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

1. Вставьте столбец слева от столбца A и введите 1 в ячейку A2, см. Снимок экрана:

2. Затем положите эту формулу = A2 + C2 в A3 и перетащите маркер заполнения в ячейку A6, см. снимок экрана:

3. Затем введите 1 в ячейку D2 и заполните число, перетащив Автозаполнение на 12, что является общим числом раз в столбце C, см. Снимок экрана:

4. Затем введите эту формулу = ВПР (D2; $ A $ 1: $ B $ 6,2) в ячейку E2 и скопируйте, вы получите следующий результат:

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

Легко копируйте и вставляйте строки X раз на основе определенного числа

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

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 60 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Повторить значения ячеек X раз с кодом VBA

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

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: повторить значения ячеек X раз

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно с напоминанием о выборе диапазона, который содержит значения и повторяющиеся времена, см. снимок экрана:

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

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

Копируйте и вставляйте значения ячеек X раз с потрясающей функцией

Иногда вам может потребоваться скопировать и вставить значения ячеек x раз в зависимости от определенного числа, Kutools for Excel's Повторяющиеся строки / столбцы на основе значения ячейки может помочь вам быстро скопировать и вставить строки на основе указанного вами числа.

Советы: Чтобы применить это Повторяющиеся строки / столбцы на основе значения ячейки функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Kutools > Вставить > Повторяющиеся строки / столбцы на основе значения ячейки, см. снимок экрана:

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

4, Затем нажмите Ok or Применить кнопку, вы получите такой результат, как вам нужно:

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

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

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

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

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

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

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

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

Для начала, нам нужно выделить всю таблицу, как в первом шаге предыдущего раздела.
Во вкладке “Главная” находим кнопку “Форматировать как таблицу” (раздел инструментов “Стили“). Кликаем на стрелку вниз справа от названия кнопки и выбираем понравившуюся цветовую схему таблицы.
После выбора стиля откроется окно настроек, в котором указывается диапазон для создания “умной таблицы“. Так как ячейки были выделены заранее, то следует просто убедиться, что в окошке указаны верные данные. Если это не так, то вносим исправления, проверяем, чтобы пункт “Таблица с заголовками” был отмечен галочкой и нажимаем ОК. На этом процесс создания “умной таблицы” завершен.

ставим курсор на произвольную ячейку таблицы;
переключаемся во вкладку “Конструктор” (если после создания “умной таблицы” переход не был осуществлен автоматически);
в разделе “Инструменты” жмем кнопку “Удалить дубликаты“.

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

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

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

Метод 4: условное форматирование

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

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

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

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

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

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

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

Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter:
=ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));"") .
Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
- ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
- жмем служебную клавишу F2 на клавиатуре;
- затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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

Заключение

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

Читайте также: