2021/05/08

条件付き書式で作る月単位シフト表

• １．背景
• ２．システム概要
• ３．日付部の設定
• ４．シフト表の設定
• 　４－１．時間軸の表示設定
• 　４－２．開始・終了時刻からの基本的な設定
• 　４－３．日を跨ぐ開始・終了時刻での設定
• 　　　　　よりみち（ベン図を使った数式の表現）
• 　４－４．開始・終了時間を２４時間内での計算にする為の設定
• 　４－５．シフト表の日付を移動するための設定
• 　４－６．休みの日を集約する設定
• 　４－７．休みの日をシフト表に反映する設定
• 　４－８．休みの日の計算を数式に盛り込んだ設定
• ５．最後に

１．背景

店舗などのパート（バイト）さんのシフトをどの様に調整するかは、責任者にとっては重要事項のはずで、私が覗いているYahoo知恵袋でも時々要望が上がってきているようです。
シフトのタイプが色々あるので、全てを網羅したシフト表は存在しませんし、もし存在したとしても操作が相当複雑になるに違いありません。

今回は「24時間営業」の店で、「パートは複数人を使用」且つ「各人の時間は固定（日を跨ぐシフトの人もいる）」の条件で、「あるパートさんが休みを予約した時に、その日のシフト状況がどうなるか」を「条件付き書式を使ってガントチャートで見える化」するものを紹介します。
いつもならマクロ（VBA）を使ってシステムを作っていますが、今回は数式だけで組み立てています。

２．システム概要

今回システムでは、図２－１のような横長の表を考えました。

図２－１（サンプルファイルのSheet1）

シートは月単位で独立させています。まずＢ２セル①に、対象の年月を「月の初日（例えば、2021年2月1日）」の日付データとして「手入力」します。
データとして入力が必要なのは「パート名⑥」「開始・終了時間⑦」です。日を跨ぐシフトの人（図２－１のAさん、Eさん）は「23:00」～「5:00」のように入力します。また24時を越しているという意味で「23:00」～「29:00」のような入力をしてもOKです。（29:00と入力しても、表示は5:00となります）

「休日予定欄⑧」には、休みたい日に「休」の文字列を記入します。実際には「休」に限らず、文字列や数値（長さゼロの文字列 以外）を入力しても「休み」の扱いになります。
また、日を跨ぐシフトの場合は「開始時間の日付で休みを設定」することにしました。たとえば深夜23:00～朝5:00のシフトの人が「2月2日の23:00から2月3日の5:00までの分を休みたい」場合には「2月2日に『休』マークをつける」こととします。
なお、24:00始まりのシフトの場合は0:00始まりと考えて休みの日を設定します。つまり「2月1日の24:00（＝2月2日の0:00）からのシフトを休みたい場合は「2月2日が休み」になります。

なお、前月の最終日が休みの場合には「休日予定欄⑧」の一番左列（E列）に「休」の文字列を入力してください（日を跨ぐシフトの人の勤務状況を正確に表示するためです）。
なお次月の日付（図２－１だとAH～AJ列）への入力は必須ではありません。

以上の入力データを計算して「各パートのシフト状況表⑨」にガントチャートを表示します。この表は24時間軸になっており、上部のスクロールバー⑤でシフト表の表示日付の変更が出来ます。表示日付はスクロールバーの横④に表示しています。

誰も休みが無い場合には図２－１のようなガントチャートとなりますが、休みがある場合には図２－２のようになります。図２－２は2月3日の予定が表示されていることを示します。

図２－２

例えば「Bさん」の場合は「日を跨がないシフト（3:00～8:00）」ですので、「休みは2月3日」とした場合は「2月3日のシフト表から消える」ことになります。
一方「Aさん」の場合は「日を跨ぐシフト（23:00～5:00）」ですので、「休みは2月2日」とした場合は「2月2日の23:00から2月3日の朝5:00までが休み」となりますので、2月3日のシフト表上では「0:00～5:00までが消える」ことになります。2月3日のシフト表上で見えている夜23:00～24:00までの分は、2月3日勤務分ですので出勤していることになります。

なお今回の勤務時間は「時間単位」で作ってあります。もし30分単位・15分単位が必要な場合は「各パートのシフト状況表⑨」をその単位に合わせた表とする必要があります。

３．日付部の設定

シートの年月はＢ２セル（図２－１の①）に入力されます。その値は日付型として「月の初日（例えば、2021年2月1日）」を入力しますが、表示としては「年月」までで良いので、Ｂ２セルの書式設定は「YYYY/M」としてあります。ですので「2021/2/1」であれば「2021/2」と表示されます。

その月の初日①を元にして、「休日予定欄⑧」の日付（３行目）と曜日（４行目）を自動計算しています。その日付は、訳があって「前月の最終日」から始めています。ですので左端セル（図２－１のＥ３セル）には、数式として「=B2-1」を入れています。
その右のＦ３セルからはその月の日にちとなりますので、Ｆ３セルには「=B2」、Ｇ３セルには「=F3+1」を入れ、それより右は数式コピーをしているため「１日ずつ増えていく日付」が入っています。日付セル（Ｅ３～ＡＪ３セル）の書式は、ユーザー定義で「D」としているため「日にちだけが表示される」ことになります。
また日付セル（Ｅ３～ＡＪ３セル）には、図３－１のように条件付き書式を設定し「前月分と次月分の日にちに色付け」をしています。

図３－１

条件付き書式の数式は「=NOT(MONTH(E3)=MONTH($B$2))」としており、「シートの年月①の月で無い場合」という意味になりますので、図３－１のような2月の日付セルでは、１月末日のE3セル、及び３月のAH3～AJ3セルの背景色がオレンジ色になります。

日付セルの一つ下のE4～AJ4セルは「曜日セル」としています。曜日セルには図３－２のように「=TEXT(E3,"aaa")」（E4セルの場合）という数式を入れることで「曜日」を表示させています。それより右側のセルへは数式コピーしています。

図３－２

今回システムでは「曜日による色付け」などはしていませんが、定休日などで色付けが必要な場合は条件付き書式を使って背景色を設定するのも良いと思います。

４．シフト表の設定

４－１．時間軸の表示設定

まず、シフト表の時間軸の設定をします。図４－１はシフト表の部分を見易くするために、休日予定欄（E～AJ列）を非表示にしたものです。

図４－１

時間軸（AM4～BJ4セル）には「1:00」「2:00」･･･「24:00」と、時刻（Date型）を入力してあります。そしてその書式設定として、ユーザー定義で「h」を設定することで、「1:00」なら「1」、「2:00」なら「2」と表示されることになります。
このようにDate型で時刻を設定をしているのは、C～D列の「勤務開始・終了時間」とAM4～BJ4セルの「シフト表の時刻」の値を比較し易くするためです。もちろん「シフト表の時刻」として、数値の「１」「２」・・・を設定しても良いのですが、その時には「Date型⇔数値」変換を数式の中で行う必要が発生します。

また「シフト表の時間軸」の右端は「24:00（=翌日の午前0時）」に相当しますが、「1:00」「2:00」･･･「24:00」と入力していくと、24:00は「（0:00の）0」と表示されます（図４－２）。これは、書式設定を「h」としているためです。
もし「24:00 → 24」と表示したいのであれば、書式設定を「 [h] 」と「カギカッコでhを囲む」ことで「24」と表示されるようになります（25:00だったら「25」と表示されます）。

図４－２

Excelでは１日の時間の長さを「1」と計算しますので、時刻で言えば24:00が「1」に相当します。またExcelでは「1900年1月1日の0:00を1」とし、そこから積み上げた日数で日付を表しますので、図４－２の数式バーの値も「1900年1月1日」になっています。

なお今回システムでは、表示時刻の指している区切りは、図４－３のように「罫線の左隣の値が時刻を表している」ことにします。そのため、時刻を表す数値は右寄せ（数値の標準状態）としています。

図４－３

