エクセル顧客管理
商品マスタを作成、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)

エクセルで顧客管理を作ろう、今回は、ゴールデンウイークでもあり、説明漏れの部分について、詳細な説明をすることにします。(ゴールデンウイークと関係ないのでは、と突っ込まないで) オブジェクトとプロパティについて、もう少し詳しい解説をします。対象は、VBA中級以上になると思いますが、初級の方でも、VBAって奥が深いんだ…
第15回.記述による処理速度の違い(GW特別号No2)
エクセルで顧客管理を作ろう、ゴールデンウイーク特別号No2です、今回は、記述の違いで、どの程度処理速度に変化があるかを検証します。テストは以下の4点です。1.変数の型指定 2.罫線の引き方 3.行高の変更 4.配列を使用した処理 この4点について、実測して検証してみました。
第16回.処理速度の向上はどこまでやれば良い(GW特別号No3)
エクセルで顧客管理を作ろう、ゴールデンウイーク特別号No3です、前回の「記述による処理速度の違い」が好評(本当に?)のようでしたので、再度、処理速度に関する内容をお届けします。実際に、作成中の顧客管理で処理速度対策を施してみます。具体的にどの処理を対策するかですが、以前から気になっていたのですが、「顧客一覧」でF1…
第17回.商品マスタを作成、2段階の可変リスト
第18回.納品書を作成、顧客情報を取得(1)
エクセルで顧客管理を作成します、前回までで、顧客マスタ、商品マスタができましたので、いよいよ、売上関係の作成に入ります。呼び名は、売上伝票や納品伝票等々になりますが、名前は自由に変更可能なので、今回は「納品書」にしました。マイクロソフトテンプレートで直ぐにみつかったので(笑) 迷わず先頭のテンプレートをダウンロード…
第19回.納品書を作成、顧客情報を取得(2)
エクセルで顧客管理を作成します、前回の続きです、再度、エクセルのサンプルです、実際のエクセルを見ながら確認して下さい。まず、シート「納品書」です。今回は、シート上部の、コンボボックスの機能説明になります。
第20回.納品書を作成、顧客情報を取得(3)
エクセルで顧客管理を作成します、とうとう、20回まできました、では、前回の続き、3部作の最終です。再度、エクセルのサンプルです。実際のエクセルを見ながら確認して下さい。まず、シート「納品書」です。今回は、シート上部の、ボタンの機能説明になります。
第21回.イベント処理について
エクセルで顧客管理を作ります、前回で20回を達成しました、気張らず、気負わず、気を抜かず、これからも続けて行きます。今回は、特に前3回が駆け足で、説明不足であった事もあり、補足しつつ復習しましょう。その中でも、おそらく初心者が特に分かりづらいと思われるイベントについて説明します。
第22回.コントールについて
エクセルで顧客管理を作ります、今回は、コントロールについての、補足と復習です、マクロを作っても、起動方法がマクロの一覧からでは、ちょっと淋しいです。また、色々なコントロールを使えるようになれば、出来る事が増えてきます。まずは、この違いから。
第23回.納品書を作成、商品情報を取得(1)
エクセルで顧客管理を作ります、前回までで、「顧客番号」で、「顧客一覧」より情報取得が出来ました、今回は、明細部の「商品番号」で「商品マスタ」より情報取得します。今度は、複数行ですので、さらに難しくなります。本来なら、顧客番号で作成した、コードの使い回しをしたいところですが、別の方法にします。
第24回.納品書を作成、商品情報を取得(2)
エクセルで顧客管理を作ります、前回、、明細部の「商品番号」で「商品マスタ」より情報を取得しました、このコードを使って、「顧客番号」で「顧客一覧」より情報取得する部分を変更します。変更箇所は限定的です。コードの使い回しの参考にして下さい。


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

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
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)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.RangeとCellsの使い方|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.並べ替え(Sort)|VBA入門
10.メッセージボックス(MsgBox関数)|VBA入門




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


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


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