SharePointのリストには、「集計値」と呼ばれる列があります。
これは、他の列を使って計算処理できる仕組みです。エクセルの関数と同じ要領で、関数の引数に存在する列を引き渡してよろしく処理してくれるありがたい機能です。
頻繁に使う機能でもないので久しぶりに使おうとしたらハマりました。
この「集計値」の列って集計ができない。
集計値で作った数値の行すべての合計が見たかったのですが、リスト上で簡単に出せなかった。
結果的には、Power Automate（旧Flow）で日次処理を組みました。もっといいやり方がありそうな気はしますが、備忘録として投稿します。
現象について
まずは状況について。
- 「単価」と「個数」を組み合わせて「総額(これが集計列)」を出す
- ビューの集計機能で 総額の合計 を出そうとしたら出てこない
リストの内容
作ったリストは次のようなイメージです。
| 列名 | 列の種類(型) |
|---|---|
| 商品名 | 1行テキスト |
| 単価 | 通貨 |
| 個数 | 数値 |
| 総額 | 集計値 (「=[単価]*[個数]」を数式で入れる) |
上記に、適当なデータを入れると集計列である総額に、金額が入ります。
（Fire HD 8はプライムデーで一つ買いました）
集計列を集計（合計）しようとすると…
そして、各列の総額を得るためにはビューの設定が必要になります。ビュー編集の画面からから設定項目を確認します。
▲ 「集計」という設定項目があります。ここを見ると、商品名、単価、個数などの列がありますが集計列として作った総額が表示されません。
上記は色々調べてみると、データシートビューに切り替えると表示することはできました。
モダンUI環境からデータシートビューを作るには一旦クラシック表示に戻す必要がありました。
この記事投稿時点では、モダンUIのサイドバーにクラシック表示に戻すためのリンクがあります。
これなくなったらどうするんだろう…
▲ 新規に「ビューの作成」を行います。
▲ 「データシートビュー」を選びます。
▲ 集計の設定項目を見ると、確かに集計列「総額」が設定できるように表示されました。これに合計を設定してみます。
▲ ビューを新規に作ったデータシートビューの状態にすると集計された行が表示されます。しかし、総額が計算されない… SharePointでは限界を感じます。
そんなときは、Power Automateよ
列の総計出すだけなのに、なんだか大げさだなぁ…
Youtubeでヒントを得る
Power Automateで解決はできるだろうと思ってはいましたが、自分で作りつつもYoutubeでヒントとなる動画を見つけました。
▲ 内容的には私がやろうとしていたことがそのまま動画として投稿されていました。ありがたい。
- 集計列は計算できないので使わず、数値列（あるいは通貨列など）を使う
- 数値列は、Power Automateで計算させて計算結果を代入する
- SharePointのビューで集計させる
ビューの集計には、集計列が使えない仕様はあきらめ、それならば数値列を使い、計算自体はPower Automateで行うということになります。
Power Automateでの解決試み (1)
動画の通りにフローを作ります。Power Automateの概念的な説明は割愛します。Microsoft365をビジネス的なプランで契約している方は使えると思います。
▲ 自動フローで新規フローを作ります。
▲ 適当なフロー名をつけて、トリガーを設定します。トリガーは、「アイテムが作成または変更されたとき」を使います。
▲ トリガーとアクションにはそれぞれ、サイトとリスト名を指定します。
アクション「項目の更新」には必須項目を入れておきます。単価、個数は入力しないでおきます（入力しなければ変更もされない）。
▲ 総額の入力欄にカーソルを合わせて、関数式を入れます。算術関数「mul()」まで指定して個数、単価をカーソル操作で指定します。
mul(triggerOutputs()?['body/OData__x5358__x4fa1_'],triggerOutputs()?['body/OData__x500b__x6570_'])▲ 入力されたコードは複雑ですが、内容は単純で「単価×個数」としているだけです。「mul(数値,数値)」は2つの数値を乗算させる関数です。
▲ フローを作って保存すると、保存時にデバッグが走りますが構文エラーのようなメッセージが出ます。
頭のなかを整理すると、確かに無限ループになることを理解します。
- トリガー：項目が更新されたら
- アクション：項目を更新する…、というわけなので
- 2番目が「トリガー：項目が更新されたら」に当たるわけで
- 以下、ループ（そりゃそうだよね）
Youtube動画ではデバッグエラーになっていませんでした。その後、MS内でバグをエラーがでるように改善したのでしょうか。
試しに、強制的にフローを動かしましたが、確かにループしました。フローをオフにしましたが、止め忘れるとマイクロソフト経由職場に怒られそうです。
Power Automateでの解決試み (2)
当初のフロー処理はそのままに、トリガーを変更して対応しました。
トリガーにループ要因があるのでそれを取り除けばよい。ボタン押して起動するインスタントフローか、時間になると動き出す予定フローであれば無限ループにはならなそうです。
画像は予定フローです。最初に、リスト全項目を取得してループで一つずつ総額計算しています。
▲ フローができたら実行します。予定フローは指定日時実行ですが、マイフロー一覧から実行処理するとそのタイミングで処理が実行されます。
▲ ここまでやってようやく計算してくれました。
まとめ
集計列が集計ができないって不便だなぁ…と思いつつ、そういえば Tableauも集計した列の再計算はできません。Tableauだと集計した列と集計していない列で新たな計算をしてもエラーになります。
数学的な素養が低いので詳しいことはわかりませんが、集計列って難しいですね。
集計列を使わず、数値や通貨列を使うというのは目から鱗でした。
最初に考えたやり方にこだわり過ぎず、アプローチを変える発想が必要かと思いました。
