エクセルでの給与の計算式を教えてください

質問

会社の賞与計算をエクセルで行いたいのですが、級によって加算率が異なるので、うまく計算式が作れません。

(1)一律部分
　給与月額＋扶養手当＋（（給与月額＋扶養手当）×12％）
(2)職務加算部分
　（給与月額＋（給与月額×12％)）×加算部分
　　　　　　　　　　　　　　　　　　10、9級　20％
　　　　　　　　　　　　　　　　　　8、7級　　15％
　　　　　　　　　　　　　　　　　　6級　　　10％
　　　　　　　　　　　　　　　　　　5、4級　　5％
(3)管理職手当部分
　給与月額×加算部分
　　　　　　10、9級　20％
　　　　　　8、７級　15％
(4)　(1)～(3)を足したものに支給率5.25をかけます。

仮に、Ａ列に級、Ｂ列に給料月額、Ｃ列に扶養手当が入っているとして、Ｄ列に計算結果を表示するとなると、どのような計算式になるのでしょうか？
表を作ってそれにバッテングさせた方がいいのか、本を見ても解決方法が見つかりません。どなたかいい方法をご存知でしたら、教えてください！！

回答No.9

trytrytry

> ＰＳ．
> trytrytryさん
> =CHOOSE()は問題ないと思いますが・・・
> 元の式の要素が少ないような？
おぉ！？確かに．．．(1)と(2)の計算がくっついてますね。(@.@;
=((B1+C1+((B1+C1)*0.12))+((B1+(B1*0.12))*CHOOSE(A1,,,,0.05,0.05,0.1,0.15,0.15,0.2,0.2))+(B1*CHOOSE(A1,,,,,,,0.15,0.15,0.2,0.2)))*5.25
とりあえず修正するとこんなかな？合ってます？
計算ごとにかっこで括ってはみましたが．．．

回答No.8

comv

みなさん　こんばんは

私のレスでは補足要求が無かったのでホッとしております(^^;
で興味があったので 級を数式だけで率に変換する 方法を

（２）の率条件
　●　=((CEILING(A1+2,2)-(A1=5)*2)-4)*(A1>3)/40
A1
10→0.2
9→0.2
8→0.15
7→0.15
6→0.1
5→0.05
4→0.05
3以降全て0

数式に当てはめると
　=B1+C1+(B1+C1)*●

（３）の率条件
　■　=((CEILING(A1+2,2)-(A1=5)*2)-4)*(A1>6)/40
A1
10→0.2
9→0.2
8→0.15
7→0.15
6以降全て0

数式に当てはめると
　=(B1*1.12)*■
－－－－－－－－－－－－－－－－－－－－－
ＰＳ．
trytrytryさん
=CHOOSE()は問題ないと思いますが・・・
元の式の要素が少ないような？

回答No.7

trytrytry

> 実際に入力してみたのですが、何がいけないのか計算が合わないのです。
> CHOOSE(A1,,,,0.05,0.05,0.1,0.15,0.15,0.2,0.2)のＡ１からの
> 「てんてんてん」もちゃんと数えて入力してみたのですが・・・。
計算が合わない理由はファイルを見てみないとなんともいえませんが、
適当な数値(例：給与月額=150,000、扶養手当=30,000、級=7等)
を入れたとき、予定の数値(金額)とCHOOSEを使った数値(金額)が
それぞれいくつになるかを、例のような入力値と一緒に掲載していただければ
何かわかるかもしれません。

> ところで、この最初の「てんてん」は、「加算がない級は省略」
> という意味なのでしょうか？
まさに想像通りです、1～3級はありえない？為、省略しています。

回答No.6

koba004

>IF(A1>0.1,B1*A1,0)というのは、どのような計算をしているのでしょうか？
>ＩＦ関数は、参考書を読んでも良く分からなくて・・・。
>
例えば、
IF(A=1,B,C)
の場合、
A=1の条件が成り立つと"B",成り立たないと"C"の値が返されます。

IF(A1>0.1,B1*A1,0)
の場合は、
A1の値が0.1を越えていた場合、"B1*A1"の値になり、
A1の値が0.1以下の場合は"0"になります。

リンクの件すみません、直接リンクするとダウンロード不可でした。
新しくIEの画面を開いて、
http://www.geocities.com/t_koba/test.zip
をアドレスの所に張り付けてみてください。

回答No.5

nishi6

質問がよくまとまっているので、質問をメモ帳に貼り付けて、ユーザー定義関数を作ってみました。
１画面で確認できる、レートの修正が容易、給与規定の変更への対応が容易等が利点でしょうか。端数処理はしていません。重要な計算と思いますし、こういう対応方法もあるということで参考に投稿します。

質問内容がほとんど回答になっていることを表現したかったのですが、伝わりましたか？質問と回答を対比してもらえば、チョッと違うのはSelect Case だけ？！

D2に『=賞与金額(A2,B2,C2)』のようにして使います。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、
挿入→標準モジュールで標準モジュールを挿入し貼り付けます。

Public Function 賞与金額(級 As Integer, 給与月額 As Long, 扶養手当 As Long)
　　Dim 一律部分, 職務加算部分, 管理職手当部分 As Long
　　Dim 職務加算割合, 管理職手当加算割合 As Integer
　　Const 支給率 = 5.25

　　'一律部分
　　　　一律部分 = 給与月額 + 扶養手当 + ((給与月額 + 扶養手当) * 0.12)

　　'職務加算部分
　　　　Select Case 級
　　　　　　Case 10, 9: 職務加算割合 = 20
　　　　　　Case 8, 7: 職務加算割合 = 15
　　　　　　Case 6: 職務加算割合 = 10
　　　　　　Case 5, 4: 職務加算割合 = 5
　　　　　　Case Else: 職務加算割合 = 0
　　　　End Select
　　　　職務加算部分 = (給与月額 + (給与月額 * 0.12)) * 職務加算割合 / 100

　　'管理職手当部分
　　　　Select Case 級
　　　　　　Case 10, 9: 管理職手当加算割合 = 20
　　　　　　Case 8, 7: 管理職手当加算割合 = 15
　　　　　　Case Else: 管理職手当加算割合 = 0
　　　　End Select
　　　　管理職手当部分 = 給与月額 * 管理職手当加算割合 / 100

　　'賞与金額
　　　　賞与金額 = (一律部分 + 職務加算部分 + 管理職手当部分) * 支給率
End Function

お礼

nisi６さま
お礼を申し上げるのが遅くなり申し訳ありません。
丁寧な計算式を教えていただきありがとうございます。
VBA！わたしにとっては、憧れの「頭上に輝く星」のような存在です。
「たぶんきっとわたしには無理～」と思って諦めていました。
今、一生懸命入力をしながら「こういうイミなのか～」と目から何枚もウロコが落ちております。
また、差し支えなかったらVBAについて質問させて頂いて宜しいでしょうか？
本当にありがとうございました。

拙いnatutidoriの質問に、こんなに沢山の方から回答を頂けて、ほんとうに嬉しいです。ありがとうございました。今後とも、疑問をぶらさげたnatutidoriを見かけたら、よろしくお願いいたします。

回答No.4

comv

こんばんは！

計算自体は単純に各項目順に計算すればよいし、かつ
端数処理をどの時点で行うか分らないので数式は記載し
ない方が誤解を招かずよいと考えます。

お困りなのは、（２）と（３）の級の条件によって率が
変わる部分の解消と考えていますからその箇所だけ記載
します。

(2)職務加算部分

　・計算表に関係無い列又は別Sheetに以下を記載

１列目　　２列目
[職務加算]
　0　　　　0%
　4　　　　5%
　5　　　　5%
　6　　 　10%
　7　　 　15%
　8　　 　15%
　9　　 　20%
10　　 　20%

　・データ部分８行２列を選択
　・メニューの挿入　名前　定義　（名前を付けます）
　　今回は仮に　職務加算　とします。

（給与月額＋（給与月額×12％))×加算部分　の式
　　　↓
　=給与記載セル*1.12*LOOKUP(級記載セル,職務加算)

御指定の想定セルにあてはめると（２行目の場合）
　=B2*1.12*LOOKUP(A2,職務加算)

(3)管理職手当部分
　(2)と同様に　表を作り　名前を定義　仮に　管理職加算
１列目　　２列目
[管理職加算]
　0　　　　0%
　7　　 　15%
　8　　 　15%
　9　　 　20%
10　　 　20%

　給与月額×加算部分
　　　↓
　=給与記載セル*LOOKUP(級記載セル,管理職加算)

　例記
=B2*LOOKUP(A2,管理職加算)

１行記入後　(2)(3)とも必要行まで複写すればＯＫです。

試して見て下さい！

お礼

comvさま
連絡を差し上げるのが遅くなって申し訳ありません。
端数処理の問題などちきんと記入するべきだったのですね・・・。
似たような質問がないか、「教えてGoo」をあちこち探したところ、「質問のイミがわからん」と書かれていることが多かったので、「なるべく分かりやすく詳しく書かなければ！」という気持ちが空回りしておりました。
質問の仕方は本当に難しい・・・。
しかし！
拙いnatutidoriの質問の内容を汲み取っていただき、かつ適切なアドバイスをありがとうございました。ちゃんと計算できました。どうもありがとうございました。
今後とも、「教えてGoo」で疑問の海で溺れているnatutidoriを見かけたら、よろしくお願いします。

回答No.3

trytrytry

ども

試しに式を書いてみたらこんなのが出来ました。
=(B1+C1+((B1+C1)*0.12*CHOOSE(A1,,,,0.05,0.05,0.1,0.15,0.15,0.2,0.2))+(B1*CHOOSE(A1,,,,,,,0.15,0.15,0.2,0.2)))*5.25

CHOOSE関数はインデックスを使ってN番目の値を返します。
CHOOSE(インデックス,値1,値2,値3,,,)
A1に"8"が入力されていて、=CHOOSE(A1,あ,い,う,え,お,か,き,く,け,こ)
と言った式の場合は8番目の引数の"く"が返ります。

現実問題として、このような式にはせずに、
参照用の列なりシートなりを作った方が良いです。
上記の式は、どうしてもそういった領域を作る事が出来ないとき
参考にしてください。
ひょっとしたらもっと効率の良い式があるかもしれませんが．．．

あとは、マクロで作るかですね。

お礼

trytrytryさま
お返事ありがとうございました。
すぐに連絡を差し上げず申し訳ありませんでした。
CHOOSE関数というのは参考書で見たことがありましたが、使ったことがありませんでした。便利そうですね。
実際に入力してみたのですが、何がいけないのか計算が合わないのです。
CHOOSE(A1,,,,0.05,0.05,0.1,0.15,0.15,0.2,0.2)のＡ１からの「てんてんてん」もちゃんと数えて入力してみたのですが・・・。
ところで、この最初の「てんてん」は、「加算がない級は省略」という意味なのでしょうか？
もし、よろしかったらもう少しこの関数について教えてください。

回答No.2

koba004

参照についてうまく説明出来ないので作って見ました。
参照にLOOKUPを使っていますが、他にもいろいろあります。

参考URL：

http://www.geocities.com/t_koba/test.zip

お礼

koba004さま
2通もお返事いただいて、ありがとうございました。
このアドレス先なのですが、英語のヤフーだったのです。
英語は・・・ごめんなさい判読できません。
もし、違う場合は再度教えていただいて宜しいでしょうか？
よろしくお願いします。

回答No.1

koba004

素直に計算式を入れたら．．．
=(B1+C1+((B1+C1)*0.12)+(B1+(B1*0.12))*A1+IF(A1>0.1,B1*A1,0))*5.25

でしょうか？

Ａ列には率をいれていますが、
Ａ列を級で記述したい場合はもう一列作って、
率を参照させるのが良いと思います。
支離滅裂ですみません。うまく説明出来ない．．．

お礼

koba004さま
お返事ありがとうございました。
すぐにご連絡したかったのですが・・・遅くなって申し訳ありません。
トライしてみたのですが、すこし金額が合わないようです。
IF(A1>0.1,B1*A1,0)というのは、どのような計算をしているのでしょうか？
ＩＦ関数は、参考書を読んでも良く分からなくて・・・。
でも、ＩＦ関数を上手に組み合わせることが出来れば、
いろいろな条件で計算させることができるんですよね。
もし、よろしければもう少し、ＩＦ関数についてお教え頂けたら嬉しいです。
どうぞよろしくお願いします。