エクセルの条件付き書式の質問です。

エクセルの条件付き書式で第１、第３土曜日を指定して色を変えたいのです。
他の質問板ですでにあった回答を頂いて入力したのですが、一部分だけ第１、第３土曜日ではないところの色が変わってしまいます。
詳しい方いらっしゃいましたらお教えいただけると嬉しいです。

条件付き書式の式：「　=AND(WEEKDAY(D5)=1,MOD(INT((DAY(D5)-1)/7),2)=0,DAY(D5)<=21)　」
指定範囲：=$D$5:$AG$5,$D$14:$AG$14,$B$18:$AG$18,$D$23:$AG$23,$B$27:$AG$27,$B$36:$AG$36,$D$41:$AG$41,$B$45:$AG$45,$D$50:$AG$50,$B$54:$AG$54,$B$9:$AG$9,$D$32:$AG$32

質問者からの補足コメント

• 

  ちなみに日曜日の式は
  =WEEKDAY(B5)=1
  で、
  土曜日の式が
  =AND(WEEKDAY(D5)=1,MOD(INT((DAY(D5)-1)/7),2)=0,DAY(D5)<=21)
  です。
  土曜日のWEEKDAY(D5)=1を本来のWEEKDAY(D5)=７にすると金曜日が指定されます、、、。

  
    補足日時：2016/01/21 11:40

• 

  WEEKDAY(D5)=７の時のSS

  
    補足日時：2016/01/21 11:44

• 

  いただいた式を両方ともお借りしました。
  どうしましょう、、、、。

  
  No.2の回答に寄せられた補足コメントです。 補足日時：2016/01/21 11:53

• エクセルのファイルです。
  http://tikuwa.net/files/10587.zip.html
  pass:紅茶

  No.5の回答に寄せられた補足コメントです。 補足日時：2016/01/21 14:37

• 

  エクセル再インストールしたほうがいいでしょうか、、、、

    補足日時：2016/01/21 16:54

• X9をD5に変更したデータです。
  http://tikuwa.net/files/10591.zip.html
  pass:紅茶

    補足日時：2016/01/21 17:10

No.8 ベストアンサー 回答者： t_fumiaki 回答日時： 2016/01/21 21:03

>X9をD5に変更したデータです。

>http://tikuwa.net/files/10591.zip.html
>pass:紅茶

これってD5は2015/12/25の月曜日なんだから、色変わらなくて良いと思うよ。第一、第三土曜日だけ色を変えたいんでしょう？？

この回答へのお礼

親身になって相談に乗っていただいてありがとうございます。
長々のお付き合いになってしまい申し訳ないです。

D5が土曜日や日曜日の年も来ると思いまして、全体を範囲にすれば毎年使えるかと思ったのです、、。

２月からの日付については2016年2月1日分は月曜日であっていると思うのですが、、、。

とりあえず今回私がやりたかったことは

・年度を入力すればその年の日にち、曜日が自動的にでてくる
・土曜日（第１・３がお休み：青）、日曜日（赤）、祝日（お正月休みや夏休みは毎年取引先の都合で変わるので毎年手入力の予定：オレンジ色）の色を替える
・１箇所のシートにデータ（勤務開始・終了、出先、代休使用の場合いつ分の代休か）を入力しておけば、印刷用のシートに「だれ分」のかを選べば、自動でデータがでてくる
・印刷用は２１日スタートの２０日〆
でした。

わからないな中で詰め込み過ぎたようなので、もっと理解できるようになってからまたチャレンジしようと思います。

お礼日時：2016/01/22 09:08

No.7 回答者： t_fumiaki 回答日時： 2016/01/21 20:45

t_fumiakiです

そもそも論なのですが、２月からの日付と曜日が「おととい」なんですよ。
年や曜日が元々でたらめなんですけど。

最初から「なんだ？これは！」と思っていました。

No.6 回答者： t_fumiaki 回答日時： 2016/01/21 16:16

補足に有ったエクセルをダウンロードして見ました。

私の関数で上手く行きましたよ。
条件付書式を使わないと文字色が変わらないので条件付書式が設定して有りました。

そこに式を入れて、「数式が」にして条件に以下を入れて下さい。
（trueなら文字色を変更したいんですよね。）

例は1月16日
x9、y9に以下の式を入れる
=(OR((7-WEEKDAY(DATE(YEAR(X9),MONTH(X9),1)))+DATE(YEAR(X9),MONTH(X9),1)=X9,
(7-WEEKDAY(DATE(YEAR(X9),MONTH(X9),1)))+DATE(YEAR(X9),MONTH(X9),1)+14=X9))

この回答へのお礼

わざわざお手数頂きありがとうございます。
X9とY9のセルに、頂いた式を入れたのですが、循環参照というのが出てきてしまいました。

