##前書き
Excel2020くらいからの新機能のスピルですが、まだまだ知名度は低いです。
しかしながらユーザー定義関数と組み合わせることで今までと少し違ったユニークな機能を実現できそうです。
二つの具体例で説明を進めます。
##例① ～Summaryの出力～
エクセル上の特定のデータを処理して複数の値をまとめて出力する。
同時にラベリングも可能。
Public Function 統計値出力(ByRef argRange As Range) As Variant
Dim i As Long, j As Long
Dim average As Double, sigma As Double, max As Double, min As Double
average = Application.WorksheetFunction.average(argRange)
sigma = Application.WorksheetFunction.StDev(argRange)
max = Application.WorksheetFunction.max(argRange)
min = Application.WorksheetFunction.min(argRange)
Dim label As Variant, value As Variant
label = Array("平均", "標準偏差", "最大値", "最小値")
value = Array(average, sigma, max, min)
Dim myReturn As Variant
ReDim myReturn(0 To 1, 0 To UBound(value))
For i = 0 To UBound(label)
myReturn(0, i) = label(i)
myReturn(1, i) = value(i)
Next i
統計値出力 = myReturn
End Function
スピルとは新しくエクセルに導入されたいくつかの関数が持っている機能です。
スクリーンショットのrandarray()のようにスピルによって結果を複数セルに展開することが可能になっています。
これはユーザー定義関数でも同様です。（ユーザー定義関数とはプログラミングによって関数を作れる機能です。ここでは詳しくは説明しません。）
新しく作成した「統計値出力」関数で簡単にサマリを作成できます。
##例② ～データを間引く～
データを間引く関数を作成します。
引数を変えることで３行スキップの４行ごとのデータ出力、
５０行スキップの５１行ごとの出力を得ることが出来ます。
Public Function データを間引く関数( _
ByRef argRange As Range, _
Optional ByVal skipRow As Long = 0, _
Optional ByVal skipColumn As Long = 0 _
) As Variant
Dim i As Long, j As Long
Dim argRow As Long, argColumn As Long
argRow = argRange.Rows.Count
argColumn = argRange.Columns.Count
'アルゴリズムの都合上 newRow := 新しい行数から1引いた数になっていたり少しわかりずらい。
Dim newRow As Long, newColumn As Long
newRow = (argRow - 1) \ (skipRow + 1)
newColumn = (argColumn - 1) \ (skipColumn + 1)
Dim myReturn As Variant
ReDim myReturn(0 To newRow, 0 To newColumn)
'ここもアルゴリズム優先
'行数・列数の時は1 to RowNumberにしたほうが5行3列目のデータがmyReturn(5,3)に入りわかりやすい。
'関数経由でエクセルから得たargRangeも(1 to 列数,1 to 行数）となっており統一感がでる
Dim row As Long, column As Long
For row = 0 To newRow
For column = 0 To newColumn
myReturn(row, column) = argRange(row * (skipRow + 1) + 1, column * (skipColumn + 1) + 1)
Next column
Next row
データを間引く関数 = myReturn
End Function
###おわりに
いかがだったでしょうか？（一度言ってみたかった）
スピル×ユーザー定義関数のメリットとしては以下の2点を挙げることができると思います。
- リッチな出力を簡易に実現できる。
- 引数による動作の制御可能
複数人で使いまわすことがなかなか難く一人分の削減できる時間＞作成コストの見込みが強いため、自分は実際に活用するところまではいけていません。
しかしスピルと組み合わせた機能はユニークであり、レガシーが多く嫌われがちなExcelの長所を伸ばせる可能性を感じます。
引数を受け取り、処理をし、出力するといったフォーマットは今回の例で提示できていると思うので、何か面白い使い道を思いついたら作って見て下さい。