【Excel】通帳の収支を残高に反映させる方法

質問

Excel2007利用です。

通帳をExcel表にまるごと写しています。
「差し引き残高」の項目に、「お支払い金額」「お預り金額」の収支が反映するようにしたいのです。

たとえば「ご新規」と対応する「お預かり金額」に10.000と入力します。
次の「お支払い金額」5.000と入力すればその残高が5.000になるのが通帳ですが、収支の数値を入力するだけで自動的に残高が表記されるようにするにはどのような計算式、また構造にすればよいでしょうか？
当方、関数に関してはまったくの無知に近いので、初心者向けとして具体的に教えていただくと助かります。

回答No.3

bunjii · ベストアンサー

>収支の数値を入力するだけで自動的に残高が表記されるようにするにはどのような計算式、また構造にすればよいでしょうか？
貼付画像のような表で如何でしょうか？
E2へ次の数式を設定し、下へ必要数コピーすれば目的通りになるでしょう。
=IF(COUNT(C2:D2),SUM(C$2:C2)-SUM(D$2:D2),"")

この数式はC2とD2の何れかに数値が入力されているときC$2～C2の合計からD$2～D2の合計を差し引いて残高とします。
C2とD2セルが空欄（未入力）または文字列のとき文字数0の文字列を代入します。
下へコピーしたときは次のように変化します。
E3=IF(COUNT(C3:D3),SUM(C$2:C3)-SUM(D$2:D3),"")
E4=IF(COUNT(C4:D4),SUM(C$2:C4)-SUM(D$2:D4),"")
セルの番地のC2とD2は下へコピーすると行番号が変化します。
しかし、C$2とD$2は下へコピーしても行番号は変化しません。
従って、C列とD列の先頭行（2行目）から計算対象の行までの合計を常に自動計算するように設定できます。
また、IF関数の論理式（COUNT(C2:D2)）はC2とD2の一方または両方が数値のとき1または2（0でないとき正）となり、両方が未入力または文字列のとき0（誤）となりますので、両方が未入力のときはE列には何も表示されません。

補足

ご回答ありがとうございます。
関数はこれから学びたいと思っていましたので、具体的な説明があって助かります。

＞C$2～C2の合計からD$2～D2の合計を差し引いて残高とします。

この「$」「：」はどのような意味なのでしょうか？
COUNTについては、数値が入力されたセルの個数を求める、SUNについては入力した値の合計を求める）とのことですが、
残高＝数値が入力されたセルの個数（C3からD3）を合計（COUNT）し、
入力した値の合計を求める（SUM）のは(C$2:C3)から(D$2:D3)を引いたもの！？！？
というイメージです（たぶん違っていると思います）が、「$」「：」がわからず苦慮しています。
この式はどう「読めば」よいのかも教えていただけると勉強になります。

また、ご回答の例で実際に作表してみましたが、添付された画面通り正常に実行されました。
しかしセルの左上の角に緑の三角マークがつきました。詳細をみると
「数式は隣接したセルを使用していません」
とあります。三角マークは消せないのでしょうか？もし消せるのでしたら教えてください。
いずれにしてもこういうエラーがでても、そのままこの数式を使い続けても大丈夫ですか？

回答No.7

ganta-desu

知識の豊富な皆さんの回答の後に
初心者に近い者が回答することお許しください

添付の表
セル　E4 に =C4
セル E5 に =E4＋C5-E5　　
のように打ち込むか、セルをクリックして指定する
そのあと、E5セルの式を必要な分（E20でもE50まででも）
下方へドラッグすれば表計算は出来上がり

月別集計　もお作りになったらいかがでしょうか？
セル　H4に　=SUMIF(B4:B12,G4,C4:C12)
　　　K4に　=SUMIF(B4:B12,J4,D4:D12)
・・・と入力
H4、K4ともに下方へドラック（数式のコピー）
ただし、その前に指定範囲のB4からB12、D4からD12は
変わっていかないように
ファンクションキーの4　　[F4]をつかいます
数式のところの　B4をドラッグすると青く表示されます
そのまま　[F4]キー使用　
B4　が　$B$4 の用に表示されます
G4,J4以外の範囲指定の部分も同様に＄を付ける

わかりやすくいえば
H4の式　　
もしB列（科目）４～12の中にG4（例　残高）と
同じもの（科目）が有ればC列４～12の中の金額を合計する

