Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

 Як автоматично заповнювати формулу при вставці рядків у Excel?

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

doc автозаповнення формули, вставляючи рядок 1

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

Формула автоматичного заповнення при вставці порожніх рядків з кодом VBA


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


У Excel формат таблиці може допомогти вам автоматично заповнити вищезазначену формулу в нові вставлені порожні рядки, будь-ласка, виконайте такі дії:

1. Виберіть діапазон даних, який потрібно автоматично заповнити формулою, а потім клацніть Insert > таблиця, див. скріншот:

doc автозаповнення формули, вставляючи рядок 2

2, в Створити таблицю діалогове вікно, перевірте У моїй таблиці є заголовки якщо у ваших даних є заголовки, див. знімок екрана:

doc автозаповнення формули, вставляючи рядок 3

3. Потім натисніть OK , і таблиця була створена, і тепер, коли ви вставите порожній рядок, вищевказана формула буде автоматично вставлена ​​в новий рядок, див. знімок екрана:

doc автозаповнення формули, вставляючи рядок 4


Формула автоматичного заповнення при вставці порожніх рядків з кодом 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

doc автозаповнення формули, вставляючи рядок 5

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


Демонстрація: Формула автоматичного заповнення при вставці порожніх рядків


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Marcelo Moreira · 2 years ago
    Excellent simple piece of code!!!!
  • To post as a guest, your comment is unpublished.
    Niket Shet · 3 years ago
    Hi,

    This code is very helpful, however, I have a slightly different requirement. The formulas should be copied when the row is inserted manually, i.e. by "insert row".
    Can you please resend the code after incorporating this change i.e. instead of "Worksheet_BeforeDoubleClick", some other trigger point to be used "insert row/etc"

    Note - I do have any exposure to coding being from the Accounts background.


    Regards
    Niket Shet
  • To post as a guest, your comment is unpublished.
    David Banks · 3 years ago
    I am using the code to insert a line on double-click. However, there is something wrong, Excel does not increment the first reference in my formula in the next but one row. However, it is corrected for all remaining rows.
    I inserted a line at row 144 and this is what I got: (note S144 in 3rd line should be S145)
    row 144 =(S143+D144+E144)-(K144+L144+M144+N144+O144+Q144+R144)
    row 145 =(S144+D145+E145)-(K145+L145+M145+N145+O145+Q145+R145)
    row 146 =(S144+D146+E146)-(K146+L146+M146+N146+O146+Q146+R146)
    row 147 =(S146+D147+E147)-(K147+L147+M147+N147+O147+Q147+R147)
    Any clues as to why it does this, I don't think it is the VBA???
    David
  • To post as a guest, your comment is unpublished.
    gt · 4 years ago
    Does the table method work in excel for xp (2002) and if yes, how?
  • To post as a guest, your comment is unpublished.
    RR · 4 years ago
    The code works very well, but does not update cell reference in first row after the inserted row.See below"
    e.g
    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    RR · 4 years ago
    This code is very good, but it does not update the cell reference in the first following row after the inserted row. Can you please help?
    e.g:

    50 10 40
    40 10 50
    40 20 20
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 5 years ago
    Above formula is very helpful for me please tell me how to use this code permanently
  • To post as a guest, your comment is unpublished.
    Bilal shoukat · 5 years ago
    dear above formula which helps to update the above formula on inserting row by double clicking. I want to know how to use this code permanently because when i close the sheet and reopen code finishes. Please help to solve the issue. This formula is very helpful for me
    • To post as a guest, your comment is unpublished.
      David Hayes · 4 years ago
      Very easy solution. Thank you very much.
  • To post as a guest, your comment is unpublished.
    Bhavin · 5 years ago
    I followed the VBA code mentioned above for my xls. I have one .xls with multiple sheets in it , i want if i insert a row in my one sheet at the start or any of the sheet for that matter it should auto insert row in all my sheet in the same worksheet. Also each of my sheet as formulas hence i want the moment auto insert takes place the formula of above row should get autofill in the new row which is not happening totally if i follow the VBA code mentioned , the rows are getting inserted in all my sheets the formula also shows up but it misses one row in it.
    Eg. I have sheet A with 100 rows , sheet B with 100 ros and so on with other sheets. If I insert row in sheet A it shows auto insert in Sheet B , but sheet B values of some column are linked with sheet A and the moment i insert a row eg 10 , row 11 is a new row and the same row 11 in sheet B also shows up but the value it should pull from row 11 of sheet A from column C rather it shows row 12 of sheet A , this happens on any row i insert the value in sheet B is always of the next row rather than same row that i inserted. Another problem that comes here is with my first column Sr. no the moment i insert a row in 5 , sr no 6 with new row shows up but prior to that i already had row 6 as i have a formula for Sr. no A6+1 as my column starts from A6 and so on so how to sort this issue with 2 rows with same sr. no. This is very crucial sheet am working on and i need to get this sorted, i have refereed many VBA codes but none is effective. Hoping for a proper solution to my issue.