VBA100本ノック 91本目を動画で解説しています。Excelの神髄さんの模範解答はコチラ
問題
#VBA100本ノック 91本目
「勤怠」に複数月の勤怠データが入っています。
9:00～18:00の休憩1hの実動8hです。
id、月ごとに残業時間を算出し「残業」に一覧出力。
残業：単純に1日8hを超える時間数。
ただし9時前出勤は9時とする。
日々1分単位、月間30分単位で切り捨て。
※時間計算の練習問題です。
解答
Sub ノック91本目()
Dim ws残業 As Worksheet: Set ws残業 = Sheets("残業")
Dim ws勤怠 As Worksheet: Set ws勤怠 = Sheets("勤怠")
Dim 開始時刻, 残業時間, 総残業分数 'ホントはDouble
Dim outID, out年月
Dim i As Long, lastrow As Long, outrow As Long
lastrow = ws勤怠.Cells(Rows.Count, 1).End(xlUp).Row
outrow = 2
For i = 2 To lastrow
'ws勤怠のC列が9時以前なら9時開始、それ以降ならその時刻
開始時刻 = ws勤怠.Cells(i, 3)
If 開始時刻 < TimeSerial(9, 0, 0) Then 開始時刻 = TimeSerial(9, 0, 0) '9時より小さければ9時
残業時間 = ws勤怠.Cells(i, 4) - 開始時刻 - TimeSerial(9, 0, 0)
If 残業時間 > 0 Then 'もし1分でも残業があれば
総残業分数 = 総残業分数 + Hour(残業時間) * 60 + Minute(残業時間) '分数で表す
End If
outID = ws勤怠.Cells(i, 1)
out年月 = Format(ws勤怠.Cells(i, 2), "yyyymm")
'もしIDか日付が一つ下のセルと異なった場合
If ws勤怠.Cells(i + 1, 1) <> outID Or _
Format(ws勤怠.Cells(i + 1, 2), "yyyymm") <> out年月 Then
ws残業.Cells(outrow, 1) = outID
ws残業.Cells(outrow, 2) = out年月
'30分未満は切り捨て…等の処理
総残業分数 = Int(総残業分数 / 30) * 30 '30で割ってから切り捨て→再度30掛け算
ws残業.Cells(outrow, 3) = TimeSerial(0, 総残業分数, 0)
総残業分数 = 0
'outrowを1プラスする
outrow = outrow + 1
End If
Next i
End Sub■考え方・流れ
0:00 冒頭・問題確認
2:18 変数の宣言と最終行までループ
4:33 ループの中の処理の整理
6:24 開始時刻、残業時間、総残業分数の計算
9:47 ID、年月が変わったらデータを出力
11:50 月間30分単位で切り捨て
いんやぁ～、Excelで時間の計算って難しい…
これまで時間の計算はあんまり
やってきませんでしたもんね！！
俺は感覚的に
計算できるけどな！
絶対間違ってるそれ
変数の宣言と最終行までループ
まずはシートの定義から！
使うシートは二つ！
続いては最終行を”lastrow”と定義して求めるぞ！！
ws勤怠のA列の最終行を取得したぞ！
お次はループの作成！
2～lastrowまでループ！
続いてはループの中身なんだけど…
今日はちょっとややこしいから、先にやりたいことをコメントで書いておこう！
outrowってのが出力させたい先の”行数”だ！！
IDか日付、どちらかがループ中に変わったら、出力する行も変更したいから…
ループの中の処理の整理
この章では、ループ内のコメントを先に書いて整理するぞ！
まず、「それぞれの出勤時刻」もとめるぞ！！
出勤時刻が9時より前の場合は、そのまんま9時開始！(早く付けてくれてもいいのにね…)
9時より遅れた遅刻マンは、遅刻した時刻が開始時刻になるぞ！
お次、「残業があるかどうか」を求めるぞ！！
休憩1Hを考慮して、退勤した時刻-出勤した時刻-1Hが、9時間を超えた場合それが残業時間になる！
この会社の標準労働時間は8Hのようだな！！せめて7.5Hにしてくれ！！
残業時間の合計が30分未満は切り捨てって条件もあったな！！
おいおいここは1秒単位でつけてくれよ！！
以上、考えることが多かったが…！大丈夫だったか？
次の章からは出勤時刻・退勤時刻・残業時間などを変数にして解いていくからな！
開始時刻、残業時間、総残業分数の計算
ここからは必要な項目を変数にして解いていくぞ！！
ほんとは日付形式は「Double型」にするのが正解らしいが…
uぷ主はそんなこと気にせずVariantでやったが…何かこれで問題があったらスマン！！
まずは開始時刻！
9時以前に出社した人は9時開始！それ以降は出勤した時刻！
「9時」とかは「TimeSerial関数」を使った方が指定しやすいぞ！！
続いて残業時間
これは↓以下の通りじゃ！！
続いては月の「総残業分数」！！
残業した分は1日ごとに、しっかりと追加していってあげよう！！(さすがにね)
1分でも残業したら、総残業分数=総残業分数+残業時間 って式だね！！
そしてここから、残業時間をそのまんま使うんじゃなくて、
Hour関数、Minute関数を使って「分数」に直そう！
理由は後々！！
Hourの方は分数に直すから、×60を入れてくれな！
なんで分数に無理やり直すかの説明も一応入れてるから、是非動画も見てくれ！
ID、年月が変わったらデータを出力
よし！ここからは実際、IDと年月も変数に入れて、アウトプットの準備をしていくぞ！
IDはws勤怠のA列！
年月はws勤怠のB列を、月間(yyyymm)に直した分だ！
ほんでお次、もしIDか日付が異なった場合の条件分岐については…
IFとORの組み合わせでいけるね！
ほんだら、IDと月が替わったってことだから…
ws残業の方に書き出し！！！
このIFの中で、総残業分数を0に戻してあげる必要があるね！
ここまでを実行したらこんな感じ！
次の章で仕上げだ！！
月間30分単位で切り捨て
最後！！！月間の残業時間を、30の倍数にすれば完成！！
例…残業時間が95分なら90分とする
・・・120分にしてくれてもいいのにね！
昔ワイがやってたバイトは、15分単位で切り上げてくれてた気がする…(>_<)
こいつぁ～INT関数さんを使えばOK！！
INT関数さんは小数点以下を切り捨てて整数を作ってくれる！
INTさんの結果に30を書ければとりあえず30の倍数になるよね！
ほんで最後！！
30の倍数になった総残業分数を、ws残業のoutrow行目に出力するんだけど、
ここでまたTimeSerial関数さんを使えばOK！！
第二引数に入れてあげてね！！
実行結果がこちら！！
今日も頑張ったね！
お疲れんこん～～～！！
お疲れ様！
12月に入っていきなり寒くなったね！！
uぷ主は寒いの苦手なので
毎日震えながら動画作ってます！
あとがたり
おはこんばんちは。uぷ主です。
91本目！残業時間の月間合計を求める問題！！
あと…あと10本だね！！よくぞここまで来た…！！
(ブログは全然書いてないw)
いんやぁ～、Excelで時間の計算って難しいんですね。
経理とか？？ってこんな感じでExcel使ってんのかな？それともなんかツールとか使ってんのかな？
経理じゃねぇからわかんねぇが…
小規模な会社でExcelで済ませてるような所なら…、
Excel使ってもいいよね！なったことないから知らんけど！
ちな、残業時間の切り上げか切り下げ問題は何が正解なんですかね？
ワイの会社はちなみに、
「自分で15分の倍数の残業時間を申請する」って原始的な方法だぞ！！
みなさんはどうですか？？
とま、今回も超絶ありがたい問題でした。
神髄さん、ありがとうございました。
ほな、ここからはいつもの…関係ないシリーズ。
今、2022年12月1日なんすけど…、
寒すぎません？
いきなり気温下がりましたよね～～
マジで無理っす。
ワイは夏の子なんで。
マジで家にあったかいポケモン飼いたい。
あ、ちなみにポケモンスカーレットもちょくちょく進めてま。
あったかいポケモンのファイアローも使ってるよ。
そろそろ四天王いけるところまできた！！
もうちょっとだ！
今年もあと少しだけど…Excelも本業も動画もポケモンも全部頑張るぞ！！！
最後までご視聴いただいたみなさん！ありがとうございました！
コメント