SUBTOTAL et AGGREGATE avec lignes masquées en Delphi

Si SUBTOTAL(109, ...) et SUBTOTAL(9, ...) renvoient le même nombre sur un classeur contenant des lignes masquées, l'un des deux se trompe. C'est l'un des pièges les plus faciles à rencontrer quand on construit un moteur de calcul de tableur, et HotXLS, le composant tableur Excel natif pour Delphi et C++Builder, l'évite en laissant son moteur de calcul demander à la feuille si une ligne donnée est masquée

Le symptôme arrive rarement sous la forme d'une question sur les codes de formule. Il arrive sous la forme d'un écart : un traitement de lot côté serveur calcule un total, un utilisateur ouvre le même fichier dans Excel avec un filtre actif, et les deux nombres diffèrent de la somme que représentaient les lignes filtrées. Personne ne suspecte la fonction d'agrégation, car la chaîne de formule dans la cellule est identique des deux côtés. La différence tient entièrement à ce que l'évaluateur était autorisé à voir

Pourquoi SUBTOTAL 109 inclurait-il les lignes masquées ?

Parce que dans la plupart des conceptions de moteurs, la couche qui évalue une formule ne sait jamais rien de la visibilité des lignes. La conception évidente accède aux valeurs des cellules par un callback de valeur unique, comme le callback TXLSGetValue de lxCalc.pas, qui répond par une valeur pour un triplet (feuille, ligne, colonne) et rien d'autre. La visibilité est un attribut de présentation stocké sur l'enregistrement de ligne, et si rien d'autre ne descend dans la chaîne d'appels, le moteur se retrouve avec un seul chemin d'agrégation vers lequel les deux moitiés de la table des numéros de fonction SUBTOTAL se résolvent. Ce n'est pas une erreur de classe arrondi : la politique de lignes masquées est la raison d'être de la seconde moitié de la table. ECMA-376 Part 1, publié sous le nom ISO/IEC 29500-1, définit SUBTOTAL dans ses définitions de fonctions de formule (§18.17.7) avec un premier argument qui sélectionne à la fois l'agrégation interne et la politique de lignes masquées. Les codes 1 à 11 correspondent à AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR et VARP en incluant les valeurs des lignes masquées manuellement. Les codes 101 à 111 sélectionnent les mêmes onze agrégations et les excluent. Un utilisateur qui tape 109 au lieu de 9 fait une déclaration délibérée sur les données masquées, et un moteur qui aplatit la distinction outrepasse silencieusement cette déclaration

Vers quoi les numéros de fonction pointent-ils à l'intérieur du moteur

HotXLS résout le premier argument de SUBTOTAL dans CalcSubtotalFunc, qui normalise les codes 101 à 111 vers les mêmes identifiants de fonction internes que les codes 1 à 11 puis dispatche sur l'agrégation elle-même. L'essentiel de la famille passe par l'accumulateur incrémental ExcelSum, celui qui gère SUM, COUNT, COUNTA, MIN, MAX et AVERAGE. Cinq d'entre eux ne le peuvent pas : STDEV, VAR, STDEVP, VARP et PRODUCT exigent une passe en forme fermée sur les données, donc CalcSubtotalFunc route les codes internes 12, 46, 193, 194 et 183 vers un réducteur séparé, SubtotalReduceVariance. Cette scission est la première chose à cartographier avant de toucher à quoi que ce soit, car deux chemins d'agrégation indépendants signifient deux boucles de parcours de cellules indépendantes, et un test de ligne masquée ajouté à une seule d'entre elles produit le pire résultat possible : SUBTOTAL(109, ...) respecte le filtre alors que SUBTOTAL(107, ...) sur la même plage ne le fait pas. HotXLS en compte six une fois AGGREGATE inclus, répartis entre évaluation de plage, simple collecte de plage et trois réducteurs séparés

Pourquoi un champ de travail plutôt que six nouvelles signatures ?

