[[20141102165543]] 『条件付き書式でのセルの塗りつぶし』(ゆーゆ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『条件付き書式でのセルの塗りつぶし』(ゆーゆ)

空白のセルに入力した時間を過ぎたら赤く変わる条件付き書式を作っていますがうまく変わりません。
正確には一番上の行なら赤く変わりますが、行を上に追加すると時間を過ぎていない場合でも赤くなってしまいます。

 A  B    C   D   E   F  G
3     初日の日付(B7に飛ぶ)
4         時     分
5
6
7 10/26 ●   ←追加した行
8 11/2  ●  ←一番上だった行(この行以降がおかしい)  ●…未入力時に赤くなるセル(C列)
9 ・・以降+7日ずつ

条件付き書式の数式は
=AND(C7="",OR(AND(B7<TODAY(),C7=""),AND(B7=TODAY(),TIME(HOUR(NOW()),MINUTE(NOW()),)>TIME(E4,G4,0),C7="")))

となっていて未入力で今日を過ぎた日付又は未入力で今日の入力した時間・分(E4,G4)が現在の時間を過ぎたときに赤くなるはずです(7行目の位置にあるときのみ)
しかし8行目以降を追加すると今日を過ぎた日付なら赤くなりますが、今日の日付で時間が過ぎていなくても赤くなってしまいます。

ちなみに条件付き書式の下位のルールに未入力でB列の日付が今日になると緑になる条件付き書式は7行目より下に今日がきてもしっかり緑になります
数式:=AND(C7="",B7=TODAY())

わかる方がいらっしゃったら是非ご教授お願い致します。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 気になるのは
 TIME(E4,G4,0)
 の所が相対参照になって居ることですが。
  
(HANA) 2014/11/02(日) 18:09

時間を変えられるように相対参照にしてあるのですが他にどこかに問題はないでしょうか?
(ゆーゆ) 2014/11/03(月) 13:33

 7行目は、4行目の時間
 8行目は、5行目の時間
 9行目は、6行目の時間・・・・

 と言う事ですか?

 E:G列のデータも埋めてみてもらえませんか?
  
(HANA) 2014/11/06(木) 09:24

どこの行でもE4の時間、G4の分を参照するということです。
今日がC7の位置ならその時間を過ぎると赤くなるので良いのですが、今日がC8以降になったときにはその時間を過ぎてなくても常に赤くなってしまうでそこを修正したいので良い案はないでしょうか?
(ゆーゆ) 2014/11/07(金) 12:56

 一度以下で相対参照・絶対参照について調べてみてはどうか?

http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040720.html

 なお条件のほうは
 TIME(E4,G4,0)
 の行を絶対参照にして
 TIME(E$4,G$4,0)
 で、どうだろうか?

 行が相対参照だと7行目でE4セル、G4セルを参照していても8行目ではE5セル、G5セルを参照することになる。

 行が変わっても参照するセルの行を変えないために行を絶対参照としてE$4、G$4のようにする。
(ねむねむ) 2014/11/07(金) 13:10

 衝突しちゃいましたが。。。。

 >どこの行でもE4の時間、G4の分を参照するということです。
 だったら、やはり 絶対参照にしておかないといけないのでは?

 B7に
=AND(C7="",OR(AND(B7<TODAY(),C7=""),AND(B7=TODAY(),TIME(HOUR(NOW()),MINUTE(NOW()),)>TIME(E4,G4,0),C7=""))) 
 の設定だと、B8にコピーしたとき
=AND(C8="",OR(AND(B8<TODAY(),C8=""),AND(B8=TODAY(),TIME(HOUR(NOW()),MINUTE(NOW()),)>TIME(E5,G5,0),C8="")))
 目的のセルとは違うセルを参照してしまいますよね?                                      →~~ ~~

 行番号を絶対参照にしておけば、行方向にコピーした場合でも 参照先は変わりません。 
=AND(C7="",OR(AND(B7<TODAY(),C7=""),AND(B7=TODAY(),TIME(HOUR(NOW()),MINUTE(NOW()),)>TIME(E$4,G$4,0),C7="")))
                                                                                          ~   ~
 ライブラリもご参考に。
http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040720.html
  
(HANA) 2014/11/07(金) 13:13

ありがとうございます。
絶対参照にしたところうまくいきました。

追加で質問なのですがE4の時、G4の分のところに24時以降(24,25,26...)を入力した際にもその時間を過ぎた時にセルの色を赤くするといったことはできないでしょうか?
(ゆーゆ) 2014/11/09(日) 12:55


 分の所が60以下しか入れないなら
 =TEXT(B7+($E$4&":"&$F$4),"yyyymmddhhmm")<TEXT(NOW(),"yyyymmddhhmm")
 の条件でどうですか?
  
(HANA) 2014/11/10(月) 13:57

ありがとうございます。
その条件を使ってみましたが、24:00以降でうまくいきませんでした・・
ちなみに分はG4に入力するのでG4に置き換えてから試してみましたが・・・
(ゆーゆ) 2014/11/11(火) 22:49

 >ちなみに分はG4に入力するのでG4に置き換えてから試してみましたが・・・
 すみません、F4→G4ですね。

 >24:00以降でうまくいきませんでした・・ 
 どんなデータでためしていて
 何日何時の段階でどこまで色がついていれば良いか
 教えて下さい。
  
(HANA) 2014/11/13(木) 08:46

すみません・・できるようになりました
もう1つ聞きたいのですが、B7以降に今日の日付があり、E4、G4の時分より30分間だけ色を変えるという条件を作っているのですがうまくいきません・・

B7=TODAY(),TIME(E$4,G$4,0)<=TIME(HOUR(NOW()),MINUTE(NOW()),)<=TIME(E$4,G$4,0)+"0:30"

という条件式としましたが色がつきません。

B7=TODAY(),TIME(E$4,G$4,0)<=TIME(E$4,G$4,)+"0:30")

これでは逆にE4,G4に指定した時間より30分を経過しても色がついてしまいます。
またご教授お願い致します。

(ゆーゆ) 2014/11/14(金) 19:35


 単純に
 =AND((NOW()-"0:30")<(B7+($E$4&":"&$G$4)),(B7+($E$4&":"&$G$4))<(NOW()+"0:30"))
 なんてのではだめですか?
  
(HANA) 2014/11/15(土) 10:43

 こんなのでも良いかもしれません。
 =(ABS(B7+($E$4&":"&$G$4)-NOW())*24*60)<30
  
(HANA) 2014/11/15(土) 11:27

すみません。。。どちらの数式もうまくいきませんでした(色がつきません・・)
補足させてもらいますと今日だけ(B7以降に過去や未来の日付がある行では発動させない)指定した時間から30分だけ色を変えたいということです。
(ゆーゆ) 2014/11/15(土) 13:08

 どんなデータでためしていて
 何日何時の段階でどこに色がついていればければいけない所、色がついていないのか
 詳しく教えて下さい。
  
(HANA) 2014/11/15(土) 16:23

すみませんでした

 A    B       C   D   E   F  G
3          初日の日付(B7に飛ぶ)
4              時     分
5
6
7 D3の日付     ●   ←B列の日付が今日になり現在がE4,G4の時間帯のとき
8 D3の日付+7日    ●    E4,G4の時間から30分間だけ色をつけるセル(B列のある行が今日のときにC列のB列のセルの右隣のセルに色をつける)
9 ・       ●
10 ・             ●
11 ・       ●
以降+7日ずつ

こんな感じにしたいのですがわかりにくくてすみません・・
ちなみに時間は24時以降の場合、例えば11/16 0:00のときはB7の日付が11/15で時間指定は24:00というようにしたいのです
(ゆーゆ) 2014/11/15(土) 23:59


 レイアウトではなく、実際にセルに何の値を入れて試しているか教えて下さい。

 もしかして。。。
 今は、指定時間の前後30分の式になっているので
 後30分だけで良いのに前30分でも色がつくから、うまくいかない
 って事でしたか?
  ・・・でも『色がつきません・・』って書いてあるしなぁ・・・

 ちなみに、NOW関数の値は 再計算された時に更新されます。
 たとえば、
  30分より前にファイルを開く・・・・この段階では色がついていない
  その後、時間が経過し 30分の範囲に入る・・・・この段階でも色がつかない
  30分の範囲内で、再計算が行われる・・・・色がつく
 のですが、その点は大丈夫でしょうか?

 開きっぱなしで時間の経過を待っても、セルの色は変化しません。
  
(HANA) 2014/11/17(月) 08:44

再度試したところ =AND((NOW()-"0:30")<(B7+($E$4&":"&$G$4)),(B7+($E$4&":"&$G$4))<(NOW()+"0:30"))
この式にて色を変えることができました。しかし指定時間より後の30分間だけ色を変えるようにしたいです。

しっかり確認せずすみませんでした。再度ご教授お願い致します。

(ゆーゆ) 2014/11/17(月) 20:42


 =AND((NOW()-"0:30")<(B7+($E$4&":"&$G$4)),(B7+($E$4&":"&$G$4))<(NOW()+"0:30")) 
 の式の意味が分かりますか?

 (NOW()-"0:30")・・・・・今より30分前・・・・・○
 (B7+($E$4&":"&$G$4))・・指定日付&時間・・・・◆
 (NOW()+"0:30")・・・・・今より30分後・・・・・◎

 元の式はかっこが多くて見にくいですが
 =AND(○<◆,◆<◎)
 こんな式になっています。

 ですから、○の部分を
 =AND(今<◆,◆<◎)
 と言った式に変えてもらったら良いと思います。
  
(HANA) 2014/11/18(火) 11:10

ご指摘通りこのように式を変更しました。
=AND(NOW()<(B7+($E$4&":"&$G$4)),(B7+($E$4&":"&$G$4))<(NOW()+"0:30"))
しかしこの場合指定した時刻が現在の時間より前のときに色が変わりません。
逆に現在の時間より後の時間を指定すると色をつけたくないのに色がついてしまいます。

あくまで指定した時刻を基準に30分間後に現在の時間が入っているときに色を変えたいのです。
何度も申し訳ないです・・
(ゆーゆ) 2014/11/18(火) 17:40


 =AND(今<◆,◆<◎)
 じゃダメって事ですね?

 この式だと、数直線としてみてもらうと
 過去・・・・→今→・・・→指定時間→・・・→今+30分→・・・・
                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 指定時間が下線の間にある時に色が付きます。

 >指定した時刻を基準に30分間後
 ってのは、具体的にいつですか?

 たとえば、指定した時刻が 2014/11/18 17:00 だったら
 何時から何時の間で色がつけば良いですか?

 過去・・・・→指定時間→・・・→今→・・・→指定時間+30分→・・・・
                       ~~~~~~~~~~~~~~~~~~~~~~
 って事ですか?
  
(HANA) 2014/11/18(火) 19:04

説明不足ですみませんでした。

下の図の通りであっていると思います。
現在が2014/11/18 16:59で指定が2014/11/18 17:00の場合は色が付けない
現在が2014/11/18 17:10で指定が2014/11/18 17:00の場合は17:00〜17:30の間で色を付けたいので色が付ける
現在が2014/11/18 17:31で指定が2014/11/18 17:00の場合は色を付けない
といった感じにしたいです。

(ゆーゆ) 2014/11/19(水) 18:06


 >下の図の通りであっていると思います。 
 でしたら
 指定時間 ・・・・・◆
 指定時間+30分・・・◇
 であらわすと
 =AND(◆<今,今<◇)
 ですね。

 条件式を作ってみて下さい。
 うまく行かない場合は、どんな条件式を作ったのか教えて下さい。
  
(HANA) 2014/11/19(水) 22:58

返信遅れて申し訳ありません。
ありがとうございます。
うまくできました。

もう1つ伺いたいのですが、以前にに示したレイアウト通りで
現在の時間ががB7の日付の翌日3:59までだった場合に色をつけるようにしたいです。

例えば現在が11/25の3:00の場合、B7が11/24でも色をつけるようにしたいです。
(更に現在が11/24の場合でも同じです)

ご教授いただけたらよろしくお願いします。
(ゆーゆ) 2014/11/24(月) 14:23


 >指定時間+30分・・・◇
 の所が
  指定時間+1日+30分
 になるだけですか?
 それとも、違うのでしょうか?

 数直線の様に表してもらえると良いのですが。

 ちなみに、
 B8セルに D3の日付+7日
 と書いてありますがこのセルには
 =B7+7 って数式が入ってるんですよね?
  
(HANA) 2014/11/24(月) 14:42

11/24          11/25
... 22:00   23:00   0:00   1:00   2:00   3:00   4:00   5:00
|←         現在の時刻がここの間          →|

E4,G4の指定時間は関係無いですが、
上の図でいう指定した時間帯は11/25に日が変わってもB7が11/24ならば色をつけるということです。
なので11/25の4:00〜はB7が11/25となっているときに色をつけるということです。

B8には=B7+7という式が入っていますが、気にしなくていいと思います
(ゆーゆ) 2014/11/25(火) 03:45


 過去・・・・→今→・・・→B7の日付の翌日の4:00→・・・・
 ~~~~~~~~~~~~~~~~~~~~~~~~~~
 と言う事ですか?

 B7には0:00の時間で入っていると思いますので
 +1日+4時間 で表せると思いますが。。。

  ちなみに、○<1 と書くと ○に1は含まれません。
  逆に、含めたい場合は、○<=1 と書きます。

 どこでどまどっておられますか?
  
(HANA) 2014/11/25(火) 08:19

例えばB7が11/24ならば
11/24 4:00〜〜現在〜〜11/25 3:59
の間だけ色をつけたいということです。

B7が今日であることも条件にしたいので
=B7=OR(TODAY(),NOW()<TODAY()+1+"4:00")
としましたがこれでは色がつかないのです
どのように修正すればよいでしょうか?
(ゆーゆ) 2014/11/25(火) 13:49


途中からすいません
条件付き書式に左にセルを指定する必要はありません
B7が2014/11/26 3:59:00で B7 = TODAY()だと必ずFALSEになってしまいます。
この状態で今日を調べるには
=IF(INT(B7) =TODAY())とします
さらに上の状況では今日の4時以前も入ってしまうため4時より下は設定しません
私はこのように設定しました。
=AND(B7>=TODAY()+"4:00",B7<TODAY()+1+"4:00")
(デイト) 2014/11/25(火) 14:42

 >過去・・・・→指定時間→・・・→今→・・・→指定時間+30分→・・・・
 >                      ~~~~~~~~~~~~~~~~~~~~~~
 >指定時間 ・・・・・◆
 >指定時間+30分・・・◇
 >であらわすと
 >=AND(◆<今,今<◇)
 で、出来たんですよね?

 そしたら、今度は↓なので
  過去・・・・→B7の日付の4:00→・・・→今→・・・→B7の日付の翌日の4:00→・・・・
                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  B7の日付+4:00・・・・・◆
  B7の日付+1+4:00・・・・◇
  であらわすと
  =AND(◆<=今,今<◇)
 になりませんか?

 B7の日付から一定時間範囲を算出して
 そこに「今」が入るか入らないかで 色を塗り分けるのですよね?

 今日の日付から一定時間範囲を算出するのではなく。
  
(HANA) 2014/11/25(火) 15:50

ありがとうございます。
ご教授頂いた=AND(NOW()>=B7+"4:00",NOW()<B7+1+"4:00")にて今日の日付であるときの色を変えることができましたが、
今日より前の日の色を変えることができなくなってしまいました。

例えば現在が11/27の12:00だとして
...11/25     11/26     11/27         11/28
                        4:00               3:59
...←ここを別の色で色分けできない→|この期間は上の式で色分け可能|

=B7<TODAY()
という式で以前は過去の色を分けられてましたが、今回ご教授頂いた式を新しいルールとして使用してから色分けできません。

過去の色分けの式をどのように修正したら良いでしょうか?
(ゆーゆ) 2014/11/26(水) 14:05


 条件付き書式の優先度はご存じでしょうか?
 下記リンク先(複数の条件付き書式のルールが true に評価される場合の動作)を参照に、
 ゆーゆさんが設定なさった条件を確認してみてください

http://office.microsoft.com/ja-jp/excel-help/HA010342674.aspx#_Toc269129417 現在参照不可

(___) 2014/11/26(水) 14:16


 たとえば、
  11/27 4:00 より前 を 緑
  11/27 4:00〜11:28 3:59 を 青
  で塗り分けたい
 って事ですか?

 実際に設定している式をこちらに載せてもらえますか?
  最初に   =B7<TODAY()                緑
  その次に  =AND(NOW()>=B7+"4:00",NOW()<B7+1+"4:00") 青
 でしょうか?

 条件はかぶらないので、正しく設定されていれば
 塗り分け出来ると思いますが。
  
(HANA) 2014/11/26(水) 14:50

HANAさんのおっしゃるとおりで塗り分けたいということです。

設定している条件式もおっしゃるとおりです。
また条件式は=B7<TODAY()を上位に設定してあります。
しかしB7が前日以前ではHANAさんの例でいう緑にはなりますが、B7が今日であると青くならず色がつきません。
どちらかの式に不具合があるのでしょうか・・?
(ゆーゆ) 2014/11/27(木) 02:22


 >どちらかの式に不具合があるのでしょうか・・?
 そうですね。
 問題点がいくつか出てくると思います。

 一つは
 『B7が今日であると青くならず色がつきません。』
 つまり、B7が今日と等しい場合は青くしたいのですよね?

 > ちなみに、○<1 と書くと ○に1は含まれません。
 > 逆に、含めたい場合は、○<=1 と書きます。 
 このルールを思い出してください。

 =B7<TODAY() と書くと、B7にTODAY()は含まれません。
 もしも B7=TODAY()・・・B7の日付と今日の日付が一致したら FALSE になって色がつかない
 って事です。

 今日も含めるなら
 =B7<=TODAY() ですが、するとまた別の問題が発生します。

 TODAYは、11/27 0:00 〜 11/28 0:00 です。
 でも、このうち 11/27 4:00 〜 は、別の色にしたいです。
 すると、範囲がかぶってしまってます。

   11/25     11/26     11/27      【今】   11/28                  11/29
         0:00      0:00    4:00    12:00      0:00        4:00       0:00 
   青青青青青青青青青青青青青青青青青青青青青青青青青青青青青
                     緑緑緑緑緑緑緑緑緑緑緑緑緑緑緑緑

 (___) さんが書いておられる「優先度」は調べて見られましたか?
 上側にある方が優先されるので、今の設定では 重なったところは 青 になります。

 条件の設定の上下を入れ替えて、緑を上側になるように設定すれば 重なったところは緑になります。

 もう少ししっかり考えてみると
 B7セルに 11/27 と入っていたら それは
  11/27 4:00 〜 11/28 4:00
 の事です。

 なので、【今】が 11/27 4:00 〜 11/28 4:00 の間に入っていれば「緑」
          11/27 4:00 〜【今】〜 11/28 4:00     のパターン
     【今】が 11/28 4:00 より後の時「青」
                                11/28 4:00 〜【今】 のパターン

 セルの日付 11/25       11/26       11/27       11/28       11/29
 見なし日時 11/25 4:00→11/26 4:00→11/27 4:00→11/28 4:00→11/29 4:00
                                       今 11/27 12:00
             青青青青青青青青青青青青緑緑緑緑緑緑

 セルに 11/26 と入っていたら 11/26 4:00 〜 11/27 4:00 です。
 【今】を 11/27 12:00 で考えると
 見なし時間の終わり「11/27 4:00」と【今】「11/27 12:00」を比べて
 【今】より前なので そのセルは青になれば良いです。
 =B7+1+"4:00"<NOW()

 4時間ずれているので難しいですが(分単位で考えると)
 通常 11/26 と書いてある時の終わりの時間は 11/26 23:59 ですが
 今回 11/26 と書いてある時の終わりの時間は 11/27  3:59 次の日の4時前です。
                                                        ~~~~~~~~ 
(HANA) 2014/11/27(木) 09:39

ありがとうございました
うまく動作しました。
ご丁寧な質問をわざわざありがとうございます。
(ゆーゆ) 2014/11/30(日) 00:17

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.