日付入力で契約更新月数と契約更新率を割り出す【Excel】

サブスクリプション・コマースってご存知？

はろー、yukiです。

今回は、Excelを用いて契約更新表を作成します。

目的は次のようになります。

-------------------------------------------------------

1. 顧客ごとに契約更新した月数を把握
2. 更新月数あたりの更新率を算出
3. 日付入力だけ

-------------------------------------------------------

1は一般的な要件だと思います。
2は、契約更新のストップは何ヶ月目に多いか、を知ることができますね。
3は楽をしたいから（更新された月数を地道に数えて足し算しても良いんですよ）。

完成図はこちら

横に長いですね。え、画像が小さいですか（すみません）。

では、順番にみていきましょう。

まずは、骨組みを用意。必要なのは下記。

行：[合計]、[更新率]

列：[No]、[名前]、[累計]、[開始日]、[終了日]、[1..15]（[更新月数]）

ここでは、[No]と[名前]は[0001]の[a]というようにしています。
※[0004]の[d]の[終了日]には秘密がありますので、骨組みの段階では空欄で結構です。

罫線はご自由に。

次に、セルに関数を書いていきます。

今回重要なのは、[更新月数]のセル。
ここを基に他のセルの値が決まっていきます。

[更新月数]の[1]月目に該当するセル（画像のF3）に下記を入力。

=IF(OR(F$2<=$C3,F$2=$C3),"1","")

訳すと次のようになります。
IF関数で分岐。
・セルC3の値がF2以上であれば、"1"を入力。
・セルC3の値がF2と同じであれば、""（空白）を入力。

F$2は2列目を固定しています。次に続く[3、4、5…x]月目のためです。
$C3はC行を固定しています。C行には各[名前]ごとに[累計]の値が入るためですね。

その[累計]に入る関数がこちら。

=DATEDIF(D3,E3,"M")

訳すと次のようになります。
DATEDIF関数で差分算出（引き算みたいなもの）。
D3には[開始日]、E3には[終了日]が入っています。
"M"は、差分で算出する値を月単位に指定しています。

例では、[2014/4/1]から始まり、[2015/5/23]で終わっています。
この間、月数は「13」ですね（2014/5は更新1月目です）。
ちなみに、日数は"M"を"D"にする（例の場合「417」日）。
年数は"Y"と推測できますね（例の場合は「1」年）。

おっと、下（C3からC7）にオートフィル（コピー）はしてくださいね。

ここまできたら、もう終わったも同然です。

セルC11（[累計]の[合計]）には次が入ります。

=SUM(C3:C7)

訳すと次のようになります。
SUM関数で、[累計]にある数値を足し算。

※顧客ごとの契約月数を合算して総数を算出するわけ。

また、[更新月数]にある[合計]（セルF11から右へ）にはこちら。

=COUNTIF(F3:F7, "1")

訳すと次のようになります。
COUNTIF関数で、[更新月数]の[x]月目の中で入力された「1」という数値をカウント。

完成図では、[0003]の[c]以外が1月目を更新しているため、[1]月目の[合計]は「4」が算出されています。

[更新率]は単純。

=F11/$C$11

訳すと次のようになります。
[1]月目の場合、[合計]はセルF11でした。
その値をセルC11（[累計]の[合計]）で割っています。

※契約月数の総数から、各月数あたりを割り出しているわけ。

ちなみに、セルE6には次の関数が入っています（空欄にしておいたセルです）。

=TODAY()

TODAY関数により、Excelファイルを開いた日時が同セルに入力されます。
つまり、[開始月]から「今日」までの[更新月数]が分かるというわけですね。

さて、関数は以上になります。

最後は、表示の整理です。

まず、[更新率]を「パーセンテージ」に変更（切捨てはご自由に）。
次に、[開始日]と[終了日]を「日付」に変更（表記はご自由に）。

そして、エラー処理と色づけは条件付き書式で設定します。
・エラー処理
手順は次の通り。

条件付き書式 > 新しいルール > 指定の値を含むセルだけを書式設定

エラー

書式では、「フォント」を"白色"にします。

※ここで、骨組みの状態にあった[0003]のエラーが消えたはず。

・[x]月目の「1」を見えなくし、セルを塗りつぶし。
手順は次の通り。

条件付き書式 > 新しいルール > 指定の値を含むセルだけを書式設定

セルの値 - 次の値に等しい - ="1"

書式では、「フォント」と「塗りつぶし」を同色にします。

・[開始日と[終了日]が入力されている場合にセルを塗りつぶし
手順は次の通り。

条件付き書式 > 新しいルール > 指定の値を含むセルだけを書式設定

セルの値 - 次の値に等しくない - =""

書式の「塗りつぶし」で任意の色に（僕はパール系が好き）。

いかがでしたか。

手順通りにやれば、完成図のようになるはずです。

ご質問・補足などがあれば、コメント欄にお願いします。

以上。