Excelで解約率と継続率を集計する方法

解約率・継続率とは？まず押さえるべき基本の考え方

Excelで解約率や継続率を集計する前に、まずはそれぞれの意味をしっかり押さえておきましょう。特にSaaS、サブスク型サービス、会員制ビジネス、定期購入サービスなどでは、売上を伸ばすうえで「新規顧客をどれだけ獲得したか」だけでなく、「既存顧客がどれだけ残っているか」が非常に重要です。

解約率とは、一定期間内にサービスを解約した顧客の割合を表す指標です。英語では「Churn Rate（チャーンレート）」とも呼ばれます。たとえば、月初に100人の契約者がいて、その月に5人が解約した場合、解約率は5％です。

基本的な計算式は、以下のようになります。

解約率 ＝ 解約者数 ÷ 期間開始時点の契約者数

一方で、継続率とは、一定期間後もサービスを利用し続けている顧客の割合を表す指標です。先ほどの例でいうと、月初100人のうち95人が継続していれば、継続率は95％になります。

基本的な計算式は、以下のとおりです。

継続率 ＝ 継続者数 ÷ 期間開始時点の契約者数

また、シンプルに考えると、解約率と継続率は表裏一体の関係です。新規契約を考慮しない場合、次のように計算できます。

継続率 ＝ 1 − 解約率

たとえば解約率が8％であれば、継続率は92％です。Excelで集計する際も、この関係を理解しておくと計算ミスに気づきやすくなります。

ここで注意したいのは、解約率を計算するときの「母数」です。よくあるミスが、期間中に増えた新規顧客も含めて計算してしまうことです。一般的には、解約率の母数には「期間開始時点の契約者数」を使います。なぜなら、その期間に解約する可能性があった人を基準にする必要があるからです。

たとえば、月初の契約者が100人、月中に新規契約が30人、解約者が10人だった場合、解約率は「10 ÷ 130」ではなく、「10 ÷ 100」で計算するのが基本です。この場合の解約率は10％です。

解約率が高い場合、サービス内容、料金、サポート体制、オンボーディングなどに課題がある可能性があります。逆に継続率が高ければ、顧客満足度が高く、安定した売上につながりやすい状態といえます。

つまり、解約率と継続率は単なる数字ではなく、ビジネスの健康状態を示す重要なサインです。Excelで正しく集計できるようになれば、毎月の顧客動向を把握し、改善施策を考えるための強力な材料になります。

集計前に準備するExcelデータの作り方

解約率・継続率を正しく計算するには、Excel関数よりも先に元データの作り方が重要です。データの持ち方がバラバラだと、あとからCOUNTIFS関数やピボットテーブルで集計しようとしても、思ったように計算できません。

まず用意したいのは、顧客ごとの契約状況を一覧で管理する表です。1行につき1顧客、または1契約として管理すると、後の集計がしやすくなります。

顧客ID	契約開始日	解約日	ステータス	プラン	月額料金
C001	2024/01/10		継続中	スタンダード	3000
C002	2024/02/01	2024/05/20	解約済み	ライト	1000

最低限必要なのは、顧客ID・契約開始日・解約日の3つです。顧客IDは、同姓同名や表記ゆれを避けるために必ず用意しましょう。名前だけで管理すると、「山田太郎」「山田　太郎」のような違いで別人としてカウントされることがあります。

契約開始日と解約日は、必ずExcelが日付として認識できる形式で入力します。「2024年5月」「5/20頃」のような文字列が混ざると、関数で期間判定ができなくなるため注意が必要です。おすすめは「2024/05/20」のような形式に統一することです。

解約していない顧客については、解約日を空欄にしておきます。無理に「なし」「継続中」などの文字を入れると、日付として扱えなくなります。継続中かどうかを見やすくしたい場合は、別途「ステータス」列を作り、そこに「継続中」「解約済み」と入力しましょう。

また、月別に解約率や継続率を出したい場合は、集計対象となる期間を決めておくことも大切です。たとえば「2024年5月の解約率」を出すなら、5月1日時点で契約中だった顧客数と、5月中に解約した顧客数を集計できる状態にしておく必要があります。

そのため、毎月の集計用に以下のような表も別シートで作っておくと便利です。

集計月	月初契約者数	解約者数	継続者数	解約率	継続率
2024/05/01

