複数の Excel ブックの数値を集計する作業を自動化したい、という要望は昔からの鉄板ネタです。Excel VBAを使うことで実は比較的簡単に他のブックからデータを読み取ることは可能ですが、Office TANAKA/実践ワークシート協会 代表理事 田中曰く、「ユーザーがつまづく問題はそこじゃない」とのこと。
オブジェクトの階層構造を理解して、適切にブックや行・列、セルを指定できれば、画面がちらつくこともなく、複数ブックからデータを集約することが可能ですが（一方で、本来ちらついてはいけない画面のちらつきを防ぐという鉄板QAもある）、問題はフォルダー名やブック名で、その命名規則にブレがあったり、そのためファイルやフォルダーが見つからなかった場合のエラー処理の扱いが Excel VBA ユーザーを悩ませる大きな原因とのことです。
実は SharePoint Server の文書ライブラリ（ドキュメント ライブラリ）の機能のひとつとして、Office 文書のドキュメントプロパティと SharePoint のドキュメント ライブラリ列の連携というものが昔からあります。最新の Office 365 の SharePoint Online のドキュメント ライブラリでも同様です。
簡単にいうと、セルの情報（文字でも数値でも、日付でも）を SharePoint のドキュメントライブラリー列に表示して、ブックを開くことなしに、SharePoint 上で参照や集計ができる、という機能なのです。
Excel ユーザーにとっては 夢のような機能 だと思えますよね。
でも、いつものように、いくつか落とし穴があります。SharePoint Server / SharePoint Online をある程度勉強しないと理解するのが難しい仕組みがあるので、そのあたりも今回は紹介します。
SharePoint ドキュメント ライブラリの列
SharePoint のドキュメント ライブラリは Web ベースの共有フォルダーのようなものですが、そこにある「列」は、通常ファイルの「名前」や「更新日」、「更新者」くらいしか意識していないのが普通でしょう。
この列にユーザーが作成したカスタム列を追加して、Excel のブックにあるセルの内容を表示させよう、というのが、ドキュメントライブラリ列と Excel ブックの連携です。たとえば以下のようになります。
ブック/ワークシートの支店名と合計のデータ
ドキュメント ライブラリ列連携後のドキュメント ライブラリの表示
赤の部分は、ブックをドキュメント ライブラリに保存することで、ブックのセルのデータを参照して、ドキュメント ライブラリの列として表示しています。
もちろん、「数値」属性の列であればドキュメント ライブラリ内で合計も可能です。
この機能を使えば、ドキュメントライブラリに Excel ブックを保存したり、アップロードするだけで集計してくれるわけです。
Excel の観点からすれば「名前」と「内容にリンク プロパティ」がポイント
Excel 側での設定は、比較的シンプルなものです。
表示させたいデータが入っているセルに「名前」を付ける、ブックの詳細プロパティの [ユーザー設定] で名前にリンクしたプロパティを追加するだけなのです。
ブックのプロパティとしてワークシートのセルの内容を参照できようにして、SharePoint がブックを開かずにプロパティを参照し、その参照先がセルのデータである、という仕組みになります。
このように Excel 側の設定は非常にシンプルですが、SharePoint 側に設定はこれほどシンプルではありません。
この連携を可能にするためには SharePoint の「コンテンツ タイプ」を理解しないといけないのです。
コンテンツ タイプとは
コンテンツ タイプが難しいのは、コンテンツ タイプを使わなくても通常 SharePoint で文書共有や管理ができるからだと思います。必須の機能ではないことと、かなり概念的な部分もあるため、設計や運用が難しいと考えられます。
理想論的に考えると、企業や組織で扱う業務上必要な書類・ドキュメントは管理されなければなりません。そのため、どんなドキュメントが、何種類存在しているか、そのドキュメントはどんな項目を含んでいるか、といったことを包括的に管理するための「ドキュメントの単位と種類」を扱うために「コンテンツ タイプ」があると言えるでしょう。
マイクロソフトの開発者向けサイト MSDN のコンテンツ タイプの説明で以下のような文章があります。
「たとえば、業務で、経費明細書、発注書、および請求書という 3 種類のドキュメントを使用するとします。この 3 種類のドキュメントは共通の特性がいくつかあります。その 1 つは、これらのドキュメントはすべて財務関係のドキュメントで、通貨の値を使用したデータを含んでいます。ただし、各ドキュメントはそれぞれ独自のデータ要件、ドキュメント テンプレート、およびワークフローがあります。このビジネス問題に対する解決策の 1 つは、4 つのコンテンツ タイプを作成することです。 1 つ目のコンテンツ タイプ財務関係のドキュメントでは、組織内のすべての財務関係のドキュメントに共通するデータ要件をカプセル化します。残りの 3 つのコンテンツ タイプ経費明細書、発注書、請求書では、財務関係のドキュメントから共通の要素を継承します、、、、」
(MSDN Office デベロッパー センター SharePoint リスト データ モデル － コンテンツ タイプより引用）
コンテンツ マネジメントの書籍などを読むと、まるで図書館司書のような役割を持った人が組織内の「ドキュメントとデータの種類」を管理してコンテンツ タイプとしてまとめていくようなことが紹介されています。実際、そのような事例として SharePoint のサイト コンテンツ タイプ（そのサイトで使うコンテンツ タイプ＝コンテンツの種類）の「ドキュメント コンテンツ タイプ」にある Dublin Core 列（ほとんど使う状況にはならないはずです）では 17 のデータ要素（列）を定義していますが、その定義は WWW(ウェブ)上のリソースに関する情報を定義するために作られた ISO 15836 といった国際標準規格です。（Wikipedia – Dublin Core)
と、難しい話になりましたが、要は、ドキュメント ライブラリの列として、Excel ブックのプロパティを連携させるには、利用する Excel ブックというコンテンツの種類（コンテンツ タイプ）を作成して、そのコンテンツ タイプ(ブック)にはどんなデータ要素(プロパティ)が含まれるかを定義して、はじめて利用可能になる、と言えます。
Excel 連携用のコンテンツ タイプを登録(作成)する
ドキュメントの「種類」を登録すると理解して、「Excel 各支店XXXレポート」といったコンテンツ タイプを SharePoint に作成します。
Excel ブックを保存したいドキュメント ライブラリを作る予定のサイトに移動して、画面右上の歯車アイコンから [サイトの設定] を選び、サイトの設定を表示します。
[Web デザイナー ギャラリー] の [サイト コンテンツ タイプ] を選択します。
[サイトの設定 > サイト コンテンツ タイプ] の [作成] をクリックします。
ここで行う作業は実はあまりありません。変数の宣言のようなもので、「使いますよ」と言っているだけです。
[親コンテンツ タイプ] の指定は、Excel ブックなので [ドキュメント（文書）] だと考えて、ドキュメントのものを使ったほうがいいでしょう。
コンテンツ タイプを作成すると、詳細について設定する画面に移ります。
コンテンツ タイプは、上の MSDN の例でいえば個別の発注書を作るときに参照する「テンプレート」のようなものです。この考え方は Excel にも当てはまり、たとえば、Excel のブックで名前とユーザー定義のカスタム プロパティの設定をしていないブックを保存したところでなにも起こりません。このコンテンツ タイプと関連付けられている「テンプレート」を登録することで、SharePoint のサイトでそのテンプレートを使った個別のドキュメントが SharePoint で作成できるようになります。（この作成手順は後で紹介します）
その指定を [設定] の [詳細設定] ですることができます。[詳細設定] をクリックすると [ドキュメントのテンプレート] を指定する項目があります。予め名前やカスタム プロパティの設定を行ったブックを xltx 拡張子でテンプレートとして保存しておき、ここで指定することができます。
これでサイトへのサイト コンテンツ タイプの設定は終わりました。この設定により、この例では [Excel 各支店レポート] コンテンツ タイプを指定することで、[各支店レポート.xltx] テンプレートを使って Excel のブックを作成できるようになりました。
次は、この Excel ブックを扱うドキュメント ライブラリの設定になります。
ドキュメント ライブラリで Excel のプロパティ～列連携の設定する
Excel ブックを保存するドキュメント ライブラリに列を追加して、Excel のプロパティを表示させるように設定します。
そのために、まずこのドキュメント ライブラリでコンテンツ タイプを使えるようにします。
ドキュメント ライブラリの画面でリボンの [ライブラリ] タブにある [ライブラリの設定] を選び、設定画面を表示し、[全般設定] の [詳細設定] をクリックします。
[設定 > 詳細設定] の画面で、[コンテンツ タイプ] で管理を許可するで [はい] を選びます。
この時、[ブラウザーで開くドキュメント] の既定の表示方法は [クライアント アプリケーションで開く] にしておきます。
セルにリンクされたプロパティを持つ Excel ブックは Excel Online で編集できないからです。
次に列の追加をします。
この連携は単純に Excel のプロパティ名と、SharePoint の列名の「文字列比較」しかやっていません。タイプミスに注意してください。
ドキュメント ライブラリの設定の [列の作成] を選びます。
Excel のプロパティ名と同じ名前の SharePoint の列を追加します。
事前に作成した Excel のプロパティは以下です。
- 支店名 テキスト
- 合計金額 数値
- 計上月 テキスト
と同じ名前、同じ属性で列を追加します。途中の Conten...はコンテンツ タイプのテンプレートとしてアップしたブックに自動的に追加されるものです。
[列の作成] の [列名] や [この列の情報の種類] を Excel ブックのプロパティに合わせてそれぞれ作成します。
この時、[すべてのコンテンツ タイプに追加] のチェックを必ずはずしてください。これをはずさないと、変更・保存したときにセルの新しい内容が列に反映されません。
列の追加後
まだ終わりではありません。
最後にサイトのコンテンツ タイプ登録で指定した xltx テンプレートを使った Excel ブック作成をこのドキュメント ライブラリでできるように指定します。
ドキュメント ライブラリの設定の [コンテンツ タイプ] の [既定のサイト コンテンツ タイプから追加] を選び、このドキュメント ライブラリで使用するコンテンツ タイプを追加します。
これで基本的な設定が終わりました。
それではドキュメント ライブラリの画面を見てみましょう。
支店名、計上月、合計金額が「列」として追加されているのがわかります。
[新しいドキュメント] をクリックすると、コンテンツ タイプのテンプレートとして登録した xltx が表示されます。
この [Excel 各支店レポート] をクリックすると、Excel が立ちあがり、Excel各支店レポート.xltx を使ってブックが作成・表示されます。
ブックを保存するときは、[名前を付けて保存] を選び、[現在のフォルダー] であるドキュメント ライブブラリを指定します。
保存するときの名前は一意の名前とします。命名規則などを工夫して一意になるようにしてください。
ブックが保存され、ドキュメント ライブラリを見るとテンプレートから作成されたブックのプロパティの値が列に表示されていることが確認できます。変更がないようだったら F5 でブラウザをリフレッシュしてください。
一度、ドキュメント ライブラリに登録・保存したブックを再度開いて編集、上書き保存しても、その変更内容は SharePoint の列に反映されます。
以下では 20150001.xlsx をドキュメント ライブラリから開き、Excel で編集し、上書き保存した結果です。
なお、集計などを追加するには列の [ビュー設定] から行います。
SharePoint のドキュメント ライブラリやリストは「ビュー」という機能を持っています。
合計を表示するビュー、支店ごとにグループ化したビュー、計上月ごとにグループしたビューを作成することで、報告書ブックを保存する「だけ」だったドキュメント ライブラリがちょっとしたダッシュボード（現状を確認するための情報提供画面）に変わります。それも VBA を使わずに複数ブックの集計を自動的に、リアルタイムにしてくれます。
既定の列の並び順などもビューで変更が可能です。
サンプルデータを入れて、いくつかのビューを作成したものが以下になります。
既定の[すべてのドキュメント]ビューに合計をいれたビュー
月別にグループ化したビュー
支店別にグループ化したビュー
Excel エクスポートで集計ブックを作成
ここまで来て、「いや、SharePoint で確認できるメリットはわかったが、複数ブックから必要なデータを集計する「ブック」が欲しい」という人もいるかもしれません。
こうやって SharePoint 側にデータがあれば、[Excel にエクスポート] を使えば、列の情報を「テーブル」として Excel に取り込むことが可能になります。
この機能により、SharePoint の列データをテーブルとして取り込んだ結果が以下になります。
良いことづくめのようですが、ちょっとした落とし穴もあります。最後はそれを紹介します。
日付でグループ化したいがドキュメント ライブラリで年月にならない
Excel で日付(シリアル値）のセルを表示形式で「2015/01」のようにしても、SharePoint の列には表示形式で設定した結果ではなくシリアル値もしくはシステム設定の日付表記の文字列が入ります。
よって、年月などでグループ化したい場合は、2つの方法があります。
1) Excel 側で TEXT 関数を使ってシリアル値を YYYY/MM などの文字列にしておく。もちろん、プロパティや列の属性は「文字」「テキスト」です。
2) Excel 側ではなにもせず、SharePoint 側で列属性を日付で取り込み、集計列を追加し、TEXT 関数を使って YYYY/MM を取り出し、集計列でグループ化する。
SharePoint の日付の扱いは「シリアル値」です。集計列の数式では Excel と同じような関数が使えます。
参照 Excel ユーザーのための SharePoint リスト「集計値」列
ドキュメント ライブラリにある Excel ブックを開いて更新、上書きしてもドキュメント ライブラリの列の値が変化しない
セルの値を変更するとブックのユーザー定義のプロパティは更新されているのに、その変更が SharePoint の列に反映されない場合があります。
これは SharePoint の列の作成の際に [すべてのコンテンツ タイプに追加] のチェックボックスにチェックが入ったまま列を作成したためです。このチェックがオンになっていると、SharePoint 側に登録しているコンテンツ タイプ（ドキュメント プロパティ サーバー として Excel でもドキュメント情報パネルで表示・編集できる列）が優先されるため、Excel ブックのユーザー定義のリンク プロパティの変更が SharePoint 側で反映されないためです。
Excel ブックの変更をドキュメント ライブラリの列に反映させたい場合は、[すべてのコンテンツ タイプに追加] をオフにして列を作成します。
Excel Online で Excel ブックの編集ができない
メッセージの通り、これは仕様です。ローカルの Excel で編集するしか今は無いようです。
サイト コンテンツ タイプのテンプレートは使いたくないが、集計やグループ化はしたい
ユーザー設定のリンク プロパティが設定されたブックをそれぞれが持ち、指定された SharePoint のドキュメント ライブラリに保存、列で集計するだけでよい、という要望もあるでしょう。
その場合は、上記手順の「Excel連携用のコンテンツ タイプを登録(作成)する」を飛ばし、「ドキュメント ライブラリで Excel のプロパティ～列連携の設定する」から始めることで可能です。その時、一番最後の「既定のサイト コンテンツ タイプから追加」をやる必要はありません。
これで、列だけを追加し、ブックをアップロード、ドラッグ＆ドロップすることで同様の結果を得ることができます。
ただし、サイト コンテンツ タイプは最初はとっつきにくいですが、多くのメリットがあります。ひとつは「テンプレート」です。細かな修正が入る度にテンプレート ブックの再配布や、共有フォルダーからのダウンロードをお知らせしていませんか？サイト コンテンツ タイプで登録した xltx ファイルは直接修正・保存することが可能です。これにより、大元のテンプレートを修正するだけで、次から作成されるブックはそのテンプレートを参照するのです。これは大きなメリットです。
かなり長いブログの投稿になってしまいましたが、最後の最後、この連携は SharePoint のスキルの有無ではなく Excel のワークシートをどうつくるか、という知識やテクニックの問題になります。
Excel 側で「入力・計算・出力」といったデータの流れ、処理の流れを意識したワークシートを作っていれば、比較的 SharePoint 連携の Excel 側の作業は簡単でかつシンプルなものでしょう。出力の役割のセルに「名前」を付け、プロパティ登録するだけです。SharePoint 側の設定は簡単ではありませんが、その仕方さえ覚えてしまえば、それを繰り返すだけです。方や Excel のワークシート設計は業務によって変更せざるを得ません。
よって、Excel ユーザーの培ってきた知識、スキル、テクニックは Office 365 / SharePoint Online でも十分使えるどころか必須だと考えています。
加えて、ドキュメント ライブラリ～Excel 連携は、コンテンツ タイプの考え方がないと迷うことが多くなります。
ドキュメント ライブラリへのコンテンツ タイプの登録は複数可能です。どの種類のブックをひとつのドキュメント ライブラリにまとめるか、どのデータを列として表示させるか、といった「設計」はコンテンツ タイプの考え方なしではかなり難しいでしょう。
この投稿が少しでもお役に立てば幸いです。
参照 マイクロソフトのブログより
2006年2月 Excel のセル内容をドキュメントライブラリに表示する
(MSDN Blog - GroupBoard Blog 情報が古いが今でもほぼ同じ内容)
http://blogs.msdn.com/b/groupboard_blog/archive/2006/02/24/538561.aspx
2014年6月Excel 2013 セルと SharePoint ドキュメント ライブラリ列の連携方法
(TechNet Blog - Japan SharePoint Support Team Blog)
http://blogs.technet.com/b/sharepoint_support/archive/2014/06/27/excel-2013-sharepoint.aspx
2014年7月 Excel のドキュメント プロパティと SharePoint ドキュメント ライブラリ列の連携方法
(TechNet Blog - Japan SharePoint Support Team Blog)
http://blogs.technet.com/b/sharepoint_support/archive/2014/08/01/excel-sharepoint.aspx
MSDN より
2010年11月1日 コンテンツ タイプについて
https://msdn.microsoft.com/ja-jp/library/office/ms472236%28v=office.14%29.aspx
