2026/5/22
HARUKA
ユーザーの定着率を可視化する「コーホート図」の作り方です。まずサンプルデータでExcelの下ごしらえを体験して、最後の色塗りだけツールに任せてしまいましょう。
このページについて
コーホート分析とは、同じ時期に登録したユーザーのグループ（コーホート）を追跡し、「翌月も使い続けているか」を月ごとに確認する分析手法です。継続率・解約率の把握や、施策の効果検証に広く使われます。
このページはExcelでの下処理から始まるステップバイステップ形式になっています。ツールだけを使いたい方は STEP 3 へスキップ できます。
免責事項・動作確認環境
このページはブラウザ上でデータを処理します。入力データはサーバーに送信されません。出力結果の正確性には最善を期していますが、利用に際して生じた損害について当サイトは責任を負いません。動作確認環境：Chrome 最新版 / Edge 最新版 / Firefox 最新版。
サンプルデータの準備
下のボタンで架空のWebサービスのログイン履歴データをクリップボードにコピーし、Excelの新しいシートに貼り付けてください。A1セルを選んでから「Ctrl＋V」で貼り付けると、3列（ユーザーID・登録日・アクセス日）が整った状態で入ります。
データの内容について
このデータは架空のSaaSサービスのログイン履歴を模した創作データです。ユーザー155名・レコード約460件で構成されています。登録月は2025年10月〜2026年1月の4コーホートで、意図的に「12月登録コーホートが途中で回復する」という仕込みをしています。STEP 3で何が起きるか……それはお楽しみ。
列の説明：
ユーザーID（例: U-001）— 個人の識別子
登録日（例: 2025-10-03）— サービスへの初回登録日
アクセス日（例: 2025-11-15）— ログインした日（同じユーザーが複数行あります）
お使いのデータが「登録日」列のない形式の場合
システムによっては、登録日が独立した列として存在せず、下のように action_type（イベント種別） 列で区別される形式で出力されることがあります。
user_id action_date action_type
U_001 2026-01-05 signup ← この行が登録日
U_001 2026-02-12 login ← この行がアクセス日
この場合は STEP 1 の前にひと手間必要です。signup 行の action_date を「登録日」として別列に引き出す処理を行ってください。XLOOKUP関数を使う方法が簡単です。
=XLOOKUP(A2, signup行のuser_id列, signup行のaction_date列)
登録日列が用意できたら、あとはこのページのSTEP 1〜2と同じ手順で進められます。なお、ピボットテーブルを集計するときは action_type = login の行だけを対象にしてください（signup行まで含めると M0 の人数が二重にカウントされます）。
M0（登録月）に何をカウントするかについて
M0は「登録月」を意味しますが、何をもって登録とするかはサービスの定義によります。たとえば以下のような選択肢があります。
- アカウント登録日（signup日）
- 初回ログイン日
- 初回課金日・初回購入日
- プロフィール設定完了日
どれを選ぶかによってM0の人数が変わり、残存率の分母も変わります。分析の目的に合わせて事前に定義しておいてください。ジェネレーターはExcelで準備した数字をそのまま使うため、この定義はユーザー側で行います。
同じユーザーが複数のアクセス日を持っている「縦長のトランザクション形式」になっています。このままではピボットテーブルに使えないから、次のSTEPで下ごしらえをするんですよ。
Excelで経過月数を算出する
「登録年月」列を追加する（D列）
A列にユーザーID、B列に登録日、C列にアクセス日が入った状態を確認してから、D1セルに列ヘッダーとして「登録年月」と入力します。D2セルに下の数式を入力してください。
=TEXT(B2,"yyyy-mm")
これで「2025-10」のような文字列になります。ピボットテーブルの行ラベルとして使うための列です。入力したらD2セルを選択し、データの最終行までオートフィルしてください。
なぜ日付型ではなく文字列にするのか
登録日（B列）をそのままピボットの行に使うと、Excelが自動で「年→月→日」の3段階にグループ化しようとするため操作が煩雑になります。TEXT関数で「yyyy-mm」形式の文字列にしておくと、ピボットがそのままラベルとして扱ってくれるので設定が単純になります。
「経過月数」列を追加する（E列）
E1セルに「経過月数」と入力し、E2セルに下の数式を入力します。
数式の切替
標準的な関数のみで書く方法と、DATEDIFを使う方法を切り替えられます。
=(YEAR(C2)-YEAR(B2))*12+(MONTH(C2)-MONTH(B2))
登録日（B2）からアクセス日（C2）まで、何ヶ月経過したかを計算します。「年の差×12」に「月の差」を足すことで経過月数が出ます。登録月そのものは 0 になります。
=DATEDIF(B2,C2,"M")
DATEDIFは第1引数に古い日付、第2引数に新しい日付を指定します。引数の順番に注意してください（逆にするとエラーになります）。結果は標準関数版と同一です。
HARUKA
DATEDIFはExcelの公式ヘルプに載っていない「非公式」関数です。動きはするけれど、引数を逆に入れてもエラーにならず変な値が出ることがあるかも。……私は、標準関数版がいいかな。
数式を入力したら、E2セルを選択してデータの最終行までオートフィルします。E列の値が 0, 1, 2, 3… の整数になっていれば成功です。
テーブル化しておくと便利
データをExcelの「テーブル」（Ctrl＋T）に変換しておくと、新しい行を追加したときにD・E列の数式が自動で伸びます。列ヘッダーが自動でピボットのフィールド名になるため、次のSTEPの操作もスムーズになります。
ピボットテーブルで集計する
ピボットテーブルを挿入する
データ範囲内のセルをひとつクリックしてから、「挿入」タブ → ピボットテーブル → 新規ワークシート を選択してOKします。
フィールドを配置する
ピボットテーブルのフィールドリストを下のように設定してください。
| エリア | 設定するフィールド |
|---|---|
| 行 | 登録年月 |
| 列 | 経過月数 |
| 値 | ユーザーID（個数） |
値エリアに「ユーザーID」を入れると「データの個数」になるはず。もし「合計」になっていたら、値フィールドの設定を「データの個数」に変更してください。
完成形と整形のポイント
正しく設定できると、下のような「階段状の表」ができあがります。左端の列が登録年月（コーホート）、上端の行が経過月数（M0, M1, M2…）です。
| 登録年月 | 0 | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|---|
| 2025-10 | 40 | 22 | 14 | 11 | 9 | 8 |
| 2025-11 | 40 | 22 | 14 | 7 | 5 | — |
| 2025-12 | 40 | 17 | 12 | 23 | — | — |
| 2026-01 | 35 | 20 | 13 | — | — | — |
貼り付け前に以下の2点を整形してください。
- ピボットの「総計」行・列は削除する（または非表示にする）
- 値のない右下のセルは空白のままでOK（ジェネレーターが「未観測」として扱います）
「(空白)」という列が出たときは
ピボットに「(空白)」という列が出る場合、E列（経過月数）に空白セルが混じっています。データをフィルターして空白行を確認・削除してから、ピボットを「更新」してください。
AYAKO
この表ができたら、下のジェネレーターにコピペするだけ！ 苦労して加工したデータがヒートマップに変わる瞬間、ちょっと感動しませんか？
ジェネレーターで可視化・診断
貼り付けフォーマットについて
Excelのピボットテーブルをそのままコピー（Ctrl＋C）して貼り付けてください。タブ区切りのテキストであれば認識します。
（空白） 0 1 2 3 4 5
2025-10 40 22 14 11 9 8
2025-11 40 22 14 7 5
2025-12 40 17 12 23
2026-01 35 20 13
1行目はヘッダー行（左上セルは空白か任意の文字、以降が経過月数の数値）です。空白セルは未観測として扱います。