ここでのポイントは、元データと集計表を分けることです。元データには顧客ごとの情報を蓄積し、集計表では月ごとの数値を計算する形にすると、データが増えても管理しやすくなります。

さらに、Excelでは表の途中に空白行を入れたり、セルを結合したりしないようにしましょう。見た目を整えるためにセル結合を使うと、関数やフィルター、ピボットテーブルで扱いづらくなります。集計用データは「見た目のきれいさ」よりも「機械的に処理しやすい形」を優先するのがコツです。

このように、顧客ごとの契約開始日と解約日を整理しておけば、次のステップでExcel関数を使って解約率をスムーズに計算できるようになります。

Excel関数で解約率を計算する方法

ここからは、実際にExcel関数を使って解約率を計算していきます。前章で作成したように、元データには「契約開始日」と「解約日」が入っている前提で進めます。

今回は例として、元データを「顧客一覧」シート、月別の集計表を「集計表」シートに作っているとします。

シート名	列	内容
顧客一覧	B列	契約開始日
顧客一覧	C列	解約日
集計表	A列	集計月
集計表	B列	月初契約者数
集計表	C列	解約者数
集計表	E列	解約率

たとえば、集計表のA2セルに「2024/05/01」と入力されている場合、まずは5月1日時点で契約していた顧客数を出します。B2セルに以下の数式を入力します。

=COUNTIFS(顧客一覧!B:B,"<="&A2)-COUNTIFS(顧客一覧!C:C,"<"&A2,顧客一覧!C:C,"<>")

前半のCOUNTIFS(顧客一覧!B:B,"<="&A2)では、集計月の月初までに契約を開始している顧客を数えています。後半では、月初より前にすでに解約している顧客を差し引いています。

つまりこの式では、月初時点でまだ契約中だった顧客数を計算していることになります。解約率の母数になる重要な数値です。

次に、5月中に解約した人数をC2セルに計算します。

=COUNTIFS(顧客一覧!B:B,"<="&A2,顧客一覧!C:C,">="&A2,顧客一覧!C:C,"<"&EDATE(A2,1))

この数式では、契約開始日が月初以前で、かつ解約日が「5月1日以上、6月1日未満」の顧客を数えています。ポイントは、月末日を直接指定するのではなく、EDATE(A2,1)を使って翌月1日未満で判定している点です。

この書き方にしておくと、2月のように日数が少ない月や、30日・31日が混在する月でも、数式を変更せずに使い回せます。

月初契約者数と解約者数が出せたら、最後に解約率を計算します。E2セルに以下の数式を入力しましょう。

=IFERROR(C2/B2,0)

解約率は「解約者数 ÷ 月初契約者数」で計算します。IFERRORを使っているのは、月初契約者数が0人だった場合にエラー表示を防ぐためです。

数式を入力したら、E列の表示形式を「パーセンテージ」に変更します。小数点以下1桁まで表示したい場合は、セルの書式設定から「0.0%」にすると見やすくなります。

この方法で計算すると、期間中の新規契約者を母数に含めず、月初時点の契約者を基準にした解約率を出せます。月別に集計したい場合は、A列に「2024/06/01」「2024/07/01」のように集計月を追加し、B列・C列・E列の数式を下方向にコピーすればOKです。

解約率は、計算式自体はシンプルですが、母数を間違えると数字の意味が大きく変わります。Excelでは、月初契約者数を正しく出すことが、正確な解約率集計のカギになります。

Excel関数で継続率を計算する方法

続いて、Excel関数を使って継続率を計算する方法を見ていきましょう。継続率は、一定期間の終了時点で、月初に契約していた顧客がどれだけ残っているかを表す指標です。

基本の考え方は、以下のとおりです。

継続率 ＝ 継続者数 ÷ 月初契約者数

前章と同じく、集計表のA2セルに「2024/05/01」が入っており、B2セルに月初契約者数、C2セルに解約者数が計算されている前提で進めます。集計表では、D列を「継続者数」、F列を「継続率」として使うとわかりやすいです。

列	内容
B列	月初契約者数
C列	解約者数
D列	継続者数
F列	継続率

もっともシンプルな方法は、月初契約者数から解約者数を引いて、継続者数を出す方法です。D2セルに以下の数式を入力します。

=B2-C2

たとえば、月初契約者数が100人、5月中の解約者数が8人であれば、継続者数は92人です。この場合、5月の継続率は92％になります。