また、B4:B12はそのように打ち込むか、セルをドラッグで指定しても入ります
また、0が表示されるのが目障りなら
IF関数で空白なら空白の方法もありますが
「条件付き書式」で「指定の値に等しい」→「ユーザー設定の書式」
から　「値は０」文字の色を「自動」から白に指定すれば目立たなくなります

補足

ご回答ありがとうございます。

私なんて超初心者ですので、まずシンプルな式から説明されたほうがわかりやすいです。

ただ、
セル　E4 に =C4
セル E5 に =E4＋C5-E5　
の場合は、以下のようなエラーメッセージがでるんです。
----------------------------------------------------
循環参照に関する警告

循環参照が含まれているため、数式を正しく計算できない可能性があります。
循環参照とは、数式内で、その数式の結果に依存する参照を行うことです。
たとえば、セルが、それ自体の値を参照する場合や、そのセルの値に依存する他のセルを参照する場合、これらのセルには循環参照が含まれます。

循環参照の詳細、検索方法、および削除方法を確認するには［OK]をクリックしてください。循環参照を作成する場合は、［キャンセル］をクリックして続行してください。
----------------------------------------------------

循環参照とはこの式の場合どれになるのでしょう？

回答No.6

bunjii

>この「$」「：」はどのような意味なのでしょうか？
Excelではセルの位置を列記号（英字）と行番号（数字）の組み合わせで表記することはご存知と思います。
C1セルに =A1+B1 と入力するとC1セルにはA1の値とB1の値を加算した結果が代入されます。
C1セルの数式を下（C2セル）へコピーするとC2の数式は A2+B2 となります。
これはC1セルの数式のA1と表記されたセル位置はC1セルから見て相対的に左に2つ移動したセル位置と認識します。
前述と同様にB1はC1セルから見て左に１つ移動したセル位置と認識します。
これらのことからC1セルをC2へコピーするとC2セルから見て左に2つ移動したセル位置（A2）と左に1つ移動したセル位置（B2）を加算する数式と判断してC2セルに =A2+B2 と言う数式を設定します。
それでは =A$1+b1 の場合はどのようになるかを考えると行番号前に$が付加されると行番号が絶対値として数式を他のセルへコピーしても変化しないことに定められています。
従って、C1セルに =A$1+B1 と入力されているときにC2セルへコピーするとA$1は変化せずにB1がB2に変化して =A$1+B2 となります。

>三角マークは消せないのでしょうか？
エラーチェックルールの変更でエラー警告を除外できます。（貼付画像参照）

お礼

補足ありがとうございます。納得いたしました。

ご回答者様のご丁寧な解説により、おかげさまで関数がより身近になりました。
面白さがわかりました。
今後も勉強していきます！

回答No.5

tamao-chi

いろいろな回答がありますので参考に。

>どのような計算式
単純に前回の残高から収支を足し引きすれば良いです。
ただし、1行目だけイレギュラーの計算式になります。

No3さんの画像を借ります。
E2 =C2
E3 =E2+C3-D3
あとはE3をコピーしてそれ以降にペーストするだけです。
ただし、これだとE列は最後の収支入力の残高がペーストした所まで、ず～っと表示されます。
それを無くすには関数を使います。
E3 =IF(B3="","",E2+C3-D3)
以降コピーです。

もし(IF)(B3=空白("")ならば,空白(""),そうでなければE2+C3-D3)
「摘要」の列を入力すると計算結果が表示されます。
逆に、収支を入力しても摘要が空白だと計算しません。

