エクセル顧客管理 | 第33回.売上一覧より納品書を作成 | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第33回.売上一覧より納品書を作成


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


今回は、「売上一覧」で、行を選択し、F1押下で、該当の「納品書」を表示します。


前回までですと、「納品書」は新規作成だけになっていましたので、


これで、修正が可能になります。



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

ダウンロード用の別サイトが表示されます。


まずは、「Mod共通」の「ファンクションF1」に以下を追加


Select Case True
    Case ActiveSheet Is シート取得("売上一覧")
      If Not IsEmpty(Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column)) And _
        ActiveCell.Row > 開始セル取得("売上一覧").Row Then
        Range("納品書_伝票番号") = Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column + 1)
        If Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column) = 0 Then
          Range("納品書_削除").ClearContents
        Else
          Range("納品書_削除") = Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column)
        End If
        シート取得("納品書").Select
        Call 売上明細取得
      End If


このモジュールは何度も出てきています。

F1に機能を追加する場合は、まずこのモジュールに、当該シートの処理を追加します。

Range("納品書_削除")は、「納品書」の伝票番号下に追加した名前定義です。

「削除」が0以外、つまり修正前の伝票の場合は、修正不可にする為の仕掛けです。



次に、「Mod納品書」に以下を追加します。


Sub 売上明細取得()
  Dim i1 As Long, i2 As Long
  Dim lngRow As Long, lngCol As Long
  Dim lngCol1 As Long, lngCol2 As Long
  Dim varAry1 As Variant, varAry2 As Variant
  Dim blnFirst As Boolean
  Dim varNo As Variant
  Dim varDel As Variant
  
  Call マクロ開始処理
  
  '明細行をクリアする
  lngRow = Range("納品書_商品番号").Rows.Count
  Range(Range("納品書_商品番号").Cells(2, 1), Range("納品書_商品番号").Cells(lngRow, 1)) = ""
  Range(Range("納品書_商品名").Cells(2, 1), Range("納品書_商品名").Cells(lngRow, 1)) = ""
  Range(Range("納品書_数量").Cells(2, 1), Range("納品書_数量").Cells(lngRow, 1)) = ""
  Range(Range("納品書_単位").Cells(2, 1), Range("納品書_単位").Cells(lngRow, 1)) = ""
  Range(Range("納品書_単価").Cells(2, 1), Range("納品書_単価").Cells(lngRow, 1)) = ""
'  Range(Range("納品書_金額").Cells(2, 1), Range("納品書_金額").Cells(lngRow, 1)) = ""
  Range(Range("納品書_備考").Cells(2, 1), Range("納品書_備考").Cells(lngRow, 1)) = ""
  
  varNo = Range("納品書_伝票番号")
  varDel = Range("納品書_削除")
  blnFirst = False
  With シート取得("売上明細")
    '最終行を取得
    i1 = .Cells(Rows.Count, 開始セル取得("売上明細").Column).End(xlUp).Row + 1
    '伝票番号、削除で検索
    lngCol1 = 登録列取得("納品書_削除")
    lngCol2 = 登録列取得("納品書_伝票番号")
    If lngCol2 > lngCol1 Then
      lngCol = lngCol2
    Else
      lngCol = lngCol1
    End If
    varNo = Range("納品書_伝票番号").Value
    varDel = Range("納品書_削除").Value
    varAry1 = .Range(.Cells(開始セル取得("売上明細").Row + 1, lngCol1), .Cells(i1 - 1, lngCol1))
    varAry2 = .Range(.Cells(開始セル取得("売上明細").Row + 1, lngCol2), .Cells(i1 - 1, lngCol2))
    For i1 = LBound(varAry1) To UBound(varAry1)
      If varAry1(i1, 1) = varDel And _
        varAry2(i1, 1) = varNo Then
        '明細行を取得
        i2 = i1 + 開始セル取得("売上明細").Row '売上明細の行数
        lngCol = 登録列取得("納品書_行番号")
        lngRow = .Cells(i2, lngCol).Value + 1
        Range("納品書_行番号").Cells(lngRow, 1) = .Cells(i2, lngCol)
        lngCol = 登録列取得("納品書_商品番号")
        Range("納品書_商品番号").Cells(lngRow, 1) = .Cells(i2, lngCol)
        lngCol = 登録列取得("納品書_商品名")
        Range("納品書_商品名").Cells(lngRow, 1) = .Cells(i2, lngCol)
        lngCol = 登録列取得("納品書_数量")
        Range("納品書_数量").Cells(lngRow, 1) = .Cells(i2, lngCol)
        lngCol = 登録列取得("納品書_単位")
        Range("納品書_単位").Cells(lngRow, 1) = .Cells(i2, lngCol)
        lngCol = 登録列取得("納品書_単価")
        Range("納品書_単価").Cells(lngRow, 1) = .Cells(i2, lngCol)
