Wildcards Excel de HotXLS : COUNTIF, MATCH, DSUM et Find

HotXLS Delphi Component lit une même chaîne de motif de quatre façons différentes, parce qu’Excel 16 le fait. Dans COUNTIF et SUMIF, le texte a~b est littéral sauf si le critère contient aussi * ou ? ; dans le mode wildcard de MATCH et XLOOKUP, le tilde est toujours un échappement, donc a~b trouve ab ; dans DSUM et les autres fonctions de base de données, le texte nu signifie « commence par » ; et le Find cellule entière doit revenir en arrière jusqu’au dernier *. HotXLS suit ces règles mesurées depuis v2.384.52, v2.384.60 et v2.384.64

Les rapports de bug dans ce domaine ne mentionnent jamais les wildcards. Ils disent qu’un rapport généré côté serveur compte quelques lignes de moins que le même fichier recalculé dans Excel, ou qu’une référence contenant un tilde est trouvée par une formule et ignorée par la suivante. La cause, c’est un matcher qui suppose qu’un motif signifie la même chose partout. Excel ne fonctionne pas ainsi, donc un moteur dont les résultats en cache doivent coller à Excel ne le peut pas non plus. Avant v2.384.52, HotXLS faisait passer chaque critère par un masque de fichier à la DOS, qui donnait les motifs du quotidien justes et les cas limites faux en silence

Pourquoi une même chaîne de motif signifie-t-elle quatre choses différentes dans Excel ?

Une même chaîne de motif signifie quatre choses différentes parce qu’Excel a hérité quatre règles de correspondance de quatre fonctionnalités et ne les a jamais unifiées. Les fonctions de critères (COUNTIF, SUMIF, AVERAGEIF et la famille *IFS) décident critère par critère si les wildcards s’appliquent du tout. Les fonctions de recherche (MATCH avec match type 0, XLOOKUP avec match_mode 2) les appliquent toujours. Les fonctions de base de données (DSUM, DCOUNTA et consorts) suivent le filtre avancé (Advanced Filter), où un mot nu est un préfixe. La boîte de dialogue Find a ses propres modes cellule entière et partiel. Le tableau ci-dessous liste quelles cellules correspondent à chaque motif contre une colonne contenant a~b, ab, AB, abc, abcb, a*b et axb, chaque fonction dans son mode par défaut insensible à la casse

Motif	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	Critère DSUM	Find, cellule entière, wildcards activés
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	comme COUNTIF	toutes les entrées, abc inclus	comme COUNTIF
a~b	a~b seulement	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	a*b seulement	a*b seulement	a*b seulement	a*b seulement
=ab	ab, AB	sans objet	ab, AB	sans objet

La ligne a~b est celle où COUNTIF et MATCH divergent, et les références et codes tapés à la main contiennent des tildes plus souvent qu’on ne l’imagine. La ligne a*b montre l’autre piège : abc correspond pour DSUM mais pas pour COUNTIF, parce que la fonction de base de données ajoute silencieusement un *. Les entrées DSUM de ab, a*b et =ab sortent directement d’exécutions sous Excel 16 ; l’entrée DSUM de a~b découle de la même règle de préfixe, puisque le * ajouté transforme le critère en motif wildcard dans lequel ~b est un b échappé

Quand COUNTIF passe-t-il en mode wildcard ?

COUNTIF ne passe en mode wildcard que quand le texte du critère contient * ou ?, échappé ou non. Sans aucun de ces caractères, Excel compare le critère à chaque cellule comme chaîne entière, sans la casse, et un tilde n’est qu’un tilde, donc COUNTIF(A1:A7,"a~b") compte la cellule qui contient littéralement a~b. Ajoutez une seule étoile et le sens bascule : dans "a~b*" le tilde échappe désormais le b, le motif se lit « ab suivi de n’importe quoi », et la cellule a~b n’est plus comptée. HotXLS applique cette règle dans les deux moteurs depuis v2.384.52, via un unique matcher de critères dans lxCalc partagé par COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS et les fonctions de base de données

À l’intérieur du mode wildcard, les règles d’échappement sont les mêmes que partout ailleurs dans Excel : ~ rend le caractère suivant littéral quel qu’il soit, donc ~b signifie b et ~~ un seul tilde, et un tilde tout à la fin du motif est abandonné, donc "a*~" se comporte comme "a*". Les crochets ne sont jamais spéciaux. Un critère "[x]" compte les cellules qui contiennent les trois caractères [x], et "[a-z]" ne compte rien sur des données ordinaires. TXLSXWorkbook.Calculate évalue une chaîne de formule contre la feuille active et renvoie un Variant, le moyen le plus rapide de vérifier ces règles sur vos propres données

uses
  System.Variants, lxHandleX;

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;

  procedure Show(const Formula: string);
  begin
    Writeln(Formula, ' = ', VarToStr(Book.Calculate(Formula)));
  end;

begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i, 1].Value := Names[i];
      Sheet.Cells[i, 2].Value := 1 shl (i - 1);  // 1, 2, 4 ... pour qu’un total SUMIF nomme ses lignes
    end;
    Sheet.Cells[8, 1].Value := 5;                // un nombre ; A9 reste vide

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    ni * ni ? : texte nu, la cellule a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    mode wildcard : ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard sur chaîne entière, abc exclu
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  toutes les lignes sauf abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    le a*b littéral
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    le nombre 5 et le A9 vide comptent
    Show('=COUNTIF(A1:A9,"<>")');       // 8    les cellules non vides
  finally
    Book.Free;
  end;
end.

Que compte « <>text » ?

Un critère "<>text" compte chaque cellule qui n’est pas ce texte, et dans Excel 16 cela inclut les nombres, les booléens, les valeurs d’erreur et les cellules vides. Un "<>" nu est une tout autre question : il signifie « pas une cellule vide », donc il saute les cellules vides mais compte chaque valeur, y compris le texte vide que renvoie une formule telle que ="". L’ancien code HotXLS donnait les cellules texte justes mais pas les nombres : une inégalité Variant faisait convertir 'ab' en nombre par Delphi, la conversion levait une exception, un handler l’avalait comme « pas de correspondance », et les cellules numériques sortaient du comptage en silence. Le versant cellules vides de cette histoire, y compris à quoi égale un opérande vide dans une comparaison ordinaire, est couvert dans comment HotXLS gère les chaînes de comparaison, les cellules vides et SUMIF

Pourquoi MATCH trouve-t-il ab quand vous cherchez a~b ?

MATCH trouve ab quand vous cherchez a~b parce que MATCH avec match type 0 et XLOOKUP avec match_mode 2 sont toujours en mode wildcard, donc le tilde est un échappement même quand le motif ne contient ni * ni ?. Excel 16 le confirme sur une plage de deux cellules contenant a~b et ab : MATCH("a~b",D1:D2,0) renvoie 2, et sur une plage qui ne contient que a~b le même appel renvoie #N/A. Pour chercher le texte littéral a~b, il faut écrire "a~~b". Pendant ce temps, COUNTIF(D1:D2,"a~b") sur les deux mêmes cellules renvoie 1, comptant l’autre cellule. Même chaîne, même plage, cellule opposée

Voilà pourquoi HotXLS garde les deux décisions séparées plutôt que derrière un unique point d’entrée « cherche un motif ». Le matcher lui-même est partagé : depuis v2.384.52, MATCH, XLOOKUP et les fonctions de critères exécutent le même matcher à retour arrière, avec la même gestion d’échappement et la même règle du tilde final. Ce qui diffère, c’est le portail devant lui. Le chemin des critères demande d’abord « ce texte contient-il * ou ? ? » ; le chemin des recherches ne demande jamais. Fusionner les deux corrigerait une famille et casserait l’autre, et les deux directions sont vérifiées contre les valeurs d’Excel 16 dans les deux moteurs. Les recherches wildcard ont en outre leur précondition propre : XLOOKUP refuse la correspondance wildcard combinée à un mode de recherche binaire, règle décrite dans le guide HotXLS des modes de recherche XLOOKUP et XMATCH

Comment DSUM et les fonctions de base de données lisent-elles un critère texte nu ?

DSUM et les autres fonctions de base de données lisent un critère texte sans =, < ni > de tête comme « commence par », wildcards toujours actifs. C’est la règle du filtre avancé, et elle diffère de COUNTIF à dessein. Mesures prises sous Excel 16 sur une colonne Name contenant abc, ab, xab, AB, a~b et a*b : le critère ab correspond à abc, ab et AB ; =ab ne correspond qu’à ab et AB ; <>ab est une inégalité sur l’entrée entière ; a*b et a? sont des motifs de préfixe eux aussi ; >ab est une comparaison ordinaire. Avant v2.384.64, HotXLS faisait correspondre ab exactement, donc un DSUM sur ces données de test renvoyait 10 là où Excel renvoie 11

