HotXLS 查閱掃描與假循環參照

在 B 欄的某個儲存格裡放上 =VLOOKUP(A1,B:B,1),Excel 毫無怨言地計算它。把同一份活頁簿交給相依圖重新計算引擎,您很可能得到循環參照錯誤,因為這條公式依賴一個包含公式自身的範圍。HotXLS 直到 v2.361.98 都正是這樣回報的。修復不是針對整欄範圍的特例;它是試算表引擎需要、而普通有向圖沒有的兩種相依邊之間的區分

查閱家族——LOOKUP、MATCH、HLOOKUP、VLOOKUP、XLOOKUP 與 XMATCH——的查閱陣列引數,現在標記為掃描參照。掃描參照仍會播種髒標記,所以編輯範圍內的儲存格仍會讓公式重新計算,但它絕不參與循環偵測或評估排序。真正的循環照樣被找到;假的那些消失了

為什麼 Excel 允許查閱範圍包含公式自身?

因為那個引數不像算術運算元那樣被消費。查閱家族在範圍裡掃描快取值並回傳一個符合項;它不要求範圍先完成評估。Excel 把自重疊的查閱範圍視為讀取那些儲存格目前持有的值,與它套用在任何非反覆運算活頁簿上的語意相同:本輪尚未重新計算的儲存格,貢獻它們上一次計算的值

整欄參照讓這成為常見情況而不是罕見案例。在會持續追加列的工作表裡,B:B 是表達「整個查閱表」的慣用寫法,而住在 B 欄的任何公式隨即位於自己的查閱範圍之內。財務模型、對帳表與稽核活頁簿時常這樣做,通常沒有人注意到範圍重疊

相依圖對同一條公式做了什麼

HotXLS 增量重新計算,這需要一個真正的相依圖:儲存格為節點、參照為邊、評估用拓撲排序,加上一輪強連通分量分析來分類循環。那套機制在增量重新計算文章中描述,而這正是誤報出現的原因

從儲存格 B7 的 =VLOOKUP(A1,B:B,1) 擷取相依,第二個引數產出一個包含 B7 自身的範圍。圖上現在有一個自環。那個節點的入度永遠到不了零,所以拓撲輪永遠排不到它,分量輪把它分類為循環。引擎對它拿到的圖推理正確;錯的是圖這個模型,因為它把試算表裡的兩種邊型編碼成了一種

兩類邊,一張圖

這次變更在已解析參照記錄上加了旗標 TXLSDepRange.LookupScan,相依擷取器在走訪六個函式之一的查閱陣列引數時設定它。下游,源自這些參照的邊與普通邊分開儲存:圖節點在正常的前驅與後繼清單之外,另持 ScanDependents 與 ScanPrecedents 清單

這個分離正是語意正確的原因。掃描邊由髒傳播走訪,所以在 B:B 任何位置的編輯仍會把 B7 標髒,B7 仍會重新計算。掃描邊絕不計入入度、絕不進入分量建構器,所以它們無法製造拓撲死鎖,也無法被分類為循環。程式庫裡兩個圖實作——經典的每活頁簿圖與承載分量分析的跨活頁簿工作區圖——一起改動;讓它們漂移,會產出一份單獨開啟與作為工作區一部分開啟時重新計算結果不同的活頁簿

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // 查閱範圍涵蓋 B 欄,而這條公式就住在其中
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // 在 v2.361.98 之前,這個工作表走不到這個分支
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

把掃描邊排除在排序之外,您放棄了什麼

恰好一件事,而且值得直說而不是藏起來。因為掃描邊不參與拓撲排序,一條查閱公式可能在同一輪裡、在其查閱範圍內某些儲存格重新計算之前被評估,然後讀到它們先前的值。結果會在下一次重新計算時收斂

可以接受,因為這正是 Excel 的做法。對未啟用反覆運算計算的活頁簿,Excel 對本輪尚未重新計算的值,自己的答案就是上一次計算的值,所以重現這個行為的引擎是在匹配參考實作,而不是近似它。如果您需要對自參照模型得到真正收斂的答案,對應機制是帶明確迭代上限的反覆運算計算,見反覆運算計算文章,它適用於真正的循環,而不適用於掃描重疊

藏在修復裡的退化風險

給 TXLSDepRange 加 LookupScan 帶來一個與查閱無關、卻與 Pascal 息息相關的風險。TXLSDepRange 是非受管記錄,所以該型別的區域變數不會被零初始化。程式碼庫裡每一個手工建構它的位置,包括資料表相依區塊與幾個測試輔助器,都必須更新為明確設定新欄位。漏掉一處,堆疊上碰巧是什麼位元組就決定了那個參照是否被當成掃描邊,產出一個隨無關程式碼改動而出現又消失的重新計算錯誤

// 在非受管記錄中新增一個 Boolean 欄位,讓每個手工建構點
// 都變成潛伏錯誤。兩種安全慣用法:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // 全部歸零,再填入
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // 或者在每個位置設定每個欄位,包括新欄位
  R.LookupScan := False;
end;

由此換來的一般規則:給一個在多處於堆疊上建構的記錄加欄位,是比看起來風險更高的改動,而且編譯器不會幫您找出那些位置。如果該記錄可從熱路徑觸及,寧可提供一個完整初始化它的輔助函式,也不要指望每個呼叫點都會被更新

分辨真循環與掃描重疊

這次變更沒有削弱任何循環偵測。B7 裡的 =B7+1 仍是循環,三條公式閉合成鏈仍是循環,兩者仍透過重新計算結果回報,循環成員保留先前的快取值,循環之外的一切保持最新。改變的只是:查閱陣列引數不再製造 Excel 看不見的循環

如果您正在稽核一份活頁簿,想知道引擎實際解析了哪些參照、以什麼順序,評估追蹤器就是那個工具;公式評估追蹤器文章說明如何讀它的輸出。HotXLS 是不依賴 Excel 安裝即可讀寫 XLS、XLSX、ODS 與 CSV 的原生 Delphi 與 C++Builder 試算表元件,重新計算引擎在每種格式上都相同;目前的函式與引擎涵蓋列於 HotXLS Delphi spreadsheet component 產品頁