AGGREGATE parinkčių matrica ir vartų nuotėkis HotXLS Delphi

HotXLS, savoji Excel skaičiuoklių komponentė, skirta Delphi ir C++Builder, 2026 m. rugsėjį išleido du susijusius AGGREGATE pataisymus. 2.382.0 versija ištaisė parinkčių argumentą taip, kad kodai 1/3/5/7 nepaiso paslėptų eilučių, 2/3/6/7 nepaiso klaidų, o nuo 0 iki 3 nepaiso įdėtų SUBTOTAL ir AGGREGATE elementų – lygiai taip, kaip dokumentuoja Microsoft. 2.382.3 versija tada sustabdė tuos pasirinkimo žymenis nuo nutekėjimo į tų pačių elementų, į kuriuos funkcija nurodo, vertinimą. Pirmasis defektas erzina taip, kaip lentelių perrašymo defektai visada erzina: bitų pozicijos buvo sukeistos, tad kiekviena formulė, naudojusi nenulinį parinkčių kodą, gaudavo politiką, kurios jos autorius neprašė. Antrasis įdomesnis, nes tai forma, su kuria susidursite bet kuriame vertintuve, naudojančiame trumpalaikį lauką kontekstui perduoti į rekursyvų ėjimą. Išorinė agregacija užkuria vėliavėlę, pereina intervalą ir patraukia elementą, kurio formulė dar nebuvo apskaičiuota. Ta formulė paleidžiama tame pačiame skaičiuotuve, pamato tą pačią užkurtą vėliavėlę ir tyliai agreguoja neteisingas eilutes, duodama skaičių, kurio nukrypimo niekas negali paaiškinti vien iš formulės teksto

Ką AGGREGATE parinktys nuo 0 iki 7 iš tikrųjų parenka?

AGGREGATE parinkčių argumentas yra trijų bitų matrica, ir tie trys bitai yra nepriklausomi. 0 bitas (reikšmė 1) reiškia nepaisyti paslėptų eilučių, 1 bitas (reikšmė 2) reiškia nepaisyti klaidų reikšmių, o 2 bitas (reikšmė 4) reiškia nustoti nepaisyti įdėtų SUBTOTAL ir AGGREGATE elementų, nes jų praleidimas yra numatytasis žemų kodų elgesys. Du dalykai čia lengvai suprantami atvirkščiai. Paslėptų eilučių bitas yra žemasis, o ne vidurinis, tad AGGREGATE(9,1,...) yra filtruotos sumos forma, o AGGREGATE(9,2,...) – klaidoms atspari forma. O įdėtų agregatų politika yra apversta kitų dviejų atžvilgiu: tik kodai nuo 4 iki 7 elementą, kurio paties formulė yra SUBTOTAL arba AGGREGATE, traktuoja kaip paprastą reikšmę. ECMA-376 1 dalies §18.17.7 apibrėžia SUBTOTAL su tuo pačiu paslėptų eilučių įtraukimo ar neįtraukimo skirstymu pagal kodus 1-11 ir 101-111, o AGGREGATE, OOXML failuose saugomas su _xlfn. priešdėliu, tą skirstymą apibendrina į parinkčių argumentą, tad Microsoft paskelbta AGGREGATE funkcijos lentelė yra kontraktas, kurį variklis turi atitikti, o ne patogumo priemonė

Parinktis	Paslėptos eilutės	Klaidų reikšmės	Įdėti SUBTOTAL / AGGREGATE
0	įtraukiamos	propaguojamos	nepaisoma
1	nepaisoma	propaguojamos	nepaisoma
2	įtraukiamos	nepaisoma	nepaisoma
3	nepaisoma	nepaisoma	nepaisoma
4	įtraukiamos	propaguojamos	įtraukiami
5	nepaisoma	propaguojamos	įtraukiami
6	įtraukiamos	nepaisoma	įtraukiami
7	nepaisoma	nepaisoma	įtraukiami

Kodėl HotXLS AGGREGATE parinktis turėjo atvirkščiai?

