【Excel】エクセルで複数の銀行口座を管理するファイルの作り方

銀行口座を複数持っていると、どの口座にいくらの預金があるのかがわからなくなってくる。

そこでエクセルを使って、ひとまとめに出来ないかと考えた。

複数口座の資産が一目でわかるようなったものが作成できたので、ご紹介したい。

広告

作成する銀行口座管理用エクセルのサンプル

今回作成する複数口座管理用エクセルの目的を以下に示す。

・使用する関数は少なく
・口座間の資金移動を明確にする
・収入と支出を口座毎に振り分ける
・複数口座の合計金額(総資産)を算出

大まかな完成イメージは以下の通り。

複数口座管理用エクセルのサンプル

使用する関数

今回作成するエクセルで使用する関数は以下の通りだ。

・SUM関数(サムかんすう)
→選択範囲の数値を合計する。
※各講座の金額を合計する時に使用する。
記述例=SUM(F3:M3)
→F3セルからM3セルまでの数値を合計する
・入れ子のIF関数(イフかんすう)
→条件を設定し、処理を分岐させる。
※口座の仕訳をする時に使用する。
記述例=IF($E3=”お小遣い”,IF($D3=”収入”,F2+$C3,F2-$C3),F2)
→E3セルの値が お小遣い と入力されたら次のIF文へ分岐する。D3セルの値が 収入 と入力されたら、足し算、それ以外は引き算をする。それ以外の場合は上のセルを参照する。

今回は「口座の仕訳」と「収入・支出」を判別するために入れ子のIF関数を使用した。

最初のIF文で口座を判別し、2個目のIF文で収入と支出の判別している。

作成する記入欄

以下の項目をシートに記入しておく。

・A列:日付
・B列:品目
・C列:金額
・D列:収支
・E列:宛(あて)
・F列:お小遣い
・G列~:銀行(所持している口座分)
・M列:証券口座(所持している口座分)
・N列:資産

銀行や証券口座についてはご自身の銀行名にするとわかりやすい。

口座数も所持している口座分だけ作って欲しい。

複数口座管理用エクセルのサンプル

複数口座管理用エクセルの作成手順

手順①:デザインの作り方

完成形のエクセルシートを参考に自分の持っている銀行名毎に項目を作成する。

色分けをしておくと、見易くなるのでおすすめ。

複数口座管理用エクセルのサンプル欄

手順②:収支欄リストの作り方

収支欄は後のIF文作成の際に、誤記入を防ぐためプルダウンのリストから「収入」と「支出」を選択できるように設定する。

完成イメージは以下の通り。

エクセルのデータ入力規則(収入・支出)完成イメージ

データの入力規則の作成方法

①「データ」タブの「入力規則」を選択
②「データの入力規則」というウィンドウが開く
③「データの入力規則」内の「条件設定」から「リスト」を選択
④「元の値」欄に項目を記載する

実際の画面は以下の通りだ。

D2セルを選択した状態で「データ」タブの「入力規則」を選択する。

エクセルのデータタブ

「入力規則」を押すと「データの入力規則」というウィンドウが開く。

エクセルのデータ入力規則ウィンドウ

「データの入力規則」内の「条件設定」から「リスト」を選択する。

「元の値」欄に「収入,支出」と記載する。※各項目は「,(カンマ)」で区切る。

エクセルのデータ入力規則(収入・支出)

同様にK3セルとN3セルにも設定する。

記載が完了したらそれぞれ必要な行までコピーする。

手順③:宛先リストの作り方

収支欄と同様に宛先もリストから選択できるようにする。

次節以降で使用するエクセルの関数は半角、全角、小文字、大文字を違う文字列として認識するため、誤記入を防ぐためだ。

サンプルでは6口座分を用意した。

完成イメージは以下の通りだ。

エクセルのデータ入力規則(銀行)完成イメージ

上述のように「データの入力規則」内の「条件設定」から「リスト」を選択する。

エクセルのデータ入力規則ウィンドウ

「元の値」欄に「お小遣い,A銀行,B銀行,C銀行,D銀行,E銀行,F銀行」と記載する。※各項目は「,(カンマ)」で区切る。

エクセルのデータ入力規則(銀行)

アルファベットにしている銀行名は、ご自身が所持している銀行口座名にしていただきたい。

手順④:各口座欄に数式を入れる

複数口座管理用エクセルのサンプル欄

各口座ごとに入出金を仕分けるために、口座欄には以下の数式を記入する。

A銀行の項目(G2セル)
=IF($E3=”A銀行”,IF($D3=”収入”,G2+$C3,G2-$C3),G2)

式はE3セルの値が A銀行と入力されたら次のIF文へ分岐する。D3セルの値が 収入 と入力されたら、足し算、それ以外は引き算をする。それ以外の場合は上のセルを参照する、という内容だ。エクセルの関数は、半角、全角、小文字、大文字を違う文字列として認識する点に注意が必要だ。

例えば、「エクセル」と「エクセル」では違う文字列と認識される。

なので、入力ミスを減らすために「収支」と「宛先」はプルダウンのリストから選択できるように設定をした。

記載が完了したら、所持している口座分だけコピーを行う。

列が移動すると参照するセルが異なるため、B銀行以降についても以下に記述する。

B銀行(H2セル)
=IF($E3=”B銀行”,IF($D3=”収入”,H2+$C3,H2-$C3),H2)
C銀行(I2セル)
=IF($E3=”C銀行”,IF($D3=”収入”,I2+$C3,I2-$C3),I2)
D銀行(J2セル)
=IF($E3=”D銀行”,IF($D3=”収入”,J2+$C3,J2-$C3),J2)
E銀行(K2セル)
=IF($E3=”E銀行”,IF($D3=”収入”,K2+$C3,K2-$C3),K2)
F銀行(L2セル)
=IF($E3=”F銀行”,IF($D3=”収入”,L2+$C3,L2-$C3),L2)

手順⑤:証券口座欄の作り方

証券口座欄は手動で入力する。

証券はその時々で金額が変動するからだ。

私は毎月末の時価を記入するようにしている。

手順⑥:資産欄の作り方

資産欄はお小遣いを含めた全銀行口座の金額を合計する。

総資産の算出
=SUM(F3:M3)

式はお小遣いと各銀行口座、証券口座の金額を合計する。

私はお小遣いまで含めたが、銀行に入っている分のみで良い場合は合計範囲を変更してほしい。

複数口座管理用エクセルの作り方のまとめ

銀行口座を複数持っている人は多いと思い、複数口座管理用エクセルを作成した。

口座が増えてくると管理が難しくなる。

今回エクセル作成手順が、口座管理の手助けになることを祈っている。

コメント

  1. 素晴らしい! 
    Wordばかりで、Excelなんてほとんど使ったことがなかったので、最初はよくわかりませんでしたが、プルダウンのやり方とif関数のおかげで、自分なりの「口座出納帳」が作れました。
    感謝いたします! 
    他の記事も、順次読ませていただきます。

    • のびおさん
      ありがとうございます!

タイトルとURLをコピーしました