Excelで名寄せしてデータ個数を集計する方法

質問

このような表があります。
001　山田
001　山田
002　鈴木
003　田中
004　高橋
004　高橋
005　鈴木
005　鈴木
005　鈴木
006　高橋
007　鈴木
008　鈴木
この名前（「山田」等）を基準にして名寄せをし、かつデータ個数を集計しなくてはなりません。

普通に集計をすると、
001　山田
001　山田
山田　データ個数 2
002　鈴木
鈴木　データ個数 1
003　田中
田中　データ個数 1
004　高橋
004　高橋
高橋　データ個数 2
005　鈴木
005　鈴木
005　鈴木
鈴木　データ個数 3
006　高橋
高橋　データ個数 1
007　鈴木
008　鈴木
鈴木　データ個数 2
・・・という具合になりますが、番号(001等)の枠を超えて名寄せしたいのです。

希望する集計結果としては、
001　山田
001　山田
山田　データ個数 2
002　鈴木
005　鈴木
005　鈴木
005　鈴木
007　鈴木
008　鈴木
鈴木　データ個数 6
003　田中
田中　データ個数 1
004　高橋
004　高橋
006　高橋
高橋　データ個数 3
・・・という表示になるようにしたいのです。（番号も必要データなので消去せずに残したいです）

今までは、番号＆名前レベルでまず集計し、あとは検索して同じ名前があればカット＆ペーストという非常に面倒な作業をマニュアルでしてきました。
何かもっと楽にできる方法をご存知の方はぜひ教えてください。

回答No.8

zenjee · ベストアンサー

姓（B列）毎にデータの個数を集計したい。ただしその順序はコード(A列）順を崩したくない。このような意味ですよね。
　それであればこのような方法はいかがでしょうか。
　１行目を見出列とし、A1を「コード」、B1を「姓」とします。そして一旦最初のデータをデータ→並べ替えで最優先キーを姓、２番目のキーをコードにして並べ替えます。すると次のようになります。（なお、最初の順番を残しておく必要があるときはD列辺りに一連番号を振っておきます。）
コード　姓
004 高橋
004 高橋
006 高橋
001 山田
001 山田
003 田中
002 鈴木
005 鈴木
005 鈴木
005 鈴木
007 鈴木
008 鈴木
　C列に作業列を設けます。C1の見出しを「コード順」とでもします。
　C2に次の式を入力して、最下行までコピーします。
=IF(B2<>B1,A2,C1)　　又は　　=IF(B2＝B1,C1,A2)

すると次のようになります
コード　姓　コード順
004 高橋 4
004 高橋 4
006 高橋 4
001 山田 1
001 山田 1
003 田中 3
002 鈴木 2
005 鈴木 2
005 鈴木 2
005 鈴木 2
007 鈴木 2
008 鈴木 2
　この表をデータ→並べ替えで最優先キーをコード順、２番目のキーをコードにして並べ替えます。すると次のようになります。
コード　姓　コード順
001 山田 1
001 山田 1
002 鈴木 2
005 鈴木 2
005 鈴木 2
005 鈴木 2
007 鈴木 2
008 鈴木 2
003 田中 3
004 高橋 4
004 高橋 4
006 高橋 4
　この段階でA1:C13を選択、データ→集計をクリック、グループの基準を「姓」、集計方法を「データの個数」集計フィールドは「姓」にチェック、でＯＫします。
　以上でお望みの結果が得られると思いますがいかがでしょうか。

お礼

まさに望み通りの結果になりました！
知識のない私でも簡単にできて有難いです。
毎月面倒かつミスを発生しかねない作業をしていましたが、これからは解放されます。
ありがとうございました！！

回答No.7

imogasi

例データ
A２:B13
質問のデータを、B列＋A列でソートは許してください。そうしないと下記は成り立ちません。そこをスタートにします。
A列　　B列　　C列　E列　F列
004 高橋 2 004 高橋
004 高橋 3 004 高橋
006 高橋 4 006 高橋
001 山田 6 3
001 山田 7 001 山田
003 田中 9 001 山田
002 鈴木 11 2
005 鈴木 12 003 田中
005 鈴木 13 1
005 鈴木 14 002 鈴木
007 鈴木 15 005 鈴木
008 鈴木 16 005 鈴木
005 鈴木
007 鈴木
008 鈴木
6
OKWAVEでは列が崩れますがお許しを。１４－１７行はE、F列です
C1に０、
C2に=IF(B2=B1,MAX($C$1:C1)+1,MAX($C$1:C1)+2)
と入れてC13まで式複写。結果は上記C列。
E列のE2に
=IF(ISERROR(INDEX(A$2:$B$13,MATCH(ROW(),$C$2:$C$13,0),1)),"",INDEX(A$2:$B$13,MATCH(ROW(),$C$2:$C$13,0),1))
と入れてE17まで式を複写。結果は上記E列の通り。
F列のF2に
=IF(ISERROR(INDEX(B$2:$B$13,MATCH(ROW(),$C$2:$C$13,0),1)),COUNTIF($B$2:$B$13,F1),INDEX(B$2:$B$13,MATCH(ROW(),$C$2:$C$13,0),1))
と入れてF17まで式を複写。
結果は上記F列の通り。

回答No.6

e10go

ピボットテーブルを使って、下の様な表にできます。

番号 高橋 山田 田中 鈴木 総計
001 _ 2 _ _ 2
002 _ _ _ 1 1
003 _ _ 1 _ 1
004 2 _ _ _ 2
005 _ _ _ 3 3
006 1 _ _ _ 1
007 _ _ _ 1 1
008 _ _ _ 1 1
総計 3 2 1 6 12
(注)"_"は、空白セル

ピボットテーブルのレイアウトで、「行」に001(番号)、「列」に山田(名前)、「データ」に001(または山田)を入れれば、上のような表になります。

なお、質問のような形式にしたいなら、マクロを使えば可能です。
マクロの例を下に記載します。
下のマクロは、A列とB列「番号、名前」のデータが1～12行目から入っているとして、集計結果をD列、E列に書き出します。

Sub NameSearch()
Dim lng1 As Long
Dim lng2 As Long
Dim lngRow As Long
Dim lngRowName As Long
lngRow = 1
For lng1 = 1 To 12
If Application.CountIf(Range(Cells(1, "D"), Cells(lngRow, "D")), Cells(lng1, "B")) = 0 Then
lngRowName = lngRow
For lng2 = lng1 To 12
If Cells(lng2, "B") = Cells(lng1, "B") Then
Cells(lngRow, "D") = Cells(lng2, "A")
Cells(lngRow, "E") = Cells(lng2, "B")
lngRow = lngRow + 1
End If
Next lng2
Cells(lngRow, "D") = Cells(lng1, "B")
Cells(lngRow, "E") = "データ個数 " & lngRow - lngRowName
lngRow = lngRow + 1
End If
Next lng1
End Sub

なお、私の回答は、左のセルに番号、右隣のセルに名前が入っているのを想定しています。
もし、1つのセルに番号と名前が入っているなら、番号と名前を別々のセルに別ける必要があります。

回答No.5

takkuni

#4さんの方法で名前を基準に並べ替えをすると番号が無視されますから、質問の趣旨からすると不都合だと思われます。
番号の橫に新に名前毎に山田は1、鈴木は2という風にcode番号を設けて、code順に並べ替えて名前を基準に集計すればできると思います。
一度並べ替えをしてしまうとデータは元に戻らないので、新しいシートにでもコピーして実行した方がいいと思います。

お礼

１人で考えている限りでは、code番号を設けるという発想には至らなかったので、とても新鮮な回答でした。
そういう方法があったのですね。
件数が多いので、code番号をマニュアルで設けるのは無理だなぁと思っていましたが、別の方の回答でそれも解決しました。
ご回答どうもありがとうございました。

回答No.4

merlionXX

一旦、名前の列を基準に、メニューの「ツール」、「データ」、「並べ替え」をして、それから名前を基準に「集計」したらいかがでしょう？

回答No.3

noname#14589

002　鈴木
005　鈴木
005　鈴木
005　鈴木
007　鈴木
008　鈴木

これは、オーフィルタで名前ごとに表示すればよいだけです。

両方同時に表示したい場合、ピポットテーブルとLookup関数でできると思います。

具体的なセルの位置（001などはA列、名前はB列）などを提示して、答えてねっとできくと、誰かが計算式と方法を書いてくれると思います。

参考URL：

http://www.kotaete-net.net/

回答No.2

noname#14589

これはピポットテーブルという機能を使うと、一発でできます。

書式はお望みどおりとは限らないですが、
山田　データ個数 2
鈴木　データ個数 6
田中　データ個数 1
高橋　データ個数 3

みたいになります。
データが増減しても、ピポットテーブルを更新すれば、集計値はすぐに更新されます。

お礼

ご回答ありがとうございます。
ピボットテーブルは試したことがあるのですが、データ個数の集計だけになってしまい、番号が消えてしまったのです。
発送物のチェックリストとして使用するため、名前と同じく番号も必要なのです・・

補足

ご回答ありがとうございます。
ピボットテーブルでも試したことがあるのですが、番号＆名前ごとの集計になってしまったのです。

回答No.1

grumpy_the_dwarf

Excelの集計機能って使ったことないんだけど、一時的にでも番号よ
り名前を左の列にしちゃえば一発じゃないかな。

お礼

列を入れ替えるというのは思いつかなかったので早速やってみたのですが、名前も番号も同じものだけの集計結果となってしまいました。
ご回答ありがとうございました。