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



最終更新日:2014-11-11

第18回.納品書を作成、顧客情報を取得(1)


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


前回までで、顧客マスタ、商品マスタができましたので、


いよいよ、売上関係の作成に入ります。


呼び名は、売上伝票や納品伝票等々になりますが、名前は自由に変更可能なので、


今回は「納品書」にしました。



マイクロソフト テンプレートで直ぐにみつかったので(笑)


迷わず先頭のテンプレートをダウンロードしたら・・・ちょっと使いづらいかな?どうかな?


多少手直ししましたが、まあ、一般的といえば一般的かな。


実際、レイアウトは何でもOKです。


最低限の決まりごと(項目名や名前定義)を守れば、後はマクロで対応します。


さすがに、今回は文章だけでは説明しきれないので、


先に、エクセルのサンプル をアップしておきます。


実際のエクセルを見ながら確認して下さい。



では、シート「納品書」を作成します。



上記画像は、既にいろいろ組み込んだ状態です。


事前作業として、以下を行います。


1.シート「設定」に、追加したシートを全て追加します。

  設定済のシートの下に、未設定のシートを全て設定します。

  「納品書」「商品マスタ」「分類マスタ」です。

  =RIGHT(CELL("filename",納品書!A1),LEN(CELL("filename",納品書!A1))-FIND("]",CELL("filename",納品書!A1)))

  上のシート名のみ変更します。

2.「顧客一覧」の顧客番号に対し、可変の名前定義をします。

  可変の名前定義は何度も出てますので、計算式のみ

  =OFFSET(顧客一覧!$B$4,0,0,COUNTA(顧客一覧!$B:$B)-1,1)

3.「納品書」の顧客情報のA列に、項目名を入力

  上の画像または、サンプルエクセルを参考にして下さい。

  住所〜担当者のA列に、「顧客一覧」の項目名を入力します。

  サンプルでは、文字を赤くしています。

4.「納品書」の顧客情報の各セルに名前定義します。

  「納品書_顧客番号」

  「納品書_郵便番号」

  「納品書_住所1」

  「納品書_住所2」

  「納品書_顧客名」

  「納品書_担当者名」

  にして下さい、マクロ内でこの名前を使用します。



さて、どこからやりましょうか。

まずは、顧客番号を手入力することで、顧客情報を表示するようにしましょう。


最初は、手入力で関数を入れます。

="〒"& VLOOKUP($B$11,顧客一覧!$B$4:$O$94,MATCH($A4,顧客一覧!$B$3:$O$3,0),FALSE)

$B$11が顧客番号のセルです。

もうそろそろ、当ブログ読者は、直ぐに分かりますよね


一応説明しますね。

=VLOOKUP(検索値,範囲,列番号,検索方法)

範囲の左端の列で検索値を検索し、見つかった行の列番号のセルの値を返します。

検索方法がFalseは完全一致になります。

MATCH(検査値, 検査範囲, 照合の型)

検査値検査範囲の中で何番目にあるかを返す関数です。

照合の型が0は完全一致です。


この数式で、「顧客番号」で「顧客一覧」から該当行を見つけ、A列の項目名の列のデータを取得します。

この数式は、絶対参照と、相対参照が組み合わされていますので、他の項目にもコピー出来ます。

ただし、「="〒"&・・・」 は、「=・・・ & " 御中"」等に変更して下さい。


これで、顧客番号を入力すると、顧客情報が表示されるようになります。


これだけで良いようにも思えるかもしれませんが、ここからが本番なのです。

この状態では、以下の問題があります。

1.顧客番号なんて記憶していない。

  毎回、顧客一覧で確認してからでないと入力出来ない。

2.たまに送付先の担当者を変更することがあるけど、

  変更したら、計算式が消えてしまう。


そうなんです、このような問題が生産性を落とします。

たまに、このような問題を抱えたままのソフトも存在しますが、論外です。

なんとかしましょう。


気合を入れなおして


まず、計算式の再設定を完全自動で行います。


シート「納品書」のシートモジュールに以下を作成します。

シートモジュールは説明しましたよね、

