Articolo tecnico

Wildcard Excel in HotXLS: COUNTIF, MATCH, DSUM e Find

Con a~b in D1 e ab in D2, =COUNTIF(D1:D2,"a~b") restituisce 1, e la cella che conta è D1. =MATCH("a~b",D1:D2,0) restituisce 2, che punta a D2. Stessa stringa di pattern, stesse due celle, ed Excel sceglie una cella diversa a seconda della funzione che legge il pattern. Un motore di foglio di calcolo che vuole che i suoi risultati in cache concordino con Excel deve riprodurre quella divisione, più altre due regole: le funzioni di database come DSUM trattano il testo nudo come "inizia per", e il Find a cella intera deve fare backtracking fino all'ultimo *

I report che vi incorrono raramente menzionano le wildcard. Dicono che una cartella di lavoro generata da un server conta due righe in meno dello stesso file ricalcolato su un desktop, o che un codice parte contenente una tilde viene trovato da una formula e ignorato dalla successiva. Passare ogni pattern da una file mask in stile DOS azzecca i casi quotidiani, quindi la deriva appare solo quando dei veri codici prodotto iniziano a contenere ~ o [

Una stringa di pattern, quattro set di regole Excel

Excel non ha mai unificato le sue regole di confronto; ogni funzionalità si è portata dietro le sue. Le funzioni di criteri (COUNTIF, SUMIF, AVERAGEIF e la famiglia *IFS) decidono criterio per criterio se le wildcard si applicano o meno. Le funzioni di lookup (MATCH con match type 0, XLOOKUP con match_mode 2) le applicano sempre. Le funzioni di database (DSUM, DCOUNTA e le altre) ereditano la regola dell'Advanced Filter, dove una parola nuda è un prefisso. La finestra di Find ha le sue modalità a cella intera e parziale. HotXLS Delphi Component valuta ogni famiglia secondo la sua regola, sia nel motore classico sia nel motore XLSX. La tabella elenca quali celle combaciano con ogni pattern su una colonna che contiene a~b, ab, AB, abc, abcb, a*b e axb, con ogni funzione nella sua modalità case-insensitive di default

PatternCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP modalità 2Criterio DSUMFind, cella intera, wildcard attive
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbcome COUNTIFogni voce, abc compresacome COUNTIF
a~bsolo a~bab, ABab, AB, abc, abcbab, AB
a~*bsolo a*bsolo a*bsolo a*bsolo a*b
=abab, ABnon applicabileab, ABnon applicabile

Due righe meritano un secondo sguardo. Nella riga di a~b, COUNTIF e MATCH atterrano su celle diverse, e i codici battuti a mano contengono tilde più spesso di quanto si pianifichi. Nella riga di a*b, abc combacia per DSUM e fallisce per COUNTIF, perché la funzione di database aggiunge in silenzio una * al criterio. La voce DSUM per a~b discende da quella stessa stella aggiunta: una volta lì, il criterio è un pattern wildcard, e ~b è solo una b con escape

Quando COUNTIF passa in modalità wildcard?

COUNTIF passa in modalità wildcard solo quando il testo del criterio contiene * o ?, con o senza escape. Senza alcuno dei due caratteri, il criterio viene confrontato con ogni cella come stringa intera, senza distinzione maiuscole/minuscole, e la tilde è un carattere ordinario, così COUNTIF(A1:A7,"a~b") conta la cella che contiene letteralmente a~b. Una stella sola capovolge il significato. In "a~b*" la tilde ora fa l'escape della b, il pattern si legge come "ab seguito da qualsiasi cosa", e la cella a~b esce dal conteggio. HotXLS applica questa condizione a COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS e alle funzioni di database allo stesso modo

Diagramma del cancelletto wildcard HotXLS: COUNTIF e SUMIF applicano le wildcard solo quando il criterio contiene una stella o un punto interrogativo, così a~b conta la cella letterale e restituisce 1, mentre MATCH tipo 0 e XLOOKUP modalità 2 sono sempre in modalità wildcard, così a~b trova ab alla posizione 2
Il cancelletto è tutta la differenza: COUNTIF chiede una stella o un punto interrogativo prima di trattare una tilde come escape, MATCH non chiede mai, così una stringa pattern conta una cella e trova l'altra

Dentro la modalità wildcard la regola di escape è breve. ~ rende letterale il carattere successivo qualunque esso sia, così ~b significa b e ~~ significa una tilde; una tilde proprio alla fine del pattern viene scartata, quindi "a*~" si comporta come "a*". Le parentesi quadre non sono mai speciali. Un criterio "[x]" conta le celle che contengono i tre caratteri [x], e "[a-z]" non conta nulla su dati ordinari. Il modo più rapido di verificare tutto questo sui tuoi dati è TXLSXWorkbook.Calculate, che valuta una stringa di formula sul foglio attivo e restituisce un Variant; una cartella appena creata rende attivo il primo foglio aggiunto

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');            // primo foglio, quindi è quello attivo
    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 ... così un totale SUMIF nomina le sue righe
    end;
    Sheet.Cells[8, 1].Value := 5;                // un numero; A9 resta vuota

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    niente * o ?: testo semplice, la cella a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    modalità wildcard: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard sull'intera stringa, abc escluso
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  ogni riga tranne abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    la a*b letterale
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    contano il numero 5 e la A9 vuota
    Show('=COUNTIF(A1:A9,"<>")');       // 8    celle non vuote
  finally
    Book.Free;
  end;
end.

Cosa conta davvero "<>text"

Un criterio "<>text" conta ogni cella che non è quel testo, e in Excel questo include numeri, booleani, valori di errore e celle vuote. Un "<>" nudo chiede tutt'altra cosa: "non una cella vuota". Salta le celle vuote ma conta ogni valore, incluso il testo vuoto che una formula come ="" restituisce. La trappola Delphi sta nel confronto tra Variant. Confronti 'ab' con una cella numerica e Delphi prova a convertire 'ab' in numero, la conversione solleva un'eccezione, e un handler che la ingoia come "nessuna corrispondenza" fa cadere le celle numeriche dal conteggio. HotXLS le conta, quindi le ultime due righe dell'esempio restituiscono 7 e 8. Il versante delle celle vuote, incluso cosa uguaglia un operando vuoto in un confronto ordinario, è trattato in come HotXLS gestisce catene di confronto, celle vuote e SUMIF

Perché MATCH trova ab quando cerchi a~b?

MATCH con match type 0 e XLOOKUP con match_mode 2 sono sempre in modalità wildcard, quindi la tilde fa escape anche quando il pattern non contiene * o ?. Sull'intervallo a due celle dell'apertura, MATCH("a~b",D1:D2,0) restituisce 2. Su un intervallo che contiene solo a~b, la stessa chiamata restituisce #N/A. Cercare il testo letterale a~b richiede "a~~b". Quando le chiavi di lookup arrivano da input utente o da un sistema esterno e potrebbero contenere una tilde, raddoppia ogni tilde prima di costruire la formula; quella StringReplace costa meno dello spiegare poi una riga sparita

HotXLS tiene distinte queste due decisioni. MATCH, XLOOKUP e le funzioni di criteri condividono un matcher con backtracking, con la stessa gestione degli escape e la stessa regola della tilde finale; ciò che cambia è il controllo davanti. Il percorso dei criteri chiede prima se il testo contiene * o ?. Il percorso di lookup non lo chiede mai. Un unico punto di ingresso "combacia un pattern" usato da entrambi azzeccerebbe una famiglia e sbaglierebbe l'altra, in qualunque modo fosse impostato il controllo. I lookup wildcard portano una condizione in più: XLOOKUP restituisce #VALUE! quando match_mode 2 è combinato con una modalità di ricerca binaria, come trattato nella guida HotXLS alle modalità di ricerca di XLOOKUP e XMATCH

Criteri DSUM: il testo nudo significa "inizia per"

DSUM e le altre funzioni di database leggono un criterio di testo senza =, < o > iniziale come un prefisso, con wildcard ancora attive. È la regola dell'Advanced Filter, e differisce da COUNTIF apposta. Su una colonna Name che contiene abc, ab, xab e AB, il criterio ab combacia con abc, ab e AB, mentre =ab combacia solo con ab e AB. <>ab è una disuguaglianza sulla voce intera, a*b e a? sono pattern di prefisso anch'essi, e >ab è un confronto di testo ordinario. xab non combacia mai con ab; la regola è "inizia per", non "contiene"

La parte scomoda per un'implementazione è che un parser dei criteri che rimuove l'operatore vede ab e =ab come la stessa prova di uguaglianza. Solo il testo grezzo del criterio li distingue. HotXLS quindi guarda quel testo grezzo prima di fidarsi della condizione analizzata: un criterio di testo il cui primo carattere non è =, < o > riceve una * in coda e passa dal matcher wildcard, e tutto il resto conserva il suo confronto sulla voce intera

Diagramma HotXLS della regola del criterio DSUM: un criterio di testo nudo riceve una stella in coda e combacia come prefisso, così ab raggiunge ab, AB, abc e abcb, equals ab confronta la voce intera, angle bracket ab esclude entrambi, e una tilde stella sopravvive come a*b letterale, con i totali DSUM risultanti 30, 6, 121 e 32
Excel ha ereditato dalle funzioni di database la regola dell'Advanced Filter: testo nudo significa inizia per, mentre un segno uguale o diverso iniziale confronta la voce intera; HotXLS esamina il testo grezzo del criterio prima di fidarsi della condizione analizzata

Costruire un intervallo di criteri via codice ha una insidia specifica del motore. Nel motore XLSX, assegnare la stringa '=ab' a TXLSXCell.Value memorizza testo, che è esattamente ciò che serve a una cella di criterio. Il motore classico TXLSWorkbook compila un valore stringa che inizia con = come formula, a meno che tu non lo prefissi con un apostrofo. Per intervalli di criteri costruiti a runtime, il motore XLSX è la scelta meno soggetta a errori. Il ciclo qui sotto scambia cinque criteri in D2 e ricalcola lo stesso DSUM ogni volta

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';            // header dei criteri in D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // resta testo nel motore XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (inizia per)
    // =ab  -> 6    ab, AB (voce intera)
    // <>ab -> 121  tutto tranne ab e AB
    // a*b  -> 127  a*b* combacia tutti e sette, abc incluso
    // a~*  -> 32   solo la a*b letterale
  finally
    Book.Free;
  end;
end;

I criteri di ordinamento come >ab seguono ancora un'altra regola. Un confronto per code point rende "a~b">"ab" VERO, perché ~ sta sopra ogni lettera in Unicode. Excel mette la punteggiatura prima delle lettere e restituisce FALSE. Su Windows, HotXLS valuta i criteri di testo > e <, il confronto di testo ordinario e l'ordinamento con la collazione word-sort di sistema per la locale corrente dell'utente, così tutte e tre concordano tra loro e con Excel

Find a cella intera in Delphi: backtracking fino all'ultima stella

La scorciatoia allettante per il Find a cella intera è riutilizzare il matcher parziale dietro Replace e poi pretendere che il combaciamento copra l'intera cella. Sui dati ordinari fallisce. Un matcher parziale ritorna appena il pattern è esaurito, così a*b contro abcb si ferma dopo ab, ha consumato 2 dei 4 caratteri, e la cella viene rifiutata. HotXLS fa girare il Find a cella intera tramite un matcher separato che tratta "pattern finito, testo no" come un'ulteriore mancanza di combaciamento e riprova dall'ultima stella. Con ciò, a*b combacia con abcb e a?b*b combacia con axbyb, come fa Excel Find con "Match entire cell contents" spuntata

Diagramma HotXLS del backtracking del Find wildcard a cella intera: il pattern a*b consuma a e b nella cella abcb e un matcher che si ferma quando il pattern è esaurito rifiuta la cella, mentre il matcher HotXLS tratta pattern esaurito con testo residuo come un'ulteriore mancanza e riprova dall'ultima stella finché l'intera cella non combacia
Un combaciamento a cella intera non è finito quando il pattern si esaurisce; trattare il testo residuo come un'ulteriore mancanza rimanda il matcher all'ultima stella, ed è così che a*b raggiunge abcb come Excel Find

Excel Find, sia in modalità a cella intera sia parziale, tratta ~ come escape per qualsiasi carattere seguente: a~b trova ab, a~~b trova a~b, e una tilde finale viene ignorata, quindi q~ si comporta come q. Un matcher che conoscesse solo ~*, ~? e ~~ farebbe sì che a~b trovi il testo a~b, la cella opposta. HotXLS fa l'escape di qualsiasi carattere dopo una tilde, nel Find e nel Replace come nelle formule

Nel motore XLSX la chiamata è TXLSXWorksheet.FindText con un insieme di TXLSXFindOptions. lxfUseWildcards attiva *, ? e ~; lxfWholeCell esige che l'intera cella combaci; lxfMatchCase rende il confronto sensibile alle maiuscole. Senza lxfUseWildcards ogni carattere, stella inclusa, è letterale. Find guarda solo i valori di testo. Le celle numeriche vengono saltate, e le celle formula vengono saltate a meno che lxfSearchFormulas sia impostato, nel qual caso viene cercato il testo della formula. lxfBackward e lxfByColumns cambiano l'ordine di scansione. L'ancora data da StartRow e StartCol è inclusiva, quindi un ciclo Find All deve avanzare di una colonna oltre ogni hit

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 rifiutata, abcb fa backtrack
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b è una b con escape
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ è una tilde letterale

    // Confronto parziale, Find All: la cella ancora è inclusa, quindi avanza oltre ogni hit
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // righe 1, 2, 3 e 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Il replace wildcard a cella intera riscrive solo la a~b letterale
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Il ciclo parziale riporta tutte e quattro le righe, abc compresa, perché in modalità parziale a*b deve solo trovarsi da qualche parte dentro la cella. FindTextIn e ReplaceTextIn prendono le stesse opzioni più una finestra FirstRow, FirstCol, LastRow, LastCol, la versione programmatica della ricerca dentro una selezione; passa 0 per qualsiasi confine da lasciare aperto. Il motore classico espone le stesse regole di combaciamento tramite un overload con tre booleani, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), più un overload ReplaceText corrispondente. I suoi risultati di riga e colonna sono 1-based, e come la versione XLSX salta le celle formula e i valori non di testo

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;

