Smartsheetで列選択と複数列の回答に基づいてロールアップする方法（Community）

はじめに

Smartsheetで、列選択と複数の列にわたる回答の数に基づいてロールアップする方法について説明します。具体的には、下の１番目の表のように、元となるデータでは、行方向にビジネスユニットが選択され、縦方向に評価したいAppが複数あり、この縦横でできる表の各セルに重要、重要でない、やや重要などの回答があり、この回答データを元に、２番目の表のように、ビジネスユニットと評価に基づき、例えば、ビジネスユニットで重要でないと回答しているのは何個あるか（すべてのAppに対する回答を合計して）というやや複雑なロールアップをします。

質問の要約

KPeltzelさんは、48個のアプリケーションについて6つの異なるビジネスユニットからフィードバックを得るフォームを作成しました。参加者は、ドロップダウンリストから自分のビジネスユニットを選択し、各アプリケーションを重要度に基づいてランク付けします。

KPeltzelさんは、各ビジネスユニットが重要度に基づいてどれくらいの頻度で何かを選択しているかをまとめたいと考えています。

答えの要約

このディスカッションでは何段階かの質問と答えのやり取りがありました。

元のデータで集計基準のビジネスユニットが複数出ない前提での回答

回答データで最初の答えでは、上の1番目の表のBusiness Unitの列がBusiness Unit 1、Business Unit 2などの選択が1回づつでるとの前提で以下の式を提案しました。
=COUNTIF(INDEX([App 1]:[App 5], MATCH(Business#, Business:Business)), [選択された回答]#)
この式のポイントはApp1からApp5までの列からビジネスユニットが選択したビジネスユニットに合致する行をINDEX関数を用いて取得していることです。通常、INDEX式では1列が選択され、そこからMATCHと組み合わせて特定の行の1の値のみ取得しますが、選択した行の範囲を取得して、それをCOUNTIFに渡す、という工夫を提案しています。

元のデータで集計基準のビジネスユニットが複数出でる前提での回答

しかし、その後のやり取りで、上の1番目の表のNameに相当する回答者がユニーク、すなわち、1列に同じ人は出てきませんが、ビジネスユニットに関しては、同じビジネスユニットが複数出ることがわかりました。つまり、Aさんがビジネスユニット１について回答し、Cさんもビジネスユニット１について回答する、といったことが想定されています。そうすると、上の式では解決しないので、ヘルパー列を追加して、その列で、例えば、Not Importantという回答がそれぞれの回答でいくつあるかカウントして、そのカウント数を選択したビジネスユニット、例えば、ビジネスユニット１のものがいくつあるかカウントする、という２段階の方法を取ることになりました。

列名にスペースや数字が含まれる場合の式での引用の仕方

なお、質問者は式の中で MATCH(“BU 2”, Business Unit:Business Unit)の「Business Unit」のように単語の間に空白が入っているものをそのまま「:」で繋いで範囲を指定しようとしていたので、列名にスペースや数字が含まれる場合は、角括弧で囲む、すなわち、[Business Unit]:[Business Unit]のようにする必要があることも説明しました。

解決策

ヘルパー列を作成する
まず、各行の回答を追加するヘルパー列を作成します。この列には、次の式を使用します。
[# of Answers]
=COUNTIF([System 1]@row:[System 49]@row, Answer#)
式を説明すると、その行のシステム１から４９までについて、答えが選択したもの（Answer＃のサマリーフィルドの値、例、Not Important）のものをカウントする。
COUNTIFS関数を使用する
次に、COUNTIFS関数を使用して、選択したビジネスユニットに一致するヘルパー列の値を合計します。
=SUMIFS([# of Answers]:[# of Answers], [Business Unit]:[Business Unit], Business#)

まとめ

COUNTIF関数、ヘルパー列、SUMIF関数などを組み合わせて、1つの列からの選択と複数の列にわたる回答の数に基づいてロールアップすることができます。この方法は、複数のビジネスユニットからフィードバックを収集する場合や、回答を重要度に基づいて分析する場合に役立ちます。

ポイント

集計基準となるビジネスユニット列に同じ値が複数存在する場合は、ヘルパー列を使用して各行の回答をカウントしておく必要があります。
ヘルパー列は、SUM関数を使用して、選択した回答に基づき数をカウントします。
列名にスペースや数字が含まれる場合は、角括弧で囲みます。

やり取りの翻訳

Kペルツェル ✭✭
01/18/24

48 の異なるアプリケーションに関して 6 つの異なるビジネスユニットからフィードバックを得るフォームを作成しました。参加者は、単一の選択ドロップダウンから自分のビジネスユニット名を選択します。次に、各アプリケーションを「自分自身またはチームで使用していない」、「よくわからない」、「重要ではない」、「少し重要」、「やや重要」、「かなり重要」、「非常に重要」の順にランク付けします。もう一度ドロップダウンを 1 つ選択します。

フォームがフィードされるシートは次のようになります。

各事業部門が重要度に基づいてどれくらいの頻度で何かを選択しているかをまとめたいと思います。

現在、「重要性」の回答をアプリケーション (48 列) ごとに集計していますが、事業単位に基づいた高レベルのロールアップが必要です。私のテーブルはこんな感じです。

いくつかのバリエーションの数式を試しましたが、解析できないか、間違った引数セットが返されます。私が試した最新のものは次のとおりですが、エラーは解析できません。

=COUNTIFS({BusinessUnit}, “ビジネスユニット 1”, {AllSystemColumns}, HAS(@row, “非常に重要”))

{AllSystemColumns} は、チェックする 48 列の名前範囲です (上記の例では、App 1、App 2、App 3、App 4、App 5 になります)。

@cell または @row (行全体を検索するため) を試しましたが、どちらも機能しません。ご協力をいただければ幸いです。

質問と回答のやり取り

jmyzk_cloudsmart_jp ✭✭✭✭✭✭
01/19/24 編集 01/19/24
- こんにちは@KPeltzel
  まず、COLLECTを使用して範囲をフィルタリングしようとしました。 (私の場合はApp1:App5。)
  ただし、2 次元の範囲を行のように水平方向にフィルター処理する必要があるため、INDEX(MATCH()) によって望ましい結果が得られました。
  =COUNTIF(INDEX([アプリ 1]:[アプリ 5], MATCH(ビジネス#, ビジネス:ビジネス)), [選択した回答]#)
  例: =COUNTIF(INDEX([アプリ 1]:[アプリ 5], MATCH(“ビジネスユニット 4”, ビジネス:ビジネス)), “重要ではない”)
  私のデモでは、ビジネスと回答の組み合わせ表を含む別のシートの代わりに、シート概要の 2 つのフィールドを使用して必要な数を表示します。ただし、式の構造は同じである必要があります。 (以下のリンクにアクセスし、ビジネスと回答を変更してデモシートの動作を確認してください。
- (「重要ではない」に対してのみ条件付き書式を作成したため、同じ画像を表示するには「重要ではない」を選択してください)
Kペルツェル ✭✭
01/19/24 2024 年 1 月 22 日編集
- @jmyzk_cloudsmart_jp
  これは部分的にはうまくいきました。
  メトリクスシートはありますが、これが機能するかどうかを確認するためにシートの概要から始めました。 =COUNTIF([システム 1]:[システム 3], “重要ではありません”) で全体の数を取得できます。
  INDEX と MATCH を入力すると、「解析不能」になります。 =COUNTIF(INDEX([システム 1]:[システム 3], MATCH(“BU 2”, ビジネスユニット:ビジネスユニット)), “重要ではない”)
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
2024/01/21 2024/01/21編集
- こんにちは @KPeltzel
  列名にスペースや数字が含まれる場合は、それらを角括弧で囲みます。
  =COUNTIF(INDEX([システム 1]:[システム 3], MATCH(“BU 2”,ビジネスユニット:ビジネスユニット））、 “重要じゃない”）
  =COUNTIF(INDEX([システム 1]:[システム 3], MATCH(“BU 2”, [ビジネスユニット]:[ビジネスユニット]））、 “重要じゃない”）
  ちなみに、Number of Surveys フィールドには COUNT を使用する必要があります。
  =SUM([行ID]:[行ID]) vs =COUNT([行ID]:[行ID])
Kペルツェル ✭✭
2024/01/22 2024 年 1 月 22 日編集
- @jmyzk_cloudsmart_jp とても近いです！まず、括弧と列名についての説明をありがとうございます。とても助かりました。また、SUMではなくCOUNTです。
  数式は機能していますが、すべての入力/列がカウントされていません。 47 列にわたってカウントしていますが、カウントできる数に制限はありますか? 35 だけが返されますが、「重要ではない」の出現をすべてカウントすると 304 になるため、何かが欠けています。何かご意見は？ 3 つの列にわたる下書きテーブルで作業する場合は機能しますが、さらに多くの列を合計しようとすると問題が発生するようです。
  テーブル上で 47 列すべてが強調表示され、インデックスが作成されているように見えますが、返される合計は正しくありません。
  =COUNTIFS(INDEX([システム 1]:[システム 47], MATCH(“BU 1”, [ビジネスユニット]:[ビジネスユニット])), “重要ではない”)
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
01/23/24
- こんにちは@KPeltzel
  ビジネスユニット列に「BU 1」のような同じビジネスユニットが複数あるとします。この場合、MATCH() 関数は指定されたビジネスユニットが最初に出現する行番号を返すため、数式は機能しません。
  「BU 1」選択のすべての「重要ではない」回答をカウントする必要がある場合は、各行の回答を追加するヘルパー列を追加します。
- 次に、選択したビジネスユニットに一致するヘルパー列の値を合計します。
  =SUMIFS([回答数]:[回答数], [ビジネスユニット]:[ビジネスユニット], ビジネス#)
- https://app.smartsheet.com/b/publish?EQBCT=eb5bcc7c9d2d4c508d75d5a419ea0a93
Kペルツェル ✭✭
24/01/24
- @jmyzk_cloudsmart_jp それが私に欠けていたものです！複数の事業部門がこれらの質問に答えました。 1 分かかりましたが、最終的にシートの概要を理解して、メトリックシートの式を再作成することができました。手伝ってくれてどうもありがとう！
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
24/01/24
- @KPeltzel シートの概要で問題を解決し、数式を再作成したと聞いてうれしく思います。😁