Excel 資料分析 - 公式審計



您可能需要檢查公式的準確性或查詢錯誤的來源。Excel 公式審計命令提供了一種簡單的方法來查詢

  • 哪些單元格參與了活動單元格中公式的計算。
  • 哪些公式引用了活動單元格。

這些查詢結果以箭頭線的方式圖形化顯示,使視覺化變得更容易。您可以使用單個命令顯示活動工作表中的所有公式。如果您的公式引用了不同工作簿中的單元格,請開啟該工作簿。Excel 無法轉到未開啟的工作簿中的單元格。

設定顯示選項

您需要檢查正在使用的所有工作簿的顯示選項是否設定正確。

  • 單擊檔案 > 選項
  • 在 Excel 選項對話方塊中,單擊高階。
  • 在工作簿的顯示選項中 -
    • 選擇工作簿。
    • 檢查“對於物件,顯示”下是否選擇了“全部”。
  • 對要審計的所有工作簿重複此步驟。
Setting the Display Options

跟蹤前導單元格

前導單元格是指活動單元格中的公式引用的那些單元格。

在以下示例中,活動單元格為 C2。在 C2 中,您有公式=B2*C4

B2 和 C4 是 C2 的前導單元格。

Tracing Precedents

要跟蹤單元格 C2 的前導單元格,

  • 單擊單元格 C2。
  • 單擊公式選項卡。
  • 單擊“公式審計”組中的“跟蹤前導單元格”。
Trace Precedents

將顯示兩條箭頭,一條從 B2 到 C2,另一條從 C4 到 C2,跟蹤前導單元格。

Two Arrows Displayed

請注意,要跟蹤單元格的前導單元格,該單元格應包含具有有效引用的公式。否則,您將收到錯誤訊息。

  • 單擊不包含公式的單元格或單擊空單元格。
  • 單擊“公式審計”組中的“跟蹤前導單元格”。

您將收到一條訊息。

Get Message

移除箭頭

單擊“公式審計”組中的“移除箭頭”。

Removing Arrows

工作表中的所有箭頭都將消失。

跟蹤後繼單元格

後繼單元格包含引用其他單元格的公式。這意味著,如果活動單元格參與了另一個單元格中的公式計算,則另一個單元格是活動單元格的後繼單元格。

在下面的示例中,C2 有公式=B2*C4。因此,C2 是單元格 B2 和 C4 的後繼單元格。

Tracing Dependents

要跟蹤單元格 B2 的後繼單元格,

  • 單擊單元格 B2。
  • 單擊公式選項卡。
  • 單擊“公式審計”組中的“跟蹤後繼單元格”。
Trace Dependents in Formula Auditing

將顯示一條從 B2 到 C2 的箭頭,表明 C2 依賴於 B2。

要跟蹤單元格 C4 的後繼單元格 -

  • 單擊單元格 C4。
  • 單擊公式選項卡 > “公式審計”組中的“跟蹤後繼單元格”。

將顯示另一條從 C4 到 C2 的箭頭,表明 C2 也依賴於 C4。

Trace Dependents of Cell

單擊“公式審計”組中的“移除箭頭”。工作表中的所有箭頭都將消失。

注意 - 要跟蹤單元格的後繼單元格,該單元格應被另一個單元格中的公式引用。否則,您將收到錯誤訊息。

  • 單擊單元格 B6,它沒有被任何公式引用,或者單擊任何空單元格。
  • 單擊“公式審計”組中的“跟蹤後繼單元格”。您將收到一條訊息。
Click Trace Dependents

使用公式

您已經瞭解了前導單元格和後繼單元格的概念。現在,考慮一個包含多個公式的工作表。

Working with Formulae
  • 單擊“考試成績”表中“及格類別”下方的單元格。
  • 單擊“跟蹤前導單元格”。其左側的單元格(分數)和區域 E4:F8 將被對映為前導單元格。
  • 對“考試成績”表中“及格類別”下方的所有單元格重複此操作。
Exam Results Table
  • 單擊“學生成績”表中“及格類別”下方的單元格。

  • 單擊“跟蹤後繼單元格”。“考試成績”表中“及格類別”下方的所有單元格都將被對映為後繼單元格。

