使い方

PowerApps│集計とグラフ化もお手のもの！コレクションの活用方法

アイキャッチ

こんにちは、あんこ先生です。

PowerAppsでExcelのような集計やグラフ化をしてみませんか？

とはいえ、アプリで集計するにはコードが複雑化するし、委任問題もあって使いにくいイメージがあります。

ところが、データソースをコレクションに落とし込み、加工すればExcelのような集計やグラフ化がかんたんにできるんです。

さらに、コレクションは委任問題を完全に回避できる優れものです。

そこで、今回は「複数テーブルを結合して集計したい」、「きれいな棒グラフを作りたい」といった方を対象に、集計とグラフ化に特化したコレクションの活用方法を紹介します。

この記事を読めば、コレクションの加工方法を深く理解でき、望んだ集計ができるようになります。

いつも通り、コピペで使えるサンプルコードも載せていますので安心してください。


テーブルの仕組みを理解している前提の記事ですので、自信のない方は先にこの記事をご覧ください。

関連記事PowerApps│もう迷わない！テーブルとレコードの違いと使い方
できること
  1. 計算項目を追加して集計する
  2. グラフに展開し可視化する
  3. グループ化して集計する

集計とグラフ化のためのコレクション活用方法

今回は、サンプルとして商品の仕入と売上データを用意して集計します。

本来はSharePointリストを用意した方がより実践向けなのですが、コレクションで代用しています。


コレクションの事前準備

_M商品商品情報をとりまとめたマスターテーブルです。

サンプルでは５品目を登録しており、商品CD・商品名・仕入と販売単価で構成されています。

_T入出荷商品入出荷記録のトランザクションテーブルです。

ランダムで１０日分作成します。日付・商品CD・入荷と出荷数で構成されています。

それぞれ適当なボタンに次のコードを埋め込みます。

また、スライダーコントロールを１つ用意し、Defaultプロパティに変数_countを設定しておきます。

_T入出荷は、１クリックごとに変数_countが増加し、１日分のデータが生成されます。

_M商品

//商品マスター作成とコレクションや変数初期化
ClearCollect(_M商品,
    {商品CD:101,商品名:"いちご",仕入単価:100,販売単価:250},
    {商品CD:102,商品名:"りんご",仕入単価:120,販売単価:150},
    {商品CD:103,商品名:"バナナ",仕入単価:210,販売単価:350},
    {商品CD:104,商品名:"ぶどう",仕入単価:85,販売単価:160},
    {商品CD:105,商品名:"パイナップル",仕入単価:230,販売単価:320}
);

Clear(_T入出荷);
Set(_count,0)

_T入出荷

If(_count=0,Clear(_T入出荷));

ForAll(_M商品,
    Collect(_T入出荷,
        {
            日付:Today()+Slider1.Value,
            商品CD:ThisRecord.商品CD,
            入荷数:RoundDown(Rand()*5,0),
            出荷数:RoundDown(Rand()*5,0)
        }
    )
);

Set(_count,_count+1)

コレクションから商品別入出荷数を求める

期間中に商品をいくつ仕入れていくつ売り上げたかを集計します。

ギャラリーコントロールのデータソースは_M商品を指定し、棒グラフに適した横型を配置します。


期間中の経過日数

_T入出荷に記録された日付の最小値と最大値の差に１を足します。

最小値はMin関数、最大値はMax関数を使います。

経過日数はDateDiff関数を使います。引き算でも構いません。

DateDiff関数は同日だと0を返すため、1を足しています。

適当なラベルコントロールに次のコードを埋め込みます。

DateDiff(Min(_T入出荷,日付),Max(_T入出荷,日付))+1&" 日間の集計"


商品別の累計値

商品別に入荷と出荷の累計値を算出します。

グラフでいうラベル部分ですね。

_T入出荷の記録から_M商品の商品CDでフィルターし、その結果をSum関数で合計します。

ここも適当なラベルコントロールに次のコードを埋め込みます。

上段が入荷用、下段が出荷用です。

Sum(Filter(_T入出荷,商品CD=ThisItem.商品CD),入荷数)
Sum(Filter(_T入出荷,商品CD=ThisItem.商品CD),出荷数)


グラフの表示

今度はグラフをギャラリーコントロールと図形で作成します。

そのために、図形の高さとＹ座標を指定します。

まず、高さは累計値と同じ求め方に重みを乗じます。

今回は5を設定していますが、枠からはみ出るようであれば少なくします。

次に、Ｙ座標はグラフの下底（灰色部分）のＹ座標から自身の高さを引いて求めます。

入荷と出荷は色を変えるとわかりやすくなりますね。

Heightプロパティ

Sum(Filter(_T入出荷,商品CD=ThisItem.商品CD),入荷数)*5

Yプロパティ

GG_Line.Y-Self.Height  //下底（GG_Line）

以上で完成です。そこそこ見栄えの良いものができたと思います。

２つのコレクションを結合し、利益を求める

今度は日々の商品別売上高から仕入高を差し引いて、利益を求めてみます。


日々の商品別売上と仕入の項目追加

数量は_T入出荷、単価は_M商品にあるので、それらを参照させて_T利益を作成します。

ベースは_T入出荷とし、利益の算出元となる仕入高と売上高の項目を追加します。

項目の追加はAddColumns関数を使います。

この辺の詳しい使い方はこの記事をご覧ください。

関連記事PowerApps│複写！抽出！追加！結合！コレクションの加工方法

とりあえず、新しいボタンコントロールに次のコードを埋め込んでクリックします。

//仕入高、売上高の項目を追加
ClearCollect(_T利益,
    AddColumns(_T入出荷,
        "仕入",LookUp(_M商品,商品CD=ThisRecord.商品CD,仕入単価*入荷数),
        "売上",LookUp(_M商品,商品CD=ThisRecord.商品CD,販売単価*出荷数)
        )
)

利益を求める際は、売上から仕入を差し引けば求められますね。

最初から利益の項目を追加してもよいと思います。


一時的に項目を増やす

利益の項目を後から追加してみます。

一度しか使わないと仮定し、コレクションには追加しない方法を試します。

そのギャラリーコントロールにしか使わないなど用途が限られている場合は、Itemsプロパティに次の項目を埋め込みます。

AddColumns(_T利益,
    "利益",ThisRecord.売上-ThisRecord.仕入
)

これでそのギャラリーコントロールでのみ利益の項目が利用できるようになりました。

あとはテンプレート部分の適当なラベルコントロールにThisItem.利益と記述します。

一応、AllItemsプロパティを使えば、他コントロールでも利用できます。

テーブルを入れ子にして集計する


テーブルの入れ子

入れ子とは、プログラミングにおいては、あるモノの中にそれと同じモノを入れた構造です。

ネストともいいます。今回の場合は、テーブルの中にテーブルを格納することです。

テーブルを入れ子にすると、例えば期間内の商品別集計が可能になります。


商品別に期間累計を集計

テーブルを入れ子にするにはGroupBy関数を使用します。

GroupBy関数は、1つ以上の列の値に基づいて、条件に合致したレコードをグループ化したテーブルを返します。

GroupBy(元テーブル,グループ化する列名,戻り値を格納する列名)

では実際にテーブルを入れ子にしてみましょう。

まずギャラリーコントロールのItemsプロパティに次のコードを埋め込みます。

GroupBy(_T利益,"商品CD","日別集計")

次に、テンプレートのラベルコントロールに次のコードを埋め込みます。

Sum(ThisItem.日別集計,売上)  //仕入も同様
Sum(ThisItem.日別集計,売上)-Sum(ThisItem.日別集計,仕入)  //利益用

今回は利益の項目を追加していないので、引き算で求めています。

入れ子で日別の明細を表示する


ギャラリーコントロールを入れ子にする

入れ子となったテーブルデータを一覧表示するには、ギャラリーコントロールを入れ子にすることで実現できます。

今回の場合、縦方向ギャラリーコントロールに_T利益、入れ子となった横方向ギャラリーコントロールに日別集計を表示させています。

具体的なコードは次の通りです。

Itemsプロパティ

GroupBy(_T利益,"日付","日別集計")

Labelxx.Text

日別利益合計
Text(Sum(ThisItem.日別集計,売上-仕入),"￥#,###")

Itemsプロパティ

//金額0は表示しない
Filter(ThisItem.日別集計,仕入||売上)

Labelxx.Text

ThisItem.仕入  //日別仕入明細
ThisItem.売上  //日別売上明細

その他活用方法


ギャラリーコントロールで選択したところのみグラフ化する

１日当たりの明細をグラフ化したい場合です。

これは１日ごとに集計されたコレクションを表示しているGalleryxx.Selectedを指定すれば容易に表示できます。

これだけではつまらないので、コレクションの加工を行わない、つまり_T入出荷を指定した場合の仕入や売上の算出方法を紹介します。

この場合だと_T入出荷は単価を持ち合わせていないので、_M商品から引っ張ってくる必要があります。

LookUp(_M商品,商品CD=ThisItem.商品CD,ThisItem.入荷数*仕入単価)/25

仕入だけでも記述が長くてうんざりしますね。

コレクションの加工済みならこの短さです。

ThisItem.仕入/25

どちらが見やすく、負担が軽いか明白ですよね。


売上高の推移

日付順に売上高が表示されて、その推移がよくわかる人気の棒グラフです。

売上高の推移を表示させるためには、日付を基準にGroupBy関数でグループ化します。

Sum関数でこのグループを指定すれば日別の全商品売上合計が集計できます。

こんなかんじで、ギャラリーコントロールのItemsプロパティに次のコードを埋め込みます。

GroupBy(_T利益,"日付","日別集計")

あとはテンプレートに図形を加えて、棒グラフをこさえていきます。

Heightプロパティ

Sum(ThisItem.日別集計,売上)/50

Yプロパティ

GG_Line.Y-Self.Height  //下底（GG_Line）

まとめ

今回は、コレクションの活用方法について紹介しました。

本来、集計やグラフ化はPowerBIやExcelの出番なんですが、PowerAppsでもある程度は作れることがわかりましたね。

コレクションを加工することで、その後の集計やコード記述が楽になります。

また、入れ子にすることで特定グループの累計をかんたんに算出できますし、グラフ化の幅も広がりましたね。

ぜひ色々と集計やグラフ化を試してみてください。

ご支援よろしくおねがいします

この記事を気に入ってくださった方、寄付してあげてもいいよという方がいらっしゃっいましたら、ご支援いただけると助かります。

下記リンクからAmazonギフト券で、金額は15円からお気持ちを入力してください。

受取人のメールアドレスは amazon@anko7793.com までお願いします。

メッセージもいただけるとたいへん喜びます。

よろしくお願いします。

Amazonギフト券(Eメールタイプ) 

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

ABOUT US
七草あんこ
非IT系中間管理職やってます。社命によりoffice365を主軸とした業務改善プロジェクトメンバーに任命されたことでPowerAppsと出会えました。いまではビジネス・プライベートを問わず、欠かせないツールになっています。導入初期やアプリ作成時に遭遇した諸問題の解決法とサンプルアプリの作り方を紹介していきます。主にTwitterで情報収集しているので不明点など呟いているとお邪魔するかもしれません。