CRM(顧客・案件管理)

受注データ(注文明細)から顧客別の売上を求める方法

顧客別の売上、把握していますでしょうか?優良顧客を見つけるためには、顧客別の売上を求めることが必要です。
売上だけでなく、利益やその他の属性を見ることも重要ですが、売上は最初に確認しやすい項目です。

この記事では、受注データ(注文明細)から、エクセル(Excel)を使って顧客別の売上を求める方法をご紹介します。

<スポンサードリンク>



受注データ(注文明細)はどこにあるか?

まず、元となるデータを準備します。

  1. 日付(受注日/出荷日など)
  2. 顧客(会社名/氏名/顧客IDなど)
  3. 金額
  4. 商品(商品名/商品IDなど)
  5. 受注ID(1回の受注を特定するためのID)

これらのデータを準備します。
顧客別売上を算出するのに最低限必要なのは、1~3です。

会社によって、このデータが蓄積されているところは異なります。

  • 小売業であればPOS
  • ECサイトを運営していれば、そこに蓄積されているデータ
  • 営業チームが管理している受注台帳
  • 経理で入力している会計ソフト

など、会社によって使えるデータのある場所は変わりますので、まずは確認してみましょう。

補足ですが、データ活用をする上での前提は、自社にとって必要なデータがどこにあるか?を把握することです。
活用するだけでなく、情報を守る(情報セキュリティを高める)ためにも、非常に重要です。

データを生成・整理する

データを生成する

POSやECサイト、会計ソフトなどからデータを出力します。
大抵の場合、CSVファイルで出力することができます。

※かなり古いソフトの場合、CSVですら出力できないことがあります。
今後のデータ活用を考えた場合、そのような古いソフトは変えておきたいです。

例えば2020年1年間のデータを分析したい場合には、その期間を特定して、データを出力します。
そのような期間指定ができない場合には、全部出力して、データを整理する段階で、余計なデータを削除します。

データをデータベース形式に整理する

エクセルで顧客別売上を算出するためには、データを処理できる形に変えておく必要があります。
まずデータを処理する前のダメな状態をご紹介します。

良くあるパターン(その1)は、空白の行や列・セルがある場合です。
表の左側・上側にも、余計な空白行・列を入れないようにしましょう。
(A1セルから詰めてデータを入力します)

良くあるパターン(その2)は、1つのセルに複数のデータが含まれる場合です。

どちらもエクセルでは処理できない形です。
以下のように1行ずつ、キレイにデータが含まれている形式に整えます。

またセル結合は絶対に禁止です。
(エクセルからセル結合の機能が無くなれば良いのに・・と思います)

この例は1回の受注(=受注IDが同じ)で、同じ顧客が3つの商品を購入しています。
同じ受注ID、受注日、顧客IDが繰り返されるのは、人間としては見にくいかもしれません。
ただ、データを一括処理するときには、このような形式(データベース形式)が必須になります。

ちなみにデータ活用する上で、最も手間が掛かるのは、このデータ整理です。
(余談ですが、データ分析における「前処理」と言います)

したがってできる限り最初からデータベース形式で出力できるツール(POSや会計ソフト)を利用することをお勧めしています。
事業の本質じゃないところで、余計な手間をかけたくないですからね。

エクセル(Excel)で顧客別売上を算出する

このようにデータを整理できたら、エクセルで顧客別売上を算出します。
やり方は簡単です。
受注日、顧客ID、金額の3つからなるシンプルなパターンで見てみましょう。

このように整理できているデータがあれば、顧客別売上はSUMIF関数を使って一発で算出できます。

顧客一覧を作成

最初にE列に顧客IDの一覧を作成しておきます。
この例では顧客IDなので、オートフィルによる連続データの作成で一発で作成しています。

これが顧客会社名などの場合は、別途、顧客マスタなどからコピーしてくる必要があります。

IT・データを理解するためのスタートラインは「マスターデータ」マスターデータとは何でしょうか?従業員・顧客・商品などをしっかり区別するための基準となるデータのことです。コンピューター登場以前から台帳として存在していました。データやITを活用する経営をするための土台となる考え方ですから、経営者は身につけておきましょう。 ...

顧客一覧がどこにあるか?も会社によって異なります。
これを機会に確認して、いつでも利用できる状態にしておきましょう。

顧客別売上を算出

ここまで長い道のりでしたが、顧客別売上の算出は一瞬です。
エクセルのSUMIF関数を利用します。

顧客(B0001:E2セル)の1年間の金額を算出するには、以下の式を入力します。

F2 = SUMIF(B:B, E2,C:C)

SUMIF関数は、以下のようなものです。

=SUMIF(範囲、検索条件、合計範囲)

  1. 表内で「範囲」の列と「合計範囲」の列を指定
  2. 「範囲」のなかで、検索条件に一致するセルを探す
  3. 「合計範囲」のセルの数値だけを合計する

この例では、B列(顧客ID)の中からE2(B0001)を検索して、C列(金額)の合計を出すことになります。
受注日が2020年1月1日~2020年12月31日までに絞り込めていれば、年間の合計金額を出すことができます。

同じ式を全顧客IDにコピーすれば、顧客ごとの売上が分かります。

順位を把握したり、売れている顧客とそうでない顧客を分類するには、こちらの記事をご覧下さい。

注力すべき顧客を判別するためのデシル分析どの顧客に注力すべきか?識別する手法の1つとしてデシル分析があります。この記事ではExcelを使ったデシル分析のやり方と注意点をご紹介します。リソース(人員・時間・予算)が限られるなか、注力すべき顧客を判別することは非常に重要です。...

補足

この例では「受注日」を基準としていますので、厳密に言えば「売上」ではありません。
分析するときに、何を基準にしているのか?を意識して、分析するときにいつも同じ基準を使うようにしましょう。
その都度、受注日になったり出荷日になったり基準が変わると、分析結果も変わってしまいます。

また、同じやり方で、商品別売上を算出することも可能です。
顧客別よりも商品別売上が重要な事業の場合には、商品別に分けて分析してみましょう。

まとめ
  • 受注・売上データのあるところは会社によって異なる(把握しておく)
  • エクセルで処理する前に、データベース形式に整える
  • SUMIF関数を使って顧客別売上を算出する



【編集後記】
自分でつくる昼食のパターンが少なすぎて飽きてきました。。
昨日は考え事しながら準備したら、ミートソーススパゲティ・トマトスープ・野菜ジュース(トマト中心)と、トマトだらけに(笑)
もう少しレパートリーを増やさないと。


メルマガ『経営は100種競技!』を毎日配信しています。
マーケティングやITを身につけたい。
ビジネスを楽しみたい。
変化・成長したいというビジネスパーソンにお読みいただいています。

渋屋 隆一
プロフィール
マーケティングとIT、そしてデータを使った「売れ続ける仕組みづくり」「業務改善」が得意。コンサルティングや研修・セミナーで中小企業の経営支援をしています。元IT企業でエンジニア→マーケティング。中小企業診断士。
\ Follow me /