エクセル顧客管理 | 第17回.商品マスタを作成、2段階の可変リスト | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

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


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


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


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


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


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


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



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


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


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


このサイト内にある、
入力規則のリストを、2段階の絞り込みで作成1
入力規則のリストを、2段階の絞り込みで作成2
も参考にして下さい。


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


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



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


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


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


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




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は使い道の広い組み合わせです。


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


もうちょっとです


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


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



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行分を作成し、罫線を引きました。


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


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



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

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

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

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

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

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






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

第18回.納品書を作成、顧客情報を取得(1)
第19回.納品書を作成、顧客情報を取得(2)
第20回.納品書を作成、顧客情報を取得(3)
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
第24回.納品書を作成、商品情報を取得(2)

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

データクレンジングと名寄せ|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.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 商品マスタを作成、2段階の可変リスト

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


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

    ↑ PAGE TOP