今回は採用していませんが、数値を左寄せに設定すれば、逆側の罫線を使用しているように見せることも可能です。但しその場合は、図４－４以降で示す「条件付き書式設定の数式のイコール（＝）の付け方」が変わってきますので注意が必要です。

４－２．開始・終了時刻からの基本的な設定

まず、シフト表（AM5～BJ9セル）の書式設定に於いて、図４－４のように基本的な設定をします。

図４－４（サンプルファイルのSheet2）

数式は「=AND(AM$4>$C5,AM$4<=$D5)」です。これは「条件付き書式の適用先（今回は AM5～BJ9セル）の左上角のセルでの数式」を表しています。数式の意味は「シフト時刻＞開始時間　且つ　シフト時刻＜＝終了時間」ということになります。

例えば「Bさん」について考えてみます。Bさんの場合は条件付き書式の適用先で言うと２行目になりますので、数式についても「行が１つズレる」ことになります。ですので、２行目の先頭列（AM6のセル）での数式は「=AND(AM$4>$C6,AM$4<=$D6)」となります。数式は「AND」で繋がれている「AM$4>$C6」「AM$4<=$D6」の２つの数式で出来ています。
まず１つ目の「AM$4>$C6」の数式が成立するのは、図４－５の左図の赤点線枠の範囲になります。

図４－５

一方、２つ目の「AM$4<=$D6」の数式が成立するのは、図４－５の右図の範囲になります。この２つの範囲の重なる（AND）部分が「=AND(AM$4>$C6,AM$4<=$D6)」が成立するセルとなり、そのセルが設定したオレンジ色の背景色に変更されます。

ここで、この数式の「不等号にイコールを付けるか否か」は、図４－３に示した「どの罫線が何時を示しているか」によって変わります。今回は「罫線の左隣の値が時刻を表している」ので、１つ目の数式「AM$4>$C6」にはイコールを付けていません。これにイコールを付けて「AM$4>=$C6」としてしまうと「１時間前からの勤務」になってしまいます。
また２つ目の数式「AM$4<=$D6」にイコールを付けないと「AM$4<$D6」となり、これも「１時間前までの勤務」となってしまいますので注意が必要です。

４－３．日を跨ぐ開始・終了時刻での設定

しかし図４－４のシフト表では、「Aさん、Eさんのシフトが表示されない」ことに気が付くと思います。これは、数式が「=AND(AM$4>$C5,AM$4<=$D5)」だと、「２つの範囲の重なる（AND）部分が無い」ことが表示されない原因です。

図４－６

例えば図４－６のAさんは「深夜23:00～翌朝5:00」の勤務形態ですが、数式が「=AND(AM$4>$C5,AM$4<=$D5)」だと、前半の「AM$4>$C5」では「BJ5セル」が抽出され、後半の「AM$4<=$D5」では「AM5～AQ5セル」が抽出されます。ANDで結んでいますが、「重なる部分が無いためにシフト表が表示されない」ことになります。

では「深夜23:00～翌朝5:00」の時でも、ちゃんと表示される方法を考えていきます。
恐らく何通りかの考え方があると思いますが、今回はまず「開始時間と終了時間を入れ替え」ることを考えました。
数式の「開始（$C5）」と「終了（$D5）」を入れ替え、「=AND(AM$4>$D5,AM$4<=$C5)」とすることで、図４－７のように「働いていない時間帯が抽出」されることが分かります。

図４－７（サンプルファイルのSheet3）

この数式を反転（NOT）すれば、図４－８のように「働いている時間帯が抽出」されることになります。数式は「=NOT(AND(AM$4>$D5,AM$4<=$C5))」です。

図４－８（サンプルファイルのSheet4）

今回の表には「日を跨ぐシフト」と「日を跨がないシフト」が混ざっています。跨ぐシフトの場合は図４－８の「=NOT(AND(AM$4>$D5,AM$4<=$C5))」の数式を使えば良いのですが、跨がないシフトの場合は、図４－４で示した「=AND(AM$4>$C5,AM$4<=$D5)」を使う必要があります。

