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

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

Read More

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

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 のスキルのみで業務を遂行することが可能になったのは非常に大きな効果である。
上記がなんらかの参考になれば幸いである。

Read More

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 のビュー設定で既定のビュー設定を変えることで、入力のときは [選択肢]、通常見るアイテムは [集計値による数値データ] に切り替えることが可能で、データ エキスポート用のビューの設定も可能だ。

Read More

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

以前の投稿でも 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年の上旬との大きな違いです。

Read More

Power Query を使って絞り込んだデータを取得する － 日付編

SharePoint リストなどからデータを取得し、Excel で加工、集計をする場合、おおよそ全件データを取得し Excel のテーブルにデータを展開してから、ピボットテーブルを使ったり、ワークシート関数を使うことが多いと思う。現在 Excel は百万行を超えるレコード（行）を扱うことができるため、実務上はほぼ問題ないが、場合によっては、絞り込んだデータのみを扱いたいときがある。

過去のデータは一切関係なく、たとえばセミナー申込みの管理などで今日（または指定日）以降に実施される予定コースの登録者アイテムのみが欲しい、といったケースだ。後工程でデータの加工、集計をするのだが、そこで絞り込みを行うのではなく、必要なデータ「だけ」がテーブルに存在していたほうが都合がよい場合だ。

SharePoint リスト接続は全件データをテーブルもしくはピボットキャッシュに取り込む。Office データ接続は上記に加えてデータ モデルに全件データを読み込んでから、Excel 側での処理が行われる。

OLE DB クエリ接続もしくはモデル OLE DB クエリ接続の Power Query の場合も、基本的には全件データを Power Query に取り込んでいる。しかし、Power Query の場合は、Power Query エディターを使って、データの絞り込みや、並べ替え、カスタム列の追加を行ってから Excel に渡すことができる。

(注) SharePoint リスト接続、Office データ接続、OLE DB クエリ接続、モデル OLE DB クエリ接続については、こちらの投稿を参考にしていただきたい。

Power Query のクエリ エディターの画面で絞り込みを行っているところ

絞り込んだデータをどうしても欲しい場合は、Office データ接続によるデータ取得ではなく、Power Query を使わざるを得ない。2015年1月の段階では、Power Query のモデル OLE DB クエリ接続は Excel Online でのデータ更新ができない制限があるが、この制限の実務上の問題がなければ、Power Query を使うことになる。

日付による絞り込み

絞り込みの条件はさまざまあるが、今回は上述のように「日付」に注目して、その絞り込み方法を紹介したい。
とはいえ、Excel ユーザーのとっては有難いことに、Excel のオートフィルターを使った絞り込みと同等の操作を行うことで実現が可能だ。

Power Query エディターの「日付/時刻フィルター」

Excel オートフィルターの「日付フィルター」

Power Query の場合、このオプションに加えて関数を使って指定することが可能なのが特徴だろう。特に「今日」より～ という指定が可能なことだ。

Excelのオートフィルターでも今日より後、といった「今日」という指定は可能だ。

しかし、この「今日」のボタンを押すと、「2015/1/25」といった今日の文字の値が入力される。Excel のオートフィルターという使い方では問題ないが、データ接続のデータ更新により SharePoint リストなどのデータ ソースからデータを抽出する場合は「今日」は「その日」でなければ困る場合が多い。
Power Query エディターの日付/時刻フィルターの「今日」や「明日」の扱いは、Excel のオートフィルターとは異なる。
「今日」を指定しても Excel のように日時を指定するのではなく、Data.IsInCurrentDay([実施日]) という関数を使って配列（複数件データ）を取得して、それをテーブルに展開するのだ。

Power Query エディターの日付/時刻フィルター オプションの「今日」

「今日」を指定したときの式

この指定であれば、データ更新をすると「その日」のデータを持ってくることができる。

関数を使った指定

上図のように、Power Query エディターで絞り込みなどをすると、その操作はすべて「式」として記録さている。

たとえば、「今日より以降（後）のデータ」を指定するときは、オプションのダイアログから「次の値より後...」で「今日」を入力することができない。

