Options AGGREGATE et gardes imbriquées dans HotXLS en Delphi

Quatre cellules suffisent pour qu'un évaluateur écrit à la main renvoie 20 pour =AGGREGATE(9,7,A1:A4) là où Excel affiche 40. A1 contient 10, A2 contient 20 sur une ligne masquée, A3 contient =1/0, et A4 contient =SUBTOTAL(9,A1:A2), un sous-total de groupe qui vaut 30. L'option 7 signifie ignorer les lignes masquées, ignorer les erreurs et compter les sous-totaux imbriqués comme des valeurs, donc Excel additionne 10 et 30. Le 20 apparaît quand le réglage « skip hidden rows » de l'appel externe est encore actif pendant que A4 calcule sa propre valeur, ce qui réduit A4 à 10. Obtenir 40 demande deux choses : décoder les trois bits d'options aux bonnes positions, et garantir qu'une formule référencée ne voit jamais les options de la formule qui l'a référencée

Que sélectionnent réellement les options AGGREGATE 0 à 7 ?

L'argument options est une matrice de trois bits, et les bits sont indépendants les uns des autres. Le bit 0 (valeur 1) saute les lignes masquées, le bit 1 (valeur 2) saute les valeurs d'erreur, et le bit 2 (valeur 4) cesse de sauter les cellules dont la propre formule est un SUBTOTAL ou un AGGREGATE, parce que les sauter est le comportement par défaut des codes bas. Deux passages se lisent facilement à l'envers. Le bit des lignes masquées est le bit bas, donc AGGREGATE(9,1,...) est le total filtré et AGGREGATE(9,2,...) celui qui tolère les erreurs. Et la politique d'imbrication fonctionne à l'inverse des deux autres : seuls les codes 4 à 7 traitent une agrégation imbriquée comme un nombre ordinaire. ECMA-376 Partie 1 §18.17.7 définit SUBTOTAL avec le même partage inclure-ou-exclure les lignes masquées entre les codes 1–11 et 101–111 ; AGGREGATE, stocké en OOXML sous le préfixe _xlfn., replie ce partage dans son argument options, et la table que Microsoft publie pour la fonction est le contrat qu'un moteur doit respecter

Option	Lignes masquées	Valeurs d'erreur	SUBTOTAL / AGGREGATE imbriqué
0	incluses	propagées	ignorés
1	ignorées	propagées	ignorés
2	incluses	ignorées	ignorés
3	ignorées	ignorées	ignorés
4	incluses	propagées	inclus
5	ignorées	propagées	inclus
6	incluses	ignorées	inclus
7	ignorées	ignorées	inclus

Tout code hors de 0 à 7 est rejeté avec #VALUE!, ce qui est aussi ce que fait Excel

Pourquoi les décodages se trompent-ils sur les codes à un seul bit

Un décodage écrit d'après un résumé de la table plutôt que d'après la table elle-même ressemble typiquement à ceci : ignorer les erreurs pour les codes 4 et au-delà, sauter les lignes masquées pour 2, 3, 6 et 7, et laisser complètement de côté la politique d'imbrication. Cette version peut survivre longtemps en production. La combinaison que tout le monde utilise réellement, lignes masquées plus erreurs, tombe sur les codes 3 et 7 dans les deux lectures, si bien que l'inversion ne se voit que sur un code à un seul bit. AGGREGATE(9,1,A1:A4) renvoie alors la somme non filtrée, et AGGREGATE(9,2,...) saute les lignes masquées tout en propageant quand même #DIV/0!. Les vrais classeurs utilisent rarement les codes 1 et 2 seuls, et c'est exactement pourquoi une revue contre la table publiée attrape plus d'erreurs que l'attente d'un fichier client

HotXLS décode les options comme trois tests d'appartenance à un ensemble, une par colonne de la table, et affecte les trois résultats au démarrage de l'appel AGGREGATE, en restaurant ce qui était en vigueur avant à la fin. L'affectation est inconditionnelle à dessein. Un décodage écrit sous la forme « si l'option le demande, passe le drapeau à vrai » laisse un AGGREGATE de code 4 placé dans un SUBTOTAL(109, ...) hériter du réglage externe de lignes masquées au lieu de l'effacer. Le croquis ci-dessous montre la même règle pour quiconque écrit ou relit son propre évaluateur

// Décodage illustratif pour votre propre évaluateur : TAggPolicy et
// DecodeAggregateOptions sont des noms que vous définissez, pas une API HotXLS
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // tout autre code est #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 à zéro
end;

Le côté SUBTOTAL des mêmes règles de lignes masquées, y compris la comparaison entre lignes filtrées et lignes masquées manuellement, est traité dans l'article sur les lignes masquées de SUBTOTAL et AGGREGATE

Pourquoi un AGGREGATE externe atteint-il les formules qu'il référence ?

Parce que la manière naturelle de passer « saute les lignes masquées » à six ou sept boucles de parcours de cellules est un champ de l'objet calculateur, et un seul calculateur évalue toutes les formules d'une passe de recalcul. Ce design tient tant que tout ce qui s'exécute pendant que le champ est positionné appartient à l'agrégation qui l'a positionné. Il cesse de tenir à un moment précis : la récupération de cellule. Quand le parcours demande la valeur d'une cellule et que cette cellule contient une formule sans résultat en cache, le classeur compile et évalue cette formule sur place, sur le même calculateur, avec les réglages externes toujours en place

Déroulez l'exemple d'ouverture dans un tel moteur. AGGREGATE avec le code 7 active le saut des lignes masquées et démarre à A1. A1 donne 10, A2 est masquée et sautée, A3 est une erreur et sautée. Puis le parcours atteint A4, qui n'a pas été calculée. Le SUBTOTAL(9,A1:A2) de A4 s'exécute, et une implémentation de SUBTOTAL qui n'active le saut des lignes masquées que pour les codes 101 à 111 (une façon parfaitement raisonnable de l'écrire) ne le désactive jamais pour le code 9. Elle trouve le réglage déjà actif, saute A2, et renvoie 10. Le total externe devient 10 + 10 = 20. Rien dans la formule de A4 ne mentionne les lignes masquées

Le réglage d'agrégats imbriqués fuit dans les deux sens. Avec les codes 0 à 3 il est actif, et un parcours de plage générique qui le respecte ferait qu'un précédent comme =SUM(B1:B3) perd B2 chaque fois que B2 contient un SUBTOTAL. Dans l'autre sens, un SUBTOTAL qui désactive le saut imbriqué quand il termine, au lieu de restaurer l'état antérieur, invalide la politique de l'AGGREGATE externe pour le reste de son parcours. Tout évaluateur qui utilise un champ transitoire pour porter du contexte dans un parcours récursif a cette forme ; le champ est correct pour le cadre qui l'a positionné et faux pour chaque cadre qui l'hérite

Couper la politique à la frontière de récupération

HotXLS place une frontière autour de chaque point où AGGREGATE lit une valeur qu'il n'a pas calculée lui-même. Avant la récupération, les deux réglages sont sauvegardés et effacés ; après, ils sont restaurés dans un bloc finally. L'agrégation externe applique toujours sa propre politique à la cellule qu'elle vient de récupérer, car les tests de ligne masquée et de cellule imbriquée ont lieu dans le parcours autour de la récupération. La formule référencée s'exécute sans aucune politique, ce qui correspond à Excel. Les trois familles de parcours passent par cette frontière : la famille SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), les réductions à forme close (PRODUCT, STDEV, VAR), et les formes qui prennent un argument k (MEDIAN, LARGE, SMALL, les percentiles et quartiles)

// Parcours illustratif pour votre propre évaluateur. FPolicy, FetchCell,
// IsRowHidden et IsSubtotalCell sont des membres que vous définissez vous-même
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // la formule référencée a sa propre politique
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// à l'intérieur du parcours de plage : la politique externe s'applique autour de la récupération
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);
    // ... appliquer la politique d'erreur à Code / V, puis accumuler ...
  end;
end;

Les arguments qui ne sont pas des plages simples demandent plus que des drapeaux effacés. Un argument tel que A1:A4/(B1:B4-20) est un tableau calculé, et sa forme d'éléments doit survivre pour que l'option 6 puisse écarter uniquement les éléments qui ont divisé par zéro. HotXLS transforme chaque plage référencée en tableau bidimensionnel par la même frontière, stocke une cellule en échec comme élément d'erreur au lieu d'avorter, et applique les opérateurs arithmétiques et de comparaison élément par élément. Une plage qui s'étend sur plusieurs feuilles, ou dont les coins sont inversés, renvoie #VALUE!. Une plage trop grande pour TXLSCalculator.FormulaArrayMemoryLimit (64 Mo par défaut) renvoie un résultat de limite de ressources, et ce résultat est délibérément en dehors de l'ensemble des erreurs que les options 2, 3, 6 et 7 peuvent sauter. Avaler le propre signal de mémoire épuisée du moteur parce que la formule a demandé d'ignorer #N/A produirait un nombre faux exprimé avec assurance, donc la formule échoue à la place

Quelle erreur AGGREGATE renvoie-t-il quand il n'ignore pas les erreurs ?

La première qu'il rencontre, inchangée. Aplatir toutes les erreurs en #VALUE! est le raccourci facile, et cela fait que AGGREGATE(9,4,A1:A3) sur une cellule #DIV/0! est en désaccord avec Excel, qui laisse passer #DIV/0!. HotXLS ramène aussi bien les vraies variantes d'erreur que les sept chaînes d'erreur (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) à leurs propres codes, et chaque parcours renvoie le premier code qu'il enregistre. Une cellule dont la formule n'a jamais été calculée livre son erreur comme code de retour de la récupération plutôt que comme valeur en cache ; elle se propage exactement comme une cellule en cache

Deux fonctions de comptage suivent SUBTOTAL plutôt que SUM. Pour la fonction 2, COUNT, une cellule d'erreur n'est jamais comptée ni propagée, quel que soit le code d'options, parce que COUNT ne compte que des nombres. Pour la fonction 3, COUNTA, une cellule d'erreur est une valeur non vide et compte pour 1 sauf si le code d'options saute les erreurs. Cette asymétrie est la façon dont Excel traite COUNT et COUNTA hors d'AGGREGATE aussi, et une règle générale « si erreur alors propager » se trompe sur les deux

Vérifier les huit codes sur une seule feuille de calcul

Les quatre cellules d'ouverture font une vérification compacte sur chaque code. Évaluez la forme SUM et la forme MEDIAN sur A1:A4 pour chaque code de 0 à 7 et comparez à des valeurs calculées à la main. Les codes 0, 1, 4 et 5 doivent propager le #DIV/0! de A3, puisqu'aucun ne saute les erreurs. Le code 2 donne SUM 30 et MEDIAN 15, à partir de 10 et 20 avec le A4 imbriqué sauté. Le code 3 donne 10 et 10. Le code 6 donne 60 et 20, parce que le 30 de A4 compte maintenant. Le code 7 donne 40 et 20, et c'est celui qu'un moteur sans la frontière de récupération rapporte comme 20. La vérification n'a de sens que si A4 est sans cache quand l'AGGREGATE s'exécute, donc évaluez chaque cas une fois avec les précédents déjà calculés et une fois sans

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

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  lignes masquées sautées, l'erreur se propage
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       lignes masquées, erreur et imbriqué sautés
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       seules les erreurs sont sautées
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 signifie qu'un réglage a fui dans A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN de 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Ouvrir le fichier enregistré dans Excel et presser Ctrl+Alt+F9 donne un second avis sur les mêmes cellules. Pour un balayage plus large, exécutez chaque numéro de fonction de 1 à 19 contre les huit codes

Câbler les callbacks quand vous construisez vous-même un calculateur

Les deux réglages dépendent de deux callbacks. TXLSCalculator.Create prend un TXLSIsRowHidden et un TXLSIsSubtotalCell après le getter de valeur, et les deux valent nil par défaut. Si l'un manque, la colonne correspondante de la table ne s'applique jamais, si bien que chaque code d'options inclut les lignes masquées, les agrégations imbriquées, ou les deux. Les objets classeur XLSX et XLS classique passent leurs propres implémentations dans leurs constructeurs ; le code qui crée un TXLSCalculator à la main au-dessus de sa propre source de données doit fournir les deux. Les classeurs reconnaissent une agrégation imbriquée à une formule qui commence par SUBTOTAL(, AGGREGATE( ou _xlfn.AGGREGATE(, avec ou sans le signe égal de tête, et une source personnalisée devrait utiliser la même règle

// TMySheetSource et FormulaAt sont vos propres types ; les trois signatures
// de méthodes correspondent à TXLSGetValue, TXLSIsRowHidden et TXLSIsSubtotalCell
// (SheetIndex basé sur 1, Row et Col basés sur 0 comme les classeurs les passent)
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 est la table des noms de feuilles que votre hôte conserve déjà
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

La règle du préfixe a une conséquence pour les classeurs générés. Un générateur qui écrit =ROUND(SUBTOTAL(9,B2:B9),2) produit une cellule que le test de préfixe ne reconnaît pas, si bien que les codes 0 à 3 la comptent et que le groupe atterrit deux fois dans le total général. Gardez l'appel SUBTOTAL ou AGGREGATE au plus extérieur et mettez l'arrondi dans le format de nombre. Un Recalculate complet évalue les précédents avant les dépendants, si bien que les agrégations lisent normalement des valeurs en cache, et le même ordre vaut sous le recalcul incrémental sur le graphe de dépendances. Quand un total semble toujours faux et que le texte de la formule semble juste, tracer l'évaluation pas à pas montre si un précédent a tourné à la demande pendant le parcours ou si un callback n'a jamais été attaché

Le décodeur d'options, la frontière de récupération et les hooks de callback décrits ici accompagnent le composant tableur Delphi HotXLS, qui lit, écrit et recalcule des classeurs XLS, XLSX et ODS depuis du code Delphi et C++Builder sans installation d'Excel sur la machine