エクセル顧客管理 | 第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)

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

Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)
ファイルの一覧取得・削除(File)|Google Apps Script入門(1月24日)
フォルダの一覧取得・作成・削除(Folder)|Google Apps Script入門(1月24日)
フォルダとファイルを扱う(DriveApp)|Google Apps Script入門(1月24日)
スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)

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

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



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

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


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

    ↑ PAGE TOP