エクセルで出納帳を作成しています。

質問

エクセル２００３で出納帳を作成しています。
預金口座の入出金・振込等を別のシートでも作成したいのですが
出納帳に預金口座の入出金等を入力すると
自動的に口座のシートに反映させる方法を教えてください。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.6

tom04 · ベストアンサー

こんばんは！
またまた・・・顔を出してしまいました。

なんか交換日記状態ですね！

ところで本題ですが、
確かに補足にあるように
口座から現金の出し入れがあると、現金出納帳の増減と、口座預金の増減が逆になりますよね。

そこで無理矢理って方法になりますが、
↓の画像のように数列ほど作業用の列を作らせてもらいました。
とりあえず、「○○銀行」のみでの説明になります。
かなり長文になりそうです。

まず、Sheet1に連番を付けさせてもらいます。
（これは銀行口座の収入・支出が現金出納帳と逆になることがあるため、とりあえず、出入金関係なく1列に表示させたあと、そこから抽出するためです。）

そして、各銀行毎のSheetに

Sheet1の現金勘定科目にあるものを参照して、出納帳の残高の欄で
銀行関連の出入金は金額を変動させないようにするためです。
G4セルに
=IF(OR(COUNTIF($L$2:$L$10,C4),D4="現金"),G3+E4-F4,G3)
という数式を入れ、なおかつ条件付書式で
「セル値が」→「次の値に等しい」→数式欄に「=G3」として
フォントの色を「白」にします。そして、オートフィルで下へコピーすると
現金出納帳の金額に変動ない場合は数値は表面上では見えなくなります。

H4セルに
=IF(AND(D4=$H$1,COUNTIF(Sheet2!$H$2:$H$10,C4)),ROW(B1),"")
I4セルに
=IF(AND(D4=$H$1,COUNTIF(Sheet2!$I$2:$I$10,C4)),ROW(B1),"")
J4セルに
=IF(E4+F4=0,"",E4+F4)
としてG4～J4セルを範囲指定し、J4セルのフィルハンドルでずぃ～～～！っと下へコピーします。
まず、1ヵ月のデータはこれ以上ない！というくらいまでコピーしておいたほうが良いかもしれません。

Sheet2のA4セルに
=IF(COUNT(Sheet1!$H$4:$I$1000)<ROW(A1),"",INDEX(Sheet1!A$4:A$1000,SMALL(Sheet1!$H$4:$I$1000,ROW(A1))))
として、C4セルまでコピー

D4セルに
=IF($A4="","",IF(COUNTIF(H$2:H$10,$C4),INDEX(Sheet1!$J$4:$J$1000,$A4),0))
とし、E4セルまでコピー

F4セルには
=IF(A4="","",F3+D4-E4)

最後にA4～F4セルを範囲指定し、F4セルのフィルハンドルで下へコピーすると
画像のような感じになります。

これで、Sheet1の操作だけでそれぞれのSheetに反映されないでしょうか？

以上、かなり強引な方法で長々と書きましたが、
参考になれば良いのですが・・・

どうも失礼しました。m(__)m

お礼

こんばんは。
丁寧にご指導いただき、ありがとうございます！！
希望通りの出納帳が出来ました！！
現金に入力すると口座１・２に反映させる事が出来ました。

まだまだ　エクセル初心者で
何回もお世話になり、丁寧に教えてくださり
わかりやすかったです。

これから　いろいろ　勉強していきたいと思います。
本当にありがとうございましたm(_ _)m

回答No.5

tom04

No.3・4です！
今回も返答が遅くなってごめんなさいね。
（昨日は同窓会で飲みすぎました）

前回の補足を読ませていただく限りにおいては
別Sheetへの転記は希望に近い形になったのですね？

Sheet1の現金出納帳の収支合計の欄の問題だと解釈しましたので、
前回の表をそのまま利用させていただき、
摘要欄に「○○銀行」または「△△銀行」の項目があれば
Sheet1の残高の欄は変動がないようにしてみました。