その「日を跨ぐか否か」の分岐を「開始時間と終了時間のどちらが大きいか」で判断することにします。
数式の形としては「=IF($C5<$D5,[日を跨がないシフトの数式],[日を跨ぐシフトの数式]」の形になりますので、条件付き書式の数式を以下のようにします。
「=IF($C5<$D5, AND(AM$4>$C5,AM$4<=$D5), NOT(AND(AM$4>$D5,AM$4<=$C5)))」
この数式により、図４－９のように「日を跨ぐシフトと跨がないシフトの混在が可能」になります。


図４－９（サンプルファイルのSheet5）

今回、日を跨ぐシフトの時には「=NOT(AND(AM$4>$D5,AM$4<=$C5))」という数式を使いました。しかし図４－６の赤点線枠を見て、「=OR(AM$4>$C5,AM$4<=$D5)」という数式を思い浮かべた方もいると思います。 実は、この２つの数式は同じ結果が得られます。 それを確かめる為に、図４－１０のように集合的に考えてみます。 前提として「AM$4（AM$4～BJ$4の一番左端）」は「全ての時刻」を表しています。また「$C5（開始時間）」「$D5（終了時間）」は時刻の境界で、図４－１０では円として表現しています。円の内側が「時刻の境界より小さな値」、円の外側が「時刻の境界より大きな値」とします（不等号にイコールが付くか否かまでは、ここでは表現できていません）。 図４－１０ まず、$C5（開始時間）を境界として小さい方の集合は①、大きい方の集合は②と表せます。また$D5（終了時間）を境界として小さい方の集合は④、大きい方の集合は③となります。 この４種の組み合わせで、①と③の積集合（AND）が⑤になり、更にそれの否定（NOT）が⑥になります。この⑥を数式で表すと「NOT(AND(AM$4>$D5,AM$4<=$C5))」と、今回の「日を跨ぐ開始・終了時刻での数式」になります。 一方、②と④の和集合（OR）が⑦になり、数式では「OR(AM$4>$C5,AM$4<=$D5)」となります。 ⑥と⑦のベン図を見比べてみると全く同じであるため、「NOT(AND(AM$4>$D5,AM$4<=$C5))」と「OR(AM$4>$C5,AM$4<=$D5)」とでは同じ結果が得られることが分かります。 今回システムでは、日を跨ぐ開始・終了時刻での数式として「NOT(AND(AM$4>$D5,AM$4<=$C5))」を使いました。その理由は「ORを使うのは、絞り込んでいる気がしない」くらいのものです。どちらの数式を使っても大丈夫なはずです。

４－４．開始・終了時間を２４時間内での計算にする為の設定

「日を跨ぐ・日を跨がない（＝開始時間と終了時間のどちらが大きいか）」の設定は出来ましたが、開始・終了時間に色々な値を入力して確かめてみると「正常に表示されないことがある」ことに気が付きます。それは図４－１１のEさんの行のように、例えば午前1時を「25時」として入力した時です。

図４－１１（サンプルファイルのSheet6）

セルの表示としては２４時間未満の表示なのですが、実際に入力している値が２４時間を超えており、数式内で値の大小を比べているために、数式が「成立するはずのセルが不成立（図４－１１のAさん、Eさん）」だったり、逆に「不成立のはずが成立（図４－１１のBさん）」してしまいます。

そこで、開始時間・終了時間は「24時間未満の値に置換」することを考えました。
１日（24時間）の長さは、Excelでは「１」です。つまり「１未満の値」になるように値を調整します。
例えば「3:00」は値で言えば「0.125」、「25:00」は「1.125」です。つまり、開始・終了時間を小数点のみにすれば良いので、 「$C5（開始時間）」→「$C5 - INT($C5)」、「$D5（終了時間）」→「$D5 - INT($D5)」　に置換すれば良いことになります。
前回の数式の中の「$C5（開始時間）」と「$D5（終了時間）」の部分を、置き換えると以下の様になります。

この数式を盛り込んだのが、図４－１２です。これなら「25:00」などの24時より大きな値を入れても、正しく表示されます。

図４－１２（サンプルファイルのSheet7）

なお、例えば「18:00～24:00」のようなシフトの場合は、実際には「日を跨いでいない」のですが、表示は「18:00～0:00」となり「開始時間＞終了時間」と計算されますので、「日を跨いでいる」側と判断されます。しかし問題無く正しいシフト表となります。

４－５．シフト表の日付を移動するための設定

今までの工程は、各人に定まった開始・終了時間をシフト表に盛り込む部分でした。ここからは、そのシフト表に「休みの日の設定表（E列～AJ列）」の内容を反映していきます。

まず、表示するシフト表の日付を設定します。その日付は、今回システムでは「AM2セル」に表示しています。
図４－１３のように、AM2セル（６つのセルをセル結合させて表示しています）に数式「 =B2+AZ2-1 」を入れています。B2セルが「シートの年月の初日」で、AZ2セルが「日付の増分」です。日付の増分は１から始まる整数ですので「-1」を引いています。

図４－１３

「日付の増分」のAZ2セルには、すぐ横のスクロールバーから値を書き込んでいます。スクロールバーを操作することで、その「現在値」 がAZ2セルにリンクするように設定しています。
また月の日数に合わせるように、最小値を「１」、最大値を「３１」に設定しています。月によっては月末は３１日以下になりますが、その調整はしていないので、スクロールバーを目一杯右に動かすと「次の月」に入ってしまいますがご了承下さい。

このようにスクロールバーを移動することで、その結果がAM2セル（＝シフト表の日付）に表示されることになります。

４－６．休みの日を集約する設定

上記のシフト表の日付（AM2セル値）の時に、図４－１５のように各人が休みか否かをAL列に表示させます。またAM2セルの日付当日だけでは無く、その前日が休みか否かの情報も必要ですので、AK列にも「シフト表の日付の前日」の情報を表示させます。

その「表示の当日」と「表示日の前日」の両方の休み情報が必要な理由を説明します。図４－１４のように毎日は連続しており、各人のシフトもシフト形態が日を跨ぐ・跨がないにかかわらず24時間単位で動いています。

図４－１４

例えば図４－１４のAさんが「2月2日に休みたい」と言った場合、②＋③のシフトの分を休みたいのか、④＋⑤のシフトの分を休みたいのかが明確ではありません。人によって受け止め方が違う可能性もあります。ですので今回システムでは、休みの日の届け出は「開始時間の日で計算」することにしました。

このような決め方の上であれば、Aさんが「2月1日の分を休みたい」と言えば「②＋③のシフト分を休みたい」ことが誰にでも分かります。なお、日を跨がないシフトのCさんが「2月2日に休みたい」と言えば、特に決まりが無くても「⑧のシフト分を休みたい」事になります。

そこで、Aさんが「2月1日の分を休みたい」と言った時、2月1日のシフト表を表示した時には「①は有るが②は無い」ことになります。これは2月1日（＝当日）の情報があれば可能です。しかし2月2日のシフト表の表示時には「③は無いが④はある」ことになります。この表現のためには2月2日（＝当日）の情報だけでは足りず、2月1日（＝前日）の情報が必要です。
そのため「シフト表の表示日当日」および「その前日」の休み情報を取得する必要があるのです。

また、シフト表の日付はスクロールバーで移動できますが、スクロールバーのMin値を１としていますので、シフト表は「その月の初日以降」の表示となります。ですので、シフト表の表示範囲よりも１つ前日（＝前月の最終日）の情報までが得られれば、シフト表を正しく表示させることが可能となります。
そのため、休日予定入力部（E列～AJ列）は前月の最終日から入力（前月最終日が休みの場合は入力必須）するようにしています。

では、AK列・AL列の数式を図４－１５で見ていきます。

図４－１５（サンプルファイルのSheet8）

先に「当日」の休み情報（AL列）の数式を確認していきます。数式は以下の通りです。
　・当日（AL列）：「=INDEX($E:$AJ,ROW(), MATCH($AM$2,$E$3:$AJ$3))&""」
まず、INDEX関数の構文は「NDEX(配列,行位置,列位置)」で、その戻り値としては「セル参照」です。

第一引数の「配列」は「$E:$AJ」で、E列～AJ列を丸ごと指定しています。
第二引数の「行位置」は「ROW()」で、その数式が掛かれている「行位置」を得ることができます。
第三引数の「列位置」は「MATCH($AM$2,$E$3:$AJ$3)」です。

MATCH関数の構文は「MATCH(検査値,検査範囲[,照合種類])」です。
MATCHの第一引数は「$AM$2」ですので「シフト表の日付」、第二引数の「$E$3:$AJ$3」は「休み入力表の日付部分」です。第三引数は今回省略していますので「検査値以下の最大値」を検索することになります。
よって「MATCH($AM$2,$E$3:$AJ$3)」は「休み入力表の日付の中から、シフト表と同じ日付を探して、その相対列位置を取得」するという意味になります。

ということで、AL列の数式の前側部分「=INDEX($E:$AJ,ROW(), MATCH($AM$2,$E$3:$AJ$3))」は、「休み入力表から、シフト表の日付のあるセルの値」を取得することになります。休み入力表に「休」の文字列があれば、それがAL列のセルに表示されます。

一方、対象のセルが空白セルだった場合には、数値の「0」が表示されてしまいます。これは、例えばA1セルが空白だとして、A2セルに数式で「=A1」と入力した時にはA2セルには「0」と表示されるのと同じです。
しかし、休みの記号として数値の「0」を使いたい、などの場合には「空白セルの0」と「入力した数値0」の区別がつかなくなってしまいます。ですので「空白セルの時には、空白セルを返す」ようにするのが、数式の一番右の「&""」です。

INDEX関数の結果に「&""」を加えることで、もしセル参照値が空であった場合には「長さゼロの文字列」に変換しています。また、セル参照値が数値（休み入力表に、数値のゼロを入れたような場合）だった場合には、「0 & ""」となりますので、最終的に「文字列の"0"」が得られます。休み入力表に何か数値（セル内で右寄せ表示）を入れると、AL列ではセル内で左寄せ表示になっているのが確認できると思います。

AL列（当日）と異なるのは、赤字で示した部分「$AM$2」→「$AM$2-1」だけです。シフト表の表示日から１を引くことで、前日を検索するようにしています。

４－７．休みの日をシフト表に反映する設定

このAK列（前日）・AL列（当日）の休み情報を使って、条件付き書式の数式を作っていきます。
まず、AK列・AL列に値が入っている（＝休みの設定がされている）場合に、シフト表のどこを見えなくするべきかを考えます。図４－１６では、見えなくする部分を点線枠で囲っています。
赤い点線枠は「日を跨ぐシフト」の場合、青い点線枠は「日を跨がないシフト」の場合です。

図４－１６

まず簡単な「日を跨がないシフト」の場合を考えます。
図４－１６の左側（2月1日のシフト表）では、Cさんが当日（2月1日）休み設定になっていますので、⑤が無くなる部分です。また右側（2月2日のシフト表）では、Bさんが当日（2月2日）休み設定になっていますので、⑥が無くなる部分です。
つまり「当日が休みか否か」のみの判断で「シフト表の表示日に表示を消す」ことでＯＫということです。

前回までの条件付き書式の数式は以下であり、赤い部分が「日を跨がないシフト」の部分です。
この部分を「AL列（当日）に値が入っていたら、表示しない」又は「AL列（当日）に値が入っていない時のみ、表示する」設定にします。数式にすると、以下のようになります。赤い部分が変更を加えたところです。
次に「日を跨ぐシフト」の場合を考えます。
図４－１６の左側（2月1日のシフト表）では、Aさんが当日（2月1日）休み設定になっていますので、②の部分を消す必要があります。しかし①の部分は前日（1月31日）からの続きで「休みでは無い」ので表示する必要があります。
一方、右側（2月2日のシフト表）では、Aさんが前日（2月1日）休み設定になっていますので、③の部分を消す必要があります。④の部分は2月2日の勤務ですので「休みでは無い」ので表示する必要があります。

数式の考え方はいくつかあるかもしれませんが、今回は以下の様に考えました。
　・当日（AL列）が休みの時＝開始時間を0:00（＝0）にする
　・前日（AK列）が休みの時＝終了時間を24:00（＝1）にする
しかしこれを条件付き書式の数式に入れてみると、思った通りのシフト表になりません。理由は「日を跨ぐシフト」の数式は「開始時間＞終了時間」とひっくり返っていることが前提として成立しているためです。
ですので、ここでは逆に考え、以下の様にします。
　・当日（AL列）が休みの時＝開始時間を24:00（＝1）にする
　・前日（AK列）が休みの時＝終了時間を0:00（＝0）にする

これを数式にすると以下の様になります。赤い部分が変更を加えたところです。（日を跨がないシフトの分も盛り込んでいます。）
この数式を条件付き書式に盛り込んだシフト表が図４－１７になります。前日と当日の休み設定が盛り込まれているのが分かると思います。

図４－１７（サンプルファイルのSheet9）

４－８．休みの日の計算を数式に盛り込んだ設定

ここまででシフト表は完成状態です。しかし実際の使用場面を考えると、例えばパートさんが増えた時には「（条件付き書式の再設定をしないで済むように）既存のシフト表内で行挿入をすると思います。その時に「挿入行のAK列・AL列には、休みを表示させるための数式をコピー」する必要が出てきます。これを忘れてしまうと「シフト表は未完状態」に戻ってしまいます。
そこで、行挿入しただけですぐに使える表にするために、AK列・AL列の数式を「全て条件付き書式の数式に盛り込んでしまう」ことにします。

AK列・AL列の数式は下記の通りです。
　・AK列：「=INDEX($E:$AJ,ROW(), MATCH($AM$2-1,$E$3:$AJ$3))&""」
　・AL列：「=INDEX($E:$AJ,ROW(), MATCH($AM$2,$E$3:$AJ$3))&""」

一方、現在の条件付き書式の数式は以下の通りです。赤字の部分がAK列・AL列を参照している部分です。
このAK列・AL列を参照している部分を、上の数式で置き換えます。
これを盛り込んだシフト表が図４－１８になります。AK列・AL列は空にしています。

図４－１８（サンプルファイルのSheet10）

５．最後に

今回は条件付き書式の数式に全てを盛り込んだ結果、最後の姿の数式は290文字にもなります。これを見た人が内容を解読しようとすると苦労をすると思います。つまり「可読性は良くない」システムということになります。

これを防ぐ（可読性を良くする）ためには、１段階ずつ「作業列」を作って「中間結果」を書き出しながら、最終的な数式にする　という方が、私は正しい姿だと思います。
しかし、行挿入をする可能性のある今回のような表で「作業列」を作っていると、そこの数式のコピーを確実にしていかないと、正しくない最終結果が出てしまうことになります。
作業列の数式くらいであれば、システムの構造を分かっている人が行挿入時に補修すればよさそうですが、作った本人ですら少し時間が経つと忘れたり分からなくなったりするものです。

そこで今回は可読性を無視し、一般ユーザーが行挿入しても動くように作ってみました。ユーザーのあらゆる操作に全て対応できている訳ではありませんが、少しは気楽に使えるのではないかと思います。

なお、条件付き書式の数式の長さはExcel2007以降では8192文字ですので、今回の290文字ではその3％にしかなりません。もっともっと複雑な数式でも可能です。

下のサンプルファイルでは、「Sheet1」と「Sheet10」が最終仕様となっています。他のシートは途中段階で、本文と見比べられるようにしているつもりです。シート間のリンクはありませんので、もし使用するときはシートコピーして下さい。
また、説明図を出来るだけ大きく表示させるために、サンプルファイルではセル幅をギリギリ小さく設定しています。ファイルを開く環境によっては、日付や時刻の数値が「#」になってしまう場合がありますので、適当にセル幅を調整して下さい。

条件付き書式で作る月単位シフト表（it-058.xlsx）