各店舗の売り上げを四半期ごとに集計する

オペレーション　活用編

予実管理では状況を様々な角度から比較・分析することが重要です。
今回のドリルでは、予算額と売上額のデータを四半期ごとに集計し、それぞれの店舗の状況を一目で見ることができる予実一覧表の作成方法を解説します。
データ集計にはSQLの知識が必要とされるケースも多々あるかと思いますが、Forguncyではコマンドを組み合わせることでSQLを使わず実現することも可能です。
本記事では、コマンドで実装する方法とSQLを使って実装する方法の両方をご紹介します。

1.プロジェクトを確認する

今回の演習で使用するプロジェクトを確認します。

手順1-1.テーブルを確認

ナビゲーションウィンドウのテーブル欄には「店別予実管理」、「wk_店別四半期集計」という2つのテーブルが存在します。

店別予実管理テーブルには、各店舗の予算額と実績額のデータが月単位で保管されています。

今回は既に予算と実績の情報がまとまっているテーブルが用意されていますが、実際の業務シーンでは予算と実績を別々のテーブルで管理しているケースもあると思います。

そういった場合にはこちらの記事で紹介している方法でデータを集約することができます。

本記事と合わせてご利用ください。

＞参考記事：日ごとに集計している実績データを月単位で集計し予実の一覧表を作る

wk_店別四半期集計テーブルは、SQLを使わずに集計をする際に使用するワークテーブル（一時テーブル）として用意しているテーブルです。

ワークテーブルは、集計処理を行って欲しい形でデータを得られるようにするため一時的にデータを入れておくためのテーブルです。

Forguncyにはワークテーブル機能はないため、このテーブルも本質的には通常のテーブルと相違ありません。

テーブルの構成は店舗名と年度、第1四半期～第4四半期の予算と実績のフィールドとなっています。

また、今回はあらかじめForguncyに内部テーブルを用意していますが、ExcelやCSVなどのファイルからデータを取り込んで使用することもできます。

＞参考記事：ExcelやCSVから予実のデータをインポートする

手順1-2.ページを確認

今回の演習には「店舗別予実一覧（四半期集計）」と「SQL使用_店舗別予実一覧（四半期集計）」という2つのページがあらかじめ用意されています。

実装前のプロジェクトファイルでは、この2つのページの違いは「表示」ボタンと「クリア」ボタンの有無だけです。

今回、SQLを使わないで実装する場合には、

1. 基となるデータを持つテーブルから集計処理を実行
2. そのデータをワークテーブルに書き込み
3. ワークテーブルのデータをリストビューで表示

という手順を採っています。
集計処理はデータの件数が増えると必要な時間も増加するため、処理の実行はボタンで行うように設定しています。
一方でSQLを使う場合は、都度ワークテーブルに集計データを仮置きしておく必要がありません。
そのため、これらのボタンは削除してあります。
また、それぞれのページの黒い太枠線はリストビューを配置する場所の目安です。

2.SQLを使わずに四半期ごとの集計をする

SQLを使わないで四半期ごとに予実状況を集計するには、コマンド機能を使用します。
今回は多数のコマンドを使用します。
それぞれの役割や設定手順は順を追って紹介しますが、大まかに言うと

1. ワークテーブルを空にする
2. 年度の選択を確認
3. 集計値を一旦入れておく箱（変数）の作成
4. 集計

の4ステップに分かれています。

手順2-1.ワークテーブルを空にする

集計処理を行う前に、ワークテーブルの中を空にしておく必要があります。
今回のアプリではワークテーブルのデータを一覧表示します。
そのため、集計処理の開始前に余計なデータが残ってしまっていると、一覧表にも不要なデータが表示されてしまいます。
それを防ぐために、実行ボタンを押下した際には必ずワークテーブルのクリア処理から始めるように設定します。

手順2-1-1.ワークテーブルのデータ件数を確認

