#今回やりたい事
エクセルではピボットテーブルを使って簡単にクロス集計ができますね。
残念ながらPowerAppsにはそのような機能がまだありません。
ですから関数を組み合わせてやってみます。
例えば下図の様な簡単なテーブルをクロス集計してみます。
実際にボタンを一つ追加してその「OnSelect」属性に下記のコードをコピペして、Altキーを押しながらそのボタンをクリックします。
ClearCollect(テーブル,{商品:"商品A",売上:500,月:5},{商品:"商品B",売上:300,月:6},{商品:"商品B",売上:700,月:7},{商品:"商品A",売上:1000,月:6})
すると上図の様な「テーブル」という名のコレクションが作くられます。
これを商品を各行にして、各月の売上を各列にしたクロス集計をします。
#データテーブルにクロス集計結果を表示
データテーブルを追加してその「Items」属性に下記のコードをコピペします。
AddColumns(
GroupBy(テーブル,"商品","その他")
,"５月",Sum(Filter(その他,月=5 And 商品=商品),売上)
,"６月",Sum(Filter(その他,月=6 And 商品=商品),売上)
,"７月",Sum(Filter(その他,月=7 And 商品=商品),売上)
)
すると下図の様な結果を得られます。
#「GroupBy」関数を理解する。
ネストされたPowerApps関数を理解するには内側の関数から外の関数へと見ていくのが基本です。
このネストの内側の関数は「GroupBy」関数ですね。
「GroupBy」関数はMSの公式ページを読んでも良く分かりません。
参照の為にそのリンクを貼っておきます。
「GroupBy」関数は単独で使われる事はほとんどありません。
今回の例の様にデータをグループ化をする構造を作ります。
参考のために下記のコードでコレクションを作り、その状態がその下の画像の様になります。
ClearCollect(テーブル２,GroupBy(テーブル,"商品","その他"))
GroupBy(テーブル,"商品","その他")
第一引数で集計をするテーブルを指定します。
この例では「テーブル」という名のコレクションです。
第二引数でグループ化をする列名を指定します。
この例では「商品」列です。
実はこの列は複数列を指定する事が可能です。
「,（カンマ）」に続けて列名を追加していきます。
最後の引数に指定していない列を全てまとめる新たな列名を任意で指定します。
今回は「その他」にしました。
この列名は全て半角のダブルクォーテーションで挟みます。
上のコレクションの画像の様に「商品」列だけ重複無しの商品名が表示されています。
そして「その他」列が追加されているのが分かります。
各行にテーブルの形をしたアイコンが表示されています。
そのアイコンをクリックしたら下図の新たなサブテーブルが表示されます。
これは「商品A」の他の列のデータがまとめられたのを意味します。
ここまでが「GroupBy」関数の働きです。
#「AddColumns」関数を理解する。
「GroupBy」関数の外側にある関数が「AddColumns」関数ですね。
「AddColumns」関数は色んなところで使いますが、基本は対象テーブル内のデータを使い計算結果を出す列を追加する事ができます。
今回の例では各商品の毎月の売上の合計を集計した列を追加しています。
ここで一つ問題なのはエクセルのピボットテーブルは元データによって動的に列名も含めて列数を増減してくれますが、
今回のやり方では予め列名を指定しなければいけません。
ですので最初のコレクションを作る時にこれを意識してコレクションを作る必要があります。
今回はそのやり方は割愛します。
AddColumns(テーブル, "任意の列名１", 計算式１ [, "任意の列名２", 計算式２, ... ] )
これが「AddColumns」関数の構造です。
第一引数に対象となるテーブルを指定します。
第二引数に追加する列の列名です。これは単なる名前なので文字列で、つまり半角のダブルクォーテーションで挟みます。
第三引数が出したい結果の計算式を入れます。
複数の列を追加したい時は上の第二、第三引数を繰返します。
#「GroupBy」と「AddColumns」関数のネストでクロス集計をする。
もう一度データテーブルの「Items」属性のコードを見てみましょう。
AddColumns(
GroupBy(テーブル,"商品","その他")
,"５月",Sum(Filter(その他,月=5 And 商品=商品),売上)
,"６月",Sum(Filter(その他,月=6 And 商品=商品),売上)
,"７月",Sum(Filter(その他,月=7 And 商品=商品),売上)
)
今回は単純に「５月」、「６月」、「７月」の結果だけを３列で出しています。
この列名に変数を使えば動的にもできそうですが、今回はやりません。
「５月」の列の追加の構造を見ましょう。
追加する列名は「５月」ですね。
計算式の引数を見ると「Filter」関数を使って条件で絞ったテーブルの「売上」列の値を「Sum」関数で合計しています。
ここのミソは対象テーブルが「その他」テーブルである事です。
これは「GroupBy」関数の最後の引数で作った列名です。
これが新たなテーブルになっているのです。
もう一度そのサブテーブルの画像を見ると「売上」と「月」列があります。そしてその外に「商品」列もあります。
ですから「Filter」関数で「月」列が「5」であり、「商品」列が各商品である全行の「売上」列の値の合計を「５月」列に表示しています。
同じ事を「６月」と「７月」列でやっているわけです。
#数値でなく文字列を表示したい時にどうするか？
クロス集計をする時は対象（今回の例では「売上」）は必ず数値なので「Sum」関数を使いました。
しかし、クロス集計ではなく、縦のデータを横に並べ直す、つまり行と列を入れ替えたい時もあります。
エクセルVBAでは「Transpose」関数があります。
PowerAppsでは残念ながらその機能もまだありませんが、似たような事も今回のやり方でできます。
しかし、その値が文字列であれば「Sum」関数は使えません。
その場合は「LookUp」関数を使い、次のコードになります。
AddColumns(
GroupBy(テーブル,"商品","All")
,"５月",LookUp(All,月=5 And 商品=商品,売上)
,"６月",LookUp(All,月=6 And 商品=商品,売上)
,"７月",LookUp(All,月=7 And 商品=商品,売上)
)
ここでの注意点は対象のテーブルに同じ「月」と同じ「商品」で複数の行を持っていてはいけません。
「LookUp」関数は複数の行が条件に合う時は最初（一番上）の行だけを返すからです。
ご覧の様に今回のやり方は色んな制約がありますが、PowerAppsでもクロス集計ができます。
しかし、やっぱりPowerAppsでピボットテーブルみたいな機能が早く実装されるを期待しちゃいます。