Opțiunile AGGREGATE și scurgerea de poartă în HotXLS Delphi

HotXLS, componenta nativă de foi de calcul Excel pentru Delphi și C++Builder, a livrat în septembrie 2026 două reparații AGGREGATE înrudite. Versiunea 2.382.0 a corectat argumentul de opțiuni astfel încât codurile 1/3/5/7 ignoră rândurile ascunse, 2/3/6/7 ignoră erorile, iar 0 până la 3 ignoră celulele SUBTOTAL și AGGREGATE imbricate, exact cum documentează Microsoft. Versiunea 2.382.3 a oprit apoi acele flag-uri de selecție să se scurgă în evaluarea chiar a celulelor pe care le referă funcția. Primul defect este jenant în felul în care sunt mereu bug-urile de transcriere de tabele: pozițiile biților erau inversate, așa că fiecare formulă care folosea un cod de opțiuni diferit de zero primea o politică pe care autorul ei nu o ceruse. Al doilea este mai interesant, pentru că este o formă pe care o veți întâlni în orice evaluator care folosește un câmp tranzitoriu ca să paseze context într-o parcurgere recursivă. O agregare exterioară armează un flag, parcurge un interval și trage o celulă a cărei formulă nu a fost încă calculată. Formula aceea rulează pe același calculator, vede același flag armat și agregă în tăcere rândurile greșite, producând un număr care diferă cu o sumă pe care nimeni nu o poate explica doar din textul formulei

Ce selectează de fapt opțiunile AGGREGATE de la 0 la 7?

Argumentul de opțiuni al lui AGGREGATE este o matrice de trei biți, iar cei trei biți sunt independenți. Bitul 0 (valoarea 1) înseamnă ignoră rândurile ascunse, bitul 1 (valoarea 2) înseamnă ignoră valorile de eroare, iar bitul 2 (valoarea 4) înseamnă oprește ignorarea celulelor SUBTOTAL și AGGREGATE imbricate, pentru că sărirea lor este implicitul pentru codurile mici. Două lucruri aici sunt ușor de înțeles pe dos. Bitul de rânduri ascunse este bitul inferior, nu cel din mijloc, așa că AGGREGATE(9,1,...) este forma de total filtrat, iar AGGREGATE(9,2,...) este cea tolerantă la erori. Iar politica de agregare imbricată este inversată față de celelalte două: doar codurile 4 până la 7 tratează o celulă a cărei formulă este ea însăși un SUBTOTAL sau AGGREGATE ca pe o valoare obișnuită. ECMA-376 partea 1 §18.17.7 definește SUBTOTAL cu aceeași separare include-sau-exclude a rândurilor ascunse între codurile 1-11 și 101-111, iar AGGREGATE, stocat în fișierele OOXML sub prefixul _xlfn., generalizează separarea aceea în argumentul de opțiuni, așa că tabelul pe care îl publică Microsoft pentru funcția AGGREGATE este contractul pe care un motor trebuie să îl îndeplinească, nu o comoditate

Opțiune	Rânduri ascunse	Valori de eroare	SUBTOTAL / AGGREGATE imbricat
0	incluse	propagate	ignorate
1	ignorate	propagate	ignorate
2	incluse	ignorate	ignorate
3	ignorate	ignorate	ignorate
4	incluse	propagate	incluse
5	ignorate	propagate	incluse
6	incluse	ignorate	incluse
7	ignorate	ignorate	incluse

De ce avea HotXLS opțiunile AGGREGATE pe dos?

Pentru că TXLSCalculator.CalcAggregateFunc original a fost scris dintr-o parafrază a tabelului, nu din tabel. Calcula ignoreErrors := (optCode >= 4) and (optCode <= 7) și arma poarta de rânduri ascunse pentru codurile 2, 3, 6 și 7, în timp ce politica de agregare imbricată nu era implementată deloc. Articolul anterior despre rândurile ascunse la SUBTOTAL și AGGREGATE listase golul acela ca limită deschisă și descria vechea mapare așa cum era livrată atunci; descrierea era corectă în privința codului și greșită în privința Excel, și nimeni nu a observat mult timp pentru că cele două politici pe care le combină cei mai mulți, ascunse plus erori, cad pe codurile 3 și 7 sub ambele tabele. Doar un cod cu un singur bit a expus inversarea: AGGREGATE(9,1,A1:A4) întorcea suma nefiltrată, iar AGGREGATE(9,2,...) sărea rândurile ascunse, propagând totodată #DIV/0!. Defectul a ieșit la iveală dintr-o revizuire statică a lui lxCalc.pas, înregistrat ca HXLS-008 în registrul de probleme cunoscute al proiectului, nu dintr-un fișier de client, ceea ce spune ceva despre cât de rar apar codurile cu un singur bit în registrele de lucru de producție. Versiunea 2.382.0 a rescris decodarea ca trei teste de apartenență la mulțimi și a adăugat o a doua poartă pentru politica imbricată, legată printr-un callback nou TXLSIsSubtotalCell pe care registrul de lucru îl furnizează alături de TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, v2.382.3 form
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel respinge codurile din afara 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
  // ... mapează function_num la iftab-ul interior, parcurge ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Observați că cele două flag-uri sunt atribuite necondiționat, nu doar setate când opțiunea le cere. Versiunea 2.382.0 încă folosea if ... then FIgnoreHiddenRows := True, ceea ce însemna că un AGGREGATE cu codul 4 imbricat într-un SUBTOTAL(109, ...) moștenea poarta exterioară de rânduri ascunse în loc să o curețe. Atribuirea valorii decodate la intrare și restaurarea valorii anterioare în blocul finally face ca fiecare apel AGGREGATE să își dețină politica pe durata parcurgerii lui și nimic mai mult. Versiunea 2.382.0 a făcut și forma de tablou onestă: când un argument se evaluează la un tablou Variant unidimensional sau bidimensional, CalcAggregateFunc parcurge acum fiecare element și aplică politica de erori per element, în timp ce codul vechi testa doar dacă era un double NaN și altfel preda tot tabloul lui ExcelSum

De ce se scurge un AGGREGATE exterior în formulele pe care le referă?

Pentru că FIgnoreHiddenRows și FIgnoreSubtotalCells sunt câmpuri pe calculator, iar calculatorul este partajat de fiecare formulă evaluată în timpul unei recalculări. Porțile au fost proiectate ca câmpuri de lucru tocmai ca șase bucle de parcurgere de celule să le poată consulta fără să treacă un parametru prin fiecare semnătură, iar designul acela este solid atâta timp cât tot ce rulează cu o poartă armată aparține agregării care a armat-o. Presupunerea se rupe într-un punct anume: FGetValue. Când un parcurgător cere registrului de lucru valoarea unei celule, iar celula aceea ține o formulă fără rezultat din cache, registrul compilează formula și o evaluează pe loc, pe același TXLSCalculator, cu porțile exterioare încă setate. Fixture-ul de regresie din HotXLS.WorkbookApiTests.pas arată eșecul cu patru celule. A1 ține 10, A2 ține 20 pe un rând ascuns, A3 ține =1/0, iar A4 ține =SUBTOTAL(9,A1:A2), a cărei valoare corectă este 30. Acum evaluați =AGGREGATE(9,7,A1:A4): ignoră rândurile ascunse, ignoră erorile, numără subtotalul imbricat ca valoare. Excel întoarce 10 + 30 = 40. Cu A4 necachetă, motorul de dinainte de 2.382.3 arma poarta de rânduri ascunse, parcurgea până la A4, declanșa evaluarea ei, iar CalcSubtotalFunc pentru codul 9 moștenea poarta armată, pentru că el doar setează flag-ul pentru codurile 101 până la 111 și nu îl curăță niciodată. A4 se evalua la 10 în loc de 30, iar totalul exterior ieșea 20. Nimic din vreuna dintre formule nu menționează rânduri ascunse pe calea care a produs numărul greșit

Poarta de agregare imbricată s-a scurs la fel în cealaltă direcție. Cu codurile 0 până la 3, FIgnoreSubtotalCells este armat, iar parcurgătorul generic de intervale din GetValueItemRange îl respectă, așa că un precedent a cărui formulă este =SUM(B1:B3) ar scăpa în tăcere B2 dacă B2 se nimerea să conțină un SUBTOTAL. Mai rău, CalcSubtotalFunc resetează FIgnoreSubtotalCells la False la ieșire în loc să restabilească valoarea anterioară, așa că un precedent SUBTOTAL necachet atins la mijlocul parcurgerii dezarma poarta exterioară pentru fiecare celulă de după el. Registrul de probleme cunoscute al proiectului înregistrează asta sub HXLS-008 ca scurgere de stare de selecție imbricată, și acesta este numele corect pentru clasa de bug: un flag global tranzitoriu care este corect pentru cadrul care l-a setat și greșit pentru fiecare cadru care îl moștenește

Cum izolează AggregateGetCellValue și AggregateGetItemValue parcurgerea

Reparația din v2.382.3 pune o graniță în jurul fiecărui punct în care AGGREGATE citește o valoare pe care nu a calculat-o ea însăși. TXLSCalculator.AggregateGetCellValue împachetează apelul brut FGetValue: salvează ambele flag-uri, le curăță, face citirea și le restaurează într-un bloc finally. Agregarea exterioară își aplică în continuare propria politică pe celula pe care tocmai a citit-o, pentru că testele de rând ascuns și de celulă imbricată se fac în parcurgător, în jurul citirii, dar formula precedentă rulează ea însăși fără nicio politică, care este ce face 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;        // o formulă precedentă își deține propria politică
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue face același lucru pentru argumentele care nu sunt intervale și trebuie să facă mai mult decât să curețe flag-uri, pentru că un argument precum A1:A4/(B1:B4-20) este un tablou calculat a cărui formă de elemente trebuie să supraviețuiască. Wrapper-ul materializează un interval simplu într-un tablou Variant bidimensional prin AggregateGetCellValue, mapând o celulă care a întors un cod de eroare la VarAsError, ca politica de erori să poată fi aplicată în continuare per element, și recursează prin nodurile de operatori binari și unari (SA_ADD, SA_DIV, SA_UNARMINUS și restul) cu ApplyArrayBinaryOp și ApplyArrayUnaryOp; orice altceva cade pe GetValueItem normal. Două paze stau în fața materializării: un interval mai mare decât EffectiveFormulaArrayMemoryLimit întoarce lxErrorResourceLimit, iar un interval multi-foaie sau inversat întoarce #VALUE!. Un cod de limită de resurse nu este tratat deliberat ca eroare de celulă ignorabilă nici sub opțiunile 2/3/6/7, pentru că un motor care și-ar înghiți propriul semnal de memorie epuizată pentru că utilizatorul a cerut să sară peste #N/A ar minți. Toți cei trei parcurgători AGGREGATE, AggregateCollectRange pentru familia SUM, AggregateReduceVariance pentru STDEV, VAR și PRODUCT și AggregateReduceWithK pentru MEDIAN și formele de cuantilă, au fost trecuți de la FGetValue și GetValueItem la cele două wrappere, iar fiecare a primit testul de celulă imbricată prin FIsSubtotalCell

Ce eroare întoarce AGGREGATE când nu ignoră erorile?

Pe cea originală, din v2.382.3. Versiunea 2.382.0 detecta corect celulele de eroare, dar le prăbușea pe toate în lxErrorValue, așa că AGGREGATE(9,4,A1:A3) peste o celulă #DIV/0! întorcea #VALUE!, pe când Excel propagă neschimbată prima eroare pe care o întâlnește. Helper-ul de înlocuire AggregateErrorCode mapează un Variant la codul lxError* corespunzător, fie că Variant-ul este un varError autentic, fie unul dintre cele șapte șiruri de eroare, iar AggregateValueIsError este acum doar un test pentru un rezultat diferit de zero. Fiecare parcurgător înregistrează primul cod de eroare pe care îl vede și întoarce codul acela, ceea ce înseamnă și că o celulă a cărei formulă nu a fost niciodată calculată, și a cărei eroare sosește deci ca cod de întoarcere din FGetValue în loc de un Variant cachet, se propagă la fel ca una cachetă. Două funcții de numărare primesc tratament special în interiorul AggregateCollectRange, iar tratamentul corespunde lui SUBTOTAL, nu lui SUM. Pentru funcția interioară 0, COUNT, o celulă de eroare nu este niciodată numărată și niciodată propagată, indiferent de codul de opțiuni, pentru că COUNT numără doar numere. Pentru funcția interioară 169, COUNTA, o celulă de eroare este o valoare ne-goală și se numără ca 1, în afară de cazul în care codul de opțiuni ignoră erorile, când este sărită. Asimetria aceea este felul în care Excel tratează COUNT și COUNTA și în afara lui AGGREGATE, și este genul de detaliu pe care o regulă generică de „dacă e eroare, propagă” îl greșește în tăcere

