エクセル顧客管理
商品マスタを作成、2段階の可変リスト

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

第17回.商品マスタを作成、2段階の可変リスト


エクセルで顧客管理を作ろう、


GW特別号が続いたので、そろそろ本題のソフト作成を進めましょう。


でも、技術解説していた方が楽しい気もしますが・・・


これからも時々、やることにします。


どうしても、ソフト作成の説明では、VBAそのものの解説がおろそかになってしまうので、


それを補完する形で、特集で技術解説をしようと思います。



ソフト作成は、売上の登録へ進んでいきます。


その前に、最低限必要な、商品マスタを作成します。


ここでは、2段階の可変リストを作ります。


このサイト内にある、
入力規則のリストを、2段階の絞り込みで作成1

一覧リストを使用して、「入力規則」の「リスト」を作成し、「リスト」の選択結果により、となりの「リスト」の内容を自動で変化させます、さらに、一覧リストへの追加・削除に自動対応させるものです。入力規則のリストを、追加・削除に自動対応で作成 を理解した上でお読みください。
入力規則のリストを、2段階の絞り込みで作成2
一覧リストを使用して、「入力規則」の「リスト」を作成し、「リスト」の選択結果により、となりの「リスト」の内容を自動で変化させます、「入力規則のリストを、2段階の絞り込みで作成1」こちらのリストの縦横を入れ替えたパターンになります。入力規則のリストを、追加・削除に自動対応で作成一覧リストを使用して、「入力規則」の「リ…
も参考にして下さい。


さらにその前に、商品マスタで使用する、分類マスタを先に作りましょう。


以下のようなシートを作成して下さい、シート名は「分類マスタ」とします。


Excel VBA 解説

B2から横に、大分類名称を入れていきます。


大分類の2行目以下に、それぞれに含まれる中分類を入れます。


縦横とも可変で参照可能にしますので、件数はご自由にどうぞ!


続いて、シート「商品マスタ」を作成します。


Excel VBA 解説

B3から横に順に、「商品番号」「大分類」「中分類」「商品名」「規格」「入数」「単位」「単価」「備考」


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


では関数を入れていきます。


一息入れて


大分類の名前を定義します。

  1. シート「分類マスタ」を選択します。
  2. Ctrl+F3で名前定義を起動する。メニュー等からの起動はバージョン毎に違います。
  3. 「名前」に「大分類」と入力。
  4. 2007以降の場合、範囲は「ブック」、2003にはありません。
  5. 「参照範囲」に「=OFFSET(分類マスタ!$B$1,0,0,1,COUNTA(分類マスタ!$1:$1))」
  6. 「OK」

中分類の名前を定義します。

  1. シート「商品マスタ」のセルD4を選択します。選択セルが違うと正しく設定できません。
  2. Ctrl+F3で名前定義を起動する。メニュー等からの起動はバージョン毎に違います。
  3. 「名前」に「中分類」と入力。
  4. 2007以降の場合、範囲は「ブック」、2003にはありません。
  5. 「参照範囲」に「=OFFSET(分類マスタ!$B$2,0,MATCH(!$C4,大分類,0)-1,COUNTA(OFFSET(分類マスタ!$B:$B,0,MATCH(!$C4,大分類,0)-1))-1,1)」
  6. 「OK」

大分類の[入力規則]の[リスト]の設定です。

  1. シート「商品マスタ」のC4を選択
  2. [データ]-[入力規則]
  3. [設定]の[入力値の種類]で[リスト]を選択
  4. [元の値] に「大分類」と入力。F3を押すと名前定義の一覧から選択できます。
  5. 「OK」

中分類の[入力規則]の[リスト]の設定です。

  1. シート「商品マスタ」のD4を選択
  2. [データ]-[入力規則]
  3. [設定]の[入力値の種類]で[リスト]を選択
  4. [元の値] に「中分類」と入力。F3を押すと名前定義の一覧から選択できます。
  5. 「OK」

