エクセル顧客管理 | 第31回.売上一覧(伝票合計の一覧)を作成(1) | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第31回.売上一覧(伝票合計の一覧)を作成(1)


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


作成された、「売上明細」より、伝票合計の一覧を作成します、


いろいろなやり方が考えられます、


まずは、配列を使用せずに、エクセルの機能を使用してやってみます。


どの程度のパフォーマンスが得られるかの実験です。


次回は、配列を使っての処理をやります。


その違いを確認して下さい。


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

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



まずは、シート「売上一覧」を作成します。


以下のように作成しました。


>

上部は、検索条件を範囲指定、および、部分一致でも指定できるようにしました。


ちょっと面倒な設計です、本来なら、もっと単純にした方が良いのですが、


ブログでのサンプルとしては、面白いかなと考えた結果です。


事前作業

B2には、「売上一覧_開始」と名前定義しました。

シート「設定」への追加、「Mos共通」の「シート取得」、「開始セル取得」の修正は、

前例にならって直しています。


左端のボタンは次回のモジュール起動用です。

また、共通モジュールとして、新たに、「Mod売上一覧」を作成し、ここに追加します。



では、今回のモジュールです。


「Mod売上一覧」に以下を追加します。


Sub 売上一覧作成1()
  Dim iMax As Long, jMax As Long
  Dim i As Long, j As Long, k As Long
  Dim rtn As Integer
  
  rtn = MsgBox("売上一覧を作成してもよろしいですか?" & vbLf & "(配列未使用)", vbYesNo, "確認")
  If rtn = vbNo Then Exit Sub
  
  Debug.Print Now
  Call マクロ開始処理
  
  Application.StatusBar = "作業列を作成中。"
  DoEvents

  '検索結果範囲をクリア
  Range(Rows(開始セル取得("売上一覧").Row + cns間隔), Rows(Cells.SpecialCells(xlLastCell).Row)).Delete
  
  With シート取得("売上明細")
    'キーとなる削除と伝票番号を結構し、新たなキー列を作成
    iMax = .Cells(Rows.Count, 開始セル取得("売上明細").Column).End(xlUp).Row
    jMax = 開始セル取得("売上明細").End(xlToRight).Column
    For i = 開始セル取得("売上明細").Row + 1 To _
        .Cells(Rows.Count, 開始セル取得("売上明細").Column).End(xlUp).Row
      .Cells(i, jMax + 2) = .Cells(i, 1) & "," & .Cells(i, 2)
    Next
    Application.StatusBar = "検索キーを重複無視で取り込み。"
    DoEvents
    'フィルターオプションを使用して、重複を無視して複写する。
    .Range(.Cells(開始セル取得("売上明細").Offset(1, 0).Row, jMax + 2), _
        .Cells(iMax, jMax + 2)).AdvancedFilter Action:=xlFilterCopy, _
      CopyToRange:=Range("L7"), Unique:=True
    
    For i = 7 To Cells(Rows.Count, 12).End(xlUp).Row
      Application.StatusBar = i & "/" & Cells(Rows.Count, 12).End(xlUp).Row - 6 & " 行目を処理中"
      DoEvents
      '結合されたキー列で検索する
      j = Application.WorksheetFunction.Match(Cells(i, 12), _
        .Range(.Cells(4, jMax + 2), .Cells(iMax, jMax + 2)), 0)
      '列検索はせずに、固定列でデータを取得
      Cells(i, 2) = .Cells(j + 3, 1)
      Cells(i, 3) = .Cells(j + 3, 2)
      Cells(i, 4) = .Cells(j + 3, 3)
      Cells(i, 5) = .Cells(j + 3, 4)
      Cells(i, 6) = .Cells(j + 3, 5)
      Cells(i, 7) = .Cells(j + 3, 6)
      Cells(i, 8) = .Cells(j + 3, 19)
      Cells(i, 9) = .Cells(j + 3, 20)
      Cells(i, 10) = .Cells(j + 3, 21)
    Next
  End With
  
  Call マクロ終了処理
  Debug.Print Now
