Opcje AGGREGATE i zagnieżdżone bramki w HotXLS dla Delphi

Cztery komórki wystarczą, żeby ręcznie pisany ewaluator zwrócił dla =AGGREGATE(9,7,A1:A4) 20 tam, gdzie Excel pokazuje 40. A1 trzyma 10, A2 trzyma 20 w ukrytym wierszu, A3 trzyma =1/0, a A4 trzyma =SUBTOTAL(9,A1:A2), subtotal grupowy wart 30. Opcja 7 znaczy: pomiń ukryte wiersze, pomiń błędy i traktuj zagnieżdżone subtotale jako wartości, więc Excel dodaje 10 i 30. Dwudziestka pojawia się, gdy ustawienie „pomiń ukryte wiersze” z wywołania zewnętrznego jest wciąż włączone w chwili, gdy A4 liczy własną wartość, co zbiega A4 do 10. Żeby dostać 40, potrzebne są dwie rzeczy: dekodowanie trzech bitów opcji na właściwych pozycjach i pewność, że formuła precedensu nigdy nie widzi opcji formuły, która się do niej odwołuje

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

Argument opcji AGGREGATE to macierz trzech bitów, a bity są od siebie niezależne. Bit 0 (wartość 1) pomija ukryte wiersze, bit 1 (wartość 2) pomija wartości błędów, a bit 2 (wartość 4) zaprzestaje pomijania komórek, których własna formuła to SUBTOTAL albo AGGREGATE, bo pomijanie ich jest domyślne dla niskich kodów. Dwie rzeczy łatwo tu odczytać na odwrót. Bit ukrytych wierszy to bit najmłodszy, 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 działa odwrotnie niż pozostałe dwie: tylko kody od 4 do 7 traktują zagnieżdżoną agregację jak zwykłą liczbę. 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 OOXML pod przedrostkiem _xlfn., zwija ten podział do argumentu opcji, więc tabela publikowana przez Microsoft dla tej funkcji to kontrakt, który silnik musi spełnić

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

Każdy kod poza zakresem od 0 do 7 jest odrzucany błędem #VALUE!, i dokładnie tak samo zachowuje się Excel

Dlaczego dekodowania psują się właśnie na kodach jednobitowych

Dekodowanie napisane z parafrazy tabeli, a nie z samej tabeli, wygląda zwykle tak: błędy pomijane od kodu 4 w górę, ukryte wiersze pomijane dla 2, 3, 6 i 7, a polityka zagnieżdżonych w ogóle nie istnieje. Taka wersja może długo przeżyć na produkcji. Połączenie, którego ludzie faktycznie używają — ukryte wiersze plus błędy — trafia na kody 3 i 7 przy obu odczytaniach, więc zamiana wychodzi na jaw dopiero na kodzie jednobitowym: AGGREGATE(9,1,A1:A4) zwraca wtedy sumę nieprzefiltrowaną, a AGGREGATE(9,2,...) pomija ukryte wiersze, wciąż propagując #DIV/0!. Prawdziwe skoroszyty rzadko używają kodów 1 i 2 w izolacji — i właśnie dlatego przegląd względem opublikowanej tabeli wyłapuje więcej niż czekanie na plik od klienta

HotXLS dekoduje opcje jako trzy testy przynależności do zbioru, po jednym na kolumnę tabeli, i przypisuje wszystkie trzy wyniki na starcie wywołania AGGREGATE, przywracając poprzedni stan na wyjściu. Przypisanie jest bezwarunkowe nie bez powodu. Dekodowanie napisane w duchu „skoro opcja prosi, włącz flagę” pozwala AGGREGATE z kodem 4 zagnieżdżonemu w SUBTOTAL(109, ...) odziedziczyć zewnętrzne ustawienie ukrytych wierszy zamiast go wyczyścić. Szkic poniżej pokazuje tę samą regułę dla każdego, kto pisze albo recenzuje własny ewaluator

// Ilustracyjne dekodowanie dla własnego ewaluatora: TAggPolicy i
// DecodeAggregateOptions to nazwy, które definiujesz sam, to nie API HotXLS
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // wszystko inne to #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 wyzerowany
end;

Stronę SUBTOTAL tych samych reguł ukrytych wierszy, łącznie z porównaniem wierszy przefiltrowanych i ukrytych ręcznie, opisuje artykuł o ukrytych wierszach w SUBTOTAL i AGGREGATE

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

