EXCEL 時刻引き算で結果が###になってしまう

質問

お世話になります。

EXCELの時間の計算方法について質問させていただきます。

下記のように1日の従業員の作業状況を求めるEXCELを作成しています。

A1・・作業時間 (表示形式"時刻") 例 3:00:00(3時間)
B1・・空き時間 (表示形式"時刻") 例 4:00:00(4時間)

C1=A1-B1でその日の従業員の空時間の多い、少ないを求めています。

C1 (表示形式"時刻") の結果がプラスの場合は良いのですがマイナスの場合は#####となってしまいます。これを防ぐ良い方法はありますでしょうか。

どなたかお知恵をお借りできませんでしょうか。
よろしくお願い致します。

環境 Windows XP SP3 EXCEL2003

回答No.4

keithin · ベストアンサー

こんばんは。

エクセルは負の時間数を問題なく扱えますが、ふつーの設定では表示ができません。
ツールメニューのオプションの計算方法タブで「1904年」のチェックを入れておくと、負の時間も＃＃＃にならずに表示されるようにできます。

ただし、既に生データで「日付」を記入してあるエクセルブックでこの設定を触ると、「記入済みの日付が」４年と１日ずれた日付に変わってしまうので注意が必要です。先にチェックを入れたブックに、あとから日付時刻を記入して使う分には支障ありません。
同様にチェックを入れたブックと入れてない（通常設定の）ブックを混在して使っていると、間違いの元なのでそちらも注意してください。

お礼

keituinさん、いつも素早いご連絡をいただきまして誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

ご教授いただきました方法で解決できました！
本当にありがとうございます!!

日付のズレに関しましても他のExcelから参照するようなこともなさそうなので、
この方法で素早く解決できました。

本当にいつもありがとうございますm(__)m

回答No.10

basic_gg

C列をコピーしてC列に挿入すると、C列が#REF!に、D列が正常値になるとおもいます。
C1=A1-B1
D1=IF(COUNT(A1:B1)<>2,"",IF(C1<0,TEXT(ABS(C1),"-"&"h：mm：ｓｓ"),TEXT(C1,"h:mm：ｓｓ")))
にして、下にオートフィルします。
もちろんC列には"####"が表示されますが、D列は希望通りに表示されます。
C10=SUM(C1:C9)
D10=IF(C10="","",IF(C10<0,TEXT(ABS(C10),"-"&"h：mm：ｓｓ"),TEXT(C10,"h:mm：ｓｓ")))
にすると、合計を表示します。
この状態でC列を"表示しない"に設定します。
C列非表示がいやなら、作業列を離れた列にすればいい。
計算できるためには数値である必要があります。
つまり、時刻の書式設定で－を表さなければならない。
やり方を回答してくれた人がいます。
その人もそうですが、私も使いません。

お礼

basic_ggnさん、ご連絡誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。
この度は細かなご説明を添えていただきまして本当にありがとうございます。
そもそもの[考え方]についても勉強させていただきました。

ありがとうございました。

回答No.9

chie65536（@chie65535）

セルの表示形式を時刻（h:mm:ss）にしている場合、

=IF(A1<B1,TEXT(B1-A1,"-h:mm:ss"),A1-B1)

と言う式を書く。

このままでは、A1の方が大きい場合は、そのまま時刻で表示されるので、右揃えで表示されるが、A1の方が小さい場合は、マイナス記号が付いて文字列として表示されるので、左揃えで表示される。

値によって右揃えになったり左揃えになったりしては困るので、セルの表示を「右揃え」にする。

セルの表示形式を「h:mm」などの秒無しに変えた場合は、式の中の「h:mm:ss」の部分を、表示形式に合わせて変更すること。

お礼

chie65535さん、ご連絡誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。

表示形式を時刻（h:mm:ss）にする際のテクニックとして大変勉強になりました。
ありがとうございました。

回答No.8

MarcoRossiItaly

セルの書式が時刻関係のものに設定されているとき、マイナスの時間は、皆さんのおっしゃっているとおり、表示することができません。（その書式では表示ができないだけで、実は正しい値を計算できてはいるのですが）

対処法としては、(1)時刻関係でないセルの書式に切り替える、(2)マイナスを回避する数式を書く、(3)マイナスの符号を付加した文字列を力ずくで合成する、といった工夫をするということになろうかと思います。

(1)
例えばセルの書式として「数値」を選び、「小数点以下の桁数」を適当な値に設定。次式により C1 セルには「-0.50000」（時間）が得られます。

A1　3:30
B1　4:00
C1　=24*(a1-b1)

以下では、例えば C1 セルの書式として「ユーザー定義」の「h:mm:ss」を設定するとしましょう。

(2)-1
マイナスになる場合は「0:00:00」と表示

C1　=max(,a1-b1)

(2)-2
マイナスになる場合は「-」という 1 文字を表示

C1　=if(a1<b1,"-",a1-b1)

以下では、C1 セルの書式は何でも構いません。

(3)
マイナスになる場合は時刻を表す文字列の先頭に力ずくで「-」の符号を付加

C1　=if(a1<b1,"-",)&text(abs(a1-b1),"h:mm:ss")

お礼

MarcoRossiltalyさん、ご連絡誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。

今回は様々な方法(考え方)をご教授いただきまして本当にありがとうございました!今後Excelを使用していくうえでの知識として頭に叩き込んでおきたいと思います！
ありがとうございました。

回答No.7

kagakusuki

ANo.5様の御回答に捕捉させて頂きます。

>休息時間＞作業時間の場合は、どちらかの数値が間違っている！

　「休息時間＞作業時間」という事でしたら確かに間違っていますが、本件の質問内容は、「休息時間＞作業時間」ではなく、「空き時間＞作業時間」の場合に関する事ですから、間違ってはいないと思います。（休息時間と空き時間は異なります）

回答No.6

kagakusuki

>しかしこれですと表示は####になりませんが文字列なので集計ができず不効率で困っています。

> =IF(A1-B1<0,TEXT(ABS(A1-B1),"-"&"h：mm：ｓｓ"),TEXT(A1-B1,"h:mm：ｓｓ"))

　マイナスの時間も集計しなければならないという事ですね。
　それでしたら、適当な列を作業列として使用して、その作業列の値を集計すれば良いと思います。
　作業列の結果が表示されているのが見えてしまいますと見苦しくなりますので、適当な未使用のシート上の列を作業列として使用します。

　今仮に、A列とB列に元データを入力するシートがSheet1であり、Sheet2のA列を作業列として使用するものとします。
　まず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(AND(COUNT(Sheet1!$A1,Sheet1!$B1)=2,Sheet1!$A1>=0,Sheet1!$B1>=0),Sheet1!$A1-Sheet1!$B1,"")

或いは

=IF(AND(COUNT(INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$B:$B,ROW()))=2,INDEX(Sheet1!$A:$A,ROW())>=0,INDEX(Sheet1!$B:$B,ROW())>=0),INDEX(Sheet1!$A:$A,ROW())-INDEX(Sheet1!$B:$B,ROW()),"")

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。
　これで、Sheet1のA列とB列の両方に時刻が入力されている場合には、「[作業時間]-[空き時間]」に相当する数値が表示され、それ以外の場合には、Sheet2のA列には何も表示されません。

　次に、Sheet1のC1セルに次の関数を入力して下さい。

=IF(ISNUMBER(Sheet2!$A1),IF(Sheet2!$A1<0,"-","")&TEXT(ABS(Sheet2!$A1),"[hh]:mm:ss"),"")

或いは

=IF(ISNUMBER(INDEX(Sheet2!$A:$A,ROW())),IF(INDEX(Sheet2!$A:$A,ROW())<0,"-","")&TEXT(ABS(INDEX(Sheet2!$A:$A,ROW())),"[hh]:mm:ss"),"")

　次に、Sheet1のC1セルをコピーして、Sheet1のC2以下に貼り付けて下さい。

　そして、Sheet1のC列の値を集計するのではなく、Sheet2のA列の値を集計するようにしますと、マイナスの値も集計に加える事が出来ます。
　尚、Sheet2のA列の書式設定の表示形式は、[標準]のままとした方が、表示が####とならずに済むため見やすくなりますが、表示形式を[時刻]として、マイナスの数値が####という表示となっているままであっても、表示が見えないだけで、そのまま集計する事は可能です。

お礼

kagakusukiさん、ご連絡誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。

今回は細かなロジックまでご教授いただきまして本当にありがとうございました!

すごいロジックですね！

ただ、今の私にはすぐに理解するには少し難しくてまだ試しておりませんが(申し訳ございません)、一度家に帰ってからじっくり考えて意味を理解したうえで試させていただきたいと思います！
今後の知識として頭に叩き込んでおきたいと思います。

この度は本当にありがとうございました!

回答No.5

K Kazz（@JazzCorp）

そのケース、時間数と時間数、２つの引き算は、純粋に算術的に考えるしかない。
それぞれが論理的に妥当な数値か、確認したうえで計算するしかない。
休息時間＞作業時間の場合は、どちらかの数値が間違っている！（＝の場合も同じようなもの？）
どちらが間違っているのかは断定できないので両方を確認することになる。

ここからは余談、
上に出てくる作業時間の計算でも、日付またがりの勤務時間帯があると、その計算は厄介？そうに思えるが、しかし、これは意外と簡単！、
Ａ１：出社時刻
Ｂ１：退社時刻
Ｃ１：休息時間」
２４時間表記で値の代償考えずに、論理的に組み立てた引き算に、「１」すなわち、２４Ｈｒ（＝１日）を補正する、これだけ、、、ホントかいな？？？、、、

作業時間＝（（Ｂ１－Ａ１）－Ｃ１）＋１

お礼

JazzCorpさん、ご連絡誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

今回は他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。

数式もそうですが[考え方]について勉強させていただきました。
奥が深いですね！継続的に勉強に励んでいきたいと思います！

この度は本当にありがとうございました!

回答No.3

kagakusuki

Excelではマイナスの時間やマイナスの日付というものは扱えませんので、計算結果がマイナスの場合には#の繰り返しが表示されるだけとなります。
　ですから、計算結果がマイナスの場合には、時間が表示されない様に関数を工夫しなくてはなりません。

=IF(AND(ISNUMBER(TEXT($A1,"h:m:s")+0),ISNUMBER(TEXT($B1,"h:m:s")+0),$A1>$B1),$A1-$B1,"")

回答No.2

shintaro-2

エクセルの時間表示は、
あくまでも年月の一部としての時間なので”負”という概念がありません。

皆さんお悩みのようで、okwaveの過去の質問にもあります。

例えば
http://okwave.jp/qa/q7790142.html
http://okwave.jp/qa/q7067806.html

webで検索してみてください。

お礼

shintaro-2pさん、ご連絡誠にありがとうございます。
またお返事が遅くなりまして申し訳ございません。

今回は他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。

参考URLをご提示いただきまして誠にありがとうございます。
過去にも同じように頭を悩ませていた方がたくさんいらっしゃったのですね・・
自分での調査も足りなかった気がします(反省)・・

この度は本当にありがとうございました!

回答No.1

big_egg

単純にセルの幅が足りないのではないかと。
一文字分程セルの幅を広げてみてはどうでしょうか。

お礼

bi_eggさん、お返事が遅くなりまして申し訳ございません。

今回は他の方からご教授いただきました[2004年9月から計算する]方法で解決いたしました。

この度はお早いご連絡をいただきましたこと大変感謝いたします。
ありがとうございました！

補足

big_eggさん、早速のご連絡ありがとうございます。

セルの幅を10ｃｍ以上広げましたが同じでした・・。

ちなみにC1には####対策のために以下のような数式を埋め込んで文字列にして場をしのでいいます。
しかしこれですと表示は####になりませんが文字列なので集計ができず不効率で困っています。

=IF(A1-B1<0,TEXT(ABS(A1-B1),"-"&"h：mm：ｓｓ"),TEXT(A1-B1,"h:mm：ｓｓ"))