Power Query 行のフィルター選択

 ここで日を指定すると数式は以下のようになる。

今日よりも後を指定したいのであれば、赤い線の部分が「今日」になればよい。
ワークシート関数の Today() や Now() に相当するものが入ればよさそうなことは想像に難くない。
それが、DateTime.LocalNow() 関数だ。

実際に数式を直接書き換えてみた結果が以下だ。

ダイアログからは DateTime.LocalNow() 関数はバリデーションチェックによって入力ができないが、数式バー（のようなもの）では直接入力、修正が可能なのだ。

シリアル値ではない日付形式

Excel における日付や時間(時刻）の扱いは「シリアル値」を使っている。Excel ではないプラットフォームではシリアル値が使われていないため、日付・時間の扱いには注意が必要になることが多い。

Power Query では「DateTime」型が基本である。 DateTime 型は Date 型と Time 型から成り立っている。たとえば既出の DateTime.LocalNow() は DateTime 型を返す。

DateTime.LocalNow()  ->  2015/01/07 11:47:45

ここから日付だけを抜き出したい場合は、Date プロパティを参照する。

DateTime.Date(DateTime.LocalNow())  -> 2015/01/07

時間だけを抜き出したい場合は、Time プロパティを参照する。

DateTime.Time(DateTime.LocalNow())  -> 11:47:45

年や月や日を抜き出す場合、ちょっとしたテクニックが必要になる。
DateTime 型に含まれる Date 型からプロパティ参照する。そして、Year や Monty、Day プロパティを参照すると、その戻り値は「数値」になる。

Date.Year([申込日])  -> 2015
Date.Month([申込日])  -> 1
Date.Day([申込])  -> 7

ゼロパディングしたい場合、1月は 01、7日は 07 の場合はテキストに置き換える必要がある。戻り値は「数値」なので、以下のようにする。

Number.ToText(Date.Day([申込]), "00")  -> 07

もちろん、12 や24 の場合も問題ない。

ならば、DateTime から ToText を使えばよさそうだと思うだろう。
しかし、en-us, ja-jp などのカルチャが関係しそうな記述がヘルプにあるのだが、 フォーマットオプションについては期待する動きを見つけられていないので注意されたい。（現状、私は使用していない）

DateTime.ToText([日付], "yyyy")  -> 2015 (文字列）
DateTime.ToText([日付], "yy")  -> 15 (文字列）

DateTime.ToText([日付], "d")  -> 2015/01/07 (文字列）


最後にシリアル値であれば 1 をプラスすることで1日後となるが、DateTime の場合は、AddDays メソッドを使う。

Date.AddDays([申込日], 2)  ->  2015/1/9 (申込日が 2015/1/7の場合）
Date.AddDays([申込日], -30)  ->  2014/12/8 (申込日が 2015/1/7の場合）

Power Query で利用できる関数は以下のページにある。解説が親切ではないので、いろいろと試して確認するのがいいだろう。

Power Query fomula categories
https://weu-odcsup.office.com/en-SG/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

Read More

Power Query を使った重複行の削除

重複行の削除もしくはユニークなデータのリスト作成も実務で Excel を使うユーザーにとっては往々にして直面する課題である。

いくつかある重複行の削除

現在、重複行の削除で Excel 2007 以降に追加された データ タブ － データ ツールにある「重複行の削除」がもっとも紹介されている機能だろう。

表の重複している項目を削除する（Microsoft atLife）
http://www.microsoft.com/ja-jp/atlife/tips/archive/office/tips/002.aspx


たしかにこの機能により重複行を削除し、ユニークデータのリストの作成が可能なのだが、元データが更新されれば、同じ作業をし直さなければならず、この機能を活用するシーンは私自身はあまりない。正直、単純で汎用性が乏しく実務では応用した活用が難しいのだ。


一方、ピボットテーブルを使うことで元のデータが更新されても重複行を削除した表/リスト作成が可能だ。この方法であれば、元データが更新されてもピボットテーブルの「更新」をすることで対応できる。

ただし、注意したいのは、ピボットテーブル レポートのピボットテーブルは構造化参照可能なテーブルではない。ユニークデータリストを作って終わり、ではなく、そこから何らかの集計・計算・データ利用において、構造化参照によるテーブルの利用ができないため、他への再利用が難しい。テーブルではないことから、データの増減が発生したときの構造化参照テーブルのメリットも使えない。

上記の例ではデータカウントを取ることが目的ではない。逆にピボットテーブルなのでカウントの取得は簡単だ。しかし、このブログでも再三紹介しているように、現在そして今後 Excel はテーブル機能をベースにして拡張、新機能が追加されている。なるべくテーブルを使った課題解決方法を手にしておきたいところだ。

参考までに、もちろん、VBA を使った重複行削除のテクニックもある。

重複行を削除する（OfficeTANAKA）
http://officetanaka.net/excel/vba/tips/tips14.htm

VBA を使えば、重複行削除をしたユニークデータのリストを作成し、それをテーブルに変換することができる。

ただし今では VBA プログラミングをせずに「機能」だけで上記を実現できる。 それが Power Query だ。

Power Query を使った重複行の削除

Power Query については以前にも一部の機能を紹介しているが、そこでも述べたように Power Query はサーバーやクラウドからデータを Excel に取り込むことだけを目的としたアドインではない。Excel のテーブルもデータ ソースとして指定が可能だ。

そして、Power Query のクエリ エディターの「列の削減」で「重複部分の削除」の機能があるのだ。
これを使うことで、「重複行の削除」と同様のことができる。

もちろん、Power Query で取り込んだデータは Excel のテーブルとなる。構造化参照可能なユニークデータの「テーブル」となる。これでピボットテーブルの重複行削除でできなかったことが可能になる。

Power Query が Excel をデータ ソースに変える

しかし、本当に重要なのは、重複行の削除ができることではない。

データを Excel のテーブルとして持ち、Power Query を使うことで、Excel をまるで「データベース」のように扱うことができる、ということが重要なのだ。これが Power Query をすべての Excel ユーザーに勧めたい理由である。 重複行の削除はほんの一例でしかない。

蓄積されたデータの中から必要なデータを抜き出し、それを加工したい、分析したい、集計したい、というニーズは Excel ユーザーにとっては「通常」のことだと思う。

そしてその作業を「繰り返して」はいないだろうか。更新されたデータ（ワークシート、テーブルといったデータの固まり）を対象に、同じ条件で抜き出す、加工する、レポートを作る、といったことだ。

手作業でデータを抜き出すのであればオートフィルターを使っているだろう。そこから絞り込んだデータを他のワークシートやブックにコピーしていないだろうか。

実践ワークシート協会の VBA セミナー スタンダードを受けた受講者であれば、それら一連の作業を VBA で行うことができるだろう。

Power Query を使うことで、Excel のテーブルをデータ ソースとして指定し、抽出するための複数条件をクエリ エディターを使って設定し、結果を Excel のテーブルとして出力する、それらすべての設定を「クエリ」として保存し、再利用が可能なのだ。

そして、Power Query は条件を設定し抽出するだけではない。カスタム列の追加も可能なのだ。ピボット テーブルの集計フィールド、Power Pivot の集計列と同じだ。データ型の変換までできる。

以下のアニメーション GIF では、Data2 列の数値を文字列変換し、ゼロパディングで 1 を 001 にして Data 列の文字列と結合させ、文字列の Data3 を数値に変換している。列の順番も変更可能だ。このようなデータの操作・加工も Power Query で可能で、ある意味、元のテーブルからまったく別のテーブルを作っているようなものだ。

Power Query のクエリ エディタの式で使える関数はワークシート関数でもなく、Power Pivot の DAX 関数でもない。しかし、マイクロソフトが公開している記事を参考にしながら Excel のワークシート関数の知識で試してみればその使い方はわかると思う。重要なのは全く別のものでなく、かぶっていることが多い、だから、Excel ユーザーであれば「わかる」だろう、想像してみよう、ということだ。

上記のアニメーション GIF でも、関数の Number.ToText の引数として、"000" を入れたのはワークシート関数の TEXT 関数の知識からだ。それで期待通りの動きになるのはさすがマイクロソフトと言わざるを得ないだろう。

少しでもこの情報が参考になれば幸いである。

Read More

Excel Online / Excel Web Access (Excel Services) - データ接続の更新 SharePoint リスト

Office 365 SharePoint Online に Excel ブックを保存して他のユーザーと共有する、という使い方でもメリットがあるが、できれば保存したブックの中を簡易的に確認したい、編集する必要はなく参照だけで良い、という使い方もあるだろう。

 

このような場合、Office 365 SharePoint Online では以下の使い方が用意されている。

 

・ Excel Online で Excel ブックを開く

・ Excel Web Access Web パーツ(Excel Services) でサイトのページに貼り付ける

 

いずれの場合も「現時点での最新データを見たい」という目的であることは明確だ。

 

ブックで扱うデータがワークシートへの手入力の場合、Excel ブックの SharePoint に保存した状態を参照できる。ある意味、それが最新であり問題はない。問題になるのは「データ接続」している場合である。

 

Excel はさまざまなデータ ソースに外部データ接続機能を使って接続できるが、今回は SharePoint リストに接続したブックを Excel Online や Excel Web Access Web パーツで扱う場合を紹介したいと思う。

 

注意点としては、一部 TechNet や MSDN に明確に書かれていない方法を紹介することになる。米国マイクロソフトの英語版 Office ブログやフォーラムで Microsoft の担当者からの情報などを参考にしているが、私自身はそれを元に TechNet/MSDN といった公式技術文書で同様の記述をまだ見つけ出すことができていない。その点は留意されたい。

 

1つではない SharePoint リストとのデータ接続方法

 

SharePoint リストのデータを Excel にインポートする方法の代表格は SharePoint リストの「リスト」タブにある「Excel にエクスポート」だろう。

 

 

通常業務でリストのアイテムを Excel に取り込んで PC で集計・分析・レポートを作るのであれば、このエクスポートでほとんど問題がない。

 

ところが、この接続方法を使ったブックを SharePoint に保存し、それを Excel Online で開こうとして以下のようなメッセージを見たことがある人は多いだろう。

 

 

[詳細の表示] ボタンをクリックすると以下のダイアログが表示される。

 

 

このメッセージを見れば、多くの人は「SharePoint リストを使った Excel ブックは Excel Online で使えないのか」と思っても仕方ない。この接続方法を含んだブックのデータ接続は Excel Online で使えない、サポートされていないことは事実である。

 

実は SharePoint リストのデータを Excel にエクスポートする方法は数種類ある。区別を明確にするために、「接続のプロパティ」の「接続の種類」で使われている名称を使って分類したものが以下だ。

 

a) 接続の種類 : SharePoint リスト

SharePoint の [リスト] タブの [Excel にエクスポート] を使ったデータ接続。

 

b) 接続の種類 : Office データ接続

Excel のデータ タブの [その他のデータソース] の [OData データ フィード] を使ったデータ接続。
データ モデルは強制的に作成される。

 

c) 接続の種類 : OLE DB クエリ

PowerQuery の [その他のデータソース] の [OData フィードから] を使ったデータ接続。

ただし、データモデルの作成はしていないタイプ。

 

d) 接続の種類 : モデル OLE DB クエリ

PowerQuery の [その他のデータソース] の [OData フィードから] を使ったデータ接続。

データ接続作成の際、データモデルの作成も指定。

 

Excel と Office 365 SharePoint Online との接続という意味では上記の4つがある。

 

「SharePoint リスト」という接続の種類を含んだブックは Excel Online では利用できないが、MSDN や TechNet、Office Online などを調べると、OData フィードによる接続は Excel Online で利用可能、という記述を見つけることができる。

 

https://support.office.com/en-us/article/Use-external-data-in-workbooks-in-SharePoint-Online-8d7f5dc6-8384-4d7d-b00f-b283c1e192ef?ui=en-US&rs=en-001&ad=US

 

ところが、OData フィードによる接続も上記のように「Office データ接続」、「OLE DB クエリ」、「モデル OLE DB クエリ」の3種類存在し、かつ、そのまま利用しても、いずれの OData フィードのデータ接続でデータ接続の「更新」(refresh)ができないのが現状だ。

結論からいえば、ある「設定」をすることで、Excel Online や Excel Web Access Web パーツでもブックのデータ接続を更新して最新のデータを見ることが可能だ。その手順およびそれに対応した接続方法を紹介する。

 

Office データ接続で SharePoint リストをエクスポートする

 

Excel Online や Excel Web Access Web パーツ（Excel Services) での利用を考えているならば、SharePoint リストからのデータ取得は接続の種類「Office データ接続」を使うべきと言える。この接続方法であれば、ある設定（アプリ権限の付与）をすることで Excel Online 上でデータ接続更新が可能になる。

 

では、Office データ接続による OData データ フィードの構成をしてみよう。

 

1) エクスポートしたい SharePoint リストの URL を控える。

 