'        lngCol = 登録列取得("納品書_金額")
'        Range("納品書_金額").Cells(lngRow, 1) = .Cells(i2, lngCol)
        lngCol = 登録列取得("納品書_備考")
        Range("納品書_備考").Cells(lngRow, 1) = .Cells(i2, lngCol)
        If blnFirst = False Then
          lngCol = 登録列取得("納品書_伝票番号")
          Range("納品書_伝票番号").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_納品日")
          Range("納品書_納品日").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_自社担当")
          Range("納品書_自社担当").Value = .Cells(i2, lngCol)
'          lngCol = 登録列取得("納品書_税抜")
'          Range("納品書_税抜").Value = .Cells(i2, lngCol)
'          lngCol = 登録列取得("納品書_消費税")
'          Range("納品書_消費税").Value = .Cells(i2, lngCol)
'          lngCol = 登録列取得("納品書_総額")
'          Range("納品書_総額").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_郵便番号")
          Range("納品書_郵便番号").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_住所1")
          Range("納品書_住所1").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_住所2")
          Range("納品書_住所2").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_顧客名")
          Range("納品書_顧客名").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_担当者名")
          Range("納品書_担当者名").Value = .Cells(i2, lngCol)
          lngCol = 登録列取得("納品書_顧客番号")
          Range("納品書_顧客番号").Value = .Cells(i2, lngCol)
          blnFirst = True
        End If
      End If
    Next
  End With
  
  Call マクロ終了処理
End Sub


長いですね、でもほとんどは、個別の項目へのデータ設定です。


Function 登録列取得

を使用していますが、Subモジュール「売上明細へ登録」を作成した時に作ったものです。

このように、汎用性のあるFunctionを作っておくと、後々使う事が出来ます。


処理内容は、「売上明細へ登録」とほぼ同様です。

「売上明細へ登録」は、「納品書」→「売上明細」

「売上明細取得」は、「売上明細」→「納品書」

ですので、代入式の左右を入れ替えれば、ほぼ完成となっています。

もちろん、多少の修正は必要です。


全体の流れは、


明細行(商品の各行)をクリア

「売上明細」の「削除」から「伝票番号」までを、配列に入れます。

この時、「削除」と「伝票番号」の列番号の大小比較をしています。

配列を全行処理

  ↓

  「削除」「伝票番号」が一致している場合

    ↓

    「売上明細」→「納品書」を実行します。

    最初の1件目の場合のみ、ヘッダー項目(顧客情報等)を設定


注意点としては、

For i1 = LBound(varAry1) To UBound(varAry1)
の中で、途中でExitすることなく、最後まで処理しています。

一見無駄なようです、他の伝票になった時点でExitすれば良いように思えます。

しかし、エクセルはデータを使用者が自由に触る事が可能ですので、

ソート等をしていたりすると、伝票が、泣きわかれになっている場合もあります。

このような場合も想定して、1件残らず処理しています。

もちろん、先にソートしておく事も方法としては有効です。


さらに、先頭に「'」になっている行が多くあります。

これは、コメント行なのはおわかりだろうと思います。

では、何故、わざわざ残しているかが問題です。

読み手に対し、金額は計算式があるので、値は設定しませんと伝えています。

