スポンサーリンク

Excel関数で,VBAマクロを使わずに,重複行レコードを検出して削除する方法。サンプルシートつき

Excel/Wordオフィス製品のTipsまとめへ


Excel関数で,シート上の行データの重複を検出し,削除したい。

ここで,重複しているかどうかの判定は,複数の列データを参照したい。


このような「重複検出」の処理を,VBAマクロを使わずに

シート関数だけで実現するために,役立つサンプルを掲載。


サンプルデータを,Excelブックとしてダウンロードできます。

こちらから→ http://computer-technology.study-tips.info/2016_01_excel_choufuku/excel_kansuu_de_choufuku_wo_shiraberu.xls


目次:

(1)サンプルデータの表

ここでは,以下のような「家計簿」のデータを処理しよう。

買い物で購入した品目名と,金額のリストだ。

購入品目金額
パンの耳30
パンの耳30
節約生活マニュアル500
もやし20
もやし20
もやし25
豆腐100
納豆50
ボールペン80
納豆60
iPad Air 389800
するめ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 389800iPad 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パンの耳_302
パンの耳30パンの耳_302
節約生活マニュアル500節約生活マニュアル_5001
もやし20もやし_202
もやし20もやし_202
もやし25もやし_251
豆腐100豆腐_1001
納豆50納豆_501
ボールペン80ボールペン_801
納豆60納豆_601
iPad Air 389800iPad Air 3_898001
するめ200するめ_2001


これで,各データの個数が表示された。

  • 「1」となっていれば一意なので,重複は無い。
  • 2以上であれば,それらのデータは重複している。

E列でソートすれば,「1」なのか「2以上」なのかすぐに分けられる。

あとは重複行をチマチマと手で消していくことになる。

(4)重複を調べる方法その2: 消すべき行だけに重複マークを表示する

上の方法だと,どの行を消して,どの行を残すべきなのかが少しわかりづらい。

なので,消すべき行だけに印をつけてみよう。


F列に
 =IF(COUNTIF(D$2:D2,D2)>=2,"重複","○")
と入力して,下までドラッグ。

これは,「行を上から順番に調べて,すでに登場済みなら重複と表示する」。


下記のような表になる。

購入品目金額購入品目と金額の
結合データ
全体の中の
個数
重複
フラグ
パンの耳30パンの耳_302
パンの耳30パンの耳_302重複
節約生活マニュアル500節約生活マニュアル_5001
もやし20もやし_202
もやし20もやし_202重複
もやし25もやし_251
豆腐100豆腐_1001
納豆50納豆_501
ボールペン80ボールペン_801
納豆60納豆_601
iPad Air 389800iPad Air 3_898001
するめ200するめ_2001


○が付いている行が,そのまま残すべきデータ。

「重複」と表示された行は,消すべき行だ。


こうすれば,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...

Excel/Wordオフィス製品のTipsまとめへ