Ce verifică matricea de regresie cu opt opțiuni

Fixture-ul descris mai sus este exercitat ca matrice completă în AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: pentru fiecare cod de opțiuni de la 0 la 7 evaluează atât forma SUM, cât și forma MEDIAN peste A1:A4 și verifică rezultatul față de o așteptare derivată manual. Codurile 0, 1, 4 și 5 trebuie să propage #DIV/0! din A3, pentru că niciunul dintre ele nu ignoră erorile. Codul 2 dă SUM 30 și MEDIAN 15, din 10 și 20 cu A4 imbricată sărită. Codul 3 dă 10 și 10. Codul 6 dă 60 și 20, pentru că 30 din A4 se numără acum. Codul 7 dă 40 și 20, care este cazul ce întorcea 20 înainte de reparația scurgerii. Rularea de acceptanță mai largă înregistrată în registrul de probleme cunoscute acoperă toate cele nouăsprezece numere de funcție împotriva tuturor celor opt coduri, cu fiecare precedent atât cachet, cât și necachet, pentru 304 scenarii pe 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 de grup = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  ascunse sărite, eroarea se propagă
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       ascunse + erori + imbricate sărite
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       doar erorile sărite
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       era 20 înainte de v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Unde este încă granița

Trei limite merită știute înainte de a construi pe asta. Întâi, predicatul de agregare imbricată este textual. TXLSXWorkbook.GetCalcIsSubtotalCell și geamănul lui din motorul clasic răspund True când formula unei celule începe cu SUBTOTAL(, AGGREGATE( sau _xlfn.AGGREGATE(, cu sau fără semnul egal din frunte, așa că o formulă precum =IF(C1,SUBTOTAL(9,B1:B9),0) sau =SUBTOTAL(9,B1:B9)*2 nu este recunoscută ca imbricată și va fi numărată de două ori de codurile 0 până la 3 acolo unde Excel ar sări-o; un generator care emite subtotaluri calculate ar trebui să țină apelul de agregare în capul formulei. Al doilea, izolarea trăiește în cei trei parcurgători AGGREGATE. CalcSubtotalFunc parcurge în continuare prin GetValueItemRange, CollectRangeValues și SubtotalReduceVariance, care apelează FGetValue direct, așa că un SUBTOTAL(109, ...) al cărui interval conține o formulă precedentă necachetă poate încă să îi paseze poarta de rânduri ascunse precedentului acela. Un Recalculate complet evaluează precedentele înaintea dependenților, așa că se ia calea cachetă și poarta nu este niciodată moștenită; expunerea este limitată la evaluarea ad hoc prin Calculate și la registrele de lucru încărcate fără valori cachete, iar dacă vă bazați pe recalcularea incrementală peste graful de dependențe ca să țineți modelele mari receptive, aceeași garanție de ordonare este ce ține scurgerea asta dormantă. Al treilea, ambele porți sunt condiționate de Assigned(FIsRowHidden) și Assigned(FIsSubtotalCell). Ambele fațade de registru de lucru leagă callback-urile în constructorii lor, dar codul care construiește un TXLSCalculator manual, cu doar cele două argumente originale, primește în tăcere comportamentul moștenit de includere a tot pentru fiecare cod de opțiuni. Când un total arată greșit și textul formulei arată corect, urmărirea evaluării pas cu pas este calea cea mai rapidă de a vedea dacă un precedent a fost evaluat sub o poartă moștenită sau dacă un callback pur și simplu nu a fost atașat niciodată

Motorul de calcul descris aici, decodorul de opțiuni, wrapperele de citire izolate și matricea de regresie care le fixează sunt toate livrate ca sursă împreună cu componenta HotXLS pentru Delphi, care citește, scrie și recalculează registre de lucru XLS, XLSX și ODS în Delphi și C++Builder fără o instalare Excel