動態陣列公式與溢出的陣列行為

傳回一組值(又稱為陣列)的 Excel 公式會將這些值傳回連續的儲存格。 此行為稱為 [溢出]。

可以傳回可變大小陣列的公式稱為動態陣列公式。 目前傳回成功超出陣列的公式可以稱為 [溢出陣列公式]。 

以下是一些可協助您瞭解及使用這些公式類型的筆記。 

溢出代表什麼意思?

附註: 較舊的陣列公式(稱為傳統陣列公式)永遠會傳回固定大小的結果-它們永遠會溢出至相同的儲存格數。 本主題中所述的超出行為不適用於舊版陣列公式。

[溢出] 表示公式已產生多個值,而這些值已放在連續的儲存格中。 例如, = SORT (D2: D11,1,-1),以遞減順序將陣列排序,將會傳回10列高的對應陣列。 但您只需要在左上角儲存格中輸入公式,或在這種情況下,它會自動向下溢出至儲存格 F11。

使用 = SORT (D2: D11,1,-1),將儲存格 D2: D11 中的值排序。

重點

  • 當您按下 Enter 以確認公式時,Excel 會動態調整輸出範圍的大小,並將結果放入該範圍內的每個儲存格。

  • 如果您正在撰寫的動態陣列公式是作用於資料清單,則將它放在Excel 表格中,然後使用結構化參照來參照資料,可能會很有用。 這是因為結構化參照會自動調整為在表格中新增或移除列。

  • 在 Excel 表格本身中不支援溢出的陣列公式,因此您應該將它們放在表格外的格線中。 表格最適合用來存放獨立資料的列和欄。

  • 在您輸入溢出的陣列公式之後,當您選取溢位區域域中的任何儲存格時,Excel 就會將醒目提示的框線放在該範圍的周圍。 當您選取區域外的儲存格時,框線就會消失。

    使用藍色框線醒目提示輸出範圍的陣列公式

  • 只有溢位區域域中的第一個儲存格才能進行編輯。 如果您在溢位區域中選取另一個儲存格,公式將會顯示在資料編輯列中,但文字會是「幻像」,且無法變更。 如果您需要更新公式,您應該選取陣列範圍中的左上角儲存格,視需要變更,當您按enter時,Excel 會自動更新其餘溢位區域域。

    幻影陣列公式的影像(也就是無法編輯),因為它不是陣列範圍中的第一個儲存格

  • 公式重迭-如果有任何封鎖輸出範圍的專案,陣列公式就無法輸入。 如果發生這種情況,Excel 會傳回#SPILL!錯誤,指出發生了問題。 如果您移除封鎖,公式即會如預期溢出。 在下列範例中,公式的輸出範圍會與資料進行另一個範圍的重疊,並以虛線框線重迭儲存格,且其值指出它不能溢出。 移除封鎖資料,或將它複製到其他位置,公式就會如預期溢出。

    #SPILL 的影像! 錯誤,指出陣列公式輸出遇到阻礙超出的阻塞。

  • 使用CTRL + SHIFT + ENTER (CSE)輸入的舊版陣列公式仍支援與後相容性原因,但不應再使用。 如果您想要的話,您可以在陣列範圍中找出第一個儲存格,將舊版陣列公式轉換成動態陣列公式、複製公式的文字、刪除原有陣列的整個範圍,然後在左上方儲存格中重新輸入公式。 在將舊版陣列公式升級至動態陣列公式之前,請注意兩者之間的一些計算差異

  • Excel 對活頁簿之間的動態陣列提供有限支援,只有同時開啟活頁簿時才支援這種情況。 如果您關閉來源活頁簿,當您重新整理時,任何連結的動態陣列公式均會傳回 #REF! 錯誤

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

FILTER 函數

RANDARRAY 函數

SEQUENCE 函數

SORT 函數

SORTBY 函數

UNIQUE 函數

Excel 中的 #SPILL! 錯誤

隱含交集運算子:@

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×