找到 Excel 中隱藏的差異

如果您曾經處理複雜的財務工作表,尤其是與別人共同作業處理工作表時,您就會知道最炫目的工作表有時候隱藏了最令人難堪的錯誤。幸運的是,Excel 擁有很多工具和訣竅,幫助您確定資料及計算的正確性。

在此範例中,有些公式中的錯誤會計算出不對的結果。

具有隱藏差異的工作表

圖說文字 1 此儲存格的公式具有錯誤的儲存格範圍。[追蹤前導參照] 工具將清楚指出這個錯誤。

圖說文字 2 此值看起來正確,但是如果您檢視此頁上的所有公式,會發現一個輸入值覆寫了公式。

圖說文字 3 此儲存格具有錯誤的公式。Excel 利用 [錯誤檢查] 工具可以發現這類錯誤。

圖說文字 4 當公式具有無效的儲存格參照時,Excel 會顯示此參照。

工作表中會出現什麼錯誤呢?

會出現什麼樣的錯誤呢?要如何在浪費時間,或更糟的情況 ─ 送出有錯誤的工作表前,發現這些錯誤呢?

Excel 可以幫助您找出這些常見的工作表問題。

問題 範例 如何找出錯誤
公式中有無法辨識的文字 拼錯函數名稱 錯誤值 #NAME?
巢狀公式寫法錯誤 公式中括號位置錯誤 評估公式工具
計算時沒有包含所有資料 公式不包含後來新增的列 追蹤公式與儲存格之間的關聯
公式具有數種常見問題之一的問題 區域中不一致的公式 公式錯誤檢查程式

找到問題

若要尋找工作表中的差異,可以逐步執行五個步驟:

  1. 解決所有錯誤值。
  2. 以一次一個步驟的方式計算巢狀公式。
  3. 追蹤公式與儲存格之間的關聯。
  4. 使用「錯誤檢查」工具來檢視工作表是否有常見的公式問題。
  5. 檢視或列印所有公式。

解決所有錯誤值

先從明顯的錯誤值開始解決。當您輸入 Excel 無法評估結果的公式時,Excel 會在儲存格內顯示錯誤值。

顯示##### — 欄的寬度不夠,或使用負數的日期和時間

如果欄的寬度不足以顯示內容,可以加大欄寬,或讓資料適合欄寬。

  • 加大欄寬     拖曳欄名的右邊界,直到您想要的欄寬為止。

拖曳欄邊界

  • 加大欄寬以符合內容長度     按兩下欄名的右邊界。
  • 縮小內容以適合欄寬     選取欄,然後按一下 [格式] 功能表的 [儲存格]。在 [對齊方式] 索引標籤的 [文字控制] 下,選取 [縮小字型以適合欄寬] 核取方塊,然後按一下 [確定]
  • 變更格式     不同格式的內容可能會適合欄寬。例如,使用沒有貨幣符號或小數位數較少的格式。選取欄,然後按一下 [格式] 功能表的 [類別]。選取一個格式化樣式,視需要變更小數位數,然後按一下 [確定]

如果日期或時間是負數,檢查您的資料和公式。

  • 日期和時間必須是正值。
  • 如果在公式使用日期和時間,請確定您的公式是正確的。
  • 如果負值是正確結果,請將儲存格格式變更成非日期或非時間格式。選取儲存格,按一下 [格式] 功能表的 [儲存格],按一下 [數值] 索引標籤,然後選取一個不是日期或時間格式的格式。

顯示#VALUE! — 使用了錯誤的引數或運算元

使用「追蹤錯誤」工具檢查您的引數,例如儲存格參照、數字和 運算元 (運算元:公式中位於運算子兩邊的項目。在 Excel 中,運算元可以是值、儲存格參照、名稱、標籤及函數。)

  1. 按一下包含錯誤的儲存格。
  2. 按一下 [公式稽核] 工具列的 [追蹤錯誤]按鈕圖像

 附註   若沒有看到 [公式稽核] 工具列,指向 [檢視] 功能表的 [工具列],然後按一下 [公式稽核]

顯示#DIV/0! — 當一個數值除以零 (0) 時。

  • 檢查公式有無除以零 (0) 的除式 — 例如,=5/0。
  • 如果公式中有除數的儲存格參照到空白儲存格,或包含零的儲存格,執行下列動作之一:
    • 變更儲存格參照至另一個儲存格。
    • 在儲存格中輸入零以外的值來當作除數。
    • 在被參照的儲存格中輸入值 #N/A 作為除數,則公式的結果將會是 #N/A 而不是 #DIV/0!,表示沒有除數值。
    • 若要避免出現錯誤值,可使用 IF 工作表函數,僅在除數不為零 (0) 時來計算值。例如,若產生的公式為 =A5/B5,則使用 =IF(B5=0,"",A5/B5) 來代替。其中,兩個引號代表了一個空文字串。
  • 如果您在執行巨集,請確定巨集函數或公式中的除數不為零或空白的。

顯示#NAME? — 公式包含 Microsoft Excel 無法辨識的文字

檢查常見的可能原因:

顯示如果公式使用屬於「分析工具箱」增益集的函數,但該增益集尚未載入時,請安裝和載入「分析工具箱」增益集。

  1. 按一下 [工具] 功能表的 [增益集]
  2. [現有的增益集] 清單中,選取 [分析工具箱] 方塊,然後按一下 [確定]
  3. 如有需要,依循安裝程式上的指示動作。

顯示請確定該名稱確實存在。

指向 [插入] 功能表的 [名稱],然後按一下 [定義]

如果未列出名稱,請定義名稱:

  1. [現有名稱] 方塊中輸入公式名稱。
  2. [參照到] 方塊中鍵入 = (等號),接著輸入公式或 常數 (常數:不計算因而不變更的值。例如,數字 210 及文字 "Quarterly Earnings" 是常數。運算式或運算式得出的結果值不是常數。) 值。

顯示檢查名稱的拼字。

確認拼字。選取資料編輯列 資料編輯列 中的名稱,按 F3,按一下您想要使用的名稱,然後按一下 [確定]。如果沒有看到資料編輯列,按一下 [檢視] 功能表的 [資料編輯列]

顯示如果公式在其中使用標籤,確定可以使用標籤。

若要允許使用標籤,按一下 [工具] 功能表的 [選項],然後按一下 [計算] 索引標籤。在 [活頁簿選項] 底下,選取 [公式中允許使用標籤] 核取方塊。

顯示確定公式的拼法正確。

若要在公式中插入正確函數名稱,按一下 [插入] 功能表上的 [函數]

顯示將公式中的任何文字用雙引號標記圍住

Excel 嘗試將您輸入的東西解釋為名稱,即使您原先想要將它們當作文字使用。

顯示確保公式中的所有範圍參照都使用了冒號 (:)。

在範圍參照之間使用冒號 (:),例如,SUM(A1:C10)。

顯示檢查未以單引號標記包圍起來的工作表參照。

若公式中參照了其他工作表或活頁簿中的值或儲存格,且那些活頁簿或工作表的名稱中包含非字母字元或空格,那麼您必須用單引號 (') 將這個名稱括起來。

顯示#REF! — 公式包含無效的儲存格參照

  • 如果您刪除公式參照的儲存格,或是在儲存格中貼上其他內容,請變更公式,或是在刪除或貼上儲存格後,立即按一下 [復原]按鈕圖像 ,還原工作表上的這些儲存格。
  • 確定公式連結到的所有程式都在執行中。
  • 如果連結到「動態資料交換」(DDE) 主題,請確定您使用正確的 DDE 主題。
  • 檢查任何巨集函數,看看引數是否參照到無效的儲存格或無效的儲存格範圍。

以一次一個步驟的方式計算巢狀公式

仔細檢查複雜的巢狀公式,方法是依照計算的順序來評估不同部分的公式。

  1. 選取您要評估的儲存格,一次只能評估一個儲存格。
  2. 指向 [工具] 功能表的 [公式稽核] 功能表,然後按一下 [評估值公式]
  3. 按一下 [評估] 來檢查加底線參照的值。評估結果以斜體顯示。如果公式中加底線的部份是另一個公式的參照,按一下 [逐步執行],在 [評估] 方塊顯示其他的公式。按一下 [跳出] 返回先前的儲存格與公式。
  4. 繼續作業,直到公式的每一個部份都評估完畢。
  5. 若要重新檢視評估,按一下 [重新啟動]。若要結束評估,按一下 [關閉]

 附註    參照第二次出現在公式中,或公式參照不同活頁簿中的儲存格時,[逐步執行] 按鈕就無法使用。

追蹤公式與儲存格之間的關聯

您可以追蹤公式的前導參照儲存格和從屬參照儲存格,簡單地就看出資料的來源及去向。例如,您可以一眼就看出「飲料銷售額」沒有包含在「總銷售額淨值」的公式內。

公式參照不完整的範圍

顯示追蹤提供資料給公式的儲存格 (前導參照)

  1. 選取含有您想要尋找的前導參照儲存格的公式之儲存格。
  2. 按一下 [公式稽核] 工具列上的 [追蹤前導參照]按鈕圖像  。

 附註 

  • 若沒有看到 [稽核工具列],指向 [工具] 功能表的 [公式稽核],然後按一下 [顯示稽核工具列]
  • 如果 [公式稽核] 工具列上沒有 [追蹤前導參照],請執行下列操作:
    1. 按一下 [工具] 功能表的 [選項],然後按一下 [檢視] 索引標籤。
    2. 確定 [物件] 下的 [全部顯示][僅顯示預留位置] 已選取。
  1. 若要識別提供資料給作用儲存格的下一層級儲存格,再按一下 [追蹤前導參照]按鈕圖像 。 
  2. 若要從離作用儲存格最遠的前導參照儲存格開始,一次移除一層追蹤箭號,按一下 [移除前導參照箭號] 移除前導參照箭號 。若要移除另一層級的追蹤箭號,再按一下按鈕。

顯示追蹤參照特定儲存格的公式 (從屬參照)

  1. 選取要識別為從屬參照儲存格之儲存格。
  2. 按一下 [公式稽核] 工具列上的 [追蹤從屬參照]按鈕圖像

 附註 

  • 若沒有看到 [稽核工具列],指向 [工具] 功能表上的 [公式稽核],然後按一下 [顯示稽核工具列]
  • 如果 [公式稽核] 工具列上沒有 [追蹤從屬參照] ,請執行下列操作:
    1. 按一下 [工具] 功能表的 [選項],然後按一下 [檢視] 索引標籤。
    2. 確定 [物件] 下的 [全部顯示][僅顯示預留位置] 已選取。
  1. 若要識別從屬於作用儲存格的下一級儲存格,再按一下 [追蹤從屬參照]按鈕圖像
  2. 若要從離作用儲存格最遠的從屬參照儲存格開始,一次移除一層追蹤箭號,按一下 [移除從屬參照箭號]按鈕圖像 。若要移除另一層級的追蹤箭號,再按一下按鈕。
  3. 若要移除工作表中所有的追蹤箭號,按一下 [公式稽核] 工具列的 [移除所有箭號]按鈕圖像

使用「錯誤檢查」工具來檢查工作表是否有常見的公式問題

不論拼字和文法檢查程式為文書處理作了什麼工作,Excel 中的「錯誤檢查」工具就為工作表作了什麼工作。「錯誤檢查」工具使用特定規則來檢查公式中的問題。這些規則無法保證工作表中完全沒有錯誤,但是它們可以檢查常見錯誤。在此範例中,「錯誤檢查」工具會找出不一致的公式。

不一致的公式

  1. 選取您要檢查錯誤的工作表。
  2. 如果工作表是手動計算,請按 F9 立即重新計算。
  3. 按一下 [工具] 功能表的 [錯誤檢查]。如果「錯誤檢查」工具發現可能發生的錯誤,會開啟 [錯誤檢查] 對話方塊。
  4. 如果您先前曾忽略錯誤,而想重新檢查該錯誤,請依序按一下 [選項][重設被忽略的錯誤][確定],然後按一下 [繼續]
  5. 對話方塊中被參照的儲存格會醒目顯示,而且有問題的公式會顯示在資料編輯列 資料編輯列 中。對話方塊中的文字會描述檢查工具所發現的問題。
  6. 按一下對話方塊右邊的按鈕。每種類型的問題都會出現不同的選項。如果您按一下 [忽略錯誤],該問題於後續檢查時都會標示為忽略。
  7. [下一步]
  8. 繼續檢查直到錯誤檢查完成。

顯示我如何變更 Excel 會檢查的常見問題呢?

  1. 按一下 [工具] 功能表的 [選項],然後按一下 [錯誤檢查] 索引標籤。
  2. 選取或清除您需要選項的核取方塊。

檢視或列印所有公式

您可以看到工作表中的所有公式,並列印出來以供日後參考。檢視公式可幫助您找出不正確的公式。在此範例中,您可立即看到公式已經不小心由一個值覆寫。

公式不小心被值覆寫

若要顯示所有公式:

  1. 指向 [工具] 功能表的 [公式稽核],然後按一下 [公式稽核模式]按鈕圖像
  2. 按一下 [檔案] 功能表的 [列印]
  3. [列印內容] 下選取列印選取範圍、使用中工作表,或整個活頁簿的選項。

[監看視窗] 工具列

[監看視窗] 工具列是另一個可以幫助您找到錯誤及不想要結果的工具。

透過 [監看視窗] 工具列,即使儲存格不在您視線範圍內,您還是可以在工作時監看儲存格及其公式。監看儲存格可以 幫助您更快地發覺出不想要的結果或錯誤。

  1. 選取想要監看的儲存格。
  2. 指向 [工具] 功能表的 [公式稽核],然後按一下 [顯示監看視窗]
  3. 按一下 [新增監看式]按鈕圖像
  4. 按一下 [新增]
  5. 移動 [監看視窗] 工具列至視窗上方、底部、左邊或右邊。

若要變更欄寬,拖曳欄名右邊的邊界。

若要顯示 [監看視窗] 工具列上之項目所參照的儲存格,按兩下該項目。

監看工作表上所有具有公式的儲存格

若要於選取工作表上具有公式的所有儲存格,按一下 [編輯] 功能表的 [到],按一下 [特殊],然後按一下 [公式]

檢視有連結到其他活頁簿的儲存格

若要查看有連結到其他活頁簿的儲存格,請開啟其他活頁簿。

 
 
適用:
Excel 2003