VBEで該当シートを選択し、「表示」→「コード」、または、F7

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim strSht As String
  Dim lngRow As Long, lngCol As Long
  Dim adrCode As String
  Dim strAdr1 As String, strAdr2 As String
  Dim strCalc As String
  
  adrCode = Range("納品書_顧客番号").Address(1, 1, xlR1C1)
  With シート取得("顧客一覧")
    strSht = .Name & "!"
    lngRow = .Range("顧客番号").Rows.Count
    lngCol = .Cells.SpecialCells(xlLastCell).Column - .Range("顧客一覧開始").Column
    strAdr1 = .Range(.Range("顧客番号").Cells(1, 1), _
            .Range("顧客番号").Cells(1, 1).Offset(lngRow - 1, lngCol)).Address(1, 1, xlR1C1)
    strAdr2 = .Range(.Range("顧客一覧開始").Cells(1, 1), _
            .Range("顧客一覧開始").Cells(1, 1).Offset(0, lngCol)).Address(1, 1, xlR1C1)
  End With
  
  strCalc = "VLOOKUP(" & adrCode & "," & strSht & strAdr1 & ",MATCH(RC1," & strSht & strAdr2 & ",0),FALSE)"
  
  Select Case True
    Case Not Intersect(Target.Cells(1.1), Range("納品書_顧客番号")) Is Nothing
      Range("納品書_郵便番号").FormulaR1C1 = "=""〒""&" & strCalc
      Range("納品書_住所1").FormulaR1C1 = "=" & strCalc
      Range("納品書_住所2").FormulaR1C1 = "=" & strCalc
      Range("納品書_顧客名").FormulaR1C1 = "=" & strCalc & "&"" 御中"""
      Range("納品書_担当者名").FormulaR1C1 = "=" & strCalc & "&"" 様"""
    Case Not Intersect(Target.Cells(1.1), Range("納品書_郵便番号")) Is Nothing
      If IsEmpty(Cells(Target.Row, Target.Column)) Then
        Target.Cells(1.1).FormulaR1C1 = "=""〒""&" & strCalc
      End If
    Case Not Intersect(Target.Cells(1.1), Range("納品書_住所1")) Is Nothing, _
        Not Intersect(Target.Cells(1.1), Range("納品書_住所2")) Is Nothing
      If IsEmpty(Cells(Target.Row, Target.Column)) Then
        Target.Cells(1.1).FormulaR1C1 = "=" & strCalc
      End If
    Case Not Intersect(Target.Cells(1.1), Range("納品書_顧客名")) Is Nothing
      If IsEmpty(Cells(Target.Row, Target.Column)) Then
        Target.Cells(1.1).FormulaR1C1 = "=" & strCalc & "&"" 御中"""
      End If
    Case Not Intersect(Target.Cells(1.1), Range("納品書_担当者名")) Is Nothing
      If IsEmpty(Cells(Target.Row, Target.Column)) Then
        Target.Cells(1.1).FormulaR1C1 = "=" & strCalc & "&"" 様"""
      End If
  End Select
End Sub


にしても、ちょっとこれは・・・ですね、私もそう思います。


まず何をしているかと言うと、

顧客番号が変更された場合は、計算式を全て再設定します。

各項目は、「Delete」等で消去された場合は、計算式を再設定します。


つまり、直接変更すれば、それでよし、消したら計算式が戻ります。

どうです、便利でしょ。

よく、計算式設定のボタンとか付けますけど、これなら意識する必要がありません。


とにかく、説明をしましょう。


まず、これは、シートに変更があった場合に起動されるイベントです。

シートに変更があった場合に、どのセルが変更されたかを判断し処理します。


.Address(行参照方法, 列参照方法, xlR1C1)

これが何度も出てきますが、Rangeのアドレスを絶対参照のR1C1形式で取得します。


.Offset(行, 列)

これは、対象セルより、行数、列数ずれた位置のセルを返します。


adrCodeには、顧客番号のアドレス、つまりVLOOKUPの検索値
strAdr1には、「顧客一覧」のデータ部分の範囲のアドレス、VLOOKUPの検索範囲

strAdr2には、「顧客一覧」の見出し部分の範囲のアドレス、MATCHの検査範囲

どうやって、この範囲を算出しているかは、プログラムをじっーーーと見つめていれば分かります(笑)

正直、これを説明してもあまり意味がないように思えます。

頭の体操です、現在持っている情報をどのように組み合わせて、目的の情報を得るかです。

始点のセルがわかり、行数、列数がわかれば、範囲を指定できますよね。

私も結構悩みました。


これを基に、strCalcに、VLOOKUPの計算式部分を作成します。

後の、計算式の設定時に記述を簡潔にするためです。


Select Case True
    Case Not Intersect(Target.Cells(1.1), Range("納品書_顧客番号")) Is Nothing

ここが問題ですよね。

Targetは、変更されたセル範囲が入っています。

Intersectは、指定された複数の範囲の共通部分を返します。

共通部分が無い場合は、Nothingが帰ります。

Notを付けているので、Nothing以外の時に、Trueとなり、Caseが成立します。


また、Case ○, △は、2つの条件を同時に指定しています。ORと同じです。


後は、変更されたセル毎に、計算式を設定しています。


ちょっと面倒でしょうか、「〒」「御中」「様」等を付けなければ、もう少し簡単なんですが。

書式設定だと、いろいろまずそうなのでVBAにて設定しています。


また、Intersectなんて、私も初めて使いました。

今回の場合、単純には、

Target.Cells(1.1),AddressとRange("納品書_顧客番号").Cells(1.1),Addressを比較すれば済みますが、

せっかく名前定義を使っているのだから、もう少しスマートな記述にしようと思い、

そういえば、何かあったな・・・で探しました(笑)

せっかくブログを書くので、いろいろな事を紹介する意味もあります。



とりあえず、今回はここまで。


続きは、次回に!






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

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

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

最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
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日)

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

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技術解説



  • >
  • >
  • >
  • 納品書を作成、顧客情報を取得(1)

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


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

    ↑ PAGE TOP