Le correctif devait contourner l’analyseur de conditions, qui fond ab et =ab dans une même condition d’égalité. HotXLS inspecte donc le texte brut du critère avant de faire confiance à la condition analysée : un critère texte dont le premier caractère n’est pas =, < ni > reçoit un * en suffixe et passe par le matcher wildcard, et tout le reste garde sa comparaison sur l’entrée entière. Une note pratique quand vous construisez des plages de critères en code : dans le moteur XLSX, affecter la chaîne '=ab' à TXLSXCell.Value stocke du texte, tandis que le moteur classique TXLSWorkbook compile une valeur commençant par = comme formule, sauf si vous la préfixez d’une apostrophe

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
  Criteria: array [0..4] of string = ('ab', '=ab', '<>ab', 'a*b', 'a~*');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Db');
    Sheet.Cells[1, 1].Value := 'Name';
    Sheet.Cells[1, 2].Value := 'Val';
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i + 1, 1].Value := Names[i];
      Sheet.Cells[i + 1, 2].Value := 1 shl (i - 1);
    end;
    Sheet.Cells[1, 4].Value := 'Name';            // en-tête de critères en D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // reste du texte dans le moteur XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (commence par)
    // =ab  -> 6    ab, AB (entrée entière)
    // <>ab -> 121  tout sauf ab et AB
    // a*b  -> 127  a*b* correspond aux sept, abc inclus
    // a~*  -> 32   seulement le a*b littéral
  finally
    Book.Free;
  end;
end;

Une différence liée a survécu au correctif de préfixe et compte sur les builds plus anciens. Les comparaisons de texte telles que >ab utilisaient l’ordre des points de code, alors qu’Excel place la ponctuation avant les lettres, donc "a~b">"ab" vaut FALSE dans Excel et valait TRUE dans HotXLS. Depuis v2.384.67, les critères > et <, comme la comparaison de texte ordinaire et le tri, utilisent la collation word sort d’Excel sous la locale utilisateur courante, et les deux sont d’accord à nouveau

Pourquoi le Find cellule entière a-t-il raté abcb ?

Le Find cellule entière ratait abcb parce que le matcher s’arrêtait au premier point où le motif était épuisé au lieu de revenir en arrière jusqu’au dernier *. Le matcher de correspondance partielle derrière Replace renvoie dès que le motif est épuisé ; le Find cellule entière le réutilisait puis exigeait que la correspondance couvre toute la cellule : a*b face à abcb s’arrêtait après ab, consommait 2 caractères sur 4, et était rejeté. Depuis v2.384.60, le matcher cellule entière est une implémentation distincte qui traite « motif fini, texte pas fini » comme un énième désaccord et retente depuis la dernière étoile, donc a*b correspond à abcb et a?b*b à axbyb, comme le fait le Find d’Excel 16 avec « Match entire cell contents » coché

La même version a changé le tilde. Le Find d’Excel 16, en mode cellule entière comme partiel, traite ~ comme un échappement pour n’importe quel caractère suivant : a~b trouve ab, a~~b trouve a~b, et un tilde final est ignoré, donc q~ se comporte comme q. L’ancien matcher HotXLS ne reconnaissait comme échappements que ~*, ~? et ~~, donc a~b trouvait le texte a~b. Un motif Find d’un seul ~ est instable dans Excel lui-même, correspondant à toute cellule comme un motif vide, et HotXLS n’imite pas cela

Dans le moteur XLSX, la recherche est TXLSXWorksheet.FindText avec un ensemble TXLSXFindOptions : lxfUseWildcards active *, ? et ~, lxfWholeCell exige que toute la cellule corresponde, et lxfMatchCase rend la comparaison sensible à la casse. Sans lxfUseWildcards, chaque caractère, étoile incluse, est littéral. Find ne regarde que les valeurs texte ; les cellules numériques sont sautées, et les cellules formule sont sautées sauf si lxfSearchFormulas est actif, auquel cas le texte de la formule est fouillé. L’ancre donnée par StartRow et StartCol est inclusive, donc une boucle Find All avance d’une colonne après chaque succès

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row, Col, NextRow, NextCol, Changed: Integer;
  Opts: TXLSXFindOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Parts');
    Sheet.Cells[1, 1].Value := WideString('abc');
    Sheet.Cells[2, 1].Value := WideString('abcb');
    Sheet.Cells[3, 1].Value := WideString('a~b');
    Sheet.Cells[4, 1].Value := WideString('ab');

    Opts := [lxfUseWildcards, lxfWholeCell];
    if Sheet.FindText('a*b', Row, Col, Opts, 1, 1) then
      Writeln('a*b  whole cell -> row ', Row);   // 2 : abc rejeté, abcb retente en arrière
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4 : ~b est un b échappé
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3 : ~~ est un seul tilde littéral

    // Correspondance partielle, Find All : la cellule ancre est incluse, avancez après chaque succès
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // lignes 1, 2, 3 et 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Le remplacement wildcard cellule entière ne réécrit que le a~b littéral
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

La boucle partielle trouve les quatre lignes, abc comprise, parce qu’en mode partiel a*b n’a qu’à apparaître quelque part dans la cellule. FindTextIn et ReplaceTextIn prennent les mêmes options plus une fenêtre FirstRow, FirstCol, LastRow, LastCol, l’équivalent programmatique de chercher dans une sélection. Le moteur classique expose les mêmes règles via une surcharge à trois booléens, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus une surcharge ReplaceText correspondante, avec des résultats de ligne et colonne à base 1 :

var
  Classic: IXLSWorkbook;
  Sheet: TXLSWorksheet;
  Row, Col: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Sheet := Classic.Sheets.Add;
  Sheet.Range['A1', 'A1'].Value := 'abcb';
  // MatchCase = False, UseWildcards = True, WholeCell = True
  if Sheet.FindText('a*b', Row, Col, False, True, True) then
    Writeln('found at ', Row, ',', Col);           // 1,1
  if not Sheet.FindText('a*c', Row, Col, False, True, True) then
    Writeln('a*c does not cover abcb');
end;

Qu’est-ce que l’ancien matcher à masque DOS ratait ?

L’ancien matcher se trompait sur les caractères spéciaux, parce qu’un masque de fichier DOS est une autre langue qu’un wildcard Excel. Avant v2.384.52, les fonctions de critères et les fonctions de base de données passaient chaque motif à MatchesMask, un matcher de masques de fichier dans l’unité lxMasks. Sa syntaxe recouvre celle d’Excel pour les cas courants, voilà pourquoi le problème est resté caché, mais elle diverge là où les données réelles deviennent intéressantes :

• [x] était lu comme un ensemble de caractères, donc COUNTIF(A1:A10,"[x]") comptait les cellules contenant x au lieu du texte entre crochets, et "[a-z]" correspondait à toute cellule d’une seule lettre
• Il n’y avait pas d’échappement tilde, donc "a~*b" ne pouvait pas correspondre à un astérisque littéral
• Un masque malformé, tel qu’un crochet non fermé, levait une exception que l’appelant avalait comme « pas de correspondance », transformant une coquille dans un critère en total faux en silence
• Côté recherches, MATCH et XLOOKUP ne traitaient que ~*, ~? et ~~ comme échappements, donc MATCH("a~b",…,0) trouvait le a~b littéral au lieu de ab

Si vos classeurs n’ont utilisé que * et ? sur des données alphanumériques simples, les résultats étaient déjà justes et ne changeront pas. S’ils contiennent des crochets, des tildes, des colonnes de types mixtes sous "<>text", ou des critères DSUM écrits en mots nus, les recalculer avec v2.384.64 ou ultérieur peut changer des totaux, et les nouveaux totaux sont ceux qu’affiche Excel. La même distinction entre comment Excel stocke un critère et comment il le compare resurgit pour les filtres enregistrés, traitée dans l’article HotXLS sur les critères DOPER de l’AutoFilter BIFF8

Aide-mémoire : les règles wildcards Excel dans HotXLS

• COUNTIF, SUMIF, AVERAGEIF et la famille *IFS n’utilisent les wildcards que si le critère contient * ou ? ; sinon ils comparent des chaînes entières sans la casse et ~ est littéral (depuis v2.384.52)
• MATCH avec match type 0 et XLOOKUP avec match_mode 2 utilisent toujours les wildcards, donc a~b trouve ab et le littéral exige a~~b (depuis v2.384.52)
• En mode wildcard, ~ échappe tout caractère suivant et un ~ final est abandonné ; [ et ] sont des caractères ordinaires
• "<>text" compte les nombres, les booléens, les erreurs et les cellules vides ; un "<>" nu compte les cellules non vides, résultats ="" inclus
• DSUM et les autres fonctions de base de données traitent le texte nu comme « commence par » ; =text et <>text comparent l’entrée entière (depuis v2.384.64)
• Le Find cellule entière avec lxfUseWildcards et lxfWholeCell revient en arrière, donc a*b correspond à abcb ; Find et Replace traitent ~ comme l’échappement de tout caractère (depuis v2.384.60)
• L’ordre du texte dans les critères > et < suit la collation word sort d’Excel, ponctuation avant lettres (depuis v2.384.67)

La compatibilité Excel dans un moteur de formules, ce sont surtout des cas limites comme ceux-ci, mesurés contre Excel plutôt que devinés dans la documentation. HotXLS évalue COUNTIF, MATCH, XLOOKUP, DSUM et le reste de sa bibliothèque de fonctions nativement en Delphi et C++Builder, dans le moteur classique comme dans le moteur XLSX, sans Excel installé. Détails, éditions et essai à télécharger sont sur la page du composant tableur HotXLS Delphi