如何修正 #REF! 錯誤

如何修正 #REF! 錯誤

當公式參照無效的儲存格時,會顯示 #REF! 錯誤。當參照儲存格的公式遭到刪除或被貼上的內容覆蓋時,最常發生這種情形。

範例 - 刪除欄所造成的 #REF! 錯誤

下列範例在欄 E 中使用公式 =SUM(B2,C2,D2)。

使用明確儲存格參照的公式,例如 =SUM(B2,C2,D2),如果某欄遭到刪除就會產生 #REF! 錯誤。

如果您要刪除欄 B、C 或 D,則會造成 #REF! 錯誤。在此案例中,我們會刪除欄 C (2007 銷售額),而公式現在會變成 =SUM(B2,#REF!,C2)。當您使用像這樣的明確儲存格參照 (您在其中個別參照每個儲存格並以逗號分隔) 且刪除參照的列或欄時,Excel 無法解析,所以就會傳回 #REF! 錯誤。這就是為什麼不建議在函數中使用明確儲存格參照的主因。

刪除欄位而造成的 #REF! 錯誤範例。

解決方法

  • 如果您不小心刪除列或欄,您可以立刻按一下 [快速存取工具列] 上的 [復原] 按鈕 (或按 CTRL+Z) 以還原內容。

  • 將公式調整為使用範圍參照 (而不是個別儲存格),例如 =SUM(B2:D2)。現在您可以刪除加總範圍內的任何欄,而 Excel 會自動調整公式。您也可以使用 =SUM(B2:B5) 取得各列的加總。

範例 - VLOOKUP 與錯誤範圍參照

在下列範例中,=VLOOKUP(A8,A2:D5,5,FALSE) 會傳回 #REF! 錯誤,因為它在尋找要從欄 5 傳回的值,但參照的範圍為 A:D (只有 4 欄)。

VLOOKUP 公式與錯誤範圍的範例。公式為 =VLOOKU(A8,A2:D5,5,FALSE)。VLOOKUP 範圍中沒有第五欄,所以 5 會造成 #REF! 錯誤。

解決方法

將範圍調大,或將欄查閱值縮小為符合參照範圍。就如同 =VLOOKUP(A8,A2:D5,4,FALSE) 一樣,=VLOOKUP(A8,A2:E5,5,FALSE) 會是有效的參照範圍。

範例 - INDEX 與錯誤列或欄參照

在此範例中,公式 =INDEX(B2:E5,5,5) 傳回 #REF! 錯誤,因為 INDEX 範圍為 4 列 4 欄,但公式要求傳回第 5 列和第 5 欄的值。

INDEX 公式與無效範圍參照的範例。公式為 =INDEX(B2:E5,5,5),但範圍只是 4 列 4 欄。

解決方法

將列或欄參照調整為在 INDEX 查閱範圍內。 INDEX(B2:E5,4,4) 就會傳回有效的值。

範例 – 使用 INDIRECT 參照已關閉的活頁簿

在下列範例中,INDIRECT 函數嘗試參照已關閉的活頁簿,因而造成 #REF! 錯誤。

INDIRECT 參照已關閉的活頁簿而造成的 #REF! 錯誤範例。

解決方法

開啟參照的活頁簿

OLE/DDE 問題

如果您已使用的物件連結與嵌入 (OLE) 連結傳回 #REF! 錯誤,則請啟動連結正在呼叫的程式。

附註:OLE 是一種您可以用來在程式之間共用資訊的技術。

如果您已使用的動態資料交換 (DDE) 主題傳回 #REF! 錯誤,請確認您參照的是正確的主題。

附註:DDE 是一種為交換以 Microsoft Windows 為基礎的程式之間的資料而建立的通訊協定。

巨集問題

如果巨集在工作表輸入的函數參照函數上方的儲存格,而含有函數的儲存格位於列 1,函數則會傳回 #REF!,因為列 1 上方沒有儲存格。檢查函數確認引數是否參照無效的儲存格或儲存格範圍。這可能需要在 Visual Basic 編輯器 (VBE) 中編輯巨集,以將該情況納入考量。

需要更多協助嗎?

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

請參閱

Excel 公式概觀

如何避免公式出錯

使用錯誤檢查偵測公式中的錯誤

Excel 函數 (依英文字母順序排列)

Excel 函數 (依類別)

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×