エクセルの関数について

さっと見ただけですが、Ｂ２セルに入力する文字、もしくはIF文のＢが、全角でないか、チェックしてみてください。

そもそも論で突っ込んでもいいですか？
これって出勤簿なんですよね？
出勤簿って体調によっては遅刻したり早退したり、時には早出残業や普通残業とか深夜残業とかありますよね？出勤の有無はもちろん、そういうのも管理していくんじゃないですか？
そういう要素は全く考えなくてもよい職場なんでしょうか？

というのも、記載されている関数をざっと見ると、

１．Ｃ列（例としてＣ２）

　　B2(どのシフトかリスト等入力規則で選択させるセル)が
　　
　　Aだったら9:00
　　Bだったら10:00
　　Cだったら8:00
　　休だったら0
　　
　　を記入しなさい、という意味です。

　　つまり、シフトを選択した時点で、出勤時刻を強制的にそれぞれの
　　シフトの開始時間にしているわけです。
　　
　　業務命令等で、どうしてもやらなければいけない事があって、出勤時刻が
　　普段より2時間とか早く来た場合、いわゆる早出残業とか考慮する必要は
　　ないのでしょうか？

　　また、寝坊、体調不良、外回り等何らかの理由で遅刻した場合はどうする
　　のでしょうか？
　　それでも出勤時刻は変わらないのでしょうか？

　　仮に、出勤時刻は強制的に埋め込むという事にしたとしても、A～Cの場合は
　　hh:mm形式の"時刻形式"を埋め込むのに対し、休みの場合は0という数字を
　　埋め込んでいます。
　　
　　エクセルは賢いのである程度これでも計算をやってくれますが、必ずどこか
　　で無理（不良）が出てきます。
　　
　　時間計算させるなら時間が入る箇所はすべて時間形式に統一した方がエクセル
　　関数に慣れるまでは良いですし、統一しないなら統一しないなりに工夫が必要です。
　　
　　
　　同様の事がＤ列（退勤時間）にも言えます。
　　早退した場合や、仮にＡシフトの人が19時まで残業したとしても、強制的に
　　退勤時刻が18:00になるのはおかしいのでは？
　　

２．実働時間

　　出勤時刻と退勤時刻を強制的に埋め込んでいるので、ここが日々変化するはずが
　　ないですよね？
　　だったら8：00という時間形式か、統一しないんだったら８という生値を埋め込ん
　　でも良いように思いますが？
　　B2が休の時だけ0：00か0を埋め込む形式にして。

３．残業時間

　　ここも、出勤時刻と退勤時刻を強制的に埋め込んでいるので意味ないのでは？
　　常に残業は生じえませんよね？
　　
　　これがきちんと出勤時刻と退勤時刻を入力する、というのでしたら話は別ですが。
　　
　　ちなみに#####は、表示するセルの幅が足りないに場合に出るんじゃないですかね？
　　ここで最初の話に戻りますが、扱っているのは時間計算です。
　　
　　E列及びF列のセルの書式設定（表示形式）は、標準になっていませんか？
　　もし標準になっていたら、時刻にしたらどうなりますか？
　　
　　
　　こういうところで落とし穴に落ちるのですが、エクセルは冒頭でも言った通り、
　　ある程度は自分で判断して計算してくれるので良いのですが、たまにそれが
　　原因で思ってもいない計算結果が出たりします。
　　今回はTIME関数の使い方がおかしい気がしますが、エクセルが補填してくれて
　　いるのでしょう。
　　何故おかしいかは、割愛しますが、計算結果にシリアル値を返したいのか、
　　それとも時刻形式を返したいのか考えて関数を使っていかないと、後々面倒になります。
　　
　　また、休憩時間も1時間と決め付けているのもどうかと思います。
　　御社の就業規則上は、6時間労働だろうが、1時間強制的に休憩を取らせる
　　規程なのでしょうか？
　　Aシフトの人が、病院などにより11時に出社して、やっぱり体調悪くて
　　12時に帰った場合も1時間休憩時間として控除されるのでしょうか？
　　
　　今、記載している数式はそういう式ですので、よく就業規則に照らし合わせて
　　作って下さい。

