拠点ごとの予実状況を可視化する

520-30 | 予実管理

完成イメージ

予実管理アプリ

• 拠点ごとに部門-担当者と掘り下げて予実状況が確認できる
• 各部門、拠点単位に集計を行っている
• 予算、実績、達成率がを縦に並べ、コンパクトかつ隣り合う月と比較しやすいレイアウト

アプリの構成

• 営業担当者の予算と実績を管理するアプリ
• 「予実区分」で予算/実績を選択し、そのレコードがどちらの区分に属するか指定する

• 予算は毎月のレコードを作成しておき、実績は都度実績があがるたびに登録する運用

１.krewSheetのモードを「Xrossモード」に切り替える

1. 「Sheet」というロゴをクリックする
2. 「Sheet/Xross」のロゴが表示されるので、Xrossを選択してXrossモードに切り替える

モードを切り替えると、画面が次のように変化します。

２.行にフィールドを配置する（オレンジのエリア）

1. 行に次のフィールドを配置する
   ・拠点名 ・部門名 ・担当者 ・予実区分
   

ポイント

ここでは、列に「 拠点名-部門名-担当者-予実区分 」のように階層順に並べました。このような順番で並べると、以下のように拠点名と部門名、担当者をセル結合した状態で見せることができます。

３.列にフィールドを配置する（黄色のエリア）

1. 列に「年-月」で表示する形式の「予実年月日」フィールドを配置する
   ※「年-月」などの形式は、予算年月横の▼をクリックすると表示されます
   
   
   
2. 列フィールドの歯車マークをクリックして列設定を開く
   
   
   
3. カスタムラベルを「予実年月」に設定する
   
   
   
4. 設定後はこのような画面になります
   

４.値にフィールドを配置する（青のエリア）

1. 値項目に次のフィールドを設定する
   ・金額
   
   
   
2. 列フィールドの設定を開く
3. カスタムラベルを「金額」に変更する
   
   
   
4. 設定後はこのようになります
   

ワンポイント

プレビューで表示できるのは一覧設定のソート順に上位500レコード分のみです。
一覧のソート順によっては、プレビューで確認できる内容と上記の内容が異なったり、配置したはずのフィールドの値が表示されないかもしれません。その場合には、実行画面で設定の出来上がりを確認しながら設定してみてください。

また、列にかなり先の日程を指定した場合、表示件数が非常に多くなります。プレビュー画面で表示できる行列の最大数は１００ × １００ 行までとなります。

５.個人の達成率を計算する

赤い丸で印をつけた達成率の計算を行います。ここでは、個人ごとの達成率を計算します。

Xrossモードの「カスタム小計」という機能を使って設定を進めます。

1. 行に設定した「予実区分」のフィールド設定を開く
   
   
   
2. 設定のタブを「カスタム小計」に切り替える
   
   
   
3. 次のように設定する
   ・カスタム小計名：達成率
   ・挿入位置：
   – 拠点名　=　本社
   – 部門名　=　第1営業部
   – 担当者名　=　大神 十介
   – 予実区分　=　実績　　　の後
   ・数式：大神さんのレコードを抽出し、実績÷予算を計算している
   SUMIFS(金額[Sum],部門名,"=第1営業部",担当者,"=大神 十介",予実区分,"=実績") / SUMIFS(金額[Sum],部門名,"=第1営業部",担当者,"=大神 十介",予実区分,"=予算")
   
   
   
   この達成率の計算は担当者の数だけ行う必要があります。大神さんの設定内容を参考に、実際にビューを作る際には他の人も同様に設定を行ってみてください。以下の項目は変更が必要です。
   ・挿入位置
   ・数式

ワンポイント

今回は予算と実績が別レコードに登録される運用であるためカスタム集計機能を使って計算を行いました。予算と実績が同じレコード内に存在する構成の場合は、集計フィールドを使うと同様の達成率の集計が行えます。
〈予実①のリンクを挿入〉

達成率の書式設定

ここまで設定すると、次のように表示されます。達成率はパーセント表示したいため、書式設定を行います。

条件付き書式の設定はリボンのメニュー「ホームタブ」内にある「条件付き書式」/「新規ルール」から開始します。

1. 次のように設定する
   ・範囲：金額
   小計と総計を含まない
   ・数式を使用して、書式設定するセルを決定
   ・数式：予実区分=”達成率”
   
   
   
   
2. 書式をクリックする
3. 書式タブに切り替える
4. 次のように設定する
   カテゴリ：パーセンテージ
   小数点以下の桁数：2
   

すべて設定すると、このように表示されます。

６.部門ごとに予算・実績・達成率の計算を行う

部門ごとに予算・実績・達成率の計算を行います。

第1営業部の予算

1. 行に設定した部門名の歯車をクリックしてフィールド設定を開く
   
   
   
