Excel のスピルをRangeで編集

やっと、お家のＰＣでスピルが使えるようになりました。
現在のバージョンは、1811(ビルド11015.20015)　です。

スピルでテーブルを作成します。

Excelでスピルを使う方法の関数A_TDIで、下記のテーブルが表示することが出来ます。

(V1)  =TAKE(10,MPL(M01N_[[W]:[O]],S01M_,"a"))      [5a 17a 73a 85a]
(V14) =A_TDI(V1,"LOT,日付,出庫,在庫,済,発注,依頼")

テーブルの範囲はRangeで指定できます。

上記でスピルした配列は　V14#　という名前が自動的につきます。
　# 記号は、スピル範囲演算子と呼ばれます。
V14#の型はRangeとなります。V14#を日付でSORTします。
しかし単純にSORTするとTITLE部分も並び替えられて、期待した結果になりません。

(V26) =SORT(V14#,2)

関数でテーブルのTITLE部分をCUTします。

なんと、Offset,Resizeを使うと一行になります。

(V37) =TC(V14#)

Function TC(R)                         ' Table Title Cut
    TC = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count)
End Function

'Function TC(R, Optional s = "")         ' Table Title Cut
'    Dim M: M = R.Rows.Count
'    Dim N: N = R.Columns.Count
'
'    ReDim s(2 To M, 1 To N)
'
'    Dim I: For I = 2 To M
'        Dim J: For J = 1 To N
'            s(I, J) = DI(R, I, J)
'        Next J
'    Next I
'    TC = s
'End Function

期待した結果にします。

TITLEを消した配列をSORTし、元のテーブルに関数TRWで書き直します。

=TRW(V14#,SORT(TC(V14#),2))

Function TRW(R, A, Optional S = "")     ' TABLE reWRITE
    Dim M: M = R.Rows.Count
    Dim N: N = R.Columns.Count

    S = R

    Dim I: For I = 2 To M
        Dim J: For J = 1 To N
            S(I, J) = A(I - 1, J)
        Next J
    Next I
    TRW = S
End Function

細かいことですが、S = R のSはVariant()ですが、Set S = R だと、SはRangeのままです。

テーブルのLOT列から重複を除きます。

配列 V14#にそのままUNIQUIEを適用しても望む結果が得られません。
そこで、LOT部分列を抜きし、抜き出した列に対してUNIQUEを適用します。

(R14) =TE(V14#,"LOT")
(S14) =UNIQUE(TE(V14#,"LOT"))

Function IFNC(C, V, Optional S = "")
     If C Then IFNC = S Else IFNC = V
End Function

Function IFZS(V, Optional S = "")   'IF ZERO SPACE
    IFZS = IFNC(V = 0, V, S)
End Function

Function TINIT(R, Optional T = "", Optional M = 0, Optional N = 0, Optional S = "")
    If T = "" Then
        S = R
    Else
        M = IFZS(M, R.Rows.Count)
        Dim TA: TA = Split(TRIM_(T), ",")
        N = UBound(TA) - LBound(TA) + 1
        S = R.Resize(M, N)

        Dim J: For J = 1 To N
            S(1, J) = TA(J - 1)
        Next J
    End If    
    TINIT = S
End Function

Function TE(R, Optional T = "", Optional S = "", Optional U = "") ' TABLE EDIT
    U = R.Resize(1, R.Columns.Count)
    S = TINIT(R, T)

    Dim J: For J = 1 To UBound(S, 2)
        Dim K: K = MI(U, S(1, J))
        S(1, J) = U(1, K)

        Dim I: For I = 2 To UBound(S, 1)
            If K = "" Then
                S(I, J) = ""
            Else
                S(I, J) = DI(R, I, K)
            End If
        Next I
    Next J
    TE = S
End Function

Rangeから配列を生成できます。

上記の関数の中で、Rangeを使って配列を作成しています。
ReDimを使わなくても、配列が作成でき、大きさもResizeで変更できます。
しかも、Rangeの内容もそのまま初期化されないというおまけ付きです。

ではでは。