AGGREGATE-valintamatriisi ja porttivuoto HotXLS:ssä

HotXLS eli natiivi Excel-taulukkokomponentti Delphille ja C++Builderille toimitti kaksi toisiinsa liittyvää AGGREGATE-korjausta syyskuussa 2026. Versio 2.382.0 korjasi valinta-argumentin niin, että koodit 1/3/5/7 jättävät piilotetut rivit huomiotta, 2/3/6/7 jättävät virheet huomiotta ja koodit 0–3 jättävät sisäkkäiset SUBTOTAL- ja AGGREGATE-solut huomiotta täsmälleen niin kuin Microsoft dokumentoi. Versio 2.382.3 esti sitten noita valintalippuja vuotamasta niiden solujen evaluointiin, joihin funktio viittaa. Ensimmäinen vika on nolo sillä tavalla kuin taulukon kopiointivirheet aina ovat: bittien paikat olivat vaihtuneet, joten jokainen kaava, joka käytti nollasta poikkeavaa valintakoodia, sai politiikan jota sen tekijä ei pyytänyt. Toinen on kiinnostavampi, koska se on muoto, johon törmäät jokaisessa evaluoijassa, joka välittää kontekstia rekursiiviseen kävelyyn tilapäiskentän kautta. Ulompi aggregaatti virittää lipun, kävelee alueen läpi ja poimii solun, jonka kaavaa ei ole vielä laskettu. Tuo kaava ajetaan samalla laskurilla, näkee saman viritetyn lipun ja aggregoi hiljaa väärät rivit tuottaen luvun, joka on pielessä määrällä, jota kukaan ei pysty selittämään pelkän kaavatekstin perusteella

Mitä AGGREGATE-valinnat 0–7 oikeastaan valitsevat?

AGGREGATEn valinta-argumentti on kolmen bitin matriisi, ja ne kolme bittiä ovat riippumattomia. Bitti 0 (arvo 1) tarkoittaa piilotettujen rivien ohittamista, bitti 1 (arvo 2) virhearvojen ohittamista ja bitti 2 (arvo 4) tarkoittaa, että sisäkkäisten SUBTOTAL- ja AGGREGATE-solujen ohittaminen lopetetaan, koska niiden ohittaminen on matalien koodien oletus. Kaksi asiaa tässä on helppo käsittää väärinpäin. Piilotetun rivin bitti on matala bitti eikä keskimmäinen, joten AGGREGATE(9,1,...) on suodatetun summan muoto ja AGGREGATE(9,2,...) on virheensietoinen muoto. Ja sisäkkäisaggregaattien politiikka on käänteinen kahteen muuhun nähden: vain koodit 4–7 kohtelevat solua, jonka oma kaava on SUBTOTAL tai AGGREGATE, tavallisena arvona. ECMA-376 osa 1 §18.17.7 määrittelee SUBTOTALIN samalla piilotetut rivit sisältävällä tai poissulkevalla jaolla koodien 1-11 ja 101-111 yli, ja AGGREGATE, joka tallennetaan OOXML-tiedostoihin _xlfn.-etuliitteen alle, yleistää tuon jaon valinta-argumentiksi, joten Microsoftin AGGREGATE-funktiolle julkaisema taulukko on sopimus, jonka moottorin on täytettävä, eikä mukavuus

Valinta	Piilotetut rivit	Virhearvot	Sisäkkäinen SUBTOTAL / AGGREGATE
0	mukana	välittyy	ohitetaan
1	ohitetaan	välittyy	ohitetaan
2	mukana	ohitetaan	ohitetaan
3	ohitetaan	ohitetaan	ohitetaan
4	mukana	välittyy	mukana
5	ohitetaan	välittyy	mukana
6	mukana	ohitetaan	mukana
7	ohitetaan	ohitetaan	mukana

Miksi HotXLS:n AGGREGATE-valinnat olivat väärinpäin?

Koska alkuperäinen TXLSCalculator.CalcAggregateFunc kirjoitettiin taulukon selityksen eikä itse taulukon pohjalta. Se laski ignoreErrors := (optCode >= 4) and (optCode <= 7) ja viritti piilotettujen rivien portin koodeille 2, 3, 6 ja 7, kun taas sisäkkäisaggregaattien politiikkaa ei toteutettu lainkaan. Aiempi artikkeli SUBTOTALIN ja AGGREGATEN piilotetuista riveistä luetteli tuon puutteen avoimena rajoituksena ja kuvasi vanhan kuvauksen sellaisena kuin se silloin toimitettiin; kuvaus oli tarkka koodin osalta ja väärä Excelin osalta, eikä kukaan huomannut sitä pitkään aikaan, koska ne kaksi politiikkaa, jotka useimmat yhdistävät, piilotetut rivit ja virheet, osuvat koodeihin 3 ja 7 molemmissa taulukoissa. Vain yksibittinen koodi paljasti vaihdon: AGGREGATE(9,1,A1:A4) palautti suodattamattoman summan, ja AGGREGATE(9,2,...) ohitti piilotetut rivit mutta välitti yhä #DIV/0!-virheen. Vika nousi esiin lxCalc.pas-tiedoston staattisesta katselmoinnista, kirjattuna HXLS-008-tunnuksella projektin tunnettujen ongelmien rekisteriin, ei asiakkaan tiedostosta, mikä kertoo jotain siitä, miten harvoin yksibittiset koodit esiintyvät tuotantotyökirjoissa. Versio 2.382.0 kirjoitti dekoodauksen uudelleen kolmeksi joukkojäsenyystestiksi ja lisäsi toisen portin sisäkkäispolitiikalle kytkettynä uuden TXLSIsSubtotalCell-kutsuttavan kautta, jonka työkirja tarjoaa TXLSIsRowHidden-kutsuttavan rinnalla

// TXLSCalculator.CalcAggregateFunc, muoto versiosta v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel hylkää koodit 0..7:n ulkopuolelta
  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
  // ... kuvaa function_num sisäiseen iftab-tauluun, kävele ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Huomaa, että molemmat liput sijoitetaan ehdoitta sen sijaan että ne vain asetettaisiin kun valinta pyytää niitä. Version v2.382.0 versio käytti yhä muotoa if ... then FIgnoreHiddenRows := True, mikä tarkoitti, että SUBTOTAL(109, ...)-kutsun sisään sisäkkäin asetettu AGGREGATE koodilla 4 peri ulomman piilotettujen rivien portin sen sijaan että olisi nollannut sen. Dekoodatun arvon sijoittaminen sisääntulossa ja edellisen arvon palauttaminen finally-lohkossa saa jokaisen AGGREGATE-kutsun omistamaan politiikkansa kävelynsä ajaksi eikä mitään sen pidemmälle. Versio 2.382.0 teki myös taulukkemuodosta rehellisen: kun argumentti evaluoituu yksi- tai kaksiulotteiseksi Variant-taulukoksi, CalcAggregateFunc käy nyt läpi jokaisen alkion ja soveltaa virhepolitiikkaa alkio kerrallaan, kun vanha koodi testasi vain NaN-doublea ja antoi muussa tapauksessa koko taulukon ExcelSum-funktiolle

Miksi ulompi AGGREGATE vuotaa kaavoihin, joihin se viittaa?

