EXCEL（2013）で給与計算したい

質問

EXCEL（2013）で出勤時間・退社時間を入力するだけで、給与計算できるフォーマットを作りたいです。　いろいろHP見ながらチャレンジしていますが全くできず困っています。
どなたか助けてください。

　★勤務開始（出勤時間）から8時間超えが残業
　★深夜勤務　22時～翌朝5時　（うち2時～5時まで休憩　カウントしない）
　★出勤して退社し、また再出勤して通常勤務ー深夜勤務ー早朝勤務まですることがある。
　★通常時間帯と深夜勤務の時給が違う。（例：通常　920円　深夜勤務　1120円）

　・A列　出勤時間　（手入力）
　・B列　退社時間　（手入力）
　・C列　再出勤時間　（手入力）
　・D列　再退社時間　（手入力）
　・C列　通常勤務時間（勤務開始（出勤時間）から8時間以内）
　・D列　残業時間（8時間を超え22時まで）
　・E列　深夜勤務（22時～翌朝5時まで　うち休憩が2時～5時）
　・F列　深夜残業（22時以降の勤務で通常勤務で8時間を超えた時間）？　
　　　　　　〃　　　残りの時間？
　・　列　早朝勤務（翌朝5時以降の勤務）
　・　列　早朝残業（翌朝5時以降の勤務で既に8時間を超えてる時間）？
　　　　　　〃　　（残りの時間）？
　　　列　給与金額

勤務時間が8時間を超えた時点で、22時まで×1.25　深夜勤務×1.5　早朝勤務×1.25　の係数になるためどの勤務パターンで8時間になり、8時間以降は何時間にるのか知りたい。

関数など詳しくありませんので、出来ればコピペできるようにお願いしたいです。
うまく説明できませんが、どなたかご教示いただければ助かります。

回答No.8

iftaha_simsim

24:00を超えて勤務する場合の退出時刻は、24:00＋退出時刻とする。
早朝勤務が何時まで対象となるか不明なので、09:00までと仮定する。

【通常勤務時間】（値がマイナスの時はゼロ時間とする）
出社時刻 退社時刻
05:00～ ～22:00 通常勤務=退社時刻-09:00
09:00～ ～22:00 通常勤務=退社時刻-出勤時刻
09:00～ 22:00～ 通常勤務=22:00-出勤時刻

【深夜勤務時間】（値がマイナスの時はゼロ時間とする）
退社時刻
～26:00 深夜勤務=退社時刻-22:00
26:00～ 深夜勤務時間=4:00

【早朝勤務時間】（値がマイナスの時はゼロ時間とする）
出社時刻 退社時刻
05:00～ ～9:00 早朝勤務=退社時刻-出勤時刻
05:00～ 9:00～ 早朝勤務=9:00-出勤時刻
09:00～ ～33:00 早朝勤務時間=退出時刻-04:00
09:00～ 33:00～ 早朝勤務時間=4:00

【翌日通常勤務時間】
退社時刻
33:00～ 通常勤務=退社時刻-33:00

(注) 33:00 = 翌日09:00

以上から、通常勤務時間、 深夜勤務時間、早朝勤務時間、翌日通常勤務時間を算出します。
総勤務時間＝上記の総和・・となります。

残業時間＝総勤務時間 - 8:00 となります。
残業時間を翌日通常勤務時間、早朝勤務時間、深夜勤務時間、通常勤務時間に各々ゼロになるまで割り振り各残業時間を求めます。

求めた各勤務時間と各残業時間に所定の時給と倍率を掛けて給与を計算します。

以上のマトリクスを一旦IF文で作業シートに記載し、結果をあなたが求める所定のセルに戻してやれば完成です。

私であれば、上記のややこしいIF文をワークシートに埋め込むよりもマクロ(EXCEL VBA)で作成します。
マクロも全て計算し結果だけ返す方法と、上記のIF文をカスタム関数にしてワークシートでその関数を使うと言う方法が考えられます。

IF文でゴチャゴチャ記述するよりVBAのSelect Case文を使った方が読み易く簡潔になり私の趣味に合いますので。

回答No.7

FEX2053

>手討算はできますのでエラーを表示させる方法も勉強してみます。

では、一言だけアドバイス。

こういう「ユーザー側で確認処理をする」ためのプログラムは、全ての
条件を解決しようとしないことが肝要です。

「全体の８割が自動計算できればよし」と考えるんですね。それでも
相当な業務改善になります。だって「同じ作業の反復」が全部無くなる
んですよ。

プログラムは「特殊事態に対応しようとすると、途端に難しくなる」ん
です。「一番多い"普通の事態"たけに対応させる」なら比較的簡単で
間違いも少ないんですけどね。コンピュータはそういう「普通の事態=
実務的には同じ作業の反復」には強いですから。「異例事態=特殊な
計算が必要」な場合に、手順ミスが紛れ込むんです。

なので、「通常事態以外」は手計算させるんです。「異例事態」は人間が
見れば一発でわかるんで、それは計算させなければいいんです。人間は
「異例事態」には強いですからね。ハイブリッドってやつです。

なので、まずはそこから・・・をキモに銘じて業務の効率化をしてみてください。

お礼

ありがとうございます。
がんばります～！！

回答No.6

hue2011

給与計算と言えばお金の計算です。
コンピュータ処理では最も繊細に注意深くやらないと大変な分野です。

それを誰かに作ってもらい、コピペしたい？

誰が責任を持つのですか。

関数など詳しくありません、と威張るのですか。

詳しくなかったらきっちり勉強する義務があるのではないのですか。
あなたはこの仕事で給料をもらっているのですよね。

まあ普通のプログラムができる人間なら簡単にできますし、Excelが詳しい程度でも実現は困難ではありません。
だけど、それはあなたとおなじ空間で同じ責任をとれる人間なら、です。

仮に1円でも誤差がでたら業務的にどうするのですか。そういうルールは誰もしらないのですよ。

会計事務所に頼む、あるいはソフトハウスに相談すべきです。

お礼

これはあくまでも自分の確認レべルです。
関数が詳しくないと威張ってるつもりもありません。
関数も色々あるので解答（逆パターン）から勉強しようと思ってました。

ご指摘ありがとうございました。

回答No.5

mshr1962

列が重複してるのでそのままでは、計算するセルが特定できません。

・A列 出勤時間　（手入力）

・B列 退社時間　（手入力）

・C列 再出勤時間　（手入力）

・D列 再退社時間　（手入力）

・E列 勤務時間
計
・F列 通常勤務時間 （勤務開始（出勤時間）から8時間以内）

・G列 残業時間 （8時間を超え22時まで）

・H列 深夜勤務計 （22時～翌朝5時まで勤務時間）

・I列 深夜勤務 （22時～翌朝5時の通常勤務時間）　

・J列 深夜残業 （22時～翌朝5時の残業時間　最大5時間）

・K列 深夜休憩 （22時～翌朝5時の休憩時間　5時間超分）
・L列 早朝勤務計 （翌朝5時以降の勤務時間）

・M列 早朝勤務 （翌朝5時以降の勤務で通常勤務時間）

・N列 早朝残業 （翌朝5時以降の勤務で残業時間）

・O列 給与金額

として、翌日にまたがる時間は、AM1:00なら25:00(書式 [h]:mm)のように入力することが条件で

E2=IF(COUNT(A2:B2),B2-A2,0)+IF(COUNT(D2:C2),D2-C2,0)

F2=MIN(E2,8/24)

G2=MAX(MIN(B2,22/24)-MIN(A2,22/24)+MIN(D2,22/24)-MIN(C2,22/24)-F2,0)
H2=MIN(MAX(B2,22/24),29/24)-MIN(MAX(A2,22/24),29/24)+MIN(MAX(D2,22/24),29/24)-MIN(MAX(C2,22/24),29/24)

I2=MIN(MAX(8/24-(E2-G2-L2),0),H2)
J2=MIN(MAX(H2-I2,0),5/24)
K2=H2-I2-J2
L2=MAX(B2,29/24)-MAX(A2,29/24)+MAX(D2,29/24)-MAX(C2,29/24)