たとえば、以下のようにブラウザで SharePoint リストを表示した時、控えておきたい URL は "_layouts/15/start.aspx#/Lists/Seminar/" の前にある "https://jpwa.sharepoint.com/sites/r2co/" を控えておく。

 

2) Excel のデータ タブ - その他のデータ ソース の OData データ フィードで接続を構成する。

 

データ タブの [OData データ フィード」を開く。

 

 

データ接続ウィザードのダイアログが開く。
控えておいた URL の後に "_vti_bin/listdata.svc " と入力して [次へ] をクリックする。
このサンプルの場合は、"https://jpwa.sharepoint.com/sites/r2co/_vti_bin/listdata.svc "と入力する。

 

 

[追記] ここで Office 365 へのサインイン画面が表示される場合がある。一度、接続に対してアカウントとパスワードを登録することで、接続情報を削除しない、パスワードを変えないかぎり、接続の際のサインイン画面をスキップすることが可能になる。
[追記終わり]

テーブルの選択をする。ここでのテーブルは SharePoint の「リスト」を指している。
取り込みたいリストにチェックを入れて [次へ] をクリックする。

 

 

ファイル名や説明を変更できる最終ダイアログが表示される。Excel Services などの認証は変更せずにこのまま [完了] ボタンをクリックする。

 

 

データのインポート ダイアログが開く。表示方法の選択肢があるが、テーブルとしてインポートするのであれば、そのまま [OK] をクリックする。ここで [このデータをデータ モデルに追加する] オプションがチェック済みになっていてグレイアウトされている。強制的にデータ モデルを作成していることがわかる。

 

 

SharePoint Online のリストが Excel のテーブルとしてインポートされた。

 

 

この素のままのテーブル データを見るより、ピボット テーブルを使ってレポート形式にした方が実用的だ。このテーブルを利用してピボット テーブルを作成する。もちろん、この前の処理でのデータ インポートで「ピボット テーブル レポート」を選択して、素のテーブルを取り込まないことも可能だ。

 

OData データ フィード接続を含んだブックを SharePoint に保存する

このブックを SharePoint Online のドキュメントライブラリに保存する。
一旦ローカルに保存したものをアップロードしてもよいし、直接 SharePoint Online のドキュメントライブラリーを指定してもよい。この時、上で作ったピボットテーブルだけを Excel Online で表示・参照させたい場合は、ブラウザーオプションでピボットテーブルだけを指定しておく。

 

ではこのブックを SharePoint Online から Excel Online を使って開いてみる。
SharePoint リスト接続と違うのは、ブックを開いたとき、SharePoint リスト接続のような警告メッセージは表示されずに、指定したピボットテーブルが表示される。

 

なお、この状態でピボットテーブルのフィールドリストを操作してデータの分析が可能であり、これだけでも使い道は多いにあるだろう。

しかし、まだ、これだけでは SharePoint とのデータ接続のデータ更新は成功しない。Excel Online でデータ更新しエラーになる状況をアニメーションGIFでとったものが以下である。なお、データは上記とは別のブックで、データ接続更新設定をしていない別のテナント(Office 365) で実施したものになる。

SharePoint リスト接続とは違う以下のエラーメッセージが表示され、データ更新に失敗している。

----
外部データの更新が失敗しました。
ブック内のデータ モデルを処理しているときにエラーが発生しました。もう一度やりなおしてください。

このブックに指定されている１つ以上のデータ接続を更新できません。
以下の接続を更新できませんでした：
----

接続名はデータ接続ファイルを保存した時に指定したものが表示される。

Excel Online でデータ接続更新を可能にする設定（アプリ権限付与）を行う

冒頭に述べたように私自身が Office Online/TechNet/MSDN 内で公式な技術文書として探し出せていないのが、この設定である。ただし、この情報ソースは米国マイクロソフトの英語による社員ブログやフォーラムでマイクロソフト社員より提供されているものである。

(参照)
Office Blogs - Project Online and Excel Web App: Cloud data improves reporting
Project Online の OData フィードを Excel Web App で利用しデータ更新を可能にする設定について書かれたブログ（2013年3月29日）
http://blogs.office.com/2013/03/29/project-online-and-excel-web-app-cloud-data-improves-reporting/

[SOLVED] Excel service refresh issue
SharePoint リストを OData データ フィードで Excel 2013 で取り込み、Excel Online でデータ更新できない件について、MSFT Support から、この設定を提示しているフォーラムの投稿（2014年12月20日）
http://community.office365.com/en-us/f/172/t/284523.aspx

もし、Office データ接続 OData データ フィードを使った Excel Online でのデータ更新が成功している場合、すでにこの設定が他の管理者権限を持っているユーザーによって行われていると考えられる。この設定登録はサイト コレクションレベルでの登録だが、設定そのものはOffice 365 の「テナント」レベル（契約している Office 365 全体）にも登録される。そのため、例えば、テスト用のサイト コレクションでアプリ権限付与設定を行い、テスト終了後にサイト コレクションに登録された権限付与設定を削除しても、テナントレベルの登録を削除しない限り、テナント全てのサイト コレクションで有効状態になっている。そのため、該当するサイト コレクションで登録していなくてもデータ接続更新が可能になっている場合がある。

[追記] 上記の表現は正確でなかった。リンク先の記事の XML で「テナント」範囲での指定をしているからだ。スコープの指定がサイト コレクションであれば、登録したサイト コレクションのみ有効になる。サイト コレクションのみ有効になる XML は追記した。
[追記終わり]

登録するアプリのプリンシパル ID は "00000009-0000-0000-c000-000000000000" である。現在このプリンシパル IDのアプリ名は "Power BI" もしくは "Microsoft Power BI Reporting and Analytics" となっているはずだ。上述の Office Blogs では "Microsoft Azure Analysis Services" だった。(2013年3月末）
今後もアプリ名(Title)が変わる可能性があることに注意されたい。

1) テナントでの権限付与状態の確認

上記のプリンシパル ID のアプリへの権限がテナントに登録されていないことを一応確認する。
この確認は全体管理者権限を持っていないとできないのでユーザーの権限に注意すること。

管理ポータルを開く。

もしくは、以下から管理ポータルを開く

SharePoint 管理センターに移動する。

SharePoint アプリの管理へ移動する。左サイドバーの [アプリ] をクリックする。

アプリの権限をクリックする。

アプリの表示名に「Power BI」もしくは「Microsoft Power BI Reporting and Analytics」が無い、もしくは、「00000009-0000-0000-c000-000000000000」を含んだアプリIDが無いことを確認する。

なお、登録されているアプリはそれぞれのテナント環境で違うので上記図と同じにならない場合もあることを留意されたい。

2) サイト コレクションレベルでの確認とアプリの登録

