エクセル2007を使っています。

エクセルで勤怠表を作成するのですが、問題点があるので
お教え頂ければ幸いです。

1.交代勤務をしています。 早番・遅番・日勤・深夜勤があります。
  この区別をしたいです。

2.遅刻、早退が多いので評価に反映させたいです。
  極端ですが、1分の遅刻や早退でも、15分単位として勤務時間を減らしたいのです。

3.時間の表現は、通常「8」時間、15分単位としての遅刻、早退などがある場合は、
「7.75」時間、「7.5」時間というように勤務時間を表示したいのです。

A1列に出勤時間、B1列に退社時間というような標準的な入力としています。

よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (8件)

次の例を参考にしてはどうでしょう。


例えばシート1ではA1セルに氏名、B1セルに出勤時間、C1セルに退社時間、D1セルに勤務形態、E1セルに遅刻、F1セルに早退、G1セルには定刻の勤務時間が9時間としてそれを過ぎた超勤時間、H1セルに勤務時間の項目名がそれぞれ入力されているとします。A、B,C列の2行目以降にはデータをそれぞれ入力するとします。
そこで初めに勤務形態を決めた時間表を用意することが必要になりますがそれらはデータベースとしてシート2に作成するとします。
シート2ではA2セルに早番、A3セルに日勤、A4セルに遅番、A5セルに夜勤の項目名を入力します。
B1セルには出勤時間、C1セルには退社時間の文字を入力します。
そこで実際のデータを入力するわけですがここでは例えばB2セルから下方には6:00,8:00,10:00,22:00と入力し、C2セルから下方には15:00,17.:00,19:00,7:00と入力します。
これらの条件は勤務形態を決めるうえで非常に重要になりますね。
そこでシート1に戻ってD2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(B2>"5:30"*1,B2<"7:30"*1),"早番",IF(AND(B2>="7:30"*1,B2<"9:30"*1),"日勤",IF(AND(B2>="9:30"*1,B2<"12:00"*1),"遅番",IF(AND(B2>="21:30"*1,B2<="24:00"*1),"夜勤",""))))

この式では勤務形態が出勤時間を判断して決めるような形になっています。それができない場合には手入力であるいはリスト表示から入力する方法もありますね。
次にE2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(B2<=INDEX(Sheet2!B:B,MATCH(D2,Sheet2!A:A,0)),0,CEILING(B2,"0:15")-INDEX(Sheet2!B:B,MATCH(D2,Sheet2!A:A,0))))

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

=IF(D2="","",IF(C2>B2,IF(C2<INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0))-FLOOR(C2,"0:15"),0),IF(C2>INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),0,INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0))-FLOOR(C2,"0:15"))))

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

=IF(D2="","",IF(C2>B2,IF(C2>INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),FLOOR(C2,"0:15")-INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),""),IF(D2<>"夜勤",CEILING(C2,"0:15")+"24:00"-INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),IF(C2>INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),FLOOR(C2,"0:15")-INDEX(Sheet2!C:C,MATCH(D2,Sheet2!A:A,0)),""))))

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

=IF(D2="","",("9:00"-E2-F2+G2)*24)

最後にE,F,G列についてはセルの表示形式は時刻から指定します。H列については標準にします。
    • good
    • 0
この回答へのお礼

ご回答いただきました皆様。
どうもありがとうございました。
今回は自分で解決となりました。

お礼日時:2012/04/23 19:56

No.2・6です。



(1)遅刻と早退をしているところがあり、そこでは15分と15分で30分を・・・
(2)一人、一月にばらばらのシフトで日勤、夜勤、休み、遅番、早番・・・

とありますので、前回の案に少し手を加えた感じで!

一人1Sheetになりますが↓の画像のようにA列に日付(単に数値のみ)を入れておきます。
Sheet2には前回同様表を作成し、Sheet2のA列部分をSheet1のプルダウンリスト表示できるようにしてみてはどうでしょう?

Sheet2のA2~A6セルを範囲指定 → 名前ボックス(画面左上のセル番地が表示されているところ)に
仮に 勤務形態 としてOK
(名前はどんな名前でも構いません。文字頭に数値・アルファベットは使用しない方が良いみたいです)

これでSheet2のA2~A6セルが「勤務形態」と名前定義されました。
Sheet1のB2セル以降を範囲指定 → データ → 入力規則 → リスト を選択 → 元の値の欄に
=勤務形態
としてOK
これでSheet1のB列セルがプルダウンでSheet2のA2~A6セルを選択できるようになります。

後は前回同様です
Sheet1のE2セルに
=IF(COUNTBLANK($B2:$D2),"",VLOOKUP($B2,Sheet2!$A$2:$C$6,COLUMN(B1),0))
という数式を入れ隣のF2セルまでオートフィルでコピー!

G2セルは
=IF(COUNTBLANK(C2:F2),"",SUM(IF(C2>E2,CEILING(C2-E2,"0:15"),0),IF(D2<F2,CEILING(F2-D2,"0:15"))))

H2セルは
=IF(COUNTBLANK(C2:G2),"","8:00"-G2)

として最後にE2~H2セルを範囲指定 → H2セルのフィルハンドルで下へコピー!
これで何とかご希望の近い形にならないでしょうか?m(_ _)m
「エクセルで勤怠表作成について教えてくださ」の回答画像7
    • good
    • 0

No.2です!


一つの案です。

↓の画像のようにSheet2に各勤務形態の定時出勤時刻・退勤時刻の表を作成してみてはどうでしょうか?

D2セルに
=IF($A2="","",VLOOKUP($A2,Sheet2!$A$2:$C$5,COLUMN(B1),0))
という数式を入れ、隣のE2セルまでオートフィルでコピー

F2セルに
=IF(COUNTBLANK(A2:E2),"",CEILING(IF(B2>D2,B2-D2,0)+IF(C2<E2,E2-C2,0),"0:15"))

G2セルに
=IF(F2="","",HOUR("8:00"-F2)+MINUTE("8:00"-F2)/60)
という数式を入れ、D2~G2セルを範囲指定 → G2セルのフィルハンドルで
下へコピーすると、画像のような感じになります。

仮に数分だけ遅刻して、退勤時刻が定時より大幅に遅くても遅刻だけしか考慮していません。
(残業時間等の考慮なし)

※ D・E列が目障りであれば非表示にしても良いかと思います。

参考になりますかね?m(_ _)m
「エクセルで勤怠表作成について教えてくださ」の回答画像6
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

画像付きでたいへん参考になりました。
ですが、遅刻と早退をしているところがあり、そこでは15分と15分で30分を
カウントしなければなりませんが、15分になっていますから、惜しいですね。
これをヒントに作成したいと思いますが、一人分で数日分しかありませんね。
一人、一月にばらばらのシフトで日勤、夜勤、休み、遅番、早番・・・と
続いていくので膨大になりそうです。

お礼日時:2012/04/12 00:50

遅刻や早退、早番、遅番などの複雑な計算については最近のURLを参考にしてください。



http://oshiete.goo.ne.jp/qa/7410058.html
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

お礼日時:2012/04/11 11:37

回答No3です。


一部の表現が間違っていました。
また出勤時間については15分単位で処理をして余った時間は切り上げ処理をしています。
    • good
    • 0

15分単位で時間を処理する方法と7.5といったように勤務時間を表示させる方法について述べます。


例えばA2セルに出勤時間として9:05、B2セルには退社時間として17:40のように入力させているとしたらその勤務時間は

=(FLOOR(B2,"0:15")-CEILING(A2,"0:15"))*24

この式では退社時間については15分単位で処理するのですが余った時間は切り捨てをしています。また出勤時間については15分単位で処理をして余った時間は切り捨て処理をしています。
上記の場合には17:00から9:15を引いた時間となります。
7.5時間の表示にするためには時間の計算はシリアル値で行われ日にち単位の計算になっていますので24を掛けることで行っています。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

これだと深夜勤の場合は、マイナスになってしまいます。

お礼日時:2012/04/11 11:31

こんにちは!


具体的なセル配置が判らないので・・・

(2)に関しては

=CEILING(遅刻時間+早退時間,"0:15")
(遅刻時間・早退時間はシリアル値です。0:31 のような感じ)

としてセルの表示形式は「時刻」でも構いませんが、
ユーザー定義から
[h]:mm 
としておいた方が良いかもしれません。

(3)については
仮にA1セルに「7:45」と表示されているとすると

=HOUR(A1)+MINUTE(A1)/60

として、セルの表示形式は「標準」にしておきます。

こんなんでどうでしょうか?m(_ _)m
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

3については、同じ意見です。
2については、よくわかりません。

お礼日時:2012/04/11 11:34

出勤時間をA1、退勤時間をA2にそれぞれ「日付シリアル」


(9:15 17:10 など、時刻表示のこと)で入力してあったとして。

=HOUR(A2-A1)+ROUNDDOWN(MINUTE(A2-A1)/15,0)/4

これで、セルの表示形式を数値、または標準にすれば、
「X.XX」時間として表示される筈です。

ただし、休憩時間のことは全く考えてませんので、その分を
どうやって控除するか、という問題はあります。

早番、遅番、日勤、深夜勤は、各々別の列に入力しておけば
いいだけなんですが、例えば出勤時間で判断できるなら、

=IF(A1>TIME(17,0,0),"深夜勤",IF(A1>TIME(12,0,0),"遅番","早番"))

などと設定すれば良いだけの話です。
    • good
    • 0
この回答へのお礼

ご回答ありがとうございました。

確かに出勤時間で○番なのか判断しますが、
早めに出てくるのはいいのですが、遅刻してくる人が多いので、
一概に断定できません。さらに、遅刻してその上早退する人もいますから、
困っています。

お礼日時:2012/04/11 11:36

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで勤怠管理表で休憩時間を計算させるには

エクセルで、社員の出勤時間の計算をしたいのですが、
休憩時間の控除の計算式がよくわからずに困っています。

パートさんが多いので、労働時間により休憩時間が異なり、下記のようにしています。
4時間30分未満=0分
4時間30分以上=30分
6時間以上=45分
8時間以上=60分

現在使用しているデータの時間表示は、出社「9:00」退社「17:00」延べ時間「8:00」というような、60進法の時分表示です。
これを、10進法表示にはしない方法で計算したいです。

現在の表では、
Aセル=出社時刻、Bセル=退社時刻、Cセル=述べ就労時間、Dセル=休憩時間、Eセル=勤務時間(←述べ就労時間-休憩時間)
にしているのですが、

Dセルの休憩時間が自動計算設定の仕方がわからず、述べ就労時間を見ながら手入力しています。(かなり手間なのと、間違いのもとなので)
これを自動計算できるようにしたいのですが・・

教えて頂けたら大変助かります。
宜しくお願いします。

Aベストアンサー

IF文で
=IF(C2<"4:30"*1,0,IF(C2<"6:00"*1,"0:30"*1,IF(C2<"8:00"*1,"0:45"*1,"1:00"*1)))

Q残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいの

残業時間の計算をするにはどのような関数あるいは計算式を設定すればいいのですか?
私、エクセルについてはまったくの初心者です。よろしくお願いします。

(例)
       A      B
 
1行目 出勤時間  9:00

2行目 退社時間  19:30

3行目 残業時間  2:00 ←ここの計算式を教えていただきたいのです。

             ※ ただし、定時の終了時間は17:30で、拘束時間は休憩時間を含めて
               8:30とした場合でお願いします。

Aベストアンサー

勤務時間=退社時間-出勤時間
残業時間=勤務時間-8:30
で数式を組み立てると

残業時間 B3=IF(OR(B1>0,B2>B1),MAX(B2-B1-"8:30",0),"")

時間を30分単位で切り捨てにする場合
残業時間 B3=IF(OR(B1>0,B2>B1),FLOOR(MAX(B2-B1-"8:30",0),"0:30"*1),"")

Qエクセルを使っての実務勤務時間計算を教えて下さい。

エクセル初心者のため、何の知識もなく困っています。
実務勤務時間の計算をしたいのですが、どうすればいいのか・・・
よろしくお願いします。

まず、A1に早出の開始時間、B1に早出の終了時間(=9:00、固定)、C3に早出の実務労働時間、D1に残業の開始時間(=17:30、固定)、E1に残業の終了時間、F1に残業時間数、G1に休日出勤をした場合の開始時間、H1に休日出勤の終了時間、I1に休日出勤をした日の実務労働時間が入るようにしたいです。
休日出勤をした場合の条件としては、12:00~13:00は昼休みの為実務労働時間から1時間を引くことと、もし休日出勤の開始時間や終了時間が昼休みの時間帯であれば、その時間は出社していたとしても、労働時間に加算されないようにしたいです。

うまく説明ができてない気もしますが、どうかよろしくお願いします。

Aベストアンサー

C1:
=B1-A1
F1:
=E1-D1
I1:
=IF(G1<TIMEVALUE("12:00"),H1-G1-TIMEVALUE("1:00"),IF(G1<TIMEVALUE("13:00"),H1-TIMEVALUE("13:00"),H1-G1))

時間の丸めや、エラー値の処理などは考慮していません。

Qエクセル2010 深夜残業時間の計算

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→8:30~17:15
 残業→5:00~8:30 と17:15~22:00
 深夜残業→22:00~翌朝5:00
 休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、 G欄に深夜残業としています。
現状は時間外を出すのに  =SUM(E5-D5)-$B$1-$C$1-G5 と単純にしてます。(G5は手入力)
これを自動で入力できたらと考えています。
5:00前に出勤することはほとんどありませんが、22:00を超えることはあります。
ただし、24:00を超えると翌日計算になります。

過去欄を参考にしようにも理解できなく困っています。
http://oshiete.goo.ne.jp/qa/8147465.html
よろしくお願いいたします。

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
 基本時間→8:30~17:15
 残業→5:00~8:30 と17:15~22:00
 深夜残業→22:00~翌朝5:00
 休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、 G欄に深夜残業としています。
現状...続きを読む

Aベストアンサー

深夜部分の残業時間を計算するなら、以下のような数式で早朝(5:00以前)部分と夜間(22:00以降)部分の深夜残業を足し算するのが簡単です。
(退出時間が翌日になる場合は25:00のような形式で入力した場合)。

=5/24-MIN(D5,5/24)+MAX(E5,22/24)-22/24

退出時間を「25:00」ではなく通常の「1:00」のように入力する場合は、以下の式で計算することができます。

=5/24-MIN(D5,5/24)+MAX((E5<D5)+E5,22/24)-22/24

ところで、深夜部分がメインの勤務がある場合、休憩時間などは考慮しなくてもよいのでしょうか?

QExcelで自作の勤怠表を作成したいです

Excelにて自作の勤怠表を作製したいです

勤務開始-勤務終了-休憩時間=勤務時間
勤務時間-残業時間-残業時間=定時時間
は数式で出来たのですが

勤務時間の一ヶ月の合計
定時時間の一ヶ月の合計
の二つをうまく関数で計算したいのですが

セルには時間の形式で
00:00という風に入力をしてます

Excelの関数に詳しい方ご教授お願いします

Aベストアンサー

時間の計算はコツがいります。

合計を出すのはsum()でできます。
合計のセルの表示を、時刻ではなく、ユーザー定義で[h]:mm とします。

給料を計算するなら、時給をかけるときに時間は24倍すること。

Qエクセル出退勤表で時間外と深夜時間外の関数

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で表示)

Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
Q4:出勤時間と退勤時間を入力すれば時間外と深夜時間外が表示されるようにしたいのですが可能ですか?
よろしくお願いします。