Parce que passer un nouveau paramètre à travers six fonctions de parcours de cellules, plus tout ce qui les appelle, est une modification étendue d'un chemin de code très sollicité pour un simple booléen. HotXLS a un précédent pour l'alternative : un champ transitoire sur le calculateur, dans le même esprit que le champ de travail que GetRangeInfo utilise pour mémoriser qu'une référence 3D s'est résolue dans un classeur externe. Le garde de lignes masquées en est un second : un type de callback, TXLSIsRowHidden, déclaré comme fonction de (SheetIndex, ligne) renvoyant un Boolean, stocké dans FIsRowHidden, plus un drapeau transitoire FIgnoreHiddenRows. Le drapeau est armé à l'entrée de CalcSubtotalFunc quand le code de fonction tombe entre 101 et 111, et à l'entrée de CalcAggregateFunc pour les codes d'option AGGREGATE qui sélectionnent l'exclusion des lignes masquées. Chaque boucle de parcours de cellules l'inspecte ensuite et saute une ligne quand il est armé, au prix d'une seule ligne de code chacune

// La forme répétée dans les six boucles de parcours de cellules
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... plie Cells[rr, cc] dans l'accumulateur ...
    end;
  end;

Deux détails dans le code d'armement portent la correction de tout le schéma. L'indicateur est sauvegardé puis restauré plutôt que simplement positionné puis effacé, car un argument de SUBTOTAL peut contenir une expression qui exécute sa propre évaluation pendant que l'agrégation externe est encore sur la pile, et ce travail imbriqué ne doit ni hériter ni détruire la porte externe. Et la restauration se trouve dans un bloc finally, car CalcSubtotalFunc a plusieurs sorties anticipées pour des codes d'erreur ; un indicateur laissé armé après un retour d'erreur corromprait silencieusement la prochaine formule sans rapport dans l'ordre de recalcul

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // agrège sur Item.Child[2] .. Item.Child[ChildCount]
  // chaque chemin Exit ci-dessous est couvert par le finally
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Le test Assigned est ce qui maintient la compatibilité de l'API du calculateur. Le constructeur de TXLSCalculator prend un troisième paramètre qui vaut nil par défaut, donc le code qui construit un calculateur avec l'appel à deux arguments compile sans changement et obtient le comportement « lignes masquées incluses »

D'où vient réellement le bit de ligne masquée ?

De la feuille de calcul, via deux sources différentes, car HotXLS embarque deux moteurs de classeur. Le côté BIFF historique répond depuis TXLSRowInfoList.GetHidden, rejoint via TXLSWorkbook.GetRowHidden. Le côté OOXML répond depuis TXLSXWorksheet.GetRowHidden, rejoint via TXLSXWorkbook.GetCalcRowHidden. Les deux sont câblés dans le calculateur à la construction, aux côtés du callback de valeurs de cellules qu'ils reflètent. Les conventions de lignes sont l'endroit où ce genre de pont se trompe habituellement, elles méritent donc d'être énoncées explicitement. Le calculateur remet au callback une ligne en base 0, cohérente avec les coordonnées que TXLSGetValue utilise déjà. La feuille XLSX indexe sa table de lignes masquées par numéro de ligne en base 1, exactement comme Excel numérote les lignes, ce qu'expose aussi la propriété publique RowHidden[ARow]. Le pont XLSX ajoute donc un avant la recherche, pas le pont BIFF, car TXLSRowInfoList est déjà en base 0. Les deux ponts traitent un index de feuille ou une ligne hors plage comme visible, si bien qu'une requête hors bornes se dégrade en réponse « lignes masquées incluses » au lieu de perdre des données

Ce qui change pour les classeurs filtrés

C'est le cas où la politique de lignes masquées importe le plus. Appliquer un AutoFilter dans HotXLS via ApplyAutoFilter évalue les critères de colonnes et masque chaque ligne de données qui ne correspond pas, exactement ce que fait Excel quand un utilisateur clique sur une liste déroulante de filtre. Ces lignes sont invisibles pour l'utilisateur et, comme le moteur de calcul consulte le même état de lignes, un SUBTOTAL(109, ...) côté serveur rapporte le total filtré plutôt que le total non filtré

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // masque les lignes qui ne correspondent pas

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // La valeur de la cellule concorde désormais avec ce qu'Excel montre pour le même filtre,
    // et VisibleRows vous dit combien de lignes l'ont alimentée

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

Le masquage manuel fonctionne de la même façon, puisque RowHidden[ARow] := True écrit le même état que le filtre. Cette équivalence est délibérée dans Excel et vaut aussi dans HotXLS. Une conséquence mérite une note dans la documentation qui accompagne vos classeurs générés : un total calculé avec le code 109 est un nombre dépendant de la vue, donc un destinataire qui retire le filtre le change. Quand un rapport doit afficher un chiffre fixe quel que soit ce que le lecteur fait de la vue, le code 9 est le bon choix, et l'a toujours été. Filtres, validation et tables sont traités ensemble dans l'article sur la validation de données, l'AutoFilter et les tables. Comme masquer des lignes ne touche aucune formule, cela ne salit pas non plus le graphe de dépendance de lui-même, ce qui est bon à savoir si vous comptez sur le recalcul incrémental sur le sous-graphe sale pour garder de grands classeurs réactifs

Les codes d'option AGGREGATE

AGGREGATE est SUBTOTAL avec un second argument de politique, et HotXLS le gère dans CalcAggregateFunc. L'argument d'option encode des commutateurs indépendants : si les appels SUBTOTAL et AGGREGATE imbriqués dans la plage sont ignorés, si les valeurs des lignes masquées sont ignorées, et si les valeurs d'erreur sont supprimées plutôt que propagées. HotXLS arme le garde de lignes masquées partagé pour les codes d'option 2, 3, 6 et 7, et supprime les valeurs d'erreur pour les codes d'option 4 à 7. L'argument numéro de fonction sélectionne ensuite l'agrégation exactement comme SUBTOTAL, y compris le routage de la variance, de l'écart type et du produit via leurs propres réducteurs. Les vrais classeurs placent presque toujours les formules SUBTOTAL hors des plages qu'agrègent les autres formules SUBTOTAL ; si votre générateur construit des plages d'agré9gation qui se chevauchent, gardez les totaux internes hors de la plage externe plutôt que de compter sur les codes d'option pour les dédupliquer

Un garde d'arité dans le même dispatcher

Le même dispatcher embarque un test de nombre d'arguments, et la raison de conception est la même que derrière le champ de travail : mettre le test là où on peut l'écrire une seule fois. Avec environ 280 corps de fonctions intégrées, laisser chacun vérifier son propre nombre d'arguments contre Item.ChildCount ne donne aucune frontière cohérente pour le cas « trop d'arguments », et le piège est facile à manquer : un appel comme =SIN(1,2) atteint un corps de fonction qui examine son premier argument, ignore l'excédent et renvoie un nombre plausible là où Excel renvoie #VALUE!. HotXLS stocke l'arité déclarée de chaque fonction intégrée dans son registre de fonctions, exposée via THashFunc.ArgsCnt où -1 marque une fonction variadique comme SUM, IF ou CONCAT, la transmet via la propriété TXLSFormula.FuncArgsCntByPtg et la teste dans un garde unique en tête de GetValueItemFunc, le dispatcher principal

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] est le nœud de la fonction
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) donne désormais #VALUE!
    Exit;
  end;
end;

Le garde rejette les « trop d'arguments » et ne dit délibérément rien des « trop peu ». Omettre un argument optionnel final est légal dans Excel pour VLOOKUP, SUBSTITUTE et une longue liste d'autres, donc un test symétrique casserait des formules correctes pour en attraper d'incorrectes. Les identifiants inconnus se déclarent variadiques et contournent entièrement le garde, ce qui tient les fonctions définies par l'utilisateur à l'écart ; si vous enregistrez vos propres fonctions, le comportement décrit dans le guide du moteur de formule et des fonctions personnalisées n'est pas affecté. Le cas « trop peu » reste dans chaque corps de fonction, car chacun a ses propres sémantiques de codes d'erreur

Le moteur de calcul décrit ici, les deux façades de classeur, et les API d'AutoFilter et de visibilité de ligne qui l'alimentent font partie du composant de feuille de calcul HotXLS pour Delphi, livré avec le code source complet pour Delphi et C++Builder et ne nécessitant aucune installation d'Excel sur la machine qui l'exécute