Opzioni AGGREGATE e gate annidati in HotXLS per Delphi

Bastano quattro celle per far restituire 20 a =AGGREGATE(9,7,A1:A4) in un motore di valutazione scritto a mano mentre Excel mostra 40. A1 contiene 10, A2 contiene 20 su una riga nascosta, A3 contiene =1/0 e A4 contiene =SUBTOTAL(9,A1:A2), un subtotale di gruppo del valore di 30. L'opzione 7 significa salta le righe nascoste, salta gli errori e conta i subtotali annidati come valori, quindi Excel somma 10 e 30. Il 20 compare quando l'impostazione "skip hidden rows" della chiamata esterna è ancora attiva mentre A4 calcola il proprio valore, il che riduce A4 a 10. Per arrivare a 40 servono due cose: decodificare i tre bit delle opzioni nelle posizioni giuste, e fare in modo che una formula referenziata non veda mai le opzioni della formula che l'ha referenziata

Cosa selezionano davvero le opzioni AGGREGATE da 0 a 7?

L'argomento options è una matrice di tre bit, e i bit sono indipendenti fra loro. Il bit 0 (valore 1) salta le righe nascoste, il bit 1 (valore 2) salta i valori di errore, e il bit 2 (valore 4) smette di saltare le celle la cui formula è a sua volta una SUBTOTAL o una AGGREGATE, perché saltarle è il comportamento predefinito per i codici bassi. Due parti di tutto questo si leggono facilmente al contrario. Il bit delle righe nascoste è il bit basso, quindi AGGREGATE(9,1,...) è il totale filtrato e AGGREGATE(9,2,...) è quello tollerante agli errori. E la politica sui nested gira al contrario rispetto alle altre due: solo i codici da 4 a 7 trattano un aggregato annidato come un numero qualsiasi. ECMA-376 Part 1 §18.17.7 definisce SUBTOTAL con la stessa suddivisione include/escludi righe nascoste sui codici 1–11 e 101–111; AGGREGATE, memorizzata in OOXML con il prefisso _xlfn., assorbe quella suddivisione nel suo argomento options, e la tabella che Microsoft pubblica per la funzione è il contratto che un motore deve rispettare

Opzione	Righe nascoste	Valori di errore	SUBTOTAL / AGGREGATE annidato
0	incluse	propagato	ignorato
1	ignorate	propagato	ignorato
2	incluse	ignorato	ignorato
3	ignorate	ignorato	ignorato
4	incluse	propagato	incluso
5	ignorate	propagato	incluso
6	incluse	ignorato	incluso
7	ignorate	ignorato	incluso

Qualsiasi codice fuori dall'intervallo da 0 a 7 viene rifiutato con #VALUE!, ed è anche ciò che fa Excel

Perché i codici a bit singolo sono dove le decodifiche vanno storte

Una decodifica scritta da una parafrasi della tabella invece che dalla tabella stessa tende ad avere questo aspetto: ignora gli errori per i codici da 4 in su, salta le righe nascoste per 2, 3, 6 e 7, e lascia la politica sui nested completamente fuori. Quella versione può sopravvivere a lungo in produzione. La combinazione che la gente usa davvero, righe nascoste più errori, cade sui codici 3 e 7 con entrambe le letture, quindi lo scambio si vede solo su un codice a bit singolo. AGGREGATE(9,1,A1:A4) restituisce allora la somma non filtrata, e AGGREGATE(9,2,...) salta le righe nascoste mentre continua a propagare #DIV/0!. I workbook reali usano raramente i codici 1 e 2 da soli, ed è proprio per questo che una verifica contro la tabella pubblicata trova più di quanto ne trovi l'attesa del file di un cliente

HotXLS decodifica le opzioni come tre test di appartenenza a insiemi, uno per colonna della tabella, e assegna tutti e tre i risultati quando la chiamata AGGREGATE inizia, ripristinando alla fine ciò che era in vigore prima. L'assegnazione è incondizionata di proposito. Una decodifica scritta come "se l'opzione lo chiede, accendi il flag" lascia che una AGGREGATE con codice 4 dentro una SUBTOTAL(109, ...) erediti l'impostazione esterna sulle righe nascoste invece di azzerarla. Lo schema qui sotto mostra la stessa regola per chi scrive o revisiona il proprio motore di valutazione

// Decodifica illustrativa per il vostro motore di valutazione: TAggPolicy e
// DecodeAggregateOptions sono nomi che definite voi, non API di HotXLS
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // tutto il resto è #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 a zero
end;

Il lato SUBTOTAL delle stesse regole sulle righe nascoste, incluso il confronto fra righe filtrate e righe nascoste a mano, è trattato nell'articolo sulle righe nascoste di SUBTOTAL e AGGREGATE

Perché una AGGREGATE esterna arriva nelle formule a cui fa riferimento?

Perché il modo naturale di passare "skip hidden rows" giù fino a sei o sette cicli di scansione delle celle è un campo sull'oggetto calculator, e un solo calculator valuta ogni formula in un passaggio di ricalcolo. Quel progetto regge finché tutto ciò che gira mentre il campo è impostato appartiene all'aggregazione che lo ha impostato. Smette di reggere in un momento preciso: il recupero della cella. Quando il walker chiede il valore di una cella e quella cella contiene una formula senza risultato in cache, il workbook compila e valuta quella formula al volo, sullo stesso calculator, con le impostazioni esterne ancora al loro posto

Seguite l'esempio d'apertura attraverso un motore del genere. La AGGREGATE con codice 7 attiva il salto delle righe nascoste e parte da A1. A1 dà 10, A2 è nascosta e viene saltata, A3 è un errore e viene saltata. Poi il walker arriva ad A4, che non è stata calcolata. La SUBTOTAL(9,A1:A2) di A4 gira, e un'implementazione di SUBTOTAL che attiva il salto delle righe nascoste solo per i codici da 101 a 111 (un modo del tutto ragionevole di scriverla) non lo disattiva mai per il codice 9. Trova l'impostazione già attiva, salta A2 e restituisce 10. Il totale esterno diventa 10 + 10 = 20. Nulla nella formula di A4 menziona le righe nascoste

L'impostazione sugli aggregati annidati filtra in entrambe le direzioni. Con i codici da 0 a 3 è attiva, e un walker generico di range che la rispetti farebbe sì che un precedente come =SUM(B1:B3) scarti B2 ogniqualvolta B2 contiene per caso una SUBTOTAL. Nell'altra direzione, una SUBTOTAL che disattiva il salto dei nested quando finisce, invece di ripristinare lo stato precedente, disabilita la politica della AGGREGATE esterna per il resto della sua visita. Qualsiasi motore che usa un campo transitorio per portare il contesto in una visita ricorsiva ha questa forma; il campo è corretto per il frame che l'ha impostato e sbagliato per ogni frame che lo eredita

Tagliare la politica al confine del fetch

HotXLS mette un confine attorno a ogni punto in cui AGGREGATE legge un valore che non ha calcolato lei. Prima del fetch entrambe le impostazioni vengono salvate e azzerate; dopo, vengono ripristinate in un blocco finally. L'aggregazione esterna applica comunque la propria politica alla cella appena recuperata, perché i test su righe nascoste e celle annidate avvengono nel walker attorno al fetch. La formula referenziata gira senza alcuna politica, il che corrisponde a Excel. Tutte e tre le famiglie di walker passano per quel confine: la famiglia SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), le riduzioni in forma chiusa (PRODUCT, STDEV, VAR) e le forme che prendono un argomento k (MEDIAN, LARGE, SMALL, i percentili e i quartili)

// Walker illustrativo per il vostro motore di valutazione. FPolicy, FetchCell,
// IsRowHidden e IsSubtotalCell sono membri che definite voi
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // la formula referenziata possiede la propria politica
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// dentro la scansione del range: la politica esterna è applicata attorno al fetch
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);
    // ... applicare la politica degli errori a Code / V, poi accumulare ...
  end;
end;

Gli argomenti che non sono range semplici richiedono più di flag azzerati. Un argomento come A1:A4/(B1:B4-20) è un array calcolato, e la sua forma per elemento deve sopravvivere perché l'opzione 6 possa scartare solo gli elementi divisi per zero. HotXLS trasforma ogni range referenziato in un array bidimensionale attraverso lo stesso confine, memorizza una cella fallita come elemento di errore invece di interrompere, e applica gli operatori aritmetici e di confronto elemento per elemento. Un range che copre più fogli, o con gli angoli invertiti, restituisce #VALUE!. Un range troppo grande per TXLSCalculator.FormulaArrayMemoryLimit (64 MB di default) restituisce un risultato di limite risorse, e quel risultato è deliberatamente fuori dall'insieme di errori che le opzioni 2, 3, 6 e 7 possono saltare. Ingoiare il segnale di out-of-memory del motore perché la formula ha chiesto di ignorare #N/A produrrebbe un numero sicuro di sé ma sbagliato, quindi la formula fallisce

Quale errore restituisce AGGREGATE quando non ignora gli errori?