Koska FIgnoreHiddenRows ja FIgnoreSubtotalCells ovat laskurin kenttiä, ja laskuri on yhteinen jokaiselle kaavalle, joka evaluoidaan yhden uudelleenlaskennan aikana. Portit suunniteltiin raapustuskentiksi juuri siksi, että kuusi solukävelyluuppia voisivat kysyä niiltä välittämättä parametria jokaisen allekirjoituksen läpi, ja tuo suunnittelu on terve niin kauan kuin kaikki se, mikä ajetaan portin ollessa viritettynä, kuuluu aggregaatille joka sen viritti. Oletus rikkoutuu yhdessä tietyssä pisteessä: FGetValue. Kun kävelijä kysyy työkirjalta solun arvoa ja tuo solu sisältää kaavan ilman välimuistiin tallennettua tulosta, työkirja kääntää kaavan ja evaluoi sen paikan päällä samalla TXLSCalculator-laskurilla ulompien porttien ollessa yhä asetettuina. Regressiofixture HotXLS.WorkbookApiTests.pas-tiedostossa näyttää vian neljällä solulla. A1 sisältää 10, A2 sisältää 20 piilotetulla rivillä, A3 sisältää =1/0 ja A4 sisältää =SUBTOTAL(9,A1:A2), jonka oikea arvo on 30. Evaluoi nyt =AGGREGATE(9,7,A1:A4): ohita piilotetut rivit, ohita virheet, laske sisäkkäinen välisumma arvoksi. Excel palauttaa 10 + 30 = 40. Kun A4 ei ollut välimuistissa, versiota 2.382.3 edeltävä moottori viritti piilotettujen rivien portin, käveli A4:ään, laukaisi sen evaluoinnin, ja CalcSubtotalFunc koodille 9 peri viritetyn portin, koska se asettaa lipun vain koodeille 101–111 eikä koskaan nollaa sitä. A4 evaluoitui arvoksi 10 eikä 30, ja ulompi kokonaissumma palasi arvona 20. Kummassakaan kaavassa ei mainita piilotettuja rivejä sillä polulla, joka tuotti väärän luvun

Sisäkkäisaggregaattien portti vuoti samalla tavalla toiseen suuntaan. Koodeilla 0–3 FIgnoreSubtotalCells on viritetty, ja GetValueItemRange-funktion yleiskäyttöinen alueläpikävelijä noudattaa sitä, joten ennakkokaava, jonka kaava on =SUM(B1:B3), pudottaisi hiljaa B2:n, jos B2 sattui sisältämään SUBTOTALIN. Pahempaa: CalcSubtotalFunc nollaa FIgnoreSubtotalCells-arvon Falseksi poistuessaan sen sijaan että palauttaisi edellisen arvon, joten kesken kävelyn tavoitettu välimuistiton SUBTOTAL-ennakkokaava poisti ulomman portin käytöstä jokaiselta sen jälkeiseltä solulta. Projektin tunnettujen ongelmien rekisteri kirjaa tämän HXLS-008-tunnuksen alle sisäkkäisen valintatilan vuotona, ja se on oikea nimi tälle vikaluokalle: globaali tilapäislippu, joka on oikein sen kehyksen kannalta joka sen asetti ja väärä jokaisen sen perivän kehyksen kannalta

Miten AggregateGetCellValue ja AggregateGetItemValue eristävät kävelyn

Version v2.382.3 korjaus asettaa rajan jokaisen sellaisen pisteen ympärille, jossa AGGREGATE lukee arvon, jota se ei itse laskenut. TXLSCalculator.AggregateGetCellValue käärii raa'an FGetValue-kutsun: se tallentaa molemmat liput, nollaa ne, suorittaa haun ja palauttaa ne finally-lohkossa. Ulompi aggregaatti soveltaa yhä omaa politiikkaansa juuri hakemaansa soluun, koska piilotetun rivin ja sisäkkäisen solun testit tehdään kävelijässä haun ympärillä, mutta itse ennakkokaava ajetaan ilman mitään politiikkaa, ja juuri niin Excel tekee

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;        // ennakkokaava omistaa oman politiikkansa
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue tekee saman muille kuin alueargumenteille, ja sen on tehtävä muutakin kuin nollattava lippuja, koska argumentti kuten A1:A4/(B1:B4-20) on laskettu taulukko, jonka alkioiden muodon on säilyttävä. Kääre materialisoi tavallisen alueen kaksiulotteiseksi Variant-taulukoksi AggregateGetCellValue-kutsun kautta kuvaten virhekoodin palauttaneen solun VarAsError-arvoksi, jotta virhepolitiikkaa voidaan yhä soveltaa alkio kerrallaan, ja se etenee rekursiivisesti binääristen ja unaaristen operaattorisolmujen (SA_ADD, SA_DIV, SA_UNARMINUS ja loput) läpi ApplyArrayBinaryOp- ja ApplyArrayUnaryOp-funktioilla; kaikki muu putoaa tavalliseen GetValueItem-kutsuun. Materialisoinnin edessä on kaksi vartijaa: EffectiveFormulaArrayMemoryLimit-arvoa suurempi alue palauttaa lxErrorResourceLimit-koodin, ja usean taulukon tai käänteinen alue palauttaa #VALUE!-virheen. Resurssirajan koodia ei tarkoituksella kohdella ohitettavana soluvirheenä edes valintojen 2/3/6/7 alla, koska moottori, joka nielisi oman muistin loppumisen signaalinsa sen takia että käyttäjä pyysi ohittamaan #N/A-virheen, valehtelisi. Kaikki kolme AGGREGATE-kävelijää, AggregateCollectRange SUM-perheelle, AggregateReduceVariance STDEVille, VARille ja PRODUCTille sekä AggregateReduceWithK MEDIANille ja kvantiilimuodoille, vaihdettiin FGetValue- ja GetValueItem -kutsuista näihin kahteen kääreeseen, ja jokainen sai sisäkkäisen solun testin FIsSubtotalCell-kutsuttavan kautta

