
- Excel 資料分析教程
- Excel 資料分析 - 首頁
- 資料分析 - 概述
- 資料分析 - 流程
- Excel 資料分析 - 概述
- 使用單元格區域名稱
- 表格
- 使用文字函式清理資料
- 清理包含日期值的資料
- 使用時間值
- 條件格式
- 排序
- 篩選
- 使用區域計算小計
- 快速分析
- 查詢函式
- 資料透視表
- 資料視覺化
- 資料驗證
- 財務分析
- 使用多個工作表
- 公式審計
- 查詢
- 高階資料分析
- 高階資料分析 - 概述
- 資料整合
- 假設分析
- 使用資料表進行假設分析
- 假設分析方案管理器
- 使用目標求解進行假設分析
- 使用 Excel 求解器進行最佳化
- 將資料匯入 Excel
- 資料模型
- 使用資料透視表探索資料
- 使用 Power Pivot 探索資料
- 使用 Power View 探索資料
- 探索 Power View 圖表
- 探索 Power View 地圖
- 探索 Power View 多個檢視
- 探索 Power View 磁貼
- 使用層次結構探索資料
- 美化 Power View 報表
- 關鍵績效指標
- Excel 資料分析資源
- Excel 資料分析 - 快速指南
- Excel 資料分析 - 資源
- Excel 資料分析 - 討論
Excel 資料分析 - 公式審計
您可能需要檢查公式的準確性或查詢錯誤的來源。Excel 公式審計命令提供了一種簡單的方法來查詢
- 哪些單元格參與了活動單元格中公式的計算。
- 哪些公式引用了活動單元格。
這些查詢結果以箭頭線的方式圖形化顯示,使視覺化變得更容易。您可以使用單個命令顯示活動工作表中的所有公式。如果您的公式引用了不同工作簿中的單元格,請開啟該工作簿。Excel 無法轉到未開啟的工作簿中的單元格。
設定顯示選項
您需要檢查正在使用的所有工作簿的顯示選項是否設定正確。
- 單擊檔案 > 選項。
- 在 Excel 選項對話方塊中,單擊高階。
- 在工作簿的顯示選項中 -
- 選擇工作簿。
- 檢查“對於物件,顯示”下是否選擇了“全部”。
- 對要審計的所有工作簿重複此步驟。

跟蹤前導單元格
前導單元格是指活動單元格中的公式引用的那些單元格。
在以下示例中,活動單元格為 C2。在 C2 中,您有公式=B2*C4。
B2 和 C4 是 C2 的前導單元格。

要跟蹤單元格 C2 的前導單元格,
- 單擊單元格 C2。
- 單擊公式選項卡。
- 單擊“公式審計”組中的“跟蹤前導單元格”。

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

請注意,要跟蹤單元格的前導單元格,該單元格應包含具有有效引用的公式。否則,您將收到錯誤訊息。
- 單擊不包含公式的單元格或單擊空單元格。
- 單擊“公式審計”組中的“跟蹤前導單元格”。
您將收到一條訊息。

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

工作表中的所有箭頭都將消失。
跟蹤後繼單元格
後繼單元格包含引用其他單元格的公式。這意味著,如果活動單元格參與了另一個單元格中的公式計算,則另一個單元格是活動單元格的後繼單元格。
在下面的示例中,C2 有公式=B2*C4。因此,C2 是單元格 B2 和 C4 的後繼單元格。

要跟蹤單元格 B2 的後繼單元格,
- 單擊單元格 B2。
- 單擊公式選項卡。
- 單擊“公式審計”組中的“跟蹤後繼單元格”。

將顯示一條從 B2 到 C2 的箭頭,表明 C2 依賴於 B2。
要跟蹤單元格 C4 的後繼單元格 -
- 單擊單元格 C4。
- 單擊公式選項卡 > “公式審計”組中的“跟蹤後繼單元格”。
將顯示另一條從 C4 到 C2 的箭頭,表明 C2 也依賴於 C4。

單擊“公式審計”組中的“移除箭頭”。工作表中的所有箭頭都將消失。
注意 - 要跟蹤單元格的後繼單元格,該單元格應被另一個單元格中的公式引用。否則,您將收到錯誤訊息。
- 單擊單元格 B6,它沒有被任何公式引用,或者單擊任何空單元格。
- 單擊“公式審計”組中的“跟蹤後繼單元格”。您將收到一條訊息。

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

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

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

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

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

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

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

如您所見,有四個前導單元格,分佈在四個不同的工作表中。
- 單擊其中一個前導單元格的引用。
- 該引用將顯示在“引用”框中。
- 單擊“確定”。包含該前導單元格的工作表將顯示。
評估公式
要逐步瞭解單元格中複雜公式的工作原理,可以使用“評估公式”命令。
考慮單元格 C14 中的公式 NPV(中間年份)。該公式為
=SQRT (1 + C2)*C10
- 單擊單元格 C14。
- 單擊功能區上的“公式”選項卡。
- 單擊“公式審計”組中的“評估公式”。“評估公式”對話框出現。

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

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

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

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

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

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

1.09544511501033*4976.8518518515 將被評估為 5,451.87。
沒有更多表達式需要評估,這就是答案。“評估”按鈕將更改為“重新開始”按鈕,表示評估已完成。
錯誤檢查
在工作表和/或工作簿準備好進行計算後,進行錯誤檢查是一個好習慣。
考慮以下簡單的計算。

單元格中的計算導致錯誤 #DIV/0!。
單擊單元格 C5。
單擊功能區上的“公式”選項卡。
單擊“公式審計”組中“錯誤檢查”旁邊的箭頭。在下拉列表中,您會發現“迴圈引用”已停用,表示您的工作表中沒有迴圈引用。
從下拉列表中選擇“跟蹤錯誤”。

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

- 單擊“移除箭頭”。
- 單擊“錯誤檢查”旁邊的箭頭。
- 從下拉列表中選擇“錯誤檢查”。

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

觀察以下內容 -
如果單擊“有關此錯誤的幫助”,將顯示 Excel 有關該錯誤的幫助。
如果單擊“顯示計算步驟”,將出現“評估公式”對話方塊。
如果單擊“忽略錯誤”,“錯誤檢查”對話方塊將關閉,如果再次單擊“錯誤檢查”命令,它將忽略此錯誤。
如果單擊“在公式欄中編輯”,您將轉到公式欄中的公式,以便您可以編輯單元格中的公式。