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 回答日時： 2005/11/15 05:47

　姓（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を選択、データ→集計をクリック、グループの基準を「姓」、集計方法を「データの個数」集計フィールドは「姓」にチェック、でＯＫします。
　以上でお望みの結果が得られると思いますがいかがでしょうか。

この回答へのお礼

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

お礼日時：2005/11/18 11:04

No.7 回答者： imogasi 回答日時： 2005/11/14 23:23

例データ

A２:B13
質問のデータを、B列＋A列でソートは許してください。そうしないと下記は成り立ちません。そこをスタートにします。
A列　　B列　　C列　E列　F列
004高橋2004高橋
004高橋3004高橋
006高橋4006高橋
001山田63
001山田7001山田
003田中9001山田
002鈴木112
005鈴木12003田中
005鈴木131
005鈴木14002鈴木
007鈴木15005鈴木
008鈴木16005鈴木
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 回答日時： 2005/11/14 22:08

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

番号 高橋 山田 田中 鈴木 総計
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 回答日時： 2005/11/14 21:41

#4さんの方法で名前を基準に並べ替えをすると番号が無視されますから、質問の趣旨からすると不都合だと思われます。

番号の橫に新に名前毎に山田は1、鈴木は2という風にcode番号を設けて、code順に並べ替えて名前を基準に集計すればできると思います。
一度並べ替えをしてしまうとデータは元に戻らないので、新しいシートにでもコピーして実行した方がいいと思います。

この回答へのお礼

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

お礼日時：2005/11/18 11:13

No.4 回答者： merlionXX 回答日時： 2005/11/14 18:12

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

No.3 回答者： noname#14589 回答日時： 2005/11/14 18:11

002　鈴木

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

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

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

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

参考URL：http://www.kotaete-net.net/

No.2 回答者： noname#14589 回答日時： 2005/11/14 18:05

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

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

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

この回答への補足

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

補足日時：2005/11/14 18:36

この回答へのお礼

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

お礼日時：2005/11/14 18:26

No.1 回答者： grumpy_the_dwarf 回答日時： 2005/11/14 17:44

Excelの集計機能って使ったことないんだけど、一時的にでも番号よ

り名前を左の列にしちゃえば一発じゃないかな。

この回答へのお礼

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

お礼日時：2005/11/14 18:23