行を挿入すると、色が付いてしまう

質問

excel2013です。
画像のような感じのカレンダーを作っています。
セルには、条件式書式で、上から、

=COUNTIF(祝日!$A$2:$A$54,$A791)>=1

=WEEKDAY(A791)=1

=WEEKDAY(A791)=7

を入力して、日曜・祝日は、文字色赤、土曜日は、文字色青、背景はどれも薄い青にしました。
そこで質問です。
行を挿入すると、土日祝日以外でも、上記の曜日のように、色が付いてしまいます。
なぜこのようになるのでしょうか？
また、解決するには、条件式書式をどのように変更するば良いのでしょうか？

回答No.5

kagakusuki · ベストアンサー

>あと、<>""　このれ意味がよくわかりません。

　「<>」は「=」ではないという意味です。
　物語や小説の中で登場人物が話しているセリフを表す際に「　」(かぎ括弧)で台詞の部分を括っているのと同じようなもので、Excelでは文字列データを指定する場合には「"」(ダブルクォーテーション)で文字列を括ります。
　「""」は２個の"の中に何も文字がありませんから、何も値が存在していない状態の事を表しています。
　例えば

A791=""

とした場合には、「A791セルの中に『何も値が存在していない状態』」である事を表します。
　そして

A791<>""

の場合は、「『A791セルの中に何も値が存在していない状態』以外の状態」である事を表す事になり、即ち、「A791セルの中に何か値が入力されている状態」である事を表します。
　従って、例えば

=AND(A791<>"",WEEKDAY(A791)=7)

ですと、「『A791セルの中に何か値が入力されていて』尚且つ「WEEKDAY関数の結果が7(土曜日)」の場合にのみ、条件付き書式で設定した書式で表示するという事です。
　そのため、A791セルに何も入力されていない場合には、セルの塗りつぶしが行われない訳です。
　因みに、Excelではシリアル値と言って、「1900年１月１日を数値の1と置いて、その日を基点として経過した日数を加算した数値」で日付を表しています。
　A791セルに何も入力されていない状態でWEEKDAY関数等の日付を扱う関数による計算を行いますと、「A791セルに何も入力されていない」という事は「A791セルの値は0である」と見做して計算が行われる事になり、シリアル値が1の場合は1900年１月１日を表すのですから、シリアル値の0はその1日前である1899年12月31日(土曜日)を表す事になりますので、

=WEEKDAY(A791)=7

だけでは空欄のセルも土曜日と見做されるため塗りつぶされてしまっていたのです。

> =AND(A791<>"",COUNTIF(祝日!$A$2:$A$54,$A791))
> =AND(A791<>"",WEEKDAY(A791)=7)
> =AND(A791<>"",WEEKDAY(A791)=1)

>これで、うまく行きました。

　日曜日と祝日の書式が同じ書式でしたので、回答No.2では

=AND(A791<>"",OR(WEEKDAY(A791)=1,COUNTIF(祝日!$A$2:$A$54,A791)))

という具合に、日曜日と祝日の条件付き書式を一つにまとめておいたのですが、そちらは上手く行かなかったのでしょうか?

お礼

ありがとうございました。
「<>""」の説明、書いてある場所を探せませんでした。正確にはあったのですが、理解出来ないくらい簡単な記述なので、チンプンカンプンでした。
まあ、初歩過ぎるのかも？

＞シリアル値の0はその1日前である1899年12月31日(土曜日)を表す事になりますので、

目からうろこです。例えば、土曜日の条件付書式を設定していなければ、私の疑問は生まれなかったのがわかりました。

＞日曜日と祝日の条件付き書式を一つにまとめておいたのですが、そちらは上手く行かなかったのでしょうか?

祝日のみ、塗りつぶしの色を変えたいなと、思ったからです。

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

補足

ありがとうございました。
1900年１月１日（日）は、日曜日で、その前のセルは、1900年1月0日（土）になるので、日付は間違っているが、曜日は土曜日と認識される事に気付きました。
また、この年の閏年の件も勉強になりました。

回答No.4

bunjii