Perché una file mask DOS è il matcher sbagliato per i criteri Excel

Un matcher da file mask come MatchesMask parla una lingua diversa dalle wildcard Excel, anche se le due si sovrappongono su * e ?. Quella sovrapposizione è esattamente il motivo per cui viene scelto, e per cui i suoi errori restano nascosti finché i dati non diventano interessanti:

  • [x] veniva letto come insieme di caratteri, quindi COUNTIF(A1:A10,"[x]") contava le celle che contenevano x anziché il testo tra parentesi, e "[a-z]" combaciava con qualsiasi cella di una lettera
  • L'escape con tilde non esisteva, quindi "a~*b" non poteva combaciare con un asterisco letterale
  • Una mask malformata, come una parentesi quadra non chiusa, solleva un'eccezione che un chiamante può facilmente ingoiare come "nessuna corrispondenza", trasformando un refuso in un criterio in un totale sbagliato
  • Sul versante lookup, un set di escape limitato a ~*, ~? e ~~ resta corto: MATCH("a~b",…,0) deve trovare ab, non la a~b letterale

Se le tue cartelle di lavoro usano solo * e ? su dati alfanumerici semplici, tutti questi matcher concordano e nulla di tutto ciò conta. Parentesi quadre, tilde, colonne a tipi misti sotto "<>text" e criteri DSUM scritti come parole nude sono i punti in cui i totali di un matcher più semplice prendono le distanze da quelli di Excel, e sono i casi che il codice qui sopra esercita. I filtri salvati sollevano una questione affine su come Excel memorizza un criterio rispetto a come lo confronta, discussa nell'articolo HotXLS sui criteri DOPER dell'AutoFilter BIFF8

HotXLS valuta COUNTIF, MATCH, XLOOKUP, DSUM e il resto della sua libreria di funzioni nativamente in Delphi e C++Builder, nel motore classico e nel motore XLSX, senza installazione di Excel sulla macchina. Edizioni, documentazione e build di prova sono elencate nella pagina di HotXLS Delphi spreadsheet component