エクセル顧客管理 | 第2回.顧客一覧のシートを作成 | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第2回.顧客一覧のシートを作成


エクセルで顧客管理を作ります、


顧客管理でメインとなる、顧客一覧のシートを作成しましょう、


シート名は、「顧客一覧」で良いでしょう。


上2行と、左1列を空けて、セルB3から使い始めましょう。


上2行は、後にボタン等を付けて、いろいろ機能追加するときに使います。


左1行は見た目だけですが、とりあえず空けておきます。


では、セルB3から横に使用する項目を入力していきます。


B3〜N3に

顧客番号

顧客区分

顧客名

顧客名カナ

郵便番号

住所

電話番号

携帯番号

URL

メール

担当者名

担当者携帯

担当メール


もっと多くの項目が必要になると思いますが、このシートで、いつでも追加・削除したものが、他のシートに反映するように作っていきますので、心配いりません。


ダウンこんな感じになると思います。


↑では、見出しに色を付け、罫線も100行分(103行まで)引きました。

ウインドウもE4で固定しています。

この行数も、いつでも増やせるので、とりあえずこの位でよいでしょう。


作成した項目の中で、「顧客区分」がちょっと分かりずらいと思います。


これは、顧客をグループ分けするために使用します。


ランク別にしたり、地域別にしたりと、いろいろ使い道があります。


ただし、その都度適当に入れてしまっては、グループ化できません。


そこで、「入力規則」の「リスト」を使用します。


「入力規則」の「リスト」を使用する前準備として、


このような項目名をリスト化する、専用のシート「項目名」を作成します。

そして、A列を顧客区分で使用しますので、


A1に、「顧客区分」と入力し、その下のA2から、実際の顧客区分を入力します。


ダウンこんな感じになると思います。


分かりやすく、ABCランクにしました。


ここまでは、問題ないですよね。



では、この顧客区分を使って、


シート「顧客一覧」の「顧客区分」に、「入力規則」の「リスト」を設定します。


名前定義

1.シート「項目名」を選択

2.Ctrl+F3を押して下さい。

  Ctrl+F3は名前定義の画面を表示するショートカットです。

  メニューからは、2003なら、「挿入」→「名前」→「定義」、

  2007以降なら、「数式」→「名前の管理」→「新規作成」

2.「名前」に、「顧客管理」と入力

3.2007以降の場合は、「範囲」は「ブック」のままにして下さい。

4.「参照範囲」に、「=OFFSET($A$2,0,0,COUNTA(項目名!$A:$A)-1,1)」と入力

5.「OK」


「入力規則」の「リスト」の設定

1.シート「顧客一覧」を選択

2.セルC4:C103までを選択。顧客区分の全範囲です。

3.2003なら、「データ」→「入力規則」、

  2007以降なら、「データ」→「データの入力規則」

4.「入力値の種類」で、「リスト」を選択」

5.「元の値」で、「顧客管理」と入力。F3で一覧から選択できます。

6.「OK」


これで、顧客区分にリストが設定されたはずです。


ここは説明が必要ですね。


まず、通常「リスト」で指定できるのは、同一シートだけです。

「入力規則」の「リスト」の設定の5.では、他シートは参照できません。

これを可能にするのが、名前定義です。

そして、シート「項目名」の追加・削除に自動的に対応させる為に、

「参照範囲」で、OFFSET関数を使用しています。

OFFSET(基準, 行数, 列数, 高さ, 幅)

基準のセルから指定の行数と列数だけシフトした位置にある、高さと幅のセル範囲を返す関数です。
COUNTA(範囲)

指定範囲の、空白でないセルの個数を返します。

-1は見出しがあるので、その分を引いています。


つまり、A2から0行0列シフト(同じA2)から、A列に入力された行数分の範囲になります。

これで、シート「項目名」の顧客区分への追加・削除に自動的に範囲が設定されます。


ついてこれていますか(笑)、次にいきますよ。



次は、「顧客名カナ」を自動で表示されるように、関数入れます。


セルE4で、「=PHONETIC(D4)」と入力して下さい。


PHONETIC(範囲)

ふりがなの文字列を取り出します。


セルE4をE103までコピーします。

コピーのテクニック(罫線を壊さずにコピーする方法)

E4をコピーし、E5:E103を選択後、「形式を選択して貼り付け」→「値」

または、E4のフィルハンドル(E4選択時の右下の小さい■)を、「Shift」を押しながらE103までドラッグ


少しは、それらしくなってきたと思います。


今回は、ここまで。



今日の復讐


名前定義

「入力規則」の「リスト」の設定
OFFSET(基準, 行数, 列数, 高さ, 幅)

COUNTA(範囲)PHONETIC(範囲)
PHONETIC(範囲)






同じテーマ「エクセル顧客管理」の記事

第3回.顧客登録のシートを作成、その前にマクロって何?
第4回.顧客登録のシートを作成(1)
第5回.顧客登録のシートを作成(2)
第6回.ここまでの復習
第7回.本格的なプログラムへ
第8回.顧客一覧より顧客データを取得
第9回.イベントを使ってマクロを起動させる

新着記事 ・・・新着記事一覧を見る

データクレンジングと名寄せ|ExcelマクロVBA技術解説(10月20日)
SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 顧客一覧のシートを作成

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP