Excelのエラー表示に対し正規の表示を求む

質問

ご存知の方、ご指導の程よろしくお願い致します。

使用機種等は、Windows 8.1　Excel 2013 を使用しています。

作成中のエクセルの表で説明を求めるのが筋ですが、OKWaveで表示する方法が判らず、理解し難いでしょうがよろしくお願い致します。

従業員の定時労働時間・時間外時間・休日労働時間・労働日数をエクセルの表からリンクさせ、数値を求める表を作成したのですが、日によっては(8)の時間外時間にエラー表示「######」が表示されました。

各列は(1)は日、(2)は曜、(3)は始業時刻、(4)は終業時刻、(5)は最低時間、(6)は休憩時間、(7)は実働時間、(8)は時間外時間 を示します。
(1)　(2)　(3)　　(4)　　(5)　　(6)　　(7)　　(8)
1　月　7:00　11:00　4:00　0:00　4:00　######
2　火　5:30　15:30　8:00　1:00　10:00　1:00
3　水　5:10　15:00　8:00　1:00　9:50　0:50

(8)の時間外時間が、0:00のように表示される方法をご指導下さい。
0:00等に表示されれば、リンクされて必要な数値が求められるのですが。

参考までに、
インターネットで「Excelエラーの原因と対策まとめ」から調べたのですが、
原因は　日付や時刻がマイナスとなる場合
対策は　「1904年から計算する」という設定にすると、日付や時刻もマイナス表記が可能になります。
操作方法は　「ファイル」タブ→「オプション」→「詳細設定」→
「1904年から計算する」チェック→「OK」の流れで設定可能です。
設定したファイル内で日付・時刻がすでに入力済みの場合、日付・時刻の値が変わってしまいますので、都度正しい内容に修正してあげる必要が別途あります。

回答No.7

kagakusuki

回答No.6です。

>ただ、現在の表はセルの二つを一つに連結しており、上手く表示されません。

　「セルの二つを一つに連結」とは、「？

【参考URL 】
　エクセル2013の使い方: 複数のセルを結合する - Excel2013
　　http://excel-2013.blogspot.jp/2014/03/excel2013_22.html

　「セルの二つ」というだけでは、「縦に並んだセルの二つ」なのか「横に並んだセルの二つ」なのか判りませんが、もし「横に並んだセルの二つを一つに結合している」という場合であれば、回答No.6の関数における$C:$C、$D:$D、$E:$E、$G:$Gの各列を指定している箇所を、「一つに結合されている横に並んだ複数のセル」の内の左端の1列のみを指定する様に修正して下さい。

　それとも例えば

　　　　A列　　B列　　C列　　D列　　 E列　　　F列
1行目　　 1　　 7:00
　　　　　　　　　　　 4:00　　0:00　　4:00　　######
2行目　　月　　11:00

3行目　　 2　　 5:30
　　　　　　　　　　　 8:00　　1:00　 10:00　 　1:00
4行目　　火　　15:30

5行目　　 3　　 5:10
　　　　　　　　　　　 8:00　　1:00　　9:50　 　0:50
6行目　　水　　15:00

などの様に一部のデータが縦に2つに並んでいるセルを1組として入力されているという事なのでしょうか？
　もしそうだとしましても、やり方次第でどうにでもなりますので、もし縦に2つの場合には、「始業時刻」、「終業時刻」、「最低時間」、「休憩時間」、「実働時間」、「時間外時間」の各セルがどのセルなのかという事を、(1)～(8)などという不明瞭な表現ではなく、A1とかG2等の様な実際のセル番号を使用して御説明願います。

補足

お礼のコメントを見て頂き驚くとともに、本当にありがとうございます。
なかなか説明が上手く出来ずに申し訳ありません。
A1とかG2等の様な実際のセル番号を使用して説明をし、最初から再度実際のものをOKWaveにて質問致します。
当然、前回の文書が若干変わりますが、お許し下さい。
送信まで若干時間がかかりますが、よろしくお願い致します。

回答No.6

kagakusuki

(1)～(8)が何列に入力されているのか不明なままでは回答のしようが御座いませんので、取り敢えず仮の話として、(1)～(8)がA列～H列に入力されているものとして回答させて頂きます。

　まず、G列の実働時間を表示するセルの関数を次の様なものにして下さい。(下記の関数の場合、入力するセルの行番号が幾つであるのかには関係なく、どの行のセルに入力する場合でも同じ関数です)

=IF(COUNT(INDEX($C:$C,ROW()),INDEX($D:$D,ROW()))=2,IF(AND(INDEX($C:$C,ROW())>=0,INDEX($C:$C,ROW())<1,INDEX($D:$D,ROW())>=0,INDEX($D:$D,ROW())<2),IFERROR(TEXT(INDEX($D:$D,ROW())-SUM(INDEX($C:$C,ROW()),INDEX($F:$F,ROW()))+1.000001,"h:m")+0,""),""),"")

　次に、E列の最低時間を表示するセルの関数を次の様なものにして下さい。

=IF(ISNUMBER(INDEX($G:$G,ROW())),MIN(INDEX($G:$G,ROW()),"8:0"+0),"")

　次に、H列の時間外時間を表示するセルの関数を次の様なものにして下さい。

=IF(ISNUMBER(INDEX($E:$E,ROW())),TEXT(INDEX($G:$G,ROW())-INDEX($E:$E,ROW())+0.000001,"h:m")+0,"")

　尚、職場によっては、出勤時刻の10分未満の端数は切り上げ、退勤時刻の10分未満の端数は切り捨てて勤務時間を計算するなどといった類の方法を採用している所もありますが、質問者様の職場ではその辺りの事がどの様になっているのかという事に関して特に説明が無かったため、上記の3つの関数では時刻の切り上げや切捨て等は行っておりません。

お礼

昨夜はお忙しいのに、しかも理解し難い文面を見て長い関数を組んで頂き、ご指導を頂き本当にありがとうございました。
今朝、昨夜の関数をコピーしてExcelの表に当てはめテストをしてみました。
ただ、現在の表はセルの二つを一つに連結しており、上手く表示されません。
従って、練習用として一つづつの正規のセルにしたものによりテストとして時間をかけて実施してみようと思います。
私の方は簡単なお礼で申し訳ありません。
貴重な時間を費やして頂き、本当にありがとうございました。
日本列島全般で寒波が来ておりますので、風邪などひかれないようにご注意をして下さい。

回答No.5

bunjii

>日によっては(8)の時間外時間にエラー表示「######」が表示されました。
「時間外時間」を算出している数式を提示して頂かないと原因が分かりません。

>(8)の時間外時間が、0:00のように表示される方法をご指導下さい。
前述のようにエラーになる原因を排除しないと解決になりません。

>対策は　「1904年から計算する」という設定にすると、日付や時刻もマイナス表記が可能になります。
勤務時間を算出するのにマイナスの値は不合理です。
「1904年から計算する」でマイナスの時間を算出すれば解決するのでしょうか？

>設定したファイル内で日付・時刻がすでに入力済みの場合、日付・時刻の値が変わってしまいますので、都度正しい内容に修正してあげる必要が別途あります。
何のことか分かるように説明してください。

提示された模擬データからの推測で次のように計算されていると考えます。
最低時間=MN(実働時間,8:00)
実働時間=集票時刻-始業時刻
時間外時間=実働時間-最低時間-休憩時間
但し、始業時刻、終業時刻、休憩時間は手入力とし、日付を跨いだ（24:00を超える）勤務はないものとする。
上記の条件では元データ（手入力）の値に誤りが無ければエラーにならないでしょう。

回答No.4

mimazoku_2

その列のセル幅を広げてみてください。
単純に表示幅が足らないだけだと思います。

回答No.3

m_and_dmp

数式は、
(5)=(4)-(3)
(8)=(7)-(6)-(5)
で、よろしいですか？
Excel 2003 で確認しましたが、(8) のところには、0:00 と表示されました。
(8) の表示形式を「標準」にして、どのような値が表示されるか見てください。この値がマイナスになると、表示形式が「hh:mm」の場合「########################」になります。
マイナスになる原因を突き止めてください。
(3), (4), (6) の数値がて入力ではなく、別のシートの計算結果を引っ張ってきている場合は、4:00 のように区切の良い数値に見えても、3:59:59.99999.....が丸められた値になっていることがあります。

お礼

ありがとうございました。
解決に向けてのご指導をお願い致します。

回答No.2

Prome_Lin

私が普段エクセルを使っていて、表示が「#######」となるときは、単に列の幅が狭くて、値を表示しきれないときなのですが、下の値が問題なく表示されているようなので、考えにくいですね？

ただ、念のため、少し（もしくは、思い切ってたくさん）「(8)」の列の幅を広げてみてください。

変な（想定外の）値が入っていて表示しきれていないのかも知れません。

回答No.1

neKo_quatre

> (8)の時間外時間が、0:00のように表示される方法をご指導下さい。

(8)の式、参照してるセルがあるならそちらの式も分からなきゃ無理です。
単に、エラーだったら0:00にするのなら、
=IF(ISERR(【(8)の式】), 0, 【(8)の式】))
とか。