如何修正 #VALUE! 錯誤

如何修正 #VALUE! 錯誤

#VALUE 是 Excel 的說法,表示「您輸入公式的方式錯誤。或,您參照的儲存格錯誤。」這是非常一般的錯誤,且可能很難找到發生錯誤的確實原因。此頁面上的資訊說明一般問題和錯誤的解決方案。您可能需要嘗試一或多個解決方案才能修正您遇到的特定問題。

修正特定函數的錯誤

如需詳細資訊,請參閱修正 CONCATENATE 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 DATEVALUE 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 DAYS 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 IF 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 SUMIF/SUMIFS 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 SUMPRODUCT 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 TIMEVALUE 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 TRANSPOSE 函數中的 #VALUE! 錯誤

如需詳細資訊,請參閱修正 VLOOKUP 函數中的 #VALUE! 錯誤

此清單中看不到您的函數嗎?請嘗試下面列出的其他解決方案。

減法問題

如果您是使用 Excel 的新手,您輸入的減法公式可能不正確。有兩種方法可以執行此操作:

從另一個儲存格減去儲存格參照

儲存格 D2 為 $2,000.00、儲存格 E2 為 $1,500.00、儲存格 F2 有公式:=D2-E2 且結果為 $500.00

在兩個個別的儲存格中輸入兩個值。在第三個儲存格中,從另一個儲存格減去一個儲存格參照。在這個範例中,儲存格 D2 具有預算金額,儲存格 E2 則具有實際金額。F2 的公式為 =D2-E2

或,使用 SUM 搭配正數和負數

儲存格 D6 為 $2,000.00、儲存格 E6 為 $1,500.00、儲存格 F6 有公式:=SUM(D6,E6) 且結果為 $500.00

在一個儲存格中輸入一個正數值,在另一個儲存格中輸入一個負數值。在第三個儲存格中,使用 SUM 函數將兩個儲存格相加。在這個範例中,儲存格 D6 具有預算金額,儲存格 E6 則具有負數的實際金額。F6 的公式為 =SUM(D6,E6)

如果您是使用 Windows,您可能會連執行最基本的減法公式都會發生 #VALUE! 錯誤。以下方式可能可以解決您的問題:

  1. 先執行快速測試。在新的活頁簿中,於儲存格 A1 中輸入 2。在儲存格 B1 中輸入 4。然後在 C1 輸入這個公式 =B1-A1。如果您發生 #VALUE! 錯誤,請移至下一個步驟。如果您沒有發生錯誤,請嘗試此頁面上的其他解決方案。

  2. 在 Windows 中,開啟您的 [地區] 控制台。

    • Windows 10:按一下 [開始],輸入地區,然後按一下 [地區] 控制台。

    • Windows 8:在 [開始] 畫面上,輸入地區,按一下 [設定],然後按一下 [地區]。

    • Windows 7:按一下 [開始],然後輸入地區,然後按一下 [地區和語言]。

  3. 在 [格式] 索引標籤上,按一下 [其他設定]。

  4. 尋找 [清單分隔字元]。如果 [清單分隔字元] 是設定為減號,請將它變更為其他符號。例如,逗號就是一個常見的清單分隔字元。分號也很常見。但是,其他清單分隔字元可能會更適合您所在的特定區域。

  5. 按一下 [確定]。

  6. 開啟您的活頁簿。如果儲存格包含一個 #VALUE! 錯誤,請按兩下來編輯它。

  7. 如果有逗號的位置應該使用減號來用於減法,請將它們變更為減號。

  8. 按下 ENTER。

  9. 請為有錯誤的其他儲存格重複此程序。

從另一個儲存格減去儲存格參照

儲存格 D10 為 1/1/2016、儲存格 E10 為 4/24/2016、儲存格 F10 有公式:=E10-D10 且結果為 114

在兩個個別的儲存格中輸入兩個日期。在第三個儲存格中,從另一個儲存格減去一個儲存格參照。在這個範例中,儲存格 D10 有開始日期,儲存格 E10 有結束日期。F10 有公式 =E10-D10

或者,使用 DATEDIF 函數

儲存格 D15 為 1/1/2016、儲存格 E15 為 4/24/2016、儲存格 F15 有公式:=DATEDIF(D15,E15,"d") 且結果為 114

在兩個個別的儲存格中輸入兩個日期。在第三個儲存格中,使用 DATEDIF 函數找出日期中的差異。如需 DATEDIF 函數的詳細資訊,請參閱計算兩個日期之間的差

請將您的日期欄加寬。如果您的日期已經靠右對齊,那它就是日期。但如果它是靠左對齊,這表示日期並非真正的日期。它是文字。Excel 不會將文字辨識為日期。以下是一些可協助解決這個問題的解決方案。

檢查前置空格

  1. 按兩下減法公式中使用的日期。

  2. 將您的游標放在開頭,然後看看您是否可以選取一或多個空格。以下是在儲存格開頭位置選取空格後的樣子: 1/1/2016 前已選取空格的儲存格

    如果您的儲存格有問題,請繼續下一個步驟。如果您沒有看到一或多個空格,請移至下一節以檢查您電腦的日期設定。

  3. 按一下欄標題來選取包含日期的欄。

  4. 按一下 [日期] > [資料剖析]。

  5. 按兩次 [下一步]

  6. 在精靈的 3-3 步驟中,於 [欄位的資料格式] 底下,按一下 [日期]。

  7. 選擇日期格式,然後按一下 [完成]。

  8. 為其他欄位重複這個步驟以確保它們的日期前面沒有前置空格。

檢查您電腦的日期設定

Excel 是使用您電腦的日期系統。如果儲存格日期不是使用相同日期系統輸入,Excel 將不會把它辨識為真正的日期。

例如,假設您的電腦顯示日期為 mm/dd/yyyy。如果您在儲存格中輸入像那樣的日期,Excel 會將它辨識為日期,且您可以在減法公式中使用它。但是,如果您輸入像 dd/mm/yy 這樣的日期,Excel 就不會將它辨識為日期。而是會將它視為文字。

這個問題有兩個解決方案:您可以將電腦使用的日期系統變更為和您想要在 Excel 中輸入的日期系統相符的系統。或者,您可以在 Excel 中建立一個新的欄位,並使用 DATE 函數依據儲存為文字的日期建立一個真正的日期。假設您的電腦日期系統為 mm/dd/yyy 且您的文字日期為 31/12/2017,以下是在儲存格 A1 中的做法:

  1. 請建立像這樣的公式:=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

  2. 結果會是 12/31/2017

  3. 如果您想要的顯示格式為 dd/mm/yy,請按 CTRL+1 (在 Mac 上為 MAC Command 按鈕圖示的影像 + 1)。

  4. 選擇使用 dd/mm/yy 格式的不同地區設定,例如 [英文 (英國)]。當您完成套用格式時,結果會是 31/12/2017,且它會是真正的日期,而不是文字日期。

附註: 上述公式是使用 DATERIGHTMIDLEFT 函數撰寫。請注意,它是在假設文字日期有兩個天次字元、兩個月份字元,以及四個年份字元的情況下撰寫。您可能需要自訂您的日期適用的公式。

空格和文字問題

通常發生 #VALUE! 的原因是因為您的公式參照其他包含空格,或甚至包含更棘手的隱藏空格的儲存格。這些空格可以讓儲存格「看起來」是空白的,但實際上它們「不是」空白的。

1. 選取參照的儲存格

選取的欄

尋找您的公式參照的儲存格並選取它們。在許多情況中,移除整欄的空格是很好的做法,因為您可以一次取代超過一個空格。在這個範例中,按一下 [E] 來選取整欄。

2. 尋找及取代

[常用] 索引標籤 > [尋找與選取] > [取代]

在 [常用] 索引標籤上,按一下 [尋找與選取] > [取代]。

3. 以空白取代空格

尋找含有空格的方塊,以空字串取代

在 [尋找目標] 方塊中,輸入單一空格。然後在 [取代為] 方塊中,刪除可能已存在的任何內容。

4. 取代或全部取代

[全部取代] 按鈕

如果您確定欄位中的所有空格都應該移除,請按一下 [全部取代]。如果您想要使用空白逐一個別取代空格,您可以先按一下 [找下一個],然後在您確定不需要空格時再按一下 [取代]。當您完成時,可能就會解決 #VALUE! 錯誤。如果沒有,請移至下一個步驟。

5. 開啟篩選

[常用] > [排序與篩選] > [篩選]

有時候空格以外的其他隱藏字元可能會導致儲存格「看起來」空白,但它並不是「真的」空白。儲存格內的單一單引號就會造成這個問題。若要在欄位中排除這些字元,請移至 [常用] > [排序與篩選] > [篩選] 來開啟篩選。

6. 設定篩選

[篩選] 功能表中未選取 [全選] 核取方塊、已選取 [(空格)] 核取方塊

按一下篩選箭號 篩選箭號 ,然後取消選取 [全選]。然後,選取 [空格] 核取方塊。

7. 選取任何未命名的核取方塊

已選取未命名的核取方塊

選取任何旁邊沒有任何內容的核取方塊,就像這個核取方塊一樣。

8.選取空白的儲存格,然後刪除

已選取經過篩選的空白儲存格

當 Excel 重新顯示空白的儲存格時,請選取它們。然後按 [刪除] 鍵。這將會清除儲存格中任何隱藏的字元。

9.清除篩選

[篩選] 功能表、清除篩選 ...

按一下篩選箭號 篩選箭號 ,然後按一下 [清除篩選...],就能夠看到所有儲存格。

10.結果

#VALUE! 錯誤消失,並以公式結果取代。儲存格 E4 中的綠色矩形

如果空格是導致發生 #VALUE! 錯誤的罪魁禍首,那麼希望您的錯誤已經由公式結果取代,如我們這裡的範例中所示。如果沒有,請為您的公式所參照的其他儲存格重複這個程序。或者,嘗試此頁面上的其他解決方案。

附註: 在這個範例中,請注意儲存格 E4 有一個綠色三角形,且數字是靠左對齊。這表示數字是儲存為文字。這可能會在之後導致發生更多問題。如果您看到這個問題,我們建議將儲存成文字的數值轉換成數值

儲存格內的文字或特殊字元可能會導致發生 #VALUE! 錯誤。但是有時候很難看到哪些儲存格中有這些問題。解決方案:使用 ISTEXT 函數來檢查儲存格。請注意,ISTEXT 並不會解決錯誤,它只能找出可能導致發生錯誤的儲存格。

#VALUE! 的範例

H4 為 =E2+E3+E4+E5 且結果為 #VALUE!

以下有 #VALUE! 錯誤的公式範例:似乎是因為儲存格 E2 而發生這個錯誤。在 "00" 之後有一個顯示為小方塊的特殊字元。或如下一個圖片所示,您可以在個別欄位中使用 ISTEXT 函數來檢查文字。

相同範例,使用 ISTEXT

儲存格 F2 為 =ISTEXT(E2) 且結果為 TRUE

以下是在 F 欄中加上 ISTEXT 函數。除了包含 TRUE 值的儲存格之外,所有儲存格都沒問題。這表示儲存格 E2 中有文字。若要解決這個問題,您可以刪除儲存格的內容並重新輸入 1865.00 的值。或是您也可以使用 CLEAN 函數來清除字元,或使用 REPLACE 函數 來使用其他值取代特殊字元。

使用 CLEAN 或 REPLACE 之後,您可以複製結果,然後使用 [常用] > [貼上] > [選擇性貼上] > [值]。您也可能必須將儲存成文字的數值轉換成數值

使用像 + 與 * 等數學運算子的公式,可能無法計算含有文字或空格的儲存格。在此情況下,請嘗試改為使用函數。函數通常會忽略文字值,並將所有值視為數字來運算,以排除 #VALUE! 錯誤。例如,不使用 =A2+B2+C2,而是輸入 =SUM(A2:C2)。或,不使用 =A2*B2,而是輸入=PRODUCT(A2,B2)

嘗試其他解決方案

選取錯誤

儲存格 H4 有公式 =E2+E3+E4+E5 且結果為 #VALUE!

先選取含有 #VALUE! 錯誤的儲存格。

按一下 [公式] > [評估值公式]

[評估值公式] 對話方塊有 " "+E3+E4+E5

按一下 [公式] > [評估值公式] > [評估值]。Excel 就會逐步執行公式的每個部分。在這個例子中,公式 =E2+E3+E4+E5 會運算出錯,因為儲存格 E2 含有隱藏空格。您無法透過尋找方式在儲存格 E2 中看到此空格。但是,您可以在這裡看到此空格。它會顯示為 [" "]。

有時候您只想將 #VALUE! 錯誤取代為其他內容,例如您自己指定的文字、數字 0,或者空白儲存格。在此情況下,您可以在公式中加上 IFERROR 函數。IFERROR 會檢查是否有錯誤,如果有,就用您選擇的另一個數值取代。如果沒有錯誤,就會按照您的原始公式計算。IFERROR 只適用於 Excel 2007 和更新版本。舊版 Excel 可以使用 IF(ISERROR())

警告: IFERROR 將會隱藏任何錯誤,不只是 #VALUE! 錯誤。不建議隱藏錯誤,因為錯誤通常是有項目需要修正 (而非隱藏) 的指標。我們不建議使用此函數,除非您非常確定您的公式是以您想要的方式的運作。

包含 #VALUE! 的儲存格

儲存格 H4 為 =E2+E3+E4+E5 且結果為 #VALUE!

以下範例中,由於儲存格 E2 含有隱藏空格,因此公式傳回 #VALUE! 錯誤。

由 IFERROR 隱藏的錯誤

儲存格 H4 為 =IFERROR(E2+E3+E4+E5,"--")

以下是已在公式中加入 IFERROR 的相同公式。您可以將公式讀為:「計算公式,但如果有任何類型的錯誤,請使用兩個虛線取代它。」請注意,您也可以使用 [""] 來顯示空白,而不是顯示兩個虛線。或者您可以替換成您自己的文字,例如:[Total Error]。

但是您可以看出,IFERROR 並不會真的解決錯誤,只是將錯誤隱藏起來而已。所以請務必確定隱藏錯誤比修正錯誤有益再使用。

有時候您的資料連線可能無法使用。若要修正此問題,請還原資料連線,或盡可能考慮匯入資料。如果您無法存取連線,請要求活頁簿建立者為您建立一個新的檔案。在理想的狀況下,新的檔案中只會有值而沒有連線。建立者可以複製所有的儲存格,然後只貼上值,以達到此目的。若要只貼上值,可以按一下 [常用] > [貼上] > [選擇性貼上] > [值]。這能去除所有公式和連線,因此也會移除任何 #VALUE! 錯誤。

如果您此時仍不確定該如何處理,您可以在 Excel 社群論壇中搜尋類似問題,或自行張貼問題。

連結至 Excel 社群論壇

在 Excel 社群論壇張貼問題

請參閱

Excel 公式概觀

如何避免公式出錯

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×