こんにちは。エクセル使いこなし隊隊長、吉井良平です。
個人で事業をされている方から、エクセルで顧客管理をする方法について問い合わせがありました。
最近はクラウド型顧客管理システムもあるので、いくつか見てみたのですが、ちょっと個人事業主には仕組みが大きすぎるな、というのが正直な印象です。
やっぱり個人事業のうちはエクセル主体の方がお金もかからず、使い勝手も良いだろうな、と思います。
(美容系などの大きな業界は、個人事業主用のパッケージシステムで良いものがあると思います)
とは言っても、エクセルは結構アバウトなところがあるソフトなので、そのままの状態で顧客管理をすると、使いにくい表になったり、入力ミスや漏れが多くなってしまうのも事実です。
ということで、今回から2回にわたって、エクセルで顧客管理をする場合の作り方や、注意点を書いていきます。
今回は、顧客マスターの作り方についてです。
やっぱりミスなく漏れなく作るためには、マクロを使ったり、ちょっとややこしい設定をした方が良いので、「難しい!」と思われるかもしれませんが、一つ一つ落ち着いてやってみてくださいね。
(今回の手順で作ったファイルは、後でダウンロードできます)
なお、少しお金を出しても良いので、顧客別の販売回数、実績まで管理したい場合は、「小さな会社、個人事業主のための顧客管理・販売管理システム」の記事をご覧くださいね。
目次
顧客マスターを作る
まず、顧客(クライアント)のマスター、データベースを作ります。
(いきなり横文字が出てきて恐縮ですが、どちらも良く使う言葉なので覚えておいて損はありません。)
マスターとは、「情報の元になるデータ」のことです。
将来的に、顧客にお礼状を出したり、新サービスの案内を出したりすることが考えられますよね。そんなときに、情報を引っ張ってくる元になるデータを作りましょう。
マスターを作る際には、「データベース」という形で作ります。
データベースとは、「一定の法則で並んだデータ」のことです。
一定の法則とは何かというと、簡単にいうと「縦方向に、隙間なく」データが並んでいると考えてください。
重要なので、もう一度書きます。「縦方向に、隙間なく」です。
横方向はダメ
下の図を見てください。

縦方向に隙間なく
都道府県の人口と面積が、縦方向に隙間なく並んでいますよね。
これはデータベースと言えます。
では、こちらの図はどうでしょう?

横方向にデータが増えていっている
先ほどと同じデータですが、横方向に並んでいますよね。
人間の目で見ると同じじゃないかと思いますが、横方向ではダメなんです。
横方向ではダメな決定的な理由が、2つほどあります。
それでも横方向で作りたい人は作ってもらってもそりゃいいんですけど、できるだけ縦方向でデータを並べる習慣をつけておいてくださいね。
① フィルターが使えない
エクセルには「フィルター」という機能があって、特定の値だけを表示させることができます。
(四国地方だけを、フィルターを使って表示させた例です)

四国だけを表示させた
上側の▼ボタンがフィルターで、データタブ → フィルター で設定します。
特定の値だけを表示させる便利な機能ですが、横方向になっている(=データベースになっていない)形だと、この機能を使うことができません。
② 並べ替えができない
データベースになっていると、項目ごとの並べ替えが簡単にできます。
先ほどの表を、人口の多い順に並べ替えてみました。

人口順に並べ替え
横方向にデータを作ってしまうと、この並べ替え機能が使えません。
顧客マスターであれば、住所ごとに並べ替えたり、特定の地域に住んでいる人だけを表示させたり、男女別に並べ替えたり、いろいろやりたいことがあるはずです。
横方向にデータを作ってしまうと、これらの機能が使えなくなってしまうのです。
隙間があってもダメ
たまに、見やすいようにということだと思うのですが、表の中にスキマ(空白行、空白列)を入れている表を見ることがあります。

