エクセル顧客管理
顧客一覧のシートを作成

Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材
公開日:2013年5月以前 最終更新日:2014-11-11

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


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


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


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


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


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


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


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


B3~N3に

顧客番号

顧客区分

顧客名

顧客名カナ

郵便番号

住所

電話番号

携帯番号

URL

メール

担当者名

担当者携帯

担当メール


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


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

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

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

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


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


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


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


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


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


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


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

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


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


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

Excel VBA 解説
分かりやすく、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(範囲)





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

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


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

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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