AGGREGATE-opties en geneste gates in HotXLS voor Delphi

Vier cellen zijn genoeg om =AGGREGATE(9,7,A1:A4) in een zelfgeschreven evaluator 20 te laten teruggeven waar Excel 40 laat zien. A1 bevat 10, A2 bevat 20 op een verborgen rij, A3 bevat =1/0, en A4 bevat =SUBTOTAL(9,A1:A2), een groepssubtotaal ter waarde van 30. Optie 7 betekent: verborgen rijen overslaan, fouten overslaan en geneste subtotalen als waarde meetellen, dus Excel telt 10 en 30 op. De 20 verschijnt wanneer de instelling "verborgen rijen overslaan" van de buitenste aanroep nog aan staat terwijl A4 zijn eigen waarde berekent, waardoor A4 krimpt tot 10. Aan 40 komen vergt twee dingen: de drie optiebits op de juiste posities decoderen, en zorgen dat een formule waarnaar verwezen wordt nooit de opties ziet van de formule die ernaar verwijst

Wat selecteren de AGGREGATE-opties 0 tot en met 7 nu eigenlijk?

Het optieargument is een matrix van drie bits, en de bits zijn onafhankelijk van elkaar. Bit 0 (waarde 1) slaat verborgen rijen over, bit 1 (waarde 2) slaat foutwaarden over, en bit 2 (waarde 4) zet het overslaan van cellen waarvan de eigen formule een SUBTOTAL of AGGREGATE is juist uit, want dat overslaan is de standaard bij de lage codes. Twee delen daarvan liggen makkelijk omgekeerd te lezen. De verborgen-rijen-bit is de laagste bit, dus AGGREGATE(9,1,...) is het gefilterde totaal en AGGREGATE(9,2,...) is de fouttolerante variant. En het geneste beleid loopt tegengesteld aan de andere twee: alleen codes 4 tot en met 7 behandelen een geneste aggregatie als gewoon getal. ECMA-376 Part 1 §18.17.7 definieert SUBTOTAL met dezelfde splitsing tussen verborgen rijen meenemen en negeren, over de codes 1-11 en 101-111; AGGREGATE, in OOXML opgeslagen onder het _xlfn.-voorvoegsel, vouwt die splitsing in zijn optieargument, en de tabel die Microsoft voor de functie publiceert is het contract waaraan een engine moet voldoen

Optie	Verborgen rijen	Foutwaarden	Geneste SUBTOTAL / AGGREGATE
0	meegenomen	doorgegeven	genegeerd
1	genegeerd	doorgegeven	genegeerd
2	meegenomen	genegeerd	genegeerd
3	genegeerd	genegeerd	genegeerd
4	meegenomen	doorgegeven	meegenomen
5	genegeerd	doorgegeven	meegenomen
6	meegenomen	genegeerd	meegenomen
7	genegeerd	genegeerd	meegenomen

Elke code buiten 0 tot en met 7 wordt geweigerd met #VALUE!, precies zoals Excel dat doet

Waarom juist single-bit codes decodes laten misgaan

Een decode geschreven vanuit een parafrase van de tabel in plaats van uit de tabel zelf gaat er meestal zo uit: fouten negeren vanaf code 4, verborgen rijen overslaan bij 2, 3, 6 en 7, en het geneste beleid helemaal weglaten. Zo'n versie kan lang in productie overleven. De combinatie die de meeste mensen echt gebruiken, verborgen rijen plus fouten, landt onder beide lezingen op codes 3 en 7, dus de verwisseling wordt pas zichtbaar bij een single-bit code. AGGREGATE(9,1,A1:A4) geeft dan het ongefilterde totaal terug en AGGREGATE(9,2,...) slaat verborgen rijen over terwijl #DIV/0! gewoon wordt doorgegeven. Echte workbooks gebruiken codes 1 en 2 zelden op zichzelf, en dat is precies waarom een review tegen de gepubliceerde tabel meer oplevert dan wachten op een klantbestand

HotXLS decodeert de opties als drie set-membership-tests, een per kolom van de tabel, en wijst alle drie de resultaten toe zodra de AGGREGATE-aanroep start; bij het einde wordt hersteld wat daarvoor gold. Die toewijzing is met opzet onvoorwaardelijk. Een decode in de stijl van "als de optie erom vraagt, zet de vlag aan" laat een AGGREGATE met code 4 die binnen een SUBTOTAL(109, ...) staat de buitenste verborgen-rijen-instelling erven in plaats van die te wissen. De schets hieronder toont dezelfde regel voor wie een eigen evaluator schrijft of reviewt

// Illustratieve decode voor je eigen evaluator: TAggPolicy en
// DecodeAggregateOptions zijn namen die je zelf definieert, geen HotXLS-API
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // alles anders is #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 gewist
end;

De SUBTOTAL-kant van dezelfde verborgen-rijen-regels, inclusief hoe gefilterde rijen en handmatig verborgen rijen zich tot elkaar verhouden, staat in het artikel over verborgen rijen bij SUBTOTAL en AGGREGATE

Waarom treedt een buitenste AGGREGATE binnen in de formules waarnaar hij verwijst?

Omdat de natuurlijke manier om "verborgen rijen overslaan" door te geven aan zes of zeven cellen-loops een veld op het calculatorobject is, en een calculator elke formule in een herberekeningspass evalueert. Dat ontwerp houdt zolang alles wat draait terwijl het veld gezet is, bij de aggregatie hoort die het zette. Het breekt op een specifiek moment: het ophalen van de cel. Vraagt de walker om een celwaarde en bevat die cel een formule zonder gecachte uitkomst, dan compileert en evalueert de workbook die formule ter plekke, op dezelfde calculator, met de buitenste instellingen nog actief

Loop het openingsvoorbeeld door zo'n engine heen. AGGREGATE met code 7 zet het overslaan van verborgen rijen aan en begint bij A1. A1 geeft 10, A2 is verborgen en wordt overgeslagen, A3 is een fout en wordt overgeslagen. Dan bereikt de walker A4, die nog niet berekend is. De SUBTOTAL(9,A1:A2) van A4 draait, en een SUBTOTAL-implementatie die het overslaan van verborgen rijen alleen aanzet voor codes 101 tot en met 111 (een volstrekt redelijke manier om dat te schrijven) zet het voor code 9 nooit uit. Hij treft de instelling al aan, slaat A2 over en geeft 10 terug. Het buitenste totaal wordt 10 + 10 = 20. Niets in de formule van A4 rept over verborgen rijen

De geneste-aggregate-instelling lekt in beide richtingen. Bij codes 0 tot en met 3 staat hij aan, en een generieke range-walker die hem honoreert zou een precedent als =SUM(B1:B3) B2 laten vallen zodra B2 toevallig een SUBTOTAL bevat. In de andere richting schakelt een SUBTOTAL die het overslaan van geneste cellen uitzet als hij klaar is, in plaats van de eerdere staat te herstellen, het beleid van de buitenste AGGREGATE uit voor de rest van zijn wandeling. Elke evaluator die een transient veld gebruikt om context een recursieve wandeling in te dragen heeft deze vorm; het veld is correct voor het frame dat het zette en fout voor elk frame dat het erft

Het beleid afkappen bij de ophaalgrens

HotXLS zet een grens rond elk punt waar AGGREGATE een waarde leest die hij niet zelf heeft berekend. Voor het ophalen worden beide instellingen bewaard en gewist; erna worden ze in een finally-blok hersteld. De buitenste aggregatie past zijn eigen beleid nog steeds toe op de cel die hij net ophaalde, want de verborgen-rijen- en geneste-cellen-tests gebeuren in de walker rond het ophalen. De formule waarnaar verwezen wordt draait zonder enig beleid, wat overeenkomt met Excel. Alle drie de walker-families gaan door die grens: de SUM-familie (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), de gesloten-vorm-reducties (PRODUCT, STDEV, VAR) en de vormen met een k-argument (MEDIAN, LARGE, SMALL, de percentielen en kwartielen)

// Illustratieve walker voor je eigen evaluator. FPolicy, FetchCell,
// IsRowHidden en IsSubtotalCell zijn members die je zelf definieert
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // de formule waarnaar verwezen wordt bezit zijn eigen beleid
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// binnen de range-wandeling: het buitenste beleid wordt rond de opvraag toegepast
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // // ... pas het foutenbeleid toe op Code / V, en accumuleer daarna ...
  end;
end;

Argumenten die geen gewone ranges zijn, vragen meer dan gewiste vlaggen. Een argument als A1:A4/(B1:B4-20) is een berekende array, en zijn elementvorm moet overleven zodat optie 6 precies de elementen laat vallen die door nul deelden. HotXLS zet elke gerefereerde range door dezelfde grens om in een tweedimensionale array, slaat een cel die mislukte op als foutelement in plaats van af te breken, en past de reken- en vergelijkingsoperators element voor element toe. Een range die meerdere sheets beslaat, of een waarvan de hoeken omgekeerd zijn, geeft #VALUE! terug. Een range die te groot is voor TXLSCalculator.FormulaArrayMemoryLimit (standaard 64 MB) geeft een resource-limiet-resultaat, en dat resultaat ligt bewust buiten de verzameling fouten die opties 2, 3, 6 en 7 mogen overslaan. Het eigen out-of-memory-signaal van de engine inslikken omdat de formule vroeg om #N/A te negeren zou een zelfverzekerd fout getal opleveren, dus de formule faalt in plaats daarvan

Welke fout geeft AGGREGATE terug wanneer hij fouten niet negeert?

De eerste die hij tegenkomt, onveranderd. Elke fout samenvouwen tot #VALUE! is de makkelijke shortcut, en dan wijkt AGGREGATE(9,4,A1:A3) boven een #DIV/0!-cel af van Excel, dat #DIV/0! doorgeeft. HotXLS herleidt zowel echte foutvarianten als de zeven foutstrings (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) naar hun eigen codes, en elke walker geeft de eerste code terug die hij registreerde. Een cel waarvan de formule nooit berekend was, levert zijn fout als returncode van de opvraag in plaats van als gecachte waarde; die propageert precies zoals een gecachte

Twee telfuncties volgen SUBTOTAL in plaats van SUM. Bij functie 2, COUNT, wordt een foutcel nooit meegeteld en nooit doorgegeven, met welke optiecode dan ook, want COUNT telt alleen getallen. Bij functie 3, COUNTA, is een foutcel een niet-lege waarde en telt hij als 1, tenzij de optiecode fouten overslaat. Die asymmetrie is ook hoe Excel COUNT en COUNTA buiten AGGREGATE behandelt, en een algemene "bij fout doorgeven"-regel krijgt beide verkeerd

Alle acht codes controleren op een werkblad

De vier openingscellen vormen een compacte controle over elke code. Evalueer de SUM-vorm en de MEDIAN-vorm over A1:A4 voor elke code van 0 tot 7 en vergelijk met met de hand uitgewerkte waarden. Codes 0, 1, 4 en 5 moeten de #DIV/0! uit A3 doorgeven, want geen van hen slaat fouten over. Code 2 geeft voor SUM 30 en voor MEDIAN 15, uit 10 en 20 met de geneste A4 overgeslagen. Code 3 geeft 10 en 10. Code 6 geeft 60 en 20, omdat de 30 in A4 nu meetelt. Code 7 geeft 40 en 20, en dat is precies de code die een engine zonder ophaalgrens als 20 meldt. De controle is alleen zinvol als A4 ongecached is wanneer de AGGREGATE draait, dus evalueer elk geval een keer met al berekende precedents en een keer zonder

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)';    // groepssubtotaal = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  verborgen overgeslagen, fout propageert
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       verborgen, fout en geneste overgeslagen
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       alleen fouten overgeslagen
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 betekent dat er een instelling in A4 lekte
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN van 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Het opgeslagen bestand in Excel openen en op Ctrl+Alt+F9 drukken geeft een second opinion over dezelfde cellen. Voor een bredere sweep: draai elk functienummer van 1 tot en met 19 tegen alle acht de codes

De callbacks bedraden als je zelf een calculator bouwt

Beide instellingen hangen aan twee callbacks. TXLSCalculator.Create neemt na de value-getter een TXLSIsRowHidden en een TXLSIsSubtotalCell aan, en beide defaulten op nil. Ontbreekt er een, dan wordt de bijbehorende kolom van de tabel nooit toegepast, dus elke optiecode omvat verborgen rijen, geneste aggregaties, of beide. De XLSX- en klassieke XLS-workbookobjecten geven hun eigen implementaties mee in hun constructors; code die zelf een TXLSCalculator boven een eigen databron aanmaakt, moet beide aanleveren. De workbooks herkennen een geneste aggregatie aan een formule die begint met SUBTOTAL(, AGGREGATE( of _xlfn.AGGREGATE(, met of zonder voorafgaand =-teken, en een eigen bron hoort dezelfde regel aan te houden

// TMySheetSource en FormulaAt zijn je eigen types; de drie methode-
// signatures komen overeen met TXLSGetValue, TXLSIsRowHidden en TXLSIsSubtotalCell
// (SheetIndex 1-based, Row en Col zero-based zoals de workbooks ze doorgeven)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern is de sheetnaamtabel die je host al bijhoudt
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

De voorvoegselregel heeft een consequentie voor gegenereerde workbooks. Een generator die =ROUND(SUBTOTAL(9,B2:B9),2) wegschrijft, produceert een cel die de voorvoegseltest niet herkent, dus codes 0 tot en met 3 tellen hem mee en de groep belandt twee keer in het eindtotaal. Houd de SUBTOTAL- of AGGREGATE-aanroep buitenst en zet de afronding in het getalformaat. Een volledige Recalculate evalueert precedents voor afhankelijken, dus aggregaties lezen normaal gesproken gecachte waarden, en onder incrementele herberekening over de afhankelijkheidsgraaf geldt dezelfde volgorde. Als een totaal nog steeds verkeerd lijkt en de formuletekst goed lijkt, laat het stap-voor-stap traceren van de evaluatie zien of een precedent on-demand binnen de wandeling draaide of dat een callback nooit gekoppeld is

De optiesdecoder, de ophaalgrens en de callback-haken die hier beschreven staan, worden meegeleverd met de HotXLS Delphi-spreadsheetcomponent, die XLS-, XLSX- en ODS-workbooks leest, schrijft en herberekent vanuit Delphi- en C++Builder-code, zonder Excel-installatie op de machine