Автоматическое добавление строк в таблицу excel
Как автоматически вставлять строки в Excel?
Если вам нужно часто вставлять пустые строки на лист, вы можете щелкнуть правой кнопкой мыши всю строку и выбрать «Вставить», чтобы вставить пустую строку. Однако, возможно, метод щелчка правой кнопкой мыши вам недостаточно удобен. Теперь я представлю вам несколько быстрых клавиш для вставки пустых строк в Excel.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Автоматически вставлять строки с помощью ярлыков в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
К счастью, есть ярлыки, с помощью которых можно быстро вставить пустую строку в Excel.
Выделите всю строку, над которой вы хотите вставить пустую строку, и нажмите Shift + Ctrl + + вместе, то вставляется пустая строка.
Автоматически вставлять строки с помощью VBA
Есть код VBA, который может создать еще один ярлык для вставки пустой строки.
1. Нажмите Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Модули > Вставить вставить Модули window и скопируйте в него приведенный ниже VBA.
VBA: вставить пустую строку.
3. Закройте окно, вернитесь в Excel и нажмите Alt + F8 ключи для открытия Макрос Диалог.
4. в Макрос диалоговое окно, выберите Вставить строку из списка и нажмите Доступные опции. Смотрите скриншот:
5. в Параметры макроса диалог, нажмите Shift и R вместе, затем нажмите OK , чтобы вернуться к Макрос Диалог.
6. Нажмите Run закрывать Макрос диалог. Теперь нажмите Shift + Ctrl + R вместе, чтобы вставить пустую строку под активной ячейкой.
Добавление строк или столбцов в умной таблице не связано с добавлением строк на листе. Для подтверждения этого правила пронумеруем строки за пределами таблицы и добавим пару внутренних строк.
Автоматическое добавление строк в таблицу Excel
Чтобы в таблицу автоматически добавилась строка достаточно под данными ввести новое значение:
В результате в таблицу автоматически добавилась новая строка.
Интересный факт! Новые строки таблиц не связаны со строками листа. Чтобы убедиться в этом сделаем следующее:
- Пронумеруйте строки в диапазоне A2:A6 так, чтобы номера были за пределами.
- Вертикально выделите две ячейки в двух строках (например, B3:B4) и щелкните по ним правой кнопкой мышки.
- Выберите опцию в контекстном меню: «Вставить»-«Строки таблицы выше». Или выделите одну ячейку и два раза нажмите CTRL+SHIFT+«+».
Как видно одновременно вставилось 2 строки, та как было выделено 2 ячейки в двух строках. А номера каждой строки, которые находятся вне диапазона, остались без изменений и разрывов, что подтверждает независимость новых строк и столбцов таблицы от листа.
Автоматическое добавление столбцов в таблицу Excel
Полезная функция – автоматическое расширение после ввода любых данных в смежные ячейки (по вертикали и по горизонтали). Если данные введены в ближайшей ячейке справа от таблицы – добавится столбец, а если под таблицей – строка.
Расширим таблицу с помощью ввода данных в смежную ячейку. В ячейку D1 введите текст «Прибыль» и автоматически добавится новый столбец. После ввода в ячейку справа от нее появился инструмент с выпадающим меню:
- «Отменить авторазвертывание таблицы» – отменяет создание новой колонки. В этой ситуации для данной опции так же можно нажать комбинацию клавиш CTRL+Z – результат будет тот же.
- «Не развертывать таблицы автоматически» – опция полностью отключает автоматическое расширение всех таблиц при заполнении смежных ячеек.
- «Параметры автозамены» – открывает диалоговое окно «Автозамена» с активной закладкой «Автоформат при вводе».
Чтобы снова включить автоматическое добавление строк и столбцов таблицы при заполнении ближайших ячеек следует открыть настройки программы: «Файл»-«Параметры»-«Правописание»-«Параметры автозамены»-«Автоформат при вводе». На данной вкладке отмечаем галочкой опцию: «Выполнять в ходе работы»-«Включать в таблицу новые строки и столбцы».
В данном практическом примере приведен код макроса для умной вставки пустых строк между ячейками. Пошаговая инструкция как вставлять строки между объединенными и необъединенными ячейками одновременно для любой таблицы.
Макрос для вставки строк с определенной высотой
В таблице отчета по продажам в розничных точках содержаться объединенные ячейки, как показано ниже на рисунке:
Необходимо экспонировать группы ячеек для каждого штата. Для этого мы добавим по одной пустой строке между каждой группой розничных точек. При этом не имеет значение будет ли содержать группа объединенные ячейки или нет. Ведь некоторые группы состоят из одной строки. А также дополнительно уменьшим высоту этих пустых строк, чтобы внешний вид таблицы был стильным. Выполнить вручную все эти действия: выделение отдельных групп ячеек, вставка между ними пустых строк, а потом изменение высоты для этих же строк – это не рационально использование сил и времени. Особенно если таблица имеет десятки тысяч строк. Лучше написать свой макрос, который сам автоматически и молниеносно выполнит эту рутинную работу за Вас.
Перейдите в режим редактора макросов Visual Basic (ALT+F11):
Создайте в нем новый модуль с помощью инструмента: «Insert»-«Module». А потом запишите в него VBA-код самого макроса:
Sub VstavkaStrok()
Dim i As Long
Dim pustroka As Long
For i = Selection.Rows.Count To 2 Step -1
pustroka = Selection(i, 1).Row + 1
ActiveSheet.Rows(pustroka).Insert xlShiftDown
ActiveSheet.Rows(pustroka).RowHeight = 7
ActiveSheet.Rows(pustroka).Borders(xlInsideVertical). _
LineStyle = xlLineStyleNone
ActiveSheet.Rows(pustroka).Borders(xlEdgeLeft). _
LineStyle = xlLineStyleNone
ActiveSheet.Rows(pustroka).Borders(xlEdgeRight). _
LineStyle = xlLineStyleNone
ActiveSheet.Rows(pustroka).Interior. _
ColorIndex = xlColorIndexNone
i = i - Selection(i, 1).MergeArea.Rows.Count + 1
Next
End Sub
Теперь если мы хотим вставить по одной пустой строке между каждой объединенной и необъединенной ячейкой, которые находиться в столбце A? Тогда а в таблице отчета по продажам выделяем диапазон ячеек A:D18 и запускаем наш макрос выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«VstavkaStrok»-«Выполнить». После запуска макроса таблица будет выглядеть как показано на рисунке:
Сначала в коде объявлены две переменные:
- i – переменная выполняет роль счетчика в цикле.
- pustroka – переменная будет хранить в себе очередной номер для каждой строки выделенного диапазона.
В макросе находиться цикл, который запускает целый ряд инструкций для каждой строки в выделенном диапазоне ячеек. Направление цикла идет с нижней строки выделения к верхней.
- В первой инструкции мы присваиваем для переменной pustroka номер строки которая находиться под текущей строкой.
- Следующая инструкция добавляет пустую строку с высотой в 7 пикселей.
- Удаляются в добавленной строке все вертикальные границы, а также заливка.
- Уменьшается значение переменной i на количество строк, которые охватывает текущая объединенная ячейка, находящаяся в первом столбце выделенного диапазона.
Умная вставка строк с помощью макроса
Если же мы хотим экспонировать только самые большие группы. Допустим Вы желаете сделать так, чтобы макросом были вставлены пустые строки только после объединенных ячеек в столбце A, которые охватывают много строк. И не вставлять пустые строки после необъединенных ячеек или тех объединенных ячеек, которые охватывают не более 1-ой строки. Тогда после строки в коде макроса где описано начало цикла добавляем строку кода с условной инструкцией:
If Selection(i, 1).MergeArea.Rows.Count <> 1 Then
Также перед инструкцией конца цикла Next следует вставить инструкцию конца условия – End If.
Обратите внимание! Параметр условия для игнорирования объединенных ячеек с определенным количеством озвучиваемых строк можно будет даже настраивать, изменяя число после оператора сравнения.
Такая модификация кода макроса внутри цикла будет следить за тем применять ли ряд инструкций к текущей строке или игнорировать их на данном этапе прохода по срокам. Если же текущая строка не содержит необъединенной ячейки или объединенная ячейка охватывает более чем 1-ну строку, тогда для нее применяться все инструкции форматирования. Полная версия модифицированного года выглядит так:
Sub VstavkaStrok1()
Dim i As Long
Dim pustroka As Long
For i = Selection.Rows.Count To 2 Step -1
If Selection(i, 1).MergeArea.Rows.Count <> 1 Then
pustroka = Selection(i, 1).Row + 1
ActiveSheet.Rows(pustroka).Insert xlShiftDown
ActiveSheet.Rows(pustroka).RowHeight = 7
ActiveSheet.Rows(pustroka).Borders(xlInsideVertical). _
LineStyle = xlLineStyleNone
ActiveSheet.Rows(pustroka).Borders(xlEdgeLeft). _
LineStyle = xlLineStyleNone
ActiveSheet.Rows(pustroka).Borders(xlEdgeRight). _
LineStyle = xlLineStyleNone
ActiveSheet.Rows(pustroka).Interior. _
ColorIndex = xlColorIndexNone
i = i - Selection(i, 1).MergeArea.Rows.Count + 1
End If
Next
End Sub
Результат автоматического форматирования таблицы отчета с учетом новых условий в коде макроса:
Как видите с помощью макросов таблицы любых объемов данных можно форматировать в один клик мышкой.
Вставить одну, пять или десять пустых строк в Excel не составляет труда. А с помощью надстройки можно быстро вставить большое количество строк на лист Excel, а также добавлять строки по различным условиям.
Описание работы
Иногда необходимо вставлять большое количество пустых строк по различным условиям и в ручную этот процесс занимает много времени. Для упрощения данного процесса в функционал надстройки добавлена функция «Вставить пустые строки».
Чтобы воспользоваться функцией перейдите на вкладку «VBA-Excel» в разделе «Ячейки и диапазоны» откройте меню «Вставить», выберите «Вставить пустые строки».
Появится диалоговое окно с выбором опций для вставки строк. Необходимо выбрать опции в соответствии с тем какая стоит задача.
Вставка произвольного количества строк
Для этого в поле «Условие» необходимо выбрать пункт «Вставить N пустых строк»
Для того что бы указать в какую строку вставлять пустые строки нужно в поле «Начиная со строки» указать номер нужной строки.
Это можно сделать двумя способами:
- Вручную ввести номер строки.
- Нажать кнопку «…» расположенную рядом с полем «Начиная со строки», после чего откроется окно выбора строки. Далее нужно выбрать любую ячейку находящуюся в нужной строку и нажать «Ок»
После чего в поле Количество указать сколько пустых строк нужно вставить. И нажать кнопку «Ок»
Например, нужно вставить 5 пустых строк с 7 строки. тогда ввод в диалоговое окно будет выглядеть следующим образом:
Вставка строк через заданное количество строк
Для этого в поле «Условие» должно быть выбрано «Вставлять через каждые N строк».
В данном режиме нужное количество строк вставляется через равные промежутки в пределах, указанных в поле «Диапазон»
В поле «Диапазон» можно указать «Текущий лист» или «Выделение».
- В первом случае вставляются пустые строки с первой строки до последней, в которой есть какие-либо значения.
- Во втором пустые строки вставляются только в рамках текущего выделения.
Как и в первом режиме нужно указать строку, с которой начнет работать алгоритм и количество пустых строк
А в поле «Вставлять через» указывается сколько строк будет пропускаться при вставке следующих пустых строк.
Например, нужно вставить 2 пустые строки через каждые 2 строки, начиная с 6 строки, тогда диалоговое окно примет следующий вид:
Вставка строк при изменении значения в столбце
Для этого в поле «Условие» должно быть выбрано «Вставлять по изменению значения в столбце»
В этом режиме пустые строки вставляются в том случае если в выбранном столбце меняется значение.
Для указания столбца, по которому просматриваются значения, нужно в поле «Столбец» ввести имя столбца. Это можно сделать двумя способами:
- Вручную ввести имя столбца
- Нажать кнопку «…» расположенную рядом с полем «Столбец», после чего откроется окно выбора столбца. Далее нужно выбрать любую ячейку находящуюся в нужном столбце и нажать «Ок»
Например, вам нужно отделить каждый новый артикул в столбце А пустой строкой. Тогда диалоговое окно примет вид:
Вставка строк при определенном значении в столбце
Для этого в поле «Условие» должно быть выбрано «Вставлять по значению в столбце».
В этом случае вставка пустых строк происходит по совпадению значения выбранного столбца со значением указанным в поле «Значение».
Например, нужно вставить пустые строки там, где в столбце «Флаг» находится значение «f». В этом случае выбираем следующие настройки:
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Предположим, у вас есть диапазон данных, и вы хотите автоматически вставлять пустые строки выше или ниже определенного значения в Excel, например, автоматически вставлять строки ниже нулевого значения, как показано ниже. В Excel нет прямого способа решить эту задачу, но я могу ввести код макроса для автоматической вставки строк на основе определенного значения в Excel.
Вставить строку ниже на основе значения ячейки с помощью VBA
Вставить строку выше на основе значения ячейки с помощью Kutools for Excel
Вставить строку ниже на основе значения ячейки с помощью VBA
Чтобы вставить строку на основе значения ячейки, запустив VBA, выполните следующие действия:
1. Нажмите Alt + F11 одновременно, а Microsoft Visual Basic для приложений окно выскакивает.
2. Нажмите Вставить > Модули, затем вставьте ниже код VBA во всплывающий Модули окно.
VBA: вставьте строку ниже на основе значения ячейки.
3. Нажмите F5 ключа или Run нажмите кнопку, появится диалоговое окно и выберите столбец, содержащий ноль. Смотрите скриншот:
4. Нажмите OK. Тогда пустые строки будут вставлены ниже нулевого значения.
Функции:
1. Если вы хотите вставить строки на основе другого значения, вы можете изменить 0 на любое значение, которое вы хотите в VBA: Если Rng.Value = "0", то .
2. Если вы хотите вставить строки выше нуля или другого значения, вы можете использовать приведенный ниже код vba.
VBA: вставить строку выше нулевого значения:
Вставить строку выше на основе значения ячейки с помощью Kutools for Excel
Если вы не знакомы с VBA, вы можете попробовать Kutools for Excel's Выбрать определенные ячейки утилита, а затем вставьте строки выше.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите список, из которого вы хотите найти определенные ячейки, и нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. В появившемся диалоговом окне отметьте Весь ряд вариант, а затем перейдите, чтобы выбрать Равно из Конкретный тип список, а затем введите значение, которое вы хотите найти, в правом текстовом поле. Смотрите скриншот:
3. Нажмите Ok, и появится диалоговое окно, напоминающее количество выбранных строк, просто закройте его.
4. Поместите курсор в одну выбранную строку и щелкните правой кнопкой мыши, чтобы выбрать Вставить из контекстного меню. Смотрите скриншот:
Теперь строки вставляются выше на основе определенного значения.
Читайте также: