複数列の数量を集計したい

質問

こんばんは。
EXCELの集計の式についてお聞きします。

品番　日付　数量　伝票No.　　数量　伝票No.　　数量　　伝票No.
A 7/23 20 111
A 7/24 20 222
A 7/24 5 111
A 7/25 5 111
A 7/26 10 222
A 7/27 5 222
と数量が３列あります。
このデータを
品番、伝票No.(3列分まとめて)で数量を集計したいのです。
但し2列目の数量だけは引きます(数量合計=1列目-2列目+3列目)

品番 伝票No.　　数量
A　　111 10
A 222 25

と集計したいのですが。
EXCELの式だけでこのような事はできますか？
分かる方おられましたら、
どうぞ宜しくお願いします。

回答No.6

imogasi · ベストアンサー

VBAで回答を出しましたが、馴染みのない人用に、その他の方法として
「複数列データの、重複しないデータの求め方」
（１）操作で行う方法で
（２）ピボットテーブルを使う方法（機能借用的応用）です。
テストは２０１３です。
特徴は、（複数列に）３ペアのデータがあるとする。
Sheet1にデータ　A,BとC,DとE、F列の３ペア
コード 値 コード2 値 コード3 値
123 1 1231 1 123 1
34 2 342 2 342 2
23 3 233 3 23 3
45 4 45 4 453 4
678 5 678 5 678 5
234 6 234 6 234 6
111 7 1112 7 1114 7
11 8 11 8 115 8
ーー
Sheet1で、
ALT+D+P　ピボットの（旧バージョン方式？）画面が開く
複数のワークシート範囲、を選択
次へ
A列とB列のデータ範囲指定をマウスでして指定
次へ
追加（のボタンクリック）
C列とD列のデータ範囲指定
追加
E列とF列のデータ範囲指定
追加
次へ
新規ワークシートのボタンをＯＮ
完了
ーー
結果
行ラベル
11
23
34
45
111
115
123
233
234
342
453
678
1112
1114
1231
の列の部分を、コピーして、シートの必要とするセルに貼り付けてください。
重複のないリストが作られているはずです。

お礼

何度もありがとうございます。
あまりピボットテーブルは使った事がなく
こちらの方法も検証したいと思います。
やはりSUMPRODUCT関数
だと比較する列が無いと難しいでしょうか？
Unionの機能を使うのも
初めてなので確認したいと思います。

回答No.7

HohoPapa

求めは、伝票Noごとの集計のように思われますが、
今後、品番ごと、伝票Noごとに集計する可能性を加味し
VBAとADODB（とSQL）で集計、抽出するコードを作成してみました。

よかったら、使ってみてください。
※SQLが絡むので、若干難解です。　

Sub UNION_SQL()

　'変数定義
　Dim cn As Object
　Dim rs As Object
　Dim wkSQL As String

　'出力先シートクリアー
　ThisWorkbook.Sheets("Sheet4").Cells.ClearContents

　'ADODB定義
　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"
　cn.Open ThisWorkbook.FullName

　'SQL文の組み立て
　wkSQL = "SELECT TTbl.F1,TTbl.Den,Sum(TTbl.Kazu)"
　wkSQL = wkSQL & "From　" & vbCrLf
　wkSQL = wkSQL & "(SELECT F1,F2,F3 as Kazu,F4 as Den " & vbCrLf
　wkSQL = wkSQL & "FROM [Sheet3$A2:Z65000]" & vbCrLf
　wkSQL = wkSQL & "Where F3 is not Null" & vbCrLf
　wkSQL = wkSQL & "UNION ALL" & vbCrLf
　wkSQL = wkSQL & "SELECT F1,F2,(F5 * -1) as Kazu ,F6 as Den" & vbCrLf
　wkSQL = wkSQL & "FROM [Sheet3$A2:Z65000]" & vbCrLf
　wkSQL = wkSQL & "Where F5 is not Null" & vbCrLf
　wkSQL = wkSQL & "UNION ALL" & vbCrLf
　wkSQL = wkSQL & "SELECT F1,F2,F7 as Kazu,F8 as Den" & vbCrLf
　wkSQL = wkSQL & "FROM [Sheet3$A2:Z65000]" & vbCrLf
　wkSQL = wkSQL & "Where F7 is not Null" & vbCrLf
　wkSQL = wkSQL & ") TTbl " & vbCrLf
　wkSQL = wkSQL & " Group by F1,Den" & vbCrLf