M2=MIN(MAX(8/24-(E2-G2-H2),0),L2)
N2=MAX(L2-M2,0)
O2=(F2+(G2+N2)*1.25+J2*1.5)*24*920

※?/24の数値は、時間をシリアル値に換算したもの

回答No.4

bunjii

あなたの賃金が正しく計算されてるか否かの確認用でしょうか？
>　・C列　再出勤時間　（手入力）
>　・D列　再退社時間　（手入力）
>　・C列　通常勤務時間（勤務開始（出勤時間）から8時間以内）
>　・D列　残業時間（8時間を超え22時まで）
C列とD列の扱いが重複しています。
何方が正しいのでしょうか？
尚、再出勤時間、再退社時間とは何ですか？

時間外手当（時給の0.25倍）と深夜手当（時給の0.25倍）を個別に計算すれば次のように考えられます。
日給=1日の勤務時間*時給+(時間外勤務の時間+深夜勤務の時間)*時給*0.25
時間外=(勤務時間>8:00)-8:00と0の最大値
深夜勤務の時間は少々面倒な論理で数式を組み立てる必要があります。

>出来ればコピペできるようにお願いしたいです。
模擬データの提示があれば具体的な数式を提示できます。
貼付画像はExcel 2013で検証した結果です。
日給は1円未満を切り上げにしてあります。

回答No.3

kimamaoyaji

退勤の計算ですか
http://www.eurus.dti.ne.jp/~Yoneyama/Excel/nyumon/kinmu/index.html
http://matome.naver.jp/odai/2141084345609618201
http://sichisan21.blog58.fc2.com/blog-entry-12.html
残業などのマクロ
https://books.google.co.jp/books?id=ixKmvhparpEC&pg=PA173&lpg=PA173&dq=EXCEL+%E9%80%80%E5%8B%A4&source=bl&ots=MNVzYUqv5B&sig=YY5_klsOIsAQp7lzZHoiR-wKcXY&hl=ja&sa=X&ei=ZyE3VYWiOoXc8AWBmYDIDw&redir_esc=y#v=onepage&q=EXCEL%20%E9%80%80%E5%8B%A4&f=false
日付が変わる場合
http://www.wanichan.com/pc/excel/2010/5/page12.html
この辺りを参考にしても良いかもしれません
http://search.vector.co.jp/vsearch/vsearch.php?kanji=utf-8&key=%E9%80%80%E5%8B%A4

お礼

あリがとうございます。
ひとつひとつ見ながら勉強します。

回答No.2

FEX2053

プロに任せた方がいい・・・という答えに私も賛成。

この手の処理は、何か異常値を入れた時にどう「エラー」として
はじくかが結構難しく、異常値を飲み込んで結果が出てしまうと
それが「正しいもの」としてとおってしまうことが一番の問題です。

お金が絡むと、１円でも間違うと大騒ぎになりますので。

とりあえず「再出勤」「深夜勤務」「早朝勤務」があったら「エラー」
として手計算にする前提で作ってみてはどうですか？それなら

「退社時間」－「出社時間」－８時間＝「残業時間（仮）」
MAX（残業時間（仮）,０）＝「残業時間」
「退社時間」－「出社時間」－「残業時間」＝「通常勤務時間」

で計算できますよね。

お礼

ありがとうございます。
手討算はできますのでエラーを表示させる方法も勉強してみます。

回答No.1

kimamaoyaji

無理でしょう、給与計算を手計算で出来るスキルが有るなら可能かもしれませんが、それにしてもコピペでは無理です。
貴方の会社と全く同じテンプレートなど有りませんので。
それに給与ですから全くミスが許されない部分ですから、市販ソフト会社にカスタマイズで依頼するべきだと思います。
また会社の依頼している会計士さんに多々問題を指摘される羽目になると思いますが？

補足

タイトルが給与計算にしていたので誤解を生じたかもしれません。
すみません!!!

ただ、事務上で確認できれば・・・・と思っていました。
このエクセルで計算した結果が支給金額になるって設定ではありません。

8時間こえがどの勤務時間帯なのか、その範囲で残りの時間が何時間がいちばん知りたいところでした・・・・・