エクセルで顧客データベースを作る｜探しやすくする列の決め方

エクセルで顧客管理のデータベースを作ろうとしたとき、手が止まるのはたいてい列の設計です。名前と電話番号を並べた表なら誰でも作れますが、そこから「先月来たお客さまだけを出す」「よく頼まれているメニューを数える」「しばらく来ていない人に声をかける」といった使い方に進もうとすると、列の作りが足を引っ張ります。表として見やすい形と、データベースとして扱いやすい形は違うからです。ここでは、探しやすく、集計しやすく、後から壊れにくい列の決め方を、作る順番に沿って扱います。関数を覚えるより先に、列を決めるところに時間をかけるほうが、後の作業がはるかに軽くなります。

表とデータベースは何が違うのか

同じエクセルのシートでも、人が読むための表と、機械に処理させるためのデータベースでは作りが逆になります。人が読む表は、見出しを大きくし、セルを結合して整え、空行を入れて区切り、合計を途中に挟みます。読みやすさのための工夫です。データベースとして扱う表では、この工夫のすべてが邪魔になります。

データベースとして扱える形には、決まった条件があります。1行目が列の名前だけの行になっていること。2行目から下がデータの行で、途中に空行や小計の行が挟まらないこと。1行が1件を表していること。1つの列には1種類の情報だけが入っていること。セルの結合が使われていないこと。この5つが揃っていれば、並べ替え、絞り込み、集計、他のシートからの参照が、すべて素直に動きます。

逆に、この5つのうち1つでも崩れると、できることが急に減ります。空行が1本入るだけで、並べ替えの範囲がそこで途切れます。セルが結合されていると、絞り込みが正しく効きません。1つのセルに「山田太郎様（080-1234-5678）」のように2種類の情報が入っていると、電話番号だけで探すことができなくなります。

作り始める前に押さえておきたいのはこの点です。台帳として印刷して壁に貼りたいのか、それとも中から必要な行を取り出して使いたいのか。後者であれば、見た目を整える作業は最後に回して、まず形を整えます。

この記事では、エクセルを使った顧客管理データベースの基本的な作り方から、VLOOKUP関数やピボットテーブルを活用した高度なデータ分析、さらには業務効率を劇的に向上させる活用術まで、プロのノウハウを網羅的に解説します。 出典: flexcrm.jp

関数や集計の機能は、形が整った表の上でしか力を出しません。順番としては、列の設計、表の形、そのあとに関数です。

作る前に決める3つの設計

シートを開く前に決めておくことが3つあります。この3つを決めずに入力を始めると、数百行入れたところで作り直すことになります。

1つ目は、1行が何を表すかです。顧客の台帳なら、1行は1人のお客さまです。来店の記録なら、1行は1回の来店です。この2つを同じシートに混ぜると、どちらの用途にも使えない表になります。混ぜてしまう典型が、お客さまの行に「2回目の来店日」「3回目の来店日」という列を横に足していく形です。来店が10回になったら列が10本になり、11回目で行き止まります。

2つ目は、1件を見分ける鍵を何にするかです。氏名は鍵になりません。同姓同名がいますし、姓が変わることもあります。電話番号は鍵として使えますが、家族で同じ番号を使っている場合に重なります。素直なのは、連番の顧客IDを1列持つことです。1から順に振るだけで、後から何があっても揺らぎません。

3つ目は、シートを何枚にするかです。予約制のお店なら、顧客の台帳、来店の記録、集計の3枚が基本になります。メニューの一覧やスタッフの一覧を別のシートに持つと、入力規則のリストとして使えて、表記のゆれが消えます。シートを増やすことを恐れる必要はありません。1枚に詰め込むほど、後から扱いにくくなります。

列は分けられるところまで分ける

列の設計で最も効くのが、1つの列に1種類だけ入れるという原則です。分けておけば後から結合できますが、まとめて入れたものを後から分けるのは手間がかかります。

氏名は、姓と名を分けるかどうかで迷います。予約制のお店で姓だけで呼ぶ運用なら分けたほうが便利ですし、フルネームで扱うなら1列でも困りません。分けるかどうかより大事なのは、ふりがなの列を別に持つことです。電話で予約を受けるとき、漢字が分からないお客さまをふりがなで探せると、待たせる時間が変わります。ふりがなを氏名のセルに括弧書きで入れると、探せなくなります。