登録は管理センターからはできず、サイト コレクションから行う。どのサイト コレクションから登録しても結果としてテナント レベルの登録になるが、一応、Excel Online で使いたいブックを含んだサイトから登録する。

[追記] テナントレベルの登録になるのは、後述する XML によるアプリの権限要求でテナントレベルを指定したためだった。
参考： http://msdn.microsoft.com/ja-jp/library/office/fp142383(v=office.15).aspx
追記したアプリの権限要求 XML で登録作業をしたサイト コレクションのみ有効にすることが可能。
[追記終わり]


SharePoint サイトに移動して右上の「歯車アイコン」から「サイトの設定」を選択する。

[サイト コレクションの管理] の [サイト コレクションのアプリの権限] をクリックする。
サブサイトのサイトの設定画面を開いている場合は [トップ レベルのサイト設定に移動] をクリックして、[サイト コレクションのアプリの権限] をクリックすること。

Microsoft Power BI Reporting and Analytics が無いことを確認する。

次はアプリの登録とアクセス権の設定をするのだが、これまで行ってきたメニューからの操作が現状ではできない。登録画面の URL を直接入力することになる。

現在、[サイトの設定 > サイト コレクションのアプリの権限] の画面を開いている。その URL は以下のようなものだ。/_layouts/ より前の部分はそれぞれの環境で違うが、/layouts/ 以降は同じだ。

https://hogehoge.sharepoint.com/sites/hoge/_layouts/15/start.aspx#/_layouts/15/appprincipals.aspx

この appprincipals.aspx を appinv.aspx に変更して Enter キーを押す。

すると以下の画面が表示される。

アプリID: に 00000009-0000-0000-c000-000000000000 を入力し、[参照] ボタンをクリックする。
タイトルに [Power BI]（違う場合もある)、アプリ ドメインに [analysis.windows.net] が表示される。タイトルはテナントの SharePoint Online のリリースによって違う場合があることを確認している。

アプリの権限要求 XML に以下の XML をコピーして貼り付け、[作成] ボタンをクリックする。

<AppPermissionRequests><AppPermissionRequest Scope = "http://sharepoint/projectserver/reporting" Right="Read"></AppPermissionRequest><AppPermissionRequest Scope = "http://sharepoint/content/tenant" Right="FullControl"></AppPermissionRequest></AppPermissionRequests>

[追記] http://msdn.microsoft.com/ja-jp/library/office/fp142383(v=office.15).aspx を参考にして、必要ない projectserver の AppPermissionRequest Scope を除き、サイト コレクションでの権限にしたものが以下になる。

<AppPermissionRequests>
  <AppPermissionRequest Scope = "http://sharepoint/content/sitecollection" Right="FullControl"></AppPermissionRequest>
</AppPermissionRequests>

この XML で Excel Online のデータ接続更新が可能を確認している。
[追記終わり]


タイトル名のアプリを信頼しますか？という確認画面がでるので、[信頼する] ボタンをクリックする。

サイトの設定画面にもどるので再度[サイト コレクションのアプリの権限]を開いて登録されていることを確認する。繰り返しになるが、アプリのタイトルはテナントのリリースによって違うことが確認されている。重要なのはアプリIDであることに留意されたい。

この状態で、(追記： アプリ権限要求の XML で Scope をテナント指定していれば）再度テナントレベルを確認すると以下のようにアプリが登録されていることがわかる。

なお、上記の操作でアプリのタイトルが「Power BI」になっているが、この操作をしたテナントでは Power BI for Office 365 のサブスクリプションは購入していない。

もし Power BI for Office 365 をすでに購入していた場合は、テナントレベルでのアプリの権限で「Power BI」というアプリの表示名が表示されるが、そのアプリ ID は 00000009-0000-0000-c000-000000000000 ではない。これで判別ができるだろう。

3) Excel Online でデータ接続更新の確認

Office データ接続 OData データ フィードによるデータ接続を使ったブックをアップロードし、レポートのアプリ ID を登録してアクセス権を付与した状態で、はじめて Excel Online 上でのデータ接続更新が可能になる。実際に試してみよう。

