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
| 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 |
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
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
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
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, quindiCOUNTIF(A1:A10,"[x]")contava le celle che contenevanoxanziché 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 trovareab, non laa~bletterale
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