[[20221104091358]]　『条件付き書式による3行ごとの色付け設定について』（ログ）　ページの最後に飛ぶ

『条件付き書式による3行ごとの色付け設定について』（ログ）

条件付き書式による色付けの設定について、教えてください。
日曜日から始まるカレンダーを以下のように作成しています。
B列が日曜日、H列が土曜日としてまして、以下のように日付が入力されたセルを含む3行ごとに色を付けたいと思っています。
例えば、今月11月ですと、以下のようになります。今月ですと、B21には日付は入らないので、その場合は色は付けないことにしたいです。
出来れば、祝日(3日と23日)にも同様に色を付けたいです。別シートに祝日の一覧表を作成してまして、その範囲名を「祝日」としています。
どうぞよろしくお願いします。

___B___C___D___E___F___G____H
6___________________3________5
7
8
9___6_______________________12
10
11
12_13_______________________19
13
14
15_20___________23__________26
16
17
18_27_____________
19
20
21___
22
23

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 条件付き書式の数式を使用して書式設定するセルを決定でB6セルからH21セルを選択して

 =AND(B6<>"",COUNTIF(祝日,B6))     祝日の色
 =COUNT($B6:$H6)                   3行ごとの色
 と設定してはどうだろうか？
 なお、ダイアログで上記の順番になるよう設定してくれ。
（ねむねむ） 2022/11/04(金) 09:33:50

 祝日の一覧表に3、23と祝日の日のみが入力されていてそれに祝日と名前が付けられているとする。
（ねむねむ） 2022/11/04(金) 09:36:04

ねむねむ様、ご返事ありがとうございます。
早速、ご指示通りに設定してみましたが、まず、3行ごとに色が付かないのと、祝日にも色が付きません。条件として、「祝日の一覧表に3、23と祝日の日のみが入力されていてそれに祝日と名前が付けられている」は満たしています。
また、日曜日の3行ごとの色がピンクなら、土曜日は水色にしたいのですが・・・
今一度、よろしくお願いします。
（ログ） 2022/11/04(金) 10:21:48

 カレンダー上の日付部分は手入力だろうか？
 式が入っているのだろうか？
 もし、式が入っているのであればその式を示してみてくれ。
（ねむねむ） 2022/11/04(金) 10:34:45

ねむねむ様、以下のように設定しています。

B6:H6=IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=$B$5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),""))
B9:H9、B12:H12、B15:H15=H6+1
B18:H18=IF(ISERR(DATE(YEAR(H15),MONTH(H15),DAY(H15)+1)=TRUE),"",H15+1))
B21:C21=IF(ISERR(DATE(YEAR(H18),MONTH(H18),DAY(H18)+1)=TRUE),"",H18+1))
（ログ） 2022/11/04(金) 10:54:56

 B2セルに年度、B3セルに月が数値で入っているとしてその式でカレンダーが表示されるだろうか？

 もしB6:H6の式が
 =IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=B$5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),""))
 と$B$5がB$5だったとしてもD6セルに表示されるだけだが。

 あと日付部分は1、2、3、…と日の数値ではなく表示形式で日にちだけ表示している日付ということだろうか？
（ねむねむ） 2022/11/04(金) 11:15:12

ねむねむ様、ありがとうございます。

＞B2セルに年度、B3セルに月が数値で入っているとしてその式でカレンダーが表示されるだろうか？
その通りです。

＞もしB6:H6の式が =IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=B$5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),""))

 と$B$5がB$5だったとしてもD6セルに表示されるだけだが。
すみません。正確には、以下の通りです。
B6=IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=$B$5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),""))
C6=IF(B6<>"",B6+1,IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=C5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),"")))
D6=IF(C6<>"",C6+1,IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=D5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),"")))
E6=IF(D6<>"",D6+1,IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=E5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),"")))
F6=IF(E6<>"",E6+1,IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=F5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),"")))
G6=IF(F6<>"",F6+1,IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=G5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),"")))
H6=IF(G6<>"",G6+1,IF(CHOOSE(WEEKDAY(DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)),"日","月","火","水","木","金","土")=H5,DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1),"")))