End Sub


上記プログラムは未完成です。


とりあえず、評価する為のテストプログラムです。


配列を使用せずに、エクセルの機能をなるべく使ってプログラミングしています。


検索条件での絞り込みは実装していません。



このプログラムが、やっている処理の流れは、


「売上明細」に、作業列として、削除・伝票番号の結合列を作成

フィルタオプションで、重複データを無視して、

データを「売上一覧」にコピーします。

削除・伝票番号の結合列を基に、「売上明細」のデータを取得します。


となっています。


では、今まで使用していない命令のみ説明します。


Application.StatusBar = "作業列を作成中。"
ステータスバーにメッセージを表示します。

時間のかかる処理の場合は、使用者に進捗を知らせる為に使用出来ます。

これに伴い、「マクロ終了処理」に、

Application.StatusBar = False

を追加しました。これは、ステータスバーを元にもどします。


DoEvents

発生したイベントをOSに処理させます。

これにより、ステータスバーへの表示を有効にしています。


.Range(.Cells(開始セル取得("売上明細").Offset(1, 0).Row, jMax + 2), _
      .Cells(iMax, jMax + 2)).AdvancedFilter Action:=xlFilterCopy, _
      CopyToRange:=Range("L7"), Unique:=True
今回の目玉ですね。

これは、ワークシートで、フィルターオプションを使っての「重複するデータは無視する」の機能です。

フィルターオプションは、

2003なら、「データ」→「フィルタ」→「フィルタオプションの設定」

2007以降ならなら、「データ」→「並べ替えとフィルタ」→「詳細設定」

になります、この使い方については、今回は割愛します。


j = Application.WorksheetFunction.Match(Cells(i, 12), _
    .Range(.Cells(4, jMax + 2), .Cells(iMax, jMax + 2)), 0)
上でコピーした結合列を基に、「売上明細」の結合列を検索しています。


上記以降は、当該行について、横の項目を列固定でデータコピーしています。

この部分は、本来は、見出しの項目名で、列を検索する必要があるのですが、

テストプログラムとして、固定で処理しています。


この時点で、データを3万件入れて、テストしました。


処理に要した時間は、2分40秒でした。


テストPCは古いノートPCですが、予想以上のスピードでした。

これなら、最新のPCなら問題ないかもしれません。

ただし、前記のように、横項目の検索や、検索条件の絞り込みは未実装です。

もっとも、絞り込みに関しては、フィルターを使えば処理時間は問題ありませんが。


しかし、実際に時間のかかっているのは、データのコピーです。

1行づつコピーはやはり遅いです。



そこで、もっと早く処理する方法がありそうです。


「Mod売上一覧」に以下を追加します


Sub 売上一覧作成2()
  Dim iMax As Long, jMax As Long
  Dim i As Long, j As Long, k As Long
  Dim rtn As Integer
  
  rtn = MsgBox("売上一覧を作成してもよろしいですか?" & vbLf & "(配列未使用2)", vbYesNo, "確認")
  If rtn = vbNo Then Exit Sub
  
  Debug.Print Now
  Call マクロ開始処理
  
  Application.StatusBar = "作業列を作成中。"
  DoEvents

  '検索結果範囲をクリア
  Range(Rows(開始セル取得("売上一覧").Row + cns間隔), Rows(Cells.SpecialCells(xlLastCell).Row)).Delete
  
  With シート取得("売上明細")
    'キーとなる削除と伝票番号が前行と違う場合に1、同一なら0
    iMax = .Cells(Rows.Count, 開始セル取得("売上明細").Column).End(xlUp).Row
    jMax = 開始セル取得("売上明細").End(xlToRight).Column
    For i = 開始セル取得("売上明細").Row + 1 To _
        .Cells(Rows.Count, 開始セル取得("売上明細").Column).End(xlUp).Row
      If .Cells(i, 1) = .Cells(i - 1, 1) And .Cells(i, 2) = .Cells(i - 1, 2) Then
        .Cells(i, jMax + 2) = 0
      Else
        .Cells(i, jMax + 2) = 1
      End If
    Next
    
    Application.StatusBar = "フィルターを設定し重複無しに"
    DoEvents
    'フィルター設定する。
    .Range(.Cells(開始セル取得("売上明細").Row + 1, jMax + 2), .Cells(iMax, jMax + 2)).AutoFilter Field:=1, Criteria1:="1"
    'フィルター後のセル範囲を、値のみコピー
    .Range(.Cells(開始セル取得("売上明細").Row + 1, 1), .Cells(iMax, 6)).Copy
    Cells(開始セル取得("売上一覧").Row + cns間隔, 2) _
      .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    .Range(.Cells(開始セル取得("売上明細").Row + 1, 19), .Cells(iMax, 21)).Copy
    Cells(開始セル取得("売上一覧").Row + cns間隔, 8) _
      .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
  End With
  
  Call マクロ終了処理
  Debug.Print Now
