Article technique

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

Avec a~b en D1 et ab en D2, =COUNTIF(D1:D2,"a~b") renvoie 1, et la cellule comptée est D1. =MATCH("a~b",D1:D2,0) renvoie 2, ce qui pointe vers D2. Même chaîne de motif, mêmes deux cellules, et Excel choisit une cellule différente selon la fonction qui lit le motif. Un moteur de tableur qui veut que ses résultats en cache tombent d'accord avec Excel doit reproduire cette scission, avec deux règles de plus : les fonctions de base de données comme DSUM traitent le texte nu comme « commence par », et le Find de cellule entière doit revenir en arrière dans le dernier *

Les rapports qui tombent là-dessus mentionnent rarement les wildcards. Ils disent qu'un classeur généré par un serveur compte deux lignes de moins que le même fichier recalculé sur un poste de bureau, ou qu'un numéro de pièce contenant un tilde est trouvé par une formule et ignoré par la suivante. Passer chaque motif par un masque de fichiers façon DOS donne le bon résultat pour les cas du quotidien, si bien que la dérive n'apparaît que quand de vrais codes produits se mettent à contenir ~ ou [

Une chaîne de motif, quatre ensembles de règles Excel

Excel n'a jamais unifié ses règles de correspondance ; chaque fonctionnalité est venue avec la sienne. Les fonctions de critères (COUNTIF, SUMIF, AVERAGEIF et la famille des *IFS) décident critère par critère si les wildcards s'appliquent ou pas. Les fonctions de recherche (MATCH avec type de correspondance 0, XLOOKUP avec match_mode 2) les appliquent toujours. Les fonctions de base de données (DSUM, DCOUNTA et les autres) héritent de la règle du filtre avancé, où un mot nu est un préfixe. La boîte de dialogue Find a ses propres modes cellule entière et partiel. HotXLS Delphi Component évalue chaque famille selon sa propre règle, dans le moteur classique comme dans le moteur XLSX. Le tableau liste les cellules que chaque motif fait correspondre sur une colonne contenant a~b, ab, AB, abc, abcb, a*b et axb, chaque fonction dans son mode insensible à la casse par défaut

MotifCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP mode 2Critère DSUMFind, cellule entière, wildcards activés
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbcomme COUNTIFtoutes les entrées, abc incluscomme COUNTIF
a~ba~b seulementab, ABab, AB, abc, abcbab, AB
a~*ba*b seulementa*b seulementa*b seulementa*b seulement
=abab, ABsans objetab, ABsans objet

Deux lignes méritent un second regard. Sur la ligne a~b, COUNTIF et MATCH atterrissent sur des cellules différentes, et les codes saisis à la main contiennent des tildes plus souvent qu'on ne le prévoit. Sur la ligne a*b, abc correspond pour DSUM et échoue pour COUNTIF, parce que la fonction de base de données ajoute discrètement un * au critère. L'entrée DSUM pour a~b découle de cette même étoile ajoutée : une fois là, le critère est un motif à wildcards, et ~b n'est qu'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 l'un de ces caractères, le critère est comparé à chaque cellule comme chaîne entière, sans tenir compte de la casse, et un tilde est un caractère ordinaire, si bien que COUNTIF(A1:A7,"a~b") compte la cellule qui contient littéralement a~b. Une seule étoile retourne le sens. Dans "a~b*", le tilde échappe maintenant le b, le motif se lit « ab suivi de n'importe quoi », et la cellule a~b sort du compte. HotXLS applique ce garde dans COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS et les fonctions de base de données pareillement

Schéma du portail wildcard HotXLS : COUNTIF et SUMIF n’appliquent les wildcards que si le critère contient une étoile ou un point d’interrogation, donc a~b compte la cellule littérale et renvoie 1, tandis que MATCH type 0 et XLOOKUP mode 2 sont toujours en mode wildcard, donc a~b trouve ab en position 2
Le portail fait toute la différence : COUNTIF demande une étoile ou un point d’interrogation avant de traiter un tilde comme échappement, MATCH ne demande jamais, donc une même chaîne de motif compte une cellule et en trouve une autre

En mode wildcard, la règle d'échappement est courte. ~ rend le caractère suivant littéral quel qu'il soit, donc ~b veut dire b et ~~ veut dire un tilde ; un tilde tout à la fin du motif est abandonné, si bien que "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. La façon la plus rapide de vérifier tout ça sur vos propres données est TXLSXWorkbook.Calculate, qui évalue une chaîne de formule contre la feuille active et renvoie un Variant ; un classeur neuf fait de sa première feuille ajoutée la feuille active

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');            // première feuille, donc elle est active
    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.

Ce que « <>text » compte réellement

Un critère "<>text" compte toutes les cellules qui ne sont pas ce texte, et dans Excel cela inclut les nombres, les booléens, les valeurs d'erreur et les cellules vides. Un "<>" nu demande tout autre chose : « pas une cellule vide ». Il saute les cellules vides mais compte toute valeur, y compris le texte vide qu'une formule comme ="" renvoie. Le piège Delphi se situe dans la comparaison Variant. Comparez 'ab' avec une cellule numérique et Delphi tente de convertir 'ab' en nombre, la conversion lève, et un gestionnaire qui avale l'exception comme « pas de correspondance » fait sortir les cellules numériques du compte. HotXLS les compte, si bien que les deux dernières lignes de l'exemple renvoient 7 et 8. Le côté cellules vides de cette histoire, y compris ce à quoi un opérande vide égale dans une comparaison ordinaire, est couvert dans la façon dont 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 avec type de correspondance 0 et XLOOKUP avec match_mode 2 sont toujours en mode wildcard, si bien que le tilde est un échappement même quand le motif ne contient ni * ni ?. Sur la plage à deux cellules de l'ouverture, MATCH("a~b",D1:D2,0) renvoie 2. Sur une plage qui ne contient que a~b, le même appel renvoie #N/A. Chercher le texte littéral a~b demande "a~~b". Quand les clés de recherche viennent d'une saisie utilisateur ou d'un système externe et peuvent contenir un tilde, doublez chaque tilde avant de construire la formule ; ce seul StringReplace coûte moins cher que d'expliquer plus tard une ligne manquante

HotXLS garde ces deux décisions séparées. MATCH, XLOOKUP et les fonctions de critères partagent un apparieur à retour arrière, avec la même gestion des échappements et la même règle du tilde final ; ce qui diffère, c'est le garde devant lui. Le chemin des critères demande d'abord si le texte contient * ou ?. Le chemin des recherches ne le demande jamais. Un unique point d'entrée « apparier un motif » utilisé par les deux familles en ferait une correcte et l'autre fausse, quel que soit le réglage du garde. Les recherches à wildcards portent une condition de plus : XLOOKUP renvoie #VALUE! quand le mode 2 est combiné à un mode de recherche binaire, ce qui est couvert dans le guide HotXLS des modes de recherche XLOOKUP et XMATCH

Critères DSUM : le texte nu veut dire « commence par »

DSUM et les autres fonctions de base de données lisent un critère texte sans =, < ou > de tête comme un préfixe, wildcards toujours actifs. C'est la règle du filtre avancé, et elle diffère de COUNTIF exprès. Sur une colonne Name contenant abc, ab, xab et AB, le critère ab fait correspondre abc, ab et AB, tandis que =ab ne fait correspondre que 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, et >ab est une comparaison de texte ordinaire. xab ne correspond jamais à ab ; la règle est « commence par », pas « contient »

Le côté pénible pour une implémentation, c'est qu'un analyseur de critères qui retire l'opérateur voit ab et =ab comme le même test d'égalité. Seul le texte brut du critère les distingue. HotXLS regarde donc ce texte brut avant de faire confiance à la condition analysée : un critère texte dont le premier caractère n'est ni =, ni <, ni > reçoit un * ajouté et passe par l'apparieur à wildcards, et tout le reste garde sa comparaison sur l'entrée entière

Diagramme HotXLS de la règle de critère DSUM : un critère texte nu reçoit une étoile ajoutée et matche comme un préfixe, si bien que ab atteint ab, AB, abc et abcb, =ab compare l'entrée entière, <>ab exclut les deux, et un tilde-étoile survit comme a*b littéral, avec les totaux DSUM qui en résultent 30, 6, 121 et 32
Excel a hérité de la règle du filtre avancé pour les fonctions de base de données : le texte nu veut dire commence par, tandis qu'un signe égal ou différent de tête compare l'entrée entière ; HotXLS inspecte le texte brut du critère avant de faire confiance à la condition analysée

Construire une plage de critères en code a une spécificité de moteur. Dans le moteur XLSX, assigner la chaîne '=ab' à TXLSXCell.Value stocke du texte, exactement ce dont une cellule de critère a besoin. Le moteur classique TXLSWorkbook compile une valeur de chaîne qui commence par = comme une formule, sauf si vous la faites précéder d'une apostrophe. Pour des plages de critères construites à l'exécution, le moteur XLSX est le choix le moins sujet aux erreurs. La boucle ci-dessous échange cinq critères dans D2 et recalcule le même DSUM à chaque fois

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;

Les critères d'ordre comme >ab suivent encore une autre règle. Une comparaison par point de code pure rend "a~b">"ab" VRAI, parce que ~ est au-dessus de chaque lettre en Unicode. Excel met la ponctuation avant les lettres et renvoie FAUX. Sous Windows, HotXLS évalue les critères texte > et <, la comparaison de texte ordinaire et le tri avec le classement de tri de mots du système pour la locale de l'utilisateur courant, si bien que les trois tombent d'accord entre eux et avec Excel

Find de cellule entière en Delphi : le retour arrière dans la dernière étoile

Le raccourci tentant pour le Find de cellule entière est de réutiliser l'apparieur partiel derrière Replace puis d'exiger que la correspondance couvre toute la cellule. Ça échoue sur des données ordinaires. Un apparieur partiel rend la main dès que le motif est épuisé, si bien que a*b contre abcb s'arrête après ab, a consommé 2 des 4 caractères, et la cellule est rejetée. HotXLS fait passer le Find de cellule entière par un apparieur séparé qui traite « motif terminé alors que du texte reste » comme un désaccord de plus et retente depuis la dernière étoile. Avec ça, a*b apparie abcb et a?b*b apparie axbyb, comme le fait Excel Find avec « Match entire cell contents » coché

Diagramme HotXLS du retour arrière du Find à wildcards de cellule entière : le motif a*b consomme a et b dans la cellule abcb et un apparieur qui s'arrête quand le motif est épuisé rejette la cellule, tandis que l'apparieur HotXLS traite motif terminé avec du texte restant comme un désaccord de plus et retente depuis la dernière étoile jusqu'à ce que toute la cellule apparie
Une correspondance de cellule entière n'est pas finie quand le motif s'épuise ; traiter le texte restant comme un désaccord de plus renvoie l'apparieur à la dernière étoile, ce qui permet à a*b d'atteindre abcb comme Excel Find

Excel Find, en mode cellule entière comme en mode 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é, si bien que q~ se comporte comme q. Un apparieur qui ne connaît que ~*, ~? et ~~ ferait trouver à a~b le texte a~b, la cellule opposée. HotXLS échappe n'importe quel caractère après un tilde, dans Find et Replace comme dans les formules

Dans le moteur XLSX, l'appel est TXLSXWorksheet.FindText avec un ensemble TXLSXFindOptions. lxfUseWildcards allume *, ? et ~ ; lxfWholeCell exige que toute la cellule apparie ; 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 posé, auquel cas le texte de la formule est fouillé. lxfBackward et lxfByColumns changent l'ordre de balayage. L'ancre donnée par StartRow et StartCol est inclusive, si bien qu'une boucle de Find All doit avancer d'une colonne après chaque réussite

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 rapporte 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, la version programmatique de la recherche dans une sélection ; passez 0 pour toute borne à laisser ouverte. Le moteur classique expose les mêmes règles d'appariement par une surcharge à trois booléens, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus une surcharge ReplaceText assortie. Ses résultats de ligne et colonne sont à base 1, et comme la version XLSX il saute les cellules à formule et les valeurs non texte

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;

Pourquoi un masque de fichiers DOS est le mauvais apparieur pour les critères Excel

Un apparieur de masques de fichiers comme MatchesMask parle une autre langue que les wildcards Excel, même si les deux se recouvrent sur * et ?. Ce recouvrement est exactement pourquoi on le choisit, et pourquoi ses erreurs restent cachées jusqu'à ce que les données 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é, comme un crochet non fermé, lève une exception qu'un appelant avale facilement comme « pas de correspondance », transformant une coquille dans un critère en total faux
  • Côté recherche, un ensemble d'échappements limité à ~*, ~? et ~~ reste court lui aussi : MATCH("a~b",…,0) doit trouver ab, pas le a~b littéral

Si vos classeurs n'utilisent que * et ? sur des données alphanumériques simples, tous ces apparieurs sont d'accord et rien de tout ça n'a d'importance. Les crochets, les tildes, les colonnes de types mélangés sous "<>text" et les critères DSUM écrits en mots nus sont les endroits où les totaux d'un apparieur plus simple dérivent de ceux d'Excel, et ce sont les cas que le code ci-dessus exerce. Les filtres enregistrés posent une question voisine sur la façon dont Excel stocke un critère versus la façon dont il le compare, traitée dans l'article HotXLS sur les critères DOPER de l'AutoFilter BIFF8

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 et le moteur XLSX, sans installation d'Excel sur la machine. Éditions, documentation et build d'essai sont listés sur la page du composant tableur HotXLS pour Delphi