2014/11/28

Excel ユーザーのための Office 365 SharePoint Online

Office 365 というサービスは今後のマイクロソフト社の主流のサービスと位置付けられていると考えてよい。多くの製品開発はまず Office 365 というクラウドサービスを対象に行われ、その後、オンプレミスと呼ばれるサーバー製品にフィードバックされることが、マイクロソフトの開発責任者によって明らかにされている。

Office にしても、Office Premium や Office 365 Solo といった商品・サービスが登場し、クラウドや Office 365 の存在を無視し続けることがデメリットになりつつある。まして、企業で Office を利用しているユーザーであればあるほど、Office 365 の恩恵を受ける可能性が高いのだ。

その Office 365 の中でとりわけ利用・活用に頭を悩ませているのが SharePoint Online であるというユーザーも少なくない。SharePoint Online は「なんでもできる共通基盤」として紹介されてしまうため、逆に何をやっていいかのイメージがつきづらいサービスである。


実際、コラボレーションというキーワードから「社内ポータルサイト」および「共有文書管理のファイルサーバー」として使うユーザーもいれば、Notes からの移行でワークフローの機能を使い、社内のプロセスをワークフローでまわそうとする企業もある。

そこに独自の開発を入れる、もしくは開発を入れないと要件が満たされないということから、開発を依頼する会社も多い。


しかし、反面、SharePoint は Excel ユーザーにとっては「長年の課題」をお手軽に解決する可能性があるサービスであることはあまり語られない。

Excel ユーザーが長年課題として持っているもの、それは情報の収集・集約方法である。

複数のユーザーに対して Excel ブックで入力シートを作成し、それを配布、記入後にメール添付で返信、というような使い方をしているユーザーは多いだろう。そして、それら返信されたブックを開き、集約する作業、コピーする作業などを手作業でやるか、VBA でマクロ機能を使うか、といったものである。

そこで、SharePoint の「リスト」である。
SharePoint の「リスト」という機能は Excel との親和性が非常に高い。

以前に Excel のブックをそのまま SharePoint リストにする方法を紹介した。
http://road2cloudoffice.blogspot.jp/2014/11/office-365-sharepoint.html

実際、このようにすんなりいくことは稀であり、SharePoint リストの構造、リストでやれることやれないことを理解することで、この Excel エクスポートを使いこなすことも可能になる。

そこでおさえておきたい SharePoint リストの機能を紹介したい。ただ、この活用のポイントは SharePoint 側で「あまり凝ったことをやりすぎない」であることは最初に述べておく。そのかわり我々には Excel があるのだから、Excel でその補填をすることを考えると幸せになれるかもしれないということだ。

SharePoint のリストはワーシートみたいなもの

以下を見てほしい。これは SharePoint のリストをデータシートビューでみたものである。


このように1行が1件分のデータとして表示されているのを見るとワークシートの表に近いことがわかると思う。すでに紹介しているが、SharePoint リストは Excel へのデータ エクスポートが容易であることから、もし、データ入力が SharePoint 側で行われれば、その後の集計や分析は Excel のみで実施することができるのである。

SharePoint リストの1件分のデータを構成する「列」はどのようなものからなるか。始めから Excel にエクスポートすることを念頭において構成することで、その後の作業効率が大きく変わることは言うまでもない。

列作成で選択可能な種類

・ 1行テキスト
Excel にエクスポートすれば「文字列」になる。

・ 複数行テキスト
Excel にエクスポートすれば「標準」になり、データ(アイテム)のどれかに改行が入ったものがあれば「折り返して全体を表示する」のチェックがついた状態になる。いずれも文字列扱い。

・ 選択肢(メニューから選択)
Excel では文字列になる。選択肢は列の設定で候補を入力して、そこから選択した文字列が入る。

・ 数値(1, 1.0, 100)
Excel にエクスポートすると「通貨」分類の記号「なし」に設定される。パーセンテージの設定の場合は、「50.5%」とリスト表示され、Excel 側では表示形式「パーセンテージ」の 50.5% となる。

・ 通貨
あまり使わないが、リスト側で「\2,000」と表示され、Excel では表示形式「会計」の記号「\日本語」になる。

・ 日付と時刻
SharePoint リストのアイテム入力ではカレンダーコントロールを使うことができる。また、直接文字の入力も可能である。Excel にエクスポートすると、日付として認識されシリアル値としてエクスポートされる。

・ 参照
SharePoint リストの列の参照は、他のリストのデータを参照して、そこからドロップダウンなどで選択できる。Excel 側では文字列として設定される。

・ はい/いいえ(チェックボックス)
SharePoint リストでの入力画面はチェックボックスのオン/オフだが、入力後の表示は「はい、いいえ」になる。Excel にエクスポートすると「TRUE/FALSE」となり、標準セル扱い。


おおよそ上述の種類から選択することになるだろう。
うれしいことは日付のデータを「シリアル値」として Excel 側にエクスポートしてくれる点。( Excel が日付と判断してシリアル値にしていると思ったが、SharePoint リストの集計フィールドの数式でシリアル値を扱うことができるのでシリアル値をエクスポートしているかもしれない。) 他についても表示形式を設定して文字は文字、数値は数値として Excel 側で認識することが可能である。

ちなみに、文字列(1行テキスト)として定義した列の「001」は当然文字列の「001」としてエクスポートされ、「1-1」が「1月1日」になることはない。

なお、これらの列定義をしてリストを作成すると、入力画面は以下のようなものになる。
カレンダーコントロールや、ドロップダウンリスト、チェックボックスでシンプルな入力支援が可能だ。
必須入力の設定やちょっとした入力規則のような設定、IMEのオン/オフの設定も可能なので、ユーザーが入力しやすい、誤入力を避ける設定を検討すると良いだろう。


ビューやオートフィルターを使って「表形式で編集」する

SharePoint リストの画面では、特に設定をしなくても Excel と同じように「オートフィルター」による絞り込みが可能である。オートフィルターをうまく使って「リスト編集」によるデータシートビューでデータを確認、変更するのは Excel を使ってデータを操作する感覚に似ている。

ここで、入力されたデータの整合性のチェックなど、あるロジックを追加したい場合、手作業・目視で1件1件調べるか、SharePoint のアプリケーション開発となるが、このリストデータをそっくり Excel 側にエクスポートすれば、Excel ユーザーはワークシート関数やピボットテーブル、または VBA を使ってチェックが可能である。 Excel を使いこなしていれば、ここに開発のコストをかけることなく、整合性チェック程度は可能になるのだ。

もちろん、レコード件数を意識することになるが、数千件程度であれば現在の Excel と PC 能力をもってすればそれほど時間のかかる重い処理にはならない。

SharePoint リストはテーブル形式でエクスポートされる

SharePoint リストのデータはテーブル形式で Excel 側にエクスポートされるので、行数が変わっても構造化参照やピボットテーブルから参照していれば、「データ更新」するだけで最新のデータを参照・利用することが可能である。

テーブルのすすめ 構造化参照
http://road2cloudoffice.blogspot.jp/2014/11/blog-post.html

おおよそ、このようにテーブル形式でエクスポートされたデータを Excel で扱うのはピボットテーブルが使いやすい。このエクスポートされたテーブルは iqy ファイルを使ってデータ接続し、ワークシート上のテーブルのデータを更新するタイプで、前回まで紹介した「データ モデル」を使っていない。よって、データ モデルで話題となった集計フィールドや集計アイテムはこのテーブルを参照したピボットテーブルで使える。

なお、このようなデータ接続は SharePoint から Excel への一方通行のデータ接続である。Excel 側でテーブルを修正しても、その修正結果は SharePoint には反映されない。あくまで参照用であることを心に留めてほしい。

よって、Excel 側で整合性のチェックをし、その情報を元に SharePoint のリストのデータを修正、さらにデータ更新をかけて、整合性のチェックを行うことを繰り返すことになる。

また、そのチェック用の Excel ブックは SharePoint に保管して、他のユーザーと共有にしてもよい。他のユーザーもリストがあるサイトにアクセスが可能であればブックの接続プロパティにあるプロバイダとコマンド文字列の情報から、PCにある Excel でブックを開いてデータソースの更新が可能になる。

問題は Excel Services と Excel Online

上記の方法の問題点は Excel Services と Excel Online で iqy ファイルを使ったデータ接続の更新ができないことである。

ただし、SharePoint リストと Excel のデータ接続は iqy ファイルを使った接続だけではない。
このあたりの情報が整理つき次第紹介したいと思う。

[追記] OData データ フィード接続による Excel Online データ接続更新可能の記事が以下になります。
http://road2cloudoffice.blogspot.jp/2015/01/excel-online-excel-web-access-excel.html
[追記終わり]

ポータルだ、サイトだ、と考えずに

全社/部門ポータルを作る、などのアプローチから SharePoint に触れるケースも多いだろうが、現状 Excel でやっているこまごまとした業務を「入力」「計算」「出力」のロールやフェーズにわけて、入力の部分を SharePoint リストで行い、計算や出力の部分を Excel で行ってみてはいかがだろう。