Bo naturalny sposób, żeby przekazać „pomiń ukryte wiersze” sześciu czy siedmiu pętlom przechodzącym komórki, to pole na obiekcie kalkulatora, a jeden kalkulator oblicza każdą formułę w jednym przebiegu przeliczania. Ten projekt broni się, dopóki wszystko, co biegnie przy ustawionym polu, należy do agregacji, która je ustawiła. Przestaje bronić się w jednym konkretnym momencie: przy pobraniu komórki. Gdy przechodzący pyta o wartość komórki, a ta trzyma formułę bez zbuforowanego wyniku, skoroszyt kompiluje ją i oblicza na miejscu, na tym samym kalkulatorze, przy wciąż obowiązujących ustawieniach zewnętrznych

Przeprowadź przykład otwierający przez taki silnik. AGGREGATE z kodem 7 włącza pomijanie ukrytych wierszy i zaczyna od A1. A1 daje 10, A2 jest ukryte i zostaje pominięte, A3 to błąd i również zostaje pominięte. Potem przechodzący dochodzi do A4, które jeszcze nie zostało policzone. Uruchamia się SUBTOTAL(9,A1:A2) z A4, a implementacja SUBTOTAL, która włącza pomijanie ukrytych wierszy tylko dla kodów od 101 do 111 (całkiem rozsądny sposób pisania), nigdy nie wyłącza go dla kodu 9. Znajduje ustawienie już włączone, pomija A2 i zwraca 10. Zewnętrzna suma staje się 10 + 10 = 20. Nic w formule A4 nie wspomina o ukrytych wierszach

Ustawienie zagnieżdżonych agregatów wycieka w obie strony. Przy kodach od 0 do 3 jest włączone, i ogólne przejście po zakresie, które je respektuje, sprawiłoby, że precedens typu =SUM(B1:B3) po cichu wyrzuciłby B2, gdyby B2 akurat zawierał SUBTOTAL. W drugą stronę SUBTOTAL, który wyłącza pomijanie zagnieżdżonych przy zakończeniu, zamiast przywrócić wcześniejszy stan, unieważnia politykę zewnętrznego AGGREGATE na resztę jego przejścia. Każdy ewaluator, który niesie kontekst do rekurencyjnego przejścia w przemijającym polu, ma ten kształt; pole jest poprawne dla ramki, która je ustawiła, i błędne dla każdej ramki, która je odziedziczy

Odcięcie polityki na granicy pobrania

HotXLS stawia granicę wokół każdego miejsca, w którym AGGREGATE czyta wartość, której sam nie policzył. Przed pobraniem oba ustawienia są zapisywane i czyszczone, po pobraniu są przywracane w bloku finally. Zewnętrzna agregacja wciąż stosuje własną politykę do komórki, którą właśnie pobrała, bo testy ukrytych wierszy i zagnieżdżonych komórek dzieją się w przechodzącym wokół pobrania. Formuła precedensu biegnie bez żadnej polityki, zgodnie z zachowaniem Excela. Przez tę granicę przechodzą wszystkie trzy rodziny przejść: rodzina SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), redukcje w postaci zamkniętej (PRODUCT, STDEV, VAR) i postaci przyjmujące argument k (MEDIAN, LARGE, SMALL, percentyle i kwantyle)

// Ilustracyjny przechodzący dla własnego ewaluatora. FPolicy, FetchCell,
// IsRowHidden i IsSubtotalCell to składowe, które definiujesz sam
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // formuła precedensu ma własną politykę
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// wewnątrz przejścia po zakresie: zewnętrzna polityka jest stosowana wokół pobrania
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... zastosuj politykę błędów do Code / V, potem akumuluj ...
  end;
end;

Argumenty, które nie są zwykłymi zakresami, potrzebują więcej niż wyczyszczonych flag. Argument taki jak A1:A4/(B1:B4-20) to tablica wyliczana, której kształt elementów musi przetrwać, żeby opcja 6 mogła wyrzucić wyłącznie elementy z dzieleniem przez zero. HotXLS zamienia każdy referencjonowany zakres w dwuwymiarową tablicę przez tę samą granicę, zapisuje komórkę, która zawiodła, jako element błędu zamiast przerywać pracę, i stosuje operatory arytmetyczne i porównania element po elemencie. Zakres obejmujący kilka arkuszy albo o odwróconych narożnikach zwraca #VALUE!. Zakres zbyt duży dla TXLSCalculator.FormulaArrayMemoryLimit (domyślnie 64 MB) zwraca wynik limitu zasobów, a ten wynik celowo nie należy do zbioru błędów, które opcje 2, 3, 6 i 7 mogą pominąć. Połknięcie własnego sygnału braku pamięci dlatego, że formuła poprosiła o ignorowanie #N/A, dałoby pewną siebie, błędną liczbę, więc formuła po prostu zawodzi

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

Pierwszy napotkany, bez zmian. Zwijanie każdego błędu do #VALUE! to łatwa droga na skróty i sprawia, że AGGREGATE(9,4,A1:A3) po komórce #DIV/0! rozmija się z Excelem, który przepuszcza #DIV/0! dalej. HotXLS mapuje zarówno prawdziwe warianty błędów, jak i siedem łańcuchów błędów (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) z powrotem na ich własne kody, a każde przejście zwraca pierwszy zapisany kod. Komórka, której formuła nigdy nie została policzona, podaje błąd jako kod zwrotny z pobrania, a nie jako wartość z bufora; propaguje się dokładnie tak samo jak zbuforowana

Dwie funkcje zliczające trzymają się SUBTOTAL, a nie SUM. Dla funkcji 2, 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 funkcji 3, COUNTA, komórka błędu jest wartością niepustą i liczy się jako 1, chyba że kod opcji pomija błędy. Ta asymetria to sposób, w jaki Excel traktuje COUNT i COUNTA także poza AGGREGATE, a ogólna reguła „jeśli błąd, to propaguj” gubi obie

Sprawdzenie wszystkich ośmiu kodów na jednym arkuszu

Cztery komórki z otwarcia tworzą zwięzłe sprawdzenie po każdym kodzie. Dla każdego kodu od 0 do 7 oblicz postać SUM i postać MEDIAN po A1:A4 i porównaj z wartościami wyprowadzonymi 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 — i to jest przypadek, w którym silnik bez granicy pobrania raportuje 20. Sprawdzenie ma sens tylko wtedy, gdy A4 nie ma bufora w chwili uruchomienia AGGREGATE, więc każdy przypadek policz raz z już obliczonymi precedensami i raz bez nich

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 i 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       20 znaczy, że ustawienie wyciekło do A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN z 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Otwarcie zapisanego pliku w Excelu i wciśnięcie Ctrl+Alt+F9 daje drugą opinię o tych samych komórkach. Szerszy przegląd zrobisz, puszczając każdy numer funkcji od 1 do 19 względem wszystkich ośmiu kodów

Podłączanie callbacków, gdy budujesz kalkulator sam

Oba ustawienia zależą od dwóch callbacków. TXLSCalculator.Create przyjmuje TXLSIsRowHidden i TXLSIsSubtotalCell za getterem wartości, a oba domyślnie są nil. Gdy któregoś brakuje, odpowiadająca kolumna tabeli nigdy nie zadziała, więc każdy kod opcji wlicza ukryte wiersze, zagnieżdżone agregacje albo jedno i drugie. Obiekty skoroszytów XLSX i klasycznego XLS podłączają własne implementacje w konstruktorach; kod, który buduje TXLSCalculator ręcznie na własnym źródle danych, musi dostarczyć oba. Skoroszyty rozpoznają zagnieżdżoną agregację po formule zaczynającej się od SUBTOTAL(, AGGREGATE( albo _xlfn.AGGREGATE(, z wiodącym znakiem równości albo bez niego, i własne źródło powinno używać tej samej reguły

// TMySheetSource i FormulaAt to twoje własne typy; trzy sygnatury metod
// odpowiadają TXLSGetValue, TXLSIsRowHidden i TXLSIsSubtotalCell
// (SheetIndex liczony od 1, Row i Col od zera, tak jak przekazują je skoroszyty)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern to tabela nazw arkuszy, którą twój host już prowadzi
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

Reguła przedrostka ma konsekwencję dla generowanych skoroszytów. Generator, który wypisze =ROUND(SUBTOTAL(9,B2:B9),2), produkuje komórkę nierozpoznawaną przez test przedrostka, więc kody od 0 do 3 ją zliczą i grupa wyląduje w sumie ogólnej dwa razy. Trzymaj wywołanie SUBTOTAL albo AGGREGATE na zewnątrz, a zaokrąglenie przenieś do formatu liczby. Pełne Recalculate oblicza precedensy przed zależnymi, więc agregacje normalnie czytają zbuforowane wartości, i ten sam porządek obowiązuje przy przeliczaniu przyrostowym po grafie zależności. Gdy suma nadal wygląda źle, a tekst formuły dobrze, śledzenie obliczania krok po kroku pokaże, czy precedens biegł na żądanie wewnątrz przejścia, czy callback po prostu nigdy nie został podłączony

Dekoder opcji, granica pobrania i punkty podłączania callbacków opisane w tym artykule są dostarczane z komponentem arkusza HotXLS dla Delphi, który czyta, zapisuje i przelicza skoroszyty XLS, XLSX i ODS z kodu Delphi i C++Buildera bez instalacji Excela na maszynie