Excelで店舗・部門別の労働時間を集計｜SUMIFSの式と分→時間の例

これは操作を確認するための架空の記録です。集計対象はこの表にある日付の全件で、月や給与締め期間を自動判定する式ではありません。実務では対象期間で抽出した表を使うか、日付条件も追加します。重複記録がないか、休憩を控除した分か、予定と実績のどちらかも元データでそろえてください。

空のシートでD列・E列の表示形式を先に「文字列」にし、次のタブ区切りをD1へ貼り付けます。H列は数値のままにします。H8は未入力、H7は確認済みの0分です。0分の行を使う理由は職場で確認し、空欄の代わりに埋めないでください。

J1を店番、K1を部門コード、L1を合計分とします。J2に文字列の001、K2に文字列の010を入力し、L2に次の式を入れます。D列もE列も条件を満たす行だけをH列から合計します。

この例は480＋510＋480＋0で1470分です。H8の空欄は加算されませんが、これだけで全件確認済みとは判断できません。合計対象、店番、部門の範囲はすべて2～9行にそろえます。行を追加する場合は三つの範囲を同時に広げてください。

SUMIFSの合計だけでは、未入力を含む途中の値と、確認を終えた値を見分けられません。I1を入力確認とし、I2に次の式を入力してI9までコピーします。数値の0は「数値」、空欄は「未入力」、文字列の480や負数は「要確認」となります。

見た目が480でも文字列なら数値として集計されないことがあります。文字列の分を見つけたら元データと単位を確認して数値に直します。すべてを一律に0へ変えたり、エラーを隠したりしないでください。ISNUMBERは文字列を数値へ自動変換せずに判定できます。セル自体がエラー値の場合も、集計前に原因を解消します。

M1を該当行数、N1を未確認行数、O1を状態とします。I列の入力確認式を全行に入れた後で、以下を2行目に入力します。M列は一致する記録の行数で、従業員の人数ではありません。

店番001・部門010は該当5行のうち1行が未入力なので「要確認」です。1470分は現時点の数値行の小計として使い、完成した実績とは扱いません。店番999は該当0行なので「該当なし」です。数値0だけの記録がある場合は、該当行数が1以上で未確認行数が0になります。

L2の1470は分なので、小数時間なら60で割ります。P1を小数時間とし、P2へ=L2/60を入力すると24.5です。24.5は24時間50分ではなく24時間30分です。

時間・分で表示したい場合は、Q1を累計時間とし、Q2へ=L2/1440を入力します。Excelの時間値は1日を1として扱うため、分から変換するときは1日分の1440で割ります。デスクトップ版Excelのセルの書式設定でユーザー定義を[h]:mmにすると24:30と表示できます。h:mmでは24時間で折り返すため、この例は0:30になります。

Microsoftの案内ではWeb版はカスタム数値形式の設定に制約があります。設定できない環境では小数時間のP列を使うか、書式を設定できるデスクトップ版で確認してください。表示形式は入力の誤りや未確認行を解消するものではありません。

店番001を数値として取り込んだ後で文字列に変えても、失われた桁は自動で戻りません。元のコード体系を確認し、D列・E列・J列・K列で同じ形式を使います。001と1を別の識別子として扱う運用では、SUMIFSだけで区別できると決めつけず、S001などの接頭辞付きコードに統一するなど、照合方法を別途確認してください。

同じ行を二度取り込むとSUMIFSも二度加算します。G列の記録IDや元ファイルを使って重複を調べ、予定と実績、確定前と確定後のファイルを混ぜないようにします。このページの式は入力済みの分を集計するもので、勤務開始・終了から休憩を算出したり、給与の割増を計算したりするものではありません。