Excel VBA スタンダードの暗記項目まとめ【第六章セルの検索とオートフィルターの操作】

6月 22, 20208月 7, 2020Excel VBA スタンダード

この記事では、VBAエキスパートの「Excel VBA スタンダード」の受験用に、暗記すべき項目について、公式の試験範囲の第六章をまとめました。

とくに暗記すべきところは「太文字」や「表」でまとめてあります。とくにステートメントやメソッド、プロパティについては、記述できるレベルまで仕上げておくことをおすすめします。

この記事では暗記を優先するため、解説をできるだけ省いています。学習未経験の人は、Excel VBA スタンダードの公式テキストなどで試験範囲の内容に目を通してからの利用を推奨します。

また、公式テキストに掲載されていないなど、試験に出る可能性の低い内容の解説は、基本的に割愛しています。

Excel VBA スタンダード第6章検索とオートフィルター（2019年5月改訂で追加）

6.1 検索の基本（2019年5月改訂で追加）

セルの検索は、Findメソッドを利用します。記述は以下のように行います。

「セル範囲.Find(What:=検索する値, LookAt:=検索方法)」

上記以外の引数については、公式テキストでは簡単に内容が紹介されているだけで、利用例は登場しません。あまり理解を深める必要性は低いかと思いますが、念のためまとめておきます。

What：検索する値を指定します
After：指定したセルの次から検索を開始するようになります。この引数を省略すると、検索対象セル範囲の左上セルから検索が開始されます
LookIn：セルの検索対象について、値・数式・コメントで指定できます
LookAt：完全一致か、部分一致かの検索条件を指定できます
SearchOrder：ワークシートの検索方向（行方向か、列方向か）を指定できます
SearchDirection：前方に検索するか、後方に検索するかを指定できます
MatchCase：大文字と小文字を区別するかを指定できます
MatchByte：半角と全角を区別するかを指定できます
SearchFormat：書式を検索するか、しないかを指定できます

Findメソッドの引数はWhat以外は省略可能ですが、公式テキストではLookAtは省略しないように推奨されています（前回の操作条件が引き継がれるので）。

LookAtについては、完全一致で検索する「LookAt:=xlWhole」と部分一致で検索する「LookAt:=xlPart」についておさえておきましょう。

6.2 見つからなかったときの判定（2019年5月改訂で追加）

Findメソッドで値が見つからなかった場合、Nothingが返されます。

また、Nothingを使った条件分岐を利用する場合は「If A Is Nothing Then」のように、Is演算子を使います。

6.3 検索したセルを使う（2019年5月改訂で追加）

Deleteメソッド

Deleteメソッドは、選択範囲の削除に利用できます。

EntireRowプロパティとEntireColumnプロパティ

EntireRowプロパティは、指定したセルを含む行全体を選択します。

同様に、EntireColumnプロパティは指定したセルを含む列全体を選択します。

Findメソッドで見つけた結果をRangeオブジェクトとして定義した「SearchResult」に代入した場合、先に紹介したDeleteメソッドと組み合わせて

「SearchResult.EntireRow.Delete」

とすると、検索結果の行全体を削除することができます。

Offsetプロパティ

Offsetプロパティは、指定したセルを基準にして、周囲にある別のセルを操作する際に利用されます。検索結果を「SearchResult」に代入した場合、

「SearchResult.Offset(2, 3)」

とすると、検索対象のセルの2行下、3列右のセルを指定することができます。

Copyメソッド

Copyメソッドは、「コピー元のセル範囲.Copy コピー先のセル」のようにして、セルの内容をコピーします。

Resizeプロパティ

Resizeプロパティでは、選択範囲を変更することができます。「選択範囲.Resize(行のサイズ, 列のサイズ)」のように記述して利用します。

例えば、Findメソッドと組み合わせる場合、検索結果を「SearchResult」に格納した後で、

