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

可返回可變大小的陣列的公式稱為 動態陣列 公式。 目前返回成功溢出之陣列的公式可稱為 溢出陣列 公式。 

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

溢出是什麼意思?

附註: 較舊的陣列公式 ,稱為 舊版陣列 公式,一直會返回固定大小的結果 -它們一直溢出到相同的儲存格數目。 本主題所述的溢出行為不適用於舊版陣列公式。

溢出表示公式已產生多個值,而這些值已置於連續的儲存格中。 例如 ,=SORT (D2:D11,1,-1) 會以遞減順序排序陣列,會返回 10 列高的對應陣列。 但您只需要在左上方儲存格中輸入公式,或在此案例中輸入 F2,公式就會自動溢出到儲存格 F11。

使用 =SORT (D2:D11,1,-1)

要點

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

  • 如果您要撰寫動態陣列公式以對資料清單採取行動,將公式放在 Excel 資料表中,然後使用結構化參照來參照資料會很有用 這是因為結構化參照會在資料表中新增或移除列時自動調整。

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

  • 輸入溢出的陣列公式後,當您選取溢位區域域內的任何儲存格時,Excel範圍周圍會放置一個強調的邊框。 當您選取區域外的儲存格時,邊框會消失。

    以藍色邊框顯示輸出範圍的陣列公式

  • 只有溢位區域域中的第一個儲存格可以編輯。 如果您選取溢位區域域中的另一個儲存格,公式就會顯示在資料編輯欄中,但文字是「虛無的」,而且無法變更。 如果您需要更新公式,您應該選取陣列範圍中的左上方儲存格,並根據需要變更,Excel 會在您按下Enter時自動更新溢位區域域的其餘部分。

    虛影陣列公式的影像,表示它無法編輯,因為它不是陣列範圍中的第一個儲存格

  • 公式重迭 - 如果有任何封鎖輸出範圍的話,陣列公式無法輸入。 如果發生此情況,Excel會#SPILL錯誤,指出有封鎖。 如果您移除封鎖,公式即會如預期溢出。 在下列範例中,公式的輸出範圍會與資料重迭另一個範圍,並顯示為點狀邊框重迭儲存格,其值表示無法溢出。 移除封鎖資料,或將其複製到其他位置,公式會如預期溢出。

    圖片的#SPILL! 錯誤,指出陣列公式輸出發生封鎖,無法溢出。

  • 基於背面相容性的原因,仍支援透過 CTRL+SHIFT+ENTER (CSE) 輸入的舊版陣列公式,但不應再使用。 您可以依需要將舊版陣列公式轉換成動態陣列公式,方法為:找到陣列範圍中的第一個儲存格、複製公式的文字、刪除舊版陣列的整個範圍,然後重新輸入左上方儲存格中的公式。 在將舊版陣列公式升級為動態陣列公式之前,您應該先瞭解兩者之間的一 些計算差異。

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

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家,或是在 Answers 社群取得支援。

另請參閱

FILTER 函數

RANDARRAY 函數

SEQUENCE 函數

SORT 函數

SORTBY 函數

UNIQUE 函數

Excel 中的 #SPILL! 錯誤

隱含交集運算子:@

需要更多協助?

擴展您的技能
探索訓練
優先取得新功能
加入 Microsoft 測試人員

這項資訊有幫助嗎?

您對翻譯品質的滿意度為何?
會影響您使用體驗的因素為何?

感謝您的意見反應!

×