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

オペレーション　活用編

企業の経営活動において、予実管理は欠かせない存在です。
予算と実績のデータが同じ単位で集計されていればよいですが、現実には日次と月次など異なる単位で集計されているケースがあります。
今回はそういったケースを想定し、日次で集計している実績データを月次の形に整え、予算データと突き合わせた一覧表を作成する方法を解説します。

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

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

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

ナビゲーションウィンドウのテーブル欄には「部署マスタ」、「科目マスタ」、「予算管理」、「実績管理」の4つが存在しています。
予算管理テーブルは月単位で金額を集計していますが、実績管理テーブルでは一日ごとに集計しています。

今回の記事ではこの2つのテーブルの金額の集計単位を合わせ、予実管理として1つの表の形でデータを閲覧できるようにしていきます。
予算管理テーブルと実績管理テーブルでは、部署や科目のデータをコードでのみ保持しています。
そのため、各部や科目の名称はそれぞれ部署マスタと科目マスタから取得して表示します。
また、今回はあらかじめForgucnyに内部テーブルを用意していますが、ExcelやCSVなどのファイルからデータを取り込んで使用することもできます。

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

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

ナビゲーションウィンドウから「予実一覧」ページを開きます。
ページには年度、部署、科目と書かれたコンボボックスが配置されています。
これらのコンボボックスは一覧表示するデータを絞り込むのに使用します。
その下の黒い枠線に囲まれたセルは、予実の一覧表を表示するリストビューを配置する範囲です。

2.実績データの年度を算出する

予実を1つの表で表現するには実績データを月単位で集計し直す必要があります。
そのために実績管理テーブルの日付フィールドから年と月を取り出さなければなりませんが、今回はそれに加えて年度を算出します。
年、月、年度を使用するには、実績管理テーブルを元にしたビューを作成します。

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

なお、予算管理テーブルには既に年度フィールドが存在しているため、今回は実績管理テーブルのみにこの操作を行います。

手順2-1.ビューの作成

リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_実績管理_年度計算」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT 
 CASE
   WHEN 
     CAST(実績集計.月 AS INTEGER) BETWEEN 1 AND 3 
       THEN CAST(実績集計.年 AS INTEGER) -1
  ELSE CAST(実績集計.年 AS INTEGER)
  END AS 年度
 ,実績集計.年 AS 年
 ,実績集計.月 AS 月
  ,実績集計.部署コード AS 部署コード
  ,実績集計.科目コード AS 科目コード
  ,実績集計.金額
FROM 
 (
  SELECT 
    strftime('%Y',date((実績管理.日付-25569)*60*60*24,'unixepoch'))  AS 年
   ,strftime('%m',date((実績管理.日付-25569)*60*60*24,'unixepoch'))  AS 月
  ,実績管理.部署コード
  ,実績管理.科目コード
  ,実績管理.金額
  FROM 
   実績管理) 実績集計
 ORDER BY
 年度, 月, 部署コード, 科目コード　　

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

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

ナビゲーションウィンドウからv_実績管理_年度計算ビューを開き、構成を確認します。
実績管理テーブルから日付フィールドがなくなり、代わりに年度、年、月のフィールドが増加しています。
部署コード、科目コード、金額フィールドは実績管理テーブルと変化はありません。

3.実績データを月次集計する

手順2で作成したビューv_実績管理_年度計算の金額データを月ごとに集計します。
この集計も新しいビューを作ることで実現します。

手順3-1.ビューの作成

リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_実績管理_月単位」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT
 年度 AS 年度
  ,実績集計.部署コード AS 部署コード
 ,実績集計.科目コード
 ,SUM(実績集計._4月金額) AS _4月
 ,SUM(実績集計._5月金額) AS _5月
 ,SUM(実績集計._6月金額) AS _6月
 ,SUM(実績集計._7月金額) AS _7月
 ,SUM(実績集計._8月金額) AS _8月
 ,SUM(実績集計._9月金額) AS _9月
 ,SUM(実績集計._10月金額) AS _10月
 ,SUM(実績集計._11月金額) AS _11月
 ,SUM(実績集計._12月金額) AS _12月
 ,SUM(実績集計._1月金額) AS _1月
 ,SUM(実績集計._2月金額) AS _2月
 ,SUM(実績集計._3月金額) AS _3月
FROM
 (
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,金額 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 4
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,金額 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 5
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,金額 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 6
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,金額 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 7
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,金額 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 8
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,金額 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
 v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 9
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,金額 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 10
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,金額 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 11
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,金額 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 12
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,金額 AS _1月金額
 ,0 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 1
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,金額 AS _2月金額
 ,0 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 2
 UNION ALL
 SELECT
  v_実績管理_年度計算.年度 AS 年度
  ,部署コード AS 部署コード
  ,科目コード AS 科目コード
  ,0 AS _4月金額
  ,0 AS _5月金額
  ,0 AS _6月金額
 ,0 AS _7月金額
 ,0 AS _8月金額
 ,0 AS _9月金額
 ,0 AS _10月金額
 ,0 AS _11月金額
 ,0 AS _12月金額
 ,0 AS _1月金額
 ,0 AS _2月金額
 ,金額 AS _3月金額
 FROM
  v_実績管理_年度計算
 WHERE CAST(v_実績管理_年度計算.月 AS INTEGER) = 3
 ) 実績集計
GROUP BY
 実績集計.年度
 ,実績集計.部署コード
 ,実績集計.科目コード

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

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

ナビゲーションウィンドウからv_実績管理_月単位ビューを開き、構成を確認します。
基になったv_実績管理_年度計算ビューから構成が大幅に変化しています。
年度、部署コード、科目コードフィールドは変わらず存在していますが、その後には_4月～_3月という12個の新しいフィールドができています。
ここには、それぞれの月ごとに集計された金額フィールドの値が格納されています。
また、予算管理テーブルとビューv_実績管理_月単位を比較すると、フィールド構成が同じになっていることが確認できます。
これで実績データの加工が完了し、予算データと突き合わせる準備が完了しました。

4.予算と実績のデータを突き合わせる

予算管理テーブルとv_実績管理_月単位ビューを合体させて、予実のデータを作成します。
この段階で科目マスタと部署マスタも使用し、科目や部署をコードではなく名称で表示できるようにします。

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

リボンの[作成]＞[テーブル]＞[ビュー]から[ビューの作成]を押下して、ビューの作成ウィンドウを開きます。
ビュー名欄に「v_予実一覧_月単位」と入力し、下記のSQL文をコピーして貼り付けます。

SELECT
 予算管理.年度 AS 年度
 ,予算管理.部署コード AS 部署コード
 ,部署マスタ.部署名 AS 部署名
 ,予算管理.科目コード AS 科目コード
 ,科目マスタ.科目名 AS 科目名
 ,予算管理._4月 AS _4月予算
 ,v_実績管理_月単位._4月 AS _4月実績
 ,予算管理._5月 AS _5月予算
 ,v_実績管理_月単位._5月 AS _5月実績
 ,予算管理._6月 AS _6月予算
 ,v_実績管理_月単位._6月 AS _6月実績
 ,予算管理._7月 AS _7月予算
 ,v_実績管理_月単位._7月 AS _7月実績
 ,予算管理._8月 AS _8月予算
 ,v_実績管理_月単位._8月 AS _8月実績
 ,予算管理._9月 AS _9月予算
 ,v_実績管理_月単位._9月 AS _9月実績
 ,予算管理._10月 AS _10月予算
 ,v_実績管理_月単位._10月 AS _10月実績
 ,予算管理._11月 AS _11月予算
 ,v_実績管理_月単位._11月 AS _11月実績
 ,予算管理._12月 AS _12月予算
 ,v_実績管理_月単位._12月 AS _12月実績
 ,予算管理._1月 AS _1月予算
 ,v_実績管理_月単位._1月 AS _1月実績
 ,予算管理._2月 AS _2月予算
 ,v_実績管理_月単位._2月 AS _2月実績
 ,予算管理._3月 AS _3月予算
 ,v_実績管理_月単位._3月 AS _3月実績
FROM 予算管理
  INNER JOIN v_実績管理_月単位
   ON 予算管理.年度 = v_実績管理_月単位.年度
   AND 予算管理.部署コード = v_実績管理_月単位.部署コード
   AND 予算管理.科目コード = v_実績管理_月単位.科目コード
  INNER JOIN 部署マスタ
   ON 予算管理.部署コード = 部署マスタ.部署コード
  INNER JOIN 科目マスタ
   ON 予算管理.科目コード = 科目マスタ.科目コード
 ORDER BY 年度, 部署コード,科目コード

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

