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