労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA

2012年1月29日

エクセルマクロ・VBA達人養成塾小川です。

労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA

受講生村瀬正昭さんからいただいた添削依頼の内容を、ブログ上で添削しています。

テーマは、就業時間内、時間外、深夜割増での賃金を計算するマクロです。

この記事は、以下のシリーズの第6回です。

第1回: 給与計算ソフトは結構市販されていますが、融通が利かない場合も – Excelマクロ・VBA

第2回: 添削事例 – 給与計算をするマクロ（その２） – Excelマクロ・VBA

第3回: 範囲を指定してデータを一括クリアする – 給与計算をするマクロ（その３） – Excelマクロ・VBA

第4回: 就業時間内、時間外、深夜割増での賃金を計算する（その４） – Excelマクロ・VBA

第5回: 就業時間内、時間外、深夜割増での合計労働時間を一発で計算する（その５） – Excelマクロ・VBA

今日は、いただいたマクロの、以下の部分を添削。

Sub Monthly_total_salary() ‘

    Monthly_Totaltm

    Dim d1 As Long
    Dim h1 As Long
    Dim m1 As Long
    Dim d2 As Long
    Dim h2 As Long
    Dim m2 As Long
    Dim d3 As Long
    Dim h3 As Long
    Dim m3 As Long


    Dim Tt As Double
    Dim Tt2 As Double
    Dim Tt3 As Double
    Tt = Range(“E35”).Value
    Tt2 = Range(“F35”).Value
    Tt3 = Range(“G35”).Value

    d1 = Int(Tt) ‘就業時間合計が24時間超になるごとに1が加算される。
        h1 = Hour(Tt) ‘就業時間合計を24で割った余りを示す。
        m1 = Minute(Tt) ‘就業時間合計のうち、分単位のものを示す。
    d2 = Int(Tt2) ‘就業時間合計が24時間超になるごとに1が加算される。
        h2 = Hour(Tt2) ‘就業時間合計を24で割った余りを示す。
        m2 = Minute(Tt2) ‘就業時間合計のうち、分単位のものを示す。
    d3 = Int(Tt3) ‘就業時間合計が24時間超になるごとに1が加算される。
        h3 = Hour(Tt3) ‘就業時間合計を24で割った余りを示す。
        m3 = Minute(Tt3) ‘就業時間合計のうち、分単位のものを示す。


    Range(“E36”).Value = Range(“E3”).Value * d1 * 24 + Range(“E3”).Value * h1 + Range(“E3”).Value * m1 / 60
    Range(“F36”).Value = Range(“F3”).Value * d2 * 24 + Range(“F3”).Value * h2 + Range(“F3”).Value * m2 / 60
    Range(“G36”).Value = Range(“G3”).Value * d3 * 24 + Range(“G3”).Value * h3 + Range(“G3”).Value * m3 / 60

    With Range(“E37”)
        .Value = Range(“E35”).Value + Range(“F35”).Value
        .NumberFormatLocal = “[h]:mm”
    End With

    With Range(“E38”)
        .Value = Range(“E36”).Value + Range(“F36”).Value + Range(“G36”).Value
        .NumberFormatLocal = “#,##0_);[赤](#,##0)”
    End With

End Sub

Monthly_Totaltm は呼び出されているマクロ。前回のブログで解説しました。

..ということで。

h1, m1, h2, m2, h3, m3 に値を入れるところを修正。

Sub Monthly_total_salary_ogawa1() ‘

    Monthly_Totaltm

    Dim d1 As Long ‘[*1]
    Dim h1 As Long
    Dim m1 As Long
    Dim d2 As Long
    Dim h2 As Long
    Dim m2 As Long
    Dim d3 As Long
    Dim h3 As Long
    Dim m3 As Long


    Dim Tt As Double
    Dim Tt2 As Double
    Dim Tt3 As Double
    Tt = Range(“E35”).Value
    Tt2 = Range(“F35”).Value
    Tt3 = Range(“G35”).Value

    d1 = Int(Tt) ‘就業時間合計が24時間超になるごとに1が加算される。
    h1 = Hour(Tt) ‘就業時間合計を24で割った余りを示す。
    m1 = Minute(Tt) ‘就業時間合計のうち、分単位のものを示す。
    d2 = Int(Tt2) ‘就業時間合計が24時間超になるごとに1が加算される。
    h2 = Hour(Tt2) ‘就業時間合計を24で割った余りを示す。
    m2 = Minute(Tt2) ‘就業時間合計のうち、分単位のものを示す。
    d3 = Int(Tt3) ‘就業時間合計が24時間超になるごとに1が加算される。
    h3 = Hour(Tt3) ‘就業時間合計を24で割った余りを示す。
    m3 = Minute(Tt3) ‘就業時間合計のうち、分単位のものを示す。


    Range(“E36”).Value = Range(“E3”).Value * d1 * 24 + Range(“E3”).Value * h1 + Range(“E3”).Value * m1 / 60
    Range(“F36”).Value = Range(“F3”).Value * d2 * 24 + Range(“F3”).Value * h2 + Range(“F3”).Value * m2 / 60
    Range(“G36”).Value = Range(“G3”).Value * d3 * 24 + Range(“G3”).Value * h3 + Range(“G3”).Value * m3 / 60 ‘[*2]

    With Range(“E37”)
        .Value = Range(“E35”).Value + Range(“F35”).Value
        .NumberFormatLocal = “[h]:mm”
    End With

    With Range(“E38”)
        .Value = Range(“E36”).Value + Range(“F36”).Value + Range(“G36”).Value
        .NumberFormatLocal = “#,##0_);[赤](#,##0)”
    End With

