エクセル勤怠表 SUM関数の表示がちゃんと出ない

度々、同じ勤怠表にて質問させて頂きます。
日々状況が変わり作り変えてます。

図のようにS8セルにSUM関数を入れているのですが、SUM合計がちゃんと表示されません。
Ｎ列のSUM合計は15:00になるはずなのに、13:00です。
Ｎ列どれか１つ5:00を消すとS8セルには8:00と結果が出ます。
N列の数式を残して結果の数字だけすべて消すと、########となります。

N列の計算は
=IF(M8<>"",G8-F8-E8-1/3,"")
M8に数字がある時に残業時間を計算するように（しているつもり）

なぜ単純なSUM関数が変な結果を出してくるのでしょうか？
Ｎ列の計算式がおかしな式なのでしょうか？
隣のR8とQ8も同じようにSUM関数ですが、ちゃんとした結果が出ます。

それと、Ｎ29に残業時間は0:00ですが、この0:00を表示させないのはどうやったらいいでしょうか？
ユーザー定義でh:mm;;にしてます。
L列も同様残業0:00が表示されてしまいます。

どこか私が悪いのでしょうが、何がダメなのか分かりません。
ご指摘をお願いいたします。

画像をもっとはっきりとして添付したいのですが、元は大きいのですが、小さく見え難くなってしまいます。すみません。

質問者からの補足コメント

• chonami様
  引き続きありがとうございます。
  画像、同じようにしてるんですが、何故か見えないですね（泣）
  http://metal-cat.pupu.jp/www/goo_kintai.jpg
  こちらにupしました。お手数おかけします
  ダウンロードされちゃうけど…
  http://metal-cat.pupu.jp/www/goo_kintai.xlsx　
  Excelファイルもupしました。
  
  残業は8時間労働以降の時間
  深夜は20:00～5:00までの労働時間（滅多にないですが一応付けました）
  
  よろしくお願いいたします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/07/01 17:31

