Excel2010第二、第四土曜日に〇印を

質問

教えてください。
図のようにカレンダーがありE1より月を取得し、D1～AH31まで日付を表示しています。今５行目に第一、第四土曜日に図のように〇印を付加を考えています。どのような記述になるのかご指導いただければ幸いです。

回答No.8

tom04 · ベストアンサー

続けてお邪魔します。

補足の件について

>(WEEKDAY($D2:D2)=7)*1)<=2)
＞$D2:D2の範囲指定しているのがよくわかりません。

これは　SUMPRODUCT関数内に入れています。
すなわち、D列から右へオートフィルでコピーするたびに範囲指定内に「土曜日」はいくつあるか？
を調べているだけです。

どこか使っていない行に
=SUMPRODUCT((WEEKDAY($D2:D2)=7)*1)
という数式を入れ列方向にオートフィルでコピーしてみてください。

「土曜日」が最初に出現すると「1」が7個続けて表示され、
2番目に出現したセル以降は「2」が7個続けて表示されるはずです。

そしてAND条件で
その数が「偶数」の場合　なおかつ、そのセルが土曜日のセルに「○」を表示させています。
これが最初に投稿した第二・第四土曜の場合の数式でした。

※　同様に第一・第二週の場合の数式は
=IF(AND(WEEKDAY(D2)=7,SUMPRODUCT((WEEKDAY($D2:D2)=7)*1)<=2),"○","")
となる訳です。

今まで投稿した数式は上記の意味でした。

参考になりますかね？m(_ _)m

補足

大変参考になりました。ありがとうございました。今後もよろしくお願いします。

回答No.7

tom04

No.1です。

補足の件について・・・

＞今第一と第二土曜日を指定していますが
とありますが前回の数式は第二・第四土曜日の場合に「○」が表示される数式にしています。

2行目がシリアル値だという前提での数式でしたので
ISEVEN関数は偶数の場合に「TRUE」となります。
前回の数式の意味は2行目がWEEKDAY関数で土曜日、かつ　D列からWEEKDAY=7の数が
2・4・6・・・の場合に「○」を表示し、それ以外は空白に！という数式にしていました。

もし、3行目が文字列　=TEXT(D2,"aaa") のような数式を入れていらっしゃるのであれば
3行目を利用して
=IF(AND(D3="土",ISEVEN(COUNTIF($D3:D3,"土"))),"○","")
という数式でも同様の表示になると思います。

＞第六週に土曜日がない前提など、
に関しては今月のような場合は第6週まである月ですが、実際は6週目は日曜日もしくは月曜日までです。
すなわち土曜日で第6週になるコトはあり得ません。

補足の
＞今第一と第二土曜日を指定していますが
のように第一・第二土曜日を指定する場合は

＞=IF(AND(WEEKDAY(D2)=7,SUMPRODUCT((WEEKDAY($D2:D2)=7)*1)<=2),"○","")
もしくは3行目がTEXT関数で文字列になっていれば
＞=IF(AND(D3="土",COUNTIF($D3:D3,"土")<=2),"○","")

のようにすれば第一・第二土曜日に「○」が表示されます。

以上、長々と書きましたがこの程度でよろしいでしょうか？m(_ _)m

補足

