エクセル 複数行ある同一商品を1行にまとめるには？

質問

同一内容が入力された複数行の合計を出す方法を教えてください。

エクセルの表でA列に商品名、B列に色の種類、Ｃ列～Ｅ列にサイズごとの個数がある、まったく同じ商品が、複数行にランダムに掲載されています。

Ａ列、Ｂ列で同一の情報を持つ商品について、Ｃ列～Ｅ列のサイズごとの合計を出したいのです。

例）商品名、色、サイズ個数（Ｃ列：Ｓサイズ、Ｄ列：Ｍサイズ、Ｅ列：Ｌサイズの順です。）

A1：Ｔシャツ　B1：ホワイト　C1：5　D1：4　E1：3　　
A2：Ｔシャツ　B2：ブラック　C2：3　D2：5　E2：5
A3: Ｔシャツ　B2：ホワイト　C3：3　D3：3　E3：2
A4: Ｔシャツ　B2：ブラック　C4：3　D4：2　E4：3

各商品が300行程あり、オートサムではやりきれません。
すべての各商品（Ａ列、Ｂ列が一致するもの）を1行ごとにまとめたシートを作成するにはどうしたら良いでしょうか？

回答No.6

merlionXX · ベストアンサー

merlionXXです。
お書きになった質問の通り、関数で回答すれば、「質問の例が簡単すぎたようです」と言われ、再度VBAで回答すれば、「例は大変簡略化しておりまして、実際には～」とおっしゃる。
どうしてそんなに情報を小出しにするのかなあ？
しかも新たに出された条件で「商品情報がA～Dまでが商品情報」？？
A～Dまでが商品情報だからなんなんですか？
A～Dまでがすべて一致することが必要なんですか？それとも別に何か違う条件があるのですか？

一応、A～Dまでが一致するもので集計するというコードです。
データがあるシート名はSheet1
サイズ別に個数をだすシート名はSheet2とします。
今後は、もう少し回答者のことも考えて、必要な情報は正しく、洩れなく、わかりやすく質問なさるようにお願いしますね。

Sub test02()
Dim myDic As Object, ms As Object, ns As Object
Dim c As Range, i As Integer, dta As String
Set myDic = CreateObject("Scripting.Dictionary")
Set ms = Sheets("Sheet1")
Set ns = Sheets("Sheet2")
For i = 4 To 8
For Each c In ms.Range(ms.Cells(1, "A"), ms.Cells(Rows.Count, "A").End(xlUp))
dta = c.Value & ":" & c.Offset(0, 1).Value & ":" & c.Offset(0, 2).Value & ":" & c.Offset(0, 3).Value
If Not myDic.exists(dta) Then
myDic.Add dta, c.Offset(0, i).Value
Else
myDic(dta) = myDic(dta) + c.Offset(0, i).Value
End If
Next c
ns.Range("A1").Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Keys)
ns.Cells(1, i + 1).Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Items)
myDic.RemoveAll
Next i
ns.Columns("A:A").TextToColumns DataType:=xlDelimited, Destination:=Range("A1"), Other:=True, OtherChar:=":"
Set myDic = Nothing
Set ms = Nothing
Set ns = Nothing
End Sub

お礼

どうもありがとうございました。
こちらで解決できました。これから大変役に立ちそうです。
大変感謝しております。

また何度も大変申し訳ございませんでした。
オートサムくらいしか使えないのにも関わらず
データを見れば自分で仕組みが分かるだろうなどと
思っていたものですから大変失礼いたしました。

回答No.7

sekkii

見当違いの返答でしたらごめんなさい。
たぶん　並べ替え→集計（excel2007だと小計）　で
ご希望に添えませんか？
並べ替えちゃいけなかったかな？

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/syukei.html

お礼

ありがとうございます。
集計をやってみたのですが、条件指定で躓きました。
今後この機能についても勉強させていただきます。
とても参考になりました。ありがとうございました。

回答No.5

merlionXX

No4で貼ったコードにコピーミスがあったので貼りなおします。

Sub test01()
Dim myDic As Object, ms As Object, ns As Object
Dim c As Range, i As Integer, dta As String
Set myDic = CreateObject("Scripting.Dictionary")
Set ms = Sheets("Sheet1")
Set ns = Sheets("Sheet3")
For i = 2 To 4
For Each c In ms.Range(ms.Cells(1, "A"), ms.Cells(Rows.Count, "A").End(xlUp))
dta = c.Value & ":" & c.Offset(0, 1).Value
If Not myDic.exists(dta) Then
myDic.Add dta, c.Offset(0, i).Value
Else
myDic(dta) = myDic(dta) + c.Offset(0, i).Value
End If
Next c
ns.Range("A1").Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Keys)
ns.Cells(1, i).Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Items)
myDic.RemoveAll
Next i
ns.Columns("B:B").Insert Shift:=xlToRight
ns.Columns("A:A").TextToColumns DataType:=xlDelimited, Destination:=Range("A1"), Other:=True, OtherChar:=":"
Set myDic = Nothing
Set ms = Nothing
Set ns = Nothing
End Sub

補足

どうもありがとうございます。
私があげた例では、これで完璧でした。

しかし、例は大変簡略化しておりまして、実際には列が　I　列（9列）まであります。
そのうち、A～Dまでが商品情報、E～Iまでがサイズ別個数となります。
サイズはS～XLまであります。（例ではS～Lでした。）

返答を見ればアレンジできるだろうと思っていたのですが
私には高度すぎて、どこを直したらよいのかさっぱりわかりませんでした。
恐縮ですが、このような状況ですとどこを修正すれば良いでしょうか。
よろしくお願いいたします。

回答No.4

merlionXX

