第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なら問題ないかもしれません。
ただし、前記のように、横項目の検索や、検索条件の絞り込みは未実装です。
もっとも、絞り込みに関しては、フィルターを使えば処理時間は問題ありませんが。
しかし、実際に時間のかかっているのは、データのコピーです。
１行づつコピーはやはり遅いです。
そこで、もっと早く処理する方法がありそうです。
「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秒
でした。
これは、もう一瞬で終わりました。
ただし、日付の書式は設定されませんが、事前に設定しておけば良いでしょう。
上記２通りを紹介しました。
通常は、どちらかで良いと思われます。
後者は早いですが、データのコピー時に何か処理を加えたい事があれば、
コピー後に全行に対して処理を加える必要があり、処理時間は前者と同じ程度になるでしょう。
ただ、フィルターを使用しての処理は、いかにも融通がききません。
複数条件での重複処理には、作業列が必要になりますし、関数では、先頭しか取得できません。
例えば、伝票行数を表示する等を加えたい場合は、さらに仕掛けが必要になります。
そこで、次回は、配列処理を使い、正しく絞り込みも行い、自由度の高いプログラムにします。
しかし、今回のプログラムは、いかにもエクセルVBAらしいプログラムだと思います。
私は、エクセルでは、基本的には、このようなプログラムで良いと思っています。
エクセルVBAの利点の１つに、全てのPCに開発環境があることです。
誰もが理解出来るエクセルの基本機能を使用したプログラミングであれば、
誰もが自由に変更できます。
これは、とても重要な事です。
次回のプログラムは、おそらく簡単には理解できないプログラムになっています。
さらに、開発工数は、今回プログラムの数倍はかかっています。
それをどう評価するか・・・
まずは、次回の難読なプログラムにご期待下さい。（笑）
同じテーマ「エクセル顧客管理」の記事
第28回.納品書データをデータベース化(1)
第29回.納品書データをデータベース化(2)
第30回.配列の使い方について
第31回.売上一覧（伝票合計の一覧）を作成(1)
第32回.売上一覧（伝票合計の一覧）を作成(2)
第33回.売上一覧より納品書を作成
第34回.伝票番号の自動採番機能を追加
第35回.メニューを作成
第36回.最終回
その後№1、CSV出力を追加
その後№2、ベクター掲載
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。