表の中にスキマがある
これも、横書きと同様に「フィルターが使えない」「並べ替えが上手くいかない」という理由でNGです。
もし、空白行や空白列を作っている方がいらっしゃったら、セルの幅や高さを調整して見栄えを調整するようにしてください。
スキマを作ってしまうと、エクセルの機能を活かすことができないので、もったいないですよ。
「縦方向に」「スキマなく」作っておくと、集計に便利な「ピボットテーブル」という機能も使えます。
データベースの各部分の名称
この辺は特に覚えなくても大丈夫ですが、知っておくと得する知識なので、データベースで使う用語を書いておきますね。

データベースの名称
一件一件のデータ(赤枠で囲っている部分)を、レコードといいます。レコード=記録です。
緑枠で囲った縦の部分を、フィールドといいます。人口なら人口フィールド、面積なら面積フィールドです。
青枠で囲った部分は、フィールドの名前です。
おそらく、この辺の名称は変わることがないので、一生役立つ知識です。
実際に顧客マスターを作ってみる
だいぶ前置きが長くなってしまいました^^
ここからは、実際に顧客マスターを作っていきます。
顧客マスターで管理する項目は、あなたの仕事で必要な項目です。
一般的には、名前、職業、郵便番号、住所、電話番号、FAX番号、携帯番号、メールアドレス、ぐらいですかね?
B to Bの場合は、役職なども入ってくるでしょう。
生年月日や男女別、既婚未婚などが必要な場合は、それも入れておいてください。
この辺の項目は、それぞれのお仕事で違うと思うので、ご自分の仕事で必要な項目を入力してみてください。

とりあえずフィールド名を入力した
タイトルは、クライアント一覧とか、顧客管理表とか、お客様一覧とか、ご自分の分かりやすい名前で構いません。(なくても構いません)
上記の例では、最初にスペースがあった方が見やすいと思うので、A列を何も入力しない欄にしていますが、これもA列から入力しはじめても構いません。
ただ、最初にID欄を作っておいてください。
登録した順番が分かるというのと、もし同姓同名の方が現れた時に区別がつきやすくなります。
見栄えを整える
とりあえず1件入力してみて、それで大体の見栄えを整えていきましょう。
この辺はお好みですが、下記の例ではセルの幅を増やしたり減らしたりして、フィールド名の部分には色をつけて分かりやすくしました。
あとで項目(フィールド)を追加したりするかもしれないので、見栄えに関しては、最初はあまり凝らなくて良いと思います。

見栄えを調整した
ウインドウ枠を固定する
データが増えていっても大丈夫なように、ウインドウ枠を固定しておきます。
こうしておくと、データが増えていっても、常にタイトル行(フィールド名)が表示されるようになります。

ウインドウ枠の固定
とりあえず、顧客マスターシートの設定はこのぐらいです。
入力用シートを作ろう
ここからの設定は、ちょっと難しいので、面倒くさい人はしなくても大丈夫です。
が、逆に面倒くさがりの人の方がやった方が良い設定です。
先ほどの顧客マスターに、直接お客さんの情報を入力していっても良いのですが、やっぱり横方向だと入力しづらいですよね。
「入力しづらい→メンテナンスしなくなる」ということにも、なりかねません。
あと、せっかくなので見込み客の管理もできればやっていきたいところです。(最近の言葉でいうと、リードというヤツです)
なので、入力用の別シートを作って、顧客マスターとリード管理リストに転記をするようにしてみましょう。
新規登録用のシートを追加する
新しくシートを追加して、「新規登録」シートを作ります。

シートを追加する
このシートに、顧客マスターの項目を、入力しやすいように並べましょう。
入力しやすいように、行の高さや列の幅も調整してください。入力位置を迷わないように、色付けもします。
入力するセル以外をロックする方法もありますが、個人事業主の場合はそこまでしなくても、という気がしますので、そこまではしません。

こんな感じです
後でちょっとしたマクロを追加して、ここに入力したものを顧客マスターに追加するようにします。
入力設定をする
郵便番号、電話番号など、半角英数でしか入力しない欄に関しては、セルに入力規則をつけておきましょう。
そうすると入力も楽ですし、全角と半角が入り乱れた顧客マスターにならなくてすみます。

入力規則の設定方法
見込み客管理シートを作る
ホームページ、ブログ、チラシなどからの問い合わせが、必ず顧客になるとは限りませんよね。
問い合わせ→見積もり→商談→成約など、あなたなりの営業プロセスがあるはずです。
この営業プロセスを管理できるシートも作ります。

