Excel であったり、SharePoint リストであったり、なんらかの方法で入力されたデータのチェックをする必要が出てきた場合のピボットテーブルの使い方を紹介する。
入力値のチェック
本来、入力時に入力されたデータが正しいかどうかのチェックを行い、もし、間違っているようであれば再入力を促すのが正攻法であろう。Excel ではそのために「入力規則」という機能が用意されている。
[Office Support] セルにデータの入力規則を提供する
また、SharePoint リスト入力でも簡単な入力値のチェックの設定(入力されるデータの「型」の指定など）は可能だ。さらに条件によって入力値のチェックをするのであれば、InfoPath を使ったり、JavaScript/CSS/HTML によるリスト フォームの変更、SharePoint アプリの開発が必要になる。いわゆる「入力フォーム」を作成することになる。
しかし、このフォーム カスタマイズのためのサードパーティーのツールがいくつか提供されているという現状から、すぐに素人が標準機能で作成できるものではなく、それなりにトレーニングを受け、実務で OJT を通して経験を積まなければ、思い描く入力フォームをすぐに作成できないのが現実だ。
データ入力フェーズとして 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 のスキルのみで業務を遂行することが可能になったのは非常に大きな効果である。
上記がなんらかの参考になれば幸いである。
0 コメント:
コメントを投稿