エクセルの知識

エクセルで顧客管理!ボタンひとつで希望のデータを検索する方法

自営業の人や個人事業主の人は、顧客管理をエクセルで行っている人も多いかと思います。

エクセルで顧客管理のメリット

  • パソコンにエクセルが入っていれば費用ゼロで使える
  • エクセルは使い慣れているので、誰でも簡単に利用できる

通常、エクセルの顧客管理表に登録されたデータは

  1. エクセルのフィルター機能を利用する
  2. エクセルの検索機能(コントロールキー+F)で検索する

この2つのいずれかを利用して検索しますが、この検索方法だと、希望する条件で絞り込めなかったり、誤って登録済みのデータを消してしまう危険性もあります。

そこで今回は、顧客登録と顧客検索をカンタンに行える顧客管理システムを作成してみました!

検索条件を入力してボタンを押すだけで、希望のデータを抽出することが可能となりますよ。

顧客管理システムの概要

顧客情報を入力して登録するシート

顧客登録

登録データを格納する顧客マスター

顧客マスター

登録済みの顧客マスターからデータを検索する抽出シート

顧客検索

検索条件は「あいまい検索」に対応させました!

氏名や住所の一部が分かれば、顧客データを検索することが可能です。

データの整合性を担保したり、複雑な条件で検索するならアクセスなどのデータベースソフトを利用するのが良いのですが、簡易的な顧客管理ならエクセルでも実現可能です。

自営業や個人事業主の方でしたら、この顧客管理システムでも十分対応できるかと思います。

このエクセル版顧客管理システムはVBAを使って作成しているので、同じコードを書けば自作も十分可能ですが、

・エクセルのVBAに不慣れな方
・すぐに顧客管理システムを利用したい方

のために、ダウンロードデータも用意しております。

購入特典

ご購入者さまからの要望を受け、すべての項目で検索できるようにバージョンアップしました!

ご購入頂けた方の特典として、顧客IDの自動採番や項目の追加など、簡易なカスタマイズは無料で対応致します。

シートが追加となるような大幅な改修は対象外となる場合もあるので、カスタマイズのご希望がある方は、購入前にご相談下さい!

エクセル顧客管理
検索項目をバージョンアップ!

顧客管理システムのダウンロード

エクセルで顧客管理を行うための前提条件

エクセルは表計算ソフトなので自由自在に値を入力することができますが、顧客管理表としてデータを管理し、VBAで検索を行う場合は、データベースの規則に沿って作成する必要があります。

とはいえ、簡易的な顧客管理表なら次の2つのルールを守ればOKです。

  1. 顧客データは縦方向に追加する
  2. 顧客データに空白行を作らない

このルールを守らなければならない理由はいくつもありますが、説明が長くなってしまうので今回は割愛します。

データの整合性が担保できないと、プログラムでは検索できないと考えて下さい。

エクセル版顧客管理システムの作成手順

今回作成する顧客管理表は、次の3つのシートから成り立っています。

  • 顧客データを登録するシート(シート名:input)
  • 顧客データを格納するシート(シート名:data)
  • 検索条件を入力し、抽出結果を表示するシート(シート名:select)

ポイント

抽出結果を表示するシートは、検索条件に合致する顧客データをdataシートから見つけ出してコピーしています。

そのため抽出結果を上書きしたり、消してしまっても、元データとなる顧客データは更新されない仕様です。

なお、各ボタンに記述したVBAでは、シート名称を判別して処理を行っています。

シート名は 「input」、 「data」、「select」に必ず変更して下さい。

顧客情報を管理するシートを作成する

今回、顧客管理表で管理する項目は以下の通りです。

顧客管理表で管理する項目

  1. 顧客ID
  2. 顧客氏名(漢字)
  3. 顧客氏名(カナ)
  4. 性別
  5. 郵便番号
  6. 住所
  7. 電話番号
  8. 生年月日
  9. Mail
  10. 退会
  11. 備考

顧客データを管理するシート(シート名:data)の1行目に、次の項目を入力しましょう。

なお顧客データの場合、退会や解約というケースも考えられますが、DELETEキーでエクセルからデータを消してしまうと空白の行が発生してしまいます。

不要な行をすべて削除すれば良いのですが、今回作成する顧客検索システムでは、顧客を管理する表に「削除」や「退会」を識別する項目を設定。

削除の印をつける方式を採用することにします。

この方法は「論理削除」と呼ばれており、一般的なデータベースではよく利用される方法になります。

顧客データの場合、退会の取り消しや、再入会するケースは頻繁に発生します。

そのためカンタンにデータを復活できる「論理削除」方式が便利です。

これに対して、データをすべて消してしまう方法は「物理削除」と呼ばれており、データを完全に消去してセキュリティを確保したい場合などに利用します。

ここまでを踏まえたうえで、顧客管理表には以下のようなテストデータを入力してみました。

※氏名や住所、電話番号は実在しない仮データです

検索結果を表示するシートを追加する

では次に、顧客管理表を検索するためのシートを作成します。

シート「select」の1行目と2行目には、各種検索条件を入力するセルと、検索を実行するボタンを配置するので、4行目に顧客管理表「data」のヘッダ項目をコピーしましょう。

検索条件を入力するセルと検索用のボタンを設定する

抽出結果を表示するシート「select」の1行目、2行目に、検索対象となる条件を入力するセルと、実際に検索を行うボタンをセットします。

※検索条件を入力するセルは、分かりやすいように黄色にしてあります。不要なら元の色に戻して大丈夫です!

ボタンの追加方法

メニューより「開発タブ」を選択。

「挿入」、「フォームコントロール」よりボタンを選択してエクセルに配置します。

ボタンを配置する際に「マクロの登録」画面が開きますが、今は何も記入せずに「OK」で閉じておきましょう。

作成したボタンを右クリックすると、ボタンのサイズを変えたり移動することができるので、各検索条件の入力セルの隣に配置します。

「テキストの編集」で名称を変えることができるので、ボタン名を「検索」に変更します。

注意ポイント

エクセルの「開発タブ」は、デフォルトの状態では非表示となっています。

「開発タブ」が表示されていない人は、ファイル→オプションから「Excelのオプション」を開いて、「リボンのユーザー設定」の下にある「開発」チェックボックスをオンにすれば表示されるようになります。

1つボタンが配置されたら、検索する項目の数だけボタンをコピーして、それぞれの検索条件の隣に配置。
セルに罫線や色をつけて見た目を整えておきます。

現時点での「select」のシートはこのようになりました。

今回作成するエクセルの顧客管理表は、作成したボタンにVBAを記述するのでマクロ有効ブック(拡張子xlsm)である必要があります。


VBAを記述する前に「名前をつけて保存」を選んで、拡張子を「xlsm」に変更しておきましょう。

各検索ボタンに抽出を行うVBAを記述する

では次に、先ほど作成した検索ボタンに検索用のVBAを記述していきます。

step
1
顧客ID検索用マクロの作成

作成したシート「select」のシートを開いて、キーボードの「ALT」+ F8を押すとマクロの入力画面が開きます。

マクロ名に「顧客ID検索」と入力して作成ボタンを押しましょう。

VBAの入力画面が起動します。

「Sub 顧客ID検索()」から「End Sub」の間に、以下のVBAを記述して保存します。

VBAのコードはコピペすればOKです!

step
2
顧客ID検索用マクロの割り当て

「select」のシートに作成したボタンに、VBAを記述したマクロを割り当てます。

顧客ID右側の検索ボタンを右クリック、マクロの登録を選択。

作成したVBAのマクロ「顧客ID検索」を選んでOKを押します。

これで顧客ID検索ボタンに、作成したマクロが実装されました!

step
3
顧客ID検索用ボタンの動作確認

記述したVBAが正しく機能するかを確認してみましょう。

顧客IDに「01」と入力して、検索ボタンを押してみてください。

顧客マスター(dataシート)より、検索条件に合致した顧客情報が抽出されました!

顧客ID「01」で検索すると、11件の顧客データが検索されますが、これは「顧客ID」の検索条件をあいまい検索にしてあるためです。

顧客IDが「ID-0001」だけを抽出したい場合は、検索条件に「ID-0001」もしくは「0001」と入力すればOKです。

VBAの内容を変更すれば、検索条件を完全一致にすることも可能ですが、今回は苗字や住所の一部しか分からなくても検索できるように、部分一致にしました!

それぞれの検索条件に合致するマクロの作成とボタンへの割り当て

同様の手順で、「顧客氏名カナ」、「住所」、「電話番号」、「退会」のマクロを作成。
それぞれの検索ボタンに作成したマクロを割り当てます。

検索条件を入力するセルの位置が異なるので、以下のコードをコピペして設定して下さい。

顧客氏名カナ検索

住所検索

電話番号検索

退会検索

データクリア

顧客検索シートは以上で完成です!

顧客データ登録用のシートを作成する

管理したい顧客データは、dataシートに直接記載しても良いのですが、入力済みの顧客データを間違って更新しないためにも、専用の登録シートを利用するようにしました。

シート名:inputに、以下のレイアウトを作成し、登録用ボタンを配置しておきます。

顧客登録シート

ポイント

  • 顧客IDが未入力で登録ボタンを押すと、エラーメッセージが表示
  • 顧客氏名(漢字)を入力すると、顧客氏名(カナ)に自動でフリガナ表示
  • 入力項目以外のセルをロック
  • 顧客IDは、顧客データを登録する際の位置を特定するために使用するため、入力必須にしています。
    そのため、顧客IDが未入力で登録ボタンを押すと、エラーメッセージが表示されて登録できません。

    顧客登録

    また、 入力の手間を軽減するために、顧客氏名(カナ)には、PHONETIC関数を用いて、顧客氏名(漢字)が自動でフリガナ表示されるようにしました。

    ※カナ氏名はまれに間違って変換されることもあるので、その際は手動で訂正して下さい。

    さらに、顧客データとして管理する項目名称(オレンジのセル)は、間違って消されないように、入力項目以外のセルにロックをかけてあります。

    ロックを解除するパスワードには「1234」に設定してありますので、ご自由に変更して下さい。

    顧客登録ボタンのVBA

    以上で、エクセル版顧客管理システムの完成です!

    エクセル版顧客管理システムのまとめ

    エクセル版顧客管理の特徴

    • 検索条件はすべてあいまい検索に対応
    • 苗字だけ、市町村での検索などを、ボタンひとつでカンタンに実行できる
    • 検索結果を消しても、元データには影響しない
    • 削除や退会した顧客の復活が可能
    • 必要なソフトはエクセルのみ。費用ゼロで使える

    ちなみに「顧客対応履歴」、「宛名ラベル印刷」などの機能を実装するには、大がかりなプログラムを組む必要があります。

    作成にかかる時間やメンテナンスの手間などを考えると、エクセルでの運用は実用的とは言えないでしょう。

    総合的な顧客管理システムを使いたいなら、データベースソフトである「アクセス」の利用がオススメです!

    今回作成した顧客管理システムは、こちらからダウンロードすることが出来るようしました。

    購入特典

    ご購入者さまからの要望を受け、すべての項目で検索できるようにバージョンアップしました!

    ご購入頂けた方の特典として、顧客IDの自動採番や項目の追加など、簡易なカスタマイズは無料で対応致します。

    シートが追加となるような大幅な改修は対象外となる場合もあるので、カスタマイズのご希望がある方は、購入前にご相談下さい!

    顧客管理システムのダウンロード

    人気の投稿

    1

    会員制のサイトにログインするために必要なパスワード、どうやって管理していますか?エクセルやメモ帳などよりも便利で使いやすいソフトを作成しました。

    2

    自営業の人や個人事業主の人は、顧客管理をエクセルで行っている人も多いかと思います。 エクセルで顧客管理のメリット パソコンにエクセルが入っていれば費用ゼロで使え ...

    顧客管理 3

    ちょっとした顧客管理や請求書などの作成にエクセルを利用している会社は多いと思います。 特に自営業や個人事業を営んでいる人、まだ顧客数が少ない中小企業などでは、導 ...

    -エクセルの知識
    -,

    Copyright© ビジツールラボ , 2020 All Rights Reserved.