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

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

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

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

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

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 06/15/24

    @real_beginner さん、こんにちは。

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

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

    1 レポートを使う方法

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

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

    フィルター

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

    グループ

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

    サマリー(集計)

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

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

    表2,表3のレポートは、新規に保存(コピー)を利用して効率的に同様のレポートを作成することができます。

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

    表2の変更点(グループ化)

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

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

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

    表3の変更点(フィルター)

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

    2 SUMIFS関数を使う方法(集計用のシートを作る必要がある場合)

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

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

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

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

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

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

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

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

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

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

    表2シートの変更点

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

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

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

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

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

    表3シートの変更点

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

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

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

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

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

    以上まとめると、簡単に集計するにはレポートがオススメで、見栄えを良くするなどシートを別個に作る必要がある場合は、SUMIFS関数のようなシート間関数を用いるとで目的の集計などを行うことができます。

    以前は、集計用のシートを作り、それを下にダッシュボードでグラフ化することが一般的でしたが、レポートの機能が向上したので、レポートのみでダッシュボード表示表の表やグラフが作成できるようになりました。

    上で紹介したシートなどをまとめてダッシュボードに貼っておきます。

ヘルプ & ラーニング センター