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

HotXLS Delphi Component legge la stessa stringa pattern in quattro modi diversi, perché Excel 16 fa così. In COUNTIF e SUMIF il testo a~b è letterale a meno che il criterio non contenga anche * o ?; in MATCH e in XLOOKUP in modalità wildcard la tilde è sempre un escape, quindi a~b trova ab; in DSUM e nelle altre funzioni di database il testo semplice significa "inizia per"; e il Find a cella intera deve fare backtrack sull'ultimo *. HotXLS segue queste regole misurate dalle v2.384.52, v2.384.60 e v2.384.64

Le segnalazioni di bug in quest'area non menzionano mai le wildcard. Dicono che un report generato sul server conta un paio di righe in meno dello stesso file ricalcolato in Excel, o che un codice parte contenente una tilde viene trovato da una formula e ignorato dalla successiva. La causa è un matcher che presuppone che un pattern significhi una cosa sola ovunque. Excel non funziona così, quindi nemmeno un motore i cui risultati in cache devono andare d'accordo con Excel può permetterselo. Prima della v2.384.52 HotXLS passava ogni criterio per una maschera file in stile DOS, che azzeccava i pattern di tutti i giorni e sbagliava in silenzio i casi limite

Perché una stringa pattern significa quattro cose diverse in Excel?

Una stringa pattern significa quattro cose diverse perché Excel ha ereditato quattro regole di corrispondenza da quattro funzionalità e non le ha mai unificate. Le funzioni di criteri (COUNTIF, SUMIF, AVERAGEIF e la famiglia *IFS) decidono criterio per criterio se le wildcard si applicano del tutto. Le funzioni di lookup (MATCH con match type 0, XLOOKUP con match_mode 2) le applicano sempre. Le funzioni di database (DSUM, DCOUNTA e compagnie) seguono l'Advanced Filter, dove una parola nuda è un prefisso. La finestra Find ha le sue modalità a cella intera e parziale. La tabella qui sotto 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à predefinita senza distinzione maiuscole/minuscole

Pattern	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP modalità 2	Criterio DSUM	Find, cella intera, wildcard attive
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	come COUNTIF	ogni voce, abc compresa	come COUNTIF
a~b	solo a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	solo a*b	solo a*b	solo a*b	solo a*b
=ab	ab, AB	non applicabile	ab, AB	non applicabile

La riga a~b è quella in cui COUNTIF e MATCH non sono d'accordo, e i codici parte e i codici digitati a mano contengono tilde più spesso di quanto chiunque si aspetti. La riga a*b mostra l'altra trappola: abc combacia per DSUM ma non per COUNTIF, perché la funzione di database aggiunge in silenzio una *. Le voci DSUM per ab, a*b e =ab vengono dritte da esecuzioni su Excel 16; la voce DSUM per a~b discende dalla stessa regola del prefisso, visto che la * aggiunta trasforma il criterio in un pattern wildcard in cui ~b è una b con escape

Quando COUNTIF passa in modalità wildcard?

COUNTIF passa in modalità wildcard solo quando il testo del criterio contiene * o ?, con escape o senza. Senza nessuno dei due caratteri, Excel confronta il criterio con ogni cella come intera stringa, senza distinguere maiuscole e minuscole, e una tilde è solo una tilde, così COUNTIF(A1:A7,"a~b") conta la cella che contiene alla lettera a~b. Aggiungi una sola stella e il significato si ribalta: in "a~b*" la tilde ora mette in escape la b, il pattern si legge come "ab seguito da qualsiasi cosa", e la cella a~b non viene più contata. HotXLS applica questa regola in entrambi i motori dalla v2.384.52, attraverso un unico matcher di criteri in lxCalc condiviso da COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS e dalle funzioni di database

Dentro la modalità wildcard le regole di escape sono le stesse di ovunque altro in Excel: ~ rende letterale il carattere successivo quale che sia, quindi ~b significa b e ~~ significa una tilde, e una tilde proprio in fondo al pattern viene scartata, così "a*~" si comporta come "a*". Le parentesi quadre non sono mai speciali. Un criterio di "[x]" conta le celle che contengono i tre caratteri [x], e "[a-z]" non conta nulla su dati ordinari. TXLSXWorkbook.Calculate valuta una stringa di formula sul foglio attivo e restituisce un Variant, il modo più rapido di verificare queste regole sui tuoi dati

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 ... 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.

Che cosa conta "<>text"?

Un criterio "<>text" conta ogni cella che non è quel testo, e in Excel 16 questo include numeri, booleani, valori di errore e celle vuote. Un "<>" nudo è una domanda completamente diversa: significa "non una cella vuota", quindi salta le celle vuote ma conta ogni valore, compreso il testo vuoto che restituisce una formula come ="". Il vecchio codice HotXLS azzeccava le celle testuali ma non i numeri: una disuguaglianza su Variant faceva convertire a Delphi 'ab' in numero, la conversione sollevava un'eccezione, un handler la ingoiava come "nessuna corrispondenza", e le celle numeriche uscivano in silenzio dal conteggio. Il lato celle vuote di questa storia, compreso ciò a cui eguaglia un operando vuoto in un confronto ordinario, è coperto in come HotXLS gestisce catene di confronto, celle vuote e SUMIF

Perché MATCH trova ab quando cerchi a~b?

MATCH trova ab quando cerchi a~b perché MATCH con match type 0 e XLOOKUP con match_mode 2 sono sempre in modalità wildcard, quindi la tilde è un escape anche quando il pattern non contiene * o ?. Excel 16 lo conferma su un intervallo di due celle che contiene a~b e ab: MATCH("a~b",D1:D2,0) restituisce 2, e su un intervallo che contiene solo a~b la stessa chiamata restituisce #N/A. Per cercare il testo letterale a~b devi scrivere "a~~b". Nel frattempo COUNTIF(D1:D2,"a~b") sulle stesse due celle restituisce 1, contando l'altra cella. Stessa stringa, stesso intervallo, cella opposta

Ecco perché HotXLS tiene le due decisioni separate anziché dietro a un unico punto d'ingresso "combacia un pattern". Il matcher in sé è condiviso: dalla v2.384.52, MATCH, XLOOKUP e le funzioni di criteri fanno girare lo stesso matcher con backtrack, con la stessa gestione degli escape e la stessa regola della tilde finale. Quel che cambia è il cancelletto davanti. Il percorso dei criteri chiede prima "questo testo contiene * o ??"; il percorso dei lookup non lo chiede mai. Fondere i due sistemerebbe una famiglia e romperebbe l'altra, e entrambe le direzioni sono verificate contro i valori di Excel 16 in entrambi i motori. I lookup wildcard hanno anche un prerequisito tutto loro: XLOOKUP rifiuta la corrispondenza wildcard combinata con una modalità di ricerca binaria, una regola descritta in la guida HotXLS alle modalità di ricerca di XLOOKUP e XMATCH

Come leggono DSUM e le funzioni di database un criterio di testo semplice?

DSUM e le altre funzioni di database leggono un criterio testuale senza =, < o > iniziale come "inizia per", con le wildcard ancora attive. È la regola dell'Advanced Filter, e differisce da COUNTIF di proposito. Misurazioni su Excel 16 con una colonna Name che contiene abc, ab, xab, AB, a~b e a*b: il criterio ab combacia con abc, ab e AB; =ab combacia solo con ab e AB; <>ab è una disuguaglianza sull'intera voce; a*b e a? sono anch'essi pattern per prefisso; >ab è un confronto ordinario. Prima della v2.384.64 HotXLS faceva combaciare ab esattamente, quindi un DSUM su quei dati di test restituiva 10 dove Excel restituisce 11

La correzione doveva aggirare il parser delle condizioni, che riporta sia ab sia =ab alla stessa condizione di uguaglianza. HotXLS quindi ispeziona il testo grezzo del criterio prima di fidarsi della condizione parseata: un criterio testuale il cui primo carattere non è =, < o > si prende una * in coda e passa per il matcher wildcard, e tutto il resto conserva il suo confronto sull'intera voce. Una nota pratica quando costruisci intervalli di criteri in codice: nel motore XLSX, assegnare la stringa '=ab' a TXLSXCell.Value memorizza testo, mentre il motore classico TXLSWorkbook compila come formula un valore che inizia con = a meno che tu non lo prefigga con un apostrofo

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;

Una differenza correlata è sopravvissuta alla correzione del prefisso e conta sulle build più vecchie. I confronti testuali come >ab usavano l'ordine per code point, mentre Excel mette la punteggiatura prima delle lettere, quindi "a~b">"ab" è FALSE in Excel ed era TRUE in HotXLS. Dalla v2.384.67 i criteri > e <, insieme al confronto testuale ordinario e all'ordinamento, usano la collation word sort di Excel sotto il locale utente corrente, e i due sono di nuovo d'accordo

Perché il Find a cella intera perdeva abcb?

Il Find a cella intera perdeva abcb perché il matcher si fermava al primo punto in cui il pattern era esaurito invece di fare backtrack sull'ultimo *. Il matcher a corrispondenza parziale dietro a Replace restituisce appena il pattern è esaurito; il Find a cella intera lo riutilizzava e poi pretendeva che la corrispondenza coprisse l'intera cella: a*b contro abcb si fermava dopo ab, consumava 2 caratteri su 4, e veniva rifiutata. Dalla v2.384.60 il matcher a cella intera è un'implementazione separata che tratta "pattern finito, testo no" come un disallineamento in più e riprova dall'ultima stella, così a*b combacia con abcb e a?b*b combacia con axbyb, come fa il Find di Excel 16 con l'opzione di cercare l'intero contenuto della cella spuntata

La stessa release ha cambiato la tilde. Il Find di Excel 16, 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, così q~ si comporta come q. Il vecchio matcher HotXLS riconosceva come escape solo ~*, ~? e ~~, quindi a~b trovava il testo a~b. Un pattern Find di una sola ~ è instabile in Excel stesso, combacia qualsiasi cella come un pattern vuoto, e HotXLS non imita quel comportamento

Nel motore XLSX la ricerca è TXLSXWorksheet.FindText con un insieme TXLSXFindOptions: lxfUseWildcards accende *, ? e ~, lxfWholeCell pretende che l'intera cella combaci, e lxfMatchCase rende il confronto sensibile al maiuscolo/minuscolo. Senza lxfUseWildcards ogni carattere, stella compresa, è letterale. Il Find guarda solo i valori testuali; 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. L'ancora data da StartRow e StartCol è inclusiva, quindi un loop Find All avanza 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 loop parziale trova 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, l'equivalente programmatico del cercare dentro una selezione. Il motore classico espone le stesse regole attraverso un overload con tre booleani, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), più un overload ReplaceText corrispondente, con risultati di riga e colonna a base uno:

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;

Che cosa sbagliava il vecchio matcher con maschere DOS?

Il vecchio matcher sbagliava i caratteri speciali, perché una maschera file DOS è un linguaggio diverso da una wildcard Excel. Prima della v2.384.52 le funzioni di criteri e le funzioni di database passavano ogni pattern a MatchesMask, un matcher di maschere file nell'unità lxMasks. La sua sintassi si sovrappone a quella di Excel per i casi comuni, ecco perché il problema è rimasto nascosto, ma diverge dove i dati reali si fanno 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 maschera malformata, come una parentesi non chiusa, sollevava un'eccezione che il chiamante ingoiava come "nessuna corrispondenza", trasformando un typo in un criterio in un totale silenziosamente sbagliato
• Sul lato lookup, MATCH e XLOOKUP trattavano come escape solo ~*, ~? e ~~, quindi MATCH("a~b",…,0) trovava la a~b letterale anziché ab

Se le tue cartelle di lavoro hanno usato solo * e ? su dati alfanumerici semplici, i risultati erano già giusti e non cambieranno. Se contengono parentesi, tilde, colonne a tipi misti sotto "<>text", o criteri DSUM scritti come parole nude, ricalcolarle con la v2.384.64 o successiva può cambiare i totali, e i nuovi totali sono quelli che Excel mostra. La stessa distinzione tra come Excel memorizza un criterio e come lo confronta torna per i filtri salvati, discussa in l'articolo HotXLS sui criteri DOPER dell'AutoFilter BIFF8

Riferimento rapido: regole wildcard Excel in HotXLS

• COUNTIF, SUMIF, AVERAGEIF e la famiglia *IFS usano le wildcard solo quando il criterio contiene * o ?; altrimenti confrontano intere stringhe senza distinzione maiuscole/minuscole e ~ è letterale (dalla v2.384.52)
• MATCH con match type 0 e XLOOKUP con match_mode 2 usano sempre le wildcard, quindi a~b trova ab e la forma letterale richiede a~~b (dalla v2.384.52)
• In modalità wildcard ~ mette in escape il carattere successivo qualsiasi sia e una ~ finale viene scartata; [ e ] sono caratteri ordinari
• "<>text" conta numeri, booleani, errori e celle vuote; un "<>" nudo conta le celle non vuote, risultati ="" compresi
• DSUM e le altre funzioni di database trattano il testo semplice come "inizia per"; =text e <>text confrontano l'intera voce (dalla v2.384.64)
• Il Find a cella intera con lxfUseWildcards e lxfWholeCell fa backtrack, quindi a*b combacia con abcb; Find e Replace trattano ~ come escape per qualsiasi carattere (dalla v2.384.60)
• L'ordine testuale nei criteri > e < segue la collation word sort di Excel, punteggiatura prima delle lettere (dalla v2.384.67)

La compatibilità con Excel in un motore di formule è per lo più fatta di casi limite come questi, misurati contro Excel anziché indovinati dalla documentazione. HotXLS valuta COUNTIF, MATCH, XLOOKUP, DSUM e il resto della sua libreria di funzioni nativamente in Delphi e C++Builder, sia nel motore classico sia nel motore XLSX, senza Excel installato. Dettagli, edizioni e download di prova sono sulla pagina del componente foglio di calcolo Delphi HotXLS