エクセル顧客管理 | 第24回.納品書を作成、商品情報を取得(2) | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第24回.納品書を作成、商品情報を取得(2)


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


前回、、明細部の「商品番号」で「商品マスタ」より情報を取得しました、


このコードを使って、「顧客番号」で「顧客一覧」より情報取得する部分を変更します。


変更箇所は限定的です。



コードの使い回しの参考にして下さい。


また、このコードをコピペして、自身で少し改造して使ってみると、良く理解できると思います。


先に、エクセルのサンプル をアップします。サイズが大きくなってきたのでZIPにしました。



「納品書」のシートモジュールです。

以下の2つのSubモジュールを新規に作成しました。


Private Sub 顧客情報取得(ByVal Target As Range)
  Dim lngRow As Long, lngCol As Long '行列の計算用
  Dim rngCode As Range        '商品番号のセル
  Dim rngVlookup As Range       'Vlookupの範囲
  Dim rngMatch As Range       'Matchの検索範囲
  Dim tCell As Range         '変更されたセルの取り出し
  
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  For Each tCell In Target
    With シート取得("顧客一覧")
      lngRow = .Range("顧客番号").Rows(.Range("顧客番号").Rows.Count).Row
      lngCol = .Cells.SpecialCells(xlLastCell).Column
      Set rngCode = Range("納品書_顧客番号").Cells(1, 1)
      Set rngVlookup = .Range(.Range("顧客番号").Cells(1, 1), _
                .Cells(lngRow, lngCol))
      Set rngMatch = .Range(開始セル取得("顧客一覧"), _
                .Cells(.Range("顧客一覧開始").Cells(1, 1).Row, lngCol))
    End With
    
    If Not Intersect(tCell, Range("納品書_顧客番号")) Is Nothing Then
      Range("納品書_郵便番号").Cells(1, 1) = ""
      Range("納品書_住所1").Cells(1, 1) = ""
      Range("納品書_住所2").Cells(1, 1) = ""
      Range("納品書_顧客名").Cells(1, 1) = ""
      Range("納品書_担当者名").Cells(1, 1) = ""
      Call Get顧客情報(Range("納品書_郵便番号").Cells(1, 1), _
              "納品書_郵便番号", rngCode, rngVlookup, rngMatch)
      Call Get顧客情報(Range("納品書_住所1").Cells(1, 1), _
              "納品書_住所1", rngCode, rngVlookup, rngMatch)
      Call Get顧客情報(Range("納品書_住所2").Cells(1, 1), _
              "納品書_住所2", rngCode, rngVlookup, rngMatch)
      Call Get顧客情報(Range("納品書_顧客名").Cells(1, 1), _
              "納品書_顧客名", rngCode, rngVlookup, rngMatch)
      Call Get顧客情報(Range("納品書_担当者名").Cells(1, 1), _
              "納品書_担当者名", rngCode, rngVlookup, rngMatch)
    End If

    Call Get顧客情報(tCell, "納品書_郵便番号", rngCode, rngVlookup, rngMatch)
    Call Get顧客情報(tCell, "納品書_住所1", rngCode, rngVlookup, rngMatch)
    Call Get顧客情報(tCell, "納品書_住所2", rngCode, rngVlookup, rngMatch)
    Call Get顧客情報(tCell, "納品書_顧客名", rngCode, rngVlookup, rngMatch)
    Call Get顧客情報(tCell, "納品書_担当者名", rngCode, rngVlookup, rngMatch)
  Next
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
End Sub

'tCell:対象セル
'strName:名前定義
'rngCode:検索値セル
'rngVlookup:Vlookup範囲
'rngMatch:Match検索範囲
Private Sub Get顧客情報(ByVal tCell As Range, _
          ByVal strName As String, _
          ByVal rngCode As Range, _
          ByVal rngVlookup As Range, _
          ByVal rngMatch As Range)
  Dim lngMatch As Integer   'Matchで取得した列番号
  If Intersect(tCell, Range(strName)) Is Nothing Or _
    Not IsEmpty(tCell) Then
    Exit Sub
  End If
  
  On Error Resume Next
  lngMatch = Application.WorksheetFunction.Match( _
          Cells(Range(strName).Cells(1, 1).Row, 1), _
          rngMatch, _
          0)
  tCell.Value = Application.WorksheetFunction.VLookup( _
          rngCode, _
          rngVlookup, _
          lngMatch, _
          False)
  On Error GoTo 0
