グッドデザイン賞を受賞したウォーターサーバー >>

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

  A列   B列 C列 D列 E列
1 日にち  科目 収入 支出 残高
2 10/1  光熱費    10000
3 10/2  給料  200000   190000
4 ・・・・・・

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

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

#3の回答者です。



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

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

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

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

もう少し条件がはっきりするといいのですが…


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

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

こんにちは。



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

------------------------------------
   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),"")

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

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

>行挿入をすると数式もコピーされ、


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

そこで、もう少し簡単な方法を回答します(それでもマクロは必要です)
それは「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列の上のセルに式がないときは何もしません。その点はご承知おきください
    • good
    • 4

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



1)Aファイルを開く
2)エクセルの左上にある ファイル(F) をクリックする
3)開く(O)をクリックする
4)Bファイルを開く
5)ウインドウ(W)をクリックする
6)Aファイルを選択する
  ※画面に出ていないときには下向きの二重のカギカッコをクリックしてください。
7)コピーする行の数字(画面一番左)を選択してコピーする。
8)Bファイルを選択して行を選択し、貼り付けする。

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセル。行を挿入すると、関数が反映されない

とても困っているのでどうかお願いします。
たとえば、ある列にエクセル関数を挿入したとします。
今回私が挿入したのはIF関数でした。=IF(C1="","",10)をC1に入れて、それより下は、C2,C3,,とドラッグしていきました。
その途中に行を挿入したとき、たとえば15行目に挿入したとき、理想は勝手に
。=IF(C15="","",10)
となってくれることです。しかしやってみると、実際にはそうなっていません。
関数は全く入っておらず、空欄になっています。

これでは、行を挿入するたびに関数をまた入れなおししないといけないということでしょうか?
マクロなどでしか解決できないのでしょうか?(それとも条件付書式などで、列そのものに挿入するのでしょうか?)
ちなみに条件付書式の場合、どこかからセルを貼り付けするとき、値のみにしないと、そのセルの書式も変更されてしまいますよね?だからあまり使いたくないのですが、、、

どなたかご存知の方はどうかお願いいたします。

Aベストアンサー

数式が入っている行を行ごとコピーした状態で、挿入したい行を右クリックすれば、「コピーしたセルの挿入」というメニュー項目があります。これを選択すれば、挿入された行にも数式が設定されています。

Qエクセルで行挿入した際、自動的に計算式も入力される方法を教えて下さい。

例えば、A列は単価を入力する欄、B列には数量を入力する欄、C列には=A1*B1(1行目の場合)の計算式が入っている表があるとします。
表の途中で行挿入すると、自動的にCのセルに数式が入力される方法はありますでしょうか?
複数の人に毎月この表(実際はもっと列が多い表)に入力して貰って集計等をしているのですが、各個人で表の途中で行挿入して行を増やした際、数式がコピーされてない人がいるため、行挿入すると自動的に数式が入力される方法がないかと思っております。
エクセルのバージョンは2000又は2003、行挿入は必要なので、行挿入を禁止する事はできません。
宜しくお願いします。

Aベストアンサー

こんにちは。

Excel2003 ですと、その場所をリストに変更しておけば、自動的に数式は入ります。

Excelの2000の場合ですが、簡単に言えば、行の挿入のそれぞれのボタンにインスタンスを設ければよいわけですが、結構、これが複雑です。以下のようになります。(Excel2003でも可能です)

コマンドボタンにインスタンスを適用したので、他のブックにも影響します。出来れば、ブック単独でお使いになったほうがよいと思います。

ブックを開いた時に設定され、ブックを閉じた時に、解除されます。
単独で、起動する場合は、Auto_Open 、単独で解除する場合は、Auto_Close を実行すれば、行えます。

'----------------------------------------------------------
'以下Class1 モジュール

Private WithEvents myBtn As CommandBarButton

Public Property Set Btn(ByVal myNewBtn As CommandBarButton)
  Set myBtn = myNewBtn
End Property

