Zenn
🦚

Excel VBA_代表的なワークシート関数の使い方

2022/12/31に公開
VBA
ワークシート関数
tech

この記事では代表的なワークシート関数の操作方法についてまとめます。

  • 関数とは何か？
  • VBA関数、ワークシート関数
  • 代表的なワークシート関数

関数とは何か？

データを投入すると、何らかの処理をして、結果を返却する仕組みです。 関数とはその名の通り「数の関わり合い」を意味しますが、具体的には「数における原因と結果の関わり合い」を意味しています。分かりやすいのがf(x) = x^{2}の関数です。原因として数が投入された際に、新しい結果の数を返却することができますよね。

VBAにおける関数とは、特定の処理をするために用意された便利な命令集と考えることもできます。関数を呼び出せば、文字列や日付、時刻などの操作をカンタンに扱うことができます。部品として処理を1まとめにすることで、より見やすく効率的にコードを書くことが可能となります。

VBA関数、ワークシート関数

VBAであらかじめ用意されている関数をVBA関数と呼びます。 VBA関数はプロシージャ内に関数名を記述してそのまま使うことができます。

一方でワークシート上で使う関数をワークシート関数といいます。　ワークシート関数で代表的なモノにはSUM関数、IF関数などがあります。これらはVBAのプロシージャ内でそのまま使うことはできないため特別な書き方をしたり、そもそもVBAでは使用できないモノもあります。

ワークシート関数の使い方

ワークシート関数をVBAで利用するにはWorksheetFunctionオブジェクトの後にワークシート関数名を記述します。 WorksheetFunctionオブジェクトはワークシート関数の親オブジェクトであり、WorksheetFunctionプロパティを使って取得します。

Application.WorksheetFunction.ワークシート関数名

Application.は省略できますので下記のように書いた方が便利です。

WorksheetFunction.ワークシート関数名

ワークシート関数の代表的なSUM関数は次のようにVBA内で使うことができます。ここではセルA1~A5の値の合計を求めています。

Dim 合計値 As Long
合計値 = WorksheetFunction.Sum(Range("A1:A5"))

使用できないワークシート関数

LEFT関数やIF関数など一部のワークシート関数はVBAで使うことができません。代わりにVBA関数のleft関数やIf~Thenステートメントを使います。VBAで使用できるワークシート関数はWorksheetFunction.と入力してから表示される自動メンバー表示の一覧で確認できます。

もし表示できない場合はCtrl + Spaceで再表示をしてみて下さい。

どちらを使えば良いのか？

ワークシート関数を積極的に利用して、必要な部分はマクロでコードを書くというのがあるべき姿だと個人的には考えています。VBA関数よりはるかに多くの関数がワークシート関数には用意されています。わざわざ機能が用意されているならそれを活用しない手はありませんし、ワークシート関数を使えばVBAコードを非常に簡潔に記述できるケースは非常に多いです。

例えばセルA1~A5の値を合計するプログラムを比較してみます。

マクロで記述した場合
Dim i As Long
Dim 合計値 As Long
For i = 1 To 5
    合計値 = 合計値 + Range("A" & i).Value
Next
MsgBox 合計値
ワークシート関数を使った場合
Dim 合計値 As Long
合計値 = WorksheetFunction.Sum(Range("A1:A5"))
MsgBox 合計値

Excelを有効活用できる人がVBAでマクロを組むと非常にさっぱりとコードを書けます。関数なら１行で済む事を自力でダラダラと何十行も書くようなことがないよう、ワークシート関数についても理解を深めておきましょう。

代表的なワークシート関数

ここからは代表的なワークシート関数をご紹介していきます。

SUM, AVERAGE関数

セルの合計値/平均値を求めることができます。

合計値を求める
Dim 合計値 As Long
合計値 = WorksheetFunction.Sum(Range("A1:A5"))
平均値を求める
Dim 平均値 As Long
平均値 = WorksheetFunction.Average(Range("A1:A5"))

MAX, MIN関数

セルの最大値/最小値を求めることができます。

最大値を求める
Dim 最大値 As Long
最大値 = WorksheetFunction.Max(Range("A1:A5"))
最小値を求める
Dim 最小値 As Long
最小値 = WorksheetFunction.Min(Range("A1:A5"))

VLOOKUP関数

VBAでも威力を発揮するのがVLOOKUP関数です。下記の表から商品に対応する金額を検索します。

例えばここでは商品Fに対応する金額35000を検索します。まずはVBAプログラミングによるFor文とIf文を組み合わせた方法からです。

Dim i As Long
Dim 金額 As Long
For i = 3 To 12
    If Range("B" & i) = "商品F" Then
        金額 = Range("C" & i).Value
        MsgBox 金額
    End If
Next i

同じことをVLOOKUP関数で行うと次のようになります。

Dim 金額 As Long
金額 = WorksheetFunction.VLookup("商品F", Range("B3:C12"), 2, False)
MsgBox 金額

こちらの方がずっとカンタンにできますよね。

COUNT, COUNTIF関数

データが存在するかどうか？を調べる際に力を発揮するのがCOUNTIF関数です。条件に当てはまるセルの数をカウントする関数ですが、文字列を検索するにも有効に使うことができます。

If WorksheetFunction.CountIf(Range("B3:B12"), "商品F") > 0 Then
    MsgBox "商品Fが見つかりました"
Else
    MsgBox "商品Fは存在しません"
End If

Discussion