エクセル家計簿でデータが無い日空白にしたい

質問

Excel2013を使っています。家計簿を作るべく下記のようにしました。
A列：月日（収入や支出が無くても1日から月末日まで表示）
B列：収入や支出の項目文
C列：収入金額
D列：支出金額
E列：残高→1日（ついたち）以外は、「前日残高」+「当日収入」-「当日支出」＝「当日残高」　という計算式を全日フィルハンドルで入れます。
きょうが16日とすると、17日以降は16日の残高が表示され、見てくれが悪いので、2日の残高セルに
　　IF（B5=””、””、E4+C5-D5)
と入力し、それ以降の日にフィルハンドルコピーしました。
　これで、毎日データがある場合は問題ないのですが、データが無い日があると次の日の残高がエラーになります（””をたしざんに使うようになるので当然ですが）。これを何とか回避する方法はないものでしょうか。A列でデータが発生する日だけ記入とすると問題ないのですが、年間集計表に持ってゆくセルの位置が毎月変わるのでやりたくはありません。
　よろしくお願いします。

回答No.2

keithin · ベストアンサー

まず、先月からの繰越金をどこに記載していますか。１日の収入として繰り越しているなら、まぁそれはそれで構いませんが。

＞データが無い日があると次の日の残高がエラーになります

こういうときは次にようにするのが定石です。
ところで「具体的な数式」を教わるには「あなたが作った家計簿の具体的なレイアウト」の情報をちゃんと提供してもらわないと、「勝手に回答するからご自分でちゃんと修正して使ってね」という事になっちゃいます。

４行目に記録の１行目が来るのでしたら
Ｅ４：
=IF(B4="","",SUM($C$4:C4)-SUM($D$4:D4))
以下コピー
のように、今日の残高は「期首の繰入額＋今日までの収入の合計－今日までの支出の合計」で計算します。

もしも例えばＥ３セルに先月からの繰越金が記入してあるなら、上述に更に$E$3を足しこんでおくということです。

で、この考え方をキチンと使いこなすと、
○一日に複数回の支出や収入がある場合
○年間を通じて集計したい場合
などについても「セルを固定」とか全然必要なくて、全く同じ考え方で応用する事ができるようになります。頑張ってマスターしてみてください。

お礼

数人の方から意見を頂き、ありがたく思っています。多分できないだろうと思っていましたが、浅はかでした。keithinさんの以下の式が簡単で一番分かり易かったので、これを採用しようと思います。
=IF(B4="","",SUM($C$4:C4)-SUM($D$4:D4))
本当にありがとうございました。

補足

回答本当にありがとうございます。もしかしたら「それは不可能です」と言われるかな、と思ったら、keithinさんのような考え方をすればできますね。数式が分かり易いです。

回答No.6

chayamati

こんにちは

＞E列：残高→1日（ついたち）以外は、
＞「前日残高」+「当日収入」-「当日支出」＝「当日残高」

１行目を見出し行として
２行目を繰越欄とし、繰越金額を当日収入欄に入力します。
当日残高は当日までの収入の累計から支出の累計を減算して求めます。
こうすると途中で行を挿入しても残高計算は正常です。

２行目の当日残高セルE2は
=SUM($C$2:C2)-SUM($D$2:D2)
として
セルE2を縦方向にコピペします

補足

回答ありがとうございます。この方法では、収入や支出が無い日の残高セルに前日と同じ数字が入ってしまいます。空白にはなりませんが。

回答No.5

bunjii

>これは、フィルハンドルでコピーした後、Ctrl+Shift+Enter　を押すということでしょうか。
違います。
提示の式を数式バーにコピー＆ペーストで貼り付けてCtrl+Shift+Enterで確定します。
貼り付けた時に改行も含んでいるときは確定前に改行のみ削除してください。
その後、オートフィルで下へコピーします。

=IF(AND(C3="",D3=""),"",INDEX(E$1:E2,MAX(IF(E$1:E2<>"",ROW(E$1:E2))))+C3-D3)

>何も考えずにこのまま採用すればいいのでしょうね。
取り敢えず、やってみると良いでしょう。
実行してPCが壊れることはありません。
目的通りにならないときは数式と配列に矛盾があるだけです。

補足

ありがとうございます。早速やってみます。

回答No.4

keithin

違う計算方法のお話は聞きたくないんですね。

んじゃ、Ｅ４セルには
=IF(B4="","",IFERROR(LOOKUP(9999999,$E$3:E3),)+C4-D4)
と記入して、以下コピーしておきます。

回答No.3

bunjii

>これで、毎日データがある場合は問題ないのですが、データが無い日があると次の日の残高がエラーになります（””をたしざんに使うようになるので当然ですが）。これを何とか回避する方法はないものでしょうか。
論理が分かり難いと思いますが次の式を残高の列（E）へ設定すれば収入・支出が無い日を飛ばして計算できます。

E3=IF(AND(C3="",D3=""),"",INDEX(E$1:E2,MAX(IF(E$1:E2<>"",ROW(E$1:E2))))+C3-D3)
この式は配列の値を返す必要がありますので入力後Ctrl+Shift+Enterで確定します。

E2セルは繰越なので E2=C2 で良いことになります。

補足

回答本当にありがとうございます。不可能ではないかと思っていましたが、難しくはあるものの手はあるんですね。添付されているJPGとまったく同じ形式の家計簿です。しかし、年寄の家計簿にしては、ものすごい関数でビビっています。これは、フィルハンドルでコピーした後、Ctrl+Shift+Enter　を押すということでしょうか。何も考えずにこのまま採用すればいいのでしょうね。

回答No.1

0yo0

エクセルのエラーを非表示にするには

↓↓のような関数にするとできます。

=IF(ISERROR(IF（B5=""、""、E4+C5-D5))),"",IF（B5=""、""、E4+C5-D5))

複雑な関数なので、説明がうまくできませんが、これでできると思います。

なぜ空白にできるのかについて知りたければ
http://kokodane.com/kan42.htm
などを参考にするとよいかもしれません。

補足

回答本当にありがとうございます。エラーを表示しないようにしても、エラーのところで数字が出なくなりますので、計算が続かなくなります。
　ところで、紹介いただいたURLで、「条件付き書式」というのがありました。これを利用して何とかできないでしょうか。例えば、残高の計算式をIF文を使わずに　E4+C5-D5　にします。これを月末までフィルハンドルでコピーします。収入や支出が無いところは、前日と同じ数字になります。そこで、「項目文」のセルがNullだったら残高のところは、背景色と同じにする、ということはできないでしょうか。