快適・意識高い系のテレワーク環境が月額2,000円〜(リンクを開く)

SharePointの集計値の列が合計されない仕様との格闘。自動処理を加えてやや微妙な解決。

SharePointの集計値の列が合計されない仕様との格闘。自動処理を加えてやや微妙な解決。 SharePoint
スポンサーリンク

SharePointのリストには、「集計値」と呼ばれる列があります。

これは、他の列を使って計算処理できる仕組みです。エクセルの関数と同じ要領で、関数の引数に存在する列を引き渡してよろしく処理してくれるありがたい機能です。

頻繁に使う機能でもないので久しぶりに使おうとしたらハマりました。

この「集計値」の列って集計ができない。

集計値で作った数値の行すべての合計が見たかったのですが、リスト上で簡単に出せなかった。

結果的には、Power Automate(旧Flow)で日次処理を組みました。もっといいやり方がありそうな気はしますが、備忘録として投稿します。

スポンサーリンク

現象について

まずは状況について。

  • 「単価」と「個数」を組み合わせて「総額(これが集計列)」を出す
  • ビューの集計機能で 総額の合計 を出そうとしたら出てこない

リストの内容

作ったリストは次のようなイメージです。

列名 列の種類(型)
商品名 1行テキスト
単価 通貨
個数 数値
総額 集計値
(「=[単価]*[個数]」を数式で入れる)

上記に、適当なデータを入れると集計列である総額に、金額が入ります。

SharePointの集計値の列で集計(合計)できない件を解決する(リストの例」)
リスト例
(Fire HD 8はプライムデーで一つ買いました)

集計列を集計(合計)しようとすると…

そして、各列の総額を得るためにはビューの設定が必要になります。ビュー編集の画面からから設定項目を確認します。

SharePointの集計値の列で集計(合計)できない件を解決する(ビュー集計の設定項目に集計列で作った列が見当たらない)

▲ 「集計」という設定項目があります。ここを見ると、商品名、単価、個数などの列がありますが集計列として作った総額が表示されません。

上記は色々調べてみると、データシートビューに切り替えると表示することはできました。

SharePointでモダンUIからクラシックUIに切り替えるためのリンクはサイドバー下部にある

モダンUI環境からデータシートビューを作るには一旦クラシック表示に戻す必要がありました。

この記事投稿時点では、モダンUIのサイドバーにクラシック表示に戻すためのリンクがあります。

これなくなったらどうするんだろう…

SharePointモダンUIからクラシックUIに戻した
クラシック表示に戻す

▲ 新規に「ビューの作成」を行います。

SharePointの集計値の列で集計(合計)できない件を解決する(データシートビューを作る)

▲ 「データシートビュー」を選びます。

SharePointの集計値の列で集計(合計)できない件を解決する(集計列で作った列が表示された)

▲ 集計の設定項目を見ると、確かに集計列「総額」が設定できるように表示されました。これに合計を設定してみます。

SharePointの集計値の列で集計(合計)できない件を解決する(しかし、集計列を集計・合計してくれない)

▲ ビューを新規に作ったデータシートビューの状態にすると集計された行が表示されます。しかし、総額が計算されない… SharePointでは限界を感じます。

マレー
マレー

そんなときは、Power Automateよ

アレコレメモ
アレコレメモ

列の総計出すだけなのに、なんだか大げさだなぁ…

スポンサーリンク

Youtubeでヒントを得る

Power Automateで解決はできるだろうと思ってはいましたが、自分で作りつつもYoutubeでヒントとなる動画を見つけました。

Flow that calculates sums in SharePoint lists

▲ 内容的には私がやろうとしていたことがそのまま動画として投稿されていました。ありがたい。

動画内容の要約
  • 集計列は計算できないので使わず、数値列(あるいは通貨列など)を使う
  • 数値列は、Power Automateで計算させて計算結果を代入する
  • SharePointのビューで集計させる

ビューの集計には、集計列が使えない仕様はあきらめ、それならば数値列を使い、計算自体はPower Automateで行うということになります。

Power Automateでの解決試み (1)

動画の通りにフローを作ります。Power Automateの概念的な説明は割愛します。Microsoft365をビジネス的なプランで契約している方は使えると思います。

集計列で作った総額の項目は一旦削除、通貨列として作り直しています。

Youtube動画のとおりに作りますがバグがあります。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで自動フローを作る)

▲ 自動フローで新規フローを作ります。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで自動フローを作る)

▲ 適当なフロー名をつけて、トリガーを設定します。トリガーは、「アイテムが作成または変更されたとき」を使います。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで自動フローを作る)

▲ トリガーとアクションにはそれぞれ、サイトとリスト名を指定します。

アクション「項目の更新」には必須項目を入れておきます。単価、個数は入力しないでおきます(入力しなければ変更もされない)。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで自動フローを作る)

▲ 総額の入力欄にカーソルを合わせて、関数式を入れます。算術関数「mul()」まで指定して個数、単価をカーソル操作で指定します。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで自動フローを作る)
mul(triggerOutputs()?['body/OData__x5358__x4fa1_'],triggerOutputs()?['body/OData__x500b__x6570_'])

▲ 入力されたコードは複雑ですが、内容は単純で「単価×個数」としているだけです。「mul(数値,数値)」は2つの数値を乗算させる関数です。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで自動フローを作る)

▲ フローを作って保存すると、保存時にデバッグが走りますが構文エラーのようなメッセージが出ます。

頭のなかを整理すると、確かに無限ループになることを理解します。

  1. トリガー:項目が更新されたら
  2. アクション:項目を更新する…、というわけなので
  3. 2番目が「トリガー:項目が更新されたら」に当たるわけで
  4. 以下、ループ(そりゃそうだよね)

Youtube動画ではデバッグエラーになっていませんでした。その後、MS内でバグをエラーがでるように改善したのでしょうか。

試しに、強制的にフローを動かしましたが、確かにループしました。フローをオフにしましたが、止め忘れるとマイクロソフト経由職場に怒られそうです。

Power Automateでの解決試み (2)

当初のフロー処理はそのままに、トリガーを変更して対応しました。

トリガーにループ要因があるのでそれを取り除けばよい。ボタン押して起動するインスタントフローか、時間になると動き出す予定フローであれば無限ループにはならなそうです。

画像は予定フローです。最初に、リスト全項目を取得してループで一つずつ総額計算しています。

SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで予定フローを作る)
SharePointの集計値の列で集計(合計)できない件を解決する(Power Automateで作ったフローを実行する)

▲ フローができたら実行します。予定フローは指定日時実行ですが、マイフロー一覧から実行処理するとそのタイミングで処理が実行されます。

SharePointの集計値の列で集計(合計)できない件を解決する(総額の総計が出せた)

▲ ここまでやってようやく計算してくれました。

スポンサーリンク

まとめ

集計列が集計ができないって不便だなぁ…と思いつつ、そういえば Tableauも集計した列の再計算はできません。Tableauだと集計した列と集計していない列で新たな計算をしてもエラーになります。

数学的な素養が低いので詳しいことはわかりませんが、集計列って難しいですね。

集計列を使わず、数値や通貨列を使うというのは目から鱗でした。

最初に考えたやり方にこだわり過ぎず、アプローチを変える発想が必要かと思いました。

タイトルとURLをコピーしました