Excel vbaで正規表現(RegEx)を使用して、データをフィルターする

正規表現(Regular Expression)は、文字列のフィルターと一致に対応する強力なツールです。Perlプログラミング言語で提供されるRegular Expressionを使用して、サーバー管理と文字処理を実行します。その後、データ処理にExcelをよく使うため、Regular ExpressionをExcelに追加できれば、データ処理の効率が上がると考えます。その後、いくつかのマクロを作成でき、MicrosoftのVBScript Regular Expressionを使って文字データを処理してみたところ、かなり使いやすいと思います。 アドインとして設計し、後で[データ処理フィルターアドイン]に配置します。VBScript Regular ExpressionはPerlプログラミング言語で提供されるRegular Expressionと違いますが、概念は非常に似ています。その後の.netはRegular Expressionのステートメントを提供しますが、すでに、VBScript Regular Expressionから提供されるステートメントを使用することに慣れているため、それを変更せず、今まで使用し続けました。

[データ処理フィルターアドオン]では、いくつかのRegular Expressionによってデータフィルター、処理、抽出の機能を作成しました。これらの機能は[ワークシートデータフィルター]、[ワークシートデータ抽出]、[ワークシートデータ処理]、[テキストデータ抽出]、[バッチテンプレートデータファイルの生成]、[レポートデータ抽出]です。

ユーザーは一部の機能が変と思うかもしれません。役立たずみたいのに、どうしてこれらの機能を作成しますか?その理由は、学校で就職期間に、各事務室のデータを処理するため、これらの機能を作成できました。実際の職場では特殊な実例が様々です。ただ一つ特殊な実例のために新しい機能を作成することは割に合わないですが、当時まだ若くて、時間や精力があり、プログラミングの練習としてやってみました。また、当時教職員の人数が多いため、一件ずつ処理するのは大変です。

最初に、VBScript Regular Expressionが提供されるいくつかのステートメントをご紹介します。

正規表現のステートメントの参照

正規表現のステートメントの説明: (参照元:Microsoftの正規表現の説明)

正規表現(Regular Express)は、特定の文字を組み合わせて生成される文字列一致ルールです。この一致ルールは、特定の文字列を一致するために使用できます。このルールはパターンと呼ばれ 、//を使用してパターンを示します。例えば、文字列に英字のnがあるかどうかを一致する場合、一致するパターンは/n/と記述します。

次には、このプログラムでサポートされている正規表現の記号と操作方法について説明します。

