大家さんのたなこ管理 「家賃台帳」


匠への技

「匠への技」第13弾は、大家さんのためのたなこの家賃管理です。
この作品は、昨年に賃貸住宅を所有する経営主さんからの依頼で作成したブックを少々機能を端折って作り直した作品です。がしかし、家賃管理には十分すぎる機能を有し、また十分申告にも使えるように仕上げています。3つのシートを1つにまとめた都合上、デザイン的に「きれい...」といったものではありませんがデザインは別として機能重視でお試し下さい。また、気に入らない箇所は任意カスタマイズで対応下さい。
尚、本格的にこのフォームを利用しカスタマイズを依頼したい方はメールにてご相談下さい。

家賃台帳管理システムの主要な機能
@たなこの入退去を管理することができます。 
Aたなこ単位に敷金管理ができます。
Bたなこ単位に月別の入金状況を把握することができます。
C会計年度末には「家賃台帳」を印刷し申告に備えることができます。

<リメイクへのアドバイス>
  たなこの住所、緊急連絡先、保証人といったものが項目として不足してますので改造する場合はその点に留意するとよい。

■ シートの構図





シート全体を貼り付けることができないので3つに分割して貼り付けしました。多少見づらい点につきましてはご了解願います。
シートの黄色部分がエントリー域です。
列は、A列からU列までです。セルH3には申告者の名前を入力します。また、セルT3には建物の名前を入力します。2行で1たなこのデータとなります。

図のセル位置 項  目 説  明
H3 申告者 申告する人の名前を入力
T3 建物名称 アパート名等建物の名称を入力
A6 部屋番号 任意に番号を採番  (ユニーク)
B6 氏名 たなこの名前を入力
C6 敷金 預かっている敷金を入力
D6 家賃 毎月の家賃を入力
E6 移動 入居月 新しく入居した時にその月を入力する。
F6 移動 退去月 退去した月を入力する。
G6 氏名 ※ここから申告書に使用する台帳印刷の範囲となる。
セルB3を代入   関数式:  =B3  (非入力)
H6 家賃 セルD3を代入   関数式:  =D3  (非入力)
I6〜T6
I7〜T7
1月〜12月 1月から12月までの収納状況を管理する欄となる。
上段:家賃 → 収納月日を入力すると家賃が自動的に表示される。 (非入力)
下段:収納月日 → 11/8の形式で収納した月日を入力する。
U6 横合計 1月〜12月までの家賃の合計  (非入力)
I46〜T46 縦合計 月ごとにたなこ家賃の隔行(偶数行)合計  (下図)



■ 特別な関数式

  1. セルI6からT6の関数式  (セルI6の式で説明)
    条件は、
    • セルI7の月日が入力された時にセルD6の家賃セルI6へ代入する。
    • セルE6の入居月が入力されている時は、その月以下の収納月日が入力されても家賃は代入しない。
    • セルF6の退去月が入力されている時は、その月以降の収納月日が入力されても家賃は代入しない。


    書式:  =IF(AND($E6="",$F6=""),IF(I7<>"",$D6,0),
           IF($E6<>"",IF($E6<=(COLUMN()-8),IF(I7<>"",$D6,0),0),
             IF($F6>=(COLUMN()-8),IF(I7<>"",$D6,0),0)))
    • IF(AND($E6="",$F6="")
      列を固定にしている理由($E、$F)は式をI列からT列までフィルドラッグで対応させるための工夫です。論理式ANDは、セルE6とセルF6がどちらも無記入であることを判断しています。
      <真の条件>
      IF(I7<>"",$D6,0)   セルI7の収納月日が入力されている時は セルD6の家賃を代入しそうでなかったら 0 を代入します。

      <偽の条件>
      IF($E6<>""  入居月の入力確認です。 入力されている場合が真の条件となります。
      • 真の条件 
        IF($E6<=(COLUMN()-8),IF(I7<>"",$D6,0),0)
        入居月が COLUMN()-8 と等しいかもしくは小さい場合の判断です。入居月が入力されている場合はその月から家賃が発生することになりそれまでの期間の判定のための式となります。 COLUMN()-8 とはカレントセルが何月なのかを求めるための式です。例えばセルI6であれば COLUMN関数により9が求められ 9−8=1 I列=1月の意味です。次のJ列であれば 10−8=2 となり2月を意味します。
        真の時、セルI7の収納月日が入力されている時にセルD6の家賃を代入、そうでなければ 0 を代入します。
      • 偽の条件
        IF($F6>=(COLUMN()-8),IF(I7<>"",$D6,0),0)
        退去月が COLUMN()-8 と等しいかもしくは大きい場合の判断です。その他は真の条件と同様の説明となるので省略します。

  2. セルI46からT46の関数式   (セルI46の式で説明)
    条件は、セルI列の6行、8行、10行、12行.....44行の家賃を集計します。1行置きの集計は、6行から44行までの偶数行が対象となります。
    そこで関数式は 
    {=SUM((MOD(ROW(I6:I45),2)=0)*(I6:I45))} となります。 配列数式です。  =SUM((MOD(ROW(I6:I45),2)=0)*(I6:I45)) 式を入力後に Ctrl+Shift+Enter キーを押下し配列数式の宣言を行います。 もしくは、 =SUMPRODUCT((MOD(ROW(J6:J45),2)=0)*(J6:J45)) でも同様の値を求めることができます。

    MOD関数は、数値を除数で割ったときの剰余を計算します。 (ちなみにMOD関数の読み方は MODulus (モデュラスと読む)です。)

    配列展開図  (セルI6〜I11の範囲と仮定)
    (MOD(ROW(J6:J11),2)=0) (I6:I11) (MOD(ROW(J6:J11),2)=0)*(I6:I11)
    セル ROW関数の値 MOD関数の値 真・偽 金額  計算式・値
    I6 6/2=3.. TRUE 1,000  1×1,000=1,000
    I7 7/2=3.. FALSE 2,000  0×2,000=0
    I8 8/2=4.. TRUE 3,000  1×3,000=3,000
    I9 9/2=4.. FALSE 4,000  0×4,000=0
    I10 10 10/2=5.. TRUE 5,000  1×5,000=5,000
    I11 11 11/2=5.. FALSE 6,000  0×6,000=0
    MOD関数でカレント行を2で割った時の余りを求めます。0の時は偶数、1の時は奇数です。
    また、真・偽のTRUEは1が返値です。FALSEは0が返値です。
    この例の合計は9,000となります。

■ 印刷

印刷は、マクロで組んでいます。既にPrintボタンにリンクさせています。

●VBAソースコード

Sub prt()
'
  Range("G1:U46").Select
  Selection.PrintOut Copies:=1, Collate:=True
  Range("A6").Select

End Sub

■ シートの保護

誤って数式の入力されているセルにかぶせ書きしてしまったりするので以下の手順でシートを保護しましょう。(Downloadブックは保護済みです。)

●保護手順

メニューバー「ツール」 → 「保護」 → 「シートの保護」
'
  図のように指定します。


※カーソルの方向を標準「下」から「右」に変更することで入力がより容易になります。


■ お試しブックのダウンロード

Program room の FreeWare からDownloadして下さい。下図 Downloadボタン をクリックするとジャンプします。