Student Grades Table

顯示公式

下面的工作表包含了銷售人員在東、北、南、西四個區域的銷售彙總。

Showing Formulas
  • 單擊功能區上的“公式”選項卡。

  • 單擊“公式審計”組中的“顯示公式”。工作表中的公式將顯示出來,以便您知道哪些單元格包含公式以及公式是什麼。

Show Formula
  • 單擊“總銷售額”下方的單元格。

  • 單擊“跟蹤前導單元格”。工作表圖標出現在箭頭的末端。工作表圖標表示前導單元格在不同的工作表中。

Click Trace Precedents

雙擊箭頭。“轉到”對話框出現,顯示前導單元格。

Go To Dialog Box

如您所見,有四個前導單元格,分佈在四個不同的工作表中。

  • 單擊其中一個前導單元格的引用。
  • 該引用將顯示在“引用”框中。
  • 單擊“確定”。包含該前導單元格的工作表將顯示。

評估公式

要逐步瞭解單元格中複雜公式的工作原理,可以使用“評估公式”命令。

考慮單元格 C14 中的公式 NPV(中間年份)。該公式為

=SQRT (1 + C2)*C10

  • 單擊單元格 C14。
  • 單擊功能區上的“公式”選項卡。
  • 單擊“公式審計”組中的“評估公式”。“評估公式”對話框出現。
Evaluating Formula

在“評估公式”對話方塊中,公式顯示在“評估”下的框中。透過多次單擊“評估”按鈕,公式將逐步進行評估。帶下劃線的表示式將始終是下一步執行的表示式。

Evaluate Formula

這裡,公式中 C2 帶有下劃線。因此,它將在下一步進行評估。單擊“評估”。

Click Evaluate Button

單元格 C2 的值為 0.2。因此,C2 將被評估為 0.2。“1+0.2”帶下劃線,表示它是下一步。單擊“評估”。

Click Evaluate

1+0.2 將被評估為 1.2。“SQRT(1.2)”帶下劃線,表示它是下一步。單擊“評估”。

Evaluate

SQRT(1.2) 將被評估為 1.09544511501033。“C10”帶下劃線,表示它是下一步。單擊“評估”。

Evaluate SQRT

C10 將被評估為 4976.8518518515。

1.09544511501033*4976.8518518515 帶下劃線,表示它是下一步。單擊“評估”。

Restart Button

1.09544511501033*4976.8518518515 將被評估為 5,451.87。

沒有更多表達式需要評估,這就是答案。“評估”按鈕將更改為“重新開始”按鈕,表示評估已完成。

錯誤檢查

在工作表和/或工作簿準備好進行計算後,進行錯誤檢查是一個好習慣。

考慮以下簡單的計算。

Error Checking

單元格中的計算導致錯誤 #DIV/0!。

  • 單擊單元格 C5。

  • 單擊功能區上的“公式”選項卡。

  • 單擊“公式審計”組中“錯誤檢查”旁邊的箭頭。在下拉列表中,您會發現“迴圈引用”已停用,表示您的工作表中沒有迴圈引用。

  • 從下拉列表中選擇“跟蹤錯誤”。

Select Trace Error

計算活動單元格所需的單元格以藍色箭頭表示。

Activate Cell
  • 單擊“移除箭頭”。
  • 單擊“錯誤檢查”旁邊的箭頭。
  • 從下拉列表中選擇“錯誤檢查”。
Select Error Checking

“錯誤檢查”對話框出現。

Error Checking Dialog Box

觀察以下內容 -

  • 如果單擊“有關此錯誤的幫助”,將顯示 Excel 有關該錯誤的幫助。

  • 如果單擊“顯示計算步驟”,將出現“評估公式”對話方塊。

  • 如果單擊“忽略錯誤”,“錯誤檢查”對話方塊將關閉,如果再次單擊“錯誤檢查”命令,它將忽略此錯誤。

  • 如果單擊“在公式欄中編輯”,您將轉到公式欄中的公式,以便您可以編輯單元格中的公式。

廣告