エクセルでシフト管理と勤務表を作成したい

質問

いろいろフリーソフトを探しましたがぴったりくるものがありませんでした。
作り方を教えて頂ければ幸いです。よろしくお願いいたします

　Ａ列：シフトパターン（Ａ13：00～18：00、Ｂ10：00～18：00、Ｃ9：00～18：00）
　Ｂ列：実際の出勤時間（手入力）
　Ｃ列：実際の退社時間（手入力）
　Ｄ列：実際の休憩時間（手入力）
　Ｅ列：実働時間（Ｂ・Ｃ列から時間を読み取って自動計算）
　Ｆ列：法廷内残業時間（Ａ列からシフト外で勤務した時間の自動計算）
　Ｇ列：法定外残業（８時間を超えて勤務した時間の自動計算）

回答No.2

KURUMITO · ベストアンサー

例えばA1セルにパターン、B1セルに出勤時間、C1セルに退社時間、D1セルに休憩時間、E1セルに実働時間、F1セルに法廷内残業、G1セルに法定外残業、とそれぞれ項目名が有るとします。
A列にはA,B,Cなどのパターンを入力します。
それらのパターンにかかわる時間の表をJ列からL列に入力して作成するとします。
J1セルにはA,J2セルにはB,J3セルにはCと入力します。
K1セルには13:00、K2セルには10:00、K3セルには9:00、L1セルには18:00、L2セルには18:00、L3セルには18;00と入力します。

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

=IF(COUNT(B2:C2)<>2,"",C2-B2)

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

=IF(E2="","",IF(B2<VLOOKUP(A2,J:L,2,FALSE),VLOOKUP(A2,J:L,2,FALSE)-B2,0)+IF(C2>VLOOKUP(A2,J:L,3,FALSE),C2-VLOOKUP(A2,J:L,3,FALSE),0))

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

=IF(E2="","",IF(E2-D2>"8:00"*1,E2-D2-"8:00",0))

なお、E,F,G列を選択したのちに「セルの書式設定」から「セルの表示形式」で「時刻」からお好みの表示を選択します。

回答No.3

KURUMITO

回答No2ですが休憩時間を含めた計算をするのかなど実態と合わないところがあるかもしれませんが工夫してください。

補足

返事が遅くなりました。申し訳ございません。

私が求めているものができそうだと思い早速、回答Ｎｏ1で教えて頂いた通り新たに作ってみましたがＦ2セルが「＃Ｎ/Ａ」となってしまいます。

数式は説明文からコピペしているのですが・・・。

回答No.1

K Kazz（@JazzCorp）

下のリンクに本格的実用版！があるので、試してみては？
先ず、「労働時間の計算.xls」をダウンロードする。網掛けの色のセンスが極悪！、なのはこの際我慢！！
＜＜前提＞＞
・休息時間は抽象的な時間ではなく、時間帯を設定する。定時時間以外の時間帯は大変長いので、合理的な休息時間帯を設定する必要がある。
＜＜使い方＞＞
・背景色のない（白抜きのセル）が入力、設定または出力（計算結果）、背景色のある部分は中間結果なので見なくて良いが、実用にするときは、列を非表示にする必要がある。
・休息時間の時間帯をシートの上部の表に入力する。（最大6個設定できる）
休息時間帯の設定は共通のものなので、別のシートに分離すべき。変更した場合は、当然個人別の集計表の計算式も変更する必要がある。
・個人別の日毎の勤務時間（開始と終了時刻：タイムレコーダ）は下の表に入力する。
深夜扱いの時間帯は法定？どおりに計算してるようだ。

・シートには保護が掛かっているようなので、果たして狙い通りに変更できるのか？

「労働時間の計算(Calculation of working hour)」
https://sites.google.com/site/roudoujikannnokeisann/
エクセルで労働時間を計算する方法については、以下をクリックしてご覧下さい。
勤務1～勤務6、休憩1～休憩6の開始時刻と終了時刻(赤文字部)を入力した上で、出勤時刻と退勤時刻(赤文字部)を入力すると、通常勤務時間、通常残業時間、深夜残業時間が、(分間)と(時間)の単位で、計算されます。
[労働時間の計算.xls]

お礼

詳しい説明をいただきありがとうございました。

教えて頂いたサイトは初心者の私にはかなりハードルが高いものでした。
返事が遅くなりました。申し訳ございませんでした。