Как сделать повторяющиеся строки в excel
Как правило, может не быть хорошего и быстрого способа дублировать каждую строку определенного диапазона n раз на основе чисел в определенном столбце, кроме кода VBA в Excel. Но с Повторяющиеся строки / столбцы на основе значения ячейки полезности Kutools for Excel, вы можете быстро сделать это всего за несколько кликов.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Нажмите Kutools> Вставить> Дублировать строки / столбцы на основе значения ячейки, чтобы включить утилиту.
Повторяющиеся строки на основе значения ячейки в столбце
Предположим, у вас есть диапазон данных, как показано на скриншоте ниже. Теперь, чтобы дублировать каждую строку несколько раз на основе числовых значений в столбце F, сделайте следующее.
1. Нажмите Kutools > Вставить > Повторяющиеся строки / столбцы на основе значения ячейки. Смотрите скриншот:
2. в Копировать и вставлять строки и столбцы диалог, вам необходимо:
- Выберите Скопировать и вставить строкуs вариант в Тип раздел;
- Выберите диапазон, в котором вы будете дублировать строки в Вставить диапазон коробка;
- Выберите диапазон столбцов, в котором вы хотите дублировать строки на основе Время повтора коробка;
- Нажмите OK or Применить кнопка. Смотрите скриншот:
Внимание: Вы можете нажать Пример раздел, чтобы развернуть диалоговое окно для получения дополнительных сведений о примерах шагов.
Затем строки дублируются, как показано на скриншоте ниже.
Demo:
Повторяющиеся столбцы на основе значения ячейки в строке
Эта утилита также может помочь несколько раз дублировать каждый столбец на основе числовых значений в определенной строке.
1. Нажмите Kutools > Вставить > Повторяющиеся строки / столбцы на основе значения ячейки для включения утилиты.
2. в Копировать и вставлять строки и столбцы диалог, вам необходимо:
- Выберите Копировать и вставлять столбцы вариант в Тип раздел;
- Выберите диапазон, в котором вы будете дублировать столбцы в Вставить диапазон коробка;
- Выберите диапазон строк, в котором вы хотите дублировать столбцы на основе Время повтора коробка;
- Нажмите OK or Применить кнопка. Смотрите скриншот:
Внимание: Вы можете нажать Пример раздел, чтобы развернуть диалоговое окно для получения дополнительных сведений о примерах шагов.
Затем выбранные столбцы дублируются, как показано на скриншоте ниже.
Demo:
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Загрузите бесплатную пробную версию прямо сейчас!
Рекомендуемые инструменты для повышения производительностиСледующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2019, 2016, 2013, 2010, 2007 и Office 365.
Classic Menu for Office : Верните знакомые меню в Office 2007, 2010, 2013, 2016, 2019 и 365, как если бы это были Office 2000 и 2003.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
Допустим, что у нас есть вот такая таблица с именами людей, заказавших билет в кино и количеством билетов для каждого:
Каждому билету нужно присвоить уникальный 6-значный номер, который формируется здесь простой функцией СЛУЧМЕЖДУ (RANDBETWEEN) , генерирующей целое случайное число в заданном диапазоне 100000-999999. Да, я знаю, что теоретически совпадения могут быть, но вероятность очень невелика и пока нас, допустим, устраивает (кого не устраивает - см.эту статью). Проблема в другом: для тех, кто заказал больше одного билета, нужно вставить в таблицу новые строки-дубли (по количеству заказанных билетов), т.е. на выходе получить вот такое:
Руками такое делать - тоскливо, формулами - сложно. Так что остаются два наиболее удобных варианта - макросы и Power Query.
Способ 1. Создание дубликатов строк макросом
Откроем редактор макросов кнопкой Visual Basic на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F11 . Вставим новый модуль через меню Insert - Module и скопируем туда текст нашего макроса:
Принцип тут не самый сложный:
- проходим сверху-вниз по столбцу начиная с B2 до первой пустой ячейки
- если число в ячейке >1, то вставляем пустых строк под ячейкой на одну меньше, чем число билетов
- заполняем пустые ячейки (метод FillDown - аналог "протягивания за черный крестик" в правом нижнем углу ячейки)
- переходим к следующей ячейке и т.д.
Способ 2. Создание дубликатов строк в Power Query
Тем, кто хотя бы немного сталкивался с Power Query, рекламировать его мощь не нужно :) Для тех, кто не знаком (если коротко), то Power Query - это бесплатная надстройка для Excel от Microsoft, умеющая делать с данными практически все, что только можно себе представить: загрузку из любых источников, очистку, трансформацию, анализ данных и т.д. Для Excel 2010-2013 ее можно скачать с сайта Microsoft (появится отдельная вкладка Power Query после установки), а в Excel 2016 она уже встроена по-умолчанию (группа Получить внешние данные на вкладке Данные).
Power Query может легко и красиво решить нашу проблему с генерацией дубликатов.
Для начала, выделим нашу таблицу и загрузим ее в Power Query кнопкой Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или Power Query:
После окна подтверждения увидим редактор запросов и нашу таблицу. Добавим пользовательский столбец на вкладке Добавить столбец (Add Column - Custom Column) :
В появившемся окне введем имя столбца и формулу, которая создает список чисел от 1 до количества билетов в каждой строке:
После нажатия на ОК появится новый столбец со списками, элементы которых можно развернуть в строки, используя кнопку в шапке таблицы:
В итоге, получаем практически то, что хотелось:
Осталось удалить ненужный больше столбец Список (правой кнопкой мыши по заголовку - Удалить столбец) и выгрузить данные обратно на лист на вкладке Главная (Home) с помощью кнопки Закрыть и загрузить - Закрыть и загрузить в. (Close&Load - Close&Load to. ) и указать подходящее место для результирующей таблицы:
И останется совсем простая часть - добавить к таблице столбец с формулой СЛУЧМЕЖДУ (RANDBETWEEN) для генерации случайных номеров билетов:
Особенно приятно, что при любых изменениях в исходной (левой) таблице (добавлении новых людей или изменении количества билетов), достаточно будет просто обновить правой кнопкой мыши нашу результирующую таблицу с номерами билетов.
Если нужно, чтобы случайные числа не генерировались каждый раз заново при пересчете листа, а формировались один раз, сохраняя потом свои значения, то придется использовать макро-функцию StaticRandBetween из надстройки PLEX или что-то аналогичное.
Также можно, для наглядности, склеивать через дефис номер билета и порядковый номер из столбца Список прямо в Power Query, используя команду Объединить столбцы на вкладке Преобразование (Transform) .
Если таблица длинная, данных много, то удобно пользоваться автоматическим выводом заголовков таблицы на каждой странице при печати. В официальных формах документов такие заголовки имеют нумерацию, которую нужно переносить на каждую следующую страницу. Как это сделать в Excel?
Обращу внимание, что есть вариант закрепления строк или столбцов (когда они фиксируются на экране), а весь документ сткроллится (пролистывается). Это используется для удобства работы с таблицей на экране, но при печати строки выводятся один раз только на первом листе.
И есть вариант, который будет рассмотрен в этой статье. Строка или столбец показывается на экране один раз, но на печати выводится как шапка столько раз, сколько всего в итоге получится листов в печати.
Для этого перейдите на ленте в раздел Разметка страницы — значок в правом нижнем углу для вызова окна Параметры страницы — вкладка Лист.
Здесь и далее нажмите для увеличения
Далее можно выбрать сквозные строки или сквозные столбцы. Можно вписать диапазон вручную, но удобнее нажать на значок справа и выделить те строки, которые мы планируем сделать сквозными на каждой странице при печати.
Далее нажимаем ОК.
Теперь, если открыть предварительный просмотр документа на печати, увидим, что строки автоматически выводятся на каждом листе.
Как удалить сквозные повторяющиеся строки?
Для этого также перейдем в Параметры страницы — значок справа — вкладка Лист и удалим то, что написано в полях Сквозные строки и Сквозные ячейки. Скриншоты выше.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
Девиз: поднемите руки выше!
по
Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.
Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.
О работе с сайтом
Мы используем cookie.
Публикуя материалы на сайте (комментарии, статьи, разработки и др.), пользователи берут на себя всю ответственность за содержание материалов и разрешение любых спорных вопросов с третьми лицами.
При этом редакция сайта готова оказывать всяческую поддержку как в публикации, так и других вопросах.
Если вы обнаружили, что на нашем сайте незаконно используются материалы, сообщите администратору — материалы будут удалены.
В рамках моей постоянной работы несколько лет назад одной из вещей, с которыми мне приходилось иметь дело, было объединение данных из разных рабочих тетрадей, которыми пользуются другие люди.
И одной из распространенных задач было объединить данные таким образом, чтобы не было повторяющихся записей.
Например, ниже представлен набор данных, содержащий несколько записей для одного и того же региона.
И конечным результатом должен быть консолидированный набор данных, в котором каждая страна представлена только один раз.
В этом руководстве я покажу вам, как объединить повторяющиеся строки и суммировать значения для создания единого консолидированного набора данных.
Объединение и суммирование данных с помощью опции консолидации
Если все, что вам нужно сделать, это объединить данные и добавить все значения для повторяющихся записей, лучше всего использовать функцию консолидации в Excel.
Предположим, у вас есть набор данных, показанный ниже, в котором название страны повторяется несколько раз.
Хотя это уникальные записи, так как стоимость продаж различается, для целей отчетности вы можете удалить несколько экземпляров одной и той же страны и показать стоимость продаж как одну консолидированную сумму.
Ниже приведены шаги для этого:
Вышеупомянутые шаги объединят данные, удалив повторяющиеся записи и добавив значения для каждой страны.
В конечном результате вы получите уникальный список стран вместе со стоимостью продаж из исходного набора данных.
Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Счетчик» или «Среднее» или «Макс. / Мин.».
В этом примере я показал вам, как объединить данные в единый набор данных на листе. вы также можете использовать эту функцию для консолидации данных из нескольких листов в одной книге и даже из нескольких разных книг.
Объединение и суммирование данных с помощью сводных таблиц
Он может легко дать вам сводку, которая представляет собой комбинированный набор данных без дубликатов и значений, являющихся суммой всех похожих записей, и многое другое.
Обратной стороной этого метода по сравнению с предыдущим является то, что этот метод требует больше кликов и на несколько секунд больше по сравнению с предыдущим.
Предположим, у вас есть набор данных, показанный ниже, в котором название страны повторяется несколько раз, и вы хотите объединить эти данные.
Ниже приведены шаги по созданию сводной таблицы:
Вышеупомянутые шаги вставят сводную таблицу в выбранную целевую ячейку.
Теперь с помощью сводной таблицы мы можем делать все, что угодно, включая консолидацию набора данных и удаление дубликатов.
Ниже приведены шаги для этого:
- Щелкните в любом месте области сводной таблицы, и откроется панель сводной таблицы справа.
- Перетащите поле Country в область Row.
- Перетащите и поместите поле «Продажи» в область «Значения».
Вышеупомянутые шаги суммируют данные и дают вам сумму продаж по всем странам.
Если это все, что вам нужно, и вам не нужна сводная таблица, вы можете скопировать данные, вставить их как значения в другое место и удалить сводную таблицу.
Это также поможет вам уменьшить размер вашей книги Excel.
Итак, это два быстрых и простых метода, которые вы можете использовать для консолидации данных, где они объединяют повторяющиеся строки и суммируют все значения в этих записях.
Читайте также: