エクセル関数についてです

勤怠管理のためエクセルで管理表を作成しているのですが「関数」が複合になるとまったくわかりません。お手数ですが教えていただけませんでしょうか。

設定は下記のとおりです
（　）内はそれぞれの条件です

a1 出勤時刻（２４時間で入力）
b1 退勤時刻（２４時間で入力）
c1 休憩時刻（９０分もしくは７５分を手入力）
　　　　　　※複雑すぎなければa1=8:15の場合90分、a1=20:15の場合は75分を表示
d1 勤務時間（b1-a1-c1 ２４時間で表示）
e1 基本給（d1*1000円）
f1 残業時間（c1-8時間)
g1 残業手当(f1*(1000円*25%))
h1 深夜時間(a1b1の時間から22時～５時を含む場合に7時間を表示）
j1 深夜手当(h1*(1000円*25%))
k1 日計(d1+g1+j1)

基本的情報

基本Ａ
勤務　８時１５分から２０時３０分
休憩　１５分×３回、４５分×１回
残業　２０時３０分以降を残業とする

基本Ｂ
勤務　２０時１５分～８時１０分
休憩　１５分×３回、３０分×１回
残業　８時３０分以降を残業とする


ぜひともお願いいたします

質問者からの補足コメント

• ご指摘いただきました残業時間の件ですがおっしゃる通りです。
  正しくは、
  基本Ａは１７：１５以降
  基本Ｂは６：１５以降　になります
  
  ご指摘ありがとうございます

    補足日時：2016/04/08 19:12

• もう一つ忘れていました
  Ｉ「アイ」は１「イチ」と見間違え安いのであえて外しました。
  事前に理由を記すべきでした

    補足日時：2016/04/08 19:14

No.5 回答者： jmuiny 回答日時： 2016/04/09 09:22

No.2です。

補足を読みました。B勤務の場合残業開始は5:15ではないか、深夜の割増などまだ疑問がありますが、そのままでのサンプルです。
なお、連続24時間以上の勤務はないことを前提にしています。
金額の端数は仮に切り上げにしています。ここはご自分で変更して下さい。

作業列を2列使います。式を長くしない為です。
1.日を跨ぐ勤務があるので、計算上の退勤時間（M列）
2.残業開始時間（N列）
どちらも書式はユーザー定義で　[h]:mm　にします。
M2
=IF(B2="","",IF(A2<B2,B2,B2+"24:00"))
N2
=IF(A2="","",IF(A2<"20:15"*1,"17:15"*1,"30:15"*1))

C2
=IF(A2="","",IF(A2<"20:15"*1,90,75))
D2
=IF(A2="","",M2-A2-C2/24/60)
E2
=IF(A2="","",ROUNDUP(1000*D2*24,0))
F2
=IF(A2="","",MAX(0,M2-N2))
G2
=IF(A2="","",ROUNDUP(1000*0.25*F2*24,0))
H2
=IF(A2="","",MAX(0,MIN(N2,"29:00")-MAX(A2,"22:00")))
J2
=IF(A2="","",ROUNDUP(1000*0.25*H2*24,0))
K2
=IF(A2="","",E2+G2+J2)

添付図は見やすくする為にあえて2段に分けています。

No.4 回答者： zircon3 回答日時： 2016/04/08 20:33

No.1です。

自分が使う物だから設計はいらない、処理手順をまとめる必要は無いとういうのはちょっと違うと思います。
人は時間が経つと忘れますから、後々手直しようとなった際に式を見て「なんでこうなっているの？」ってことは結構あることです。それと日本語で書き出すことで考え違いや仕様漏れなどを防ぐことができます。
入力欄の配置なども同様です。使い始めると作る人ではなく使う人にあるので必ず「ああしたい」、「こうしたい」が出て来ます。それを事前に見ることで「もうちょっとこうしておこう」、「この方が使いやすそうだ」を作る前に検出できます。
要領よく書き出せばさして時間の掛かることではないのでおやりになることを個人的にはおすすめします。

また、VBAはもってのほかとのことですが、深夜時間（ご質問のh1）は「～場合に」とありますからif文を使うのではないでしょうか？　ここも目視で確認しての入力であればVBAは不要ではあります。

あと、Excelを既にお持ちでしたら以下のようなことをされてみてはいかがでしょう。
既におやりでしたらすみません。

(1) Excelを起動する。
(2) 表示された空の表の1行目のA列からD列までの4つのセルを選択状態にし、右ボタンクリックで表示されたメニューで「セルの書式設定」（でしたっけ？）を選択し、表示されたウィンドウで「時間」とする。（細かな指定項目名を忘れました）
(3) セルA1（1行目A列のセル）に8:00と入力、同B1に17:00と入力、同C1に1:30と入力。
(4) セルD1に「=B1-A1-C1」と入力。（セルの指定は該当セルをクリックすると簡単）

お分かりと思いますが、A1が出勤時刻、B1が退勤時刻、C1が休息時間（分入力ではなく時間入力）で、D1が勤務時間です。

この回答へのお礼

引き続きのご指摘ありがとうございます。
No.1様の仰る通り出直します

お礼日時：2016/04/08 20:45

No.3 回答者： 銀鱗 回答日時： 2016/04/08 17:39

自分で作らず他人に全部お任せということだろうか。

ならば、代行して作成してくれる代理店を探して依頼したほうがよいと思う。

・・・
具体的に分からない部分についての質問やアドバイスを受けるくらいなら可能だろうが、
全部をここで答えるのはほぼ不可能。


もう1つだけアドバイス。
日付を跨いでの勤務の場合の勤務時間計算について考えておく必要があると思う。

この回答へのお礼

仰る通りです。分かったうえでお願いさせて頂いておりますし、ご賛同いただける方に回答いただければ幸いと思い質問させて頂いております。

お礼日時：2016/04/08 19:09

No.2 回答者： jmuiny 回答日時： 2016/04/08 15:30

不明点がいくつかありますので補足した方がいいと思います。

1.Aの場合で書きますが、
「f1 残業時間（c1-8時間)」と「残業　２０時３０分以降を残業とする」は矛盾しています。
8:15から勤務の場合、8時間と休憩90分を足すと、17:45です。何故20:30以降が残業なのでしょうか。17:45～20:30は残業でない理由を教えて下さい。

2.深夜割増の率が残業と同じ25%になっていますが、間違っていませんか。（労働法に反するのでは）

3.I列は理由があって使っていないのですか。

No.1 回答者： zircon3 回答日時： 2016/04/08 09:41

まず具体的にどんな見た目のExcel表にしたいのか、入力欄や各種表示欄を考えましょう。

次に、入力欄に人が入力し、どういう操作をしたら1日の勤務時間や残業時間の計算機能が動作したり、一ヶ月のまとめ機能が動作したりするのかを決めましょう。

三つ目に、それが出来たらのそれぞれの処理手順を日本語で箇条書きにしてみましょう。その際、「もし○○だったら、そうでなかったら」といった場合分けがある場合はそれがきちんとわかる書き方でまとめましょう。

それが出来れば日本語からVBAへ翻訳出来るはずです。

この回答へのお礼

早速の回答をありがとうごいます。
まず、わかるか方から見るとこれくらいの関数を？と思いになるのでは。
ですのでＶＢＡやマクロなんていうのはもってのほかです。初めから考えもしてませんでした。
そしてどんな表ということでしたが、これは公的なものでなく詩的なものなので入力はすべて私で行います。
ですので時間の四捨五入などは行いません

お礼日時：2016/04/08 19:18