今日の講座「エラーデータを捜し出す方法」

―――――――――――――――――――――――――――――――――――
◆今日の講座
―――――――――――――――――――――――――――――――――――



┏━━▼ Lesson 159　　　 　　　　　--- エラーデータの対処方法 ---
┃
┃　　　・エラーデータを捜し出す方法
┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━▲


今回から「エラーデータの対処方法」と題しまして、いわゆるエラートラップ
をマクロで処理する為の基本から応用まで、数回にわたってご紹介していきた
いと思います。


今回、主に取り上げるテーマとしては、

・エラーデータの検索（エラーデータを捜し出す方法）
・エラーデータ確認表の作成（見つけたエラーを別シートに分ける方法）
・エラーデータの自動修復（見つけたエラーを自動で修復する方法）
・エラーデータの自動集計（修復したエラーの一覧を別表に残す方法）
・エラー条件の設定方法（色々なエラー条件の設定方法）

などなど予定しています。


読者の皆さんの中には、数千行、数万行（はたまた数十万行）といった大量の
データを扱ってるという方も多いかと思いますが、そのデータの中には必ずと
言っていいほど想定外といいますか、規定外のデータ(いわゆるエラーデータ)
というものが存在する場合が多いと思います。

そのエラーデータというものは、例えば、数万件のデータ中に数個あったり、
数十個、数百個ある場合もあろうかと思いますが、

大量のデータの中からそれらを捜し出し、手作業で修正したり、削除したり、
別シートに移動したり、とやるのは大変な手間の掛かる作業だと思いますので
日々そのようなエラーデータと格闘して苦労しているという方も多いのではな
いでしょうか。

そこで、今回はそうしたエラーデータに対処する方法（むろん、この講座では
いちいちそのような面倒な手作業はしませんので、「マクロで、自動で、」
です。^^ ）、そのためのマクロの作り方について勉強していきます。


で、エラーと一言でいっても色々なケースがありますし、その対処方法も実に
様々なわけですが、

今連載の最終的な目標は、エラーデータを自動修復しそのエラーデータの修復
一覧表(修復前と後のデータ)を別表に出力する。というところまでを目標に、
やってみたいと思います。


当然ですが、エラーデータか？エラーデータでないか？その条件や判断基準も
実に様々です。

例えば、

・必ずゼロ以上(正の整数)になるはずデータが負の数字になっていたり、
・数字データであるはずの項目に文字データが入っていたり、
・健康診断の一覧データに身長が 15m もあることになっている社員が
　存在していたり、
・取引データの日付が1805年1月1日みたいな有り得ない日ひにちになって
　いたり、

だったりします。


これ、ちょっと知ったエクセルユーザーなら、セルの関数や入力規則（メニ
ューの [データ(D)]→[入力規則(L)] ）でと考える方もいるかも知れません
が、既にサーバー等からエクセルのシート上に落としたデータに入力規則は
適用しませんし、大量のデータにいちいち関数を組み込んでいったら大変な
シートができてしまうと思いますので、

そこは地道に、検索、置換、オートフィル、ピボットテーブル、・・・etc
Excelの既存機能を駆使して面倒な手作業でやっているというケースが多い
だろうと思います。


そこでまず手始めに、今回はエラーデータを捜し出して、そこに目印の色を
塗るというマクロを作るところからやっていきます。



1)
ではまずはじめに、いつものようにマクロの記録を使ってその基となる
プログラム作りから始めます。
――――――――――――――――――――――――――――＋
1.エクセルを起動させます。（空のエクセルを立ち上げてください）

2.まず、マクロの記録を開始します。
　[ツール(T)] → [マクロ(M)] → [●新しいマクロの記録(R)...]
　→ マクロの記録画面が出るのでそのまま[ＯＫ]をクリック。

3.まず、B2のセルを選択し、とりあえず 123 と打ってください。
　打ち終わったら一度[Enter]キーを押して入力モードを解除して、

4.そうしたらもう一度、B2のセルを選択し、このセルを黄色く
　塗りつぶしてください。（コマンドバーの[塗りつぶしの色(黄)]
　にて）

5.最後に、[ツール(T)] → [マクロ(M)] → [■記録終了(R)]とやって、
　マクロの記録を終了させます。

――――――――――――――――――――――――――――＋



2)
では、いま作ったプログラムの中身をのぞいてみます。
――――――――――――――――――――――――――――＋
1.まず、いつもの様にプログラム用の画面を表示させます。
　[ツール(T)] → [マクロ(M)] → [Visual Basic Editor(V)]

2.この画面の左上半分の[＋標準モジュール]という所の＋の部分を
　クリックすると、そのすぐ下に[Module1]と表示されるので、その
　[Module1]をダブルクリックします。
――――――――――――――――――――――――――――＋


＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "123"
Range("B2").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―