以上で2段階のリスト選択が完成です。



C4の大分類を入力選択すると、D4のリストにはその大分類に含まれる中分類だけが表示されます。


また、シート「分類マスタ」の大分類も中分類も追加・削除に自動対応されます。


説明が必要な部分は、


「=OFFSET(分類マスタ!$B$2,0,MATCH(!$C4,大分類,0)-1,COUNTA(OFFSET(分類マスタ!$B:$B,0,MATCH(!$C4,大分類,0)-1))-1,1)」


まず、OFFSETは、OFFSET(基準, 行数, 列数, 高さ, 幅)です。
基準のセルから指定の行数列数だけシフトした位置にある、高さのセル範囲を返す関数です。
また、MATCHは、MATCH(検査値, 検査範囲, 照合の型)です。
検査値検査範囲の中で何番目にあるかを返す関数です。


結局、C列の値を、大分類のリストの何番目かを探し、

その列数分ずれた中分類のリストを使用している事になります。


さらに、MATCH(!$A2,の!は間違いではありません、これが無いと正しく出来ません。
あえて指定するなら、商品マスタ!となりますが、汎用性を持たせ他のシートでも使えるようにしています。


ここでの、OFFSET、MATCH、COUNTAは使い道の広い組み合わせです。


覚えれば、いろいろな事ができるようになります。


もうちょっとです


続いて、単位のリストを作成します。


シート「項目名」に以下を追加して下さい。


Excel VBA 解説

C2以下はお好きなだけ入れられます、行数は可変に対応します。


もうこれは、大分類と同じですね。


単位の名前を定義します。

  1. シート「項目名」を選択します。
  2. Ctrl+F3で名前定義を起動する。メニュー等からの起動はバージョン毎に違います。
  3. 「名前」に「単位」と入力。
  4. 2007以降の場合、範囲は「ブック」、2003にはありません。
  5. 「参照範囲」に「=OFFSET(項目名!$C$2,0,0,COUNTA(項目名!$C:$C)-1,1)」
  6. 「OK」

単位の[入力規則]の[リスト]の設定です。

  1. シート「商品マスタ」のH4を選択
  2. [データ]-[入力規則]
  3. [設定]の[入力値の種類]で[リスト]を選択
  4. [元の値] に「単位」と入力。F3を押すと名前定義の一覧から選択できます。
  5. 「OK」

これで、関数の設定は全て終了しました。


シート「商品マスタ」の4行目を使用する行までコピーして下さい。


私は、とりあえず100行分を作成し、罫線を引きました。


今回は、関数とリストの設定だけでした。


このような関数の使い方は、「関数・超技関数 」をご覧下さい、いろいろ掲載しています。


エクセルの関数の応用技の解説。関数サンプルと必須の基本技術から応用・高等テクニックまでを紹介しています。1年後の日付、○か月後の日付 複数条件の合計・件数・サンプルデータ・複数条件の合計・複数条件の件数・スピルと新関数 入力規則のリストを、追加・削除に自動対応で作成一覧リストを使用して、「入力規則」の「リスト」を作…



関数は工夫次第で、かなりの事が可能です。

ただし、どうしても複雑になってしまいます。

内容によっては、VBAで処理した方が良い場合も多いです。

しかし、上記内容をVBAで全てやろうとしたら、かなり大変です。

関数と、VBAの使い分けが重要です。

豊富な関数が使える事がエクセルの最大の利点ですので、上手く活用しましょう。





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

第14回.オブジェクトとプロパティの真実(GW特別号No1)
第15回.記述による処理速度の違い(GW特別号No2)
第16回.処理速度の向上はどこまでやれば良い(GW特別号No3)
第17回.商品マスタを作成、2段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
第24回.納品書を作成、商品情報を取得(2)


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

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
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)


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

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」をお願いいたします。
本文下部へ