Office 2019 Excel 2019

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

投稿日:2022年6月6日 更新日:

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

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

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

ピボットテーブル

元データの確認

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

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

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

重複データが混在したデータ

ピボットテーブルの作成

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

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

ピボットテーブル

複雑なデータをピボットテーブルに簡単に配置し、集計します。

参考:値をダブルクリックすると、どの値が合計に含まれるかを確認することができます。

[挿入]タブの[テーブル]グループにある[ピボットテーブル]

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

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

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

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

[テーブルまたは範囲からのピボットテーブル]ダイアログボックス

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

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

ピボットテーブルを開始する位置を指定

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

情報を入力した後の[テーブルまたは範囲からのピボットテーブル]ダイアログボックス

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

空のピボットテーブル

フィールド[商品コード]を[行]へドラッグ

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

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

[商品コード]フィールドを[行エリア]へドラッグ

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

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

ピボットテーブルの[行ラベル]のリストをコピー

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

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

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

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

[商品コード]を[値エリア]へドラッグ

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

テーブル間でリレーションシップを設定してピボットテーブルを作成

Excel2013以降では、テーブル間でリレーションシップを設定して1つのレポートを作成できるようになっています。 以下の記事では、[Pow ...

続きを見る

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

Excelのデータをテーブルにインポート

AccessでExcelのデータをインポートしてテーブルを作成する方法です。 操作は簡単なのですが、なかなか思うように行かないこともあります ...

続きを見る

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

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

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

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

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

同じ値のデータが並んでいるピボットテーブル

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

行ラベルのリストを昇順に並べ替え

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

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

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

元データに[番号]列を追加

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

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

[ピボットテーブル分析]タブの[データ]タブの[データソースの変更]

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

[ピボットテーブルの移動]ダイアログボックスで範囲を修正

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

[ピボットテーブル分析]タブの[データ]タブの[更新]

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

ピボットテーブルのフィールドリストには[番号]が表示

データの確認と修正

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

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

行ラベルの1番目の[BT-04]を選択

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

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

行ラベルの2番目の[BT-04]を選択

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

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

半角スペースが入力されている[BT-04]の集計値でダブルクリック

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

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

新しいシートに表示されて詳細データ

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

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

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

行ラベルの3番目の[BT-04]を選択

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

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

新しいシートに表示された詳細データ

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

元データで空白スペースを削除

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

更新後のピボットテーブル

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

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

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

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

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

[詳細データの表示]ダイアログボックスで[番号]を選択

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

詳細データの表示

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

インジゲーターをクリックして詳細データを表示

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

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

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

ショートカットメニューの[展開/折りたたみ]

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

ピボットテーブルのフィールド[行]エリアの[番号]

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

ショートカットメニューの[フィールドの削除]

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

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

Excelの機能を使用する方法

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

[フィルターオプションの設定]ダイアログボックス

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

複数の条件に合うものを抽出する方法(別シートへの書き出しも可能)

Excelのデータ抽出の方法はいろいろありますが、ここでは[データ]タブの[並べ替えとフィルター]グループにある[詳細設定]を使って、複数の ...

続きを見る

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

フィルタオプションの設定[重複するレコードは無視する]で重複削除

大きなリストの中から、重複データを探し出して削除する方法です。 下のような表があります。 この中から重複するデータをまとめて削除したいと思い ...

続きを見る

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

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

重複する値の削除(条件付き書式でチェックしてデータを削除)

Excel2007以降には、[重複の削除]という機能があります。 [重複の削除]を実行すると1番目に登場した値が残り、残りのデータはすべて削 ...

続きを見る

[重複の削除]は1番目の値が残るがデータの空白スペースには注意

Excel2007以降には、[データ]タブに[重複の削除]というコマンドがあります。 ポップヒントには、以下のように記されています。 重複の ...

続きを見る

Power Queryにも[重複の削除]があります。

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

重複する値の処理 - Power Query | Microsoft Docs(Microsoft)

Power Queryの[重複の削除]

関数を使用した方法

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

=COUNTIF($A$2:$A$18,$A2)

COUNTIF関数で出現回数をチェック

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

=IF(COUNTIF($A$2:$A$18,$A2)>1,"重複","")

COUNTIF関数を使用した重複データのチェック

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

重複のチェック(COUNTIF関数を使って)

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

重複を削除してリストを作成 (COUNTIF関数)

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

相対参照と絶対参照と複合参照は【 F4 】キーで切替

セルの参照方法は、相対参照、絶対参照、複合参照があります。 絶対参照と複合参照では、[$]を挿入する必要があります。 このとき、直接入力して ...

続きを見る

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

COUNTIF 関数(Microsoft)

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

=UNIQUE(A2:A18)

UNIQUE関数

UNIQUE 関数(Microsoft)

UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす

Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。 UNIQUE関数を使 ...

続きを見る

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

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

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

B列とC列ともに同じ値の行に書式設定

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

重複しない一意の値を求める方法と複数列で重複をチェックする方法

Excelで2つの列にデータが入力されていて、そのデータの差分を求めたい場合がありますね。 A列には分析用データとして別表に貼り付け済みのI ...

続きを見る

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

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

フィルター機能を使ってデータをチェック

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

フィルターと置換機能を使って不揃いのデータを整える

Excelのフィルター機能は、データのチェックをして整えるときにも便利です。 ピボットテーブルの元表には、統一されたデータがなければ、分析も ...

続きを見る

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

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

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

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

テーブルやピボットテーブル作成がうまくいかない場合の見直し点

Excelのテーブルやピボットテーブルは、データベースとしての管理や分析が簡単にできるようになる、とても便利な機能です。 でも、既存の表をテ ...

続きを見る

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

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

ピボットテーブルとピボットグラフを同時に作成

ピボットテーブルの作成に慣れたなら、グラフも同時に作成してみませんか? ここでは、Excel2016で解説します。 サンプルのデータは以下の ...

続きを見る

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

アンケートはピボットテーブルで簡単集計

アンケートをとって、顧客満足度などの調査をすることがあると思います。 アンケートをとった後は、集計しますね。その時にピボットテーブルを活用す ...

続きを見る

検索

ほかの記事も検索してみませんか?

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2022 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2022 AllRights Reserved Powered by AFFINGER4.