ピボットテーブルで重複データをチェックして一意のリストを作成

Excelのリストで重複データが混在している場合、重複データをチェックして一意のリストを作成したい場合があります。

重複データをチェックするには、関数を使用したり、Excelの標準機能を使ったりする方法がありますが、ここではピボットテーブルを使用してリストを作成する方法を紹介します。

また、ピボットテーブルを使って、不揃いのデータをクレンジングすることもできます。

元データの確認

以下のようなリストがあります。

このリストには同じ値が混在しています。重複データを削除して、一意のリストを作成していきます。

この時の手法としてピボットテーブルを使用します。

ピボットテーブルの作成

表内のセルを1つ選択します。ここでは、セル［B1］を選択しています。

［挿入］タブの［テーブル］グループにある［ピボットテーブル］をクリックします。

［テーブルまたは範囲からのピボットテーブル］ダイアログボックスが表示されます。

［テーブル/範囲］で範囲を確認します。修正が必要であれば、右端の［ダイアログボックス拡大/縮小］ボタンをクリックして範囲を指定します。

ピボットテーブルを配置する場所は、既定では［新規ワークシート］が選択されていますが、ここでは［既存のワークシート］を選択します。

［場所］の右端にある［ダイアログボックス拡大/縮小］ボタンをクリックします。

ピボットテーブルを作成する位置を指定します。

ここでは、セル［D1］を選択します。テキストボックス内にセル位置が表示されたら、［ダイアログボックス拡大/縮小］ボタンをクリックして戻ります。

［テーブルまたは範囲からのピボットテーブル］ダイアログボックスが以下のように完成したら、［OK］ボタンをクリックします。

以下のように、シートに空のピボットテーブルが表示されます。

フィールド［商品コード］を［行］へドラッグ

［ピボットテーブルのフィールド］作業ウィンドウの［商品コード］フィールドを［行エリア］へドラッグします。

ピボットテーブルは以下のようになります。

これで［商品コード］の一意のリストは完成です。

行ラベルのリストをコピーして、目的の表を作成すればいいですね。

フィールド［商品コード］を［値エリア］へドラッグして個数を求める

［商品コード］を［行エリア］へドラッグするだけでも、一意のリストは作成できますが、［値エリア］へドラッグすると項目の件数を表示できます。

［値エリア］は数値以外のフィールドを配置すると、個数がカウントされます。

これも立派なピボットテーブルです。

参考一意のリストが作成できると、Excelでリレーションシップも作成できます。

1列のピボットテーブルから、複数のシートを作成することもできます。

Accessでデータをインポートして、管理することもできるようになります。

不揃いのデータを見つけて元データを整形する方法

ピボットテーブルを作成してみると、同じに見えるデータを複数発見することがあります。

文字列の末尾に空白スペースが入っている場合などが多いです。

その場合の対処方法です。

もし、先ほどと同じようにピボットテーブルを作成したら、［BT-04］のデータが複数並んでいたとします。

参考もし、リストが並んでいない場合は、［行ラベル］の▼をクリックして［昇順］または［降順］をクリックしてみてください。

ピボットテーブルの元データへ連番を追加

ピボットテーブルの元データに［番号］列を追加して、連番を入力します。

オートフィルを使用すると早いですね。数値でかまいません。

ピボットテーブルのデータソースを変更して更新

ピボットテーブル内のセルを選択して、［ピボットテーブル分析］タブの［データ］グループにある［データソースの変更］をクリックします。

［ピボットテーブルの移動］ダイアログボックスが表示されますので、［ダイアログボックス拡大/縮小］ボタンをクリックし範囲を修正して［OK］ボタンをクリックします。

［ピボットテーブル分析］タブの［データ］グループにある［更新］ボタンをクリックして、ピボットテーブルを更新します。

［ピボットテーブルのフィールド］作業ウィンドウに［番号］フィールドが表示されていることを確認します。

データの確認と修正

［BT-04］の項目を1つ選択して、数式バーへカーソルを移動させます。

1番目の［BT-04］には問題はなさそうです。

2番目の［BT-04］を選択してみます。数式バーを見ると半角スペースが入力されています。

注意ピボットテーブルから元データの編集はできません。

どのセルの値かを調べるには、半角スペースが入力されている［BT-04］の集計値（個数）［1］でダブルクリックします。

値をダブルクリックすると、どの値が含まれるているかを確認できます。

新しいシートに以下のようにデータの詳細が表示されます。

番号7の商品コード［BT-04］に半角スペースが入力されているというのが分かります。