エクセルで出退勤表を作りたいと思っています。
所定労働時間は10:00~18:30で休憩1時間入り、7:30です。
18:30~22:00までは時間外労働時間、22:00~翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。
セルの縦列には
A:日付(例3/11で表示)
B:曜日
C:出勤時間(例10:00で表示)
D:退勤時間(例23:30で表示)
E:所定労働時間(7:30で固定で休憩の1時間を含んでいない)
F:時間外労働時間(例3:00で表示)
G:深夜時間外(例2:30で...続きを読む

Aベストアンサー

>Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか?
B2=TEXT(A2,"aaa")

>Q2:Fセルの18:30~22:00までの時間外労働時間を計算する関数を教えて下さい。
F2=IF(OR(C2="",D2=""),"",MAX(MIN(D2+(D2<C2),22/24)-C2-17/48,0))

>Q3:Gセルの22:00~翌5:00までの深夜残業時間を計算する関数を教えて下さい。
G2=IF(OR(C2="",D2=""),"",MAX(D2+(D2<C2),22/24)-22/24)

※D2+(D2<C2) C2=10:00で、D2=5:00の場合にD2=27:00とします。
※22/24="22:00"=22時のこと
※17/48=8.5/24="08:30"=8時間半(休憩時間を含む所定労働時間)のこと

QExcelでの勤務表作成 *夜勤がある場合の計算式

Excelで勤務表を作成しているのですが
関数の作成に困っています。

私の職場には日勤(9:30~18:00)と夜勤があります
夜勤は 宿直(17:00~24:00)、宿明け(0:00~10:00)
の時間帯です。

セルA 開始時間
セルB 終了時間
セルC 休憩時間
セルD 稼働時間
セルE 所定時間
セルF 時間外残業

セルDの稼働時間を求める関数に
=IF(OR(A1="",B1=""),0,B1-A1-C1)

セルEの所定時間の関数を
=IF(D1>$N$44,$N$44,D1)  としました。*N44には7:30を投入

この場合は日勤の場合の勤務時間は正しく反映されますが
夜勤(宿直、宿明け)の勤務時間を投入した場合
時間外残業が正しく反映されません。

所定時間を日勤の場合は7:30(7時間30分)
宿直の場合は6:00(6時間)
宿明けの場合は9:00(9時間)
と設定すればうまくいくと思うのですが
どのように設定したらよいでしょうか?

Aベストアンサー

開始時間で判定するなら
E1=MIN(D1,LOOKUP(A1*24,{0,9.5,17},{9,7.5,6})/24)

上記で開始時間の範囲で
00:00~09:29→宿明 9時間
09:30~16:59→日勤 7時間30分
17:00~23:59→宿直 6時間
が判定値になります。

Qエクセルで時間計算を教えてください!!

 作業の効率化と計算ミスを防ぐため、エクセルで勤務表を作成したいと思っております。
イメージとして

    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日運行等になった時のみです。
  休日など空白になっている時は、空白のまま。
 
説明が下手で申し訳ありませんが、お知恵を貸してください!!
宜しくお願いします。

 作業の効率化と計算ミスを防ぐため、エクセルで勤務表を作成したいと思っております。
イメージとして

    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...続きを読む

Aベストアンサー

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))

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-...続きを読む

Q複数の休憩時間がある場合の休憩時間の算出方法

Excel で勤務表を作っていますが、
休憩時間が複数ある場合の休憩時間の算出方法が分かりません。

D5 10:00 (出勤時刻)
E5 19:00 (退勤時刻)
F5 休憩時間
H5 =E5-D5-F5 (実稼働時間)

D6 12:00 (出勤時刻)
E6 22:00 (退勤時刻)
F6 休憩時間
H6 =E6-D6-F6 (実稼働時間)

休憩時間1
D38 12:00 (休憩開始時刻)
E38 13:00 (休憩終了時刻)
F38 1:00 (休憩時間1のトータル)

休憩時間2
D39 17:15 (休憩開始時刻)
E39 17:30 (休憩終了時刻)
F39 0:15 (休憩時間2のトータル)

休憩時間2の休憩時間帯が変わってもカスタマイズできるように
(休憩時間3、4、5と実際にはあります)
D38,D39,E38,E39は使わず
F38とF39を使って、F5、F6セルに、休憩時間を算出したいです。

また、13時以降の出社の場合は、12時から13時の休憩時間を含めないようにもしたいです。

どんな関数を用いれば実現可能でしょうか?
アドバイスよろしくお願い致します。

Excel で勤務表を作っていますが、
休憩時間が複数ある場合の休憩時間の算出方法が分かりません。

D5 10:00 (出勤時刻)
E5 19:00 (退勤時刻)
F5 休憩時間
H5 =E5-D5-F5 (実稼働時間)

D6 12:00 (出勤時刻)
E6 22:00 (退勤時刻)
F6 休憩時間
H6 =E6-D6-F6 (実稼働時間)

休憩時間1
D38 12:00 (休憩開始時刻)
E38 13:00 (休憩終了時刻)
F38 1:00 (休憩時間1のトータル)

休憩時間2
D39 17:15 (休憩開始時刻)
E39 17:30 (休憩終了時刻)
F39 0:15 (休憩時間2のトータル)

休憩時間2の休憩時間帯が変わってもカスタマ...続きを読む

Aベストアンサー

>休憩3、休憩4、休憩5、休憩6まである場合は、関数はどう変更すればいいですか?
始めにSUM関数の引数に不備がありましたので訂正します。
序に、出勤時刻と退勤時刻が未入力のとき空欄とするためのIF関数を追加しました。
=IF(COUNT(D5:E5)=2,SUM(MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38),MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39),MIN(MAX(E5,$D$40),$E$40)-MAX(MIN(D5,$E$40),$D$40),MIN(MAX(E5,$D$41),$E$41)-MAX(MIN(D5,$E$41),$D$41),MIN(MAX(E5,$D$42),$E$42)-MAX(MIN(D5,$E$42),$D$42),MIN(MAX(E5,$D$43),$E$43)-MAX(MIN(D5,$E$43),$D$43)),"")
SUM関数の内容を確認してください。
第1引数(休憩1) MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38)
第2引数(休憩2) MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39)
     ・
     ・
     ・
