team
エクセルで勤務表を自動作成して休み希望まで反映させたいと考える時点で、毎月の組み直しに半日から1日が消えているはずです。紙やチャットで希望を集め、人数を数え、資格の配置を確かめ、誰かの希望を通すたびに別の場所が崩れる。この作業を関数で片づけたいという発想は正しく、そして多くの場合、期待した場所では効きません。
先に結論を書きます。エクセルの自動化で時間が減るのは、表を組む工程ではなく、希望を集める工程と検算する工程です。組む工程に手を入れる前にこの2つを固めたほうが、同じ労力で減る時間が大きくなります。組む工程そのものを関数やソルバーに任せようとすると、人数と日数が増えた時点で必ず壁に当たります。この記事では、自動作成という言葉が指している3つの水準を分け、希望の集め方の設計、シート構成と関数の具体的な組み方、ソルバーを使う場合の上限、検算の列の作り方、そして専用のシステムに移す境目までを順に扱います。
同じ「自動作成」という言葉で、まったく難易度の違うものが語られています。ここを分けないまま作り始めると、途中で目指す形が変わって作り直しになります。
1つめは、入力を楽にする水準です。日付と曜日の自動生成、祝日の色分け、勤務記号の入力規則、シフト記号から勤務時間への変換。関数と条件付き書式だけで組めて、崩れにくく、引き継ぎもできます。作業時間で言えば、素案を組む前の下準備がなくなる分が効きます。
2つめは、検算を自動にする水準です。時間帯ごとの必要人数を満たしているか、連続勤務が上限を超えていないか、資格を持つ人が各日に1人以上いるか、希望をいくつ通せたか。これらを人が目で追うのをやめて、セルの色と件数で出す形にします。ここが実は最も投資対効果が高い場所です。目で追う検算は30分から1時間かかり、しかも見落としが残ります。
3つめが、割り当てそのものを解かせる水準です。誰をどの日のどの枠に置くかを、ソルバーやVBAに計算させます。うまく動いたときの効果は大きい一方で、規模の上限があり、条件が1つ増えるたびに作り直しが必要になり、作った本人以外が保守できなくなります。
職場によって正解は変わります。ただ、勤務表をエクセルで作り続けている職場が多いのは、惰性ではなく理由があります。人と勤務の組み合わせのルールが職場ごとに違いすぎて、汎用の画面に収まらないからです。専用のシステムに移しても、最後の微調整はエクセルに書き出して手で直しているという話は珍しくありません。だからこそ、どの水準を狙うのかを先に決める価値があります。
労働時間の枠組みそのものは、表の組み方より前に置かれる前提です。1日の労働時間と1週の労働時間には法律上の原則があり、変形労働時間制や時間外の取り扱いは制度の選び方で変わります。1日8時間・週40時間という原則の当てはめ方や、シフト制での休憩と休日の数え方は職場の制度によって結論が変わるため、表を作る前に厚生労働省の案内や所管の労働基準監督署で確かめるのが確実です。関数で自動化できるのは数え方であって、どう数えるべきかの判断ではありません。
自動作成が動かない原因を追うと、ほとんどが希望の入り口にあります。集まってくる形がバラバラなら、関数は何もできません。
よくある状態を並べます。紙に手書きで「15日と16日は休みたいです。できれば22日も」と書かれている。チャットに「来月の第2週あたり、実家の用事で1日ください」と流れてくる。締切を過ぎてから「やっぱり20日も」と追加が来る。この3つが混ざると、集計の前に解読の作業が入り、ここで30分から1時間が消えます。
直し方は、入り口を1枚のシートに固定することです。縦に日付、横に人、セルには決めた記号だけを入れる。記号は3つあれば足ります。絶対に休みたい日、できれば休みたい日、働ける日です。この3段階は後の集計で決定的な差を生みます。すべての希望を同じ重みで扱うと、通せなかったときにどれを諦めたのか説明できなくなり、不公平の感覚だけが残ります。
入力規則を使って、決めた記号以外を受け付けないようにしておきます。データの入力規則でリストを指定し、日本語入力を自動でオフにする設定まで入れておくと、全角と半角の混在という地味な事故が消えます。全角の記号と半角の記号は別物として数えられるため、混ざるとCOUNTIFSが静かに数え落とします。この事故は表が壊れて見えないので、検算の列を作るまで気づけません。
締切の運用も、表の設計の一部です。締切を過ぎた人の扱いを先に決めておくと、催促が交渉になりません。未提出の人の欄を色で出すだけの列を1つ作り、締切の時点でその色が残っている人は働ける日として扱うと書いておく。この一文があるかないかで、回収にかかる時間が変わります。
希望の集約が済んだ段階で、通せる見込みを先に出してしまうのも効きます。絶対に休みたい日の合計を日付ごとに数え、その日の必要人数と引き算する。この時点でマイナスになる日があれば、表を組む前に相談が必要です。組んでから崩れるのと、組む前に分かっているのとでは、相談の質がまるで違います。
希望が集まっても、組む条件が数になっていなければ関数は動きません。ここで曖昧なまま進むと、埋める作業のたびに判断が発生して、結局は頭の中で解く作業に戻ります。決めておくべき数は5つです。
1つめは、時間帯ごとの必要人数です。1日あたり何人ではなく、朝の時間帯に何人、日中に何人、夜に何人という形まで割る必要があります。1日8人と決めても、その8人が全員日中に集まっていれば意味がありません。交代制の職場では、早番、日勤、遅番、夜勤のそれぞれに下限を置きます。曜日で変わる場合は曜日ごとの表にし、月末や特定の日だけ増える場合はその日を別に書き出します。
2つめは、人ごとの上限です。月の勤務日数、月の合計時間、週の勤務日数の3つを名簿に持たせます。短時間勤務の人や、扶養の範囲を意識して月の合計時間に上限を持つ人がいる職場では、この列がないと組んだ後に必ず作り直しになります。上限だけでなく下限も要る場合があります。最低これだけは入れてほしいという希望を持つ人がいるからです。
3つめは、連続勤務の上限と勤務の間隔です。何日まで続けてよいか、夜勤の翌日に何を入れてよいか、夜勤の後に必要な休みは何日か。この条件は職場ごとの取り決めと制度の両方に関わるため、数にする前に何を根拠にしているかを確かめておきます。
4つめは、資格や役割の最低人数です。特定の資格を持つ人が各日に何人必要か、時間帯ごとに必要か、責任者にあたる役割が毎日1人置かれるか。この条件は検算で最も見落としやすく、配った後に発覚しやすい項目です。
5つめは、希望の優先順位と公平の測り方です。ここだけは数にしていない職場が多く、そして後で最もこじれます。次の節で扱います。
この5つを書き出す作業は、初回は1時間から2時間かかります。ただし一度書けば翌月以降は見直すだけで済み、担当が代わったときの引き継ぎ資料にもそのまま使えます。頭の中にしか無い条件は、引き継げません。
同じ日に休みの希望が集まり、全員を通すと人数が足りなくなる。この状況は毎月起きます。ここで決め方を持っていないと、声の大きさや言い出す早さで決まることになり、提出する側の納得が失われます。
配り方には4つの型があります。1つめは先着で、提出の早い順に通す形です。運用は単純ですが、締切前に出す人が有利になるため、締切を守らない人に催促している職場では逆効果になります。2つめは持ち回りで、前回通らなかった人を次に優先する形です。記録が必要な代わりに、長い目で見た公平は保たれます。3つめは理由で優先する形で、通院や家族の行事といった事情を先に置きます。公平の感覚は得られやすいものの、理由を申告させる負担と、判断する側の裁量が増えます。4つめが抽選で、透明性は最も高い一方、事情を持つ人が外れたときに別の相談が必要になります。
現実的には、絶対に休みたい日は理由か持ち回りで扱い、できれば休みたい日は抽選か先着で扱うという二段構えが機能します。記号を3段階にしておく意味はここにあります。全部を同じ重みで扱うと、この二段構えが作れません。
どの型を選んでも、記録は必要です。人ごとに、絶対に休みたい日を何件出して何件通ったかを月ごとに残す。集計のシートに1行足すだけで済みます。この記録があると、翌月に優先する根拠が示せて、通らなかった人への説明が交渉になりません。
もう1つ、公平を数で見るなら土日と祝日の出勤回数を人ごとに数えておきます。平日の日数が同じでも、土日の割り当てが偏っていれば不公平として受け取られます。人ごとの土日出勤回数を出して、最大と最小の差が2を超えたら色を出す。この列があるだけで、組んだ本人が気づけなかった偏りが見えます。
具体的な組み方に入ります。1枚のシートに全部を詰めると必ず壊れるため、役割でシートを分けます。
シートは4枚です。1枚めが名簿で、名前、雇用区分、保有資格、1か月の上限時間、週の上限日数を持ちます。2枚めが希望表で、縦に日付、横に人、記号だけが入ります。3枚めが勤務表で、これが最終的に配る表です。4枚めが集計と検算で、人ごとの日数と時間、日ごとの人数、違反の件数を出します。
勤務表のシートは、日付の列を数式で作ります。月初のセルに開始日を入れ、その右を前日プラス1日にしておけば、開始日を書き換えるだけで翌月の表になります。曜日はTEXT関数で出し、土日と祝日は条件付き書式で色を変えます。祝日は別シートに一覧を持ち、COUNTIFで一致を見る形が扱いやすいです。年によって日付が動く祝日があるため、一覧は毎年見直す前提で置きます。
希望の反映は、勤務表のセルに希望表を参照する数式を置くところから始めます。XLOOKUPかINDEXとMATCHの組み合わせで、その人のその日の希望記号を引いてきます。絶対に休みたい日なら休みの記号を確定で入れ、それ以外を空欄にしておく。この段階で、確定している休みだけが埋まった骨組みができます。
ここから先、残った空欄を埋める作業が本体です。関数だけで完全に埋めることはできませんが、埋めやすくする準備は関数でできます。日ごとの残り必要人数を出す列、人ごとの残り必要日数を出す行、そして両方が0になったら色が消える条件付き書式。この3つを用意すると、埋める作業が「色が残っている場所を探す」作業に変わります。頭の中で条件を保持する必要がなくなるため、20人規模で3時間かかっていた工程が1時間台に入ることは十分あります。
集計側は、SUMPRODUCTとCOUNTIFSの2つで足ります。日ごとの出勤人数は、その日の列に対して出勤記号をCOUNTIFで数える。人ごとの勤務時間は、記号と時間の対応表を作ってSUMPRODUCTで掛け合わせる。記号から時間を求める形にしておけば、早番の時間が変わったときに対応表の1か所を直すだけで全体が追随します。勤務表のセルに直接時間を書いてしまうと、この変更が地獄になります。
資格の条件は、名簿の資格列と勤務表を突き合わせるCOUNTIFSで出します。その日に出勤している人のうち、特定の資格を持つ人が何人かを数え、1未満なら色を出す。この列が1つあるだけで、目で追う確認のうち最も見落としやすい部分が消えます。
割り当てそのものを計算させたい場合、エクセルにはソルバーというアドインが標準で付いています。アドインの追加から有効にすると、データタブに現れます。
組み方の考え方は決まっています。人と日の組み合わせごとに0か1の変数を置き、1なら出勤とする。制約として、日ごとの合計が必要人数と等しいこと、人ごとの合計が上限日数以下であること、絶対に休みたい日の変数が0であることを入れる。目的関数には、できれば休みたい日に出勤した件数の合計を置いて、それを最小にする。これで希望の充足を最大化する割り当てが出ます。
ただし、ここに明確な上限があります。エクセルに付属するソルバーで指定できる変数のセルは200個までです。この上限は公式の案内に明記されています（Solver を使用して問題を定義して解決する）。人と日の組み合わせで変数を作ると、20人で31日の月は620マスになり、上限の3倍を超えます。1か月分を一度に解かせることは、標準のソルバーではできません。
現実的な使い方は2つあります。1つは期間を切ることです。6人で31日なら186マスで上限に収まります。10人なら20日分ずつ、2回に分けて解く。区切りの前後で連続勤務の数え方が切れてしまうため、境目の数日は手で見る前提になります。もう1つは、枠を絞ることです。全員の全日を解かせるのではなく、資格の条件が絡む枠だけをソルバーで決め、残りは色を頼りに手で埋める。この分け方は、一番難しい部分だけを機械に渡すという意味で筋がよく、上限にも当たりにくくなります。
もう1つ知っておくべき性質があります。ソルバーが返すのは「条件を満たす1つの解」であって、人が納得する解とは限りません。数の上では公平でも、特定の人に土日が固まる配置が返ることは普通に起きます。公平を数にしていない条件は、機械には見えません。土日の出勤回数を人ごとに数えて、その最大と最小の差を目的関数に加えるところまでやれば近づきますが、条件を足すほど解けなくなります。この綱引きが、ソルバーで勤務表を組む作業の実態です。
VBAで手続き的に埋める方法もあります。上限の縛りはなくなりますが、書いた人以外が直せないという別の問題が来ます。異動や退職でその人がいなくなった瞬間に、毎月の勤務表が誰も触れないファイルになります。これは技術の問題ではなく運用の問題で、後述する引き継ぎの決めごとと同じ話です。
勤務表で起きる事故のほとんどは、組み方の失敗ではなく検算の抜けです。配った後に見つかると、直すだけでなく周知のやり直しが必要になり、手戻りが倍になります。
先に作っておくべき検算は5つです。1つめは人数の充足で、日ごと、時間帯ごとに必要人数を満たしているか。2つめは連続勤務で、何日続いているかを数え、上限を超えたセルに色を出す。3つめは資格の配置で、各日に必要な資格の保有者が置かれているか。4つめは上限時間で、月の合計時間が名簿の上限を超えていないか。扶養の範囲を意識して上限を持つ人がいる職場では、これを超えると本人の手取りに影響するため、色で出す優先度が高い項目です。5つめが希望の充足で、絶対に休みたい日が何件通り、できれば休みたい日が何件通ったかを人ごとに出します。
連続勤務の数え方だけ、少し工夫が要ります。単純な連続判定では、月をまたぐ部分が見えません。前月の最終週を勤務表の左に数日ぶん置いておき、そこは参照専用にしておくと、月初の連続勤務が正しく数えられます。この数日を置いていないために、月初に7連勤が生まれる事故が起きます。
5つの検算を1つのセルに集約するところまでやると、運用が変わります。違反の合計件数を出すセルを1つ作り、0以外なら赤くする。配る前にそのセルだけ見れば済む形にしておくと、確認の手順が人に依存しなくなります。担当が代わったときに引き継げるのは、この1セルの意味だけです。
検算は、作るのは面倒でも一度作れば毎月効きます。5つの検算を関数で置く作業は、慣れていれば2時間から3時間です。月に1時間の確認作業が数分に変わるなら、3か月で元が取れます。作り込みの投資対効果が最も高いのがこの部分で、割り当てを解かせる仕組みに時間を使う前に、まずここを固めるほうが確実に楽になります。
希望の充足率は、公平の説明にも使えます。絶対に休みたい日を通せなかった人が誰で、何件だったかを月ごとに残しておくと、翌月に優先して通す根拠になります。この記録を持たないまま「今月は我慢して」を繰り返すと、提出する側が希望を出さなくなり、最終的に表そのものが実態を映さなくなります。入力する人が減ると、表は必ず嘘になります。
移す判断は、機能の多さではなく、いま何に時間を使っているかで決まります。
希望の回収と集計を自動にする道具については、専用のシステムが持っている機能が明確です。
従業員から収集した希望シフトをテンプレートに入力するだけで勤務表が作成できます。希望シフトを基に表を一から作成する必要がなく、勤務時間の集計、人件費の計算などの自動化が可能です。 出典: shifop.jp
判断の軸を表にします。費用は公開されているプランで確かめられる範囲が職場ごとに違うため、ここでは金額ではなく性質で並べます。
| 手段 | 向いている状況 | 強い所 | つまずく所 |
|---|---|---|---|
| 関数と条件付き書式だけ | 20人までで、ルールが職場固有 | 費用が増えない。誰でも中身が読める | 埋める作業は人が残る |
| ソルバー | 6人前後の枠、資格の条件が難しい | 希望の充足を数で最適化できる | 変数の上限があり月単位では解けない |
| VBAで自動化 | 毎月の形が固定している | 手作業がほぼ消える | 書いた人しか保守できない |
| シフト管理システム | 回収と周知に時間が消えている | 提出から共有まで一続きになる | 職場固有の条件が画面に収まらないことがある |
| 進行の板にまとめる | 勤務表と工程と連絡が別々 | 同じ画面で状態が1つになる | 勤怠の法定帳簿としての機能は別に必要 |
境目を1つ挙げるなら、回収と周知に使っている時間です。素案を組む工程が重い職場は、エクセルの検算を作り込むだけで大きく楽になります。一方で、希望を集めるのに催促が要り、確定した表を配った後に版が分かれているような職場では、エクセルをどれだけ磨いても効きません。ファイルを配る形そのものが原因だからです。
もう1つの境目は、確定後の変更の頻度です。月に数回なら手で直せます。週に何度も交代が入る職場では、直した人以外が最新を持っていない状態が常に生まれます。エクセルは1人が開いて直す道具で、同時に複数人が見ながら直す道具ではありません。ここが限界だと感じたら、道具の性質そのものを変える段階です。
勤務表のファイルは、作った人が異動した瞬間に価値を失いがちです。これを防ぐ決めごとは3つあります。
1つめは、数式の中に定数を書かないことです。必要人数、上限日数、記号と時間の対応。これらはすべて別シートの表に置き、数式はそこを参照する形にします。来月から早番の時間が30分早まったとき、直す場所が1か所であれば誰でもできます。数式の中に埋まっていれば、探せる人だけの仕事になります。
2つめは、ファイルの版を増やさないことです。ファイル名に日付や「最新」「修正版」を足していく運用は、必ず事故を呼びます。共有のドライブに1つだけ置き、月ごとにシートを足す形にする。配るときはPDFか印刷にして、編集できるファイルは1つに保つ。この決めごとがないと、違う版を見ている人が必ず出ます。
3つめは、手順を同じファイルに書くことです。1枚めのシートに、締切の日、希望の記号の意味、検算のセルの場所、直してよい場所と触ってはいけない場所を箇条で書いておく。別の文書に書くと読まれません。ファイルを開いた人の目に入る場所にあることが条件です。
VBAを使う場合は、この3つに加えてコードの中身に日本語のコメントを残します。動く理由が書かれていないコードは、次の担当者にとって触れない箱です。触れない箱になった時点で、自動化は負債に変わります。
引き継ぎの目安として、作った本人以外が1人でも翌月の表を作れる状態になっているかを一度試すのが確実です。手順を書いた本人が横で見ていると、書かれていない暗黙の判断に気づけません。別の人に最初から通してもらい、詰まった場所だけを手順に足していく。この確認を1回やっておくと、異動や急な休みで表が止まる事態を避けられます。勤務表は、止まると全員の予定が止まる種類の仕事です。作り込みの精度よりも、止まらないことのほうが優先されます。
エクセルの限界の多くは、関数の力不足ではなく、ファイルという形から来ています。1人が開いて直す前提の道具に、複数人が同時に見て直す仕事を載せているから無理が出ます。
勤務表を工程表や連絡と同じ画面に置くと、変わることが3つあります。1つめは、最新がどれかという問題が消えることです。画面が1つなら版は分かれません。2つめは、変更の理由が同じ場所に残ることです。誰の希望でどの日を動かしたかがカードに残れば、翌月の公平の判断材料になります。3つめは、当日の交代が待たなくなることです。担当が表を直すのを待つのではなく、当事者が直接書ける形にできます。
どの表示で見るかは仕事によって変わります。人と日のマスで見たいときと、工程の前後関係で見たいときと、暦で見たいときがあり、移行なしで切り替えられると使い分けが定着します。板の種類や時間割の表示を含めて何ができるかはできることにまとまっています。
道具を増やす判断で最後に残るのは費用です。人数で急に上がる料金設計だと、6人めが入る月に導入そのものが揺れます。機能で絞らず、区切るのは人数とボードの数だけという形なら、試す段階で機能の比較に悩む必要がなくなります。実際の区切りは料金で確かめられます。
いま使っている道具から移す場合の比較は、それぞれの相手ごとに整理してあります。どの道具にも向いている使い方があり、乗り換える理由がない場合もあります。判断材料は比較の一覧にあります。加えて、勤怠の記録として法定の要件を満たす必要がある場合は、勤務表を作る道具と記録を残す道具を分けて考えるのが安全です。この線引きや、データの扱いについての疑問はよくある質問で扱っています。
なお、記録としての要件や労働時間の数え方は制度と職場の制度設計で変わります。表を作る道具をどれにするかとは別の判断になるため、所管の窓口や社会保険労務士に確かめたうえで決めるのが確実です。
希望の反映と検算までは関数だけで自動になりますが、割り当てを埋める工程は完全には自動になりません。付属のソルバーは変数のセルが200個までのため、20人で1か月の620マスは一度に解けません。期間や枠を区切って使うか、色で残りを示して人が埋める形が現実的です。
縦に日付、横に人を並べた1枚のシートに、決めた記号だけを入れてもらう形が扱いやすいです。記号は絶対に休みたい日、できれば休みたい日、働ける日の3段階で足ります。入力規則で記号以外を弾いておくと、全角と半角の混在で数え落とす事故が防げます。
XLOOKUP、COUNTIFS、SUMPRODUCT、TEXT、それに条件付き書式とデータの入力規則で、ほとんどの勤務表は組めます。記号から勤務時間を引く対応表を別シートに置き、数式の中に時間を直接書かないようにしておくと、勤務時間が変わったときの修正が1か所で済みます。
素案を組む時間が重いだけならエクセルの検算を作り込むほうが早く、回収の催促や配布後の版の分かれに時間が消えているなら道具を変える段階です。確定後の交代が週に何度も入る職場は、1人が開いて直すファイルの形そのものが原因になっています。