メインコンテンツへスキップ

 Excelに行を挿入するときに数式を自動入力するにはどうすればよいですか?

既存のデータ行の間に空白行を挿入すると、数式はコピーされて新しい行に貼り付けられないため、手動でドラッグまたは入力する必要があります。 ただし、次のスクリーンショットに示すように、データ範囲の間に空白行を自動的に挿入するときに数式を入力したい場合があります。この記事では、このタスクを解決するための簡単なトリックについて説明します。

行1を挿入するドキュメントの自動入力式

テーブルの作成時に空白行を挿入する際の数式の自動入力

VBAコードで空白行を挿入するときの自動入力式


テーブルの作成時に空白行を挿入する際の数式の自動入力

Excelでは、テーブル形式を使用すると、上記の数式を新しく挿入された空白行に自動的に入力できます。次のようにしてください。

1. 数式を自動入力するデータ範囲を選択し、[ インセット > 、スクリーンショットを参照してください:

行2を挿入するドキュメントの自動入力式

2。 の中に テーブルの作成 ダイアログで、 私のテーブルにはヘッダーがあります データにヘッダーがある場合は、スクリーンショットを参照してください。

行3を挿入するドキュメントの自動入力式

3。 次に、をクリックします OK ボタンをクリックすると、テーブルが作成されます。空白の行を挿入すると、上記の数式が新しい行に自動的に挿入されます。スクリーンショットを参照してください。

行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

行5を挿入するドキュメントの自動入力式

2。 次に、このコードを保存して閉じ、ワークシートに戻ります。データ間のセルをダブルクリックすると、アクティブセルの下に新しい行が挿入され、数式も自動的に入力されます。


デモ:空白行を挿入するときの数式の自動入力

最高のオフィス生産性向上ツール

🤖 Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行   |  コードを生成  |  カスタム数式の作成  |  データを分析してグラフを生成する  |  Kutools関数を呼び出す...
人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト ツール (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 ツール (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 ツール (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット ツール (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

説明


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This works GREAT, until I protect the worksheet (to protect my forumulas in other cells). Can you let me know what i need to do to keep this functionality once the worksheet is protected?
This comment was minimized by the moderator on the site
Excellent simple piece of code!!!!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi Niket. You can see all the options for different trigger points in the tab itself.

For this click on CTRL + F11 to open the code editor as explained in the video above, and there will be 2 dropdowns on the top of the code which you would copy, click on the second dropdown and you can find many options for different trigger points.

The first dropdown would be ideally set to "Worksheet" and the second one would be set to "BeforeDoubleClick". You can change the second trigger point to your liking and use accordingly.

Hope it helps!
This comment was minimized by the moderator on the site
I had the same question as Niket, so I tried looking at the list of trigger points in the second dropdown and I don't see anything that clearly works as Niket or I intend with the (select row+right click+insert row functionality). I tried the "BeforeRightClick" option but it just inserts a row w/formulas anytime I right click a cell, which is definitely not what I was looking for. Is there a resource somewhere that explains what each of the triggers can be used for? Or is there possibly another way to get to this functionality?

Thanks!
Jeff
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Does the table method work in excel for xp (2002) and if yes, how?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Above formula is very helpful for me please tell me how to use this code permanently
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hi Bilal. You need to save the file as a "Macro Enabled" file to get the code to work once you close and reopen the file. To save the file as Macro, just use "Save as" and then select "Excel-Macro Enabled Workbook" to get the desired result.
This comment was minimized by the moderator on the site
Very easy solution. Thank you very much.
This comment was minimized by the moderator on the site
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.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations