日曜日&祝日と隔週の土曜日を除く日に”営業”

いつもお世話になります。
WIN7 EXCELL2010 です。

現在は土日及び祝日を除く日は”営業”と表示することは下記の数式(*1)で何とかできています。
しかしこの関数ですとすべての土曜日が”営業”と表示されます。

ご指導を仰ぎたいのは、
13週目の土曜日も”営業”になるような数式にしたいのですがご教示いただけませんか。
よろしくお願いします

参照図で言うと下記のようにしたいです。
B6 “営業”　1週目の土曜日
P6 “営業” 3週目の土曜日　　*参照図にはないが参考に
I6 “休日”　2週目の土曜日　　*参照図にはないが参考に
W6 “休日”　4週目の土曜日　 *参照図にはないが参考に

条件付書式
B6 =AND(OR(ROUNDUP(DAY(B4)/7,0)=2,ROUNDUP(DAY(B4)/7,0)=4),WEEKDAY(B4)=7)
B6
=IF(OR(WEEKDAY(B4)=7,WEEKDAY(B4)=1,AG3="○"),"","営業")
* ↑この数式ですと　B6 は休日になります。

参考に
B2
=TEXT(B4,"yyyymmdd")
B3
=IF(ISNA(VLOOKUP(B2,祭日,3,FALSE)),"",VLOOKUP(B2,祭日,3,FALSE))

参照図を11月度にしたのは1　3週目の土曜日　休日が表示されているので他には特に意図はありません。

No.1 回答者： mshr1962 回答日時： 2014/01/23 10:15

確認事項

１．AG3="○"はどういう条件でしょうか？　あとセルは固定？相対？
２．今回の条件だと、1日と15日が営業ということですが、第5週の29日は休日ですか？営業ですか？
３．隔週となってますが月によって、第2,4土曜日が“営業”対象の可能性はありますか？
４．第1,3土曜日と祭日がかぶった時は休日ですか？

上記によって、数式の作り方が変わるので補足してください。

この回答への補足

早速回答の御準備を頂きありがとうございます。
捕捉させていただきます。

１．AG3="○"はどういう条件でしょうか？　あとセルは固定？相対？
シート「祭日」　4列目は　祭日があるセルは”○” で今回の質問を考えるときに何かきっかけをつかみたく入れたものです。

=IF(ISNA(VLOOKUP(B2,祭日,4,FALSE)),"",VLOOKUP(B2,祭日,4,FALSE))

２．今回の条件だと、1日と15日が営業ということですが、第5週の29日は休日ですか？営業ですか？
“営業”です

３．隔週となってますが月によって、第2,4土曜日が“営業”対象の可能性はありますか？
絶対にありません

４．第1,3土曜日と祭日がかぶった時は休日ですか？
“休日”です

補足日時：2014/01/23 12:42

No.2 回答者： tom04 回答日時： 2014/01/23 10:18

こんにちは！

一案です。

前提条件として、↓の画像で
F1セルの表示形式はユーザー定義から　0年度　として、2014という数値のみを入力
A3セルの表示形式もユーザー定義から　0月度　として、11（月の数値のみ）を入力しています。

↓の画像でA1セル（実際は目障りにならないセル）に
=DATE(F1,A3,0)-WEEKDAY(DATE(F1,A3,0)-5,3)
という数式を入れるとA1セルが先月の最終土曜日になりますので、
これを利用する方法です。
このシリアル値に14をプラスしたのが第2土曜日・28をプラスしたのが第4土曜日となります。

画像ではB6セルに
=IF(B4="","",IF(OR(B3<>"",WEEKDAY(B4)=1,B4=$A1+14,B4=$A1+28),"休日","営業"))
という数式を入れ右方向にオートフィルでコピーしています。

これで第2・第4土曜以外の土曜日と祝日・日曜以外は「営業」となり、
他のセルは「休日」になると思います。m(_ _)m

この回答へのお礼

TOM04様
お久しぶりです。
いつもご回答いただき誠に有難うございます。
試させていただいたところ本当にうまくできました。
重ね重ねありがとうございます。

お礼日時：2014/01/23 12:46

No.3 回答者： MackyNo1 回答日時： 2014/01/23 11:10

＞B6

=IF(OR(WEEKDAY(B4)=7,WEEKDAY(B4)=1,AG3="○"),"","営業")
* ↑この数式ですと　B6 は休日になります。

上記の数式では日曜日、または土曜日、またはAG3セルが「○」の場合は「空白」表示になりますが、「休日」の間違いでしょうか？

ご質問の意味が、単純に第２、第４土曜日のみ休日と表示したいだけなら、数式の「WEEKDAY(B4)=7」の部分を条件付き書式の「AND(OR(ROUNDUP(DAY(B4)/7,0)=2,ROUNDUP(DAY(B4)/7,0)=4),WEEKDAY(B4)=7）」と置き換えるだけでよいと思います。

この回答への補足

私の知識不足をお許し下さい。
置き換えの意味をわからずですが
下記にその一例を示しましたがうまくゆきません。

=AND((ROUNDUP(DAY(B4)/7,0)=2,ROUNDUP(DAY(B4)/7,0)=4),WEEKDAY(B4)=7),WEEKDAY(B4)=1,AG3="○"),"","営業")

お手数ですが再度ご指導いただけませんでしょうか。

補足日時：2014/01/23 12:56

No.4 回答者： kagakusuki 回答日時： 2014/01/23 12:54

　まず、前提として、B4～AF4にはシリアル値で日付データが入っているものとします。

　その場合、B6セルの関数は次の様なものになります。

=IF(ISNUMBER(1/DAY(B$4)),IF(OR(WEEKDAY(B$4)=1,ISNUMBER(VLOOKUP(B$4,祭日,1,FALSE)),MOD(DAY(B$4)-1+WEEKDAY(DATE(YEAR(B$4),MONTH(B$4),1)),14)=0),"休日","営業"),"")

　尚、本来であれば、

ISNUMBER(VLOOKUP(B$4,祭日,1,FALSE))

という部分は、COUNTIF関数を使用して、「祭日」というセル範囲の中の左端の列の中に、B4に入力されている値が存在しているのか否かを判定させた方が良いのですが、「祭日」というセル範囲の中の左端の列が、どの列になっているのかという事に関して、何も説明されていないため、やむなく

ISNUMBER(VLOOKUP(B$4,祭日,1,FALSE))

などという面倒な判定方法としております。


　次に、B5～AF5における休日の場合にセルとの色を変える条件付き書式の数式は次の様になります。
　　（B5セルを基準に設定する場合）

=B$6="休日"

　尚、上記の条件付書式を設定する際には、該当する条件付書式の「条件付書式ルールの管理」ダイアログボックスにおける「条件を満たす場合は停止」欄のチェックを外して下さい。


　次に、B3～AF4における祭日の場合にセルと文字の色を変える条件付き書式の数式は次の様になります。
　　（B3セルを基準に設定する場合）

=ISNUMBER(VLOOKUP(B$4,祭日,1,FALSE))

この回答へのお礼

詳細なご指導いただき誠に有難うございました。
今度ともよろしくお願いいたします。

お礼日時：2014/01/23 16:49

No.5 回答者： MackyNo1 回答日時： 2014/01/23 14:19

＞置き換えの意味をわからずですが下記にその一例を示しましたがうまくゆきません。

=AND((ROUNDUP(DAY(B4)/7,0)=2,ROUNDUP(DAY(B4)/7,0)=4),WEEKDAY(B4)=7),WEEKDAY(B4)=1,AG3="○"),"","営業")

数式を置き換えると、以下のような関数になります（ただし休日は空白表示になります）。

=IF(OR(AND(OR(ROUNDUP(DAY(B4)/7,0)=2,ROUNDUP(DAY(B4)/7,0)=4),WEEKDAY(B4)=7）,WEEKDAY(B4)=1,AG3="○"),"","営業")

この回答へのお礼

再指導をいただきありがとうございます。
お陰様でうまくゆきました。
皆様のどれもよく迷っています。

お礼日時：2014/01/23 16:44

No.6 ベストアンサー 回答者： mshr1962 回答日時： 2014/01/23 15:17

補足ありがとうございます。

条件付き書式
B6=OR(AND(MOD(CEILING(DAY(B4),7),14)=0,WEEKDAY(B4)=7),WEEKDAY(B4)=1,AG3="○")

“営業”、“休日”
B6=IF(OR(AND(MOD(CEILING(DAY(B4),7),14)=0,WEEKDAY(B4)=7),WEEKDAY(B4)=1,AG3="○"),"休日","営業")

※AG3="○"　の部分は　B3<>""　でも代替可能です。

この回答へのお礼

再指導いただき誠に有難うございました。
皆様にご指導いただき本当に幸せでした。

この数式が一番簡単にできたのがベストの決めになりました。

お礼日時：2014/01/23 16:53