人事担当必見!  『残業計算』


 「匠への技」第5弾は、勤怠管理の中の残業計算を取り上げました。 労働基準法第37条 に基づく計算区分は以外と複雑で悩みます。今回は作成に非常に時間を要してしまいました。
vol-5では、10種類の関数を使用しましたがそのうちの7種類が新しくお目見えです。
 さらに、vol-1からvol-4までのパターンと説明の仕方を変更しております。
フローチャート(流れ図)で図解しています。
フローチャートの意義は、例えばExcelで作表する時、頭に描いた内容をプロトタイプし構築すると思いますが、結構条件が複雑になると頭で抱えきれなくなる場合があります。そんなときにフローチャートは非常に役に立ちます。システム設計やプログラミング製造の基本です。

 画面デザイン       シート名:残業計算

 明細部 項目説明へJUMP                     10  11


 ※このシナリオは、勤務時間にかかわらずその就業した時間帯で求めるように作っています。
  このフォームの利用は自由ですが、よく内容を確認した上でご使用下さい。


 使用する関数の種類    (新たに用いた関数色)
  1. IF(論理式,真の条件,偽の条件)   
  2. ROW(配列)
  3. MAX(数値1,数値2,・・・・) 
  4. CEILING(数値,基準値)
  5. FLOOR(数値,基準値)
  6. ROUNDUP(数値,桁数)
  7. CHOOSE(インデックス,値1,値2,...)
  8. WEEKDAY(シリアル値,種類)
  9. TIMEVALUE(時刻文字列)
  10. MIN(数値1,数値2,...)
・・・・・5回目登場
・・・・・2回目登場
・・・・・2回目登場
・・・・・初回
・・・・・初回
・・・・・初回
・・・・・初回
・・・・・初回
・・・・・初回
・・・・・初回


 解説
--+ 見出し部  (鑑部)
  1. セルA2 社員番号 (入力)

  2. セルB2 社員名 (入力)

  3. セルB5 西暦年4桁 (入力)

  4. セルD5 月 (入力)

  5. セルF5 基本給 (入力)

  6. セルH5 残業単価

    関数式   =ROUNDUP(F5/160,0)
    160とは? 週休2日制の場合1ヶ月の労働日数が約20日であり、1日あたりの労働時間8時間を乗算すると1ヶ月の労働時間160時間が求められます。
    基本給÷160時間の計算で1時間当たりの時間給単価が求められる公式です。
    尚、計算結果は、切り上げで計算しています。 (労働者に有利となるような給与計算が基本)

  7. セルK5 残業手当

    関数式  =ROUNDUP((H5*G40+H5*H40*H7+H5*I40*I7+H5*J40*J7+H5*K40*K7),0)
    残業単価×各法定残業の合計(1.00〜1.60毎)×基準率を計算し小数点以下切り上げで残業手当を求めます。 (労働者に有利となるような給与計算が基本)

--+ 明細部
 ※明細部のセルは列で説明します。また、関数式のサンプルは明細の先頭行となる8行目を基準としています。

  1. A列 勤務日                                        <画面デザインへ>
    対象月の日を1日から末尾まで行に入力を行います。

  2. B列 曜日

    関数式  =CHOOSE(WEEKDAY($B$5&"/"&$D$5&"/"&ROW(A1)),"日","月","火","水","木","金","土")
    CHOOSE関数、WEEKDAY関数、ROW関数の組み合わせです。WEEKDAY関数で求められるシリアル値を添字として該当する添字の配列位置の曜日を返します。ROW関数ですがA列の勤務日セルを引数とすればROW関数を使う必要がありません。ここで使用した理由は前回のROW関数の復習と考え使いました。ROW関数の()内セルA1から行を求めていますのでオートフィルで書式を複写すると+1づつ加算され日にちとしても活用できることを理解されればここでの引用の意義が成り立ちます。(勝手な理由!)

  3. C列 区分                                          <画面デザインへ>
    入力対象項目です。通常営業日と法定休日の割増の計算時に使用します。
    0:通常営業日の法定時間外残業計算
    1:法定外休日の時間外計算  (祝祭日を除く土曜日)
    2:法定休日の時間外計算   (祝祭日)

  4. D列 出勤時刻                                      <画面デザインへ>
    入力対象項目です。但し、計算上ではこのセルの値は引用しません。
    出勤時刻は、計算上15分刻み、小数点以下切り上げでワークセル L列 W出勤に求めています。

    ◎L列 W出勤 の関数式
    =IF(D8="","",IF(C8=0,D8,CEILING(D8,"0:15")))
    • CEILING(D8,"0:15") 15分の倍数値に切り上げする関数式です。
      • 例:9:27 であれば 9:30 と出勤時間が補正されます。

  5. E列 退社時刻                                      <画面デザインへ>
    入力対象項目です。但し、計算上ではこのセルの値は引用しません。
    退社時刻は、計算上15分刻み、小数点以下切り捨てでワークセル M列 W退社 に求めています。
    また、深夜残業で午後11時59分(つまり翌日の午前0時)以降の場合出勤時間より値が小さくなり計算式が複雑になるため24時間換算でのシリアル値を求めるワークセル N列 24Hシリアル を用意し実際の計算を実行しています。

    ◎M列 W退社 の関数式
    関数式  =IF(E8="","",FLOOR(E8,"0:15"))
    • FLOOR(E8,"0:15") 15分の倍数値に切り捨てする関数式です。CEILING関数とは逆の機能です。
      • 例:19:32 であれば 19:30 と退社時間が補正されます。

    N列 24Hシリアル
    関数式  =IF(D8>M8,M8+1,M8)
    • 24時間表示で入力を行う場合深夜0時を過ぎると出勤時間よりシリアル値が小さくなります。その補正策としてシリアル値に+1(24時間)を加算し午前5時までを基準としているので29時間式。
      1=24時間とは、時間を表すシリアル値を参考すると(関数あらかると日付・時間を参照)時間の場合小数点以下の値となり、0 〜 0.99999999 の範囲にある値で、0:00:00 (午前 0 時) から 23:59:59 (午後 11 時 59 分 59 秒) までの時刻を表すことになります。つまり、整数値 1 とは 24時間 と同値となります。

  6. F列 残業時間                                       <画面デザインへ>
    関数式 =IF(M8<>"",IF(C8=0,IF(N8-TIMEVALUE("17:00")<=0,0,(N8-TIMEVALUE("17:00"))*24),(N8-L8)*24),"")
    TIMEVALUE関数は、文字列で表された時刻を小数(シリアル値)に変換します。時間計算の時は良く用います。F列以降も *24 を見受けますが意味は60分を1とした場合の時間の拾い方です。例として1時間30分の場合この式で求められる値は 1.5 となります。

    関数式に至る流れ図
    • ◇は判定文(IF関数)です。 y が真の時の条件、 n が偽の時の条件の先です。長方形 □ は処理です。楕円のような図は始まりと終わりを表します。アルファベットは、セル列と読み替えて下さい。ご覧の通り、フローチャート化すると流れが複雑でも意味がわかると思います。

  7. G列 1.00  (法定時間内残業の時間)                          <画面デザインへ>
    17:00から18:00までの時間と休日残業(土曜日)の9時から18:00までの時間を求めます。

    関数式
      =IF(OR(D8="",E8=""),"",IF(C8=0,IF(N8<=TIMEVALUE("18:00"),
        IF(N8<TIMEVALUE("17:15"),"",(MIN(N8,TIMEVALUE("18:00"))-TIMEVALUE("17:00"))*24),1),
          IF(C8=1,(MIN(N8,TIMEVALUE("18:00"))-L8)*24,"")))
    • 複雑そうですけどフローチャートで解析すれば簡単と思います。IF関数から始まり論理式がOR関数を使っています。意味ですが、D8 出勤時刻 E8 退社時刻の入力確認です。双方入力されている場合偽の条件判定が成立します。真の時は、気根が整っていないので "" 値をセルに代入しています。ここでのポイントはMIN関数にあります。引数の内いづれかの小さい値を返値として時間計算を仕組んでいます。

    関数式に至る流れ図
    • C列の区分で2の法定休日の場合返値が””になっている理由は、G列 1.00 が条件として存在しません。
    • N8<TIMEVALUE("17:15")の判断は、早退者など法定時間内に帰宅した社員を考慮して17:15(15分刻みの残業時間の付与時間最低値)以下の場合は 0 を代入するようにしています。
    • C列 区分1の法定外休日(土曜日)の場合は、出勤時刻から退社時刻までが残業となり、1.00に該当する出勤時刻から18:00もしくは18:00以前に退社した時間までの計算を行います。

  8. H列 1.25  (法定時間外残業の時間)                      <画面デザインへ> 
    通常営業日と法定外休日の18:00〜22:00までの残業時間を求めます。
    関数式 

      =IF(OR(D8="",E8=""),"",IF(C8>1,"",
        IF(N8>TIMEVALUE("18:00"),(MIN(N8,TIMEVALUE("22:00"))-TIMEVALUE("18:00"))*24,"")))
    • G列 1.00と同様の関数式です。ここでは、18:00以降22:00までが規定時間となるのでその判断が関数式の中に組み立てられています。
    関数式に至る流れ図


  9. I 列  1.35  (法定時間外残業の時間)                       <画面デザインへ>
    法定休日の9:00〜22:00までの残業時間を求めます。

    関数式 =IF(OR(D8="",E8=""),"",IF(C8<>2,"",(MIN(N8,TIMEVALUE("22:00"))-L8)*24))

    関数式に至る流れ図


  10. J列 1.50  (法定時間外残業の時間)                       <画面デザインへ>
    通常営業日の22:00〜am5:00までの残業時間を求めます。am5:00を打ち止めとしています。ここでの所見はMIN関数とMAX関数の組み合わせ技です。G列1.00同様法定休日は該当しません。

    関数式 
      =IF(OR(D8="",E8=""),"",
         IF(C8>1,"",
            IF(N8<=TIMEVALUE("22:00"),"",
                (MIN(N8,1+TIMEVALUE("5:00"))-MAX(L8,TIMEVALUE("22:00")))*24)))
    • MIN(N8,1+TIMEVALUE("5:00")) N列 退社時刻とam5:00を比較しいずれか小さい方の値を返値とします。
    • MAX(L8,TIMEVALUE("22:00")) 法定外休日の判断も必要となりますので出勤時間と22:00の大きい方の値を返値とします。
    • MIN関数の値からMAX関数の値を減算してその結果に *24 の結果を代入します。

    関数式に至る流れ図


  11. K列 1.60  (法定時間外残業の時間)                        <画面デザインへ>
    法定休日の22:00以降の残業時間を求めます。

    関数式 
      =IF(OR(D8="",E8=""),"",
        IF(C8<>2,"",
          IF(N8<=TIMEVALUE("22:00"),"",
            (MIN(N8,1+TIMEVALUE("5:00"))-MAX(L8,TIMEVALUE("22:00")))*24)))

    関数式に至る流れ図


 もひとつ おまけ
今回のダウンロードファイルを開くと白を基調としたワークシートに気づきます。
今までは、セルの境界線を付けて提供していたのですが今度は隠しています。
その方法は、2通りです。
 @メニューバー 「ツール」→「オプション」→【表示タブ】→【枠線】 のチェックをはずし「OK」ボタンをクリックします。

 A全セル選択ボタンをクリックします。 お好みの色で塗りつぶします。

   



○「匠への技」VOL-5で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-005.xls  Download file: takumivol-005.lhz