ワークテーブルに存在するレコードの数だけ削除処理を行いたいので、まずは変数の設定コマンドを使用してワークテーブルのレコード数を取得します。
ページ上の表示ボタンを選択した状態で、右ペインの[セル型]タブにある[コマンド…]をクリックします。
コマンドウィンドウが開くので、新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は以下の通りです。

手順2-1-2.ワークテーブルのデータを削除する

ワークテーブルの中のデータを削除する処理を実装します。
レコードの数だけ削除処理を繰り返すよう、コマンドウィンドウの新しいコマンドボタンを押下し、繰り返しコマンドを追加します。
[繰り返しのルール]は「指定した回数分だけ繰り返し処理を行う」を選択し、[繰り返し回数、または繰り返し配列]には先ほど作成した変数「ワークテーブルのレコード数」を指定します。

繰り返しコマンドの子コマンドにデータの削除処理を実装します。
繰り返しコマンドの下の[コマンド未設定]を押下し、テーブルデータの更新コマンドを追加します。
[処理の種類]は「削除」を選択し、対象テーブルにはwk_店別四半期集計を設定します。
この時、[確認ダイアログを表示する]のチェックを外すのを忘れないようにしてください。
チェックが入ったままだとデータ削除のたびに確認メッセージが出てしまいます。

手順2-2.年度が選択されていることを確認する

ユーザーが選択した年度のデータのみを一覧表示できるよう、コンボボックスで年度を選択済みかどうか判定する処理を実装します。
もし年度が選択されていなかった場合はメッセージを表示し、全ての処理を中止します。

手順2-2-1.条件分岐コマンドの作成

コマンドウィンドウで新しいコマンドボタンを押下し条件分岐コマンドを追加します。
[If [条件式]]をクリックし、コンボボックスの中身の有無を条件とするよう、[条件式]タブで新しい条件ボタンを押下し下記のように設定します。

手順2-2-2.処理を中断するコマンドを設定

設定した条件に合致したらメッセージを表示し、全ての処理を中断するよう設定します。
コマンドウィンドウで指定した条件の下にある[コマンド未設定]をクリックし、メッセージの表示コマンドを追加します。 メッセージには「年度を選択してください。」と入力します。

その状態で、新しいコマンドボタンを押下しコマンドの強制終了コマンドを追加します。
条件分岐の子コマンドとなっていることを確認してください。

手順2-3.集計値を入れておく箱（変数）を作る

この先の処理で集計に使用する変数を作成します。
変数の作成はすべて変数の設定コマンドで行います。
この手順で作成するコマンドは、先に作成した条件分岐コマンドの子コマンドではないことに注意してください。

手順2-3-1.参照中のデータが1件目か否か判定する変数を作成

詳細は後述しますが、今回の処理では「参照中のデータの店舗名が1つ前のデータのものとは異なった際にワークテーブルにデータを書き込む」という方法を採用しています。
このため1件目のデータを参照している際、1つ前のデータは存在しないので特別な処理をする必要が生じます。
その処理を行うかどうか、条件分岐をするための変数を作成します。
コマンドウィンドウで新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のように行います。　

手順2-3-2.基となるテーブルのデータ件数を取得

集計を行う際、基となるテーブルである店別予実管理テーブルを参照します。
変数には複数のデータを入れておくことができます。
そのため、今回は店別予実管理テーブルの持つデータのうち、ユーザーが選択した年度に合致するものをこれから作成する変数に入れておきます。
コマンドウィンドウで新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のように行います。

クエリータブ

並べ替えタブ

手順2-3-3.店舗名を保持する変数を作成

前述のように、今回の処理では「今参照しているデータの店舗名」と「1つ前に見ていたデータの店舗名」を比較します。
そのため、「1つ前に見ていたデータの店舗名」を保持しておく変数を作成します。
コマンドウィンドウで新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

手順2-3-4.各四半期の予算と実績の集計値を保持する変数を作成

予算と実績は四半期ごと、つまり3か月ごとに集計します。
店別予実管理テーブルのデータは月ごとなので、集計するために値を入れておく変数を作成します。
予算と実績の2つを4つの四半期分キープしておきたいので、ここでは合計8つの変数を作成します。
この8つの変数は現時点では名前のみが異なるため、1つ作成したら残りはコマンドの複製で作るのがおすすめです。
コマンドウィンドウで新しいコマンドボタンを押下し変数の設定コマンドを追加します。

設定は下記のようにします。

今作成したコマンド「変数の設定：wk_第1四半期予算」を右クリックするとコマンドの複製が可能です。
複製機能を利用して、予算を入れる変数wk_第2四半期予算～wk_第4四半期予算の3つと実績を入れる変数wk_第1四半期実績～wk_第4四半期実績の4つの変数を作成します。

手順2-4.集計を行う

ここまでに作成した変数を使い、各四半期ごとに予算と実績の集計処理を行います。
集計処理の内容は、

1. 基となるデータを持つテーブルの件数分繰り返しを設定
2. 1件目のデータ参照時のみ店舗名を変数にセット
3. 店舗名が1件前のレコードと異なったらワークテーブルにデータを書き込み
4. 四半期ごとに予算と実績を変数に加算
5. 次のレコードを見る前に今のデータの店舗名を変数に記録
6. 最終レコードを参照した際の書き込み処理

となります。

文章で見ると分かりづらいですが、1つずつコマンドを作成する際に改めて役割を概説します。

手順2-4-1.繰り返し設定

データ元である店別予実管理テーブルのレコード件数分だけ繰り返し処理を行えるよう設定をします。
コマンドウィンドウで新しいコマンドボタンを押下し繰り返しコマンドを追加します。
[繰り返しのルール]は「指定した回数分だけ繰り返し処理を行う」を選択し、[繰り返し回数、または繰り返し配列]には先ほど作成した変数「全件取得」を指定します。

手順2-4-2.初回限定の処理を設定

ワークテーブルに書き込み処理を行うかは、今参照しているレコードと、1つ前に見ていたレコードの店舗名が違うかどうかで判定を行います。
当然ですが、1件目のレコードを参照している時には1つ前のデータは存在しません。
そのため1つ前のレコードの店舗名を保持する変数wk_店舗が空になっており、書き込みが必要かを正しく判定することができません。
それを回避するため、1件目のレコード参照時には店舗名を変数wk_店舗にセットする処理を行います。

手順2-4-2-1.繰り返しコマンドの作成

先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し条件分岐コマンドを追加します。
[If [条件式]]をクリックし、コンボボックスの中身の有無を条件とするよう、[条件式]タブで新しい条件ボタンを押下し下記のように設定します。

これで作成しておいた変数初回フラグの値が0、つまり最初のレコードを参照しているかどうかを判定するコマンドが作成されました。

手順2-4-2-2.店舗名の取得とフラグのクリア設定

条件分岐コマンドの子コマンドにwk_店舗の値を変える処理を追加します。
新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。
＝Item.店舗のItemというのは繰り返しコマンドで設定した変数全件取得の中身、つまり店別予実管理テーブルのデータが入っています。

店舗名を取得したら、変数初回フラグの値を変更します。
この処理をしないと毎回上記の処理を実行してしまい、条件分岐の判定がうまく行われなくなってしまいます。
新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

手順2-4-3.ワークテーブルにデータを追加

wk_店別四半期集計テーブルにデータの書き込みが必要か判定を行い、必要な場合は書き込み処理を行います。
書き込みを行った場合は四半期ごとの予算と実績を保持している変数の値をクリアする処理を実装します。

手順2-4-3-1.条件分岐コマンドの追加

先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し条件分岐コマンドを追加します。
参照中のレコードの店舗と1つ前のレコードの店舗が異なるかを判定するよう、下記のように設定します。

手順2-4-3-2.ワークテーブルにデータを書き込み

設定した条件に合致したらワークテーブルにデータの追加処理を行うよう設定します。
コマンドウィンドウで指定した条件の下にある[コマンド未設定]をクリックし、テーブルデータの更新コマンドを追加します。
[処理の種類]で「追加」、[対象テーブル]はwk_店別四半期集計を選びます。
[確認ダイアログを表示する]は今回もチェックを外しておきます。
各フィールドにセットする値は以下の通りです。

手順2-4-3-3.集計用変数のクリア

予算と実績の集計値をテーブルに書き込んだので、変数に保持していた値をクリアします。
コマンドウィンドウで新しいコマンドボタンを押下し変数の設定コマンドを追加します。
設定は下記のようにします。

同様に、変数wk_第2四半期予算～wk_第4四半期予算、変数wk_第1四半期実績～wk_第4四半期実績の値もクリアします。
変数名以外は変わらないため、これも変数の設定コマンドを1つ作成したら右クリック＞複製で作成することをおすすめします。

手順2-4-4.四半期ごとに金額を集計

参照中のレコードが持っている予算、実績のデータがどの四半期にあてはまるのかを判定し、あてはまる期の変数に加算していく処理を追加します。

手順2-4-4-1.処理を分岐する条件の設定

Forguncyのテーブルにおいて、データ型が日付/時刻型になっている場合は四半期を自動で判定するフィールドを使用することができます。
今回はその四半期フィールドを使って、参照中のデータが第1四半期～第4四半期のどれにあてはまるかを判定します。
先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し条件分岐コマンドを追加します。
[If [条件式]]をクリックし、コンボボックスの中身の有無を条件とするよう、[条件式]タブで新しい条件ボタンを押下し下記のように設定します。

これで「参照中のレコードが第1四半期のものかどうか」を判定できるようになりました。
判定に合致した際のコマンドを設定する前に、第2四半期～第4四半期の判定もできるように分岐を追加します。
コマンドウィンドウの中にある今追加したばかりの条件分岐コマンドを押下します。
Else Ifの追加ボタンを押下し、新しく追加された[Else If [条件式]]から第2四半期用の判定条件を以下のように設定します。

同様に、第3四半期と第4四半期もElse Ifの追加ボタンで条件を設定します。
最終的に条件分岐コマンドは下図のようになります。

手順2-4-4-2.予算額と実績額を変数に加算

作成済みの変数wk_第1四半期予算～wk_第4四半期実績の8つそれぞれにデータを加算していく処理を追加します。
加算処理は、先ほど追加した条件分岐コマンドの子コマンドに変数の設定コマンドを追加することで実現します。
第1四半期の予算データを加算するには、1つ目の条件の下に変数の設定コマンドを追加します。
設定は下記の通りにします。

これで予算額を加算していく処理が追加されました。
同様に、実績額を加算していく処理を作成します。
先程のコマンドの下にもうひとつ変数の設定コマンドを追加します。
設定は下記の通りにします。

これで第1四半期の分岐は設定完了です。
同様に、第2四半期から第4四半期の分岐にも2つずつ変数の設定コマンドを追加して加算処理を作成します。
それぞれの分岐に追加する変数の設定コマンドの設定値は以下の表を参照してください。

手順2-4-5.店舗名を変数に格納

次のレコードを参照する前に、条件分岐で使用するため店舗のデータを変数に格納しておきます。
先に追加した繰り返しコマンドの子コマンドとして、新しいコマンドボタンを押下し変数の設定コマンドを追加します。
このコマンドは条件分岐の子コマンドではないことに留意してください。
変数の設定コマンドは下記のように設定します。

手順2-4-6.最終レコードの処理

今回の処理では設定上参照中の1つ前までのレコードデータを書き込んでいるため、最終レコードのデータ書き込み処理は別途追加する必要があります。
書き込み自体はテーブルデータの更新コマンドで行います。

しかし、今回は先に作成した同コマンドと全く同じ処理を実行したいので条件分岐コマンドの子コマンドとして作成済みのテーブルデータの更新コマンドを右クリックし複製します。
その後、コマンドリストの一番下にドラッグ&ドロップでコマンドを移動します。
この時、どのコマンドの子コマンドでもなくなるように配置してください。

これで、SQLを使わずに四半期ごとの集計をする処理の作成は完了です。

3.SQLを使って四半期ごとの集計をする

SQLを使う場合は、ビュー機能を使用します。
ビューについては開発スタートガイドやヘルプに記事がありますので、詳細はそちらを参照してください。

＞参考記事：クエリを使ってビューを定義する

＞Forguncyヘルプ – ビューの作成

手順3-1.四半期ごとにデータを集計する

ビューを作る際にはForguncyの日付/時刻型フィールドに自動で生成される[四半期]フィールドを使用することはできません。
そのため、まずは四半期ごとに予算と実績を集計し、それがどの年度・どの四半期のものなのか分かるようなビューを作成します。
リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_店別四半期集計」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT
 * 
 FROM
  (
   SELECT 
     店舗 
     ,CASE
       WHEN 
         MONTH(店別予実管理.年月) <4 
           THEN YEAR(店別予実管理.年月) - 1
       ELSE YEAR(店別予実管理.年月)
      END AS 年度
     ,CASE
         WHEN MONTH(年月) BETWEEN 4 AND 6 THEN '1'
         WHEN MONTH(年月) BETWEEN 7 AND 9 THEN '2'
         WHEN MONTH(年月) BETWEEN 10 AND 12 THEN '3'
         WHEN MONTH(年月) BETWEEN 1 AND 3 THEN '4'
       END AS 四半期
      ,SUM(予算) AS 予算
      ,SUM(実績) AS 実績
   FROM 店別予実管理
     GROUP BY 年度,店舗,
       CASE
         WHEN MONTH(年月) BETWEEN 4 AND 6 THEN '1'
         WHEN MONTH(年月) BETWEEN 7 AND 9 THEN '2'
         WHEN MONTH(年月) BETWEEN 10 AND 12 THEN '3'
         WHEN MONTH(年月) BETWEEN 1 AND 3 THEN '4'
      END) 

OKボタンを押下してウィンドウを閉じます。

手順3-2.表示用にデータを整形する

今回は店舗ごとに1レコードの形で一覧表示を行います。
先に作成したビューの形では各店舗、四半期ごとに1レコードとなってしまっているため、それを整形するためのビューを新たに作成します。
リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_店別四半期集計_整形」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT 
  集計.年度
  ,集計.店舗
  ,SUM(集計.Q1予算) AS 第1四半期予算
  ,SUM(集計.Q1実績) AS 第1四半期実績
  ,SUM(集計.Q2予算) AS 第2四半期予算
  ,SUM(集計.Q2実績) AS 第2四半期実績
  ,SUM(集計.Q3予算) AS 第3四半期予算
  ,SUM(集計.Q3実績) AS 第3四半期実績
  ,SUM(集計.Q4予算) AS 第4四半期予算
  ,SUM(集計.Q4実績) AS 第4四半期実績
 FROM
  (
    SELECT
      年度
     ,店舗
     ,予算 AS Q1予算
     ,0 AS Q2予算
     ,0 AS Q3予算
     ,0 AS Q4予算
     ,実績 AS Q1実績
     ,0 AS Q2実績
     ,0 AS Q3実績
     ,0 AS Q4実績
    FROM 
      v_店別四半期集計
       WHERE 四半期 = '1'
         GROUP BY 年度, 店舗
    UNION ALL
    SELECT
      年度
     ,店舗
     ,0 AS Q1予算
     ,予算 AS Q2予算
     ,0 AS Q3予算
     ,0 AS Q4予算
     ,0 AS Q1実績
     ,実績 AS Q2実績
     ,0 AS Q3実績
     ,0 AS Q4実績
    FROM 
      v_店別四半期集計
       WHERE 四半期 = '2'
         GROUP BY 年度, 店舗
    UNION ALL
    SELECT
      年度
     ,店舗
     ,0 AS Q1予算
     ,0 AS Q2予算
     ,予算 AS Q3予算
     ,0 AS Q4予算
     ,0 AS Q1実績
     ,0 AS Q2実績
     ,実績 AS Q3実績
     ,0 AS Q4実績
    FROM 
      v_店別四半期集計
       WHERE 四半期 = '3'
         GROUP BY 年度, 店舗
    UNION ALL
    SELECT
      年度
     ,店舗
     ,0 AS Q1予算
     ,0 AS Q2予算
     ,0 AS Q3予算
     ,予算 AS Q4予算
     ,0 AS Q1実績
     ,0 AS Q2実績
     ,0 AS Q3実績
     ,実績 AS Q4実績
    FROM 
      v_店別四半期集計
       WHERE 四半期 = '4'
         GROUP BY 年度, 店舗
    )集計
       GROUP BY
         集計.年度
        ,集計.店舗

