複数月の資金繰り表をExcelで作成したい

質問

よろしくお願いします。

現在、製造業の経理部で資金繰りを担当しています（預金5口座。現金は担当外）。

前任者は資金繰り表を作成していなかったので、自分なりにExcel2003で作ったものを使用しています。

しくみとしては、
(イ)シート(1)に入出金取引を入力する（入出金の別、取引日、支払日（入金日）、支払先（入金元）、実際金額、概算金額(*)、額面金額(*)、支払方法（＝振込・自動引落など）、銀行）。
(ロ)シート(2)の横軸に日付、縦軸に大項目＝支払方法、小項目＝銀行を入力し、交わった所にsumproduct関数を入れ、シート(1)の金額を参照させる。

(*)「概算金額」：月例の支払いについて、請求書が来る前から資金繰り表に取り込めるよう、前月の実際金額を入力したもの。請求書が来た時点で、「額面金額」に金額を入力する。「実際金額」には、(1)額面金額(2)概算金額の優先順位で数字が参照されるようにif関数を入れている。

というものです。

この方法では、不便な点が2点あります。

（A)一つは、数百件ある月例の支払をハンドルしにくいことです。
月例分については毎月コピペ修正しているのですが、シート(1)の中に月例取引とスポット取引が混在しているため、ゴチャゴチャしてきて辛くなってきます。
メインのシート(1)と別のシートを作ろうかと思うのですが、複数のシートをsumproduct関数で集計するのは難しい由、迷っています。
できれば、「月例振込」「自動引落」「その他振込」「入金」「資金移動」を別々に管理し、「月例振込」「自動引落」「入金」については、毎月コピペ→上書きで作成したいと思っています。

（B)もう一つは、月またがりのシートがうまく作れないことです。
支払には、各月発生・翌月末払いだけではなく、翌々月払いのもの、隔月で発生するもの、様々なものがあります。
以前は各月でシート(1)を作っていました。その方法ですと、シート(2)に設定できる日付はその月に限定されてしまい、月またがりの入出金が反映されません。
そこで、入出金の履歴をすべてシート(1)に集めることにしたのですが、（A)のような問題が発生し、ますますグダグダになってしまっています。

Accessを使って管理したいとも思いましたが、Accessが超初級レベルのため、データベースを1から構築できる気がしません。

そこで、Accessで取引データを入力→Excelにシート(1)としてエクスポート→シート(2)で参照
＝シート(1)の支払日を絞りこむことにより、シート(2)で月またがりの資金繰り表が自由に作れる、という形を考えています。

それでも、テーブルやリレーションの設定にさえ迷っている状態です。
「こういう風にやってみたら？」というアイディアがありましたら、大枠をご教示いただけないでしょうか？

回答No.1

imogasi · ベストアンサー

回答が付きませんね。察するに、ここで答える層と,こういうことをする層は違うからだと思う。
質問内容がごたごたしていて、読むのも、回答も手間がかかるし。
ここの質問は単品が中心。
何が聞きたいか質問文で的が絞れてない。呟き的な質問である。
こういうのは人により色々言える。
こうと書いても、質問者に実現するスキルが無くては、難しいと思う。
私は持論で、エクセルを業務に使うのは、エクセルVBAの知識は必須であると思うが、
VBAについては一言もでてないが経験はないのでしょう。
関数だけで処理をするのは無理です。データの抜き出し１つをとっても
複雑な式になる。
こういうVBAの勉強などをしないで、あれもやりたい、といっても出来ないでしょう。
ーー
話題は資金繰り表と大きく出ているが、内容は出納帳ではないか。
「エクセル　出納帳」でフリーソフトの照会をして、良さそうなものを使うか、出納・経理ソフトを
購入使用したら。仕様は窮屈でも我慢しなければならない点はある。
これからは、作るよりも、ソフトを使う時代です。
市販ソフトは数万円であり、質問者が何ヶ月もこの課題で時間を使えば、会社にとってトータルでは損になる。
また複雑なものを作ると次の人は使いませんよ。
ーーー
前提（こういうポリシーが大切では。質問には書いてないね）
・毎日実績データを入力し、集計して、計数を掴む方式とする（月初に月１で前月分を集計する
方式ではない。）
・現金小口出納の実出金は除外
・入金予定管理は除外（実績による。取り込みたい誘惑に駆られるが）
・支払いは（判っている範囲で）支払い予定管理を含める。
・手形支払い管理は？取り込まない。
・小切手の実引落は（振り出し日に出金扱い？）
・銀行別の出入残高の掌握は、はずす？（下記（２）から導出？）
・予定（未実現）と済み分をコードで判別し、事跡は済み分を計算する事とする。
　　予定額と書く定額を２列に分けて考え、A。実績で集計する計数とB.予定も含める
　　計数と使い分ける（実際、概算、額面と３つ要るかな？）
ーー
３段構え
（１）テンプレート（シートのコピーで使う）
定例支払い（振り込み、口座振り替え）、決定済み支払い、定例入金
　　毎月微修正し月初第１日までに
それを下記（２）シートへ毎月複写して使い、実績データを入れる。
（２）実データ（実際入金出金を時系列で記録）いわば「明細」
（３）まとめデータ（資金繰りに近付けるとか）
必要な項目（大項目）をまとめたシートを作る。
エクセルでは出来るだけシートは分けたくないが、毎月別シートはやむを得ないだろう。
ーー
シートが別シートになる場合の翌月へ残高繰越は関数では難しいが、VBAなら見出し漢字などを
頼りになんとかなる。
行的に途中空白が無い場合、Sheet1のB列最下行の値はSheet2で=LOOKUP(10^16,Sheet1!B:B)
途中行まで=LOOKUP(10^16,Sheet1!B2:B7)
VBAではユーザー関数（１００行目以前で最下行）
Function 最下行(b) 'Bは列記号
最下行 = Range(b & 100).End(xlUp).Row
End Function

お礼

ありがとうございました。

資金繰りソフトを買ってもらう方向で話をすすめます。