エクセル関数を使って効率化しているけれど、関数だけでは限界を感じる。
VBAでエクセル関数の処理をしたいけど、どう書いていいかわからない。
関数での効率化に限界を感じることはありませんか？関数を消してしまい使えなくなったり、関数入力に時間がかかったりしていませんか？またVBAがわかっても、エクセル関数をVBA使えない人も意外と多いのも事実です。
今回は、VBAとエクセル関数を組み合わせる方法を紹介します。関数は、簡単に早く実装できるため、是非とも活用してほしい内容です！
またVLOOKUP・SUMIF・COUNTIFは、コピペで使えるコードも紹介しています。ぜひ活用しながら学んでください！
またこのブログでは、コピペで使えるVBAも紹介しています。そちらもぜひ参考にしてみてください。
【コピペ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列の値を返します。これで、resultという変数にVLOOKUPの結果が格納されます。
なお「変数」については、別記事でも詳しく解説しています。自信がない方は、そちらもぜひ見てみてください。
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などの関数をそのまま書けばOKです。
ここから詳しく範囲の指定と、データ取得の方法ついて解説します。ここでのポイントは次の2点です。
- データ範囲はRANGEで指定する
- 結果は変数に格納される。
この二つをおさえておけば、おおむねOKです。
2. データ範囲の指定方法
エクセル関数をVBAで使用する際に重要なのが、データ範囲の指定方法です。VBAでは、`Range`を使って範囲を指定します。ここではSumifを例に、解説をしていきます。
注意が必要なのは、VBAでは引数の説明が自動的に表示されないことです。エクセル関数と同じ項目を設定しますが、どこに何を書くかは確認が必要です
ちなみにSUMIF関数は、以下を指定します。
- 第一引数：範囲
- 第二引数：検索条件
- 第三引数：合計範囲
これを踏まえて、範囲を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. ループ処理との組み合わせで効率化
ループ処理と関数を組み合わせで、効率よく関数を使用できます。例えば、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
ループを使えば、手作業で関数を適用する手間を大幅に削減できます。
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を使えば、複数のシートやファイルをまたいで関数を適用することもできます。例えば、次のようなコードになります。
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つ以上ある場合などは、一気に処理を行えるようになります。
さいごに
今回は、よく使われる関数を例にVBAでエクセル関数を使用する方法を紹介しました。VBAならではの効率化手法は、VBAならではの役に立つ機能となります。ループや変数などと、関数を組み合わせることで、ぐっと効率的になります。
このブログでは、ほかにもChatGPTを活用したVBAの書き方などの情報を紹介しています。そういった記事も参考にしながら、効率化でスキルアップを行っていってください！
コメント