AGGREGATE-valgmatrise og portlekkasje i HotXLS for Delphi

HotXLS, den native Excel-regnearkkomponenten for Delphi og C++Builder, leverte to beslektede AGGREGATE-fikser i september 2026. Versjon 2.382.0 rettet valgargumentet så kodene 1/3/5/7 ignorerer skjulte rader, 2/3/6/7 ignorerer feil, og 0 til 3 ignorerer nestede SUBTOTAL- og AGGREGATE-celler, nøyaktig slik Microsoft dokumenterer det. Versjon 2.382.3 stoppet deretter de valgflaggene fra å lekke inn i evalueringen av nettopp de cellene funksjonen refererer til. Den første defekten er pinlig på den måten tabellavskrivingsfeil alltid er: bitposisjonene var byttet om, så hver formel som brukte en valgkode forskjellig fra null, fikk en policy forfatteren ikke ba om. Den andre er mer interessant, fordi det er en form du vil møte i enhver evaluator som bruker et transient felt til å føre kontekst inn i en rekursiv gjennomgang. En ytre aggregering armerer et flagg, går gjennom et område, og henter en celle hvis formel ennå ikke er beregnet. Den formelen kjører på samme kalkulator, ser det samme armerte flagget, og aggregerer stille feil rader, og produserer et tall som avviker med et beløp ingen kan forklare ut fra formelteksten alene

Hva velger egentlig AGGREGATE-valgene 0 til 7?

Valgargumentet til AGGREGATE er en matrise på tre biter, og de tre bitene er uavhengige. Bit 0 (verdi 1) betyr ignorer skjulte rader, bit 1 (verdi 2) betyr ignorer feilverdier, og bit 2 (verdi 4) betyr slutt å ignorere nestede SUBTOTAL- og AGGREGATE-celler, fordi å hoppe over dem er standarden for de lave kodene. To ting med dette er lette å få bakvendt. Skjult rad-biten er den lave biten, ikke den midterste, så AGGREGATE(9,1,...) er den filtrerte totalformen og AGGREGATE(9,2,...) er den feiltolerante. Og policyen for nestede aggregeringer er invertert i forhold til de to andre: bare kodene 4 til 7 behandler en celle hvis egen formel er en SUBTOTAL eller AGGREGATE som en vanlig verdi. ECMA-376 del 1 §18.17.7 definerer SUBTOTAL med samme deling mellom inkludering og ekskludering av skjulte rader over kodene 1-11 og 101-111, og AGGREGATE, lagret i OOXML-filer under prefikset _xlfn., generaliserer den delingen inn i valgargumentet, så tabellen Microsoft publiserer for AGGREGATE-funksjonen er kontrakten en motor må oppfylle, snarere enn en bekvemmelighet

Valg	Skjulte rader	Feilverdier	Nestet SUBTOTAL / AGGREGATE
0	inkludert	videreført	ignorert
1	ignorert	videreført	ignorert
2	inkludert	ignorert	ignorert
3	ignorert	ignorert	ignorert
4	inkludert	videreført	inkludert
5	ignorert	videreført	inkludert
6	inkludert	ignorert	inkludert
7	ignorert	ignorert	inkludert

Hvorfor hadde HotXLS AGGREGATE-valgene bakvendt?

Fordi den opprinnelige TXLSCalculator.CalcAggregateFunc var skrevet fra en omskriving av tabellen i stedet for fra tabellen. Den beregnet ignoreErrors := (optCode >= 4) and (optCode <= 7) og armerte skjult rad-porten for kodene 2, 3, 6 og 7, mens policyen for nestede aggregeringer ikke var implementert i det hele tatt. Den tidligere artikkelen om skjulte rader i SUBTOTAL og AGGREGATE listet det gapet som en åpen grense og beskrev den gamle mappingen slik den da ble levert; beskrivelsen var nøyaktig om koden og feil om Excel, og ingen la merke til det på lenge fordi de to policyene folk flest kombinerer, skjulte pluss feil, lander på kodene 3 og 7 under begge tabellene. Bare en enkeltbitkode avslørte byttet: AGGREGATE(9,1,A1:A4) returnerte den ufiltrerte summen, og AGGREGATE(9,2,...) hoppet over skjulte rader mens den fortsatt videreførte #DIV/0!. Defekten dukket opp fra en statisk gjennomgang av lxCalc.pas, logget som HXLS-008 i prosjektets register over kjente problemer, ikke fra en kundefil, noe som sier noe om hvor sjelden enkeltbitkodene forekommer i produksjonsarbeidsbøker. Versjon 2.382.0 skrev om dekodingen til tre settmedlemskapstester og la til en andre port for den nestede policyen, koblet gjennom en ny TXLSIsSubtotalCell-callback som arbeidsboken tilbyr ved siden av TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, formen fra v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel avviser koder utenfor 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
  // ... map function_num til den indre iftab, gå gjennom ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Legg merke til at de to flaggene tilordnes ubetinget i stedet for bare å settes når valget ber om det. Versjon 2.382.0 brukte fortsatt if ... then FIgnoreHiddenRows := True, noe som betydde at en AGGREGATE med kode 4 nestet inne i en SUBTOTAL(109, ...) arvet den ytre skjult rad-porten i stedet for å tømme den. Å tilordne den dekodede verdien ved inngang og gjenopprette den forrige verdien i finally-blokken gjør at hvert AGGREGATE-kall eier sin egen policy i varigheten av gjennomgangen sin og ikke mer. Versjon 2.382.0 gjorde også arrayformen ærlig: når et argument evaluerer til en en- eller todimensjonal Variant-array, går CalcAggregateFunc nå gjennom hvert element og bruker feilpolicyen per element, der den gamle koden bare testet for en NaN-double og ellers ga hele arrayen til ExcelSum

Hvorfor lekker en ytre AGGREGATE inn i formlene den refererer til?

Fordi FIgnoreHiddenRows og FIgnoreSubtotalCells er felt på kalkulatoren, og kalkulatoren deles av hver formel som evalueres i løpet av én omberegning. Portene ble designet som skrapefelt nettopp for at seks celle-gjennomgangssløyfer kunne konsultere dem uten å tre en parameter gjennom hver signatur, og det designet er solid så lenge alt som kjører mens en port er armert, tilhører aggregeringen som armerte den. Antakelsen bryter sammen på ett bestemt punkt: FGetValue. Når en gjennomgang ber arbeidsboken om en celleverdi og den cellen holder en formel uten hurtiglagret resultat, kompilerer arbeidsboken formelen og evaluerer den på stedet, på den samme TXLSCalculator, med de ytre portene fortsatt satt. Regresjonsfiksturen i HotXLS.WorkbookApiTests.pas viser feilen med fire celler. A1 holder 10, A2 holder 20 på en skjult rad, A3 holder =1/0, og A4 holder =SUBTOTAL(9,A1:A2), hvis korrekte verdi er 30. Evaluer nå =AGGREGATE(9,7,A1:A4): ignorer skjulte rader, ignorer feil, tell den nestede subtotalen som en verdi. Excel returnerer 10 + 30 = 40. Med A4 uhurtiglagret armerte motoren før 2.382.3 skjult rad-porten, gikk til A4, utløste evalueringen av den, og CalcSubtotalFunc for kode 9 arvet den armerte porten, fordi den bare noen gang setter flagget for kodene 101 til 111 og aldri tømmer det. A4 evaluerte til 10 i stedet for 30, og den ytre totalen kom tilbake som 20. Ingenting i noen av formlene nevner skjulte rader på veien som produserte det gale tallet

Porten for nestede aggregeringer lakk på samme måte i den andre retningen. Med kodene 0 til 3 er FIgnoreSubtotalCells armert, og den generiske områdegjennomgangen i GetValueItemRange ærer det, så en presedens hvis formel er =SUM(B1:B3) ville stille droppet B2 hvis B2 tilfeldigvis inneholdt en SUBTOTAL. Verre: CalcSubtotalFunc nullstiller FIgnoreSubtotalCells til False ved utgang i stedet for å gjenopprette den forrige verdien, så en uhurtiglagret SUBTOTAL-presedens som ble nådd midt i gjennomgangen, disarmerte den ytre porten for hver celle etter den. Prosjektets register over kjente problemer fører dette under HXLS-008 som lekkasje av nestet utvalgstilstand, og det er det riktige navnet på denne feilklassen: et globalt transient flagg som er riktig for rammen som satte det og galt for hver ramme som arver det

Hvordan AggregateGetCellValue og AggregateGetItemValue isolerer gjennomgangen

Fiksen i v2.382.3 legger en grense rundt hvert punkt der AGGREGATE leser en verdi den ikke selv har beregnet. TXLSCalculator.AggregateGetCellValue pakker det rå FGetValue-kallet inn: den lagrer begge flaggene, tømmer dem, utfører hentingen, og gjenoppretter dem i en finally-blokk. Den ytre aggregeringen bruker fortsatt sin egen policy på cellen den nettopp hentet, fordi testene for skjulte rader og nestede celler skjer i gjennomgangen rundt hentingen, men selve presedensformelen kjører uten noen policy i det hele tatt, som er det Excel gjør

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;        // en presedensformel eier sin egen policy
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue gjør det samme for argumenter som ikke er områder, og den må gjøre mer enn å tømme flagg, fordi et argument som A1:A4/(B1:B4-20) er en beregnet array hvis elementform må overleve. Wrapperen materialiserer et vanlig område til en todimensjonal Variant-array gjennom AggregateGetCellValue, mapper en celle som returnerte en feilkode til VarAsError så feilpolicyen fortsatt kan brukes per element, og den rekurserer gjennom de binære og unære operatornodene (SA_ADD, SA_DIV, SA_UNARMINUS og resten) med ApplyArrayBinaryOp og ApplyArrayUnaryOp; alt annet faller gjennom til den vanlige GetValueItem. To vakter står foran materialiseringen: et område større enn EffectiveFormulaArrayMemoryLimit returnerer lxErrorResourceLimit, og et område over flere ark eller med omvendt rekkefølge returnerer #VALUE!. En ressursgrensekode behandles bevisst ikke som en ignorerbar cellefeil selv under valgene 2/3/6/7, siden en motor som slukte sitt eget minnemangelsignal fordi brukeren ba om å hoppe over #N/A, ville lyve. Alle tre AGGREGATE-gjennomgangene, AggregateCollectRange for SUM-familien, AggregateReduceVariance for STDEV, VAR og PRODUCT, og AggregateReduceWithK for MEDIAN og kvantilformene, ble byttet fra FGetValue og GetValueItem til de to wrapperne, og hver av dem fikk testen for nestede celler gjennom FIsSubtotalCell

Hvilken feil returnerer AGGREGATE når den ikke ignorerer feil?

Den opprinnelige, siden v2.382.3. Versjon 2.382.0 oppdaget feilceller korrekt men slo hver av dem sammen til lxErrorValue, så AGGREGATE(9,4,A1:A3) over en #DIV/0!-celle returnerte #VALUE!, der Excel viderefører den første feilen den møter, uendret. Hjelperen som erstattet den, AggregateErrorCode, mapper en Variant til den matchende lxError*-koden, enten Varianten er en ekte varError eller en av de sju feilstrengene, og AggregateValueIsError er nå bare en test på et resultat forskjellig fra null. Hver gjennomgang registrerer den første feilkoden den ser og returnerer den koden, noe som også betyr at en celle hvis formel aldri ble beregnet, og hvis feil derfor kommer som en returkode fra FGetValue i stedet for som en hurtiglagret Variant, videreføres på samme måte som en hurtiglagret. To tellefunksjoner får spesiell behandling inne i AggregateCollectRange, og behandlingen følger SUBTOTAL snarere enn SUM. For indre funksjon 0, COUNT, telles en feilcelle aldri og videreføres aldri uavhengig av valgkoden, fordi COUNT bare teller tall. For indre funksjon 169, COUNTA, er en feilcelle en ikke-tom verdi og teller som 1 med mindre valgkoden ignorerer feil, i hvilket tilfelle den hoppes over. Den asymmetrien er hvordan Excel behandler COUNT og COUNTA også utenfor AGGREGATE, og det er den typen detalj en generisk regel om «hvis feil, viderefør» stille gjør feil

Hva den åtte-alternativs regresjonsmatrisen verifiserer

Fiksturen beskrevet over kjøres som en full matrise i AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: for hver valgkode fra 0 til 7 evaluerer den både SUM-formen og MEDIAN-formen over A1:A4 og sjekker resultatet mot en håndutledet forventning. Kodene 0, 1, 4 og 5 må videreføre #DIV/0! fra A3, siden ingen av dem ignorerer feil. Kode 2 gir SUM 30 og MEDIAN 15, fra 10 og 20 med den nestede A4 hoppet over. Kode 3 gir 10 og 10. Kode 6 gir 60 og 20, fordi de 30 i A4 nå teller med. Kode 7 gir 40 og 20, som er tilfellet som returnerte 20 før lekkasjefiksen. Den bredere akseptansekjøringen som er registrert i registeret over kjente problemer, dekker alle nitten funksjonsnumre mot alle åtte koder, med hver presedens både hurtiglagret og uhurtiglagret, i 304 scenarier på Win32 og 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)';   // gruppesubtotal = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  skjulte hoppet over, feil videreføres
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       skjulte + feil + nestet hoppet over
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       bare feil hoppet over
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       var 20 før v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Hvor grensen fortsatt går

Tre grenser er verdt å kjenne før du bygger på dette. For det første er predikatet for nestede aggregeringer tekstlig. TXLSXWorkbook.GetCalcIsSubtotalCell og dens tvilling i den klassiske motoren svarer True når en celles formel begynner med SUBTOTAL(, AGGREGATE( eller _xlfn.AGGREGATE(, med eller uten det ledende likhetstegnet, så en formel som =IF(C1,SUBTOTAL(9,B1:B9),0) eller =SUBTOTAL(9,B1:B9)*2 gjenkjennes ikke som nestet og vil bli dobbelttelt av kodene 0 til 3 der Excel ville hoppet over den; en generator som skriver ut beregnede subtotaler, bør holde aggregeringskallet i starten av formelen. For det andre bor isolasjonen i de tre AGGREGATE-gjennomgangene. CalcSubtotalFunc går fortsatt gjennom GetValueItemRange, CollectRangeValues og SubtotalReduceVariance, som kaller FGetValue direkte, så en SUBTOTAL(109, ...) hvis område inneholder en uhurtiglagret presedensformel, kan fortsatt føre skjult rad-porten sin inn i den presedensen. En full Recalculate evaluerer presedenser før dependenter, så den hurtiglagrede veien brukes og porten arves aldri; eksponeringen er begrenset til ad hoc-evaluering gjennom Calculate og til arbeidsbøker lastet uten hurtiglagrede verdier, og hvis du lener deg på inkrementell omberegning over avhengighetsgrafen for å holde store modeller responsive, er den samme rekkefølgegarantien det som holder denne lekkasjen sovende. For det tredje er begge portene betinget av Assigned(FIsRowHidden) og Assigned(FIsSubtotalCell). Begge arbeidsbokfasadene kobler callbacksene i konstruktørene sine, men kode som bygger en TXLSCalculator for hånd med bare de to opprinnelige argumentene, får den gamle inkluder-alt-oppførselen for hver valgkode, i stillhet. Når en total ser gal ut og formelteksten ser riktig ut, er å spore evalueringen trinn for trinn den raskeste måten å se om en presedens ble evaluert under en arvet port, eller om en callback rett og slett aldri ble koblet til

Beregningsmotoren beskrevet her, valgdekoderen, de isolerte hentewrapperne og regresjonsmatrisen som fester dem, leveres alle som kildekode med HotXLS Delphi spreadsheet component, som leser, skriver og regner om XLS-, XLSX- og ODS-arbeidsbøker i Delphi og C++Builder uten en Excel-installasjon