Lookup scan của HotXLS và tham chiếu vòng giả

Đặt =VLOOKUP(A1,B:B,1) vào một ô ở cột B và Excel tính nó không một lời than. Đưa cùng sổ làm việc đó cho một engine tính lại dựa đồ thị phụ thuộc và bạn nhiều khả năng nhận một lỗi tham chiếu vòng, vì công thức phụ thuộc vào một vùng chứa chính công thức. HotXLS đã báo chính xác điều đó cho đến v2.361.98. Bản sửa không phải một trường hợp đặc biệt cho các vùng nguyên cột; nó là sự phân biệt giữa hai loại cạnh phụ thuộc mà một engine bảng tính cần và một đồ thị có hướng giản dị không có

Đối số lookup-array của họ hàm tra cứu — LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP và XMATCH — giờ được đánh dấu là tham chiếu scan. Một tham chiếu scan vẫn gieo trạng thái bẩn, nên sửa một ô trong vùng vẫn làm tính lại công thức, nhưng nó chưa bao giờ góp vào phát hiện vòng hay thứ tự đánh giá. Các vòng thật vẫn được tìm thấy; các vòng giả biến mất

Vì sao Excel cho phép vùng tra cứu chứa công thức?

Vì đối số đó không được tiêu thụ theo cách một toán hạng số học được. Họ hàm tra cứu quét vùng tìm các giá trị cache và trả về một kết quả khớp; nó không đòi hỏi vùng phải được đánh giá trọn vẹn trước. Excel coi một vùng tra cứu tự chồng lấn là đọc bất cứ giá trị nào mà những ô đó đang giữ, cùng ngữ nghĩa mà nó áp dụng cho bất kỳ sổ làm việc không lặp nào: các ô chưa được tính lại trong lượt này đóng góp giá trị tính lần cuối của chúng

Các tham chiếu nguyên cột khiến đây là trường hợp phổ biến chứ không phải hiếm hoi. B:B là cách thành ngữ để viết "toàn bộ bảng tra cứu" trong một bảng mà các hàng được nối thêm, và bất kỳ công thức nào sống ở cột B khi đó nằm bên trong vùng tra cứu của chính nó. Các mô hình tài chính, bảng đối soát và sổ kiểm toán làm điều này liên tục, thường mà không ai nhận ra vùng bị chồng lấn

Một đồ thị phụ thuộc làm gì với cùng công thức đó

HotXLS tính lại theo kiểu tăng dần, điều đòi hỏi một đồ thị phụ thuộc thật: các nút cho ô, các cạnh cho tham chiếu, một thứ tự tôpô cho đánh giá và một lượt thành phần liên thông mạnh để phân loại vòng. Cỗ máy đó được mô tả trong bài viết về tính lại tăng dần, và nó chính xác là lý do kết quả dương giả xuất hiện

Trích phụ thuộc từ =VLOOKUP(A1,B:B,1) ở ô B7 và đối số thứ hai cho ra một vùng chứa chính B7. Đồ thị giờ có một vòng tự quấn. Bậc vào của nút đó chưa bao giờ về không, nên lượt tôpô chưa bao giờ xếp được lịch cho nó, và lượt thành phần phân loại nó là một vòng. Engine đang suy luận đúng về đồ thị mà nó được đưa. Đồ thị là mô hình sai, vì nó mã hóa một loại cạnh trong khi bảng tính có hai

Hai lớp cạnh, một đồ thị

Thay đổi thêm một cờ vào bản ghi tham chiếu đã phân giải, TXLSDepRange.LookupScan, mà bộ trích phụ thuộc đặt khi nó đi qua đối số lookup-array của một trong sáu hàm. Phía hạ nguồn, các cạnh xuất phát từ những tham chiếu đó được lưu tách khỏi các cạnh thường: nút đồ thị giữ các danh sách ScanDependents và ScanPrecedents cạnh các danh sách phụ thuộc và tiền nhiệm thông thường của nó