End Sub

で、[*1] ～ [*2] では、結局のところ、

○セルE35に入った時間を元にしてセルE36に金額を入れる
○セルF35に入った時間を元にしてセルF36に金額を入れる
○セルG35に入った時間を元にしてセルG36に金額を入れる

という単純作業しかしていないので。

僕なら、ここは、こう書き換える。

Sub Monthly_total_salary_ogawa2()

    Monthly_Totaltm

    Range(“E36”).Value = Range(“E35”).Value * Range(“E3”).Value * 24 ‘[*2-1]
    Range(“F36”).Value = Range(“F35”).Value * Range(“F3”).Value * 24 ‘[*2-2]
    Range(“G36”).Value = Range(“G35”).Value * Range(“G3”).Value * 24 ‘[*2-3]

    With Range(“E37”)
        .Value = Range(“E35”).Value + Range(“F35”).Value
        .NumberFormatLocal = “[h]:mm” ‘[3-1]
    End With
    With Range(“E38”)
        .Value = Range(“E36”).Value + Range(“F36”).Value + Range(“G36”).Value
        .NumberFormatLocal = “#,##0_);[赤](#,##0)” ‘[3-2]
    End With
End Sub

27行かけていたところが、3行で済んだヾ(´ー｀)ﾉ

解説すると…。

まず、前提として知っておいて欲しいことは、VBAでは、小数点つきの数値で日付を管理している、ということ。

以下、深いことをちゃんと書こうと思うと大変なので、ざっくりとした説明。
以下のお話は、何を言っているのか、なんとなく分かればよいです。

例えば、以下のマクロを実行してみる。

Sub hiduketest1()
    Dim dt As Date
    Dim db As Double
    dt = 0.5
    db = 0.5

    Debug.Print dt
    Debug.Print db
End Sub

イミディエイトウィンドウには、以下のように出力される。

12:00:00
0.5

つまり、同じ 0.5 という値を投入しようとしても、
入れる相手の変数が日付型だと、入るのは、「12:00:00」という値。

次に、以下を実行してみる。
CDate関数は、任意の値を日付型のデータに変換する関数。

Sub hiduketest2()
    Dim db As Double
    db = 0.5
    Debug.Print CDate(db)
End Sub

これも、結果として、「12:00:00」という値を出力する。

今度は、逆に、CDbl関数を使って、日付型のデータを小数で表現してみよう。
以下のマクロを実行すると、「0.5」という値が出力される。

Sub hiduketest3()
    Dim dt As Date
    dt = #12:00:00 PM#
    Debug.Print CDbl(dt)
End Sub

ここから分かるとおり、日付としての12:00:00と小数の0.5は、同値のようなもの。

ということで。

例えば、セルE35に「24:00:00」という値が入っていたとする。

時給\3,000-の人を想定して、その人の労働報酬を計算するなら、

Range(“E36”).Value = Range(“E35”).Value * 3000 * 24

ということ。

最後に、「 * 24 」としていることに注目。
Range(“E35”) の値が、「24:00:00」であれば、それは、小数として扱うなら、「1」と同じですね。

なんで、時給単価とのかけ算を行うなら、24倍しなくてはです。

もっとも、上記のマクロは、本当は以下のように書いたほうが丁寧。

Range(“E36”).Value = CDbl(Range(“E35”).Value) * 3000 * 24

ということで、さらにつづき。

[3-1], [3-2] も、セルの書式関係。

Time_Clearcontents の中に納めてしまおう。以下の要領。
ついでに、今気がついたのだが、セルE36～G36も、セルE38と書式を統一したほうがよいな。

ということで、

Time_Clearcontents は、以下のように変更。

Sub Time_Clearcontents_ogawa3()
    Dim gyo As Long
    gyo = Range(“E” & ActiveSheet.Rows.Count).End(xlUp).Row
    If gyo > 3 Then
        Range(“E4:G” & gyo).Clearcontents
    End If
    Range(“E4:G35, E37”).NumberFormatLocal = “[h]:mm”
    Range(“E36:G36, E38”).NumberFormatLocal = “#,##0_);[赤](#,##0)”
