Excelの条件付き書式で時刻を指定する方法

質問

A1に=NOW()で現在時刻が表示され、
A2からのA列は30分毎の時間軸になっています。

13：00：00~13：29：59の場合、行4に条件付き書式を適用したいのですが、
どの様な数式を入力すればよいでしょうか？

A1＝NOW()で不可能な場合は、A1の数式に直接時刻が入力されている場合の方法を教えて下さい。

環境
OS：Vista32bit
Version：Office2010

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

回答No.8

kagakusuki · ベストアンサー

ANo.2,7です。

>1時間単位の時刻以外も表示させるにはどうしたら良いでしょうか？

　ANo.7において2番目に挙げた方法では如何でしょうか？
　A2セルとA4セルに時刻を入力して、その時間の差から、何時間何分何秒単位で時刻を表示させるのかが決まる方法ですから、一々、関数を修正する必要がなくなります。

>もし、表示する時刻を1時間刻みではなく、20分刻みとか、100秒刻みといった、その都度異なる時間間隔で表示させる場合には、次の様な方法もあります。
>　まず、A6セルに次の関数を入力して下さい。

=IF(COUNT($A$2,A$4)=2,A$2+ROUND((A$4-A$2)*86400,0)*(ROW()-ROW(A$2))/2/86400,"")

>　次に、A6:A7の範囲を下方に向かってオートフィルして下さい。

　尚、上記の関数において、

>２行１時間単位ですが、４行１時間単位など応用するにはどう数式をいじったら良いでしょうか？

を行われる際には、関数中の

*(ROW()-ROW(A$2))/2

という箇所の末尾にある

/2

を

/4

に変更し、「A$4」となっている部分を、「A$6」に変更して下さい。

　尚、どうしても

=A$2+TIME(INT((ROW()-ROW(A$2))/2),,)

の方をベースとして、「1時間単位の時刻以外も表示させる」ようにしたいと言われるのでしたら、

=A$2+TIME(,60*INT((ROW()-ROW(A$2))/2),)

の様にした上で（「60*INT((ROW()-ROW(A$2))/2)」の前後に付く「,」の数に注意）、
表示する時刻の差を、例えば1時間（=60分）単位から20分単位とする場合には、

60*INT(

の部分の60を20に変更して、

=A$2+TIME(,20*INT((ROW()-ROW(A$2))/2),)

として下さい。
　又、２行１時間単位を４行１時間単位にする際には、関数中の

INT((ROW()-ROW(A$2))/2

という箇所の末尾にある

/2

を

/4

に変更して下さい。

お礼

遅くなりました。
やっと出来ました。

めちゃめちゃ勉強になります。
繰り返し参考にさせて頂きます。

回答No.7

kagakusuki

>A4セルに=A4+TIME(ROWS($4:4),0,0)
>ということでしたが、どうしてもできません。

　申し訳御座いません、その関数は大間違いでした。
　A4セルに入力する正しい関数は次の様になります。

=A$2+TIME(INT((ROW()-ROW(A$2))/2),,)

>A２セルではなくてA4セルに入力するのは何故ですか？

　上記の関数は、A2セルに入力されている時刻を基にして、2行ごとに1時間ずつ時間を増やしていく関数です。
　1時間おきの時刻を表示する際に、何時の時刻から表示を開始するのかをパソコンに指示してあげなければ、パソコンは何時の時刻を表示すれば良いのか判断出来ませんので、一番最初の時刻であるA2セルの時刻は、手入力で指定しなければならない訳です。

　それから、後で思い付いたのですが、もし、表示する時刻を1時間刻みではなく、20分刻みとか、100秒刻みといった、その都度異なる時間間隔で表示させる場合には、次の様な方法もあります。
　まず、A6セルに次の関数を入力して下さい。

=IF(COUNT($A$2,A$4)=2,A$2+ROUND((A$4-A$2)*86400,0)*(ROW()-ROW(A$2))/2/86400,"")

　次に、A6:A7の範囲を下方に向かってオートフィルして下さい。
　尚、こちらの方法の場合、何時の時刻から表示を開始するのかを指定するためにA2セルに時刻を入力し、A2セルに入力した時刻から時刻表示を開始して、どの位の間隔をあけた時刻で表示して行けば良いのかをパソコンに指示するために、A4セルに時刻を入力しなければなりません。（そのため、関数による自動表示はA6セルからとなります）

　後、余談ですが、FLOOR関数は「指定した基準値の倍数のうち、最も近い値に数値を切り捨てる」関数で、例えば

=FLOOR(10,0.6)

とした場合には、10÷0.6=16余り0.4であり、10は0.6の16倍よりも大きく、0.6の17倍よりは小さいため、余りの0.4を切り捨てて、0.6の16倍である9.6が表示されます。（0.6×16=9.6）
　この様な関数であるため、

=FLOOR(20,10)

の場合は、20は10の丁度2倍ですから20になりますが、

=FLOOR(19.9999999999999,10)

の様に、僅かな差によって10が表示されてしまう事もあります。
　又、FLOOR関数とは逆に、余りがある場合には切り上げを行うCEILING関数というものもあります。
　そして、時刻を表すシリアル値には誤差の問題があるため、時間を扱う際にFLOOR関数やCEILING関数を使用しますと、僅かな誤差によって、期待したものとは異なる結果となる場合もまれにはありますから、時間を扱う場合にFLOOR関数やCEILING関数を使用するには注意が必要で、良く解らない場合には、FLOOR関数やCEILING関数を使用する事を避けた方が無難です。

お礼

=A$2+TIME(INT((ROW()-ROW(A$2))/2),,)
でオートフィルすると、「10：30」のように、1時間の間の時間が表示されません。
でもExcelは「10：30」という認識はしているようで条件付き書式がきちんと適用されました。
1時間単位の時刻以外も表示させるにはどうしたら良いでしょうか？

また、=A$2+TIME(INT((ROW()-ROW(A$2))/2),,)は
２行１時間単位ですが、４行１時間単位など応用するにはどう数式をいじったら良いでしょうか？
沢山聞いてすみません。
もしよろしければお願い致します。

回答No.6

KURUMITO

=A2=FLOOR(TIME(HOUR(A$1),MINUTE(A$1),SECOND(A$1)),"0:30")

上の式の意味について考える前に初めにA1セルにはNOW関数を使って現在の日付と時間を求めています。
NOW関数では時間の他に日付が入っているのです。
ところがA2セルから下方のセルでは8:00，8:30などと入力しており、日付は含まれておりません。
したがって条件付き書式を設定するに当たってはA1セルに入力したデータのうちで時間の部分のみを使ってA2セル以降のデータと比較することが必要となります。
そのためにTIME関数を使って時間の部分（何時何分何秒）を取り出しています。
その取り出した時間を30分単位の時間にするためにFLOOR関数を使っています。
例えばNOW関数で2012/11/24 13:15:00 となっているのでしたらTIME関数で　13:15:00 のみを取り出し、FLOOR関数を使って13:00:00　のように30分単位で切り捨てた時間を求めています。
その切り捨て処理をした時間がA2セルから下方のセルに入力した時間と一致する場所のセルを条件付き書式で色を付けるという操作を行っています。
なお、エクセルでの日付や時間の計算はすべてのデータをシリアル値として捉えて計算をしています。
シリアル値は1900年1月0日からの経過の日数や時間を表しています。
セルに1と入力してそれをセルの表示形式を日付にしてみれば1900/1/1のように表示されます。
1:00と入力してセルの表示形式を標準にすれば0.0416666666666667のように表示されます。この値は1時間を24時間で割った値に相当します。つまり日にち単位の数値として取り扱っています。
1900/1/1 1:00 と入力しセルの表示形式を標準にすれば1.0416666666666667のような数値として計算に使われるのです。
なお、A1セルにNOW関数を使って日付と時間を求めてからそのデータとの比較で条件付き書式を作っていますが、A1セルを使わなくともA2セルから下方の条件付き書式の設定では次のような式にすればよいでしょう。

=A2=FLOOR(TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),"0:30")

A$1の代わりにNOW()をそのまま使用しています。

お礼

シリアル値の意味がわからなかったので凄く助かりました。
A1を参照せずに適用する方法も分かりました。

親切丁寧に本当にありがとうございました。

何度も読み返して学習します！

回答No.5

keithin

こんにちは。

まず。
既出回答でも指摘されている通り、Ａ２とＡ４に生数字の時刻を入れてオートフィルドラッグすると「間違った時刻の値」が記入される危険があるので、やってはいけません。
まぁやっても構いませんが、計算に使ってはいけません。

準備（参考）：
Ａ２に開始の時刻を手で記入する。たとえば12:00
Ａ４には
=$A$2+ROW(A2)*"0:30"
と記入し、各セルにコピーする

準備：
Ａ１には
=MOD(NOW(),1)
と数式を記入し、時刻の表示形式を付けておく
＃ただNOW関数を放り込んでおくのではないので、間違えないように注意してください

手順：
Ａ２以下を選択し、条件付き書式▼から新しいルールを開始
「数式を使用して…」を選び、下の空欄に
=FLOOR($A$1-$A$2,"0:30")/"0:30"+ROW($A$2)=ROW()
と記入、書式ボタンで書式を施す。

＃参考
Ａ１セルにＮＯＷ関数を入れても、Ａ１は「勝手に自動で時を刻み続ける」訳ではありません。
何もしないで放っておいたら、現実の時刻が次のゾーンに進んでも、勝手に色帯が追従していくという事は無いので念のため注意してください。
Ｆ９を押すとか、セルに何か記入するとか、ブックを開きなおすとかして、初めて現在の状態に表示が変わります。

お礼

Ａ４に
=$A$2+ROW(A2)*"0:30"
と記入し、各セルにコピーする

これで各セルにコピーするとなぜか全部同じ時刻が表示されてしまいました。

原因がわかりませんが、A４をオートフィルしてから数式バーEnterすると表示もきちんとなりました。

回答No.4

KURUMITO

回答No1.3です。
A1せるには＝NOW()と入力します。
A2セルには例えば8:00と入力し、A3セルには8:30と入力します。
A2セルとA3セルを選択してから右下隅の■をクリックして下方にドラッグすれば30分ごとの時間が表示されます。
その上でA2セルから下方のセルを範囲として選択します。A2セルがアクティブの状態のセルになっています。
「ホーム」タブの「条件付き書式」から「新しいルール」で「数式を使用して書式設定するセルを決定」にチェックをし、数式の窓には次の式を入力します。

=A2=FLOOR(TIME(HOUR(A$1),MINUTE(A$1),SECOND(A$1)),"0:30")

「書式」をクリックして「塗りつぶし」のタブで好みの色を選択してOKします。

お礼

やり方は合っていたのでダメ元でもう一度チャレンジしたら、
A2からのA列の数式バーに「1900/1/1 3:30:00」と入っていたので、
時刻を直接打ち直してオートフィル後にちゃんと出来ました。

何度も教えて下さりありがとうございました。

数式の意味をこれから調べて勉強します！

回答No.3

KURUMITO

A1セルに＝NOW()と入力してA2セルから下方には8:00，8:30，9:00，9:30・・・・・と30分ごとに時刻が入力されているとしてA1セルの時刻にあったセルに条件付き書式で色を付けるとしたら次のようにします。
A2セルから下方を範囲として選択したのちに「ホーム」タブの「条件付き書式」から「新しいルール」で「数式を使用して書式設定するセルを決定」にチェックをし、数式の窓には次の式を入力します。

=A2=FLOOR(TIME(HOUR(A$1),MINUTE(A$1),SECOND(A$1)),"0:30")

「書式」をクリックして「塗りつぶし」のタブで好みの色を選択してOKします。

お礼

上記の条件の表を作り試しましたができませんでした。
何かミスしているかもしれません。
もっと勉強します。
ありがとうございました。

回答No.2

kagakusuki

>A1＝NOW()で不可能な場合は、A1の数式に直接時刻が入力されている場合の方法を教えて下さい。

　まず最初に、A4以下に時刻を入力する際には、A2セルの値を単純にオートフィル（ドラッグ　アンド　ドロップ）する事で入力しますと、時刻を表すシリアル値の誤差の問題から、入力された時刻データにわずかな誤差が生じてしまい、条件付き書式の設定で想定した通りの表示が得られなくなる事があります。
　例えば、A2セルに0:00と入力されている場合に、A4セルに1:00と入力してから、A2:A5をオートフィルしますと、A20セルの値は、表示だけは9:00になっているものの、実際のデータは9:00よりも極僅かに小さな値となるらしく、A1セルに9:00入力した際に、9:00のセルではなく8:00のセルに対して条件付き書式が現れる事があります。
　ですから、各セル毎に直接、キーボード入力を行うか、或いは、A4セルに、

=A4+TIME(ROWS($4:4),0,0)

の様な関数を入力してからオートフィルする事で、時刻入力を行って下さい。
　その上で、次の様に操作を行って下さい。

A2セルを選択
　　↓
[ホーム]タグをクリック
　　↓
「スタイル」グループの中にある[条件付き書式] をクリック
　　↓
現れた選択肢の中にある[ルールの管理] をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定] をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に次の数式を入力

=MATCH($A$1,$A:$A)=ROW()

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスにおいて、条件を満たした場合の書式を設定
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「ルール(表示順で適用)」欄が「数式:=AND($A$...」となっている行の「適用先」欄をクリック
　　↓
「適用先」欄の内容を一旦消去
　　↓
A2セルをクリック
　　↓
A列において時刻が入力されているセルの中で、最も下にあるセルにカーソルを合わせてから、[Shift]キーを押しながら、マウスの左ボタンを押す
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　以上です。

お礼

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

補足

とっても詳しくて分かりやすかったのでできました。
ずっと模索していた解決策の一つが見つかり嬉しいです。

A列の時刻をオートフィルするとズレてしまうので、
A4セルに=A4+TIME(ROWS($4:4),0,0)
ということでしたが、どうしてもできません。

A4セルは１：００ですが、この数式を入力してオートフィルすると、
全部１：００になってしまい連続データのコピーができません。
A２セルではなくてA4セルに入力するのは何故ですか？
宜しければもう少し教えて下さい。

回答No.1

KURUMITO

A1に現在の時刻が入力されるとしてA2セルから下方には90分ごとに例えば5:00,5:30,6:00・・・のように入力されるのでしょうか？
13:00:00から13:29:59の場合4行目に条件付き書式を適用するとはどんなことでしょう。A1セルとの関係が分かりません。

お礼

すでに回答をくださっていたことに気づかず補足してすみません。
ありがとうございます。

補足

Excel始めたばかりで、ずれた質問をしているかもしれません。
目的は現在時刻に応じて、それに合う時間軸の行の書式を変えることです。
A1に時刻を入力し、それを使用しなければいけないと思い込んでいましたが他にもっと簡単な方法がありますか？