【配列操作】VBAで2次元配列をフィルターする関数(高速)

VBAで2次元配列をフィルターする

パパ
今日は2次元配列のフィルターをやるよ!
あこ
上級向けのテクニックだね。
目次目次[閉じる]

今回の関数の内容

例として、以下の気象庁のデータを用います。

気象庁データの例

こちらのデータのH列の「天気概要」の列が「快晴」のものだけを取り出します。

フィルター後のデータ

今回の関数は配列操作なので、上記のようなシートを実際に操作するのではなく、これを配列上で行います。

あこ
配列上で操作すると高速で処理できるメリットがあるよ

全コード紹介(コピペOK)

まずは、全体のコードを紹介します。コピペでも動きます。

'************************************************************************************************************
'**2次元配列をフィルターする(特定の1列に対してひとつの値に一致するものを抽出
'**第一引数:data => フィルターしたい2次元配列 バリアント型
'**第二引数:col_num => フィルターする列の番号(左から1から数える)整数型
'**第三引数:key_array => 抽出したいキーワード
'**※dataにヘッダーが含まれる前提、ヘッダーも含まれた配列を返す
'**※Option Base 1 を必ず使用した上で利用すること
'*************************************************************************************************************
Function 二次元配列フィルター関数(ByVal data As Variant, ByVal col_num As Integer, ByVal key As String) As Variant
    Dim cnt As Long 'フィルター後の配列の行数
    Dim n_col As Long '配列の列数
    Dim dic As Object
    Dim r_array As Variant '条件に合う列の各列を一時的に格納するための配列
    Dim data_fil As Variant 'フィルター後の2次元配列

    '元の配列(data)の列数(=フィルター後の列数
    n_col = UBound(data, 2)
    '上記の列数(変数)を用いてr_arrayを再定義
    ReDim r_array(n_col) As String

    '辞書型配列を定義
    Set dic = CreateObject("Scripting.Dictionary")

    'フィルター後の配列の行数をカウントし、該当した行を配列に格納する
    cnt = 1 'ヘッダー分の列数をあらかじめ考慮
    For i = 2 To UBound(data)
        If CStr(data(i, col_num)) = key Then
            '取り出したい行の列を配列に格納する
            For j = 1 To n_col
                r_array(j) = data(i, j)
            Next j
            cnt = cnt + 1
            dic.Add cnt, r_array
        End If
    Next i

    'ヘッダー部分を辞書型配列に格納
    For j = 1 To n_col
        r_array(j) = data(1, j)
    Next j
    dic.Add 1, r_array

    'フィルター後の配列の再定義
    ReDim data_fil(cnt, n_col) As String

    'フィルター後の配列にヘッダーを格納
    For j = 1 To n_col
        data_fil(1, j) = dic.Item(1)(j)
    Next j

    'ヘッダー以外の値をフィルター後の配列に格納
    For i = 2 To cnt
        For j = 1 To n_col
            data_fil(i, j) = dic.Item(i)(j)
        Next j
    Next i

    '関数に代入
    二次元配列フィルター関数 = data_fil

End Function

コードの使い方

今回は以下の3つの引数を用います。

  1. フィルターしたい2次元配列
  2. キーワードがある列番号(整数型)
  3. キーワード(文字列)

これらの引数を上記の順で関数に代入して使います。

関数の実際の使い方

今回の場合は8番めのH列が「快晴」のものを取り出すのでこのような入力になります。

また、1点このコードを使う上での注意点です。

ポイント
Option Base 1 を使用すること

このオプションを利用しないと正しく動かないので注意してください。

このオプションは配列の番号を0 スタートではなく、1スタートにするオプションです。

これをsubの上部に入れてください。

コードの解説

パパ
ここからは固まりごとにコードの解説をしてくよ!
    '元の配列(data)の列数(=フィルター後の列数
    n_col = UBound(data, 2)
    '上記の列数(変数)を用いてr_arrayを再定義
    ReDim r_array(n_col) As String

まずはデータの列数をUboundで取得して、変数n_colに代入します。 そして、n_colを要素数とする配列r_arrayを定義します。

次がコードの肝になります。

    '辞書型配列を定義
    Set dic = CreateObject("Scripting.Dictionary")

    'フィルター後の配列の行数をカウントし、該当した行を配列に格納する
    cnt = 1 'ヘッダー分の列数をあらかじめ考慮
    For i = 2 To UBound(data)
        If CStr(data(i, col_num)) = key Then
            '取り出したい行の列を配列に格納する
            For j = 1 To n_col
                r_array(j) = data(i, j)
            Next j
            cnt = cnt + 1
            dic.Add cnt, r_array
        End If
    Next i

Forループで2行目から順に8行目の値を確認し、キーワードと一致したらその行をr_arrayに格納します。

コードの補足説明その1

また、同時に変数cntを1ずつ増やし、フィルター後の配列数をカウントしてきます。

格納された配列と行数を表すcntをセットで2行目で定義された辞書型配列dicに追加します。

配列の要素をカウントする

ちなみに、cntが2から始まるようになっているのはヘッダーを入れる場所をあらかじめ確保するためです。

次にdataの1行目にある各列名を辞書型配列dicに格納します。

    'ヘッダー部分を辞書型配列に格納
    For j = 1 To n_col
        r_array(j) = data(1, j)
    Next j
    dic.Add 1, r_array

フィルターした後の配列data_filを定義して、dicに格納した値をdata_filに格納していきます。

    'フィルター後の配列の再定義
    ReDim data_fil(cnt, n_col) As String

    'フィルター後の配列に配列に値を格納
    For i = 1 To cnt
        For j = 1 To n_col
            data_fil(i, j) = dic.Item(i)(j)
        Next j
    Next i

配列の要素数は行数がcnt、列数は変わらないのでn_colです。

配列の定義に変数を用いる場合は、dimではなく、Redimを使って定義する必要があります。

辞書型配列dicの中の値をdata_filの中に格納していきます。

辞書型配列を使ってdata_filを埋めていく

最後に定義した関数にdata_filを代入して完了です。

    '関数に代入
    二次元配列フィルター関数 = data_fil

最後に

VBAで配列操作に慣れるといろんな処理を高速で行えるようになります。

パパ
あこちゃんどうだった?
あこ
フィルターはよく使う操作だから活用の機会多そうだね。
パパ
そうだね!関数をコピペするだけで簡単だからガンガン使ってね!