在 Excel 2021 中可以使用動態陣列公式，而公式自動溢出的動作也帶來了一些便利性。
例如，輸入公式，儲存格J4：=SORT(UNIQUE(代碼))
在此代碼已先定義名稱「代碼」，儲存格範圍為D欄中的資料範圍。
其中使用了 SORT 函數和 UNIQUE 函數，這二個函數支援動態陣列。
當你在儲存格J4輸入完成後，Excel 會根據運算結果，自動將公式溢出到儲存格J4:J18。
設計者並不需要也不會預先知道公式會溢出至那些儲存格，而且只要代碼欄位裡的資料變動，儲存格J4的公式結果也會隨之改變。
但是，在K欄中的運算公式，使用的是 SUMPRODUCT 函數。
當你輸入公式，儲存格K4：=SUMPRODUCT((代碼=J4)*單價*數量)
你必須還要：複製儲存格K4，貼至儲存格K4:K18。
如果，代碼欄位內容變動，導致動態陣列公式結果改變，就會出現下圖中公式必須進行修正的問題。
公式如果更改，例如：
儲存格K4：=IF(J4<>"",SUMPRODUCT((代碼=J4)*單價*數量),"")
則可以避開這些動態陣列公式的問題。總之還是不太方便，你還可以試試別的設計方式。
如下圖，儲存格J4：=UNIQUE(店名)，店名是由B欄中定義名稱的儲存格範圍。
你可以將公式使用在另一個名稱定義上，例如：
分店：=UNIQUE(店名)
所以，只要輸入公式，儲存格J15：=分店，即可獲得動態陣列結果。
但是，如果你想要將動態陣列結果使用在資料驗驗證中，例如，設定清單來源：=分店。
將會得到錯誤訊息。(原想要使用分店的清單來製作一個下拉式清單)
如果你有需要將動態陣列結果轉置，則可以透過 TRANSPOSE 函數。
如果以 ARRAYTOTEXT 函數來看其陣列內容，請注意：
(1) 資料分列的動態陣列是以「分號」隔開，例如：
{"民權店";"仁愛店";"信義店";"忠孝店";"民生店";"和平店";"民族店"}
(2) 資料分欄的動態陣列是以「逗號」隔開，例如：
{"民權店","仁愛店","信義店","忠孝店","民生店","和平店","民族店"}
【參考資料】
SORT 函數參考微軟提供的說明網頁：SORT 函數 SUMPRODUCT 函數參考微軟提供的說明網頁：SUMPRODUCT 函數 TRANSPOSE 函數可以參考微軟提供的說明網頁：TRANSPOSE 函數 ARRAYTOTEXT 函數參考微軟提供的說明網頁：ARRAYTOTEXT 函數
留言列表