年間仕事用シフトカレンダーに、日勤夜勤休みに色分けをした。

Question

エクセルで
年間仕事用シフトカレンダーに、関数で日勤 夜勤 休み に色分けをしたい
のですが出来ますか？　いままで手作業でやっていました。
２0２3年版の作成をしたいです。
以下は2022年版しかまだないのでサンプルは今年の分を
参考して作成した場合
Web用
http://afurieitohannei.la.coocan.jp/sns/snssifuto.htm

※エクセル　原本
http://afurieitohannei.la.coocan.jp/sns/snssifuto.xlsx
シフトは完全6周期です。（サンプルは4日出勤の2日休み、1年366日完全シフト制）
土日祝祭日関係無し、年間1/3が休みなので算出しやすいと思います。

配色は以下です。
1）休み「赤」
2）日勤「黄」
3）夜勤「青」
　以上組んだ場合　どんな関数になりますか？
関数だけでの日にち色分けは可能でしょうか？
　よろしくお願いいたします。

※エクセル　原本　をダウンロードされる場合、
　nifiy のレンタルサーバーですが
　最新のウイルスバスターでウイルスチェックはしました。
　万が一ウイルスがいた場合、自己責任でお願い致します。

goomania · Accepted Answer

考え方は既にfujillinさんがお示しになっておられます。

12日周期で、「日勤4日、休2日、夜勤4日、休2日」が繰り返すことになります。fujillinさんの説明にもあるとおり、カレンダーをどのように作成するかで異なりますが、ご質問者がアップされたEXCELファイルは単に、日付欄に日にちの数値を入力してあるだけのようです。

これから説明する方法の前提として、数式でも手入力でもよいのですが、単なる日にちの数値ではなく、シリアル値を用いてカレンダー作成するものとします。

つまり「1月1日」であれば「2023/1/1」のように入力したと同様、シリアル値「44927」が入力されていて、セルの書式設定の表示形式のユーザー定義で「d」として日付の数値のみ表示されている状態であることが前提です。

添付画像をご覧ください。
カレンダーはすでに、シリアル値で作成済みであるものとします。

3つに色分けするため、色ごとに条件先書式を設定します。仕事始めの特殊事情がある場合を考慮し、1/1～1/6は条件付き書式での色付け対象から除き(手動で作成)、勤務開始日は1月7日とし、条件付き書式での色付もここから開始するものとします。

日勤は黄色、夜勤は青色、休は赤色で塗りつぶすので、添付画像でいうと1月日付欄の左上隅セルであるC6セルに条件付き書式の「数式を使用して、書式設定するセルを決定」を選択して、「次の数式を満たす場合に値を書式設定(O)」欄に以下の数式を記述します。

=(CHOOSE(MOD(対象セルのシリアル値-勤務開始日のシリアル値,12)+1,1～12までの数値に対応した日勤・夜勤・休の区別)=日勤・夜勤・休のいずれか)*(対象セル<>"")*(対象セルのシリアル値-勤務開始日のシリアル値>=0)

つまり、

=(CHOOSE(MOD(C6-"2023/1/7"*1,12)+1,"日","日","日","日","休","休","夜","夜","夜","夜","休","休")="日")*(C6<>"")*(C6-"2023/1/7"*1>=0)･･････①

を日勤つまり黄色の条件

=(CHOOSE(MOD(C6-"2023/1/7"*1,12)+1,"日","日","日","日","休","休","夜","夜","夜","夜","休","休")="夜")*(C6<>"")*(C6-"2023/1/7"*1>=0)･･････②

を夜勤つまり青色の条件

=(CHOOSE(MOD(C6-"2023/1/7"*1,12)+1,"日","日","日","日","休","休","夜","夜","夜","夜","休","休")="休")*(C6<>"")*(C6-"2023/1/7"*1>=0)･･････③

を休つまり赤色の条件

として記述します。
このあとC6セルをコピーして、日付を表示するセルをCTRLキーを押しながらマウスで全て選択して、「形式を選択して貼り付け」で「書式設定」で日付表示セル全てに書式を貼り付けします。

fujillinさんの説明にあるように、

>0～3は日勤、4,5は休み、5～8は夜勤・・・と判断ができます。

ということなのですが、ここでは徐余に1加えて1～12までの数値になるようにし、さらにCHOOSE関数を用いて、1～4が日勤、5、6は休、7～10は夜勤、11、12は休となるようにCHOOSE関数の選択リストを"日","日","日","日","休","休","夜","夜","夜","夜","休","休"のようにしています。
これは後から条件付き書式の設定を見返したとき、或いは、作成者以外の人か見たときに判り易くするためのものです。これにより、日勤、夜勤、休の判別を数式の一箇所の変更だけで可能にしています。

ですから、簡潔を好むなら数式①を

=(CHOOSE(MOD(C6-"2023/1/7"*1,12)+1,1,1,1,1,2,2,3,3,3,3,2,2)=1)*(C6<>"")*(C6-"2023/1/7"*1>=0)

とか、fujillinさんの説明どおりに数式①を

=(MOD(C6-"2023/1/7"*1,12)>=0)*(MOD(C6-"2023/1/7"*1,12)<=3)*(C6<>"")*(C6-"2023/1/7"*1>=0)

のように記述しても、条件自体は同じであり、日勤、夜勤、休の区別は可能なので数式としても、もちろん有効です。

お好みの数式をお使いいただいて構わないということになります。

fujillin · Answer

こんにちは

＞1月７日(金)の日勤～12月末まで完全6日周期となります。
No2様の回答にもありますが、6日ではなく12日周期なのではないでしょうか？
翌年以降もずっとそのまま連続してゆくというのであれば、計算で求めることは可能です。
要領としては、カレンダーで曜日を求めるのと同じです。
曜日は7日周期ですが、これを12日周期として考えればよいだけになります。

ただし、
＞正月の休みの兼ね合いで順不同で手動で作成。
のようなことが起きるのであれば、計算では算出できません。
（うるう年のように、それを式化できるのなら計算可能ですが・・）

イレギュラーは発生しないと仮定しても良いのなら、
基準日からの経過日数を12で除算した際の徐余で、その日の順番がわかります。
（曜日なら7で除した余りでわかるのと同様で、周期の12で除します）

例えば、2022/1/7を基準日とすると、これは「日勤×4、休×2、夜勤×4、休×2」というサイクルのスタート日と考えることができます。
除余は0～11になりますので、0～3は日勤、4,5は休み、5～8は夜勤・・・と判断ができます。
具体的には、
　DATEDIF(基準日,調べたい日付,"D")
で経過日数が求められますので、徐余は
　MOD(DATEDIF(基準日,調べたい日付,"D"),12)
となります。
一方で、エクセルの場合だと上記の結果は
　MOD(調べたい日付,12)
と一致しますので、経過日数の計算を省くこともできます。

カレンダーをどのように作成なさっているのか不明ですが、日付のセルの値がシリアル値（＝エクセルの日付型の値）になっていて、セルの書式表示の「d」などで日にちだけを表示しているのなら、そのままセルの値を利用できます。
「条件付き書式」を利用して、上記の判定式で色が着くようにしておけばよいでしょう。
セル値がシリアル値ではなく単なる1、2・・・の数字の場合は、その日の日付を計算する必要があります。
日付に換算するには
　DATE(年, 月, 日)
でシリアル値を得ることができます。（DATEVALUEでも計算可能です）

カレンダーの作り方を（よくある「万年カレンダー」のように）どこかのセルに「年」を指定すればその年のカレンダーが表示されるような仕組みにしておいて、色付けの仕組みをセットしておくことで、
　「年を指定すればその年のシフトカレンダーが表示される」
シートを作成することも可能でしょう。
ただし、何度も書きますが「ルールにイレギュラーが発生しない」ということが条件になります。

銀鱗 · Answer

12日周期で同じ勤務パターンということのようですので、
添付した画像の ”ような感じ” にしたいということであれば、
　・始めの12日分に色を付けて、
　・そのセルをコピーし、
　・残りの場所をすべて選択して「書式の貼り付け」をする。
でいいと思います。
コピーしたパターンが繰り返して貼り付けられますよ。

・・・

(´・ω・`) 数式でやっても、次は2023年は何日がパターンの始めになるのか……で数式を修正することになります。
ですので、初めから手作業で色を付けることを勧めます。

これなら、同じ表にある別の人のシフトにも色を付けることができます。

・・・別回答・・・

「日勤」「夜勤」「休み」と入力されているセルに色を付けるなら、
添付した画像の例なら、色を付けたい範囲の一番左上がC2セルなので、
色を付けたい範囲をすべて選択して、C2セルに値を入力できる状態で、
　＝C2="日勤"
　＝C2="夜勤"
　＝C2="休み"
と3つの条件を「条件付き書式」に設定して、それぞれの書式を設定してやればいい。

・・・余談・・・

こんな感じに、画像を貼るという方法もあります。

銀鱗 · Answer

＞関数だけでの日にち色分けは可能でしょうか？

たぶん可能。
「休み」「日勤」「夜勤」をどうやって見分けるかという条件を示してもらえれば、できると思いますよ。

・・・

(´・ω・`) ごめんね。
＞万が一ウイルスがいた場合、自己責任でお願い致します。
それじゃ怖くて手が出ない。

ダウンロード前提で質問をする場合は、
　・検査エンジンのバージョン、
　・定義ファイルのバージョン、
　・検査日
この３つも示したほうがいいでしょう。
それで納得できない人はダウンロードしなければいい。
自分ならそれが本当に最新なのかを確かめたうえで判断します。
（公官庁ではそうやって健全性を示しています）