エクセル顧客管理データベースの作り方【壊れない情報システム設計】
はじめに
Excel（エクセル）
- 「あとから列を追加したら使いづらくなり、
リンクも切れてしまった」 - 「フィルターや集計がうまく機能しない」
- 「入力方法が人によってバラバラになっている」
Excelで顧客管理を行う場合でも、
この記事では、
- 顧客管理データベースの正しい設計手順
- 「1行1データ」
「顧客情報と履歴を分ける」 理由 - テーブル化すると何がどう便利になるのか
- 入力・修正・閲覧・分析まで一貫して使える構成
を、
「とりあえず表を作る」
Excelのバージョン
本記事は、
環境によっては、
※ 本記事では「Excel（エクセル）
エクセル顧客管理データベースの作り方【基本手順】
Excelで顧客管理データベースを作成する際の、
STEP1 : 管理項目を設定する（まず顧客情報を整理）
STEP2 : 顧客データを入力する（テーブル化のルールに従う）
STEP3 : テーブル機能でデータベース化する
STEP1：管理項目を設定する（まず顧客情報を整理）
データベースの基本は「表（テーブル）
記録すべき顧客情報は、
【BtoBの場合】
- 顧客ID（主キー）
- 企業名 住所 郵便番号 電話番号 担当部署 担当者名 メールアドレス など
【BtoCの場合】
- 顧客ID / 会員番号（主キー）
- 氏名 住所 電話番号 メールアドレス 性別 生年月日 など
主キーとは
主キー とは、
1件のデータを一意に識別するための値で、
連番や一意な文字列が使われることが一般的です。
主キーは、
並び替え後に元の順序へ戻す際にも利用されます。
顧客情報 と 履歴情報 は分けて考える べきです。
STEP2：顧客データを入力する（テーブル化のルールに従う）
管理項目が決まったら、
基本ルールは次の通りです。
- 横（列）
方向に項目を配置する。 - 1行に見出し（項目名）
、 その下以降にデータを記入する。 - 1行につき1件のデータを入力する。
- セル結合は使用しない。
これらのルールを守らないと、
※ 本記事で使用するサンプルデータはテスト用の架空のデータであり、
ルールは他にもいくつか存在します。
列幅の自動調整
複数列の幅は一括で自動調整できます。
- 調整したい複数の列を選択する（Shift を押しながら列の端をクリック）
。 - 選択した列のいずれかの境界をダブルクリック。
STEP3：テーブル機能でデータベース化する
シートに必要な項目とデータを入力できたら、
テーブル化の方法
- テーブルにするセルを選択（範囲選択も可）
。 - 挿入タブのテーブル（Ctrl + t）
を選択。
テーブル化することで、
【テーブル化のメリット】
-
スタイリングが簡単 :
テーブル化すると自動で装飾が適用されます。
罫線を1セルずつ設定する必要がなく、手間をかけずに見た目を整えられます。 -
フィルターボタンが標準搭載 :
テーブル化すると、
フィルターボタンが自動的に表示されます
（不要な場合はOFFにすることも可能）。
フィルターボタンから、フィルタリングや並び替え（ソート） をすぐに実行できます。 -
構造化参照（テーブル参照）
が使える : テーブル内のセルは、
構造化参照（テーブル参照） という形式で参照できます。
この形式で範囲指定しておくと、データを追加した際に参照範囲が自動で拡張されます。 また、
別ブックから外部参照した場合でも、 リンクが切れにくくなります（Power Query使用時） 。
テーブル化した後は、
テーブル名の設定方法
- テーブル内のセルを選択。
- テーブルデザインタブのテーブル名欄にテーブル名を入力（例：M_顧客）
。
顧客管理を効率化するエクセルの便利機能
作成した顧客管理データベースを活用するうえで、
用途や場面に応じて、
-
最初に設定 しておきたい必須機能（システム拡張）
: - データの入力規則, 条件付き書式, ウィンドウ枠の固定, グループ化
-
データ入力・修正 を効率化する操作テクニック :
- 移動・行追加, コピー＆ペースト, ドロップダウンリスト
-
データ閲覧・確認 のための機能 :
- フィルター, 並び替え・ソート, 検索
この章で紹介するのは、
1. 最初に設定しておきたい必須機能（システム拡張）
データベースを作成した後、
データの入力規則：入力値を制限する
データの入力規則 は、
たとえば、
【「顧客ID列で重複不可」
- 入力値の種類 : ユーザー設定
- 数式 : =COUNTIF($A:$A,A2)=1
- エラーメッセージ
- タイトル : 「重複禁止」
- エラーメッセージ : 「重複しないIDを入力してください。
」
- タイトル : 「重複禁止」
データの入力規則 は、
データの入力規則の主な機能
- 入力できる セルの値を制限 する
- ドロップダウンリスト から選択して入力できるようにする
- セル入力時に メッセージを表示 する
制限できるのはセルへの直接入力のみ
データの入力規則 で制限できるのは、
コピー＆ペーストによる入力にも対応するには、
条件付き書式：規則違反セルをハイライトする
条件付き書式 は、
たとえば、
【「企業名列内の重複をハイライト」
- ルール : 重複する値
- 書式 : 濃い黄色の文字、黄色の背景
「同一列内の重複をハイライト」
条件付き書式の設定は、
これを活用すれば、
手順は次のとおりです。
- 1つのテーブル列に「同一列内の重複をハイライト」
する設定を行う。 - 設定した列をコピー。
- 別の列に書式を貼り付け。
- 右クリック > 貼り付けオプションの書式設定
- または Ctrl + Alt + v > 書式
ウィンドウ枠の固定：見やすさを向上させる
ウィンドウ枠の固定 は、
【上1行とA〜B列を固定する例】
テーブルの見出しはデフォルトで固定される
最近のExcelでは、
見出し行はデフォルトで固定表示 されます
（厳密には、
ただし、
グループ化：列を一時的に非表示にする
グループ化 は、
列列 だけでなく 行行 に対しても設定できますが、
【担当部署列を折りたたむ例】
「セルの非表示」
列を見えなくする方法として「セルの非表示」
非表示箇所が分かりづらく、
テーブルの 見栄え見栄え を重視しすぎると、
見栄え見栄え と 再利用性再利用性 を両立させる考え方については、
2. データ入力を効率化する操作テクニック
作成したデータベースに
移動・行追加
テーブル内の移動 や 新規行の追加 を行うためのショートカットです。
- 下 / 右へ移動：Enter / Tab（Shift を同時に押すと逆方向）
- 新規行の追加：Tab（テーブルの右下端で）
コピー＆ペースト
選択した範囲のデータをクリップボードに保存し、
ショートカット：Ctrl + c（コピー）
ドロップダウンリスト（プルダウン、
ドロップダウンリストから選択してセルに入力できます。
ショートカット：Alt + ↓
セル値の置換（セル値の一括変換）
置換機能を使うと、
事前にセル範囲を選択しておくことで、
ショートカット：Ctrl + h（ダイアログ表示）
データ入力や修正時に役立つテクニックやショートカットは、
3. データ閲覧・確認するための機能
作成したデータベース内の
フィルター
フィルター機能 は、
並び替え・ソート
並び替え・ソート機能 も、
検索
検索機能 を使うと、
ショートカット：Ctrl + f（ダイアログ表示）
作成した顧客管理データベースの活用方法
データベースは 閲覧するため だけだけ のものではありません。
- マスタデータとして再利用する方法
- 顧客分析に活用する方法
マスタデータとして再利用する方法
データベースは、
最新の情報を参照することで、
作成したデータベースを利用する方法は、
関数とPower Query（パワークエリ）
-
関数 :
同一ブック内で参照する場合に主に使用します。
特に、取得するセルが単一の場合に有効です。 -
Power Query :
別ブックから参照する場合に主に使用します。
同一ブック内であっても、取得するセルが複数ある場合には便利です。
顧客分析に活用する方法
顧客管理データベースを活用することで、
データベース設計の考え方に沿って、
- 顧客情報と履歴情報を、
それぞれ別のテーブルとして管理する。 - 履歴情報をもとに 顧客分析 を行い、
顧客ごとの評価（顧客ランクなど） を算出する。 - 算出した顧客評価を、
履歴情報に反映する。
ここでは、
エクセルで顧客管理データベースを作るメリット
Excelで顧客管理データベースを作成する主なメリットは、
- ① 導入コスト：環境構築が不要（コストゼロですぐに始められる）
- ② 教育コスト：多くの人が使い方に慣れている
- ③ カスタマイズ性：できることの範囲が広い
① 導入コスト：環境構築が不要
多くの企業のPCには、
必要な機能を後から追加する場合でも、
細かな調整を柔軟に行える点も利点です。
② 教育コスト：多くの人が使い方に慣れている
入力方法やカーソル操作など、
まったく新しいツールを導入する場合、
使い方を覚えてもらうための 教育コスト が発生するだけでなく、
導入時に 心理的な抵抗感 を生んでしまう可能性もあります。
③ カスタマイズ性：できることの範囲が広い
Excelは表計算ツールとして優れているだけでなく、
難易度は上がりますが、
さらに対応できる範囲が広がります。
「あと少しだけ便利にしたい」
エクセル顧客管理のデメリットと注意点
Excelには 苦手な作業や明確な限界 もあります。
Excelで顧客管理データベースを作成する主なデメリットは、
- ① セキュリティ対策が難しい（ファイル管理）
- ② 誤操作・削除リスクがある（自動バックアップ非搭載）
- ③ 属人化や維持管理コストが発生するリスクがある
- ④ 大容量データでは動作が重くなる
- ⑤ 同時編集や共有、
マルチモバイル対応が弱い
① セキュリティ対策が難しい（ファイルで管理されている）
Excelはデータをファイルとして保存するため、
② 誤操作・削除リスクがある（自動バックアップ非搭載）
Excelには、
「自動回復ファイル保存」
③ 属人化、
Excelで顧客管理データベースを作成する場合、
1テーブル程度のシンプルな構成で、
④ 大容量データでは重くなる
Excelは、
- フリーズしやすくなる
- 計算処理が遅くなる
- 保存に時間がかかる
- 行数が約100万行を超えると保存できない
約100万行超えのデータは、
⑤ 同時編集、
Excelはもともと、
また共有機能も限定的で、
エクセルの弱点を補うための対処方法
Excelで顧客管理データベースを作成する際の弱点を補うために、
- A. 誤操作・削除リスクと共有面に対応 ：クラウドストレージやWeb版を活用
- B. 大容量データに対応 ：Excelブックを分割し、
Power Queryで結合 - C. すべてに一括対応 ：専用ツール（CRM：Customer Relationship Management）
の導入
【Excel顧客管理のデメリットと対策方法】
| No. | デメリット | 対策方法 |
|---|---|---|
| ① | セキュリティ対策（情報漏洩・権限管理の弱さ） | → 専用ツールの導入 |
| ② | 誤操作によるデータ破損・削除リスク | → クラウドストレージの活用 → 専用ツール（CRM） |
| ③ | 属人化・維持管理コスト発生のリスク | → 専用ツール（CRM） |
| ④ | 大容量データによる動作遅延・フリーズ | → Excelブック分割＋Power Queryで結合 → 専用ツール（CRM） |
| ⑤ | 同時編集不可、 | → クラウドストレージやWeb版の活用 → 専用ツール（CRM） |
A. 誤操作・削除リスクと共有面に対応：クラウドストレージやWeb版を活用
「② 誤操作によるデータ破損・削除リスク」
多くのクラウドストレージには
「ファイルの履歴復元機能」
クラウド上に保存したExcelブックであれば、
誤ってデータを書き換えたり削除した場合でも、
過去の状態にさかのぼって復元できます。
また、
Web版Excel を利用できます。
Web版では、
「複数人での同時編集」
※ ただしマクロ/VBA入りの.xlsmファイルなど、
スマホやタブレットの場合は、
B. 大容量データに対応：Excelブック分割＋Power Queryで結合
「④ 大容量データによる動作遅延・フリーズ」
具体的には、
ただし、
C. すべてに一括対応：専用ツール（CRM）
ここまで挙げた、
CRMでは、
データをクラウド上で管理し、
共有機能、
Excelの弱点を補うことができます。
さらに、
どこからでも簡単にデータ入力できるなど、
業務に直結する便利な機能を備えている点もメリットです。
ただし、
まとめ｜エクセル顧客管理データベースを正しく使うために
Excelで顧客管理を行う際は、
小規模な組織であれば、
一方で、
Excelによる顧客管理データベースにも弱点があるため、
「個人に依存したシステムにしたくない」
「共有やマルチデバイス対応にしたい」
のような場合は、
専用ツール CRM の導入も検討するのが良いでしょう。