エクセルで就業管理をしようとして困っています。

質問

エクセルに数式を入力して就業管理をしようと試みているのですが、上手く動かず困っているので、どなたか手を差し伸べてくださいませ。。

作成している表のすべてを出してしまうと煩雑になるので、問題箇所だけを抜き出して言いますと…
エクセルの【HOUR関数】を用いて『退勤時刻－出勤時刻－休憩時間－所定労働時間(8時間)＝時間外労働』という数式を導き出したいのですが、上手くいきません。（ちなみにここでは【MINUTE】は無視しています。）

たとえば、エクセルの【A1に出勤時刻】【B1に退勤時刻】【C1に休憩時間】を手入力し、【D1に数式】を入れます。
D1に入れる数式は、、、

【=B1-A1-C1-"8:00"】
とします。

A1、B1をそれぞれ…
8:00　と　17:00　や　8:15　と　17:15　にした場合は上手くいくのですが、8:20　と　17:20　や　8:30　と　17:30　にした場合などは数式のエラーで『NUM』が返ってきます。

数式の検証などもしてみたのですが、解決しません。

どなたか詳しい方、お導き願えませんでしょうか。
よろしくお願いします。

回答No.4

KURUMITO · ベストアンサー

回答３です。
エラー表示でVALUEが表示されるケースは次のようなものですね。ご質問のケースでは考えられないことです。

http://office.microsoft.com/ja-jp/excel-help/HP005203950.aspx

回答No.5

kagakusuki

回答番号:ANo.2です。

>エラーとなってしまうのを、どうにかしたいという事でしょうか。

>いいえ、違います。

という事は、エラーを解決する方法を御尋ねされている訳ではないという事になりますが、それでは一体何を御尋ねになっておられるのでしょうか？
　「いいえ、違います。」で終えてしまっているだけで、どの様に違うのかに関する御説明が無いため、御質問の趣旨が解りません。

　もしかすると、「#NUM!」エラーが現れる理由を御尋ねになっておられるのでしょうか？
　「#NUM!」エラーは、数式、或いは関数に入る数値に問題がある場合に表示されるエラーです。
　HOUR関数は、関数内に入れられたシリアル値で表される年月日と時刻の内、年月日と分、秒を無視して、時間の部分のみを取り出す関数です。

　例えば、A1セルに

2011/4/1 17:59

と入力されている場合には、

=HOUR(A1)

の値は、時刻の時間の部分である17という整数値になります。
　Excelのシリアル値は、1899年12月31日の午前0時00分00秒から、何日が経過した時間であるかを表した数値になっていますから、例えばセルに午前9時を表す

9:00

を入力すると、それは1899年12月31日の午前9時00分00秒のを意味する

1900/1/0 9:00:00

が数式バーに表示されます。
　これは、1899年12月31日の午前0時00分00秒から、24分の9日が経過した時間になりますから、データとしてのシリアル値は0.375という数値になります。（9時間＝0.375日）

A1セルに8:00、B1に17:00を入力した場合、その差は8時間になりますから、C1セルに0よりも大きな値が入力されていると、

=B1-A1-C1-"8:00"

という数式の計算結果はマイナスになります。
　Excelのシリアル値で扱う事が出来るのは、1899年12月31日の午前0時00分00秒から経過した時間だけであり、それ以前の時間は扱う事が出来ません。
　そのため、HOUR関数にマイナスの値を入れると、「#NUM!」エラーとなる訳です。

回答No.3

KURUMITO

エラー表示が時間の計算でマイナスになる場合などには####が表示されますが、NUMの表示は考えられません。お示しの計算式では計算できない桁数の計算を実行している場合ですがおかしいですね。別のファイルを使った新しいシートでも同じことが起こるのでしょうか？

補足

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

＞エラー表示が時間の計算でマイナスになる場合などには####が表示されますが、NUMの表示は考えられません。

とのことですが、実際には画像のように『#NUM!』が出てしまっているのですが、これはどう理解すればいいのでしょうか？

＞お示しの計算式では計算できない桁数の計算を実行している場合ですがおかしいですね。

どういう意味なのでしょうか？
私の示した数式は『桁数の制限により、計算できない』ということですか？

＞別のファイルを使った新しいシートでも同じことが起こるのでしょうか？

画像のシートは、現在私が作成している管理表とは、別ファイルです。

回答No.2

kagakusuki

要するに、時間外労働時間をシリアル値ではなく、1時間単位の数値で算出する際に、実労働時間が8時間未満の場合は、時間外労働時間がマイナスとなるため、エラーとなってしまうのを、どうにかしたいという事でしょうか。
　それならば、実労働時間が8時間未満の場合は、時間外労働時間を0時間とすれば宜しいと思います。

　そのための関数の一例は、次の様なものです。（セルの書式設定は「標準」にして下さい）

=IF(COUNT(A1,B1,C1)=3,IF(B1+(A1>B1)-A1-C1-"8:00"<0,0,FLOOR(B1+(A1>B1)-A1-C1-"8:00"+"0:00:01","1:00"+0)*24),"")

　計算結果は、1時間単位で表した場合の数値データとなり、1時間未満の端数は切り捨てとなります。
　又、実労働時間が8時間以下の場合は、値は0となります。
　尚、22時以降の深夜労働時間に関しては、特に考慮しておりません。
　因みに、1時間刻みではなく、例えば15分刻みで端数切り捨てた時間を、1時間単位で表した数値データとして計算する場合には、数式中の

"1:00"+0

という箇所を、

"0:15"+0

に変えて下さい。

　それから、数式中の

COUNT(A1,B1,C1)=3

という部分は、A1,B1,C1の各セルに、何れも時刻（又は、日付や数値）が入力されている事を判定している部分で、IF関数との組み合わせにより、3セルの内、時刻が入力されていないセルが1個でもあれば、何も表示されない様になっています。

　数式中の

B1+(A1>B1)-A1-C1-"8:00"

の部分は、時間外労働時間をシリアル値で計算している部分で、その中の

+(A1>B1)

の部分は、例えば、出勤時刻が16:00で、退勤時刻が夜中の1:00である場合の様に、A1の値の方が、B1の値よりも大きい場合には、計算する上では数値の1として扱われ、
逆に、A1の値の方が、B1の値よりも大きい場合や、A1とB1が等しい場合には、数値の0として扱われます。
　シリアル値の計算では、年月日、時間、分、秒の全ては、1日の何倍の時間であるのかで表していますから（例えば1時間はシリアル値では24分の1に等しい数値になります）、
A1の値の方が、B1の値よりも大きい場合には、計算する上では数値の1として扱われる数式が足されているという事は、
即ち、出勤時刻が退勤時刻よりも小さい時刻の場合には、退勤は翌日になってから行ったと見做して、退勤時刻に1日（＝24時間）を加えるためにある部分という訳です。（流石に連続して24時間以上勤務した場合には対応していません）
　FLOOR関数は、指定した数値刻みで数えた場合に、端数を切り捨てる関数ですから、

FLOOR(B1+(A1>B1)-A1-C1-"8:00"+"0:00:10","1:00"+0)

の部分は、時間外労働時間を、1:00（1時間）刻みで端数を切り捨てる関数になります。

"1:00"

の部分は、計算式の中に組み込まないと、時刻を表す数値とは見做されないため、0を足して、数式の中に組み込んだ形にしています。
　シリアル値の計算では誤差が発生する場合があり、見かけは1時間0分0秒を表す

1:00:00

というデータが、厳密には、0時間59分59.999・・・・・秒であった場合には、
1時間未満の端数を切り捨てると値が0時間となってしまう、
という場合もあるため、端数を切り捨てる前に、念のために10秒を余分に加えてから、端数を切り捨てています。
　この様にして計算した「シリアル値で表した時間外労働時間」を24倍する事で、1時間単位の数値に変換しています。

お礼

＞要するに、時間外労働時間をシリアル値ではなく、1時間単位の数値で算出する際に、実労働時間が8時間未満の場合は、時間外労働時間がマイナスとなるため、エラーとなってしまうのを、どうにかしたいという事でしょうか。

いいえ、違います。
お示しいただき、ありがとうございました。

回答No.1

waterline123

一応参考ということで
【A1に出勤時刻】【B1に退勤時刻】【C1に休憩時間】
を前提にすると
D列に
=B1-A1-C1
と記述すればD列は時間のシリアル値が出てきます（24時間が1.0ですので、この値に24をかけると時間が出てくるという仕組みです。）
E列に
=HOUR(D1)-HOUR(8/24)
と記載すれば、時間外労働の（時間）が出てきます。（8時間勤務前提で8/24としています）
F列に
=MINUTE(D1)/60
と記載すれば時間外労働の（分）が出てきます。
（60で割っているのは、給与計算しやすくするためです）
G列に
=E1+F1
と記載すれば、F列＋G列なので、時間外労働の時間＋分が出てきます。
たとえば、1時間半時間外労働すればG列は1.5が戻ります。

あとは、G列に時間外労働の時給を掛け算すれば計算できます。

A列　8：30（出勤時刻）
B列　19：00（退社時刻）
C列　1：00（休憩時間）
と記入すれば
D列　0.396（実労働時間のシリアル値）
E列　1.000（D列から基準労働時間8時間を引いた時間）
F列　0.500（D列から基準労働時間8時間を引いた分を60で割ったもの）
G列　1.500
になるはずです。

一応、わかりやすくするために分割していますが、当然一つのセルにまとめることは可能です。

エクセルでの時間計算は慣れないとめちゃくちゃ混乱するので、
ひとつずつ順番に考えるとやりやすいですよ。
あくまでも基本はシリアル値であることをお忘れなく。

お礼

シリアル値…1=24Hが参考になりました。
その他の記述は質問外であったので蛇足でした。
ありがとうございました。