SharePoint リストの場合はリスト アイテム単位のアクセス権の設定はリストの詳細設定から可能だが、込み入ったデータの扱いではリスト側で一工夫必要になる。それでも、単純な集計や申込み・申請業務などは SharePoint リストと Excel データ接続による Excel の処理を検討してみる価値はあるだろう。


2014/11/26

PowerPivot で計算フィールドを使う

Excel の Pivot テーブルの集計アイテムや集計フィールドを使いこなすのはなかなか難しいが、もし使いこなしているとすれば、PowerPivot で同様に機能を提供する「計算列」や「計算フィールド(メジャー)」の考え方は問題ないであろう。

PowerPivot で「集計アイテム」や「集計フィールド」がグレイアウトされて選択できない状態になるが、これはこれまでの制限の裏返しでもある。

以下の Excel のピボットテーブルの Office Online の記事「ピボットテーブル レポートで値を計算する」を読むとわかるが、ことあるごとに「OLAPデータベースサーバーのデータは使えない、OLAPの管理者の問い合わせてください」というくだりが出てくる。

Office Online 「ピボットテーブル レポートで値を計算する」
http://office.microsoft.com/ja-jp/excel-help/HP010096323.aspx

逆に、PowerPivot を使えば、これまでの「集計アイテム」や「集計フィールド」は使えなくなるが、PowerPivot の「計算列」や「計算フィールド」を使うことで、データソースの種類に関係なく、Excel のピボットテーブル レポートを作成することができる、と言える。OLAP のデータも、Excel のテーブルもデータ モデルとして扱うことで、それが可能になるのである。

データ モデルという仕組みが、どんなデータであろうと Excel に対して共通のインターフェースを提供し、Excel 側では単一のツールでさまざまなデータソースのデータを利用できるメリットは大きい。

特に Office 365 と Excel の組み合わせでは、このデータ モデルによるデータソースの多様化の恩恵を PowerPivot や PowerQuery を使うことで最大限に受けることが可能になる。

話が逸れてしまったが、あらためて Excel はエンドユーザー側のフロントエンドのツールとして、レポート作成に使われたり、分析に使われたりする機会が多くなるだろう。そのため、PowerPivot などの機能を習得することは無駄にはならないはずである。

計算フィールド(メジャー)を定義する

計算列はすでに紹介したが、実際のところ計算列は理解するのにそれほど苦労はないだろう。ワークシートのような PowerPivot ウィンドウのデータビューの「列の追加」での操作は、ポイントとなる DAX 関数さえ知っていれば Excel ユーザーとっては慣れたものであろう。


一方で計算フィールドは計算列ほど単純ではないと感じるユーザーも多いかもしれない。
一番わかりやすいのは、数量などの合計をピボットテーブルでも計算するが、=SUM() などの計算式を入れずに「値フィールドの設定」から選択して使うことが多いだろう。これを PowerPivot では「暗黙的な計算フィールド」と呼んでいる。ピボットテーブルのフィールドリストの「Σ 値」のエリアに配置されるものだ。


計算フィールドの作成方法は2つの方法が存在している。ひとつは Excel の PowerPivot タブの計算にある [計算フィールド] を使う方法。もうひとつは PowerPivot ウィンドウのデータ ビューで「計算領域」に記入する方法である。

PowerPivot タブの[計算フィールド]

PowerPivot ウィンドウの計算領域での計算フィールドの設定

計算フィールドそのものの解説や、計算フィールドの利用方法や設定方法、計算フィールドと計算列の違いなどは以前紹介した自習書やピボットテーブルの解説を参照してほしい。重要なのは PowerPivot を使ったからといってピボットテーブル レポート内で数式を使った計算(集計アイテム)ができないことはない、ということだ。

PowerPivot 自習書
SQL Server 2012 自習書シリーズ
PowerPivot for Excel によるセルフ サービス分析

Office Online - ピボットテーブル レポートで値を計算する
http://office.microsoft.com/ja-jp/excel-help/HP010096323.aspx

Office Online - PowerPivot での計算フィールドの作成
https://support.office.com/ja-jp/article/PowerPivot-%E3%81%A7%E3%81%AE%E8%A8%88%E7%AE%97%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89%E3%81%AE%E4%BD%9C%E6%88%90-D3CC1495-B4E5-48E7-BA98-163022A71198?ui=ja-JP&rs=ja-JP&ad=JP

特に通常の Excel であれば条件付き書式を使ったデータの可視化、データ分析では KPI と称されることが多いが、これを集計フィールドの KPI アイコンとして設定することができる。この方法も自習書シリーズで解説されているので参考できるだろう。

2014/11/21

PowerPivot で計算列を作る

PowerPivot や Excel 2013 以降の新機能である「リレーションシップ」を使いテーブルをデータ モデルに追加するとピボットテーブルの「グループ化」や「集計フィールド」、「集計アイテム」が使えなくなることはすでに紹介した。

http://road2cloudoffice.blogspot.jp/2014/11/powerpivot.html

結論から言えば「データ モデル」を使った場合は PowerPivot を併用しないと、これまでピボットテーブルで行っていたことができないと思ってもいいだろう。前回は「階層」を使ったグループ化を紹介したが、今回は集計フィールドや集計アイテムに相当する「計算列」と「計算フィールド」について紹介する。

なお、繰り返しになるが、データ モデルを使うことで「グループ化」や「集計フィールド」や「集計アイテム」が使えなくなり、「新しい Excel 使えない」と早合点しないでほしい。そもそも PowerPivot は SQL Server や SQL Server Analysis Services と Excel を使った「データ分析」のために作られた Excel のアドイン機能であり、そのような分析のための機能は当然持っている。
そして、わざわざデータ モデルを使う理由はある。一つの例として巨大なデータを扱えることだ。データ モデルにすることでワークシートの制限がはずれ、100万行をゆうに超えるデータを扱えるようになるからだ。PowerPivot で扱う元データが数千万件であっても問題ない。そして、何件まで扱えるかは PC に搭載されているメモリーに依存する。そのため、そのような巨大なデータを扱う場合は Windows は 64bit を使い、Excel も 64 bit 版のものを使うことが推奨される。

計算列はすべてのレコードを対象に計算する

計算列や計算フィールドは PowerPivot ウィンドウで設定する。ここで設定をすることで、Excel のピボットテーブルのフィールドリストに表示され利用可能になる。そこからはこれまでのピボットテーブルと同様の操作性を提供することになる。

以下のようなリレーションのテーブルを例にして説明する。


個人売上テーブルのデータは「日付」、「名前」、「商品」、「個数」しかない。ここに売上金額の計算結果を追加したい、というケースだ。

個人売上テーブルの PowerPivot ウィンドウのデータビューが以下になる。


欲しいのは商品の単価と、個数と単価を掛け合わした金額である。
すでに個人売上テーブルと商品テーブルは「商品コード」(A,B,C,,,)でリレーションを張っているので、商品テーブルから「単価」の列をこの個人売上テーブルに追加する。
そのときに使うのが DAX 関数の RELATED 関数である。RELATED 関数を使うことで、関連付けられているテーブルから該当する「単価」が参照できる。Excel の VLOOKUP 関数のような働きをすると考えれば理解しやすいであろう。
以下がその操作である。Excel 同様に数式オートコンプリートも使える。非常に簡単なのがわかると思う。


次に個数と単価を掛け合わせた金額の計算列を追加する。これは Excel の数式とまったく同じ操作性だ。しかし、1点注意しなければならないのは Excel のテーブル同様、構造化参照の数式のように、最初の行に数式を入力することですべての行に対して同じ数式が入力されることだ。


これで金額の計算列が追加された。ここからピボットテーブルを作ってみよう。
そうすると、先ほど追加した計算列の「単価」と「金額」がフィールドリストにも表示されていることが確認できる。


個人売上テーブルには「商品名」はなかったが、商品テーブルとリレーションを張っているのでピボットテーブルでは参照可能だ。このためわざわざ商品名を RELATED 関数で参照する必要はない。


計算列ではほぼワークシート関数と同じものを使用することができる。
IF なども利用可能なので、単純な数値演算だけでなく、論理演算も可能だ。
Excel をすでに使いこなしているユーザーであれば、PowerPivot の計算列をすぐに活用できるだろう。

長くなったので、次回で「計算フィールド」について紹介したい。計算列がすべてのレコードを対象にして計算するものであり、計算フィールドは「ある条件で絞られたレコードを対象に計算する」と考えれば理解しやすいだろう。

マイクロソフトの記事
PowerPivotの計算列
https://support.office.com/ja-jp/article/PowerPivot-%E3%81%AE%E8%A8%88%E7%AE%97%E5%88%97-a0eb7167-33fc-4ade-a23f-fb9217c193af?ui=ja-JP&rs=ja-JP&ad=JP

PowerPivotの自習書
SQL Server 2012 自習書シリーズ
PowerPivot for Excel によるセルフ サービス分析

2014/11/16

PowerPivot で日付のグループ化

PowerPivot は強力な Excel のアドインであることは以前に紹介した。


ここで紹介したのが PowerPivot はデータモデルを採用しているため、これまでの Pivot テーブルの機能で使えていたものが使えなくなる、その代表が「グループ化」と「集計フィールド、集計アイテム」の追加だ。

コンテキストメニューのグループ化がグレイアウトされ選択できない状態

集計フィールド、集計アイテムがグレイアウトされ選択できない状態

特に日付のグループ化はデータを分析するためには必須である。この対応方法を今回は紹介する。なお、そもそも PowerPivot はマイクロソフト社の SQL Server のデータを Excel で分析するためのアドインとして開発された。そのため、Office や Excel から PowerPivot のテクニックを探すのは正直まだ情報が多いとは言えない。そのため、情報サイトなどで Excel を駆使して代替案を提示している場合が多いのだが、PowerPivot の機能として参照・検索するのであれば、SQL Server 側からの情報として探すと見つかる場合が多い。

日付のグループ化は「階層」を使う

(注) Excel 2016 の Power Query (取得と変換) は、自動グループ化機能が実装されました。

Excel はシリアル値というすばらしい仕組みを内蔵しているため、こと日付に関する処理・操作は非常に簡単に複雑なことができるようになっている。Excel の Pivot テーブルで日付をグループ化する機能などはシリアル値の恩恵を受けている。

一方、シリアル値を持たない通常のシステム、サーバーで日付を扱うには「年」や「月」といったデータを日付形式のデータから抜き出してレコードの別フィールドとして持たせる必要がある。Excel から見れば非常に面倒な処理をしているように思えるが、これは仕方ないとあきらめるしかない。

同様にデータモデルである PowerPivot では日付形式のデータから「年」や「月」を抜き出す必要が出てくる。

[PowerPivot] タブから [管理 データモデル] をクリックして、PowerPivot ウィンドウを開く。
そうするとそのブックに含まれているデータモデルを参照することができるので、該当するテーブルをデータシートビューで開く。


このデータモデルには「日付」があるので、このフィールドから「年」と「月」の列を新たに追加する。PowerPivot の場合はなるべく Excel ユーザーにも使いやすいように Excel に似た UI と関数が用意されている。(ただし、Excel の関数ではない。この PowerPivot で使う Excel のワークシート関数に似た関数を DAX 関数と呼ぶ。)

YEAR関数を使って年を抜き出す。データシートビューはワークシートではないので、関数の入力は直接セル(のようなもの)にできない。関数入力ボックスから行う必要があるので注意されたい。


MONTH関数を使って月を抜き出す。


列名は上書きで変更できるので、年、月とする。
次にこの「年」と「月」を使って、「年月」の階層を作成する。

データシートビューからダイアグラムビューに切り替える。
データモデルにある「年」で右クリックでコンテキストメニューを開き、「階層の作成」を選ぶ。


階層が作成されるので名前を「年月」にする。


「月」をドラッグして「年月」の「年(年)」の下にドロップする。


これで「年月」の階層が作られた。

これで PowerPivot ウィンドウから Pivot テープルを作ると、サンプルとして使った個人売上テーブルに「年月」フィールドが別の枠として追加されているのがわかる。


この「年月」の階層を使えば、日付のグループ化、グルーピングと同じことができるようになる。

以下がその操作だ。



まずは、PowerPivot の使い方などの情報は SQL Server の自習書としてマイクロソフトが公開しているので、本家の情報に目を通していただきたい。

SQL Server 2012 自習書シリーズ
PowerPivot for Excel によるセルフ サービス分析

MSDN PowerPivot for Excel チュートリアル
http://msdn.microsoft.com/ja-jp/library/gg413497(v=sql.110).aspx

2014/11/11

テーブルのすすめ Office 365 連携 SharePoint リスト

Excel 2007 以降で「テーブル機能」が重要になることを紹介してきた。

ピボットテーブルの参照先や、グラフの参照先にテーブルが指定されていれば、同様に元のデータの増減に自動的に対応する。元データの増減への自動対応は Excel 2003 以前では複数の関数を組み合わせることで対応できた場合もあったが、テーブル機能を使えばそのようなテクニックを使わなくても簡単に実現することがわかったと思う。

このような「参照先データ」は、通常「マスターデータ」と呼ばれるものや、ある条件の集計結果データであることが多い。(月次締めなど)それらの多くは個々人の PC に入っている Excel のワークシートで管理されていることは稀であり、小規模でも Access のデータベース、大規模になれば SQL Server や Oracle といった基幹システムのデータベース サーバーにある。

そのため、それらの参照データを「CSV形式」のファイルで入手し、Excel にインポートし、分析作業や、報告書作成のための集計作業をしているユーザーが今でも多い。

もし、それら元データの取得を Excel で取り込み、分析・集計作業が一連の流れの中で止まることなくできたら、、、と思うのは当然である。

Office 365 とは、Office 365 Pro Plus (Excel, Word, PowerPoint など) と、サーバーサービスである Exchange Online (メール サービス)、SharePoint Online (ポータル/ドキュメント共有サービス)、Lync (メッセージ サービス)を組み合わせた総称である。(Office 365 のプランによっては Office 365 Pro Plus が含まれないものもあるので注意すること。)

サーバーサービスが持っているデータと Excel を連携させるために、ここでも Excel の「テーブル機能」が重要な役割を担うことになる。いくつかご紹介しよう。

1) Excel のテーブルからリストを作る

SharePoint Online は「リスト」という機能を使って、データの蓄積が可能である。リストを使うためにはリストの設定をしなければならないが、このリストの基本設定は Excel を使ってできるのである。

以下のような「コースマスター」テーブルを使って SharePoint リストを作ってみよう。


コースリストのデータ列は「文字列」で書式設定されている。日数は数値、講師は文字列、開始日は YYYY/MM/DD のシリアル値だ。

SharePoint Online の詳細な説明はここではしないが、SharePoint で他の社員と共有するようなスペース(サイトと呼ぶ)を作成し、そこにこのコースマスターを元にしたリストを作ってみる。

SharePoint Online チームサイト 作成直後の初期状態トップページ

このサイトのトップページの URL を控えておき、Excel でコースリストのテーブル内にアクティブセルをおいて、リボンの [デザイン ツール] の [デザイン] タブの [外部のテーブル データ] セクションにある [エクスポート] の ▼ をクリックする。


[テーブルを SharePoint リストにエクスポートする] をクリックする。


アドレスに SharePoint のサイトの URL を入れる。ここでは読み取り専用接続を作成せずに、名前と説明を適宜入力する。なお、名前は英語で入力しておき、あとで日本語に変更することをお勧めする。 URL が最初に入力した英語で簡略化できるからである。


データ型についての確認ダイアログがでる。問題が無ければ [完了] をクリックする。
Excel のテーブルが正しく SharePoint のリストとしてエクスポートが成功すれば以下のダイアログが表示される。


ダイアログの URL をクリックすると、作成された SharePoint リストのページがブラウザで開く。


これで Excel テーブルの SharePoint Online のエクスポートが完了した。
勘違いしないでいただきたいのは、これは Excel のテーブルを「ひな形」として新たに SharePoint にリストを作成したものである。ここではなんの連携機能もない。コピーしたようなものである。

運用的に、今後は SharePoint Online のこの「コースリスト」がマスターデータとなって、データの追加や修正はこの SharePoint のコースリストで行う。このコースリストを参照する Excel ブックは、コピーを自分のブック上に持つのではなく、データ接続を使ってリアルタイムに SharePoint 上のコースリストを参照して、Excel で処理を行う、というものだ。

では、SharePoint のコースリストを新しい Excel ブックと連携させてみる。

SharePoint のコースリストのページにある [リスト] タブをクリックして、[Excel にエクスポート] をクリックする。


IE の下部に以下のダイアログが表示される。[ファイルを開く] をクリックする。





Excel が立ち上がり、データ接続をしようとするためセキュリティの確認ダイアログが表示される。[有効にする] をクリックする。


データのインポート方法を選択する。[テーブル] で、新規ブックで作成してみる。


SharePont Online のリスト「コースリスト」からテーブルが作成されたことが確認できる。


元の Excel のテーブルと比較すると [アイテムの種類] や [パス] が列として追加されている。

データ接続による SharePoint から Excel へのデータ エクスポートは SharePoint → Excel の一方通行である。Excel 側にあるテーブルはあくまで「参照用」であって、この Excel ブックのデータを変更しても SharePoint のリストが更新されることはない。

逆に、SharePoint 側のリストが変更されると、その変更はデータ接続をしているすべてのテーブルに反映される。ただし、その反映のタイミングは Excel ブックで [データ] タブの接続の更新もしくは [すべて更新] をクリックしたときである。(手動設定の場合)

一度データ接続を設定すれば、あとは明示的にデータ接続情報を消さないかぎり再利用可能だ。

以下は SharePoint 側で新たにデータを追加した手順である。

SharePoint のリストが更新された状態だけでは Excel ブックに変更は反映されていない。
以下、[すべての更新]をクリックした動きである。


このように SharePoint からのデータは「テーブル」となって Excel ブックで利用可能になる。
よって、Excel 側ではテーブルの利用方法、活用方法、さらにテーブルに対応したブックを作成しておくことで、Office 365 SharePoint との連携が現実となってくるのである。

なお、SharePoint Online のリストの制限(件数など)などが気になるだろう。
以下が SharePoint Online のリストの制限である。参照されたい。

列数: 列の要素によって制限が異なる。1行テキストの場合は最大 276 列を使用できる。
行数: 数千が実用範囲。数万もいけるがパフォーマンスを考慮すべき。数万の場合は Access アプリ(データベースエンジンは SQL Azure を使用)などを考慮。

アイテム数が多いリストとライブラリを管理する
http://office.microsoft.com/ja-jp/sharepoint-server-help/HA102771361.aspx

テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

2014/11/07

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

今回も Excel 2007 以降の重要な機能追加である「テーブル機能」を使った話です。

ピボットテーブルの参照先としてテーブルを使う

すでにテーブルを参照先範囲に指定することでデータの増減に自動的に対応する恩恵を受けることは入力規則や関数で紹介してきました。
同様にピボットテーブルの参照先範囲としてテーブルを指定すると、動的にデータの増減(参照先範囲の変化)に対応できます。ただしピボットテーブルの「更新」はこれまで同様押す必要はあります。


元データの行の追加だけでなく、列の追加にもピボットテーブルは対応します。


ピボットテーブルを使うユーザーはすでに参照先データがきれいな「表」になっているはずです。その表をテーブルに変換して、ピボットテーブルの参照先としてテーブルを指定するだけでこの恩恵を受けられるのです。これを使わない手はありません。

ただ、残念なことに、この「ピボットテーブル」の結果の表は「テーブル」機能で実装されていません。テーブル機能が出る前からピボットテーブルは存在し、その実装は同じ「テーブル」という用語を使っていても違うのです。ピボットテーブルの参照が構造化参照のようになれば、、、と思いがちですが注意してください。

そのかわりと言ってはなんですが、Excel 2013 以降でこのピボットテーブルとテーブルの組み合わせでさらに強力に業務を支援する機能が追加されています。それが「リレーションシップ」です。

VLOOKUPはもう使わない?Excel 2013 から実装されたリレーションシップ

上記で使ったテーブルには商品コードが記入されていて、商品名はありません。このようなデータの持ち方の場合は、商品テーブルが別に存在して、そこから商品名を VLOOKUP でこれまでは検索してきました。
(注釈ですが、この図のようなデータの持ち方、ピボットテーブルの配置はお勧めではないどころかやってはいけません。テーブルを使う場合は混乱を避けるために1ワークシート1テーブルとすべきだと個人的に考えます。すべてのデータを見せるためにこのような配置をしています。)


2つ程度であれば、列を追加して VLOOKUP を使ってデータを参照してもいいですが、基幹業務システムからデータをインポートしたり、何等かの形でそのデータを利用しようとしたりすると複数のテーブルに分けられた「正規化」されたテーブルであることが多いです。

これに対応するために Excel 2013 では「リレーションシップ」という機能が追加されました。

それぞれの参照データ範囲は「テーブル」に変換されている必要があります。
テーブルに変換されていると [データ] タブの [データ ツール] セクションにある [リレーションシップ] の [リレーションシップの管理] ダイアログで参照・テーブル間の関連付けが可能になります。


以下の3つのテーブルの関連付けを行ってみます。

個人売上テーブル


商品テーブル


地域テーブル


欲しいデータは個人売上テーブルの商品コードである A や B が商品名になったものや、その商品の地域名です。そのため、まず、個人売上テーブルと商品コードを関連づけます。ブック内のテーブルは「リレーションシップの管理」ダイアログのプルダウン リストに表示されるのでシートを移動して範囲指定するようなことはありません。個人売上テーブルで A, B, C... がある [商品] 列と、商品テーブルで A, B, C... がある [商品コード] を関連付けます。
続けて、商品テーブルと地域テーブルを関連付けます。今度は 001, 002, 003... がある列をそれぞれ関連付けます。



今は以下のような関連付けになっています。


ここまで設定したらピボットテーブルでこの関連付けが利用可能になります。
個人売上テーブルを参照先として指定したピボットテーブルを作ってみます。この時重要なのは、このリレーションシップを利用したい場合は必ず [複数のテーブルを分析するかどうかを選択] の [データモデルに追加する] をチェックすることです。


これで作成したピボットテーブルのフィールド リストにはこれまでなかったタブが表示されます。その中の [すべてのフィールド] をクリックすると、個人売上テーブルに関連付けられたテーブルが表示され、それを展開すると列名が表示されます。


