【Excel2003】氏名が重複の場合の集計

質問

Excelに関する質問です。

例1を例2に変えたいと思います。
言葉でうまく伝える自信がないので、図にしてみました。

ご回答よろしくお願い致します。m(_ _)m

【例１】
A　 B 　　　C
氏名 番号 　金額
山田 1 ￥120
鈴木 2 ￥150
鈴木 3 ￥200
鈴木 4 ￥100
山本 5 ￥100
鈴木 6 ￥100

　　　　　　　　　↓
【例2】
　　A　 B 　　　C
氏名 番号 　金額
山田 1 　　　 ￥120
鈴木 2,3,4,6 ￥550
山本 5 　　　 ￥100

回答No.1

tom04 · ベストアンサー

こんばんは！
【例2】の部分でB列だけに番号を表示させなければならないとなると、関数では難しいと思います。

番号を複数列に表示しても良いのであれば関数で可能です。
しかし、実際問題として重複数がいくつあるか決まっていないと思いますので・・・
VBAでの一例です。

Sheet1のデータをSheet2にまとめるようにしてみました。
Sheet1のデータはA～C列（1行目はタイトル行）にあるとしています。

画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
Dim vl As Variant
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1") '←Sheet名は実際のSheet名に！
Set ws2 = Worksheets("sheet2") 'こちらのSheet名も適宜変更
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Range(ws1.Cells(2, 1), ws1.Cells(i, 1)), ws1.Cells(i, 1)) = 1 Then
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
Dim str, buf As String
For j = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 1) = ws2.Cells(j, 1) Then
str = ws1.Cells(i, 2)
buf = buf & str & ","
vl = vl + Cells(i, 3)
End If
Next i
ws2.Cells(j, 2) = Left(buf, Len(buf) - 1)
ws2.Cells(j, 3) = vl
buf = ""
vl = 0
Next j
ws2.Columns("A:C").AutoFit
End Sub 'この行まで

こんな感じではどうでしょうか？m(__)m

お礼

すごいです・・。
ご丁寧なご回答ありがとうございましたm(_ _)m

回答No.6

某HN クロメート(Chromate)（@CoalTar）

>言葉でうまく伝える自信がないので、図にしてみました。
当方も得意ではないのですが、図を補足材料にすることで説明しやすくなります。
わからないなりに説明してみる必要があります。

添付図はピボットテーブルを使って集計したものです。
行フィールドに [氏名][番号]、データフィールドに[金額]を入れてあります

お礼

>図を補足材料にすることで説明しやすくなります。
わからないなりに説明してみる必要があります

図の載せ方等参考にさせて頂きます。

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

回答No.5

KURUMITO

回答No４です。
先の場合にはシート２の結果では山本の下にスズキのデータが表示されます。
それをお示しのような結果に、つまり鈴木の後に山本の結果を表示させる場合にはシート１のF２への入力の式は次の式にしてください。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,INT(MAX(F$1:F1))+1,IF(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),INDEX(F$1:F1,MATCH(A2,A:A,0))+0.1,"")))

その上でシート２のA２セルには次の式を入力してC２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$F:$F,0)),IF(COLUMN(A1)=2,IF(COUNTIF(Sheet1!$A:$A,$A2)=1,SUBSTITUTE(INDEX(Sheet1!$E:$E,MATCH(ROW(A1),Sheet1!$F:$F,0)),$A2&",",""),SUBSTITUTE(INDEX(Sheet1!$E:$E,MATCH(ROW(A1)+0.1,Sheet1!$F:$F,0)),$A2&",","")),IF(COLUMN(A1)=3,SUMIF(Sheet1!$A:$A,$A2,Sheet1!$C:$C)))))

回答No.4

KURUMITO

番号の表示が一番難しいところですね。作業列を作って対応してはどうでしょう。
元の表がシート１に有るとして１行目は項目名がA列からC列にあるとして、D２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",MATCH(A2,A:A,0)+COUNTIF(A$2:A2,A2)/1000)

