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

SuitUP 売上分析

売上データを有効活用できていますか? Excelを活用すれば、複雑な売上データをわかりやすく可視化し、ビジネスのヒントを見つけ出すことができます。この記事では、売上分析の基本から、具体的な分析方法までを詳しく解説します。

Excelの機能を最大限に活用して、売上データから隠された真実を引き出しましょう。売上向上や新たな戦略立案に繋がる、効果的な売上分析の方法を習得したい方は、ぜひご一読ください。

目次

売上分析の必要性

売上分析とは、過去の売上データから、何が売れているのか、どんな客層に人気があるのかといったことを詳しく調べることです。この分析を通して、ビジネスの課題を見つけ出し、改善点を見つけ、将来の売上アップにつなげるためのヒントを得ることができます。

売上分析では、売上額だけでなく、商品別、顧客別、地域別など、様々な角度からデータを切り口にして分析します。これにより、特定の商品が伸び悩んでいる理由や、新たな顧客層を開拓する可能性など、具体的な施策につながる情報を発見できます。

売上分析を行うメリットとして主に次の3点が挙げられます。

収益性の高い商品・顧客がわかる

商品や顧客ごとに売上を細分化することで、どの商品が売れているのか、どの顧客が貢献度が高いのかを明確にできます。限られたリソースを最も効果的に活用するために、収益性の高い商品・顧客を特定することは、企業の成長にとって非常に重要です。

正確な売上予測が立てられるようになる

売上分析により、どの商品が売れているのか、どの顧客が貢献度が高いのかといった詳細なデータが得られます。これに基づいて、例えば、人気商品を増産したり、不人気商品は値下げしたりといった最適な生産・販売戦略を立てることができます。

また、季節変動や経済状況などの外部要因も考慮することで、より精度の高い売上予測を行い、在庫不足や過剰生産といったリスクを回避することができます。

例えば、アパレルメーカーの場合、過去の売上データを分析することで、どの季節にどのアイテムが売れるのかという傾向を把握できます。このデータに基づいて、事前に必要な量の商品を生産することで、在庫過多を防ぎ、売れ残りを減らすことができます。

また、売上が伸び悩んでいる時期には、割引セールや新商品の投入といった販促活動を行うことで、売上を回復させることも可能です。

商品開発や新規顧客の開拓に繋がる

どの商品が売れているのか、どの機能が求められているのかを分析することで、顧客のニーズに合致した商品を開発することができます。また、競合他社の製品との比較分析を行い、自社製品の強みと弱みを明確にし、競合との差別化を図ることができます。

さらに、どの顧客層が自社の商品を購入しているのかを分析し、新たなターゲット顧客層を特定することができ、新規顧客層に対して、効果的なマーケティング施策を展開することができます。

エクセルでできる売上分析

エクセルでできる売上分析の方法を3点紹介します。

ABC分析

ABC分析は、ある対象を重要度によってA、B、Cの3つのグループに分ける分析手法です。別名「重点分析」とも呼ばれ、例えば、商品を売上高によって分類することで、どの商品が売上に大きく貢献しているかが一目で分かります。

この手法は、パレートの法則という「全体の数値の8割は、上位2割の要素が生み出す」という考え方にもとづいています。ABC分析では、一般的に、売上高上位20%の商品をAグループ、20~80%の商品をBグループ、残りをCグループと分類します。

ABC分析を活用することで、重要な商品に経営資源を集中させたり、在庫管理を最適化したりすることができます。

エクセルでABC分析を行う方法

STEP
分析したい売上データーを降順に並び変える

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

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

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

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

STEP
累積値をSUM関数で入力する

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

STEP
累積割合を算出する

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

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

 

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

STEP
ABCのランク分けをVLOOKUP関数を使用しておこなう

累積割合に基づいて、商品を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セルをコピーし、他のセルに貼り付けると、全ての商品のランクが自動で表示されます。

STEP
ABC分析結果から商品別の重要度を見直す

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

RFM分析

RFM分析とは、顧客一人ひとりの購買データを基に、顧客をグループ化し、その特徴を捉えるための分析手法です。

RFMは、以下の3つの指標の頭文字を取ったものです。

  • Recency : 最終購入日
  • Frequency : 購入頻度
  • Monetary : 購入金額

この3つの指標をもとに、顧客を「最近購入したか」「頻繁に購入するか」「高額で購入するか」といった視点で分類します。

RFM分析を行うことで、以下のようなことが可能になります。

RFM分析のメリット
  • 顧客のセグメンテーション: 顧客を価値の高い顧客、将来性のある顧客など、複数のグループに分類し、それぞれのグループに合わせたマーケティング施策を実行できます。
  • 顧客のLTV(顧客生涯価値)の予測: 顧客が今後どれだけ企業に貢献してくれるかを予測し、顧客の育成に繋げることができます。
  • 最適なマーケティング施策の立案: 顧客の特性に合わせて、最適なプロモーションやキャンペーンを実施できます。

エクセルでRFM分析を行う方法

STEP
分析に必要な売上データーを入力する

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

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

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

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

STEP
RFMランクを定義する

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

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

STEP
「if関数」を利用してランク分けする

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

  • 【ランク5】30日未満
  • 【ランク4】30日以上60日未満
  • 【ランク3】60日以上90日未満
  • 【ランク2】90日以上180日未満
  • 【ランク1】180日以上

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も入力します。

STEP
RFM分析を行う

1.合計値を算出する

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

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

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

2.2軸を組み合わせて分析する

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

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

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

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

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

相関係数による分析

売上分析において、相関係数による分析とは、ある2つの数値データ間の関係性の強さを数値で表し、それらの関係性を分析する手法です。

例えば、

  • 広告費と売上の関係
  • 商品の価格と売上数量の関係
  • 気温と売上の関係

といったように、売上に関わる様々な要素を数値化し、それらの関係性を相関係数で表すことで、より深く売上を分析することができます。

エクセルで相関係数による分析を行う方法

今回は顧客の訪問回数と売上高の相関関係を分析します。

STEP
分析に必要な売上データーを入力する。

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

  • A列:月
  • B列:顧客訪問回数
  • C列:売上高
STEP
散布図を挿入する

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

STEP
散布図の体裁を整える

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

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

STEP
関数で相関関係の強弱を計算する

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

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

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

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

相関係数の意味
  • 1〜0.7 強い正の相関関係(片方の数字が上がると、もう一方の数字も上がる)
  • 0.7〜0.5 弱い正の相関関係
  • 0.5〜▲0.5 相関関係なし
  • ▲0.5〜▲0.7 弱い負の相関関係
  • ▲0.7〜▲1 強い負の相関関係(片方の数字が上がると、もう一方の数字は下がる)
よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

目次