エクセルで条件書式と入力規則を使い、カレンダーを作りたい！

質問

仕事でエクセルを使って、
「各営業所における営業日の実績データ」を収集しているのですが、
PCを得意としていない人たちが入力していることもあり、
休業日など目的と違うところにデータを入力してしまうことがあります。

そこで、視覚的に入力するところを分かりやすくし、
それでも間違ったところに入力してしまうのを防ぐため、
条件書式と入力規則を使って、カレンダーを作りました。

年月を入力すれば自動的に日付が変わるカレンダーを作り、
日付は正しく反映されているのですが、
営業日と休業日が上手く反映されず、その原因が分かりません・・・。

当社の営業日設定なのですが、
基本的に平日と日曜が営業日で、休業日は土曜と祝日になります。
ただし、平日が祝日だった場合は休業ですが、
日曜日と祝日が重なった場合は営業日になります。
なお、年末年始（12/31～1/3）は休業日になります。
さらにややこしくなってしまうのですが、
GW中（4/28～5/7）の日曜日、年末年始（12/30～1/5）の日曜日は、
長期連休ということで休業日になっています。

エクセルのデータですが、
列Aには日付（A3が1日）、列Bには曜日が入力してあり、
列Cと列Dに実績データを入力してもらう形にしてあり、
ここに条件書式と入力規則を設定しています。
また、セルV2～W33までに各祝日のリストとその振替休日、
加えてそれ以外の休業日（12/30、1/2、1/3）の日付を記入してあります。

前置きが長くなってしまって申し訳ございません。
以下が、私の考えた条件書式と入力規則の流れです。

まず休業日を考え、入力規則のユーザー設定に、

=OR(
COUNTIF($V$2:$W$33,$A3)<>0,
MOD($A3,7)=0
) （※祝日リストに日付があるか、土曜日である）

として、さらにGW・年末年始休業の部分を、

AND(MOD($A3,7)=1,AND(MONTH($A3)=4,DAY($A3)>=28)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=5,DAY($A3)<=7)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=12,DAY($A3)>=30)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=1,DAY($A3)<=5)),

と考えました。また祝日かつ日曜日は営業日となることを、

AND(COUNTIF($V$2:$W$33,$A3)<>0,MOD($A3,7)=1),

と考えて、その逆が休業日であることから、

=OR(
COUNTIF($V$2:$W$33,$A3)<>0,
MOD($A3,7)=0,
OR(COUNTIF($V$2:$W$33,$A3)=0,MOD($A3,7)<>1),
AND(MOD($A3,7)=1,AND(MONTH($A3)=5,DAY($A3)<=7)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=4,DAY($A3)>=28)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=12,DAY($A3)>=30)),
AND(MOD($A3,7)=1,AND(MONTH($A3)=1,DAY($A3)<=5))
)

上記を休業日として入力規則に入力しようとしましたが、
長過ぎて入らないようなので、GW・年末年始休業の部分を、
必要な該当月ごとに書き換えてみましたが、うまくいきませんでした。
ちなみに、その逆が営業日であるので、条件付き書式の数式に、

=AND(
COUNTIF($V$2:$W$33,$A3)=0,
MOD($A3,7)<>0,
AND(COUNTIF($V$2:$W$33,$A3)<>0,MOD($A3,7)=1),
OR(MOD($A3,7)<>1,OR(MONTH($A3)<>12,DAY($A3)<30))
)

と入力し、確認しましたが、やはりダメでした。
（ちなみに、「祝日かつ日曜日」があり「年末年始休業日」もある
　2012年12月に設定を入力しながら結果を確認していました。）

いろいろ試行錯誤した結果、一つ一つはどうやら正しいので、
組み合わせたときに、「祝日かつ日曜日」と「祝日でない」が
同居しているのが悪いと思うのですが、何か解決策はないでしょうか？
長い文章になってしまい恐縮ですが、ご教授お願い致します。

回答No.2

mt2008 · ベストアンサー

お休みの条件を整理しましょう

1.土曜日
2.日曜日ではない祝日
3.年末年始（12/31～1/3）
4.GW中（4/28～5/7）の日曜日
5.年末年始期間（12/30～1/5）の日曜日

1.　=(WEEKDAY(A3)=7)
2.　=AND(COUNTIF($V$2:$W$33,$A3)>0,WEEKDAY(A3)>1)
3.　=OR((A3=DATEVALUE(YEAR(A3)&"/12/31")),(A3<=DATEVALUE(YEAR(A3)&"/1/3")))
4.　=AND((A3>=DATEVALUE(YEAR(A3)&"/4/28")),(A3<=DATEVALUE(YEAR(A3)&"/5/7")),WEEKDAY(A3)=1)
5.　=AND(OR((A3>=DATEVALUE(YEAR(A3)&"/12/30")),(A3<=DATEVALUE(YEAR(A3)&"/1/5"))),WEEKDAY(A3)=1)

この５つの条件のどれかが成立した時が休業日になります。

添付の図では、各条件をF～J列に入れて、判り易くするため1を掛けてTRUE/FALSEを1/0で表示しています。
また、E列には　=MAX(F3:J3)と入れて休業日条件が１つでも成立している日に１を表示させています。

お礼

回答を参考にして無事目的通りできました！
これはエクセルの問題というより、国語の問題でしたね（＾＾；
画像まで付けてご丁寧に回答していただき、ありがとうございました！

回答No.1

tom04

こんばんは！
外していたらごめんなさい。
↓の画像で説明させていただきます。
当方使用のExcel2003の場合です。

実際の祝日・休日データがどのようになっているか判らないので
勝手に一般的な祝日のみのデータを表示しています。
（方法だけ理解していただければ良いかな！って思っています。）
画像の祝日データはV2～W24セルまではいっていますが
質問ではこの範囲を変更していただければ対応できると思います。
このデータ内に一般的な祝日以外に貴社の休日データを追加しておけば問題ないと思います。

A3セルの表示形式をユーザー定義から　d　としておきます。
A3セルは
=IF(MONTH(DATE($A$1,$C$1,ROW(A1)))=$C$1,DATE($A$1,$C$1,ROW(A1)),"")
B3セルの表示形式はユーザー定義から　aaaa　としています。
B3セルに
=IF(A3="","",A3)
という数式を入れ、B3・C3セルを範囲指定しC3セルのフィルハンドルで
31日まで下へコピーします。

そして条件付書式の件ですが
まず、日曜日と祝日が重なる部分を条件1とします。

A3～B33セルを範囲指定し、
数式がの数式欄に
=AND(WEEKDAY(A3)=1,COUNTIF($V$2:$W$24,A3))
として「書式」パターンから「色なし」を選択します。

続いて条件2の数式欄に
=OR(WEEKDAY(A3)=7,COUNTIF($V$2:$W$24,A3))
として書式　→　パターン　→　「赤」を選択すると
↓の画像のような感じになります。
これで日曜日と祝日が重なっている日は色なしになり、
土曜日と祝日は赤になると思います。

以上、長々と書きましたが
参考になれば幸いです。
的外れなら読み流してくださいね。m(__)m