檢測和解決Excel公式中問題的工具和方法

一、使用公式錯(cuò)誤檢查器

如果 Excel 檢測到單元格中包含錯(cuò)誤，當(dāng)單擊該單元格時(shí)，單元格附件將顯示錯(cuò)誤檢查器圖標(biāo) 。單擊該圖標(biāo)，彈出如圖1所示的菜單，其中包含用于錯(cuò)誤檢查與處理的相關(guān)命令。

圖1　錯(cuò)誤檢查器菜單

彈出菜單頂部顯示的是錯(cuò)誤的類型(“數(shù)字錯(cuò)誤”)，菜單中其他命令的含義如下：

1、關(guān)于此錯(cuò)誤的幫助 ：選擇該選項(xiàng)，將打開幫助窗口，并顯示相應(yīng)的錯(cuò)誤幫助主題。

2、 顯示計(jì)算步驟 ：選擇該選項(xiàng)，可通過分步計(jì)算檢查發(fā)生錯(cuò)誤的位置。

3、 忽略錯(cuò)誤 ：選擇該選項(xiàng)，將不理會發(fā)生的錯(cuò)誤，保留公式的當(dāng)前值。

4、 在編輯欄中編輯 ：選擇該選項(xiàng)，將允許用戶直接在公式欄中修改公式錯(cuò)誤，在狀態(tài)欄中顯示“編輯”字樣。

5、錯(cuò)誤檢查選項(xiàng) ：選擇該選項(xiàng)，可設(shè)置 Excel 對錯(cuò)誤的檢查規(guī)則。根據(jù)實(shí)際情況，用戶可以選擇上述命令來解決出現(xiàn)的問題。

二、定位特定類型的數(shù)據(jù)

如果要檢查工作表中的某一類數(shù)據(jù)，可以使用【定位條件】對話框。按【F5】鍵打開【定位】對話框，單擊【定位條件】按鈕，打開【定位條件】對話框。根據(jù)需要選擇不同的選項(xiàng)，單擊【確定】按鈕后，系統(tǒng)會自動選中當(dāng)前工作表中符合指定類型的所有單元格。例如，可以選擇【公式】，然后再選擇公式下的【錯(cuò)誤】，定位工作表中所有出現(xiàn)錯(cuò)誤的公式，如圖2所示。

圖2 使用定位條件快速定位指定類型的數(shù)據(jù)

三、跟蹤單元格關(guān)系

由于大部分公式都涉及單元格的引用，而很多公式的問題都是由單元格引用引起的。為此，用戶可以使用 Excel 的追蹤功能來發(fā)現(xiàn)到底是公式中的哪些單元格引起的公式錯(cuò)誤。追蹤錯(cuò)誤單元格時(shí)，需要了解以下 3 個(gè)概念 ：

1、引用單元格 ：被其他單元格中的公式引用的單元格。例如，單元格 B1 中包含公式“=A1+A2”，單元格 A1 和 A2 就是單元格 B1 的引用單元格，更確切地講是直接引用單元格。如果上面公式的單元格 A1 中又包含公式“=A3*A4”，那么單元格 A3 和A4 就是單元格 B1 的間接引用單元格。

2、從屬單元格 ：從屬單元格中包含引用其他單元格的公式。例如，單元格 B1 中包含公式“=A1+A2”，那么單元格 B1 就是單元格 A1 和 A2 的從屬單元格。

3、錯(cuò)誤單元格 ：在公式中直接或間接引用的、包含錯(cuò)誤的單元格。要追蹤為公式提供數(shù)據(jù)的單元格(引用單元格)，需要先單擊包含公式的單元格，然后單擊功能區(qū)中的【公式】、【公式審核】、【追蹤引用單元格】按鈕。這樣將顯示所有為該公式提供數(shù)據(jù)的單元格，并使用箭頭由這些單元格指向包含公式的單元格，如圖3所示。

圖3　追蹤直接引用單元格

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【公式審核】、【追蹤引用單元格】命令來完成相同的操作。

再次選擇【追蹤引用單元格】命令時(shí)，將顯示間接引用單元格，如圖4所示。

圖4　追蹤間接引用單元格

當(dāng)公式引用了當(dāng)前工作表外的其他單元格時(shí)，Excel 將顯示虛線箭頭和工作表圖標(biāo)，如圖5所示。

圖5　追蹤工作表外的單元格

單擊要查看包含從屬單元格的單元格，然后選擇【公式】、【公式審核】、【追蹤從屬單元格】命令。Excel 將從當(dāng)前單元格指向其從屬的單元格，如圖6所示。

圖6　追蹤從屬單元格

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【公式審核】、【追蹤從屬單元格】命令來完成相同的操作。

當(dāng)公式出現(xiàn)錯(cuò)誤時(shí)，可以單擊包含錯(cuò)誤的單元格，然后選擇【公式】、【公式審核】、【錯(cuò)誤檢查】、【追蹤錯(cuò)誤】命令，Excel 會自動指向出錯(cuò)單元格，如圖7所示。

圖7　追蹤有錯(cuò)誤的單元格

注意：在追蹤錯(cuò)誤時(shí)，紅色箭頭表示有錯(cuò)誤的單元格，藍(lán)色箭頭表示無錯(cuò)誤的單元格。在上圖中，出錯(cuò)單元格為C3。

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【公式審核】、【追蹤錯(cuò)誤】命令來完成相同的操作。

四、監(jiān)視單元格內(nèi)容

單元格監(jiān)視一般用于追蹤距離較遠(yuǎn)的單元格，例如位于跨工作表的單元格。設(shè)置單元格監(jiān)視的具體操作如下 ：

步驟1：單擊要監(jiān)視的包含公式的單元格，然后單擊功能區(qū)中的【公式】、【公式審核】、【監(jiān)視窗口】按鈕。

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【公式審核】、【顯示監(jiān)視窗口】命令來完成相同的操作。

步驟2：打開【監(jiān)視窗口】對話框，選擇【添加監(jiān)視】命令，打開【添加監(jiān)視點(diǎn)】對話框，在文本框中輸入或者直接單擊工作表中要進(jìn)行監(jiān)視的單元格，如圖8所示。

圖8　添加要監(jiān)視的單元格

步驟3：單擊【添加】按鈕，Excel 將對選擇的單元格進(jìn)行監(jiān)視，如圖9 所示。選擇監(jiān)控對象，然后選擇【刪除監(jiān)視】命令，可刪除所選監(jiān)視。

圖9　查看被監(jiān)視單元格的詳細(xì)情況

五、使用公式求值器

如果出錯(cuò)的公式很復(fù)雜，由多個(gè)計(jì)算步驟組成，那么查找錯(cuò)誤根源時(shí)會比較費(fèi)力。使用 Excel 的分步計(jì)算功能可以將復(fù)雜計(jì)算過程分解為單步計(jì)算，找到錯(cuò)誤出在哪里，以便快速解決公式問題。要進(jìn)行分步計(jì)算，需要先單擊出錯(cuò)公式所在的單元格，然后單擊功能區(qū)中的【公式】、【公式審核】、【公式求值】按鈕，打開【公式求值】對話框，如圖10所示。

圖10　【公式求值】對話框

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【公式審核】、【公式求值】命令來完成相同的操作。

帶有下劃線的內(nèi)容表示當(dāng)前準(zhǔn)備計(jì)算的公式，單擊【求值】按鈕，得到下劃線部分的計(jì)算結(jié)果，如圖11 所示。繼續(xù)單擊【求值】按鈕，直到獲得整個(gè)公式的最終結(jié)果。計(jì)算后可以單擊【重新啟動】按鈕，重新進(jìn)行分步計(jì)算。

圖11　計(jì)算第一部分公式后的結(jié)果

在【公式求值】對話框中還有兩個(gè)按鈕—【步入】和【步出】。當(dāng)要求值的公式包含從屬單元格時(shí)，【步入】按鈕將變?yōu)榭捎?。單擊該按鈕，將顯示當(dāng)前單元格的第一個(gè)從屬單元格。如果該單元格還有從屬單元格，還可以再次單擊【步入】按鈕，顯示間接從屬單元格。如果不想顯示從屬單元格，可以單擊【步出】按鈕。