エクセルで数字の合計の羅列を0にしたい・・

質問

エクセル2003を使っていて、おこづかい帳を作っています。

A1=元の金額3万円
B1=その日出て行った金額1000円

D1=A1-B1

A2=記入なし
B2=その日出て行った金額550円

D2=D1-B2

A3=記入なし
B3=その日出て行った金額850円

D3=D2-B3

としていくと

A30=記入なし
B30=その日出て行った金額2000円

D30=D29-B30

となっていきます。

オートフィルでD1からD31まで一気に数式を当てはめました。

こうすると、1日目にD1は2万9千円です。
しかし、この2万9千円という数字が、D1だけでなく、D2～D31まですべてに出てしまいます。
B2,B3に数字を書いていけば、数値は低くなっていくんですが、合計金額のDの値の羅列は数値が引かれた状態で、出たままです。

これを、D1では2万9千円とでているけれど、この状態のときではD2～D31までは0と表示し、B2,B3に書き込んでいけば、そのたびにその行のDの値のみが変化するように変えるには、どういった数式を書けばいいのでしょうか？
また、どうしてその式を使用するのかを教えてほしいです。

分かりづらい説明で申し訳ないのですが、教えていただければ幸いです。
お願いします。

回答No.6

shorun · ベストアンサー

>私が出した数式ではいけないんでしょうか？
B4=空欄なのに　D4=D3-B4　としているので　D4=(D3-B4)=D3 になってしまいます。

D1の式は　=IF(OR(B1="",B1*0<>0),0,SUM(A$1:A1)-SUM(B$1:B1))
この式をオートフィルでD1からD31までコピーすればOKです。

式の説明
(B1=空欄　または　B1×0≠0) YESなら,　D1=0 ,
NOなら　D1= A列1行目までの計-B列1行目までの計

オートフィルでD1からD31までコピーしてあれば
D31　は A列31行目までの計-B列31行目までの計になります。

なお、No5さん提案のようにB列空欄ならD列も空欄にするなら
D1の式は　=IF(OR(B1="",B1*0<>0),"",SUM(A$1:A1)-SUM(B$1:B1))
になります。

回答No.7

MarcoRossiItaly

No.4です。すみません、C列とD列を間違えました。No.4の数式は次のとおり修正でお願いします。

=(a2+b2>0)*d1+a2-b2

回答No.5

KURUMITO

回答No1及び2です。
初めにあなたがD1セルに入力した式は=A1-B1ですね。この式を下方にドラッグコピーすればD2セルでは=A2-B2の式に変わっていますのでA2セルもB2セルも空ならば0と表示されてしまいますね。
D2セルに入力した=D1-B2の式の場合にはB2セルが空ならば29000ですね。この式を下方にドッグコピーした場合にはD3セルでは=D2-B3の式に変わりますので、B3セルが空の場合にはD2セルの値29000が表示されますね。したがってB2セルから下のセルにデータが入力されていなければD列には29000が並ぶことになります。
ところで回答No1で示した式ですがD1セルには=SUM(A$1:A1)-SUM(B$1:B1)を入力して下方にドラッグコピーします。この式では例えばD2セルでは=SUM(A$1:A2)-SUM(B$1:B2)となり、D3セルでは=SUM(A$1:A3)-SUM(B$1:B3)となります。A1やB1セルは絶対参照の形なっていますのでセル番地が変わりません。A列は収入の金額が入力され、B列には支出の金額が入力されるとしたら、例えばD3の式ではA1セルからA3セルまでの金額の合計（SUM)からB1セルからB3セルまでの金額の合計を引いたものが残額としてD列に表示されることになります。この式の特徴は例えばD1セルからD30までの行で仮に空白の行が有ったとしても何の問題もなく計算ができることです。例えばD15のセルが完全に空白の状態となったとしてもD30には残額が表示されますね。一方で=D1-B2のような式の場合にはD15のセルが空になったような場合にはが16行目からのデータは間違ったデータとなってしまいます。また、仮にB15のセルに数値ではなく例えば文字列で”休み”などの文字や空白が入ってしまった場合にもD15以下にはエラーの表示がされてしまいます。SUM関数を使っている場合にはそのようなことが無いので一般に使われる方法です。29000がD30セルまで表示されますがD列は残額ですから当然のことと思います。
しかし、ご質問のようにB列にデータが無い場合にはD列の値を0にしたいとのことには答えられませんね。
そのため回答2を示したのですが一部で誤りがありました。ごめんなさい。
次の式に直してください。この式をD1セルに入力して下方にドラッグコピーします。

=IF(COUNT(A1:B1)=0,"",SUM(A$1:A1)-SUM(B$1:B1))

ここでCOUNT関数はA列とB列に数値のデータが無ければ0となりますので、その場合にはD列のセルを空白としなさいとの意味になります。ご質問ではB列にデータが無ければ0に表示したいとのことですが、D列は残額を示すものですから0にするよりも空白のセルにすることの方がよいように思いますね。
あなたが最初にD2セルに入力した式では=D1-B2の式で必ずD列のセルの一つ上のセルのデータが必要となりますので、使うことができませんがSUM関数を使用することで必ず一つ上のデータが必要となることはありません。
なおCOUNT関数はA1とB1セルの範囲に数値の入ったセルが幾つあるかを数える関数になっています。A列やB列のみに数値が有る場合には1でA列とB列に数値が有る場合には2となります。

回答No.4

MarcoRossiItaly

D1 セルに質問者さんが考えた数式を入力したままにするなら、D2 以下は次式でもオッケー。

=(a2+b2>0)*c1+a2-b2

＞どうしてその式を使用するのかを教えてほしいです。

「a2+b2>0」という論理式は、「TRUE」（真）か「FALSE」（偽）という論理値を返します。これに数値（C1 セルの値など）をかけると、TRUE は「1」に、FALSE は「0」になります。つまり a2+b2>0 が成り立つときだけ C1 セルの値の 1 倍が加算されることになります。A・B列は正数であることが前提だから、a2+b2>0 が成り立つときというのは、A・B列のいずれかに「0」以外の数値が入力されているケースということになります。未入力は「0」として扱われます。

回答No.3

keithin

Ｄ１に
=IF(A1+B1,SUM($A$1:A1)-SUM($B$1:B1),0)
と記入して，下向けにつるつるっとコピーして入れておきます。

現在の残高は，これまでの収入の総額（繰越繰入金を含む）から支出の総額を引き算した額なので，こういう計算になります。
Ａ列Ｂ列に数字が記入されていないときはゼロにするというご要望なので，こういう具合にＩＦ関数の計算をします。
数式で使っている関数の一個一個に分解して，何が「計算できてる」のか理解してください。

回答No.2

KURUMITO

D2セルにはA列やB列に記載がなければ空白の表示にするには次のよぷな式にすればよいでしょう。

=IF(COUNT(A1:A2)=0,"",SUM(A$1:A1)-SUM(B$1:B1))

この式を下方にドラッグコピーします。

お礼

ありがとうございます。
しかし、D1に数値は出るのですが、下方にドラッグコピーしてもなにも出てきません。

また、その数式にしたのはなぜなんでしょうか？
私が出した数式ではいけないんでしょうか？
なぜ相対参照しているのでしょうか？
また、なぜcount関数を使っているのでしょうか？

回答No.1

KURUMITO

D1セルには次の式を入力して下方にドラッグコピーすることで問題は解決するように思います。

=SUM(A$1:A1)-SUM(B$1:B1)

お礼

ありがとうございます。
しかし、D4でD3と同じ数値が並んでしまいました。

また、その数式にしたのはなぜなんでしょうか？
私が出した数式ではいけないんでしょうか？
なぜ相対参照しているのでしょうか？