HotXLS Excel wildcards: COUNTIF, MATCH, DSUM i Find

HotXLS Delphi Component čita isti niz uzorka na četiri različita načina, baš kao i Excel 16. U COUNTIF i SUMIF tekst a~b je literal dok kriterij osim toga ne sadrži * ili ?; u MATCH i XLOOKUP wildcard načinu tilde je uvijek escape, pa a~b nalazi ab; u DSUM i ostalim funkcijama baze običan tekst znači "počinje s", a Find po cijeloj ćeliji mora raditi backtrack do zadnje *. HotXLS slijedi ta izmjerena pravila od v2.384.52, v2.384.60 i v2.384.64

Bug izvještaji s tog područja nikad ne spominju wildcardove. Kažu da izvještaj koji generira server broji par redova manje nego ista datoteka preračunata u Excelu, ili da šifra dijela koja sadrži tilde bude pronađena jednom formulom, a ignorirana od sljedeće. Uzrok je matcher koji pretpostavlja da uzorak znači jednu te istu stvar posvuda. Excel tako ne radi, pa motor čiji predmemorirani rezultati moraju odgovarati Excelu to ne smije ni sam. Prije v2.384.52 HotXLS je svaki kriterij provlačio kroz masku datoteka u DOS stilu, koja je svakodnevne uzorke pogađala, a rubne slučajeve tiho promašivala

Zašto jedan niz uzorka u Excelu znači četiri različite stvari?

Jedan niz uzorka znači četiri različite stvari jer je Excel naslijedio četiri pravila poklapanja iz četiri značajke i nikad ih nije ujedinio. Funkcije kriterija (COUNTIF, SUMIF, AVERAGEIF i obitelj *IFS) po kriteriju odlučuju primjenjuju li se wildcardovi uopće. Funkcije pretraživanja (MATCH s tipom poklapanja 0, XLOOKUP s match_mode 2) primjenjuju ih uvijek. Funkcije baze podataka (DSUM, DCOUNTA i drugi) slijede Advanced Filter, gdje je gola riječ prefiks. Dijalog Find ima vlastite načine: cijela ćelija i djelomični. Tablica u nastavku navodi koje ćelije svaki uzorak pogađa nad jednim stupcem koji drži a~b, ab, AB, abc, abcb, a*b i axb, sa svakom funkcijom u njezinu zadanom načinu bez razlikovanja velikih i malih slova

Uzorak	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP način 2	DSUM kriterij	Find, cijela ćelija, wildcardovi uključeni
`ab`	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
`a*b`	a~b, ab, AB, abcb, a*b, axb	isto kao COUNTIF	svaki unos, uključivo abc	isto kao COUNTIF
`a~b`	samo a~b	ab, AB	ab, AB, abc, abcb	ab, AB
`a~*b`	samo a*b	samo a*b	samo a*b	samo a*b
`=ab`	ab, AB	ne primjenjuje se	ab, AB	ne primjenjuje se

Red s a~b jest onaj gdje se COUNTIF i MATCH razilaze, a šifre dijelova i ručno tipkani kodovi sadrže tilde češće nego itko očekuje. Red s a*b pokazuje drugu zamku: abc pogađa za DSUM, ali ne i za COUNTIF, jer funkcija baze tiho dodaje *. DSUM unosi za ab, a*b i =ab dolaze izravno iz pokretanja Excela 16; DSUM unos za a~b slijedi iz istog pravila prefiksa, jer dodani * pretvara kriterij u wildcard uzorak u kojem je ~b escapirano b

Kada COUNTIF prelazi u wildcard način?

COUNTIF prelazi u wildcard način samo kad tekst kriterija sadrži * ili ?, escapirane ili ne. Bez ijednog od ta dva znaka Excel uspoređuje kriterij sa svakom ćelijom kao cijeli niz, bez razlikovanja velikih i malih slova, a tilde je samo običan znak, pa COUNTIF(A1:A7,"a~b") broji ćeliju koja doslovno drži a~b. Dodajte jednu zvjezdicu i značenje se okreće: u "a~b*" tilde sada escapira b, uzorak se čita kao "ab pa bilo što", i ćelija a~b se više ne broji. HotXLS primjenjuje ovo pravilo u oba motora od v2.384.52, kroz jedan matcher kriterija u lxCalc koji dijele COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS i funkcije baze

HotXLS dijagram wildcard kapije: COUNTIF i SUMIF primjenjuju wildcardove samo kad kriterij sadrži zvjezdicu ili upitnik, pa a~b broji literalnu ćeliju i vraća 1, dok su MATCH tip 0 i XLOOKUP način 2 uvijek u wildcard načinu, pa a~b nalazi ab na poziciji 2 — Kapija je cijela razlika: COUNTIF traži zvjezdicu ili upitnik prije nego tilde tretira kao escape, MATCH nikad ne pita, pa jedan niz uzorka broji jednu ćeliju, a nalazi drugu

Unutar wildcard načina pravila escapiranja ista su kao i svugdje drugdje u Excelu: ~ čini sljedeći znak literalnim kakav god bio, pa ~b znači b, a ~~ znači jedan znak tilde, a tilde na samom kraju uzorka baca se, pa se "a*~" ponaša kao "a*". Uglate zagrade nikad nisu posebne. Kriterij "[x]" broji ćelije koje drže tri znaka [x], a "[a-z]" na običnim podacima ne broji ništa. TXLSXWorkbook.Calculate evaluira niz formule nad aktivnim listom i vraća Variant, najbrži način da ta pravila provjerite nad vlastitim podacima

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 ... pa zbroj SUMIF-a imenuje svoje redove
    end;
    Sheet.Cells[8, 1].Value := 5;                // broj; A9 ostaje prazan

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    bez * ili ?: običan tekst, ćelija a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard način: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard nad cijelim nizom, abc isključen
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  svaki red osim abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    literalni a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    broj 5 i prazan A9 se broje
    Show('=COUNTIF(A1:A9,"<>")');       // 8    neprazne ćelije
  finally
    Book.Free;
  end;
end.

Što broji "<>text"?

Kriterij "<>text" broji svaku ćeliju koja nije taj tekst, a u Excelu 16 to uključuje brojeve, logičke vrijednosti, vrijednosti grešaka i prazne ćelije. Goli "<>" posve je drugo pitanje: znači "nije prazna ćelija", pa preskače prazne ćelije, ali broji svaku vrijednost, uključujući prazan tekst koji vraća formula poput ="". Stari HotXLS kod tekstualne ćelije pogađao je točno, ali ne i brojeve: nejednakost nad Variant tjerala je Delphi da 'ab' pretvori u broj, pretvorba je podigla iznimku, handler ju je progutao kao "nema podudaranja", i numeričke ćelije su tiho ispale iz brojanja. Stranu priče o praznim ćelijama, uključujući čemu je jednak prazan operand u običnoj usporedbi, pokriva kako HotXLS rukuje lancima usporedbi, praznim ćelijama i SUMIF-om

Zašto MATCH nalazi ab kad tražite a~b?

MATCH nalazi ab kad tražite a~b jer su MATCH s tipom poklapanja 0 i XLOOKUP s match_mode 2 uvijek u wildcard načinu, pa je tilde escape čak i kad uzorak ne sadrži * ni ?. Excel 16 to potvrđuje na rasponu od dvije ćelije koji drži a~b i ab: MATCH("a~b",D1:D2,0) vraća 2, a na rasponu koji drži samo a~b isti poziv vraća #N/A. Da biste dohvatili literalni tekst a~b, morate napisati "a~~b". U međuvremenu COUNTIF(D1:D2,"a~b") nad istim dvjema ćelijama vraća 1 i broji drugu ćeliju. Isti niz, isti raspon, suprotna ćelija

Zato HotXLS drži te dvije odluke odvojene, a ne iza jedne ulazne točke "poklopi uzorak". Sam matcher je zajednički: od v2.384.52 MATCH, XLOOKUP i funkcije kriterija pokreću isti backtracking matcher, s istim rukovanjem escapeovima i istim pravilom tilde na kraju. Razlika je u kapiji ispred njega. Put kriterija prvo pita "sadrži li ovaj tekst * ili ??"; put pretraživanja nikad ne pita. Spajanje dvaju popravilo bi jednu obitelj i slomilo drugu, a oba smjera provjeravaju se protiv vrijednosti Excela 16 u oba motora. Wildcard pretraživanja imaju i svoj vlastiti preduvjet: XLOOKUP odbija wildcard poklapanje u kombinaciji s načinom binarnog pretraživanja, pravilo opisano u HotXLS vodiču kroz XLOOKUP i XMATCH načine pretraživanja

Kako DSUM i funkcije baze čitaju kriterij od običnog teksta?

DSUM i ostale funkcije baze čitaju tekstualni kriterij bez vodećeg =, < ili > kao "počinje s", uz i dalje aktivne wildcardove. To je pravilo Advanced Filtera i od COUNTIF se razlikuje namjerno. Excel 16 izmjeren nad stupcem Name koji drži abc, ab, xab, AB, a~b i a*b: kriterij ab pogađa abc, ab i AB; =ab pogađa samo ab i AB; <>ab je nejednakost cijelog unosa; a*b i a? također su prefiks uzorci; >ab je obična usporedba. Prije v2.384.64 HotXLS je ab pogađao točno, pa je DSUM nad tim testnim podacima vraćao 10 tamo gdje Excel vraća 11

Popravak je morao zaobići parser uvjeta, koji i ab i =ab svodi na isti uvjet jednakosti. HotXLS zato pregledava sirovi tekst kriterija prije nego uzda se u parsirani uvjet: tekstualni kriterij čiji prvi znak nije =, < ili > dobiva dodani * i prolazi kroz wildcard matcher, a sve ostalo zadržava usporedbu cijelog unosa. Jedna praktična napomena kad kriterijske raspone gradite u kodu: u XLSX motoru dodjela niza '=ab' u TXLSXCell.Value sprema tekst, dok klasični TXLSWorkbook motor vrijednost koja počinje s = kompajlira kao formulu, osim ako je prethodite apostrofom

HotXLS dijagram pravila DSUM kriterija: goli tekstualni kriterij dobiva dodanu zvjezdicu i pogađa kao prefiks pa ab doseže ab, AB, abc i abcb, jednako ab uspoređuje cijeli unos, uglasta zagrada ab isključuje oba, a tilde zvjezdica preživi kao literalni a*b, uz izmjerene DSUM ukupnosti 30, 6, 121 i 32 — Excel je naslijedio pravilo Advanced Filtera za funkcije baze: goli tekst znači počinje s, dok vodeći znak jednakosti ili nejednakosti uspoređuje cijeli unos; HotXLS pregledava sirovi tekst kriterija prije nego uzda se u parsirani uvjet

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';            // zaglavlje kriterija u D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // ostaje tekst u XLSX motoru
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (počinje s)
    // =ab  -> 6    ab, AB (cijeli unos)
    // <>ab -> 121  sve osim ab i AB
    // a*b  -> 127  a*b* pogađa svih sedam, uključivo abc
    // a~*  -> 32   samo literalni a*b
  finally
    Book.Free;
  end;
end;

Jedna povezana razlika preživjela je popravak prefiksa i važi na starijim buildovima. Tekstualne usporedbe poput >ab koristile su poredak po kodnim točkama, dok Excel interpunkciju stavlja prije slova, pa je "a~b">"ab" FALSE u Excelu, a u HotXLS-u je bilo TRUE. Od v2.384.67 kriteriji > i <, zajedno s običnom tekstualnom usporedbom i sortiranjem, koriste Excelovo word-sort uspoređivanje prema trenutnim lokalnim postavkama korisnika, pa se dvoje opet slaže

Zašto Find po cijeloj ćeliji promašio abcb?

Find po cijeloj ćeliji promašio je abcb jer je matcher stao na prvoj točki gdje je uzorak potrošen umjesto da radi backtrack do zadnje *. Matcher djelomičnog podudaranja koji stoji iza Replacea vraća se čim je uzorak iscrpljen; Find po cijeloj ćeliji iskoristio ga je, a zatim zahtijevao da podudaranje pokrije cijelu ćeliju: a*b nad abcb stao je nakon ab, potrošio 2 od 4 znaka i bio odbijen. Od v2.384.60 matcher cijele ćelije zasebna je implementacija koja "uzorak gotov, tekst nije" tretira kao još jedno nepodudaranje i pokušava iznova od zadnje zvjezdice, pa a*b pogađa abcb, a a?b*b pogađa axbyb, kao Excel 16 Find s uključenom opcijom "Match entire cell contents"

Podudaranje po cijeloj ćeliji nije gotovo kad se uzorak iscrpi; tretiranje preostalog teksta kao još jednog nepodudaranja vraća matcher zadnjoj zvjezdici, pa a*b doseže abcb kao Excel 16 Find

Isto izdanje promijenilo je i tildu. Excel 16 Find, i u načinu cijele ćelije i u djelomičnom, tretira ~ kao escape za bilo koji sljedeći znak: a~b nalazi ab, a~~b nalazi a~b, a tilde na kraju zanemaruje se, pa se q~ ponaša kao q. Stariji HotXLS matcher priznavao je samo ~*, ~? i ~~ kao escapeove, pa je a~b nalazio tekst a~b. Find uzorak od jedne ~ nestabilan je i u samom Excelu — pogađa svaku ćeliju kao prazan uzorak — i HotXLS to ne oponaša

