Вставка строк в excel с копированием формул - Word и Excel

В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.

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

Как добавить новую строку с формулами в Excel?

Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?

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

Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).

После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.

Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).

Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.

Как копировать без изменения формул в Excel?

Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?

Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».

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

На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:

Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.

Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».

Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.

Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»).

Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все».

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

Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места

В результате при копировании во всех строках ссылки формул остались неизменными.

Источник

Добрый день,

Есть таблица с формулами и столбцом с выпадающим списком, нужно чтобы макрос по нажатию кнопки около итогов добавлял новую строку и копировал форматы и формулы без значений из строки выше.
На форуме я находил макрос вставки строк, но он привязан к activecell, соответственно нужно каждый раз руками выбирать ячейку над которой нужно вставить строку, и строка вставлялась пустая. А нужно чтобы макрос автоматом вставлял над итогом новую строку. Модифицировать макрос у меня не получилось, я пока только учусь :oops:

Жду помощи и заранее больше спасибо.

delete as…

Пользователь

Сообщений: 4
Регистрация: 05.02.2014

05.02.2014 13:11:04

Я пока смог только в добавление строки над итогом. Как скопировать формулы не разобрался пока

Код
Sub vststr() With ActiveSheet LastRow = .Cells(65536, 3).End(xlUp).Row .Rows(LastRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End With End Sub

	МатросНаЗебре, Спасибо, работает Код более изящный, пойду разбираться, спасибо!

Sanja

Пользователь

Сообщений: 14838
Регистрация: 10.01.2013

05.02.2014 13:28:05

Вариант

Код

Sub vststr()
    With ActiveSheet
        LastRow = .Cells(Rows.Count, 3).End(xlUp).Row
        With .Rows(LastRow)
            .Copy
            .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End With
            .Rows(LastRow + 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub

Согласие есть продукт при полном непротивлении сторон.

	Sanja, спасибо, тоже работает! но можно ли при вставке сделать проверку на формулы и вставлять только их? в текущих вариантах вставляются и ячейки с данными забитыми руками Изменено: delete as… — 05.02.2014 14:36:46

	Знаю, что попросите в следующий раз — высылаю желаемое. Сумма в итогах исправлена.

Добрый день!
У меня вопрос похож на проблему создателя темы:
При нажатии на кнопку «Button1» добавляется то кол-во строк, которое указано на листе «CNR» в ячейке A1, но добавляются они с форматом 2-й строки, а нужно, что строки добавлялись с форматом и формулами нижней строки.

Прикрепленные файлы

Sample.xlsm (20.72 КБ)

Сергей Эм Пользователь Сообщений: 61 Регистрация: 01.01.1970	МатросНаЗебре, при добавлении строки не создается выпадающий список по валюте.

Сергей Эм Пользователь Сообщений: 61 Регистрация: 01.01.1970	Sanja, вылетает ошибка Compile error: Variable not defined при запуске макроса. Указывает на LastRow =

zaolog

Пользователь

Сообщений: 58
Регистрация: 19.01.2016

#12

02.02.2020 13:13:24

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

Код

Sub Добавление_строк_пользователем()

'для получения выбранного значения
Dim vRetVal
vRetVal = InputBox("Введите номер добавления строки:", "Добавление нужной строки", "")

'используем Val для преобразования текста vRetVal в число
'Val() преобразует число как текст в число.
'Если будет указан текст(например "третий") - он будет преобразован в 0
vRetVal = Val(vRetVal)
   
    If Val(vRetVal) = 0 Then
        MsgBox "Номер строки должен быть целым числом больше нуля!", vbCritical, "DelCols"
        Exit Sub
    End If

Rows(vRetVal).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Прикрепленные файлы

Вставка строк.xlsm (16.17 КБ)

Изменено: zaolog — 02.02.2020 13:14:11

Источник

Добавление строки с копированием текста и формул

Leprotto

Дата: Четверг, 23.07.2015, 11:09 |
Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 139

Репутация:
1
±

Замечаний:
0% ±

Excel 2016

Добрый день! Пример приложил. Задача: добавить строку ниже при заполнении ячейки в столбце G. При этом в добавленную строку из столбцов B-E cкопировать текст и из столбцов AC-AG скопировать формулы. Со столбцами F-AB ничего не делать. Заранее благодарю!

К сообщению приложен файл:

__.xlsm
(14.8 Kb)

Сообщение отредактировал Leprotto — Четверг, 23.07.2015, 11:10

Ответить

KSV

Дата: Четверг, 23.07.2015, 12:13 |
Сообщение № 2

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация:
255
±

Замечаний:
0% ±

Excel 2013

Добрый день!
Так?

К сообщению приложен файл:

-1-.xlsm
(20.4 Kb)

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Leprotto

Дата: Четверг, 23.07.2015, 12:46 |
Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 139

Репутация:
1
±

Замечаний:
0% ±

Excel 2016

KSV, Да, в точности так. Спасибо огромное!!!

Ответить

KSV

Дата: Четверг, 23.07.2015, 12:48 |
Сообщение № 4

Группа: Друзья

Ранг: Ветеран

Сообщений: 770

Репутация:
255
±

Замечаний:
0% ±

Excel 2013

пожалуйста!

KSV.VBA@gmail.com
Яндекс.Деньги: 410011921213333

Ответить

Источник

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

Формула автозаполнения при вставке пустых строк при создании таблицы

Формула автозаполнения при вставке пустых строк с кодом VBA

Формула автозаполнения при вставке пустых строк при создании таблицы

В Excel формат таблицы может помочь вам автоматически заполнить приведенную выше формулу в новые вставленные пустые строки, пожалуйста, сделайте следующее:

1. Выберите диапазон данных, в котором вы хотите автоматически заполнить формулу, а затем щелкните Вставить > Настольные, см. снимок экрана:

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

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

Формула автозаполнения при вставке пустых строк с кодом VBA

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

1. Выберите вкладку рабочего листа, содержащую формулы, которые вы хотите автоматически заполнить, и щелкните правой кнопкой мыши, чтобы выбрать Просмотреть код из контекстного меню, чтобы перейти к Microsoft Visual Basic для приложений окно, а затем скопируйте и вставьте следующий код в модуль:

Код VBA: формула автозаполнения при вставке пустых строк

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

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

Демонстрация: формула автозаполнения при вставке пустых строк

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (13)

Оценок пока нет. Оцените первым!

Источник

0 / 0 / 0

Регистрация: 25.08.2015

Сообщений: 33

Автоматическое копирование формул при добавлении строки

27.09.2016, 14:32. Показов 18941. Ответов 5

В таблицу часто вносятся данные путём добавления строк (зачастую в хаотичном порядке то в середину то выше то ниже, но главное что не в первую и не в последнюю строку)
А на против этих данных есть формулы.
Так вот, можно ли как то при добавлении новой строки заставить Exel автоматически скопировать формулы с верхней строки???

Если да то как???

Простой пример прилагаю.

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

27.09.2016, 14:53

Сообщение было отмечено Kamekadze_rus как решение

Решение

Отформатируйте таблицу как Таблицу и тогда все Ваши пожелания реализуются.

0 / 0 / 0

Регистрация: 25.08.2015

Сообщений: 33

27.09.2016, 15:01

[ТС]

А как это сделать? ))))
Обвожу ячейки нажимаю вторую кнопку мыши формат ячеек… А там нет таблицы (((
Я так понимаю я что то не так делаю???

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

27.09.2016, 15:08

Kamekadze_rus, выделить таблицу — Главная — группа Стили — Форматировать как таблицу.
В столбцах должно быть названия и не объединяйте ячейки

2640 / 1697 / 694

Регистрация: 04.09.2015

Сообщений: 3,367

27.09.2016, 15:11

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

0 / 0 / 0

Регистрация: 25.08.2015

Сообщений: 33

27.09.2016, 15:16

[ТС]

Всем большое спасибо!
Теперь разобрался где найти формат таблицы!!!
И про формулу МИН тоже спасибо за подсказку! учту при составлении таблицы.

IT_Exp

Эксперт

87844 / 49110 / 22898

Регистрация: 17.06.2006

Сообщений: 92,604

27.09.2016, 15:16

Помогаю со студенческими работами здесь

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

QTextBrowser — автоматическое копирование текста при нажатии
Здравствуйте, есть определённый текст в QTextBrowser, мне нужно выделить одну из частей текста и…

Как сделать автоматическое копирование данных при их изменении?
Я не люблю C++, но вынужден программировать на нем. До этого я пользовался языком, в котором нет…

Excel. Копирование столбца, при заполнении 22-й строки продолжить копирование в соседний столбец
Всем привет. Задача:
Есть таблица Excel, заполнены 2 столбца, из них первый — порядковые номера,…

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:

Источник

Как добавить новую строку с формулами в Excel?

Как копировать без изменения формул в Excel?

Формула автозаполнения при вставке пустых строк при создании таблицы

Формула автозаполнения при вставке пустых строк с кодом VBA

Демонстрация: формула автозаполнения при вставке пустых строк

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Автоматическое копирование формул при добавлении строки

Решение

А вот еще интересные статьи: