予実の一覧表に部門ごとの小計を表示する

オペレーション 活用編

予実管理では状況を様々な角度から比較・分析することが重要です。
今回のドリルでは、部門ごとの小計を表示できる予実一覧表の作成方法を解説します。

プロジェクトファイル
(作成バージョン:8.0.41.0)
budget-control-3_before.fgcp
budget-control-3_after.fgcp(実装済みプロジェクトファイル)

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

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

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

ナビゲーションウィンドウのテーブル欄には「部署マスタ」、「社員マスタ」、「営業予算管理」、「営業案件管理」の4つが存在しています。
営業予算管理テーブルは月単位で金額を集計しています。
対して営業案件管理テーブルでは、案件のフェーズごとにレコードが作成されています。
今回はこの営業案件管理テーブルから実績を集計し、営業予算管理テーブルと突き合わせることで予実管理表を作成します。
そのために、それぞれのテーブルデータを欲しい形に加工する作業が必要となります。

営業予算管理テーブルと営業案件管理テーブルでは、部署や担当者名のデータをコードでのみ保持しています。
一覧表にこれらの情報を表示するには、それぞれ部署マスタ社員マスタからデータを取得する必要があります。
それぞれのテーブルから必要な情報を集めるのに、今回はビューを使用します。
ビューについては開発スタートガイドやヘルプに記事がありますので、詳細はそちらを参照してください。

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

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

ナビゲーションウィンドウから「営業予実管理」ページを開きます。
ページには枠線に囲まれたセル範囲が2つあります。
今回は一覧表を作るのにピボットテーブルを使用します。
Forguncyのピボットテーブルを使用するには、そのデータソースとして同じページ上にリストビューが配置されている必要があります。
ページ上の枠線はこれらのパーツを置く範囲の目印です。

2.案件管理データから営業実績データを取得する

今回のアプリケーションでは営業案件管理テーブルのデータから営業実績データを取得します。
営業案件管理テーブルには金額フェーズのフィールドがあるため、フェーズが「受注」のレコードの金額を集計することで実績データとして使用できます。
営業案件管理テーブルから欲しいデータを抽出するにはビューを使います。

手順2-1.案件テータから年度・年・日付を抽出

営業案件管理テーブルのデータが持つ日時の情報は一日単位のため、そこからまず年度・年・月の3つを取り出します。
またこの時、フェーズが「受注」であるレコードのみに絞り込む作業も同時に行います。
これらは手順1-1で触れた、ビューを作ることで実現します。

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

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

・SQL文

SELECT 
 CASE
   WHEN 
     CAST(営業案件集計.月 AS INTEGER) BETWEEN 1 AND 3 
       THEN CAST(営業案件集計.年 AS INTEGER) -1
  ELSE CAST(営業案件集計.年 AS INTEGER)
  END AS 年度
  ,営業案件集計.年 AS 年
  ,営業案件集計.月 AS 月
  ,営業案件集計.部署コード
  ,営業案件集計.担当者社員番号
  ,営業案件集計.案件名
  ,営業案件集計.フェーズ
  ,営業案件集計.金額
FROM 
 (
  SELECT 
    strftime('%Y',date((営業案件管理.日付-25569)*60*60*24,'unixepoch'))  AS 年
   ,strftime('%m',date((営業案件管理.日付-25569)*60*60*24,'unixepoch'))  AS 月
  ,営業案件管理.部署コード
  ,営業案件管理.担当者社員番号
  ,営業案件管理.案件名
  ,営業案件管理.フェーズ
  ,営業案件管理.金額
  FROM 
   営業案件管理
    WHERE 営業案件管理.フェーズ = '受注'
) 営業案件集計
 ORDER BY
 年, 月, 部署コード, 担当者社員番号

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

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

ナビゲーションウィンドウからv_案件実績管理_年度計算ビューを開き、構成を確認します。
営業案件管理テーブルから日付フィールドがなくなり、代わりに年度、年、月のフィールドが増加しています。
また、フェーズフィールドの値も「受注」であるレコード以外は省かれ、存在しなくなっています。
その他のフィールドは営業案件管理テーブルと変化はありません。

手順2-2.実績金額データを月次集計

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

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

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

・SQL文

SELECT
 年度
 ,年
 ,月
 ,部署コード
 ,担当者社員番号
 ,SUM(金額) AS 実績金額
FROM 
  v_案件実績管理_年度計算
GROUP BY
 v_案件実績管理_年度計算.年
 ,v_案件実績管理_年度計算.月
 ,v_案件実績管理_年度計算.部署コード
 ,v_案件実績管理_年度計算.担当者社員番号

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

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