3番目の［BT-04］を選択してみます。

今度は、全角スペースが入力されているのが分かります。

同じように集計値でダブルクリックします。

新しいシートに以下のようにデータの詳細が表示されます。

番号13の商品コード［BT-04］に全角スペースが入力されているというのが分かります。

元データの番号7と番号13の商品コードに入力されていたスペースを削除します。

ピボットテーブルの更新をすると、以下のように商品コード［BT-04］の項目は1個になります。

参考ピボットテーブルでは、半角スペースと全角スペースが区別されます。

詳細データの表示をピボットテーブルに表示

上記のピボットテーブルでは、詳細データを別シートに表示させましたが、ピボットテーブルに表示することもできます。

行ラベルの詳細を表示したい項目でダブルクリックします。

［詳細データの表示］ダイアログボックスが表示されますので、［番号］を選択して［OK］ボタンをクリックします。

以下のように詳細データが表示されます。

展開したい箇所の［展開］インジゲーターをクリックすると、データを表示できます。

フィールド全体の展開と折りたたみ

［行ラベル］内で右クリックして、ショートカットメニューの［展開/折りたたみ］から［フィールド全体の展開］をクリックすると、リスト全体の詳細を表示できます。

元に戻すには、［フィールド全体の折りたたみ］をクリックします。

インジゲーターがないピボットテーブルに戻すには、［ピボットテーブルのフィールド］作業ウィンドウの［行］エリアから［番号］を削除します。

フィールドリスト［番号］を削除するには、［行］エリア外へドラッグするか、右クリックしてショートカットメニューから［フィールドの削除］をクリックします。

そのほかの重複データのチェック方法

重複データをチェックして削除する方法は、いろいろあります。

Excelの機能を使用する方法

古くからある機能です。［データ］タブの［詳細設定］で表示される［フィルターオプションの設定］ダイアログボックスで操作できます。

以下の記事内の重複レコードの削除で解説しています。

以下は、Excel2003での解説です。

Excel2007以降は、［重複する値］や［重複の削除］という機能もあります。

ただ、怪しい動作も報告されていますので、目視で確認できるくらいのデータであれば問題ないと思いますが、大量のデータの場合はおすすめはできません。

Power Queryにも［重複の削除］があります。

［データ］タブの［重複の削除］は大文字と小文字の区別はされませんが、Power Queryでは区別されます。

重複する値の処理 - Power Query | Microsoft Learn（Microsoft）

関数を使用した方法

COUNTIF関数を使用して出現回数を表示できます。

IF関数と組み合わせて、［重複］文字を表示したい場合は以下のようにします。

以下の記事で解説しています。

重複のチェック（COUNTIF関数を使って）

COUNTIFの絶対参照を省くと何回目の表示になっているかを確認できます。

重複を削除してリストを作成 （COUNTIF関数）

数式内で使用している絶対参照については、以下で解説しています。

COUNTIF関数については、Microsoftの記事を参考にしてください。

COUNTIF 関数 - Microsoft サポート（Microsoft）

参考Microsoft 365のExcelとExcel 2021では、UNIQUE関数を使用すると便利です。範囲または配列から一意の値を返す関数です。

UNIQUE 関数 - Microsoft サポート（Microsoft）

複数列で重複をチェックする方法

条件付き書式の重複のチェックは便利ですが、複数列で共に重複しているデータはチェックできません。

2つの列（以下の画像ではB列とC列）でともに同じ値のものをチェックするには、条件付き書式でCOUNTIFS関数を使用します。

以下の記事で解説しています。

2つの表の複数列でともに重複している値をチェックする方法も解説しています。

データをクレンジングしてピボットテーブルを作成

フィルター機能も、データのチェックをして整えるときに便利です。

ただ、フィルターでは半角スペースのチェックはできません。

ピボットテーブルを作成するには、まず元になるデータのクレンジングが必要になります。

ピボットテーブルができないというより、ピボットテーブルの元データの作成できないということが多いです。

サンプルデータなどは、初めから綺麗なデータなのでできるのですが、実際の実務では綺麗な（クレンジングされた）データに出会うことは少ないです。また、日本語独自の難しさもあります。

初めから大きなデータをピボットテーブルに変換しようと思わずに、小さなデータから始めてみてください。

ピボットテーブルの解説は、以下の記事が参考になると思います。

ピボットテーブルと一緒にグラフも作成することができます。

アンケート集計などもピボットテーブルを利用すると効率的です。