システムから出力したExcelやCSV形式のデータ一覧を、「データに誤入力がないか？」「不要なデータが出力されてないか」など、チェックする場合があると思います。
データが100件くらいであれば、目視で確認してもいいかもしれないですが、データが1000件、2000件…となると、確認作業に１日費やしてしまいます。
これが毎月、毎週となればさらに負担になります。
このように、毎週や毎月決まったデータチェックを目視で行っているのであれば、それをVBAに任せてしまった方が断然楽です。
データチェックするファイルの条件
このデータチェック自動化では、前提として以下の条件を満たしているExcelファイルを対象とします。
・Excel形式もしくは、CSVをExcel形式に変換したファイルである
・データチェックする表の列と、チェック内容が決まっている
・データチェックする列に対してセルの結合をしていない
・データチェックする列のセルに数式や関数を入れていない
上記を満たしていないExcelファイルの場合は、VBAでのデータチェックが正しく行われない可能性があります。
データチェック対象のファイルについて
今回のデータチェックの元となるファイルについてです。
このようなデータ一覧ファイルがあったとします。
このデータ一覧は、社内のある部屋に入室した場合に記録する、入退室データです。
誰が入室したか、入室時間と退室時間を記録しています。
今回使用するデータ一覧ファイルはこちらからダウンロードできます↓
Excelファイルをダウンロードしたら、Excelマクロ有効ブック（*.xlsm）で保存します。
※ダミーの住所や氏名は、以下サイトからランダムに生成して使用しています。
大量データの変換を楽々にするWEBサービス（無料） (nextvitz.com)
データチェック自動化の実装
今回の内容としては、以下2つのデータチェック機能をVBAで自動化していきます。
・入退室記録の入室日と退室日に相違があるか確認する
・入退室記録の入室時間から勤務時間外の入室を確認する
セルを上から順番に確認していき、これらのデータチェック時に該当があれば、セルの背景色に色を付けるという内容のVBAを作成します。
ボタンの設置
まずは、２つのデータチェック機能を実行するためのボタンを設置していきます。
ボタン設置用のシートを、［入退室記録］シートとは別に作成します。シート名は「ボタン」で良いです。
［開発］メニューから、［挿入］をクリックし、ActiveXコントロールのボタンを選択します。
ボタンシートの任意の場所をクリックしてボタンを設置します。
ボタンを右クリックし、[プロパティ]を開きます。
※このとき、[デザインモード]を選択した状態でないと、ボタンの編集をすることができないので、デザインモードにしておきます。
[オブジェクト名]と[Caption]に「入退室日誤入力チェック」と入力します。
オブジェクト名はVBA内で使われます。Captionはボタンの表示テキストになります。
※日本語を入力して文字化けする場合は、メモ帳などに入力したものをコピーして、ここに張り付けてください。
オブジェクトについて詳しくはこちらにあります↓
VBAのオブジェクトとは？オブジェクト、プロパティ、メソッドの使い方
同じように「時間外入室チェック」のボタンも設置します。
「時間外入室チェック」ボタンのプロパティを変更します。
次に、 作成したボタンをクリックするとデータチェックが行われるよう、VBAで作成していきます。
入退室日誤入力チェック機能の実装
入退室日誤入力チェック機能は、セルとセルの値を比較し、相違をチェックを行う機能です。
デザインモードでボタンを右クリックし、[コードの表示]をクリックします。
Visual Basic Editorが開き、自動でボタンクリックイベントのプロシージャが生成されます。
この中に入退室日誤入力チェック機能の内容を書いていきます。
VBAのイベントについて詳しくはこちらにあります↓
ExcelVBAのイベントとは？どんなイベントがあるか確認する方法
Visual Basic Editorについて詳しくはこちらにあります↓
VBA作成の流れとしては以下となります。
・2行目からデータの入る最下行までの繰り返し処理をFor文で作成する
・入室日と退室日を比較し相違があった場合は、セルの背景色を黄色にする
・繰り返し処理が終了したら完了メッセージを表示する
2行目からデータの入る最下行までの繰り返し処理をFor文で作成する
For文を使った繰り返し処理は以下のようになります。
Dim i
For i = 2 To Worksheets("入退室記録").Cells(Rows.Count, 1).End(xlUp).Row
‘ここに入室日と退室日の比較処理が入る
Nextiはカウント変数です。繰り返し処理は、1行目の項目名を除いた2行目から処理を開始するので、２をカウント変数 i の初期値として格納します。
Nextまで処理を行い、Forに戻る際に自動的にカウント変数 i に＋１されます。 2行目から、［入退室記録］シートの最終行まで繰り返し処理を行います。
Worksheets("入退室記録").Cells(Rows.Count, 1).End(xlUp).Row上記の一文は、For文を使用する際によく使われます。データが入力されている一番最後の行番号を取得できます。
For文について詳しくはこちらにあります↓
ExcelVBAの繰り返し処理For文について。構文や例文で解説
入室日と退室日を比較し相違があった場合は、セルの背景色を黄色にする
入室日と退室日を比較するには、If文を使用します。式は以下のようになります。
If Worksheets("入退室記録").Cells(入室日セル行番号, 入室日セル列番号) <> Worksheets("入退室記録").Cells(退室日セル行番号, 退室日セル列番号) Then
「入室日セルの値と退室日セルの値が異なる」という式は、演算子＜＞を使うことで作成できます。
これをFor文の中に書いていきます。
'検索する列を変数に格納
Dim 入室日, 退室日
入室日 = 4
退室日 = 6
'For文を使って相違チェック
Dim i
For i = 2 To Worksheets("入退室記録").Cells(Rows.Count, 1).End(xlUp).Row
'入室日と退室日の相違チェック
If Worksheets("入退室記録").Cells(i, 入室日) <> Worksheets("入退室記録").Cells(i, 退室日) Then
‘if文の条件に当てはまった場合の処理をここに書く
End If
Next［入室日セル行番号］と［退室日セル行番号］に、For文のカウント変数 i を使用することで、For文の処理を繰り返すたびに、自動的に行番号がカウントアップされます。
列番号は「入室日＝４列目」、「退室日＝６列目」と固定なので、For文の前にわかりやすい名称で変数に格納しておきます。
If文について詳しくはこちらにあります↓
ExcelVBAの条件分岐の書き方。if文について解説します。
if文の条件に当てはまる場合、（入室日セルの値と退室日セルの値が違っていた場合）セルの背景色を変更する処理は以下のように書きます。
Worksheets("入退室記録").Cells(i, 入室日).Interior.ColorIndex = 6（カラーインデックス番号）
Worksheets("入退室記録").Cells(i, 退室日).Interior.ColorIndex = 6（カラーインデックス番号）黄色のカラーインデックス番号は６番です。
ここまで書いたFor文とif文で、シートを指定するコード［Worksheets(“入退室記録“)］が複数出てきているので、［With~End With］を使用して省略します。
'検索する列を変数に格納
Dim 入室日, 退室日
入室日 = 4
退室日 = 6
'For文を使って相違チェック
With Worksheets("入退室記録")
Dim i
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
'入室日と退室日の相違チェック
If .Cells(i, 入室日) <> .Cells(i, 退室日) Then
'セルの背景色を黄色にする
.Cells(i, 入室日).Interior.ColorIndex = 6
.Cells(i, 退室日).Interior.ColorIndex = 6
End If
Next
End With※［With～End With］を使用したら、ピリオドを付けて[.Cells～]と書くこと。ピリオド無しでセルの指定など行うと、コードを記述している[ボタン]シートのセルの値を取得することになるので注意。
繰り返し処理が終了したら完了メッセージを表示する
最後にFor文を抜けたら、完了メッセージを表示するコードを書いて終わりです。
'完了メッセージ表示
MsgBox "入退室日の誤入力チェックが完了しました"
コード全体です。
Private Sub 入退室日誤入力チェックボタン_Click()
'検索する列を変数に格納
Dim 入室日, 退室日
入室日 = 4
退室日 = 6
'For文を使って相違チェック
With Worksheets("入退室記録")
Dim i
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
'入室日と退室日の相違チェック
If .Cells(i, 入室日) <> .Cells(i, 退室日) Then
'セルの背景色を黄色にする
.Cells(i, 入室日).Interior.ColorIndex = 6
.Cells(i, 退室日).Interior.ColorIndex = 6
End If
Next
End With
'完了メッセージ表示
MsgBox "入退室日の誤入力チェックが完了しました"
End Sub 
一旦テストして動作確認してみましょう。
[入退室日誤入力チェック]ボタンを押すと、すぐに完了メッセージが表示されます。
OKをクリックして入退室記録シートを確認すると、誤入力があったセルの背景色が変わっているのが分かります。
入退室日誤入力チェック機能の実装は完了です。
時間外入室チェック機能の実装
こちらの機能もクリックイベントプロシージャを作成し記述していきます。
VBA作成の流れとしては以下となります。
・2行目からデータの入る最下行までの繰り返し処理をFor文で作成する
・勤務時間外（9：00～22：00以外）の入室があった場合はセルの背景色を緑色にする
・繰り返し処理が終了したら完了メッセージを表示する
・2行目からデータの入る最下行までの繰り返し処理をFor文で作成する
・繰り返し処理が終了したら完了メッセージを表示する
については、[入退室日相違チェック]のコードと全く一緒のため省略します。
勤務時間外（9：00～22：00以外）の入室があった場合はセルの背景色を緑色にする
勤務時間外である9時以前、22時以降の入室を条件式で表すと次のように記述できます。
'検索する列を変数に格納
Dim 入室時間
入室時間 = 5
'時間外入室のチェック
If .Cells(i, 入室時間) < CDate("9時00分") Or .Cells(i, 入室時間) > CDate("22時00分") Then
'セルの背景色を緑色にする処理がここに入る
End If時間の比較をしていますが、“９時00分”と入力してしまうと文字列として認識されてしまうため間違った結果となってしまいます。
そのためCDate関数を使用して文字列を日付型に変換します。
.Cells(i, 入室時間) < CDate("9時00分")
データ型変換関数についてはこちらに詳しくあります↓
データ型変換関数 (VBA) | Microsoft Learn
if文の条件に当てはまる場合、（勤務時間外に入室があった場合）セルの背景色を変更する処理は以下のように書きます。
'セルの背景色を緑色にする
.Cells(i, 入室時間).Interior.ColorIndex = 4
カラーインデックス番号については、以下が参考になります。
ColorIndex プロパティ (Excel グラフ) | Microsoft Learn
コード全体です。
Private Sub 時間外入室チェックボタン_Click()
'検索する列を変数に格納
Dim 入室時間
入室時間 = 5
'For文を使って相違チェック
With Worksheets("入退室記録")
Dim i
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
'時間外入室のチェック
If .Cells(i, 入室時間) < CDate("9時00分") Or .Cells(i, 入室時間) > CDate("22時00分") Then
'セルの背景色を緑色にする
.Cells(i, 入室時間).Interior.ColorIndex = 4
End If
Next
End With
'完了メッセージ表示
MsgBox "時間外入室チェックが完了しました"
End Sub 
こちらも一度テストしてみます。
[時間外入室チェック]ボタンをクリックすると、完了メッセージが表示されます。
OKをクリックし、[入退室記録]シートを見に行くと、背景色が変更されているセルがあることが確認できます。
以上で2つのデータチェック機能の実装は完了です。
おまけ：クリア機能の実装
2つのデータチェック機能で着色されたセルに対して、背景色を無色にする機能も作成すると便利です。
[ボタン]シート内に[クリア]ボタンを設置します。
プロパティ画面でオブジェクト名とCaptionを変更しておきます。
背景色を無色にするコードは以下のように書けます。
.Interior.ColorIndex = xlNone背景色は今までカラーインデックス番号を指定していましたが、背景色をクリアする場合には[xlNone]を指定します。
コード全体です。
Private Sub クリアボタン_Click()
With Worksheets("入退室記録")
'セルの背景色をクリアする
.Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 7)).Interior.ColorIndex = xlNone
End With
End Sub 
[クリア]ボタンをクリックすると、着色されていたセルの背景色がクリアされたのが確認できます。
まとめ
今回は、Excelファイルでのデータチェック機能をVBAで実装しました。毎月、毎週データチェック業務に追われている人は、今回のVBAを参考にしてみてください。
その他に、ExcelVBAで表集計の自動化や、書類作成の自動化についても書いているので、業務で使えそうな機能があれば取り入れていただけると嬉しいです。