条件付き書式設定の「▶数式を使用して、書式設定するセルを決定」の数式を入れる欄に、頂いた数式をそのままいれました。
５月分の２２日（４月２２日）だけ青く変わりました。

頂いた数式のX9をD5に変更して「▶数式を使用して、書式設定するセルを決定」の数式を入れる欄に入れました。
第１、３金曜日の色が変わって、２月分だけ第１金曜日だけ色が変わりました。

お礼日時：2016/01/21 16:49

No.5 回答者： t_fumiaki 回答日時： 2016/01/21 12:50

NO4回答への補足

条件式ではTRUEなら１、FALSEなら０を出力しているので、適宜修正して下さい

No.4 回答者： t_fumiaki 回答日時： 2016/01/21 12:47

前質問に最後に回答した内容を再度転記します。

要は、その日付が第一土曜か第三土曜かが判定できれば良いのでしょう？
前質問では日付格納セルがC6だったので、そのまま記載。
適宜修正して下さい。

=IF(OR((7-WEEKDAY(DATE(YEAR(C6),MONTH(C6),1)))+DATE(YEAR(C6),MONTH(C6),1)=C6,
(7-WEEKDAY(DATE(YEAR(C6),MONTH(C6),1)))+DATE(YEAR(C6),MONTH(C6),1)+14=C6),1,0)

No.3 回答者： 銀鱗 回答日時： 2016/01/21 11:56

あ…なんだ。

そういうことか。
日付が ”日” しか入っていないということではないだろうか。

「2016年4月1日」のように、「年月日」で日付を入力し、
「セルの書式設定」のユーザー書式で ”d” を指定して日付だけ。
「セルの書式設定」のユーザー書式で ”aaa” を指定して曜日（曜日の日月火水木金土の部分だけ）。
…のようにしてみよう。

DAY関数やWEEKDAY関数は「年月日」を示すシリアル値を変換するのだ。

たぶんこれが意図しない状態になる原因ではないかと思う。

この回答へのお礼

日付は、年度を入力する所（今は2016/1/1と入力してます）から引っ張っていて

今年の１月分最初の日付　2015/12/21の箇所に入力した年度－１１（書式設定はdになっていました)

翌日からは2015/12/22には前日＋１で入力を１月分終わり（2016/1/20まで)とつづけています。

年度を入力している所→A1
2015/12/21→セル位置(D5)→中に入っている関数：=A1-11
2015/12/22→ 〃　 (F5)→　　　　〃　　　　：=D5+1　


2016/1/18→ 〃　 (AB9)→　　　　〃　　　　：=Z9+1　
2016/ 1/19→セル位置(AD9)→中に入っている関数：=IF(DAY(AB9)<>20,AB9+1,"")
2016/ 1/20→ 〃　 (AF9)→　　　　〃　　　　：=IFERROR(IF(DAY(AD9)=19,AD+1,""),"")

19、２０日のは3月などを想定して造りました。
いまのところ、日にちのところにはDAY,MONTH関数は１９、２０日にしかはいっていませんでした、、、。

お礼日時：2016/01/21 12:50

No.2 回答者： 銀鱗 回答日時： 2016/01/21 11:46

・第1曜日は１～７日の範囲

・第3曜日は15～21日の範囲
であるから、条件としては
１．土曜日であること。
　WEEKDAY(D5)=7
２．日付を14で割って余りが7以下になること。
　MOD(DAY(D5),14)<=7
３．第5曜日を除外するため28日以下の日付であること。（21日以下としてもよい）
　DAY(D5)<=28
の3つを満たせば良いということになるので、
＝AND(WEEKDAY(D5)=7 , MOD(DAY(D5),14)<=7 , DAY(D5)<=28)
となるのではないだろうか。

日曜日も同様に「WEEKDAY(D5)=7」の部分を「WEEKDAY(D5)=1」にするだけだ。
前に答えてくれた人は２つ目の条件をちょっと複雑に考えすぎたのだろうと思う。

この回答へのお礼

式を解りやすくして頂きありがとうございます！

ですが、うまくいきませんでした、、、。

他のページではうまくいっているんですが、、、、。

お礼日時：2016/01/21 11:55

No.1 回答者： 銀鱗 回答日時： 2016/01/21 11:27

=AND(WEEKDAY(D5)=1,MOD(INT((DAY(D5)-1)/7),2)=0,DAY(D5)<=21)

WEEKDAY(D5)=1
　これは日曜日！！
土曜日に割り付けた条件をもう一度貼り直さないと問題を解決できないと思う。

この回答へのお礼

早速の回答有り難うございます。
失礼しました。
書き忘れていましたが、なぜかWEEKDAY(D5)=７だと金曜日が選ばれるんです、、、。

お礼日時：2016/01/21 11:33