業務改善と標準化を同時に実現:MS 365業務システム開発

【PowerApps 】 GroupByとAddColumns関数でピボットテーブルみたいな集計をする

  
【PowerApps 】 GroupByとAddColumns関数でピボットテーブルみたいな集計をする
\ この記事を共有 /
【PowerApps 】 GroupByとAddColumns関数でピボ...

PowerAppsでピボットテーブルみたいなクロス集計をする時にGroupBy関数¥とAddColumns関数を使います。そのクロス集計で文字列、個数、合計を求める仕方をステップバイステップで解説しています。

(動画時間:11:57)

 

PowerAsppsのGroupBy関数でできる事

 

こんにちは、リーンシグマ、ブラックベルトのマイク根上です。
業務改善コンサルをしています。

 

今日は久しぶりにPowerAppsの動画リクエストからです。

 

GroupBy関数 動画リクエスト

「チーム勤務表の作成方法について詳しく教えて頂きたいです。」

 

ちょっとお名前を言うのが照れちゃうんですが、
ねもねもにゃんにゃんさん、リクエストありがとうございました!

 

これは以前、PowerAppsで作った勤怠管理アプリを
ご紹介した動画でコメントを頂きました。
⇒「PowerApps で本格的な勤怠管理アプリを作る【テレワーク対策】」

 

勤怠管理アプリ チーム勤怠表画面

 

そのチーム勤務表の機能はPowerAppsのGroupBy関数、
AddColumns関数そしてShowColumns関数を使って作成をしています。
今日はそれらの関数をこの実例を使ってご説明します。

 

まずは、元データの状況を確認します。

 

使用データベース = SharePointリスト
リスト名 =「Tbl_Labor_Records」
「使用フィールド名」:データ型、機能=

  • 「Date」:日付型、出勤打刻をした日、
  • 「Employee1」:文字列型、従業員名
  • 「Department」:文字列型、所属部署
  • 「Work_Type」:文字列型、出勤形態が記録される
  • 「Hours_Worked 」:数値型、その日に働いた時間

 

勤怠管理アプリのDB

 

これはフラットなデータ形式です。
さっきの勤務表はエクセルだったら
ピボットテーブルとINDEX関数を組合せてできますが、
PowerAppsだとGroupByやAddColumns、
そしてShowColumns関数をネストして使います。
各関数の働きは分かり難いので
後で実例と共にご説明します。

 

今回は表の結果を文字列の出勤形態を出していますが、
ピボットテーブルみたいに個数や合計などを計算する
クロス集計もこの方法でできます。
それは後半でお見せします。それでは早速やってみましょう。

 

PowerAppsのAddColumns関数とShowColumns関数の使い方

 

勤怠管理アプリ チーム勤怠表画面

 

このチーム勤務表はデータテーブルコントロールを使っていて、
その下でその作成の再現をしてみます。

 

メニュバーの「挿入」から、「データテーブル」を追加して、
データソースとして「Tbl_Labor_Records」を選びます。

 

最初は全てのデータが表示されていますので
「Items」属性で表示するデータを絞ります。

 

左のドロップダウンには最近の各月曜日の日にちが並んでいて、
そして右のドロップダウンでは部署名が並んでいます。
それらで表示したい週と部署を選ぶのです。

 

その結果を表示するにはFilter関数が使えます。
変更後の数式がこれです:

 

データテーブルの「Items」属性=
Filter(Tbl_Labor_Records,

   Date>=DD_月曜日選択3.Selected.Value &&
   Date<=DD_月曜日選択3.Selected.Value +6 &&
   Department=DD_部署選択3.Selected.Result
)

 

上の式を説明しますと、
Date列の日付がドロップダウンで選択された日
(これはいつも月曜日です)から
6日後の日までの期間で絞ります。
つまり、一週間です。

 

そして「&&」の後にもう一つの条件入れて、
Department列を部署選択のドロップダウンで
選択された部署で絞るのです。
ドロップダウンで選択を変えると
表示データがうまく変わります。

 

数式バー内でカーソルを最後のカッコの外に置くと、
その下に下矢印が出てきます。

 

数式バーの下の下矢印

 

それをクリックすると選択可能な列が沢山あるのが分かります。
日付はありますが、しかし、必要な曜日がありません。
それにはAddColumns関数が使えます。
この関数で一時的に新たに列を追加でき、こうします:

 

データテーブルの「Items」属性=
AddColumns(

   Filter(Tbl_Labor_Records,
      Date>=DD_月曜日選択3.Selected.Value &&
      Date<=DD_月曜日選択3.Selected.Value +6 &&
      Department=DD_部署選択3.Selected.Result
   ),”曜日”,Text(Date,”ddd”)
)

 

第二引数に加えたい任意の列名で「”曜日”」にします。
この時点では文字列扱いですの
半角のダブルクォーテーションが必要です。

 

第三引数に表示させたい結果を求める数式が必要です。
日付を曜日に変換するのにText関数が使え、
「”ddd”」は3文字の曜日を表示する記号です。

 

また数式バーの下で確認すると
実際に曜日列が追加されているのが分かりますね。
ここには使わない列が沢山ありますので、
必要な列だけにすると後の作業がし易いです。
それにはShowColumns関数を使い、こうします:

 

データテーブルの「Items」属性=
ShowColumns(
   AddColumns(
      Filter(Tbl_Labor_Records,
         Date>=DD_月曜日選択3.Selected.Value &&
         Date<=DD_月曜日選択3.Selected.Value +6 &&
         Department=DD_部署選択3.Selected.Result
      ),”曜日”,Text(Date,”ddd”)
   ),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
)

 

データソースの後に使う列名を書き込むだけです。
このチーム勤務表で必要なのは、従業員名、曜日、
そして勤務形態のWork_Type列だけです。
この記事の最後にクロス集計もお見せしたいので
「Hours_Worked 」も入れておきます。
数式バーの下の結果を見ると
うまく行っているのが分かりますね。

 

数式バーの下の下矢印2

 

PowerAppsのGroupBy関数の役割と使い方

 

PowerAppsでクロス集計をする

 

チーム勤務表の完成形を見ると、
各従業員名は一行ずつ表示され、
残りのデータは横方向に並べたいです。
そこで従業員名を基にグループ化をするのです。
それにはGroupBy関数を使い、こうします:

 

データテーブルの「Items」属性=
GroupBy(

   ShowColumns(
      AddColumns(
         Filter(Tbl_Labor_Records,
            Date>=DD_月曜日選択3.Selected.Value &&
            Date<=DD_月曜日選択3.Selected.Value +6 &&
            Department=DD_部署選択3.Selected.Result
         ),”曜日”,Text(Date,”ddd”)
      ),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
   ),”Employee1″,”他”
)

 

 

第二引数に基にしたい列「“Employee1”」を選び、
残りは一まとめにし、その任意の名前を「“他”」としておきます。
数式バーで結果を見ると従業員は重複無しで表示され、
残りのデータがサブテーブルに入っています。
ここだと各サブテーブルの中身が見えません。

 

数式バーの下の下矢印3

 

そこで、この数式を全てコピーして、ボタンを一つ追加し、
その「OnSelect」属性に、Collect関数で
さっきの数式の結果を入れたコレクションを作成します。
Altキーを押しながらそのボタンを押すとコレクションが作成され、
こっちは「他」列にテーブルアイコンが出て、
それをクリックするとサブテーブルの中身が見えます。

 

コレクションでサブテーブルの中身を見る

 

従業員毎の残りのデータが全て入っているのが確認できます。
これがGroupBy関数の働きです。

 

GroupBy関数でグループ化したデータの使い方

 

しかし、この関数はデータの準備をするだけで、
基にした従業員名しか出てきません。
そこで月曜日から日曜日までの出勤形態を
表示する列を追加したいので、
またAddColumns関数を使い、こうします。

 

データテーブルの「Items」属性=
AddColumns(GroupBy(

   ShowColumns(
      AddColumns(
         Filter(Tbl_Labor_Records,
            Date>=DD_月曜日選択3.Selected.Value &&
            Date<=DD_月曜日選択3.Selected.Value +6 &&
            Department=DD_部署選択3.Selected.Result
         ),”曜日”,Text(Date,”ddd”)
      ),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
   ),”Employee1″,”他”
),”(月)”,LookUp(他,曜日=”月曜日”,Work_Type))

 

AddColumns関数を加えて、
第二引数の列名を「”(月)”」にします。
次の引数の式ですが、
もう一度コレクションの「他」サブテーブルを見ましょう。

 

コレクションでサブテーブルの中身を見る

 

この中の「曜日」列に「月曜日」がある勤務形態を持ってきたいので、
LookUp関数が使えます。
次がポイントですが、
LookUp関数のソースがサブテーブルの「他」になります。
そして条件として、「曜日」列に「月曜日」が入った
Work_Typeを持ってくるのです。

  LookUp(他,曜日=”月曜日”,Work_Type)

するとちゃんと「(月)」列の結果が出てきました。

 

数式バーの下の下矢印4

 

AddColumns関数には複数の列を追加できますので、
,”(月)”,LookUp(他,曜日=”月曜日”,Work_Type)
の部分の式をもう6つ分コピペします。
そして曜日だけ変更していきます。
これで全ての曜日の列のデータが用意できました。

 

最後にデータテーブルに表示する列を再指定するだけです。
まず、ツリービュー内で既定でできた列を
従業員名以外全て削除します。

 

データテーブルのプロパティ一覧内の
「フィールドの編集」と「フィールドの追加」をクリックして、
月から日までの列を追加します。
しかし、そのままだと曜日の順番がばらばらなので、
ちゃんと並べ替えをします。

 

「見出しのサイズ」属性で列名のサイズを変えたり、
従業員列を選んで、その「ヘッダーテキスト」属性を
「従業員」に変更すると見栄えも良くなります。
これで週を変更したり、部署を変更したら
ちゃんと従業員や勤務形態が変わって表示されました。
これで完成です。

 

GroupBy関数でクロス集計の仕方

 

GroupBy関数は最初は分かり難い概念ですが、
慣れるとすごく便利です。
例えば、今回の例では従業員毎のデータ数を数えたら
その週の打刻数を表示できます。
また、各従業員の労働時間の合計も出せます。
そのやり方もお見せしましょう。

 

データテーブルの「Items」属性=
AddColumns(
GroupBy(
   ShowColumns(
      AddColumns(
         Filter(Tbl_Labor_Records,
            Date>=DD_月曜日選択3.Selected.Value &&
            Date<=DD_月曜日選択3.Selected.Value +6 &&
            Department=DD_部署選択3.Selected.Result
         ),”曜日”,Text(Date,”ddd”)
      ),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
   ),”Employee1″,”他”
),”(月)”,LookUp(他,曜日=”月曜日”,Work_Type),”打刻数”,CountRows(他))

 

新しい列名を「打刻数」にして、
表示する結果の式にCountRows関数で
サブテーブルの「他」を指定します。
これで各従業員の打刻数が出てきます。

 

データテーブルの「Items」属性=
AddColumns(
GroupBy(
   ShowColumns(
      AddColumns(
         Filter(Tbl_Labor_Records,
            Date>=DD_月曜日選択3.Selected.Value &&
            Date<=DD_月曜日選択3.Selected.Value +6 &&
            Department=DD_部署選択3.Selected.Result
         ),”曜日”,Text(Date,”ddd”)
      ),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
   ),”Employee1″,”他”
),”(月)”,LookUp(他,曜日=”月曜日”,Work_Type),”打刻数”,CountRows(他),”労働時間”,Sum(“他”,Hours_Worked))

 

また、「労働時間」列を加えて、
Sum関数で、「他」と労働時間である
「Hours_Worked」を指定します。
これで各従業員のその週の労働時間を出せます。
それらの結果がこれです:

 

数式バーの下の下矢印5

 

この様にGroupBy関数とAddColumns関数を使えば
PowerAppsでもクロス集計ができるのです。

 

これによりアプリのレポート機能を強化できますので、
ぜひ使いこなして下さい。

 

  「こちらの記事も読まれてます

 

コメント

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