End Sub


太字が、「商品情報取得」からの変更箇所です。


ほぼ、名前定義の名前の変更だけです。


唯一の違いは、


商品は複数行あるので、対象行の特定をしていましたが、それが不要になっていることと、


見出しが、A列固定にしている所です。


つまり、「番号」を入力し、VLOOKUPで取得する場合なら、


名前定義さえすれば、どちらかのパターンで、ほぼそのまま使えるはずです。



関数の3つの使い方

顧客情報の取得は、二転三転しました。


しかし、これは無駄ではありませんし、何より、その変遷が重要だと思います。


以下のように進みました。


手入力でVLOOKUPの関数を入力・・・MATCHの組み合わせ、それなりに難易度は高い。

VBAで、関数を自動作成・・・このような手段が必要な場合もあります。

VBAのVLookUpで、値を取得し設定


この3通りとも、必要な技術です。


どれが良いかは、ケースバイケースでしょう。


多分、VBAで関数を自動作成するのが、もっとも面倒だとは思います。



Subモジュールから、機能の一部を別のSubモジュールに


今回のコードでは、WorksheetFunctionを使う部分を、別モジュールにしています。


このように、Subモジュール内で、複数回同じ処理を繰り返す場合は、


その部分を別のモジュールにすることで、コードが短くすっきりします。


また、変更時も1箇所ですむようになり、「保守性」が高まります。


しかし、複数回同じ処理と言っても、全く同一ではありません。

(完全に同一処理では、繰り返す意味がありませんよね)


そこで、どこに違いがあるかを見極めます。


その違いが、全て変数に入れる事ができる違いならば、


それは、別モジュールにする事ができるということです。


そして、その変数が、引数となる訳です。


かといって、引数が何十個もあったり、


引数をIF文で複雑な判定をしなければならない場合も出てくるでしょう。


その場合は、さらに、その中の一部を別モジュールにしていきます。



最大公約数を段階的に求めていくような感じでしょうか。


例えば、


36、30、16、42、32


この最大公約数は・・・あれっ、何だ・・・6ではダメ、4もダメ・・・2ですかね。


この2をいきなり探して、Subモジュールにしたら、余計に面倒になってしまいます。


まず、36、30、42の最大公約数の6をSubモジュールにし、


次に、16、32の最大公約数の8をSubモジュールにします。


そして、さらに、6、8の最大公約数の2をSubモジュールにしていきます。


36、30、42は、それぞれ引数を、6、5、7にして、6のモジュールをCallします。


16、32は、それぞれ引数を、2、4にして、8のモジュールをCallします。


そして、6のモジュールからは引数3、8のモジュールからは引数4で、2のモジュールをCallします。


たまに見かけるのは、多くの引数を使い、さらに複雑な判定で処理を分けているものがあります。


それなら、コピーして、似たようなSubモジュールを複数作成し、


そこから、共通部分を見つけ出し、別のSubモジュールを作るべきです。



なんか、余計に分かりづらくなってしまいましたでしょうか。


言いたいのは、思考の過程です、いきなり複雑な事を考えるのではなく、


なるべく単純化して考えるということです。


私も、なかなか出来ないのですが、常に気を付けている事です。



WorksheetFunctionについて


これは、VBAにおいては有効に活用すべきです。


セルを上からぐるぐる回して探していたのでは、さすがに処理時間がかかってしまいます。


ワークシートで使う関数と、使い方(引数等)は同じです。


しかし、注意すべき点はあります。


これについては、そうですね、次々回あたりに詳しくやることにします。



それでは、また次回に。






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

第25回.納品書を作成、商品情報を取得(3)
第26回.WorksheetFunctionについて
第27回.RangeとCellsの深遠
第28回.納品書データをデータベース化(1)
第29回.納品書データをデータベース化(2)
第30回.配列の使い方について
第31回.売上一覧(伝票合計の一覧)を作成(1)

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

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日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

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



  • >
  • >
  • >
  • 納品書を作成、商品情報を取得(2)

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


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

    ↑ PAGE TOP