Как повторить строку в excel
Возможно, вам потребуется сделать определенную строку, например строку заголовка, всегда видимой при прокрутке листа. Эта статья покажет вам, как этого добиться.
Повторять строки при прокрутке на листе
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Функция Freeze Panes в Microsoft Excel может помочь вам повторять строки при прокрутке листа. Пожалуйста, сделайте следующее.
1. На листе, в котором вам нужно повторять строки при прокрутке, щелкните «Вид»> «Закрепить области».
2. Если вы просто хотите повторить первую строку заголовка на этом листе, выберите Freeze Top Row из файла Замерзшие оконные стекла выпадающий список.
Чтобы повторить определенную строку, выберите первую ячейку под этой строкой, затем нажмите Замерзшие оконные стекла из Замерзшие оконные стекла раскрывающийся список. Смотрите скриншот:
Повторять строки при прокрутке на всех листах
Если вы хотите применить закрепленные области ко всем листам в текущей книге одновременно. Пожалуйста, попробуйте заморозить несколько листов утилитой Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите первую ячейку под указанной строкой, которую нужно повторить при прокрутке листа, затем щелкните Kutools Plus > Рабочий лист > Закрепить области нескольких листов.
Затем закрепленные области применяются сразу ко всем листам текущей книги. При прокрутке листа указанная строка всегда будет отображаться вверху.
Чтобы отключить эту утилиту, нажмите Kutools Plus > Рабочий лист > Разморозить панели нескольких листов как показано ниже.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
Допустим, что у нас есть вот такая таблица с именами людей, заказавших билет в кино и количеством билетов для каждого:
Каждому билету нужно присвоить уникальный 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) .
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Вы когда-нибудь пытались повторить строку на основе другого значения столбца, как показано на скриншоте ниже? В этой статье я расскажу о методе решения этой задачи в Excel.
Повторить строки на основе другого значения столбца с VBA
Здесь у меня есть код, который может быстро повторять строки на основе последних значений столбца.
1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модуль, затем скопируйте приведенный ниже код и вставьте его в новый Модуль скрипты.
VBA: повторять строки на основе другого значения столбца
3. Нажмите F5 , появится диалоговое окно, выберите список номеров, на основе которого будут повторяться строки.
4. Нажмите OK, строки повторяются на основе выбранного значения столбца.
Повторять строки с фиксированным временем Kutools for Excel
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите диапазон данных, в котором должны повторяться строки, щелкните Kutools > Вставить > Вставить пустые строки и столбцы. Тогда в Вставить пустые строки и столбцы диалог, проверьте Пустые строки вариант, затем введите 1 в Интервал of и введите число, в котором вы хотите повторить строки Ряды текстовое окно.
2. Нажмите Ok, пустые строки были вставлены под каждой строкой.
3. Не снимая выделения с диапазона, щелкните Kutools > Вставить > Заполнить пустые ячейки. В Заполнить пустые ячейки диалог, проверьте На основе ценностей и вниз настройки.
4. Нажмите Ok. Теперь пустые ячейки заполнены указанным выше значением.
Читайте также: