Macierz opcji AGGREGATE i wyciek bramek w HotXLS dla Delphi

HotXLS, natywny komponent arkusza Excel dla Delphi i C++Buildera, wydał we wrześniu 2026 dwie powiązane poprawki AGGREGATE. Wersja 2.382.0 poprawiła argument opcji tak, że kody 1/3/5/7 pomijają ukryte wiersze, 2/3/6/7 pomijają błędy, a 0 do 3 pomijają zagnieżdżone komórki SUBTOTAL i AGGREGATE, dokładnie tak, jak dokumentuje to Microsoft. Wersja 2.382.3 powstrzymała potem te flagi wyboru przed wyciekaniem do obliczania tych samych komórek, do których funkcja się odwołuje. Pierwszy defekt jest wstydliwy w ten sposób, w jaki błędy przepisywania tabel są zawsze wstydliwe: pozycje bitów były zamienione, więc każda formuła używająca niezerowego kodu opcji dostawała politykę, o którą jej autor nie prosił. Drugi jest ciekawszy, bo to kształt, na który trafisz w każdym ewaluatorze używającym przemijającego pola do przekazywania kontekstu do rekurencyjnego przejścia. Zewnętrzna agregacja uzbraja flagę, przechodzi zakres i sięga po komórkę, której formuła nie została jeszcze policzona. Ta formuła biegnie na tym samym kalkulatorze, widzi tę samą uzbrojoną flagę i po cichu agreguje niewłaściwe wiersze, dając liczbę odbiegającą o wartość, której nikt nie wyjaśni z samego tekstu formuły

Co właściwie wybierają opcje AGGREGATE od 0 do 7?

Argument opcji AGGREGATE to macierz trzech bitów, a te trzy bity są niezależne. Bit 0 (wartość 1) oznacza pomijanie ukrytych wierszy, bit 1 (wartość 2) oznacza pomijanie wartości błędów, a bit 2 (wartość 4) oznacza zaprzestanie pomijania zagnieżdżonych komórek SUBTOTAL i AGGREGATE, bo pomijanie ich jest domyślne dla niskich kodów. Dwie rzeczy łatwo tu odwrócić. Bit ukrytych wierszy to bit najmłodszy, a nie środkowy, więc AGGREGATE(9,1,...) to postać sumująca przefiltrowane, a AGGREGATE(9,2,...) to postać tolerująca błędy. A polityka zagnieżdżonych agregatów jest odwrócona względem dwóch pozostałych: tylko kody od 4 do 7 traktują komórkę, której własna formuła jest SUBTOTAL albo AGGREGATE, jak zwykłą wartość. ECMA-376 Part 1 §18.17.7 definiuje SUBTOTAL z tym samym podziałem na włączanie i wyłączanie ukrytych wierszy między kodami 1-11 i 101-111, a AGGREGATE, zapisywane w plikach OOXML pod przedrostkiem _xlfn., uogólnia ten podział na argument opcji, więc tabela publikowana przez Microsoft dla funkcji AGGREGATE jest kontraktem, który silnik musi spełnić, a nie udogodnieniem

Opcja	Ukryte wiersze	Wartości błędów	Zagnieżdżone SUBTOTAL / AGGREGATE
0	wliczane	propagowane	pomijane
1	pomijane	propagowane	pomijane
2	wliczane	pomijane	pomijane
3	pomijane	pomijane	pomijane
4	wliczane	propagowane	wliczane
5	pomijane	propagowane	wliczane
6	wliczane	pomijane	wliczane
7	pomijane	pomijane	wliczane

Dlaczego HotXLS miał opcje AGGREGATE odwrócone?

Bo oryginalne TXLSCalculator.CalcAggregateFunc powstało z parafrazy tabeli, a nie z tabeli. Liczyło ignoreErrors := (optCode >= 4) and (optCode <= 7) i uzbrajało bramkę ukrytych wierszy dla kodów 2, 3, 6 i 7, a polityka zagnieżdżonych agregatów nie była zaimplementowana wcale. Wcześniejszy artykuł o ukrytych wierszach w SUBTOTAL i AGGREGATE wymieniał tę lukę jako otwarte ograniczenie i opisywał stare mapowanie tak, jak wtedy działało; opis był zgodny z kodem i niezgodny z Excelem, i nikt długo tego nie zauważył, bo dwie polityki, które ludzie łączą najczęściej, ukryte wiersze plus błędy, trafiają na kody 3 i 7 w obu tabelach. Zamianę ujawnił dopiero kod jednobitowy: AGGREGATE(9,1,A1:A4) zwracało sumę nieprzefiltrowaną, a AGGREGATE(9,2,...) pomijało ukryte wiersze, wciąż propagując #DIV/0!. Defekt wyszedł ze statycznego przeglądu lxCalc.pas, zarejestrowany jako HXLS-008 w rejestrze znanych problemów projektu, a nie z pliku klienta, co coś mówi o tym, jak rzadko kody jednobitowe pojawiają się w produkcyjnych skoroszytach. Wersja 2.382.0 przepisała dekodowanie na trzy testy przynależności do zbioru i dodała drugą bramkę dla polityki zagnieżdżonej, podłączoną przez nowy callback TXLSIsSubtotalCell, który skoroszyt dostarcza obok TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, v2.382.3 form
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel odrzuca kody spoza 0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... zmapuj function_num na wewnętrzną iftab, przejdź ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Zwróć uwagę, że obie flagi są przypisywane bezwarunkowo, a nie ustawiane tylko wtedy, gdy opcja o to prosi. Wersja 2.382.0 wciąż używała if ... then FIgnoreHiddenRows := True, co znaczyło, że AGGREGATE z kodem 4 zagnieżdżone w SUBTOTAL(109, ...) dziedziczyło zewnętrzną bramkę ukrytych wierszy, zamiast ją wyczyścić. Przypisanie zdekodowanej wartości na wejściu i przywrócenie poprzedniej w bloku finally sprawia, że każde wywołanie AGGREGATE jest właścicielem swojej polityki przez czas swojego przejścia i nic więcej. Wersja 2.382.0 uczyniła też uczciwą postać tablicową: gdy argument wylicza się do jednowymiarowej albo dwuwymiarowej tablicy Variant, CalcAggregateFunc przechodzi teraz każdy element i stosuje politykę błędów na element, podczas gdy stary kod sprawdzał tylko, czy double nie jest NaN-em, a w przeciwnym razie przekazywał całą tablicę do ExcelSum

Dlaczego zewnętrzne AGGREGATE wycieka do formuł, do których się odwołuje?

Bo FIgnoreHiddenRows i FIgnoreSubtotalCells są polami kalkulatora, a kalkulator jest wspólny dla każdej formuły obliczanej w trakcie jednego przeliczenia. Bramki zostały zaprojektowane jako pola pomocnicze właśnie po to, żeby sześć pętli przechodzących komórki mogło je konsultować bez przewlekania parametru przez każdą sygnaturę, i ten projekt jest zdrowy, dopóki wszystko, co biegnie przy uzbrojonej bramce, należy do agregacji, która ją uzbroiła. Założenie łamie się w jednym konkretnym punkcie: FGetValue. Gdy przechodzący pyta skoroszyt o wartość komórki, a ta komórka trzyma formułę bez zbuforowanego wyniku, skoroszyt kompiluje formułę i oblicza ją na miejscu, na tym samym TXLSCalculator, z wciąż ustawionymi zewnętrznymi bramkami. Fixture regresyjny w HotXLS.WorkbookApiTests.pas pokazuje tę awarię na czterech komórkach. A1 trzyma 10, A2 trzyma 20 w ukrytym wierszu, A3 trzyma =1/0, a A4 trzyma =SUBTOTAL(9,A1:A2), którego poprawna wartość to 30. Teraz policz =AGGREGATE(9,7,A1:A4): pomiń ukryte wiersze, pomiń błędy, policz zagnieżdżony subtotal jako wartość. Excel zwraca 10 + 30 = 40. Bez zbuforowanego A4 silnik sprzed 2.382.3 uzbrajał bramkę ukrytych wierszy, dochodził do A4, wywoływał jego obliczenie, a CalcSubtotalFunc dla kodu 9 dziedziczyło uzbrojoną bramkę, bo ustawia flagę tylko dla kodów od 101 do 111 i nigdy jej nie czyści. A4 wyliczało się na 10 zamiast 30, a zewnętrzna suma wracała jako 20. Nic w żadnej z tych formuł nie wspomina o ukrytych wierszach na ścieżce, która dała błędną liczbę

