（ 3 ）銀行口座別の資金繰り予定表の作成

「銀行口座残高を管理するExcelファイル」では、銀行口座ごとの資金繰り予定表を作成するとともに、すべての銀行口座の残高予測を一覧にすることを目的としています。

今回は、銀行口座別の資金繰り予定表の作成についてコメントいたします。

使える資金繰り予定表の枠組み

本稿では、将来の入金情報と出金情報を簡単に入力することで、複数ある銀行口座の残高を日繰りベースで一覧表でチェックできるような資金繰り予定表の作成を試みています。

その枠組みは、ひとつのフォルダを作って、その中に次の2つのExcelファイルを作成し、フォルダ内でリンクをするというものです。

入金予定と出金予定を管理するExcelファイル
銀行口座残高を管理するExcelファイル

具体的には、「入金予定と出金予定を管理するExcelファイル」のデータが、SUMIFS関数によって「銀行口座残高を管理するExcelファイル」に返されることになります。

銀行口座別の資金繰り予定表の構成

「銀行口座残高を管理するExcelファイル」では、銀行口座別にシートを作成し、各シートでは「入金予定と出金予定を管理するExcelファイル」に入力されたデータから、銀行口座別の資金繰り予定表を作成します。

さらに、すべてのシートを串刺しにして全ての口座の残高を一覧できるシートを作ります。

銀行口座別資金繰り予定表のシートの構成として、次のようなイメージが考えられます。

まず、1行目のA1のセルに預金口座の名前を入れます。

A1セルに預金口座の名前を入れたセルを作るのは、SUMIFS関数で「入金予定と出金予定を管理するExcelファイル」のデータからこの預金口座の情報だけを引っ張ってこれるようにするためです。

2行目に項目を入れます。A列が「日付」、B列が「入金」、C列が「出金」、D列が「入金前当日残高」、E列が「当日残高」、F列以下に「備考」や「コメント」などを入れます。

A列には下までダーッと日付を入れます。

オートフィルでいくのもよし、「ホーム」→「フィル」→「連続データの作成」でいくのもよしです。

ポイントはそれぞれの事業所で必要十分な最終的なフォーマットが固まるまでは大展開をせず、ひとつかふたつの口座だけにとどめて試行錯誤をすべきです。フォーマットが固まっていないのにたくさんのシートを作ってしまうとそれだけリカバリーに時間がかかってしまいます。

まずは、ひとつの銀行口座で作ってみることから始めるべきです。

SUMIFS関数のリンク貼り付け

前回は「入金予定と出金予定を管理するExcelファイル」の同じシートのなかでSUMIFS関数でイメージどおりの計算ができたかどうか確認しました。今回はいよいよ別のファイルで行います。

さて、「特定の銀行口座」の「特定の日付」という複数の条件を付けるにはSUMIFS関数を使います。

銀行口座別資金繰り予定表のシートにおいて、「特定の銀行口座」は、まさにA1セルに入力されている銀行口座名です。

そして、「特定の日付」は、まさにA列に入力されている日付です。

数式を下にコピーすれば毎日の合計値が返されることになります。

ただし、ただ下に数式をコピーすればよいわけではありません。1行下がれば数式のセルも1行下を参照してしまいます。このため、数式のセルやセル範囲に適切に「$」マークを付けて固定する必要があります。

まず、SUMIFS関数のうち、「合計対象範囲」「条件範囲1（日付）」「条件範囲2（銀行口座）」は「入金予定と出金予定を管理するExcelファイル」にある内容なので絶対参照で固定しなければなりません。そこで、列を示すアルファベットの両側に「$」を付します。

同じく「条件2（特定の日付）」はA1セルに入力されている銀行口座名であり、常に固定されていなければならないため、列を示すアルファベット（つまりA）の両側に「$」を付して「$A$1」とします。そして、「条件1（日付）」のみはそのままにして下にコピーします。

すると、「入金予定と出金予定を管理するExcelファイル」にランダムに入力されたデータから、A1セルに入力された銀行口座について日付ごとの入金額（合計額）が表示されます。

続いて、出金額についての数式を入れます。

ここで、入金額（B列）のセルのある数式を上から下までそのまま出金額（C列）にコピーしてもうまくいきません。

なぜなら、B列（入金）でA列（日付）を参照していた数式をそのままC列（出金）にコピーすると数式の参照がA列からB列に移動してしまいA列（日付）を参照しないからです。

そこで、B列の数式をC列に横方向へコピーするときは、日付（A列）をいったん絶対参照（アルファベットの両側に$を付ける）にしてコピーし、その後に下方向にコピーするときは絶対参照を解除（アルファベットの両側の$を外す）してからコピーします。

これで出金額についても数式が入りました。

銀行口座別の資金繰り予定表の完成

「入金予定と出金予定を管理するExcelファイル」のデータが、SUMIFS関数によって「銀行口座残高を管理するExcelファイル」に返されました。

そして、各銀行口座のシートで「前日残高」「入金」「出金」から「当日入金前残高」「当日残高」を算定します。

前日残高－当日出金＝当日入金前残高
当日入金前残高＋当日入金＝当日残高

これで将来の日付ごとの資金繰り予定表が完成します。

シートの複製

計算結果が正しいかどうかを、「入金予定と出金予定を管理するExcelファイル」でピボットテーブルを作成してチェックしたり、「日付列」「銀行口座列」をフィルターして合計値と一致しているか確認します。

これで大丈夫ということになれば、いよいよシートをコピーして別の銀行口座用のシートを作ります。

「A1」セルに銀行口座名を入れれば、「入金予定と出金予定を管理するExcelファイル」からその銀行口座の入出金情報が飛んでくるはずです。

これにより多数の銀行口座別の資金繰り予定表のシートを作ります。

ただ、これで終了ではありません。

多数の銀行口座の将来の残高の状況をすぐにチェックできるようにしなければなりません。

（つづく）