Nes pirminis TXLSCalculator.CalcAggregateFunc buvo parašytas pagal lentelės perfrazavimą, o ne pagal pačią lentelę. Jis skaičiavo ignoreErrors := (optCode >= 4) and (optCode <= 7) ir užkurdavo paslėptų eilučių vartus kodams 2, 3, 6 ir 7, o įdėtų agregatų politika nebuvo įgyvendinta visai. Ankstesnis straipsnis apie SUBTOTAL ir AGGREGATE paslėptas eilutes tą spragą išvardijo kaip atvirą ribą ir aprašė senąjį susiejimą tokį, koks jis tada buvo išleistas; aprašymas buvo teisingas apie kodą ir neteisingas apie Excel, ir ilgai niekas to nepastebėjo, nes dvi politikos, kurias dauguma žmonių derina, paslėptos ir klaidos, abiejose lentelėse patenka į kodus 3 ir 7. Sukeitimą atskleidė tik vieno bito kodas: AGGREGATE(9,1,A1:A4) grąžino nefiltruotą sumą, o AGGREGATE(9,2,...) praleisdavo paslėptas eilutes ir vis tiek propaguodavo #DIV/0!. Defektas išryškėjo iš statinės lxCalc.pas peržiūros, užregistruotas kaip HXLS-008 projekto žinomų problemų registre, o ne iš kliento failo, ir tai kai ką pasako apie tai, kaip retai vieno bito kodai pasirodo produkcinėse darbo knygose. 2.382.0 versija perrašė dekodavimą kaip tris aibės priklausomybės patikrinimus ir pridėjo antruosius vartus įdėtų agregatų politikai, sujungtus per naują TXLSIsSubtotalCell atgalinį kvietimą, kurį darbo knyga pateikia greta TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, v2.382.3 forma
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel atmeta kodus už 0..7 ribų
  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
  // ... susiejamas function_num su vidine iftab, pereinama ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Atkreipkite dėmesį, kad abi vėliavėlės priskiriamos besąlygiškai, o ne tik nustatomos tada, kai parinktis jų prašo. 2.382.0 versija vis dar naudojo if ... then FIgnoreHiddenRows := True, kas reiškė, kad AGGREGATE su kodu 4, įdėtas į SUBTOTAL(109, ...), paveldėdavo išorinius paslėptų eilučių vartus, o ne juos išvalydavo. Nusidekoduotos reikšmės priskyrimas įėjimo metu ir ankstesnės reikšmės atstatymas finally bloke padaro taip, kad kiekvienas AGGREGATE kvietimas valdo savo politiką tik savo ėjimo trukmei ir nieko daugiau. 2.382.0 versija taip pat padarė masyvo formą sąžiningą: kai argumentas nusivertina į vienmatį ar dvimatį Variant masyvą, CalcAggregateFunc dabar pereina kiekvieną elementą ir taiko klaidų politiką kiekvienam elementui, o senas kodas tikrindavo tik NaN double reikšmę ir kitu atveju visą masyvą perduodavo ExcelSum

Kodėl išorinis AGGREGATE nuteka į formules, į kurias nurodo?

Nes FIgnoreHiddenRows ir FIgnoreSubtotalCells yra skaičiuotuvo laukai, o skaičiuotuvas yra bendras kiekvienai formulei, vertinamai per vieną perskaičiavimą. Tie vartai buvo sumanyti kaip pagalbiniai laukai būtent tam, kad šešios elementų ėjimo kilpos galėtų į juos kreiptis neperleisdamos parametro per kiekvieną parašą, ir tas sumanymas yra tvirtas tol, kol viskas, kas vyksta užkurtiems vartams esant, priklauso agregacijai, kuri juos užkūrė. Prielaida lūžta viename konkrečiame taške: FGetValue. Kai ėjikas paprašo darbo knygos elemento reikšmės, o tame elemente yra formulė be podėlyje laikomo rezultato, darbo knyga tą formulę sukompiliuoja ir įvertina vietoje, tame pačiame TXLSCalculator, dar esant užkurtiems išoriniams vartams. Regresijos testinis failas HotXLS.WorkbookApiTests.pas parodo gedimą su keturiais elementais. A1 laiko 10, A2 laiko 20 paslėptoje eilutėje, A3 laiko =1/0, o A4 laiko =SUBTOTAL(9,A1:A2), kurio teisinga reikšmė yra 30. Dabar įvertinkite =AGGREGATE(9,7,A1:A4): nepaisyti paslėptų eilučių, nepaisyti klaidų, įdėtą subsumą skaičiuoti kaip reikšmę. Excel grąžina 10 + 30 = 40. Kai A4 nepodėliuotas, iki 2.382.3 buvęs variklis užkurdavo paslėptų eilučių vartus, nuėjo iki A4, paleido jo vertinimą, ir CalcSubtotalFunc su kodu 9 paveldėjo užkurtus vartus, nes jis vėliavėlę nustato tik kodams nuo 101 iki 111 ir niekada jos neišvalo. A4 nusivertino į 10 vietoje 30, ir išorinė suma grįžo kaip 20. Nė viena formulė tame kelyje, kuris pagamino neteisingą skaičių, nemini paslėptų eilučių

