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

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

アイキャッチ画像

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

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

リンク

「標準偏差」

「標準偏差」は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

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

マクロ実行後のシート

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

招待コード ポイ活アプリ

招待コード・キャンペーン

おすすめゲームアプリ

ポイントの貯め方

PayPay

楽天ポイント

dポイント

Pontaポイント

ポイ活方法

おすすめポイ活アプリ紹介

おすすめポイ活アプリ紹介

おすすめプロテイン

プロテインの味

安く買う方法

新着＆プロフィール

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

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

「標準偏差」

「偏差値」

招待コードポイ活アプリ