または、
E3 =IF(AND(C3="",D3=""),"",E2+C3-D3)
もし((C3=空白及び,D3=空白）ならば,空白,そうでなければE2+C3-D3)
「お預かり」又は「お支払い」の列を入力すると計算結果が表示されます。
これはNo3さんのCOUNT関数の部分と類似しています。
COUNTでは、もし誤って文字が入力されても空白で返しますが、=""を使うと計算できないのでエラーを返します。

No3さんの回答のほうがスマートですね。

お節介かもしれませんが、No3さんへの補足の回答です。

＞この「$」「：」はどのような意味なのでしょうか？

「$」については
「C$2とD$2は下へコピーしても行番号は変化しません。」と解説しています。
$を付けると絶対参照となりコピー貼り付けしても変化しません。
ここでは行に$を付けているので、例えばC$2を下の行へコピー貼り付けしてもC$2のままです。
列方向へコピー貼り付けするとD$2,E$2と変化します。
列、行ともに絶対参照とする場合は「$C$2」と入力します。コピー貼り付けしても常にC2を参照します。

「:」については
「C$2～C2の合計からD$2～D2の合計」と表記されています。
：＝～です。範囲を意味します。

＞残高＝数値が入力されたセルの個数（C3からD3）を合計（COUNT）し、
入力した値の合計を求める（SUM）のは(C$2:C3)から(D$2:D3)を引いたもの！？！？

E3 =IF(COUNT(C3:D3),SUM(C$2:C3)-SUM(D$2:D3),"")
もし、（C3からD3に数値入力がある場合,C2～C3の合計 - D2～D3の合計,数値入力が無ければ空白）

ここでのCOUNTは数値が入力されているか判断しているだけです。

お礼

ご回答ありがとうございます。

>E列は最後の収支入力の残高がペーストした所まで、ず～っと表示

確かに、E列への数式だけコピペしたところすべて残高は0となっていますし、試用の数値を入れてみても最後の収支の残高がそれ以降も反映されてしまってます。

そしてこれを解消するのが関数ということで、すごいなと。

＞もし(IF)(B3=空白("")ならば,空白(""),そうでなければE2+C3-D3)

IFはやはり「もし」と読むのですね。
そして関数はプログラミングなのですね。
いろいろな指令どおりに動いて、その方法もいろいろある。
このような計算式もひとつだけではない。
ということで、関数ははまるとなかなか抜け出せないかもしれません！

＞「摘要」の列を入力すると計算結果が表示されます。

この部分がわからなかったのですが、「お預かり」と「お支払い」に数値を入力しても自動的に「残高」に結果はでませんが、「摘要」に何らかの数値を入れると残高に数式の答えがでるということでよいと思いますが、

＞収支を入力しても摘要が空白だと計算しません。

確かにこのことを証明してくれました。

ある部分を絶対値として他の場所でも反映させるなどが関数のだいご味でしょうか。

分かりやすいご説明でぐんと関数が身近になりました。
本当にありがとうございます。

回答No.4

naoto0216

No1です。
ですよね。。通帳ってそういうものでした。
失礼しました。

1.シート名のところを右クリック⇒コードの表示を選択。
2.画面左上にある（General）をWorksheeｔに変更。
3.Private Sub Worksheet ・・・

　End Sub
　と表示されるので、その間に以下をコピペ。

rw = ActiveCell.Row: clm = ActiveCell.Column
If rw >= 2 And clm <= 3 Then
Cells(rw, 3).FormulaR1C1 = "=SUM(R1C[-2]:RC[-2])-SUM(R1C[-1]:RC[-1])"
End If

4.×で閉じる。

これで、通帳通りになるかと思いますので、一度お試しください。

お礼

ご回答ありがとうございます。
関数というよりもプログラミングでしょうか…
今後の参考にさせていただきます。

回答No.2

caf-caf

Ａ　　Ｂ　　Ｃ　
１　入金　出金　残高
２　　　　　　　１００（繰越残高）
３から入金、または出金を入力するのであれば、

Ｃ３の残高のセルに、=IF(COUNT(A3:B3),$C$2+SUM(A$3:A3)-SUM(B$3:B3),"")

というのはお試しになりましたか。

お礼

ご回答ありがとうございます。
今後の参考にさせていただきます。

回答No.1

naoto0216

「差し引き残高」のセルに、「お預かり金額」-「お支払い金額」でいいかと。

例えば、
　　A列　　　　　　　　　B列　　　　　　　　　C列
1　お預かり金額　　　お支払い金額　　　差し引き残高
2　10000　　　　　　　5000　　　　　　　　=A2-B2
3　　5000　　　　　　　　500　　　　　　　　＝A3-B3

って感じでしょうか。
A列とB列に金額を入力すると、C列には自動的に算出された金額が
表示されるようになります。
C2に式を入れたら、そのセルをグィーっと下にひっぱってコピーすれば
よいかと思います。

補足

ご回答ありがとうございます。

違うんです。
このような形なんです。

A列　　　　　　　　　B列　　　　　　　　　C列
1　お預かり金額　　　お支払い金額　　　差し引き残高
2　10000　　　　　　　　　　　　　　　=A2-B2
3　 　　　　　　　5000　　　　　＝A3-B3

通帳では実際、お預かりかお支払いどちらかですよね？
このように、A2やB2、A3やB3すべてのセルに数値を入れるのではなく、あくまでも「通帳に記帳されるとおり」に計算させるにはどうしたらよいかということです。

またこの方法ですと、C2の残高が5.000円でも、次に「お預かり」に5.000円入れたら残高は10.000円になるはずですが、これは単純に行の中だけでの計算式じゃないですか。