　'SQLの実行
　rs.Open wkSQL, cn

　'タイトル、結果セット出力
　With ThisWorkbook.Sheets("Sheet4")
　　.Cells.ClearContents
　　.Cells(1, 1).Value = "品番"
　　.Cells(1, 2).Value = "伝票No"
　　.Cells(1, 3).Value = "数量"
　　.Cells(2, 1).CopyFromRecordset rs
　End With

　'後処理
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Sub

お礼

大変丁寧な回答ありがとうございます。
なるほど、EXCELでSQL文ですか。
UNIONで結合してGROUP BYの発想は無かったです。
すいません、やはり式だけでは難しいですか？

回答No.5

imogasi

#2です。参考までに。
質問者の、補足で
＞出来る事なら、A13に111と入力しないで全セルにある伝票ごとの集計をしたいのです。
多分下記のようなことが必要と直面するだろう。
ーー
この「複数列にある同質データを使って、重複のないデータを、指定する１列に出す」のは、フィルタオプションー重複するデータは無視する、の操作が使えないと思うので、自分で別方法で、作らないとならない。関数ではこういうのは複雑になると予想する。
あえて関数マニアの回答に期待して、再質問するかだね。
ーー
小生が、VBA（の付加的システムScripting.Dictionary）を使ってやってみると
Sheet1　元データ
B列
コード　　＜－項目見出し
112
134
112
444
ーー
D列
コード
345
543
345
666
ーー
F列
コード
342
342
112
445
ーー
標準モジュールに
Sub Sample1()
Dim Dic, i As Long, buf As String
Worksheets("Sheet1").Activate
Set Rng = Union(Range("B2:B100"), Range("D2:D100"), Range("F2:F100"))
Set Dic = CreateObject("Scripting.Dictionary")
For Each R In Rng
If R <> "" Then
''セルA2からセルA8までを処理する
buf = R.Value ''セルの値を変数bufに格納する
If Not Dic.Exists(buf) Then ''まだ登録されていなかったら…
Dic.Add buf, buf ''セルの値を連想配列に登録する
End If
End If
Next
Keys = Dic.Keys
For i = 0 To Dic.Count - 1
Sheets("Sheet2").Cells(i + 1, "A") = Keys(i)
Next i
Set Dic = Nothing
End Sub
処理の要点は、その時点までに出て来ていたかどうか、Dic.Exists(buf)のExistsで判定できる仕組みを使うのだが。
結果　Sheet2に
112
134
444
345
543
666
342
445
ーー
上記のUnionの部分は、もう少し改良できると思うが。
参考　http://officetanaka.net/excel/vba/tips/tips80.htm

お礼

回答ありがとうございます。
早速検証したいと思います。

回答No.4

bunjii

>サンプルデータの中で7/24と7/25と7/27の5枚のデータが2列目のマイナスデータで,7/26の10枚が3列目のデータになります。
2列目の値は負数ではないですよね？
提示の情報を元に数表を作成して検証してみました。
貼付画像のC12セルに下記の数式を設定し、下へコピーしました。
=SUMPRODUCT((D$2:D$7=B12)*C$2:C$7-(F$2:F$7=B12)*E$2:E$7+(H$2:H$7=B12)*G$2:G$7)
別解としてE12セルへ次の数式を設定して下へコピーしました。
=SUM(SUMIF(D$2:D$7,B12,C$2:C$7),-SUMIF(F$2:F$7,B12,E$2:E$7),SUMIF(H$2:H$7,B12,G$2:G$7))
何れも結果は同じです。
C、E、Gの各列の計算範囲に文字列が入力されているとC12以下の計算ではエラーになりますがE12以下の計算ではエラーにならないはずです。