　　それと、エクセルの時間計算では、日付をまたぐ時間計算は注意が必要ですよ。
　　御社がそのような残業が生じない職場なら良いのですが。

　　以上

削除されました

EXCELでは書式が「時間」のセルに負の値を入力するとエラーになり　"######"　表示になります。
また、EXCELでは時刻のシリアル値は数値の小数部を利用しています。結果として、時刻計算は小数同士の計算となり、この小数同士の計算の際に、微少ではあるが演算誤差が生じることがあります。例題では、実働時間が8時間で残業ゼロとなるべきですが、演算誤差のために負の値となり、エラー表示になっているのではないでしょうか。
残業時間が負の値にならないようにするには　F2　の式を　=IF(C2=0,0,
max（D2-C2-TIME(1,0,0)-TIME(8,0,0),0）)　とすれば良いのではないでしょうか。　

数式、B2セル内どちらも確認しましたが、全角半角が問題ではないようです。
19:00-10:00に問題があるようで、仮にBシフトを11時-20時に変更するとエラーが出ませんでした。


> さっと見ただけですが、Ｂ２セルに入力する文字、もしくはIF文のＢが、全角でないか、チェックしてみてください。

削除されました

早速試したところ、無事に計算が動きました。
まだまだ勉強不足であることを痛感いたしました。

この度は本当にありがとうございます。


> EXCELでは書式が「時間」のセルに負の値を入力するとエラーになり　"######"　表示になります。
> また、EXCELでは時刻のシリアル値は数値の小数部を利用しています。結果として、時刻計算は小数同士の計算となり、この小数同士の計算の際に、微少ではあるが演算誤差が生じることがあります。例題では、実働時間が8時間で残業ゼロとなるべきですが、演算誤差のために負の値となり、エラー表示になっているのではないでしょうか。
> 残業時間が負の値にならないようにするには　F2　の式を　=IF(C2=0,0,
> max（D2-C2-TIME(1,0,0)-TIME(8,0,0),0）)　とすれば良いのではないでしょうか。　

kmary さん


こんにちは。
げんたです。

> EXCELでは書式が「時間」のセルに負の値を入力するとエラーになり　"######"　表示になります。

本当ですね。
マイナスの時間というのは有り得ないので、これまで深夜残業等の日付を跨ぐ時間計算の場合もそうですが、
シリアル値に変換して減算処理等必要な計算をした後、必要であれば時刻形式などに戻したり、
マイナスになる場合は0時間や０分と扱うなど、必要な数値にするなどしてましたので全く意識
してませんでした。

しかし、スレ主の例題のBシフトの場合、単純に時間計算の部分だけ見れば　19時から10時を
引いて休憩と所定労働時間を引いたら残業は0時間になるはずですが、時刻形式の演算の中に
シリアル値を扱うTIME関数を使っていることからkmaryさんのいう通りシリアル値計算の演算
誤差が生じているのかもしれません。
ちなみに私の環境（Excel2003）では、スレ主の式でもマイナスにならずに0：00：00となって
います。

D2、C2が時刻形式で表示しているので、休憩時間も所定労働時間も同じ形式にしてから減算すれば
良いとも思いますが。

　例）D2-C2-TIME(1,0,0)-TIME(8,0,0)の式を　D2-C2-"1:00"-"8:00" と変える。
　　　もちろん、セルの書式設定は時刻に設定して。


ただ、MAX関数は引数の最大値を返すだけですが、kmaryさんのMAX関数の使い方、面白いですね！なるほど、と感心しました。


まぁ、出勤時刻や退勤時刻を強制的に記入させる式を見る限り、スレ主は例として上記計算式を
挙げただけかも知れませんし、スレ主のエクセルのバージョン等も分かりませんし、時間計算の
結果がマイナスになるのでしたら成らないように式を工夫して、としか言えないですが。