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

前回は入力されたデータが正しいか、正しくないかをピボットテーブルを使ってチェックする方法を紹介した。今回は「重複したデータのチェック」をピボットテーブルで行う方法を紹介したい。
重複行の削除
以前の投稿で重複行の削除を行ったユニークデータ リスト（テーブル）の作成方法を紹介した。
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セミナー受講後は、これさえあれば何もいらない

入力されたデータをチェックする - ピボットテーブルの応用

Excel であったり、SharePoint リストであったり、なんらかの方法で入力されたデータのチェックをする必要が出てきた場合のピボットテーブルの使い方を紹介する。

入力値のチェック

本来、入力時に入力されたデータが正しいかどうかのチェックを行い、もし、間違っているようであれば再入力を促すのが正攻法であろう。Excel ではそのために「入力規則」という機能が用意されている。

[Office Support] セルにデータの入力規則を提供する

また、SharePoint リスト入力でも簡単な入力値のチェックの設定(入力されるデータの「型」の指定など）は可能だ。さらに条件によって入力値のチェックをするのであれば、InfoPath を使ったり、JavaScript/CSS/HTML によるリスト フォームの変更、SharePoint アプリの開発が必要になる。いわゆる「入力フォーム」を作成することになる。

しかし、このフォーム カスタマイズのためのサードパーティーのツールがいくつか提供されているという現状から、すぐに素人が標準機能で作成できるものではなく、それなりにトレーニングを受け、実務で OJT を通して経験を積まなければ、思い描く入力フォームをすぐに作成できないのが現実だ。

アンク様 SharePoint ソリューション

データ入力フェーズとして SharePoint リストや SQL Azure を利用する Office 365 Access アプリといったクラウドサービスの場合は、複数人による利用、入力を前提としている。これらを利用することで Excel 単体のみで「入力ー計算ー出力」の実務データの流れを実装するより、はるかにファイル（ブック）のロックや「他の人が使用中」といった問題、または入力用ブックを多数配布した後の集計をどうするか、といった考慮すべき点が少なくなる。

反面、データの型（文字か、数値か）やデータの範囲の入力制限、ユニークキーといった一意の値の列のみ入力などは容易に設定できるものの、ロジックや条件によって正しいか正しくないかを判定することは、その設定（アプリケーション作成）のハードルがやや高くなることは否めない。

もちろん、この入力業務を数十人以上といった大規模で行うのであれば、お金と時間をかけてでもバリデーションチェックを組み込んだ入力フォームを作るべきだが、3～5人の業務であれば 「注意して入力して！」 と担当者にお願いするのが関の山だ。それでも「誤入力」は起こる。

この誤入力を Excel 側で発見して対応するのが今回の目的である。

VBA は使わない

誤解しないでほしいのは、VBAを使えないわけではない。VBAを使えば、ほぼやりたいことはできる。しかし、VBAは最後の手段としてとっておきたい。理由は「業務上の引継ぎ」での「メンテナンスのためのスキル」からだ。機能や関数はわかっていても VBA はちょっと、、、というユーザーが多いためだが、もうひとつ、その他の要素として Office 365 SharePoint Online との親和性の問題がある。SharePoint Online 上の Excel Online で VBA を動かすことができないからだ。VBA を含んだブック (.xlsm) は一度 PC にダウンロードして、PC 側の Excel で開くことで利用が可能だが、できれば Excel Online だけで完結する方法をまずは検討してみたい。

入力された値が正しいかチェックする

ピボットテーブルというと「クロス集計表」を作るためのもの、と認識されるだろう。ピボットテーブルの主たる目的はそのためであり間違いではない。ただ、ピボットテーブルの「可能性」を認識してもらえば、さらに応用がきく使い方ができる。そのひとつが入力値のバリデーションチェックだ。
バリデーションチェックのパターンとして入力された値が正しいかどうかのチェックをしたい場合がある。 たとえば以下のようなケースだ。

・ 入力された価格が価格テーブルのものと同じかどうか

以下は実践ワークシート協会の VBA セミナーのお申込み管理の事例だが、VBA セミナー（ベーシックコース、スタンダードコース）の標準受講料は 49,800 円である。ただし、割引制度がいくつかあり、割引によって受講料が変わる。

・ 標準受講料 49,800円
・ サポーター割引　39,800円
・ 継続割引　39,800円
・ セット割引　35,000円
・ おともだち割引　35,000円

このような入力の場合は、通常、選択した割引タイプから該当する授業料をもってくるように入力フォームを作成する。Excel であれば、VLOOKUP 関数やリレーションシップを使うことになるだろう。

SharePoint リストの入力でも同様の設定が可能だ。それが 「参照」 列だが、VLOOKUP との大きな違いとして、VLOOKUP は参照した値（49,800 や 39,800）そのものを入力しているのに対し、参照列は値ではなく ID を参照している。たとえば、継続割引を 39,800 円から 35,000 円に変更しようとした場合、もし割引テーブルを参照している状態でテーブルの価格を変えると、新規入力のものだけではなく、過去のデータもすべて変わるという動きをする。この件は過去の投稿で紹介している。

Excel ユーザーのための SharePoint リスト　「参照」 列

そのため、実践ワークシート協会の申込情報入力では、少しでも入力業務を楽にするために、割引タイプをドロップダウン リストから選択し、対応する受講料もドロップダウン リストから選択する形にした。ドロップダウン リストからの選択は「値」の代入となるからだ。

割引タイプに対応する受講料は入力画面の受講料の例に追記しているが、それでも間違って入力（選択）することがないとはいえない。
協会では、そのチェックを SharePoint 側の開発で行わず、Excel それも Excel Services (Excel Web Access) を使って、SharePoint 上で確認している。そこで使っている機能が「ピボットテーブル」である。

ピボットテーブルとリレーションシップを使った値の比較

実は仕組みはいたって簡単だ。入力されたデータと、本来マスターから取得したかったデータを比較し、同じであれば “OK”、違う値であれば “NG” と表示する数式をいれた集計列を追加して、その集計列の OK と NG をピボットテーブルで表示するだけだ。

第1のポイントは「本来取得したかったデータ」をリレーションシップを使って関連付けし、参照していることだろう。協会の仕組みでは、データ接続タイプは Excel Online 上でのデータ接続更新を可能にするため [データ] タブの [その他のデータ ソース] の [OData データ フィード] を使い、リレーションシップと集計列の追加は Power Pivot を使い、最終的にピボット テーブルを作成した。

データ ダイアグラムによるリレーション

集計列 [金額チェック] を追加し、数式を挿入

ピボットテーブルで [金額チェック] の結果を集計する

第2のポイントは、このピボットテーブルを Excel  Services (Excel Web Access) を使い、入力業務ページの SharePoint 上で即時に更新可能に設定していることだ。こうすることで、わざわざローカル PC で Excel ブックを開くことなく、SharePoint 上でピボットテーブルの更新が可能だ。

問題がなければ、つねに「OK」のみの件数が表示され、問題がある場合のみ「NG」が表示され、NG件数がわかる。通常は、入力した直後にこのデータ更新によるチェックをかけるが、もし、複数件の NG が発生した場合は、ピボットテーブルをローカルPCで開き、NG件数をダブルクリックすることで該当データの詳細が表示される。残念ながら Excel Web Access 内でピボットテーブルからのドリルダウンはできないが、ドリルダウンによる分析が主ではないため、それほど問題にはならない。

なお、Excel Web Access / Excel Services によるデータ接続の更新については以下の記事が参考になるだろう。

http://road2cloudoffice.blogspot.jp/2015/01/excel-online-excel-web-access-excel.html

SharePoint 上での　Excel Web Access データ接続更新が可能になったおかげで、多くの確認処理を SharePoint サイト上のピボットテーブルで実装することが可能になり、Excel のスキルのみで業務を遂行することが可能になったのは非常に大きな効果である。
上記がなんらかの参考になれば幸いである。

SharePoint リスト列の選択肢がExcelで計算されない

SharePoint リストの [接続とエクスポート] の [Excel にエクスポート] (SharePoint リスト接続）でリストを Excel のテーブルとして抽出することで、Excel 側で加工・計算できることを紹介してきた。

通常の使い方であれば、エクスポートした後のテーブルを参照してピボットテーブルを使い、さまざまな視点でデータを集計することで、おおよその業務目的は達成できるが、SharePoint リスト接続で問題になるのが列の種類で「選択肢（メニューから選択）」を選び、選択肢として「数字」をいれた列の扱いである。

この列への入力はドロップダウン メニューから該当する数字を選択する。アイテム入力後のリストは以下のように表示されている。

この状態（選択肢から数字データを選択した状態）は数字に見えるデータは「文字列」であり、現に SharePoint リストの [集計] でも個数の集計しかできず、「数値」としての合計はできない。

一方、Excel は通常手入力でセルにデータを入れた場合は、全角であろうが数字が入ると半角に直し「数値」としてセルにデータが入力される。SharePoint リスト接続によるエクスポートでもこのような動きを期待したいところだが、実際は、エクスポートしたテーブルからピボットテーブルを作成しようとすると、セルに数字が入っているにもかかわらず、リストの [集計] と同様個数のカウントしかできず、数値（金額）の合計ができない。

 

Excel のテーブル上では以下のようになっている。こちらでも集計行を使って計算はできない。

[文字列] であるセルの書式設定（列の書式設定）を [数値] に変えただけではこのデータは数値にならない。各セルで編集モードにして Enter を押してはじめて [数値] になる。数十、数百のリスト アイテムがあるテーブルの場合はさすがにこの対応はない。

この SharePoint リスト接続を使用したエキスポートの場合の対応方法は2つだ。

1) SharePoint 側で数値データに変換する

・ 集計値を使ってデータを変換する

SharePoint リストの集計値の列で VALUE 関数を使い文字列を数値にする。見た目も3桁カンマが挿入される。ただし、SharePoint リストの集計値そのもの集計することができないのが難点（もう一歩）である。

SharePoint リストの集計値については以下の投稿も参照されたい。

Excel ユーザーのための SharePoint リスト 「集計値」列　(2015/12/5)

2) Excel 側で数値データに変換する