(注）コメント行(うす緑色になってる部分)は省略しています。
(注）Excel2007バージョン以降ではトゥルーカラーというものが採用され
　　上記のColorIndexの数字の部分（カラー番号）が大きな数字になって
　　いるなどの違いがあると思いますが、それら（With ～ End With まで
　　の下記の部分）の違いは気にしなくて大丈夫です。

　　Excel2007以降の場合
　　＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
　　＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


これは、いつものようにこれから作るプログラムのがらを作るために、まず
処理対象とするセルに印をつけたという状態のマクロです。



3)
それでは早速、このプログラムにちょっと手を加えていきます。
――――――――――――――――――――――――――――＋
1.まず、
　 ActiveCell.FormulaR1C1 = "123"
　と書いてある行を
　　　a = ActiveCell.Value
　と書き改めてください。

2.次に、その下の
　 Range("B2").Select
　という1行を下記のIF文に修正してください。
　　　If a < 100 Then

　※今回プログラム中にこの Range("B2").Select という行は
　　2ヶ所ありますので、ここの修正はその内の下の方だけです。
　（上の方を修正しないよう、ご注意ください。）


3.最後に、プログラム最後の行
　　　End Sub
　のすぐ上に、下記の1行を追加します。
　　　End If

　（いつものように、For文 ～ Next i までの間の4行はTABキー
　　にて字下げをして(見やすくして)おいてください。）

――――――――――――――――――――――――――――＋


修正した後のプログラムは下記の様になります。


＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
Range("B2").Select
a = ActiveCell.Value
If a < 100 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


いま行った修正は、ある条件が当てはまる場合にセルに色を塗るというもの
です。（この場合、ある条件というのは100より小さい場合で、この時塗る色
は黄色だということになります。）



4)
それでは、実行してみましょう。
――――――――――――――――――――――――――――＋
1.エクセルの画面に戻って、まだ何も書かかれていない空のシート
　[Sheet2]を開いてください。

2.次に、その[Sheet2]のB2のセルに 200 という数字を入力して
　ください。

3.では、実行します。
　[ツール(T)] → [マクロ(M)] → [マクロ(M)...] → マクロ画面が
　出るので、そのまま[実行]をクリック。

4.ではもう一度、今度は同じB2のセルを 50 という数字に打ち直して
　ください。

5.では、再び実行します。
　[ツール(T)] → [マクロ(M)] → [マクロ(M)...] → マクロ画面が
　出るので、そのまま[実行]をクリック。
――――――――――――――――――――――――――――＋


1回目は何も起こらず、2回目の実行ではB2のセルが黄色く塗つぶされればＯＫ
ということになります。（これは単純に、1回目の実行データの 200 は条件の
100より大きく、2回目の 50 は100より小さいため(IF分の条件に合ったから)
の結果です。）



5)
それでは、もう少しこのプログラムを修正していきます。
――――――――――――――――――――――――――――＋
1.プログラムの画面に戻って、まず、プログラム先頭の
　　　Range("B2").Select
　という行の上に、下記の2行を追加します。
　　　n = Cells(Rows.Count, "B").End(xlUp).Row
　　　For i = 2 To n

2.続けて、プログラム最後
　　　End Sub
　という行の上に、下記の1行を追加します。
　　　Next i

(そうしたら、ここもいつものようにいま追加したループの中
　For文 ～ Next i までの間の8行をTabキーを使って字下げを
　行っておいてください。）

3.最後に、
Range("B2").Select
　という行を、下記に修正します。
Range("B" & i).Select

――――――――――――――――――――――――――――＋


修正した後のプログラムはつぎの様になります。


＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―
Sub Macro1()
'
n = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To n
Range("B" & i).Select
a = ActiveCell.Value
If a < 100 Then
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next i
End Sub
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋―


この辺の修正の仕方は何度もやっていますので、もうみなさんお馴染みかとは
思いますが、要するに、

ループをかぶせたらその For ～ Next の間で固定になっている数字の部分を

Range("B2").Select → Range("B" & i).Select

のように修正して、ループ変数( i とか j とか)に変えておくという、当講座
お決まりのパターンです。（ぜひこのワンパターン覚えておいてください。）



6)
それでは、実行してみましょう。
――――――――――――――――――――――――――――＋
1.エクセルの画面に戻って、まだ何も書かかれていない空のシート
　[Sheet3]を開いてください。

2.まず、実行する前にテストデータの準備をします。
　以下のテキスト(計11行)をコピーして、セルA1の位置に
　そのまま貼り付けてください。

氏名
Ａさん
Ｂさん
Ｃさん
Ｄさん
Ｅさん
Ｆさん
Ｇさん
Ｈさん
Ｉさん
Ｊさん


3.同様に、以下のテキスト(計11行)をコピーして、セルB1の位置に
　そのまま貼り付けてください。

身長
156
180
177
165
171
18
149
172
-56
159


4.では、実行します。
　[ツール(T)] → [マクロ(M)] → [マクロ(M)...] → マクロ画面が
　出るので、そのまま[実行]をクリック。

――――――――――――――――――――――――――――＋


実行した結果、7行目のＦさんと10行目のＩさんの身長データのセルが黄色く
塗つぶされればＯＫということになります。


これは、世の中に身長が18センチの人やマイナスの人はいないので、そのよう
な有り得ないデータ（エラーデータ）を今回作ったマクロで見つけ出して目印
の色を塗ったという結果です。



今回はこれで終了です。
今日作ったマクロプログラムは次回もこの続きでまた使いますので、大切に
保管しておいてください。（ファイル名 "gogo159.xls" ）


次回は、この見つけたエラーデータを別表に分けるといったマクロを作って
いきます。次回もお楽しみに！




★夏休みの1日でマクロ作りがマスターできる講座
<a href="http://www.wat3d.com/seminar/">エクセルマクロの「初心者サマーセミナー 2012」