[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件付き書式による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
カレンダー上の日付部分は手入力だろうか? 式が入っているのだろうか? もし、式が入っているのであればその式を示してみてくれ。 (ねむねむ) 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
まず数式を以下のようにする。
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
行に一件も日付がなくても色を付けるということであれば =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
日曜日、土曜日、祝日は、何もデータを入力していなくても無条件に、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
>>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
すまない。 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
こちらでもテストしてみたが再現できなかった。 いろいろ回答がごちゃごちゃしているのでまとめてみたので再度確認してみてほしい。
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
>(ねむねむ) 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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.