1行目は前月からの繰越金の欄にしていますので、
数式は2行目のF4セルに入れています。

=IF(COUNTBLANK(D4:E4)=2,"",IF(OR(C4={"○○銀行","△△銀行"}),F3,F3+D4-E4))

としてオートフィルで下へコピーすると
画像のような感じになるのですが・・・

これで、現金関係の収支があった場合にのみ、残高欄の数値が変動すると思います。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

補足

いつも丁寧に答えて頂き、ありがとうございます。

またまた、説明不足で申し訳ありません。

１回の作業で口座のシートに反映され
口座の振込み入金(収入)や引落とし（支出)のときに
現金出納帳の動きがなくなったのはいいのですが、

口座から現金を引き出したり、預け入れるときの事は
どうすればいいのでしょうか？

口座から引き出したときは、現金出納帳には収入ですが
口座の出納帳は支出なのです。

うまく反映できたと思って喜んでいましたが
口座引出・預け入れの場合は
現金出納帳と口座出納帳で収入と支出が逆にならないと
計算が合わないのです。

それと
摘要項目を増やして○○銀行　△△銀行としていても
引き出しや預入の場合は
現金出納帳の残高の動きに反映したいのですが・・・。

ややこしくて申し訳ないです。

こういうことって　出来るのでしょうか？

何度も申し訳ありませんが、よろしくお願いします。

回答No.4

tom04

こんにちは！
No.3です！

返答が遅くなってごめんなさい。

結局銀行が二つあり、現金出納帳にその銀行関連の入出金があれば
それぞれのSheetに反映させたいと言うことだと解釈して
回答させていただきます。

Sheet1が現金出納帳でSheet2・Sheet3が各銀行関係のSheetにしています。

Sheet1に各銀行に振り分けるための作業用の列を2列使わせてもらっています。
尚、勝手に摘要欄は増やさせてもらいました。

Sheet1のG3セルに
=IF($A3="","",IF($C3=G$2,ROW(A1),""))
H3セルに
=IF($A3="","",IF($C3=H$2,ROW(B1),""))
とし、G3・H3セルを範囲指定し、H3セルのフィルハンドルで下へずぃ～～～！っとコピーします。
（とりあえず数式が1000行まで対応できるようにしていますので
そのくらいまでコピーしても構いません）

Sheet2の
A3セルに
=IF(COUNT(Sheet1!$G$3:$G$1000)<ROW(A1),"",INDEX(Sheet1!A$3:A$1000,SMALL(Sheet1!G$3:G$1000,ROW(A1))))
として隣のB3セルまでコピー
C3セルに
=IF($A3="","",INDEX(Sheet1!D$3:D$1000,SMALL(Sheet1!$G$3:$G$1000,ROW(A1))))
として、隣のD3セルまでコピー
最後にA3～D3セルを範囲指定し、D3セルのフィルハンドルで下へコピーすると
画像のような感じになります。

同様にSheet3にも
A3セルに
=IF(COUNT(Sheet1!$H$3:$H$1000)<ROW(A1),"",INDEX(Sheet1!A$3:A$1000,SMALL(Sheet1!$H$3:$H$1000,ROW(A1))))

C3セルに
=IF($A3="","",INDEX(Sheet1!D$3:D$1000,SMALL(Sheet1!$H$3:$H$1000,ROW(A1))))

とし、Sheet2のコピー方法と同じ操作をしてみてください。

これで希望に近い形にならないでしょうか？
尚、空白は「0」となりますので
前回同様、メニュー　→　ツール　→　オプションから「ゼロ値」のチェックは外しておきます。

以上、今回も長々と書いてごめんなさいね。
参考になれば幸いです。m(__)m

補足

丁寧に教えてくださり、ありがとうございます。
口座シートにそれぞれ反映することが出来ました。

こちらの説明不足で申し訳ありませんが
シート1(金銭出納帳)は
現金の動きだけを見たいのです。