ナビゲーションウィンドウからv_案件実績管理_月単位ビューを開き、構成を確認します。
基になったv_案件実績管理_年度計算ビューから構成はさほど変わっていないように見えますが、金額(新しいビューでは実績金額)フィールドの値が大きく変化し、担当者別で月単位に集計されています。
これで案件データの加工が完了しました。

3.予算データを加工する

営業予算管理テーブルはひと月に1レコードではなく、担当者ごとに1レコードのデータを持っています。
レコードの中にはそれぞれの担当者の予算金額が1ヶ月ごとに区切られて保持されています。
そのためこれを手順2で作成したv_案件実績管理_月単位ビューと同じ「金額が担当者別で月単位に集計」されている状態に加工します。
この手順にもビューを使用します。

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

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

・SQL文

SELECT
 年度
 ,月
 ,部署コード
 ,担当者社員番号
 ,予算金額
 FROM
(
 SELECT
  営業予算管理.年度
  ,4 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._4月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,5 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._5月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,6 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._6月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,7 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._7月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,8 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._8月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,9 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._9月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,10 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._10月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,11 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._11月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,12 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._12月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,1 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._1月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,2 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._2月 AS 予算金額
 FROM
  営業予算管理
 UNION ALL
 SELECT
  営業予算管理.年度
  ,3 AS 月
  ,営業予算管理.部署コード
  ,営業予算管理.担当者社員番号
  ,営業予算管理._3月 AS 予算金額
 FROM
  営業予算管理
 )
ORDER BY
 年度, 月, 部署コード, 担当者社員番号

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

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

ナビゲーションウィンドウからv_予算管理_月単位ビューを開き、構成を確認します。
営業予算管理テーブルから_4月_3月までの金額を保持していたフィールドがなくなり、新たに予算金額フィールドが生成されました。
どの月の予算金額なのかを示すフィールドも同時に生成されており、担当者ごとに各月1レコードを持つようになったため、レコードの総数も増加しています。

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

v_予算管理_月単位ビューとv_案件実績管理_月単位ビューを合体させて、予実のデータを作成します。
この段階で部署マスタ社員マスタも使用し、部署や担当者の情報をコードではなく名称で表示できるようにしていきます。

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

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

・SQL文

SELECT
 予算.年度
 ,予算.月
 ,予算.部署コード
 ,部署マスタ.部署名
 ,予算.担当者社員番号 AS 社員番号
 ,社員マスタ.氏名 AS 営業担当者
 ,予算.予算金額 ,実績.実績金額
 FROM
 v_予算管理_月単位 AS 予算
 LEFT JOIN 
 v_案件実績管理_月単位 AS 実績
  ON 予算.年度 = 実績.年度
  AND 予算.月 = CAST(実績.月 AS INTEGER)
  AND 予算.部署コード = 実績.部署コード
  AND 予算.担当者社員番号 = 実績.担当者社員番号
   LEFT JOIN 部署マスタ
    ON 予算.部署コード = 部署マスタ.部署コード
     LEFT JOIN 社員マスタ
      ON 予算.担当者社員番号 = 社員マスタ.社員番号
 ORDER BY 実績.年, 予算.月, 予算.部署コード, 社員番号

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

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

ナビゲーションウィンドウからv_営業予実管理ビューを開き、構成を確認します。
年度、月、部署や社員の情報の他にv_予算管理_月単位ビューから取得した予算金額フィールドとv_案件実績管理_月単位ビューから取得した実績金額フィールドが生成されています。
これで一覧表を作成するためのデータの加工は完了しました。

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

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

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

最終的な一覧表はピボットテーブルで作成しますが、それに必要なリストビューの配置を行います。

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

ページ上の黒い枠線に囲われたセル範囲のうち、上側のセルを選択した状態でナビゲーションウィンドウからv_営業予実管理ビューをそのセル範囲にドラッグ&ドロップします。

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

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

リストビューにv_営業予実管理ビューの各項目をセットしていきます。
ナビゲーションウィンドウのv_営業予実管理ビューを展開し、フィールド名が表示されるようにします。
リストビューの列ヘッダーの真下の行に、下記のようにビューに存在する全ての項目を配置します。
最終的な画面ではこのリストビューは非表示に設定するため、リストビューの見た目を気にする必要はありません。

手順5-1-3.予実の差の計算

最終的な表には、予算額と実績額だけでなく両者の差異も表示します。
この差異はビューに無い値ですが、リストビュー上に数式をセットすることで利用可能になります。
リストビューの最後の項目をセットした右隣の列に、以下のように入力します。

セル番地
J5差異
J6=I6-H6

手順5-1-4.リストビューの非表示設定