E２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,A2&","&B2,INDEX(E$1:E1,MATCH(ROUNDDOWN(D2-0.001,3),D$1:D1,0))&","&B2))

F２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),MAX(F$1:F1)+1,""))

なお、D,E,F列が目障りでしたらこれらの行を選んで右クリックし、「非表示」を選択すればよいでしょう。

お求めの表はシート２に表示させるとしてA２セルには次の式を入力してC２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$F:$F,0)),IF(COLUMN(A1)=2,SUBSTITUTE(INDEX(Sheet1!$E:$E,MATCH(ROW(A1),Sheet1!$F:$F,0)),$A2&",",""),IF(COLUMN(A1)=3,SUMIF(Sheet1!$A:$A,$A2,Sheet1!$C:$C)))))

お礼

ご回答ありがとうございましたm(_ _)m

回答No.3

kagakusuki

今仮に、【例１】で「氏名」と入力されているセルが、Sheet1のA1セルで、Sheet3のA列～C列を作業列として使用し、Sheet2に【例２】を表示するものとします。

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

=IF(COUNTIF(Sheet1!$A$1:$A2,Sheet1!$A2)=1,ROW(Sheet1!$A2),"")

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

=IF(Sheet1!$A2="","",Sheet1!$A2&COUNTIF(Sheet1!$A$1:$A2,Sheet1!$A2))

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

=IF(Sheet1!$A2="","",IF(COUNTIF(Sheet1!$A$1:$A2,Sheet1!$A2)=1,Sheet1!$B2&"",VLOOKUP(Sheet1!$A2&COUNTIF(Sheet1!$A$1:$A2,Sheet1!$A2)-1,$B:$C,2,FALSE)&","&Sheet1!$B2))

　次に、Sheet3のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet2の
A1セルに　　氏名
B1セルに　　番号
C1セルに　　金額
と入力して下さい。
　次に、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS(A$2:A2)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS(A$2:A2))))

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

=IF($A2="","",VLOOKUP($A2&COUNTIF(Sheet1!$A:$A,$A2),Sheet3!$B:$C,2,FALSE))

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

=IF($A2="","",SUMIF(Sheet1!$A:$A,$A2,Sheet1!$C:$C))

　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これでSheet2に【例２】の表が表示されます。

お礼

ご回答ありがとうございましたm(_ _)m

回答No.2

imogasi

金額だけなら、SUMIFやSUMPRODUCT関数で簡単に出来る。
ただし氏名の重複のないリストを１つの列のセルに作るのはやさしい関数ではない。
これも質問表現には入っていない点だが、軽く見てはいけない。
フィルター「フィルタオプションの設定」の「重複するレコードは無視する」、をお奨めする。
ーー
問題は鈴木の２，３，４，６を１セルに出すことだが、関数では難しい（難しい関数の組み合わせになる）と思う。エクセル関数の型破りの質問だ。
どうしても必要なら、私ならVBAで処理する。
例データ
A列ーE列
氏名 番号 金額
山田 1 ￥120 山田 1
鈴木 2 ￥150 鈴木 2,3,4,6
鈴木 3 ￥200 山本 5
鈴木 4 ￥100
山本 5 ￥100
鈴木 6 ￥100
E２セルに　=gaitou("A",D2) ”A"は氏名の「ある列の列番号、D列は氏名のあるセル（gaitouはユーザー関数）
下方向に式を複写
結果銃器E列
ーー
その前に
標準モジュールに
Function gaitou(a, b)
d = Cells(65536, a).End(xlUp).Row
For i = 1 To d
If Range(a & i) = b Then
s = s & Cells(i, "B") & ","
End If
Next i
gaitou = Left(s, Len(s) - 1)
End Function
を作っておく。
ーー
合計数は平凡なことだから略。
上記では番号の列がB列にあることを仮定している。

お礼

ご回答ありがとうございましたm(_ _)m