エクセルVBAで時給計算表を作ります。
第5回目の今回は、勤務時間と時給で日給を計算するエクセルVBAコードを組み立てて行きます。
時間と金額をかけ合わせますので、これにはちょっとした工夫が必要です。
エクセルVBAで作る時給計算表の作成の記事5回目です。
今回は、勤務時間と時給から日給を計算するVBAコードの組み立て方になります。
こんにちは、じゅんぱ店長(@junpa33)です。
時給計算に関連する記事の一覧
無料DL!時給計算表エクセルソフト 個人別ブックで管理出来ます。
の記事で行うことが出来ます。
コンテンツ
勤務時間と時給金額で日給を計算する
今回は勤務時間と時間給から日給を計算します。
時給計算表の今回記事完了時点で出来た作成イメージはこのようになります。
計算ルール的には、勤務日時毎の実労時間マルメはできません。日給金額マルメは計算可能です。
日給を計算する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倍しています。
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
勤務時間と時給金額で日給を計算するのまとめ
この様に、
Excelのルールの時間のシリアル値を上手く利用することで、容易に日給計算をすることが出来ます。
エクセル特有のシリアル値。計算方法は是非覚えておきましょう。
次回は、時給計算表の集計部分の計算のVBAコードについて紹介して行きます。
短期間でエクセルVBAの独学習得を目指したいなら
エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。
けれども、
出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。
独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。
先人が行った勉強方法をあなたがそのまま利用すればよいということです。
独習のための大切な7つのポイントは、上記記事にて解説しています。
独習によるVBA習得のキーワードは、
出来るだけ多くの実例に触れること!
です。
正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。
ハッキリ言って、
本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、
手っ取り早く短期間習得できるというのは間違いないでしょう。
このサイトでよく使うVBAのコードのトピックを纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。