Sự tách biệt là điều khiến ngữ nghĩa đúng. Các cạnh scan được đi qua bởi lan truyền bẩn, nên một chỉnh sửa ở bất kỳ đâu trong B:B vẫn đánh dấu B7 bẩn và B7 tính lại. Các cạnh scan chưa bao giờ được tính vào bậc vào và chưa bao giờ vào bộ dựng thành phần, nên chúng không thể tạo một bế tắc tôpô và không thể bị phân loại là vòng. Cả hai hiện thực đồ thị trong thư viện — đồ thị kiểu cổ điển theo từng sổ làm việc và đồ thị workspace đa sổ mang phân tích thành phần — được thay đổi cùng nhau; để chúng trôi dạt sẽ tạo ra một sổ làm việc tính lại khác nhau tùy việc nó được mở một mình hay như một phần của workspace

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;
    // Vùng tra cứu phủ cột B, và công thức này sống trong nó
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Trước v2.361.98 nhánh này không thể với tới với bảng này
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Bạn nhường lại điều gì khi loại cạnh scan khỏi thứ tự

Chính xác một thứ, và đáng nói thẳng thay vì giấu giếm. Vì các cạnh scan không tham gia thứ tự tôpô, một công thức tra cứu có thể được đánh giá trong cùng lượt trước khi một số ô trong vùng tra cứu của nó được tính lại, và nó sẽ khi đó đọc các giá trị trước đó của chúng. Kết quả hội tụ ở lần tính lại kế tiếp

Điều đó chấp nhận được vì đó là điều Excel làm. Với một sổ làm việc không bật tính toán lặp, câu trả lời của chính Excel cho một giá trị chưa được tính lại trong lượt hiện tại là giá trị tính lần cuối, nên một engine tái hiện hành vi này đang khớp với hiện thực tham chiếu chứ không phải xấp xỉ nó. Nếu bạn cần một câu trả lời hội tụ thật sự trên một mô hình tự tham chiếu, cơ chế cho điều đó là tính toán lặp với một giới hạn lặp tường minh, được đề cập trong bài viết về tính toán lặp, và nó áp dụng cho các vòng thật chứ không phải các chồng lấn scan

Mối nguy hồi quy trốn bên trong bản sửa

Thêm LookupScan vào TXLSDepRange giới thiệu một rủi ro chẳng liên quan gì tra cứu mà dính dáng hết mọi thứ tới Pascal. TXLSDepRange là một record không được quản lý, nên một biến cục bộ kiểu đó không được khởi tạo bằng không. Mọi nơi trong codebase dựng nó bằng tay, gồm các khối phụ thuộc data-table và vài helper kiểm thử, vì vậy phải được cập nhật để đặt trường mới một cách tường minh. Bỏ sót một nơi và byte tình cờ nào đó trên stack quyết định tham chiếu đó có bị coi là cạnh scan hay không, thứ tạo ra một bug tính lại xuất hiện và biến mất cùng các thay đổi mã không liên quan

// Một trường Boolean mới trong một record không quản lý biến mọi nơi
// dựng thủ công thành một bug tiềm ẩn. Hai thành ngữ an toàn:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // về không mọi thứ, rồi điền vào
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // hoặc đặt mọi trường, gồm trường mới, tại mọi nơi
  R.LookupScan := False;
end;

Quy tắc chung mà điều này kiếm được: thêm một trường vào một record được dựng trên stack tại nhiều hơn một ít nơi là một thay đổi rủi ro cao hơn vẻ ngoài, và trình biên dịch sẽ không giúp bạn tìm các nơi đó. Nếu record chạm được từ một đường nóng, hãy chuộng một helper khởi tạo nó hoàn toàn hơn là tin mỗi call site sẽ được cập nhật

Phân biệt một vòng thật với một chồng lấn scan

Chẳng gì trong thay đổi này làm yếu phát hiện vòng. =B7+1 trong B7 vẫn là một vòng, một chuỗi ba công thức khép lại vào chính nó vẫn là một vòng, và cả hai vẫn được báo qua kết quả tính lại với các thành viên vòng giữ các giá trị cache trước đó trong khi mọi thứ ngoài vòng giữ hiện hành. Thay đổi chỉ là đối số lookup-array không còn chế tạo các vòng mà Excel không nhìn thấy

Nếu bạn đang kiểm toán một sổ làm việc và muốn biết engine thực sự phân giải những tham chiếu nào và theo thứ tự nào, trình tracer đánh giá là công cụ cho việc đó; bài viết về tracer đánh giá công thức trình bày cách đọc đầu ra của nó. HotXLS là một thành phần bảng tính Delphi và C++Builder thuần đọc và ghi XLS, XLSX, ODS và CSV mà không cần cài Excel, và engine tính lại là như nhau trên mọi định dạng; độ phủ hàm và engine hiện tại được liệt kê trên trang sản phẩm HotXLS Delphi spreadsheet component