ユーザー定義Functionにスピル機能を持たせる

365なら2019年から、買い切り版なら2021年から、
Excelのシート数式に「スピル」機能が追加されました。

UNIQUE関数やFILTER関数など、
このスピル機能を利用した便利な関数がたくさん実装されています。

さてこのスピル関数ですが、
Functionで作成するユーザー定義関数にも実装することが可能です。

これを活用して便利な関数を自作することもできますので、
スピル好きの方は是非一度試してみてください。

ユーザー定義関数をスピルさせる

ユーザー定義関数をスピルさせるには、
配列(Array)を返り値として返すだけでOKです。

たとえば簡単にこんな関数を作ってシート上に書いてみましょう。

Function スピルテスト() As Variant
    スピルテスト = Array(1, 2, 3)
End Function

しっかりスピルしてくれていますね。

ユーザー定義関数の返り値に一次元配列を渡した場合は、
横方向にスピルする関数として動きます。

たとえばこんな関数を作って使用することができます。

Function DATESPLIT(日付 As Date) As Variant
    
    Dim 配列()
    ReDim 配列(1 To 3)

    配列(1) = Year(日付)
    配列(2) = Month(日付)
    配列(3) = Day(日付)

    DATESPLIT = 配列

End Function

簡単な関数ですが、この作業を結構やる方にとっては、
これでもなかなか便利ではないでしょうか。

スピルするFunctionを作成する際のポイントとしては、
まず配列の要素番号(添字)は0/1はじまりどちらでも問題ありません。

セル範囲.Valueで取得する配列とあわせておくと何かと都合がいいので、
基本は1はじまりとして定義しておくと良いと思います。

もうひとつポイントとして、
返り値はただのVarinatでも問題なく動かすことができます。

こうしておくことで、最後に返り値を代入する、

DATESPLIT = 配列

このコードで余計なエラーになる可能性を減らせます。

逆に返り値を「As Varinat()」と配列であることを明示してしまうと、
String()であるSplit関数の返り値を代入できなくなる等の制約が出ます。

配列ではなく単独の値を返したい場合が発生しても、
Variant型にしておけば数値や文字列を返すことができます。

たとえ配列を返す関数であったとしても、
型と配列の明示はしなくてもいいことは覚えておきましょう。

ユーザー定義関数を縦方向にスピルさせる

一次元配列を返り値に用いた際は横方向にスピルしますが、
返り値をn行1列の二次元配列にすれば縦方向にスピルします。

Function DATESPLIT(日付 As Date) As Variant
    
    Dim 配列()
    ReDim 配列(1 To 3, 1 To 1)

    配列(1, 1) = Year(日付)
    配列(2, 1) = Month(日付)
    配列(3, 1) = Day(日付)

    DATESPLIT = 配列

End Function

これでUNIQUE関数やFILTER関数などのように、
返り値をデータとして使うような関数が作れますね。

ちなみにこの仕様の関数を作るときは、
Dictionaryを使った処理と相性がいいことが多いです。

受け取ったセル範囲をDictionaryで処理し、
Itemsで取得できる配列を返り値に渡すイメージですね。

しかしDictionaryのItemsは一次元配列であるため、
そのまま使用すると横方向にスピルしてしまいます。

Dictionaryをはじめとした「一次元配列を返す機能」を縦にスピルさせたい場合は、
WorksheetFunction.Transposeを使って配列を転置してください。

縦にスピルさせたいユーザー定義関数 = WorksheetFunction.Transpose(Dic対象データ.Items)

セル範囲と配列をどちらも受け取る引数にする

FILTER関数の結果をUNIQUE関数に渡して計算するとき、
UNIQUE関数は「二次元配列」を受け取って動いています。

このようにシート関数はスピル関数の結果を渡しても動くよう、
セル範囲・配列どちらを渡しても同じ処理ができるようになっています。

これをユーザー定義関数で実装するには、引数をVariantにして、
渡された方に応じて処理を分岐させる必要があります。

Function スピル対応ユーザー定義関数(セル範囲または配列 As Variant) As Variant
    
    Dim Arr引数配列 As Variant
    
    ' 引数がRangeの場合
    If TypeName(対象セル) = "Range" Then
        
        ' 単独セルが渡っている場合の例外処理
        If 対象セル.Count = 1 Then
            スピル対応ユーザー定義関数 = "○○"
            Exit Function
        
        ' セル範囲なら.Valueで二次元配列へ
        Else
            Arr引数配列 = セル範囲または配列.Value
        End If
    
    ' 引数が配列の場合はそのまま使用
    ElseIf IsArray(対象セル) Then
        Arr引数配列 = セル範囲または配列
    End If
    
    ' 配列でもセルでもない場合はExit
    If IsEmpty(Arr引数配列) Then Exit Function
    
    ' ここからArr引数配列に対する処理を書く
    
End Function

これでセル範囲を渡しても配列を動かしても動く関数にすることができます。

この手の「引数の型にパターンがある関数」を実装する場合は、
「Rangeの場合の処理/配列の場合の処理」
のように処理を分岐しようとすると大変なことになります。

こういった場合は上記サンプルコードのように、

• まずは各パターンの引数を統一したひとつの型へ変換するコードを書く
• メインの処理は統一したひとつの型(今回は二次元配列)に対して書く

という手順を踏むことで複雑化をある程度抑えることができます。

この手法も覚えておきましょう。

なかなか面倒なコードのため気軽に実装はできませんが、
どうしてもこの仕様にしたい関数があればご参考ください。