＃灑！ Excel中的錯誤 - 意味著什麼以及如何修復

Excel 中的#SPILL 錯誤：診斷和解決方法

您是否遇到過這種情況：升級到包含最新Excel 更新的Microsoft Office 365 後，您多年來一直使用的可靠公式突然停止工作，取而代之的是#SPILL 錯誤？別擔心，本文將為您解答。

SPILL 錯誤的含義

簡單來說，當公式產生多個結果但無法在工作表上全部顯示時，就會出現#SPILL!錯誤。

Excel 365 中引入了動態數組，任何產生多個計算結果的公式都會自動將其結果“溢出”到相鄰單元格中，即使該公式最初並非設計用於處理數組。包含結果的單元格範圍稱為溢出範圍。如果工作表上的某些內容阻止填充該範圍，則會發生#SPILL! 錯誤。

大多數情況下，此行為是可以理解和預測的。例如，如果您的公式預計返回多個值，但附近的單元格已填充其他數據，只需刪除這些數據，錯誤就會消失。

但有時原因並不那麼明顯，因此令人困惑。也許溢出區域中一個或多個單元格包含空格或人眼無法看到的不可打印字符。或者，複製到整列的相同公式會阻止溢出範圍。或者，您可能遇到了一些極少數不支持動態數組的功能。要修復錯誤，您必須分別調查每個案例並確定問題的根本原因。

解決Excel 中的#SPILL! 錯誤

SPILL! 錯誤可能由多種原因引起。要了解問題的根本原因，請單擊警告圖標（帶有感嘆號的黃色菱形），並閱讀以灰色突出顯示的第一行中的消息：

確定原因後，請在下面找到相應的示例，其中包含有關如何解決特定案例的詳細說明。

溢出範圍非空

原因：公式結果要填充的區域包含非空單元格。

解決方案：清除預期的溢出範圍。

在最簡單的場景中，只需單擊公式單元格，您就會看到一個虛線邊框指示溢出範圍的邊界——其中的任何數據都是障礙。因此，要么從溢出區域刪除現有數據，要么將公式移動到沒有阻塞的另一個位置。

但是，在某些情況下，溢出範圍可能看起來為空白，但實際上並非如此。問題可能在於潛伏在某些單元格中的不可見字符（例如空格），或者公式返回空字符串。

要檢測此類單元格，請單擊警告標誌，您將看到此說明——溢出範圍非空。在其下方，有多個選項。單擊選擇阻塞單元格，Excel 將向您顯示哪些單元格阻止公式溢出。

在下圖中，阻塞單元格是A6，它包含公式返回的空字符串(“”)。

要清除阻塞單元格，請轉到“開始”選項卡> “編輯”組，然後單擊“清除”>“全部清除”。

溢出範圍包含合併單元格

原因：溢出不適用於合併單元格。

解決方案：取消合併溢出區域中的單元格或將公式移動到沒有合併單元格的另一個位置。

如果預計溢出數組中存在一個或多個合併單元格，則會顯示以下錯誤消息：溢出範圍包含合併單元格。

如果您難以直觀地檢測合併單元格，請使用選擇阻塞單元格選項跳轉到有問題的單元格：

表格中的溢出範圍

原因：Excel 表格不支持動態數組。

解決方案：將表格轉換為普通範圍或將公式放在表格外部以允許其溢出。目前尚不清楚為什麼動態數組公式無法在Excel 表格內工作（可能是由於結構化引用的特定語法），但無論如何，這兩種非常有用的東西並不兼容:(

要確認問題的根本原因，請單擊帶有感嘆號的圖標，您將在第一行中看到此文本：表格中的溢出範圍。

在這種情況下，您可以做的最好的事情是將表格轉換為範圍。為此，右鍵單擊表格內的任意位置，然後單擊表格>轉換為範圍。或者，您可以將公式移動到表格邊界之外。

溢出範圍未知

原因：Excel 無法確定溢出數組的大小。

解決方案：嘗試為您的任務制定不同的公式。

當將RANDARRAY、RAND 或RANDBETWEEN 等易失性函數與動態數組函數結合使用時，可能會出現#SPILL 錯誤，因為易失性函數返回的數組在電子表格的計算之間會發生變化，並且“包裝器”函數無法確定其大小。

在這種情況下，錯誤消息顯示為：溢出範圍未知。

例如，以下公式會引發#SPILL! 錯誤，因為RANDBETWEEN 輸出不斷變化，而SEQUENCE 不知道要生成多少個值：

=SEQUENCE(RANDBETWEEN(1,100))

溢出範圍過大

原因：Excel 無法輸出溢出數組，因為它超出了電子表格的邊緣。

解決方案：不要計算整列，而是引用已用範圍、單個單元格或添加@ 運算符以執行隱式交集。

這是最複雜的情況，具體取決於您的目標和工作表的結構，您可能需要以不同的方式調整公式。在這裡，我們將通過一個非常簡單的示例演示一種通用方法。

假設您在B 列中有一列數字，並且您希望在C 列中返回這些數字的10%。

在傳統的Excel 2019 和更早版本中，以下公式可以正常工作（在下圖的左側部分，它位於單元格C2 到C7 中）：

=B:B*10%

在動態Excel 365 中，相同的公式會觸發#SPILL 錯誤。

錯誤的原因是溢出範圍過大。

為什麼新Excel 中的公式會損壞？因為隱式交集不再在後台默默執行。這個術語聽起來很神秘，但其背後的邏輯很簡單——將多個值減少為單個值。

在365 之前的版本中，這是Excel 的默認行為——由於單元格只能包含一個值，因此公式被迫返回單個結果。在我們的示例中，即使我們提供一整列(B:B)，Excel 也只處理與公式位於同一行的單個值。因此，C2 中的公式計算B2 中值的10%，C3 中的公式計算B3 中值的10%，依此類推。

在動態數組Excel 中，默認行為有所不同——任何可能返回多個結果的公式都會自動將其溢出到工作表上。在我們的案例中，Excel 將B 列中的每個單元格乘以10%，得到超過一百萬個結果（更準確地說，是1,048,576 個），嘗試將所有這些結果輸出到從C2 開始的C 列中，但到達了工作表網格的末尾——因此出現#SPILL 錯誤。猜猜如果我們將公式放在C1 中會發生什麼？是的，它會起作用，因為這次有足夠的單元格可以填充——溢出範圍將佔據正好1,048,576 個單元格！

解決方案1. 引用範圍而不是列

這是一種非常合理的方法——不要引用整列，而只引用相關數據。除了修復#SPILL 錯誤外，它還有助於節省Excel 資源並提高性能。

對於我們的示例數據集，公式為：

=B2:B10*10%

該公式僅輸入到一個單元格（C2）中。結果是一個動態數組，會自動溢出到多個單元格中：

注意。由於Excel 表格不支持動態數組，因此此解決方案僅適用於普通範圍。

解決方案2. 計算單個單元格並將公式向下複製

這是傳統方法——引用同一行上的一個單元格並將公式向下複製。

在我們的案例中，以下公式位於C2 中，然後您可以將其向下拖動到C10：

=B2*10%

與動態數組公式不同，這種傳統的公式樣式在範圍和表格中同樣有效：

解決方案3. 應用隱式交集

這種方法稍微複雜一些——使用隱式交集運算符(@ 符號)，它是在Excel 365 中作為動態數組更新的一部分引入的。

在您的公式中，在要將數組或範圍減少為單個值的位置插入@ 字符，例如在範圍/列/行引用、函數或表達式之前。將公式輸入到最上面的單元格中，然後根據需要將其向下拖動到多行。

對於我們的示例數據集，公式採用以下形式：

=@B:B*10%

由於結果是單個值，而不是動態數組，因此此公式既可以在範圍中使用，也可以在表格中使用。但是，在後一種情況下，結構化引用將是更優雅和有效的解決方案：

=[@Sales]*10%

在某種程度上，這解釋了為什麼在舊版本中創建的公式中偶爾會出現“@”字符——為了保持兼容性。因為在動態之前的Excel 中，公式無法溢出到多個單元格，所以當公式導入到動態數組Excel 中時，隱式交集運算符可以確保相同的行為。

我個人的建議是謹慎使用這種方法，因為它會禁用Excel 的一項非常高效且在許多場景中非常有用的新功能。

這就是如何排查和修復Excel 中的#SPILL 錯誤。在接下來的文章中，我們將調查並在一些流行的公式（如VLOOKUP、INDEX MATCH、SUMIF 等）中解決此錯誤。感謝您的閱讀，敬請關注！

以上是＃灑！ Excel中的錯誤 - 意味著什麼以及如何修復的詳細內容。更多資訊請關注PHP中文網其他相關文章！