2015/02/18

SharePoint リスト列の選択肢がExcelで計算されない

SharePoint リストの [接続とエクスポート] の [Excel にエクスポート] (SharePoint リスト接続)でリストを Excel のテーブルとして抽出することで、Excel 側で加工・計算できることを紹介してきた。

通常の使い方であれば、エクスポートした後のテーブルを参照してピボットテーブルを使い、さまざまな視点でデータを集計することで、おおよその業務目的は達成できるが、SharePoint リスト接続で問題になるのが列の種類で「選択肢(メニューから選択)」を選び、選択肢として「数字」をいれた列の扱いである。

r2coリスト列定義

この列への入力はドロップダウン メニューから該当する数字を選択する。アイテム入力後のリストは以下のように表示されている。

r2coリスト2

この状態(選択肢から数字データを選択した状態)は数字に見えるデータは「文字列」であり、現に SharePoint リストの [集計] でも個数の集計しかできず、「数値」としての合計はできない。

一方、Excel は通常手入力でセルにデータを入れた場合は、全角であろうが数字が入ると半角に直し「数値」としてセルにデータが入力される。SharePoint リスト接続によるエクスポートでもこのような動きを期待したいところだが、実際は、エクスポートしたテーブルからピボットテーブルを作成しようとすると、セルに数字が入っているにもかかわらず、リストの [集計] と同様個数のカウントしかできず、数値(金額)の合計ができない。

r2coPivot 

Excel のテーブル上では以下のようになっている。こちらでも集計行を使って計算はできない。

r2coテーブルSharePoint

[文字列] であるセルの書式設定(列の書式設定)を [数値] に変えただけではこのデータは数値にならない。各セルで編集モードにして Enter を押してはじめて [数値] になる。数十、数百のリスト アイテムがあるテーブルの場合はさすがにこの対応はない。

この SharePoint リスト接続を使用したエキスポートの場合の対応方法は2つだ。

1) SharePoint 側で数値データに変換する

・ 集計値を使ってデータを変換する

SharePoint リストの集計値の列で VALUE 関数を使い文字列を数値にする。見た目も3桁カンマが挿入される。ただし、SharePoint リストの集計値そのもの集計することができないのが難点(もう一歩)である。

r2co数値化列2

r2co数値化列1

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

r2coテーブル2

0 コメント:

コメントを投稿

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を返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。