Skan wyszukiwania HotXLS i fałszywe odwołania cykliczne

Wstaw =VLOOKUP(A1,B:B,1) w komórce w kolumnie B, a Excel policzy to bez słowa skargi. Podaj ten sam skoroszyt silnikowi przeliczania na grafie zależności i możliwe, że dostaniesz błąd odwołania cyklicznego, bo formuła zależy od zakresu, który zawiera tę formułę. HotXLS zgłaszał dokładnie to do v2.361.98. Poprawka nie jest przypadkiem szczególnym dla zakresów całej kolumny; to rozróżnienie dwóch rodzajów krawędzi zależności, których potrzebuje silnik arkusza, a zwykły graf skierowany nie ma

Argument tablicy wyszukiwania rodziny wyszukiwań, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP i XMATCH, jest teraz oznaczany jako referencja skanowa. Referencja skanowa wciąż rozsiewa stan brudny, więc edycja komórki wewnątrz zakresu przelicza formułę, ale nigdy nie wnosi do detekcji cykli ani do porządkowania ewaluacji. Prawdziwe cykle są wciąż znajdowane; fałszywe zniknęły

Czemu Excel pozwala, by zakres wyszukiwania zawierał formułę?

Bo ten argument nie jest konsumowany tak, jak operand arytmetyczny. Rodzina wyszukiwań skanuje zakres pod kątem wartości z cache i zwraca dopasowanie; nie wymaga, by zakres był najpierw ewaluowany do końca. Excel traktuje samonakładający się zakres wyszukiwania jako czytanie tego, co te komórki aktualnie trzymają, czyli tę samą semantykę, którą stosuje do każdego nieiteracyjnego skoroszytu: komórki nieprzeliczone w tym przebiegu wnoszą swoją ostatnią policzoną wartość

Referencje całej kolumny czynią z tego przypadek pospolity, a nie egzotyczny. B:B to idiomatyczny sposób zapisania „całej tabeli wyszukiwania” w arkuszu, do którego dosypywane są wiersze, a każda formuła mieszkająca w kolumnie B jest wtedy wewnątrz własnego zakresu wyszukiwania. Modele finansowe, arkusze uzgadniania i skoroszyty audytowe robią to ciągle, zwykle bez zauważenia przez kogokolwiek, że zakres się nakłada

Co graf zależności robi z tą samą formułą

HotXLS przelicza przyrostowo, co wymaga prawdziwego grafu zależności: węzłów dla komórek, krawędzi dla referencji, porządku topologicznego dla ewaluacji i przebiegu silnie spójnych składowych do klasyfikacji cykli. Ta maszyneria jest opisana w artykule o przeliczaniu przyrostowym i dokładnie dlatego pojawił się fałszywy alarm

Wyodrębnij zależności z =VLOOKUP(A1,B:B,1) w komórce B7, a drugi argument da zakres zawierający samą B7. Graf ma teraz pętlę własną. Stopień wejściowy tego węzła nigdy nie osiąga zera, więc przebieg topologiczny nigdy nie może go uszeregować, a przebieg składowych klasyfikuje go jako cykl. Silnik rozumuje poprawnie o grafie, który dostał. Graf jest złym modelem, bo koduje jeden typ krawędzi tam, gdzie arkusz ma dwa

Dwie klasy krawędzi, jeden graf

Zmiana dodaje flagę do rekordu rozwiązanej referencji, TXLSDepRange.LookupScan, którą ekstraktor zależności ustawia, gdy przechodzi argument tablicy wyszukiwania jednej z sześciu funkcji. W dół strumienia krawędzie pochodzące z tych referencji są przechowywane osobno od zwykłych krawędzi: węzeł grafu trzyma listy ScanDependents i ScanPrecedents obok swoich zwykłych list zależnych i poprzedzających

Rozdzielenie jest tym, co robi semantykę właściwą. Krawędzie skanu są przechodzone przez propagację brudu, więc edycja gdziekolwiek w B:B wciąż oznacza B7 jako brudne i B7 się przelicza. Krawędzie skanu nigdy nie są liczone do stopnia wejściowego i nigdy nie wchodzą do budowniczego składowych, więc nie mogą stworzyć topologicznego zakleszczenia ani zostać sklasyfikowane jako cykl. Obie implementacje grafu w bibliotece, klasyczny graf per skoroszyt i między-skoroszytowy graf obszaru roboczego niosący analizę składowych, zostały zmienione razem; pozwolenie im się rozjechać wyprodukowałoby skoroszyt przeliczający się inaczej zależnie od tego, czy został otwarty samodzielnie, czy jako część obszaru roboczego

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;
    // Zakres wyszukiwania obejmuje kolumnę B, a ta formuła w niej mieszka
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Przed v2.361.98 ta gałąź była nieosiągalna dla tego arkusza
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Co poświęcasz, wyłączając krawędzie skanu z uszeregowania

