3-3 エクセルで契約更新表を作成する方法

更新の案内、更新料の請求を確実に行っていくには、管理物件の契約を網羅的に把握しなければならないため、人間の記憶だけに頼って対応することは不可能です。その為、何らかのツール（エクセルやシステム）を利用しないと適切な更新管理を行うことはできません。ここでは、契約更新管理表をエクセルでいくつかの関数を使いながら作成する方法をご紹介していきます。

契約終了時期を迎える入居者をエクセルで特定する

更新管理表を作成するのに利用するエクセルの関数は3つだけです。
また、サンプルの数式をコピー＆ペーストしても動きますので、ご参考ください。

更新管理はどのようにされていますか？

皆さんは契約の更新管理はどのようにされていますでしょうか？
私が賃貸管理ソフトのご紹介や操作のデモに伺った各不動産管理会社様でも、手作業・手入力で振り分けていて、非常に非効率でアナログな状況で悩んでいるというご相談をいただくことも少なくありませんでした。
具体的な更新管理方法はというと、

契約を締結したら、契約更新エクセルの対象月のシートに契約者情報を入力しておく。
毎年年初に契約内容を見返して、更新年月別に更新者リストを毎年作成する。
年度別更新ファイルを作成して、紙ベースで更新情報を管理する。

といったような形で、管理戸数が少ないうちはまだ大丈夫そうだけど、「管理物件が増えてきたとき」や「新しく人を増やすことになったとき」などに苦労しそうだと感じられている会社様も少なくありません。

今日は、「エクセル（Excel）で契約更新表を作成する方法」について、解説していきます。
この解説を見ながら、エクセルシートを作成していけば、契約者を追加しておくだけで更新案内が必要な契約者の特定までは可能となっています。

エクセルで更新一覧表を作成していこう！

では、エクセル表の作成方法を解説していきます。更新管理表の作成手順は以下のとおりです。

契約書を見ながら更新管理表の大枠を作成する。
更新判定式を作成する。
ソートをかけて、更新対象者を絞り込む。

1.まずは、更新管理表の大枠から作成していきましょう。

まずは、各物件の契約者情報をエクセルの各セルに入力していきます。この辺りは、それぞれの契約条件を契約書や重要事項説明書を見ながら作成していきます。
まずは、以下の表を見ながら「A〜H」までの対応するセルに項目を入力していきます。

A列：物件名	対象のマンションやアパートの物件名を入力。	B列：部屋番号	対象の部屋番号を入力。
C列：契約者名	入居中の契約者名を入力。	D列：連絡先	契約者の電話番号を入力。
E列：契約終了日	契約期間の終了日を入力。	F列：解約予告	解約予告期間を入力。
G列：更新料	請求する更新料を入力。	H列：更新事務手数料	更新料とは別に更新事務手数料を請求している場合は入力。
I列：更新案内猶予	どれくらいの余裕を持って更新案内を行うか。	J列：更新判定	「契約終了日」と「解約予告」「更新案内猶予」を比較して、更新判定を行う。（関数の式は後述） ◯：更新案内対象者

※「列」：エクセルの縦の部分。
※「行」：エクセルの横の部分。

契約条件を入力すると、以下のような契約者一覧ができます。

2.次は、更新判定式を作成していきます。

次は、I列に「更新案内猶予」を入力していきます。
契約期間が終了する直前に更新案内を行っても入居者さんもすぐには対応できなかったり、解約したかったけれども、解約予告期間を入れると契約が終了してしまうなどの場合、無用なトラブルとなってしまう可能性があります。
そのため、契約期間終了日から解約予告期間とプラスアルファの余裕を持って更新対応を行う事が望ましいです。
では、I列のセルに入力してみましょう。

では、ここからが数式の登場です。
このエクセルを開いている日付（例えば2016年3月15日）に、更新対応をしておかないといけない入居者には「更新判定」が【◯】が入るように関数を作成していきます。

関数の考え方は非常にシンプルなものです。
「契約期間終了日　-　解約予告期間　-　更新案内猶予」 = 「更新案内を実施する日」
となるようにエクセルの式を作っていきます。

ここで、エクセルのめんどくさい部分が出てきます。
単純に「E2（契約終了日） - F2（解約予告） - I2（更新案内猶予）」としても、求める日付は出てきません。
そのため、ここでは日付計算を行う関数の一つである、「EDATE」というエクセル関数を使わなければなりません。

まずは、「J2」のセルに「更新案内を実施する日」を計算してみましょう。条件式は以下の通りです。
=EDATE(E2, -(F2+I2)) ・・・①
これで各契約者の「更新案内を実施する日」が計算されます。

次は、①で計算した日付が今日（エクセルを開いている日付）と比較した時に、過去（もしくは当日）の日付であれば【◯】とするように関数を組んでいきます。
条件式は以下の通りです。
=IF(TODAY() >= EDATE(E2, -(F2+I2)), "◯", "")
これで、更新案内を実施する日に到達している場合は「J列」に【◯】がつくようになりました。

エクセルの式を見るのが苦手な方へ

最終的にJ2のセルに入るエクセルの条件式は
=IF(TODAY() >= EDATE(E2, -(F2+I2)), "◯", "")
となります。こちらをコピー&ペーストすれば、エクセルの式は動きますのでお試しください。

3.あとは、ソートで絞り込みをかければ更新対応が必要な契約者の特定は完了です。

これくらいの数であれば、ソートをかけなくても目視でみても十分確認できるかと思います。
しかし、これが100人、200人となった場合は確認漏れなどが起こる可能性あるため、ソートで絞り込む方法も覚えておきましょう。
【◯】のみで絞り込みをかければエクセルが勝手に更新対象者を絞り込んでくれます。

まとめ

これで、「エクセル（Excel）での更新管理表の作成方法」は終了です。
このサンプルで作った更新管理表では更新対応が終わった後も、シート上に残り続けるので、対応済みとなった場合は別シートに移し替えるなどの作業を行えば可能です。

もちろん、もう少し複雑な関数を組めば「更新済みの場合は、【※】マークをつける」「契約期間を過ぎたら背景を灰色に、更新対応期間中の場合は背景色をオレンジに」といったこともエクセルであれば可能です。
エクセルを利用するにあたっての注意点とすれば「データや関数が複雑になったり、増えたりすると動作が重くなる」という特徴があります。
うまく、管理状況にあった形や拡張性を考えた形で関数を設計していくことが必要であると言えます。

最後に、少し宣伝になりますが、上記の更新対象者の特定や更新合意書の自動作などはエクセルを使わなくても、弊社のクラウド型賃貸管理ソフトReDocS（リドックス）で対応可能な機能です。
「これから賃貸管理を初めて始める」「本格的なソフトを導入するのは初めて」といったお客様にも「シンプルで使いやすい」などの声を頂いておりますので、「エクセルだと対応しきれなくなってきた」「エクセルがそもそも苦手」という方は、一度、リドックスを試してみてください。

次回は、「契約更新対応マニュアルについて」について解説していきたいと思っています。

文： Bamboooby株式会社代表取締役高田圭佑