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

スポンサーリンク

f:id:tainers:20200308093446j:plain
この記事では、VBAのコードでワークシート関数の利用方法を単純集計の例をだしながら解説させていただきます。

 

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

 

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

WorkSheetFunction.関数(引数)

 

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

f:id:tainers:20200307235225j:plain

 

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

「人数」は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(検索値,検索範囲,戻り範囲)で利用ができます。詳しい関数の利用方法は下記をご覧ください。

「標準偏差」

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

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

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

標準偏差=\sqrt{\frac{1}{N}\sum_{i=1}^N(x_i - \bar{x})^2}

 

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

 

「偏差値」

「偏差値」については、「平均」「標準偏差」を利用して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

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

  \displaystyle 偏差値 = \frac{得点-平均点}{標準偏差} × 10 + 50

 これを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

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

f:id:tainers:20200308015217j:plain

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

 

これにて終了します。ブログを読んでいただきありがとうございます。