＞あと日付部分は1、2、3、…と日の数値ではなく表示形式で日にちだけ表示している日付ということだろうか？
その通りです。表示形式「d」です。
（ログ） 2022/11/04(金) 12:27:45

 日付部分だが、当月でない部分(先月、翌月)は空白("")でないとだめだろうか？
 書式設定で見えなくしているだけでも構わないだろうか？
（ねむねむ） 2022/11/04(金) 13:06:00

ねむねむ様、度々ありがとうございます。
日付部分は、空白でなくとも書式設定で見えなくする形でも構いません。
（ログ） 2022/11/04(金) 13:17:20

 まず数式を以下のようにする。

 B6セル :=DATE($B$2+($B$3<4),$B$3,1)-WEEKDAY(DATE($B$2+($B$3<4),$B$3,1),1)+1
 B9セル :=B6+7
 B12セル:=B9+7 
 B15セル:=B12+7
 B18セル:=B15+7
 B21セル:=B18+7

 C6セル :=B6+1
 C9セル :=B9+1
 C12セル:=B12+1 
 C15セル:=B15+1
 C18セル:=B18+1
 C21セル:=B21+1
 と入力してC6セルからC21セルを横へコピー。

 次にB6セルからH21セルを選択して条件付き書式で
 =AND(B6<>"",TEXT(B6,"aaa")="日",MONTH(B6)=$B$3)            日曜日の色
 =AND(B6<>"",TEXT(B6,"aaa")="土",MONTH(B6)=$B$3)            土曜日の色
 =COUNTIF(祝日,B6)                                          休日の色
 =AND(SUMPRODUCT((MONTH($B6:$H6)=$B$3)*1),MOD(ROW(),3)=0)   3行ごとの色
 =MONTH(B6)<>$B$3                                           書式の表示形式で分類にユーザー定義、種類に;;;

 ではどうだろうか？
（ねむねむ） 2022/11/04(金) 13:57:55

ねむねむ様、ありがとうございます。
以下だけが、3行ごとに色が付かず、日付の入った行のみがB～H列まで色が付いてしまいます。
=AND(SUMPRODUCT((MONTH($B6:$H6)=$B$3)*1),MOD(ROW(),3)=0) 3行ごとの色
また、月を12にした場合、B21とC21に日曜日の色が付いてしまいます。12月の日付そのものは入っていないですが・・・
（ログ） 2022/11/04(金) 14:26:45

 行に一件も日付がなくても色を付けるということであれば
 =MOD(ROW(),3)=0
 で試してみてくれ。

 >また、月を12にした場合、B21とC21に日曜日の色が付いてしまいます。
 こちらは見当がつかず。

 上記とは関係ないが修正。
 >C6セル :=B6+1
 >C9セル :=B9+1
 >C12セル:=B12+1 
 >C15セル:=B15+1
 >C18セル:=B18+1
 >C21セル:=B21+1
 これは必要なく、B6セルからB21セルを右へコピーで。

（ねむねむ） 2022/11/04(金) 14:39:12

 何度も済まない。
 C6セルだけは
 =B6+1
 として横へコピーしてくれ。
（ねむねむ） 2022/11/04(金) 14:58:48

ねむねむ様、ありがとうございます。
=MOD(ROW(),3)=0を設定しましたが、やはり、3行に色が付きません。
ただ、私の説明が不足しているように思います。改めて、ご説明すると、

日曜日、土曜日、祝日は、何もデータを入力していなくても無条件に、3行すべて色を付けたいのです。
現状は、何かしらのデータを入力すると色が付くようになっています。
（ログ） 2022/11/04(金) 15:05:26

 その場合であれば
 >=AND(B6<>"",TEXT(B6,"aaa")="日",MONTH(B6)=$B$3)            日曜日の色
 >=AND(B6<>"",TEXT(B6,"aaa")="土",MONTH(B6)=$B$3)            土曜日の色
 を
 =AND(B6<>"",TEXT(B6,"aaa")="日")            日曜日の色
 =AND(B6<>"",TEXT(B6,"aaa")="土")            土曜日の色
 としてみてくれ。

 というか行の色、および土日の色は通常のセルの書式設定で設定し、祝日のみ条件付き書式を使うのでもいいのでは？
