1. Home » 
2. Excel講座の総目次 » 
3. Excel実用編：目次 » 
4. 重複データのチェック&重複なしのデータ抽出

重複データのチェック&重複なしのデータ抽出：Excel実用編

• このページではエクセルで重複データのチェック法、重複しないデータの取り出し方を説明しています。
• 別シートとの重複/重複なしのデータを抽出する方法は　2つのシートの重複データを別シートへ抽出します をご覧ください。
  マクロの例は「重複なしのデータを抽出するいろいろな方法」をご覧ください。

2021/09/09

はじめに　　　topへ

• このページは重複するデータをチェックして、重複しないデータを抽出するのが目的でした。
  Microsoft 365ではUNIQUE関数(ユニーク関数)が新しく使えるようになりました。
  よって、これからはUNIQUE関数が主流になると思われます。
• 従来の方法も何かの参考になるかもしれませんので、このページを残しておきます。
• このページで使用するデータです。
  

  	B	C	D
  2	商品名	産地	数量
  3	スイカ	熊本	30
  4	マンゴー	鹿児島	10
  5	マンゴー	鹿児島	20
  6	スイカ	鹿児島	10
  7	ポンカン	種子島	20
  8	スイカ	宮崎	30
  9	ポンカン	屋久島	20
  10	たんかん	屋久島	10
  11	マンゴー	宮崎	30
  12	ポンカン	鹿児島	25
  13	サワーポメロ	鹿児島	10
  14	たんかん	種子島	30
  15	サワーポメロ	鹿児島	10
  16	たんかん	種子島	30
  17	ポンカン	鹿児島	20
  18	マンゴー	宮崎	20
  19	たんかん	鹿児島	30
  20	ポンカン	屋久島	30
  21	サワーポメロ	鹿児島	20
  22	スイカ	熊本	20

UNIQUE関数を使って重複しないデータを抽出する

• Microsoft 365をご利用の方はUNIQUE関数が利用できます。
  詳細な使い方は UNIQUE関数で重複しない値を取り出す:Excel関数　をご覧ください。
• 一つのセルに数式を入力するだけです。これまで四苦八苦していた操作が不要になります。
  F3セルに　=UNIQUE(B3:B22)　と入力すると、重複しない商品名を取り出すことができます。
  
• F3セルに　=UNIQUE(B3:C22)　と入力すると、商品名と産地の組み合わせで重複しないデータを取り出すことができます。
  
• なお、SORT関数を組み合わせて、商品名の昇順で並べ替えた例です。
  数式は　=SORT(UNIQUE(B3:C22))　としています。
  • SORT関数の使い方は　SORT関数でデータを並べ替える:Excel関数　をご覧ください。
  
• 数量の計算もMicrosoft 365ではSpill(スピル) が使用できますので、数式を1つ入力するだけで済みます。
  H3セルに　=SUMIFS(D3:D22,B3:B22,F3:F14,C3:C22,G3:G14)　と入力します。
  • Spillが使えない場合は、=SUMIFS($D$3:$D$22,$B$3:$B$22,F3,$C$3:$C$22,G3) として、下方向へ数式をコピーする必要がありました。
    SUMIFS関数が使えない場合は、=SUMPRODUCT($D$3:$D$22,($B$3:$B$22=F3)*($C$3:$C$22=G3))　とします。
  