U XLSX motoru pretraživanje je TXLSXWorksheet.FindText sa skupom TXLSXFindOptions: lxfUseWildcards uključuje *, ? i ~, lxfWholeCell zahtijeva da se poklopi cijela ćelija, a lxfMatchCase čini usporedbu osjetljivom na velika i mala slova. Bez lxfUseWildcards svaki je znak, zvjezdica uključena, literal. Find gleda samo tekstovne vrijednosti; numeričke ćelije se preskaču, a ćelije formula se preskaču osim ako je postavljen lxfSearchFormulas, kada se pretražuje tekst formule. Sidro zadano s StartRow i StartCol uključivo je, pa Find All petlja staje jedan stupac iza svakog pogotka

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 odbijen, abcb radi backtrack
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b je escapirano b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ je jedna literalna tilde

    // Djelomično podudaranje, Find All: sidro je uključeno, pa zađite iza svakog pogotka
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // redovi 1, 2, 3 i 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Zamjena s wildcardovima po cijeloj ćeliji prepisuje samo literalni a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Djelomična petlja nalazi sva četiri reda, uključivo abc, jer u djelomičnom načinu a*b samo mora negdje postojati unutar ćelije. FindTextIn i ReplaceTextIn primaju iste opcije plus prozor FirstRow, FirstCol, LastRow, LastCol, programski ekvivalent pretraživanja unutar selekcije. Klasični motor ista pravila izlaže kroz overload s tri booleana, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus odgovarajući ReplaceText overload, s rezultatima redova i stupaca od 1:

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;

Što je stari matcher s DOS maskama radio krivo?

Stari matcher pogriješio je u posebnim znakovima jer je DOS maska datoteka drugačiji jezik od Excelova wildcarda. Prije v2.384.52 funkcije kriterija i funkcije baze prosljeđivale su svaki uzorak u MatchesMask, matcher maski datoteka u jedinici lxMasks. Njegova se sintaksa za uobičajene slučajeve preklapa s Excelovom, pa je problem ostao skriven, ali razilazi se ondje gdje pravi podaci postaju zanimljivi:

[x] čitan je kao skup znakova, pa je COUNTIF(A1:A10,"[x]") brojao ćelije koje drže x umjesto tekst u zagradama, a "[a-z]" pogađao je svaku jednoslovnu ćeliju
Nije postojao escape za tilde, pa "a~*b" nije mogao poklopiti literalnu zvjezdicu
Deformirana maska, poput nezatvorene zagrade, podizala je iznimku koju je pozivatelj progutao kao "nema podudaranja", pa je tipfeler u kriteriju postajao tiho pogrešan zbroj
Na strani pretraživanja MATCH i XLOOKUP samo su ~*, ~? i ~~ tretirali kao escapeove, pa je MATCH("a~b",…,0) nalazio literalni a~b umjesto ab

Ako vaše radne knjige nikad nisu koristile ništa osim * i ? nad običnim alfanumeričkim podacima, rezultati su već bili točni i neće se promijeniti. Ako sadrže uglate zagrade, tilde, stupce miješanih tipova pod "<>text" ili DSUM kriterije zapisane kao gole riječi, njihovo preračunavanje s v2.384.64 ili novijom može promijeniti zbrojeve, a novi su zbrojevi oni koje Excel prikazuje. Ista razlika između kako Excel sprema kriterij i kako ga uspoređuje javlja se i kod spremljenih filtara, o čemu govori HotXLS članak o BIFF8 AutoFilter DOPER kriterijima

Brza referenca: Excel pravila wildcarda u HotXLS-u

COUNTIF, SUMIF, AVERAGEIF i obitelj *IFS koriste wildcardove samo kad kriterij sadrži * ili ?; inače uspoređuju cijele nizove bez razlikovanja velikih i malih slova, a ~ je literal (od v2.384.52)
MATCH s tipom poklapanja 0 i XLOOKUP s match_mode 2 uvijek koriste wildcardove, pa a~b nalazi ab, a za literal treba a~~b (od v2.384.52)
U wildcard načinu ~ escapira bilo koji sljedeći znak, a ~ na kraju baca se; [ i ] obični su znakovi
"<>text" broji brojeve, logičke vrijednosti, greške i prazne ćelije; goli "<>" broji neprazne ćelije, uključujući rezultate =""
DSUM i ostale funkcije baze običan tekst tretiraju kao "počinje s"; =text i <>text uspoređuju cijeli unos (od v2.384.64)
Find po cijeloj ćeliji s lxfUseWildcards i lxfWholeCell radi backtrack, pa a*b pogađa abcb; Find i Replace ~ tretiraju kao escape za bilo koji znak (od v2.384.60)
Redoslijed teksta u kriterijima > i < slijedi Excelovo word-sort uspoređivanje, interpunkcija prije slova (od v2.384.67)

Excel kompatibilnost u motoru formula uglavnom su rubni slučajevi poput ovih, izmjereni protiv Excela, a ne nagađani iz dokumentacije. HotXLS evaluira COUNTIF, MATCH, XLOOKUP, DSUM i ostatak svoje biblioteke funkcija nativno u Delphiju i C++Builderu, u klasičnom i XLSX motoru, bez instaliranog Excela. Detalji, izdanja i probno preuzimanje na stranici HotXLS Delphi proračunske komponente