読者です 読者をやめる 読者になる 読者になる

雪ん子パースペクティヴ

読むとちょっとタメになるエントリー。

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

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

 

はろー、yukiです。

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

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

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

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

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


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


完成図はこちら

f:id:yuki_sasano:20160611174658j:plain

 

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


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

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

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

列:[No]、[名前]、[累計]、[開始日]、[終了日]、[1..15]([更新月数])


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

f:id:yuki_sasano:20160611174710j:plain

罫線はご自由に。

 

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

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

 

[更新月数]の[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"

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

 

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

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

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

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

 

 

いかがでしたか。

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

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

 

 

以上。