如何修正 #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 公式概觀

如何避免公式出錯

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×