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

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

SPILL 錯誤的含義

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

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

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

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

解決Excel 中的#SPILL! 錯誤

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

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

溢出範圍非空

原因：公式結(jié)果要填充的區(qū)域包含非空單元格。

解決方案：清除預(yù)期的溢出範圍。

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

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

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

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

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

溢出範圍包含合併單元格

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

解決方案：取消合併溢出區(qū)域中的單元格或?qū)⒐揭苿拥經(jīng)]有合併單元格的另一個位置。

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

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

表格中的溢出範圍

原因：Excel 表格不支持動態(tài)數(shù)組。

解決方案：將表格轉(zhuǎn)換為普通範圍或?qū)⒐椒旁诒砀裢獠恳栽试S其溢出。目前尚不清楚為什麼動態(tài)數(shù)組公式無法在Excel 表格內(nèi)工作（可能是由於結(jié)構(gòu)化引用的特定語法），但無論如何，這兩種非常有用的東西並不兼容:(

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

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

溢出範圍未知

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

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

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

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

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

=SEQUENCE(RANDBETWEEN(1,100))

溢出範圍過大

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

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

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

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

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

=B:B*10%

在動態(tài)Excel 365 中，相同的公式會觸發(fā)#SPILL 錯誤。

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

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

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

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

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

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

對於我們的示例數(shù)據(jù)集，公式為：

=B2:B10*10%

該公式僅輸入到一個單元格（C2）中。結(jié)果是一個動態(tài)數(shù)組，會自動溢出到多個單元格中：

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

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

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

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

=B2*10%

與動態(tài)數(shù)組公式不同，這種傳統(tǒng)的公式樣式在範圍和表格中同樣有效：

解決方案3. 應(yīng)用隱式交集

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

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

對於我們的示例數(shù)據(jù)集，公式採用以下形式：

=@B:B*10%

由於結(jié)果是單個值，而不是動態(tài)數(shù)組，因此此公式既可以在範圍中使用，也可以在表格中使用。但是，在後一種情況下，結(jié)構(gòu)化引用將是更優(yōu)雅和有效的解決方案：

=[@Sales]*10%

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

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

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

以上是＃灑！ Excel中的錯誤 - 意味著什麼以及如何修復(fù)的詳細內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！