避免Excel公式中常見錯(cuò)誤的方法和技巧

一、括號(hào)不匹配

一個(gè)最為常見的錯(cuò)誤是在輸入公式并按下【Enter】鍵后，收到 Excel 的錯(cuò)誤信息，同時(shí)公式不允許被輸入到單元格中。該錯(cuò)誤的主要原因是用戶的誤操作，即用戶只輸入了左括號(hào)或右括號(hào)。但是如果用戶輸入函數(shù)后只輸入了左括號(hào)，那么在按下【Enter】鍵后，Excel 會(huì)自動(dòng)補(bǔ)齊缺少的右括號(hào)，并在單元格中顯示公式的結(jié)果。

二、以#號(hào)填充單元格

有時(shí)輸入公式后，單元格會(huì)被 # 號(hào)填充。出現(xiàn)這種情況可能有以下原因 ：

1、列寬不足以容納單元格中的內(nèi)容。

2、單元格包含一個(gè)無(wú)效日期或時(shí)間。

第一個(gè)問題很好解決，只需加大列寬即可。第二個(gè)問題則需要檢查公式中的日期是否在 Excel 的有效范圍之內(nèi)。Excel 不支持 1900 年以前的日期或負(fù)的時(shí)間值。

三、空白但非空的單元格

有些單元格中看似并無(wú)任何內(nèi)容，但是使用 ISBLANK 函數(shù)或 COUNTA 函數(shù)進(jìn)行判斷或統(tǒng)計(jì)時(shí)，這些看似空白的單元格仍被計(jì)算在內(nèi)。例如，將下面的公式輸入單元格B1中，用于判斷單元格A1是否包含內(nèi)容，如果包含內(nèi)容，則返回“有內(nèi)容”，否則返回空字符串。

=IF(A1<>"","有內(nèi)容","")

當(dāng)單元格 A1 無(wú)任何內(nèi)容時(shí)，單元格 B1 顯示空白。用戶也許會(huì)認(rèn)為單元格 B1 是空的，但其實(shí)不是。如果使用 ISBLANK 函數(shù)測(cè)試，就會(huì)發(fā)現(xiàn)該函數(shù)返回 FALSE，說明單元格 B1 非空，如圖1所示。

圖1　看似空白其實(shí)非空的單元格

四、顯示值與實(shí)際值

如圖2所示，將單元格 A1、A2、A3 中的值設(shè)置為保留 5 位小數(shù)，然后在單元格A4中輸入一個(gè)求和公式，用于計(jì)算單元格A1:A3的總和，但是發(fā)現(xiàn)得到了錯(cuò)誤的結(jié)果。

圖2　顯示的合計(jì)值不正確

這是由于公式使用的是區(qū)域 A1:A3 中的真實(shí)值而非顯示值所致。用戶可以在【Excel 選項(xiàng)】對(duì)話框【高級(jí)】選項(xiàng)卡中選中【將精度設(shè)為所顯示的精度】復(fù)選框，如圖3 所示。此后 Excel 將使用顯示值進(jìn)行計(jì)算。

圖3　選擇【將精度設(shè)為所顯示的精度】復(fù)選框使用顯示值進(jìn)行計(jì)算

注意：該功能務(wù)必謹(jǐn)慎使用。因?yàn)樗矔?huì)影響輸入到單元格中的值。如果單元格包含1.68，通過設(shè)置單元格格式后顯示為2。當(dāng)開啟該功能后，該單元格的值就是2，即使關(guān)閉該功能，單元格的值也無(wú)法恢復(fù)到1.68。

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【選項(xiàng)】命令，然后在打開的【選項(xiàng)】對(duì)話框的【重新計(jì)算】選項(xiàng)卡中進(jìn)行設(shè)置。

五、返回錯(cuò)誤值

沒有任何人能夠保證在 Excel 中輸入的公式永遠(yuǎn)正確，當(dāng)出現(xiàn)問題時(shí)，應(yīng)該首先了解導(dǎo)致問題的大致原因，以便找到問題解決的方法。

六、循環(huán)引用

如果單元格的公式中引用了公式所在的單元格，當(dāng)按下【Enter】鍵輸入公式時(shí)，會(huì)彈出如圖4所示的對(duì)話框，表示當(dāng)前公式正在循環(huán)引用其自身。

圖4　出現(xiàn)循環(huán)引用時(shí)彈出的提示信息

單擊【確定】按鈕，公式返回 0，然后可以重新編輯公式，以便解決公式循環(huán)引用的問題。如果公式中包含了間接循環(huán)引用，Excel 將會(huì)使用箭頭標(biāo)記，以便指出產(chǎn)生循環(huán)引用的根源在哪兒。

大多數(shù)情況下，循環(huán)引用是一種公式錯(cuò)誤。然而，有時(shí)也可以利用循環(huán)引用來巧妙地解決一些問題。如果需要使用循環(huán)引用，則首先要開啟迭代計(jì)算功能 ：

步驟1：?jiǎn)螕?Excel 窗口左上角的【文件】按鈕，在彈出菜單中選擇【選項(xiàng)】命令，打開【Excel 選項(xiàng)】對(duì)話框。

步驟2：選擇【公式】選項(xiàng)卡，在右側(cè)選中【啟用迭代計(jì)算】復(fù)選框，如圖5所示。根據(jù)需要修改【最多迭代次數(shù)】中的數(shù)字，該數(shù)字表示要進(jìn)行循環(huán)計(jì)算的次數(shù)。用戶可以通過指定【最大誤差】來控制迭代計(jì)算的精確度，數(shù)字越小，則說明要求的精確度越高。

圖5　開啟迭代計(jì)算功能

Excel版本提醒

如果是在 Excel 2003 中操作，需要單擊菜單欄中的【工具】、【選項(xiàng)】命令，然后在打開的【選項(xiàng)】對(duì)話框的【重新計(jì)算】選項(xiàng)卡中進(jìn)行設(shè)置。