レコードの項目列のデータに対し、左から一定の文字列を取得することにより、結果的に余計な情報を一気に削除するサンプルコードです。
集計業務全般に言えることですが、最終的なクロス集計をする前に、必ず集計対象のデータをチェックすることは大切ですよね。
例えば、文字列が入る項目に数値が入っていたり、規程の文字長さより長かったり、その逆に短かったりするデータを、Excelのフィルタリング機能を使って調べたりします。
そうして見つけたイレギュラーなデータは、置換機能や関数などを使って手作業で修正することが多いと思いますが、まれに置換後の文字を間違えたり、関数の使い方を間違えたりして、思わぬミスをすることも少なからずあります。
その点、VBAコードなら手作業よりも正確に、早くデータの修正を終わらせることができます。今回はそのVBAコードを使ったデータ修正の例をご紹介します。
まずはデータ修正が発生する例を考えてみますね。
たとえば、データを取りまとめる際、データを提供してくれる人に対し、テンプレートを配布し、結果をテンプレートに沿って返してもらえると助かりますよね。
少なくとも余計な挿入行や挿入列などを入れて返されることは少なくなります。そうなるとデータシートの行列数が違うようないびつ感はなくなりますので、そのまま表を合体して集計できそうな感じがします。
ところが、データの中身について確認してみると、こちらの意図しないデータがちらほら入っていたりします。そのまま集計すると、当然集計結果も正しいものではなくなりますよね。
そこで、最終的な集計前の大切な確認作業として、「データのクレンジング」が必要になります。つまり、集計対象の1つ1つのデータの誤記、漏れ、表記のゆれなどをチェックし修復する作業です。
今回はそのデータ修復に便利なVBA関数の1つ「Left関数」の使用例を紹介します。
では、今回のサンプルファイルになりますので、準備してくださいね。（Excelファイル形式は97-2003形式です)
シートはこれまでのサンプルシートに、少しアレンジを加えたものです。このシートは、鈴木さんから提供されたデータとして用意しました。
わたしの経験ですが、テンプレートにより行列を制限すると、このような補足情報をセルの中に一緒に記載されてくる場合がありました。
今回の鈴木さんの例もそうです。鈴木さんは、よほど休暇を取りたいのか、あるいはほかの担当者に迷惑を掛けたくないという配慮なのか、自分の名前の横に休暇取得予定を補足として記載しています。
また、自分の担当する「市場」の情報についても、親切に補足情報として書いてますね。
ただいずれの補足情報も、この後の全体の合算集計をするのに都合が悪いですよね。そこで、鈴木さんの休暇予定と、市場の補足情報については大切に別のメモなどに取った後、VBAで集計に不要なそれらのデータを取り除きます。
では今回のコードです。サンプルファイルに標準モジュールを準備して、下記のコードを真似て書いてみてくださいね。
書き終えましたら、VBAコードを実行してみましょう。上手くいけば、Sample01と同じになります。きれいに余計なデータが無くなりましたよね。
では続いてコードの解説です。
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 | Option Explicit 'LEFT関数の使い方 Sub Sample035() Dim wstSelf As Worksheet 'ワークシート用 Dim lngERow As Integer '最終行番号取得用 Dim r As Integer '行ルーチン処理用 'ワークシートをオブジェクト変数にセット Set wstSelf = Worksheets("Left関数") With wstSelf '最終行番号取得 lngERow = .Range("A" & .Rows.Count).End(xlUp).Row '全レコードルーチン処理 For r = 2 To lngERow '「仕入担当」から名前だけを抽出 .Cells(r, 5) = Left(.Cells(r, 5), 2) '「仕入先」から市場情報だけを抽出 .Cells(r, 6) = Left(.Cells(r, 6), 3) Next End With End Sub |
上記VBAコードは、すべてのレコードを繰り返し処理する中で、「仕入担当」の項目（E列、5列目)と「仕入先」の項目(F列、6列目)について、必要な情報の抽出を行っています。
20行目：.Cells(r, 5) = Left(.Cells(r, 5), 2)
23行目：.Cells(r, 6) = Left(.Cells(r, 6), 3)
5列目「仕入担当」と6列目「仕入先」の処理になります。右辺の結果を、左辺に代入しています。では、初登場の「Left関数」について次図で説明しますね。
まずLeft関数は、文字列を処理する関数です。そして処理対象の文字列の左側から（先頭から）指定した文字数分を抽出して、結果を文字列として返します。
使い方ですが、Left()の中に「処理対象の文字列」と「左側から（先頭から）抽出したい文字数」の順番に「,」で並べます。
上記のLeft関数の使い方を、今回の例に当てはめて考えると次のようになります。
「仕入担当」の左から2文字目までを抽出することにより、3文字目以降の余計なデータは排除されます。
ただし、ここでは仮に「苗字が2文字の人しかいない」という前提にしています。苗字が3文字以上を考慮する処理については複雑になりますので、それについては別の機会に紹介しますね。
「仕入先」についても同様、A市場からE市場まで必要なデータは3文字分ですので、先頭の3文字を抽出し、4文字目以降の余計なデータは排除されます。
まとめ
今回は「Left関数」の使い方についてお話ししました。集計業務では、データのゆらぎ修正でよく使う関数の1つですので、繰り返し使い方を練習してみてくださいね。