チーム全体の予算消化率を正しく計算するには？
予算と支出をそれぞれ合計してから、支出合計を予算合計で割ります。個別の使用率を足した値や単純平均とは異なります。
予算100を使い切った担当者と、予算900を使っていない担当者がいる場合、チーム全体の使用率は10%です。2人の使用率の平均50%では金額の違いが反映されません。JOIN SHEETで元の金額を先に合計して計算します。
比率ではなく元の金額を用意する
次の架空データでは、予算と支出の対象期間・通貨をそろえています。Cチームは予算が0なのに支出が20あり、元の記録を確認する必要があります。ここでは支出20を合計に含め、計算できない消化率は空欄にします。
| チーム | 担当 | 予算 | 支出 |
|---|---|---|---|
| A | U1 | 100 | 100 |
| A | U2 | 900 | 0 |
| B | U3 | 200 | 50 |
| B | U4 | 200 | 150 |
| C | U5 | 0 | 20 |
2つの合計から比率を計算する
- シートを結果の基準に選び、予算と支出が数値であることを確認します。
- 「結果を整理」で「集計」を追加し、「まとめる項目」にチームを選びます。最初の集計を予算の「合計」に変え、「結果の項目名」に予算合計と入力します。「集計を追加」を押して支出の「合計」も作り、こちらの「結果の項目名」には支出合計と入力します。
- 集計の下に「計算」を追加します。「比率 (%)」のAを支出合計、Bを予算合計にし、名前を「予算消化率 (%)」にします。
- 「適用」を押し、合計と比率を一緒に確認します。
Aチームは10%、Bチームは50%です
Aは100÷1000×100=10、Bは200÷400×100=50です。Cは分母0のため空の値ですが、支出20は残ります。0%と読んだり、報告からそのまま除外したりしないでください。
| チーム | 予算合計 | 支出合計 | 予算消化率 (%) |
|---|---|---|---|
| A | 1000 | 100 | 10 |
| B | 400 | 200 | 50 |
| C | 0 | 20 | 空の値 |
予算が空欄なのに支出だけを足すと、予算と支出で集計対象がそろわなくなります。先に抜けている金額を確認してください。予算0の行を集計対象にしないと決めた場合は、予算だけでなく支出も含めて、その行全体を集計前に除きます。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Percentage summaries calculating as a roll upの質問を参考に、独自の架空データで手順を作成しました。
