Excel条件付書式で時間計算の色分け

質問

こんにちは。

Excelでの条件付書式の設定で時間計算の色分けについて教えてほしいです。

業務報告書で

A1　　　　　B1　　　　　　　　　　C1　　　　　　　　　E1
案件名　　作業内容　　　　　終了目標時間　　実際にかかった時間
ああ　　　　いい　　　　　　　　0：30　　　　　　　　0：50

という項目を業務案件ごとに入力しています。

上の内容は、「目標時間を30分に設定したけど、実際は50分かかった」
という記録になります。

目標時間より、実際は20分オーバーしたことになるのですが、
15分以上オーバーした時は、案件ごとに区切ったセル（上の内容だと1行）
全体の色を変えたいと思っています。

1行全体を選択して
「数式を使用して、書式設定するセルを決定」を選び
「＝C1-E1＜0：15」と入力してみましたが、
エラーになってしまいました。

関数が間違っているのは、十分わかっているのですが、
どのように設定すれば、色が変わるように設定できるのでしょうか？

時間計算がよくわからず、初心者です。
ご指導よろしくお願いします。

回答No.11

keithin

セルの書式が「文字列」になっていても、回答済みの方法でやれば問題なく出来ます。
あなたの情報提供がウソだったので、実地に応用できなかっただけですね。

まず練習で次の通りに操作します：
Ａ１：Ｆ８を選択する
条件付き書式▼から新しいルールを開始して
数式を使用して…を選び
=$A$8-$A$7-"0:15">=0
と記入して書式ボタンで書式を取り付ける

本番で次の通りに操作します：
Ａ１：ＡＤ８を選択する
条件付き書式▼から新しいルールを開始して
数式を使用して…を選び
=OFFSET(A$8,0,-MOD(COLUMN()-1,6))-OFFSET(A$7,0,-MOD(COLUMN()-1,6))-"0:15">=0
と記入して書式ボタンで書式を取り付ける

今度はちゃんとやってくださいね。

回答No.10

toshi_2010

>数式を
>=$A$8>=A$7+"0:15"
>と入力し、

惜しい・・=A$8>=A$7+"0:15"　だと思います。

しかし、これだけだと出来ません。

表計算はそもそも行、単位に意味を持たせて使うものです。

絶対参照、相対参照の元の位置がＢ～Ｆ列に適用されないためです。

=$A$1:$A$8、=$Ｂ$1:$Ｂ$8、=$C$1:$Ｃ$8、=$D$1:$D$8、=$E$1:$E$8、=$F$1:$F$8
の範囲それぞれに=A$8>=A$7+"0:15"を設定してください。

次のブロックも同様に=$G$1:$G$8、・・・、=$L$1:$L$8

絶対参照、相対参照を理解するために

Ａ９セルに　=A$8>=A$7+"0:15"　を入力して

Ｇ９セルにＡ９セルをコピーしてください。

=Ｇ$8>=Ｇ$7+"0:15"　となります。・・・やりたいことと同じ結果です

次にB９セルにＡ９セルをコピーしてください。

=B$8>=B$7+"0:15"　となります。・・・やりたいことと違う結果です

回答No.9

kagakusuki

>ですが、やはりセルをさわると色が変わる

とは一体どのような現象の事を指しておられるのでしょうか？
　若しかしますと、マウスの左ボタンを押すたびに、色が付いているセルが不規則に変化するという事なのでしょうか？
　又、「パラパラした感じ」とは一体どのような状態を指しておられるのでしょうか？

>実際の位置関係はこんな感じです。

>A1　　　　　　　　B1　　　　　　　…　　　　　F1
>案件名　　　　　作業内容　　　　　　
>ああ　　　　　　　いい　　　　　　　

>A7
>実際にかかった時間
>0：30

>A8
>終了目標時間
>0：50

>1案件で
>列だとA~Fまで情報があり
>行だと1～8まで情報があります

範囲を
=$A$1:$AD$8

>と選択しています。

>これを1ブロックとして
>月～土の5列

ということは、A1:F8の範囲内において、「実際にかかった時間」が入力されているのはA7セル只１箇所のみであり、同じく「終了目標時間」が入力されているのはA8セル只１箇所のみであり、１行目から8行目の範囲に関しては、B列、C列、D列、E列、F列の何れの列も、全てA列の7行目と８行目の値に応じて色を変えたいという事なのでしょうか？
　それとも、A列の7行目と８行目の値に応じて色を変えたい範囲は、A～F列の1～８行目ではなく、A～AD列の1～８行目なのでしょうか？
　又、

>1日10案件分あります。

とありますが、次の案件において、、「実際にかかった時間」が入力されているセルと、「終了目標時間」が入力されているセルはどのセルになるのかが、記されておりませんので、各々のセル番号を御教え願います。

回答No.8

toshi_2010

ANo.3、ANo.4　で回答したものです。

ANo.7で回答された方に補足させていただきます。

今回の条件書式で、重要なのは
書式を設定する範囲のどこのセルでも
C列とE列を参照した条件で書式を反映させる

数式に入力する＄に意味があります。（絶対参照、相対参照）
（数式バーで数式のセル参照にマウスで選択後、F4を押してみてください。
　$の位置が押すたびに変わるはずです。）

$列行（$C2)・・・列は絶対参照、行は相対参照
列$行（C$2)・・・列は相対参照、行は絶対参照
$列$行（$C$2)・・・列は絶対参照、行は絶対参照

Excelのヘルプで『セルとセル範囲の参照について』を一読していただき
もう一度、ANo.7で回答された方の回答を参考にしてください。

お礼

大変丁寧なご説明ありがとうございます。
Excelのヘルプで『セルとセル範囲の参照について』も拝見させていただきました。

まずは、
セルの書式設定が「文字列」になっていました。

そして、質問を重ねていくうちに、
位置関係の指示が非常に重要だということを知り、

どんな計算式を作ったら良いのかわからず、
簡潔に説明してしまっておりました。すみません。

実際の位置関係はこんな感じです。

A1　　　　　　　　B1　　　　　　　…　　　　　F1
案件名　　　　　作業内容　　　　　　
ああ　　　　　　　いい　　　　　　　

A7
実際にかかった時間
0：30

A8
終了目標時間
0：50

1案件で
列だとA~Fまで情報があり
行だと1～8まで情報があります

この範囲をすべてを時間の計算で変えようを考えており、
現在

数式を
=$A$8>=A$7+"0:15"
と入力し、

範囲を
=$A$1:$AD$8

と選択しています。

これを1ブロックとして
月～土の5列
1日10案件分あります。
最終的には全体に設定したいと考えております。

自身がわからないことだらけで質問自体が
しっかり伝えることができませんでした。
申し訳ございません。

最初から皆さんの力を頼って
全てお知らせすべきだったと反省しております。

こちらの情報で何か原因がお分かりになるでしょうか？

回答No.7

kagakusuki

>=$E1>=$C1+"0:15"

>と設定してみたのですが、空欄以外はどの数字を入力しても（例えば「0：10」と入力しても色が変わってしまいました。

　若しかしますと、E1セルに時刻を入力される際に、

0:10

の様な形式ではなく、

="010"

や

"0:10"

'0:10

「0:10」

と言った形式で入力されておられるのではないでしょうか？
　或いは、E1セルの書式設定の表示形式が文字列になってはいませんでしょうか？
　その様な形式で入力された場合、数値や時刻データではなく、文字列データとして扱われてしまい、文字列は数値や日付、時刻よりも大きな値として扱われてしまいますので、

"0:10"

と入力しただけでも、

23:59:59

よりも遙かに大きな値として扱われてしまいます。
　そのために、条件付き書式が適用されてしまうのではないかと思います。
　ですから、セルの書式設定の表示形式を、[標準]か或いは[時刻]等とされた上で、

0:10

の形式で入力される様に願います。

>=$E$1>=$C$1+"0:15"

>実際は上記のように入力しています。

　それでは１行目以外の行に時刻を入力した場合には、色は変わりません。

=$E1>=$C1+"0:15"

と入力して下さい。
　色が時間を入力したセルしか変わらず（E1)他のセルを触るとパラパラと色が変わっていきます。
　回答と異なる数式を入力した事が原因だと思われます。
　正しい数式を入力して下さい。

お礼

大変丁寧なご説明ありがとうございます。

セルの書式設定が「文字列」になっていました。

ですが、やはりセルをさわると色が変わるような
パラパラした感じです。

どんな計算式を作ったら良いのかわからず、
簡潔に説明してしまったので、位置関係が問題だということに
気づけませんでした。すみません。

実際の位置関係はこんな感じです。

A1　　　　　　　　B1　　　　　　　…　　　　　F1
案件名　　　　　作業内容　　　　　　
ああ　　　　　　　いい　　　　　　　

A7
実際にかかった時間
0：30

A8
終了目標時間
0：50

1案件で
列だとA~Fまで情報があり
行だと1～8まで情報があります

この範囲をすべてを時間の計算で変えようを考えており、
現在

数式を
=$A$8>=A$7+"0:15"
と入力し、

範囲を
=$A$1:$AD$8

と選択しています。

これを1ブロックとして
月～土の5列
1日10案件分あります。
最終的には全体に設定したいと考えております。

自身がわからないことだらけで質問自体が
しっかり伝えることができませんでした。
申し訳ございません。

最初から皆さんの力を頼って
全てお知らせすべきだったと反省しております。

こちらの情報で何か原因がお分かりになるでしょうか？

回答No.6

KURUMITO

＝C1-E1＜0：15
と入力されていますね。
この式ではいくつかの誤りがあります。
○一つは時間の計算においては結果がマイナスの時間というのは計算することができませんのでエラーになります。
Ｅ1の時間がＣ1の時間よりも大きくなるケースを計算していますのでエラーになりますね。
○時間を計算式の中で使う場合には"0:15"のようにダブルコーテーションで囲むことが必要です。
セルに直接0:15と入力することはできますが=0:15＋0:15のように入力することはできません。="0:15"+"0:15"のようにすることが必要です。この場合にセルには0.020833のような数値が表示されますが、これはシリアル値といって日単位の数値になっています。この数値に24＊60を掛けることで30になります。このセルの表示を0:30と表示させるためにはセルを右クリックして「セルの書式設定」から「表示形式」に時刻から表示形式を選ぶことでできますね。
○式では<0:15のように使っていますがこれを>"0:15"のように使っても有効に働きません。元来"0:15"というのは文字列であって数値ではないのです。しかし="0:15"+"0:15"のような場合にはパソコンが自動的に時間の計算であると判断して計算しているのです。>"0:15"のように単独で使用する場合には文字列である時間表示を数値として使用できるように1などの数値を掛ける操作を施します。>"0:15"*1のように使用します。
次に条件付き書式の設定についてです。
例えばＡ列からＥ列までで該当する行に色を付ける場合ですが、初めにＡ列からＥ列までを範囲として選択します。
するとＡ1セルがアクティブなセルとなっています。条件付き書式の設定で入力する式はこのアクティブなセル、すなわちＡ1セルに式を入力すると考えてください。その式が範囲を指定したすべてのセルにコピーされ貼り付けられると考えてください。
お示しの式では　＝C1-E1　のようにセル番地が入力されていますね。この式がＡ1セルに入力されているのですからＣ1セルでは=E1-G1の式になっていますし、Ｅ1セルでは=G1-I1の式に変わっています。つまりはセルの位置によって条件付き書式で設定した式が変わってしまっているのです。これでは意味がありませんね。
列が変わって設定した式の列番号が変わらずにして、行が変わった場合には行番号だけが変わるように式そのものを工夫することが必要です。そのためには列番号については$を付けて変わらないようにすることです。例えば　=$C1-$G1 とすることで列番号が変わることなく行番号だけが変化します。
以上述べてきましたが、最終的にお示しのケースで適当な式は例えば次のようになりますね。

=$E1>=$C1+"0:15"

この式の意味はＣ列の時間よりもＥ列の時間が15分以上になったら条件付き書式を適用することになりますね。
エクセルで時間のデータを入力する際の主な注意点や条件付き書式を設定する際は範囲として選択した中でアクティブな状態になっているセルを対象として式を入力するのであることいったことを述べました。参考になりましたら幸いです。

お礼

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

=$E1>=$C1+"0:15"

と設定してみたのですが、
空欄以外はどの数字を入力しても（例えば「0：10」と入力しても
色が変わってしまいました。

何か間違えているのでしょうか？

=$E$1>=$C$1+"0:15"

実際は上記のように入力しています。

回答No.5

kagakusuki

=$E1>=$C1+"0:15"

でも出来ます。

　処で、ANo.4様が

>ちなみに　ANo.3、ANo.2さん　は１５分を含みますが、ANo.1さんの答えは１５分は含まれません。

と仰っておられますが、ANo.2様の方法の場合は、丁度15分だけオーバーした場合には、色は変わりません。

補足

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

=$E1>=$C1+"0:15"

と設定してみたのですが、
空欄以外はどの数字を入力しても（例えば「0：10」と入力しても
色が変わってしまいました。

何か間違えているのでしょうか？

=$E$1>=$C$1+"0:15"

実際は上記のように入力しています。

回答No.4

toshi_2010

参考までに

たったこれだけでも色々な方法で実現できます。

ちなみに　ANo.3、ANo.2さん　は１５分を含みますが、ANo.1さんの答えは１５分は含まれません。

回答No.3

toshi_2010

>1行全体を選択して
>「数式を使用して、書式設定するセルを決定」を選び
EXCEL2007以上と思いますので、EXCEL2007だとして答えさせていただきます。

A列からE列で書式設定したい行数が10あるとします。
まずA2からE11セルを選択し
「数式を使用して、書式設定するセルを決定」を選び
数式に＝＄E２-＄C2＞＝TIME(0,15,0)

補足

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

=$E$1-$C$1>=TIME(0,15,0)

と設定してみました。見事色が変わりました！

ですが、色が時間を入力したセルしか変わらず（E1)

他のセルを触るとパラパラと色が変わっていきます。

何かのバグ？でしょうか、それともスペックの問題ですか

もし、お分かりになるようでしたら、教えて頂けるとありがたいです。

回答No.2

keithin

「＝C1-E1＜0：15」と入力してみましたが、

まずＣ１－Ｅ１は間違いですね。
次に「＜０：１５」とか、ほかにも「日付と比較する」とかで、よく間違いやすいです。

こんな風にします：
Ａ列からＥ列まで列選択して
条件付き書式▼から新しいルールを開始して
数式を使用して…を選び
=$E1-$C1-"0:15">0
と記入してみます。

お礼

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

色々な方から関数の候補をいただいており、

一つ一つ検証しています。

答えが見つかりそうです。

大変助かりました。ありがとうござます。