End Sub


上記プログラムは前記プログラム同様に未完成です。


とりあえず、評価する為のテストプログラムです。


配列を使用せずに、エクセルの機能をなるべく使ってプログラミングしています。


検索条件での絞り込みは実装していません。



このプログラムが、やっている処理の流れは、


「売上明細」に、作業列として、削除・伝票番号を前行と比較し、同じ0、違う1を設定

フィルターで、1のみにする

フィルター後の「売上明細」より、値のみコピーします。


となっています。

もっともポピュラーな処理かもしれません。



.Range(.Cells(開始セル取得("売上明細").Row + 1, jMax + 2), .Cells(iMax, jMax + 2)).AutoFilter Field:=1, Criteria1:="1"
作業列にフィルターを設定しています。

前行と違う1のみ選択しています。


.Range(.Cells(開始セル取得("売上明細").Row + 1, 1), .Cells(iMax, 6)).Copy
Cells(開始セル取得("売上一覧").Row + cns間隔, 1) _
  .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
値のみコピーです。

列位置の検索は省略し、固定位置でのコピーをしています。


前記と同じ、データ3万件での、テスト結果は、2秒
でした。


これは、もう一瞬で終わりました。


ただし、日付の書式は設定されませんが、事前に設定しておけば良いでしょう。



上記2通りを紹介しました。


通常は、どちらかで良いと思われます。


後者は早いですが、データのコピー時に何か処理を加えたい事があれば、


コピー後に全行に対して処理を加える必要があり、処理時間は前者と同じ程度になるでしょう。




ただ、フィルターを使用しての処理は、いかにも融通がききません。


複数条件での重複処理には、作業列が必要になりますし、関数では、先頭しか取得できません。


例えば、伝票行数を表示する等を加えたい場合は、さらに仕掛けが必要になります。


そこで、次回は、配列処理を使い、正しく絞り込みも行い、自由度の高いプログラムにします。


しかし、今回のプログラムは、いかにもエクセルVBAらしいプログラムだと思います。


私は、エクセルでは、基本的には、このようなプログラムで良いと思っています。


エクセルVBAの利点の1つに、全てのPCに開発環境があることです。


誰もが理解出来るエクセルの基本機能を使用したプログラミングであれば、


誰もが自由に変更できます。


これは、とても重要な事です。


次回のプログラムは、おそらく簡単には理解できないプログラムになっています。


さらに、開発工数は、今回プログラムの数倍はかかっています。


それをどう評価するか・・・


まずは、次回の難読なプログラムにご期待下さい。(笑)






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

第32回.売上一覧(伝票合計の一覧)を作成(2)
第33回.売上一覧より納品書を作成
第34回.伝票番号の自動採番機能を追加
第35回.メニューを作成
第36回.最終回
その後1、CSV出力を追加
その後2、ベクター掲載

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

ファイルの一覧取得・削除(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日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)

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

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



  • >
  • >
  • >
  • 売上一覧(伝票合計の一覧)を作成(1)

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


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

    ↑ PAGE TOP