住所は、商圏を知りたいなら都道府県と市区町村を分けます。DMを送らないお店なら、1列にまとめても構いません。ただし、郵便番号は必ず別の列にします。番号で並べ替えると、地域ごとにまとまります。

電話番号は、ハイフンを入れるか入れないかを店として1つに決めます。混在すると検索で半分が漏れます。入力規則で半角の数字とハイフンだけを許す設定にしておくと、全角で入る事故が防げます。

日付は、必ず日付として入れます。「2026/9/22」と打てば日付になり、「9月22日ごろ」と打つと文字列になります。文字列になった日付は、並べ替えても順番が狂い、経過日数の計算もできません。初回来店日と最終来店日の2列は、どちらも日付形式で持ちます。

金額は数値で入れます。「3,000円」と文字を付けて入れると文字列になり、合計が出せません。見た目に円を付けたいなら、表示形式で付けます。

複数の値が入りうる項目は、列を分ける形か、別のシートに逃がす形にします。たとえば「利用したメニュー」は来店ごとに変わるので、台帳ではなく来店記録の側に置きます。台帳に置くなら「よく頼むメニュー」のように、1つだけ入る形に言い換えます。

表をテーブルに変換して名前を付ける

列が決まったら、入力する範囲を選んでテーブルに変換します。この操作をしておくと、3つのことが自動になります。行を足したときに書式が下へ広がること、数式が下へ複製されること、列の名前にフィルターのボタンが付くことです。

テーブルのデザインはエクセル上にある「テーブルデザイン」から変更できます。これで顧客情報のデータベース化は完了となります。 出典: flexcrm.jp

テーブルには名前を付けます。既定では「テーブル1」のような名前が付きますが、「顧客台帳」「来店記録」のように中身が分かる名前に変えておきます。別のシートから参照するときに、範囲の指定がずれなくなるのが利点です。範囲を「A2からA500まで」のように書いて参照していると、501行目を足したときに参照から漏れます。テーブル名で参照していれば、足した行も自動で範囲に入ります。

このとき、表の上に飾りの行を置かないようにします。タイトルや説明を上に積むと、1行目が列の名前ではなくなり、テーブルへの変換がうまくいきません。タイトルを入れたい場合は、別のシートに置くか、シート名で代用します。

台帳と履歴を鍵でつなぐ

データベースらしい形になるのは、シートを2枚に分けて鍵でつないだ時点からです。

台帳のシートは1行1人で、顧客ID、氏名、ふりがな、電話番号、メールアドレス、住所、初回来店日、担当スタッフ、備考が並びます。来店記録のシートは1行1回で、来店日、顧客ID、メニュー、料金、担当スタッフ、施術のメモ、次回の予定が並びます。この2枚に共通して入っているのが顧客IDで、これが鍵になります。

来店記録のシートでは、顧客IDを入れると氏名が出る列を1つ作ります。XLOOKUP関数かVLOOKUP関数で、台帳のテーブルから引きます。名前を毎回手で打つと、「山田太郎」と「山田 太郎」が別人として扱われ、記録が2つに割れます。引いてくる形にすれば、この事故が構造的に起きなくなります。

台帳の側には、来店記録から計算する列を作ります。来店回数はCOUNTIF関数で、その顧客IDが来店記録に何回現れるかを数えます。最終来店日はMAXIFS関数で、その顧客IDの来店日のうち最も新しいものを取ります。累計の売上を出したいならSUMIF関数です。どれも手で書き換える必要がなく、来店記録に1行足した瞬間に更新されます。

手で書き換える運用にしていると、忙しい日に書き換えを飛ばした行が残り、その行だけ古い情報のまま置き去りになります。数字が1つでも古いと、台帳全体が信用されなくなります。計算できるものは計算させる、というのがこの設計の要点です。

探しやすくする4つの仕掛け

列が整った表には、探すための仕掛けを4つ入れておきます。

1つ目が、ウィンドウ枠の固定です。1行目を固定しておくと、下へスクロールしても列の名前が見えます。列が多い台帳では、これがないと何列目を見ているか分からなくなります。

