セルからマクロ関数を呼び出す
オートフィルターを使って、表示されているデータに対して合計、平均、個数、最大、最小といった集計を行いたい場合は、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も万能ではないという話です。