大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、「作業列」を使ってオートフィルタをフィルタする方法について、ご紹介します。実務では複雑なフィルタが必要になります。このような場合でも、「作業列」を使えば、簡単にフィルタすることができます。作業列を使う場合は、高速化のため「配列」を使いましょう。

はじめに

この記事では、作業列を使ってオートフィルタをフィルタする方法にについて、ご紹介します。

オートフィルタの標準機能は、「2つの条件」もしくは「配列」を使ったものです。

ただ、実務では複雑なフィルタ条件が必要になるので、標準の機能ではフィルタ出来ない場合があります。

「作業列」を使えば、どんなフィルタ条件でも自由にフィルタできます。

さらに「配列」を使って、作業列に一括で入力すると、高速化もできます。

複雑なフィルタが必要な場合は、「配列」を使って「作業列」に一括入力してフィルタしましょう。

では、作業列を使ってオートフィルタをフィルタする方法について、解説していきます。

この記事で紹介すること

  • 作業列を使ってオートフィルタをフィルタする方法

目次から見たい項目へ移動すると便利ですよ。

目次

オートフィルタでフィルタできる条件

オートフィルタでフィルタできるのは、「2つの条件」もしくは「配列」を使ったフィルタです。

「2つの条件」もしくは「配列」

「2つの条件」もしくは「配列」でフィルタしてみます。

「2つの条件」は、「xlAnd」と「xlOr」でフィルタができます。

「xlAnd」を使う

「xlAnd」を使ったVBAコードです。

Sub TEST1()
    
    '「xlAnd」を使って2つの条件でフィルタ
    Range("A1").AutoFilter 2, "<=700", xlAnd, ">=300"
    
End Sub

表を用意しておきます。

表を用意しておきます

実行します。

「xlAnd」を使ってフィルタ

「xlAnd」を使って2つの条件でフィルタする場合です。

「xlOr」を使う

「xlOr」を使ってフィルタしてみます。

Sub TEST2()
    
    '「xlOr」を使って2つの条件でフィルタ
    Range("A1").AutoFilter 2, ">700", xlOr, "<300"
    
End Sub

実行します。

「xlOr」を使ってフィルタ

という感じで、「xlOr」を使ってフィルタできます。

「配列」を使う

「配列」を使う場合は、こんな感じです。

Sub TEST3()
    
    '配列を使って3つの値をフィルタ
    Range("A1").AutoFilter 2, Array("800", "600", "300"), xlFilterValues
    
End Sub

実行します。

「配列」を使ってフィルタ

「配列」を使ってフィルタできました。

という感じで、オートフィルタの標準機能としては、「2つの条件」や「配列」を使ったフィルタができます。

ただ、実務ではもっと複雑なフィルタが必要ということがあります。

標準のフィルタで対応できない場合は、「作業列」を使って、フィルタをしましょう。

オートフィルタで作業列を使ってフィルタ

オートフィルタで作業列を使ってフィルタしてみます。

新しく作業列を追加して、条件の一致した行に対して、印を入力していきます。

「AとDとG」以外をフィルタ

作業列を使って、「AとDとG」以外をフィルタしてみます。

手順としては、

  • 条件の一致する行に「〇」を入力
  • 作業列で「〇」をフィルタ

という流れです。

Sub TEST4()
    
    For i = 2 To 13
        '商品がA、D、G以外の場合
        If Cells(i, "A") <> "A" And Cells(i, "A") <> "D" And Cells(i, "A") <> "G" Then
            Cells(i, "C") = "〇" '作業列に印をつける
        End If
    Next
    
    '作業列でフィルタ
    Range("A1").AutoFilter 3, "〇"
    
End Sub

表を用意しておきます。

表を用意

表を用意

では、VBAコードを実行してみます。

「AとDとG」以外をフィルタ

「AとDとG」以外をフィルタ

「AとDとG」以外をフィルタできました。

こんな感じで、作業列を使えば、標準の機能以外のフィルタもできます。

他の例もやってみます。

2つの列を「OR」でフィルタ

2つの列を「OR」でフィルタする、というのをやってみます。

下記の条件を「OR」でフィルタします。

  • 商品が「A、DもしくはG」
  • 支店が「東京もしくは福岡」

VBAコードは、こんな感じになります。

Sub TEST5()
    
    For i = 2 To 13
        '商品がA、DもしくはGの場合
        If Cells(i, "A") = "A" Or Cells(i, "A") = "D" Or Cells(i, "A") = "G" Then
            Cells(i, "D") = "〇"
        End If
        '支店が東京もしくは福岡の場合
        If Cells(i, "B") = "東京" Or Cells(i, "B") = "福岡" Then
            Cells(i, "D") = "〇"
        End If
    Next
    
    '作業列でフィルタ
    Range("A1").AutoFilter 4, "〇"
    
End Sub

表を用意しておきます。

表を用意

表を用意

では、VBAコードを実行してみます。

2つの列を「OR」でフィルタ

2つの列を「OR」でフィルタ

2つの列を「OR」でフィルタできました。

もうちょい複雑なものでやってみます。

3つの列の条件でフィルタ

3つの列の条件でフィルタしてみます。

  • 価格が「500以上」

で、下記の「OR」条件でフィルタします。

  • 商品が「A、DもしくはG」
  • 支店が「東京もしくは福岡」

これも、作業列を使えば簡単にフィルタできます。

Sub TEST6()
    
    For i = 2 To 13
        '価格が「800以上」の場合
        If Cells(i, "C") >= 800 Then
            '商品がA、DもしくはGの場合
            If Cells(i, "A") = "A" Or Cells(i, "A") = "D" Or Cells(i, "A") = "G" Then
                Cells(i, "D") = "〇"
            End If
            '支店が東京もしくは福岡の場合
            If Cells(i, "B") = "東京" Or Cells(i, "B") = "福岡" Then
                Cells(i, "D") = "〇"
            End If
        End If
    Next
    
    '作業列でフィルタ
    Range("A1").AutoFilter 4, "〇"
    
End Sub

表を用意します。

表を用意

表を用意

では、VBAコードを実行してみます。

3つの列の条件でフィルタ

3つの列の条件でフィルタ

3つの列の条件でフィルタできました。

標準のフィルタ機能でできないものは、「作業列」を使いましょう。

作業列を使う際のポイント

作業列を使う際のポイントは、「最終行」を取得して使うことと、「配列」を使って高速化することです。

「End」で最終行を取得する

「End」で最終行を取得して、作業列を使ってフィルタしてみます。

最終行は、「Cells(Rows.Count, "A").End(xlUp).Row」で取得できます。

Sub TEST7()
    
    '最終行までループ
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        '商品がA、D、G以外の場合
        If Cells(i, "A") <> "A" And Cells(i, "A") <> "D" And Cells(i, "A") <> "G" Then
            Cells(i, "C") = "〇"
        End If
    Next
    
    '作業列でフィルタ
    Range("A1").AutoFilter 3, "〇"
     
End Sub

表を用意しておきます。

表を用意

表を用意

では、VBAコードを実行してみます。

最終行までループしてフィルタ

最終行までループしてフィルタ

「AとDとG」以外でフィルタできました。

最終行までループしてフィルタできています。

配列を使って高速化する

もう一つのポイントは、「配列」を使って高速化することです。

作業列を使う場合は、セルに値を入力する必要があります。

Excelの中では、「セルに値を入力」する作業に時間がかかってしまいます。

なので、配列を使って「一括で」入力すると高速化できます。

配列を使って高速化

配列を使って高速化するには、先に配列を作成しておきます。

「ReDim X(1 To LastRow, 1 To 1)」で最終行までの配列を作成します。

条件に一致した場合は、「X(i, 1) = "〇"」というように、配列に値を入力していきます。

Sub TEST8()
    
    '最終行を取得
    Dim LastRow
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    '配列を作成
    Dim X
    ReDim X(1 To LastRow, 1 To 1)
    X(1, 1) = "作業列" '見出しを入力
    
    '最終行までループ
    For i = 2 To LastRow
        '商品がA、D、G以外の場合
        If Cells(i, "A") <> "A" And Cells(i, "A") <> "D" And Cells(i, "A") <> "G" Then
            X(i, 1) = "〇" '配列に入力
        End If
    Next
    
    'フィルタを解除
    Range("A1").AutoFilter
    
    '配列を作業列に入力
    Range("C1").Resize(UBound(X, 1)) = X
    
    '作業列でフィルタ
    Range("A1").AutoFilter 3, "〇"
        
End Sub

表を用意しておきます。

表を用意しておきます

実行してみます。

下記のコードで、一括で、作業列に値を入力できます。

'配列を作業列に入力
Range("C1").Resize(UBound(X, 1)) = X

実行するとこのようになります。

結果を一括で作業列に入力できる

では、実行した結果をみてみます。

先ほどと同じ結果となります。

配列を使って作業列に結果を代入してフィルタした結果

「AとDとG」以外をフィルタできました。

時間を計測してみる

2001行の表で、時間を計測してみます。

「配列を使わない場合」と「配列を使った場合」で比較します。

2001行の表を用意

作業列を使って「AとDとG」以外をフィルタした結果は、このようになります。

AとDとG以外をフィルタした結果

「AとDとG」以外でフィルタできました。

計測した結果は、次のようになります。

  • 配列なし:7.270 秒
  • 配列あり:0.070 秒

「配列」を使った場合は、「0.070 秒」です。

2001行ではありますけども、作業列を使っても高速にフィルタすることができます。

実務で複雑なフィルタが必要になった場合は、「配列」で一括入力する「作業列」を使って、フィルタしましょう。

おわりに

この記事では、作業列を使ってオートフィルタをフィルタする方法にについて、ご紹介しました。

オートフィルタの標準機能は、「2つの条件」もしくは「配列」を使ったものです。

ただ、実務では複雑なフィルタ条件が必要になるので、標準の機能ではフィルタ出来ない場合があります。

「作業列」を使えば、どんなフィルタ条件でも自由にフィルタできます。

さらに「配列」を使って、作業列に一括で入力すると、高速化もできます。

複雑なフィルタが必要な場合は、「配列」を使って「作業列」に一括入力してフィルタしましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す