🔧 阿川の電商水電行
什麼是 Spill（動態數組）？
當在儲存格上指定矩陣狀的多個值（動態數組）時，這是一項 將值擴展到相鄰儲存格的功能。動態數組本身也稱為 Spill。本文將統一使用 Spill 這個名稱。
這個功能在最近的 Excel 版本中可用（2019 年及以後，嚴格來說是從 2021 年開始）。
例如，將 Spill 的指定式 ={1,2,3; 4,5,6} 輸入到 A1 時，會展開成如下矩形：
在指定矩陣的式子中，,（逗號）是列方向的分隔符，;（分號）則是行方向的分隔符。通常會將 Spill 用作儲存格的行列參照（如 =A1:B3 的參照形式），但本文將基本上為了說明進行此標記，請務必記住。
Spill 的表示範例
={
1,2,3;
4,5,6
}如果將值展開到的儲存格中已經有某些值，則會出現錯誤。
可以認為，傳統的陣列公式等功能已經被整合到這個 Spill 中。
處理 Spill 的公式
常規運算子
Spill 之間可以像單一值一樣通過二元運算子進行連接。這只是對同一位置的元素進行單一值的二元運算，並不是特別複雜的矩陣乘法之類的運算。
所有基本的四則運算、^（冪）及 >（大於比較）等的二元運算子，根據確認的情況下皆可適用。（對於比較運算子而言，則會生成布林值的動態數組）
順便提一下，若一方為標量（單一）值或動態數組的大小不同，則產生的結果將會是較大一方的行數與列數。
={1,2,3,4,5; 6,7,8,9,10} < 6
// => {TRUE,TRUE,TRUE,TRUE,TRUE; FALSE,FALSE,FALSE,FALSE,FALSE}後置運算子
要引用整個 Spill 區域，需使用 # 後置運算子。若不使用，則僅會引用儲存格部分。
// 若 A1 中為 ={1,2,3; 4,5,6}
=A1 // => 1
=A1# // => {1,2,3; 4,5,6}
=SUM(A1) // => 1
=SUM(A1#) // => 21
// INDIRECT 函數亦可相同使用，但在字符串中包含或作為後置運算子使用均可
=INDIRECT("A1#") // => {1,2,3; 4,5,6}
=INDIRECT("A1")# // => {1,2,3; 4,5,6}函數
有一些函數也能同樣處理 Spill。
存在行方向和列方向的概念，函數可能會分開或同一函數可以同時處理，因此需分別使用。
// TEXTSPLIT: 可將字符串在行和列方向上分割
=TEXTSPLIT("a,b,c", ",") // => ={"a","b","c"}
=TEXTSPLIT("a,b,c", , ",") // => ={"a"; "b"; "c"}
// TRANSPOSE: 將 Spill 轉置
=TRANSPOSE({1,2,3; 4,5,6}) // => ={1,4; 2,5; 3,6}
// SEQUENCE: 生成連續數字的 Spill
=SEQUENCE(2,3) // => ={1,2,3; 4,5,6}
// UNIQUE: 排除重複值
=UNIQUE({1; 1; 3; 3; 2}) // => ={1; 3; 2} ※ 行方向
=UNIQUE({1,1,3,3,2}, TRUE) // => ={1,3,2} ※ 列方向
// HSTACK: 行方向結合
=HSTACK({1; 2; 3}, {4; 5; 6}) // => ={1,4; 2,5; 3,6}
// VSTACK: 列方向結合
=VSTACK({1,2,3}, {4,5,6}) // => ={1,2,3; 4,5,6}
// INDEX: 從 Spill 中擷取特定的行、列或元素（行 → 列 使用一基索引指定）
=INDEX({1,2,3; 4,5,6}, 2) // => ={4,5,6}
=INDEX({1,2,3; 4,5,6}, , 3) // => ={3; 6}
=INDEX({1,2,3; 4,5,6}, 2, 3) // => 6過去存在的函數也有很多已經支持 Spill，並使得可以透明地處理 Spill。
=MOD({1; 2; 3; 4; 5; 6}, 2) // => ={1; 0; 1; 0; 1; 0}
=MOD({1; 2; 3; 4; 5; 6}, 2) = 0 // => ={FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}有一些函數以多個 Spill 為參數，並將它們的元素順序視為相互關聯進行處理。（如比較參數的 Spill 1 的第 N 項與別的參數的 Spill 2 的第 N 項的函數）
// FILTER: 將第 1 個參數到第 2 個參數視為相關的 Spill，當後者為 TRUE 時抽取前者的元素
=LET(
products, {"水果"; "水果"; "水果"; "點心"},
prices, {100; 150; 80; 300},
prices_preds, products="水果", // => {TRUE; TRUE; TRUE; FALSE}
FILTER(prices, prices_preds)
) // => ={100; 150; 80}
// GROUPBY: 將第 1 個參數到第 2 個參數視為相關的 Spill，以前者為鍵，後者以第 3 參數的彙總函數進行彙總
// ※ 通過引數指定可移除合計
=LET(
products, {"水果"; "水果"; "水果"; "點心"},
prices, {100; 150; 80; 300},
GROUPBY(products, prices, SUM)
) // => ={{"水果",330}; {"點心",300}, {"合計",630}}
// PIVOTBY: 將第 1 個參數（列標籤）、第 2 個參數（行標籤）、第 3 個參數（彙總值）視為相關的 Spill，使用第 4 個參數的彙總函數生成彙總表
=LET(
years, {2024; 2024; 2025; 2025},
products, {"水果"; "水果"; "水果"; "點心"},
prices, {100; 150; 80; 300},
PIVOTBY(products, years, prices, SUM)
) // => ={{"","水果","點心","合計"};
// {"2024",250,"",250};
// {"2025",80,300,380};
// {"合計",330,300,630}}
// XLOOKUP: 將第 2 個參數到第 3 個參數視為相關的 Spill，並用第 1 個參數搜索第 2 個參數，提取對應的第 3 個參數的元素
=LET(
products, {"水果"; "水果"; "水果"; "點心"},
prices, {100; 150; 80; 300},
XLOOKUP({"水果"; "點心"}, products, prices)
) // => ={100; 300}
=LET(
products, {"水果"; "水果"; "水果"; "點心"},
prices, {100; 150; 80; 300},
XLOOKUP({"水果"; "點心"}, products, prices)
) // => ={100,300}透過 Spill 與各種運算或函數的透明結合，使得可以進行強大的操作。過去需分割工作表或儲存格的情況，如今只需一個公式即可完成。
在 VBA 函數中的 Spill
自行創建入出 Spill 的函數
也可以自定義處理 Spill 的函數。
這裡顯示了一個簡易的實作範例，該函數接收傳遞的 Spill 並將其加倍後返回。
Function DoubleSpill(ByVal val As Variant) As Variant
Dim result As Variant
result = val ' 考慮到 Range 對象的情況，暫時轉換為 Variant(,)
For i = LBound(result, 1) To UBound(result, 1)
For j = LBound(result, 2) To UBound(result, 2)
result(i, j) = result(i, j) * 2
Next
Next
DoubleSpill = result
End Function=DoubleSpill({1,2,3; 4,5,6}) // => ={2,4,6; 8,10,12}
=DoubleSpill(A1#) // 這樣也能運作基本上這樣就可以，但由於傳遞的參數在某些情況下型別或維度會有所不同，因此實際上需要考慮的情況許多。
- 基本來源於儲存格的情況：
Range對象- 雖然可以像
rng(1, 2)這樣訪問，但LBound等的概念並不在Range中，因此需要暫時將變量分配給Variant以便與多維數組同等處理
- 雖然可以像
- SEQUENCE 函數等 Spill 計算值的情況：
Variant(,)對象- 但若計算結果僅為一行時：
Variant()對象
- 但若計算結果僅為一行時：
那麼在下面的情況會怎樣呢？
=DoubleSpill(SEQUENCE(1,3)) // => 錯誤（無法轉換一維數組）
// 若 A1 中為 ={1,2,3} 的情況
=DoubleSpill(A1#) // => ={2,4,6}（因為從 Range 對象轉換為二維數組，所以沒有問題）
=DoubleSpill(DoubleSpill(A1#)) // 錯誤（DoubleSpill 的結果為一維數組，因此出現錯誤 一旦儲存在儲存格中再參考該儲存格則沒有問題）若要考慮到傳遞值的邊界情況，思考 Range、Variant、Variant(,) 中的任一種可能傳遞，或者傳遞標量值應該會比較合適。
探測 Spill 範圍
也可以從 VBA 中檢測 Spill 範圍：
Function CheckSpillRange$(ByVal r As Range)
If r.HasSpill Then
CheckSpillRange$ = r.SpillParent.SpillingToRange.Address
End If
End Function=CheckSpillRange(B2) // => （例如）"$A$1:$C$2"Excel 外的 Spill 處理
在 Google 試算表中嘗試了各種操作，基本概念似乎相同，但可能會有 Spill 用的函數，或者在將陣列值 Spill 到儲存格時，外層需要 ARRAYFORMULA(陣列公式) 函數。
此外，針對讀寫 Excel 工作簿的庫，其對 Spill 的支持似乎也很少。Go 語言的 excelize 與 Java 的 Apache POI 當前看似未直接支持 Spill 的功能。（不過 POI 應該對傳統陣列公式有支持）
對於 Spill，當前不要指望在 Excel 外部能夠流暢地相互操作。 減少用於 Excel 資料分析的人的印象。
總結
最近的 Excel 引入了 LAMBDA 函數和 Python 連接等新功能話題，但 Spill 也是一項非常強大且直觀的功能。
或許很多像我這樣在 Excel 的知識停留在真正向雲端和進步之前，這是時候要跟上趨勢了。
原文出處：https://qiita.com/ssc-ksaitou/items/c3a0a0ac72edb5614aa0
1) --- 會變成分隔線（上一行必須是空白）
2) # 會變成一級標題
3) ## 會變成二級標題
4) ### 會變成三級標題
5) **粗體文字**會顯示粗體文字
6) ```當第一行與最後一行會顯示程式碼
7) 請搜尋 Markdown 語法，了解各種格式