（ねむねむ） 2022/11/04(金) 15:10:48

＞B6セル :=DATE($B$2+($B$3<4),$B$3,1)-WEEKDAY(DATE($B$2+($B$3<4),$B$3,1),1)+1
($B$3<4)?
（はてな） 2022/11/04(金) 16:28:56

 >＞B2セルに年度、B3セルに月が数値で入っているとしてその式でカレンダーが表示されるだろうか？
 >その通りです。
 年ではなく年度なので2022年度1月は2023年1月。

 最初のログさんの式でも
 >DATE(IF(OR($B$3=1,$B$3=2,$B$3=3),$B$2+1,$B$2),$B$3,1)
 と月が1、2、3の時に年度に1を足している。
（ねむねむ） 2022/11/04(金) 16:35:11

ログさんの式よく見てませんでした。
失礼しました。
（はてな） 2022/11/04(金) 16:57:03

ねむねむ様、ご返事が遅くなりすみません。
2022/11/04(金) 15:10:48にて、日曜日と土曜日の3行単位での色付け設定の変更をいただいたのですが、
どうしても3行ごとに色が付きません。
例えば、2022年11月ですと、以下の日付が日曜日になりますので、それぞれ3行がすべて色を付けたいのです。つまりは、B9:B20までが色付けにしたいのです。
6日(B9:B11)
13日(B12:B14)
20日(B15:17)
27日(B18:20)
土曜日ですと、以下の日付が土曜日になりますので、それぞれ3行がすべて色を付けたいのです。つまりは、H6:H17までが色付けにしたいのです。
5日(H6:H8)
12日(H9:H11)
19日(H12:14)
26日(H15:H17)
祝日も現状の設定ですと、日付が入った1行のみしか色が付きません。これも同様に3行すべて、11月ですと、3日(F6:F8)、23日(E15:E17)のそれぞれ3行すべてに色付けしたいのです。
（ログ） 2022/11/06(日) 21:24:25

追伸
2022年12月とした場合、B21とC21にも色が付いてしまいます。12月の日付は入っていないのに。
これは、祝日マスタに2023年1月1日(元旦)と2023年1月2日(振替休日)を入れているから？だと思われるのですが、あくまで12月のみに色を付けたいというものなので、色が付かないようにしたいのですが…。
（ログ） 2022/11/06(日) 21:36:27

 すまない。
 3行ごとを6、9、12・・と3行おきにと読んでいた。

 B6セルからH23セルを選択して
 =AND(MONTH(SUM(OFFSET(B6,-MOD(ROW(),3),)))=$B$3,TEXT(SUM(OFFSET(B6,-MOD(ROW(),3),)),"aaa")="土")
 土曜日の色

 =AND(MONTH(SUM(OFFSET(B6,-MOD(ROW(),3),)))=$B$3,TEXT(SUM(OFFSET(B6,-MOD(ROW(),3),)),"aaa")="日")
 日曜日の色

 =AND(MONTH(SUM(OFFSET(B6,-MOD(ROW(),3),)))=$B$3,COUNTIF(祝日,SUM(OFFSET(B6,-MOD(ROW(),3),))))
 休日の色

 ではどうだろうか？
