【Excel（エクセル）/マクロ/VBA】WorkSheetFunctionによるワークシート関数の利用方法を図解

この記事では、VBAのコードでワークシート関数の利用方法を単純集計の例を出しながら解説しています。

まず初めに、関数について少し補足しておきますと、Excelにはシートで利用できる関数であるワークシート関数とマクロで利用する関数がございます。今回は、VBAでワークシート関数を利用する方法について解説させていただきます。

VBAをこれから使い始めるという方はまず、以下の記事をご覧ください。

VBAの環境構築の方法をまとめました

この記事では、Excelマクロ・VBAを使い始めるための環境構築の方法をまとめています。これからVBAを活用したい方はぜひ参考にしてください。

記事を読む

それでは本題に入っていきます。VBAでワークシート関数を利用するときは、下記のように「WorkSheetFunction」を利用します。

WorkSheetFunction.関数（引数）

では利用方法に入っていきます。下記のような表があるとします。本来ならば、合計のところはSUM関数、平均のところはAVERAGE関数を利用しますね。しかし、今回はマクロを利用して集計します。

「人数」「合計」「平均」

「人数」はCOUNT関数、「合計」はSUM関数、「平均」はAVERAGE関数を利用して求めるので、下記のようなコードとなります。

    '人数'
    Range("G3") = WorksheetFunction.Count(Range("C3:C12"))

    '合計'
    Range("G4") = WorksheetFunction.Sum(Range("C3:C12"))
    
    '平均'
    Range("G5") = Round(WorksheetFunction.Average(Range("C3:C12")), 1)

平均ではRound()を利用することで小数点第1位まで表示させています。

※Round()はマクロの関数なので、WorksheetFunctionは必要ありません。

「最高得点」「最高得点者」「最低得点」「最低得点者」

これらを求めるコードは下記の通りです。

    '最高得点’
    Range("G6") = WorksheetFunction.Max(Range("C3:C12"))
    
    '最高得点者'
    Range("G7") = WorksheetFunction.XLookup(Range("G6"), Range("C3:C12"), Range("B3:B12"))
    
    '最低得点'
    Range("G8") = WorksheetFunction.Min(Range("C3:C12"))
    
    '最低得点者'
    Range("G9") = WorksheetFunction.XLookup(Range("G8"), Range("C3:C12"), Range("B3:B12"))

「最高得点」はMAX関数、「最低得点」はMIN関数を利用します。

「最高得点者」と「最低得点者」に関しては、XLOOKUP関数を利用します。XLOOKUP関数は、2019年8月に発表された新しい関数です。言うなればVLOOKUP関数の上位版です。利用するには、Officeを最新版にバージョンアップする必要がございます。

XLOOKUP関数は、XLOOKUP(検索値,検索範囲,戻り範囲)で利用ができます。詳しい関数の利用方法は下記をご覧ください。

新たに実装されたXLOOKUP関数の基本的な使い方を図解

この記事では、VLOOKUPをさらに改良した関数であるXLOOKUPの使い方について画像を使いながら解説しています。

記事を読む

また、途中にはなりますが、以下ExcelのVBAを学ぶためにおすす目の書籍なので、紹介しておきます。

リンク

「標準偏差」

「標準偏差」はSTDEV関数を利用して下記のようなコードとなります。

    '標準偏差'
    Range("G10") = Round(WorksheetFunction.StDev(Range("C3:C12")), 1)

本来、標準偏差は下記の公式で求めることができます。

標準偏差=

計算が多くなって面倒ですが、関数を利用すれば一発で数値を出すことができます。

「偏差値」

「偏差値」については、「平均」「標準偏差」を利用してFor文で求めることができます。コードは下記の通りです。

    '偏差値’
    Dim i As Long
    For i = 1 To Range("G3")
        Cells(i + 2, 4) = Round(Cells(i + 2, 3) - Range("G5") / Range("G10") * 10 + 50, 1)
    Next

偏差値の公式は下記の通りです。

これをFor文を用いて求めていくという方法です。For文については次回以降解説させていただきます。

これで全ての値を求めることができるようになりました。全てのコードを合わせるてマクロのタイトルを「集計」とすると下記のようになります。

Sub 集計()

    '人数'
    Range("G3") = WorksheetFunction.Count(Range("C3:C12"))

    '合計値'
    Range("G4") = WorksheetFunction.Sum(Range("C3:C12"))
    
    '平均値'
    Range("G5") = Round(WorksheetFunction.Average(Range("C3:C12")), 1)
    
    '最高得点’
    Range("G6") = WorksheetFunction.Max(Range("C3:C12"))
    
    '最高得点者'
    Range("G7") = WorksheetFunction.XLookup(Range("G6"), Range("C3:C12"), Range("B3:B12"))
    
    '最低点数'
    Range("G8") = WorksheetFunction.Min(Range("C3:C12"))
    
    '最低得点者'
    Range("G9") = WorksheetFunction.XLookup(Range("G8"), Range("C3:C12"), Range("B3:B12"))
    
    '標準偏差'
    Range("G10") = Round(WorksheetFunction.StDev(Range("C3:C12")), 1)
    
    '偏差値’
    Dim i As Long
    For i = 1 To Range("G3")
        Cells(i + 2, 4) = Round(((Cells(i + 2, 3) - Range("G5")) / Range("G10")) * 10 + 50, 1)
    Next
End Sub

このコードを実行すると下記の画像のように全ての値を求めることができました。

このようにVBAでワークシート関数を利用することができます。ワークシート関数をVBAで利用できれば、プログラムの幅も広がり多くのマクロを作成できるようになります。ぜひ、活用してみましょう。