Minkä virheen AGGREGATE palauttaa, kun se ei ohita virheitä?

Alkuperäisen, versiosta v2.382.3 lähtien. Versio 2.382.0 tunnisti virhesolut oikein mutta kutisti jokaisen niistä lxErrorValue-arvoksi, joten AGGREGATE(9,4,A1:A3) #DIV/0!-solun yli palautti #VALUE!-virheen, kun Excel välittää ensimmäisen kohtaamansa virheen muuttumattomana. Korvaava apufunktio AggregateErrorCode kuvaa Variantin sitä vastaavaksi lxError*-koodiksi riippumatta siitä, onko Variant aito varError vai yksi seitsemästä virhemerkkijonosta, ja AggregateValueIsError on nyt pelkkä testi nollasta poikkeavalle tulokselle. Jokainen kävelijä tallentaa ensimmäisen näkemänsä virhekoodin ja palauttaa tuon koodin, mikä tarkoittaa myös sitä, että solu, jonka kaavaa ei koskaan laskettu ja jonka virhe saapuu sen takia FGetValue-kutsun paluukoodina eikä välimuistiin tallennettuna Variantina, välittyy samalla tavalla kuin välimuistiin tallennettu. Kaksi laskentafunktiota saa AggregateCollectRange-funktion sisällä erityiskohtelun, ja kohtelu vastaa SUBTOTALIA eikä SUMia. Sisäiselle funktiolle 0, COUNT, virhesolua ei koskaan lasketa eikä välitetä valintakoodista riippumatta, koska COUNT laskee vain numeroita. Sisäiselle funktiolle 169, COUNTA, virhesolu on ei-tyhjä arvo ja lasketaan yhdeksi, ellei valintakoodi ohita virheitä, jolloin se ohitetaan. Tuo epäsymmetria on sama, jolla Excel kohtelee COUNT- ja COUNTA-funktioita AGGREGATEN ulkopuolellakin, ja se on juuri sellainen yksityiskohta, jonka yleinen ”jos virhe, välitä” -sääntö hoitaa hiljaa väärin

Mitä kahdeksan valinnan regressiomatriisi varmentaa

