Excel関数で,VBAマクロを使わずに,重複行レコードを検出して削除する方法。サンプルシートつき
Excel関数で,シート上の行データの重複を検出し,削除したい。
ここで,重複しているかどうかの判定は,複数の列データを参照したい。
このような「重複検出」の処理を,VBAマクロを使わずに
シート関数だけで実現するために,役立つサンプルを掲載。
※サンプルデータを,Excelブックとしてダウンロードできます。
目次:
(1)サンプルデータの表
ここでは,以下のような「家計簿」のデータを処理しよう。
買い物で購入した品目名と,金額のリストだ。
購入品目 | 金額 |
---|---|
パンの耳 | 30 |
パンの耳 | 30 |
節約生活マニュアル | 500 |
もやし | 20 |
もやし | 20 |
もやし | 25 |
豆腐 | 100 |
納豆 | 50 |
ボールペン | 80 |
納豆 | 60 |
iPad Air 3 | 89800 |
するめ | 200 |
それぞれ,シート上のA列とB列に書かれているとする。
この中から,「品目名と金額」が重複した行を,関数で自動的に見つけたい。
品目名だけの重複ではダメだ。
同じ品目名で,金額が異なるデータがあるから。
品目だけでなく,金額もセットで考えたうえで,「2列とも重複しない行」だけを残したい。
そうすれば,買い物した商品について一意(ユニーク)なリストを作成できる。
どうすればよいだろうか?
(2)まず,重複判定に使う複数の列を,1列にまとめる
重複判定を楽に行うために,重複判定に使う列を1列にまとめよう。
ここでは,品目名と金額を結合すればよい。
D列に =A2&"_"&B2 と入力して下へドラッグ。
& は &(アンド)マークの半角文字であり,文字列として結合している。(CONCATENATE関数と同じ)
この結果,品目と金額が結合した列ができる。
購入品目 | 金額 | 購入品目と金額の 結合データ | |
---|---|---|---|
パンの耳 | 30 | パンの耳_30 | |
パンの耳 | 30 | パンの耳_30 | |
節約生活マニュアル | 500 | 節約生活マニュアル_500 | |
もやし | 20 | もやし_20 | |
もやし | 20 | もやし_20 | |
もやし | 25 | もやし_25 | |
豆腐 | 100 | 豆腐_100 | |
納豆 | 50 | 納豆_50 | |
ボールペン | 80 | ボールペン_80 | |
納豆 | 60 | 納豆_60 | |
iPad Air 3 | 89800 | iPad Air 3_89800 | |
するめ | 200 | するめ_200 |
この新しい列を見て,内容が重複しているかどうかチェックすればよい。
参考:
【エクセル】複数列の重複行削除 - Qiita
http://qiita.com/kazu56/items/75fd913...
- A列とB列で重複行を削除したい場合、A列とB列を結合し、それが複数の場合、重複とみなす。
(3)重複を調べる方法その1: 個数を表示する
重複データを見つけるために,方法は何通りかある。
まず簡単なやり方として,「同じデータが範囲内にいくつあるか?」を表示してみよう。
そうすれば,個数が「1」ではない行は重複しているとわかる。
E列に =COUNTIF(D$2:D$13,D2) と入力し,下へドラッグ。
D$2:D$13 の範囲内に D2 のデータがいくつ存在するかカウントしている。
下記のようになる。
購入品目 | 金額 | 購入品目と金額の 結合データ | 全体の中の 個数 | |
---|---|---|---|---|
パンの耳 | 30 | パンの耳_30 | 2 | |
パンの耳 | 30 | パンの耳_30 | 2 | |
節約生活マニュアル | 500 | 節約生活マニュアル_500 | 1 | |
もやし | 20 | もやし_20 | 2 | |
もやし | 20 | もやし_20 | 2 | |
もやし | 25 | もやし_25 | 1 | |
豆腐 | 100 | 豆腐_100 | 1 | |
納豆 | 50 | 納豆_50 | 1 | |
ボールペン | 80 | ボールペン_80 | 1 | |
納豆 | 60 | 納豆_60 | 1 | |
iPad Air 3 | 89800 | iPad Air 3_89800 | 1 | |
するめ | 200 | するめ_200 | 1 |
これで,各データの個数が表示された。
- 「1」となっていれば一意なので,重複は無い。
- 2以上であれば,それらのデータは重複している。
E列でソートすれば,「1」なのか「2以上」なのかすぐに分けられる。
あとは重複行をチマチマと手で消していくことになる。
(4)重複を調べる方法その2: 消すべき行だけに重複マークを表示する
上の方法だと,どの行を消して,どの行を残すべきなのかが少しわかりづらい。
なので,消すべき行だけに印をつけてみよう。
F列に
=IF(COUNTIF(D$2:D2,D2)>=2,"重複","○")
と入力して,下までドラッグ。
これは,「行を上から順番に調べて,すでに登場済みなら重複と表示する」。
下記のような表になる。
購入品目 | 金額 | 購入品目と金額の 結合データ | 全体の中の 個数 | 重複 フラグ | |
---|---|---|---|---|---|
パンの耳 | 30 | パンの耳_30 | 2 | ○ | |
パンの耳 | 30 | パンの耳_30 | 2 | 重複 | |
節約生活マニュアル | 500 | 節約生活マニュアル_500 | 1 | ○ | |
もやし | 20 | もやし_20 | 2 | ○ | |
もやし | 20 | もやし_20 | 2 | 重複 | |
もやし | 25 | もやし_25 | 1 | ○ | |
豆腐 | 100 | 豆腐_100 | 1 | ○ | |
納豆 | 50 | 納豆_50 | 1 | ○ | |
ボールペン | 80 | ボールペン_80 | 1 | ○ | |
納豆 | 60 | 納豆_60 | 1 | ○ | |
iPad Air 3 | 89800 | iPad Air 3_89800 | 1 | ○ | |
するめ | 200 | するめ_200 | 1 | ○ |
○が付いている行が,そのまま残すべきデータ。
「重複」と表示された行は,消すべき行だ。
こうすれば,F列でソートして,「重複」の行だけをまとめて削除すればよい。
一発で重複を消せるので便利だ。
参考:
Excel(エクセル)実用編:重複データのチェック&重複なしのデータ抽出
http://www.eurus.dti.ne.jp/~yoneyama/...
- 該当するデータが何個あるかを求めています。
- COUNTIF($B$3:B3,B3)>1 は1より大きい、つまり、B列の同じ行までに該当するデータが2以上のときに重複と表示する
エクセルで重複しているデータの抽出のしかたを教えてください。 - オフィス系ソフト 解決済 | 教えて!goo
http://oshiete.goo.ne.jp/qa/1939873.html
- セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。
- すると重複したデータの二番目以降に "*" のマークが付きます。
(5)重複を調べる方法その3: Excel2007以降にデフォルトで備わっている機能を使う
Excel2007以降なら,ソフトの機能だけで,重複を自動的に削除できる。
リボン内の「データ」タブで,重複データをどうするか決められる。
方法は下記を参照。
表の重複している項目を削除する - Excel (エクセル) - Microsoft atLife TIPS アーカイブ
https://www.microsoft.com/ja-jp/atlif...
- 表に入力されたデータの中で、同じ内容が重複している行を自動的に削除することができます。
- すべての項目がまったく同じ行だけを削除することも、特定の項目だけが同じである行を削除することも可能です。
- 重複を削除したい表の中の 1 つのセルを選択している状態で、[データ] タブの [重複の削除]をクリックします。
補足
このエントリは,以下の質問への回答として執筆させていただきました。
有効な回答には先着1名様に200ポイント進呈! エクセル20…
http://q.hatena.ne.jp/1456157751
- エクセルで重複するデータだけを抽出する数式、やり方を教えてください。
関連記事:
Excel表で,VLOOKUP関数に複数条件を指定したいケースの対処法 - モバイル通信とIT技術をコツコツ勉強するブログ
http://computer-technology.hateblo.jp/entry/20131024/p1
Excel関数で,空白セルを無視した「連番」を自動的に入力する方法 (シートの画面キャプチャ付き) - モバイル通信とIT技術をコツコツ勉強するブログ
http://computer-technology.hateblo.jp/entry/20140225/p1
Excel・Calcで頻出の関数の使い方パターン「1セルずつずれながら順番に参照」。INDIRECT(ADDRESS(ROW)) - モバイル通信とIT技術をコツコツ勉強するブログ
http://computer-technology.hateblo.jp/entry/20150408/p1
Excelのハマりがちな便利関数を復習。 6つの中級関数で,セル参照と文字列操作。 - モバイル通信とIT技術をコツコツ勉強するブログ
http://computer-technology.hateblo.jp/entry/20140202/p1
Excelで,表データを「要約」した円グラフを作成する方法 …SUMIF関数のワイルドカードにINDIRECT(ADDRESS(ROW()))して,情報をまとめる
http://language-and-engineering.hatenablog.jp/entry/20130916/GenerateCircleGr...