【エクセル】行挿入で数式もいっしょにコピーしたい

質問

こんにちは。
エクセルで現金出納帳を作成しています。
例えば・・・

　　Ａ列　　　Ｂ列　Ｃ列　Ｄ列　Ｅ列
１　日にち　　科目　収入　支出　残高
２　10/1　　光熱費　　　　10000
３　10/2　　給料　　200000　　　190000
４　・・・・・・

Ｅ列には=IF(E2="","",IF(AND(C3="",D3=""),"",E2+C3-D3))
という数式を入れています。
後からデータを足したい時（例えば３行目に挿入）に、行挿入をすると
数式もコピーされ、以下の行の数式も更新されるようにしたいのですが、どのようにしたら良いのでしょうか。

ご回答宜しくお願い致します。

回答No.5

Wendy02 · ベストアンサー

#3の回答者です。

再度、書き直しておきます。ある程度Excelを使ってきた人なら、使ったことがある機能だと思いますが、行の挿入と式のコピーを同時にするのは、マクロでなくても出来ます。

# 「リスト」に変更してください。挿入すれば、式はコピーされます。
というのは、

Excelのバージョンにもよりますが、近年のバージョンなら「リスト」という機能があります。

リストは、範囲をマウスで選択してから、「データ」--「リスト」--リストの作成
です。タイトル行が、太字になったりするので、それは、気に入らなければ、書式を訂正してください。なお、そのリスト範囲にカーソルを持っていくと、ドロップダウンボタンが現れてしまいます。印刷時には現れませんが。

回答No.4

masah43

もう少し条件がはっきりするといいのですが…
１．行挿入した時点で挿入行と他の列が一気に自動更新されるようにしたいのですか？あるいはあとでセルをコピーする手作業が入ってもいいですか？
２．セルＥ２は空白ですか？繰越残高が必要と思われますが…
３．これは想像ですが行挿入したときＥ列の計算式の計算対象がずれるのが解決できればよいのではと思ったのですがどうでしょうか？

ちなみに「一気に自動更新」はマクロでないとできません。

回答No.3

Wendy02

こんにちは。

最初に、数式がおかしくありませんか？
収入があったときに、残高を出すのでしょうか？

------------------------------------
　　　A　　　 B　　　 C　　　 D　　　 E
1: 日にち　　科目　　収入　　支出　　残高
2: 繰越残高　　　　　　　　　　　　　10000
3: 10月1日　　光熱費　　　　10000　　
4: 10月2日　　給料　　200000　　　　200000

(等幅フォントでないと、ずれて見えますが、全角空白で整えています）

E3:の式です。
=IF(COUNT(C3),$E$2+SUM($C$3:C3)-SUM($D$3:D3),"")

>数式もコピーされ、以下の行の数式も更新されるようにしたいのです

それは、「リスト」に変更してください。挿入すれば、式はコピーされます。

回答No.2

zap35

＞行挿入をすると数式もコピーされ、
これは簡単そうで簡単ではありません。マクロが必須ですし、それも本格的にやるならクラスモジュールという、あまり一般的ではない「難しめ」のテクニックが必要です。

そこで、もう少し簡単な方法を回答します（それでもマクロは必要です）
それは「A列からD列に値を入力したとき、E列に数式が入力されていなかったら、E列の一つ上のセルをコピーする」というものです。
ご要望とは多少意味が違いますが、これでも十分省力化になるのではないでしょうか

以下のマクロを現金出納帳のシートを開き「シート名タブ右クリック」→「コードの表示」で開く画面に貼り付けてください。貼り付けたら現金出納帳のシートを再び開き、行挿入して値を入力してみてください

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim rng, r As Range
　Set rng = Intersect(Columns("A:D"), Target)
　If Not rng Is Nothing Then
　　For Each r In rng
　　　If Cells(r.Row, "E").HasFormula Then
　　　Else
　　　　If r.Row > 1 Then
　　　　　If Cells(r.Row - 1, "E").HasFormula Then
　　　　　　Cells(r.Row - 1, "E").Copy Destination:=Cells(r.Row, "E")
　　　　　End If
　　　　End If
　　　End If
　　Next r
　End If
End Sub

なお「上のセルの式をコピー」する方式にしたのは、マクロで式を直接書き込む方式にすると、シートの式を変更したときにマクロも変更する必要があるからです。質問者さまにそれが可能かどうかわからないため「コピー」にしました。ですからE列の上のセルに式がないときは何もしません。その点はご承知おきください

回答No.1

yuusan04

エクセルを複数起動するのではなく、仮に　現金出納帳　を　Ａファイル　コピーを持って生きたいファイルを　Ｂファイル　とすると、

１）Ａファイルを開く
２）エクセルの左上にある　ファイル（Ｆ）　をクリックする
３）開く（Ｏ）をクリックする
４）Ｂファイルを開く
５）ウインドウ（Ｗ）をクリックする
６）Ａファイルを選択する
　　※画面に出ていないときには下向きの二重のカギカッコをクリックしてください。
７）コピーする行の数字（画面一番左）を選択してコピーする。
８）Ｂファイルを選択して行を選択し、貼り付けする。

これでできると思います。

できなかった理由は、エクセルを複数起動させていたために数式が文字に変換されてコピーされたためだと思います。