2つ目が、フィルターです。テーブルに変換すれば自動で付きます。担当スタッフで絞る、初回来店日の年で絞る、備考が空欄でない行だけ出す。この操作が数秒で済むかどうかで、台帳を開く回数が変わります。

3つ目が、条件付き書式です。最終来店日から今日までの日数を出す列を1つ作り、その日数が90を超えた行に色を付けます。開いた瞬間に、声をかける相手が目に入ります。電話番号の列に「重複する値」の書式を設定しておくと、同じ番号を入れた瞬間に色が付き、同一人物の二重登録が防げます。

4つ目が、入力規則です。担当スタッフとメニューの列は、別シートに置いた一覧からリストで選ぶ形にします。自由に打てる欄を減らすほど、表記のゆれが減り、絞り込みの精度が上がります。新しいスタッフが入ったら一覧に1行足すだけで、全部の入力欄に反映されます。

この4つを設定するのに要る時間は、合わせて 15分 ほどです。設定していない台帳は、探すのに時間がかかるため開かれなくなり、開かれない台帳は更新されなくなります。

集計はピボットテーブルに任せる

来店記録のシートが整っていれば、集計を手で作る必要はありません。ピボットテーブルを1つ作れば、見たい切り口を後から変えられます。

月ごとの来店件数を見るなら、行に来店日、値に件数を置きます。日付をグループ化すれば月単位でまとまります。メニュー別の売上なら、行にメニュー、値に料金の合計です。スタッフ別の担当件数なら、行に担当スタッフ、値に件数です。同じ元データから、この3つが数分で出ます。

数式で集計の表を組むこともできますが、後から切り口を変えたくなったときに作り直しになります。ピボットテーブルなら、行と列に置くものを入れ替えるだけで済みます。元データに行を足したら、更新のボタンを押すだけで反映されます。

集計を見る日を決めておくことも、設計の一部です。月に1度、来店件数とメニュー別の売上、そして90日以上来ていないお客さまの一覧を出す。この3つを見る日を決めておかないと、入力だけが続いて目的を失います。作る前に「何を見るか」を決めておく話は、ここに戻ってきます。

必要な行だけを取り出す関数

フィルターで絞り込むと、元の表そのものが絞られた状態になります。印刷したい、別のシートに一覧として残したい、という場合は、取り出す関数を使ったほうが扱いやすくなります。

FILTER関数は、条件に合う行だけを別の場所に並べます。来店記録から、特定の顧客IDの行だけを取り出す。台帳から、最終来店日が90日より前のお客さまだけを一覧にする。元の表を触らずに、別のシートに結果だけが並ぶので、元データが汚れません。条件を書き換えれば、その場で結果が変わります。

SORT関数は、取り出した結果を並べ替えます。FILTER関数の結果をそのままSORT関数で囲めば、条件に合う行が並んだ状態で出てきます。手で並べ替える操作が要らなくなるため、行の組み合わせが崩れる事故も起きません。

UNIQUE関数は、重複を取り除いた一覧を作ります。来店記録のメニューの列から、実際に使われているメニューの一覧を出す。担当スタッフの列から、記録に登場するスタッフの一覧を出す。入力規則のリストを作るときの元になります。

この3つは比較的新しい関数で、古いエクセルでは使えません。使えない場合は、フィルターの結果をコピーして貼る形か、重複の削除の機能を使う形で代えられます。重複の削除は元の表を直接書き換えるため、実行する前に必ずファイルのコピーを取ります。押してしまってから元に戻せなくなった、という相談が最も多い機能です。

関数を増やすほど便利になりますが、直せる人が減ります。台帳を長く使うなら、COUNTIF、SUMIF、MAXIFS、XLOOKUPの4つを基本にして、FILTERとSORTを必要なところだけに足す、という配分が無理がありません。

複数人で使うときに設計で決めること

触る人が2人以上になると、設計の判断が変わります。1人で使う前提の台帳をそのまま共有すると、入力のずれから崩れていきます。

まず、置き場所をクラウドにして、同時に開けるようにします。手元のファイルを共有フォルダに置く形にすると、後から開いた人が読み取り専用になります。その人は手元のメモに書いて、後でまとめて転記することになり、転記が抜けたところから記録に穴が空きます。

次に、自由に打てる列を減らします。担当スタッフ、メニュー、来店のきっかけといった列は、別シートの一覧からリストで選ぶ形にします。人によって書き方が違うと、絞り込みも集計も合わなくなります。同じメニューが「カット」と「カットのみ」に分かれていると、集計の数字が2つに割れます。

3つ目に、誰がどの列を書くかを決めます。受付が書く列と、施術者が書く列を分けておくと、書き忘れの責任が明確になります。全員が全部書ける状態にすると、結果として誰も書きません。

4つ目に、備考の書き方の型を決めます。事実だけを短く書く、評価や感想は書かない、という2点を共有します。お客さまから開示を求められたときにそのまま見せられる内容にとどめておくと、後から困りません。

5つ目に、消す操作を誰がするかを決めます。行を削除する、列を削除する、重複の削除を実行する。この3つは元に戻すのが難しいため、1人に限っておくほうが安全です。

顧客台帳は氏名と連絡先が揃った個人情報の集まりです。件数に関わらず、事業として扱う以上は個人情報の保護に関する法律の対象になります。取り扱いの決まりや相談先は個人情報保護委員会の公開資料で確かめられます。辞めたスタッフの共有フォルダの権限を消すところまでを、退職の手続きに入れておきます。

壊れないようにするための注意

エクセルで作ったデータベースが壊れる原因は、ほぼ決まっています。

行や列を挿入したときに数式の参照がずれるのが1つです。テーブルとテーブル名を使っていれば、この事故はほとんど起きません。範囲をセル番地で直書きしている数式が残っていると、そこだけずれます。作った後で参照を見直し、テーブル名に置き換えておきます。

シート名を変えたときに参照が切れることもあります。シート名は最初に決めて、以後変えないのが安全です。どうしても変えるなら、参照している数式を先に洗い出します。

並べ替えで行がばらばらになる事故もあります。1列だけを選んで並べ替えると、その列だけが動いて他の列は元の位置に残ります。氏名と電話番号の組み合わせが崩れると、復元はほぼできません。テーブルに変換しておけば、列の見出しから並べ替える操作になるため、この事故は起きません。

ファイルが増えるのも壊れる形の1つです。「顧客台帳_最新.xlsx」と「顧客台帳_最新2.xlsx」が並ぶと、どちらが本物か分からなくなります。バックアップは別名ではなく、別の場所に日付を付けて置きます。手元とクラウドの両方に1か所ずつ、と決めておくと迷いません。

最後に、作った本人以外が直せない状態を作らないことです。凝った数式を組むほど、その人が辞めた後に誰も触れなくなります。COUNTIF、SUMIF、MAXIFS、XLOOKUPの4つで足りる範囲に収めておくと、引き継ぎが可能な台帳になります。

紙やメモから移すときの進め方

すでに紙の台帳やカルテがあるお店では、移す作業そのものが最初の山になります。進め方を決めておかないと、途中で止まって紙と表の両方が中途半端になります。

対象を絞るのが最初です。全件を移すことを目標にしないでください。直近1年、または2年のあいだに来店のあったお客さまだけを移し、それより古いものは紙のまま保管します。必要が出たときに1件ずつ移せば足ります。何年も来ていないお客さまの記録を入れても、絞り込みの邪魔になるだけです。

次に、入力の順番を決めます。氏名とふりがなと電話番号だけを先に全件入れ、住所や備考は後から足すほうが、途中で止まったときの損が小さくなります。1人ずつ全部の列を埋める形で進めると、半分で止まったときに使える情報が半分しか揃いません。

入力の量は、1日あたりの目安を決めます。営業時間外に30分、というように枠で区切ると続きます。300件 程度なら、1日30件のペースで10日ほどで終わります。一気に終わらせようとすると、打ち間違いが増えます。

移し終わったら、必ず件数を照合します。紙の台帳の枚数と、表の行数が一致するかを数えます。一致しないときは、どこかで1行飛ばしているか、二重に入れています。電話番号の列に重複の書式を設定しておけば、二重の分はその場で見つかります。

紙を捨てるかどうかは、移し終わってすぐに決めなくて構いません。半年ほど表だけで運用してみて、紙を見に行く回数がゼロになったところで処分を考えます。処分するときは、個人情報が読める状態で捨てないようにします。

エクセルで越えられない線

