One CRMお金と仕事の整理印刷用シートつき

顧客管理表のエクセルの作り方|列の設計と関数(印刷用シートつき)

顧客管理表は、1シートに詰め込むと、すぐに崩れます。顧客シートと履歴シートの2シートに分ける作り方と、最後に会った日・回数・累計・今月を出す関数の例を整理しました。エクセルを開く前に列を決められる設計シートは、A4で無料で配布します。

公開
更新
読む目安
約6分
書いた人
杉本 啓輔

顧客管理表をエクセルで作るときの考え方

顧客管理表は、1シートに全部を詰め込むと、すぐに崩れます。来店のたびに行が増えて、同じ人が何行にも出てくるためです。最初から、次の2シートに分けて作ります。

顧客シート1行が1顧客です。名前、連絡先、状況、次にやることなど、「その人の状態」を書きます。最後に会った日や累計は、手入力せず、関数で出します。
履歴シート1行が1回の来店・やりとりです。日付、内容、金額を書きます。同じ人が何回来ても、行を足すだけで済みます。

この2つを「顧客ID」でつなぎます。名前でつなぐと、「田中」と「田中様」が別の人として数えられてしまうため、IDを使います。紙の台帳の書き方は顧客台帳のテンプレート(印刷用・無料)にあります。

列設計シート(顧客シートと履歴シート)

1枚目が顧客シートの列設計、2枚目が履歴シートの列設計と、顧客シートに入れる関数です。エクセルを開く前に、紙で列を決めておくと、あとから列を足し直す手間が減ります。このページの印刷ボタンを押すと、2枚だけがA4で印刷されます。PDFをダウンロードして使うこともできます。

PDFをダウンロード(A4・2枚)
顧客シートの列設計(1行1顧客)
作成日:ファイル名:
列列名入れるもの形式ルール
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
顧客シートの2行目に入れる関数(下の行へコピー)
最後に会った日(F2):=IF(COUNTIF(履歴!$A:$A,$A2)=0,"",MAXIFS(履歴!$B:$B,履歴!$A:$A,$A2))
回数(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件まで無料です。

← 読みもの一覧へ戻る