これがないと、読み手は、項目漏れがないか等が気になり、確認作業をしたりします。

ここでは、そんな心配はいりません、全項目書いてあります、と伝えています。



修正前の伝票、つまり「削除」が>1の伝票の場合は、修正不可にする為に、


Subモジュール「売上明細へ登録」の先頭に、以下を追加します。


If Range("納品書_削除") > 0 Then
  MsgBox ("この伝票は修正され、最新の伝票ではありません。" & vbLf & vbLf & _
      "削除が0の伝票以外は修正登録できません。")
  Exit Sub
End If


これは、みた通りです、削除>0、つまり修正前の伝票の場合は、

メッセージを表示して、処理を抜けるようにしています。



今回は、取り立てて説明するような内容がありませんでした。


そこで、何か無いかと思案して、セルのマージについて書きます。



結合セルの扱いについて


「売上明細取得」の最初の方で、
Range(Range("納品書_商品名").Cells(2, 1), Range("納品書_商品名").Cells(lngRow, 1)) = ""
があります。

これを

Range(Range("納品書_商品名").Cells(2, 1), Range("納品書_商品名").Cells(lngRow, 1)).ClearContents

としたら、どうなるでしょうか。

「結合されたセルの一部を変更することはできません」と、エクセルに叱られます。

Range("納品書_商品名")は、4つのセルの結合になっているからです。


ではなぜ、「 = ""」は良いのでしょうか、少し疑問ですよね。

まず、ClearContentsはメソッドで、「 = ""」は、Valueプロパティへの値の代入です。

全く違うものですが、何故、ClearContentsがエラーを返しているのかの詳細は、私にはわかりません。

この場合の、正しい記述としては、.「MergeArea.ClearContents」となります。

ただ、値を消すだけなら、「 = ""」で十分ですよね。


.MergeArea

は、そのセルが含まれる結合セルの範囲(Rangeオブジェクト)を返す、プロパティです。


結合セルでも、大抵のプロパティは、個別に設定してもエラーにはなりません。

しかし、先頭セル以外は無視されます。

従って、結合セルの先頭に対して、指定する必要があります。

これは、値の取得時についても同じです、先頭セル以外は値を取得できません。


では、先頭セルの取得はどうすれば良いでしょうか。

ネットを少し検索したのですが、掲示板等の回答では、

For Each Rangeの変数 In 指定のセル.MergeArea

このForで、結合セルを全て処理するように書いてある事が多いようです。

これでは面倒です。


以下のどれかで簡単に取得可能です。

.MergeArea.Item(1)

.MergeArea.Item(1, 1)

.MergeArea.Cells(1, 1)

いずれでも同じ結果になります。

考え方として、最も正しい指定は、Item(1)だと思われます。

上のFor Eachの最初に受け取るセルと同義になります。

下の2つは、本来は、ちょっと無理やりの指定です。

(1, 1)の行列指定が、MergeAreaを超えてしまっても取得できてしまいます。

(私は、結構使っていますが・・・)

これらについては、「第27回.RangeとCellsの深遠 」でも書いていますので、参考にして下さい。



今回は、書いてみたら、プログラムの説明があまり無かったので、急遽、結合セルについて説明をしました。


今までに何度も書いていますが、エクセルはシートの設計が第一です。


できれば、結合セルなど使用しない方が良いです。


なるべく単純化することが重要です。


作成中のエクセルでは、あまり単純化しては、ブログのネタ切れになってしまいますので(笑)


決して、このようなエクセルがお手本と言う訳ではない事だけは理解しておいて下さい。


この点は、誤解されないようにお願いします。






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

第34回.伝票番号の自動採番機能を追加
第35回.メニューを作成
第36回.最終回
その後1、CSV出力を追加
その後2、ベクター掲載
第1回.どんなソフトにするか
第2回.顧客一覧のシートを作成

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

空白セルを正しく判定する方法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日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)

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

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



  • >
  • >
  • >
  • 売上一覧より納品書を作成

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


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




    ↑ PAGE TOP