パートやアルバイトを従業員として雇っている事業所では、スケジュール管理が必要です。何日に、誰が、どの時間帯にいるか、というのをわかりやすい表にするのは意外と難しいものです。本記事では、月次の表と日次の表でわけて管理する場合の一例を挙げます。スケジュール管理にお悩みの方は、ひとつのアイデアとして参考にしてみてください。
はじめに
技術的に大切なポイントは赤色のアンダーラインで、アイデアとして大切なポイントは黄色のアンダーラインで強調しています。また、関数の記述は黒いボックス内にあります。ポイントだけ読みたい方は活用してください。
完成形の概要
完成形の概要は下記のとおりです。「月次表」と「日次表」の2つのシートを使用します。
2つのシートは役割がわかれており、月次表は元となるデータを入力するシートで、日次表は必要なデータを抽出して表示するシートです。
月次表には担当者の氏名と勤務時間を入力します。出勤日数は関数で自動的に計算します。
日次表では任意の日付をセルB1に入力するだけです。セルB1に入力すると、月次表から出勤者の氏名、月間の出勤日数、勤務時間を自動的に呼び出し、勤務時間を色付けして視覚化してくれます。
使用する機能は、関数と条件付き書式のみです。マクロは使用しません。
月次表の作り方
月次表はほとんど見た目どおりのシートです。A列が担当者の氏名、B列が出勤日数、C列以降が勤務時間帯の入力欄です。B列の出勤日数については、COUNTA関数を使用して自動的に表示します。
セルB2の数式の記述は次のとおりです。
=COUNTA(C2:AG2)エクセルに上記の数式を入力する場合は、指定のセルに数式を貼り付け・入力後に、そのセルをコピーして別のセルに反映させてください。数式を自動修正するエクセルの機能によって、各セルにおいて適切な記述に変わります。
なお、勤務時間は半角入力で「NN:NN-NN:NN」の形で入力してください（Nは0-9の半角数字）。この形式で入力しない場合、日次表が正しく機能しません。
日次表の作り方
日次表では複数の関数を使用して、1日分のスケジュールを月次表から呼び出して表示させます。A列からO列まで数式を入力しているため、列でわけて説明します。
「完成形の状態ができれば内容を理解できなくてもよい」という方は、記事を読み飛ばしながら数式だけを探して、指定のセルにコピーして作ってみてください。コピーする数式は、上述のCOUNTA関数のように黒いブロック内に記述しています。コピー先は各ブロックの右上を参照してください。
なお、関数に慣れていない方でも理解できることを目的としているので、詳しく説明しています。
日次表の処理の全体像
簡単に処理の全体像の説明をします。個別の数式の記述を確認したい方は読み飛ばしてください。なお、この日次表に利用したアイデアとして重要なのはA列の関数です。
日次表における処理の全体像としては、A列で抽出したいデータの行番号を探し出し、この行番号をもとにB列からD列にてINDEX関数で検索する、というものです。
E列とF列では、勤務時間の色付けをするための前処理として、D列の情報をLEFT関数またはMID関数で始業時間と終業時間に分解します。G列からO列の関数に含めることもできますが、記述が長くなりすぎて可読性とメンテナンス性が悪いので分割しています。
G列からO列の各セルは、この始業時間と終業時間を参照して、IF関数で判定して色を付けるセルなら1、色を付けないセルなら0を表示するセルです。基本の文字色を白に設定しているため、画面上は数字が見えません。
セルの色付けについては、G列からO列に条件付き書式を設定しています。本記事では、1を含むセルの背景色と文字色をオレンジに変更する設定です。
A列の数式（その1）
A列では、MATCH関数とOFFSET関数を組み合わせることで、セルB1の値を参照して月次表で検索する範囲を調整しながら検索しています。セルA4とセルA5以降の数式は記述が異なるため注意してください。
セルA4の数式の記述は次のとおりです。
=MATCH("*",OFFSET(月次表!$B$2:$B$100,0,$B$1),0)+1MATCH関数にてワイルドカードである*（アスタリスク）を検査値に利用して、1文字以上の文字列を含むセルを検索しています。たとえば、月次表の「1日」の入力セルであるC2:C6の範囲で*を検査値にしてMATCH関数で検索させると、「08:00-17:00」が入力されているセルC2が最初に一致するため、1が返ってきます。1が返ってくるのは、C2:C6という範囲において、C2が1番目のセルだからです。この関数の目的は、見つかったセルの行番号を探すことですが、計算結果が1だと実際の行番号である2とは1ずれています。そのため、MATCH関数の計算結果に+1をすることで行番号と一致するようにしてあります。
OFFSET関数は、MATCH関数の検査範囲の調整のために使用しています。基準とする検査範囲をB2:B6に置き、セルB1に入力した数字（日付）の分だけ検査範囲が移動する仕組みです。
A列の数式（その2）
セルA5以降の数式の記述は次のとおりです。下段には、セルA4の数式を比較用に再度表示しています。
=IFERROR(MATCH("*",OFFSET(月次表!$B$2:$B$100,A4-1,$B$1),0)+A4,"")=MATCH("*",OFFSET(月次表!$B$2:$B$100,0,$B$1),0)+1おおむねセルA4と同じ内容ですが、次の点で異なります。
まず、冒頭にIFERROR関数を追加し、計算結果がエラーになった場合は何も表示しないように変えてあります。MATCH関数で一致するセルが見つからないとエラーになり、表が#N/Aだらけになって見映えが悪いためです。
次に、MATCH関数の計算結果に足す値を+1から+A4に変更しています。これは、MATCH関数が相対位置を示すという性質上、セルA5以降は+1だと正しい行番号にならないためです。今回は、ひとつ前の検索で見つかった行番号を足すことで正しい行番号になります。
最後に、OFFSET関数に行数の移動を追加しています。セルA4では0だった部分をA4-1に替えています。2番目以降に検査値に一致するセルをMATCH関数で探させるための調整です。検査値と最初に一致したセルをOFFSET関数を使って検査範囲から外すことで、2番目以降に一致するセルを見つけられるようにしています。
OFFSET関数による検査範囲の変化は次のようになっています。OFFSET関数での範囲操作は応用の利くアイデアなので、下記の仕組みを理解しておくと役に立つかと思います。
※画面表示上、検査範囲（赤枠）自体が小さくなっているように見えますが、実際は同じサイズのまま画面外に伸びています。
（1）セルA4では上記の検査範囲で検査し、月次表シートのC2が最初のセルとして見つかる。
（2）セルA5では、OFFSET関数によって月次表シートのセルC2が検査範囲から外れ、C3から検査する。これにより、C4が最初のセルとして見つかる。
（3）セルA6では、OFFSET関数によって月次表シートのセルC4が検査範囲から外れ、C5から検査する。これにより、C5が最初のセルとして見つかる。
ここまでできたら、日次表は半分以上できたようなものです。
B列・C列の数式
A列の行番号を参照してINDEX関数を使って該当セルの値を返します。
セルB4の数式の記述は次のとおりです。
=IF(A4<>"",INDEX(月次表!$A$1:$A$100,A4),"")セルC4の数式の記述は次のとおりです。
=IF(A4<>"",INDEX(月次表!$B$1:$B$100,A4),"")エラー表示の回避のためにIF関数を使用していますが、IFERROR関数を使用しても同様の結果が得られます。
D列の関数
A列の行番号を参照してINDEX関数を使って該当セルの値を返します。
セルC4の数式の記述は次のとおりです。
=IF(A4<>"",INDEX(月次表!$C$1:$F$100,A4,$B$1),"")勤務時間については、氏名や出勤日数と違い、抽出する日付によって検査範囲の列番号が変わるため、INDEX関数の3つ目の引数に＄B$1を追加して列を調整しています。
E列・F列の数式
D列の値からLEFT関数またはMID関数を使って時間の部分のみを切り出します。
本記事では時間単位でしか管理していませんが、分の部分を切り出すなどして少し改修すれば分単位での管理も可能です。
セルE4の数式の記述は次のとおりです。
=LEFT(D4,2)セルF4の数式の記述は次のとおりです。
=MID(D4,7,2)G列からO列の数式
セルG3からセルO3の各列に対応する時間の値が、E列の始業時間の値以上かつF列の終業時間の値未満であれば1、そうでなければ0を返します。セルの文字色は白に設定してください。
セルG4の数式の記述は次のとおりです。
=IFERROR(IF(AND(G$3>=INDIRECT("E"&ROW())*1,G$3<INDIRECT("F"&ROW())*1),1,0),"")単体のセルで考えるとINDIRECT関数は必要ではないのですが、コピーだけで全範囲に適用できるように採用しています。エクセルでのコピー時に適用される関数の自動修正では対応できない部分について、INDIRECT関数で対応しています。INDIRECT関数に*1がついているのもポイントです。始業時間と終業時間が数値ではなく文字列のため、*1がないとIF関数が正しく判定できません。そこで、文字列を数値として扱わせるために1を乗算しています。E列・F列がそもそも数値の場合は*1は不要です。
条件付き書式について
上記のG列からO列の関数を入力したセルすべてを選択して条件付き書式を設定します。
ホームタブの条件付き書式より、セルの強調表示ルール＞指定の値に等しいを選択し、指定の値として1を入力します。書式については、背景色・文字色ともにオレンジを選択します。別のお好みの色を選択して構いません。
なお、G列からO列の関数を条件付き書式のルールとして修正し、登録しても同様の結果を得られます。筆者の個人的な感覚として、条件付き書式のルールに複雑な関数を登録するとメンテナンスしにくいと感じるため、本記事ではこのようにしています。
おわりに
以上、スケジュール管理表の作り方の一例でした。
使用上の注意点として、月次表を更新する際は、現在の月次表をコピーして記録用とし、コピー元のシートのデータを削除して使用してください。日次表のシートが参照しているのは常にコピー元の月次表です。INDIRECT関数を使用すれば、コピー後のシートを参照することも可能です。
記述している関数の ネスト も最大で3回までなので、可読性・メンテナンス性ともにそこそこだと思います。足りない部分はいろいろ改修してみてください。
コメント