Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
条件付き書式について
投稿日時: 17/10/12 22:56:06
投稿者: chiaki

    A       B       C
1  開始日     終了日    経過日数
2  2017/4/1    2017/5/31     60
3  2017/6/3    2017/8/10     68
4  2017/10/10   
5  2017/8/31   2017/9/15     15
6  2017/9/10
7  2017/5/24   2017/10/1     130
8  2017/10/1   2017/10/16    15
9  2017/3/10   2017/9/20     194
10  2017/6/13   2017/10/5    114
 
C2セルに「=IF((B2-A2>0),(B2-A2),"")」という式が入っています(C10までコピー)。
併せてC列に31日以上経過していたらセルを塗りつぶす条件付き書式を設定したかったのですが、
C2からC10までの適用範囲で「=C2>30」という数式の設定では
空白表示のC4、C6セルも塗りつぶされてしまいます。
どのように設定すればこれを回避できるでしょうか?
何卒ご教示お願いいたします。

回答
投稿日時: 17/10/13 04:52:12
投稿者: bi

条件付き書式の数式を
=AND(C2<>"",C2>30)
にしてみてください。あと式に不要な括弧が入っていますよ。
=IF((B2-A2>0),(B2-A2),"")

=IF(B2-A2>0,B2-A2,"")

回答
投稿日時: 17/10/13 10:42:31
投稿者: WinArrow
投稿者のウェブサイトに移動

別の観点からの提案
 
C列セルの数式を単純にする
 
=B2-A2
 
または、
=IF(B2<A2,0,B2-A2)
 
条件付き書式(1)
 
=C2>30
書式は、ご自由に
 
条件付き書式(2)
=C2<=0
書式:表示形式でユーザー設定で「;;;」

回答
投稿日時: 17/10/13 12:33:39
投稿者: んなっと

biさんの式でいいと思います。
 
文字列だとエラーになるように、算術演算子(+-*/)をわざと利用した
=C2-30>0
もあるかもしれません。
しかし今回はbiさんの式が完璧で利用しやすいと思います。

回答
投稿日時: 17/10/13 14:40:03
投稿者: Mike

=SUM(C2)>30
は如何?
完璧じゃないか?

回答
投稿日時: 17/10/13 14:54:32
投稿者: んなっと

もちろんMikeさんの式でも完璧ですね。
 
30より大きい→=SUM(C2)>30
30より小さい→=C2<30
という使い分けですね。
 
この場合は、うっかり
30より小さい→=SUM(C2)<30
としないように気を付ければいいわけです。
[長さ0の文字列""のときにも色がついてしまうのを防ぐため。]

回答
投稿日時: 17/10/14 15:23:33
投稿者: ブルー

=N(C2)>30
 
でもいいと思います。

回答
投稿日時: 17/10/14 17:54:50
投稿者: ブルー

C列の経過日数は最大でどのくらいになりますか?
例えば「10000」を超えることは絶対にないとすると
 
ルールの種類
指定の値を含むセルだけを書式設定
   ↓
「セルの値」「次の値の間」 31 と 10000
 
でもいいかもしれません。

回答
投稿日時: 17/10/15 12:20:33
投稿者: LMK

こんにちは
 
上限は「=B2」にしておけば、適当な前提を置くより安全かもしれませんね。
 
まあ N 関数の方が簡単そうですが。

回答
投稿日時: 17/10/15 12:59:01
投稿者: ブルー

「適当な前提」って何のことでしょうか?
 
どんな日付を入力するのか見当もつかないのなら
現状のExcelの仕様に合わせて、最大値を「3,000,000」にしておけば
余計なセルを使うよりいいかもしれませんね。
 
次の値の間 31 と 3000000
 
見当もつかないなんてことはないと思いますが。

回答
投稿日時: 17/10/15 14:17:17
投稿者: LMK

ブルー さんの引用:
現状のExcelの仕様に合わせて、最大値を「3,000,000」にしておけば
余計なセルを使うよりいいかもしれませんね。

 
Excelの仕様上の日付上限=9999/12/31=2,958,465
 
ということですね。
相変わらず、ブルーさんのお話は難しすぎてとてもついて行けない。
 
「余計なセルを使うよりいい」のは、まさにその通りです。
でもブルーさん以外の人(常人)には、なぜ「3000000」なのかわかりません。
 
N関数の方がまだわかりやすいですね。
 