Il primo che incontra, così com'è. Collassare ogni errore in #VALUE! è la scorciatoia facile, e fa sì che una AGGREGATE(9,4,A1:A3) su una cella #DIV/0! sia in disaccordo con Excel, che lascia passare #DIV/0!. HotXLS riporta sia le varianti di errore genuine sia le sette stringhe di errore (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) ai loro codici originali, e ogni walker restituisce il primo codice che ha registrato. Una cella la cui formula non è mai stata calcolata consegna il suo errore come codice di ritorno dal fetch invece che come valore in cache; si propaga esattamente come uno in cache

Due funzioni di conteggio seguono SUBTOTAL anziché SUM. Per la funzione 2, COUNT, una cella di errore non viene mai contata e mai propagata, qualunque sia il codice options, perché COUNT conta solo numeri. Per la funzione 3, COUNTA, una cella di errore è un valore non vuoto e conta 1 a meno che il codice options non salti gli errori. Quell'asimmetria è il modo in cui Excel tratta COUNT e COUNTA anche fuori da AGGREGATE, e una regola generica "se errore allora propaga" li sbaglia entrambi

Verificare tutti e otto i codici su un solo foglio di lavoro

Le quattro celle d'apertura formano un controllo compatto su ogni codice. Valutate la forma SUM e la forma MEDIAN su A1:A4 per ogni codice da 0 a 7 e confrontate con valori calcolati a mano. I codici 0, 1, 4 e 5 devono propagare il #DIV/0! di A3, perché nessuno di loro salta gli errori. Il codice 2 dà SUM 30 e MEDIAN 15, da 10 e 20 con la A4 annidata saltata. Il codice 3 dà 10 e 10. Il codice 6 dà 60 e 20, perché il 30 in A4 ora conta. Il codice 7 dà 40 e 20, ed è quello che un motore senza il confine di fetch riporta come 20. Il controllo ha senso solo se A4 non è in cache quando la AGGREGATE gira, quindi valutate ogni caso una volta con i precedenti già calcolati e una senza

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

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  nascoste saltate, errore propagato
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       nascoste, errori e annidati saltati
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       solo gli errori saltati
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 significa un'impostazione filtrata in A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN di 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Aprire il file salvato in Excel e premere Ctrl+Alt+F9 dà un secondo parere sulle stesse celle. Per un giro più ampio, eseguite ogni numero di funzione da 1 a 19 contro tutti e otto i codici

Cablare i callback quando costruite un calculator da voi

Entrambe le impostazioni dipendono da due callback. TXLSCalculator.Create prende una TXLSIsRowHidden e una TXLSIsSubtotalCell dopo il getter del valore, ed entrambe valgono nil per difetto. Se una delle due manca, la colonna corrispondente della tabella non si applica mai, quindi ogni codice options include le righe nascoste, gli aggregati annidati o entrambi. Gli oggetti workbook XLSX e XLS classico passano le loro implementazioni nei rispettivi costruttori; il codice che crea un TXLSCalculator a mano sulla propria sorgente dati deve fornirle entrambe. I workbook riconoscono un aggregato annidato da una formula che inizia con SUBTOTAL(, AGGREGATE( o _xlfn.AGGREGATE(, con o senza il segno uguale iniziale, e una sorgente personalizzata dovrebbe usare la stessa regola

// TMySheetSource e FormulaAt sono tipi vostri; le tre firme dei metodi
// corrispondono a TXLSGetValue, TXLSIsRowHidden e TXLSIsSubtotalCell
// (SheetIndex è 1-based, Row e Col sono zero-based come li passano i workbook)
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 è la tabella dei nomi dei fogli che il vostro host tiene già
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

La regola del prefisso ha una conseguenza per i workbook generati. Un generatore che scrive =ROUND(SUBTOTAL(9,B2:B9),2) produce una cella che il test del prefisso non riconosce, quindi i codici da 0 a 3 la contano e il gruppo finisce due volte nel totale complessivo. Tenete la chiamata SUBTOTAL o AGGREGATE più esterna e mettete l'arrotondamento nel formato numerico. Un Recalculate completo valuta i precedenti prima dei dipendenti, quindi le aggregazioni di norma leggono valori in cache, e il medesimo ordine vale con il ricalcolo incrementale sul grafo delle dipendenze. Quando un totale continua a sembrare sbagliato e il testo della formula sembra giusto, tracciare la valutazione passo per passo mostra se un precedente è stato eseguito su richiesta dentro la visita o se un callback non è mai stato collegato

Il decoder delle opzioni, il confine di fetch e gli hook dei callback descritti qui arrivano con il componente foglio di calcolo Delphi HotXLS, che legge, scrive e ricalcola workbook XLS, XLSX e ODS da codice Delphi e C++Builder senza installazione di Excel sulla macchina