No1で回答したmerlionXXです。

> Ａ列とＢ列の組み合わせを拾い出すこと自体が大変困難で

では、VBAでの方法です。
存在するすべての組み合わせおよび組み合わせごとのサイズ別の合計を求めます。
以下の手順でやってみてください。

データがあるシート名はSheet1
サイズ別に個数をだすシート名はSheet2とします。
ちがっている場合は下記コードで
Set ms = Sheets("Sheet1")
Set ns = Sheets("Sheet2")
のところの　””　内の名前を変更するか、エクセルの実際のシート名を変えるかどちらかをしてください。

1.AltキーとF11キーをいっしょに押して Visual Basic Editor を呼び出します。
2.Visual Basic Editor のメニューから挿入、標準モジュールで、出てきたコードウィンド（右側の白い広い部分）に下記のコード（SubからEnd Subまで）をコピーして貼り付けします。
3.また、Alt+F11キーでワークシートへもどります.
4.エクセルのメニューから、「ツール」、「マクロ」、「マクロ」で出てきたマクロ名（test01）を選択して実行します。

Sub test01()
Dim myDic As Object, ms As Object, ns As Object
Dim c As Range, i As Integer, dta As String
Set myDic = CreateObject("Scripting.Dictionary")
Set ms = Sheets("Sheet1")
Set ns = Sheets("Sheet2")
For i = 2 To 4
For Each c In ms.Range(ms.Cells(1, "A"), ms.Cells(Rows.Count, "A").End(xlUp))
dta = c.Value & ":" & c.Offset(0, 1).Value
If Not myDic.exists(dta) Then
myDic.Add dta, c.Offset(0, i).Value
Else
myDic(dta) = myDic(dta) + c.Offset(0, i).Value
End If
Next c
ns.Range("A1").Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Keys)
ns.Cells(1, i).Resize(myDic.Count, 1).Value = Application.Transpose(myDic.Items)
myDic.RemoveAll
Next i
ns.Columns("B:B").Insert Shift:=xlToRight
ns.Columns("A:A").TextToColumns Destination:=Range("A1"), OtherChar:=":"
Set myDic = Nothing
Set ms = Nothing
Set ns = Nothing
End Sub

回答No.3

gyouda1114

作業セルを使ってオートフィルタとSUBTOTAL関数で

C列を挿入し、C2に　=A3&"/"&B3　下方にオートフィル
オートフィルタを設定し、C列で抽出
抽出したデータの合計を出す最下行に
　=SUBTOTAL(9,D2:D5)
で可視セルの合計が計算される。

ただし、「組み合わせが100通り」だと抽出は大変ですが！
お試しを

オートフィルタ(データ抽出)
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter.htm

データベースやリストの集計はお任せ関数「SUBTOTAL」
これだけで１１種類の集計が出来ちゃうよ＾＾
http://akubizzz.hp.infoseek.co.jp/sub91.html

お礼

どうもありがとうございました。解決いたしました。
今回は組み合わせが単純ではなかったため最終的に
商品情報が4列で124の組み合わせがありましたので
この方法は最終的に使いませんでしたが今後の参考とさせていただきました。ありがとうございました。

回答No.2

tom04

こんばんは！
一例ですが・・・
A列の商品数とB列の色の数によって組み合わせの数がかなりの量になると考えられます。
しかしとりあえずオートフィルタなどで別Sheetにその組み合わせの数だけの表を作るしかないのではないでしょうか？
Sheet1に元データがあり、オートフィルタで商品と色の組み合わせを表示させ
一つ一つSheet2にコピーします。
その後、↓の画像でいえば、Sheet2のC2セルに
=SUMPRODUCT((Sheet1!$A$2:$A$10=Sheet2!$A2)*(Sheet1!$B$2:$B$10=Sheet2!$B2)*(Sheet1!C$2:C$10))
としてオートフィルでコピーしてみてください。
(※データは300行あるみたいなので数式の「10」のところをアレンジしてください。)
何とか数の合計は出来ると思います。
このSheet2の表を作るための質問のような感じもしますが・・・
この程度の回答しか出来ませんが、
的外れの回答なら読み流してください。m(__)m

お礼

どうもありがとうございました。解決いたしました。
今回は組み合わせが単純ではなかったため最終的に
商品情報が4列で124の組み合わせがありましたので
この方法は最終的に使いませんでしたが今後の参考とさせていただきました。ありがとうございました。

回答No.1

merlionXX

300行までなら
C列の合計は
=SUMPRODUCT((A1:A300="Ｔシャツ")*(B1:B300="ホワイト")*C1:C300)
D列の合計は
=SUMPRODUCT((A1:A300="Ｔシャツ")*(B1:B300="ホワイト")*D1:D300)
ででますよ。
ブラックはどこをなおせばいいかわかりますよね。

補足

ありがとうございます。
質問の例が簡単すぎたようです。

質問の補足になりますが、Ａ列とＢ列に入っている項目が
たくさんあり、自分でもＡ列の内容とＢ列の組み合わせが
何通りあるのかよくわからないくらいなのです。

300行に組み合わせが100通りあれば、
この式を100通り入力しなければならないので、これでは大変です。

また、こんなにきれいに並んでなく、色も聞き慣れない英語の名前が大変多く
商品名に対して色がそれぞれにあまりに違うため
拾い出すのも単純ではありません。
Ａ列とＢ列の組み合わせを拾い出すこと自体が大変困難で
この方法ですと見落としが生じかねません。

Ａ列のＴシャツというものを拾い出しなさいというようなものではなく
Ａ列とＢ列が同じ情報ならば、合計計算をしなさいと言ったようなものがあるとよいと思うのですが・・・。