エクセル 集計

質問

いつもお世話になります。
下記のようなデータがあり、
販売した商品の種類が一番多い担当者コードは何か？
また、何種類販売したか？
集計方法を教えていただけないでしょうか。

Ａ列：担当者コード
Ｂ列：商品コード

Ａ　　　　　Ｂ
101　　　　1004
101　　　　1210
103　　　　1515
103　　　　2798
103　　　　1210
561　　　　3158
561　　　　8741
561　　　　8741
561　　　　3158
・　　　　　　・
・　　　　　　・

上記の例では、担当者コード103の人が、3種類の商品を販売しており一番多いとなります。

担当者は500名ほど、商品コードは10,000種類ほどあり、
データは15,000行ほどになります。

よろしくお願い致します。

回答No.3

tom04 · ベストアンサー

こんばんは！

SUMPRODUCT関数を使えば可能だと思いますが、データが15000行あるということなので
PCにかなりの負担になると思います。
そこでVBAでの方法になってしまいますが、コードの一例を載せておきます。
↓の画像のようにSheet1のA・B列にあるデータをSheet2のA・B列に表示するようにしてみました。

Alt＋F11キーを押す　→　「標準モジュール」を選択し、↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
ws1.Columns(1).Insert
For i = 2 To ws1.Cells(Rows.Count, 2).End(xlUp).Row
ws1.Cells(i, 1) = ws1.Cells(i, 2) & "_" & ws1.Cells(i, 3)
If WorksheetFunction.CountIf(Range(ws1.Cells(2, 1), ws1.Cells(i, 1)), ws1.Cells(i, 1)) <> 1 Then
ws1.Cells(i, 1).ClearContents
End If
Next i
For i = 2 To ws1.Cells(Rows.Count, 2).End(xlUp).Row
If WorksheetFunction.CountIf(Range(ws1.Cells(2, 2), ws1.Cells(i, 2)), ws1.Cells(i, 2)) = 1 Then
With ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = ws1.Cells(i, 2)
.Offset(, 1) = WorksheetFunction.CountIf(ws1.Range("A:A"), ws1.Cells(i, 2) & "_*")
End With
End If
Next i
Dim j As Long
j = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Range(ws2.Cells(2, 1), ws2.Cells(j, 2)).Sort key1:=ws2.Cells(2, 2), order1:=xlDescending
ws1.Columns(1).Delete (xlToLeft)
End Sub 'この行まで

参考になれば良いのですが
他に良い方法があればごめんなさいね。m(__)m

お礼

tom04さんへ
おはようございます。

ＶＢＡをコピペして早速やってみました。
これなら次回以降もSHeet1にデータを貼り付けるだけで、集計できそうです。

重宝します。
ありがとうございました。

回答No.4

kagakusuki

関数と作業列を使用して、販売した商品の種類が多い順に、担当者コードを並べて、順位と販売した種類の数を表示させる方法です。
　尚、販売した種類の数が同じ担当者が複数いる場合には、元データの表中で、担当者コードが最初に現れている行番号が、若い順に並べた上で、同じ順位を付けています。
　因みに、販売した種類の数が同じ担当者が複数いる場合にも対応させるためには、SUMPRODUCT関数のみで無理で、作業列が必要になりますし、どのみち作業列を使わざるを得ないのでしたら、SUMPRODUCT関数を使わない方が、処理速度が速くなります。

　今仮に、元データの表が存在しているシートはSheet1であり、元データはその2行目から始まっているものとし、Sheet2のA列～D列を作業列として使用し、Sheet1のD列に順位、E列に担当者コード、F列に販売した種類の数を表示させるものとします。

　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(OR(Sheet1!$A2="",Sheet1!$B2=""),"",Sheet1!$A2&"@"&Sheet1!$B2)

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(AND($A2<>"",COUNTIF($A$1:$A2,$A2)=1),Sheet1!$A2,"")

　次に、Sheet2のC2セルに次の数式を入力して下さい。

=IF(AND($B2<>"",COUNTIF($B$1:$B2,$B2)=1),COUNTIF($B:$B,$B2),"")

　次に、Sheet2のD2セルに次の数式を入力して下さい。

=IF($C2="","",$C2+COUNTIF($C$1:$C2,$C2)/COUNTIF($C:$C,$C2))

　次に、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目以下に、元データの表の行数と同じ行数か、或いはそれ以上の行数となるまで、貼り付けて下さい。

　次に、Sheet1の
D1セルに　　順位
E1セルに　　担当者コード
F1セルに　　販売種類数
と入力して下さい。

　次に、Sheet1のE2セルに次の数式を入力して下さい。

=IF(ROW(E1)>COUNT(Sheet2!$C:$C),"",INDEX($A:$A,MATCH(LARGE(Sheet2!$D:$D,ROW(E1)),Sheet2!$D:$D,0)))

　次に、Sheet1のF2セルに次の数式を入力して下さい。

=IF($E2="","",COUNTIF(Sheet2!$B:$B,$E2))

　次に、Sheet1のD2セルに次の数式を入力して下さい。

=IF($E2="","",RANK($F2,Sheet2!$C:$C))

　次に、Sheet1のD2～F2の範囲をコピーして、同じ列の3行目以下に、担当者の人数と同じか、或いはそれ以上の数の行数となるまで、貼り付けて下さい。

　これで、販売した種類の数が多い順に担当者コードを並べて、各担当者コード毎の、販売した種類の数と、順位が、自動的に表示されます。

　尚、もし、元データの表中で、データが入力されている最初の行が1行目である場合には、Sheet2のA2～D2の範囲をコピーして、Sheet2のA1～D1の範囲に貼り付けて下さい。

お礼

kagakusukiさんへ
SUMPRODUCT関数では、15,000行以上もデータがあると、
処理が遅くて困っていました。

元データは2行目からでしたので、ご教授頂いた方法でできました。
ありがとうございました。

回答No.2

mt2008

作業列を作ってピボットテーブルで集計してみてください。
添付の図では、C2セルに↓の式を入れてC10までコピーしてあります。

=1/SUMPRODUCT((A2=$A$2:$A$10)*(B2=$B$2:$B$10)*1)

ただし、15000行もあるとレスポンスは悪くなるかもしれません。
その場合は、C2に　=A2&"_"&B2
D2に=1/COUNTIF(C:C,C2)　と入れて下にコピーしてピボットテーブルではD列の合計で集計してみてください。多少は軽くなるかと思います。

お礼

mt2008さんへ
できました！ありがとうございます。
お礼が遅くなりもうしわけございません。

Ｃ列とＤ列に関数を入れて、ピボットテーブルで集計しました。
集計時間も1分もかからず、大変助かりました。

ありがとうございました。

回答No.1

aokii

ピボットテーブルで集計してみてください。

お礼

早速のご回答ありがとうございます。
ピボットテーブルでデータの個数で集計してみました。

同じ商品コードが二つある場合、
種類は1つですが、ピボットテーブルでは2とカウントされてしまい、
上記例で言うと、担当者コード561の人が4種類となってしまい、
正確なデータが集計できませんでした。

ピボットテーブルの集計方法が間違っているのでしょうか？