Windows 10の新ブラウザと噂される「Spartan」、新機能のリーク情報Windows 10は新ブラウザ「Spartan」を採用――PC/スマートデバイス両対応

2015年01月14日

Excel 名簿の重複データを一発削除

今回は日経PC21誌のEデスクの友人からのご質問。重複データの削除だ。何万件とある卒業生名簿を管理しており、重複データの削除で四苦八苦しているとのこと。

 重複かどうかの判定では同姓同名を考慮して、氏名と住所が同じなら重複と見なす。名簿を氏名の順で並べ替え、関数なども使ってチェックするのだが、何万件もあると目で追うのは難儀。何か手はないかというお悩みだ。


 実は一発でやる方法がある。氏名とメールアドレスが両方とも同じなら重複と見なし、重複データを一括削除した例が図1だ。住所ではなくメールアドレスを判定基準にしたのは、住所だと番地やビル名などの表記が不統一のケースもあるからだ(図1上の7行目)。

図1 同姓同名の別人を考慮して重複データを削除するときは、氏名のほかにメールアドレスなどが一致した場合に重複と見なせばよい。番地の表記が不統一だったりするので、住所ではなくメールアドレスや郵便番号、生年月日、入社年などを組み合わせるとよい。なお、誌面で重複データを見分けやすいように色を付けた。ここで紹介する機能と色は無関係

図1 同姓同名の別人を考慮して重複データを削除するときは、氏名のほかにメールアドレスなどが一致した場合に重複と見なせばよい。番地の表記が不統一だったりするので、住所ではなくメールアドレスや郵便番号、生年月日、入社年などを組み合わせるとよい。なお、誌面で重複データを見分けやすいように色を付けた。ここで紹介する機能と色は無関係

利用したのはエクセルの「重複の削除」という機能(図2~図4)。これはどの項目が一致したら重複と見なすかを指定して(複数指定可)、重複データを一括削除できる優れものだ。図1の例なら氏名とメールアドレスを判定項目に指定すればOK。ご質問のケースなら氏名と住所を指定すればよい。


[左上]図2 図1上で表内のセルをどれか選択して「データ」タブの「重複の削除」をクリックする(1)~(3)
[右中央]図3 続く画面で「氏名」と「Mail」だけにチェックを入れて「OK」を押す(1)(2)
[左下]図4 メッセージが現れたら「OK」を押す。これで図1下のように重複行が削除される

[左上]図2 図1上で表内のセルをどれか選択して「データ」タブの「重複の削除」をクリックする(1)~(3)
[右中央]図3 続く画面で「氏名」と「Mail」だけにチェックを入れて「OK」を押す(1)(2)
[左下]図4 メッセージが現れたら「OK」を押す。これで図1下のように重複行が削除される



■数式で工夫して並べ替え


 この方法は手間がかからない半面、削除対象を事前に確認できないのが難点。そこで、重複データを表の末尾に集め、確認してから手作業で削除する方法も紹介しよう。図5、図6がそれだ。ここでは氏名と郵便番号が同じものを重複と見なしたが、氏名とメールアドレスなどの組み合わせでも要領は同じだ。


 まず、文字列を連結する「&」演算子を使って、氏名と郵便番号を連結した文字列を数式で作る。これが同じなら重複と見なすわけだ。そうしたら、その文字列がその行までに何個あるかをCOUNTIF(カウントイフ)関数式で計算する(G列の出現数)。


 「$」を最初の行番号だけに付けた複合参照を使うのがポイントだ。結果、この数字は初出の行なら1で、2回目以降すなわち重複行なら2以上になる。


 この数字を基準に表を並べ替えると、重複データが末尾に集まる。内容を確認して一括削除すればよい。


[上]図5 氏名と郵便番号を連結した文字列をF列で作る。それがその行までに何個あるかを計算したのがG列。初出は1で、2つめ以降は2以上になる。「F$2:F2」はコピーすると「F$2:F3」などと後半の行番号だけが変化する
[下]図6 G列のセルをどれか選択して「データ」タブの「昇順」ボタンをクリック(1)~(3)。これでG列の出現数が小さい順に並べ替えられる(画面は並べ替え後)。2以上のもの、すなわち重複データが末尾に集まるので、確認後まとめて削除すればよい(4)~(6)

[上]図5 氏名と郵便番号を連結した文字列をF列で作る。それがその行までに何個あるかを計算したのがG列。初出は1で、2つめ以降は2以上になる。「F$2:F2」はコピーすると「F$2:F3」などと後半の行番号だけが変化する
[下]図6 G列のセルをどれか選択して「データ」タブの「昇順」ボタンをクリック(1)~(3)。これでG列の出現数が小さい順に並べ替えられる(画面は並べ替え後)。2以上のもの、すなわち重複データが末尾に集まるので、確認後まとめて削除すればよい(4)~(6)


もっと慎重にやりたければ、氏名が重複している行をひとまとめにして、1つひとつ確認しながら手作業で削除する手もある(図7~図9)。
[上]図7 F列で、氏名が同じデータが表全体で何個あるかを計算する(1)。「$B$2:$B$15」はコピーしても不変で氏名欄全体を指す。そうしたら氏名の昇順で表を並べ替える。氏名のセルをどれか選択して「データ」タブの「昇順」を押せばよい(2)~(4)
[中央]図8 さらにF列を基準に並べ替える。出現数のセルをどれか選択して「データ」タブの「昇順」をクリックする
[下]図9 氏名が重複している行が末尾に集まる。1件ずつ確認しながら重複データを削除したいときはこの方法が便利だ。いきなり出現数で並べ替えると、氏名が飛び飛びになるので注意しよう。氏名、出現数の順で並べ替えると、出現数が同じ行は氏名の順で並ぶので、同じ氏名が連続する

[上]図7 F列で、氏名が同じデータが表全体で何個あるかを計算する(1)。「$B$2:$B$15」はコピーしても不変で氏名欄全体を指す。そうしたら氏名の昇順で表を並べ替える。氏名のセルをどれか選択して「データ」タブの「昇順」を押せばよい(2)~(4)
[中央]図8 さらにF列を基準に並べ替える。出現数のセルをどれか選択して「データ」タブの「昇順」をクリックする
[下]図9 氏名が重複している行が末尾に集まる。1件ずつ確認しながら重複データを削除したいときはこの方法が便利だ。いきなり出現数で並べ替えると、氏名が飛び飛びになるので注意しよう。氏名、出現数の順で並べ替えると、出現数が同じ行は氏名の順で並ぶので、同じ氏名が連続する


 この場合は、同じ氏名が表全体で(「その行までに」ではない)何個あるかをCOUNTIF関数式で計算する(F列の出現数)。この数字を基準に表を並べ替えると、氏名が重複している行が末尾に集まるので、1件ずつ確認して不要なものを削除すればよい。何万件も先頭からすべて目で追うよりは楽なはずだ。


 なお、並べ替えは最初に氏名、次に出現数の順で行う。いきなり出現数で並べ替えると氏名が飛び飛びになるので注意しよう。



windows8office2010 at 13:14│Comments(0)TrackBack(0)エクセル・ワード 使い方 

トラックバックURL

コメントする

名前
 
  絵文字
 
 
Windows 10の新ブラウザと噂される「Spartan」、新機能のリーク情報Windows 10は新ブラウザ「Spartan」を採用――PC/スマートデバイス両対応