・ VALUE関数を使って数値列を追加する

SharePoint リストの集計値同様に VALUE 関数を使って Excel のテーブルで文字列を数値に変換する。SharePoint リストのデータはテーブルとしてエクスポートされるので、最初に変換する列を追加しておけば、アイテム（レコード）が増加しても数値変換用の追加列はそのまま有効だ。
一度ブックを作成して、データ更新して使う場合などは有効だが、新規作成となると、この列追加・数式追加を必ずやらなくてはならない。

なお、ピボットテーブルの集計フィールドで VALUE 関数は使えないので注意が必要だ。

SharePoint リスト接続にこだわらなければ以下がある。

・ Power Query を使ってエキスポートし、データ変換を入れる

Power Query が使える環境であれば、データ取得時に文字列から数値へのデータ変換を [受講料] の列で行うことが可能だ。Power Query のクエリ編集は必ずやるので、その段階で行えばよい。

Power Query と SharePoint リストについては以下の投稿も参照されたい。

Excel ユーザーのための Power Query (2015/12/19)
Power Query を使った重複行の削除 (2015/1/18)
Power Query を使って絞り込んだデータを取得する 日付編 (2015/1/27)

運用していて感じるのは元データがおかしい（処理に不向き）であれば、なるべく元データ側で直すのが、最終的に手間がかからなくなる。
Excel でも当然処理・加工はできるのだが、元データ側で直せるのであればそちらを選択したほうが良いだろう。
上記の数字選択肢～文字列のケースは SharePoint のビュー設定で既定のビュー設定を変えることで、入力のときは [選択肢]、通常見るアイテムは [集計値による数値データ] に切り替えることが可能で、データ エキスポート用のビューの設定も可能だ。

