エクセル関数を使って効率化しているけれど、関数だけでは限界を感じる。
VBAでエクセル関数の処理をしたいけど、どう書いていいかわからない。
間違えて関数を消してしまってフォーマットがおかしくなったり、毎回関数を入力するのに時間がかかったり、関数での効率化に限界を感じることはありませんか?また、VBAがわかる人の中にも、エクセル関数の処理をVBAでする方法がわからないという方もいるのではないでしょうか。
今回は、VBAとエクセル関数を組み合わせて、簡単かつ効率的に作業を進めるための方法を紹介します。
VLOOKUP/SUMIF/COUNTIFについては、コピペと場所の指定だけで使えるコードも紹介していますので、ぜひ活用しながら学んでください!
【コピペOK】VLOOKUP・XLOOKUP・SUMIF・COUNTIFのVBAコード例
VLOOKUPを使うVBAコード
VBAでVLOOKUPをつかうコードは、こちらです。
result =
WorksheetFunction.VLookup(Range(“A1”).Value, Range(“B:D”), 2, False)
このコードは、セル”A1″の検索値を指定し、“B:D”列の範囲から2列目を検索します。
詳細は次の章で紹介します。
XLOOKUPを使うVBAコード
XLOOKUPのコードは、こちらです。
result =
WorksheetFunction.XLookup(Range(“A1”).Value, Range(“B:C”), Range(“C:C”))
A1セルの値を“B:C”列から検索し、対応するC列の値を返します。
このコードで、A1セルに入力した値に一致する結果がresultという変数に格納されます。
SUMIFを使うVBAコード
SUMIFのコードは、こちらです。
範囲”B:C”の中で、A1セルの値に一致するセルを合計します。
result =
WorksheetFunction.SumIf(Range(“B:C”), Range(“A1”).Value, Range(“C:C”))
このコードは、条件に合致するセルを合計します。詳細な使い方は次の章で紹介します。
COUNTIFを使うVBAコード
COUNTIFのコードは、こちらです。
“B”行の中で、A1セルの値に一致するセルの数をカウントします。
result =
WorksheetFunction.CountIf(Range(“B:B”), Range(“A1”).Value)
これによりresultに条件に合致するセルの数が格納されます。
さらに詳しい解説は次の章で行います。
【解説】VBAでエクセル関数を使う方法
ここでは、VBAでエクセル関数を使うための基本的な方法について解説します。
具体的には、`WorksheetFunction`の使い方、データ範囲の指定方法、戻り値の扱い方、そしてエラー処理の方法について解説します。
1. WorksheetFunctionの使い方
VBAからエクセル関数を呼び出すには、`WorksheetFunction`を使用します。
これを使うことで、VBA内でエクセルのワークシート関数を活用できます。
result =
WorksheetFunction.エクセル関数()
このように、`WorksheetFunction`の後に、VLOOKUPやXLOOKUPなどのエクセル関数を書いて使います。
`WorksheetFunction`を使うことで、VBAでエクセル関数を使用することができます。
ここから詳しくデータ範囲の指定方法と、結果の扱いについて解説しますが、知っておいてほしいことは次の2点です。
①データ範囲はRANGEで指定する
②結果は変数に格納される。
この二つをおさえておけば、おおむねOKです。
2. データ範囲の指定方法
エクセル関数をVBAで使用する際に重要なのが、データ範囲の指定方法です。VBAでは、`Range`を使って範囲を指定します。ここではSumifを例に、解説をしていきます。
まずSUMIF関数は、第一引数に範囲・第二引数に検索条件、第三引数に合計範囲を指定して使用します。注意が必要なのは、VBAでは引数の説明が自動的に表示されないので、確認が必要となります。

これを踏まえて、範囲・検索条件・合計範囲をRangeで指定していきます。
result =
WorksheetFunction.SumIf(Range(“B:C”), Range(“A1”).Value, Range(“C:C”))
Range(“B:C”)のように範囲を指定します。
Rangeの使い方については、次の記事でも詳しく解説しています。
今回は列で範囲を指定しますが、一番下を自動で設定することも可能です。
例えば次のようにコードで範囲を柔軟に変えることができます。
rng =Range(“B1”).End(xlDown).Row
result =
WorksheetFunction.SumIf(Range(“B1:C”&rng), Range(“A1”).Value, Range(“C1:C”&rng))
このコードは、B1から一番下の行数を計算して、範囲を設定するVBAになっています。
一番下の行数・一番右の列数の取得については、次の記事も参考にしてみてください。
3. 関数の結果(戻り値)の扱い
関数の結果(戻り値)は、変数に代入して利用します。
例えば、VLOOKUPやSUMIFの結果は`Variant`型の変数に格納します。
Dim result As Variant
result = WorksheetFunction.VLookup(Range(“A1”).Value, Range(“B1:D1000”), 2, False)
range(“E1”)=result
上のコードでは、Vlookupで取得できる値を一度resultに格納しています。そして、その結果をE1に表示しています。
個人的に変数はVariantで宣言していればとりあえずは問題ないと考えています。変数についての詳細は、別の記事で詳しく説明しています。
4. エラー処理の方法
VBAでエクセル関数を使用する際に、最も注意すべきなのがこのエラーです。
関数であれば、「#NAME?」などのコードが表示されて終わります。ただVBAでは、何も対処をしていないと、処理が止まってしまいます。vlookupで見つからない場合でも止まってしまうので、対処は必須です。
こういったエラーを防ぐために、`On Error`文を使ったエラー処理を導入することが重要となっています。
On Error Resume Next
result = WorksheetFunction.VLookup(Range(“A1”).Value, Range(“B1:D1000”), 2, False)
If IsError(result) Then
msgbox “Vlookupでエラーが発生しています。”
Else
range(“E1”)=result
End If
On Error GoTo 0
`On Error Resume Next`を使ってエラーが発生してもプログラムが止まらないようにし、`IsError`関数でエラーを検出して処理を分岐させます。このようにすることで、予期せぬエラーが発生しても処理が進むようになります。
以上のポイントを押さえることで、VBAでエクセル関数を効果的に使えるようになります。次章では、これらをさらに効率化する方法を紹介します。
【発展】VBAでエクセル関数を効率的に使う
ここでは、VBAでエクセル関数をさらに効率的に活用するためのテクニックを紹介します。ループ処理との組み合わせや変数の活用、複数シートやファイルにまたがる関数の使用について紹介します。
1. ループ処理との組み合わせで効率化
VBAのループ処理とエクセル関数を組み合わせることで、複数のセルに対して効率よく関数を適用することができます。例えば、データ範囲内の複数のセルに対してVLOOKUPを実行し、その結果を別の列に記録する処理をループで実行することが可能です。
Dim i As Variant
For i = 1 To 10
Cells(i, 3).Value = WorksheetFunction.VLookup(Cells(i, 1).Value, Range(“B:D”), 2, False)
Next i
このように、範囲内のセルごとに関数を適用し、結果をC列に書き込むことで、自動的に処理できます。
ループを使えば、手作業で関数を適用する手間を大幅に削減できます。
2. 変数を使った柔軟な関数適用
VBAの変数を使うことで、柔軟にエクセル関数を適用することができます。例えば、検索する列や参照する範囲を変数で管理することで、コードが読みやすくなります。
Dim searchValue As String
Dim searchRange As Range
Dim result As Variant
searchValue = Range(“A1”).Value
Set searchRange = Range(“B1:D1000”)
result = WorksheetFunction.VLookup(searchValue, searchRange, 2, False)
range(“E1”)=result
このように、検索値や検索範囲を変数で管理することで、読みやすさが上がります。これによりメンテナンスが容易となり、別の変数との組み合わせもスムーズになります。
3. 複数シート・ファイルを跨いだ関数使用
VBAを使えば、複数のシートやファイルをまたいで関数を適用することもできます。
例えば、SUMIF関数で調べたい範囲と検索したい文字、出力したい場所が別のシートの場合、次のようなコードになります。
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim result As Double
Set ws1 = ThisWorkbook.Sheets(“Sheet1”)
Set ws2 = ThisWorkbook.Sheets(“Sheet2”)
result = WorksheetFunction.SumIf(ws1.Range(“A1:A1000”), ws2.Range(“B1”).Value, ws1.Range(“B1:B1000”))
we2.range(“C1”)=result
このコードでは、Sheet1のデータにSumifを行い、結果をSheet2に出力しています。
2つのシート間の処理ではそれほど効果はありませんが、調べたいシートが3つ以上ある場合などは、シートの取得を自動化することで、一気に処理を行えるようになります。
さいごに
今回は、VLOOKUPやXLOOKUP、SUMIFなど、よく使われる関数例にVBAでエクセル関数を使用する方法を紹介しました。特に、ループ処理や変数を活用した柔軟な関数適用、複数シートにまたがる操作など、VBAならではの効率化手法は、VBAならではの非常に役に立つ機能となります。
このブログでは、ほかにもChatGPTを活用したVBAの書き方など、様々な効率化コンテンツを紹介しています。
ぜひともそういった記事も参考にしながら、効率化でスキルアップを行っていってください!
コメント