Smartsheet内のデータ集計計算式について

real_beginner · 06/14/24

お世話になります。Smartsheet内のデータを利用したサマリーテーブルを作成したいと思います。複数条件を指定した計算式となりますので，お教えいただければ幸いです。

【表1】データより，①支払日（予定）に日付が入っているものについて，②支店：「東京」「ロサンゼルス＋ニューヨーク」「ロンドン」毎に，③重要度：「最重要」「重要」「普通」「その他」毎に分けた，④金額（予定）の【各合計】

【表2】データより，①支払日（予定）に日付が入っているものについて，②支店：「東京」「ロサンゼルス＋ニューヨーク」「ロンドン」毎に，③重要度：「最重要＋重要」「その他」毎に，④プロジェクト：「AAA」～「CCC」毎，⑤支払日（予定）を月毎に分けた，⑥金額（予定）の【各合計】

【表3】データより，①支払日（予定）に日付が入っているものについて，②支店：全て，③重要度：「普通」かつ④変更：「あり」の，⑤支払日（予定）を月毎に分けた，⑥金額（予定）の【各合計】

heyjay · 06/14/24

https://help.smartsheet.com/ja/articles/2476736-get-started-pivot

jmyzk_cloudsmart_jp · 06/15/24

@real_beginner さん、こんにちは。

集計方法にはいくつかって、@heyjay さんが示しているようにプレミアムアプリである、Pivotを使う方法もその一つです。

ここではコアアプリのみで集計する方法として、レポートを使う方法と、SUMIFS関数を使う方法を紹介します。

１レポートを使う方法

シートの表にする必要がない場合の第一選択はレポートのフィルター、グループ、サマリー（集計）を活用することです。

表１のレポートでそれぞれの使い方を説明します。

https://app.smartsheet.com/b/publish?EQBCT=1a99afaeead14adfbd72578bfff55f3b

フィルター

表１では支払日（予定）に日付が入っているものに絞りたいので、下のイメージのようなフィルターとします。（「空白でない」でも動きますが、日付以外のデータが入った場合にも集計してしまいます。）

グループ

支店、重要度のグループで集計したいので、それらをグループに指定します。（なお、並び替えはアルファベット順となるので、例えば、最重要、重要などの順にしたい場合は、1 最重要　2 重要などとあらかじめ、ドロップダウンなどのデータを変更しておきます。）

サマリー（集計）

最後に、サマリーで集計したい列と集計方法（この場合は、Sum（合計））を指定します。

新規に保存（コピー）を利用して効率的に同様のレポートを作成

表２，表３のレポートは、新規に保存（コピー）を利用して効率的に同様のレポートを作成することができます。

これにより、対象となるシートや列を再指定する必要がなく、フィルター、グループなどのみを変更することにより、目的のレポートを作ることができます。

表２の変更点（グループ化）

表１の重要度に代えて支払月（予定）をグループ化基準とします。

支払月（予定）は元データのあるシートで以下の式で作ります。

=MONTH(支払日（予定）@row)

表３の変更点（フィルター）

重要度（普通）と変更（あり）のフィルターを追加します。

２　SUMIFS関数を使う方法（集計用のシートを作る必要がある場合）

レポートでなくシートで集計したい場合は、シート間関数であるSUMIFSを使います。

https://help.smartsheet.com/ja/function/sumifs

下のイメージの表１の[金額（予定）東京] では以下のような式を用いて集計しています。

=SUMIFS({データ集計計算元データ金額（予定）}, {データ集計計算元データ支払日（予定）}, ISDATE(@cell), {データ集計計算元データ重要度}, 重要度@row, {データ集計計算元データ支店}, "東京")

{データ集計計算元データ金額（予定）} はSUM（合計）の対象となる範囲を別のシートから指定しています。（なお、この例のようにシート名列名の用に範囲（Range）を命名するのが式や参照が複雑になっても混乱しないためのベストプラクティスです。）

{データ集計計算元データ支払日（予定）}, ISDATE(@cell)　は支払日（予定）の範囲が日付であるという条件です。ここでの@cellはやや高度なテクニックで「前にある範囲のそれぞれの値について」、といった意味です。

https://help.smartsheet.com/ja/articles/2476491-create-efficient-formulas-with-at-cell

残りの、{データ集計計算元データ重要度}, 重要度@row, {データ集計計算元データ支店}, "東京"　の条件範囲と条件についても同じよう仕組みで絞り込みを行っています。

https://app.smartsheet.com/b/publish?EQBCT=70a719761a074547bd2aa274afbaeece

新規に保存（コピー）を利用して効率的に同様の集計シートを作成

レポートの場合と同様に、新規に保存（コピー）を利用して効率的に同様の集計シートを作成することができます。

表２シートの変更点

まず、条件を指定する左端の列を支払月（予定）として、１から１２までの数字を入力します。（なお、月を追加したい場合は、右端の列のように月を式で追加します。

=IF(ISNUMBER(支払月（予定）@row), 支払月（予定）@row + "月")

次に集計式ですが、以下のように変更します。

=SUMIFS({データ集計計算元データ金額（予定）}, {データ集計計算元データ支払日（予定）}, ISDATE(@cell), {データ集計計算元データ支払月（予定）}, 支払月（予定）@row, {データ集計計算元データ支店}, "東京")

つまり、条件範囲と条件を、重要度から支払月（予定）に変更します。

https://app.smartsheet.com/b/publish?EQBCT=ea9622261a234074934a9e6fda3a22c4

表３シートの変更点

まず、金額が全支店のものとなるので、変更します。

次にSUMIFS式を以下のように変更します。

=SUMIFS({データ集計計算元データ金額（実績）}, {データ集計計算元データ支払日（予定）}, ISDATE(@cell), {データ集計計算元データ支払月（予定）}, 支払月（予定）@row, {データ集計計算元データ重要度}, "普通", {データ集計計算元データ変更}, "あり")

つまり、条件範囲と条件を、重要度が普通で、変更「あり」に変更します。

なお、上の式では、金額（実績）列を集計する場合に、SUMIFSでの集計対象の範囲を変更する必要があることを示しています。

https://app.smartsheet.com/b/publish?EQBCT=6329cf58600f4cefb83e360bd68e276a