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

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

500-80 | 予実管理

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

営業担当者、部門単位の2軸で集計しているExcelの表

使用する製品

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

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

予算管理アプリ

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

案件管理アプリ

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

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

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

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

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

予算アプリに登録してある予算データを整形します。
なお、krewDataの設定内容は「予実_縦積み_単一集計」と同じになります。

入力アプリを設定する

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

当日の年度を抽出する

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

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

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

「当日の年度」フィールドが新たに追加された

今年度予算に絞り込む

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

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

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

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

プレビューではデータがこのようになります。

ワンポイント

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

予実区分を追加する

あとで案件アプリのデータと結合した時に、「これは予算アプリのデータ」「これは案件アプリのデータ」と識別できるように、予実区分フィールドを新たに作成します。

  1. 定数の追加コマンドを追加する
  2. 次のように設定する
    ・定数を保存する新しいフィールド名：予実区分
    ・新しいフィールドのフィールドタイプ：文字列（1行）
    ・新しいフィールドの保存する定数：予算

ここまでの設定で予算データを次のように作成しました。

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

  1. フィールド選択フィールドを追加する
  2. 使用するフィールドに次を追加：年度、月、部門、営業担当者、予算、予実区分
    ※「予算」はフィールド名を「金額」に変更しておく。これは後で実績データとレコード結合する際に金額管理用のフィールドとしてうまく結合するための設定。
プレビューデータ

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

入力アプリを設定する

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

年度を抽出する

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

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

月を抽出する

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

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

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

予実区分を追加する

あとで案件アプリのデータと結合した時に、「これは予算アプリのデータ」「これは案件アプリのデータ」と識別できるように、予実区分フィールドを新たに作成します。

  1. 定数の追加コマンドを追加する
  2. 次のように設定する
    ・定数を保存する新しいフィールド名：予実区分
    ・新しいフィールドのフィールドタイプ：文字列（1行）
    ・新しいフィールドの保存する定数：実績
プレビューデータ

フィールド情報を調整する

レコード結合する際に項目同士のフィールドタイプ、フィールド名が揃っていないと異なる項目として識別されます。予算データを整形した際に「月」は文字列（1行）に設定していたため、ここでも同様に文字列（1行）となるよう設定を行います。

  1. フィールドタイプ設定コマンドを追加する
  2. 次のように設定する
    ・変更するフィールド：月
    ・新しいフィールド名：月
    ・新しいフィールドタイプ：文字列（1行）

予算と実績をレコード結合する

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

  1. レコード結合コマンドを追加する
  2. 結合方法を選択する：フィールドタイプ設定（2）
予算と実績データが集約された

3.差異と達成率を計算する

ここまで作成してきた予算・実績データを元に差異と達成率を計算します。

アプリ結合して予算と実績を1レコードにまとめる

  1. アプリ結合コマンドを追加する
  2. フィールド選択（2）とフィールドタイプ選択（2）を内部結合する
  3. 「元になる条件フィールド」と「結合するアプリの条件フィールド」を次のように設定する
    ・年度　年度
    ・月　月
    ・営業担当者　営業担当者
プレビューデータ

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

  1. フィールド選択コマンドを追加する
  2. 次のフィールドを選択する
    ・年度 ・月 ・部門 ・営業担当者 ・金額 ・金額_1
    ※「金額」は「予算」に、「金額_1」は「実績」とフィールド名を変更する
プレビューデータ

差異を計算する

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド名：「差異」という新しいフィールドを作成
    ・編集方法：数式
    ・編集内容：実績-予算
プレビューデータ

達成率を計算する

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド名：「達成率」という新しいフィールドを作成
    ・編集方法：数式
    ・編集内容：ROUND(実績/予算,2)
プレビューデータ

差異と達成率をフィールドに変換する

  1. 列ー行変換コマンドを追加する
  2. 変換する列名を保存する新しいフィールド名：予実区分
  3. 変換する列の値を保存する新しいフィールド名：金額
  4. 「行に変換する列」と「変換した行に保存する元の列を識別する値」を次のように設定する
    ・差異　差異
    ・達成率　達成率
プレビューデータ

金額フィールドのフィールドタイプを調整する

  1. フィールドタイプ設定コマンドを追加する
  2. 金額フィールドのフィールドタイプを「数値」に変更する

4.予算・実績と差異・達成率をレコード結合する

別々に作成していた各データを結合します。

  1. レコード結合コマンドを追加する
  2. 結合方法：共通する項目のみ含める