回答ありがとうございます。第一第二は間違いでした。シリアル値と文字の場合と解説ありがとうございます。現在はシリアル値で行います。テキストも大いに役立ちます。
ところで、
>(WEEKDAY($D2:D2)=7)*1)<=2)
$D2:D2の範囲指定しているのがよくわかりません。しかも、$D2で固定し、D2でセルが進むにつれて増えていく。最初のWEEKDAY関数では、D2でセルが進むと増えています。
>=IF(AND(WEEKDAY(D2)=7
何分素人でご足労をおかけしますがよろしくお願いします。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

単純なところで、もう一案
D2セル 1 右へ[Ctrl]押しつつオートフィル(連番)
D3セル (毎月更新)土 右へオートフィル

ちょっと高度にしたいなら、
=TEXT(1*($A$1&$C$1&"年"&$E$1&"月"&D2&"日"),"aaa")
右へオートフィル

第２○曜日は、X月8日～14日の間
第４○曜日は、X月22日～28日の間であり、ダブることはないことから

土曜日なら
=IF(D3<>"土","",IF(AND(8<=D2,D2<=14),"○","")&IF(AND(22<=D2,D2<=28),"○",""))
土曜日でない場合は、空白。
日付が8から14だったら「○」 (つなげて)日付が22から28だったら、「○」

回答No.5

某HN クロメート(Chromate)（@CoalTar）

D2セル =1*(A1&C1&"年"&E1&"月1日")
E2セル =IF(MONTH(D2+1)=$E$1,D2+1,"")
右へオートフィル
D2:AH2セル範囲を選択し、[Ctrl]+[1]セルの書式設定 表示形式 d
(添付図は曜日がわかるように daaa)

D5セル =IF(OR($D2+{14,28}-WEEKDAY($D2)=D2),"○","")
右へオートフィル

説明文の
>５行目に第一、第四土曜日に
第一と第四ならなら
=IF(OR($D2+{7,28}-WEEKDAY($D2)=D2),"○","")

お礼

回答ありがとうございました。大変勉強になりました。今後もよろしくお願いします。

回答No.4

kagakusuki

今仮に、A1セルに「平成」等の元号が入力されていて、C1セルに和暦の年数、E1セルに月数が入力されているものとします。

　まず、D2セルに次の数式を入力して下さい。

=IF(ISNUMBER(($A$1&$C$1&"年"&$E$1&"月"&COLUMNS($D:D)&"日")+0),($A$1&$C$1&"年"&$E$1&"月"&COLUMNS($D:D)&"日")+0,"")

　次に、D2セルの書式設定の表示形式を、[ユーザー定義]の

d

として下さい。
　次に、D3セルに次の数式を入力して下さい。

=IF(ISNUMBER(D$2),TEXT(D$2,"aaa"),"")

　次に、以下の操作を行って下さい。

Excelウィンドウの[ホーム]タブをクリック
　　↓
D2セルとD3セルをまとめて範囲選択
　　↓
選択されているセル範囲を変えないまま、「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=WEEKDAY(D$2)=1

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=WEEKDAY(D$2)=7

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある青色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、D5セルに次の数式を入力して下さい。

=IF(AND(D$3="土",OR(COUNTIF($D$3:D$3,"土")=2,COUNTIF($D$3:D$3,"土")=4)),"○","")

　そして、D2～D5の範囲をコピーして、E2～AH5の範囲に貼り付けて下さい。

　以上です。

お礼

回答ありがとうございました。大変勉強になりました。
４行には別シートの祝日一覧を表示しています。
今後もよろしくお願いします。

回答No.3

okormazd

日付(D2以下)がシリアル値で、曜日(D3以下)がWEEKDAY関数でとっているなら、

D4に、

=IF(OR(DAY(D2-1)=7*2-$D$3,DAY(D2-1)=7*4-$D$3),"〇","")

として以下フィルすればいいんじゃないか。

お礼

回答ありがとうございました。大変勉強になりました。今後もよろしくお願いします。

回答No.2

noname#204879

添付図参照
D2: 書式 d
D2: 数式 =DATEVALUE($A1&$C1&$D1&$E1&$F1&COLUMN(A1)&"日")
D3: 書式 aaa
D3: 数式 =D2
D5: =IF(ISERROR(D2),"",IF(OR(D2=DATE(YEAR($D2),$E1,2*7-WEEKDAY(DATE(YEAR($D2),$E1,-5),3)),D2=DATE(YEAR($D2),$E1,4*7-WEEKDAY(DATE(YEAR($D2),$E1,-5),3))),"○",""))

範囲 D2:D3 に次の[条件付き書式]を設定
条件1 数式が　　　=ISERROR(MOD(D2,7))
　　　フォント色　白
条件2 数式が　　　=MOD(D2,7)=0
　　　パターン色　水色
条件3 数式が　　　=MOD(D2,7)=1
　　　パターン色　赤色

範囲 D2:D3 および セル D5 をズズーッと(AH列まで)右方へドラッグ＆ペースト

お礼

回答ありがとうございました。大変勉強になりました。今後もよろしくお願いします。

回答No.1

tom04

こんばんは！
一例です。

3行目はシリアル値が入っているものとして・・・

D5セルに
=IF(AND(WEEKDAY(D2)=7,ISEVEN(SUMPRODUCT((WEEKDAY($D2:D2)=7)*1))),"○","")

という数式を入れ列方向にオートフィルでコピーではどうでしょうか？

※　その月の第6週目に土曜日はない！という前提です。m(_ _)m

お礼

回答ありがとうございました。大変勉強になりました。今後もよろしくお願いします。

補足

いつも素早い回答ありがとうございます。返信が遅れまして申し訳ありません。
D5セルにコピーしたところ一発で表示してくれました。今第一と第二土曜日を指定していますが、記述の中ではどの部分でしょうか、また、第六週に土曜日がない前提など、解説を戴ければ他の週などにも応用ができるように勉強したいと思います。お忙しいところ無理を言いますがよろしくお願いします。