Bramka zagnieżdżonej agregacji wyciekała tak samo w drugą stronę. Przy kodach od 0 do 3 uzbrojone jest FIgnoreSubtotalCells, a ogólne przejście po zakresie w GetValueItemRange je respektuje, więc precedens, którego formuła to =SUM(B1:B3), po cichu wyrzuciłby B2, gdyby B2 akurat zawierał SUBTOTAL. Co gorsza, CalcSubtotalFunc resetuje FIgnoreSubtotalCells do False przy wyjściu, zamiast przywracać poprzednią wartość, więc niezbuforowany precedens SUBTOTAL napotkany w połowie przejścia rozbrajał zewnętrzną bramkę dla każdej komórki po nim. Rejestr znanych problemów projektu wpisuje to pod HXLS-008 jako wyciek zagnieżdżonego stanu wyboru, i to jest właściwa nazwa dla tej klasy błędów: globalna przemijająca flaga, poprawna dla ramki, która ją ustawiła, i błędna dla każdej ramki, która ją dziedziczy

Jak AggregateGetCellValue i AggregateGetItemValue izolują przejście

Poprawka w v2.382.3 stawia granicę wokół każdego miejsca, w którym AGGREGATE czyta wartość, której sam nie policzył. TXLSCalculator.AggregateGetCellValue opakowuje surowe wywołanie FGetValue: zapisuje obie flagi, czyści je, wykonuje pobranie i przywraca je w bloku finally. Zewnętrzna agregacja nadal stosuje swoją politykę do komórki, którą właśnie pobrała, bo testy ukrytych wierszy i zagnieżdżonych komórek dzieją się w przejściu wokół pobrania, ale sama formuła precedensu biegnie bez żadnej polityki, tak jak robi to Excel

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // formuła precedensu ma własną politykę
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue robi to samo dla argumentów innych niż zakresy i musi robić więcej niż tylko czyścić flagi, bo argument taki jak A1:A4/(B1:B4-20) to tablica wyliczana, której kształt elementów musi przetrwać. Wrapper materializuje zwykły zakres do dwuwymiarowej tablicy Variant przez AggregateGetCellValue, mapując komórkę, która zwróciła kod błędu, na VarAsError, żeby politykę błędów nadal dało się stosować na element, i rekurencyjnie przechodzi węzły operatorów binarnych i unarnych (SA_ADD, SA_DIV, SA_UNARMINUS i resztę) przez ApplyArrayBinaryOp i ApplyArrayUnaryOp; wszystko inne spada do zwykłego GetValueItem. Przed materializacją stoją dwa zabezpieczenia: zakres większy niż EffectiveFormulaArrayMemoryLimit zwraca lxErrorResourceLimit, a zakres wieloarkuszowy albo odwrócony zwraca #VALUE!. Kod limitu zasobów celowo nie jest traktowany jako pomijalny błąd komórki nawet przy opcjach 2/3/6/7, bo silnik, który połknąłby własny sygnał braku pamięci, dlatego że użytkownik poprosił o pomijanie #N/A, kłamałby. Wszystkie trzy przejścia AGGREGATE, AggregateCollectRange dla rodziny SUM, AggregateReduceVariance dla STDEV, VAR i PRODUCT oraz AggregateReduceWithK dla MEDIAN i postaci kwantylowych, zostały przełączone z FGetValue i GetValueItem na te dwa wrappery i każde zyskało test zagnieżdżonej komórki przez FIsSubtotalCell

Który błąd zwraca AGGREGATE, gdy nie pomija błędów?

Oryginalny, od wersji v2.382.3. Wersja 2.382.0 wykrywała komórki błędów poprawnie, ale zwijała każdą z nich do lxErrorValue, więc AGGREGATE(9,4,A1:A3) po komórce #DIV/0! zwracało #VALUE!, podczas gdy Excel propaguje pierwszy napotkany błąd bez zmian. Zastępczy helper AggregateErrorCode mapuje Variant na pasujący kod lxError*, niezależnie od tego, czy Variant jest prawdziwym varError, czy jednym z siedmiu łańcuchów błędów, a AggregateValueIsError to teraz po prostu test na niezerowy wynik. Każde przejście zapisuje pierwszy napotkany kod błędu i zwraca ten kod, co znaczy też, że komórka, której formuła nigdy nie została policzona i której błąd przychodzi jako kod zwrotny z FGetValue, a nie jako zbuforowany Variant, propaguje się tak samo jak zbuforowana. Dwie funkcje zliczające dostają specjalne traktowanie wewnątrz AggregateCollectRange i jest to traktowanie zgodne z SUBTOTAL, a nie z SUM. Dla wewnętrznej funkcji 0, COUNT, komórka błędu nigdy nie jest zliczana i nigdy nie jest propagowana, niezależnie od kodu opcji, bo COUNT zlicza wyłącznie liczby. Dla wewnętrznej funkcji 169, COUNTA, komórka błędu jest wartością niepustą i liczy się jako 1, chyba że kod opcji pomija błędy, a wtedy jest pomijana. Ta asymetria to sposób, w jaki Excel traktuje COUNT i COUNTA także poza AGGREGATE, i to jest ten rodzaj szczegółu, który ogólna reguła jeśli błąd, to propaguj, po cichu myli

