VBAでマクロを組んだ

エクセルが楽しい。自動化という言葉に目がない怠惰な僕は、関数やVBAを駆使して手作業を減らせることに悦びを感じるからだ。

最近は関数とVBAの使い方を覚えるため、友達のお仕事効率化ツールの作成に勤しんでいる。

今回は、他の会社の経理部で働いている友達向けに、入力したデータが条件表のいずれかのレコードと一致する場合は○、しない場合は×を返すマクロを作った。

今までは入力されたデータが決められた条件に一致するかどうかを1件1件目で見て確認し、3時間も費やしていた作業が、このマクロを使うことで3秒で終わる。最高。

実際の画面はこんな感じ（見た目は汚いが）

 ・判定シート

 B~F列にお客さんからもらったデータを入力する（実際は毎回150レコード前後あるらしいが今回はサンプルで10レコード入力。）。

右の判定ボタンを押すことでマクロが実行され、判定列に判定結果が返される。

今回は動きをわかりやすくするため計算に使う列（判定より右側の列）も色づけしている。

・条件表シート

判定シートに貼り付けたレコードがこの中のどれかと一致したら○を返す。

判定ボタン押下後

判定列に結果が表示される。

■雑な解説

①関数

レコードの各列の値を一つのセルに連結し、countif関数で比較を行う（H列）。

数式は、

=COUNTIF($J$2:$J$13,$I2)

となっており、$J$13の部分は入力したデータ数に応じて自動で変更される。

②VBA

ソースを張り付けた方がわかりやすそうなので先に記載。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

Sub 判定()

Dim i As Integer
i = 1

Range("G2").Select
Do Until ActiveCell.Offset(0, -1) = ""

ActiveCell.Offset(1, 0).Activate
i = i + 1

Loop

Range("K2").Copy
ActiveSheet.Paste Destination:=Range("G2:G" & i)

End Sub

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

前提として、K～N列に計算に必要な関数を用意してある。

初めに基準となるセルを選択し、Do~Loopでレコード数をカウントアップした後、計算列に関数を張り付ける。

同じようなマクロをK~N列の４つ分作り、4つのマクロを順番に実行するマクロを作成し、ボタンに登録することで今回の動きを実現。

結果だけ見るとすげー単純なツールではあるが、Excel初心者としてはかなり良い勉強になった。

最後に、技術向上のため、ツールの作成依頼を募集してます。

こんなツールがあったらいいのにな、なんてときはご相談下さい。

3/6追記　機能改善

・処理時間の高速化

マシンスペックや判定するレコード数によっては処理に時間がかかるため、

以下の記事を参考に（実質丸パクリ）処理速度の改善を測った。

遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法

今回参考にさせて頂いたのは「画面表示の更新を停止」の部分。

レコード数は実際の業務より多めの200件で測定。

before

after

時間が半減。これは覚えておくと相当便利だぞ・・！

3/8　追記

レコード数の取得ロジックを変更

before

～～～～～～～～～～～～～～～～～～～～～～～～～

Range("G2").Select
Do Until ActiveCell.Offset(0, -1) = ""

ActiveCell.Offset(1, 0).Activate
i = i + 1

Loop

～～～～～～～～～～～～～～～～～～～～～～～～～

after

～～～～～～～～～～～～～～～～～～～～～～～～～

Do Until ActiveSheet.Cells(i, 6).Value = ""

i = i + 1

Loop

～～～～～～～～～～～～～～～～～～～～～～～～～

セルを移動しながら行数をカウントアップしてたけどそんな必要全くなくて、このロジック変更で処理時間が０秒になった、、ばかみたい。