- ベストアンサー
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 ・・・という表示になるようにしたいのです。（番号も必要データなので消去せずに残したいです） 今までは、番号＆名前レベルでまず集計し、あとは検索して同じ名前があればカット＆ペーストという非常に面倒な作業をマニュアルでしてきました。 何かもっと楽にできる方法をご存知の方はぜひ教えてください。
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
姓（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を選択、データ→集計をクリック、グループの基準を「姓」、集計方法を「データの個数」集計フィールドは「姓」にチェック、でＯＫします。 以上でお望みの結果が得られると思いますがいかがでしょうか。
その他の回答 (7)
- imogasi
- ベストアンサー率27% (4737/17069)
例データ 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列の通り。
- e10go
- ベストアンサー率38% (47/122)
ピボットテーブルを使って、下の様な表にできます。 番号 高橋 山田 田中 鈴木 総計 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つのセルに番号と名前が入っているなら、番号と名前を別々のセルに別ける必要があります。
- takkuni
- ベストアンサー率24% (166/676)
#4さんの方法で名前を基準に並べ替えをすると番号が無視されますから、質問の趣旨からすると不都合だと思われます。 番号の橫に新に名前毎に山田は1、鈴木は2という風にcode番号を設けて、code順に並べ替えて名前を基準に集計すればできると思います。 一度並べ替えをしてしまうとデータは元に戻らないので、新しいシートにでもコピーして実行した方がいいと思います。
お礼
１人で考えている限りでは、code番号を設けるという発想には至らなかったので、とても新鮮な回答でした。 そういう方法があったのですね。 件数が多いので、code番号をマニュアルで設けるのは無理だなぁと思っていましたが、別の方の回答でそれも解決しました。 ご回答どうもありがとうございました。
- merlionXX
- ベストアンサー率48% (1930/4007)
一旦、名前の列を基準に、メニューの「ツール」、「データ」、「並べ替え」をして、それから名前を基準に「集計」したらいかがでしょう？
002 鈴木 005 鈴木 005 鈴木 005 鈴木 007 鈴木 008 鈴木 これは、オーフィルタで名前ごとに表示すればよいだけです。 両方同時に表示したい場合、ピポットテーブルとLookup関数でできると思います。 具体的なセルの位置（001などはA列、名前はB列）などを提示して、答えてねっとできくと、誰かが計算式と方法を書いてくれると思います。
- 参考URL：
- http://www.kotaete-net.net/
これはピポットテーブルという機能を使うと、一発でできます。 書式はお望みどおりとは限らないですが、 山田 データ個数 2 鈴木 データ個数 6 田中 データ個数 1 高橋 データ個数 3 みたいになります。 データが増減しても、ピポットテーブルを更新すれば、集計値はすぐに更新されます。
お礼
ご回答ありがとうございます。 ピボットテーブルは試したことがあるのですが、データ個数の集計だけになってしまい、番号が消えてしまったのです。 発送物のチェックリストとして使用するため、名前と同じく番号も必要なのです・・
補足
ご回答ありがとうございます。 ピボットテーブルでも試したことがあるのですが、番号＆名前ごとの集計になってしまったのです。
- grumpy_the_dwarf
- ベストアンサー率48% (1628/3337)
Excelの集計機能って使ったことないんだけど、一時的にでも番号よ り名前を左の列にしちゃえば一発じゃないかな。
お礼
列を入れ替えるというのは思いつかなかったので早速やってみたのですが、名前も番号も同じものだけの集計結果となってしまいました。 ご回答ありがとうございました。
お礼
まさに望み通りの結果になりました！ 知識のない私でも簡単にできて有難いです。 毎月面倒かつミスを発生しかねない作業をしていましたが、これからは解放されます。 ありがとうございました！！