如何分析他人製作的試算表（而無需進行逆向工程）
在您信任接手的活頁簿中的數字之前，您需要確認三件事。第一，哪一個工作表才是真正的資料來源。第二，哪些儲存格包含的是手動輸入的值，而非公式。第三，該檔案在何處引用了外部連結。其他的都只是細節。
大多數人都會直接跳到摘要工作表並開始閱讀。這就是為什麼一個 11 個月前手動寫死的覆蓋值，最後會出現在董事會報告資料中。
本指南將探討為什麼接手的活頁簿會如此難以解讀、人們嘗試解碼它的三種方法，以及每種方法在何處會遇到瓶頸。
為什麼別人建立的試算表很難閱讀
活頁簿記錄的不僅僅是資料，還有決策。這些決策是看不見的，而且做決策的人通常已經離開了團隊。
最棘手的問題是，顯示 48,200 的儲存格完全無法提供其來源的線索。它可能是一個公式、一個貼上的數值，或者是某人在截止期限前手動覆寫公式的結果。這三者看起來一模一樣。
結構也會被隱藏。工作表可以被隱藏，列可以被群組並折疊，而定義名稱的範圍所指向的地方可能與其名稱所暗示的完全不同。指向您沒有的檔案的外部連結，會默默地繼續顯示其最後快取的結果，而不會發出任何警告。
接著是版本問題。當一個資料夾中包含 model_v3、model_final 和 model_final_USE_THIS 時，檔名根本無法證明任何事情。
這會讓您付出什麼代價
在您能回答問題之前，得先花上一天。 第一個要求通常很簡單，例如為什麼總和改變了。要誠實地回答這個問題，意味著必須先釐清整個活頁簿的架構，因為您無法排除那些您還沒找過的手動覆寫值。
未經證實的信心。 除了釐清架構之外，另一個選擇是直接信任摘要工作表。這能快速得出答案，但當有人提出質疑時，您將無從辯護。
隨後才會顯現的公式斷鏈。 編輯一個您尚未釐清架構的活頁簿，可能會在不知不覺中切斷參照關係。數字仍然會進行計算，因此看起來沒有任何異常，直到審查人員發現該數字不再變動為止。
這些代價最終會由最後拿到檔案的人承擔。當別人建立的試算表經過三手，每個人都加上了自己的修改，卻沒有任何人記錄下來。
人們嘗試的解決方法
方案 1：將手動輸入的數字與計算出來的數字分開
在閱讀任何邏輯之前，先找出哪些儲存格是輸入值。ISFORMULA 會針對任何包含公式的儲存格返回 TRUE，因此在工作表中使用輔助欄可以立即抓出寫死的數值。
如果您想查看邏輯而不僅僅是標記它，FORMULATEXT 可以將公式以文字形式返回。將其排列在數值旁邊，就能將令人費解的區塊變得清晰易讀。
這是最有價值的第一步，而且速度真的很快。它的局限在於覆蓋範圍：您必須逐一工作表套用，而一個大型活頁簿的工作表數量往往多到考驗您的耐心。
方案 2：追蹤參照關係
Excel 的公式稽核工具可以繪製出這些關係。Microsoft 說明文件中介紹了如何顯示公式與儲存格之間的關係，其中「追蹤前導參照」會顯示哪些儲存格影響了該儲存格，而「追蹤從屬參照」則會顯示該儲存格影響了哪些儲存格。
箭頭顏色代表不同的資訊。藍色箭頭表示沒有錯誤的儲存格，紅色箭頭則指向導致錯誤的儲存格。指向工作表圖示的黑色箭頭表示該參照位於另一個工作表或另一個活頁簿中。最後一項正是您發現外部參照方式。
對於單個複雜的公式，逐步評估公式可以顯示每個中間結果。這種方法雖然慢，但很可靠。
限制在於工作量。追蹤是針對單個儲存格的操作，而一個擁有 400 個公式的模型就意味著 400 次操作。
方案 3：進行活頁簿層級的全面盤點
與其逐個閱讀儲存格，不如為檔案建立清單。列出每個工作表（包括隱藏的工作表）、每個外部連結、每個定義名稱的範圍，以及公式規律在欄中途斷掉的每個地方。
Microsoft 針對此需求提供了一個增益集：Spreadsheet Inquire，它可以用於分析活頁簿結構 and 關係。是否可用取決於您的 Office 版本，因此在規劃使用前請先確認該頁面。循環參照需要單獨處理，Microsoft 在另一篇說明文件中介紹了如何尋找並處理循環參照。
全面盤點是最完整的方案，也是最費工的。此外，它回答的問題也與您被問到的問題不同。
共同的瓶頸。 這三種方法都只能解釋活頁簿是如何計算的。沒有一個能告訴您數字是否正確，而且一旦遇到第四版，所有的心血都將付諸流水。
如何使用 Powerdrill Bloom 分析接手的活頁簿
步驟 1：上傳活頁簿
直接上傳您收到的原始檔案，無需事先整理。Powerdrill Bloom 會在檔案上傳時立即分析每個工作表的特徵。在您閱讀任何公式之前，工作表數量、資料欄類型、空白區塊以及不一致的數值類型都已一目了然。
步驟 2：使用自然語言詢問結構性問題
從架構圖開始，而不是從數字開始。詢問哪些工作表看起來像原始輸入值，哪些看起來像衍生摘要，以及同一個欄位在不同工作表中出現不同數值的地方。
然後直接詢問信任問題。詢問哪些資料欄在中途打破了自身的規律，以及哪些總和與其下方的列不符。這兩個答案能幫您找出大部分的手動覆寫值。
步驟 3：匯出圖表、報告或簡報
匯出活頁簿的結構摘要，或從您決定信任的工作表中匯出圖表。記錄您已驗證內容的簡短書面筆記也同樣有效。
為什麼這優於逐個儲存格閱讀公式
| 手動方式 | Powerdrill Bloom | |
|---|---|---|
| 尋找寫死的數值 | 每個工作表建立輔助欄 | 詢問哪些數值打破了規律 |
| 理解參照關係 | 逐個儲存格追蹤箭頭 | 詢問哪些工作表提供資料給哪些工作表 |
| 檢查總和是否真實 | 手動重新建構 | 詢問它是否與其下方的列相符 |
| 第四版送達時 | 重複所有步驟 | 上傳新檔案 |
最後一列是改變行為模式的關鍵。釐清一次活頁簿的架構，花上一個下午還算合理。但每次同事傳來修改版本時都要重新釐清一次，正是讓人們放棄檢查的主因。
常見錯誤
信任摘要工作表。 這是任何活頁簿中被編輯最頻繁的工作表，也最有可能包含手動修改。在引用它之前，請先對照詳細資料進行驗證。
在釐清架構前進行編輯。 在您尚未理解的結構中修改儲存格，可能會在不知不覺中破壞參照關係。請先釐清架構，再進行編輯。
假設整欄一致。 一個在前面 200 列運作順暢的公式，可能會在第 201 列被手動覆寫。請檢查整欄的規律，而不僅僅是頂部的幾列。
忽略隱藏的工作表。 隱藏的工作表通常包含其他所有內容都依賴的對照表。在斷定檔案很簡單之前，請先取消隱藏所有內容。
將檔名視為版本。 一個名為 final 的檔案並不能證明任何事。在選擇檔案之前，請先比較候選檔案之間的實際數字——我們關於一次分析多個 Excel 檔案的指南中介紹了這種比較方法。
從頭重新建構。 這很誘人，但通常是個錯誤。重新建構會遺失原始檔案中所包含的未記錄規則，而這些規則往往是數字能對得上的唯一原因。
在理解之前進行清理。 刪除合併儲存格和空白列雖然能讓檔案更容易閱讀，但也會破壞其建構方式的證據。請先備份一份副本。
結論
接手的活頁簿在成為分析問題之前，首先是一個閱讀問題。找出真正的來源工作表，將手動輸入的值與計算出來的值分開，向外追蹤參照關係，然後才回答您被問到的問題。
這並不是不信任撰寫它的人。別人建立的試算表是截止期限下所做決策的記錄，而仔細閱讀它是使用它所必須付出的代價。
讓這件事變得昂貴的原因，是每次修改都要重來一次。如果這佔用了您一整週的時間，嘗試直接使用 Powerdrill Bloom 處理您收到的原始檔案。另請參閱我們關於使用 AI 分析 Excel 以及清理與刪除重複資料的指南，以及 Excel AI 助手和 AI 資料清理頁面。
常見問題
如何在別人建立的試算表中尋找寫死的數值？
使用 ISFORMULA 新增輔助欄，它會針對公式儲存格返回 TRUE，針對手動輸入的儲存格返回 FALSE。計算區塊中的每個 FALSE 都是值得調查的手動覆寫值。
如何將儲存格背後的公式顯示為文字？
在相鄰儲存格中使用 FORMULATEXT。它會將公式以易讀的字串形式返回，這樣一來，無需逐一按一下每個儲存格，就能快速瀏覽整欄的邏輯。
如何找出儲存格的參照來源？
使用「公式」索引標籤上的「追蹤前導參照」來查看哪些儲存格影響了該儲存格，並使用「追蹤從屬參照」來查看它影響了哪些儲存格。指向工作表圖示的黑色箭頭表示該參照位於目前工作表之外。
我應該在分析接手的活頁簿之前先進行清理嗎？
在釐清架構之前不要。清理會移除檔案建構方式的證據，包括標示結構的合併儲存格和空白區塊。無論如何，請保留一份未經修改的副本。
檢查總和是否值得信任最快的方法是什麼？
根據其下方的列重新計算並進行比較。如果兩者不一致，則該總和可能包含覆寫值、篩選範圍，或是參照了您尚未查看的工作表。