Yllä kuvattu fixture ajetaan täytenä matriisina AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates-testissä: jokaiselle valintakoodille 0–7 se evaluoi sekä SUM- että MEDIAN-muodon alueen A1:A4 yli ja tarkistaa tuloksen käsin johdettua odotusta vasten. Koodien 0, 1, 4 ja 5 on välitettävä A3:n #DIV/0!-virhe, koska yksikään niistä ei ohita virheitä. Koodi 2 antaa SUMiksi 30 ja MEDIANiksi 15 luvuista 10 ja 20 sisäkkäisen A4:n ollessa ohitettu. Koodi 3 antaa 10 ja 10. Koodi 6 antaa 60 ja 20, koska A4:n 30 lasketaan nyt mukaan. Koodi 7 antaa 40 ja 20, mikä on se tapaus joka palautti 20 ennen vuotokorjausta. Laajempi hyväksymisajo, joka on kirjattu tunnettujen ongelmien rekisteriin, kattaa kaikki yhdeksäntoista funktionumeroa kaikkia kahdeksaa koodia vasten, jokaisen ennakkokaavan sekä välimuistiin tallennettuna että ilman, eli 304 skenaariota Win32:lla ja Win64:llä

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)';   // ryhmän välisumma = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  piilotetut ohitetaan, virhe välittyy
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       piilotetut + virhe + sisäkkäiset ohitetaan
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       vain virheet ohitetaan
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       oli 20 ennen versiota v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Missä raja yhä kulkee

Kolme rajaa kannattaa tietää ennen kuin rakennat tämän päälle. Ensinnäkin sisäkkäisaggregaatin predikaatti on tekstuaalinen. TXLSXWorkbook.GetCalcIsSubtotalCell ja sen klassisen moottorin kaksonen vastaavat True, kun solun kaava alkaa merkkijonolla SUBTOTAL(, AGGREGATE( tai _xlfn.AGGREGATE(, yhtäsuuruusmerkin kanssa tai ilman, joten kaava kuten =IF(C1,SUBTOTAL(9,B1:B9),0) tai =SUBTOTAL(9,B1:B9)*2 ei tunnistu sisäkkäiseksi ja koodit 0–3 laskevat sen kahteen kertaan siinä missä Excel ohittaisi sen; generaattorin, joka tuottaa laskettuja välisummia, kannattaa pitää aggregaattikutsu kaavan alussa. Toiseksi eristys asuu kolmessa AGGREGATE-kävelijässä. CalcSubtotalFunc kulkee yhä GetValueItemRange-, CollectRangeValues- ja SubtotalReduceVariance -funktioiden kautta, jotka kutsuvat FGetValue-funktiota suoraan, joten SUBTOTAL(109, ...), jonka alue sisältää välimuistittoman ennakkokaavan, voi yhä välittää piilotettujen rivien porttinsa tuohon ennakkokaavaan. Täysi Recalculate evaluoi ennakkokaavat ennen niistä riippuvia, joten välimuistipolku valitaan eikä porttia koskaan peritä; altistus rajoittuu Calculate-kutsun kautta tehtävään ad hoc -evaluointiin ja työkirjoihin, jotka ladataan ilman välimuistiin tallennettuja arvoja, ja jos nojaat inkrementaaliseen uudelleenlaskentaan riippuvuusgraafin yli pitääksesi suuret mallit reagoivina, sama järjestystakuu pitää tämän vuodon lepotilassa. Kolmanneksi molemmat portit on ehdollistettu Assigned(FIsRowHidden)- ja Assigned(FIsSubtotalCell) -tarkistuksilla. Molemmat työkirjajulkisivut kytkevät kutsuttavat konstruktoreissaan, mutta koodi, joka rakentaa TXLSCalculator-laskurin käsin vain kahdella alkuperäisellä argumentilla, saa perityn kaiken sisällyttävän käyttäytymisen jokaiselle valintakoodille hiljaa. Kun kokonaissumma näyttää väärältä ja kaavateksti oikealta, evaluoinnin vaiheittainen jäljitys on nopein tapa nähdä, evaluoitiinko ennakkokaava perityn portin alla vai jäikö kutsuttava yksinkertaisesti koskaan kytkemättä

Tässä kuvattu laskentamoottori, valintojen dekooderi, eristetyt hakukääreet ja ne kiinnittävä regressiomatriisi toimitetaan kaikki lähdekoodina HotXLS Delphi -taulukkokomponentin mukana, joka lukee, kirjoittaa ja laskee uudelleen XLS-, XLSX- ja ODS-työkirjoja Delphissä ja C++Builderissa ilman Excel-asennusta