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

スポンサーリンク
SEO関係・課題解決

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

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

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

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

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

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

準備:表の作成

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

=EDATE(I11,1)

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

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

期間内のセルに値を入力

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

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

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

セルの塗りつぶし

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

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

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

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

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

契約期間の月数を計算

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

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

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

DATEDIF 関数 - Microsoft サポート
2 つの日付間の日数、月数、年数を計算します。

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

グラフの表示

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

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

=SUM(I12:I27)

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

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

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

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

エクセルでサブスクタイプの契約管理!契約期間を塗りつぶして可視化したり、月次契約金額の集計で予実管理!
自動化・効率化できます!金融向けGoogle スプレッドシートのすぐ使える簡単な使い方4選!(Google Finance,セントルイス連銀,アメリカ合衆国財務省,スクレイピング)【コピペで動く!】
無料・新規費用をかけずにマインクラフトのゲーム動画投稿する方法、GPUをマインクラフトに割り当てて負荷分散の方法を紹介!(Windows10・マイクラ)
自動化・効率化でなにができるのか!Google FinanceやYahoo Financeからデータ取得して年初来パフォーマンスや週次騰落率のファクターチェック
ConoHa WINGレンタルサーバーでPython! Webアプリcgiを動かす手順を紹介!3つの例あり【コピペで動く!】
ネット上にあるPDFをChatGPT(OpenAI)で英訳したり和訳するウェブアプリをBlogに埋め込んでみた!
受注確度の管理をPythonで行いたい!成約確度と見込み金額の掛け合わせ
Pythonを使ってTwitterに自動投稿。APIの設定など。(Twitter API v2とAPI v1.1の併用)
対話型チャットAI「ChatGPT」が人気なので、アカウントの作成とか難しいことが分からない人向けにとりあえずBlogに埋め込んでみた!
OpenAIのWhisperを使ってYouTube動画から文字起こしをしよう!モデルの精度を確認!StreamlitでWebアプリ化!
Google Search ConsoleとPythonの連携方法【コピペで動く!】
【違いをみつけろ!】Googleトレンドで株式市場のセンチメント調査 ”VIX”で先行きは予想できるのか?! 
IB証券(インタラクティブ・ブローカーズ証券)のAPIを利用して個別株、ETF、先物のヒストリカルデータの出力できるJupyter notebookファイルの提供
IB証券(インタラクティブ・ブローカーズ証券)のAPIを利用してVIXオプションの各限月の特定の権利行使価格で、CALL,PUTで日足ベース引け値のヒストリカルデータ出力するJupyter notebookファイルの提供やメンテナンス・補修サービス、Windows10での環境構築をさせていただきました。
PCで楽天ECなどのスマホサイトの作業効率アップ!商品・アイテムのスマートフォンリンクを表示できます!

コメント

タイトルとURLをコピーしました