リレーションシップとデータ モデル

以前の投稿でも Excel 2013 から新機能として追加されて「リレーションシップ」を紹介した。

テーブルのすすめ　ピボットテーブルとリレーションシップ

このリレーションシップはテーブル間のリレーションを設定した上で、ピボットテーブル作成時に「複数のテーブルを分析するかどうかを選択」の「このデータをデータ モデルに追加する」のチェックを入れることで、ピボットテーブルのフィールド リストで利用可能になる。

またリレーションシップと同様の機能として Power Pivot の「計算列」の紹介で RELATED 関数を使った列の追加と複数テーブルの分析を可能にするピボットテーブルの作成も紹介した。

Power Pivot で計算列を作る

さらに、Power Query でも「マージ」という機能でリレーションシップ同様の結果（マージされたテーブル）を得る方法も紹介している。

Excel ユーザーのための Power Query

Excel 単体のみで利用する環境であれば、実際のところリレーショナル データベースのようなテーブルの「正規化」をすることはあまりないが、マスターデータがサーバーやクラウドといった他のシステムにある、もしくは日々のトランザクション データを他のシステムからインポートする、となると、VLOOKUP 関数や MATCH/INDEX 関数を使って複数のテーブルや表を参照しなければならないケースが出てくる。このようなケースが多くなると「リレーションシップ」の機能の利用を検討したほうが良い場合がある。

Power Pivot や Power Query という Power BI のアドインによってさまざまな可能性が提示されているが、上記のように「似たような機能」が複数あり、その選択に悩んだり、特徴を理解するのに時間がかかるのも事実である。

さらにデータ モデルを使ったピボットテーブルでは「集計フィールド」、「集計アイテム」、「日付のグループ化」ができなくなる（もちろん、それを実現するための代案もある）ということも無視できない。
[追記] 日付のグループ化はデータ モデルに追加しても可能になりました。
そこでその特徴をまとめてみた。上述のような「リレーションシップ」や「マージ」を行うと、その後工程でピボットテーブルを利用することが多いため、ピボットテーブルの利用という観点からまとめてみる。ただし、あくまで実践ワークシート協会の業務の中で実際に利用した経験上からの示唆である。

