エクセル・マクロで、請求書番号ごとに集計する方法

質問

請求書毎に集計した表をマクロで作りたいと思っています。

例えば、

取引先　　請求書No　　商品　　　売上
山田　　　　0001　　　りんご　　100　　
山田　　　　0001　　　なし　　　200　　
山田　　　　0001　　　みかん　　300　　
鈴木　　　　0002　　　りんご　　100
鈴木　　　　0002　　　バナナ　　200
山田　　　　0003　　　みかん　　100　　

というデータがあるとします。

その場合

取引先　　請求書No　　売上
山田　　　　0001　　　600
鈴木　　　　0002　　　300
山田　　　　0003　　　100

というように、請求書毎に、売上の合計と取引先名などが分かるように集計した表を作成したいと思っています。

請求書番号は、基本的には連番ですが、すこし飛ぶ可能性がありますし、
毎月、どのくらいの番号まで付番するのかははっきりしません。

また、請求書毎に、どれだけの種類の製品を出荷するのか（請求書毎に、何行になるのか）も不確定です。

ピポットですと、取引先名が表示されません。
また、所定の表を作って自動集計するというよりは、マクロで請求書毎に集計したほうが良いかと思っています。

基本的なコードや考え方を教えて頂けないでしょうか。

宜しくお願い致します。

回答No.7

keithin

第三者さんからダメ出しが出ているのでコメントしておきます。

＞エクセルのバージョンに依存した数字は使わない方が良いのではないでしょうか？

ご相談者さんの「実際のエクセル」が６万件を超えるようなデータを処理している場合には、65536ではなくcells.rows.countを利用する必要があります。
ただし実用的には、そういった大規模データベースの処理を想定するのなら、抜本からやり直さないとお話になりません。ましてや１行ずつ総舐めしていくようなプログラムも問題外です。

現実的な想定範囲では、エクセルで「今回ご相談のようなデータ処理」ができるのはどんなに多くても数千件がいいところです。無駄にプログラムを作り込む必要は無いと思いますが、もちろん趣味の世界でrows.countにこだわるのはプログラマの自由です。また言わずもがなですが実際に仕事でそれを使う人は、ご自分が扱うデータ量をキチンと想定した上で適切なプログラムを作成しなければいけません。

お礼

私もマクロに詳しいわけではないので、考え方が大変参考になります。

回答No.6

Prome_Lin

すみません！　No.2です。

プログラム、間違っていました。
同じ人が集まっているときは合っているのですが、それ以外はダメでした。

直しましたので、よろしくお願いします。

なお、僭越（せんえつ）ながらNo.5の「keithin」さん、「lastrow = w.range("B65536").end(xlup).row」の「65536」というようなエクセルのバージョンに依存した数字は使わない方が良いのではないでしょうか？

私、今、エクセルVBAの勉強を始めたばかりで、ホント、こんなことを言うのは失礼なのですが、私が勉強しているサイトでは「Rows.Count」を使って、エクセルのバージョンに依存しないようにする旨、書かれていました。

「65536」は確か、エクセル2003ぐらいだと思いますが、「2007」以降は、もっともっと大きな数字です。

では、正しいプログラムです。

Option Explicit
Sub Totalization()
Dim e, p As Long
Dim c, i, j, l As Integer
e = Range("A1").End(xlDown).Row
Range("F2").Value = Range("A2").Value
Range("G2").Value = Range("B2").Value
Range("H2").Value = Range("D2").Value
l = 2
For i = 3 To e
c = 0
For j = 2 To l
If Cells(i, 2).Value = Cells(j, 7).Value Then
c = 1
p = Cells(j, 8).Value
p = p + Cells(i, 4).Value
Cells(j, 8).Value = p
Exit For
End If
Next j
If c = 0 Then
l = l + 1
Cells(l, 6).Value = Cells(i, 1).Value
Cells(l, 7).Value = Cells(i, 2).Value
Cells(l, 8).Value = Cells(i, 4).Value
End If
Next i
End Sub

お礼

ありがとうございます。私も初心者なので参考になります。

回答No.5

keithin

＞請求書毎に集計

その通りに作成してみます。

【準備】
元のシートの１行目にタイトル行、２行目からデータ、Ａ，Ｂ，Ｃ，Ｄ列に取引先、請求書No、品名、売上が列記されている。

sub macro1()
　dim w0 as worksheet
　dim w as worksheet
　dim LastRow as long

’抽出先シートを新調する
　set w0 = activesheet
　set w = worksheets.add(after:=w0)
　w.range("A1") = w0.range("A1")
　w.range("C1") = w0.range("D1")