手順4-2.ビューの確認

ナビゲーションウィンドウからv_予実一覧_月単位ビューを開き、構成を確認します。
予算管理テーブルが持っていた年度、部署コード、科目コードに加えて部署マスタから取得した部署名と科目マスタから取得した科目名フィールドが生成されました。
その後には、予算管理テーブルから取得した_○月予算フィールドと、v_実績管理_月単位ビューから取得した_○月実績フィールドが12か月分生成されています。
これで各部署の科目単位の予算と実績が、年度ごとに1つのレコードに集約されました。

5.一覧を表示する画面を作る

ここまでの手順で整形したデータを一覧表形式で表示するページを作成します。

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

ページ上の黒い枠線に囲われた範囲のセルを選択した状態で、ナビゲーションウィンドウからv_予実一覧_月単位ビューをそのセル範囲にドラッグ&ドロップします。

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

手順5-2.項目をセット

リストビューにv_予実一覧_月単位ビューの各項目をセットしていきます。
ナビゲーションウィンドウのv_予実一覧_月単位ビューを展開し、フィールド名が表示されるようにします。
リストビューの列ヘッダーの真下の行に、下記のように項目を配置します。

手順5-3.消化率の計算

各月の予算と実績のデータから、消化率を算出します。
消化率はビューには無い項目ですが、リストビューに数式をセットすることで実現できます。
消化率は、実績÷予算で求められます。
_4月実績をセットした右隣のセル4つを結合し、下記の数式をセットします。

• =P9/L9

手順5-4.予実の差の計算

消化率と同様に、各月の予算と実績のデータからその差を算出します。
差もビューには無い項目ですが、リストビューに数式をセットして表示します。
予実の差は、予算-実績で求めます。
4月の消化率をセットした右隣のセル4つを結合し、下記の数式をセットします。

• =L9-P9

4月分の予算、実績、消化率、差をセットできたら、5月～3月も同様にこれらの項目をリストビューに配置します。

手順5-5.クエリーの設定

ページにあらかじめ作成されていたコンボボックスを使用して、表示するデータを絞り込む処理を実装します。

＞関連記事：部署や科目を指定して表示データを絞り込む

データの絞り込みにはリストビューにクエリーを設定します。
リストビューを右クリックし、[クエリー条件…]をクリックします。
クエリー条件ウィンドウが開くので、下記の3つの条件を設定します。

これで条件指定が完了しました。

6.外観の設定

ここまでで「日ごとに集計している実績データを月単位で集計し予実の一覧表を作る」という要件はクリアしていますが、データを見やすくするためにリストビューの外観を整えます。

手順6-1.ヘッダーを複数行に変更

リストビューを右クリックし、[リストビュー設定]を押下してリストビュー設定ウィンドウを開きます
[外観]タブの[列ヘッダーの行数]を2に増やし、ウィンドウを閉じます。
ページに戻るとリストビューのヘッダー行が1行増え、明細行が9行目から10行目に移動しています。
数式に使っているセル番地も自動で修正されるため、数式を修正する必要はありません。
下図のように、セルを結合したりヘッダーの文言を修正したりして見た目を整えます。

手順6-2.部署名を複数行表示に変更

部署名の列は、同じ部署名が複数行に渡って表示されます。
この状態だと表が見づらくなるため、同じ値が複数行に渡って続く時はセルを自動で結合するよう設定を行います。
リボンの[リストビューツール]＞[デザイン]＞[列設定]＞[セルの自動結合]にチェックを入れます。

手順6-3.セルの書式設定

項目によって何を表しているか分かりやすくするため、リストビュー内のセルに対して書式設定を行います。
リストビュー内のセルの書式設定は明細の1行目に設定すると他の行にも適用されます。
そのため、10行目の各セルにのみ設定を行います。
設定を行うには各セルを右クリックし、[セルの書式設定]をクリックします。
セルの書式設定ウィンドウが開くので、[表示形式]タブで下記のとおりに設定を行います。
下記に記載のない項目は特に設定を行いません。

これで、ページの設定は完了です。

7.デバッグを実行する

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

作成したページがブラウザで表示されます。
各月の予算、実績、消化率、差が一覧表示されていること、またコンボボックスの値を変更することでデータの絞り込みができることを確認してください。

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

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

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

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

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

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

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

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