注目キーワード
  1. 予実管理

予実_横積み_複数集計（Excel→kintoneアプリへの移行解説）

500-100 | 予実管理

下記のようなExcelで作成している予実管理表をどのようにkintoneでアプリ化し、元の表と同じイメージで分析できるか具体的な手順を解説します。なお、このExcelでは4月～翌年3月までを会計期間とします。

営業担当者ごと・部門ごとの集計を行う表

使用する製品

  • krewData：予実データの加工処理に利用します
    本記事では【1】krewDataで予実データを整形する で解説しています。
  • krewDashboard：集計した予実データを可視化するために利用します
    【2】krewDashboardで予実データを可視化する（予実管理表の作成） で解説しています。

入力アプリのフィールド構成

予算管理アプリ

  • 予算（目標額）を管理するアプリ
  • 営業担当者ごとに年間の予算を1レコードで管理する構成

案件管理アプリ

  • 案件を管理するアプリ
  • このアプリでフェーズが「受注」になったレコードを集計対象とする

出力アプリのフィールド構成

  • 部門ごとに「予算」「実績」「差異」「達成率」を計算した結果を出力するアプリ
  • このアプリにkrewDashboardを適用して可視化する
  • 上記のアプリにkrewDashboardを設定して、下記のような見た目に整える

【1】krewDataで予実データを整形する

このステップは「予実_横積み_単一集計」と全く同じになります。【2】krewDashboardで予実データを可視化する（予実管理表の作成）の設定はこの記事特有の内容です。

1.予算データを整形する

予算アプリに登録してある予算データを整形します。

入力アプリを設定する

  1. 入力アプリコマンドを追加し、アプリの選択で「予算」アプリを選択する
  2. フィールドの選択で「年度」「部門」「営業担当者」「4月～翌年3月」を選択する
プレビューデータ

当日の年度を抽出する

今年度のデータだけを対象とできるよう、当日の年度を抽出します。

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド：「当日の年度」という新しいフィールドを作成
    ・編集方法：数式
    ・編集内容：IF(MONTH(TODAY())>=4, YEAR(TODAY()), YEAR(TODAY()-1))

プレビューではこのように表示されます。

当日の年度がフィールドとして追加された

今年度予算に絞り込む

  1. フィルタコマンドを追加する
  2. 数式で次のように絞り込む
    数式：年度=当日の年度

予算アプリのレイアウトを変更する

予算アプリは1レコードで年間予算を管理していたのに対して、案件アプリでは案件ごとにレコードを登録します。あとの設定で予算と実績の突合せができるように、ここでは予算データのレイアウトを案件アプリにあわせて変更しておきます。

  1. 列ー行変換コマンドを追加する
  2. 次のように設定する
    ・変換する列名を保存する新しいフィールド名：月
    ・変換する列の値を保存する新しいフィールド名：予算
    ・次の設定を12か月分行う
    1. 月に変換する列：4月
      ・変換した行に保存するの列を識別する値：4
プレビューデータ

ワンポイント

列ー行変換を経てデータが次のように変化しました。

2.実績データを整形する

1で整形した予算データと突き合わせるために案件管理アプリのデータを整形します。

入力アプリを設定する

  1. 入力アプリコマンドを追加し、アプリの選択で「案件管理」アプリを選択する
  2. 次の条件でフィルタする：フェーズ　次のいずれかを含む　受注
    ※必要に応じて日付フィールドを使って集計対象レコードの絞り込みも行ってください
  3. フィールドの選択で「日付」「部門」「営業担当者」「案件名」「金額」「フェーズ」を選択する
プレビューデータ

年度を抽出する

案件アプリに登録されている日付フィールドから年度を抽出します。

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド：「年度」という新しいフィールドを作成
    ・編集方法：数式
    ・編集内容：IF(MONTH(日付)>=4, YEAR(日付), YEAR(日付)-1)
プレビューデータ

月を抽出する

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド：「月」という新しいフィールドを作成
    ・編集方法：日付編集
    ・編集内容：「日付」フィールドから月を抽出する

営業担当者ごとに集計（グループ化）する

  1. グループ化コマンドを追加する
  2. グループ化する項目フィールドに次を選択：年度、月、営業担当者
  3. 次のように設定する
    ・集計する値が保存されているフィールド：金額
    ・集計した値を保存するフィールド名：金額
    ・集計方法：合計
プレビューデータ

3.予算データと実績データを使い予実集計を行う

予算データと実績データをアプリ結合する

「アプリ結合」コマンドで予算と実績のレコードを横並びに結合することで、予算と実績を横に並べて比較する予実レイアウトを実現できます。

  1. アプリ結合コマンドを追加する
  2. 「予算アプリのレイアウトを変更」と「実績集計」を内部結合する
  3. 「元になるアプリの条件フィールド」と「結合するアプリの条件フィールド」を次のように設定する
    ・年度　年度
    ・月　月　
    ・営業担当者　営業担当者
プレビューデータ

必要なフィールドだけに絞り込む

  1. フィールド選択コマンドを追加する
  2. 次のフィールドを選択する
    ・年度 ・月 ・部門 ・営業担当者 ・予算 ・実績
プレビューデータ

krewDashboardで使用する日付フィールドを作成する

ここまで作成したデータには年度と月フィールドがそれぞれありますが、krewDashboardで設定を行う際には日付フィールドを使った方が見栄えよく設定を行うことができます。ここでは、実際の日付を基準に新たにフィールドを作成します。

年月フィールドを作成する

  1. データ編集コマンドを追加する
  2. 次のように設定する
  3. ・結果を保存するフィールド：「年月」という新しいフィールド
  4. ・編集方法：数式
  5. ・編集内容：年度&”-“&月
プレビューデータ

作成した年月フィールドのフィールドタイプを変更する

  1. フィールドタイプ設定コマンドを追加する
  2. 年月フィールドのフィールドタイプを「日付」に変更する
プレビューデータ

年月を実際の日付に直す

年度を基準として年月フィールドを作成していたため、実際の日付基準となるよう調整します。

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド：「年月」フィールドのデータを置換する
    ・編集方法：数式
    ・編集内容：IF(MONTH(年月)<4, DATE(YEAR(年月)+1,MONTH(年月),DAY(年月)), 年月)
プレビューデータ

4.出力する

  1. 出力アプリコマンドを追加して、アプリの選択で「予実_横積み_単一集計」アプリを選択する
  2. 出力方式に「更新」を選択し、「更新または追加」にチェックする
  3. データ編集フローのフィールドで、アプリのフィールドと対になるデータ編集フローのフィールドを選択する
  4. 更新キーとして、「年月」「営業担当者」にチェックする

出力結果

月ごとに集計されたデータが出力されました。この後、【2】のステップでこのデータをExcelライクに可視化していきます。

ワンポイント

krewDataで予実集計をする場合、差異や達成率もあわせて集計することが多いのですが、横積みのパターンではkrewDashboardでも差異や達成率が計算できるので、krewDataではあえて計算しません。
※値としてレコードに持たせておきたい場合には集計を行ってください。

【2】krewDashboardで予実データを可視化する（予実管理表の作成）

krewDashboardの設定を行う

出力アプリにkrewDashboardを適用する

krewDataで整形したデータを出力した「予実_横積み_複数集計」アプリにkrewDashboardを適用します。

アプリ設定内にある「プラグイン」でkrewDashboardを追加する

ワンポイント
krewDashboardを契約していない方はこちらのページからトライアルをお申込みください。
トライアル：https://krew.grapecity.com/trial/krewdashboard.htm

krewDashboardの設定をする

  1. krewDashboardのデザイン画面でピボットテーブルをドラッグ&ドロップで配置する
  2. データアプリとして「予実_横積み_単一集計」アプリを選択する
  3. 次のようにフィールド情報を行列値に設定する
    ・行：部門名、営業担当者
    ・列：（年月を展開して選択する）年、（年月を展開して選択する）月
    ・値：予算、実績

年度の開始月を4月に設定します。

差異と達成率を計算する

予算と実績値を元に差異と達成率を計算します。使用するのは「集計フィールド」という機能です。

  1. デザインタブに切り替える
  2. 「集計フィールド」をクリックする


  3. 差異を計算するために次のように設定する
    ・名前：差異
    ・数式：実績-予算


  4. 達成率を計算するために次のように設定する
    ・名前：達成率
    ・数式：実績/予算


  5. 集計フィールドを作成すると、赤枠内の場所に追加したフィールドが表示される


  6. 差異と達成率を値に追加する

書式を設定する

数値の頭に￥をつけたり、達成率が正しく表示されるように書式の調整を行います。

  1. ホームタブを開く
  2. 「標準の書式」内にある「数値」の横の「…」をクリックする


  3. 書式を通貨にするなど、必要に応じて設定する


  4. 達成率の書式はフィールド設定を開く

  5. 表示形式-から書式をパーセンテージに設定する

ここまでの設定でこのような画面が作成されます。

条件付き書式を設定する

予算と実績の列に背景色を設定します。

  1. デザインタブに切り替える
  2. 「条件付き書式」をクリックし、「新しいルール」を選択する


  3. 予算列を次のように設定する
    ・範囲：予算
    ・ルールの種類
    ・ルールの内容：年月[fiscalYear]=年月[fiscalYear]
    ・書式：背景色を好みの色に変更する

  4. 実績列も同様に次のように設定する
    ・範囲：実績
    ・ルールの種類
    ・ルールの内容：年月[fiscalYear]=年月[fiscalYear]
    ・書式：背景色を好みの色に変更する

ここまで設定できたらアプリを更新します。

参考：元のExcel

元々Excelで管理していたのがこのような表でした。

営業担当者ごと・部門ごとの集計を行う表

サンプルファイルのダウンロードはこちら
https://download.krew.mescius.jp/study/krewdata-drill/pratical/dt500-100aggregate-horizontal-multiple.zip

演習をお客様のkintone環境で試す

krewDataのはじめ方
実際の動作を確認できるテンプレートを公開しています。
こちらの記事でご紹介した内容をご自身の環境で試したい方はダウンロードしてご利用ください。
演習をお客様のkintone環境で試す