見込み客管理シート
フィールド名には営業プロセスを入力していきます。
完了したら日付を入力していけば、このシートでTODOが管理できるようにしていきます。
顧客マスター、見込み客管理シートに追加するマクロを付け加える
マクロを使ったことが無い方は、ここからがちょっと難しいかもしれません。分かりにくかったら書き換えるので、遠慮なくコメントしてくださいね。
マクロを登録できるようにする
マクロを登録するためには、エクセルに開発タブを出す必要があります。
下記の記事を開いていただいて、①開発タブを表示させる ②標準モジュールを表示させるところまでやってみてください。
「はじめての・・・エクセルマクロ! ちょっと下ネタ交じりにご紹介!」
(だいぶ前に書いた記事なので、ちょっと恥ずかしいですけど・・・)
標準モジュールが出たら、モジュールに下記のコード(プログラム)をコピペで貼り付けてください。
コードは初めての人にも分かりやすいように、長めに書いています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
Sub 新規登録() ' Dim WS1 As Object Dim WS2 As Object Dim WS3 As Object Dim LCRN As Integer Dim N1 As Long Dim MyName As String Set WS1 = Worksheets("新規登録") Set WS2 = Worksheets("顧客マスター") Set WS3 = Worksheets("見込み客管理") WS1.Select 'C4の値を、変数に格納 MyName = Cells(4, 3) 'C4~11の値を、コピー Range("C4:C11").Select Selection.Copy WS2.Select 'データを追加する行を取得 N1 = ActiveSheet.Rows.Count LCRN = Cells(N1, 2).End(xlUp).Row + 1 'IDを1個追加 Cells(LCRN, 2) = Cells(LCRN - 1, 2) + 1 Cells(LCRN, 3).Select '行列を変換して、値のみ貼り付け Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True WS3.Select 'データを追加する行を取得 LCRN = Cells(N1, 2).End(xlUp).Row + 1 'IDを1個追加 Cells(LCRN, 2) = Cells(LCRN - 1, 2) + 1 '名前と日付を登録 Cells(LCRN, 3) = MyName Cells(LCRN, 4) = Date '新規登録シートの値をクリア WS1.Range("C4:C11").ClearContents End Sub |
もし入力項目が多い場合は、新規登録シートの入力欄の数に応じて、コピー元の範囲(21行目)を変更してください。
(Range(“C4:C11”)の部分はC4~C11までという意味なので、ここをC12とか13とか、数を増やせば大丈夫です)
マクロをボタンに追加しよう
マクロを簡単に実行するために、ボタンにマクロを登録しましょう。
新規登録シートの適当な位置に、テキストボックスでも何でも良いので、図形を作ります。
作った図形の上で右クリックして、マクロの登録をクリックします。

図形の上で右クリック
マクロ名「新規登録」を選択して、OKを押してください。

