未来の日付日欄を無色（空白）で表示させたい

質問

お世話になります。
先日、貴重なスペースをお借りし、教えて頂きましたが
バージョンアップ？した要望を言われ再び困っています。

Windows7　エクセル2007を利用しています。

-----------

売上の日別管理表を作成しています。

A列→月日（（例）A2から縦へ20140201・・・20140228表示）
B列→取引先名
C列→計画（売上目標）
D列→実績
E列→計画に対する遂行率

E列の遂行率を分かりやすく(目立たせる）するために
条件書式を設定。

C列の計画を設定していない取引先があるので
計画のない取引先は”無色”で表示させたかったので

条件書式の”数式を使用して、・・・”欄へ

=AND(E2<>"",E2>=100%)　→書式青
=AND(E2<>"",E2<90%)　→書式赤
99%から90%の間　→書式黄

と入力しました。

----------------------------

上記の数式を教えて頂いて上手くいったのですが、
下記のような要望を言われ困っています。

日別で管理しているので、当日と当日より未来の日付の
実績欄へは数字は入力されません。

未来の日付欄（該当Eセル）は、”無色(空白)表示”できないか？
というものです。

条件書式へD列が空白ならと
と追加してもうまくいきません。

※D列の実績は
合計する数式（=SUM)を入力しています。
※E列には、条件書式の設定　と　D列/C列
　=IF(ISERROR・・・　を使って#DIV/0!が表示されないようにしています。

改めて、自分なりに検索してみましたが
希望のものを見つけられませんでした。

度々の質問で申し訳ございませんが
よい、お知恵をお持ちの方がいらっしゃいましたら
ご教示お願いいたします。

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

回答No.5

bunjii · ベストアンサー

難しく考えているようですが論理を整理すれば自ずから解決できます。

昨日まではC列とD列に値が入力されているものとすればE列に達成率が表示されます。
また、C列が空欄のときはIFERROR関数でE列を空欄にしていると言うことであり、条件付き書式は以下のようにすれば問題ありません。

=AND(E2<>"",E2>=100%)　青
=AND(E2<>"",E2<100%,E2>=90%)　黄
=AND(E2<>"",E2<90%)　赤
上記の3条件は重複するセルが発生しませんので優先順位を指定しなくても問題ありません。

念のためにE列の数式は以下のようにすれば昨日までの遂行率が表示され当日以降は空欄になります。

=IF(A2<TODAY(),IFERROR(D2/C2,""),"")

お礼

m(_　_)m　m(_　_)m　m(_　_)m

端的、正確なご教示ありがとうございました。

特に、”＝TODAY()”に教えて頂いたような使い方が
出来るとは（＠o＠）ビックリでした。

悩みが、100％解決できました。
ありがとうございました。

回答No.6

tom04

No.1～4です。

No.2の補足にある
＞また、空白にしたいのは、未来の日付のセルのみです。
＞実績は、未来の日付のセルには表示されません

を勘違いしていました。

No.4の方法は未来に対しても「計画欄」に入力があり、「実績欄」に入力がない場合も
「赤」になるようにしていました。

結局No.5さんの方が正しいと思います。

※　条件付き書式に優先順位を付けたのは
「黄」の条件範囲を90%～99%にしなくても良いのと、「赤」の条件付き書式が「青」・「黄」以外になる！
という単なる手抜きのためでした。m（_ _）m

お礼

お世話になります。

お忙しい中、丁寧にご対応いただきまして
ありがとうございました。

教えて頂いた、

=IF(OR(C2="",N2=""),"",IFERROR(N2/C2,""))
=IF(COUNTBLANK(C2:D2),"",IFERROR(D2/C2,""))

につきましては、応用して今後も活用できそうです。

ありがとうございました。m(_　_）m

回答No.4

tom04

列の配置が違う訳ですね！

数式はE列に入れ、条件付き書式もE列に設定すると解釈して・・・

E2セルの数式を
=IF(OR(C2="",N2=""),"",N2/C2)
としてみてください。

（おそらく「計画」に「0」を入力することはないと思いますので、IFERRORは必要ないような気がします）
万一C列に「0」が入るコトがある場合は
=IF(OR(C2="",N2=""),"",IFERROR(N2/C2,""))
という数式にしてください。

条件付き書式の「赤」の場合の数式は
前回そのままの
=C2<>""

だけで良いと思います。

今度はうまくいけばよいのですが。m(_ _)m

回答No.3

tom04

続けてお邪魔します。
補足を読ませてもらいました。

結局「赤」の場合の条件付き書式がなかなか思い通りにならない！というコトですね？

日付（A列）での判断ではなく、C列「計画（売り上げ目標）」の欄に数値が入っていて、
D列「実績」の欄が空白なら「赤」にする方法にしてみてはどうでしょうか？

余計なお世話かもしれませんが、E2セルの数式に手を加えてみます。
E2セルに
=IF(COUNTBLANK(C2:D2),"",IFERROR(D2/C2,""))
という数式を入れオートフィルでずぃ～～～！っと下へコピー！
（C・D列のどちらか一方でも空白セルがあれば空白に、そうでない場合は　D列÷C列　という数式）

そうした上で前回の「赤」の条件付き書式の数式を
=C2<>""
としてみてはどうなるでしょうか？

※　条件付き書式の優先順位はNo.1の通りにしておきます。m(_ _)m

補足

迅速、丁寧なご対応本当に感謝いたします。

度々、不足で申し訳ございません。

説明しやすいかと思いc列とD列を並べたのですが、
間の列は”実際”数列空いて（他の数字があります。）
いて隣合わせではないのです。

D列にSUMを使っての合計式を利用しているのは、
商品の大きさとサイズがあり、詳細な数字があるからなのです。

C列→計画
D列→LLシルバー実績
E列→Lシルバー実績
F列→Mシルバー実績
G列→Sシルバー実績
H列→LLレッド実績
　・
　・
　・
N列→実績合計
といった形です。

>=IF(COUNTBLANK(C2:D2),"",IFERROR(D2/C2,""))

(C2:D2）←この部分を離れているC2とN2といった具合に
できればうまくいくような気がするのですが・・・。

スキル不足を実感してしまいます。
：を,にしてもダメでした。

m(_ _)m

何卒、もう一度、お力を
m(_ _)mm(_ _)m

※優先順位の変更はバッチリです。m(_ _)m

回答No.2

tom04

No.1です！
たびたびごめんなさい。

投稿後、気づきました。
E列は数式が入っていたのですね！

やはり
数式欄にはすべて「空白以外」を追加してください。

青の場合　→　=AND(E2<>"",E2>=100%)
黄の場合　→　=AND(E2<>"",E2>=90%)
赤の場合　→　=AND(E2<>"",E2>0)

として優先順位は前回通りにします。

どうも失礼しました。m(_ _)m

補足

お世話になります。早々にご回答ありがとうございます。
ご親切に画像も表示して頂きましてとても分かりやすかったです。

それから、説明が分かり辛く、不足していたようで
申し訳ございません。m（__)m

早速、試してみたのですが、赤にしておきたい部分も
空白になってしまいました。

毎日、売上が上がればいいのですが、無い日もあります。
その場合は、計画に対して、目標を達成していない
ということになりますので、セルを赤表示にしておきたいのです。

また、空白にしたいのは、未来の日付のセルのみです。

実績は、未来の日付のセルには表示されません。

当月の場合、

例）本日は2/19（20140219とA列に表示）なので、
20140219（2/19）
20142020（2/20）
20142021（2/21）
　・
　・
　・
　・
20140228（2/21）

のE列を日々データを更新しますので、
空白から、青や黄や赤で
表示するようにさせたいのですが、
可能でしょうか？

度々で申し訳ございません。
よろしくお願いいたします。

回答No.1

tom04

こんばんは！

質問文を拝見すると、条件付き書式はE2セル以降を範囲指定して設定してあるようですので、
あとは条件付き書式の優先順位だけの問題だと思います。

E列の数式が　D列/C列　というコトですので
おそらくマイナスにはならないはずです。

そこで条件付き書式の設定を以下のようにしてみてください。

新しいルール　→　数式を使用して・・・　→　数式欄に
=E2>=100%
とし、書式から「青」を設定

同じく条件付き書式を追加
新しいルール　→　・・・（中略）・・・　→　数式欄に
=E2>=90%
として、書式から「黄」を選択

同じく条件付き書式を追加
新しいルール　→　・・・（中略）・・・　→　数式欄に
=E2>0
として、書式から「赤」を選択し

最後に↓の画像の順に優先順位を決めます。
上側の条件が優先されますので、上から「青」・「黄」・「赤」の順にします。
優先順位を変更する場合は、変更したい条件付き書式を選んで、赤丸部分の▲・▼で
上位や下位に設定できます。

※　おそらくE列に「0％」はないであろう！という前提の数式ですので、
万一「0％」の可能性がある場合は
「赤」の条件付き書式の数式を
=AND(E2<>"",E2>0)
に変更してみてください。

※　優先順位を決めれば
=AND(E2<>"",E2>=100%)
の部分の「空白以外」は不要です。

※　優先順位を決めるまではおそらく思った通りの色にならないと思いますが、
優先順位を決定すればお望み通りの表示になるはずです。m(_ _)m