EXCELでの入金管理するためのデータの持ち方

質問

昨日掲載したものの画像の貼り忘れがあり、再掲します。よろしくお願いいたします。

駐車場料金の入金管理をしています。
・月額固定料金５０００円
・当月分は前月末までに支払う
・末日を過ぎれば延滞
・過入金は翌月分に充当する
・末日時点での入金合計をみたい

今までは各人の入金状況を月ごと１２ヶ月分を横長の表に入れて管理していましたが、あまりに煩雑なので作り直したいのです。件数は５００件あります。

１年使った専用ソフトは高機能すぎて却って面倒でした。

試しに画像のように作ってみました。項目に過不足は無いでしょうか？オートフィルターなどを使う予定です。難しい関数は使えません。

参考になるシートがあれば見てみたいです。

質問内容に不明・不足があれば補足欄で説明します。

よろしくお願いいたします。

回答No.3

masnoske · ベストアンサー

No.2です。
簡単とはいえデータベースなので、１シート１テーブルの原則を崩したくないからです。そのほうが結果的にメンテが楽で、機能拡張もしやすいです。

簡単なデータベースなら Excelのテーブル機能を利用して、１シートに複数テーブルを持つのはありですね。

お礼

テーブルとピボットテーブルの概念を初めて知りました。使えそうなので深堀りしてみます。

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

回答No.2

masnoske

私なら、３枚のシートを使ってピボットテーブルで集計します。

全ユーザーの入金データシートを１枚。
ユーザーの契約開始日と終了日のシートを１枚（過不足の計算に使用）。
月別の契約料金シートを１枚（当面は一律5000円なので無くても大丈夫）。

お礼

ピボットテーブルは昔別の業務でためしたことがありました。すっかり存在を忘れていました。今回ネットで少し調べて使えそうな感じがしてきました。

※契約開始時と終了日を入金管理とは別シートにする理由を教えて下さい。同じシートではいけませんか？

ありがとうございます。

回答No.1

imogasi

こういう質問は、こういう質問コーナーで解決するのは
無理があると思う。
金を払って業者にたのむべきなのだ。
あるいは市販既存ソフトを探すとか、
ＷＥＢでフリーソフトがないか探すとか
（Ｖｅｃｔｏｒとか）。
普通はシステムを作る人と、駐車場貸主が対話して、どうするか
練り上げていくもので、無料質問メールコーナーでやろうと
いうのが無理だ。
＝＝＝
下記を参考までに読んでみて。
下記のことも何も触れてないのが、システムを考えたことがない
ようだね？　
　　であれば手作業と関数ぐらいしか頼れないな。
（１）やはり納付状況管理目的か、税務・雑所得の申告用か？
（２）入金は、銀行口座へ振り込み（Ｏｎｒｙ）か？
　　　その他の手段は？
（３）一覧データは、何をまず見たい？
　　　　エクセルシートは２次元表なので、多くの
　　項目を盛り込むのは、作成が面倒でわかりにくくなる。
　　できればマスタデータシートで、それらニーズをほとんど
　　満たせればよいが。
（４）質問者は、ＶＢＡなどプログラムは組めるのか？
　　　システムの経験がないと、良いものはできにくい。
　　　質問者は、エクセル関数利用経験経験どまりか？
　　　それでは、増しなものはできないと思うが。
（５）駐車場利用者は何人？画像例の４人か？
　　　システムを考えるときに、簡易に済ませられるか
　　　どうかを左右する。
（６）現に、何に、困っているのか？
　　　例えば過剰・不足入金がないか調べることとか？
　　　　こんなの頻繁に起こっているの？
　　　数人は毎月あるかもしれないだ、手作業で済ませられない？
　　　未納者の割り出しはもちろん必須だろうが。
＝＝＝
　小生なら、ＶＢＡを少しできるので、古典的ではあるが
オフライン・バッチ処理方式にする。
（１）マスターデータは
　　　利用者番号順。又は利用者漢字名順やカナ順。
　　Ａ列　利用者番号（駐車場スペースの１台分の番号、数字？）
　　Ｂ列　漢字氏名　必要なら別列にかな・カナ氏名。
　　Ｃ列　１か月の料金（毎月定額だよね）
　　Ｄ列から１２列　　暦年の１月から１２月までの列
できれば銀行通帳での振込人名（カナ？）欄もあれば
これでプログラムでマッチング処理できるかも。
（２）振り込み入金のある銀行の通帳から、手作業でシートへ入力
　　　氏名　日付　金額
　　氏名は前もって入れたシートの表を作ておくとか。各月回転使用する。
　　(日付・金額は通帳でわかるが、振込人名はどうなるか？
　　カタカナ等で入ってくる場合が多いのでは？
　　利用者番号はどうするか？利用者番号は通帳からでは判るまい。
　　人間が振るか。漢字氏名を振るか？振込人名を入力して、
　　－－＞利用者番号を特定するか
ーー
　　（３）（２）のデータで（１）のデータを更新する。
　　　該当者の行の当該月列に、入金日付、金額を、キーをマッチングして
　　　ＶＢＡでデータ入力。
　　（４）ＶＢＡで、同時に契約料金と入金金額を比べ、不足。余分、
　　　　複数月を先納付者、未入金者などを割り出し印刷したりする。
　　（５）（４）の修正分は少数だろうから、手作業で（１）を修正

お礼

詳細な解説をありがとうございます。