記号説明
\一致の部分は正規表現の特殊文字が含まれている場合は、\記号を追加する必要があります。例えば、文字列に改行記号があるかどうかを一致する場合、パターン一致は/\ n/と記述します。 文字列に\記号があるかどうかを一致するには、パターンを/\/と記述します。
^既定では、文字列の先頭で一致する必要があります。複数行モードでは、行の先頭で一致する必要があります。例えば、文字列の先頭がaであるかどうかを確認するために、パターンは/^a/と記述します。
$既定では、文字列の末尾で一致するか、文字列の末尾にある \n の前で一致する必要があります。例えば、文字列の末尾がaであるかどうかを確認するために、パターンは/a$/と記述します。
*直前の要素と 0 回以上一致します。例えば、パターンは/zo*/と記述され、一致する文字列は”z”、”zo”又は”zoooo”です。
+直前の要素と 1 回以上一致します。例えば、パターンは/zo*/と記述され、一致する文字列には”zo”、”zoooo”がありますが、”z”は一致しません。
?直前の要素と 0 回または 1 回一致します。例えば、パターンは/z?oom/と記述され、一致する文字列には”zoom”又は”oom”です。
.ワイルドカード:\n を除く任意の 1 文字と一致します。
( pattern )一致した部分式をキャプチャして、1 から始まる序数を代入します。()を一致する場合は、()と記述します。
x | y縦棒 (|) 文字で区切られたいずれかの要素と一致します。
{ n }直前の要素とちょうど n 回一致します。nは正の整数である必要があります。例えば、/e{2}/はfeedやfeelなどの文字列と一致しますが、keyは一致しません。
{ n ,}直前の要素と n 回以上一致します。nは正の整数である必要があります。例えば、/e{2}/はfeed、feelやgeeeなどの文字列と一致しますが、keyは一致しません。
{ n , m }直前の要素と n 回以上 m 回以下で一致します。nとmは正の整数である必要があります。例えば、/e{1,2}/はfeed、key、feelなどの文字列と一致しますが、geeeは一致しません。
[ xyz ][]内の任意の 1 文字と一致します。たとえば、[abc]はafter、book文字列と一致しますが、dogとは一致しません。
[^ xyz ][^ ] 内にない任意の 1 文字と一致します。例えば、[abc]は文字列dog、foodと一致しますが、after、bookとは一致しません。
\b \w(英数字) と \W (英数字以外) 文字の境界位置で一致する必要があります。例えば、文字列”cook cake”では/k\b/と一致するパターンは、cakeのkではなく、cookのkです。
\B \b境界以外で一致する必要があります。例えば、”cook cake”では/k\B/ と一致するパターンはcakeのkではなく、cookのkです。
\d 10進数字と一致します。
\D10 進数以外の任意の文字と一致します。
\fform-feedの \u000C と一致します。
\n改行文字の \u000A と一致します。
\rcarriage returnの \u000D と一致します。
\s空白文字と一致します。例えば、space, tab, form-feedなど、[ \f\n\r\t\v]と記述できます。
\S空白以外の文字と一致します。 [^ \f\n\r\t\v]と記述できます。
\ttabの \u0009 と一致します。
\vvertical tabの \u000B と一致します。
\w単語に使用される任意の文字と一致します。[A-Za-z0-9_]と記述できます。
\W単語に使用される文字以外の任意の文字と一致します。 [^A-Za-z0-9_]と記述できます。

Excelで使用する場合、次の手順に従って操作してください。

ステップ1

[Microsoft VBScript Regular Expressions 5.5]にVBA参照を追加します。

1、Excelワークブックを開きます。

2、Alt + F11を押して、VBA ウィンドウを開きます。

3、[ツール]—[参照]の順に、正規表現式への参照を追加します。

4、Microsoft VBScript Regular Expression 5.5を選択します。

ステップ2

モデルを定義します。

基本定義:

A1範囲

  • 例えば、A1はaからzまでの小文字と一致します。
  • 例えば、A1は0から5までの任意の数と一致します。

A1は、()内のオブジェクトと完全一致します。

  • 例えば、A1は文字aと一致します。
  • 例えば、A1は1つの文字(a、b、またはc)と一致します。
  • 例えば、A1は、任意の1つの小文字と一致します。

A1は異なる目標区域と一致するオブジェクトを返します。次の例をご覧ください。

A1は、その前に定義されたパターンの重複している副本の乗数として使用されます。

  • 例えば、A1は2つの連続する小文字(a:A2)と一致します。
  • 例えば、A1は、少なくとも1つ、最大3つの小文字[A2,A3,()]と一致します。

A1は、その前に定義されたパターンと1回以上一致します。

  • 例えば、A1は、連続するA2,A3,(),などと一致します。

A1は、その前に定義されたパターンと0回以上、1回以下一致します。

  • 例えば、パターンは存在する場合と存在しない場合がありますが、一致することは1回だけです。
  • 例えば、A1は、空白または任意の1つの小文字と一致します。

A1は、その前に定義された0個以上のパターンと一致します。 例えば、存在する場合と存在しない場合があるパターンのワイルドカードです。 例えば、A2は空白または小文字の文字列と一致します。

A1は、改行記号以外のすべての文字A2と一致します。

  • 例えば、A1は、aで始まり、A2以外で終わる二重文字列と一致します。

A1 OR 演算子

  • 例えば、A1は、A2またはA3と一致することを意味します。
  • 例えば、A1は1つの色と完全に一致します。

A1 NOT演算子

  • 例えば、A1文字に数字を含めることはできません。
  • 例えば、A1文字を小文字のA2または大文字のA3にすることはできません。

A1は後の特殊文字をエスケープします。(上記の操作をオーバーライド)

  • 例えば、A1,A2,A3,(),$1,()

アンカーモード:

A1との一致することは、文字列の先頭で発生する必要があります。

  • 例えば、A1の最初の文字は小文字のA2でなければなりません。
  • 例えば、A1の最初の文字は数字でなければなりません。

A1との一致することは、文字列の最後で発生する必要があります。

  • 例えば、A1の最後の文字は小文字のA2でなければなりません。

優先順位表:

OrderNameRepresentation
1Parentheses( )
2Multipliers? + * {m,n} {m, n}?
3Sequence & Anchors abc^ $
4Alternation|

事前定義された文字の略語:

abrsame asmeaning
\d[0-9]Any single digit
\D[^0-9]Any single character that’s not a digit
\w[a-zA-Z0-9_]Any word character
\W[^a-zA-Z0-9_]Any non-word character
\s[ \r\t\n\f] Any space character
\S[^ \r\t\n\f]Any non-space character
\n[\n]New line

例1:マクロとして実行

次のマクロの例では、セルA1の値を調べて、最初の1文字または2文字が数字であるかどうかを確認します。 数値である場合、それらは削除され、残った文字列が表示されます。そうでない場合は、一致するものが見つからなかったことを示すメッセージボックスが表示されます。 セルA2の値A3は()を、値$1は()を返し、値abc123は文字列の先頭が数字ではないため、[不一致]を返します。

Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A1")

If strPattern <> "" Then
    strInput = Myrange.Value

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        MsgBox (regEx.Replace(strInput, strReplace))
    Else
        MsgBox ("Not matched")
    End If
End If

End Sub

例2:セル内の関数として実行

この例は例1と同じですが、セル内関数として実行するように設定されています。 使用する際には、コードを次のように変更します。

Function simpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String

strPattern = "^[0-9]{1,3}"

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = ""

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.test(strInput) Then
        simpleCellRegex = regEx.Replace(strInput, strReplace)
    Else
        simpleCellRegex = "Not matched"
    End If
End If

End Function

文字列( “12abc”)をセルA1に記述します。 この式A2をセルA3に入力すると、結果は”abc”を返します。

例3:ループ範囲

Private Sub simpleRegex()
Dim strPattern As String: strPattern = "^[0-9]{1,2}"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A1:A5")

For Each cell In Myrange
    If strPattern <> "" Then
        strInput = cell.Value

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
Next

End Sub

例4:異なるパターンを分割する

この例では、範囲(A1,A2&amp; A3)をループして、3つの数字で始まり、1つの英文字、4つの数字が続く文字列を検索します。()によって一致のパターンを隣接する部分に分割して出力します。$1は最初の()内のパターンを表します。

Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A1:A3")

For Each C In Myrange
    strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

    If strPattern <> "" Then
        strInput = C.Value

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            C.Offset(0, 1) = regEx.Replace(strInput, "$1")
            C.Offset(0, 2) = regEx.Replace(strInput, "$2")
            C.Offset(0, 3) = regEx.Replace(strInput, "$3")
        Else
            C.Offset(0, 1) = "(Not matched)"
        End If
    End If
Next

End Sub

結果:

付加パターンの例

StringRegex PatternExplanation
a1aaa[a-zA-Z][0-9][a-zA-Z]{3}Single alpha, single digit, three alpha characters
a1aaa[a-zA-Z]?[0-9][a-zA-Z]{3}May or may not have preceeding alpha character
a1aaa[a-zA-Z][0-9][a-zA-Z]{0,3}Single alpha, single digit, 0 to 3 alpha characters
a1aaa[a-zA-Z][0-9][a-zA-Z]*Single alpha, single digit, followed by any number of alpha characters
</i8>\<\/[a-zA-Z][0-9]>Exact non-word character except any single alpha followed by any single digit

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です