日付入力で契約更新月数と契約更新率を割り出す【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"
書式では、「フォント」と「塗りつぶし」を同色にします。
・[開始日と[終了日]が入力されている場合にセルを塗りつぶし
手順は次の通り。
条件付き書式 > 新しいルール > 指定の値を含むセルだけを書式設定
セルの値 - 次の値に等しくない - =""
書式の「塗りつぶし」で任意の色に(僕はパール系が好き)。
いかがでしたか。
手順通りにやれば、完成図のようになるはずです。
ご質問・補足などがあれば、コメント欄にお願いします。
以上。