• 銀鱗様
  ご指摘ありがとうございます。
  外部サイト…。私がレンタルしている有料サーバーなので安全ではあるので、大丈夫だと思いました。
  
  [####」はおっしゃる通り標準にしてみたらマイナスになっていました。
  0:00は「-1/3」を「-"8:00"」にしてみても変わりませんでした。
  
  S8にSUM関数のN列範囲　=SUM(N4:N32)
  値　=IF(M8<>"",G8-F8-E8-1/3,"")の結果として、N列に5:00が3箇所でSUMの合計が15:00になる予定が13:00になってる
  
  Ｅ列　　　Ｆ列　　G列　　　Ｈ列　　　・・・・Ｍ列
  出勤時間　休憩　退勤時間　実労働時間　　日・祝出勤労働時間
  9:00　　1:00　　23:00　　13:00　　　　　　13:00
  
  H列の計算式は
  =IF(G8="","",G8-F8-E8)
  
  画像頑張ってみました。見えるでしょうか？

  
  No.4の回答に寄せられた補足コメントです。 補足日時：2021/07/02 13:30

No.1 回答者： chonami 回答日時： 2021/07/01 16:33

全然内容がわからないです。

まだ前回の添付の方が数字がクリアに見えましたが同じように貼り付けはできないですか？

それと、残業と深夜の基準もよくわからないです。

この回答へのお礼

補足いれました。
画像に関しては、分からないので、別途質問あげようかな。
別途詳しい人がいらっしゃるかもしれないので。

お礼日時：2021/07/02 10:28

No.2 回答者： mike_g 回答日時： 2021/07/01 18:36

》 画像をもっとはっきりとして添付したいのですが、

》 元は大きいのですが、…
真面目にそう思うのなら、細切れ何回かに分けて補足欄にアップすれば好いのにィ～！

この回答へのお礼

ご回答ありがとうございます。
補足があってそこに画像が貼れるのを初めてしりました。
今後参考にします。

しかし、こちらを拝見して、細切れにしてみようと
試みましたが、縦ｘ横のサイズも自動変更されるようで、
エクセル画面(モニターサイズ？）を見えるギリギリの７０％に下げて
アップしてみてもそれでも元画像のほうが大きく、教えて！gooにアップされると文字等は潰れて見えなくなってしまいます。
写真とか大きな文字のみとかならいいのでしょうが、
エクセルはセルが見えないとよく分からないと思うので、A1～必要な場所まで載せると縦ｘ横がある程度必要になります。
なので、自動に小さなサイズに変換されてしまうので、どうしたものやらです。

お礼日時：2021/07/02 10:41

No.3 回答者： oniwabangairu 回答日時： 2021/07/01 19:51

N列の計算式を　修正してはいかがでしょうか。

=IF(AND(M4<>"",G4-F4-E4-1/3>=0),G4-F4-E4-1/3,"")

この回答へのお礼

ありがとうございます。
N列を参照させていただき変更したら、SUM関数も結果がちゃんと出ました。
関数結果に関数結果を足すSUM関数だと、正確な計算式じゃないと反映しなかったのでしょうか。何が悪かったのか謎です。

それと補足した画像には載っていないですが、質問時に残業時間が0:00になってしまい、h:mm;;にしても表示されてしまいます。
補足画像L14セルがそうです。
土出勤(労働時間)が8:00で残業は0:00時間ですが、表示させたくない。
土出勤(労働時間)が8時間以下の場合は何も表示されないのですけどねぇ。

お礼日時：2021/07/02 11:03

No.4 回答者： 銀鱗 回答日時： 2021/07/02 11:57

ぶっちゃけ、外部サイトを経由しての質問はやめた方が良い。

あと、大きな画像は必ず500×500ピクセルに収まるサイズに縮小される。

「#######」が表示されるのは、時間がマイナス表記になっているから。
セルの表示形式を「標準」にしてみましょう。マイナスの値になっているはずです。

ゼロじゃないからそれっぽい時間が0:00と表示されるパターンがあります。
微少誤差ってやつです。
数値にすると「0.0000013」とかね。
計算に「-1/3」てのがあるせいだろうと思う。コレを「-"8:00"」と文字列で引き算させてみてください。
（まあ、改善される保証はありませんけど）

・・・本題・・・

＞N列の計算は
＞=IF(M8<>"",G8-F8-E8-1/3,"")
＞M8に数字がある時に残業時間を計算するように（しているつもり）

ええと、
まずは合計しているN列の範囲の値をすべて教えてください。
それから、
　M列セル、
　G列セル、
　F列セル、
　E列セル、
の値もすべて教えてください。
もしも数式が入力されているなら、その数式と参照しているセルの値もお願いします。

数式が悪いのか、入力されている値が悪いのかを判断するには
本来そこまで示さないと質問として成り立ちません。

この回答へのお礼

こちら、お礼してませんでした。
この度は本当にありがとうございました。

お礼日時：2021/07/05 10:27

No.5 回答者： chonami 回答日時： 2021/07/02 14:50

13時間になる原因は、5/9に日祝出勤が6時間あるから計算式がTRUEになって、6時間-8時間でマイナス2時間になっていることですね。

マイナスが表示されない表示形式にしているからわからなかったからだと思います。

N列の式を
=IF(M8="","",MAX(G8-F8-E8-"8:00",0))
などのように変えてみては？

それと、もう少し時刻計算について勉強した方がいいと思います。

余談ですが、通常は深夜勤務は22時からなんですが、会社的に20時からとなっているのでしょうか？

この回答へのお礼

ありがとうございます。

深夜勤務の時間はおっしゃる通り22時からですね。
入力間違えです。式はちゃんと22時に設定されてました。

時刻計算というより、関数を使うならもっと関数について勉強しなければ
行けない状態ですよね。
教材買ったり有料サイトで勉強したりはしているんですが、
言い訳ですが、時間がなく、先に成果を求められてしまうので、
皆様に頼ることになっております。
頑張って勉強していきます。
また、何かありましたらよろしくお願いします。

お礼日時：2021/07/02 17:21

No.6 回答者： yoreyore 回答日時： 2021/07/02 14:52

＞　h:mm;;にしても表示されてしまいます。

補足画像L14セルがそうです。
＞ 土出勤(労働時間)が8:00で残業は0:00時間ですが、表示させたくない。
＞ 土出勤(労働時間)が8時間以下の場合は何も表示されないのですけどねぇ。

セルの書式設定の仕方によっては、小数を日時や時間にしたり､マイナスを値を表示させないようにしてしまうこともできますが、そのような設定をしたセルを参照して合計などの演算をさせてしまうと、｢一見すると、演算が正しく行われていない！｣ようになってしまうと思います。
1/3を時間に扱い時間を演算する式をお考えなのですから、｢書式設定だけで対処しては、場合によってはマズイことになる｣のもおわかりかと思います。
表示だけのことを考えずに、そのセルの値をどう扱うのかも考えて、例えば G4-F4-E4-1/3 や L4-F4-E4-1/3がどのような値になり得る可能性があるか、妙な値に(自分では望まない値に)なってしまう危険がある場合には、条件式などを使うのがイイと思います。

この回答へのお礼

yoreyore様
お礼が遅くなり申し訳ございませんでした。
関数が分からないので、どの関数を使えばいいのかが発想できません。
この書式設定もネット検索した結果で使用した物です。
もっと関数を勉強せねば行けないのはわかっているのですが、
勉強する時間より成果を先に求められてしまうので、皆様に頼る事になっております。
頼った結果、色々な関数を勉強させていただいております。

ってことで、yoreyore様がおっしゃる条件式は何を使えばいいのか解りません(泣)

お礼日時：2021/07/05 10:25

No.7 ベストアンサー 回答者： 銀鱗 回答日時： 2021/07/02 15:08

補足ありがとうございます。

また、補足に添付された画像なら十分読み取れます。

・・・本題・・・

N列の数式が間違いですね。
そりゃM22セルはマイナスの値になっちゃうよ。
当然です。

そんなわけで結論から言うと、
　=IF(M8<>"",MAX("8:00",G8-F8-E8)-1/3,"")
これで解決。

要は、N列のマイナスになる計算がそもそもの原因という事です。
ならば、マイナスにしなきゃいい。ここは残業時間なんで残業時間が無ければ"0:00"で良いわけだ。
ってことで、
　=IF(M8<>"",G8-F8-E8-1/3,"")
この「G8-F8-E8」が ”8:00" よりも小さい場合は ”8:00" に固定しちゃえばいいって事。
ならば、MAX関数の出番です。
「G8-F8-E8」が"8:00"よりも大きいなら、その数値、
「G8-F8-E8」が"8:00"よりも小さいなら、"8:00"、
をそれぞれ引き算させてやれば良い。
すると
　=IF(M8<>"",　MAX("8:00",G8-F8-E8) -1/3　,"")
ってわけです。

もう時間の表記を統一するという事で
　=IF(M8<>"",MAX("8:00",G8-F8-E8)-"8:00","")
で良いと思う。
この方が分かりやすい。
（分かりやすいという事は間違えにくいという事です）

・・・

根本的な原因を見つけることができるかどうかで、問題を解決できるかが決まります。

この回答へのお礼

銀鱗様
お礼が遅くなり大変申し訳ございませんでした。
とても解りやすくご説明ありがとうございます。
結論だけではなく、そこまでの過程のご説明+プラスMAX関数が追加される理由等とても解りやすくて勉強になりました。

一つ一つ助けられながらも関数を覚えて行きます。
頭が固いので、納得行かないと、なぜ？？ってのが中々抜けませんが、
銀鱗様のようなご説明がとても助かります。
ありがとうございました。

お礼日時：2021/07/05 10:12