「SearchResult.Resize(1, 3).Copy Range(“D1")」

のようにすると、検索結果とその行の2つ右までのセルを、D1の位置にコピーすることができます。

6.4 オートフィルターの基本（2019年5月改訂で追加）

オートフィルターをVBAで利用する場合は、AutoFilterメソッドを以下のように記述して利用します。

「セル.AutoFilter Field, Criteria1, Operator, Criteria2」

引数の「Field」は何列目を条件にするかの位置を指定します。

また「Criteria1」は絞り込みの条件の文字列です。複数利用する場合は「Criteria2」を利用します。

Operatorは、フィルターの種類を引数で指定します。Excel VBA スタンダードの場合、「Criteria1またはCriteria2」という条件なら「xlOr」、「Criteria1かつCriteria2」という条件なら「xlAnd」、Criteria1に配列を指定する場合「xlFilterValues」と覚えておけばOKです（詳細なフィルター条件はXlAutoFilterOperatorを参照）。

以下の例では、セルA1を含む表の、1列目に「マントヒヒ」か「バブーン」が含まれている行を絞り込みます。

Sub Macro1()
    Range("A1").AutoFilter 1, "マントヒヒ", xlOr, "バブーン"
End Sub

今回利用したAutoFilterメソッドは、戻り値を使わないため()がありません。

同様に、MsgBoxも戻り値を使わないため「MsgBox “バブーン"」のような記述になります。

試験では問われない知識だと思いますが、理解を深め、混乱を防ぐために覚えておくことをおすすめします。

6.5 絞り込んだ結果のコピーとカウント（2019年5月改訂で追加）

CurrentRegionプロパティ

絞り込んだ結果の範囲は、絞り込むまで判断できません。そこで、「絞り込んだ結果を含むひとかたまりのセル範囲」を範囲の判定に利用します。

具体的には、任意のセル（Rangeオブジェクト）を含むひとかたまりの範囲を示すCurrentRegionプロパティを利用します。

コピーについては、先に紹介したCopyメソッドと同様です。利用方法も同じく「コピー元のセル範囲.Copy コピー先のセル」となります。

オートフィルターとCurrentRegionプロパティを組み合わせると、例えば以下のようになります。

Sub Macro1()
    Range("A1").AutoFilter 1, "マントヒヒ", xlOr, "バブーン"
    Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
End Sub

SUBTOTAL関数（絞り込んだ後の内容を数える）

SUBTOTAL関数は、第五章で紹介したWorksheetFunctionの関数の一種です。

「SUBTOTAL(集計方法, セル範囲)」の形で記述し、集計方法は以下のとおりです。

集計方法	集計方法を示す番号（1桁は非表示のセルも含む）	同等の集計関数
平均値を求める	1, 101	AVERAGE
数値の個数を求める	2, 102	COUNT
データの個数を求める	3, 103	COUNTA
最大値を求める	4, 104	MAX
最小値を求める	5, 105	MIN
積を求める	6, 106	PRODUCT
不偏標準偏差を求める	7, 107	STDEV
標本標準偏差を求める	8, 108	STDEVP
合計値を求める	9, 109	SUM
不偏分散を求める	10 , 110	VAR
標本分散を求める	11, 111	VARP

Excel VBA スタンダードで重要になるのは、「3」のデータ個数を求める（非表示のセルも含む）です。公式テキストでそれ以外には言及されていませんし、他の値を選択した場合の集計方法の解説もでてきません（あくまでオートフィルターの活用の関連で登場する内容なので）。

また、タイトルの部分も集計結果に含まれてしまうため、タイトルを集計から除外するには「SUBTOTAL関数の結果ー 1」としなければならないところに注意が必要です。ひっかけ問題に注意しましょう。

6.6 絞り込んだ結果の編集（2019年5月改訂で追加）

絞り込んだセルに一括代入する

複数セルを選択し値を入力する場合、入力した値を確定する際に「Ctrl + Enter」とすると、選択したセル全体に一括して値を入れることができます。この方法を利用して、オートフィルターで絞り込んだ結果全体を選択して、「Ctrl + Enter」を使うと一括代入が可能です。

マクロで一括代入する場合、表示されているセルのみに自動で代入を行ってくれます。ただし、表示されている範囲全てに入力されてしまう関係上、入力すべき最初と最後のセルを特定する必要があります。

最初と最後のセルの特定としては、「Range(Range(“左上のセル"), Range(“右下のセル")」を覚えておくといいでしょう。ただし、最終セル（右下のセル）については、位置の特定が難しいため、後述する「ひとかたまりの範囲の最終セルを特定する」を覚えておくといいでしょう。

ひとかたまりの範囲の最終セルを特定する（Endモードの活用）

Endモードとは、Excelのシートを矢印キーで移動する際に、データが入力されている終端にジャンプできるモードです。

「Endキー」を押すことでEndモードになることにができ、Excelの下の部分に「ENDモード」と表示されます（Endモード自体は公式テキストで深く解説されていないので、問われないかと思います）。

このEndモードを使うことで、終端のセルの特定を、マクロで実行することができます。

Excel VBA スタンダードの対策としては、Rangeオブジェクトの持つEndプロパティを利用する方法を覚える必要があります。

Endプロパティでは「End(移動する方向)」として、セルの位置を特定します。移動する方向は、下(xldown)、左(xlToLeft)、右(xlToRight)、上(xlup)の4通りです。

公式テキストの例では、目的の列の一番上のセルから下に向かってジャンプする例と、シートの一番下のセルから、目的の列の最後に向かってジャンプする例が登場します。後者の方が複雑ですが、Excel VBA スタンダードの対策としては、両方を理解する必要があります。

目的とする列がC列（C1はタイトルなので含まない）の場合、以下のようになります。

Sub Macro1()
    Range("A1").AutoFilter 1, "マントヒヒ", xlOr, "バブーン"
    Range(Range("C2"), Range("C2").End(xlDown)) = 1000
    Range("A1").AutoFilter
End Sub

Sub Macro2()
    Range("A1").AutoFilter 1, "マントヒヒ", xlOr, "バブーン"
    Range(Range("C2"), Cells(Rows.Count, 3).End(xlUp)) = 1000
    Range("A1").AutoFilter
End Sub

例でMacro1で「ひとかたまりの範囲の最終セルを特定する」処理は「Range(“C2").End(xlDown)」の部分で実行しています。

単純に、Range(“C2")から下方向に連続するセルを移動して特定するという形です。

一方、例のMacro2で「ひとかたまりの範囲の最終セルを特定する」処理は複雑です。「Cells(Rows.Count, 3).End(xlUp)」の部分が最終セルを表します。

Cells(Rows.Count, 3)はC列の最終行を表し、そこから「.End(xlUp)」によりEndモードで上に移動してセルを特定します。Endモードで上に移動しているため、空白のセルは飛ばし、最初にぶつかった値のあるセル（絞り込みで残った表示されれているセル）が、対象となります。

ちなみに、Macro2の例の場合、シートの一番下の行から絞り込み範囲までの途中に、関係のない値が入ってしまうと、下の例のように一括入力の範囲が変わってしまいます（公式テキストには登場しない内容なので、Excel VBA スタンダードの自体では出題されないかと思いますが）。