Excelで複数グループの物品配分を管理していると、一部グループが不足するケースがあります。充足率に応じて不足分を他グループから公平に補てんしたい場合、計算式を組むことで自動化が可能です。
1. 充足率を基準に負担割合を求める
不足分547個を補う際に、各グループの充足率に反比例して負担割合を決めます。充足率が高いグループほど多く補てんする方法です。
まず、各グループの充足率を合計します：199% + 162% + 149% = 510%
次に各グループの負担割合を計算します。
- グループB：199 / 510 ≈ 0.39
- グループC：162 / 510 ≈ 0.318
- グループD：149 / 510 ≈ 0.292
2. 不足分を各グループに割り当てる
負担割合を用いて、各グループが提供する個数を計算します。
- グループB：547 × 0.39 ≈ 213個
- グループC：547 × 0.318 ≈ 174個
- グループD：547 × 0.292 ≈ 160個
合計で547個となり、正しく不足分を補えます。
3. Excelで計算式を組む方法
Excelでは以下のように計算式を作ります。
- 充足率の合計セルを作る（例：
=B2+C2+D2） - 各グループの負担数を計算（例：
=547*(B2/合計セル)）
この方式で計算すれば、充足率が変動しても自動で負担割合が変化します。
4. 今後の運用ポイント
・不足分や充足率を変えた場合も、Excelの計算式が自動で補正してくれます。
・整数値に丸める場合はROUND関数を使用して調整してください。
まとめ
不足分を他グループから公平に補てんするには、充足率に基づいて負担割合を算出し、不足数に乗じる方法が有効です。Excelで計算式を組むことで、今後同様のケースでも自動計算が可能になります。
コメント