作業の効率化と計算ミスを防ぐため、エクセルで勤務表を作成したいと思っております。
イメージとして
A B C D E F G H
1列 日 出勤 退勤 残業 深夜 総労働 遅・早 休息
2列1 4:20 17:42 4:22 0:40 12:22
3列2 7:51 24:00 2:00 7:57 0:03 7:12
4列3 0:00 10:50 1:00 5:00 9:00 0:50
5列4 16:30 6:30 5:00 7:00 13:00
6列 5 8:30 17:30 8:00
A(日)・B(出勤)・C(退勤)・H列(休息)は、手入力しますが、
D(残業)・E(深夜)・F(総労働)・G(遅刻・早退)は、自動計算させたいと考えております。
F(総労働)は 退勤-出勤-休憩=X (Xが8時間以上なら-1時間 8時間に満たない時はそのまま表示)
D(残業)は、X-8時間 (Xが8時間に満たない時は、空白のまま)
E(深夜)は、22:00から5:00とする。
G(遅刻・早退)は、Xが8時間に満たない時のみ (8時間-X=Y)を表示させる。
★出勤時間・退勤時間は、人や仕事の内容によりバラバラです。
H(休息)は、2日運行等になった時のみです。
休日など空白になっている時は、空白のまま。
説明が下手で申し訳ありませんが、お知恵を貸してください!!
宜しくお願いします。
No.4ベストアンサー
- 回答日時:
D2セルには次の式を入力して下方にドラッグコピーします。
=IF(COUNT(B2:C2)<>2,"",IF(F2<="8:00"*1,"",F2-"8:00"))
E2セルには次の式を入力して下方にドラッグコピーします。
=IF(COUNT(B2:C2)<>2,"",IF(C2>B2,IF(AND(B2>="5:00"*1,C2<="22:00"*1),"",IF(B2<"5:00"*1,"5:00"-B2+IF(C2>"22:00"*1,C2-"22:00",0),IF(C2>"22:00"*1,C2-"22:00",0))),IF(C2<=B2,"24:00"-MAX(B2,"22:00")+MIN(C2,"5:00"),"")))
F2セルには次の式を入力して下方にドラッグコピーします。
=IF(COUNT(B2:C2)<>2,"",IF(C2>B2,IF(C2-B2>="8:00"*1,C2-B2-"1:00"-H2,C2-B2-H2),IF(C2+"24:00"-B2>="8:00"*1,C2+"24:00"-B2-"1:00"-H2,C2+"24:00"-B2-H2)))
G2セルには次の式を入力して下方にドラッグコピーします。
=IF(COUNT(B2:C2)<>2,"",IF(F2>="8:00"*1,"","8:00"-F2))
回答ありがとうございました。
とても勉強になりました。
作業が、はかどりそうです。
またわからないことがありましたら
宜しくお願いします。
No.3
- 回答日時:
No.2です、何度もすみません。
言い忘れました。時間として「0」が算出されているセルでも、「ユーザー定義書式」を使えば、見かけ上は空白であるように見せることは可能です。
設定したいセルを選択した状態で「セルの書式設定>表示形式>ユーザー定義」と進み、「種類」ボックスに「h:mm;;」と入力してOKボタンを押すだけです。「0」だけど、見かけは空白同様になります。
No.2
- 回答日時:
質問文には「空白」と何回か書かれていますが、「0」か「空文字列」のいずれかを入力することとなります。
完全なる空白にすることは、一発ではできません。なお、「空文字列」の入力されているセルは、見かけ上は空白セルと同様になります。時間の計算は、単に引き算をするだけです。条件により式が変わる場合は、IFなどを組み合わせます。質問文に書いてある条件を忠実に式に直すと次のとおりです。とりあえず「空白」とお書きになっている箇所には「0」が表示されるようにしてあります。「空文字列」にするには、もう少し式が長くなります。
F2 =if(c2+if(b2>c2,1)-b2-h2>=8,c2+if(b2>c2,1)-b2-h2-1,c2+if(b2>c2,1)-b2-h2)
D2 =f2-8/24
E2 =if(b2>c2,min(c2,5/24)+1-max(b2,22/24),)
G2 =-min(0,f2-8/24)
???
気になるのは、質問文に書いてあるとおりだと、労働時間が8時間だとX=7となり、7.5時間だとX=7.5となって、逆転しますが、この理解で合っているのでしょうか?
それから、日付が変わるケースがあるようですので、以前私が回答した類似の質問(参考URL)を参照し、全部読んでみてください。マイナスの時間は「####」と表示されますが、Excelのエラーではなく、マイナスの時間を表示する際のルールです。日付を表中に導入すればマイナス回避の必要がなくなるので、式がもっと簡単になるし、入力ミスなどもしにくい分かりやすいものとなりそうですが。
あと、日勤/夜勤の別を表示する列もあったほうが、紛れがなくて好ましいと思いました。上の計算にも利用できます。
参考URL:http://oshiete.goo.ne.jp/qa/7354714.html
No.1
- 回答日時:
いろいろなケースを入力してみて、正しいことを確認してから使用下さい。
質問者さんが書かれた例の3日目、Xは8時間以上ですが『遅・早』が0:50になっているのが理解できませんでした。作成したものでは0:00(空白)になります。
イ、 最上部に3行挿入して、下記のように計算のための値を入力します。(A1~F2セル)
ロ、 I,J列に計算のため22:00を0:00に換算した値を表示するセルを作ります。
A B C D E F G H I J
1 休憩時間 所定時間 深夜開始 深夜終了 深夜時間 1日の時間
2 1:00 8:00 2:00 5:00 7:00 24:00:00
3 22:00⇒0:00
4 日 出勤 退勤 残業 深夜 総労働 遅・早 休息 (出勤) (退勤)
5 D5 E5 F5 G5 I5 J5
6
7
ハ、 自動計算させたいD(残業)・E(深夜)・F(総労働)・G(遅刻・早退)と追加したI,J列に以下のように入力します。この例では5行目(D5,E5,・・・)とします。
D5:=IF(F5<=$B$2,"",F5-$B$2)
E5:=IF(AND($E$2<J5,J5<K5),"",IF(J5>$E$2,MIN(K5,$E$2),MIN(K5,$E$2)-J5))
F5:=IF(C5>B5,IF(C5-B5-H5>=$B$2,C5-B5-H5-$A$2,C5-B5-H5),IF(C5-B5-H5>=$B$2,C5-B5-H5-$A$2,C5-B5-H5)+$F$2)
G5:=IF(F5<$B$2,$B$2-F5,"")
I5:=IF(B5>=$C$2,B5-$C$2,B5+$F$2-$C$2)
J5:=IF(C5>=$C$2,C5-$C$2,C5+$F$2-$C$2)
ニ、 5行目を必要な日数分だけコピーすれば完成です。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 労働相談 有休消化について教えて下さい。 6月末にて退職の予定です。 現在、有休(1日8時間)残りが29日と半 2 2022/04/03 01:05
- 求人情報・採用情報 皆さんでしたらどちらの会社に行かれるか教えてください。 年齢は45歳 男性です。既婚者です 1会社、 4 2022/06/07 08:08
- その他(ビジネス・キャリア) 勤務時間の割増について教えてください。 現在日勤と夜勤で勤務しています。 日勤は9:00から20:0 5 2022/05/08 22:40
- 正社員 皆さんでしたらどちらの会社に行かれるか教えてください。 年齢は45歳 男性です。既婚者です 1会社、 6 2022/06/04 13:51
- 就職 ホワイト企業ですか? 3 2023/03/10 15:16
- 人事・法務・広報 みなし残業と深夜の割増賃金 1 2023/04/17 00:06
- 求人情報・採用情報 介護の求人についてお伺いしたいです。 現在フリーターでアルバイトを探しており、グループホームの求人を 1 2023/03/20 23:15
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- Excel(エクセル) エクセルで休憩時間を引く時と、引かない時の数式 3 2022/11/05 11:48
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
MicrosoftOfficeの1ユーザー2...
-
MicrosoftOffice2019なんですが、
-
Excel 日付を比較したら、同じ...
-
Microsoft365の「お支払いを更...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelで空白以外の値がある列の...
-
エクセルのシフト表を簡単にGoo...
-
理由を教えてください。
-
VBA
-
web上にあるエクセルをショート...
-
バソコンが二台とも壊れ後換装...
-
【マクロ】文字を1文字づつ、...
-
Excelのセルの重複チェックが出...
-
マイクロソフト 一時使用コード...
-
office365って抵抗感ないですか?
-
Outlook 電源OFFの受診の仕方
-
エクセルで例えば、A1に㈱ベ...
-
自分の専門分野の仕事。初見で...
-
excelの画面のグリッド線の消滅。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報