如何修正 #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 問題

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

注意事項: OLE 是一種您可以在程式之間共用資訊的技術。

DDE 問題

如果您已使用的動態資料交換 (DDE) 主題傳回 #REF! 錯誤,首先檢查確認您參照的是正確的主題。 如果您仍然收到通知#REF! 錯誤,請檢查您的 信任中心 設定外部內容,如 Office 檔封鎖或解除封鎖 外部內容所述

注意: 動態資料交換 (DDE)是一種在 Microsoft Windows 程式之間交換資料的已建立通訊協定。

巨集問題

如果宏在工作表上輸入的函數參照函數上方的儲存格,且包含該函數的儲存格位於列 1,則函數會#REF! 因為第 1 列上方沒有儲存格。 檢查函數,查看引數是否參照不正確儲存格或儲存格範圍。 這可能需要在 Visual Basic 編輯器或 VBE (中) 宏,以將這種情況納入考慮。

需要更多協助嗎?

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

另請參閱

Excel 公式概觀

如何避免公式出錯

偵測公式中的錯誤

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

需要更多協助?

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×