Private Sub myBtn_Click _
  (ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Dim c As Range
  If Selection.Columns.Count <> Columns.Count Then Exit Sub
  CancelDefault = True
  Application.ScreenUpdating = False
  Selection.Copy
  Selection.Insert Shift:=xlShiftDown
  Selection.PasteSpecial xlFormulas
  For Each c In Selection
   If c.HasFormula = False Then
    c.ClearContents
   End If
  Next c
  Application.CutCopyMode = False
  Selection.Cells(1).Select
  Application.ScreenUpdating = True
End Sub

'===================================================
'以下標準モジュール

Private myClass1() As Class1
Sub RowInsertButtonEvent()
 Dim myCtrl As CommandBarButton
 Dim i As Long
 On Error GoTo ErrHandler
   For Each myCtrl In Application.CommandBars("Row").Controls
     If myCtrl.Caption Like "挿入(&I)" Then
     ReDim Preserve myClass1(i)
       Set myClass1(i) = New Class1
       Set myClass1(i).Btn = myCtrl
     i = i + 1
     End If
   Next
 For Each myCtrl In Application.CommandBars("Worksheet Menu Bar"). _
   Controls(4).Controls
   If myCtrl.Caption Like "行*" Then
    ReDim Preserve myClass1(i)
    Set myClass1(i) = New Class1
    Set myClass1(i).Btn = myCtrl
    i = i + 1
    Exit For
   End If
 Next
ErrHandler:
 If Err.Number > 0 Then
  MsgBox "設定に失敗しました", vbCritical
 End If
End Sub

'----------------------------------------------------------
Sub Auto_Open
'起動時に設定
 Call RowInsertButtonEvent
End Sub
'----------------------------------------------------------
Sub Auto_Close
'終了時に設定解除
 Erase myClass1()
End Sub
'----------------------------------------------------------

こんにちは。

Excel2003 ですと、その場所をリストに変更しておけば、自動的に数式は入ります。

Excelの2000の場合ですが、簡単に言えば、行の挿入のそれぞれのボタンにインスタンスを設ければよいわけですが、結構、これが複雑です。以下のようになります。(Excel2003でも可能です)

コマンドボタンにインスタンスを適用したので、他のブックにも影響します。出来れば、ブック単独でお使いになったほうがよいと思います。

ブックを開いた時に設定され、ブックを閉じた時に、解除されます。
単独で、...続きを読む

Qエクセルで行を追加した時に計算式が変更されない

パソを入れ替えてオフィスの2010になってから操作方法にちょっと戸惑っています。
エクセル2010で、行を追加した時等に計算式が自動で変更されません。

例えば、sum関数で、A1~A10までの合計を計算してあったものに、行を挿入しても計算対象が自動で変わらずそのままです。
sum(a1:a10)に2行追加するとsum(a1:a12)になってくれないと間違いを起こしてしまいます。
どこか設定を変える必要があるのでしょうがどこかわかりません。教えてください。
(再計算は自動にしてあるんですが…)

Aベストアンサー

(1)2010でないのですが、例えばB10に=SUM(B1:B9)と入れておきます。
(2)B10を選択して、行を挿入してB11の式を見てください。=SUM(B1:B9)でB10は含まれません。
(3)同じく(1)の状態でB1を選択して、行挿入しても=SUM(B2:B10)となり、B1セルは含まれません。
おなじことが2010に起こっているのでは。
関連在りそうな質問・解決法
Googleで「エクセル 行挿入 SUM関数」で照会
http://efcit.co.jp/cgi-bin1/exqalounge.cgi?print+200811/08110022.txt
http://oshiete.goo.ne.jp/qa/6540450.html ほか。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qエクセルで行挿入しても計算式がずれない方法

以前にも同じような質問があったかもしれませんが、探せなかったので質問させていただきます。

エクセルで作った表のデータを数人に配り、
それを利用して各自で数字を入力し、出力した状態でこちらに提出してもらっています。
その際にいくつか困る点があり、もし、データを渡す段階で予防できるような方法があれば教えていただきたいと思います。

私自身簡単な関数くらいしかわからないので、エクセルでできることの範囲もよくわかっておりません。
見当違いの質問をしていたらすいません。

1.各自が入力の段階で行を削除・挿入することが多々あり、 それによって計算式がずれてしまうのを防ぎたい。
 計算式はその列の一番上段にあり、数ページに渡っていたとしても1ページ目にしかその欄はない。
 削除・挿入を禁止することはできない。

2.ページ毎に小計を表示させたい。
 ページ数は人によってそれぞれなので、数字の入っていない 無駄なページは作りたくない。
 セルにおさまらなくても、ヘッダーやフッターのような機能でもよい。
 

以上ですが、ご理解いただけますでしょうか?
説明が足りなければ補足させていただきます。

どなたかよろしくお願いします。

以前にも同じような質問があったかもしれませんが、探せなかったので質問させていただきます。

エクセルで作った表のデータを数人に配り、
それを利用して各自で数字を入力し、出力した状態でこちらに提出してもらっています。
その際にいくつか困る点があり、もし、データを渡す段階で予防できるような方法があれば教えていただきたいと思います。

私自身簡単な関数くらいしかわからないので、エクセルでできることの範囲もよくわかっておりません。
見当違いの質問をしていたらすいません。

1.各自...続きを読む

Aベストアンサー

#1です。

質問の内容はこんな感じでしょうか?
例えば、A1セルにA列データ(2行目以降)の平均値を表示させたい。
しかも、印刷されるページ内のデータの平均。
2ページ目、3ページ目にも上部に印刷ページ内の平均値を表示したい。

もし、こんな内容だと仮定すると、私ならSheet1をデータ入力用にして、Sheet2、Sheet3・・・を出力用として設定します。
Sheet2のA2セルに"=Sheet1!A2"と入力してデータ用シートを参照します。A3セル以降はコピーアンドペースト。
Sheet2のA1セルにSheet2のA列の平均をとる計算式を書きます。

Sheet3のA2セルには"=Sheet1!A50"のように次のデータから参照します。
こうしておけば、Sheet1データが挿入・削除されても影響を受けません。

しかし、出力用のシートは有限になりますし、印刷も面倒ですね。

答えておいて何ですが、もっといい回答が出ると思います。
そもそも、質問を曲解してるかも・・・

補足説明をすれば回答がでると思います。
私も興味があるので、ぜひお願いします。

#1です。

質問の内容はこんな感じでしょうか?
例えば、A1セルにA列データ(2行目以降)の平均値を表示させたい。
しかも、印刷されるページ内のデータの平均。
2ページ目、3ページ目にも上部に印刷ページ内の平均値を表示したい。

もし、こんな内容だと仮定すると、私ならSheet1をデータ入力用にして、Sheet2、Sheet3・・・を出力用として設定します。
Sheet2のA2セルに"=Sheet1!A2"と入力してデータ用シートを参照します。A3セル以降はコピーアンドペースト。
Sheet2のA1セルにSheet2のA列の平...続きを読む

Qエクセルで行を挿入したときに勝手に書式をひきつがな

半日検索しましたが、回答が見つからなかったので質問させてください。

エクセルで行(や列)を挿入すると、上の行(左の列)の書式(色)を勝手に引き継いでしまいます。これを解決するには、

1:空白行をコピーしてから挿入せよ。
2:「挿入オプション」で「書式のクリア」を選べ。

と別の方の質問への回答がありましたが、デフォルトで「上と同じ書式を適用」になっているのが非常に不便です(列の場合は「挿入オプション」さえ表示されません)。一発で空白行(列)が挿入できるようにする方法は無いのでしょうか? ctrl_何々 とか F何々 とかワンタッチで行ければベストです。

バージョンは2台のPCに、それぞれ 2003と2008が入っています。
どうか宜しくお願い致します。

Aベストアンサー

余計なことをするのが最近のExcelの真骨頂ですから設定一発では思い通りにならないことが多いです。

なので、簡単なキーマクロを登録したらどうでしょう。
ツールのマクロの記録を使ってから下記のとおり修正をすれば簡単にできます。
起動時毎回使えるようにするには、2003の場合、オプションの全般で起動時に
すべてのファイルを開くフォルダを指定し、下記マクロを登録したBook1.xls を
保存する必要がありますが。

---
標準モジュール Module1に下記マクロを登録

Sub Macro1()
'
' 現在カーソルのある行に空白行を挿入する:行挿入してから、その行を全クリア
' Keyboard Shortcut: Ctrl+o  ・・・ 例えばCtrl+oにこの機能を割り当てる
'
Selection.EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.Clear
End Sub

Sub Macro2()
'
' 現在カーソルのある列に空白列を挿入する:列挿入してから、その列を全クリア
' Keyboard Shortcut: Ctrl+l  ・・・ 例えばCtrl+lにこの機能を割り当てる
'
Selection.EntireColumn.Select
Selection.Insert Shift:=xlToRight
Selection.Clear
End Sub

余計なことをするのが最近のExcelの真骨頂ですから設定一発では思い通りにならないことが多いです。

なので、簡単なキーマクロを登録したらどうでしょう。
ツールのマクロの記録を使ってから下記のとおり修正をすれば簡単にできます。
起動時毎回使えるようにするには、2003の場合、オプションの全般で起動時に
すべてのファイルを開くフォルダを指定し、下記マクロを登録したBook1.xls を
保存する必要がありますが。

---
標準モジュール Module1に下記マクロを登録

Sub Macro1()
'
' 現在カーソルのある...続きを読む

Q(Excel)あるセルに文字を入力しただけで、同じブック内のほかのワークシートにも、同じ文字が自動的に入るようにするには?

こんにちは。
質問内容はタイトルのとおりです。

あるワークシートのあるセルに文字を入力すると、ほかのワークシートのセルにも同じ文字が自動的に書き込まれる方法を知りたいです(ブックは同じ)。複数のワークシートを制御するには、やはりマクロを使うのでしょうか?

よろしくお願いします。

Aベストアンサー

clam_chowderさん、こんにちは。

Sheet1のA1にたとえば「100」と入力しますね。
Sheet2のA1に、これと同じ数値を表示するには、
 =Sheet1!A1
でOKです。

数式をいれるのが苦手なら、
Sheet2のA1セルで「=」を入力すると、数式入力状態に入りますから、
ここでSheet1のシートタブをクリックし、
リンクしたいA1セルをクリックしてEnterすると、
自動的にさきほどと同じ式が入ります。

QVBAで行コピーして挿入

1行目の内容をコピーして、他の場所に指定数分だけ
挿入するマクロを作りました。

そのマクロ自体は、正しく動いたのですが、コピー元の1行目に
他のシートを参照する関数が入っていた場合、想定どおりの
結果を得ることができません。

[SHEET1:データのみを記載]
省略

[SHEET2]
A1セル:   =SHEET1!$A1 ←コピー元の行

[マクロ:一部抜粋]
myR = Application.InputBox("挿入する行数を入れてください", , "1")

For i = 1 To myR
  Rows("1:1").Copy
  Cells(ActiveCell.Row, 1).Select
  Selection.Insert Shift:=xlDown
  Selection.EntireRow.Hidden = False
Next i

どういう結果を求めたいかというと、たとえば、
SHEET2のA10セル上で、このマクロを実行し、 "挿入行 = 3" と指定したら

A10:   =SHEET1!$A10
A11:   =SHEET1!$A11
A12:   =SHEET1!$A12

となってほしかったのですが、結果は、

A10:   =SHEET1!$A10
A11:   =SHEET1!$A10
A12:   =SHEET1!$A10

となってしまいました。

どうにか、求める結果を得られるようにできないでしょうか?

1行目の内容をコピーして、他の場所に指定数分だけ
挿入するマクロを作りました。

そのマクロ自体は、正しく動いたのですが、コピー元の1行目に
他のシートを参照する関数が入っていた場合、想定どおりの
結果を得ることができません。

[SHEET1:データのみを記載]
省略

[SHEET2]
A1セル:   =SHEET1!$A1 ←コピー元の行

[マクロ:一部抜粋]
myR = Application.InputBox("挿入する行数を入れてください", , "1")

For i = 1 To myR
  Rows("1:1").Copy
  Cells(ActiveCell.Row, 1).S...続きを読む

Aベストアンサー

Active.Cellが同一の位置なのだから相対変位しません。

一例です。(ループは不要なので削除しました)
myR = Application.InputBox("挿入する行数を入れてください", , "1")
Rows("1:1").Copy
Rows(ActiveCell.Row & ":" & ActiveCell.Row + myR - 1).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング