mitokubo様 前回は出勤簿のエクセルでお世話になりました。 今回、またエクセルでご教授頂きたく指名させていただ…

Question

mitokubo様
前回は出勤簿のエクセルでお世話になりました。
今回、またエクセルでご教授頂きたく指名させていただきました。
よろしくお願いします。

給与の解体です。1本の金額を基本給と含み残業代の2本に分けたいです。

例として、給与30万の場合、その中に45時間分の含み残業が入った場合はどのように計算して導いたらいいでしょうか。
含み残業額は1.25倍です。
最終的には支給額はまったく変わらないのですが、キリのいい金額で分けたいです。

基本情報として、月148.5時間の労働時間で計算します。
30万から50万まで1万刻みの給与です。
含み残業時間は、出来ればプルダウンで15、20、25、30、35、40、45としたいです。
30万の金額の横のセルに含み残業時間をプルダウンより選択し、基本給と定額残業代の2つの金額が計算され、最後にその2つが合算されたものがジャスト30万となるのが理想です。
千円単位、もしくは万単位に出来るでしょうか。
何か不足情報がありましたら追記いたします。

本来は、基本給があって、その時間給に対して含み残業時間数を1.25倍した金額を合算するのがいいのは分かっておりますが、
現行の支給額の変更はせずに、その中で分けるということを従業員も納得の上です。
雇用契約書を取り直し、給与明細にも記載する予定です。
含み残業時間を越えた分は別途支給します。

このような稚拙な説明でご理解いただけるでしょうか。
大変不安ですがどうぞよろしくお願いします。

Answer 1

データベースとなる表をシート１に用意します。
例えばA３セルから下方には基本給となる金額を入力します。
A３セルには200000、A４セルには210000、A５セルには220000・・・とA３３セルには500000まで入力します。
B３セルから下方には148.5時間で1時間当たりの賃金を表示させます。
B3セルには次の式を入力して下方にドラッグコピーします。

=IF(A3="","",A3/148.5)

C2セルからJ2セルまでには15、20，25、…50までの数値を入力します。残業時間とします。
C3セルには次の式を入力してJ3セルまで横にドラッグコピーしたのちに33行目まで下方にもドラッグコピーします。

=ROUND($A3+$B3*C$2*1.25,-2)

そこには各基本給で残業時間についての給与が示されることになります。
この表を利用してお望みの表をシート2に作成します。
例えばシート2のA1セルには給与、B1セルには残業時間、C1セルには計算により求めた基本給、D1セルには残業代、E1セルには計算により求めた給与の項目をそれぞれ入力します。
A2セルから下方には300000とでも入力します。
B2セルから下方には残業時間をプルダウンで表示させます。
C2セルから下方には次の式を入力して下方にドラッグコピーします。

=IFERROR(ROUND((A2-INDEX(Sheet1!C$3:J$33,COUNTIF(INDEX(Sheet1!C$3:J$33,1,MATCH(B2,Sheet1!C$2:J$2,0)):INDEX(Sheet1!C$3:J$33,31,MATCH(B2,Sheet1!C$2:J$2,0)),"<="&A2),MATCH(B2,Sheet1!C$2:J$2,0)))/(INDEX(Sheet1!C$3:J$33,COUNTIF(INDEX(Sheet1!C$3:J$33,1,MATCH(B2,Sheet1!C$2:J$2,0)):INDEX(Sheet1!C$3:J$33,31,MATCH(B2,Sheet1!C$2:J$2,0)),"<="&A2)+1,MATCH(B2,Sheet1!C$2:J$2,0))-INDEX(Sheet1!C$3:J$33,COUNTIF(INDEX(Sheet1!C$3:J$33,1,MATCH(B2,Sheet1!C$2:J$2,0)):INDEX(Sheet1!C$3:J$33,31,MATCH(B2,Sheet1!C$2:J$2,0)),"<="&A2),MATCH(B2,Sheet1!C$2:J$2,0)))*10000+INDEX(Sheet1!$A$3:$A$33,COUNTIF(INDEX(Sheet1!C$3:J$33,1,MATCH(B2,Sheet1!C$2:J$2,0)):INDEX(Sheet1!C$3:J$33,31,MATCH(B2,Sheet1!C$2:J$2,0)),"<="&A2)),-2),"")

D2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",ROUND(C2/148.5*B2*1.25,-2))

E2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",C2+D2)