提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

在Excel中插入行時如何自動填充公式?

在現有數據行之間插入空白行時,公式不會復制並粘貼到新行中,您應該手動拖動或填充該行。 但是,有時候,當你在數據范圍之間插入空白行時,你會想填寫公式,如下圖所示,本文我將討論一些快速的技巧來解決這個任務。

doc自動填充公式插入行1

在創建表格時插入空白行時自動填充公式

使用VBA代碼插入空白行時自動填充公式


在創建表格時插入空白行時自動填充公式


在Excel中,表格格式可以幫助您自動將上述公式填充到新插入的空白行中,請按照以下步驟操作:

1. 選擇要自動填充公式的數據范圍,然後單擊 插入 > ,看截圖:

doc自動填充公式插入行2

2。 在 創建表格 對話框,檢查 我的表有標題 如果數據中有標題,請參閱截圖:

doc自動填充公式插入行3

3。 然後點擊 OK 按鈕,表格已經創建好了,現在,當你插入一個空白行時,上面的公式會自動插入到新行中,詳見截圖:

doc自動填充公式插入行4


使用VBA代碼插入空白行時自動填充公式

如果您不想將數據更改為表格,則下面的VBA代碼也可以幫助您實現此問題。

1。 選擇包含要自動填充的公式的工作表選項卡,然後右鍵單擊以選擇 查看代碼 從上下文菜單中去 Microsoft Visual Basic for Applications 窗口,然後將以下代碼複製並粘貼到模塊中:

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 for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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 · 4 months ago
    Excellent simple piece of code!!!!
  • To post as a guest, your comment is unpublished.
    Niket Shet · 10 months 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 · 1 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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.
    Bhavin · 3 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.