gooポイントが当たる質問投稿キャンペーン>>

エクセル2007で,勤務時間を管理表を作成していますが,残業時間月合計(G36のセル)が正確に計算されません。
社員に入力してもらうのは,出勤時間と退社時間だけです。
勤務時間(在勤時間)は8:25~16:55で,途中の休憩時間は考慮しません。
項目と入力してある関数は下のとおりです。

  A  B     C     D      E         F        G
  日 曜日 出勤時間 退社時間 在勤時間  基本在勤時間   残業時間
5 1  金    8:15    17:00   8時間30分   8:30        0時間15分
6 2   土    9:00    10:10    1時間10分   0:00        1時間10分

36 月合計                 229時間40分  68:10       68時間10分           

E列の関数・・・「=D5-C5」(表示形式は h"時間"mm"分")
F列(非表示)・・・平日は「8:30」と入力(8:25~16:55が8時間30分であるため)
           土日・休日は「0:00」と入力(勤務日でないため)
G列の関数・・・「=D5-C5-F5」(表示形式は h"時間"mm"分")
E36の関数「=SUM(E5:E35)」(表示形式は [h]"時間"mm"分")
F36の関数「=SUM(F5:F35)」(表示形式は [h]:mm)
G36の関数=SUM(G5:G35)」(表示形式は [h]"時間"mm"分")

平日で,C列とD列が入力していないのに,F列に8:00が入力してあるため,G列の計算がマイナスになることが原因で正確に計算できないのでしょうか。
簡単な関数を使って正確に残業時間月合計が計算できる方法を教えてください。
よろしくお願いします。


              

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

A 回答 (4件)

No.3です!


>でも正確に計算ができません。
とありますので・・・

計算式が入っているセルすべてに
=ROUND(計算式,5) を利用してもダメでしょうか?

=IF(COUNTBLANK(C5:D5),"",ROUND(計算式,5))
といった感じです。

他に時間計算のセルがある場合も単純にプラス・マイナスするだけでなく
=ROUND(計算式,5)
のようにします。

それでも希望通りにならなかったらごめんなさいね。m(__)m
    • good
    • 0
この回答へのお礼

うまくできました!!
急いでいたので,とても助かりました。
何度も回答いただき感謝しています。
ありがとうございました。

お礼日時:2011/01/06 00:15

こんばんは!


内容を詳しく検証していませんので、外していたらごめんなさい。

(1)F列の数式を =IF(COUNTBLANK(C5:D5),"",計算式) のようにして、C・D列のどちらかが未入力であれば数値を表示しないようにしてみてはどうでしょうか?

(2)No.1の方が仰っているように時間計算は「浮動小数点」の関係でこちらの希望通りの数値にならないことがあります。

それを回避する方法として、計算式を小数点以下5桁くらいで丸める方法があります。
=ROUND(計算式,5)
といったような感じです。

それから余計なお世話かもしれませんが、
>G列の計算がマイナスになることが原因で正確に計算できないのでしょうか。
とありますので、もしマイナスの計算を行いたい場合は
当方使用のExcel2003の方法として
メニュー → ツール → オプション → 計算方法タブ で「1904年から計算する」にチェックを入れれば
マイナス計算も可能になります。
Excel2007以降も同様の方法はあると思いますが、具体的な操作は手元にExcel2007がないのでごめんなさい。

尚、この方法はあくまで最終手段で極力時間計算等にはマイナスが出ない方が良いと思います。

この程度でごめんなさいね。m(__)m
    • good
    • 0
この回答へのお礼

ありがとうございます。
=IF(COUNTBLANK(C5:D5),"",計算式) は,勉強になりました。
利用させていただきます。
でも正確に計算ができません。
また,勉強してみます。

お礼日時:2011/01/04 23:14

5/1(金)在勤時間は8:45のはずです。

また、在勤時間と基本在勤時間が同じなのに、残業が0:15つくのは変です
E列の関数にFLOORとか使われているのですか?
さらに F列は8:30と0:00しか入力されないのなら、合計で68:10となるのも変です

>C列とD列が入力していないのに,
>F列に8:00が入力してあるため,
>G列の計算がマイナスになることが
それは大きく誤差を生むはずですので、C列およびD列にシリアル値が入力されていない場合はEもFもGもゼロと入力されるようにしなくてはならないのでは?
    • good
    • 0
この回答へのお礼

ありがとうございます。分かりにくくてすみません。
「5」「36」は行番号で,
「5」行目が1日,「36」行目が31日で,「6」から「35」の間は日にちが入力されています。

お礼日時:2011/01/04 23:12

G列を以下の式にするのはどうでしょうか?


IF(C5="",0,D5-c5=f5)

時間計算はかなり難しい物です。
また、出退勤には各社の細かいルールがあり、Excelを
これに併せるのは難しいこともあります。
試行錯誤をしてがんばってください。
    • good
    • 0
この回答へのお礼

ありがとうございます。
時間を入力すると,「FALSE」が表示されてしまいます。
試行錯誤してみます。

お礼日時:2011/01/04 23:07

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

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

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

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

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

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

QEXCELで関数を使って残業時間の計算がしたいのですが・・

EXCELを使って残業時間の計算したいと思っているのですが、関数にあまり詳しくないのでアドバイスお願い致します。
(A1)出社、(B1)退社時間は30分単位で自分で入力します。
D1に10時間以降残業にし、残業に満たない場合は未表示で30分単位は0.5と表示させようと思っています。

今現在

A1=出勤時間 8:00
B1=退社時間 20:00
C1=労働時間 12:00   関数  =IF(A1="","",B1-A1)
D1=残業時間 =IF(C1="","",IF(C1>"10:00",(C1-"10:00")*24,""))

と、いう感じにやってみましたが、D1の計算がうまくいきません。よくわからないのですが、シリアル値というのが関係してるのでしょうか?宜しくお願い致します。

Aベストアンサー

[時間(hour)] / 24 で シリアル値になります

[D1] =IF(C1="","",IF(C1>10/24,(C1-10/24),""))
これでで、10時間オーバー分の時間のシリアル値が出ます。
時間表示にすると「xx:xx」形式で表示されます。

=IF(C2="","",IF(C2>10/24,FLOOR((C2-10/24),0.5/24),""))
これに、このようにFLOOR関数を噛ませると30分刻み(端数切り捨て)になります。

30分を0.5で表す、つまり0.5時間刻み表示にするならば
=IF(C2="","",IF(C2>10/24,FLOOR((C2-10/24),0.5/24)*24,""))
表示形式は数値にしてください。

Q残業時間を計算し且つ足りない分はマイナス表示したい

労働時間のセルより所定労働時間(8h)を引いて、
残業時間のセルに計算結果を表示させたいのですが、
その値がマイナスだった場合はマイナス表示させたいのです。


頑張って調べて、どちらかなら関数を適用できるのですが、
上記両方の条件を満たす関数がどうしてもわかりません。。。
至急のご回答がほしいです。。。

宜しくお願いします!!

Aベストアンサー

>残業時間のセルに計算結果を表示させたい
>その値がマイナスだった場合はマイナス表示させたい

具体的にどんなデータでエクセルを作成しているのかの情報がご相談に書かれていませんが,残業時間(就業時間)を,どんな数字で計算したいのかによっても,やり方が色々変わります。

時間数をたとえば
123.5
とか
8.25
といった「時間単位の数字」で計算したい(実際には分単位の数字で計算する場合も多くあります)なら,話は結構簡単で

A1に例えば
10.5
とか
6.0
のように残業時間を計算してあれば
=A1-8
と計算するだけで十分です。



時間数を例えば
123:10
とか
8:30
といった「時:分形式の時刻の値」で計算したいなら,エクセルのオプションで「1904年形式で計算する」にチェックを入れることで

=A1-"8:0"
と計算する事で,たとえば-2:30といったマイナスの時間数の計算結果も表示できます。

この設定を行ったブックと,そうしてないブックの間でまたいで日付の計算をすると,計算結果が間違える事だけ注意が必要ですが,時間数の計算するだけなら特に支障となる事はありません。

>残業時間のセルに計算結果を表示させたい
>その値がマイナスだった場合はマイナス表示させたい

具体的にどんなデータでエクセルを作成しているのかの情報がご相談に書かれていませんが,残業時間(就業時間)を,どんな数字で計算したいのかによっても,やり方が色々変わります。

時間数をたとえば
123.5
とか
8.25
といった「時間単位の数字」で計算したい(実際には分単位の数字で計算する場合も多くあります)なら,話は結構簡単で

A1に例えば
10.5
とか
6.0
のように残業時間を計算し...続きを読む

Qエクセルでの時間計算(2時間30分→2.5と表示するには?)

開始時刻15:00、終了時刻17:30の場合の所要時間の計算ですが、15分=0.25で表示したいのです。この計算式を教えて下さい。
また、15分=0.25の場合、10分はいくつになるのですか?この計算方法も教えて下さい。
おバカでお恥ずかしいのですが、宜しくお願いします。

Aベストアンサー

A1セルに「15:00」と、B1セルに「17:30」と、C1セルに「=(B1-A1)*24」と入れてみて下さい。C1セルに「2.5」と表示される筈です。

エクセルでは、日付時刻は「1日間なら1、2日間なら2、12時間なら0.5」として記憶しています。つまり1時間を示す値は「1/24」として記憶しています。

そこで「日付時刻の差」を24倍すると「1時間を1にした数値」になります。

15分を0.25と決めた時、15分の場合は
15/60
=1/4
=0.25
と計算するのですから、同様にして10分の場合は
10/60
=1/6
=0.166666666…
と計算します。

Qエクセル関数で、残業時間から残業代を算出する方法

残業時間から残業代を算出する場合に、
関数を使って算出したいのですが、なかなかうまくいきません。

例えば、
該当月の残業時間が3時間30分で、時間単価が1500円の場合セルに、
=3:30*1500
といれるとエラーになってしまいます。

どなたか、うまく算出できる方法をご存知の方はおられないでしょうか?
エクセル初心者の為、かなり困っております。

わかりずらい説明で恐縮ですが、
ご回答頂ければ幸いです。

宜しくお願い致します。

Aベストアンサー

エクセルの時間や時刻シリアル値(<-日付シリアル値)という、エクセルの基礎的なことを勉強しないで、質問者は課題に取りかかろうとしている。
セルに3:30と入れると、
時刻を入力したと解釈され
1日は1=24時間という日付シリアル値との連造性で、3:30(3時間30)は24時間に対し0.145833333333333と計算されている。この値に時給2500円をかけてもダメで、3.5(時間)に1500円をかけないといけない。それでは0.145833333333333から3.5をどうして出すかだが、24(時間)をかけると出てくる。
=A1*24*B1
なぜなら
3時間30分は=(3*60+30) -->分ベース
1日は24*60分
1日24時間単位では=(3*60+30)/(24*60)が上記の数字。
これに24を掛けると=(3*60+30)/(24*60)*24-->(3*60+30)/60
=3.5 ということになる。
普通はこういう計算を一々辿らず、24を掛けるように覚えてしまう。
日付シリアル値はGoogleででも「日付シリアル値 エクセル」で照会すれば説明したサイトがたくさん出てくる。

エクセルの時間や時刻シリアル値(<-日付シリアル値)という、エクセルの基礎的なことを勉強しないで、質問者は課題に取りかかろうとしている。
セルに3:30と入れると、
時刻を入力したと解釈され
1日は1=24時間という日付シリアル値との連造性で、3:30(3時間30)は24時間に対し0.145833333333333と計算されている。この値に時給2500円をかけてもダメで、3.5(時間)に1500円をかけないといけない。それでは0.145833333333333から3.5をどうして出すかだが、24(時間)をかけると出てくる...続きを読む

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

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

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時間半(休憩時間を含む所定労働時間)のこと

Qエクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。

エクセルで時間管理表を作成しています。

A1の時間とB1の時間を足したものから10時間を差し引いたものをC1に入れたいのですが、
現状はC1に =(A1+B1)-"10:00"といれてみました。

A1+B1が10時間以上の場合は、時間が表示されるのですが、
(例えばA1が8:30、B1が11:30だと、C1は10:00 と表示されますが)
A1+B1が10時間未満だと、セルが########・・・となってしまいます。

10時間未満の場合は、10時間以上分がなし、ということで
0:00と表示させたいのですが、どのようにすればよろしいでしょうか。

尚、表示方法は、時計の時間を意味しているのではなく、
8:30の場合は、8時半ではなく、8時間30分、という意味で作成しています。

この相談箱の中で同じような事例を探してみたのですが、
見つけることができませんでしたので、
同じ質問が既にある場合は申し訳ないのですが、
どうぞ宜しくお願い致します。

Aベストアンサー

 =MAX((A1+B1)-"10:00",0)

「計算した時間が負なら0」⇒「計算した時間と0のうち大きい方」
という考え方もできます。

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.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&Aランキング