（ねむねむ） 2022/11/07(月) 10:00:54

 >3行ごとを6、9、12・・と3行おきにと読んでいた。
 同感です。

 文章だとややこしいので図にしてみました。

     |[A]|[B]   |[C]|[D]|[E]   |[F]  |[G]|[H]   
 [1] |   |      |   |   |      |     |   |      
 [2] |   |  2022|   |   |      |     |   |      
 [3] |   |    11|   |   |      |     |   |      
 [4] |   |      |   |   |      |     |   |      
 [5] |   |日    |月 |火 |水    |木   |金 |土    
 [6] |   |×　30| 31|  1|     2|○　3|  4|○　5 
 [7] |   |×    |   |   |      |○　 |   |○　  
 [8] |   |×    |   |   |      |○　 |   |○　  
 [9] |   |○　6 |  7|  8|     9|   10| 11|○　12
 [10]|   |○　  |   |   |      |     |   |○　  
 [11]|   |○　  |   |   |      |     |   |○　  
 [12]|   |○　13| 14| 15|    16|   17| 18|○　19
 [13]|   |○　  |   |   |      |     |   |○　  
 [14]|   |○　  |   |   |      |     |   |○　  
 [15]|   |○　20| 21| 22|○　23|   24| 25|○　26
 [16]|   |○　  |   |   |○　  |     |   |○　  
 [17]|   |○　  |   |   |○　  |     |   |○　  
 [18]|   |○　27| 28| 29|    30|    1|  2|×　3 
 [19]|   |○　  |   |   |      |     |   |×　  
 [20]|   |○　  |   |   |      |     |   |×　  
 [21]|   |×    |  5|  6|     7|    8|  9|×　10
 [22]|   |×    |   |   |      |     |   |×　  
 [23]|   |×    |   |   |      |     |   |×　  
 [24]|   |      |   |   |      |     |   |

 最初からこういう図で説明すればよかったのではと思います。

（はてな） 2022/11/07(月) 10:21:10

そうですね。はてな様がおっしゃるとおり説明が出来てませんでした。すみません。

