EXCELで、入力した時間に合わせてセルに色をつけるにはどのようにした

質問

EXCELで、入力した時間に合わせてセルに色をつけるにはどのようにしたら良いでしょうか？

例えばA1セルに開始時間（9時）、A2セルに終了時間（9時30分）を入力します。
B1セルが9時から9時15分、B2セルが9時15分から9時半、B3セルが9時半から9時45分・・・となっており、A1、A2に入力した時間に合わせて自動的に対応セルに色をつけるようにしたいです（上記の場合、B1とB2に色がつく）。
その際、既にB行にデータがある場合、C行に色をつけるようにしたいと思っています。

使える関数のヒントでも良いので教えていただけると助かります。

よろしくお願いします。

回答No.8

keithin · ベストアンサー

そうですね…　出来ないことと出来そうなことについて，幾つかお話しして見たいと思います。

今はＡ列の値を元に色を付けたり消したりしているので，今のままでは，Ａ列で指定した範囲から「外れ」たら消えてしまうのは，どうしようもありません。
また既にご覧になったように，「条件付き書式の色が発色している」という事実を「取り出す」方法や残しておく方法は，（マクロを使っても）ありません。

また，ご質問でヤリタイことのご説明の不足としては
＞その情報を消さずに（残したまま）、B列の色が既に変わっているのを判定して、C列・・・以降の色が変わるように

この言い方では，「１度色が付いたＢ列は，たとえば間違いや，意図した変更によってＡ列を変えても色が残ってしまい，ずっと消せない。」という動作になります。

で，出来る事の範囲で工夫してみると，例えば。
作成例：
　Ａ列に時刻を入れるとＢ列が発色する
　Ｂ列の発色しているセルに何か固定する記号(スペースでもＯＫだが目で見て判る文字の方がベター)を記入する事で，そのＢの色は付きっぱなしになり追加でＣ列も発色する
　Ａ列を変更すると，Ｂ列で記入していない範囲が新しい条件で発色し，記入したＢ列の色は残り，Ｂ列に記入した行のＣ列も反応を始める
　Ｂ列の記入内容を消去すると，記入していないＢ列の反応に戻る

B1の条件付き書式の数式
=(B1<>"")+($A$1<$A$2)*($A$1<>"")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1)
C1の条件付き書式の数式
=($A$1<$A$2)*($A$1<>"")*(B1<>"")*(C1="")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1)

のような事は出来ます。

お礼

keithinさん、何度もありがとうございます。

一つの形にこだわらず、柔軟な運用も必要ですね。
別方法も考えてみようと思います。

素人の質問に誠実に答えてくださってありがとうございました。

また何かあればお願いいたします。

回答No.7

tom04

No．4・6です！
何度もごめんなさいね！
別案を持ってきました。

B1セルには必ず9：00が入るものとしています。

C列全てを範囲指定し、条件付書式の数式欄に
=COUNTBLANK(C$1:C1)<=INT(($B$2-$B$1)/"0:15")
として好みの色を選択してみてはどうでしょうか？

尚、15分に満たない場合はセルに色が付きません。

以上、参考になれば幸いです。
ほんとぉ～～！っに！何度も失礼しました。m(__)m

回答No.6

tom04

No.4です！
たびたびお邪魔します。

色を付けたいセルには何もデータを入れないということですね？

それでは無理矢理の方法になりますが、↓の画像のように遠く離れた列に時刻を表示させておきます。
尚、時刻の場合は浮動小数点の影響で15分・30分・45分等々はきっちり区切られない場合がありますので
時刻表示の列と、B1・B2に入力する時刻もその浮動小数点を無視するようにしてみました。
今回はとりあえず、15分刻みの時刻をM列に、B1・B2に入力したデータをN1・N2に表示して
そのデータに基づきC列のセルに色を付けるようにしています。

まず、M1セルに
=ROUND("9:00"+"0:15"*(ROW(A1)-1),5)
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。
これで15分刻みで時刻が表示されます。
そして、N1セルに
=ROUND(B1,5)
N2セルに
=ROUND(B2,5)
としておきます。

最後にC列全てを範囲指定し、条件付書式の「数式が」の欄に
=AND(M1>=$N$1,M1<=$N$2)
として、パターンの色を「黄色」にしています。

以上、参考になれば良いのですが
今回も希望に添えない場合はごめんなさいね。m(__)m

お礼

tom04さん、何度もありがとうございます。

実は遠くのセルに値を入れておく方法は私も考えたのですが、印刷の関係で見送っていました。でも良く考えたら別シートに値を入力しておけば問題ないですね？

ところで、何度もお答えいただいた上に恐縮ですが、下記の件でよいアイデアがあれば教えていただけないでしょうか？

例えばtom04さんの上記のやり方の場合、C列の色が既に変わっているとき、今のままだとB列に新しい値を入力するとC列の情報は新しい値で更新されます。そのC列の情報を消さずに（残したまま）、C列の色が既に変わっているのを判定して、D列・・・以降の色が変わるように出来ないでしょうか？

元々そうしたい旨での質問だったのですが、分かりづらく手間を増やしてしまいました。申し訳ありません。

よろしくお願い致します。

回答No.5

keithin

回答No.2です。
あれれ，よく見たら誤記してました。ごめんなさい。

訂正：
B1の条件付き書式は
=($A$1<$A$2)*($A$1<>"")*(B1="")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1)
C1の条件付き書式は
=($A$1<$A$2)*($A$1<>"")*(B1<>"")*(C1="")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1

です。失礼しました。

解説１：
($A$1<$A$2)*($A$1<>"")

開始時刻(A1)と終了時刻(A2)が両方とも入力され，さらに開始時刻の方が終了時刻よりもきちんと早い時刻で記入されているときに，はじめて色帯が付くようにしています。
つまり，まずＡ１が空っぽではないこと，更にＡ２にはＡ１の時刻よりも大きい（遅い）時刻が入力されていること，です。

解説２：
B列側　(B1="")
C列側　(B1<>"")*(C1="")

ご質問の指定事項です。
Ｂ列に記入が無い(B1="")場合にＢ列に色を付けます。記入されたら（B1=""でなくなったら）色を付けません。
Ｃ列は，Ｂ列が記入されてた時(B1<>"")に色を付けます。Ｂ列が=""だった(B1<>""ではないとき）は色を付けません。
なおご質問の指定事項にはありませんが，Ｂ列同様Ｃ列も記入があった場合は色を付けません。引き続きＤ列などに条件付き書式を取り付けておいてください。

＃補足
他の方からのアドバイスでちょっと気がつきましたが，時刻の参照用に１５分刻みの時刻の値をセルのオートフィルドラッグで作成させても，計算誤差により正しい１５分ごとの時刻が生成されていません。
結果して当該時刻以前や以降の（つまり＜や＞の）計算でジャストタイムの判定を間違えますので，オートフィルドラッグで時刻を並べさせたものを計算に利用してはいけません。

お礼

keithinさん、詳しい解説ありがとうございます。
とても助かります。

何度もお答えいただいて恐縮ですが、可能ならもう一点教えていただけないでしょうか？

例えばB列の色が既に変わっているとき、今のままだとA列に新しい値を入力するとB列の情報は消えてしまいます。その情報を消さずに（残したまま）、B列の色が既に変わっているのを判定して、C列・・・以降の色が変わるように出来ないでしょうか？

いろいろ検索してみましたが、セルの色情報などで簡単に判別できないようなことが書かれていたので、自力で出来ませんでした。。。

元々そうしたい旨での”B列にデータがある場合・・・”だったのですが、教えていただいた内容を参考に自力で出来るかな？と思っており、たびたびの質問になってしまい申し訳ありません。

お時間があるようでしたら、お願い致します。

回答No.4

tom04

こんばんは！
外していたらごめんなさい。
15分ごとにセルを分けるということですが、仮に　15分とか30分きっちりの場合をどちらに入れるか？
によって色がつくせるがずれてきますので、
勝手に↓のように表を作らせてもらいました。
D列・F列とも15分刻みでシリアル値で入っています。
そして、D列以上～F列未満でそれぞれの列に条件付書式で塗りつぶしのバターンを設定しています。

まず、D列はD2セル以下の行を範囲指定し、条件付書式で数式がの欄に
=AND(D2>=CEILING($B$1,"0:15"),D2<CEILING($B$2,"0:15"))
として、書式のパターンから「黄色」を選択しています。

どうようにF列はF2以下の行を範囲指定し、条件付書式の数式欄に
=AND(F2>=FLOOR($B$1,"0:15"),F2<=FLOOR($B$2,"0:15"))
として同様に「黄色」を選択しています。

以上、直接の解決にはならないと思いますが
一つのヒントとなれば良いと思っています。
どうも失礼しました。m(__)m

お礼

tom04さん、ありがとうございます。
画像まで入れていただきとても分りやすく助かりました。

ただこのやり方はDセルとFセルの値とBセルを比較していますが、実際作りたいものはDやFに値を入れることができません。

ただCEILINGとFLOORの使用方法を覚えることが出来ました。ありがとうございます。

回答No.3

popuplt

>B行にデータがある場合、C行に色をつけるようにしたい

B列とC列にどのようなデータがあるか判りませんが、C列を作業列にして考えます。
[C1]=9:00,[c2]=9:15と入力し、C1とC2を選択して下へコピー。（15分置きの時刻が入力される）

条件付書式設定　
[B1]=(B1="")*(C1>=$A$1)*(C1<$A$2) セルを着色
[C1]=(B1<>"")*(C1>=$A$1)*(C1<$A$2) セルを着色 (15分置きのデータを見せたくなかったらフォントも色を付ける)
下へ必要なだけコピー

回答No.2

keithin

2003までのエクセルの場合。
B1を選んで条件付き書式を開始し
セルの値が　を　数式が　に変えて
右の空欄に　=($A$1<$A$2)*($A$2<>"")*(B1="")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1)
と設定して書式ボタンで色を付ける。

C1を選んで条件付き書式を開始し
セルの値が　を　数式が　に変えて
右の空欄に　=($A$1<$A$2)*($A$2<>"")*(B1<>"")*(C1="")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1)
と設定して書式ボタンで色を付ける。

B1とC1を下向けにコピーする。

Excel2007では，ホームタブの条件付き書式でだいたい同じようにして「数式を使用して，書式設定するセルを決定」を使います。

お礼

keithinさん、ありがとうございます。

動きを見ると考えていたものに近いことが実現できそうなのですが、もし可能でしたら少し入力式の解説をしていただけないでしょうか？

=($A$1<$A$2)*($A$2<>"")*(B1="")*(TIME(9,15*(ROW(B1)-1),0)<$A$2)*(TIME(9,15*ROW(B1),0)>$A$1)

特に($A$2<>"")*(B1="")の部分はどういったことなのでしょう？
お時間があればお願いいたします。

回答No.1

O_O

条件付書式から、関数を入れて、書式のパターンから色を選定すれば、できると思うが。
（関数は思いつきません。）

補足

すみません。詳しくないものでどのように書いたら良いのか分かりませんが、要は入力した時間の判別方法（例えば9：15と入力した時）をどうするか。対応したセルに反映させる良い方法（全ての時間をIFなどで指定すると長い式になってしまうと思うので簡単な方法はないのかな？と）

関数にこだわりはありません。条件式で実現できればそれも良しですが・・

んー、この説明でもちょっと分かりづらいですね。昔EXCELの質問をすると条件式なども書いて回答してくれるサイトがあったと思いますが、それば見つからなくて・・・。