ここまでの設計をすれば、エクセルはかなり実用的なデータベースになります。行数の上限は1,048,576行なので、件数で足りなくなることもありません。それでも、予約制のお店には越えられない線が2つ残ります。

1つは、予約が入ったことをエクセルは知らないことです。予約を受けたら、ファイルを開き、お客さまの行を探し、来店記録に1行足す。この手数が、予約のたびに人の手として残ります。予約が増えた日ほど抜けやすく、抜けた記録は後から正確には戻りません。

もう1つは、送る操作が残ることです。メールアドレスが台帳に入っていても、前日のリマインダーや来店後の御礼を送るのは人です。1日の予約が 10件 を超えたあたりから、手で送る運用は続かなくなります。

この2つは、表計算ソフトの側を工夫しても解けません。予約の受付と台帳が別の場所にある、という構造から生まれる手数だからです。

予約と履歴を同じ場所に置く形

越えられない線に当たったときの選択肢は、顧客管理の専用ツールを足すか、予約の受付と台帳が同じ場所にある仕組みに移すかの2つです。専用ツールを足すと、予約の情報をそこへ入力する作業が新たに生まれます。二重入力が発生する形は、続きません。

このサイトで扱っている予約システムでは、予約が入った時点でお客さまの情報が台帳に残り、来店ごとの施術メモを同じ画面に足せます。決済が必須でない業態に合わせて、予約の受付から次の来店までを一続きにしているため、当日精算や現金払いで回しているお店でも、予約完了メール、前日のリマインダー、来店後の御礼メールを自動で流したうえで、その履歴を台帳から見られます。エクセルの設計で言えば、台帳と来店記録の2枚が最初からつながっていて、予約が入るたびに来店記録の行が自動で増える形に近いです。

台帳にどの項目を持てるか、施術の記録をどこまで残せるかはできることにまとめています。プランごとに扱える件数と月額は料金で確かめられます。美容室、整体、ネイル、教室で入れる項目の違いは業種ごとの使い方に分けてあり、実際の台帳と予約の画面はデモを見るからログインなしで触れます。他の予約システムとの違いは他社との比較に、エクセルからの取り込みやデータの持ち出しはよくある質問に載せています。

エクセルで設計をやってみることには、それ自体の価値があります。1行が何を表すか、鍵をどれにするか、どの列を分けるか。この3つを自分で決めた経験があると、どの道具に移るときでも、何を記録すべきかが分かった状態から始められます。まずは台帳と来店記録の2枚を作り、顧客IDでつないで、条件付き書式で90日を超えた行に色を付けてみてください。使い始めてから足りないと感じた場所が、次に手を入れるところです。

Q1. エクセルの表とデータベースは何が違うのですか？

データベースとして扱うには、1行目が列の名前だけ、2行目以降が1行1件、途中に空行や小計を挟まない、1つの列に1種類の情報だけ、セルの結合を使わないという5つの条件が要ります。この形になっていれば並べ替え、絞り込み、集計、他シートからの参照がすべて素直に動きます。見た目を整える作業は最後に回します。

Q2. 顧客を見分ける鍵には何を使えばよいですか？

連番の顧客IDを1列持つのが素直です。氏名は同姓同名があり、結婚などで変わることもあるため鍵になりません。電話番号は家族で共有している場合に重なります。顧客IDがあれば、来店記録のシートからXLOOKUP関数で名前を引けるようになり、手入力による表記のゆれもなくなります。

Q3. 来店の履歴は台帳と同じシートに入れてよいですか？

分けてください。台帳は1行1人、来店記録は1行1回にして顧客IDでつなぎます。台帳に「2回目の来店日」「3回目の来店日」と列を横に足していく形にすると、来店が増えるほど列が増え、集計もできなくなります。来店回数はCOUNTIF関数、最終来店日はMAXIFS関数で自動的に出す形にします。

Q4. 作ったデータベースが壊れるのを防ぐには何をすればよいですか？

表をテーブルに変換して名前を付け、数式の参照をセル番地ではなくテーブル名にします。これで行や列を足したときのずれがほぼ防げます。並べ替えは列の見出しから行い、1列だけを選んで並べ替えないようにします。数式はCOUNTIF、SUMIF、MAXIFS、XLOOKUPの範囲に収め、他の人でも直せる状態にしておきます。