ねむねむ様、ありがとうございます。
見事に3行ごとの色付けが土、日、祝日ともに出来るようになりました。
ただ、自分自身のミスですが、日曜日はB列、土曜日はH列のみの範囲指定でよかったですよね。また、土曜日のOFFSET(B6ではなく、H6でよかったですよね。
理想的なカレンダーになりました。ねむねむ様、再三の修正をありがとうございました！
（ログ） 2022/11/07(月) 12:20:45

 おっと、そこは最初日付行部分にも色を塗っておいて土日祝日を別の色で塗ると勘違いしていたためどうせなら
 選択範囲をそろえていたほうがいいかと考えていた時のままになっていた。

 きちんと確認していなくてすまない。
（ねむねむ） 2022/11/07(月) 12:25:13

ねむねむ様、一つ問題が発生しました。
2022年1月の場合、最終土曜日は28日(H15:H17)なのですが、H18:H20まで色が付いてしまいます。
ちなみに、2023年、2024年・・・も同じように色が付いてしまいます。
他の月はきちんと日付があるセルのみに色が付いてくれます。1月だけがなぜか当月の日付がないセルに色が付いてしまいます。
今一度、よろしくお願いしします。
（ログ） 2022/11/29(火) 07:16:57

 こちらでもテストしてみたが再現できなかった。
 いろいろ回答がごちゃごちゃしているのでまとめてみたので再度確認してみてほしい。

 1.日付欄の数式
 　B6セル：=DATE($B$2+($B$3<4),$B$3,1)-WEEKDAY(DATE($B$2+($B$3<4),$B$3,1),1)+1
 　C6セル：=B6+1
 　C6セルをH6セルまでフィルコピー

   B9セル :=B6+7
   B12セル:=B9+7
   B15セル:=B12+7
   B18セル:=B15+7
   B21セル:=B18+7
 　B9セルからB21セルをH列までフィルコピー

 2.条件付き書式
 　B6セルからH21セルまでを選択して
 　=MONTH(B6)<>$B$3
 　書式の表示形式で分類にユーザー定義、種類に;;;

 　B6セルからB23セルを選択して
 　=AND(MONTH(SUM(OFFSET(B6,-MOD(ROW(),3),)))=$B$3,TEXT(SUM(OFFSET(B6,-MOD(ROW(),3),)),"aaa")="日")
 　日曜日の色で塗りつぶし

 　H6セルからH23セルを選択して
 　=AND(MONTH(SUM(OFFSET(H6,-MOD(ROW(),3),)))=$B$3,TEXT(SUM(OFFSET(H6,-MOD(ROW(),3),)),"aaa")="土")
 　土曜日の色で塗りつぶし
（ねむねむ） 2022/11/29(火) 09:38:22

 一応考えられることとしては式の入っていない空白セルを無理やり日付とすると
 空白セルを0と認識して1900/1/0(土)とみなし条件に合致するが21行目ならともかく
 18行目ならば必ず式があるはずだし…
（ねむねむ） 2022/11/29(火) 09:45:04

 >こちらでもテストしてみたが再現できなかった。
 同感です
 たまたま 2022/11/07(月) 10:21:10 で作成した表が残っていたので確認してみました。

（はてな） 2022/11/29(火) 12:09:22

 回答ではありません。

 　↓ も同じカレンダーなんですか？
[[20221127072516]]『データの入力規則がうまくいきません』（ログ）

 日付が表示されていないのは、空白ではなく表示形式で見えなくしているだけ？

 だったら入力規則の数式を　=B$18<>""　としてもダメですけどね。
 空白じゃないんだから。

 それとも全く別の話？

 以上、確認だけ
（笑） 2022/11/29(火) 16:57:44

皆さん、ありがとうございます。ご返事が遅くなり、すみません。

ねむねむ様、2022/11/29(火) 09:38:22への確認で一点だけ修正というかあります。
「H6セルからH23セルを選択して」とありますが、このカレンダーの場合、H21には当月の日付が入ることはあり得ないので、「H6セルからH20セルを選択して」でよいですよね？

笑様、おっしゃりとおり、[[20221127072516]]このカレンダーと同じ質問でした。
＞日付が表示されていないのは、空白ではなく表示形式で見えなくしているだけ？
最初はそうでした。しかし、[[20221127072516]]ここでお尋ねしておりますように、日付セルが空白の場合は入力を不可にしたいというところから、条件付き書式で見えないようにするだけでは、実現しないため、当月の日付ではない場合は空白(="")にするようにしています。

ちなみに、H18の数式を消して何も関数が入っていないようにしても、やはり、1月は色が付いてしまいます。。。
他に確認すべき箇所があるでしょうか？どうぞよろしくお願いします。
（ログ） 2022/11/30(水) 07:14:50

先ほどの回答の補足です。
H18=IF(G18="","",IF(MONTH(G18+1)=$B$3,G18+1,""))としています。
（ログ） 2022/11/30(水) 07:32:17

 >（ねむねむ） 2022/11/29(火) 09:38:22 の式は確認されましたか。
 それについての結果を先に行うべきではないでしょうか。
「先ほどの回答の補足です。」ではないですけど他にも式を変更しているところがあるのではないのですか。
 >日付セルが空白の場合は入力を不可にしたいというところから、条件付き書式で
 入力を不可にしたいのなら入力規則ですよ。
 >当月の日付ではない場合は空白(="")にするようにしています。 
 当月はいいかもしれませんが翌月(12月)になると31日が表示されませんよ。
 >1月は色が付いてしまいます。。。
 条件書式の式が正しくないと思います。

（はてな） 2022/11/30(水) 09:53:43

 H列の条件付き書式の数式

 ＞…… ),"aaa")="土")

 　…… ),"aaa;;")="土")
           ~~~~~~
 これでどうなりますか？（aaa の後にセミコロン「;」を2つ付ける）

 以上
（笑） 2022/11/30(水) 10:49:55

笑様のご指示どおり、H列の条件付き書式の数式にセミコロン「;」を2つ付けたところ、改善されました！
見事に土曜日の日付がないセルには色が付かなくなりました！素晴らしい！！
ありがとうございました。皆様、ありがとうございました。
（ログ） 2022/11/30(水) 18:30:34

コメント返信：

[ 一覧(最新更新順) ]

[[20221104091358]] 『条件付き書式による3行ごとの色付け設定について』（ログ） ページの最後に飛ぶ

[[20221104091358]]　『条件付き書式による3行ごとの色付け設定について』（ログ）　ページの最後に飛ぶ