Power BI Desktop と Excel の Power BI アドイン

ここにきて Power BI Desktop (旧 Power BI Designer）と、これまでこのブログでご紹介してきた Excel の Power BI アドイン（Power Query や Power Pivot）ってどう違うの？という話題が私の周りで聞くようになりました。また、Power BI と Power BI Desktop と Power BI for Office 365 など “Power BI” という単語が付いている製品・サービスの違いについても、新しい機能メインの単独紹介記事ばかりが出てくるため、関連や違いがわからない、という話も私の周りで聞きます。

これ、わかりづらいですよね。

まず、ちょっと交通整理するために、以下の単語を使って区分けしてみます。

1. Power BI Excel アドイン
2. Power BI Desktop  
3. Power BI
4. Power BI for Office 365

1. Power BI Excel アドイン

これまでこのブログで紹介してきた、単に Power Pivot などと言っていたものですが、現状では例えば Power Pivot は「the Power Pivot in Microsoft Excel add-ins」（長い。。。）と呼ばれるようになっています。

https://support.office.com/en-us/article/Start-the-Power-Pivot-in-Microsoft-Excel-add-in-a891a66d-36e3-43fc-81e8-fc4798f39ea8?ui=en-US&rs=en-US&ad=US

これまで Power BI Excel アドインとして以下をこのブログでも扱ってきましたし、私自身も Power Query や Power Pivot は業務でよく使っています。

• Power Query
• Power Pivot
• Power View
• Power Map

Power Query は Excel 2013 まではダウンロードセンターからダウンロードするアドインでしたが、Excel 2016 からは内部に組み込まれ、あらゆるエディションで利用可能となります。（一部制限あり）
このあたりのお話は以下のブログでも紹介しています。

Power Query が Excel 全エディションで利用可能になりました
Excel ユーザーのための Power BI 系インストールのお話

現実の話としては全エディションで利用可能になった Power Query 以外は、Professional Plus か Office 365 ProPlus 前提となるため、Excel アドインといえども対象となるユーザーは限定されます。

Power Query や Power Pivot は Excel をより使いやすく、リッチな機能にするアドイン、という認識ですが、以降の Power BI を見ると、実は本当に重要なアドインは Power View です。 Power Query や Power Pivot は Power View (または Power Map) による分析レポートを作成するための補助ツール、という捉え方です。

ただ通常は、Excel のワークシートでレポートは完結してしまう場合が多いので、Power View を活用している人はあまり多くないと思います。私自身も Power View までを使って分析レポートを作ることはほぼありませんでした。

しかし、Power View が目指すところは単なる綺麗な「レポート」ではないようです。レポートの共有を印刷による「紙」ではなく、たとえば、タブレットやスマホを使って、いつでも、どこでも共有可能にする、という使い方です。分析結果やキーとなる数字の共有、それもある程度定期的・自動的に更新されるデータの共有をする、それが以降の Power BI が目指すもののようです。

なお、Excel 2016 Preview (16.0.4229.1011) で、この Power View は [挿入] タブにありません。

Excel 2013 のリボン コマンド インターフェース にある パワー ビュー

パワー ビュー のリボン コマンドがない Excel 2016 (16.0.4229.1011) 2015年8月

[リボンのユーザー設定] をみると タブ レベルで [Power View] があるのですが、そのタブ自体が表示されていません。Excel のデータ分析のオプションをいぢっても変わらずです。
どうやら [挿入] タブから独立して [Power View] タブにしたものの、肝心の [Power View] タブが表示されていない、という現象のようです。

調べてみると、マイクロソフトではこれを Known Issues のバグとして認識しており、そのうち直す、としています。

なお、Excel 2016 Preview で Power View を手動で [リボンのユーザー設定] で追加することは可能です。コマンドの選択で [リボンにないコマンド] で、[Power View レポートの挿入] をユーザー設定グループに追加してください。

2. Power BI Desktop (旧 Power BI Designer)

GA(一般利用）にともない、Power BI Designer は Power BI Desktop に名前が変更されました。
Power BI Desktop の特徴は「Excel を必要としない Power View シート(的なもの)作成ツール」の1点につきます。なおかつ、Power BI Excel アドインを使っていたユーザーであれば、ほぼ同じ操作、考え方で Excel の Power View シートに相当する「レポート」を作成することが可能です。そして、この「レポート」を他のユーザーと共有するためのクラウドサービスが「Power BI」(以下の 3. Power BI)になります。

Power BI Excel アドインで慣れている人であれば、ほぼ、取説なしで利用可能なほど、操作性は同じです。
(以下は Power BI に Office 365 の組織アカウントを使って無料トライアルをサインイン済み、という状況を前提としています。）

[データを取得]は Power Query に相当します。

データ取得先が SharePoint Online であれば、 OData フィードを使います。 URL の指定の仕方などは Power Query と全く一緒です。

組織アカウントを使って、SharePoint Online サイトにサインインします。

ナビゲーターも Power Query のものと一緒です。

[編集] ボタンで Power Query と同じ クエリ エディター が立ち上がります。

[閉じて読み込む] で、ワークシートにデータを展開するのではなく、データ エリアにレコードを読み込みます。

左側にある [リレーション] タブは、Power Pivot のダイアグラムビューに相当します。

左側の [レポート] タブが Power View シートに相当します。ここで、キーとなる数値の表示や、データの推移や割合を表すグラフを使い、レポートを作成します。

これらは pbix 拡張子のファイルとしてローカルに保存できます。
Power View もそうですし、この Power BI Desktop による「レポート」も、対話型でデータの確認・分析ができることがメリットと言われています。

Power BI Desktop のもうひとつ重要な機能は、この pbix ファイルをサーバーにアップロードして、他のメンバーと共有できる点です。

あらかじめ Power BI の無料トライアルにサインアップしていれば、[発行] を押すと Power BI サイトへのサインインが求められます。

発行が成功すると、発行したレポートへのリンクが表示されます。

リンクをクリックすると、app.powerbi.com の自分の Web サイトでアップロードしたレポートを確認することができます。

3. Power BI

きっと、この Power BI というサービスが、これまで Excel と Office 365/SharePoint Online を使っていたユーザーにとって一番わかりづらいと思います。

イメージ的には、Office 365 とは違う、別のサーバー(クラウド サービス)が立っています。ただし、Azure AD でアカウントの管理を行っているので、Office 365 の組織アカウントでサインアップすると、Office 365 の一部のような形でアプリランチャーに登録されます。

ちゃっかり Office 365 管理センターの [ライセンス] にも登録されています。

実践ワークシート協会では「Power BI for Office 365」を購入していたのですが、アプリランチャーのアイコンは Power BI に置き換わりました。
なお、Power BI サイトから、従来の Power BI for Office 365 にいくには、歯車アイコンから Power BI for Office 365 を選択することが可能です。

無料のサービスでは 1GB の powerbi.com のストレージが利用可能です。このストレージに対して、Power BI Desktop で作成した pbix ファイルをアップロードするのはもちろんですが、Excel の Power View で作成したレポートを含む Excel ブックもアップロードすることが可能です。Power BI サイトの大まかな使い方は以下と言えるでしょう。

レポート共有のためのアップロード先

• Power BI Desktop で各サーバー、サービスのデータを取り込み、レポートを作成し、pbix ファイルを powerbi.com にアップロードする
• Excel で各サーバー、サービスのデータを Power Query のモデル OLE DB接続で取り込み、Power View シートを作成、xlsx ファイルを powerbi.com にアップロードする

レポート共有のためのダッシュボードの作成

• アップロードした pbix や xlsx のレポートから必要なデータを「ダッシュボード」に貼り付け、ダッシュボードの共有先を指定する（招待する）

レポートが使用しているデータソース（データセット）のアクセスと更新頻度の管理

• データ接続の即時更新
• 更新頻度の設定
• データソースへの認証情報の保存

他のサービスや共有可能なコンテンツの管理と参照

• ダッシュボード、レポート、データセットの組み合わせでコンテンツ パックを作成し、組織内で共有をする
• 同じ組織の他の人が公開しているコンテンツ パックの参照
• Salesforce や Google Analytics など他のサービスのコンテンツ パックの参照と共有（Power BI Desktop や Excel を使わない）

ダッシュボード共有先の管理

• 共有したダッシュボードが誰と共有されているかを管理する
• ダッシュボードを共有するための「グループ」を参照、編集、新規作成できる
• この「グループ」は、Outlook で作成し、ファイルやスレッド(会話)を指定したメンバーと共有する、あの「グループ」機能と同じ
• ダッシュボードに登録することで、iPhone などの Power BI アプリからダッシュボードを参照できるようになります

iPhone の Power BI アプリでダッシュボードを表示

なお、無償サービスはストレージが 1GB まで、といった制限がありますが、10GB までに拡張した Power BI Pro という有償サービスのトライアルもあります。

実はこれがやっかいというか、意識せずして Power BI Pro のトライアルに移行してしまっている場合があります。私もそれになってました。。。
Power BI Pro トライアルへの申込みページはありません。無償のサービスを使っていて、有償の機能を使った時点で自動的に有償サービスの Power BI Pro のトライアルとなるようです。（私の記憶では、有償のみの設定をして、Pro のトライアルを使うかどうかの確認をされた記憶はないのですが。。。）
自分が Power BI Pro のトライアルになっているかどうかは、Power BI サイトの歯車アイコンのストレージをみるといいでしょう。パーソナル ストレージの管理で 10GB になっていれば、 Power BI Pro のトライアルになっています。

でも、10GB なんて使わないのに、どうして Power BI Pro のトライアルに移行するのか、、、謎でした。

たぶん、多くの人が「やってしまう」Power BI Pro の機能はデータセットの「更新スケジュールの設定」でしょう。

この設定は1日に1回までであれば無償サービスの範囲ですが、1日に2回以上の更新を設定すれば、それは Power BI Pro でなければならないために Power BI Pro トライアルに移行しているようです。
[追記] 知らず知らずにトライアルに移行することはないようです。現在検証中です。私の場合状況から Power BI for Office 365 のライセンス割り当てがすでにされていることが関係しそうです。

Power BI 無償と有償の違い
https://powerbi.microsoft.com/pricing

Power BI Pro の Free Trial について
https://support.powerbi.com/knowledgebase/articles/664495

Free Trial は 60日間のようですが、60日たってどうなるか、、、までは上記のページから探すことはできませんでした。

4. Power BI for Office 365

Power BI for Office 365 も、URL から判断すると sites.powerbi.com で提供しているサービスで、このサービスを適用した SharePoint Online サイトのドキュメント ライブラリーにある Excel ブックを更新、Excel Online で表示したり、Power View を Online で操作することを目的としたものです。他に Data Management Gateway 機能で　SharePoint の BCS (Business Connectivity Services) のように Office 365 以外のオンプレミスの SQL Server のデータなどを Office 365 経由で扱えるようにしたり、個人で作成した Power Query のクエリを他のメンバーと共有することができるようになります。

ただ、Power BI for Office 365 は、正直なところ実践ワークシート協会の Office 365 / SharePoint Online の使い方から活用しきれませんでした。

もっとも大きな理由は、SharePoint Online のリスト アイテムと Excel ブックのデータ接続の更新ができないことでした。

https://social.technet.microsoft.com/Forums/en-US/332b421c-63aa-4040-a856-db65d543c2b7/power-bi-refresh-from-sharepoint-online-list-data-source?forum=powerbiforoffice365

本来であれば、Excel で SharePoint Online のリストからデータを取得し、キーとなる数値データを Power View シートで表示し、そのブックを SharePoint Online のドキュメント ライブラリーに保存、 Power BI for Office 365 の BI サイトでブックの定期更新をして、Excel Online で表示したかったのです。ところが、データ接続を使った SharePoint Online のリスト データ更新ができないため、一旦 PC 側の Excel でブックを開き、更新後にアップロードという処理になり、これであれば、すでに Office データ接続と Excel Web Access/Excel Services で実現している方法よりも「使い勝手が悪い」となりました。（念のため、これは SharePoint Online のリストをデータソースにしているためで、SQL Server やオンプレミスの SharePoint Server であれば問題ないようです）

Power BI for Office 365 そのものは、Data Management Gateway による Office 365 とオンプレミスの SharePoint Server や SQL Server の統合機能もあり、また、Power Query のクエリの共有機能もあるため、使える機能も多くあると思います。しかし、Office 365 のクラウド セントリックな業務形態の実践ワークシート協会では、その良さを活かす場面がなかなかなく、逆にデータ接続における SharePoint Online との親和性の未熟さがネックとなってしまった、ということでしょう。

あと、現状 Power BI for Office 365 の Power BI サイトを開くと以下のようになります。

画面上部の「最新版の Power BI はこちらです。今すぐ試す」の表示から考えると、Power BI for Office 365 よりも、Power BI に集約させたい、というメッセージかもしれませんね。

Power BI にサインアップすることで、Office 365 管理センターから Power BI がなくなりました。しかし、https://admin.powerbi.com にアクセスすることで、従来の Power BI 管理センターにアクセスができました。または、SharePoint サイトに作った Power BI Site トップで歯車アイコンから Power BI Admin Center を選んで Power BI 管理センターに移動できます。

Power BI for Office 365 を使いたかった理由はブックの自動更新 (scheduled data refresh) でした。現状、3. Power BI でそれが可能です。それも SharePoint Online のリストがデータ ソースであってもです。1日に1回の更新であれば、無償で十分であり、そもそも Power BI for Office 365 の管理センターでは1日に1回の更新しか指定できませんでした。このようなブックの自動更新の目的であれば、無償の Power BI で十分だと思います。

Power BI for Office 365 でブック毎に設定できる更新スケジュールの構成。頻度は1日1回か、週に1回となる。

 

今回、Power BI と Power BI Desktop を使い込むにあたり、これまで実現できていなかった機能を確認することができました。
Excel ブックに含まれているデータ接続の Excel Online での更新については、すでに Office データ接続を使って、SharePoint サイトにアプリ権限付与をすることで可能でしたが、Power BI を使うことで Power Query による SharePoint Online リストデータ接続を含む Excel ブックを Excel Online で更新可能に設定することができます。

次回はその使い勝手と設定をご紹介したいと思います。

Read More

SharePoint ドキュメントライブラリーの Excel ブックが開けない

[ご注意] 結論はそれかよ！という内容なのであまり期待しないでください。とはいえ1日悩みました。。。

ある日突然 SharePoint Online のドキュメントライブラリーに保存している Excel ブックを開くことができなくなりました。

現象としては

• Excel Online では開ける、ただし、[ブックの編集] – [Excel で編集] を選択すると開けない。
• ブックのダウンロードはできる、ダウンロードしたブックを開くことはできる。
• [ファイルを開く] ダイアログからドキュメントライブラリーの URL を指定して、Excel ブックを選択しても開けない。（ライブラリーにブックがあることは確認できる）

開けないときのダイアログのメッセージは以下。

手がかりないですよね、このダイアログだと。。。

そもそも Excel Online で参照・編集できますし、ダウンロードも可能ということからアクセス権の問題ではなさそうです。というか、今まで使っていましたし、ブックの作成者は私です。
ダウンロードしたブックを開くことはできるので、ブックが壊れていることもなさそうです。

なんと、そのサイトのみならず、他のサイトの他の Excel ブックも Excel から直接開くことができなくなっていました。

さらに、いろいろ試してみると、追加の現象として以下であることがわかりました。

• 新規ブックを作成、ローカルに一度保存して、SharePoint のドキュメントライブラリーにアップロードは可能。ただし、アップロードしたブックを直接 Excel で開くことはできない
• 新規ブックを作成し、直接 SharePoint のドキュメントライブラリーに保存しようとすると、「ファイルを保存できませんでした。」とメッセージがでて保存できない。

つまり、Excel から直接 SharePoint 上にあるブックを使うことができないと想定されます。

使用している Excel のバージョンは以下。

Microsoft Office Professional Plus 2013
Microsoft Excel 2013 (15.0.4737.1000) MSO(15.0.4737,1001) 32 ビット

もし、これがバグとすれば大問題になっているような内容です。検索してみると以下がヒットしましたが、エラーの内容やビルド番号から今利用しているものは問題がなさそうです。

SharePoint Online または SharePoint Server で Excel ファイルを開こうとするとエラーが発生する(KB2919156)
https://support.microsoft.com/ja-jp/kb/2919156

ここ数日で、なんらかの Microsoft Update はあったかもしれませんが、Windows や Office のバージョンを変えるようなことはしていません。

こういうときは90年代から「再起動せよ」が鉄則です。（笑　そこですべてのアプリケーションを閉じ、Windows　の再起動をしたところ、問題なく Excel から SharePoint のブックを開くことができるようになりました。

未だにこの方法が有効だったとは。。。

使用している Windows は 8.1 の 64bit版、Office は 32bit版、PCのメモリーは 12GB です。
再起動、、、してみるのも手かもしれません。

Read More

シート別にデータを分ける – VBA/マクロとピボットテーブルの活用

実践ワークシート協会の通常業務では、なるべく「元データ」は Office 365 の SharePoint か、SharePoint Access アプリを経由して SQL Azure に入力して、クラウド上でまとめるようにしています。

生のデータがクラウド上にあれば、あとは Excel のデータ接続を使って生データをワークシートに取り込み、自分の欲しい形に加工すればよいわけです。おおよそオートフィルターかピボットテーブルで目的は達成でき、ちょっと複雑（複数テーブルを使う、列を入れ替える、削除するなど）になれば、Power Query や Power Pivot を使います。

(Office 365 SharePoint Online のリストアイテムの取り込みは OData フィードを使うと幸せになれることが多い。くわしくはこちらを参照)

定型の業務であれば、一度つくったピボットテーブルや表を Excel Services の Excel Web Access を使って SharePoint Online のポータルに貼り付けておけば、ローカル(PC)で Excel を開くことなく状況の確認が可能で、協会のメンバーと情報を共有できます。

Excel ユーザーのための Excel Services – Office 365

強いて言えば、協会のメンバーは全員が Excel を使いこなすことができるので、準備したデータを「あ～したい」、「こ～したい」、と思ったら自分で加工できるのが大きな強みでしょう。

このように協会ではピボットテーブルやオートフィルターを活用・多用するので、最新データは「データ更新」ボタンを押してクラウド上のデータを Excel で確認するか、条件が変われば自分でピボットテーブルやオートフィルターを変更するわけですが、一般的にはそうではなく、日次や月次などで「決まった形」でワークシートを用意する、印刷して提出しなければならないケースを聞くことが多いです。（繰り返しになりますが、協会内部の業務ではこのようなケースはありません。）

例えば、支店別の売り上げとか、製品別の売り上げとか、年月での売り上げでまとめたワークシート。[東京支店][大阪支店][名古屋支店] といったワークシートが並んでいたり、[2015年4月][2015年5月][2015年6月] というようなワークシートが並んでいるブックです。

比較・確認する数字などは定例の会議で確認する項目などはほとんど決まっているのでピボットテーブルを駆使した方が最新データへの更新も考えると楽なのに、、、と思うこともしばしばありますが、もうそうやって何年も十何年もその資料を使っていると、なかなか変更するのも大変です。（変更したほうがいいんですけどね）

だったら、その作り方で楽をしよう、というのが今回のテーマです。Excel VBA と ピボットテーブル の話になります。

グループ化と明細の表示を使う

Excel のピボットテーブルには「明細の表示」という機能があります。通常はピボットテーブルによる集計済みの項目の集計元データを、別のワークシートに作成し内容を確認するためのものです。

たとえば、日付項目を「年・月」でグループ化したピボットテーブルを作り、年月の集計値をダブルクリックすると、その年のその月のデータだけが「明細の表示」としてワークシートが追加されます。

クラウドやサーバーからデータを Excel に取り込み、年・月のシートに分割する、というような定型の業務がある場合は、オートフィルターを使ってコピーする方法より、このピボットテーブルと明細の表示を使うほうがあるかに手間がかかりません。そして、この手作業を VBA で自動化すれば、一瞬で年月ワークシートを作成することができます。元データは SharePoint や SQL Azure といったサーバーから取ってきますので、その時点での最新データから年月ワークシートを作成することが可能です。

グループ化は日付だけではなく、支店名や、製品グループなどの文字列データでも有効です。ピボットテーブルのグループ化の機能が強力なのは、あらかじめどんなユニークデータがあるかを知っている必要がないことです。この性質を利用して「重複データの削除」のような使い方も可能です。

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

マクロ / VBA でピボットテーブルを作る

実践ワークシート協会代表理事/Office TANAKA の中の人 田中亨曰く、 マクロ記録で恐ろしい(難解な)結果が出る代表格の１つであるピボットテーブルの作成ですが、実は、非常にシンプルなコードでまとめることができます。このあたりは田中亨著の「Excel VBA 逆引き辞典」にシンプルなサンプルコードが紹介されています。

1回かぎりであれば手作業で十分ですが、毎回毎回同じことを繰り返す、作成するシートの数が多いようであれば、VBA を用意してそれを実行するだけで、ある条件でワークシートを作成することを自動化可能になります。

手順としては大きく4つです。

1. 元の表(テーブル)から条件を設定してピボットキャッシュ、ピボットテーブルを作成する
2. ピボットテーブルから詳細の表示を使って詳細ワークシートを作成
3. 作成したワークシートにあるテーブルを並べ替え
4. 作成したシートの名前を変更

それぞれの手順を見てみましょう。

例として以下のようなテーブルから年月別のワークシートを複数作成します。(サンプルは 10,000件）

1. 元のテーブルから条件を設定してピボットテーブルを作成する

Sub CreatePivotTable()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする　ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'Dからピボットキャッシュとテーブルの作成
   With nWS.PivotTables(1)  'ピボットテーブルのフィールドリスト操作を以下で行う
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。マクロ記録で調べるとよい。
End Sub

実行結果は以下のようになります。

ピボットテーブルから年月シートを VBA/マクロで作成する

ピボットテーブルができてしまえば、あとは [詳細の表示] に相当するコードでワークシートを追加していきます。大まかな手順は以下になります。

2. ピボットテーブルから[詳細の表示]を使って、各月のワークシートを作成する

3. 作成したワークシートを日付でソートする

4. シートの名前を年月にする

5. 最後にピボットテーブルのシートを消す

ここは一気にいきます。

上記ピボットテーブルを作成したソースに 2, 3, 4, 5 を実現するコードを加えたものが以下です。

Sub CreateMonthlySheets()
   'エラー処理なし。元のデータしかない、状態からのマクロ
   Dim cWS As Worksheet
   Dim nWS As Worksheet
   Set cWS = ActiveSheet  '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
   Set nWS = Sheets.Add   'シートを追加して、nWS とする　ActiveSheet は nWS
   nWS.Name = "tmp"       '追加シート nWS の名前を tmp に変更
   Dim D As Range
   Set D = cWS.Range("A1").CurrentRegion  '元のシートの A1 を含むデータの固まりを D とする
   ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1")  'ピボットキャッシュとnWSシートにピボットテーブルを作成
   With nWS.PivotTables(1) 'ピボットテーブルのフィールドリスト操作
       .PivotFields("日付").Orientation = xlRowField  '日付を行に設定
       .PivotFields("金額").Orientation = xlDataField  '金額を値に設定。各年月シート作成のためのフィールドで「金額」そのものには意味はない。
       .ColumnGrand = False  '集計行の総計を非表示
   End With
   Range("A3").Group Periods:=Array(False, False, False, False, True, False, True)  '日付行を年・月でグループ化する。設定はマクロ記録で調べるとよい。
   Set D = nWS.PivotTables(1).DataBodyRange  'ピボットテーブルの値の範囲（金額集計）を D とする
   Dim Item As Range
   For Each Item In D  '金額集計の範囲の数だけ For Each を回す。個々のセル(Range)は Item に入る
       Item.ShowDetail = True　　'詳細の表示を行う。このとき追加されたシートがアクティブシートになる
       With ActiveSheet.Sort.SortFields　'追加されたシート(アクティブシート）で日付(A2)のソート条件を設定する
            .Clear  'おまじない
            'A2の日付の値で、昇順(xlAscending)、標準(xlSortNormal) でソートする条件を設定する
            .Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
       End With
       With ActiveSheet.Sort　'アクティブシートの Sort オブジェクトでソートを実行する
            .SetRange Range("A1").CurrentRegion　'A1を含むデータのかたまり（範囲）を対象とし
            .Header = xlYes　'一番上の行はヘッダーとして
            .Orientation = xlTopToBottom　'上から下に向かって
            .Apply　'ソートを実行
       End With
       'セル A2 の日付から年月の文字列を作成し、シートの名前にする
       ActiveSheet.Name = Format(ActiveSheet.Range("A2"), "yyyy") & "年" & Format(ActiveSheet.Range("A2"), "m") & "月"
    Next Item
    Application.DisplayAlerts = False　'ピボットテーブルのシートを消す際のアラート表示をオフにする
    nWS.Delete　'ピボットテーブルのシートを消す
    Application.DisplayAlerts = True　'アラート表示を元に戻す
End Sub

10,000件程度であれば、数秒で年月別シートの作成が可能です。

最近は Power Query や Power Pivot といった機能の追加・拡張が Excel では多いですが、ワークシートを消す、新規に作る、追加するといった作業は VBA でしかできません。
そういった作業の自動化の要望がある限りは VBA がなくなることはないでしょうね。

ただ、ちょっとだけ「手間」を入れると Power Query の機能だけでも上記のことが可能になります。
機会があれば、Power Query で「年月別のシートを作る」手順をご紹介したいと思います。

Read More

Power Query から https 経由で SharePoint の Excel ブックを開く

Power Query (2.23.4035.242　June 2015 Release) で https 経由で SharePoint のドキュメントライブラリに保存されている Excel ブック、および OneDrive for Business にある Excel ブックへのアクセスが可能になっていることを確認しました。(もしかしたら、May 2015 Release からかもしれません、、、、）

Power Query は頻繁に更新されています。数か月前は https 経由で SharePoint や OneDrive for Biz の Excel へのデータ接続はできませんでした。ローカル PC の同期フォルダーを使っているのであれば、同期フォルダー内の Excel ブックにデータ接続可能でした。

今後は同期フォルダーを構成していなくても、https 経由で Excel ブックに接続可能です。（ただし、コンシューマー版の OneDrive へのアクセスは成功していません。([*1] 2022年追記 URLを編集して可能になっています）OneDrive for Business は Office 365 で提供されている OneDrive です。これは SharePoint をベースにしています）

取り込み手順

使い方はいたって簡単です。

[Power Query] タブの [外部データの取り込み] の [ファイルから] にある [Excel から] を選択します。

[ファイル名] に SharePoint ドキュメント ライブラリーの URL を入れます。

この URL は SharePoint のドキュメント ライブラリーであれば、_layouts の前までになります。
OneDrive for Business も同様です。

[開く] ボタンを押すと、SharePoint サイトにある「すべてのサイト コンテンツ」のリストが表示されます。

今回は [ドキュメント] にあるブックを開くので、[ドキュメント] をダブルクリックします。(英語表記は　Shared Documents）

ドキュメント ライブラリにある Excel ブックを選択し、ダブルクリックするか、[開く] ボタンを押します。
すると、Web コンテンツへのアクセス ダイアログが表示されるので、[組織アカウント] を選んで、該当する URL をチェックします。
既定が　[匿名] になっているので注意してください。

組織アカウント（Office 365 の ID）とパスワードでサインインします。

サインインが完了すると、以下のダイアログになるので [接続] をクリックします。

ナビゲーター ウィンドウが表示されるので、取り込みたいシートやテーブルを指定します。

[編集] ボタンを押せば、クエリ エディタ ウィンドウが表示され、クエリの編集をすることができます。
クエリ エディタ ウィンドウではデータの絞り込みや並べ替え、列の削除や追加、データ型の変換を行い、欲しい形でデータを取り込むことができます。

[閉じて読み込む] を押せばワークシートにデータが展開されます。特に編集の必要がなければナビゲーター ウィンドウの [読み込む] ボタンでデータの読み込みが可能です。

これまで、ファイルサーバー上の Excel ブックへのデータ接続は可能でしたが、https 経由でのデータ接続は少なくとも半年前はできませんでした。これにより、\\サーバー名 でのアクセスと同じように https:// で SharePoint 上にある Excel ブックへの接続が可能になりました。

何がうれしいの？

真っ先に思い浮かべたのが「Excel アンケート」です。

アンケートの共有をしている間は、ローカルの PC/Excel でブックを編集モードで開くことができません。

アンケート集計が溜まってくると、ピボットテーブルを使って分析したくなるのですが、Excel Online ではピボットテーブルをゼロから作ることができません。
こんなときには Power Query を使って、SharePoint にある Excel ブックをクエリで読み込むことでピボットテーブルを作り、データ分析が可能になります。もちろん [データ更新] によって、最新のデータにすることも可能です。

次に使えるのは外部ブックのテーブル オブジェクトへのリンクです。これは SharePoint のドキュメント ライブラリーにある Excel ブックに限らず、ローカル PC やファイルサーバーでも同様です。

ここに、外部ブックの範囲に対して VLOOKUP を使ってデータを参照しているブックがあるとします。範囲の場合は再計算によって「その時の」外部ブックのデータを取り込む（＝更新）することが可能です。

このブログで過去に紹介しているように、VLOOKUP や INDEX、MATCH などの「範囲指定」する関数にとって、範囲を「テーブル」にすることで、データの増減に自動的に対応可能になることは、テーブルを使う最大のメリットなのですが、テーブルの場合は外部リンクは使えないのです。このことはマイクロソフトの技術記事でも紹介されています。

Excel テーブル 数式で構造化参照を使う（support.office.com)

ここで「構造化参照を活用するヒント」に以下の記述があります。

“他のブックの Excel テーブルへの外部リンクを含むブックを使用する     ブックに別のブックの Excel テーブルへの外部リンクが含まれている場合は、リンクを含む「リンク先」ブックの #REF! エラーを回避するため、そのリンクされた「リンク元」ブックを開いておく必要があります。リンク先ブックを最初に開くと、#REF! エラーが表示され、その後リンク元ブックを開くと、エラーは解決します。リンク元ブックを最初に開くと、エラー コードは表示されません。” (引用おわり）

つまり、リンク先ブックを開いておかなければ、別のブックのテーブルへリンクしている数式は #REF! エラーになります。

これを回避するために、リンクを使わず、Power Query を使うことで #REF! エラーを回避しつつ、データ更新によって最新データを参照することが可能になります。

この機能拡張によって、Office 365 の SharePoint や OneDrive for Business を Excel の保存先として、従来のファイルサーバーのように使うことが可能になりました。

 

毎月にようにリリースされる新しい Power Query については、英語ですが Office Blogs で新リリースごとに紹介されています。

https://blogs.office.com/

[*1] OneDrive パーソナル (onederive.live.com) の Excel ファイルに接続する方法
https://road2cloudoffice.blogspot.com/2022/04/power-query-onedrive-onederivelivecom.html

Read More

Power Query が Excel 全エディションで利用可能になりました

Excel 2016 Preview 版で Power Query がデータタブに移動してアドイン扱いではなくなった、、、という投稿を書き、2015年4月時点で Power Query (Power BI Excel アドインのひとつ）をインストールできる Excel のエディションの話をまとめました。

Power Query が Excel の Professional Plus や Office 365 ProPlus だけしか使えない現状に対して、Excel 2016 で「もし」 Professional や Home and Business でも使えるようになったら、マイクロソフトの英断を称えたいですね、としました。

実に吉報です。

2014年4月リリース版の 2.22.4007.242 から、Power Query は制限付きながらも全エディションで利用できるようになりました。

https://support.office.com/en-us/article/Whats-new-in-Power-Query-936b2fca-4168-41ad-bbbd-7b83856b6776?ui=en-US&rs=en-US&ad=US&Ocid=Excel_Social_TWITTER_msexcel_20150504_176341939

制限は以下のデータ接続が使えない、という点です。（抜粋。詳しくは上記リンクで）

• Power BI データ カタログ
• Azure-based data sources
• Active Directory
• SharePoint Lists
• Oracle
• DB2
• MySQL
• Dynamics CRM
• SAP BusinessObjects
• Salesforce

これは、Microsoft SQL Server データべ―スや Access データベース、SQL Server Analysis Services データベースはデータ接続可能ということ。Facebook や ODBC も可能ですね。

また、このブログでは Office 365 の SharePoint Online と Excel の連携を紹介していますが、データ接続方法は OData Feeds を使っています。
Excel Services によるデータ更新を Web パーツでやりたい場合の接続は Power Query ではなく、標準機能の「データ接続」にある OData Feeds を勧めています。

ということは、手持ちの LENOVO Miix 2 8 に入っている Excel 2013 Home and Business でも、Power Query が使える、ということになるので、早速やってみました。

Excel 2013 Home and Business に Power Query 2.22 をインストールする（全く問題なし）

Lenovo Miix 2 8 には Excel 2013 Home and Business がインストール済みです。

上記のダウンロードセンターのリンクから 2.22.4007.242 の Power Query をダウンロードして、msi セットアップを実行すると、なんの警告もなくあっさりと Power Query が Excel 2013 Home and Business にインストールされました。

Power Query タブが追加されていますが、[外部データの取り込み] に [Azureから] がないのがわかります。

[データベースから] を展開すると、絞りこまれたデータベース ソースが表示されます。

[その他のソースから] は以下になります。

SharePoint のドキュメント ライブラリーにある Power Query OData Feed 接続による SharePoint リスト テーブルのブックを開いて更新するも、なんの変わりもなく。。。

Excel 2016 が出る前に、まさにマイクロソフトは英断しましたね。

接続先の制限があるとはいえ、通常の Excel ユーザーにしてみれば、この程度のデータ接続先で問題はないでしょう。
Microsoft さん、よくぞこの決定をしましたね。すばらしいです！

Read More

Excel ブックを開かずに複数ブックの数値を集計する - SharePoint ドキュメント ライブラリ列との連携

複数の 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

Read More