今回は、以前の頁で使った出勤簿から、給与計算のマクロ化をしたいと思います。 もう一度マクロ化する作業内容を確認しましょう。
出勤簿から給与計算マクロを作ろう
- 出勤時刻と退勤時刻から勤務時間を取得する
- アルバイト従業員ごとに小計を出す
- 勤務時間に時給を掛けて給料を算出する
以上の機能を持ったマクロを作成します。 今回は1番の、「出勤時刻と退勤時刻から勤務時間を取得する」プログラムを作成したいと思います。
勤務時間の取得
処理を想定してみる
プログラムを作り始める時には、これが最も大事なことですが、まず必要な処理をイメージしてみましょう。 簡単にでもメモをとったりすれば、さらに精度は上がります。 どういった処理が必要かを、細かく噛み砕いてみるとイメージしやすくなります。
細かい処理を箇条書きにしてみます。
- セルから出勤時刻と退勤時刻を受け取る
- 受け取るための変数が必要
- 出勤時刻と退勤時刻の差を勤務時間として5列目にセットする
- 日付をまたがないため、単純な引き算で問題ない
- 以上の処理を1行目以外の全ての行で実行する
- 1行目はヘッダ部のため、2行目からループを開始する
こういった処理が必要になると思います。 大きなプログラムを作る時ほど、細分化して整理することでスムーズな作業ができるようになります。
セルから値を変数に受け取る
まずは出勤時刻を入れる日付型の変数、退勤時刻を入れる日付型の変数、そしてループカウンタ用に整数型の変数を用意します。
Dim i As Integer 'ループカウンタ用 Dim Syukkin As Date 'セルから出勤時刻を受け取る変数 Dim Syukkin As Date 'セルから出勤時刻を受け取る変数
それでは、セルから値を受け取る部分的なコードを記述してみます。
Syukkin = Cells(i, 3).Value ' i行目の3列目の値をSyukkinに入れる Taikin = Cells(i, 4).Value ' i行目の3列目の値をTaikinに入れる
行数はループで1行ずつ処理するため、ループカウンターの「i」を置いています。 このコードで、セルから値を変数に受け取る処理は完了ですね。
セルの値へのアクセス
上のコードでも触れていますが、簡単にセルの値の扱い方をご説明したいと思います。
変数 = Cells(行, 列).Value 'セルの値を変数に受け取る Cells(行, 列).Value = 変数 '変数の値をセルにセットする
どちらも可能な処理となります。
当ブログでは登場するのがもう少し後になりますが、「Value」だけでなく他の項目に関しても、セルに対して色々な処理ができます。
Date型
日付型の変数に「17:30」のように、60未満の整数2つをコロンで区切って並べて入れることで、「時:分」として入れることができます。 「17:30:30」を日付型変数に入れると、「時:分:秒」として扱われます。
出勤時刻と退勤時刻の差をセルにセットする
一番大事な、出勤時刻と退勤時刻から勤務時間を取得する部分ですね。 日付をまたぐ勤務があると、この計算は使えません。 ですが今回は日付をまたがないものとしているため、単純に退勤時刻から出勤時刻を引き算し、勤務時間を求めるものとします。
まずは出勤時刻と退勤時刻を分単位で保持するための変数を用意します
Dim SyukkinMin As Integer '出勤時刻を分単位で保持する変数 Dim TaikinMin As Integer '退勤時刻を分単位で保持する変数
すでに時刻は取得してあるため、まずは「時」だけを取得し、60を掛けて分単位に直します。
SyukkinMin = Hour(Syukkin) * 60 '変数に時間×60の値を代入 TaikinMin = Hour(Taikin) * 60
そして次は時刻から「分」だけを取得し、各変数に加算します。 そして退勤時から出勤時を引いた値を、5列目のセルにセットします。
SyukkinMin = Minute(Syukkin) + SyukkinMin '出勤時の変数に分数を追加 TaikinMin= Minute(Taikin) + TaikinMin '退勤時の変数に分数を追加 Cells(i, 5).Value = TaikinMin – SyukkinMin '退勤時から出勤時を引いて勤務時
これで行ごとの処理は完成ですね。 次はループ処理を作って、全ての行で処理できるようにしましょう。
Hour関数、Minute関数
Date型のデータから、Hour関数は「時」、Minute関数は「分」だけを取得します。 取得する値は、整数型となります。
Hour(日付型データ) Minute(日付型データ)
ループ条件の設定
ループを使うにあたって一番の問題は、ループの終了条件です。 今回は勤務時間を求めるための、出勤時刻か退勤時刻のどちらかが空欄になっている行があったらループを終了することとします。
Do Until Cells(i, 3).Value = "" Or Cells(i, 4).Value = "" '3か4の列が空欄の行なら終了 Loop
部品は全て完成したので、後は組み合わせるだけですね。
勤務時間の算出
それでは、今回作成したプログラムを流れで見てみましょう。
Sub SetKinmuJikan() Dim i As Integer Dim Syukkin As Date Dim Taikin As Date Dim SyukkinMin As Integer '出勤時刻を分単位で保持する変数 Dim TaikinMin As Integer '退勤時刻を分単位で保持する変数 i = 2 '一行目はヘッダのため2行目から処理する SyukkinMin = 0 '変数の初期化です。 なくても結果は変わりません TaikinMin = 0 Do Until Cells(i, 3).Value = "" Or Cells(i, 4).Value = "" '空白行があったらそこで終了 Syukkin = Cells(i, 3).Value SyukkinMin = Hour(Syukkin) * 60 SyukkinMin = Minute(Syukkin) + SyukkinMin '出勤時の変数に「分」を足す Taikin = Cells(i, 4).Value TaikinMin = Hour(Taikin) * 60 TaikinMin = Minute(Taikin) + TaikinMin '退勤時の変数に「分」を足す Cells(i, 5).Value = TaikinMin - SyukkinMin '退勤時から出勤時を引いて勤務時間を算出し、5列目にセット i = i + 1 'ループカウンターを1進める Loop End Sub
このようなプログラムになります。
今回のまとめ
- プログラムを作り始める時は必要な処理をイメージする
- 処理はなるべく細分化して考える
今回はプログラムを作り始める時に、どういった流れで考えていくかをご説明しました。 プログラムを作ることに慣れてくると、自分なりの手順が考えつくようになると思います。 そうなってくると、プログラムを考えることが段々と楽しくなってきますね。
次回は、行の仕分けについてのご説明をしたいと思います。
コメント