質問: エクセルで条件書式と入力規則を使い、カレンダーを作りたい! 仕事でエクセルを使って、 「各営業所における営業日の実績データ」を収集しているのですが、 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月に設定を入力しながら結果を確認していました。) いろいろ試行錯誤した結果、一つ一つはどうやら正しいので、 組み合わせたときに、「祝日かつ日曜日」と「祝日でない」が 同居しているのが悪いと思うのですが、何か解決策はないでしょうか? 長い文章になってしまい恐縮ですが、ご教授お願い致します。
回答: こんばんは! 外していたらごめんなさい。 ↓の画像で説明させていただきます。 当方使用の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