次に、F2セルで継続率を計算します。

=IFERROR(D2/B2,0)

IFERRORを使う理由は、月初契約者数が0人だった場合に、割り算エラーを表示させないためです。数式を入れたら、F列の表示形式を「パーセンテージ」に変更しましょう。

また、解約率をすでにE列で計算している場合は、次のように計算することもできます。

=1-E2

解約率と継続率は、基本的に「合計すると100％」になる関係です。そのため、E2セルの解約率が8％なら、F2セルの継続率は92％になります。毎月の集計で手早く確認したい場合は、この方法でも問題ありません。

ただし、より厳密に「月末時点で継続している顧客」を数えたい場合は、解約日をもとに直接カウントする方法もあります。D2セルに以下のような数式を入力します。

=COUNTIFS(顧客一覧!B:B,"<="&A2,顧客一覧!C:C,"")+
COUNTIFS(顧客一覧!B:B,"<="&A2,顧客一覧!C:C,">="&EDATE(A2,1))

この式では、「月初以前に契約していて、解約日が空欄の顧客」と「月初以前に契約していて、翌月1日以降に解約した顧客」を合計しています。つまり、対象月の終了時点ではまだ継続していた顧客を数えているということです。

ポイントは、期間中に新しく契約した顧客を継続者数に含めないことです。継続率はあくまで、月初時点で契約していた顧客がどれだけ残ったかを見る指標です。新規契約者まで含めてしまうと、実態よりも継続率が高く見えてしまう可能性があります。

最後に、集計結果のチェックとして、解約率＋継続率＝100％になっているか確認しましょう。大きくズレている場合は、解約者数の集計条件や、解約日の入力形式にミスがあるかもしれません。

継続率を毎月追うことで、顧客がサービスにどれだけ定着しているかを把握できます。解約率とセットで確認すれば、売上の安定性や改善施策の効果も見えやすくなります。

グラフ化して解約・継続の傾向を見える化する方法

解約率と継続率をExcelで計算できたら、最後にグラフ化して傾向を見える化しましょう。数値の表だけでも確認はできますが、月ごとの変化はグラフにしたほうが圧倒的にわかりやすくなります。

たとえば、集計表が以下のようになっているとします。

列	内容
A列	集計月
E列	解約率
F列	継続率

まず、A列の集計月、E列の解約率、F列の継続率を選択します。離れた列を選択する場合は、Ctrlキーを押しながら範囲選択すると便利です。

範囲を選択したら、Excel上部のメニューから「挿入」→「折れ線グラフ」を選びます。解約率と継続率は月ごとの推移を見る指標なので、棒グラフよりも折れ線グラフのほうが変化を追いやすくなります。

グラフを作成したら、縦軸の表示形式をパーセンテージに変更しましょう。縦軸を右クリックして「軸の書式設定」を開き、表示形式を「パーセンテージ」にすると、10％や95％のように直感的に読めるグラフになります。

また、解約率と継続率を同じグラフに表示すると、継続率が90％台、解約率が数％台のように差が大きく、解約率の変化が見えづらくなることがあります。その場合は、解約率だけを別グラフにするのがおすすめです。

特に改善施策の効果を確認したい場合は、解約率の推移だけを折れ線グラフにすると、「キャンペーン後に解約率が下がった」「料金改定後に解約率が上がった」といった変化に気づきやすくなります。

さらに見やすくするなら、グラフタイトルも具体的に設定しましょう。たとえば「月別 解約率・継続率の推移」や「2024年 解約率の推移」のようにすると、あとから資料に貼り付けたときにも内容がすぐに伝わります。

色の使い方も重要です。解約率は赤系、継続率は青や緑系にすると、意味が直感的に伝わります。凡例も表示しておくと、上司やチームメンバーに共有する際に説明しやすくなります。

グラフを見るときは、単月の数字だけで判断しないことも大切です。たまたま大口顧客が1社解約しただけで、解約率が一時的に大きく上がることもあります。そのため、最低でも3か月から6か月程度の推移を見て、全体として上がっているのか、下がっているのかを確認しましょう。

Excelでグラフ化しておけば、毎月データを追加するだけで最新の傾向をすぐに確認できます。関数で集計し、グラフで可視化する流れを作っておくことで、解約や継続の変化に早く気づき、改善アクションにつなげやすくなります。