先日『エクセルマクロ』でとある依頼を受けました。
なんでも、某勤怠システムを導入して、
毎月月初に先月分の勤怠表をエクセルで出力して、
プリントアウトして、
ハンコを押して提出という流れだそうで。
総務の方で、
勤怠があっているかの確認が必要だけれど、
出力されるエクセルの項目が多く、
色がついてないのでとにかく見づらいと。
100人以上分のエクセルがあるので目視確認がとても大変だそうで。
要件としては、
- 土日祝 はセルの色をグレーにしてほしい
- 出勤、退勤どちらかが空白で、備考に何も記載がなければメッセージを表示してほしい
という内容でした。
打ち合わせ中、
ハンコが不要なら100人でも200人でもクリック一つで一括チェックできますよと、
かるく提案はしたものの、しばらくハンコは必須なようで。
社内マクロの打ち合わせにて、
やっぱりハンコで引っかかる。ハンコがなけりゃ、
100人分でも200人分でもクリック一つでまとめてチェックできるのに、
ハンコがあるだけで、1枚1枚のチェックにすり替わる。なんて無駄なシステムなんでしょ、と思いながらコードを書く水曜日の昼。
— 世界のアオキプログラミング×生涯学習×熊本 (@aoki_monpro) October 7, 2020
社内マクロの打ち合わせにて、やっぱりハンコで引っかかる。
ハンコがなけりゃ、100人分でも200人分でもクリック一つでまとめてチェックできるのに、
ハンコがあるだけで、1枚1枚のチェックにすり替わる。
なんて無駄なシステムなんでしょ、と思いながらコードを書く水曜日の昼。
圧倒的なハンコブレーキがかかっている状態の中、
要件だけでも叶えようとエクセルマクロを組み、
約2時間で作成したので、
その時の考え方、使った知識などを書き留めておこうと思います。
目次
エクセルマクロで勤怠表の自動チェックをする 要件の棚卸し
操作としては、
勤怠システムからエクセルファイルが出力されるので、
マクロで、そのエクセルファイルを選択して、
色やらメッセージ表示やらを実施すればOKではあります。
まずはファイル選択回りから。
エクセルマクロでファイルを選択する
ボタンを押してファイルを選ぶ、という操作ではあるけれど、
いろいろと考えないといけない事があります。
- エクセルファイルが選ばれているか。
→拡張子で .xls や .xlsx だけを表示。 -
エクセルファイルを選ばずにキャンセルした場合の動き。
→ 他のマクロを動かさず処理を止める必要がある。 -
正しい勤務表ファイルかどうかの確認。
→勤務表ファイルの複数のセルを確認して、
同じ内容になっているかどうかの判定をかける。 -
不要なシートの削除
→すでにシートがあると重複してしまうので、事前に削除しておく。 -
シートのコピー
→勤務表ファイルのシートを、マクロ登録しているファイルにコピーして閉じる。
などの対応が必要になります。
コードとしては、
1.ファイル選択
Application.GetOpenFilenameでエクセルファイル指定
2.エクセルファイルを選ばずにキャンセル
→Application.GetOpenFilenameがfalseになった場合に、
フラグを持たせてNGなら処理停止(if文)
3.正しい勤務表ファイルかどうか確認
→if文で複数セルを比較
4.不要シート削除
→for each ws in worksheets で各シートチェック
5.シートのコピー
→workbook.worksheets().copy after:=workbook.worksheet()
いずれも『エクセルマクロ』の動きがわかっている必要がありますが、
大まかな動きや流れがわかっていれば、
細かいコードのスペルなどはググりつつ構築していく場合でも、
1時間もかからずコーディングできるのではと思います。
エクセルマクロで色づけや判定
続いて、勤務表のテーブル範囲の判定。
|
1 2 |
endColumn = Cells(titleRow, Columns.Count).End(xlToLeft).Column endRow = Cells(titleRow, 1).End(xlDown).Row |
などのコードで、必要な行・列を取得して。
最初の行(1日)から最後の行(28日～31日)までを、
for文で1行ずつ確認しつつ、
運よく土日祝日には『休日』というような文字が書かれていたので、
この文字が書かれている行はグレーに設定としました。
|
1 2 3 |
for i startRow to LastRow If Cells(i, 2).Value = "休日" Then Range(Cells(i, 1), Cells(i, endColumn)).Interior.Color = RGB(155, 155, 155) |
というかたち。
休日以外で、出勤・退勤いずれかが空白で、
備考に何も記載がなければメッセージ表示というのを、
if文をいくつか組み合わせてつくっていきました。
最後にマクロが途中で止まらないよう、
|
1 2 3 4 |
With Application .ScreenUpdating = False .DisplayAlerts = False End With |
などで挟んで完成。
頂いていたテスト用ファイルでテストしつつ、一部コード修正しつつ、
トータル約２時間で作成して総務にご連絡。
総務の方も「ぽかーん」としていましたが、
ルールさえ決まればすぐに作れるという事を
実感してもらえたかなと思います。
エクセルマクロで勤務表の自動チェックを作成して
エクセルの機能がわかっていれば、さくっとつくれはするものの、
個人的には、
やはりハンコブレーキがどうにも効率化を妨げているとしか思えないので、
ウィズコロナでデジタル庁な昨今だからこそ、
なるべく早めに不要なハンコ文化はなくなってほしいなと思ったのでした。
『エクセルVBA・分析・統計』関係ではこんな記事も読まれています。
1. 【ExcelVBA】初心者向け講座をリリースしました。2. 【ExcelVBA(マクロ)】(エクセル)よく使う書き方をまとめてみた【随時更新】
3. 【Excel(エクセル)】よく使う関数をまとめてみた(組み合わせもあり)
4. 【ピボットテーブル】の使い方をわかりやすくまとめてみた【初心者向け】
5. 【Excel(エクセル)】確率・統計でよく使う関数をまとめてみた【随時更新】
6. 【分散】【標準偏差】とは わかりやすくまとめてみた【ExcelとPython】
7. 【データドリブンマーケティング】の本を頼りにデータ分析力を高めていくべし【統計学】
8. 【正規分布】とは わかりやすくまとめてみた【ExcelとPython】
9. 【確率】当たりがでる確率を計算する方法【二項分布】【Excel/Python】
10. エクセルマクロで勤務表の自動チェックを依頼され2時間でつくった話
11. 【エクセルvba(マクロ)】でできる事を増やし使える人になったら女の子に「すごぃ！」って言われた時の話
この記事へのコメントはありません。