Niejawne przecięcie dla nazw zdefiniowanych w HotXLS Delphi

Nazwa zdefiniowana wskazująca całą kolumnę jest czytana przez Excela jako pojedyncza komórka, gdy występuje w pozycji skalarnej: =Vertical+1 w wierszu 7 oznacza „komórkę wiersza 7 nazwy Vertical”, a nie cały obszar. HotXLS Delphi Component stosuje to niejawne przecięcie na dwóch poziomach, przy obliczaniu wartości i przy wyciąganiu zależności, bo samo poprawne wyliczenie wartości nie wystarcza. Walker zależności, który rozwinie nazwę do całego jej obszaru, pozwala dowolnej formule położonej dalej w grafie, która zasila komórkę tego obszaru, zamknąć cykl, który nie istnieje, a TXLSXWorkbook.Recalculate musiałby wtedy odrzucić cały skoroszyt jako cykliczny

Zwykły skoroszyt amortyzacji kredytu z 4805 formułami to przypadek obciążeniowy, który wkłada to wszystko do roboty. Poprawne przeliczenie go ze starych pamięci podręcznych zależy od trzech osobnych mechanizmów: niejawnego przecięcia dla nazw, uporządkowania skanów wyszukiwania i typu numerycznego użytego dla zagnieżdżonej arytmetyki. Pomylić którykolwiek z nich, a objawem jest albo lxErrorRef, kod odwołania cyklicznego, albo sumy odjeżdżające od niezależnego rachunku wiersz po wierszu. Ten artykuł przechodzi przez każdy mechanizm razem ze źródłem, które w nim gra

Dlaczego odwołanie skalarne do nazwy kolumny tworzy fałszywy cykl?

Bo graf zależności zna tylko krawędzie, a jedna krawędź od formuły do obszaru o 480 wierszach to 480 krawędzi, z których jedna prowadzi z powrotem przez komórkę zależną od tej formuły. Weźmy =IF(TRUE,Vertical+1,0) w B1 z Vertical zdefiniowaną jako Inputs!$A$1:$A$2 oraz =B1+1 w A2. Excel liczy B1 jako A1+1, a A2 jako B1+1 — prosty łańcuch. Walker, który zapisze B1 jako zależne od A1:A2, czyni A2 poprzednikiem B1, A2 już wymienia B1 jako poprzednika, a kolejka Kahna napędzająca przeliczanie przyrostowe w HotXLS nigdy nie doczeka się, by którykolwiek z tych węzłów osiągnął zerowy stopień wejściowy. Z tego wzoru uszyte są szablony kredytów: każdy wiersz okresu odwołuje się do nazwanych kolumn salda, stopy i liczby rat, każda nazwa obejmuje cały harmonogram, a każdy wiersz dodatkowo zapisuje do tych kolumn. Rozwiń nazwy, a graf staje się jednym wielkim silnie spójnym komponentem. Policz je z niejawnym przecięciem, a graf staje się zbiorem krótkich łańcuchów, po jednym na wiersz, co opisuje ECMA-376 Part 1 §18.17.2 dla operandu referencyjnego użytego tam, gdzie wymagana jest pojedyncza wartość

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // Pozycja skalarna: Vertical zwija się do A1, bo formuła jest w wierszu 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Nazwa, której definicją jest inna nazwa, też się przecina, więc to A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argument klasy referencyjnej: sumowany jest cały obszar, bez przecięcia
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // Wiersz 6 leży poza A1:A2, przecięcie jest puste i IFERROR je łapie
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Rozwinięcie Vertical do A1:A2 zamieniłoby tu B1 -> A2 -> B1 w fałszywy cykl
    end;
  finally
    Book.Free;
  end;
end;

Jak HotXLS rozstrzyga, że argument jest skalarny?

HotXLS czyta odpowiedź z tabeli funkcji, a nie z kształtu argumentu. Każdy wpis w TXLSFormula.InitFuncHash jest rejestrowany przez THashFunc.SetValue z opcjonalnym łańcuchem klas poszczególnych argumentów: 'IF' niesie '100', 'SUMIF' niesie '010', 'VLOOKUP' niesie '1011', a 'SUM' nie niesie żadnego, więc wszystkie jego argumenty spadają do klasy 0 na poziomie funkcji. TXLSFormula.FunctionArgumentClass(APtg, AArgument) wystawia ten bajt przez THashFuncEntry.ArgClass, a wynik 1 oznacza klasę wartości. To te same trzy klasy, które [MS-XLS] §2.2.2 przypisuje tokenom operandów, i koder też na nich polega: pisząc odwołanie, liczy ptg jako $24 + $20 * aClass, co daje PtgRef dla klasy 0, PtgRefV dla klasy 1 i PtgRefA dla klasy 2. Plik BIFF zapisany przez Excela trzyma tę klasę w każdym tokenie odwołania, więc silnik, którego tabela zgadza się ze specyfikacją, potrafi odpowiedzieć „czy ten argument jest skalarny” bez patrzenia na dane. Środkowy argument SUMIF to kryterium, czyli wartość; pierwszy i trzeci to obszary, czyli referencje. SUMPRODUCT jest zarejestrowany z klasą 2 na poziomie funkcji, tablicową, dlatego =SUMPRODUCT(Vertical,Vertical) wciąż mnoży cały obszar

Trzy funkcje nie zaglądają do własnego wpisu w tabeli w niczym poza pierwszym argumentem. IF (ptg 1), CHOOSE (ptg 100) i IFERROR (ptg 255) przepuszczają to, co wybierają, więc ich argumenty gałęzi dziedziczą klasę pozycji, którą zajmuje sama funkcja. Ta jedna reguła sprawia, że =CHOOSE(1,Vertical,0) w G2 rozwiązuje się do A2, a stojące obok =SUMIF(Vertical,">0",Vertical) wciąż sumuje oba wiersze — i to właśnie tę regułę harmonogram spłat wykorzystuje najczęściej, bo jego komórki okresów opierają się na IF przy sprawdzaniu, czy kredyt jest jeszcze otwarty

Przenoszenie klasy przez przejście po zależnościach

Ekstraktor zależności w lxCalc.pas to rekurencyjne Walk po skompilowanym drzewie składni i istnieje on dwa razy — w TXLSCalculator.ExtractDependencies dla grafu w obrębie skoroszytu i w ExtractWorkspaceDependencies dla grafu między skoroszytami. Oba walkery niosą dwa dodatkowe parametry. AScalar startuje jako True w korzeniu formuły, jest przeliczany dla każdego dziecka funkcji z FunctionArgumentClass i przechodzi bez zmian dla argumentów gałęzi ptg 1, 100 i 255. ANameRoot staje się True tylko wtedy, gdy walker schodzi do skompilowanej definicji nazwy, i przeżywa wyłącznie przez węzły SA_GROUP, czyli nawiasy, więc nazwa zdefiniowana jako =A1:A2+1 nie jest brana za zwykły obszar. Gdy w węźle SA_RANGE obie flagi są True, AddResolvedRange zawęża obszar tym samym pomocnikiem, którego używa ewaluator, zanim zapisze zależność. Pomocnik jest dość krótki, by przytoczyć go w całości

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // już jedna komórka
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // pojedyncza kolumna: weź ten wiersz
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // pojedynczy wiersz: weź tę kolumnę
    Result := True;
  end;
end;

Wszystko, co pomocnik odrzuci — obszar dwuwymiarowy, odwołanie wieloarkuszowe albo formuła, której wiersz leży poza nazwaną kolumną — daje #VALUE! po stronie obliczeń i żadnej zależności po stronie grafu, czyli dokładnie to, co Excel robi przy pustym przecięciu. Strona obliczeń mieszka w TXLSCalculator.GetValueItemName: zdejmuje opakowania SA_GROUP ze skompilowanej definicji, a jeśli korzeniem jest SA_RANGE, woła GetRangeInfo, przecina i pobiera jedną komórkę przez FGetValue zamiast liczyć całą definicję. Odwołania zewnętrzne zostają na starej ścieżce, bo nie ma lokalnego wiersza, względem którego można by przecinać. Skąd w ogóle bierze się przechowywanie i zakres nazwy, omawia artykuł o nazwach zdefiniowanych i formułach między arkuszami; tutaj chodzi tylko o to, co silnik robi, gdy nazwa się już rozwiąże

Dlaczego MATCH musi czekać na na wpół policzoną kolumnę?

Bo argument tablicy przeszukiwania w MATCH to referencja skanująca, a referencje skanujące są celowo trzymane poza twardą kolejnością wyliczania. Artykuł o skanowaniu w wyszukiwaniu wprowadza TXLSDepRange.LookupScan i formułuje, co tracisz, wyłączając krawędzie skanowania z uporządkowania: formuła wyszukiwania może pobiec, zanim każda komórka jej zakresu zostanie przeliczona, i odczytać stare wartości. W sesji interaktywnej zbiega się to przy następnym przebiegu. Przy pojedynczym wsadowym przeliczeniu szablonu, którego pamięci podręczne są stare, nie zbiega się: PaymentCount, zdefiniowany jako =MATCH(0.01,Balances,-1)+1, odczytałby stare wartości wciąż siedzące w kolumnie sald i zwrócił liczbę okresów, która nie mogłaby być poprawna

TXLSDepGraph.TopoOrder traktuje więc krawędzie skanowania jako miękkie krawędzie uporządkowania. Obok twardego stopnia wejściowego trzyma tablicę ScanInDeg, licząc stare poprzedniki skanowania na węzeł i zmniejszając licznik w miarę emisji tych poprzedników, korzystając z list ScanPrecedents, ScanDependents i ScanPrecedentCount, które graf już przechowuje. W każdej iteracji kolejka Kahna przeszukuje swoje okno gotowych węzłów w poszukiwaniu pierwszego o zerowym ScanInDeg i przestawia go na czoło; jeśli każdy gotowy węzeł wciąż czeka na poprzednik skanowania, czoło jest zdejmowane w swojej stabilnej kolejności. Krawędzie skanowania nigdy nie wchodzą do twardego stopnia wejściowego, więc samoodwołujący się VLOOKUP po własnej kolumnie wciąż jest legalny, ale wyszukiwanie, które może poczekać na możliwy do ukończenia poprzednik, czeka. Z trzema komórkami sald trzymającymi stare wartości PaymentCount wraca jako 3; przełącz wejście na zero, a =IFERROR(PaymentCount,99) zobaczy #N/A i zwróci 99

Dlaczego zagnieżdżona arytmetyka jest wymuszana na Double

Bo arytmetyka na Variantach w Delphi ma pułapkę, która pokazuje się tylko w pozycjach zagnieżdżonych. Skopiowanie najwyższego poziomu + albo - do dwóch lokalnych Double trzyma =B1-A1 dokładnym. Wewnątrz =IF(TRUE,B1-A1,0) oczywista implementacja biegnie to samo odejmowanie jako Value := Value - SubValue na dwóch Variantach, a gdy jeden operand to wartość komórki Int64, a drugi Double, wynikiem, który obserwowaliśmy, był Currency, typ stałoprzecinkowy z czterema miejscami po przecinku, więc 1066.1854641400994 minus 120 wraca obcięte do czterech miejsc. W harmonogramie, gdzie każda rata jest naliczana od poprzedniego wiersza, ten błąd przechodzi przez setki okresów, zanim dotrze do sum, dlatego TXLSCalculator.GetValueItem konwertuje oba operandy, zanim operator pobiegnie

// TXLSCalculator.GetValueItem, gałąź arytmetyki binarnej (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// Mieszana arytmetyka na Variantach Int64/Double może promować do Currency.
// Arytmetyka arkusza musi zachować precyzję zmiennoprzecinkową.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Strażnik działa przed SA_ADD, SA_SUB, SA_MUL i SA_DIV jednakowo, więc z Int64(120) w A1 i 1066.1854641400994 w B1 zagnieżdżona różnica, suma, iloczyn i iloraz wszystkie trzymają podwójną precyzję. HotXLS nie twierdzi, że zna każdą regułę promocji, którą RTL stosuje do mieszanych typów Variant w różnych wersjach kompilatora; traktuje arytmetykę arkusza jako IEEE double i czyni oba operandy double, zanim zobaczy je operator, co usuwa pytanie

Co silnik gwarantuje dla nazwanych kolumn

Z tymi trzema mechanizmami na miejscu obie architektury silnika przeliczają szablon amortyzacji ze starych pamięci podręcznych do lxOk, i wszystkie 4805 wartości zgadza się z niezależnym oczekiwaniem wiersz po wierszu w granicach 1E-7. Żadna iteracja nie jest włączona i żaden kod błędu nie jest wyciszany, żeby tam dojść. Prawdziwy cykl przez nazwę, =B1 w A1 przy B1 wciąż czytającym Vertical, nadal zwraca błąd, dokładnie tak, jak powinno być

Reguły idą za Excelem. Niejawne przecięcie dotyczy nazwy, której skompilowana definicja, po zdjęciu nawiasów, jest obszarem jednokolumnowym albo jednowierszowym na jednym arkuszu; nazwa dwuwymiarowa w pozycji skalarnej to #VALUE!, jak w Excelu, a funkcja nieznana tabeli dostaje od FunctionArgumentClass klasę 0, więc jej argumenty nazwowe są rozwijane w całości. Miękkie uporządkowanie to preferencja, nie gwarancja: cykl złożony wyłącznie ze skanów wylicza się w stabilnej kolejności i czyta cokolwiek jest w pamięci podręcznej, co jest kompromisem, na który artykuł o skanowaniu w wyszukiwaniu zgodził się świadomie. HotXLS to natywny komponent arkusza dla Delphi i C++Buildera, który czyta, przelicza i zapisuje XLS, XLSX, ODS i CSV bez zainstalowanego Excela; przecięcie nazw, tabela klas argumentów i miękkie uporządkowanie skanów dotyczą każdego formatu, bo silnik obliczeń jest współdzielony, a aktualne pokrycie funkcji jest wypisane na stronie produktu HotXLS Delphi spreadsheet component