エクセル仕事では、入力した数値に間違いがある場合があります。
そのミスを極力減らすために、人を使ってダブルチェックをすることは少なくありません。
しかし、人を2人3人と増やしてもミスがなくなるとは限りません。
むしろ、もう一人がチェックしてくれるという考えてしまい、チェックがおざなりになることもあります。
そこで、考えたいのは自動チェック機能です。
エクセル仕事で作った資料に間違いがないかどうかをチェックする機能を作れば、解決できることがあります。
この記事では、エクセルマクロVBAを使って資料の間違いを探して知らせる機能の作り方について紹介します。
目次
エクセルマクロVBAで作る自動チェック機能
この記事では、以下の自動チェック機能をエクセルマクロVBAで作成していきます。
自動チェック機能1｜入力した数値が規格値を超えていることを知らせる(アラート機能)
自動チェック機能2｜ 数値を入力すべきなのに、数値が入力されていないことを知らせる
自動チェック機能3｜ 入力し忘れ(空欄)を知らせる
以下で一つずつ紹介していきます。
自動チェック機能1｜入力した数値が規格値を超えていることを知らせる(アラート機能)
メーカーの工場に勤務していると、製造管理の試験結果をチェックする仕事があります。
製造管理では規格値が決められており、その値を超えると何かトラブルと判断されます。
その数値をチェックするために、一つ一つデータをチェックしていては終わりません。
そこで、マクロを使ってデータチェックを行います。
ここでは縦に製品ロット、横に製造ラインがそれぞれ入力されたエクセルシートを使います。具体的には以下です。
[1] 入力された数値データ
[2] 規格値上限と規格値下限(2~98までなら規格内)
[1]のデータの中で、規格値上限を超えていたり規格値下限を下回っていたりしたら、規格外です。
規格外のセルを見つけ出したいとき、人の目で確認するのは限界があります。
そこでマクロを使います。マクロを使えば、以下のように規格外の数値を自動で見つけ出すことが可能です。
ここでは、規格外の数値があれば黄色でセルを塗りつぶすようにプログラムしました。
これが分かれば、目視で確認することなく規格外のものを見つけることができます。
マクロのプログラム
入力した数値が規格値を超えていることを知らせるマクロのプログラムは以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
'ステップ1｜おまじないとタイトル Option Explicit Sub datacheck() 'ステップ2｜変数の定義 Dim myRange As Range, rg As Range Dim cmax As Long, i As Long Dim ws1 As Worksheet Dim jougen As Long, kagen As Long 'ステップ3｜変数の設定 Set ws1 = Worksheets(データ) cmax = ws1.Range(A1048576).End(xlUp).Row jougen = ws1.Range(N2).Value kagen = ws1.Range(N3).Value Set myRange = ws1.Range(B2:K ; cmax) 'ステップ4｜一つ一つのセルに対する処理(for each文) For Each rg In myRange 'ステップ5｜処理しているセルの値に応じて処理を変える(if文) If rg.Value > jougen Then rg.Interior.ColorIndex = 6 ElseIf rg.Value < kagen Then rg.Interior.ColorIndex = 6 Else rg.Interior.ColorIndex = xlNone End If 'ステップ6｜for each文の区切り Next 'ステップ7｜プログラムの終わりを示す End Sub |
以下でマクロのプログラムの説明をします。
ステップ1｜おまじないとタイトル
|
1 2 |
Option Explicit Sub datacheck() |
1行目｜おまじないです。これがなくてもマクロを動かすことは可能です。
2行目｜「datacheck」というプログラムの名称であることを示す
ステップ2｜変数の定義
|
1 2 3 4 |
Dim myRange As Range, rg As Range Dim cmax As Long, i As Long Dim ws1 As Worksheet Dim jougen As Long, kagen As Long |
1行目｜myRangeをRange型、rgをRange型として定義
2行目｜cmaxをLong型(自然数)、iをLong型(自然数)として定義
3行目｜ws1をworksheet型(ワークシート型)として定義
4行目｜jougenをLong型(自然数)、kagenをLong型(自然数)として定義
ステップ3｜変数の設定
|
1 2 3 4 5 |
Set ws1 = Worksheets(データ) cmax = ws1.Range(A1048576).End(xlUp).Row jougen = ws1.Range(N2).Value kagen = ws1.Range(N3).Value Set myRange = ws1.Range(B2:K ; cmax) |
1行目｜ws1をワークシート名「データ」としてセット
2行目｜cmaxをセルA1048576から[Ctrl] + [↑]のショートカットキーを押したときに示す行数とする(言い換えると「データの最終行」)
3行目｜jougenをws1のセルN2の値とする
4行目｜kagenをws1のセルN3の値とする
5行目｜myRangeをws1のセルB2～セルK(cmax)の複数セルとしてセット
ステップ4｜一つ一つのセルに対する処理(for each文)
|
1 |
For Each rg In myRange |
1行目｜myRangeとしてセットした複数セルを一つずつrgに代入していく
ステップ5｜処理しているセルの値に応じて処理を変える(if文)
|
1 2 3 4 5 6 7 |
If rg.Value > jougen Then rg.Interior.ColorIndex = 6 ElseIf rg.Value < kagen Then rg.Interior.ColorIndex = 6 Else rg.Interior.ColorIndex = xlNone End If |
1行目｜もし、セルrgの値がjougenより大きかったら
2行目｜rgの背景色を6(黄色)にする
3行目｜もし、セルrgの値がkagenより小さかったら
4行目｜rgの背景色を6(黄色)にする
5行目｜それ以外の条件ならば
6行目｜rgの背景色をxlnone(なし)にする
7行目｜if文の終わりを示す
ステップ6｜for each文の区切り
|
1 |
Next |
1行目｜Nextはステップ4のfor each rg in myRangeの区切りを示す（for each ~ nextをループさせる）
ステップ7｜プログラムの終わりを示す
|
1 |
End Sub |
1行目｜プログラムの終わりを示す
自動チェック機能2｜数値を入力すべきなのに、数値が入力されていないことを知らせる
エクセルに入力されている数値をチェックしていると、数値以外が入力されていることがあります。
しかし、このような入力ミスはどうしても起こってしまうものです。
そこで、マクロを使って数値かどうかの判別を行います。
まずはマクロを使う前の状態を以下に示します。
上記の赤枠内のセルで数値以外の内容が入力されていたら、知らせるようにします。
マクロを使えば、以下のように数値以外が入力されているセルを自動で見つけ出すことが可能です。
ここでは、数値以外が入力されたセルを黄色でセルを塗りつぶすようにプログラムしました。(上記の赤枠)
これであれば、わざわざ一つずつセルをチェックすることなく問題のあるセルを見つけることができます。
マクロのプログラム
数値を入力すべきなのに、数値が入力されていないことを知らせるマクロのプログラムは以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
'ステップ1｜おまじないとタイトル Option Explicit Sub isdatacheck() 'ステップ2｜変数の定義 Dim myRange As Range, rg As Range Dim cmax As Long, i As Long Dim ws1 As Worksheet 'ステップ3｜変数の設定 Set ws1 = Worksheets(データ) cmax = ws1.Range(A1048576).End(xlUp).Row Set myRange = ws1.Range(B2:K ; cmax) 'ステップ4｜一つ一つのセルに対する処理(for each文) For Each rg In myRange 'ステップ5｜処理しているセルの値に応じて処理を変える(if文) If IsNumeric(rg.Value) = False Then '---自動チェック機能1のプログラムとの相違点はここから rg.Interior.ColorIndex = 6 Else rg.Interior.ColorIndex = xlNone End If 'ステップ6｜for each文の区切り Next 'ステップ7｜プログラムの終わりを示す End Sub |
以下でマクロのプログラムの説明をします。
ステップ1~4とステップ6~7は自動チェック機能1のマクロと同じ
ステップ1~4とステップ6~7は自動チェック機能1のマクロと同じなので、説明を省きます。
ステップ5｜処理しているセルの値に応じて処理を変える(if文)
|
1 2 3 4 5 |
If IsNumeric(rg.Value) = False Then '---自動チェック機能1のプログラムとの相違点はここから rg.Interior.ColorIndex = 6 Else rg.Interior.ColorIndex = xlNone End If |
1行目｜もし、セルrgの値がNumeric(数値型)としてFalseだったら(数値型ではなかったら)
2行目｜rgの背景色を6(黄色)にする
3行目｜それ以外なら
4行目｜rgの背景色をxlnone(なし)にする
5行目｜if文の終わりを示す
この部分以外のプログラムの説明は、自動チェック機能1のマクロの説明をご覧ください
自動チェック機能3｜入力し忘れ(空欄)を知らせる
エクセルのデータチェックをしていると、数値が入力されていない場合があります。
しかしデータ入力側すれば、入力するデータが多ければ入力し忘れをしてしまうのは仕方のないことです。
そこで、マクロを使ってデータが入力されているかの判別を行います。
まずはマクロを使う前の状態を以下に示します。
上記の赤枠内のセルに何も入力されていなかったら、知らせるようにします。
マクロを使えば、以下のように入力忘れのセルを自動で見つけ出すことが可能です。
ここでは、数値以外が入力されたセルを黄色でセルを塗りつぶすようにプログラムしました。(上記の赤枠)
これであれば、わざわざ一つずつセルをチェックすることなく問題のあるセルを見つけることができます。
マクロのプログラム
入力し忘れ(空欄)を知らせるマクロのプログラムは以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
'ステップ1｜おまじないとタイトル Option Explicit Sub blankcheck() 'ステップ2｜変数の定義 Dim myRange As Range, rg As Range Dim cmax As Long, i As Long Dim ws1 As Worksheet 'ステップ3｜変数の設定 Set ws1 = Worksheets(データ) cmax = ws1.Range(A1048576).End(xlUp).Row jougen = ws1.Range(N2).Value kagen = ws1.Range(N3).Value Set myRange = ws1.Range(B2:K ; cmax) 'ステップ4｜一つ一つのセルに対する処理(for each文) For Each rg In myRange 'ステップ5｜処理しているセルの値に応じて処理を変える(if文) If rg.Value = Then '---[1]のプログラムとの相違点はここのみ rg.Interior.ColorIndex = 6 Else rg.Interior.ColorIndex = xlNone End If 'ステップ6｜for each文の区切り Next 'ステップ7｜プログラムの終わりを示す End Sub |
ステップ1~4とステップ6~7は自動チェック機能1のマクロと同じ
ステップ1~4とステップ6~7は自動チェック機能1のマクロと同じなので、説明を省きます。
ステップ5｜処理しているセルの値に応じて処理を変える(if文)
|
1 2 3 4 5 |
If rg.Value = Then '---自動チェック機能1のプログラムとの相違点はここのみ rg.Interior.ColorIndex = 6 Else rg.Interior.ColorIndex = xlNone End If |
1行目｜もし、セルrgの値が””(空欄)だったら
2行目｜rgの背景色を6(黄色)にする
3行目｜それ以外なら
4行目｜rgの背景色をxlnone(なし)にする
5行目｜if文の終わりを示す
この部分以外のプログラムの説明は、自動チェック機能1のマクロの説明をご覧ください
条件付き書式よりマクロVBAを学べ
実は、ここで紹介したマクロは条件付き書式というエクセルの機能を使っても実現可能です。
しかし、条件付き書式よりマクロを使うことをお勧めします。
なぜなら、マクロの方が色々なことができるからです。
例えば、この記事で紹介しているマクロは最終行を読み取って自動で処理範囲を変更しています。
これは、条件付き書式では難しい処理です。そのため、条件付き書式が適用されていないセルに値が入力されても、色を変えることができません。
その結果、アラートが出ないことがあるのです。
また、条件付き書式を習得するなら、同じ労力を使ってマクロを覚えた方がはるかに仕事の幅が広がります。
これについては、以下の記事で詳しく解説しています。ぜひ合わせて読んでみてください。
エクセルを自分用に編集アレンジして使いたいなら、エクセルマクロの無料動画で学ぼう
ここまでエクセルマクロのプログラムについて紹介してきました。
しかし、この記事を見たとき、「もっと○○ができるのでは？」や「ここはなんとかならないのか」と感じる人は少なくないはずです。
例えば、「他の条件を付け加えたい」や「さらに機能を増やしたい」といった要望が出るかもしれません。
このような要望を満たすには、マクロを勉強して自力でマクロを編集できるようになる必要があります。
もし、自力でマクロを編集できるようになれば、今より仕事の効率はグッと上がります。
実際、私も自力でマクロを書けるようになってからは、仕事の生産性が一気に上がりました。
他の人が30分～1時間かけて行う仕事が、ボタン一つで終わらせることができるようになったのです。その結果、周囲からの信頼も増し、仕事で高い評価を得られるようになりました。
ただ、要望に応えるようになるためには、マクロを学ぶ必要があります。
まずは無料でマクロを勉強してみる
ウェブや書籍で勉強すれば、マクロを習得できると考えている人は少なくありません。
しかし、仕事で使えるマクロを習得したいなら、仕事で使える部分に特化した教材で学ぶことをお勧めします。
なぜなら、ウェブや書籍には仕事に関係しない部分まで提供していることが多いからです。
例えば、マクロ初心者なのに配列を学ぼうとする人がいます。実は配列なしでも仕事で使えるマクロを書くことは可能です。
しかし、マクロ初心者ほど「全ての知識が必要だ」と考えて、無駄な学習に時間を使ってしまうのです。詳しくは、こちらの記事で紹介しています。
そこで、私がお勧めするのは仕事に直結するマクロ教材です。とくにお勧めするのは、こちらの無料オンライン動画です。
なぜなら、仕事に直結する部分に絞って、エクセルマクロを学ぶことができるからです。
マクロの作り方・考え方から解説しているので、教材をしっかり学べばここで紹介したマクロをゼロから書けるようになります。
マクロ初心者が、仕事に直結したいマクロを学ぶなら、まずはこちらの無料オンライン動画を試すのがいいです。
興味がある人は、まずは無料でエクセルマクロの勉強を始めてみてください。
もっと学びたいと感じたら、さらに深く勉強をしてみることをお勧めします。