今までは　振込みがあったときは

シート1(金銭)
　　　摘要　　収入　　支出　　残高
　　交通費　　　　　　1000　　9000
　　振込み　　2000　　2000　　9000

シート2(銀行1)
　　　摘要　　収入　　支出　　残高
　　振込み　　2000　　　　　　2000

と入力していました。

シート1に
今まで通りに入力すると
シート2に収入・支出とも反映され
銀行の残高が合わなくなります。

シート1の振込みを収入だけにしてしまうと
現金残高が合わなくなるのですが
どのようにしたらいいのでしょうか？

何度も申し訳ありませんが、よろしくお願いします。

回答No.3

tom04

こんばんは！
参考になるかどうか判りませんが・・・

具体的にどのような感じの表にしたいのか判らないので
こちらで勝手に↓の画像のような感じで表を作ってみました。

当方使用のExcel2003での回答です。

左側が出納帳とします。
とりあえず全て預金口座に関係する項目にしていますが、もし預金口座以外の項目があると
別の方法を考えなければならないと思います。

Sheet1のE列を作業用の列とさせてもらって、
E3セルに
=IF(COUNTBLANK(B3:C3)=2,"",ROW(A1))
という数式を入れずぃ～～～！っとオートフィルで下へコピーします。
（Sheet2の数式が1000行まで対応できるようにしていますので、そのくらいまでコピーしても構いません）

次にSheet2のA3セルに
=IF(COUNT(Sheet1!$E$3:$E$1000)<ROW(A1),"",INDEX(Sheet1!A$3:A$1000,SMALL(Sheet1!$E$3:$E$1000,ROW(A1))))
という数式を入れ、D3セルまでコピーし、
A3～D3セルを範囲指定してD3セルのフィルハンドルで下へコピーすると
画像のような感じになります。
（尚、セルの表示形式でB・C列は標準にします）

そして、空白の場合は「0」になってしまいますので、
メニュー　→　ツール　→　オプションの表示タブで「ゼロ値」のチェックを外しています。

残高の列は適当に数式を入れてください。

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

補足

ありがとうございました。
説明不足ですみません。

シートは３シート作成しています。
金銭出納帳（現金・通帳すべての出納帳）
預金1
預金2
の３シートです。

どのシートも
Ａ列に日付、Ｂ列に摘要、Ｃ列に収入、Ｄ列に支出、Ｅ列に現金残高
となっています。

自動で反映させるために項目をもうひとつ
追加しないといけないと思うもですが

どのようにすればいいでしょうか？

回答No.2

yone_sk

[出納帳]シートの
B列に入金額
C列に出金額

とすると

[口座]シートに
=SUM(出納帳!B2:B10)-SUM(出納帳!C2:C10)

お礼

ご指導ありがとうございました。
これから　もっと勉強していきたいと思います。

回答No.1

imogasi

何でも他（一般の記帳とは別にとか、抜き出して）と違うことをコンピュターにやらせるには、区別を知らさないと（区別できるものがエクセルのシート上に無ければ）、処理できません。
それを＞預金口座の入・・で片付けては質問にならない。
質問者はコンピュター処理に慣れてないのかな。
だから銀行関係の記帳行の決った列には決めたサインをいれるとか、どこかに「銀行」という語句をいれるとか（やや紛らわしいが）、何かないといけない。
質問者の場合は、それが何か、書くべきです。
その条件が関数的に単純なら、抜き出し問題として、Googleで「imogasi方式」で照会してもらえば、沢山の、関数による抜き出し問題の質問例と回答が出る。
ただ残高などは、出納帳の残高とはべつの意味なら、明細の主要な部分は出ても、別途追加入力項目は必要になろう。
質問者の場合、銀行通帳の動きは、出納帳の動きと一致させられるのかどうかとか問題。

お礼

ご指導ありがとうございます。
これから　もっと勉強していきたいと思います。