End Sub

Monthly_total_salary も、最終的に、以下のようになる。

Sub Monthly_total_salary_ogawa3()

    Monthly_Totaltm

    Range(“E36”).Value = Range(“E35”).Value * Range(“E3”).Value * 24
    Range(“F36”).Value = Range(“F35”).Value * Range(“F3”).Value * 24
    Range(“G36”).Value = Range(“G35”).Value * Range(“G3”).Value * 24

    Range(“E37”).Value = Range(“E35”).Value + Range(“F35”).Value
    Range(“E38”).Value = Range(“E36”).Value + Range(“F36”).Value + Range(“G36”).Value
End Sub

ということで。

今回は、36行あったマクロが、7行になりました。
（※上記のコードでは6行だが、Time_Clearcontents で一行増えたので、そこまで考慮すると7行）

次回は、全体の流れを整理してみます。

この記事は、以下のシリーズの第6回です。

第1回: 給与計算ソフトは結構市販されていますが、融通が利かない場合も – Excelマクロ・VBA

第2回: 添削事例 – 給与計算をするマクロ（その２） – Excelマクロ・VBA

第3回: 範囲を指定してデータを一括クリアする – 給与計算をするマクロ（その３） – Excelマクロ・VBA

第4回: 就業時間内、時間外、深夜割増での賃金を計算する（その４） – Excelマクロ・VBA

第5回: 就業時間内、時間外、深夜割増での合計労働時間を一発で計算する（その５） – Excelマクロ・VBA

お知らせ:

「親指シフト達人養成塾」（あと1名）
「エクセルデータ分析7つの上級技」（キャンセル待ち）

「エクセルデータ分析7つの上級技」、うかうかしていたら、定員オーバーになっていました。
現状、キャンセル待ちです。最新の情報はリンク先のページで記載していますので、ご確認ください。

Excelマクロ達人養成塾 1月～の日程

あなたの文章作成能力を1.7倍アップする、究極のタイピングスキル「親指シフト」
○親指シフト達人養成塾

2012年02月12日 (日) 10:00-12:00 東京・神保町（あと1名）

詳細はこちら→ http://www.exvba.com/oyayu/index.php

マクロなしでもここまでできる！高度なノウハウをあなたに
○Excelデータ分析7つの上級技

2012年02月12日 (日) 13:00-19:00 東京・神保町（キャンセル待ち）

詳細はこちら→ http://www.exvba.com/exbunseki/index.php

ベタな初心者のあなたも、一日でマクロを書けるようになります。
自己流で行き詰まっている人もぜひどうぞ。体系的に教えます。

○Excelマクロ・VBA基礎コース

  2012年02月04日（土）10:00-20:00  大阪・十三（あと4名！）
  2012年02月18日（土）10:00-20:00  東京・神保町（受付中）
  2012年03月10日（土）10:00-20:00  東京・神保町（受付開始！）
  2012年03月24日（土）10:00-20:00  大阪・十三（受付開始！）
  2012年03月31日（土）10:00-20:00  東京・神保町（受付開始！）

詳細はこちら→ http://www.exvba.com/vb/index.php

○Excelマクロ・VBA発展コース

  2012年02月19日（日）09:30-19:30  東京・神保町（あと1名！）
  2012年03月25日（日）09:30-19:30  大阪・十三（あと3名！）
  2012年04月01日（日）09:30-19:30  東京・神保町（受付中）

詳細はこちら→ http://www.exvba.com/vb/index.php

●塾長のTwitterはこちらです↓。フォローお待ちしていますね。
http://twitter.com/kanjizaibosatsu

●無料PDFレポート「誰もが知っているWindowsの、誰も知らない12の技」
http://www.exvba.com/freereport/index.php

●法人研修のお問い合わせはこちら
https://sv86.wadax.ne.jp/~exvba-com/closed/toiawase_houjin.php

●ジーザス小川の個人サイト「こねこねのさいと」へはこちらから
http://www.exvba.com/

労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA

労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA

キーワード

コメント

コメントを残すコメントをキャンセル

最新の記事

謹賀新年2022

年末年始休業のお知らせ

夏季休業のお知らせ

ゴールデンウイーク休業のお知らせ

謹賀新年2021

年末年始休業のお知らせ

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド

労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA

労働時間と時給の情報を使って支払い額を計算したい – Excelマクロ・VBA

キーワード

コメント

コメントを残す コメントをキャンセル

最新の記事

謹賀新年2022

年末年始休業のお知らせ

夏季休業のお知らせ

ゴールデンウイーク休業のお知らせ

謹賀新年2021

年末年始休業のお知らせ

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド

コメントを残すコメントをキャンセル