勤務時間と時給金額で日給を計算するVBAコードを作成。時給の計算その5

vbajikyukeisaneyecatch005

エクセルVBAで時給計算表を作ります。

第5回目の今回は、勤務時間と時給で日給を計算するエクセルVBAコードを組み立てて行きます。
時間と金額をかけ合わせますので、これにはちょっとした工夫が必要です。

エクセルVBAで作る時給計算表の作成の記事5回目です。

今回は、勤務時間と時給から日給を計算するVBAコードの組み立て方になります。

こんにちは、じゅんぱ店長(@junpa33)です。

 

時給計算に関連する記事の一覧

「時給計算」の記事一覧を開く

 

無料ソフトダウンロード
この時給計算表エクセルソフトの無料のダウンロードは、

無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。

の記事で行うことが出来ます。

 

勤務時間と時給金額で日給を計算する

 

今回は勤務時間と時間給から日給を計算します。

時給計算表の今回記事完了時点で出来た作成イメージはこのようになります。

計算ルール的には、勤務日時毎の実労時間マルメはできません。日給金額マルメは計算可能です。

vbajikyuhyoupho038

 

日給を計算するVBAコードを作成する

 

時給計算表の一番中心的な計算部分です。

それでは計算方法の紹介をしていきます。「Module2」に記述していきます。

モジュール共通の変数と設定のプロシージャーを確認します

 

今回もモジュールで共通に使っている変数とその変数への代入設定のプロシージャーを確認しておきます。

宣言セクションコード
Option Explicit

Public EmpName As String, 年月日 As String
Public OriBName As String
Public TCos, TwCos, TMal, KMal
Public SD As Date, ED As Date
Dim SaName As String, SaNamea As String
設定値代入コード
Sub 設定値代入()

Dim MB As Long
Worksheets("初期設定項目").Select

'Format,Year,Month関数を利用します
    年月日 = Format(Date, "yymmdd")

'会計期間をセット
    SD = Worksheets("初期設定項目").Range("D5").Value
    ED = Worksheets("初期設定項目").Range("F5").Value

'対象者名を変数に代入します
    EmpName = Worksheets("初期設定項目").Range("C6").Value
    
    If EmpName = "" Then
        EmpName = 年月日
        Range("C6") = 年月日
    End If

'基本時給をセット
    TCos = Worksheets("初期設定項目").Range("C7").Value
'時間外割増率をセット
    TwCos = Worksheets("初期設定項目").Range("F7").Value / 100
'時間マルメをセット
    TMal = Worksheets("初期設定項目").Range("C8").Value
'金額マルメをセット
    KMal = Worksheets("初期設定項目").Range("C9").Value

End Sub

これはすでにModule1で記述しているコードです。

今回も再度記述する必要はありません。

 

プロシージャー内変数の宣言部分

 

プロシージャー内変数とモジュール共通変数の設定代入プロシージャーを記述します。

変数宣言部分コード
Sub 金額計算()

Dim Sh As Worksheet
Dim res As Boolean
Dim i As Long
Dim Va As Long, Vb As Long
Dim Vaa As Long, Vbb As Long
Dim B32V As String

Call Module1.設定値代入

'時間外割増率が空欄の時100%とします
If TwCos = "" Then TwCos = 100

 

時給計算表の存在確認部分

 

時給計算表が既に存在しているかと存在している場合、どこの行数までデータ入力されているかを調べます。

計算シートの存在確認コード
'計算するシートが存在するかを調べる
res = False
For Each Sh In Worksheets
    If Sh.Name = EmpName & "時給計算表" Then
        res = True
        Worksheets(EmpName & "時給計算表").Select
        Exit For
    End If
Next

'ない場合はプロシージャーを終了する
    If res = False Then
        MsgBox "まだ何も設定されていません。"
        Exit Sub
    End If

'記入データ行数を調べます
B32V = Range("B32")
Range("B32") = ""
BRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("B32") = B32V

 

日給を計算する部分

 

記入データがある行数分で、実労時間と時給で日給を計算していきます。

実労時間には基準の勤務時間と時間外時間が含まれていますので、分けて計算することになります。

時間に金額を掛け算して日給を計算しますが、

人力計算なら、例えば勤務時間7時間30分であれば、

7時間×830円+30分÷60分×830円で6,225円 と計算すると思います。

ところがExcelではそのようには計算しません。

チェックポイント

時給計算表で勤務時間「7:30」と表示されているのはExcel利用者が見て解るように表示しているだけです。(表示形式)

勤務時間「7:30」の本当のデータは勤務時間「0.3125」となっています。

これは、1日24時間を数値「1」として「24:00」中の「7:30」は数値「0.3125」であるという意味です。

これをシリアル値と言います。

そして逆にシリアル値に24を掛け算すれば、

「7:30」→「0.3125」(×24)→「7.5」時間となり、時給計算をするには非常に都合のいい数値になるのです。

つまり、「時間表示×24」は知っていると非常に吉になる計算です。

 

金額計算についての変数として、「Vaa」「Vbb」「Va」「Vb」を設定しています。

「Vaa」は基準時間に対して基本時給を掛け算して算出した金額です。このままでは、小数点以下のある金額となっています。

「Va」は小数点以下の部分を切り上げて金額(整数値)に直した金額になっています。

「Vbb」は「Vaa」同様ですが、時間外の割増率が加味されています。

「Vb」は「Vb」同様ですが、時間外の割増率が加味されています。

金額マルメの計算方法としては、

1円以下を切り上げの場合は「支給金額」を10で割って小数点を切り上げた後、その金額を10倍しています。

10円以下を切り上げの場合は「支給金額」を100で割って小数点を切り上げた後、その金額を100倍しています。

100円以下を切り上げの場合は「支給金額」を1000で割って小数点を切り上げた後、その金額を1000倍しています。

 

MEMO

RoundUp関数の使い方についてはこの記事を参考にしてください。

RoundUpとRoundDown関数の使い方。数値の切り方に注意が必要です

 

日給計算と金額マルメコード
For i = 3 To BRow

'初期値にリセットします
Vaa = 0
Vbb = 0
Va = 0
Vb = 0

'時間外時間が発生しているかどうかで区別します
    If Cells(i, 8) > 0 Then
        Vaa = Cells(i, 3) * 24 * TCos

'値を切り上げ(小数点を切り上げ)
        Va = WorksheetFunction.RoundUp(Vaa, 0)

        Vbb = Cells(i, 8) * 24 * TCos * TwCos

'値を切り上げ(小数点を切り上げ)
        Vb = WorksheetFunction.RoundUp(Vbb, 0)

    Else
        Vaa = Cells(i, 7) * 24 * TCos

'値を切り上げ(小数点を切り上げ)
        Va = WorksheetFunction.RoundUp(Vaa, 0)
    
    End If

'支給金額の表示します
Cells(i, 9) = Va + Vb

'参考金額を表示します
    Cells(i, 10) = Va
    Cells(i, 11) = Vb

'金額マルメ計算
If KMal = 1 Then

'1円以下を切り上げ
    Cells(i, 9) = WorksheetFunction.RoundUp(Cells(i, 9) / 10, 0) * 10
    
ElseIf KMal = 2 Then

'10円以下を切り上げ
    Cells(i, 9) = WorksheetFunction.RoundUp(Cells(i, 9) / 100, 0) * 100
    
ElseIf KMal = 3 Then

'100円以下を切り上げ
    Cells(i, 9) = WorksheetFunction.RoundUp(Cells(i, 9) / 1000, 0) * 1000
    
Else

'切り上げしない
    Cells(i, 9) = Va + Vb
    
End If

Next i

End Sub
金額計算コード全体

通してコードを記述するとこのようになります。

vbajikyuhyoupho037

 

 

勤務時間と時給金額で日給を計算するのまとめ

 

この様に、

Excelのルールの時間のシリアル値を上手く利用することで、容易に日給計算をすることが出来ます。

エクセル特有のシリアル値。計算方法は是非覚えておきましょう。

 

次回は、時給計算表の集計部分の計算のVBAコードについて紹介して行きます。

 

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

 

このサイトでよく使うVBAのコードのトピックを纏めています。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。