最後に、リストビューを配置してある5行目~8行目を選択した状態で右クリックし、[非表示(H)]をクリックしてこれらの行を非表示に設定します。
これでリストビューの設定は完了です。

手順5-2.ピボットテーブルの作成

一つ前の手順で作成したリストビューをデータソースとして、最終的な予実の一覧表となるピボットテーブルを作成します。

手順5-2-1.ピボットテーブルの配置

ページ上の黒い枠線に囲われたセル範囲のうち、下側のセルを選択した状態で、リボンの[ホーム]>[セル型]のコンボボックスからピボットテーブルを選択します。

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

手順5-1-2.ピボットテーブルの設定

ピボットテーブル型セルを選択した状態で、右ペインの[セル型]タブからピボットテーブル設定…ハイパーリンクをクリックします。
ピボットテーブル設定ウィンドウが開くので、データソースとするリストビュー欄で既にリストビュー1(前の手順で作成したリストビュー)が選択されていることを確認します。

列、行、値の各欄に、それぞれ以下のように項目をドラッグ&ドロップで設定します。

C6:月E6:部署名
G6:営業担当者
H6:予算金額
I6:実績金額
J6:差異

値に設定した予算金額実績金額差異の3項目はドラッグ&ドロップすると自動で末尾に(個数)と追記されています。
このままだとデータの個数を表示してしまい、金額のデータをうまく見ることができません。
これを直すために各項目をクリックして[フィールドの設定]をクリックします。
フィールドの設定ウィンドウが開くので、[値フィールドの集計]を「合計」に変更します。

ここまで完了すると、ピボットテーブル設定ウィンドウは下図のようになります。

手順5-3.外観の設定

ここまででアプリケーションの要件は満たしていますが、データを見やすくするためにピボットテーブルの外観を整えます。

手順5-3-1.見出しの変更

ピボットテーブルの列に設定した月フィールドですが、見出しは数字がそのまま4や5と表示されているだけで分かりにくくなっています。
そこで、4月・5月…のように表示するため、右ペインの[セル型]タブからピボットテーブル設定…をクリックします。
ピボットテーブル設定ウィンドウが開くので、列欄にあるC6:月をクリックし[フィールドの設定]をクリックします。
フィールドの設定ウィンドウにある表示形式ボタンを押下し、フィールドの書式設定ウィンドウを開きます。
表示形式の分類を「ユーザー定義」に変更し、種類の欄に「0月」と入力しOKボタンを押下して閉じます。

ピボットテーブル設定ウィンドウをOKボタンで閉じると、エディタ上のプレビューに変更が反映され見出しが○月表記に変わります。

手順5-3-2.数値項目の表示形式変更

ピボットテーブルの値に設定した3つの数値項目に、小数点以下の表示をしない・3桁区切り・マイナスの場合は赤字にするといった表の見やすさを改善するための設定を行います。
右ペインの[セル型]タブからピボットテーブル設定…をクリックします。
ピボットテーブル設定ウィンドウが開くので、値欄にあるH6:予算金額(合計)をクリックし[フィールドの設定]をクリックします。
フィールドの設定ウィンドウにある表示形式ボタンを押下し、フィールドの書式設定ウィンドウを開きます。
表示形式の分類を「数値」に変更し、[小数点以下の桁数]を「0」に変更します。
さらに[桁区切り(,)を使用する]にチェックを入れ、[負の数の表示形式]を-1,234に変更しOKボタンを押下して閉じます。

同様に、値欄にあるI6:実績金額(合計)J6:差異(合計)にも上記の設定を行います。
ピボットテーブル設定ウィンドウをOKボタンで閉じると、エディタ上のプレビューに変更が反映され3つの項目の表記が変わります。

手順5-3-3.ピボットテーブルの外観設定

ピボットテーブルそのものの色などのスタイルを変更します。
ピボットテーブルを選択した状態で、リボンの[ホーム]>[スタイル]>[セルスタイル]をクリックします。

様々なスタイルが表示されるので、好みのものに変更します。

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

6.デバッグを実行する

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

作成したページがブラウザで表示されます。
予実のデータが一覧表示され、部門が切り替わる時には小計行が表示されていることを確認してください。

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

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

>ノーコードWebアプリ開発ツール「Forguncy」

ノーコードWebアプリ開発ツール「Forguncy」

Forguncy(フォーガンシー)は複数のシステムと直接接続して参照・更新が行えるデータ管理機能と、Excel感覚でレイアウトができる画面デザイン機能を備えたノーコードWeb開発&運用プラットフォームです。基幹システムでは対応できず、仕方なくExcelやAccessで管理していた業務のサブシステム化を強力に支援します。

CTR IMG