エクセルで現金の収入・支出の帳簿を月ごとに
作成しようとしています。帳簿の書式は以下の通りです。
A列→日付
B列→摘要欄(収入・支出の具体的内容)
C列→収入金額
D列→支出金額
E列→残高金額
なお、E2セルには前月までの繰越金額を入力しています。
また、E3からのセルには「=E2(残高)+C3(収入)-D3(支出)」
の関数式を組んでいます。
ところが帳簿をつける係は私の母親で、各セルにテンキーを
入力するのが精いっぱいです。「コピペ」のことを覚えてもらう
のが大変困難です。そこで入力者が頼りないので、
予め表に関数式を組もうと考えています。
収入・支出があった時点で、E4以下のセルに「コピペ」で
「=E2+C3-D3」の関数式を組めばいいのですが、
それでは、E列全体にその関数式の答えである残高が
表記されてしまい、見栄えがよくありません。
前振りが長くなって申し訳ありません。
ここからが質問の具体的内容です。
上記のようなケースでC列(収入)やD列(支出)が、
未発生のときについては、同行のE列(残高)の
数値が未表示となるよう関数式を教えてください。
手書きのノートで同じ作業をすれば、最後に収入・支出が
発生した時の残高がノートの途中に残り、それより下の
残高欄は空白になると思いますが、イメージ的にはそんな感じです。
ご存知の方どうぞよろしくお願いします。
なおパソコンはWindows Vistaを使っています。
No.5
- 回答日時:
回答番号:ANo.3です。
>ただ残念ながら以下の式を使って
検算をしてみたところ帳尻が合いませんでした。
>=IF(COUNT($C3,$D3)=0,"",$E$2+SUM($C3:$C3)-SUM($D$3:$D3))
申し訳御座いません、回答欄に数式を入力した際に、入力ミスをしてしまい、Cを1つ余分に入力しておりました。。
[誤]
=IF(COUNT($C3,$D3)=0,"",$E$2+SUM($C$C3:$C3)-SUM($D$3:$D3))
[正]
=IF(COUNT($C3,$D3)=0,"",$E$2+SUM($C$3:$C3)-SUM($D$3:$D3))
補足欄に書かれた質問者様の式は、収入の合計を求める際に、
SUM($C3:$C3)
という様に、合計範囲が始まるセルを指定する部分を、行番号を相対参照にしておられる事が、帳尻が合わなくなる原因です。
ですから、範囲の始まりのセルの行番号を指定する部分も、絶対参照となる様に$を付けて
SUM($C$3:$C3)
として下さい。
>1.$E$2→E2にして絶対参照を外す。
その様にされては、もし、1つ上の行のE列のセルが空欄となった場合には、それ以下の行における残高の計算が狂います。(試しに、収入と支出の両方共、空欄にした行を、途中に設けてみて下さい)
$E$2の部分は、絶対参照のままの方が宜しいです。
>2.SUM($C3:$C3)-SUM($D$3:$D3)をSUM($C3:C3)-SUM($D$3:D3)とする。
その数式をE4以下にコピーする場合には、2の変更を行っても、計算結果には全く影響しませんから、2の変更は無意味です。
アルファベットの前の$は、列番号を絶対参照とするもので、列が変わっても指定する列番号が変わらない様にしています。
E3セルをコピーして、E4以下に貼り付けても、列は同じE列のままですから、列番号を絶対参照にした場合と、列番号を相対参照にした場合とでは、結果が変わる訳ではありません。
一方、数字の前の$は、行番号を絶対参照とするもので、行が変わっても指定する行番号が変わらない様にしています。
そのため、
SUM($C$3:$C3)
という数式と
SUM($C3:$C3)
という数式を、E3セルに入力してから、E4以下に貼り付けた場合を比較すると、合計するセル範囲は次の様に変化します。
E3 $C$3:$C3 $C3:$C3 $C3:C3
E4 $C$3:$C4 $C4:$C4 $C4:C4
E5 $C$3:$C5 $C5:$C5 $C5:C5
E6 $C$3:$C6 $C6:$C6 $C6:C6
この様に、E4以下に貼り付けた場合には、$C$3:$C3以外の範囲設定以外では、C3から、その行までの合計にはなっていません。
合計範囲が正しくないため、
SUM($C3:$C3)
では、正しい結果が得られず、
SUM($C$3:$C3)
にする必要がある訳です。
たびたびのご回答ありがとうございます。
=IF(COUNT($C3,$D3)=0,"",$E$2+SUM($C$3:$C3)-SUM($D$3:$D3))
の式を入力してコピペすると帳尻が合いました。
No.4
- 回答日時:
エラーチェックと言いますがエラーではなく,エクセルのお節介機能の一つです。
添付図のような警告だった場合,リストに並べた数式が自分の行や列とずれたセル範囲を参照しているので,計算範囲がズレてませんか?と確認を出してきています。
黄色い!からエラーチェックオプションのダイアログを出し,添付図のように余計な事は考えなくて良いよと設定を変えてしまいます。
No.3
- 回答日時:
その様な場合には、
=IF(COUNT(C3,D3)=0,"",E2+C3-D3)
とします。
COUNT関数は、括弧内に指定されているセル、或いはセル範囲に、数値が入力されているセルが幾つあるのかをカウントする関数です。
IF関数は、
IF([論理式],[真の場合],[偽の場合])
という形式で表され、
[論理式]の部分が成り立っている場合には、[真の場合]の部分で指定される値を表示し、
成り立っていない場合には、[偽の場合]の部分で指定される値を表示する関数です。
ですから、
=IF(COUNT(C3,D3)=0,"",E2+C3-D3)
という関数は、C3セルとD3セルの2個のセルの内、数値が入力されているセルが0個の場合、即ち、数値が入力されているセルが無い場合には、
""
という部分で、「"」で囲まれた内容を表示します。
この場合は""の中には何も無いため、
即ち、C3セルとD3セルの2個のセルの内、数値が入力されているセルが無い場合には、何も表示しない様にするという訳です。
そして、C3セルとD3セルの2個のセルの内、数値が入力されているセルが、0個では無い場合、即ち、数値が入力されているセルがある場合には、
E2+C3-D3
の計算結果が表示されます。
尚、C列とD列に入力が無い場合には、E列に何も表示させない様にすると、途中の行に収入や支出が無い行、例えば、何かの補足等だけが入力されている行が存在すると、E列の表示の途中に空欄の行が1行挟まる事になり、
E2+C3-D3
の計算結果が
0+C3-D3
という事になってしまい、その行よりも上の行の残高が反映されなくなります。
ですから、前月までの繰越金額が、毎月必ずE2セルに入力されているものとすれば、E3に入力する関数は、次の様な数式とした方が、良いかも知れません。
=IF(COUNT($C3,$D3)=0,"",$E$2+SUM($C$C3:$C3)-SUM($D$3:$D3))
この式をE3セルに入力してから、E3セルをコピーして、E4以下に貼り付けておけば、例えばE4セルの数式は、
=IF(COUNT($C4,$D4)=0,"",$E$2+SUM($C$3:$C4)-SUM($D$3:$D4))
という様に、Excelが自動的に4行目に合う様に変換してくれます。
SUMは指定された範囲に存在する数値の合計を求める関数ですから、
SUM($C$3:$C4)
という部分は、C3~C4の合計、即ち、3行目~4行目の収入の合計値になります。
SUM($D$3:$D4)
という部分は、D3~D4の合計、即ち、3行目~4行目の支出の合計値になります。
$E$2
という部分は、E2セルの値、即ち前月までの繰越金額になります。
従って、
$E$2+SUM($C$3:$C4)-SUM($D$3:$D4)
の部分は、
前月までの繰越金額+その行までの収入の合計-その行までの支出の合計
を表し、収入や支出に記入漏れが無い限りは、その行の残高の値になります。
この回答への補足
早速のご回答ありがとうございます。
ただ残念ながら以下の式を使って
検算をしてみたところ帳尻が合いませんでした。
=IF(COUNT($C3,$D3)=0,"",$E$2+SUM($C3:$C3)-SUM($D$3:$D3))
他の回答者様と比較して自分なりに分析したところ
1.$E$2→E2にして絶対参照を外す。
2.SUM($C3:$C3)-SUM($D$3:$D3)を
SUM($C3:C3)-SUM($D$3:D3)とする。
にした方がいいのではないかと思い、変えてみたところ
繰越残高(E2セル)と各収入金額の合算は、
合致ししました。ただそこから支出金額を引くとなると
数値が合わなくなります。
他に改善する点がお気づきでしたら
ご教示願います。
お忙しいところ誠に恐れ入りますが、
よろしくお願い申し上げます。
No.2
- 回答日時:
>「=E2+C3-D3」の関数式を組めばいいのですが、
この式と「数値を未表示にする」を組み合わせると,エラーの原因になります。
E3:
=IF(C3+D3,SUM($E$2,$C$2:C3)-SUM($D$2:D3),"")
以下コピー
などのようにしてみます。
この回答への補足
早速のご回答ありがとうございます。
ダミーで帳簿を作って検算をしてみたら
ちゃんと帳尻が合いました。
ただE列(繰越残高)についてE2セルと
その時で最終記入したE列のセル以外の
セルの左端に緑の三角がついて、
エラーのお知らせがつきます。
これらのセルは「エラーを無視する」という
ことで処理してもよろしいでしょうか?
たびたびの質問、誠に恐れ入ります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel2019、2021の日付、曜日の表示について 2 2022/11/29 15:01
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) エクセルで最初に値が入っているセルを見つける方法はありますか? 2 2023/07/18 14:58
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- Excel(エクセル) 【再度】Excelの関数について教えてください。 4 2023/07/28 13:06
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) エクセルの計算式を教えてください 3 2023/03/14 13:48
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
【スプレッドシート】指定の日...
-
Microsoft Formsの「個人情報や...
-
会社PCのメールが更新されない
-
【Excel VBA】PDFを作成して,...
-
マイクロソフト 一時使用コード...
-
エクセルでXLOOKUP関数...
-
office2016のパソコン2台インス...
-
Excelのセルの重複チェックが出...
-
teams設定教えて下さい。 ①ビデ...
-
Excel テーブル内の空白行の削除
-
Microsoft365で写真をアルバム...
-
会社におけるOfficeライセンス...
-
VLOOKUP関数について
-
Windows 11で、IME言語バー(IM...
-
officeビジネス型のワードやエ...
-
VBAファイルの保存先について
-
エクセル、ワード、ネット検索...
-
Outlook で宛先が複数の場合の人数
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報