在 Excel 2021 中可以使用動態陣列公式,而公式自動溢出的動作也帶來了一些便利性。

例如,輸入公式,儲存格J4:=SORT(UNIQUE(代碼))

在此代碼已先定義名稱「代碼」,儲存格範圍為D欄中的資料範圍。

其中使用了 SORT 函數和 UNIQUE 函數,這二個函數支援動態陣列。

當你在儲存格J4輸入完成後,Excel 會根據運算結果,自動將公式溢出到儲存格J4:J18。

設計者並不需要也不會預先知道公式會溢出至那些儲存格,而且只要代碼欄位裡的資料變動,儲存格J4的公式結果也會隨之改變。

Excel-動態陣列公式和溢出陣列行為

但是,在K欄中的運算公式,使用的是 SUMPRODUCT 函數。

當你輸入公式,儲存格K4:=SUMPRODUCT((代碼=J4)*單價*數量)

你必須還要:複製儲存格K4,貼至儲存格K4:K18。

如果,代碼欄位內容變動,導致動態陣列公式結果改變,就會出現下圖中公式必須進行修正的問題。

Excel-動態陣列公式和溢出陣列行為

公式如果更改,例如:

儲存格K4:=IF(J4<>"",SUMPRODUCT((代碼=J4)*單價*數量),"")

則可以避開這些動態陣列公式的問題。總之還是不太方便,你還可以試試別的設計方式。

Excel-動態陣列公式和溢出陣列行為

如下圖,儲存格J4:=UNIQUE(店名),店名是由B欄中定義名稱的儲存格範圍。

Excel-動態陣列公式和溢出陣列行為

你可以將公式使用在另一個名稱定義上,例如:

分店:=UNIQUE(店名)

Excel-動態陣列公式和溢出陣列行為

所以,只要輸入公式,儲存格J15:=分店,即可獲得動態陣列結果。

但是,如果你想要將動態陣列結果使用在資料驗驗證中,例如,設定清單來源:=分店。

將會得到錯誤訊息。(原想要使用分店的清單來製作一個下拉式清單)

Excel-動態陣列公式和溢出陣列行為

Excel-動態陣列公式和溢出陣列行為

如果你有需要將動態陣列結果轉置,則可以透過 TRANSPOSE 函數。

如果以 ARRAYTOTEXT 函數來看其陣列內容,請注意:

(1) 資料分列的動態陣列是以「分號」隔開,例如:

{"民權店";"仁愛店";"信義店";"忠孝店";"民生店";"和平店";"民族店"}

(2) 資料分欄的動態陣列是以「逗號」隔開,例如:

{"民權店","仁愛店","信義店","忠孝店","民生店","和平店","民族店"}

Excel-動態陣列公式和溢出陣列行為

【參考資料】

 UNIQUE 函數參考微軟提供的說明網頁:UNIQUE 函數
 SORT 函數參考微軟提供的說明網頁:SORT 函數
 SUMPRODUCT 函數參考微軟提供的說明網頁:SUMPRODUCT 函數
 TRANSPOSE 函數可以參考微軟提供的說明網頁:TRANSPOSE 函數
 ARRAYTOTEXT 函數參考微軟提供的說明網頁:ARRAYTOTEXT 函數
 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel 動態陣列 公式溢出
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()