• クロス集計表も簡単に作成できます。
  J3セルには　=UNIQUE(B3:B22)
  K2セルには　=TRANSPOSE(UNIQUE(C3:C22))
  K3セルには　=SUMIFS(D3:D22,B3:B22,J3#,C3:C22,K2#)
  と入力しています。　J3# の　# はスピル範囲演算子と呼ばれるものです。
  

重複データのチェック　　　topへ

セルに関数を使って重複と表示して、フィルターで非表示にする

単一の列データの重複をチェックする(B列の商品名が重複しているか否かをチェックします)

• B列の商品名が重複しているときは2つ目の商品名からE列に「重複」と表示します。
  1つめの商品名の場合はE列は空欄になります。

1. E3セルに=IF(COUNTIF($B$3:B3,B3)>1,"重複","")　と入力して、E22セルまでフィルハンドルをドラッグして数式をコピーします。
   • $B$3:B3 と複合参照にすることで、B列の同じ行までに該当するデータが何個あるかを求めています。
     COUNTIF($B$3:B3,B3)>1　は1より大きい、つまり、B列の同じ行までに該当するデータが2以上のときに重複と表示するということになります。
   
2. データリスト内のセルを1つ選択します。
   [データ]タブの[フィルター]をクリックします。
   
3. 重複のチェックのフィルターボタンをクリックします。
   「(空白セル)」にチェックを入れます。「重複」のチェックは外します。
   [OK]ボタンをクリックします。
   
4. 重複しない商品名が表示されました。
   重複したデータは非表示になりました。
   

重複していないデータを関数を使って取り出してみます

1. E3セルに　=COUNTIF($B$3:B3,B3)　と入力して、オートフィルなどで下方向へ数式をコピーします。
   取り出したいのは、1 の行になります。
   
2. E列の値が 1 の行に 1,2,3・・・と順番を付けます。
   F3セルに　=IF(E3<>1,"",COUNTIF($E$3:E3,1))　と入力して、オートフィルなどで下方向へ数式をコピーします。
   =IF(E3<>1,""　はE3セルが1でなかったら、空白を返す。という意味です。
   
3. F列が　1,2,3・・・ のデータを取り出す数式は、G3セルに
   =IFERROR(INDEX($B$3:$B$22,MATCH(ROW(A1),$F$3:$F$22,0)),"")
   と入力して、オートフィルなどで下方向へ数式をコピーします。
   G3セル以降には下のように重複しない品名を取り出すことができます。
   

2つの列データで重複をチェックする(B列の商品名とC列の産地の組み合わせが重複しているか否かを調べます)

• E列を作業列として利用します。
  E3セルにはキーとなるB,C列の値を区切り文字「_」（アンダーバー）を使って結合します。
  =B3&"_"&C3
  • ここではアンダーバーを使用していますが、B列やC列のデータに含まれない文字であればOKです。
• F列に重複をチェックした結果を表示します。
  F3セルには=IF(COUNTIF($E$3:E3,E3)>1,"重複","")
  と入力して、E3:F3セルをE22:F22セルまでフィルハンドルをドラッグして数式をコピーします。
  
• 重複しないデータはフィルターを使って表示することができます。(重複するデータを非表示にします)

詳細設定「フィルタオプションの設定」を使って同じデータリスト内で絞り込む

• Excel2007以降では「重複の削除」コマンドが追加されました。以下のページをご覧ください。
  • エクセル2007基本講座：重複の削除
  • エクセル2010基本講座：重複データを削除する
  • Excel 2016で重複したデータ、重複なしのデータを別シートに抽出する
    Excel 2016で重複を削除するいろいろな方法
• Excel2003以前ではフィルターオプションの設定から重複したデータを非表示にしたり、重複しないデータの書き出しを行うことができます。

同一シート内でB列の商品名が重複しないデータのみを表示します。

1. 重複をチェックする列のデータのみを選択します。表全体を選択するのではありませんのでご注意ください。
   B列のデータB3:B22を選択します。
2. [データ]タブの並べ替えとフィルター グループにある[詳細設定]を実行します。
   コマンドの名称が「フィルターオプションの設定」から「詳細設定」に変わりました。
   
3. 「選択範囲内」にチェックが入っているのを確認します。
   リスト範囲が選択したデータ範囲「B2:B22」になっているのを確認します。
   「重複するレコードは無視する」にチェックを入れます。
   
4. 商品名が重複しないデータのみが表示されました
   
5. 列のデータが重複しないデータ B2:D13 をコピーして他のシートへ貼り付けることができます。
   B2:D13を選択して、[Ctrl]+[C]でコピーします。
   
   ↓
   シート2のB2セルを選択して、[Ctrl]+[V]で貼り付けると、重複していないデータを貼り付けることができます。
   
6. データをすべて表示するには、[データ]タブの[クリア]を実行します。
   

同一シート内でB列の商品名とC列の産地の組み合わせが重複しないデータのみを表示します。

1. 重複をチェックする列のデータを選択します。表全体を選択するのではありませんのでご注意ください。
   ここでは、2つの列のデータ、B:C列のデータB2:C22を選択します。
2. [データ]タブの[詳細設定]を実行します。
   (コマンドの名称が「フィルターオプションの設定」から「詳細設定」に変わりました。)
   
3. 「選択範囲内」にチェックが入っているのを確認します。
   リスト範囲が選択したデータ範囲「B2:C22」になっているのを確認します。
   「重複するレコードは無視する」にチェックを入れます。
   
4. B列の商品名とC列の産地の組み合わせが、重複しないデータのみが表示されました
   
5. B2:D19をコピーして他のシートへ貼り付けることができます。
   B2:D19を選択して、[Ctrl]+[C]でコピーします。
   
   ↓
   シート3のB2セルを選択して、[Ctrl]+[V]で貼り付けると、重複していないデータを貼り付けることができます。
   
6. データをすべて表示するには、[データ]タブの[クリア]を実行します。
   

別シートへデータを抽出する（フィルタオプションの設定を使う）

別シートへB列の商品名が重複しないデータを抽出します。

1. 抽出先のシートをアクティブにし、セルを選択します。←ここがポイントです
   ここではSheet2のB2セルを選択しています。
2. [データ]タブの[詳細設定]を実行します。
   
3. 「指定した範囲」にチェックを入れます。
   リスト範囲でデータ範囲「Sheet1!B2:B22」とします。
   抽出範囲を「Sheet2!B2」とします。
   「重複するレコードは無視する」にチェックを入れます。
   
4. 重複しない商品名がSheet2へ抽出されました。
   

別シートへB列の商品名とC列の産地の組み合わせが重複しないデータを抽出します。

1. 抽出先のシートをアクティブにし、セルを選択します。（ここがポイントです）
   ここではSheet2のB2セルを選択しています。
2. [データ]タブの並べ替えとフィルター グループにある[詳細設定]を実行します。
   
   
   • Excel2003以前では　データ→フィルタ→フィルタオプションの設定を実行します。
3. 「指定した範囲」にチェックが入っているのを確認します。
   リスト範囲が選択したデータ範囲を「Sheet1!B2:C22」とします。
   抽出範囲を「Sheet2!B2」とします。
   「重複するレコードは無視する」にチェックを入れます。
   
4. 商品名と産地の組み合わせが重複しないデータがSheet2へ抽出されました。
   

2つのシートの重複するデータを別シートへ抽出します（Excel2002のみ)

• ここで説明している方法はデータが前方一致が無いもので、更にExcel2002を使用した場合に有効な方法です。
  Excel2000,2003,2007などでは不都合なケースが出るのを忘れていました。m(__)m
  Excel2002以外のバージョンを使用し、前方一致のデータが混在する場合は、2つのシートの重複データを別シートへ抽出しますをご覧ください。

• Sheet1とSheet2のデータを比較し、重複したデータをSheet3に抽出します。
  • Sheet1
    
  • Sheer2
    
  1. 抽出先のSheet3のA1セルを選択します。（ここがポイントです）
  2. [データ]タブの並べ替えとフィルター グループにある[詳細設定]を実行します。
     
  3. 「指定した範囲」にチェックが入っているのを確認します。
     リスト範囲が選択したデータ範囲を「Sheet1!A2:A15」とします。
     検索条件範囲を「Sheet2!A2:A10」とします。
     抽出範囲を「Sheet3!A1」とします。
     
  4. Sheet3にSheet1とSheet2で重複したデータが抽出されました。
     

2つのシートの重複しないデータを別シートへ抽出します（Excel2002のみ)

