SUBTOTAL i AGGREGATE a ukryte wiersze w Delphi z HotXLS

Jeśli SUBTOTAL(109, ...) i SUBTOTAL(9, ...) zwracają tę samą liczbę na skoroszycie zawierającym ukryte wiersze, jedna z tych dwóch wartości jest zła. To jedna z najłatwiejszych pułapek przy budowaniu silnika obliczeń arkusza, a HotXLS, natywny komponent arkusza Excel dla Delphi i C++Buildera, omija ją, pozwalając swojemu silnikowi obliczeniowemu pytać arkusz, czy dany wiersz jest ukryty

Ten objaw rzadko przychodzi jako pytanie o kody formuł. Przychodzi jako niezgodność: zadanie wsadowe na serwerze liczy sumę, użytkownik otwiera ten sam plik w Excelu z nałożonym filtrem, a obie liczby różnią się o to, co akurat sumowały odfiltrowane wiersze. Nikt nie podejrzewa funkcji agregującej, bo ciąg formuły w komórce jest identyczny w obu miejscach. Różnica leży wyłącznie w tym, co ewaluator mógł zobaczyć

Dlaczego SUBTOTAL 109 miałby włączać ukryte wiersze?

Bo w większości projektów silników warstwa ewaluująca formułę nigdy nie dowiaduje się o widoczności wiersza. Oczywisty projekt dochodzi do wartości komórek przez pojedynczy callback wartości, jak TXLSGetValue w lxCalc.pas, który odpowiada wartością dla trójki (arkusz, wiersz, kolumna) i niczym więcej. Widoczność to atrybut prezentacyjny zapisany w rekordzie wiersza, a jeśli nic innego nie podróżuje w dół łańcucha wywołań, silnik kończy z jedną ścieżką agregacji, do której rozwiązują się obie połowy tabeli numerów funkcji SUBTOTAL. To nie jest błąd klasy zaokrąglenia: polityka ukrytych wierszy to cały powód istnienia drugiej połowy tabeli. ECMA-376 Part 1, opublikowany jako ISO/IEC 29500-1, definiuje SUBTOTAL w definicjach funkcji formuł (§18.17.7) z pierwszym argumentem, który wybiera i wewnętrzną agregację, i politykę ukrytych wierszy. Kody od 1 do 11 mapują się na AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR i VARP, wliczając przy tym wartości w ręcznie ukrytych wierszach. Kody od 101 do 111 wybierają te same jedenaście agregacji i je wykluczają. Użytkownik, który wpisuje 109 zamiast 9, składa świadome oświadczenie o ukrytych danych, a silnik, który zgniata to rozróżnienie, po cichu unieważnia to oświadczenie

Na co mapują się numery funkcji wewnątrz silnika

HotXLS rozwiązuje pierwszy argument SUBTOTAL w CalcSubtotalFunc, która normalizuje kody od 101 do 111 do tych samych wewnętrznych identyfikatorów funkcji co kody od 1 do 11, a potem dyspatchuje po samej agregacji. Większość rodziny płynie przez przyrostowy akumulator ExcelSum, ten obsługujący SUM, COUNT, COUNTA, MIN, MAX i AVERAGE. Pięć nie może: STDEV, VAR, STDEVP, VARP i PRODUCT potrzebują przebiegu w postaci zamkniętej po danych, więc CalcSubtotalFunc kieruje wewnętrzne kody 12, 46, 193, 194 i 183 do osobnego reduktora, SubtotalReduceVariance. Ten podział to pierwsza rzecz warta zmapowania, zanim cokolwiek się dotknie, bo dwie niezależne ścieżki agregacji znaczy dwie niezależne pętle przechodzące po komórkach, a sprawdzenie ukrytych wierszy dodane tylko do jednej z nich daje najgorszy możliwy wynik: SUBTOTAL(109, ...) respektuje filtr, podczas gdy SUBTOTAL(107, ...) na tym samym zakresie już nie. HotXLS ma ich sześć, gdy doliczyć AGGREGATE, rozłożonych między ewaluację zakresu, zwykłe zbieranie zakresu i trzy osobne reduktory

Dlaczego pole robocze zamiast sześciu nowych sygnatur?

Bo przeciągnięcie nowego parametru przez sześć funkcji przechodzących po komórkach, plus wszystko, co je woła, to szeroka zmiana w gorącej ścieżce kodu dla jednego booleana. HotXLS ma precedens dla alternatywy: pole przemijające na kalkulatorze, w tym samym duchu co pole robocze, którego GetRangeInfo używa, żeby zapisać moment, gdy odwołanie 3D rozwiązało się do zewnętrznego skoroszytu. Bramka ukrytych wierszy to drugie takie: typ callbacku TXLSIsRowHidden, zadeklarowany jako funkcja (SheetIndex, wiersz) zwracająca Boolean, przechowywany w FIsRowHidden, plus przemijająca flaga FIgnoreHiddenRows. Flaga jest uzbrajana na wejściu do CalcSubtotalFunc, gdy kod funkcji mieści się w zakresie od 101 do 111, i na wejściu do CalcAggregateFunc dla kodów opcji AGGREGATE wybierających wykluczenie ukrytych wierszy. Każda pętla przechodząca po komórkach sprawdza ją następnie i pomija jeden wiersz, gdy jest ustawiona, kosztem jednej linijki każda

// Wzorzec powtarzany we wszystkich sześciu pętlach przechodzących po komórkach
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... włóż Cells[rr, cc] do akumulatora ...
    end;
  end;

Dwa szczegóły w kodzie uzbrajania decydują o poprawności całego mechanizmu. Flaga jest zapisywana i przywracana, a nie po prostu ustawiana i czyszczona, ponieważ argument SUBTOTAL może zawierać wyrażenie, które uruchamia własną ewaluację, podczas gdy zewnętrzna agregacja wciąż jest na stosie, a ta zagnieżdżona praca nie może odziedziczyć ani zniszczyć zewnętrznej bramki. A przywrócenie znajduje się w bloku finally, ponieważ CalcSubtotalFunc ma kilka wczesnych wyjść dla kodów błędów; flaga pozostawiona uzbrojona po zwrocie błędu po cichu zepsułaby kolejną, niepowiązaną formułę w kolejności przeliczania

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // agreguj po Item.Child[2] .. Item.Child[ChildCount]
  // każda ścieżka Exit poniżej jest objęta przez finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Test Assigned to to, co trzyma zgodność API kalkulatora. Konstruktor TXLSCalculator bierze trzeci parametr domyślnie nil, więc kod budujący kalkulator wywołaniem dwuargumentowym kompiluje się bez zmian i dostaje zachowanie wliczające ukryte wiersze

Skąd właściwie bierze się bit ukrycia wiersza?

Z arkusza, przez dwa różne źródła, bo HotXLS niesie dwa silniki skoroszytów. Strona legacy BIFF odpowiada z TXLSRowInfoList.GetHidden, osiąganego przez TXLSWorkbook.GetRowHidden. Strona OOXML odpowiada z TXLSXWorksheet.GetRowHidden, osiąganego przez TXLSXWorkbook.GetCalcRowHidden. Oba są podłączane do kalkulatora w chwili konstrukcji, obok callbacku wartości komórki, który odzwierciedlają. Konwencje numeracji wierszy to miejsce, w którym ten rodzaj mostka zwykle się psuje, więc warto je wypowiedzieć wprost. Kalkulator podaje callbackowi wiersz liczony od 0, zgodnie ze współrzędnymi, których TXLSGetValue już używa. Arkusz XLSX kluczuje swoją mapę ukrycia wierszy numerem wiersza licznym od 1, dokładnie tak, jak numeruje wiersze Excel — to samo, co wystawia publiczna właściwość RowHidden[ARow]. Mostek XLSX dodaje więc jeden przed wyszukaniem, a mostek BIFF nie, bo TXLSRowInfoList już jest liczony od 0. Oba mostki traktują indeks arkusza albo wiersz spoza prawidłowego zakresu jako widoczne, więc zapytanie poza zakresem degraduje się do odpowiedzi wliczającej ukryte wiersze, zamiast gubić dane

Co się zmienia dla filtrowanych skoroszytów

To przypadek, w którym polityka ukrytych wierszy znaczy najwięcej. Nałożenie AutoFiltra w HotXLS przez ApplyAutoFilter ewaluuje kryteria kolumny i ukrywa każdy wiersz danych, który nie pasuje — dokładnie to, co robi Excel, gdy użytkownik kliknie rozwijaną listę filtra. Te wiersze są niewidoczne dla użytkownika, a ponieważ silnik obliczeniowy konsultuje ten sam stan wierszy, serwerowe SUBTOTAL(109, ...) raportuje sumę po filtrze, a nie przed nim

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // ukrywa niepasujące wiersze

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // Wartość komórki jest zgodna z tym, co Excel pokazuje dla tego samego filtra,
    // a VisibleRows mówi, ile wierszy ją zasilało

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

