RFM分析をExcelで行う手順｜注文データの集計と計算例

RFM分析は、最終購入からの経過日数（R）、購入回数（F）、購入金額（M）から顧客の購入傾向を整理する方法です。Excelでは、注文データを1注文1行に整え、同じ期間で顧客別に集計してから分類します。

この記事では架空の6注文を使い、関数で作った結果と元データを照合し、無料ツールへ渡す4列まで作ります。CRMやECから取り出した商品別の明細を、そのまま貼り付けて購入回数を誤るケースを防ぐ手順です。

最初に決めるのはスコアより集計ルール

IBMのRFM集計の説明でも、顧客IDで取引をまとめ、最新の取引日・取引回数・合計金額を顧客ごとの1行にします。計算前に、次の条件を固定します。

返品の処理方法も記録します。たとえば「キャンセル・全返品の注文を除外し、一部返品は注文金額から差し引く」と決めたら、回数・日付・金額すべてにその条件を適用します。これは本記事の集計方針の例で、すべての業務に共通する会計ルールではありません。返品を返品日の別行で管理しているデータは、注文IDとの対応を整理してから使います。

商品明細を注文IDだけで重複削除すると、金額まで消してしまいます。商品ごとの金額なら注文単位で合計し、各商品行に注文総額が繰り返されているなら総額を1回だけ採用します。どちらの形式かを出力元で確認してください。

架空の6注文をExcelに置く

以下は実在の顧客を含まない説明用データです。「注文」シートのA1:D7に入力します。日付はExcelの日付値、金額は通貨記号や「円」を付けない数値にします。顧客ID・注文IDは文字列として扱います。

この時点で6行＝6注文、購入金額の合計は110,000円です。注文IDの重複、顧客IDの空欄、文字列のままの日付、金額列の空欄があれば、先に修正します。

別の「集計」シートでH1に「開始日」、I1に「基準日」と書き、H2に 2026/01/01、I2に 2026/09/25 を日付値として入力します。A1:E1は次の見出しにします。

A2:A4にC001、C002、C003を入力します。実データでは顧客ID列のコピーから重複を除きます。元の注文データ自体を顧客IDで重複削除しないでください。

顧客別のR・F・Mを計算する関数は？

以下はExcel 2019以降またはMicrosoft 365を想定します。例では6注文なので参照範囲を2〜7行にしています。実データでは全式の終端行を同じ行まで伸ばしてください。顧客IDは例のような英数字とし、条件式で特別な意味を持つ *・?・~ を含めません。

1. 購入回数を数える。集計シートのC2に次の式を入れます。

=COUNTIFS(注文!$B$2:$B$7,A2,注文!$A$2:$A$7,">="&$H$2,注文!$A$2:$A$7,"<="&$I$2)

顧客ID・開始日・基準日の3条件をすべて満たす注文を数えます。COUNTIFSの公式説明では、各条件範囲の行数・列数を揃える必要があります。1注文1行なので、この件数がFになります。

2. 最終購入日を求める。B2に次の式を入れ、表示形式を日付にします。

=IF(C2=0,"",MAXIFS(注文!$A$2:$A$7,注文!$B$2:$B$7,A2,注文!$A$2:$A$7,">="&$H$2,注文!$A$2:$A$7,"<="&$I$2))

MAXIFSは条件を満たす値の最大値を返します。日付の最大値が最新の購入日です。期間内の注文が0件なら空欄にし、日付として扱わないようにします。

3. 購入金額を合計する。D2に次の式を入れます。

=SUMIFS(注文!$D$2:$D$7,注文!$B$2:$B$7,A2,注文!$A$2:$A$7,">="&$H$2,注文!$A$2:$A$7,"<="&$I$2)

SUMIFSで同じ3条件の金額を合計します。これがMです。Fだけ直近1年、Mだけ生涯累計といった混在を避けます。

4. Rの日数を計算する。E2に =IF(C2=0,"",$I$2-B2) と入力し、表示形式を数値・小数点以下0桁にします。B2:E2を4行目までコピーします。

この例は日付のみのデータを前提にしています。購入日時が入っている場合は、購入日の列を別に作って =INT(元の日時セル) で日付に揃えてから使います。文字列の日時は、先にExcelの日付値へ変換します。

集計結果が正しいか、どこを照合する？

計算結果は次のとおりです。Rは2026年9月25日から最終購入日を引いた日数で、小さいほど最近購入しています。

確認する順序は、①購入回数の合計が対象注文数と一致する、②購入金額の合計が対象注文の合計金額と一致する、③顧客を1人選び、最新日と回数・金額を元の注文で確認する、です。この例のC001なら12,000＋18,000＋20,000＝50,000円、3回です。

期間外の注文を含む実データでは、元データ全体ではなく同じ期間・除外条件の注文と照合します。合計が合っていても、別顧客への付け替えは見つからないため、顧客単位の確認も必要です。

4列を無料ツールへ渡して分類する

集計が合ったら、Rの日数列ではなく、顧客ID・最終購入日・購入回数・購入金額の4列を使います。最終購入日の表示形式を yyyy-mm-dd、金額を通貨記号・桁区切りなしにし、A1:D4をコピーします。合計行や購入回数0件の顧客は含めません。

下のデータはタブ区切りです。そのままRFM分析ツールの入力欄に貼り付けても、同じ例を試せます。

顧客ID	最終購入日	購入回数	購入金額
C001	2026-09-20	3	50000
C002	2026-09-05	2	15000
C003	2026-08-20	1	45000

1. ツールの集計開始日を 2026-01-01、基準日を 2026-09-25 にする。
2. 上記の4列を貼り付け、初期の分類基準で実行する。
3. C001はR5・F2・M2、C002はR5・F2・M1、C003はR4・F1・M2となることを確認する。
4. 分布と顧客一覧を見て、必要な対象をCSVで保存する。

初期設定では、Rは30日以内が5、90日以内が4。Fは2回以上5回未満が2、1回が1。Mは30,000円以上100,000円未満が2、それ未満が1です。これはツールの仮の開始値であり、自社の優良顧客を保証する基準ではありません。購入周期・単価が異なる商品群を一緒に扱うかも確認します。

よくある失敗と、分類後に確認すること

RFMで分かるのは、定めた期間の購入傾向です。LTVの将来予測や、チャーンレートによる解約の実績集計とは分けて使います。Rが大きくても、購入周期が長い商品では自然な場合があります。

分類後は対象顧客を数件見て、何を案内すると役立つかを決めます。カスタマーサクセスの接点記録や問い合わせ内容も合わせれば、単に購入回数が少ない顧客と、利用に困っている顧客を区別して検討できます。毎月更新するなら、対象期間・除外条件・分類の境界を同じ記録に残し、基準を変えた月を明示してください。