エクセルでサブスクタイプの契約管理！契約期間を塗りつぶして可視化したり、月次契約金額の集計で予実管理！

エクセルで月額課金やメンテナンス期間の収益を可視化したいと思いませんか？いつくかの関数を組み合わせると簡単に以下のような表を簡単に作ることができます。

これができれば以下のようなメリットがあります。

今月の確定している売上はいくつ？

ここ三か月内に契約が終了しそうで、営業さんに連絡してもらうべき案件はどれ？

来月からの確定売上が足りないので、新規契約が必要だ！

是非、エクセルを使いこなして、経営にデジタル化の恩恵を感じてください！

準備：表の作成

毎月の表の作成はEDATE関数を使って隣の日付から一か月後の日付を表示していきます。

=EDATE(I11,1)

EDATE関数の詳細は以下のサイトになります。

EDATE 関数 - Microsoft サポート

開始日から起算して、指定された月数だけ前または後の日付に対応するシリアル値を返します。 この関数を使用すると、伝票の発行日と同じ日に当たる支払日や満期日の日付を計算することができます。 開始日に無効な日付を指定すると、エラー値 #VALUE! が返されます。

support.microsoft.com

期間内のセルに値を入力

サブスクリプションやメンテナンス期間の日付を指定して、その期間内に特定の数字を入力することが可能です。

if関数で、条件を満たせば値を入力し、満たさない場合は空白とします。
その条件の中に上段で設定した日付と指定する日付を比較する式を入れることで期間内の場合は数値が入力されるようにします。

=IF(AND(J$11>=$B13,J$11<=$C13),ROUND($G13/$H13,0),"")

セルの塗りつぶし

値の入力と同じ条件でセルの塗りつぶしの適応します。

プルダウンメニューの「ホーム」＞「新しいルール」（もしくは「ルールの管理」）を選択します。

新規の場合は「新規ルール」、修正の場合は「ルールの編集」を選択します。

「数式を使用して、書式設定するセルを決定」を選択し、先ほどと同様の数式を入力します。

対象の日付がサブスクリプションやメンテナンスの開始日よりも大きいか、小さいかを評価する式となります。後は、塗りつぶす色などしてするとその期間だけ塗りつぶされるようになります。

契約期間の月数を計算

契約期間の月数はDATEDIF関数で計算します。

=DATEDIF(B12,C12,"M")+1

DATEDIF 関数についてはこちらが参考になります。

DATEDIF 関数 - Microsoft サポート

2 つの日付間の日数、月数、年数を計算します。

support.microsoft.com

今回は月数になりますので、”M”でのカウントになります。

グラフの表示

各月の売上などの集計してグラフにすることも可能です。

ここはSUM関数で集計可能です。

=SUM(I12:I27)

グラフについてはX軸の指定に日付、Y軸の指定に集計値を使う事ができます。

進捗管理、新規案件獲得、パイプライン（案件管理）などいろんなところで、役に立つと思います。

是非、参考にして活用してみてください。

—

課題解決関係に関する記事をご紹介します。