Excel のスピルをRangeで編集

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

# スピルでテーブルを作成します。
[Excelでスピルを使う方法](https://qiita.com/matumoto_onga/items/562a3e2283e4e4b65ef6)の関数A_TDIで、下記のテーブルが表示することが出来ます。

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

![S01A.png](https://qiita-image-store.s3.amazonaws.com/0/184069/ed744536-c9ec-28f5-bd3b-a7e48e34b1b6.png)

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

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

```vb
(V26) =SORT(V14#,2)
```

![S01B.png](https://qiita-image-store.s3.amazonaws.com/0/184069/3363a10b-5a10-c714-869a-b40b55b1e0db.png)

# 関数でテーブルのTITLE部分をCUTします。
なんと、Offset,Resizeを使うと一行になります。

```VB
(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
```
![S01C.png](https://qiita-image-store.s3.amazonaws.com/0/184069/06d9f13f-c092-c7c7-a99b-09001ae8ccf4.png)

# 期待した結果にします。

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

```VB
=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
```
![S01E.png](https://qiita-image-store.s3.amazonaws.com/0/184069/b7ea1244-8bd2-1fa9-1a91-ac01787505f7.png)


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

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

```VB
(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
```
![S01D.png](https://qiita-image-store.s3.amazonaws.com/0/184069/1ba17777-c5ac-426d-b81c-f631e246cfa9.png)

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

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


ではでは。