OKボタンを押下してウィンドウを閉じます。

手順3-3.ビューの確認

ナビゲーションウィンドウからv_店別四半期集計_整形ビューを開き、構成を確認します。
店別予実管理テーブルに保管されていたデータが各店舗、四半期ごとに1レコードとなり、さらにそれが年度ごとに各店舗1レコードの形に変わっているのが分かります。

4.一覧ページを作成する

ここまでの手順で準備したデータを一覧表形式で表示するページを作成します。
ここからの手順では、これまでの作業でSQLを使わなかった場合は「店舗別予実一覧（四半期集計）」ページを、SQLを使用した場合は「SQL使用_店舗別予実一覧（四半期集計）」ページで作業を行ってください。
なお、この時点では2つのページに名称以外の差はありません。

手順4-1.リストビューの作成

整形済みのデータを一覧表示するリストビューを作成します。

手順4-1-1.リストビューの配置

ページ上の黒い枠線に囲われたセル範囲のうち、上側のセルを選択した状態でナビゲーションウィンドウからwk_店別四半期集計テーブルまたはv_店別四半期集計_整形ビューをそのセル範囲にドラッグ&ドロップします。

リストビューがページに配置されたら、リボンの[ホーム]＞[フォント]＞[罫線]から枠線なしを選択して黒い枠線を消去してください。

手順4-1-2.項目をセット

リストビューに各項目をセットしていきます。
ナビゲーションウィンドウのからリストビューのデータソースに指定したテーブル/ビューを展開し、フィールド名が表示されるようにします。
リストビューの列ヘッダーの真下の行に、年度、店舗、第1四半期予算～第4四半期実績までの各フィールドを配置します。

手順4-1-3.表示データの絞り込み設定

リストビュー上部にある対象年度のコンボボックスで指定したデータだけを一覧表示する設定を行います。
リストビューを右クリックし、[クエリー条件…]をクリックします。
クエリー条件ウィンドウが開くので、下記のように設定します。

5.デバッグを実行する

リボンの[ホーム]>[デバッグ]>[開始]ボタン、またはForguncy Builderの左上にある▶ボタンを押下してプロジェクトをデバッグ実行します。

作成したページがブラウザで表示されます。

年度を選択し、SQLを使わないページの場合はボタンを押下してデータが一覧表示されることを確認してください。

Forguncyで行う予実管理についてもっと知る

製品サイトでは、Forguncyで行う予実管理や今回の記事で使用した機能についてより詳しくご紹介しています。
こちらもぜひご活用ください。

＞Forguncyドリル – 日ごとに集計している実績データを月単位で集計し予実の一覧表を作る

＞Forguncyドリル – 営業担当者ごとに集計した予実管理表を作る

＞Forguncyドリル – 予実の一覧表に部門ごとの小計を表示する

＞Forguncyドリル – 毎月の予実状況を累計表示する

＞デモアプリ一覧 – 予実管理

＞開発スタートガイド – クエリを使ってビューを定義する

＞開発スタートガイド – Forguncyのリストビューを使いこなす

＞開発スタートガイド – 数式を使って演算処理をする

＞Forguncyヘルプ – ビューの作成

＞課題別資料 – 脱Excelのススメ