Ręczne ukrywanie działa tak samo, bo RowHidden[ARow] := True to ten sam stan, który zapisuje filtr. Ta równoważność jest w Excelu celowa i obowiązuje też w HotXLS. Jedna konsekwencja zasługuje na wzmiankę w dokumentacji, która jedzie z twoimi generowanymi skoroszytami: suma policzona kodem 109 to liczba zależna od widoku, więc odbiorca, który wyczyści filtr, ją zmienia. Gdy raport musi podawać stałą liczbę niezależnie od tego, co czytelnik zrobi z widokiem, kod 9 jest właściwym wyborem i zawsze był. Filtry, walidacja i tabele są omówione razem w artykule o walidacji danych, AutoFiltrze i tabelach. Ponieważ ukrywanie wierszy nie dotyka żadnej formuły, samo z siebie nie brudzi też grafu zależności, co warto wiedzieć, gdy polegasz na przeliczaniu przyrostowym po brudnym podgrafie, żeby duże skoroszyty pozostawały responsywne

Kody opcji AGGREGATE

AGGREGATE to SUBTOTAL z drugim argumentem polityki, a HotXLS obsługuje ją w CalcAggregateFunc. Argument opcji koduje niezależne przełączniki: czy zagnieżdżone wywołania SUBTOTAL i AGGREGATE wewnątrz zakresu są pomijane, czy wartości w ukrytych wierszach są pomijane oraz czy wartości błędów są tłumione zamiast propagowane. HotXLS uzbraja wspólną bramkę ukrytych wierszy dla kodów opcji 2, 3, 6 i 7 i tłumi wartości błędów dla kodów opcji od 4 do 7. Argument numeru funkcji wybiera potem agregację dokładnie tak, jak robi to SUBTOTAL, wliczając kierowanie wariancji, odchylenia standardowego i iloczynu przez ich własne reduktory. Prawdziwe skoroszyty niemal zawsze umieszczają formuły SUBTOTAL poza zakresami, po których agregują inne formuły SUBTOTAL; jeśli twój generator buduje nakładające się zakresy agregacji, trzymaj wewnętrzne sumy poza zakresem zewnętrznym, zamiast polegać na kodach opcji w deduplikacji

Bramka arności w tym samym dyspatcherze

Ten sam dyspatcher niesie sprawdzenie liczby argumentów, a powód projektowy jest ten sam, co za polem roboczym: umieścić sprawdzenie tam, gdzie da się je napisać raz. Przy około 280 wbudowanych ciałach funkcji pozostawienie każdemu weryfikacji własnej liczby argumentów względem Item.ChildCount nie daje spójnej granicy dla przypadku zbyt wielu argumentów, a pułapkę łatwo przegapić: wywołanie takie jak =SIN(1,2) trafia do ciała funkcji, które ogląda pierwszy argument, ignoruje nadwyżkę i zwraca wiarygodną liczbę tam, gdzie Excel zwraca #VALUE!. HotXLS trzyma zadeklarowaną arność każdego wbudowanego w rejestrze funkcji, wystawianą jako THashFunc.ArgsCnt z -1 oznaczającym funkcję wariadyczną, jak SUM, IF czy CONCAT, przepuszcza ją przez właściwość TXLSFormula.FuncArgsCntByPtg i sprawdza w jednej bramce na szczycie GetValueItemFunc, głównego dyspatchera

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] to węzeł funkcji
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) daje #VALUE!
    Exit;
  end;
end;

Bramka odrzuca zbyt wiele argumentów i celowo nic nie mówi o zbyt małej ich liczbie. Pominięcie końcowego argumentu opcjonalnego jest legalne w Excelu dla VLOOKUP, SUBSTITUTE i długiej listy innych, więc symetryczne sprawdzenie zepsułoby poprawne formuły, próbując złapać niepoprawne. Nieznane identyfikatory zgłaszają się jako wariadyczne i całkiem omijają bramkę, co trzyma funkcje zdefiniowane przez użytkownika poza jej zasięgiem; jeśli rejestrujesz własne funkcje, zachowanie opisane w przewodniku po silniku formuł i funkcjach niestandardowych pozostaje nietknięte. Przypadek zbyt małej liczby argumentów zostaje przy każdym ciele funkcji, bo każde z tych ciał ma własną semantykę kodów błędów

Opisany tu silnik obliczeniowy, obie fasady skoroszytów oraz zasilające je API AutoFiltra i widoczności wierszy są częścią komponentu arkusza Excel HotXLS dla Delphi, który dostarczany jest z pełnym kodem źródłowym dla Delphi i C++Buildera i nie wymaga zainstalowanego Excela na maszynie, na której działa