2. カスタム小計タブに移動する
3. 次のように設定する
   ・カスタム小計名：第1営業部予算
   ・挿入位置：
   – 拠点名　=　本社
   – 部門名　=　第1営業部　の後
   ・数式：部門名が第1営業部 かつ 予実区分が予算のレコードを対象に計算
   SUMIFS(金額[Sum],部門名,"=第1営業部",予実区分,"=予算")
   
   

第1営業部の実績

1. フィールド設定画面で次のように設定する
   ・カスタム小計名：第1営業部実績
   ・挿入位置：
   – 拠点名　=　本社
   – 部門名　=　第1営業部　の後
   ・数式：部門名が第1営業部 かつ 予実区分が実績のレコードを対象に計算
   SUMIFS(金額[Sum],部門名,"=第1営業部",予実区分,"=実績")
   

第1営業部の達成率

1. フィールド設定画面で次のように設定する
   ・カスタム小計名：第1営業部達成率
   ・挿入位置：
   – 拠点名　=　本社
   – 部門名　=　第1営業部　の後
   ・数式：第1営業部の実績 ÷ 第1営業部の予算
   SUMIFS(金額[Sum],部門名,"=第1営業部",予実区分,"=実績") / SUMIFS(金額[Sum],部門名,"=第1営業部",予実区分,"=予算")
   

公開しているテンプレートや利用例では、第1営業部のほかに「第2営業部」「DX推進営業部」「ICT支援営業部」がデモデータに含まれます。第1営業部と同様に設定を行って下さい。

７.拠点ごとに予算・実績・達成率の計算を行う

拠点ごとに予算・実績・達成率の計算を行います。ここで表示している「本社」は第1営業部と第2営業部で構成されています。

本社予算

1. 行に設定した拠点名の歯車をクリックし、フィールド設定画面を開く
   
   
   
2. カスタム小計タブに切り替える
3. 次のように設定する
   ・カスタム小計名：本社予算
   ・挿入位置：
   拠点名　=　本社　の後
   ・数式：拠点名が本社 かつ 予実区分が予算のレコードを対象に計算
   SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=予算")
   

本社実績

1. フィールド設定で次のように設定する
   ・カスタム小計名：本社実績
   ・挿入位置：
   拠点名　=　本社　の後
   ・数式：拠点名が本社 かつ 予実区分が実績のレコードを対象に計算
   SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=実績")
   

本社達成率

1. フィールド設定で次のように設定する
   ・カスタム小計名：本社達成率
   ・挿入位置：
   拠点名　=　本社　の後
   ・数式：拠点名が本社 かつ 予実区分が実績のレコード ÷ 拠点名が本社 かつ 予実区分が予算のレコード
   SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=実績") / SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=予算")
   

関西支社も同様に設定を行います。全社の計算は設定の図を添付します。

全社の計算

予算
数式：本社と関西支社の予算を足し算
SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=予算") + SUMIFS(金額[Sum],拠点名,"=関西支社",予実区分,"=予算")

実績
数式：本社と関西支社の実績を足し算
SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=実績") + SUMIFS(金額[Sum],拠点名,"=関西支社",予実区分,"=実績")

達成率
数式：本社と関西支社の実績 ÷ 本社と関西支社の予算
(SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=実績") + SUMIFS(金額[Sum],拠点名,"=関西支社",予実区分,"=実績")) / (SUMIFS(金額[Sum],拠点名,"=本社",予実区分,"=予算") + SUMIFS(金額[Sum],拠点名,"=関西支社",予実区分,"=予算"))

８.小計行の背景色を変更する

小計行（カスタム小計）では背景色を変更しています。

条件付き書式の設定はリボンのメニュー「ホームタブ」内にある「条件付き書式」/「新規ルール」から開始します。

予算行の背景色設定

ここまで設定してきた部門と支社の予算を一括で設定します。

1. 次のように設定する
   ・範囲：金額
   　小計と総計を含めない
   ・ルールの種類：数式を使用して、書式設定するセルを決定
   ・ルールの内容：
   OR(COUNTIF(拠点名,”予算”),COUNTIF(部門名,”予算”))
   ・背景色：青
   

数式の補足

今回数式を次のように設定しました。
OR(COUNTIF(拠点名,”*予算”),COUNTIF(部門名,”*予算”))

前半の青で記述した部分は支社集計を、後半の赤で記述した部分は部門集計にかかる数式になります。

同様の手順で実績と達成率の行についても設定を行います。

実績

• 数式：
  OR(COUNTIF(拠点名,"*実績"),COUNTIF(部門名,"*実績"))
  

達成率

• 数式：
  OR(COUNTIF(拠点名,"*達成率"),COUNTIF(部門名,"*達成率"))
  

９.運用について

Xrossモードで表示した時に背景色がグレーになるセルはXross上で編集が行えないセルです。今回の例では、実績は複数のレコードで構成されているため、編集が行えません。

詳細機能を使うと、集計値を見ながら入力を行うことができます。必要に応じて設定してください。

サンプルファイルのダウンロードはこちら
https://download.krew.mescius.jp/study/krewsheet-drill/ks520-30budget-category.zip