データ モデル利用の有無がポイント
上述のように似たような結果が得られる様々な機能が存在している。リレーションシップという複数テーブル間の関連を設定する方法では Excel 2013 リレーションシップとデータ モデルを使ったピボットテーブル、Power Pivot、そして Power Query のマージ機能がある。
最終的に Excel のピボットテーブルを使いたい場合、この3つの機能の関係は以下のような図で表すことができる。

Power Pivot はデータ モデルを前提とする。扱うデータは必ずデータ モデルでなければいけない。
Excel 2013 リレーションシップを設定した複数テーブル分析可能なピボットテーブルもデータ モデルを作成しなければならない。
データ モデルを使って、複数テーブルを関連づけたり、列の加工を行ったりして、そのデータを Excel のピボットテーブルに渡している、と考えられる。
データ モデルはさまざまなサーバーやクラウドを前提としているため、Excel のためだけのデータではない。そのため、本来 Excel のデータ（テーブルまたは表）を前提として用意された機能・オプションが使えなくなっていると考えると妥当だろう。
データ モデルを使ったピボットテーブルでは制限がでる、ということだ。（何度も書くが、その代替策はきちんと用意されている）

日付データのグループ化も可能な「リレーションシップ」環境
さまざまなデータ ソースを扱うことを目的としたデータ モデルは今後も拡張、発展すると考えられる。しかし、製品・サービスとしては必要だが、実務ではそれほど多様なデータ ソースを扱ってはいないのも事実だ。（今後はわからないが、少なくとも現状は多くても２～３種類だろう）
これまで同様のピボットテーブルの操作感で、リレーションシップを使って複数テーブル、複数のデータ ソースの分析をしたい、となれば、上図の構成から Excel テーブルからピボットテーブルを作るしかない。そこで出てくるのは Power Query のマージ機能だ。
Power Query のマージ機能により、複数のテーブルやデータ　ソースを1つの Excel のテーブルにし、そこからピボットテーブルを作れば、これまで同様の操作性を維持することができる。
以下のような Excel のテーブルを例にとって検証してみよう。

Excel 2013 のリレーションシップを使ったピボットテーブルや、Power Pivot から作成したピボットテーブルでは、日付のグループ化、集計フィールド、集計アイテムの利用はできない。

ここでサンプルの3つのテーブルを「マージ」したテーブルを Power Query でまず作成する。
Excel のテーブルを参照するクエリを作成するとき、同じテーブルを作成する必要はないので、「接続の作成のみ」を選ぶ。もちろん、ここではデータ モデルは作成しない。

それを3つにテーブルで繰り返し、３回のマージにより1つのテーブルにする。

この最後のマージ(Merge3)によって出来上がったテーブルからピボットテーブルを作る。このピボットテーブルは Excel のテーブルを参照している通常のピボットテーブルなので、日付によるグループ化などが可能だ。

Power Pivot の意義
それでは Power Pivot の意義はないのかというと、Power Query のブック クエリのウィンドゥを見てわかるように、複雑なことをやろうとすると、クエリ、マージ、追加といった操作の繰り返しになる。Power Pivot であれば Power Pivot ウィンドゥのダイアグラム ビューを使ってビジュアルに操作が可能だ。

テーブルの数が多くなり、リレーションシップの項目も多くなれば、Power Pivot ウィンドウによる設定の容易さは計り知れない。基幹系アプリケーションが利用しているデータベースなどは多くのテーブルが存在するため、このようなビジュアルツールでなければ設定や管理が煩雑になるだろう。

データ モデルはいつ必要になるのか
よって、Excel を中心としているユーザーにとってはデータ モデルが必須となるシーンはあまりないのが現実だ。Excel のテーブルであったり、SharePoint Online の SharePoint リストからそれほど多くないリストのインポートを Excel にして利用するのであれば、データ モデルを前提とする Power Pivot や、データ モデルを作成する Power Query のモデル OLE DB 接続にする意味はあまりない。SharePoint リスト接続や Power Query の OLE DB 接続を使い、Excel 上に展開されたテーブルを扱うことでおおよそのことができるだろう。

実践ワークシート協会の業務で唯一データ モデルが必要になるのが、Excel Online / Excel Web Access といった、Excel ブックを SharePoint Online 上で活用するときである。まだ、活用しきれていないが、Power BI もデータ モデルの Excel ブックを前提としている。
いますぐは必要ないかもしれないが、データ モデルについては今後のキーとなるので引き続きウォッチが必要だろう。

[2016/5 追記]
今後、Excelブック内でのデータ モデルは必要になります。それは Power BI service でレポートやダッシュボードを他のメンバーと共有する際に、データ モデルをベースとした処理が前提となることからです。Excelの中だけで閉じるのであればデータ モデルを作成する必要はありませんが、Power BI service などのExcel以外のサービスを利用することを考えると、データ モデルが前提となるからです。これが1年以上前と 2016年の上旬との大きな違いです。