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

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

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

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

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

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

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

[st-kaiwa1]

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

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

[/st-kaiwa1]

顧客管理システムの概要

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

顧客登録

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

顧客マスター

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

顧客検索
[st-kaiwa1]

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

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

[/st-kaiwa1]

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

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

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

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

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

エクセル顧客管理
検索項目をバージョンアップ!
[st_af id=”407″]

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

エクセルは表計算ソフトなので自由自在に値を入力することができますが、顧客管理表としてデータを管理し、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キーでエクセルからデータを消してしまうと空白の行が発生してしまいます。

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

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

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

[st-kaiwa1]

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

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

[/st-kaiwa1]

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

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

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

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

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

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

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

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

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

[st-minihukidashi bgcolor=”#4FC3F7″ color=”#FFFFFF”]ボタンの追加方法[/st-minihukidashi] メニューより「開発タブ」を選択。

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

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

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

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

注意ポイント

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

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

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

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

[st-kaiwa1]

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


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

[/st-kaiwa1]

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

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

[st-step step_no=”1″]顧客ID検索用マクロの作成[/st-step]

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

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

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

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

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

Sub 顧客ID検索()
 
   '=== 変数宣言 ===
    Dim i As Integer                           '繰り返し処理用
    Dim LastRow1 As Integer                    '顧客マスタのレコード数確認用
    Dim Dcnt As Long                           '抽出結果シート(select)の開始行をセット
    Dim K_id As String                         '顧客IDの退避用
    Dim HitCnt As Long                         '検索条件一致したレコードの数をカウント
 
    '=== 抽出結果シートをクリア(セルの値のみクリアする) ===
    Worksheets("select").Range("A5:K4000").ClearContents
 
    '=== 顧客ID以外の抽出条件をクリア ===
    With Worksheets("select")
         .Cells(2, 2) = ""                       '顧客氏名カナ
         .Cells(1, 5) = ""                       '住所
         .Cells(2, 5) = ""                       '電話番号
         .Cells(1, 9) = ""                       '退会
    End With
 
    '=== 抽出結果カウント件数をクリア ===
    HitCnt = 0
    
    '=== 抽出結果シート(select)の設定開始行をセット ===
    Dcnt = 5
    
    '=== 顧客IDの入力チェック ===
    
    If Worksheets("select").Cells(1, 2) = "" Then
       MsgBox "顧客IDを入力して下さい", vbExclamation, "検索条件なし"
       Exit Sub
    End If
 
    '=== dataシートの最終行を取得 ===
 
    LastRow1 = Worksheets("data").Range("a65536").End(xlUp).Row
    
    '=== dataシートの最終レコードまで繰り返し ===
    
    For i = 2 To LastRow1
 
        K_id = Worksheets("select").Cells(1, 2)  'あいまい検索するため、検索条件(顧客ID)を変数に退避
 
        If Worksheets("data").Cells(i, 1).Value Like "*" & K_id & "*" Then
 
           With Worksheets("select")
                .Cells(Dcnt, 1).Value = Worksheets("data").Cells(i, 1).Value
                .Cells(Dcnt, 2).Value = Worksheets("data").Cells(i, 2).Value
                .Cells(Dcnt, 3).Value = Worksheets("data").Cells(i, 3).Value
                .Cells(Dcnt, 4).Value = Worksheets("data").Cells(i, 4).Value
                .Cells(Dcnt, 5).Value = Worksheets("data").Cells(i, 5).Value
                .Cells(Dcnt, 6).Value = Worksheets("data").Cells(i, 6).Value
                .Cells(Dcnt, 7).Value = Worksheets("data").Cells(i, 7).Value
                .Cells(Dcnt, 8).Value = Worksheets("data").Cells(i, 8).Value
                .Cells(Dcnt, 9).Value = Worksheets("data").Cells(i, 9).Value
                .Cells(Dcnt, 10).Value = Worksheets("data").Cells(i, 10).Value
                .Cells(Dcnt, 11).Value = Worksheets("data").Cells(i, 11).Value
            End With
            
            HitCnt = HitCnt + 1    '抽出条件に合致したデータの数をカウントアップ
 
            Dcnt = Dcnt + 1        '抽出結果シートの行数をカウントアップ
 
        End If
    Next i
 
    '=== 検索結果の件数とメッセージを表示 ===
 
    If HitCnt = 0 Then
       MsgBox "抽出条件に一致するデータが存在しません", vbExclamation, "検索NG"
    Else
       MsgBox HitCnt & "件のデータを抽出しました", vbInformation, "検索OK"
    End If
 
End Sub
[st-step step_no=”2″]顧客ID検索用マクロの割り当て[/st-step]

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

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

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

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

[st-step step_no=”3″]顧客ID検索用ボタンの動作確認[/st-step]

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

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

[st-kaiwa2]

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

[/st-kaiwa2]

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

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

[st-kaiwa1]

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

[/st-kaiwa1]

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

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

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

顧客氏名カナ検索

Sub 顧客氏名カナ検索()
    
   '=== 変数宣言 ===
    Dim i As Integer                           '繰り返し処理用
    Dim LastRow1 As Integer                    '顧客マスタのレコード数確認用
    Dim Dcnt As Long                           '抽出結果シート(select)の開始行をセット
    Dim K_id As String                         '顧客氏名カナの退避用
    Dim HitCnt As Long                         '検索条件一致したレコードの数をカウント
 
    '=== 抽出結果シートをクリア(セルの値のみクリアする) ===
    Worksheets("select").Range("A5:K4000").ClearContents
 
    '=== 顧客氏名カナ以外の抽出条件をクリア ===
    With Worksheets("select")
         .Cells(1, 2) = ""                       '顧客ID
         .Cells(1, 5) = ""                       '住所
         .Cells(2, 5) = ""                       '電話番号
         .Cells(1, 9) = ""                       '退会
    End With
 
    '=== 抽出結果カウント件数をクリア ===
    HitCnt = 0
    
    '=== 抽出結果シート(select)の設定開始行をセット ===
    Dcnt = 5
    
    '=== 顧客氏名カナの入力チェック ===
    
    If Worksheets("select").Cells(2, 2) = "" Then
       MsgBox "顧客氏名カナを入力して下さい", vbExclamation, "検索条件なし"
       Exit Sub
    End If
 
    '=== dataシートの最終行を取得 ===
 
    LastRow1 = Worksheets("data").Range("a65536").End(xlUp).Row
    
    '=== dataシートの最終レコードまで繰り返し ===
    
    For i = 2 To LastRow1
 
        K_id = Worksheets("select").Cells(2, 2)  'あいまい検索するため、検索条件(顧客氏名カナ)を変数に退避
 
        If Worksheets("data").Cells(i, 3).Value Like "*" & K_id & "*" Then
 
           With Worksheets("select")
                .Cells(Dcnt, 1).Value = Worksheets("data").Cells(i, 1).Value
                .Cells(Dcnt, 2).Value = Worksheets("data").Cells(i, 2).Value
                .Cells(Dcnt, 3).Value = Worksheets("data").Cells(i, 3).Value
                .Cells(Dcnt, 4).Value = Worksheets("data").Cells(i, 4).Value
                .Cells(Dcnt, 5).Value = Worksheets("data").Cells(i, 5).Value
                .Cells(Dcnt, 6).Value = Worksheets("data").Cells(i, 6).Value
                .Cells(Dcnt, 7).Value = Worksheets("data").Cells(i, 7).Value
                .Cells(Dcnt, 8).Value = Worksheets("data").Cells(i, 8).Value
                .Cells(Dcnt, 9).Value = Worksheets("data").Cells(i, 9).Value
                .Cells(Dcnt, 10).Value = Worksheets("data").Cells(i, 10).Value
                .Cells(Dcnt, 11).Value = Worksheets("data").Cells(i, 11).Value
            End With
            
            HitCnt = HitCnt + 1    '抽出条件に合致したデータの数をカウントアップ
 
            Dcnt = Dcnt + 1        '抽出結果シートの行数をカウントアップ
 
        End If
    Next i
 
    '=== 検索結果の件数とメッセージを表示 ===
 
    If HitCnt = 0 Then
       MsgBox "抽出条件に一致するデータが存在しません", vbExclamation, "検索NG"
    Else
       MsgBox HitCnt & "件のデータを抽出しました", vbInformation, "検索OK"
    End If
    
End Sub

住所検索

Sub 住所検索()
    
   '=== 変数宣言 ===
    Dim i As Integer                           '繰り返し処理用
    Dim LastRow1 As Integer                    '顧客マスタのレコード数確認用
    Dim Dcnt As Long                           '抽出結果シート(select)の開始行をセット
    Dim K_id As String                         '顧客住所の退避用
    Dim HitCnt As Long                         '検索条件一致したレコードの数をカウント
 
    '=== 抽出結果シートをクリア(セルの値のみクリアする) ===
    Worksheets("select").Range("A5:K4000").ClearContents
 
    '=== 顧客住所以外の抽出条件をクリア ===
    With Worksheets("select")
         .Cells(1, 2) = ""                       '顧客ID
         .Cells(2, 2) = ""                       '顧客氏名カナ
         .Cells(2, 5) = ""                       '電話番号
         .Cells(1, 9) = ""                       '退会
    End With
 
    '=== 抽出結果カウント件数をクリア ===
    HitCnt = 0
    
    '=== 抽出結果シート(select)の設定開始行をセット ===
    Dcnt = 5
    
    '=== 住所の入力チェック ===
    
    If Worksheets("select").Cells(1, 5) = "" Then
       MsgBox "住所を入力して下さい", vbExclamation, "検索条件なし"
       Exit Sub
    End If
 
    '=== dataシートの最終行を取得 ===
 
    LastRow1 = Worksheets("data").Range("a65536").End(xlUp).Row
    
    '=== dataシートの最終レコードまで繰り返し ===
    
    For i = 2 To LastRow1
 
        K_id = Worksheets("select").Cells(1, 5)  'あいまい検索するため、検索条件(住所)を変数に退避
 
        If Worksheets("data").Cells(i, 6).Value Like "*" & K_id & "*" Then
 
           With Worksheets("select")
                .Cells(Dcnt, 1).Value = Worksheets("data").Cells(i, 1).Value
                .Cells(Dcnt, 2).Value = Worksheets("data").Cells(i, 2).Value
                .Cells(Dcnt, 3).Value = Worksheets("data").Cells(i, 3).Value
                .Cells(Dcnt, 4).Value = Worksheets("data").Cells(i, 4).Value
                .Cells(Dcnt, 5).Value = Worksheets("data").Cells(i, 5).Value
                .Cells(Dcnt, 6).Value = Worksheets("data").Cells(i, 6).Value
                .Cells(Dcnt, 7).Value = Worksheets("data").Cells(i, 7).Value
                .Cells(Dcnt, 8).Value = Worksheets("data").Cells(i, 8).Value
                .Cells(Dcnt, 9).Value = Worksheets("data").Cells(i, 9).Value
                .Cells(Dcnt, 10).Value = Worksheets("data").Cells(i, 10).Value
                .Cells(Dcnt, 11).Value = Worksheets("data").Cells(i, 11).Value
            End With
            
            HitCnt = HitCnt + 1    '抽出条件に合致したデータの数をカウントアップ
 
            Dcnt = Dcnt + 1        '抽出結果シートの行数をカウントアップ
 
        End If
    Next i
 
    '=== 検索結果の件数とメッセージを表示 ===
 
    If HitCnt = 0 Then
       MsgBox "抽出条件に一致するデータが存在しません", vbExclamation, "検索NG"
    Else
       MsgBox HitCnt & "件のデータを抽出しました", vbInformation, "検索OK"
    End If
    
End Sub

電話番号検索

Sub 電話番号検索()
    
   '=== 変数宣言 ===
    Dim i As Integer                           '繰り返し処理用
    Dim LastRow1 As Integer                    '顧客マスタのレコード数確認用
    Dim Dcnt As Long                           '抽出結果シート(select)の開始行をセット
    Dim K_id As String                         '電話番号の退避用
    Dim HitCnt As Long                         '検索条件一致したレコードの数をカウント
 
    '=== 抽出結果シートをクリア(セルの値のみクリアする) ===
    Worksheets("select").Range("A5:K4000").ClearContents
 
    '=== 電話番号以外の抽出条件をクリア ===
    With Worksheets("select")
         .Cells(1, 2) = ""                       '顧客ID
         .Cells(2, 2) = ""                       '顧客氏名カナ
         .Cells(1, 5) = ""                       '住所
         .Cells(1, 9) = ""                       '退会
    End With
 
    '=== 抽出結果カウント件数をクリア ===
    HitCnt = 0
    
    '=== 抽出結果シート(select)の設定開始行をセット ===
    Dcnt = 5
    
    '=== 電話番号の入力チェック ===
    
    If Worksheets("select").Cells(2, 5) = "" Then
       MsgBox "電話番号を入力して下さい", vbExclamation, "検索条件なし"
       Exit Sub
    End If
 
    '=== dataシートの最終行を取得 ===
 
    LastRow1 = Worksheets("data").Range("a65536").End(xlUp).Row
    
    '=== dataシートの最終レコードまで繰り返し ===
    
    For i = 2 To LastRow1
 
        K_id = Worksheets("select").Cells(2, 5)  'あいまい検索するため、検索条件(電話番号)を変数に退避
 
        If Worksheets("data").Cells(i, 7).Value Like "*" & K_id & "*" Then
 
           With Worksheets("select")
                .Cells(Dcnt, 1).Value = Worksheets("data").Cells(i, 1).Value
                .Cells(Dcnt, 2).Value = Worksheets("data").Cells(i, 2).Value
                .Cells(Dcnt, 3).Value = Worksheets("data").Cells(i, 3).Value
                .Cells(Dcnt, 4).Value = Worksheets("data").Cells(i, 4).Value
                .Cells(Dcnt, 5).Value = Worksheets("data").Cells(i, 5).Value
                .Cells(Dcnt, 6).Value = Worksheets("data").Cells(i, 6).Value
                .Cells(Dcnt, 7).Value = Worksheets("data").Cells(i, 7).Value
                .Cells(Dcnt, 8).Value = Worksheets("data").Cells(i, 8).Value
                .Cells(Dcnt, 9).Value = Worksheets("data").Cells(i, 9).Value
                .Cells(Dcnt, 10).Value = Worksheets("data").Cells(i, 10).Value
                .Cells(Dcnt, 11).Value = Worksheets("data").Cells(i, 11).Value
            End With
            
            HitCnt = HitCnt + 1    '抽出条件に合致したデータの数をカウントアップ
 
            Dcnt = Dcnt + 1        '抽出結果シートの行数をカウントアップ
 
        End If
    Next i
 
    '=== 検索結果の件数とメッセージを表示 ===
 
    If HitCnt = 0 Then
       MsgBox "抽出条件に一致するデータが存在しません", vbExclamation, "検索NG"
    Else
       MsgBox HitCnt & "件のデータを抽出しました", vbInformation, "検索OK"
    End If
    
End Sub

退会検索

Sub 退会検索()
    
   '=== 変数宣言 ===
    Dim i As Integer                           '繰り返し処理用
    Dim LastRow1 As Integer                    '顧客マスタのレコード数確認用
    Dim Dcnt As Long                           '抽出結果シート(select)の開始行をセット
    Dim K_id As String                         '退会の退避用
    Dim HitCnt As Long                         '検索条件一致したレコードの数をカウント
 
    '=== 抽出結果シートをクリア(セルの値のみクリアする) ===
    Worksheets("select").Range("A5:K4000").ClearContents
 
    '=== 退会以外の抽出条件をクリア ===
    With Worksheets("select")
         .Cells(1, 2) = ""                       '顧客ID
         .Cells(2, 2) = ""                       '顧客氏名カナ
         .Cells(1, 5) = ""                       '住所
         .Cells(2, 5) = ""                       '電話番号
    End With
 
    '=== 抽出結果カウント件数をクリア ===
    HitCnt = 0
    
    '=== 抽出結果シート(select)の設定開始行をセット ===
    Dcnt = 5
    
    '=== 退会の入力チェック ===
    
    If Worksheets("select").Cells(1, 9) = "" Then
       MsgBox "検索条件を入力して下さい", vbExclamation, "検索条件なし"
       Exit Sub
    End If
 
    '=== dataシートの最終行を取得 ===
 
    LastRow1 = Worksheets("data").Range("a65536").End(xlUp).Row
    
    '=== dataシートの最終レコードまで繰り返し ===
    
    For i = 2 To LastRow1
 
        K_id = Worksheets("select").Cells(1, 9)  'あいまい検索するため、検索条件(退会)を変数に退避
 
        If Worksheets("data").Cells(i, 10).Value Like "*" & K_id & "*" Then
 
           With Worksheets("select")
                .Cells(Dcnt, 1).Value = Worksheets("data").Cells(i, 1).Value
                .Cells(Dcnt, 2).Value = Worksheets("data").Cells(i, 2).Value
                .Cells(Dcnt, 3).Value = Worksheets("data").Cells(i, 3).Value
                .Cells(Dcnt, 4).Value = Worksheets("data").Cells(i, 4).Value
                .Cells(Dcnt, 5).Value = Worksheets("data").Cells(i, 5).Value
                .Cells(Dcnt, 6).Value = Worksheets("data").Cells(i, 6).Value
                .Cells(Dcnt, 7).Value = Worksheets("data").Cells(i, 7).Value
                .Cells(Dcnt, 8).Value = Worksheets("data").Cells(i, 8).Value
                .Cells(Dcnt, 9).Value = Worksheets("data").Cells(i, 9).Value
                .Cells(Dcnt, 10).Value = Worksheets("data").Cells(i, 10).Value
                .Cells(Dcnt, 11).Value = Worksheets("data").Cells(i, 11).Value
            End With
            
            HitCnt = HitCnt + 1    '抽出条件に合致したデータの数をカウントアップ
 
            Dcnt = Dcnt + 1        '抽出結果シートの行数をカウントアップ
 
        End If
    Next i
 
    '=== 検索結果の件数とメッセージを表示 ===
 
    If HitCnt = 0 Then
       MsgBox "抽出条件に一致するデータが存在しません", vbExclamation, "検索NG"
    Else
       MsgBox HitCnt & "件のデータを抽出しました", vbInformation, "検索OK"
    End If
    
End Sub

データクリア

Sub データクリア()

  '=== 抽出結果シートをクリア(セルの値のみクリアする) ===
  Worksheets("select").Range("A5:K4000").ClearContents

  '=== 抽出条件をクリア ===
  With Worksheets("select")
   .Cells(1, 2) = "" '顧客ID
   .Cells(2, 2) = "" '顧客氏名カナ
   .Cells(1, 5) = "" '住所
   .Cells(2, 5) = "" '電話番号
   .Cells(1, 9) = "" '退会

  End With
    
End Sub

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

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

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

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

顧客登録シート
[st-mybox title=”ポイント” fontawesome=”fa-check-circle” color=”#FFD54F” bordercolor=”#FFD54F” bgcolor=”#FFFDE7″ borderwidth=”2″ borderradius=”5″ titleweight=”bold” fontsize=”” myclass=”st-mybox-class” margin=”25px 0 25px 0″]
  • 顧客IDが未入力で登録ボタンを押すと、エラーメッセージが表示
  • 顧客氏名(漢字)を入力すると、顧客氏名(カナ)に自動でフリガナ表示
  • 入力項目以外のセルをロック
  • [/st-mybox]

    顧客IDは、顧客データを登録する際の位置を特定するために使用するため、入力必須にしています。
    そのため、顧客IDが未入力で登録ボタンを押すと、エラーメッセージが表示されて登録できません。

    顧客登録

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

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

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

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

    顧客登録ボタンのVBA

    Sub btn登録_Click()
    
        '=== 入力項目チェック(顧客ID) ===
        
        If Worksheets("input").Cells(2, 2).Value = "" Then
        
           MsgBox "顧客IDを入力して下さい", vbExclamation, "顧客ID未入力"
           Exit Sub
        
        End If
        
    
        '=== 入力項目チェック(顧客氏名 漢字) ===
            
        Dim RC As String
        
        If Worksheets("input").Cells(3, 2).Value = "" Then
        
           RC = MsgBox("顧客氏名(漢字)が未入力です。" & vbCrLf & vbCrLf & "登録してよろしいですか?", vbYesNo + vbExclamation, "確認")
           
           If RC = vbYes Then
           
           Else
              
              MsgBox "顧客氏名(漢字)を入力して下さい", vbExclamation, "顧客氏名(漢字)未入力"
              Exit Sub
           
           End If
        
        End If
        
        '=== dataシートの最終行を取得 ===
        
        Dim lastRow1 As Long
        
        lastRow1 = Worksheets("data").Range("a65536").End(xlUp).Row + 1
        
        'MsgBox "最終行は" & lastRow1
        
        '=== 顧客データの登録処理 ===
        With Worksheets("data")
             .Cells(lastRow1, 1).Value = Worksheets("input").Cells(2, 2).Value         '顧客ID
             .Cells(lastRow1, 2).Value = Worksheets("input").Cells(3, 2).Value         '顧客氏名(漢字)
             .Cells(lastRow1, 3).Value = Worksheets("input").Cells(4, 2).Value         '顧客氏名(カナ)
             .Cells(lastRow1, 4).Value = Worksheets("input").Cells(5, 2).Value         '性別
             .Cells(lastRow1, 5).Value = Worksheets("input").Cells(6, 2).Value         '郵便番号
             .Cells(lastRow1, 6).Value = Worksheets("input").Cells(7, 2).Value         '住所
             .Cells(lastRow1, 7).Value = Worksheets("input").Cells(8, 2).Value         '電話番号
             .Cells(lastRow1, 8).Value = Worksheets("input").Cells(9, 2).Value         '生年月日
             .Cells(lastRow1, 9).Value = Worksheets("input").Cells(10, 2).Value        'mail
             .Cells(lastRow1, 11).Value = Worksheets("input").Cells(11, 2).Value       '備考
        End With
    
        MsgBox "顧客データを登録しました", vbInformation, "登録完了"
    
        '=== 顧客登録シートのクリア ===
    
        With Worksheets("input")
             .Cells(2, 2).Value = ""                      '顧客ID
             .Cells(3, 2).Value = ""                      '顧客氏名(漢字)
             .Cells(4, 2).Value = "=PHONETIC(B3)"         '顧客氏名(カナ)
             .Cells(5, 2).Value = ""                      '性別
             .Cells(6, 2).Value = ""                      '郵便番号
             .Cells(7, 2).Value = ""                      '住所
             .Cells(8, 2).Value = ""                      '電話番号
             .Cells(9, 2).Value = ""                      '生年月日
             .Cells(10, 2).Value = ""                     'mail
             .Cells(11, 2).Value = ""                     '備考
        End With
    
    End Sub

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

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

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

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

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

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

    [st-kaiwa1]

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

    [/st-kaiwa1]

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

    [st_af id=”407″]

    この記事を書いた人

    ビジツールラボ