Excelでスピルを使う方法

職場のPCでしか、スピルが使用できないので、大変不便です。
因みにスピル可能なのは、バージョン1810(ビルド10920.20003クイック実行)です。
お家のは、バージョン1811なのに、なぜでしょうか？

ユーザ定義関数で配列を返す

以下のような関数を作って、セルに埋め込むと、スピルが有効だと２ｘ２のデータが流出します。
これを利用すると、かなり自由なことが出来ます。、

Function A_A()
   Dim A(1, 1)
   A(0, 0) = "A2A"
   A(0, 1) = 1
   A(1, 0) = "A1A"
   A(1, 1) = 9

   A_A = A
End Function

Trimを再定義します。

なぜか、WorksheetFunctionのTrimとVBAのTrimの仕様が異なるので、
望ましい方のWorksheetFunctionのTrimを再定義します。

Function TRIM_(T)
    TRIM_ = WorksheetFunction.Trim(T)
End Function

TITLE を定義します。

テーブルの列位置を統一的に扱う為に、TITLEを定義します。
TITLEは、配列の０行目に配置され、流出すると表題として表示されるようにします。
TITLEを複数扱う場合は、コンマ区切りで連結して、下記のような文字列となります。

　"年,機,番号,W,O,日付,出庫,済,発注,依頼,製品名,在庫"

LABEL を定義します。

複数テーブルの行位置を統一的に扱う為に、LABELを定義します。
LABELは行位置の数字と、テーブルを表す英字一文字の組み合わせたものです。
例えばテーブルM01N_の１行目は、文字列 "1a"　となります。

関数TDIは、LABEL(L)とTITLE(T)でテーブルからデータを持ってきます。
関数LIJは、１つのLABELを数字部分(I)と英字部分の(J)に分けます。

Function TDI(L, T, Optional S = "")
    On Error Resume Next

    Dim I, J: Call LIJ(L, I, J)

    If I = "A" Then S = DI(Range(CC("T01N_[", T, "]")), J)
    If I = "a" Then S = DI(Range(CC("M01N_[", T, "]")), J)
    If I = "B" Then S = DI(Range(CC("T02N_[", T, "]")), J)
    If I = "b" Then S = DI(Range(CC("M02N_[", T, "]")), J)

    TDI = S
End Function

Sub LIJ(L, I, J)
    I = Right(L, 1)
    J = Val(L)
End Sub

LABELを複数扱うために、空白区切りで連結させて一つの文字列とします。
例えば、テーブルT02N_の１行目と２行目は、"1b 2b"となります。

　テーブルから条件にマッチするLABELを抽出します。

使用法は、適当なセルに、
　=ML(M02N_[O],"7000","b")
件数を制限したい場合は、返り値が文字列なので、
　=TAKE(16,ML(M02N_[O],"7000","b"))

Function ML(R, V, Optional A = "", Optional D = " ", Optional S = "")   ' MATCH LIST
    ML = S
    Dim J: J = MI(R, V)
    If J = "" Then Exit Function

    Dim I: For I = J To R.Rows.Count
        If DI(R, I) = V Then S = CC(S, I, A, D)
    Next I

    ML = S
End Function

Function TAKE(I, L, Optional D = " ", Optional S = "")
    Dim LA, H: LA = Split(TRIM_(L), D): H = UBound(LA)
    I = I - 1
    If H > I Then H = I
    Dim J: For J = 0 To H      ' REDIM S(H)して、内容を移してJoinのほうが良かった。
        S = CC(S, LA(J), D)
    Next J
    TAKE = S
End Function

LABEL,TITLEを使ってデータを加工し流出させます。

Function A_TDI(L, T, Optional S = "")
    Dim LA, H: LA = Split(TRIM_(L)):      H = UBound(LA)
    Dim TA, W: TA = Split(TRIM_(T), ","): W = UBound(TA)
    Dim I, J, LI, TJ

    ReDim S(H + 1, W)

    For J = 0 To W
        S(0, J) = TA(J)
    Next J

    For I = 1 To H + 1
        LI = LA(I - 1)
        For J = 0 To W
            TJ = TA(J)

            Select Case TJ
                Case "LOT":           S(I, J) = TLOTI(LI)
                Case "依頼", "発注":   S(I, J) = IFZS(TDI(LI, TJ))
                Case Else:            S(I, J) = TDI(LI, TJ)
           End Select
        Next J
    Next I

    A_TDI = S
End Function

テーブルからTITLEを使ってデータを加工し流出させます。

テーブルは、R.Rows.Count で簡単に行数が判ります。
それに列管理のTITLEを組み合わせて、データ加工のプログラムを作成します。

Function A_KJ(R, T, Optional S = "")    ' =A_KJ(T02N_[O],"KJ,前,次")
    Dim RC: RC = R.Rows.Count
    Dim TA: TA = Split(TRIM_(T), ",")
    Dim TC: TC = UBound(TA)
    Dim I, J

    ReDim S(RC, TC)   ' AS String とは絶対にしない。

    For J = 0 To TC
        S(0, J) = TA(J)
    Next J
    For I = 1 To RC
        For J = 0 To TC
            S(I, J) = ""
        Next J
    Next I

    Dim T_KJ: T_KJ = TI(TA, "KJ")
    Dim T_前: T_前 = TI(TA, "前")
    Dim T_次: T_次 = TI(TA, "次")
    Dim T_MD: T_MD = TI(TA, "MD")
    Dim T_KZT: T_KZT = TI(TA, "KZT")
    Dim T_材料: T_材料 = TI(TA, "材料")
    Dim T_前回: T_前回 = TI(TA, "前回")
    Dim T_製品名: T_製品名 = TI(TA, "製品名")

    Call A_MDI(S, R, "KJ_[O]", T_KJ)
    Call A_MD(S, T_前, T_KJ, T_次)
    Call A_DI(S, "KJ_[製品名]", T_製品名, T_KJ)
    Call A_DI(S, "KJ_[材料]", T_材料, T_KJ)
    Call A_MD(S, T_MD, T_材料)
    Call A_MI(S, "KZT_[SNO]", T_KZT, T_材料)

    A_KJ = S
End Function

Function TI(R, V, Optional S = "")  ' TABLE INDEX
    S = MI(R, V)               ' 配列にMatch が使えるなんて驚きだ！　　　　　　　
    If S <> "" Then S = S - 1  ' 配列にあわせるために-1

    TI = S
End Function

Sub A_MDI(S, R, RN, T)
    If T = "" Then Exit Sub

    Dim R1: Set R1 = Range(RN)
    Dim I: For I = 1 To UBound(S, 1)
        S(I, T) = MI(R1, DI(R, I))
    Next I
End Sub

Sub A_DI(S, RN, T1, T2)   ' CALL A_DI(S,"KJ_[材料]",T_材料,T_KJ)

    If T1 = "" Or T2 = "" Then Exit Sub

    Dim R: Set R = Range(RN)

    Dim I: For I = 1 To UBound(S, 1)
        S(I, T1) = DI(R, S(I, T2))
    Next I
End Sub

Sub A_MI(S, RN, T1, T2)
    If T1 = "" Or T2 = "" Then Exit Sub

    Dim R: Set R = Range(RN)

    Dim I: For I = 1 To UBound(S, 1)
        S(I, T1) = MI(R, S(I, T2))
    Next I
End Sub

Sub A_MD(S, T1, T2, Optional T3 = "")
    If (T1 = "") Or (T2 = "") Then Exit Sub
    Dim I, J
    Dim M: M = UBound(S, 1)
    For I = 1 To M
        For J = I + 1 To M
            If S(I, T2) = S(J, T2) Then
                S(I, T1) = J
                If T3 <> "" Then S(J, T3) = I
                Exit For
            End If
        Next J
    Next I
End Sub

下記の関数をセルに埋め込むとデータが表示されます。
=A_KJ(T02N_[O],"KJ,前,次,製品名,材料,MD")

こぼしたミルクをテーブルに反映させます。

上記で、テーブルに対して同じ行数の動的配列を作成しました。
しかし、テーブル内でデータを使用する場合は、書き写す必要があります。
例えば、テーブル列KJに動的配列KJ(列AE:AE)を写す場合は、下記のようにします。

　　(テーブル列K)　=SINGLE(AE:AE)