Įdėtų agregatų vartai nutekėjo taip pat, tik priešinga kryptimi. Su kodais nuo 0 iki 3 FIgnoreSubtotalCells yra užkurti, ir bendras intervalų ėjikas GetValueItemRange juos gerbia, tad precedentas, kurio formulė yra =SUM(B1:B3), tyliai išmestų B2, jei B2 atsitiktinai talpintų SUBTOTAL. Dar blogiau, CalcSubtotalFunc išėjimo metu atstato FIgnoreSubtotalCells į False, o ne atkuria ankstesnę reikšmę, tad ėjimo viduryje pasiektas nepodėliuotas SUBTOTAL precedentas išjungdavo išorinius vartus kiekvienam po jo einančiam elementui. Projekto žinomų problemų registras tai registruoja po HXLS-008 kaip įdėtos pasirinkimo būsenos nutekėjimą, ir tai yra teisingas šios defektų klasės vardas: globali trumpalaikė vėliavėlė, kuri yra teisinga kadrui, kuris ją nustatė, ir neteisinga kiekvienam kadrui, kuris ją paveldi

Kaip AggregateGetCellValue ir AggregateGetItemValue izoliuoja ėjimą

v2.382.3 pataisa pastato ribą aplink kiekvieną tašką, kuriame AGGREGATE skaito reikšmę, kurios pats neapskaičiavo. TXLSCalculator.AggregateGetCellValue apgaubia neapdorotą FGetValue kvietimą: jis išsaugo abi vėliavėles, jas išvalo, atlieka paėmimą ir atkuria jas finally bloke. Išorinė agregacija vis tiek taiko savo politiką elementui, kurį ką tik paėmė, nes paslėptų eilučių ir įdėtų elementų patikrinimai įvyksta ėjike aplink paėmimą, bet pati precedento formulė paleidžiama visai be politikos, ir būtent taip daro 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;        // precedento formulė valdo savo pačios politiką
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue daro tą patį ne intervalo argumentams, ir jis turi padaryti daugiau nei išvalyti vėliavėles, nes argumentas kaip A1:A4/(B1:B4-20) yra apskaičiuotas masyvas, kurio elementų forma turi išlikti. Apvalkalas paverčia paprastą intervalą į dvimatį Variant masyvą per AggregateGetCellValue, susiedamas elementą, grąžinusį klaidos kodą, su VarAsError, kad klaidų politika vis tiek galėtų būti taikoma kiekvienam elementui, ir rekursyviai eina per dvejetainių bei vienetinių operatorių mazgus (SA_ADD, SA_DIV, SA_UNARMINUS ir kitus) su ApplyArrayBinaryOp bei ApplyArrayUnaryOp; visa kita nukrenta į įprastą GetValueItem. Prieš materializavimą stovi dvi sargybos: intervalas, didesnis už EffectiveFormulaArrayMemoryLimit, grąžina lxErrorResourceLimit, o kelių lapų ar apverstas intervalas grąžina #VALUE!. Išteklių limito kodas sąmoningai nėra traktuojamas kaip nepaisoma elemento klaida net su parinktimis 2/3/6/7, nes variklis, kuris prarytų savo paties atminties trūkumo signalą todėl, kad naudotojas paprašė praleisti #N/A, meluotų. Visi trys AGGREGATE ėjikai – AggregateCollectRange SUM šeimai, AggregateReduceVariance STDEV, VAR ir PRODUCT bei AggregateReduceWithK MEDIAN ir kvantilių formoms – buvo perjungti nuo FGetValue ir GetValueItem prie tų dviejų apvalkalų, ir kiekvienas gavo įdėtų elementų patikrinimą per FIsSubtotalCell

Kokią klaidą AGGREGATE grąžina, kai klaidų nepaiso?

Pirminę, nuo v2.382.3. 2.382.0 versija klaidų elementus aptikdavo teisingai, bet kiekvieną iš jų suglaudindavo į lxErrorValue, tad AGGREGATE(9,4,A1:A3) per #DIV/0! elementą grąžindavo #VALUE!, o Excel propaguoja pirmą sutiktą klaidą nepakeistą. Pakaitalo pagalbininkas AggregateErrorCode susieja Variant su atitinkamu lxError* kodu, nesvarbu, ar Variant yra tikras varError, ar viena iš septynių klaidų eilučių, o AggregateValueIsError dabar tėra ne nulinio rezultato patikrinimas. Kiekvienas ėjikas užregistruoja pirmą sutiktą klaidos kodą ir grąžina tą kodą, o tai taip pat reiškia, kad elementas, kurio formulė niekada nebuvo apskaičiuota ir kurio klaida todėl ateina kaip grąžinimo kodas iš FGetValue, o ne kaip podėlyje laikomas Variant, propaguojama taip pat, kaip podėliuota. Dvi skaičiavimo funkcijos gauna specialų apdorojimą AggregateCollectRange viduje, ir tas apdorojimas atitinka SUBTOTAL, o ne SUM. Vidinei funkcijai 0, COUNT, klaidos elementas niekada nėra skaičiuojamas ir niekada nepropaguojamas, nepriklausomai nuo parinkčių kodo, nes COUNT skaičiuoja tik skaičius. Vidinei funkcijai 169, COUNTA, klaidos elementas yra ne tuščia reikšmė ir skaičiuojamas kaip 1, nebent parinkčių kodas nepaiso klaidų, ir tokiu atveju jis praleidžiamas. Ta asimetrija yra tai, kaip Excel traktuoja COUNT ir COUNTA ir už AGGREGATE ribų, ir tai yra tokia smulkmena, kurią bendra taisyklė „jei klaida, tai propaguoti“ tyliai supranta neteisingai

