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

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 で入力・計算・出力をイメージする ～ Office 365 との付き合い方

Excel ユーザーであれば、Office 365 とうまく付き合うことができる、という話である。

Excel を中心に位置付けてクラウド、Office 365をどのように活用できるか、という話題になるわけだが、これまで「テーブル」機能や「データ モデル」によるデータ接続先としての「SharePoint リスト」、さらにデータを効率的に操作するツールである「PowerPivot」を紹介してきた。

エクセル方眼紙がクラウド導入の邪魔をする

しかし、そもそも実務で使っている Excel ブック、ワークシートが「エクセル方眼紙」ばかりだと、Office 365 や SharePoint は単純な「ファイル共有サーバー」か OneDrive for Business による「ファイルの退避先、バックアップ先」という使い道しかないだろう。

一般社団法人実践ワークシート協会 代表理事の田中亨 （OfficeTANAKAサイトの管理者といったほうが知っている人が多いだろう）が提唱している Excel ブック活用のための考え方 ～ Excel 活用のための5つの原則 ～ の中に「入力・計算・出力」がある。

Excel 活用のための 5つの原則 （実践ワークシート協会サイトより）
http://www.pwa.or.jp/%E4%B8%8A%E9%81%94%E3%81%AE%E3%81%9F%E3%82%81%E3%81%AE5%E3%81%A4%E3%81%AE%E5%8E%9F%E5%89%87/

Excel にデータを入力する、入力されたデータを処理・計算する、処理・計算した結果をレポートや印刷として出力する、その「役割」や「フェーズ」をワークシート作成の際に「入力・計算・出力」として意識せよ、ということだ。

エクセル方眼紙は、この役割が入れ子になっている。まず最初に「出力用の表」を作成し、そこに計算・処理のためのワークシート関数を入れ、そのワークシートにデータを入力させ、その結果を印刷する、もしくは回収する、といった具合だ。エクセルブックで配られる「申請書」や「登録届」的なワークシートを目にすることは多いはずだ。

マイクロソフトがオンラインで提供しているテンプレートから作成

はじめから印刷をし、押印をして提出するようなものは Excel の印刷機能のメリットを十分に享受できるが、ブックで返信するような申請書などは、いったい回収した後でどう「集計」やデータの「再利用」をするのだろう。

選択肢などで図形の○を使っているものなどは、人間は目視で何を選択したか判断できるが、Excel は判断できない。（これをマクロで判断したい、などという無理・無駄・無茶な話も実際ある）

最悪なのは Excel 初級や入門コースなどで例題として出される「クロス集計表」だ。

よくある手で作成するクロス集計表

そのセルに入力するデータはいったいどこからもってきたのか？(このセルにデータを入力するために電卓を使っているユーザーを何人も目撃している）元のデータが変わったら、項目が増えたら、そのクロス集計表はどう直すのか。そもそも何故クロス集計のために用意されている最強機能である Pivot テーブルを教えないのか。事実、Pivot テーブルは「上級者編」として扱われることが多いが、ビジネスの現場にいる Excel ユーザーはクロス集計を使う場面が多いはずである。ワークシート関数などを覚える前に Pivot テーブルを覚えた方がはるかに効果的であると感じる人も多いだろう。このようなクロス集計表を手で作ってしまうと、そのワークシートに入力されたデータも、そのワークシート自体も「再利用」されないのは明白だ。

なぜこのような表を作ってしまうのか。その理由はワークシート作成時に「入力・計算・出力」を意識していないからである。また、意識せよと誰からも教わっていないからである。

クロス集計表が Excel 入門コースで使われる理由は「分析に便利なクロス集計」を教えることではない。罫線の引き方、SUM・SUBTOTAL関数の使い方、グラフの作り方、場合によっては実務で使い道が少ないアウトラインの使い方などを解説するためだけだ。さらに、入力・計算・出力の考え方からすれば、この表は手作業で作ってはいけない表なのだ。

入力・計算・出力から Office 365 の役割が明確になる

Office 365 特に SharePoint は「何でもできる魔法の箱」のような紹介をされるが、Exchange Online(メール）や Lync Online(メッセンジャー）に比べると、何をしてよいかがわかりづらいサービスである。しかし、Excel ユーザーからすると、この SharePoint Online こそ、Excel ユーザーが長年感じていた課題を解決してくれる場面が多い。

その課題とは「入力」である。

前提となるのは今扱っている表やテーブルが「1行1データの原則」に基づいて設計がされていることだ。

それができていれば SharePoint リストを入力で使えば以下が可能だ。

• だれがその「行」をいつ作成したか、さらに最終更新者はだれでいつかがわかる
• 簡単な集計やグループ化、並べ替えであれば SharePoint だけで可能である
• 入力や更新・修正はあくまで「行単位」であり、ブック全体をロックする必要がなくなる
• SharePoint リストのバージョン管理機能を使えば、バックアップを数世代とることができる

そして何より入力されたデータは SharePoint リストとして SharePoint に存在し、リストに対してアクセス権を持っている Excel ユーザーであれば複数人同時に SharePoint リストを元データとして自分の Excel でデータの処理ができるようになる。

ただ、何度も書くか、SharePoint リストに対して過大な期待は禁物だ。
Excel で現状やれていることは SharePoint リストでも可能なことが多いが、そこから「もっと便利に」と期待して作りこむと難易度が急に高くなる場合が多い。

最初は、Excel で行われている「入力」の部分を SharePoint リストにまかせる、と割り切って付き合い始めるのが良いだろう。

そうやって、SharePoint リストより収集されたデータを「テーブル」という形で Excel にエクスポートすれば、「計算」のフェーズである。四則演算だけではなく、文字列操作や、フラグによる論理演算なども「計算」だ。この計算は「出力」のための下準備である。ワークシート関数を使う、VBA を使う、Pivot テーブルを使うなどなど、様々な方法が考えられる。

そして最後は「出力」だ。

この出力のフェーズでは「エクセル方眼紙のテクニック」を存分に使ってよい。なるべく参照のみの数式にしたいところだが、どうしても論理演算からそのセル（または結合セル）を「空白」にするなど一部の関数・数式による処理も入るだろう。重要なのは出力では「見た目のための処理」に留めるべきであり、データそのものの加工や計算は印刷のための出力シートでは可能な限り避けることでメンテナンス性の高いワークシートになる。

実践ワークシート協会でも、VBA セミナーの受付はメールでいただき、そのデータを SharePoint リストに複数人で登録している。（もちろんメールボックスは共有メールボックスを使い、複数の人がメールを確認できるようにしている）アイテムを登録する時点で各開催日の申込み人数や、登録した受講者への受付、お断りといったステータスを管理している。受講者によっては「請求書」や「領収書」を希望する場合があり、その時は、SharePoint リストにデータ接続された Excel から氏名や宛先、受講コースや金額といったデータを抜出し、エクセル方眼紙テクニックで作成された「請求書ワークシート」や「領収書ワークシート」からデータを参照し、印刷している。

今後は Excel だけではない「入力・計算・出力」の考え方

前回紹介した Excel Services などはまさに「入力・計算・出力」の「出力」の部分で活用される機能だ。

このように、実践ワークシート協会の田中が提唱している「入力・計算・出力」も、Excel だけの要素で考える時代が終わりつつあると感じている。（まだ、Excel の世界だけでも浸透していない考え方であり、今後も実践ワークシート協会はこの考え方を Excel ユーザーに啓蒙していかなければならないが。）

Excel + Office 365 で「入力・計算・出力」の考え方を使って、今利用しているブックを見直し、適切な機能をそれぞれのフェーズで使うことが今後ますます求められることになるだろう。そして、それを適切に見極め、利用できるユーザーが本当の意味で Office 365 のメリットを享受することができるのではないだろうか。

その可能性を一番持っているのは Excel を使いこなしているユーザーであると考える。

Read More

Excel ユーザーのための SharePoint リスト 「集計値」列

Excel ユーザーであれば、SharePoint リストを活用できるという話だが、リストの Excel へのエクスポート機能もさることならが、SharePoint そのものの「Excel 的な要素」を実感するのが「集計値」の列である。

列の情報の種類で「集計値」を選択すると、その列に「数式」を入れることが可能になる。また、リストで定義されている列を数式で使うことができる。まさに Excel の数式に近い。

そして、この数式で利用できる「関数」も用意されている。

Excel でもそうだが、単純な四則演算の他で入力されたデータを行単位で処理するパターンとしては、

• 文字列操作
• 日付の操作・計算

が主だろう。

もちろん、Excel にエクスポートしてしまえば、Excel だけで処理可能であるが、SharePoint リストのビューのグループ化などをする場合に、グループ化の列を入力されたデータから加工して作るケースも考えられる。

Excel ユーザーであれば「ニヤリ」とする場面も多々あることから、SharePoint リストでの数式、関数を覚えておいて損はない。

しかし、いつものように「落とし穴」もあるので注意してほしい。それらも含めて紹介する。

文字列の結合

"姓"と"名"を別々に入力させて氏名にするなどは "&" を使う。姓名の間に半角スペースを入れることも可能だ。アイテム入力にはこの集計値の列は表示されない。リスト表示の際は「氏名」だけを表示するビューを作成すればよい。CONCATENATE 関数も使える。

文字列のチェック/抜出し/置き換え

文字列操作では Excel でおなじみの LEFT、RIGHT、MID 関数、FIND、SEARCH 関数、REPLACE、TRIM、CLEAN 関数が使える。私が試した範囲だと全角を半角にする ASC 関数は動いていないようだ。半角を全角にする JIS 関数はない。（構文エラーになる）

TEXT 関数と VALUE 関数もある。特に TEXT 関数は日付の表示形式でも使う。
UPPER, LOWER, EXACT 関数もある。

もちろん、関数を組み合わせて使うことも可能だ。入力されたコードの "-" より前半部分を抜きだす数式は以下になる。

=LEFT(コード1,FIND("-",コード1)-1)

ただ、数式入力ボックスは狭く、数式オートコンプリートによる入力支援機能はない。よって、引数についてはワークシート関数と同じだと思って入力するか、SharePoint リストで利用できる関数のヘルプをみて入力する必要がある。

日付はシリアル値で保存されている

Excel ユーザーにとっては「！？」という事実なのだが、SharePoint リストで日付をシリアル値で扱っていることは Excel ユーザーにとっては大きなメリットだ。つまり、シリアル値が使えることで、若干 Excel の数式/関数とは違うがほぼ Excel と同様のことができる。

年、月、日、曜日を表示する

YEAR関数、MONTH関数、DAY関数を使えば、年、月、日を抜き出すことができる。ただ、これらの関数を使うと、「返されるデータの種類」を「一行テキスト」していても数値になる。（2014なら 2,014 といったように） 

ここで TEXT 関数を使えば =TEXT([日付データ], "YYYY") で文字列として年を取り出すことができる。 Excel 同様に1月を 01 と取り出したいのであれば、=TEXT([日付データ], "MM") となる。

残念ながら "g" や "ge" で元号はとれない。

曜日は若干数式が違うので注意してほしい。

Excel であれば、=TEXT([日付データ],"aaa") で「水」になり、=TEXT([日付データ],"aaaa")で「水曜日」になる。 "ddd" で「Web」になり、"dddd" で「Wednesday」となるが、SharePoint の場合は以下になる。

=TEXT([日付データ], "ddd")   「水」
=TEXT([日付データ], "dddd") 「水曜日」

"aaa" はなく、逆に Fri や Friday と表示させたい場合は、関数で工夫するしかない。

期間/時間の計算

シリアル値を使っているので、日数計算は単純な加減計算となる。
シリアル値で計算後の時間の表記については TEXT 関数を使う。特に TEXT 関数の  "[h]:mm" で24時間を超える数値の表記が SharePoint リストの数式でも指定可能だ。

[追記]
Excel でもそうなのだが、1日はシリアル値「1」なので、1をプラスすればよいが、時間になった場合は、12時間だから 0.5 と考えてはいけない。もし、11分だったら 11/60/24 で 0.0076333333.... と循環小数になる。安易に小数点による計算を勧める記事が多すぎるのは Excel のシリアル値操作での失敗例もしくは正しい使い方をご存じないからだろう。

時間の計算はシリアル値を直接操作せずに、文字列 "h:mm:ss" を加算するだけでよい。
たとえば、開始時間をリスト アイテムとして入力させて、集計値の列で予定終了時間を 4 時間後にする場合、入力する式は以下になる。

=TEXT(開始時間+"4:00:00","YYYY/MM/DD h:mm")

TEXT関数を使わなければ、集計値の列ではシリアル値が表示されてしまうため表示形式を関数で指定している。
もちろん、シリアル値として計算するので、60分を超えれば1時間繰り上がる、24時間を超えれば1日繰り上がる。（減算もしかり）

注意点は "h:mm:ss" は時刻表記であることだ。 h は 23 を超えることをはできない、mm や ss は 60 を超えることができない、という点だ。 70分を追加したいのであれば、 "1:10:00" である。26時間を指定したいのであれば、まずシリアル値に 1 を足した後で "2:00:00" を加算してほしい。


その他（日付関連）

シリアル値を使っているので、月末の日は翌月の1日から１を引けばわかる。その時、翌月の1日を作成するには DATE 関数を使って文字列の年月日表記をシリアル値してから１を引く。

=DATE("2014","3","1")-1

上記を応用すれば、うるう年の判定も可能だ。上記式の日付が "29" だったらうるう年である。

[注意] 四則演算結果の集計値の列を SharePoint リストで集計できない

これはできると思ってしまう「落とし穴」だが、たとえば、[数量] * [単価] で金額を計算する集計値の列を作り、SharePoint リストの「集計」を使ってその合計を出そうとしたくなるだろう。
しかし、残念ながらこの集計はできない。

あまり凝ったことは SharePoint リストでやらないほうがいい

ここまで紹介して、最後の結論がこれだとがっかりくるだろう。上述した計算結果を集計できない、また、[h]:mm の時間も集計できない。[h]:mm はTEXT関数で文字列になっているからである。Excel のセルの表示形式はセルの実体は変えずに見た目だけを変更しているが、SharePoint リストの列には表示形式という機能はない。なにより、入力するエリアの横幅がせまく、数式オートコンプリートも効かないため、長い数式は鬼門である。
あくまで、入力するユーザーにとって、または、リスト表示上最低限必要なものに限って数式を使ってデータの表現方法を変えるにとどめ、複雑な計算式は Excel にエクスポートしてからおこなったほうが無難だと言える。ここでも Excel のワークシート作成テクニックである「入力」－「計算」－「出力」の考え方は活きてくる。

注意してほしいのは、データ接続によって Excel 側に出された Excel の計算結果は元の SharePoint リストに標準の機能として反映されないという点だ。

それでも、Excel の計算結果を SharePoint のページ上でみたい、（つまり、「出力」）となれば、Excel Services である。次回は Excel Services を紹介したい。

[参考]
Ｏｆｆｉｃｅ デベロッパー センター 「集計フィールドの数式」
http://msdn.microsoft.com/ja-jp/library/office/bb862071%28v=office.14%29.aspx

Office Online 「データ計算の説明」
http://office.microsoft.com/ja-jp/windows-sharepoint-services-help/HA010379914.aspx?CTT=1

Office Online 「列のタイプおよびオプション」
http://office.microsoft.com/ja-jp/office365-sharepoint-online-enterprise-help/HA010302193.aspx

Read More

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 の処理を検討してみる価値はあるだろう。

Read More

テーブルのすすめ 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

Read More