補足

おはようございます。
回答ありがとうございます。

すごいですね。
=SUMPRODUCT((D$2:D$7=B12)*C$2:C$7-(F$2:F$7=B12)*E$2:E$7+(H$2:H$7=B12)*G$2:G$7)
で集計はできます。

但しなのですが、データがエンドレスに続き、別シートに集計した結果を表示したい場合は
B列に伝票NOを111,222と指定しましたが、このような指定は出来ないと思いますが
どのようになりますか？
もう少し教えて頂けないでしょうか。
宜しくお願いします。

回答No.3

bunjii

>品番、伝票No.(3列分まとめて)で数量を集計したいのです。
>但し2列目の数量だけは引きます(数量合計=1列目-2列目+3列目)
提示の模擬データはセル位置が不明になっています。
テキストで提示の場合はセル間をカンマで区切り空白セルを明確にしてください。
また、列記号も提示して頂かないと具体的な数式を提示できませんので補足してください。
下記のようにするとExcelのSheetへコピペすると列位置を復元できます。
A,B,C,D,E,F,G,H
品番,日付,数量,伝票No.,数量,伝票No.,数量,伝票No.
A,7/23,20,111
A,7/24,,,20,222
A,7/24,,,,,5,111

>このデータを品番、伝票No.(3列分まとめて)で数量を集計したいのです。
SUMPRODUCT関数で集計すれば良いと思います。
具体的な数式は模擬データの再提示を受けてからにさせてください。

補足

回答ありがとうございます。
サンプルデータの中で7/24と7/25と7/27の5枚のデータが
2列目のマイナスデータで,7/26の10枚が3列目のデータになります。

伝票NO 111は20-5-5=10
伝票NO 222は20+10-5=25
となります。
今一度、よろしくお願いします。

回答No.2

imogasi

データで２列目はどれか？
読者によくわからないのでは。
テキストてデータをこの質問コーナーに上げるのは、次の理由から、苦労が多い。列データが、質問文の表示では、左寄せになってしまったのかな。
下記は推測。
品番 日付 数量 伝票No. 数量 伝票No. 数量 伝票No.
A 7月23日 20 111
A 7月24日 20 222
A 7月23日 　 　 -5 111
A 7月24日 　 　 -5 222
A 7月26日 　 　 　 　 10 222
A 7月27日 　 　 　 　 5 222
式は泥臭いが
A13に　１１１と入れるとして、B13に
=SUMIF(D2:D10,A13,C2:C10)+SUMIF(F2:F10,A13,E2:E10)+SUMIF(H2:H10,A13,G2:G10)
で　１５
結果は質問と違うが、質問の例の挙げ方が悪いと思う。
ーー
そもそもエクセルの表の設計時点からして、後々苦労することを、考えていないのでは。
質問者が、VBAができるなら、多少自由になると思うが。
自作データ例や、同一関数SUMIFを３つ連ねているなど、気に食わなかったら無視して。

補足

回答ありがとうございます。
すいません、見にくいサンプルで。
7/27のデータがマイナスデータであとは推測通りです。

出来る事なら、A13に111と入力しないで
全セルにある伝票ごとの集計をしたいのです。

もう少し教えて頂けないでしょうか。
よろしくお願いします。

回答No.1

aokii

数量合計=1列目-2列目+3列目を別の列に計算式で算出して、品番ごとの数量合計を、ピボットテーブルを使って集計してみてはいかがでしょう。

補足

回答ありがとうございます。
品番、伝票ごとの集計になりますがもう1列追加して
先に合計しておくということですが
やはり１列追加しないとやりづらいでしょうか？