>なぜこのようになるのでしょうか？
バグかも知れません。
土曜日の指定についてのみ未入力セルに塗りつぶしが発生します。
=WEEKDAY(A791)=7、WEEKDAY(A791,1)=7、WEEKDAY(A791,2)=6等でも同じ結果になり、WEEKDAY(A791)=1では未入力セルへの影響がないようです。
回避するには=AND(A791<>"",WEEKDAY(A791)=7)に変更すれば良いでしょう。
尚、同じ条件の書式を適用する場合は条件の数式をOR関数で括れば1つの指定で目的が達成できます。
=OR(WEEKDAY(A791)=1,COUNTIF(祝日!$A$2:$A$54,$A791))で日曜と祝日を兼ねられます。

お礼

ありがとうございました。
参考になりました。

回答No.3

chie65536（@chie65535）

追記と訂正。

祝日表の未使用の部分を何かで埋めただけでは、解決しません。

解決するには、祝日表はそのままで構わないので、祝日と土曜の条件式を

=COUNTIF(祝日!$A$2:$A$54,$A791)>=1
↓
=(COUNTIF(祝日!$A$2:$A$54,$A791)*NOT(ISBLANK($A791)))>=1

=WEEKDAY(A791)=7
↓
=(WEEKDAY(A791)*NOT(ISBLANK($A791)))=7

に変えて下さい。

祝日は、祝日表に空白セルがあると困るので、空白セルを考慮しないといけません。

土曜は、空白セルをWEEKDAY関数に渡すと「土曜日」と判断されるので、空白セルを考慮しないといけません。

日曜の条件式は変更しなくても構いません。

空白セルをWEEKDAY関数に渡しても「1」にはならないので、日曜は「空白セルは考慮しなくて良い」のです。

また、他の回答のように「祝日と日曜を１つにする」って手もありますが、１つにしちゃうと、あとで「祝日と日曜を別な色にしよう」と思った時に困るので、１つにしない方が良いでしょう。後で違う書式にしたくなった時に困ります。

お礼

ありがとうございました。
参考になりました。

回答No.2

kagakusuki

文字色赤にする条件を

=AND(A791<>"",OR(WEEKDAY(A791)=1,COUNTIF(祝日!$A$2:$A$54,A791)))

文字色青にする条件を

=AND(A791<>"",WEEKDAY(A791)=7)

にされると良いと思います。

補足

皆様ありがとうございました。皆様のアドバイス元に、出来ました。

=AND(A791<>"",COUNTIF(祝日!$A$2:$A$54,$A791))
=AND(A791<>"",WEEKDAY(A791)=7)
=AND(A791<>"",WEEKDAY(A791)=1)

これで、うまく行きました。「祝日」は、三年分入力して、年度の間を空欄にしましたが、特に問題無かったです。
あと、<>""　このれ意味がよくわかりません。

回答No.1

chie65536（@chie65535）

祝日の表（祝日!$A$2:$A$54の表）の中に「何も入力されてない空白セル」がある筈です。

で、行挿入すると、挿入された行は「空白セル」になっているので、条件付書式の

=COUNTIF(祝日!$A$2:$A$54,$A791)>=1

に一致します。

つまり「行挿入して何も入力されてない行は、祝日扱いになってしまう」のです。

祝日が53個あるなら、祝日!$A$2:$A$54の表は全部埋まって「空白のセル」は出来ませんが、祝日は53個も無いですから、後半部分は「空欄のまま、何も入力されてない筈」です。

空白セルが祝日と同じ扱いになっちゃうのは、この「祝日表の中にある空白セル」が原因なので、表の後半の余った部分には「適当な何かの文字」でも入れて、空白セルにならないようにしましょう。

祝日の表の例

A2 1月1日(木)
A3 1月12日(月)
A4 2月11日(水)
A5 3月/21日(土)
A6 4月29日(水)
A7 5月3日(日)
A8 5月4日(月)
A9 5月5日(火)
A10 5月6日(水)
A11 7月20日(月)
A12 9月21日(月)
A13 9月22日(火)
A14 9月23日(水)
A15 10月12日(月)
A16 11月3日(火)
A17 11月23日(月)
A18 12月23日(水)
A19 なし
A20 なし
A21 なし
(中略)
A50 なし
A51 なし
A52 なし
A53 なし
A54 なし

上記のように、祝日表の「後半の余った部分」に「なし」など、何かの文字を入れておけば、問題が解決します。

ただし「行挿入した所」に「なし」と書くと「祝日の表の中の『なし』と言う文字列と一致して、祝日と同じ扱いになる」ので、注意して下さい。