エクセルでできる売上分析を紹介！分析表の作り方まで解説

1.表の作成: エクセルに、以下の列を作成します。

• A列: 商品名
• B列: 売上高
• C列: 累積売上高　
• D列: 累積割合
• E列: ランク

2.データ入力: A列とB列に、分析したい商品名と売上高を入力します。

3.並べ替え: 「データ」タブの「並べ替えとフィルター」機能を使い、B列（売上高）を基準に、高い順に並べ替えます。

続いて、C列に商品ごとの累積売上高を計算しましょう。C2セルに「=SUM(B2,C1)」と入力し他のセルにも式をコピーすれば算出できます。

1.C列の累積売上高をもとに、D列に累積割合を計算しましょう。D列の書式設定: D列の全てのセルを選択し、表示形式を「パーセンテージ」に変更します。

2.D2セルに式を入力: D2セルに「=C2/$C$4」と入力します。ここで$C$4とはC列の最下段を指します。

3.D2のセルをD4まで下にドラッグすればD列全てが算出されます。

累積割合に基づいて、商品をA、B、Cの3つのランクに分けましょう。

1.ランクの基準を作る: H～K列に、商品をランク分けするための基準となる表を作成します

• H列: ランクAの開始値（％）
• I列: ～（区切り文字）
• J列: ランクAの終了値（％）
• K列: ランク（A、B、C）

2.VLOOKUP関数を使う: E2セルに以下の式を入力します。

「=VLOOKUP(D2,$H$1:$K$3,4,TRUE)」

• D2: この商品の累積割合
• $H$1:$K$3: ランクの基準表の範囲
• 4: 返す値の列番号（Hを1列目と数えるので、Kの列の値を表示させたい時は4になる）
• TRUE: 近い値を見つける この式は、D2の値が、H～K列のどの範囲に当てはまるかを探し、対応するランク（K列）をE2セルに表示します。

3.コピー: E2セルをコピーし、他のセルに貼り付けると、全ての商品のランクが自動で表示されます。

ABC分析は、様々な経営課題の解決に役立つ、シンプルながらも強力な分析手法です。パレートの法則に基づいて、重要な少数に集中することで、より効率的な経営を実現することができます。

表の作成: エクセルに、以下の列を作成します。

• A列: 顧客のIDや名前を入力します。
• B列: 最後に来店した日付（年/月/日）を入力します。
• C列: これまでに何回来店したか（累計購入回数）を入力します。
• D列: これまでにいくら使ったか（合計購入金額）を入力します。

RFM分析において、Recency（最終購入日）を数値化するために、DATEDIF関数を利用します。F1セルに分析期間の最終日を指定し、F2セルに=DATEDIF(B2,$F$1,”d”)と入力することで、各顧客の最終購入日から分析期間の最終日までの経過日数を算出できます。

この計算結果をR値として、顧客セグメンテーションなどに活用します。下図では顧客001は最終購入日から37日経過しているのがわかります。

顧客をより細かく分類するために、RFM分析では、それぞれの項目（Recency、Frequency、Monetary）をいくつかのランクに分けます。一般的には、データの分布を見て、3つまたは5つのランクに分割することが多いです。

今回は、データの状況に合わせて、以下の基準で5つのランクに分けることにしました。

次に、顧客を具体的なグループに分けるために、
ExcelのIF関数を使って、それぞれの顧客にランク付けをしましょう。今回は、次の条件で
R値を5つのランクに区分けしたいと思います。

IF関数の条件設定をしてみましょう。

条件の設定: まず、G2セルに次の式を入力します。

　「F2>=180」

この式は、「F2セルの値が180以上かどうか」を判定しています。　

条件が「真」の場合: F2の値が180以上の場合、つまり条件が満たされるとき、この式は「1」と表示されます。

条件が「偽」の場合: F2の値が180未満の場合、つまり条件が満たされないときには、さらに細かい条件分けが必要になります。

例えば、

=IF(F2>=90,2,IF(F2>=60,3,IF(F2>=30,4,5)))

のように、IF関数を入れ子にして、複数の条件を分岐させることができます。この例では、

F2が180以上なら1

F2が90以上180未満なら2

F2が60以上90未満なら3

F2が30以上60未満なら4

F2が30未満なら5 というように、5つのランクに分けることができます。

IF関数を使って最終購入日による顧客のランク分けをすると下図のようになります。

同様の手順でRとFも入力します。

1.合計値を算出する

R値、F値、M値それぞれのランクを合計して、顧客全体の評価をしてみましょう。例えば、3段階評価であれば、合計が9点の顧客が最も優良ということになり、5段階評価であれば合計が15点の顧客が最も優良ということになります。

ExcelのSUM関数を使うと、簡単に合計値を計算できます。合計したいセル範囲を選択し、数式バーに「=SUM(選択した範囲)」と入力すれば、合計値が表示されます。

この合計値は、顧客への販促活動の優先順位を決める際に役立ちます。例えば、合計値が高い顧客には、より積極的にアプローチする、といったように活用できます。

2.２軸を組み合わせて分析する

顧客の行動をより深く理解するためには、R値、F値、M値の合計値だけでなく、それぞれの組み合わせを詳しく見ていく必要があります。

例えば、合計値が同じ8点でも、R値が1でF値が5の顧客と、R値が4でF値が2の顧客では、顧客の属性が大きく異なります。前者は以前は頻繁に商品を購入をしていたが、最近は利用が減ってしまった顧客である可能性が高いです。一方、後者は最近商品を購入しているが、頻度はそれほど多くない顧客である可能性が高いです。

このような違いを把握するために、RFMの2つの軸を組み合わせて分析する方法が有効です。例えば、RとFの組み合わせを分析することで、最近頻繁に購入する顧客や、以前に比べて購入頻度が低下している顧客などを特定することができます。

• データ範囲を選択: 分析したいデータの範囲をすべて選択します。
• ピボットテーブルの作成: Excelの「挿入」タブから「ピボットテーブル」を選択し、新しいシートにピボットテーブルを作成します。
• 行ラベル: R（最終購入日からの経過日数）をドラッグします。
• 列ラベル: F（購入頻度）をドラッグします。
• 値: 顧客IDをドラッグし、「値の表示形式」で「データの個数」を選択します。
• クロス集計表の確認: 作成された表を見ると、RとFのそれぞれの組み合わせに対して、該当する顧客数が表示されます。

この表から、例えば、「最近購入して、かつ頻繁に購入している顧客が多い」といったような傾向を読み取ることができます。

表の作成: エクセルに、以下の列を作成しデーターを入力します。

• A列：月
• B列：顧客訪問回数
• C列：売上高

まず、顧客訪問回数と売上高のデータが入力されている
B1セルからC13セルまでの範囲を選択します。次に、「挿入」タブをクリックし、「グラフ」のグループから「散布図」を選びます。すると、横軸に顧客訪問回数、縦軸に売上高が表示された散布図が作成されます。このグラフを見ることで、顧客訪問回数と売上高の関係を視覚的に確認できます。

出来上がった散布図には空白部分が目立つのでグラフの横軸（X軸）を調整して、より見やすくしてみましょう。

グラフの横軸をダブルクリックし、「軸の書式設定」で「最小値」を「30」、「最大値」を「75」に設定すると、グラフの空白部分をカットできます。さらに、「グラフ要素」ボタンで軸ラベルを設定すると、グラフがより分かりやすくなります。

散布図を見ると、顧客訪問数と売上高は右肩上がりの傾向があるように見えます。しかし、このグラフだけでは、両者の関係が本当に強いのか、偶然なのかを判断するのは難しいところです。

そこで、より確かな証拠として、相関係数という数値を使って、両者の関係を詳しく調べてみましょう。相関係数は、-1から1までの数値で、2つのデータがどの程度似ているかを表します。1に近ければ強い正の相関、-1に近ければ強い負の相関、0に近ければほとんど相関がないと判断できます。

Excelでは、CORREL関数を使って相関係数を簡単に計算できます。B14セルに「=CORREL(B2:B13, C2:C13)」と入力し、Enterキーを押すと、結果は約0.99となりました。これは、顧客訪問数と売上高に強い正の相関があることを示しています。

先に相関係数は-1〜1の範囲で示されると述べましたが、それぞれの値には次のような意味があります。