プレビューデータ

krewDashboardで使用するための年月フィールドを作成する

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

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

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

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

年月を実際の日付に直す

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

  1. データ編集コマンドを追加する
  2. 次のように設定する
    ・結果を保存するフィールド：「年月」フィールドのデータを置換する
    ・編集方法：数式
    ・編集内容：IF(MONTH(年月)<4, DATE(YEAR(年月)+1,MONTH(年月),DAY(年月)), 年月)
1月～3月が正しい日付に変換された

5.出力アプリを設定する

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

出力結果

出力アプリにこのようにデータが出力されました。

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

krewSheetの設定を行う

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

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

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

krewSheetを設定する

  1. krewSheetのデザイン画面でモードをXrossモードに切り替える


  2. データアプリとして「予実_縦積み_単一集計」アプリを選択する
  3. 次のようにフィールド情報を行列値に設定する
    ・行：部門名、営業担当者、予実区分
    ・列：（年月を展開して選択する）年月
    ・値：金額

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

小計と総計を非表示にする

小計・総計行が表示されないよう設定します。

  1. デザインタブに切り替える

  2. 「小計」をクリックし、「小計を表示しない」を選択する


  3. 「総計」をクリックし、「行と列の集計を行わない」を選択する

ここまでの設定でこのような状態になります。

書式を設定する

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

  1. ホームタブを開く
  2. 「書式」をクリックする

  3. 書式タブを開く
  4. 「通貨」の中から設定したい表示形式を選択する

  5. 設定後は各数値に￥マークがつき、見やすく変化する

達成率の書式

直前の設定を行うと達成率の書式も￥マークで表示されてしまうので、達成率は個別に設定を加えます。

  1. 条件付き書式のメニュー内から「新しいルール」を選択する


  2. 次のように設定する
    ・範囲：合計 金額
    ・ルールの種類：数式を使用して、書式設定するセルを決定
    ・ルールの内容：予実区分="達成率"



    ・書式：書式をクリックしてパーセンテージを選択する

ここまでの設定で書式を整えることができました。

部門別集計を行う

カスタム小計機能を使い、部門ごとに「予算合計」「実績合計」「達成率」を計算します。

  1. 行に設定した「部門」フィールドの設定を開く（歯車マーク）


  2. カスタム小計タブに切り替える
  3. 第一営業部の予算合計を次のように設定する
    ・カスタム小計名：第一営業部予算合計
    ・挿入位置：第一営業部　の後
    ・数式：SUMIFS(金額[Sum],予実区分,"予算",部門,"第一営業部")


  4. 第一営業部の実績合計を次のように設定する
    ・カスタム小計名：第一営業部実績合計
    ・挿入位置：第一営業部　の後
    ・数式：SUMIFS(金額[Sum],予実区分,"実績",部門,"第一営業部")


  5. 第一営業部の達成率を次のように設定する
    ・カスタム小計名：第一営業部達成率
    ・挿入位置：第一営業部　の後
    ・数式：SUMIFS(金額[Sum],予実区分,"実績",部門,"第一営業部")/SUMIFS(金額[Sum],予実区分,"予算",部門,"第一営業部")

※営業部が複数ある場合には、同様の設定を繰り返す

条件付き書式を設定する

条件付き書式を設定して、予算と実績、達成率の行にそれぞれ背景色を設定します。

  1. デザインタブに切り替える
  2. 「書式」セクションにある「新規ルール」をクリックする


  3. 次のように設定する
    ・範囲：金額
    ・ルールの種類：数式を使用して、書式設定するセルを決定
    ・ルールの内容：予実区分="*予算*"
    ・書式：背景色を希望の色に設定


  4. 実績行についても次のように設定する
    ・範囲：金額
    ・ルールの種類：数式を使用して、書式設定するセルを決定
    ・ルールの内容：予実区分="*実績*"
    ・書式：背景色を希望の色に設定


  5. 達成率の行についても次のように設定する
    ・範囲：金額
    ・ルールの種類：数式を使用して、書式設定するセルを決定
    ・ルールの内容：予実区分="*達成率*"
    ・書式：背景色を希望の色に設定

ワンポイント
予実区分=”達成率” のように*（アスタリスク）を使って指定すると、部門が複数ある場合にも一括で書式の設定を行うことができます。

ここまで設定してアプリを更新すると、次のような表が確認できるようになります。

参考：元のExcel

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

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

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

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