【スプレッドシート】上司部下の立場ごと傾斜割り勘計算

序列１位の割り勘金額は、下記式でベースを算出します。

飲み会費用総額 ✕ 序列１位組負担額 / 序列１位参加人数
＝ 飲み会費用総額 ✕ （序列１位負担能力 / 序列１位～５位負担能力） / 序列１位参加人数
＝ 飲み会費用総額 ✕ （（序列１位負担係数✕人数） / （（序列１位負担係数✕人数）＋（序列２位負担係数✕人数）＋（序列３位負担係数✕人数）＋（序列４位負担係数✕人数）＋（序列５位負担係数✕人数））） / 序列１位参加人数

この内容を、関数に落とし込むと。
＝ $B$11 ✕ （（B2 ✕ C2） / SUMPRODUCT（$B2:B$6,$C2:C$6） / B2）

『SUMPRODUCT関数』は配列どおしを掛け合わせて、その総和を返してくれる関数です。今回のに、もってこいの関数ですね。

あわせて読みたい

【SUMPRODUCT スプレッドシート】配列の積の総和を求める GoogleスプレッドシートやExcelで、2つの配列の各要素をかけあわせた総和を算出することができる【SUMPRODUCT関数】について説明していきます。Product（積）をSum(和)…

また、これで算出された金額について、切り上げを盛り込むので、『CEILING関数』を用います。
なので、E2セルに入る関数は、このようになります。

=ceiling($B$11*(B2*C2/sumproduct($B2:B$6,$C2:C$6))/B2,D2)

あわせて読みたい

【CEILING関数】数値を500刻みなど半端な数ごとに切り上げたい Googleスプレッドシートやエクセルなどで、５００円刻みに切り上げたいなど、『ROUNDUP関数』では、対応できない切り上げの処理をしたい場合には、『CEILING関数』が便…

序列２位の割り勘金額は、下記式でベースを算出します。

（飲み会費用総額 － 序列１位組支払い総額） ✕ 序列２位組負担額 / 序列２位参加人数
＝ （飲み会費用総額 － 序列１位組支払い総額） ✕ （序列２位負担能力 / 序列２位～５位負担能力） / 序列２位参加人数
＝ （飲み会費用総額 － 序列１位組支払い総額） ✕ （（序列２位負担係数✕人数） / （（序列２位負担係数✕人数）＋（序列３位負担係数✕人数）＋（序列４位負担係数✕人数）＋（序列５位負担係数✕人数））） / 序列２位参加人数

序列１位組が払ってくれた分を除いた飲み会費用について、序列２位組の負担能力を掛けて、それを序列２位参加人数で割るといった計算になります。

また、この計算式に、そもそもこの序列の方がいなかったら、割り勘金額はもちろん０円だよねという条件式と、『CEILING関数』使っての切り上げ処理を加えます。

=if(B3=0,0,ceiling(($B$11-sumproduct($B$2:B2,$E$2:E2))*(B3*C3/sumproduct($B3:B$6,$C3:C$6))/B3,D3))

このような感じになりますね。

序列３位以降も同じような考え方で、その序列よりも上の序列組が払ってくれたお金を飲み会総額から除いた金額について、その序列の負担能力の割合を加味して算出するといった感じです。