登録するマクロを選ぶ
お疲れ様でした。
これで、顧客管理をするエクセルファイルが出来上がりました!
今回作ったファイルの動画
一から作るのは大変なので、今回作ったファイルはダウンロードできます。
下記のフォームにお名前とメールアドレスを入力して、ダウンロードボタンを押していただくと、返信メールにエクセルファイルがダウンロードできるURLが書いてあります。
このブログのメルマガ会員に自動登録されますが、不要な方は次に来たメルマガに登録解除アドレスがありますので、不要な方は解除してくださいね。
次回は、顧客別の商談(面談)管理ファイルの作り方を紹介します!
はじめまして。
顧客管理をエクセルでしようと思い、調べていたところこちらのページを見つけました。
今までエクセル等を使ったことがなく、本当に素人のためコメントさせていただきました。
こちらで紹介されている顧客管理はかなり理想的なものなのですが、実際使うとなると新規登録と顧客マスターだけになりそうです。
見込み客管理は必要なく、新規登録と顧客マスターだけを使用する場合どのように作成すればいいのでしょうか?
プログラム?を書き換えるだけでしょうか?
その場合どのように書き換えるのかを教えていただきたいです。
お忙しいと思いますが、よろしくお願いします。
西村様
お問合せありがとうございます。
新規登録と顧客マスターだけでしたら、見込み客管理に書き込む部分を削除すれば大丈夫です。
具体的には、記事に書いてあるプログラムの 38行目から48行目の部分を、削除してください。
(普通に、デリートキーやバックスペースキーで削除すれば大丈夫です)
またご質問があれば、ご遠慮なくお問合せくださいね。
初めまして。
顧客管理の作り方を調べてこちらにたどり着きました。すごくわかりやすく、楽しくできました。
そこで、できる範囲での回答でもかまわないのですが
顧客管理で タブに あ行 か行 さ行。。。 と分けているのですが
新規登録画面で入力したものを、各タブにのみ飛ばすことは可能でしょうか?
例えば、新規登録画面に 青木 ○○ さんを登録したとしたら
あ行のタブにのみ入力されるというのはできませんか??
また、もし可能でしたら 顧客管理からはがき印刷もできるマスターを教えて頂けたらうれしいです。
山下様
コメントいただきありがとうございます。お役に立てたようで幸いです。
タブで「あ行」「か行」「さ行」と分けているということは、シートを作っているということでしょうか?
シートに分ける場合は、少し複雑な処理が必要になって、IF文を挿入して「ふりがなの始まりが○○の場合・・・」という形で処理を分けることになります。
顧客管理からはがき印刷をするのは、マクロでプログラムを組んでも良いのですが、ワードに「差し込み印刷」という機能があります。
ワードを立ち上げて、「差し込み文書」タブを選択すると「はがき印刷」というのが出てくるので、それに従えば大丈夫だと思います。
この欄で全部説明するのは難しいので、かんたんな紹介になってしまいましたが、よろしくお願いいたします。
さっそくのお返事ありがとうございます。
メルマガに登録し、上記顧客マスターをダウンロードさせていただきそのまま使おうと思い 顧客情報を入力しました。
そして、ワードで差し込み文章で はがき印刷を設定したのですが、宛名 郵便番号等が反映されませんでした。
自分でExcelで作成したものは、B名前 C郵便番号 D住所と入力しており、ワードの方にすべて反映されるのですが セルの位置などの違いでしょうか。
山下様
ワードのはがき印刷画面になったら、上側に「フィールドの対応」という欄がありますので、そこから列番号で項目ごとに指定していきます。
(F〇〇 というのが何列目という列番号です)
もともとのエクセルファイルがワードのはがき印刷にそのまま対応できるようになっていないので、かなり手間が発生しますね。
エクセルファイルをはがき作成にそのまま対応できるような形にするか、はがき印刷用のマクロを作るか、検討してみます。
ご指摘ありがとうございました。
はじめまして。
顧客管理ソフトを調べていたところこちらのページを見つけました。
・お客様を呼び出し1ページで表示するにはどうしたらいいでしょうか?
・顔写真は写真リストを作っておき、レコード欄に写真と項目名を書き写真を張り付ければいいでしょうか?
よろしかったらご指導お願いします。
海津様
お問い合わせありがとうございます。
おそらく・・・ですが、お申し込みフォームなどがあって、そこから顧客リストを作る形式が良いんじゃないかと思います。
申し込みフォームを1枚のエクセルシートにして、そこに写真なども入れておきます。
その上で、シートの複製(個人別シート)をして、顧客一覧に追加するような形にします。
なかなか簡単に説明するのは難しいので、別途メールを送らせていただきますね。
吉井 良平
初めまして、エクセル初心者ですよろしくお願い致します
私は小さなお店を経営しております。日々の売上業務を自分でなんとか管理ができないものか、といろいろ検索してこちら様にたどり着きました。
自作のデータベースは作りましたが、新規など追加をするときには途中に差し込み挿入しています。ID順にはしていますが不要な顧客は省いているので途中が歯抜け状態になっています。
例えば「ID 1001あ1、1002あ2、1005あ5」に並んでいるので新規は「あ2」の下に名前、住所など入力でいて挿入しています。これを新規追加シートで簡単に登録できたら便利になります。
質問ですが、マクロを使えばIDもちゃんと表示され順番通りに反映されますか?
説明が分かりにくくてすいません!
宜しくご指導ください
ご質問ありがとうございます^^
マクロを使えば、IDを順番通りにすることはできます。
ご質問の例だと、1003あ3という番号で追加になれば良いんですよね。
でも、ID番号は「identification」の略で、個別の値を区別するために付ける番号なので、基本的には同じID番号を使い回さない方が良いのです。
なので、削除する方もデータ上は残しておいて、「退会済み」とかのフラグ(行を作る)とか、「削除済」などのシートを作ってそのシートに移動させるとかして、新規追加は1006あ6という番号をとった方が、良いんじゃないかと思います。その方が仕事もシンプルになります。
(何か、ID番号を100件以内にしないといけないとか、物理的な制約があれば別ですが)
これで回答になっていますでしょうか?
ご自分で管理されようというのは、素晴らしいことですね!
マクロは絶対に役立つと思いますので、チャレンジしてみてくださいね^^
はじめまして
Excel初心者です
見込みの部分とセルの範囲を変更しましたが
エラーがでてしまいます。
マクロを使うのも初めてで理屈がわかっていないのでお手上げです。
難しいですがメルマガで読み込んで勉強させていただきます。
よろしくお願いします。
お問い合わせありがとうございます。
どこの部分でエラーが出たのかがはっきりと分かりませんので、もしどうしても分からない場合は、yoshii@deskworklabo.xsrv.jpまでメッセージを送って下さいね。
初回のお問い合わせはサービスで対応していますので、大丈夫です。
エクセルは知っておくと便利な機能がたくさんありますので、頑張って下さい^^
はじめまして。今回初めてマクロに挑戦しようとダウンロードしましたが、マスターの項目のFAXを工事日へ携帯を工事内容へメールアドレスを工事金額に変更した所、型が違いますのエラーがでます。プログラムのどこを修正したらいいか検索してみましたがわかりません。よろしくおねがいします。
はじめまして。お問い合わせありがとうございます。
各項目には、データの「型」が設定してあり、メールアドレスなどは文字じゃないとエラーが出ます。(金額は数字なので、文字が入るように設定している欄に数字が入ると、エラーになります)
下記の記事で、データの型について紹介していますので、下記を参照していただけると幸いです。よろしくお願いいたします。
https://kantan-shikaku.com/ks/macro5-variable/
返信ありがとうございました。頑張ってみます。
はじめまして、団体の会員管理をしています。
すでに名簿管理しているのですが、今回この新規入力フォーム採用してみようかなと思っています。
このブログにたどりつくきっかけですが、名簿一覧をカルテ化できないかと検索していました。
この入力フォームのような形でIDをいれたらカルテのようなカードにならないかなと思っています。
もしそのようなご経験がありましたら教えてください。
お問い合わせありがとうございます。
入力フォームからカルテを作る件のは、やり方次第でものすごく便利なツールになります。
私も顧客管理のファイルを作る時には、かなり使っています。
人数が増えていった場合に、少しテクニックが必要になってきますので、その際はまた個別にお問い合わせいただければ幸いです。
はじめまして。ネットショップの顧客管理表を作りたくて、このサイトにたどり着きました。エクセル初心者ですがとてもわかりやすく、なんとか自分にもデキそうな気がしております。
そこで1点質問させていただきたいのですが、ネットショップでは同じ顧客から複数の住所に配送する事が多く、
1依頼主となるメインの顧客名と各配送先も履歴を残したい。
2次に注文が入った場合、何回目の利用かを把握したい。
この二点をどのような方法で行うかを検討しています。
プロの目から見て、こんなやり方があるよとか
こうやってみたら?というアイディアがあればアドバイスいただけないでしょうか?
勝手なお願いで申し訳ありませんが
ご返信いただければ幸いです。
コメントありがとうございます。
これは実際に行なうとすると、エクセルのマクロ機能を学習されたら良いと思います。
私のサイトでも紹介しています。 https://kantan-shikaku.com/ks/macro-vba1/
具体的な手順としては、
1.顧客マスターを作る (今回ダウンロードしたものでも、ここまでは出来ていますが、顧客コードを作った方が良いです)
2.毎回の注文時に、顧客コードを入力して、販売履歴を取っていく。
3.注文時に、顧客コードの回数をカウントする。
このようなことを、マクロを使って行なっていきます。
エクセルの初心者ですと少し難しいとは思いますが、ご参考にして下さいね。
はじめまして、Excel初心者です。わかりやすかったので顧客管理表を作成させていただきました。
伺いたいのですが、マスターを別のシートに作成してある書類に反映することはできますか?
こんにちは。
「具体的にどういうことかな?」と少し思うところもありますが、おそらく(ほぼ)可能だと思います。
少しテクニックが必要なので、下記の記事からエクセルのマクロ機能について勉強していただければ、
第6回ぐらいで、そのようなテクニックが出てくると思います。
https://kantan-shikaku.com/ks/macro-vba1/
頑張ってくださいね^^
初めまして。
EXCEL初心者です。今回の記事を参考にさせていただき、データを作成してみました。
コードをコピペし、マクロボタンを作成、入力シートにデータを打ち込み新規登録ボタンを押したところエラーが出てしまいました。
内容は、実行時エラー’9′:インデックスが有効範囲内にありません。
デバッグボタンを押し、見てみたところコード内のSet WS1=Worksheets(“新規登録”)
部分が黄色くラインが引かれておりました。
こういった場合の解決策はございますでしょうか。
ご返答いただけたら幸いです。宜しくお願い致します。
コメントありがとうございます。
インデックスが有効範囲内にありません、で、Worksheetsの部分がエラーになっているということは、
エクセルファイル内の「新規登録」という名前のシートが無いのでは、と思います。
シート名を「新規登録」にして、試してみてくださいね。
顧客管理をPCでやろうと検索していたらここにたどり着きました。
世の中には奇特な方もおられるのだなぁとただただ感謝です。
早速初めて見たのですが
「標準モジュールが出たら、モジュールに下記のコード(プログラム)をコピペで貼り付けてください。
コードは初めての人にも分かりやすいように、長めに書いています。」
とここで先に進めなくなりました。
申し訳ないのですがコピペの後の作業を具体的に教えていただけませんか?
見知らぬ方に大変恐縮ですがよろしくお願いいたします。
追加情報
恐縮です
‘行列を変換して、値のみ貼り付け
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
デバッグでここが黄色になり先に進めませんでした。
何かが影響してエラーになっているんだと思います。
(途中のどこかが違うのでは?)
個別のお問合せには、さすがに対応できませんので^^、ダウンロードしたファイルを使っていただければと思います。
よろしくお願いいたします。
早速のご回答ありがとうございます。
もう少しいじくりまわしてみます(^_^;)
ありがとうございました。
大変参考になりました。ありがとうございます。これをもとにプログラムくんでみます。
実行時エラー’13’:
型が一致しませんと出ます。
デバックを開くと
Cells(LCRN, 2) = Cells(LCRN – 1, 2) + 1
が黄色くなっています。
解決策を教えてください。
確認が遅くなり、誠に申し訳ありません。
フォーマットの位置を変更されていませんでしょうか?
簡易的なプログラムのため、開始行や列がずれると上手くいきませんので、
ご了承お願いいたします。
参考にさせていただきます。ありがとうございました。
こちらのサイトを見つけ、新規登録プログラムをコピペしてチャレンジしています。
そのままマクロ登録しても何度も実行時エラー13というのが出てしまいました。
そこで、プログラムの31、44行目の…cells(LCRN-1,2)+1をcells(LCRN)+1に変えたら、なんとかマクロ登録できました。
しかし、新規登録のID番号が1番目以降もずっと1のままのID登録になってしまいます。
どうしたらID番号が1,2,3と順番に入るようになりますか?
また31、44行目の…cells(LCRN-1,2)+1の意味も教えて頂けたらありがたいです。
コメントありがとうございます。
この辺りは、「変数」の働きを理解する必要がありますので、下記の記事をご覧になっていただけると幸いです。
https://kantan-shikaku.com/ks/macro5-variable/