いかにしてわかりやすさとバランスをとるかはとても重要だと思います。

回答
投稿日時: 17/10/15 15:01:59
投稿者: ブルー

>どんな日付を入力するのか見当もつかないのなら
 
経過日数の最大がどれくらいになるのか見当もつかないのなら、という意味です。
(以上、質問者さんへの補足)
 
 
>相変わらず、ブルーさんのお話は難しすぎて
 
「相変わらず」とはどういうことでしょうか?
 
>上限は「=B2」にしておけば
 
次の値の間 31 と =B2 
 
ということなんでしょうか?
相変わらず中途半端な書き方で常人には読解に苦労します。
 
その設定でB列が空白の場合に色がつくことを回避できるんですか?
できるのであればいいんですけど。
試せてないのでわかりません。
 
>なぜ「3000000」なのかわかりません。
 
わからなかったら質問してもらえればお答えします。
くり返しますが、そこまで範囲を広げる必要はないと思います。
 
経過日数の上限を設定するのがそんなにわかりにくいですか?
 
>「適当な前提」って何のことでしょうか?
 
この質問に答えてくれませんか?

回答
投稿日時: 17/10/15 23:28:38
投稿者: LMK

可読性を上げる意味では、上限を以下のように設定方法もありますかね。
 
="9999/12/31"+0
 
もちろん、
 
=B2
 
でも
 
=30000
 
でも
 
目的は達せられるでしょう。試してみればわかることですが。
 
(ちなみに N 関数ならシンプルな上にこういう議論をしなくていいだけ楽ですよね)

回答
投稿日時: 17/10/16 16:22:31
投稿者: ブルー

質問者のchiakiさん
 
なぜ「=C2>30」という条件で空白セルに色がついてしまうのか理解できましたか?
 
C列の空白は数式の結果によるものですよね。
空白文字列「""」を使って「空白」にしています。
これは何も入力していない未入力セルと違い、データの型は「文字列」になります。
そして、ここがポイントですが、数値と文字列を大小比較すると、全ての数値よりも文字列の方が大きいと判断されるのです。
ですので、C列が「""」の場合は文字列ですから、
「=C2>30」だけでは「30より大きい」という条件が成立して色がついてしまうわけです。
 
それを回避する方法の一つが、条件付き書式の「次の値の間」で経過日数の範囲を指定することです。
日数の設定なんですから、素直に経過日数の範囲を指定すればいいんです。
一人で議論して、一人で大騒ぎしている御仁がいますが、何も難しく考える必要はありません。
上限を「10,000」にすれば二つの日付の間隔が約27年、
「100,000」にすれば約270年あってもカバーできます。
これだけあれば十分ではありませんか?
 
ちなみにExcelの仕様では日付の最小値は通常「1900/1/1」、最大値は「9999/12/31」です。
経過日数の仕様上の最大値はその二つの日付の差「2,958,464」になります。
まあ、500万とか1000万とか、それを超えた数値を指定しても何も問題はありませんけどね。
 
と書いてきましたが、特にこの方法を推奨しているわけではありません。
数式を使わずにできる方法もありますよ、ということです。
色をつけるのがC列だけなら、数式を使う方法と比べてもわかりにくいとは全く思わない、
ということも付記しておきます。
 
最後に数式を使う場合のことも簡単に触れておくと
 
=AND(C2<>"",C2>30)
 
これは「C2が空白ではなく、かつ30より大きい」として、C列が空白の場合を除外。
 
=SUM(C2) と =N(C2) は、どちらもC2が「""」の場合「0」になります。
SUM関数は文字列を無視して合計、N関数は文字列を「0」に変換するからです。
 
=SUM(C2)>30
=N(C2)>30
 
C列が文字列「""」でも「30より大きい」とはならないので色はつきません。

投稿日時: 17/10/18 03:57:42
投稿者: chiaki

回答いただいた皆さま、ありがとうございました。
取り急ぎ、投稿翌日朝にbiさんの回答を確認し
=AND(C2<>"",C2>30) を使用させていただいておりました。
会社ではネットができず、家ではExcelに向かう時間がなかなか取れず、
返信が遅くなり大変申し訳ありません。
 
他の回答も試してみましたが、それぞれ望む結果が得られて、
ブルーさんの解説が大変理解の助けになりました。
経過日数は3年を超えることはないので、範囲の指定も分かりやすいと、発想の転換もできました。
 
たくさんの回答本当にありがとうございました。