• ここで説明している方法はデータが前方一致が無いもので、更にExcel2002を使用した場合に有効な方法です。
  Excel2000,2003,2007などでは不都合なケースが出るのを忘れていました。m(__)m
  Excel2002以外のバージョンを使用し、前方一致のデータが混在する場合は、2つのシートの重複しないデータを別シートへ抽出するをご覧ください。
• Sheet1とSheet2のデータを比較し、重複しないデータをSheet3に抽出します。
  Sheet1
  
  Sheet2
  
• この方法はSheet1のデータからフィルタオプションの設定で重複するデータを削除しようという方法です。

1. 抽出先のSheet3へ元データ(Sheet1のデータ)をコピーします。
   
2. Sheet3のA1セルを選択します。
3. [データ]タブの並べ替えとフィルター グループにある[詳細設定]を実行します。
   
4. リスト範囲が選択したデータ範囲を「A2:A15」とします。
   検索条件範囲を「Sheet2!A2:A10」とします。
   
5. Sheet3に重複したデータが表示されました。(重複していないデータが非表示になっています)
   
6. Sheet3で抽出されたデータを選択します。
   
7. [ホーム]タブのセルグループにある[削除]→[シートの行を削除]を実行ます。
   抽出データ（Sheet2との重複データ）を削除します。
   
8. [データ]タブの並べ替えとフィルター グループの[クリア]を実行します。
   
9. フィルタを解除すると、重複しないデータが残ります。
   

重複しないデータを集計する　　　topへ

• 上記は1つまたは複数の列で重複しないデータにする方法を書いてきました。
  重複しない商品名と産地が作成できたら、これらの数量の集計結果を求めたいと思います。

商品名と産地が重複しないデータを使って、数式を作成します。

1. SUMPRODUCT関数を使った例
   G2:H14に重複しないデータを書き出しています。
   I3セルに　=SUMPRODUCT(($B$3:$B$22=G3)*($C$3:$C$22=H3)*($D$3:$D$22))　と数式を入力して、合計を求めました。
   SUMPRODUCT関数の使い方は　SUMPRODUCT関数　をご覧ください。
   
2. SUMIFS関数を使って集計した例
   G2:H14に重複しないデータを書き出しています。
   I3セルに　=SUMIFS($D$3:$D$22,$B$3:$B$22,G3,$C$3:$C$22,H3)　と数式を入力して、合計を求めました。
   SUMIFS関数はExcel2007で追加された関数です。使い方は　SUMIFS関数　をご覧ください。
   

ピボットテーブルを使うと、数式を作成する必要がありません。

• ピボットテーブルの詳細な作成方法は　ピボットテーブルとピボットグラフの共通メニュー　からご覧ください。

1. [データ]タブの[並べ替え]を実行します。
   
2. 最優先されるキーに「商品名」　順序で「昇順」を選択します。
   「レベルの追加」をクリックして、
   次に優先されるキーに「産地」　順序で「昇順」を選択します。
   
3. データが並べ替えられました。
   [挿入]タブで[ピボットテーブル]を実行します。
   
4. 行に「商品名」と[産地」、Σ値に「合計/数量」を配置します。
   集計ができました。
   

スポンサードリンク

PageViewCounter
　
　Since2006/2/27