セルからマクロ関数を呼び出す

オートフィルターを使って、表示されているデータに対して合計、平均、個数、最大、最小といった集計を行いたい場合は、SUBTOTAL関数の独擅場です。え? SUMじゃダメなの?という人に簡単に説明すると、SUMは非表示のデータも合計しますが、SUBTOTALは非表示のデータは対象外です。しかも、合計だけじゃなくて平均、最大、最小といった集計もできて、さらに標準偏差まであるんです。めちゃくちゃ便利ですね。よく使うのは1,3、9でしょうか。

=SUBTOTAL( 集計方法、範囲)
集計方法:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM


COUNTIFやSUMIFもよく使います。
=COUNTIF(範囲、条件)
=SUMIF(範囲、条件)

例えば、=COUNTIF(Sheet1!C:C, "=2")とすれば、Sheet1のC列でセルの値が2の個数をカウントしてくれます。ここまではいいですね。

SUBTOTAL, COUNTIF, SUMIFの限界
COUNTIFは便利ですが、太字になっているセルの個数をカウントすることはできません。文字色がオレンジのセルの個数をカウントもできません。つまり条件に指定できる項目が限定されているということです。複数のセルも条件に指定できません。例えば、セルAの内容が"ネコ"で、セルBの内容が"メス"、セルCの内容が5歳未満かどうかなんていう条件は指定不可能なわけです。*1

では、どうしたらいいでしょうか。マクロを組みますか? そのマクロはどうやって呼び出しますか? ボタンクリックですか? 実現可能ですが煩雑ですよね。

セルからマクロ関数を呼び出す
エクセルの最大の特徴は、なんと言ってもセル関数です。マクロではありません。でも、自分が書いたマクロ関数をセルから呼び出せたらすごくないですか? はい、呼び出せるんです!  この機能を実装したプログラマは本当に天才ですね。

ただし、ちょっとだけ注意事項があって、マクロ側のパラメータはParamArrayという宣言をして、Variant型にしなければならないという条件があります。下の例はセル範囲で太字になっているセルの個数を返す簡単な例です。

セルの記述は =CountBold( A1:A10 ) という感じです。

Public Function CountBold(ParamArray pRange() As Variant)

    Dim iCount As Long
    Dim vParam As Variant
    Dim xCell As Excel.Range

    For Each vParam In pRange
        For Each xCell In vParam
            If xCell.Font.Bold Then
                iCount = iCount + 1
            End If
        Next
        Exit For
    Next
    CountBold = iCount
End Function

 

指定した範囲のセルに特定の文字列が含まれるセルの個数を数えるには
=COUNTIF( A1:A50, "=*Death Stranding*")とすればいいのですが、これを敢えてマクロ関数で記述するにはどうしたらいいでしょうか。つまり、2個以上のパラメータを渡したい場合の記述方法です。

Public Function CountKeyword(ParamArray pParameter() As Variant)
    Dim iCount As Long
    Dim sKey As String
    Dim xRange As Excel.Range
    Dim xCell As Excel.Range

    Set xRange = pParameter(0)
    sKey = pParameter(1)
    For Each xCell In xRange
        If xCell.Height > 0 Then
            If xCell.Text Like "*" & sKey & "*" Then
                iCount = iCount + 1
            End If
        End If
    Next
    CountKeyword = iCount
End Function


呼び出し側は、=CountKeyword( A1:A50, "Death Stranding")

どうでしょう。様々なケースで利用できそうですよね。

 

追記:VBAにはDateAddという便利な関数がありますが、セル関数にはこれに該当するものがありません。DateAdd使えたらいいのにと思いませんか? これも自分で関数を作ってしまえばいいんです。ただし、VBAの中でPublic Function DateAdd(... という名前の関数は問題があるので、DateAddVBAという名前にしてあります。

使用例: =DateAddVBA("m", 1, A15) 

Public Function DateAddVBA(ParamArray pParameter() As Variant)

    Dim sInterval As String
    Dim iNumber As Long
    Dim xDate As Excel.Range 

    sInterval = pParameter(0)
    iNumber = pParameter(1)
    Set xDate = pParameter(2)

    DateAddVBA = DateAdd(sInterval, iNumber, Format(xDate, "mm/dd"))

End Function

 

*1:これはCOUNTIFSで可能だということをあとで知りました。お恥ずかしい限りです。こういった単純なケースはCOUNTIFSで可能ですが、セルCのデータ項目が別シートのD列に存在するかどうかなんてのはCOUNTIFS単独では不可能です。別セルにMATCHの結果を残しておいて、ISNAでTRUEかFALSEにキャストしておいてやっとCOUNTIFSで条件指定可能になります。どっちにしろ、COUNTIFSも万能ではないという話です。