【最大10000ポイント】当たる!!質問投稿キャンペーン!

仕事でエクセルを使って、
「各営業所における営業日の実績データ」を収集しているのですが、
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月に設定を入力しながら結果を確認していました。)


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

このQ&Aに関連する最新のQ&A

A 回答 (2件)

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



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)

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

添付の図では、各条件をF~J列に入れて、判り易くするため1を掛けてTRUE/FALSEを1/0で表示しています。
また、E列には =MAX(F3:J3)と入れて休業日条件が1つでも成立している日に1を表示させています。
「エクセルで条件書式と入力規則を使い、カレ」の回答画像2
    • good
    • 0
この回答へのお礼

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

お礼日時:2010/04/05 09:10

こんばんは!


外していたらごめんなさい。
↓の画像で説明させていただきます。
当方使用の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
「エクセルで条件書式と入力規則を使い、カレ」の回答画像1
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング