最初に作ったシフト表は、出勤日数の列に =COUNTIF(C7:AG7,"早")+COUNTIF(C7:AG7,"中")+COUNTIF(C7:AG7,"遅") と書いていた。区分が3つのうちは困らない。夜勤を足した月に、15人分の式を全部直した。半日を足した月にまた直した。労働時間の列は、もっとひどかった。区分ごとの時間を式の中に直接書いていたので、遅番の終了時刻が変わったときに、どの式に9が入っているのかを探して回った。
配布しているシフト表のテンプレートは、この失敗のあとに組み直したものだ。式は3層に分けてある。記号を入れる層、記号を労働時間に変える層、時間を集計する層。ここに書く式と数字は、2026年9月20日にMicrosoft 365のExcel（バージョン16、日本語）で、配布しているファイルをそのまま開いて確かめた。
記号を1回だけ時間に変えて、あとは数字として扱う
3層のいちばん下は「シフト区分」シートで、記号と時間の対応表だ。1行が1区分。労働時間の列には次の式が入っている。
=IF(OR(C2="",D2=""),0,ROUND((IF(D2<C2,D2+1,D2)-C2)*24-IF(E2="",0,E2)/60,2))C列が開始、D列が終了、E列が休憩の分。夜勤の22:00から翌7:00は、終了が開始より小さいので1日を足してから引く。休憩60分を引いて8時間。休みや有給は開始と終了が空欄なので0になる。配布ファイルの初期値では、早番・中番・遅番・夜勤が8、半日が4、休み・有給・公休が0だ。
真ん中の層は「計算」という非表示のシートで、シフト表と同じ位置のセルに、その記号の労働時間を引いてくる式が465個（15人×31日）入っている。
=IF('シフト表'!C7="",0,IFERROR(VLOOKUP('シフト表'!C7,'シフト区分'!$A$2:$F$11,6,FALSE),0))空欄なら0。記号が対応表になければIFERRORで0。この層があるおかげで、上の集計は全部「数字の表」に対する式になる。
いちばん上の集計は単純で、右端の出勤日数は =COUNTIF('計算'!C7:AG7,">0")、労働時間は =SUM('計算'!C7:AG7)、概算人件費は =AI7*B7（労働時間×時給）、有給は記号を直接数える =COUNTIF(C7:AG7,"有")。下の日別集計は、出勤人数が =COUNTIF('計算'!C$7:C$21,">0")、総労働時間が =SUM('計算'!C$7:C$21)、人件費が =SUMPRODUCT('計算'!C$7:C$21,$B$7:$B$21) で、その日の各人の時間と時給を掛けて足している。
サンプルデータで確かめた値はこうだ。山田は早番10日で80時間、時給1,200円なので96,000円。1日は4人出勤、28時間、33,000円。月合計は252時間、294,600円。手計算と一致する。
区分を増やしても式を直さなくていい理由
区分ごとにCOUNTIFする方式では、区分が1つ増えるたびに、出勤日数の式と労働時間の式を全員分書き換える。3層にすると、「シフト区分」シートの空き行（10行目と11行目）に記号と時間を足すだけで、計算シートのVLOOKUPが勝手に拾う。上の集計は「0より大きいセルの数」と「合計」なので、区分の名前を知らない。
もう1つの理由は、時間の変更に強いこと。遅番を13:00〜22:00から12:00〜21:00に変えるなら、対応表の2つのセルを直せば、465個の式と全員の集計が同時に変わる。私が式の中に9を直接書いていたときは、これができなかった。
COUNTAで出勤日数を数える案は、最初の版で採用して失敗した。COUNTAは「休」も数える。山田の行はCOUNTAで14、休みが4つあるので、正しい出勤日数は10。確かめると、COUNTIFの「0より大きい」なら10が返る。休みを記号で入れる表では、空欄でないことと出勤は別物だ。
静かに間違う4つの場面と、見つける式
このテンプレートは、間違ったときにエラーを出さない場面がある。全部、手元で再現した。
記号の末尾にスペースが入る。「早 」と入れると、VLOOKUPは対応表に「早 」がないので、IFERRORが0を返す。エラーは出ず、その日が出勤日数から消える。山田の1日を「早 」にすると、出勤日数は10から9、労働時間は80から72になった。リストから選べば起きないが、手で打つと起きる。同じことは、対応表にない記号（「×」や「休み」）でも起きる。
見つけるには、行ごとに未登録の記号を数える式を足す。
=SUMPRODUCT((C7:AG7<>"")*(COUNTIF('シフト区分'!$A$2:$A$11,C7:AG7)=0))空欄でなく、かつ対応表に一致しないセルの数。山田の行に「早 」と「×」を入れて2が返るのを確かめた。この式をAL列あたりに入れて、0でない行だけ見ればいい。
時給に「1,200円」のように文字で入れる。右端の人件費 =AI7*B7 は値のエラー（#VALUE）になるので気づける。ところが下の日別人件費のSUMPRODUCTは、文字を0として扱い、エラーを出さずにその人の分だけ抜けた合計を返す。1日の人件費が33,000円から23,400円になった。月合計は右端の列を足しているのでエラーになる。同じ表の中で、同じ入力ミスに対して、エラーになる式と黙って減る式が同居している。SUMPRODUCTが文字を0にする挙動は、掛け算の形で書くとエラーになるなど、書き方で変わる。この話はAIに数式を頼むときの記事に書いた。
時給が文字になっている人数は =SUMPRODUCT(ISTEXT(B7:B21)*1) で分かる。1が返ったら、B列を見に行く。
時給が空欄の人は、意図した動作として0円になる。月給の人は空欄にしておく前提で、人件費の列も日別の合計も、その人の分を0として足す。エラーは出ない。これは間違いではないが、時給を入れ忘れた人と月給の人が区別できないことは知っておく。
有給に時間を持たせると、出勤日数と人件費に入る。対応表の「有」に9:00〜18:00、休憩60分を入れると8時間になる。佐藤の1日を「有」にすると、出勤日数は10のまま、労働時間80、人件費88,000円、有給列は1。有給の賃金を人件費に含めたいならこの設定でよいが、出勤日数にも数えられる。出勤日数から有給を引きたければ、右端の出勤日数から有給列を引いた列を足す。
半日を0.5日で数える式
初期値の「半」は4時間で、出勤日数では1日に数えられる。半日を0.5日にしたい職場では、出勤日数の式を次に差し替える。
=SUMPRODUCT(('計算'!C7:AG7>4)*1)+SUMPRODUCT(('計算'!C7:AG7>0)*('計算'!C7:AG7<=4)*0.5)4時間を超える日は1、0より大きく4時間以下の日は0.5。高橋は半日が9日あり、元の式では9、この式では4.5と出た。境目の「4時間」は自分の職場の定義に合わせて変える。
自分の表に移すときに直す場所
行や列の数が違う表に移すなら、直す場所は3か所だけだ。計算シートの範囲（15人なら7〜21行目、31日ならC〜AG列）、右端の集計の範囲、下の日別集計の範囲。人を増やすときは、行を挿入するのではなく最後の行をコピーして増やし、集計の範囲を広げる。行を挿入すると、条件付き書式の範囲がずれることがある。
夜勤の時間は、勤務を始めた日に全部載る。22:00〜7:00を1日に入れると、1日の労働時間に8時間、2日には0。日をまたいだ分を翌日に載せたい集計は、この表ではやっていない。深夜割増（22時〜5時の25％）も同じで、人件費はあくまで時給×労働時間の概算だ。割増を含めた計算は、勤怠管理のテンプレートのほうで扱っている。