Ką patikrina aštuonių parinkčių regresijos matrica

Aukščiau aprašytas testinis failas paleidžiamas kaip pilna matrica AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates viduje: kiekvienam parinkčių kodui nuo 0 iki 7 jis įvertina ir SUM formą, ir MEDIAN formą per A1:A4 bei patikrina rezultatą prieš ranka išvestą laukiamą reikšmę. Kodai 0, 1, 4 ir 5 turi propaguoti #DIV/0! iš A3, nes nė vienas iš jų nepaiso klaidų. Kodas 2 duoda SUM 30 ir MEDIAN 15 iš 10 ir 20, kai įdėtas A4 praleidžiamas. Kodas 3 duoda 10 ir 10. Kodas 6 duoda 60 ir 20, nes 30 A4 elemente dabar skaičiuojamas. Kodas 7 duoda 40 ir 20 – būtent tas atvejis, kuris iki nuotėkio pataisos grąžindavo 20. Platesnis priėmimo paleidimas, užregistruotas žinomų problemų registre, uždengia visus devyniolika funkcijų numerių prieš visus aštuonis kodus, kai kiekvienas precedentas yra ir podėliuotas, ir nepodėliuotas – 304 scenarijai Win32 ir 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)';   // grupės subsuma = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  paslėptos praleistos, klaida propaguojama
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       paslėptos + klaida + įdėti praleisti
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       praleidžiamos tik klaidos
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       buvo 20 iki v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Kur riba dar yra

Tris ribas verta žinoti prieš ant to statant. Pirma, įdėtų agregatų predikatas yra tekstinis. TXLSXWorkbook.GetCalcIsSubtotalCell ir jo klasikinio variklio dvynys atsako True, kai elemento formulė prasideda SUBTOTAL(, AGGREGATE( arba _xlfn.AGGREGATE(, su lygybės ženklu priekyje ar be jo, tad formulė kaip =IF(C1,SUBTOTAL(9,B1:B9),0) ar =SUBTOTAL(9,B1:B9)*2 nėra atpažįstama kaip įdėta ir bus skaičiuojama du kartus su kodais nuo 0 iki 3 ten, kur Excel ją praleistų; generatorius, išduodantis apskaičiuotas subsumas, turėtų agregavimo kvietimą laikyti formulės pradžioje. Antra, izoliacija gyvena trijuose AGGREGATE ėjikuose. CalcSubtotalFunc vis dar eina per GetValueItemRange, CollectRangeValues ir SubtotalReduceVariance, kurie FGetValue kviečia tiesiogiai, tad SUBTOTAL(109, ...), kurio intervale yra nepodėliuota precedento formulė, vis tiek gali perduoti savo paslėptų eilučių vartus tam precedentui. Pilnas Recalculate precedentus įvertina prieš priklausančius, tad einama podėliuotu keliu ir vartai niekada nepaveldimi; rizika apsiriboja ad hoc vertinimu per Calculate ir darbo knygomis, įkeltomis be podėlyje laikomų reikšmių, o jei remiatės inkrementiniu perskaičiavimu per priklausomybių grafą, kad dideli modeliai liktų greiti, būtent ta pati tvarkos garantija ir palaiko šį nuotėkį užmigdytą. Trečia, abu vartai yra sąlygojami Assigned(FIsRowHidden) ir Assigned(FIsSubtotalCell). Abi darbo knygos fasados atgalinius kvietimus sujungia savo konstruktoriuose, bet kodas, kuris TXLSCalculator pastato rankomis tik su dviem pirminiais argumentais, tyliai gauna senąją viską įtraukiančią elgseną kiekvienam parinkčių kodui. Kai suma atrodo neteisinga, o formulės tekstas atrodo teisingas, vertinimo atsekamumas žingsnis po žingsnio yra greičiausias būdas pamatyti, ar precedentas buvo įvertintas esant paveldėtiems vartams, ar atgalinis kvietimas tiesiog nebuvo prijungtas

Čia aprašytas skaičiavimo variklis, parinkčių dekoderis, izoliuoti paėmimo apvalkalai ir juos priseganti regresijos matrica visi platinami su šaltinio kodu kartu su HotXLS Delphi skaičiuoklių komponente, kuris skaito, rašo ir perskaičiuoja XLS, XLSX ir ODS darbo knygas Delphi ir C++Builder be įdiegto Excel