第6引数(休憩6) MIN(MAX(E5,$D$43),$E$43)-MAX(MIN(D5,$E$43),$D$43)
各引数は休憩時間帯を定義した行を参照しますがMAX関数とMIN関数およびセルの列記号は同じパターンになっています。
応用力で指定された休憩時間帯を増減できますので実情に合わせて変更すると良いでしょう。
これはExcelの関数を利用してどのような論理で計算するかの応用問題です。
数学が不得手の方には難しいかも知れません。

>休憩3、休憩4、休憩5、休憩6まである場合は、関数はどう変更すればいいですか?
始めにSUM関数の引数に不備がありましたので訂正します。
序に、出勤時刻と退勤時刻が未入力のとき空欄とするためのIF関数を追加しました。
=IF(COUNT(D5:E5)=2,SUM(MIN(MAX(E5,$D$38),$E$38)-MAX(MIN(D5,$E$38),$D$38),MIN(MAX(E5,$D$39),$E$39)-MAX(MIN(D5,$E$39),$D$39),MIN(MAX(E5,$D$40),$E$40)-MAX(MIN(D5,$E$40),$D$40),MIN(MAX(E5,$D$41),$E$41)-MAX(MIN(D5,$E$41),$D$41),MIN(MAX(E5,$D$42),$E$42)-MAX(MIN(D5,$E$42),$D$4...続きを読む

QExcelで勤務表を作りたいのですが

Excelで勤務表を作りたいのですが、なかなか関数がうまく使えません。
時間計算をご存知の方、教えて下さい。

入力列
A:出勤時刻
B:退勤時刻
C:勤務開始時間
*始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。
D:休憩時間
E:実働時間(始:C/終:B/マイナス:D)
F:時間内勤務(8時間まで)
G:時間外勤務(8時間を超過したところから・22時迄)
H:深夜残業(22時以降の勤務時間)

このような説明でお分かり頂けるでしょうか。
よろしくお願いします。

Aベストアンサー

順番に、その通り計算してみます。


>A:出勤時刻
>B:退勤時刻

A列とB列には「時:分」形式で時刻を記入する
0時を超えた時刻は26:15のように記入する


>C:勤務開始時間
>*始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。

A列が記入された時に計算する
9時と出社時刻の大きい方取り
=IF(A2="","",MAX(A2,TIME(9,0,0)))


>D:休憩時間

ご相談範疇外
条件に応じて休憩時間を産出する
たとえば出社時刻が12時前かつ退社時刻が13時を超えたら、など。
それ以外は0:00を計算しておく


>E:実働時間(始:C/終:B/マイナス:D)

始業時刻、退社時刻が記入された場合に計算する
=IF(COUNT(A2:B2)=2,B2-C2-D2,"")


>F:時間内勤務(8時間まで)

実働時間が計算された場合に計算する
8時間と実働時間数の小さい方取り
=IF(E2="","",MIN(TIME(8,0,0),E2))


>時間外勤務(8時間を超過したところから・22時迄)

実働時間が計算された場合に計算する
=IF(E2="","",MAX(0,MIN(TIME(22,0,0),B2)-C2-D2-TIME(8,0,0)))

>H:深夜残業(22時以降の勤務時間)

実働時間が計算された場合に計算する
=IF(E2="","",MAX(0,B2-TIME(22,0,0)))

順番に、その通り計算してみます。


>A:出勤時刻
>B:退勤時刻

A列とB列には「時:分」形式で時刻を記入する
0時を超えた時刻は26:15のように記入する


>C:勤務開始時間
>*始業9:00のため、9時より以前の出勤時刻は9:00、9:00以降の出勤はその時刻を表示する。

A列が記入された時に計算する
9時と出社時刻の大きい方取り
=IF(A2="","",MAX(A2,TIME(9,0,0)))


>D:休憩時間

ご相談範疇外
条件に応じて休憩時間を産出する
たとえば出社時刻が12時前かつ退社時刻が13時を超えたら、な...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング