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

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 Docs（Microsoft）

関数を使用した方法

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

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

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

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

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

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

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

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

COUNTIF 関数（Microsoft）

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

UNIQUE 関数（Microsoft）

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

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

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

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

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

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

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

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

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

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

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

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

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

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