’請求書Noを一覧にして抽出する
　w0.range("B:B").advancedfilter action:=xlfiltercopy, copytorange:=w.range("B1"), unique:=true

’取引先、売上合計を計算する
　lastrow = w.range("B65536").end(xlup).row
　w.range("A2:A" & lastrow).formula = "=INDEX(" & w0.name & "!A:A,MATCH(B2," & w0.name & "!B:B,0))"
　w.range("C2:C" & lastrow).formula = "=SUMIF(" & w0.name & "!B:B,B2," & w0.name & "!D:D)"

end sub

元のシートを開いてマクロを実行する。

お礼

ありがとうございます。年末年始忙しくてまだ試せていないのですが、このプログラムで何とかできると思います。
返事が遅くなりましたが、ありがとうございます。

回答No.4

Prome_Lin

回答No.3の「kagakusuki」さん、私、質問者ではありませんが、回答No.2の「Prome_Lin」です。

私には、難しいプログラムでしたので、コピーさせて頂き、勉強のため、実行してみましたところ、「Sheet2」の２行目に「請求書No：0001」の山田さんが、間違って存在しています。

取引先　請求書No　売上
山田　 　0001　　100
山田　 　0001　　600
鈴木　 　0002　　300
山田　 　0003　　100

こんな具合です。

一応、ご報告しておきます。

プログラム、勉強させて頂きます。

お礼

いろいろ、ありがとうございます。

回答No.3

kagakusuki

元データの表がどのシートのどのセル範囲に存在していて、それを集計した結果の表をどのシートのどのセル範囲に表示させれば良いのかという事が何も説明されておりませんので、取り敢えず仮の話として、元の表の中で「取引先」と入力されているセルがA2セルであり、その元の表があるシートを開いている状態でマクロを起動させると、集計済みの表がSheet2のA列～C列に出力されるようにするものとします。
　その場合のVBAのマクロの一例は以下の様なものとなります。

Sub Macro()

Const FirstRow = 2
Dim PasteSheet As Worksheet, LastRow As Long

Set PasteSheet = Sheets("Sheet2")
With PasteSheet
.Cells.Delete
ActiveSheet.Columns("A:D").Copy
.Range("A1").Insert shift:=xlShiftToRight
Application.CutCopyMode = False
.Columns("C:C").Delete shift:=xlToLeft
.Range("A" & FirstRow & ":C" & .Range("A" & Rows.Count).End(xlUp).row). _
RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
LastRow = .Range("A" & Rows.Count).End(xlUp).row
With .Range("C" & FirstRow + 1 & ":" & "C" & LastRow)
.FormulaR1C1 = "=SUMIFS('" & ActiveSheet.Name & "'!C4,'" & ActiveSheet.Name & "'!C1,RC1,'" & ActiveSheet.Name & "'!C2,RC2)"
.Value = .Value
End With
End With

End Sub

お礼

ありがとうございます。
参考にさせて頂きます。

回答No.2

Prome_Lin

前提条件としては、結果をＦ、Ｇ、Ｈ列に出力し、その１行目は項目行で、項目は入力済み、という前提でプログラムを組みました。

なお、出力先を指定して頂ければ、プログラムを直しますので、言ってください。

Option Explicit
Sub Totalization()
Dim e, p As Long
Dim c, i, j, l As Integer
e = Range("A1").End(xlDown).Row
Range("F2").Value = Range("A2").Value
Range("G2").Value = Range("B2").Value
Range("H2").Value = Range("D2").Value
l = 2
For i = 3 To e
For j = 2 To l
c = 0
If Cells(i, 2).Value = Cells(j, 7).Value Then
c = 1
p = Cells(l, 8).Value
p = p + Cells(i, 4).Value
Cells(l, 8).Value = p
Exit For
End If
Next j
If c = 0 Then
l = l + 1
Cells(l, 6).Value = Cells(i, 1).Value
Cells(l, 7).Value = Cells(i, 2).Value
Cells(l, 8).Value = Cells(i, 4).Value
End If
Next i
End Sub

お礼

ありがとうございます。

回答No.1

ushi2015

こんにちは

セルA1から表があるとして
セルE1にフィールド名として「グループ」などと入力して、
セルE2に
=A2&" "&B2
と入力して下方にフィルコピーした表を小計で集計してはどうですか？

グループの基準は「グループ」で、集計フィールドは「売上」で、
アウトライン２でまとめた表の可視セルをコピーして他のシートにコピーして
加工すればいいですし。

以上の作業をマクロに記録したコードを整理・修正すれば使いまわし出来ます。

お礼

ありがとうございます。