エクセル 条件付き書式について

エクセル 条件付き書式について
今、エクセルで一ヶ月の職員の業務予定表を作成しています。
職員の中にハーフ勤務(平日の半分だけ勤務)の人がいまして、お休みの日にグレーの色をつけています。
これをどうにか条件付き書式で年月を変えても自動で日付、曜日を見て変わるようにしたいのですが、可能でしょうか？

一日置きに色をつけるのはできるのですが、それだと土日含めて一日置きになってしまい、ずれが生じてしまいます。

よろしくお願いいたします。m(_ _)m

質問者からの補足コメント

• banzaiA様
  お返事遅くなってすみません。
  5月の連休前の出勤日は2日のみでした。
  
  が、気づくのが遅くて大変申し訳ないのですが、祝日があると休みの日と出勤日がずれる要するに一日置きのお休みではなく二日置きの可能性もあるということが判明しました。
  他の職員さんもおそらく祝日で出勤日が変動すると思われるので、今回は土日祝日だけ色をつけてお休みの日は各自で入れてもらうことにします。
  
  
  たくさんの方に回答していただき、本当にありがとうございましたm(_ _)m
  知らなかった関数も知ることができて、とてもいい勉強になりました。
  
  ベストアンサーは回答していただいた皆さんに差し上げたいのですが、そうもいかないので、画像までつけていただいたfujillin様に差し上げたいと思います。

    補足日時：2017/07/04 21:46

No.6 ベストアンサー 回答者： fujillin 回答日時： 2017/06/30 13:02

こんにちは

ハーフ勤務のパターンは、ある日に休むのと同じ日に出勤との2通りあると思います。
それに、通常出勤を加えると全部で3パターンになるのでしょうか？

平日の数を数えて偶数か奇数かで判断できそうに思いますが、いずれにしろ、実用化するには祝日が入ったらどうするかとか、ＧＷや正月休み等も考慮する必要が出てくるのではないかと想像しますので、最終的にはユーザ定義関数やマクロを利用することになりそうな気がします。


ともあれ、ご質問の範囲限定であれば条件付き書式でも対応できそうですので・・・
添付図で、
・2行目は日付がシリアル値で入っています。
・3行目はC3が =WEEKDAY(C2)の要領で曜日表示しています。
・土日は条件付き書式で色を変えてあります。
・表内の条件付き書式に以下を追加。C4セルの条件式として
　　=MOD(COUNTIFS($C$3:C$3,"<>1",$C$3:C$3,"<>7"),2)+1=$A4
　という条件でグレー表示するように設定します。
　この書式の、優先順位は「土日の設定」よりも低くしておきます。

※ 個人の勤務パターンを表す項目（A列）を作成しています。
　 数値（1または2）がハーフ勤務、未入力は通常勤務です
※ 実際には、別に氏名と勤務形態等を示すマスターがあって、そちらを参照するようなことになるのかも知れませんね。

この回答へのお礼

回答ありがとうございますm(_ _)m
また、画像までつけていただき、ありがとうございます。とてもわかりやすかったです。

やはり祝日も除くと考えると難しいですよね...

お礼日時：2017/06/30 14:58

No.11 回答者： ママチャリ 回答日時： 2017/07/01 21:07

NETWORKDAYS関数を使って初回出勤日から暦日までの営業日を求めて、偶数の場合、条件付き書式で塗りつぶし（休み）とします。

条件式は、こんな感じです。
=ISEVEN(NETWORKDAYS(初回出勤日,歴日,祝日の範囲))

ちなみに、土日祝については、別途、条件付き書式で塗りつぶされているものとします（上記の休みより優先的に）。

No.10 回答者： banzaiA 回答日時： 2017/06/30 19:31

例えば、①今年の11月22日と24日はは出勤日で、23日はお休みの日で、祝日と重なりますが、この祝日は考慮しないでいいですか？

②出勤日と祝日が重なった場合はどうするのですか？今年の5月4日は水曜日で出勤日ですが祝日で休みですね？この出勤日をどうするのですか？（今年の5月の出勤日は、2日4日8日10日です）

この回答へのお礼

回答ありがとうございますm(_ _)m
今わからないので、月曜に確認してみます。

お礼日時：2017/07/02 09:06

No.9 回答者： fxq11011 回答日時： 2017/06/30 18:49

どうもそのようですね。

言い換えれば、あなた自身は、休みの日は見分けがつくが、パソコンがそれを認識できるか、です。

この回答へのお礼

回答ありがとうございますm(_ _)m

お礼日時：2017/07/02 09:06

No.8 回答者： fujillin 回答日時： 2017/06/30 16:18

ANo6です。

＞やはり祝日も除くと考えると難しいですよね...
祝日の情報を盛り込めれば、No6と同様の方法で算出することは可能です。
例えば、No6の添付図の3行目と4行目の間に、祝日か否かの表示がされているとか・・・（不要な場合は、その行は非表示にしておく）

祝日を表示するためには、別に祝日表を作成しておいて、それを関数等で参照する方法や、祝日の論理的な規則性を利用してプログラムで算出する方法などが考えられますが、それはそれで、今回のご質問とは関係のない内容になってしまいますので省略。


しかしながら、No6で提示した方法は（『限定』と書きましたように）表の初めから平日数を数えて偶数か奇数かで判断しているだけという、所詮、付け焼刃的な方法ですので、そのままだと、月が変わると同じ人でもパターンが逆転する可能性があるなどの欠点があります。
パターンの逆転防止だけであれば、その月の最初を偶数日と考えるか奇数日と考えるかを示すセルを一つ用意しておけば、それで済むことは済みますが・・・

とはいえ、まともに月にかかわらず継続的な使用を目標とするなら、4月からの平日数を計算することが必要になりそうです。
これを関数だけで処理しようとなるとかなり面倒になると思いますので、エクセルであればVBAを利用することになりそうな気がします。
(月ごとにシートが作成されていて、前の月のシートを参照するような工夫ができれば、No6の方法でもパターンの継続も可能ですが…）


また、まったくの想像ですが、ハーフ勤務のAさんとBさんがある日だけ都合で入れ替わる（代わりに出勤）ようなことは発生しないのでしょうか？
そのような場合にも対応できるようになさりたいのであれば、単純な条件付き書式では、難しいのではないかと想像します。
（これだけに限れば、表の作り方を工夫し、うまくルールを作成すれば可能そうですが…）

実際の使用を想像すると、いろいろな可能性がありそうに思いますが、それらにある程度までは対処できるようにしておかないと、せっかく作成しても『使えない道具』になりかねませんので・・・

この回答へのお礼

回答ありがとうございますm(_ _)m
祝日の一覧はすでに作ってあるので、それで試してみます。
VBAは絶賛勉強中でして、まだ簡単なボタンくらいしか作れない私には難易度が高いです(^^;
シートは係ごとで分けていて複数になっているので、一ヶ月ごとにファイルで分ける予定です。
代わりに出勤というのは発生しないです。
とりあえず、土日祝に色をつけてあるので、お休みの日は各個人で色付けしてもらうという感じで使用していこうかと思っています。

お礼日時：2017/06/30 18:41

No.7 回答者： banzaiA 回答日時： 2017/06/30 13:57

A列に月日、B列に曜日、C列がハーフ勤務者が入力されているとします。

C列の条件付き書式で、数式を使用して・・・で
＝OR(MOD(A1,14)=3,MOD(A1,14)=5,MOD(A1,14)=9,MOD(A1,14)=11,MOD(A1,14)=13)
を入力します。
あとはグレーの色を指定して実行。
ただ祝祭日は考慮していません。

この回答へのお礼

何度も回答していただき、ありがとうございますm(_ _)m
祝祭日考慮しない場合これでうまくいきますね。
参考にさせていただきます！

お礼日時：2017/06/30 18:29

No.5 回答者： banzaiA 回答日時： 2017/06/30 12:05

＃３です。

＞始まりが4月からで第一週目は月水金が休みで火木が勤務日です
4月の第一週目が火木が出勤で、第２週目は月水金が勤務日ですか？

この回答へのお礼

そうです。

お礼日時：2017/06/30 12:08

No.4 回答者： fxq11011 回答日時： 2017/06/30 11:27

休みの日の該当セルの状況が統一されている必要があります。

例　特定の値の入力、ルックアップ等で特定の条件で検索可能・・・等。

この回答へのお礼

回答ありがとうございますm(_ _)m
業務予定表なので日付、曜日以外は空白です。
特定の値が入るということもおそらくないので、無理ですね。

お礼日時：2017/06/30 11:49

No.3 回答者： banzaiA 回答日時： 2017/06/30 11:12

対象の人の半日休日の曜日は決まっているのですか？

または、平日の一日おきなのですか？その場合は何日を基準としての判断ですか？
具体的に書いてください。

この回答へのお礼

回答ありがとうございますm(_ _)m

平日の一日おきです。
始まりが4月からで第一週目は月水金が休みで火木が勤務日です。

お礼日時：2017/06/30 11:35

No.2 回答者： milk_kokoa 回答日時： 2017/06/30 10:59

例えば、先程の例で言うとＥ列に隠し列を作るとします。

0=出勤、1=休日　とフラグを決めます。

E1（見出し行）に、前月最後の日の出勤フラグを入れます。
※平日の最終日が出勤だった場合「0」休日だった場合「1」

E2=if(B2="土",1,if(B2="日",1,if(E1=1,0,1)))
E3=if(B3="土",1,if(B3="日",1,if(B2="日",if(E1=1,0,1),if(E2=1,0,1))))
E4=if(B4="土",1,if(B4="日",1,if(B3="日",if(E1=1,0,1),if(E3=1,0,1))))
後は、E4を下の行にコピーすると、休日としたい日が「1」になります。

あとは条件付き書式で、
数式に「=$E2=1｣で書式設定をすれば出来るかと。
祝日が絡むと微妙ですが＾＾；
Ｅ行をフラグときめておけば、フラグをいじるだけで色が変わるかと思います。

この回答へのお礼

回答ありがとうございますm(_ _)m
試してみます。

お礼日時：2017/06/30 11:36