以下は、上述でアニメーションGIFで失敗例としてあげた使ったブックと同じものである。
上記手順でアプリの登録と権限付与を行い、データ ソースである SharePoint リストでアイテムを追加登録した状態で Excel Online でデータの更新をした。

Excel Web Access Web パーツをサイトに貼り付け、データ更新を実行したのが以下だ。

いつものお約束 - 注意点

実務で実際にこの機能を運用すると、以下の事にすぐ気づくはずだ。

1) データ更新しても、その状態でブックは保存されていない

よって、次に開いたときやブラウザを F5 でリロードすると「元のデータ」に戻る。

これは、通常のローカル PC の Excel のピボットテーブルを考えてもらえれば想像に難くない。データ更新しても、ブックを保存しないで Excel を終了させているようなものだ。

ただ、この設定をすることで、[Excel Online で編集] においてもデータ接続の更新が可能になるので、編集モードにしてデータ接続の更新をすれば「保存」したことになり、データも最新になった状態になる。

結局、参照のみの Excel Online でのデータ更新や、Excel Web Access Web パーツでのデータ更新より、Excel Online の編集モードでデータ更新、そして保存、という運用になってしまう。PC の Excel で更新、アップロード、という手間がなくなった、ということだ。

2) データ接続の自動更新はできない

データ接続のプロパティで自動更新のオプションがあることを知っている人も多いだろう。

これは使えない。設定してもなんの変化もない。
理由は、データ モデルが更新されていないためである。データ モデルはピボットキャッシュのようなものだと考えれば理解できる人もいるだろう。実データはデータ モデルを介してサーバー側からとりこむため、データ モデルを更新しないかぎり、ピボットテーブル レポートのデータは更新されない。そして、データ モデルの接続プロパティの [定期的に更新する] オプションはグレイアウトされて設定不可能になっている。

3) Office データ接続のみ有効で PowerQuery によるデータ接続の更新はできない

PowerQuery のデータ モデルを使った接続 (モデル OLE DB クエリ）であっても、上記のアプリ ID とアプリ権限設定後、Excel Online や Excel Web Access Web パーツ内でのデータ更新はできない。

以下のメッセージが表示されエラーになる。

外部データの更新が失敗しました
ブック内のデータ モデルを処理しているときにエラーが発生しました。もう一度やり直してください。
このブックに指定されている 1 つ以上のデータ接続を更新できません。
以下の接続を更新できませんでいた：
Power Query - List01
接続： Power Query - List01
エラー： OnPremise エラー：問題が発生しました。もう一度やり直してください。
テーブル "List01" の処理中にエラーが発生しました。
トランザクションの別の操作が失敗したため、現在の操作は取り消されました。

もう一度やりなおして、、、とあるが、何度やり直してもデータの更新はできない。
Power Query によるモデル OLE DB クエリ / OData フィードは Power BI for Office 365 の Power BI サイトで使用する。

現状、複数あるデータ接続が、使用する機能別に用意されているため難解になっていることは否めないが、ここは過去の資産の蓄積と将来のために追加された新機能として理解するしかないかもしれない。

まとめ

Office 365 SharePoint リストと Excel 連携を最大限に活用するならば、SharePoint のリスト タブにある「Excel へエクスポート」（SharePoint リスト接続）を使わず、Excel のデータ タブにある「OData データ フィード」（Office データ接続）を使ったほうが便利になりそうなことは理解できたと思う。

しかし、ものすごく便利になるか、といえば微妙であるのは否めない。
ローカル PC の Excel で集計・分析し、それを SharePoint にアップロード、アップロードした時点での情報を Excel Online/Excel Web Access Web パーツで表示、という運用で多くはカバーできるのも事実である。

Office データ接続の OData データ フィードで、Excel Online / Excel Web Access Web パーツのデータ更新が可能になるメリットを享受できるが、たぶん、実務でこの機能を求めるのであれば「自動更新」というニーズがあるはずだ。
残念ながら、Excel Online と OData データ フィードだけでは自動更新のニーズを満たすことはできない。

この自動更新のニーズを満たすのが Power BI だと考えている。

事実、Power BI には以下の設定オプションがある。

残念ながら PowerBI はサブスクリプション購入したばかりで実務運用のレベルまで使っておらず、かつ、その設定も確実に理解していないため、これ以上の紹介はできないが、近いうちに紹介することができるだろう。

非常に長いエントリーになってしまったが、マイクロソフトによる日本語ドキュメントがまだ整備されていないようなので、何等かの参考になれば幸いである。

Read More