会社で使う顧客名簿は検索しにくかったり、必要な情報が項目になかったりすることがあります。そんなとき、自分で顧客名簿を管理する「顧客名簿管理表」を自分で作成することで管理しやすくなりますし、データの分析もすることができるようになります。そのために基本的な顧客名簿を作成して、並べ替えやデータの抽出方法を知ることで活用の幅が広がります。
そこで今回は、エクセルで顧客名簿を管理する表の作り方と文字列の置き換えの方法や重複データの削除のやり方、テンプレートも無料でダウンロードできるようにしています。
目次
顧客名簿の管理表を作る
頂いた名刺などを基にして顧客名簿を作成します。作成するための方法は「 住所録の作り方 」を見ながら作るといいです。色々な項目を入れたいと思うと思いますが、基本的な項目を見出しとして入れています。私個人が使っている顧客名簿には他にも細かい項目を入れていますが、それは最後の編集後記でお伝えしますので、参考にして頂ければと思います。
され、顧客名簿の見出しに入れた項目は、No・氏名・性別・年齢・職業・会社・学校名・郵便番号・住所1・住所2・住所3・住所4・連絡先・メルアドです。
テーブル機能、データ入力規則を使うことで入力するスピードが早くなり時間短縮になります。
氏名のふりがな欄を追加する
氏名の隣にフリガナを振って名前の読み間違いを防ぎます。濁音が付くのか、付かないのか、読み方は合っているのかを確認するためにも「ふりがな」は必要です。でも、ふりがなを個別で入力するのは時間の無駄なので関数を使って表示させます。
ふりがなを表示する欄を増やすためにC列をクリックして、ホームタブをクリックし、「挿入」ボタンをクリックします。
列を挿入するとセルC1に列1と表示されます。
セルC1の「列1」を「フリガナ」と入力します。
セルC2をクリックしたら「関数の挿入」ボタンのfxをクリックします。
関数の挿入画面が表示されますので、関数の分類を「すべて表示」にして、関数名の中から「PHONETIC」をクリックして、「OK」ボタンをクリックします。
関数の引数画面が表示されますので、セルB1をクリックします。すると、参照欄に［@参照］と入力されます。数式の結果にふりがなが表示されますので「OK」ボタンをクリックします。
「OK」ボタンをクリックすると、フリガナ欄全体に関数の式が反映されます。
フリガナの間違いを編集する
フリガナの表示が間違えている部分があるときに、フリガナを修正・編集する方法です。
フリガナが間違っているのを修正・編集するには、B列の氏名をクリックします。今回は「スミ マサユキ」ではなく「カク マサユキ」に修正するため、セルB8をクリックして、ホームタブを選び、「ふりがなの表示/非表示」ボタンをクリックして「ふりがなの編集」をクリックします。
フリガナ欄が表示されますのでマウスや矢印キーの「←」を押して修正する箇所まで移動し、BackSpaceキーやDeleteキーで文字を削除して正しい文字を入力します。
氏名を「カク」に変更し、「Enter」キーを押すと文字が確定し、もう一度「Enter」キーを押すとフリガナの表示が変更されます。
これでフリガナの表示を編集・修正することができました。
カタカナ表示からふりがな表示に変更する
「B」をクリックしてB列全体を範囲選択したら、ホームタブをクリックして、「ふりがな表示/非表示」ボタンをクリックして「ふりがなの設定」をクリックします。
ふりがなタブを選び、種類の「ひらがな」をクリックしてチェックを入れたら「OK」ボタンをクリックします。
すると、ふりがなの表示になります。これは好みで選ばれるといいです。
データを並び替える
入力したデータを任意の条件に並べ替えることで検索しやすくなりますし、見やすくなります。
【1つの列を並び替える方法】
フリガナの欄を昇順・あいうえお順に並び替えたいと思います。フリガナが表示されているC列のどこでもいいのでクリックします。今回はセルC2をクリックして、データタブをクリックして、「昇順」ボタンをクリックします。
「昇順」ボタンをクリックするとフリガナ欄が昇順に並び替えられます。
【複数の列を並べ替える方法】
並べ替えをするとき複数の列を指定して並び替えることができます。今回はフリガナ列を昇順に並べ替えて、さらに性別列は男を先に表示するように並べ替えたいと思います。
表の中のどこでもいいのでクリックします。今回はセルC2をクリックしたら、ホームタブをクリックしたら、編集グループの「並べ替えとフィルター」ボタンをクリックして、「ユーザー設定の並べ替え」を選びます。
並べ替え画面が表示されます。
「先頭行をデータの見出しとして使用する」にチェックを入れます。
最優先されるキーの右側の「▼」をクリックすると見出しが表示されますので「フリガナ」をクリックして選びます。
続けて「レベルの追加」タブをクリックします。すると、「次に優先されるキー」が表示されますので右側の「▼」をクリックして「性別」を選び、順序欄の「▼」をクリックして「降順」を選び、「OK」ボタンをクリックします。
フリガナ欄があいうえお順に並び替えられ、さらにその中で性別が降順に並べ替えられています。
一定の範囲の条件でデータを抽出する
年齢の条件を一定範囲指定して抽出することもできますし、一定の条件の値よりも大きいものだけを抽出したり、小さいものだけを抽出したりすることもできます。
データを抽出したい列の「▼」をクリックして、メニューの中から「数値フィルター」をクリックして「指定の範囲内」をクリックします。この他にも指定の値に等しい・指定の値に等しくない・指定の値より大きい・平均より上などの抽出条件を指定することができます。
オートフィルターオプション画面が表示されますので、抽出したい数字を入力して「OK」ボタンをクリックします。
20歳以上35歳以下の抽出条件で表が表示されます。
解除するときは、年齢の横にあるボタンをクリックしてメニューを表示して「年齢からフィルターをクリア」をクリックします。すると抽出条件がクリアされます。
複数の条件でデータを抽出する
例えば、40歳以下の会社員を抽出したいときは「年齢」と「職業」の2つで抽出条件を指定して表示することができます。
年齢の「▼」をクリックして「数値フィルター」をクリックし「指定の値以下」を選びます。
オートフィルターオプション画面が表示されますので「40」を入力して、条件が「以下」になっていることを確認し「OK」ボタンをクリックします。
次に職業の「▼」をクリックします。表の中のフリーターや会社員などの項目すべてにチェックが入っていますので（すべて選択）のチェックをクリックして外すと、他のチェックもすべて外れます。
「会社員」だけにチェックを入れて「OK」ボタンをクリックします。
すると表が40歳以下の会社員のみが表示されます。
探している文字を素早く見つける検索方法
特定の文字列を探すときに便利なのが「検索」機能です。
ホームタブをクリックして、編集グループの「検索と選択」ボタンをクリックし「検索」をクリックします。
検索と置換画面が表示されますので検索タブをクリックして「検索する文字列」に検索したい文字を入力して「次を検索」ボタンをクリックします。今回、検索する文字列は住所の「福岡市」です。
「次を検索」ボタンをクリックすると、検索する文字列が入力されているセルが選択されます。再度、「次を検索」ボタンをクリックすると次に含まれるセルが選択されます。
「すべて検索」ボタンをクリックすると下に含まれるブック・シート・セル・値・数式が表示され、クリックすることで検索する文字が含まれるセルが選択されます。
文字列を一括で置き換える
1つや2つの文字の修正なら検索機能を使って修正してもいいですが、数が多かったりしたら修正・変更が大変です。そこで便利な機能が置換機能です。修正・変更漏れを無くすことができます。
ホームタブをクリックして、編集グループの「検索と選択」ボタンをクリックして「置換」をクリックします。
検索と置換画面が表示されますので、置換タブをクリックして、検索する文字列に修正前の文字列を入力し、置換後の文字列に変更後の文字列を入力して「すべて置換」ボタンをクリックします。
今回は「男」という文字列を「男性」にすべて変えようと思います。
「すべて置換」ボタンをクリックすると置換件数が表示されますので「OK」ボタンをクリックします。
性別欄の「男」がすべて「男性」に修正されました。
重複データを確認して削除する
顧客名簿の中に重複データがあるかどうかを確認する方法と削除する方法をお伝えします。
今回は分かりやすいように赤枠で表示しています。No4とNo14に同じデータがありますので削除したいと思います。
テーブルの中をクリックして、デザインタブをクリックし、ツールグループの「重複の削除」ボタンをクリックします。
Noだけチェックを外して他のチェックは全てチェックを入れて「OK」ボタンをクリックします。
「No」にチェックを入れるとNoが違うと削除対象とならないのでチェックを外します。他の項目がすべて一致するものがあれば削除するということです。
重複する1個の値が見つかり、削除されました。一意の値が19個残っています。と表示されますので「OK」ボタンをクリックします。
No4の情報が残り、No14のデータが削除されました。最初のデータが残り、後に入力したデータが削除されます。
エクセル顧客名簿管理表のテンプレート
今回作成したエクセルの顧客名簿の管理表をダウンロードできるようにしました。作成するときの参考テンプレートとして、または顧客名簿として使って頂いて構いません。ダウンロードは無料ですのでご自由にお使い下さい。記入されている情報はダミーです。
編集後記
顧客名簿の見出しは必要最低限だと思われる項目を入れていますので、使いやすいように追加・削除して下さい。
私は、顧客名簿に生年月日・趣味・部所名・お子さんの名前・年齢・学年などの情報も入れています。役立つのは飲み会でどのような飲み物が好きなのか、好きな食べ物、お酒が強いのかどうか、喫煙・禁煙なども情報としてあるといいですよ。
それらの情報を使うことで気遣いのある対応ができます。誕生日におめでとうメールを送ったり、飲み会をするときに好きなものが揃っているお店を探したりすることができます。