この各テーブルの列名を使ってピボットテーブルを作成することが可能です。

このフィールド リストを使って、商品名別と地域名別の売上のピボットテーブルを作成してみます。


複数テーブルを使ったシンプルなピボットテーブルであれば、このリレーションの機能をどんどん活用すべきですが、その反面、実はこれまで Excel のピボットテーブルでできていたこと(それも重要なこと)がいくつかできなくなっていることに注意しなければなりません。(もちろん、その対応方法は存在します)それを以下に紹介します。

データモデルとは

ところで、ピボットデーブルを作成するときに [複数のテーブルを分析するかどうかを選択] で [このデータをデータモデルに追加する] にチェックを入れました。一体データモデルとは何なんでしょうか。

このブログで Excel 2007 以降のテーブル機能を紹介する発端となっているのは Office 365 との親和性でした。 http://road2cloudoffice.blogspot.jp/2014/10/excel-office-365.html

Office 365 の SharePoint Online や Access アプリ、クラウドや社内にあるサーバーのデータと、Excel との橋渡しをするのがテーブルです、と紹介しましたが、さらに正確にいうとテーブルとデータモデルによって連携できる、と言えます。

そもそも、このデータモデルという実装方法は Excel から発生したものではなく、マイクロソフト社のデータベースサーバーである SQL Server で Excel との連携のために開発された Excel アドインから来ています。

2007 Office リリース用 SQL Server 2005 データ マイニング アドイン
http://office.microsoft.com/ja-jp/excel-help/HA010225754.aspx

そのため、データモデルとなった「データの集まり」は Excel の機能を使うことができない場合もあります。

このリレーションシップ機能を使ったピボットテーブルとして実際に見ているデータはデータモデルから取り出しているものです。それは [データ] タブの [接続] の [ブックの接続] ダイアログから確認できます。ThisWorkbookDataModel はピボットテーブルの範囲を示しています。


そして、このデータモデルのリレーションシップを使ったピボットテーブルでの代表的な制限は以下です。
[追記] Excel 2016 からはこの制限はなくなっている。詳しくはこちらを参照のこと

・ グループ化ができない
・ 集計フィールド、集計アイテムを追加できない

グループ化はシリアル値である日付を月や年にまとめるためにピボットテーブルではよく使うでしょう。集計フィールドもデータに単価と数量があり、そこから金額を計算するときに使うことが多いです。

これらが使えないとピボットテーブルによる分析はできないも同然でしょう。しかし、そもそも「SQL Serer 2005 データ マイニング アドイン」から発展してきた実装方法です。このアドインはデータの分析のための機能です。

Excel が本来持っていた機能はデータモデルによりデータの持ち方が変わってしまったため利用できませんが、その代替は当然用意されています。

それがデータモデルに追加する「セット」であり、そこで利用する MDX 言語です。
(MDX : the MultiDimensional eXpression)

上図「ブックの接続ダイアログ」で ThisWorkbookDataModel を選択した状態で [セットの管理] ボタンを押せば、新しいセットの追加や MDX 編集画面がでます。

ただ、公開されている日本語の情報が非常に少ないのと、Excel 側からの観点よりも、SQL Server のアドイン(Analysis Services)側からの情報に偏ることが多い、さらに SQL 構文などのデータベースの知識を必要とするため、Excel ユーザーにとっては習得は厳しいかもしれない、というのが現状でしょう。

このような状況を打破し、Excel ユーザーにも直感的にわかりやすく、Excel の操作性に近い設定を提供するのが PowerPivot です。 PowerPivot の対象はクラウドやサーバーのデータだけでなく、Excel ブック内のデータモデル化したテーブルも対象にできます。つまり「テーブル」であれば、PowerPivot からの利用が可能なのです。

PowerPivot で日付グループ化、集計列追加でピボットテーブルを作った例

ただし、PowerPivot を使うには「エディション」に注意しなければなりません。
Personal エディションや Home & Business エディションでは PowerPivot を利用することができません。利用するには Office 2010 以上の Professional や Solo 、Office 365 Pro Plus が必要となります。

[追記] Power Pivot で日付のグループ化
http://road2cloudoffice.blogspot.jp/2014/11/powerpivot.html

テーブル関連の投稿
[テーブルとVLOOKUP] https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html
[テーブルと入力規則] https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html
[テーブルと集計行] https://road2cloudoffice.blogspot.jp/2014/10/blog-post_31.html
[テーブルと構造化参照] https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html
[テーブルとピボット] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html
[テーブルとVBA] https://road2cloudoffice.blogspot.jp/2014/11/blog-post_7.html

Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。