みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

関数教えて!

始めまして、エクセル2007の関数のことで教えて欲しい事があります。 手作りで勤務表を作ったのですが、残業時間を自動的に計算して表示する関数式が分からなくって困ってます。
表はシンプルに開始時間セルと終了時間セルと残業時間セルの3項目を作っています。
開始時間と終了時間を打ち込んだら自動で残業時間を表示さしたいのですが・・・ちなみに、基本時間は8:00~17:00休憩時間込で9時間です。それ以外を残業とみなしています。(早出もです!)

どなたか 関数に詳しい方教えてください。
よろしくお願いします。

投稿日時 - 2009-07-06 00:25:39

QNo.5101819

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

#5です。

>ただ、一つ「0:00」の表示の件でどうしても 
>8:00~17:00の時に「0:00」が出てしまいます。
>何か良い手はありますか?

あー、確かに、8:00~17:00 のときは、
演算誤差が発生してキッカリゼロになりませんね(^^;;

「基本時間」の組み合せなのに気付かないとは
我ながら少しウカツでした。お手数おかけしてすみません。

「シリアル値 ⇒ 文字列 ⇒ シリアル値」
で誤差を吸収するアプローチで処理します。

 =--TEXT(MAX(B2-A2-"9:00",0),"[h]:mm")

■日経PC21 / エクセル「演算誤差」対策講座
http://pc.nikkeibp.co.jp/pc21/special/gosa/index.shtml

以上ご参考まで。

投稿日時 - 2009-07-07 00:04:07

ANo.7

毎日のようにこのタイプの質問がここにでる。
質問者は過去のこのコーナーの質問や、Googleで「エクセル 勤務時間表 関数」などで照会して、勉強し、わからない点に絞って質問のこと。
課題丸投げの質問になっている。
ーー
ほかに、エクセルで、セル番地の引き算で、経過時間が出る理屈の根拠たる、エクセルの日付・時刻シリアル値に関する理解は大丈夫ですか。
Googleなどで「日付・時刻シリアル値」で照会
http://www.officepro.jp/excelfunc/date/index0.html
ほか多数出てくる。

投稿日時 - 2009-07-06 10:32:51

ANo.6

この問題は、「関数に詳しい」云々の前に、正しい認識(定義)が必要です。
この問題を解決しないで、関数式を作っても意味がありません。

正しい認識(定義)とは、残業時間の定義です。
単に、残業時間=(業務終了時刻-業務開始時刻)-9時間で、いいのですか。
残時間がマイナスになる場合や、既定就業時間(休憩時間込で9時間)は満足しているが、業務開始時刻や業務終了時刻が規定外の場合はどうするのか、時刻や時間の入力形式や表示方法はどうするのか等々。
単に、残業時間=(業務終了時刻-業務開始時刻)-9時間の場合を記述します。
A1:業務開始時刻,B1:業務開始時刻,C1:残業時間としてC1の関数式は、
 =IF(OR(A1="",B1=""),"",(B1-A1)*24-9.00))
但し、A1,B1の表示形式(書式)はユーザー定義で[hh:mm]で、「8:00」と入力する必要があります。
また、C1の表示形式(書式)は[数値]で、計算値C1は時間単位の表示になります。

投稿日時 - 2009-07-06 09:36:58

ANo.5

>残業時間のセルには時間が入力されて無いと、##### ・・?
>みたいな表示が出ています。

=MAX(B2-A2-"9:00",0)

とすれば、
・開始時刻から終了時刻までが9時間に満たない場合
・時刻が入力されていない場合
は 「0:00」 になり、オートSUMで合計を求めることができます。

なお、「0:00」ではなく空白にしたい場合は、
セルの表示形式を [h]:mm;;; としてください。
------------------------------------
それから、例えば、
 開始時刻 :  9:00
 終了時刻 : 18:00
のような場合には、
#4さんが回答された数式とは異なる結果が返ります。ご注意ください。
私の数式は上記の場合、休憩込みの9時間で残業なしと判断し、0:00 を返すようにしています。

ご参考まで。

投稿日時 - 2009-07-06 06:42:21

お礼

ご丁寧な回答、ありがとうございます!
おかげさまで、希望の動きになりました。
ただ、一つ「0:00」の表示の件でどうしても 8:00~17:00の時に「0:00」が出てしまいます。何か良い手はありますか?
それ以外は、バッチリでした。
ありがとうございます!

投稿日時 - 2009-07-06 23:10:37

ANo.4

残業時間を求めるのですね。
次のようにしてはどうでしょう。
例えばA1セルに開始時間、B1セルに終了時間があるとして残業時間は次の式であらわされます。
="8:00"-MIN("8:00",A1)+MAX("17:00",B1)-"17:00"

投稿日時 - 2009-07-06 06:29:03

ANo.3

9時間とそれ以外ならこれでも十分ですが、
おそらくこれだけではないと思いますけど。
  A      B    C
1 出勤時間 退勤時間 残業時間
2           =B2-A2-"9:00"

何も条件がありませんので、とりあえずたたき台程度です。

投稿日時 - 2009-07-06 00:58:17

ANo.2

開始時刻と終了時刻のセルにHH:MMという時刻形式で入っているの
でしたら、残業時間のセルに
=B2-A2-TIMEVALUE("9:00")
と入れてみてください。
ちなみに式の例は、セルA2に開始時刻、B2に終了時刻としています。
残業時間のセルの表示形式を時刻のHH:MMとします。

投稿日時 - 2009-07-06 00:48:29

補足

早速の回答、ありがとうございました。
残業時間が自動的にでました。
ただ一つ問題が・・・書くの忘れてしまった事が一つあります。
日にちごとの残業時間の合計をオートSAMで出せる様になってたのですが、出なくなってしまいました。
残業時間のセルには時間が入力されて無いと、##### ・・?
みたいな表示が出ています。

合計時間は、手動で打ち込まなければやはりダメでしょうか?
追加のご回答頂ければ幸いです。

投稿日時 - 2009-07-06 02:05:02

ANo.1

時刻がシリアル値で入力されているのであれば、そのまま引き算が出来ますよ。
 A      B    C
開始時間 終了時間

残業を9時間を越えた時間というならば
=B2-A2-"9:00"
残業を17:00以降の時間というならば
=B2-"17:00"
早出は ="8:00"-A2

投稿日時 - 2009-07-06 00:48:17

あなたにオススメの質問