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
| 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 |
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
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
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é
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, doncCOUNTIF(A1:A10,"[x]")comptait les cellules contenantxau 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 trouverab, pas lea~blitté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