AppSheetの関数については、公式サイトにも丁寧な説明があります。
一方、関数は1つの関数だけではく、複数の関数を組み合わせることが多いです。特に、FilterやselectなどのList型を返す関数との組み合わせは、多岐にわたります。
ここでは、私が開発する際によく使う組み合わせ（イディオム）をご紹介したいと思います。
- SELECT() – 選択した行の列値のリスト。
- ANY()- リスト内の任意の項目 1 つ。
- TOP()- リストの初期項目のリスト。
- SORT()- 順番に並べられたアイテムのリスト。
- ORDERBY()- カスタム順序の行のリスト。
- UNIQUE()- 重複が削除されたアイテムのリスト。
- COUNT()- リスト項目の数。
- AVERAGE()- 数値リストの算術平均。
- IN()- アイテムはリスト内にありますか?
- INDEX()- リスト内の特定の項目。
- INTERSECT()- 2 つのリストに共通する項目のリスト。
- ISBLANK()- リストに項目がありませんか?
- ISNOTBLANK()- リストに項目はありますか?
- MAX()- 数値または時間値のリストの最高値。
- MIN()- 数値または時間値のリストの最小値。
- SUM()- 数値リストの算術合計。
SELECT() /FILTER() /REF_ROWS()- 選択した行の列値のリスト。
最初はSELECT() /FILTER() /REF_ROWS()です。SELECTが一番使っているかもしれません。
ただ、記載していると記事量が多くなったため、別記事に出しにしました。以下を参照頂ければと思います。
ANY()- リスト内の任意の項目 1 つ
リスト（List型）から任意の 1 項目を取得して返します。
例えば、
- ANY(LIST(1, 2, 3)) は、「1」 (数値) を返します
- ANY({“赤”, “黄”, “緑”}) は、「赤」 (テキスト) を返します
注意点として、「任意」のためどの値が返されるかは指定できません。
そのため、実利用としてはFilterやSelectで主キー（Key項目）を指定し、１つしか値がない前提で、anyでList型→項目にすることが多いです。
使用例
ANY(Students[Name]) Students テーブルの Name 列から任意の値を返します。
Student[Name] 列リスト内の値の順序は、SORT関数 （後述）でラップしない限り保証されません。
TOP()- リストの初期項目のリスト。
リストの先頭にある指定した数のアイテムを含むリストを返します。指定された数がリストの長さより大きい場合はリスト全体を返します。指定された数が 1 未満の場合は空のリストを返します。
- TOP(LIST(“Red”, “Yellow”, “Green”), 2) → {Red, Yellow}
- TOP({“Red”, “Yellow”, “Green”}, 4) → {Red, Yellow, Green}
使用例
OrdersテーブルをCustomerIDごとにグループ化し、注文額の合計でトップ顧客を取得します。
TOP(GROUPBY(Orders, "CustomerID", SUM(Orders[OrderAmount])), 1)または、現在の行のTopCount列の値を使用して、取得する要素数を決定し、画面の明細表示量を、可変にしたりできます。
TOP([Products], [_THISROW].[TopCount])SORT()- 順番に並べられたアイテムのリスト。
リストを昇順または降順に並べ替えるために使用されます。リストのデータを特定の基準で並べ替える際に非常に便利です。
例えば、
- SORT([Products]) → Products列のリストを昇順に並べ替えます
- SORT([Scores], FALSE) → Scores列を降順に並べ替えます
使用例
実務では、カスタムフィールドによる並べ替え（並べ替えの基準を特定の列や式に基づいて設定）を行うことが多いです。
ProductsテーブルのProductNameを、Price列を基準に昇順で並べ替えます。
SORT(Products[ProductName], TRUE, Products[Price])ORDERBY()- カスタム順序の行のリスト。
リストを特定の条件に基づいて並べ替えるために使用します。並べ替えの基準を複数指定できるため、柔軟にリストをソートできます。他の関数よりも直感的に並べ替え条件を指定できる点が特徴です。
ORDERBY(リスト, 並べ替え条件1, 並べ替え順1, 並べ替え条件2, 並べ替え順2, …)
私は帳票の連番作成でよく使います。
例えば、
- ORDERBY(Products[ProductID], [Price], “ASC”) → ProductsテーブルのProductIDをリストとして使用し、Price列を基準に昇順で並べ替えます。
- ORDERBY(Products[ProductID], [Category], “ASC”, [Price], “DESC”) → Category列で昇順、さらにPrice列で降順に並べ替えます。
使用例
価格帯が近い商品を表示したい場合、ABS関数と組合せて、現在の行のTargetPriceに近い順にPrice列を並べ替えます。
あとはTOP関数で必要な数を指定します。
ORDERBY(Products[ProductID], ABS([Price] - [_THISROW].[TargetPrice]), "ASC")
UNIQUE()- 重複が削除されたアイテムのリスト。
重複を削除した元の順序の項目のリストを返します。リスト内の重複した要素を削除し、ユニークな値だけを保持するために使用します。テーブルの特定の列やリストの中から一意のデータを取得したい場合に便利です。
例えば、
- UNIQUE(LIST(1, 1, 2, 1, 3)) → {1, 2, 3}
- UNIQUE(LIST(“Mary”, “David”, “Joe”, “David”)) → {Mary, David, Joe}
- UNIQUE(Products[Color]) → 重複のない製品カラーのリスト
使用例
タスク一覧の中から、ステータスが未完了のタスクの担当者一覧を表示する場合などに使えます。
UNIQUE(SELECT(tasks[assigned], [Status] <> "Completed"))COUNT()- リスト項目の数
リスト（List型）の項目数を返します。
例えば、
- COUNT(LIST(“Red”, “Yellow”, “Green”)) → 3を返します。
- COUNT(LIST()) → 0を返します。
- COUNT(LIST(“”)) → 1を返します。
使用例
Color 列の値が含まれる Products テーブルの Color 列内の赤かオレンジのどちらかの合計数を数えます。
またCOUNTは条件式に使うことが多く、０以上（値が存在する）判定に使うことが多いです。。
COUNT(
SELECT(Products[Color], IN([Color], {"Red", "Orange"}))
) AVERAGE()- 数値リストの算術平均
リスト（List型）の平均値を返します。
例えば、
- AVERAGE(LIST(1, 2, 3)) : 「2.00」になります。
- AVERAGE(Products[Price]) : Products テーブルの Price 列のすべての値の平均を算出します。
IN()- アイテムはリスト内にありますか?
INは(“探し物”,”探す場所”)を指定し、その結果をYes/No を返します。その際、大文字と小文字は区別されない点は注意が必要です。
例えば、
- IN(“a”, {“a”, “b”, “c”}) → TRUE
- IN(“bc”, {“a”, “b”, “c”}) → FALSE
- IN(“d”, {“a”, “b”, “c”}) → FALSE
- IN(“Red”, {“I’m bored!”}) → TRUE
検索ターゲットが検索値の少なくとも 1 つの項目に一致する場合は TRUEとなり、検索対象が検索値の項目に一致しない場合は FALSEになります。
使用例
IN関数を使うことで、表示している画面タイプの判定ができます。
IN(
CONTEXT("ViewType"),
{"Deck", "Gallery", "Table"}
) 現在表示されているビューのタイプは Deck、Gallery、または Table のどれかに該当する場合は、TRUEが返されます。
INDEX()- リスト内の特定の項目
インデックス値に基づいてリスト内の項目の値を返します。項目がリスト外にある場合は空白を返します。
例えば、
INDEX(LIST("Red", "Yellow", "Green"), 2)→YellowINDEX({"Red", "Yellow", "Green"}, 4)→ 空白を返します（4 はリスト外です）。
使用例
本日の曜日を表示する例です。
INDEX(
{
"日曜日", "月曜日", "火曜日",
"水曜日", "木曜日", "金曜日",
"土曜日"
},
WEEKDAY(TODAY())
)INTERSECT()- 2 つのリストに共通する項目のリスト
2 つのリストに共通する項目のリストを返します。
例えば、
- INTERSECT(LIST(“Red”, “Blue”, “Green”), LIST(“Orange”, “Blue”)) → LIST(“Blue “)
使用例
例えば、従業員のスキルセットとプロジェクトで必要なスキルを比較して、必要スキルが満たされているか確認する、などに使えます。
データ構造が以下の場合に、
- 従業員のスキルリスト: [EmployeeSkills]
- プロジェクト必要スキル: [RequiredSkills]
INTERSECT()で従業員のスキルと必要スキルの共通部分を取得します。
IF(COUNT(INTERSECT([EmployeeSkills], [RequiredSkills])) = COUNT([RequiredSkills]), "Qualified", "Not Qualified")これにより、共通部分の数が必要スキルの数と一致すれば「Qualified」、そうでなければ「Not Qualified」と表示させられます。
ISBLANK()- リストに項目がありませんか?
指定された項目に値がない場合(空のリストも含む)は TRUE、 指定された項目に値がある場合は FALSEを返します。
例えば、
- ISBLANK(“”) → TRUE
- NOT(ISBLANK(“”)) → FALSE
- ISBLANK(“Hi!”) → FALSE
- ISBLANK(0) → FALSE
- ISBLANK(LIST()) → TRUE
- ISBLANK(LIST(“Red”, “Yellow”, “Green”)) → FALSE
使用例
例えば、顧客(Customers)テーブルで、住所をもっており場合、特定の都市に住んでいる顧客がいないかを判定できます。
ISBLANK(FILTER("Customers", ([City] = "中央区")))ISNOTBLANK()- リストに項目はありますか?
これは、上記のISBLANK()の逆になります。
MAX()- 数値または時間値のリストの最高値。
リスト内の最大値を返します。 list が空の値のリストの場合、0 を返します。
例えば、
- MAX(LIST(1, 2, 3)) → 3
- MAX(Products[Price]) → 商品の最高額を返します
使用例
MAX関数は、数値や日付データの最大値を効率的に取得するのに便利です。条件付きでの抽出や、他の関数と組み合わせることでさらに柔軟なデータ操作が可能です。
特定のカテゴリに属する商品の中で最大売上を取得したい場合。
データ構造:
- カテゴリ列:
[Category] - 売上列:
[Sales] - 条件付きで取得するカテゴリ:
"Electronics"
SELECT()で、Categoryが”Electronics”に該当する売上だけを抽出し、
その中の最大値をMAX()で取得します。
MAX(SELECT(Sales[Sales], [Category] = "Electronics"))MIN()- 数値または時間値のリストの最小値。
MAX関数の逆で、リストの最小値を取得します。
SUM()- 数値リストの算術合計。
リスト内の値の合計を返します。
例えば、
- SUM(LIST(1, 2, 3)) → 6
使用例
特定の期間内に行われた配送（Deliveries）の合計配送料金（DeliveryCharge）を計算する例です。
- Deliveriesテーブルから、DateDoneがBeginDate以上かつEndDate未満である行を探します。
- その行のDeliveryCharge（配送料金）列の値を取得し、リスト化します。
- そのリスト内の数値をSUM()で合計します。
- 結果として、指定された期間内の配送料金の合計が得られます。
SUM(
SELECT(
Deliveries[DeliveryCharge],
AND(
([DateDone] >= [_THISROW].[BeginDate]),
([DateDone] < [_THISROW].[EndDate])
)
)
)まとめ
いかがでしょうか？
AppSheetの開発の参考になれば幸いです。
コメントを残す