顧客管理表のエクセルの作り方|列の設計と関数(印刷用シートつき)
顧客管理表は、1シートに詰め込むと、すぐに崩れます。顧客シートと履歴シートの2シートに分ける作り方と、最後に会った日・回数・累計・今月を出す関数の例を整理しました。エクセルを開く前に列を決められる設計シートは、A4で無料で配布します。
顧客管理表をエクセルで作るときの考え方
顧客管理表は、1シートに全部を詰め込むと、すぐに崩れます。来店のたびに行が増えて、同じ人が何行にも出てくるためです。最初から、次の2シートに分けて作ります。
| 顧客シート | 1行が1顧客です。名前、連絡先、状況、次にやることなど、「その人の状態」を書きます。最後に会った日や累計は、手入力せず、関数で出します。 |
|---|---|
| 履歴シート | 1行が1回の来店・やりとりです。日付、内容、金額を書きます。同じ人が何回来ても、行を足すだけで済みます。 |
この2つを「顧客ID」でつなぎます。名前でつなぐと、「田中」と「田中様」が別の人として数えられてしまうため、IDを使います。紙の台帳の書き方は顧客台帳のテンプレート(印刷用・無料)にあります。
列設計シート(顧客シートと履歴シート)
1枚目が顧客シートの列設計、2枚目が履歴シートの列設計と、顧客シートに入れる関数です。エクセルを開く前に、紙で列を決めておくと、あとから列を足し直す手間が減ります。このページの印刷ボタンを押すと、2枚だけがA4で印刷されます。PDFをダウンロードして使うこともできます。
| 列 | 列名 | 入れるもの | 形式 | ルール |
|---|---|---|---|---|
| A | 顧客ID | 通し番号(C001, C002…) | 文字 | 名前の表記ゆれを防ぐ。履歴シートと結ぶ鍵 |
| B | 名前 | 呼び名と本名のどちらか | 文字 | 同じ人は1行だけ |
| C | よみ | ふりがな | 文字 | 並べ替え用 |
| D | 連絡先 | 連絡に使う手段を1つ | 文字 | 電話・メールなど |
| E | 初めて会った日 | きっかけも別の列に | 日付 | 日付の形式で入れる |
| F | 最後に会った日 | (関数で自動) | 日付 | 履歴シートから出す |
| G | 回数 | (関数で自動) | 数値 | 履歴シートから数える |
| H | 累計(円) | (関数で自動) | 数値 | 履歴シートから足す |
| I | 今月(円) | (関数で自動) | 数値 | 月が替わると0から |
| J | 次にやること | 一言で。期限も添える | 文字 | 空欄なら、決めていない人 |
| K | 状況 | 通う中/休み中/終了 | 選択 | 入力規則のプルダウン |
| L | メモ | 好み・要望(本人の言葉で) | 文字 | 長くなったら別の行へ |
| M | ||||
| N |
| 列 | 列名 | 入れるもの | 形式 | ルール |
|---|---|---|---|---|
| A | 顧客ID | 顧客シートのIDと同じもの | 文字 | 名前ではなくIDで結ぶ |
| B | 日付 | 会った日・やりとりした日 | 日付 | 日付の形式で入れる |
| C | 内容 | 施術・購入・話したこと | 文字 | 一言でよい |
| D | 金額(円) | 来店・取引の金額 | 数値 | 「円」やカンマは入れない |
| E | 次にやること | 次回までの宿題 | 文字 | 終わったら顧客シートへ |
| F |
回数(G2):=COUNTIF(履歴!$A:$A,$A2)
累計(H2):=SUMIF(履歴!$A:$A,$A2,履歴!$D:$D)
今月(I2):=SUMIFS(履歴!$D:$D,履歴!$A:$A,$A2,履歴!$B:$B,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),履歴!$B:$B,"<"&EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),1))
経過日数(任意の列):=IF(F2="","",TODAY()-F2)
関数の入れ方
顧客シートの「最後に会った日」「回数」「累計」「今月」は、履歴シートから関数で出します。顧客シートの2行目に入れて、下の行へコピーします。履歴シートの名前は「履歴」、列は上の設計シートのとおりとした場合の式です。
| 最後に会った日 | =IF(COUNTIF(履歴!$A:$A,$A2)=0,"",MAXIFS(履歴!$B:$B,履歴!$A:$A,$A2)) (MAXIFS は Excel 2019 以降と、Google スプレッドシートで使えます) |
|---|---|
| 回数 | =COUNTIF(履歴!$A:$A,$A2) |
| 累計 | =SUMIF(履歴!$A:$A,$A2,履歴!$D:$D) |
| 今月の合計 | =SUMIFS(履歴!$D:$D,履歴!$A:$A,$A2,履歴!$B:$B,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),履歴!$B:$B,"<"&EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),1)) (月が替わると、自動で0から数え直します) |
| 経過日数 | =IF(F2="","",TODAY()-F2) (最後に会った日からの日数。連絡する人を決めるときに並べ替えます) |
崩れないための入力のルール
- 日付は日付の形式で入れる。「10/5」ではなく、年も入る形にします。文字として入ると、並べ替えも関数も正しく動きません。
- 金額は数字だけ。「円」やカンマを手入力すると、足し算できなくなります。表示の形式は、セルの書式で整えます。
- 1つのセルに1つの情報。「電話・メール」のように2つ入れると、あとで使えません。列を分けます。
- 行を消さず、状況で区別する。来なくなった人は消さずに、「状況」を「終了」にします。フィルターで隠せます。
- 表をテーブルにする。範囲を選び、「挿入」の「テーブル」にすると、行を足したときに、関数や書式が自動で広がります。
名前や連絡先を入れるときの注意
名前や連絡先をファイルにして事業で使うなら、個人情報保護法の対象になります。ファイルは置く場所を決め、見られる人を限定します。クラウドや共有フォルダに置くときは、共有の範囲を確かめます。詳しい決まりは顧客台帳のテンプレートの記事にまとめています。
エクセルの限界と、アプリへの切り替えどき
| エクセルが得意なこと | 並べ替え、絞り込み、集計です。関数を入れておけば、最後に会った日や累計は自動で出ます。 |
|---|---|
| エクセルが苦手なこと | 外出先のスマホから、会った直後に1件記録すること。期限や、ご無沙汰の人を、向こうから教えてもらうこと。履歴が増えたあとの動作の重さ。 |
会った直後の記録がたまりがちになったら、切り替えどきです。いまのエクセルの内容は、CSV に書き出して、アプリに取り込めるものもあります。
エクセルの関数を、書かずに済ませたいとき
One CRM は、個人事業主・サロンのための顧客管理アプリです。顧客の名前と会ったことを記録すると、最後に会った日・回数・今月の売上・累計が自動で計算されます。この記事で関数を組んだ部分を、アプリが代わりに数えるイメージです。項目も呼び名も、自分で作り替えられます。
iPhone の連絡先から顧客をまとめて取り込めます。CSV の書き出しと取り込みにも対応しているので、エクセルで作った一覧を移すときにも使えます。しばらく会えていない顧客は、ホームに並びます。データは端末と自分の iCloud に保存されます。詳しくはOne CRM の紹介ページをご覧ください。
列設計シートの項目と文言は、Pohoda が独自に作成したものです。印刷して自由にお使いください。関数は一般的な書き方の例で、お使いのバージョンや設定によって、書き換えが必要なことがあります。
よくある質問
顧客管理表は、エクセルでどう作ればいいですか?
顧客シート(1行1顧客)と履歴シート(1行1回の来店・やりとり)の2シートに分け、顧客IDでつなぎます。最後に会った日、回数、累計は、履歴シートから関数で出します。1シートに全部を詰め込むと、同じ人が何行にも出てきて崩れます。
顧客管理表のエクセルテンプレートは、無料で使えますか?
Pohoda の列設計シートは無料で使えます。印刷ボタンでA4・2枚が印刷でき、PDFのダウンロードもできます。エクセルのファイルそのものではなく、列と関数を決めるための設計シートです。項目と文言は Pohoda が独自に作成したものです。
顧客管理で、最後に会った日を自動で出す関数は?
履歴シートに日付を入れておけば、MAXIFS 関数で、顧客ごとの最新の日付を出せます。MAXIFS は Excel 2019 以降と、Google スプレッドシートで使えます。回数は COUNTIF、累計は SUMIF で出せます。
顧客管理表に、入れる項目は何がいいですか?
顧客シートには、顧客ID、名前、よみ、連絡先、初めて会った日、最後に会った日、回数、累計、次にやること、状況、メモが基本です。履歴シートには、顧客ID、日付、内容、金額、次にやることを入れます。
顧客管理の表をスマホで更新できるアプリはありますか?
One CRM は、会ったことを記録すると、最後に会った日・回数・今月の売上・累計が自動で計算される顧客管理アプリです。CSV の書き出しと取り込みに対応しています。顧客は10件まで無料です。