重複したデータをチェックする - ピボットテーブルの応用

前回は入力されたデータが正しいか、正しくないかをピボットテーブルを使ってチェックする方法を紹介した。今回は「重複したデータのチェック」をピボットテーブルで行う方法を紹介したい。
重複行の削除
以前の投稿で重複行の削除を行ったユニークデータ リスト（テーブル）の作成方法を紹介した。
Power Query を使った重複行の削除
しかし、そもそも入力した段階で「重複してほしくない」というケースは多々ある。Access や SQL Server などを利用するデータベース アプリケーションであればデータベースのテーブル設計で「ユニークなキー」や「一意のデータ」として列に制約をかけて、同じデータを入力させないようにするだろう。
しかしながら、田中メソッドの「入力-計算-出力」の入力でこのような重複データ入力の禁止を Excel で実現するにはいろいろなテクニックを駆使しなくてはならない。
たとえば、Excel の入力規則でユニークデータの制約を行う場合、テーブル、名前、入力規則を組み合わせることで以下のようなチェックが可能になる。
・ 表をテーブルにする（データ増減に対応させるため）
・ 対象となる列を「名前」登録する（入力規則で構造化参照が直接できないため、名前を使う。詳しくはこちらを参照。）
・ 入力規則の [ユーザー設定] の数式を使い、その列でのカウントが2未満の場合だけ入力可能にする
そのほか、条件付き書式を使って重複したら色を変えるなどでチェックする方法もある。

一方、入力において SharePoint リストを使っている場合は、いくつかの列の種類の追加設定の [固有の値を適用する] でユニークなデータの列として設定が可能だ。
この追加設定がされた列で重複の列データを入力しようとすると、以下のように「この値は既にリストに存在しています。」と表示されアイテム保存ができなくなる。

実務はもっと複雑だった
実践ワークシート協会の業務で、この重複チェックの必要性が発生するのは複数のスタッフによるセミナー申込登録だった。
複数の人が Office 365 の同一の申込用の共有メールボックスをみて未登録のお申込みを SharePoint リストに登録する業務で、同じお申込みの多重登録を避けたい、という要件だ。
処理をはじめたメールアイテムに Outlook 上で「フラグ」を付けるなどの運用上のルールは設定したが、それにより絶対多重登録がない、とはいえない。
加えて、一意（ユニーク）なデータにする条件が上述の「ユニークキーの設定」で対応できるほど単純ではなかった。
現在、協会の Excel VBA セミナーは「ベーシック」と「スタンダード」の2種類がある。たとえば A さんがこの2つを同時に申し込むと「セット割引」が適用されるため、2つ同時に申し込むことが多く、その時、A さんの名前やメールアドレスはお申込みテーブル上、複数存在することになる。おともだち割引などもお申込みいただいた方のメールアドレスが一意にならないケースがある。同じコースを別の日に受ける再受講といったケースもある。
一意になるのは、受講するコースの、受講する日の、受講者の名前、という組み合わせになる。同じ名前の人が複数人同じ日の同じコースを受講することはない。（同姓同名はカバーできないが）
この制約を実装する方法はいくつかあるが、協会としては 1) SharePoint リスト構造はなるべくシンプルにする 2) SharePoint 開発は行わない　と考えていたので、あくまで 1つのお申込み登録リストを使い、入力後に Excel Services / Excel Web Access でチェックする方法を選択した。
この重複チェックにも Excel のピボットテーブルを使っている。
ピボットテーブルで重複データをチェックする
これも入力チェック同様ピボットテーブルの機能を使った実にシンプルな方法である。ただし、Excel のピボットテーブルは単にクロス集計表を作るだけのものではない、という認識が必要だろう。
ピボットテーブルの「値フィルター」を使うことで、受講するコース、受講する日での重複登録された受講生の名前を確認することが可能だ。
以下が、その設定方法と考え方である。
1) ユニークなキーになるための条件である [実施日]、[コース名]、[名前] の順で行を構成し、カウントするために値に [名前]を指定したピボットテーブルを作る
2) ピボットテーブル レポートの [名前] の上で右クリックでメニューを出し、[フィルター] – [値フィルター] を選択する
3) フィルター条件として「2以上だったら」を設定する
この値フィルターは [名前] の上で設定するのがポイントだ。そうすることで日付けとコース名で絞られた後の [名前] の集計に対してフィルターをかけることができる。
コース名や日付の上で [値フィルター] を設定すると、それぞれの集計数に対してのフィルターになるので違った意味になることに注意する。
以下のアニメーション GIF は、岡田さんの登録が多重になっている状態でのピボットテーブルの設定と、多重登録のレコード（行）を削除して、ピボットテーブルを更新するまでの流れである。

あとは、このピボットテーブルを Excel Web Access を使って SharePoint の受講申込サイトに貼り付け、入力した後でデータ更新をかけることで、重複登録されているかどうかのチェックが可能だ。
繰り返すが、このような入力業務を数十人でやる場合はお金、時間をかけて入力チェックを組み込んだ入力フォーム、SharePoint アプリを開発すべきだが、10人以下、同時使用も数人という規模であれば、Excel を使うことで対応可能だ。それも VBA を使ったプログラミングではなく、ピボットテーブルと SharePoint と Excel Services の機能を使うことで目的を短期間・低コストで達成できることは中小規模の企業や組織にとっては大きなアドバンテージになると思う。
この投稿がなんらかのヒントになれば幸いである。


[PR] VBAセミナー受講後は、これさえあれば何もいらない