Co weryfikuje macierz regresyjna ośmiu opcji

Opisany wyżej fixture jest ćwiczone jako pełna macierz w AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: dla każdego kodu opcji od 0 do 7 oblicza i postać SUM, i postać MEDIAN po A1:A4, i sprawdza wynik względem oczekiwania wyprowadzonego ręcznie. Kody 0, 1, 4 i 5 muszą propagować #DIV/0! z A3, bo żaden z nich nie pomija błędów. Kod 2 daje SUM 30 i MEDIAN 15, z 10 i 20 przy pominiętym zagnieżdżonym A4. Kod 3 daje 10 i 10. Kod 6 daje 60 i 20, bo 30 w A4 teraz się liczy. Kod 7 daje 40 i 20, czyli przypadek, który przed poprawką wycieku zwracał 20. Szerszy przebieg akceptacyjny zarejestrowany w rejestrze znanych problemów obejmuje wszystkie dziewiętnaście numerów funkcji względem wszystkich ośmiu kodów, z każdym precedensem i zbuforowanym, i niezbuforowanym, co daje 304 scenariusze na Win32 i Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // subtotal grupowy = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  ukryte pominięte, błąd propagowany
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ukryte + błąd + zagnieżdżone pominięte
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       pominięte tylko błędy
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       przed v2.382.3 było 20
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Gdzie wciąż leży granica

Trzy ograniczenia warto znać, zanim na tym zbudujesz. Po pierwsze, predykat zagnieżdżonej agregacji jest tekstowy. TXLSXWorkbook.GetCalcIsSubtotalCell i jego bliźniak z klasycznego silnika odpowiadają True, gdy formuła komórki zaczyna się od SUBTOTAL(, AGGREGATE( albo _xlfn.AGGREGATE(, z wiodącym znakiem równości albo bez niego, więc formuła taka jak =IF(C1,SUBTOTAL(9,B1:B9),0) albo =SUBTOTAL(9,B1:B9)*2 nie jest rozpoznawana jako zagnieżdżona i zostanie podwójnie policzona przez kody od 0 do 3 tam, gdzie Excel by ją pominął; generator wypisujący policzone subtotale powinien trzymać wywołanie agregacji na czele formuły. Po drugie, izolacja żyje w trzech przejściach AGGREGATE. CalcSubtotalFunc nadal przechodzi przez GetValueItemRange, CollectRangeValues i SubtotalReduceVariance, które wołają FGetValue wprost, więc SUBTOTAL(109, ...), którego zakres zawiera niezbuforowaną formułę precedensu, nadal może przekazać swoją bramkę ukrytych wierszy do tego precedensu. Pełne Recalculate oblicza precedensy przed zależnymi, więc wybierana jest ścieżka zbuforowana i bramka nigdy nie jest dziedziczona; ekspozycja ogranicza się do doraźnego obliczania przez Calculate i do skoroszytów wczytanych bez zbuforowanych wartości, a jeśli polegasz na przeliczaniu przyrostowym po grafie zależności, żeby duże modele pozostały responsywne, ta sama gwarancja kolejności trzyma ten wyciek w uśpieniu. Po trzecie, obie bramki są warunkowane przez Assigned(FIsRowHidden) i Assigned(FIsSubtotalCell). Obie fasady skoroszytu podłączają callbacki w swoich konstruktorach, ale kod, który buduje TXLSCalculator ręcznie, z tylko dwoma oryginalnymi argumentami, dostaje po cichu odziedziczone zachowanie wliczania wszystkiego dla każdego kodu opcji. Gdy suma wygląda źle, a tekst formuły dobrze, śledzenie obliczania krok po kroku to najszybszy sposób, żeby zobaczyć, czy precedens został obliczony pod odziedziczoną bramką, czy callback po prostu nigdy nie został podłączony

Opisany tu silnik obliczeniowy, dekoder opcji, izolowane wrappery pobierania i macierz regresyjna, która je przypina, są dostarczane jako źródło z komponentem arkusza HotXLS dla Delphi, który czyta, zapisuje i przelicza skoroszyty XLS, XLSX i ODS w Delphi i C++Builderze bez instalacji Excela