Dokładnie jedną rzecz i warte powiedzenia wprost, zamiast ukrywania. Ponieważ krawędzie skanu nie biorą udziału w porządku topologicznym, formuła wyszukiwania może być ewaluowana w tym samym przebiegu, zanim niektóre komórki jej zakresu wyszukiwania zostały przeliczone, i przeczyta wtedy ich poprzednie wartości. Wynik zbiega przy następnym przeliczeniu

To jest akceptowalne, bo dokładnie to robi Excel. Dla skoroszytu bez włączonego obliczania iteracyjnego własna odpowiedź Excela dla wartości jeszcze nieprzeliczonej w bieżącym przebiegu to ostatnia policzona wartość, więc silnik odtwarzający to zachowanie dorównuje implementacji referencyjnej, a nie przybliża ją. Jeśli potrzebujesz naprawdę zbieżnej odpowiedzi na modelu samoreferencyjnym, mechanizmem na to jest obliczanie iteracyjne z jawnym limitem iteracji, omówione w artykule o obliczaniu iteracyjnym, i dotyczy prawdziwych cykli, a nie nakładań skanów

Zagrożenie regresją kryjące się wewnątrz poprawki

Dodanie LookupScan do TXLSDepRange wprawiło w ruch ryzyko niezwiązane z wyszukiwaniami, a związane całkowicie z Pascalem. TXLSDepRange to rekord niezarządzany, więc zmienna lokalna tego typu nie jest inicjalizowana zerami. Każde miejsce w bazie kodu budujące go ręcznie, łącznie z blokami zależności tabel danych i kilkoma pomocnikami testowymi, musiało zostać zaktualizowane, by ustawiać nowe pole jawnie. Pomiń jedno i dowolny bajt, który akurat siedział na stosie, decyduje, czy ta referencja jest traktowana jako krawędź skanu, co produkuje błąd przeliczania pojawiający się i znikający z niezwiązanymi zmianami kodu

// Nowe pole Boolean w rekordzie niezarządzanym czyni każde ręczne
// miejsce konstrukcji uśpionym błędem. Dwa bezpieczne idiomy:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // wyzeruj wszystko, potem wypełnij
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // albo ustaw każde pole, łącznie z nowym, w każdym miejscu
  R.LookupScan := False;
end;

Ogólna reguła, która na tym zapracowała: dodanie pola do rekordu konstruowanego na stosie w więcej niż garstce miejsc to zmiana wyższego ryzyka, niż wygląda, a kompilator nie pomoże ci znaleźć miejsc. Jeśli rekord jest osiągalny z gorącej ścieżki, przedkładaj pomocnika inicjalizującego go całkowicie nad ufanie, że każde miejsce wywołania zostanie zaktualizowane

Odróżnianie prawdziwego cyklu od nakładania skanu

Nic w tej zmianie nie osłabia detekcji cykli. =B7+1 w B7 to wciąż cykl, łańcuch trzech formuł zamykający się na sobie to wciąż cykl, i oba są wciąż zgłaszane przez wynik przeliczania, z członkami cyklu zachowującymi swoje poprzednie wartości z cache, podczas gdy wszystko poza cyklem pozostaje aktualne. Zmieniło się tylko to, że argument tablicy wyszukiwania już nie fabrykuje cykli, których Excel nie widzi

Jeśli audytujesz skoroszyt i chcesz wiedzieć, które referencje silnik faktycznie rozwiązał i w jakiej kolejności, tracer ewaluacji jest do tego narzędziem; artykuł o tracerze ewaluacji formuł omawia, jak czytać jego wyjście. HotXLS to natywny komponent arkusza dla Delphi i C++Builder czytający i piszący XLS, XLSX, ODS i CSV bez zainstalowanego Excela, a silnik przeliczania jest ten sam na każdym formacie; aktualne pokrycie funkcji i silnika jest wypisane na stronie produktu HotXLS Delphi spreadsheet component