エクセルで賞与に個人評価を反映させる

エクセルで賞与の計算を行います。

①賞与額 ②個人評価 ③最終賞与額
A 1,000 1.2 　　　 2487.8
B 2,000 0.9 　　　 1865.9
C 3,000 1.1 　　　 2280.5
D 2,500 0.9 　　　 1865.9
計 8,500 4.1 　　　 8,500

今年から賞与計算方法が変わり、「個人評価」を反映させることになりました。
ただし、賞与の予算はすでに決まっており、変えることはできません。
上記の①賞与額の合計8,500が予算です。人事評価を元にこれを再分配します。
人事評価後の賞与額の合計は、評価前の8,500と同額にならなければなりません

③単純に個人評価の割合だけで8500を分配したもので、合計額は同じになりますが、①の賞与額がまったく反映されず、極端な差額が生じてしまいます
①②をうまく反映させつつ8500円を分け合う方法はありませんでしょうか？

わかりづらい説明で申し訳ありません。
よろしくお願いいたします。

No.7 回答者： 真魚 回答日時： 2016/06/12 01:02

「③最終賞与額」は、どういう計算で求めた値でしょうか。

単純に ①×②＝③ という計算であれば、以下のようになるはずですね。

　　①賞与額　②個人評価　③最終賞与額（増減額）
Ａ　　1,000　　　1.2　　　　1,200　　（＋200）
Ｂ　　2,000　　　0.9　　　　1,800　　（－200）
Ｃ　　3,000　　　1.1　　　　3,300　　（＋300）
Ｄ　　2,500　　　0.9　　　　2,250　　（－250）
計　　8,500　　　4.1　　　　8,550　　（＋ 50）

②の個人評価ポイント（１±アルファ）をそのまま増減額の係数にすると、
①のベース額の違いによって増減額の合計が「プラスマイナスゼロ」になりません。
この場合は差し引き50の差異（この場合は原資が不足）が生じています。

この差異（過不足）を按分して補正する必要があります。
つまり、誰かを増額した分だけ誰かを減額して、プラスマイナスゼロにするわけです。

単純に過不足の比率（8500÷8550≒0.9942）を掛けると、こんな数字になります。
少数点以下は四捨五入してあります。値が細かすぎるという場合は、下1桁～2桁がゼロになるように丸めて、全体でプラスマイナスゼロになるよう調整してください。

　　①賞与額　②個人評価　③最終賞与額（増減額）　④補正後（増減額）
Ａ　　1,000　　　1.2　　　　1,200　　（＋200）　　1,193 （＋193）
Ｂ　　2,000　　　0.9　　　　1,800　　（－200）　　1,789 （－211）
Ｃ　　3,000　　　1.1　　　　3,300　　（＋300）　　3,281 （＋281）
Ｄ　　2,500　　　0.9　　　　2,250　　（－250）　　2,237 （－263）
計　　8,500　　　4.1　　　　8,550　　（＋ 50）　 　8,500 （± 　0）


参考までに、質問文の③の値が正しいと仮定して、③÷①＝②’ を求めると以下のようになります。この値は変です。

　　①賞与額　③最終賞与額　②’ 個人評価？
Ａ　　1,000　　　2,487.8　　　2.488
Ｂ　　2,000　　　1,865.9　　　0.933
Ｃ　　3,000　　　2,280.5　　　0.760
Ｄ　　2,500　　　1,865.9　　　0.746
計　　8,500　　　8,500.1　　　4.927

No.6 回答者： 銀鱗 回答日時： 2016/06/11 20:53

賞与として用意している8500円（？）に、個人評価の合計から個人の評価の比率をかけて分配する金額を決めればよい。

Aの人は
　（1000+2000+3000+2500）×（1.2÷(1.2+0.9+1.1+0.9)）＝2487.8
こんな感じの計算。
…なんでしょ？

これを
　1000に対して重みを付けたいということ。
ならば、1000に対して単純に1.2倍。
すると
A　1200
B　1800
C　3300
D　2250
合計は8550になる。これは金額の合計ではなく分配のための重みの合計。
これを基に8500円を分配だ。

すると、Aの人は
　8500円 ×（1200÷8550）＝1192.98円
となる。
同様に他の人も計算すると、
A　1192.98円
B　1789.47円
C　3280.70円
D　2236.84円
になる。
（簡易的な四捨五入の関係で合計は8500円にはなりません）

あとはこれを普通にExcelで計算させるだけです。

※単位が円なのかk円なのか10k円（1万円）なのか分からないので、適当に「円」としてみました。

この回答へのお礼

うわあ、すごい。できました。ありがとうございます。
皆様もありがとうございました。

お礼日時：2016/06/11 21:55

No.5 回答者： tom04 回答日時： 2016/06/11 19:23

こんばんは！

具体的な内容は無視して、質問通りの数値にしたいわけですよね？
考え方としては「構成比」と一緒だと思います。

Aさんは合計「4.1」のうちの「1.2」を占める。
Bさんは合計「4.1」のうちの「0.9」を占める。・・・
という考え方でやればお望みの数値に合致すると思います。
A列にA～Dが入っていて、D列に表示するとすると
D２セルに
=B$6/C$6*C2
という数式を入れフィルハンドルで下へコピーではどうでしょうか？m(_ _)m

No.4 回答者： angkor_h 回答日時： 2016/06/11 18:55

何を調整するかによると思いますが、方法としては、以下があると思います。

1)　①②の個々を手で変更する。
2)　①全部を同比率(或いは同額)で変更する。
3)　②全部を同比率(或いは同額)で変更する。
4)　上3件を併用する。

2)3)については、以下を用意すればよいと思います。
元値、変更値、変更後の値

自動収束は、主たる変更目標と従たる変更箇所の選択が不明なので無理、と思います。

No.3 回答者： soixante 回答日時： 2016/06/11 18:53

Aの賞与が　1,000　だけど、評価は2割増し、というのであれば、1,200　支給。

Bの賞与は　2,000　だけど、評価は９掛け、というのであれば、1,800　支給。
Cの賞与は　3,000　だけど、評価は１割増し、というのであれば、3,300　支給。
Dの賞与は　2,500　だけど、評価は９掛け、というのであれば、2,250　支給。

あれ、支給額の合計が、8,550円　で予算オーバーだ。

良く見てみたらそりゃそうだ。
評価の合計が、4.1　になっています。4.0にしなくては。
予算総額が決まっていて、4人でそれを取り合う（麻雀の点棒と同じ）なので、ゼロサム。
全員　1.0からスタートで、誰かを上げたら、全体として下げる人も出てきて、あくまで全体では　4.0を維持。

No.2 回答者： ママチャリ 回答日時： 2016/06/11 18:52

各人の賞与額×個人評価で個人ポイントを求めます。

個人ポイントを合計して全体のポイントを求めます。
その上で、各人の最終賞与額を次の式で求めます。
個人ポイント／全体ポイント×賞与の予算

No.1 回答者： mai3456 回答日時： 2016/06/11 18:33

いまいちわかりませんが、

別途評価条件の表をつくり、範囲を定めておけば出来ると思います。

ＩＮＤＥＸとＭＡＴＣＨ関数をうまくつかってみてください。

http://www.d3.dion.ne.jp/~jkondou/excelvba/GA2.htm