基本ノウハウ
エクセルは低コストかつ利用できる従業員が多いことから、顧客管理でも導入されやすいツールとなっています。ただし使いやすいシートを作るためには、より実践的なエクセルの知識が必要です。
本記事ではエクセルで顧客管理データベースを作る方法や、便利な機能を解説します。無料で使えるテンプレートサイトや、顧客データの分析方法もあわせて紹介しています。ぜひ参考にしてみてください。
まずはエクセルを使った顧客管理データベースの作成方法や手順を、以下の4つに分けて解説します。
それぞれ詳しく見ていきましょう。
顧客管理データベース作成の前準備として、管理項目を選定しましょう。盛り込む情報の例は、下表の通りです。
| 基本情報 | ・企業名 ・所在地 ・電話番号 ・メールアドレス ・代表者名 など |
| 担当者情報 | ・担当者名 ・担当部署 ・決裁権の有無 など |
| 購買関連の情報 | ・購入製品名/サービス名 ・購入時期 ・購入金額 など |
| 営業関連の情報 | ・購入のきっかけ ・商談内容 ・受注確度 ・最終訪問日 ・次回の訪問予定日 など |
| その他の情報 | ・顧客管理番号 ・関係性 ・セミナー/展示会への参加歴 など |
上記のような項目から、目的に合わせて管理する内容を選定します。例えば売上管理の場合は、主に次の5つを抽出すると良いでしょう。
また案件管理の場合は、主に次の5つがおすすめです。
はじめは項目を少なくし、必要に応じて増やしていくと管理しやすいでしょう。
管理項目を選定できたら、実際にエクセルで設定します。具体的な手順は、以下の通りです。
タイトルと管理項目の間に1行空ける理由は、のちの操作でデータベースとして認識させるためです。また管理項目を横並びにすることで、フィルター機能などエクセルの便利な機能を使えるようになります。
より使いやすい顧客管理シートを完成させるためにも、上記の操作は必ず行いましょう。
次に各管理項目に合わせて、情報を縦並びに入力します。顧客番号はのちのちも管理しやすいよう、次のように重複しない数字を割り振ると良いでしょう。
ここでは顧客情報の入力時に活用したい機能として、書式設定と入力規則の設定について解説します。
データの表示方法を統一したいときは、書式設定を利用しましょう。具体的な操作方法は、次の通りです。
例えば日付の書式設定は、デフォルトの「yyyy/m/d」を種類バーで「yyyy/mm/dd」へ書き換えると日付の桁がそろい見やすくなります。
また顧客番号は「000」あるいは「000000」、金額は「#,###」（3桁区切り）といったコードを利用すると便利です。保有する顧客数や自社製品・サービスの価格に合わせて、分かりやすい表示方法を設定していきましょう。
受注確度や購入製品など入力内容が固定している場所は、入力規則を設定するのがおすすめです。ドロップダウンリストを利用できるため、入力ミスや表記揺れが減り、より精度の高い顧客管理データベースを作成できます。具体的な操作方法は、次の2パターンです。
【方法1】
【方法2】
ドロップダウンリストは入力の手間を大幅に削減できるため、業務効率化という意味でも積極的に取り入れたいところです。
最後にテーブル機能を使って、エクセルのシートをデータベース化します。具体的な操作方法は、次の通りです。
設定が完了すると管理項目にフィルターが設定され、背景色やフォントも変わります。
デザインを変更したい場合はいずれかのセルを選択し、上部バーの「テーブルデザイン」から選択しましょう。
なおテーブル機能でデータベース化すると、行を削除してもデザインのパターンが崩れません。
顧客情報の追加や修正も簡単にできるため、エクセルで顧客管理する場合はデータベース化の操作を忘れないようにしましょう。
エクセルで作成した顧客管理データベースを、より使いやすくしたい場合は次に挙げる5つの機能がおすすめです。
それぞれ詳しく見ていきましょう。
ウィンドウ枠の固定機能を活用すると、下方へスクロールしても管理項目を見られる状態にできます。具体的な操作は、次の通りです。
特に顧客情報の増加にともない、スクロールする必要性が出てきた際に重宝する機能です。
フィルター機能では、顧客情報の並び替えや抽出ができます。例えば購入製品という管理項目から、製品Aを購入した顧客を抽出し一覧化することも可能です。具体的な操作は、次のようになります。
なおテキストフィルターでは自由記述によるデータの指定や、複数条件による絞り込みもできます。また特定のセルと同じ値のデータを抽出したい場合の操作は、次の通りです。
特定の顧客データを検索したいときや、受注傾向などを分析したいときに活用してみてください。
スライサー機能では、フィルター機能よりもさらに的を絞ったデータの抽出が可能です。具体的な操作は、次のようになります。
クリック後は、選択した項目ごとに値のダイアログが表示されます。
複数の項目もワンクリックで抽出できるため、何度もフィルターをかけたいときはスライサー機能を使うとより効率的です。
重複チェック機能では、重複したデータを色付けしてくれます。具体的な操作方法は、次の通りです。
なお重複したデータは、ctrl+Fのショートカットキーでも検索できます。入力内容を置換したい場合は、ホームにある「検索と選択」でまとめて修正しましょう。
ピボットテーブル機能では、抽出したデータを表・グラフ化できます。具体的な操作方法は、次の通りです。
また作成されたピボットテーブルは、グラフ化もできます。具体的な操作方法は、次の通りです。
特に受注傾向などの分析を進める際に役立つため、顧客管理の担当者は操作に慣れておくと良いでしょう。
エクセルの顧客管理に使えるテンプレートサイトは、主に次の3つです。
それぞれ詳しく見ていきましょう。
Officeテンプレートの顧客管理表は、所在地や電話番号など企業の属性情報をまとめられるテンプレートです。基本的な顧客リストの作成に役立つため、初めて顧客管理を行う担当者でも使いやすいでしょう。
また同サイト内ではテンプレートの使い方も公開されており、エクセルの実践的な知識がない方でも分かりやすくなっています。
Excel Form 顧客管理は、フォームに入力するだけで顧客情報や売上情報を一覧化できるテンプレートサイトです。VBAマクロが公開された有料の正規版もありますが、特にカスタマイズが必要ない場合は無料版でも十分活用できます。
はがきや封筒のラベルも作成できるため、郵送DMの送付時にも役立つでしょう。
［文書］テンプレートの無料ダウンロードにある顧客管理表は、担当者やメールアドレスなど一般的な管理項目がデフォルトで設定されているテンプレートです。年賀状やお中元などの項目もありますが、必要に応じて簡単に変更・削除できます。
ダウンロード後はテーブル機能などを利用してデザインの変更や機能を追加するとより使いやすくなるでしょう。
エクセルではRFM分析やデシル分析といった、顧客データ分析も実行できます。最低限必要な項目は、次の4つです。
ここではエクセルによるRFM分析や、デシル分析のやり方を見ていきましょう。なお顧客データ分析について詳しく知りたい方は、こちらの記事もあわせてチェックしてみてください。
関連記事：「【目的別】顧客データ分析の方法9選｜契約件数1.5倍！活用事例も解説」
RFM分析とは購買行動をもとに、優良顧客や維持顧客などを見極める方法です。分析項目は、次の3つになります。
エクセルでRFM分析を進める手順は、以下の通りです。
1. ピボットテーブルでRFMを集計
2. 完成した表を新規シートへコピー＆ペースト
3. RFMを定義
a. 「最終注文日からの経過日数」という管理項目を追加
b. 基準日を決定
c. 基準日セルの値-最終注文日の値で経過日数を算出
d. データをもとに定義を決定
e. 各定義のセルに書式設定（0日以内/0回以上/0円以上など）
4. IF関数を使ってRFMランクを振り分け
a. Rのランク分け「=IF(○>=□,’’1’’,IF(○>=□,’’2’’,IF(○>=□,’’3’’)))」※1
b. F・Mも同様にランク分け
c. 行の最後まで計算式をコピー
※1○：経過日数のセル、□：日数、1～3：ランク
なお算出されたRFMは、再度ピボットテーブルを活用したクロス集計を実行することで、より深い分析ができます。例えばRとFのランクがどちらも3の場合は最優良顧客、両方とも1の場合は離反顧客と判断しやすくなるでしょう。
デシル分析とは購入金額をもとに、顧客をグループ化して分析する方法です。エクセルでの操作方法は、次のようになります。
1. ピボットテーブルで購入金額の総額を算出
2. 一番上のセルを右クリック
3. 「並べ替え」内の「降順」を選択
4. 完成した表を新規シートへコピー&ペースト
5. 順位の振り分け
6. IF関数を利用し、デシルランクを割り振り
a. 10等分した際の分割幅を決定（100人なら分割幅は10）
b. デシルランク項目の一番上にあるセルにIF関数を挿入※2
c. 行の最後まで計算式をコピー
※2：挿入するIF関数
=IF(○<=□,’’D1’’, IF(○<=□*2,’’D2’’, IF(○<=□*3,’’D3’’, IF(○<=□*4,’’D4’’, IF(○<=□*5,’’D5’’, IF(○<=□*6,’’D6’’, IF(○<=□*7,’’D7’’, IF(○<=□*8,’’D8’’, IF(○<=□*9,’’D9’’, IF(○<=□*10,’’D10’’))))))))))
○：デシルランクのセル、□：分割幅のセル、D1～D10：デシルランク
完成した表は再度ピボットテーブルを活用すると、ランクごとの傾向を分析できます。
ただしデシル分析では、ランクが高いからといって必ずしも優良顧客とは限りません。より精度の高い分析を進めたい場合は、前述したRFM分析との併用がおすすめです。
エクセル以外の顧客管理方法としては、次の2つが挙げられます。
情報の一元管理や業務効率化などを考えると、顧客管理システム（CRM）の利用がおすすめです。CRMには、次のような機能が主に備わっています。
初めて導入する場合は、SFA（営業支援システム）を内包したツールを選ぶと部門間の情報共有がよりスムーズになります。無料トライアルが利用できるツールや、無料版のある製品を知りたい方は、こちらの記事もぜひ参考にしてみてください。
関連記事：「無料の顧客管理ソフト・アプリおすすめ8選｜メリット・デメリットも解説」
エクセルで顧客管理を進めるためには、まずデータベースの作成が必要です。入力項目は案件管理や売上管理など、目的に応じて取捨選択しましょう。
また長期的に利用しやすいシートを作る上では、スライサー機能といったエクセルの便利な機能を有効活用することも大切です。特にピボットテーブルはRFM分析やデシル分析でも利用できるため、操作を覚えておきたい機能の1つといえます。
顧客管理を効率的に進めたい企業は、自社に合った方法を模索していきましょう。
