HotXLS Excel džoker znakovi: COUNTIF, MATCH, DSUM i Find

HotXLS Delphi Component čita isti string obrasca na četiri različita načina, zato što i Excel 16. U COUNTIF i SUMIF je tekst a~b literal osim ako kriterijum takođe ne sadrži * ili ?; u wildcard režimu MATCH-a i XLOOKUP-a je tilda uvek escape, pa a~b nalazi ab; u DSUM-u i ostalim database funkcijama običan tekst znači „počinje sa“; a Find cele ćelije mora da se vrati unazad do poslednje *. HotXLS prati ova izmerena pravila od v2.384.52, v2.384.60 i v2.384.64

Prijave bagova na ovom području nikad ne spominju džoker znakove. Kažu da serverski generisan izveštaj broji par redova manje nego isti fajl preračunat u Excelu, ili da se broj dela koji sadrži tildu nađe jednom formulom, a sledeća ga ignoriše. Uzrok je matcher koji pretpostavlja da obrazac znači jednu stvar svuda. Excel tako ne radi, pa engine čiji keširani rezultati moraju da se poklapaju s Excelom ne sme ni on. Pre v2.384.52 je HotXLS provlačio svaki kriterijum kroz DOS-stilsku fajl masku, koja je svakodnevne obrasce pogađala, a granične slučajeve tiho promašivala

Zašto jedan string obrasca znači četiri različite stvari u Excelu?

Jedan string obrasca znači četiri različite stvari jer je Excel nasledio četiri pravila poklapanja od četiri funkcionalnosti i nikad ih nije ujedinio. Funkcije kriterijuma (COUNTIF, SUMIF, AVERAGEIF i porodica *IFS) odlučuju po kriterijumu da li se džoker znakovi uopšte primenjuju. Funkcije pretrage (MATCH s tipom poklapanja 0, XLOOKUP s match_mode 2) ih primenjuju uvek. Database funkcije (DSUM, DCOUNTA i drugari) prate Advanced Filter, gde je gola reč prefiks. Find dijalog ima sopstvene režime cele ćelije i delimičnog poklapanja. Tabela ispod navodi koje ćelije poklapa svaki obrazac nad jednom kolonom koja drži a~b, ab, AB, abc, abcb, a*b i axb, sa svakom funkcijom u njenom podrazumevanom režimu bez razlikovanja velikih i malih slova

Obrazac	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP režim 2	DSUM kriterijum	Find, cela ć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čujući 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 primenjuje se	ab, AB	ne primenjuje se

Red a~b je onaj gde se COUNTIF i MATCH razilaze, a brojevi delova i ručno kucani kodovi sadrže tilde češće nego što bilo ko očekuje. Red a*b pokazuje drugu zamku: abc se poklapa za DSUM ali ne i za COUNTIF, jer database funkcija tiho doda *. DSUM unosi za ab, a*b i =ab potiču pravo iz Excel 16 pokretanja; DSUM unos za a~b sledi iz istog pravila prefiksa, pošto dodati * pretvara kriterijum u wildcard obrazac u kojem je ~b zaštićeno (escaped) b

Kada COUNTIF prelazi u wildcard režim?

COUNTIF prelazi u wildcard režim samo kad tekst kriterijuma sadrži * ili ?, zaštićene ili ne. Bez ijednog od ta dva znaka, Excel poredi kriterijum sa svakom ćelijom kao sa celim stringom, bez razlikovanja velikih i malih slova, i tilda je samo tilda, pa COUNTIF(A1:A7,"a~b") broji ćeliju koja bukvalno drži a~b. Dodajte jednu zvezdicu i značenje se preokreće: u "a~b*" tilda sada štiti b, obrazac se čita kao „ab pa bilo šta“, i ćelija a~b se više ne broji. HotXLS ovo pravilo primenjuje u oba engine-a od v2.384.52, kroz jedan kriterijumski matcher u lxCalc-u koji dele COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS i database funkcije

Unutar wildcard režima pravila zaštite ista su kao svuda drugde u Excelu: ~ čini sledeći znak literalnim šta god bio, pa ~b znači b a ~~ znači jednu tildu, i tilda na samom kraju obrasca se odbacuje, pa se "a*~" ponaša kao "a*". Uglaste zagrade nikad nisu posebne. Kriterijum "[x]" broji ćelije koje drže tri znaka [x], a "[a-z]" ne broji ništa na običnim podacima. TXLSXWorkbook.Calculate izračunava string formule nad aktivnim listom i vraća Variant, najbrži način da ova pravila proverite nad svojim 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 ... da SUMIF ukupno imenuje svoje redove
    end;
    Sheet.Cells[8, 1].Value := 5;                // broj; A9 ostaje prazan

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    nema * ni ?: običan tekst, ćelija a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard režim: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard celog stringa, 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 prazna A9 se računaju
    Show('=COUNTIF(A1:A9,"<>")');       // 8    neprazne ćelije
  finally
    Book.Free;
  end;
end.

Šta broji "<>text"?

Kriterijum "<>text" broji svaku ćeliju koja nije taj tekst, i u Excelu 16 to uključuje brojeve, booleane, greške i prazne ćelije. Goli "<>" je sasvim drugo pitanje: znači „nije prazna ćelija“, pa preskače prazne ćelije ali broji svaku vrednost, uključujući prazan tekst koji vrati formula poput ="". Stari HotXLS kod pogodio je tekstualne ćelije, ali ne i brojeve: Variant nejnakost naterala je Delphi da 'ab' prevede u broj, konverzija je digla izuzetak, handler ga je progutao kao „nema poklapanja“, i numeričke ćelije su tiho ispale iz brojanja. Strana praznih ćelija ove priče, uključujući čemu je jednak prazan operand u običnom poređenju, pokrivena je u tekstu o tome kako HotXLS rukuje lancima poređenja, praznim ćelijama i SUMIF-om

Zašto MATCH nađe 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 uvek u wildcard režimu, pa je tilda escape čak i kad obrazac ne sadrži * ni ?. Excel 16 to potvrđuje na opsegu od dve ćelije s a~b i ab: MATCH("a~b",D1:D2,0) vraća 2, a na opsegu koji drži samo a~b isti poziv vraća #N/A. Da biste tražili literalni tekst a~b, morate napisati "a~~b". U međuvremenu COUNTIF(D1:D2,"a~b") nad istim dvema ćelijama vraća 1, brojeći drugu ćeliju. Isti string, isti opseg, suprotna ćelija

Zato HotXLS drži te dve odluke odvojene, umesto iza jednog ulaza „poklopi obrazac“. Sam matcher je deljen: od v2.384.52 MATCH, XLOOKUP i funkcije kriterijuma vode isti matcher s vraćanjem unazad, s istim rukovanjem zaštićenim znacima i istim pravilom tilde na kraju. Razlikuje se kapija ispred njega. Put kriterijuma prvo pita „da li ovaj tekst sadrži * ili ??“; put pretrage nikad ne pita. Spajanje ta dva popravilo bi jednu porodicu i slomilo drugu, i oba smera se proveravaju protiv vrednosti Excela 16 u oba engine-a. Wildcard pretrage imaju i svoj preduslov: XLOOKUP odbija wildcard poklapanje kombinovano s binarnim režimom pretrage, pravilo opisano u HotXLS vodiču o režimima pretrage XLOOKUP i XMATCH

Kako DSUM i database funkcije čitaju tekstualni kriterijum bez znakova?

DSUM i ostale database funkcije čitaju tekstualni kriterijum bez vodećeg =, < ili > kao „počinje sa“, uz i dalje aktivne džoker znakove. To je pravilo Advanced Filter-a, i namerno se razlikuje od COUNTIF-a. Excel 16 izmeren nad kolonom Name s abc, ab, xab, AB, a~b i a*b: kriterijum ab poklapa abc, ab i AB; =ab poklapa samo ab i AB; <>ab je nejnakost celog unosa; a*b i a? su takođe prefiksni obrasci; >ab je obično poređenje. Pre v2.384.64 HotXLS je poklapao ab tačno, pa je DSUM nad tim probnim podacima vraćao 10 gde Excel vraća 11

Popravka je morala da zaobiđe parser uslova, koji ab i =ab stapa u isti uslov jednakosti. HotXLS zato pregleda sirovi tekst kriterijuma pre nego što veruje parsiranom uslovu: tekstualni kriterijum čiji prvi znak nije =, < ili > dobija dodat * i ide kroz wildcard matcher, a sve ostalo zadržava poređenje celog unosa. Jedna praktična napomena kad gradite opsege kriterijuma u kodu: u XLSX engine-u dodela stringa '=ab' u TXLSXCell.Value čuva tekst, dok klasični TXLSWorkbook engine vrednost koja počinje s = kompajlira kao formulu, osim ako je ne stavite iza apostrofa

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

Jedna srodna razlika preživela je popravku prefiksa i znači na starijim buildovima. Tekstualna poređenja poput >ab koristila su code-point red, dok Excel stavlja interpunkciju pre slova, pa je "a~b">"ab" FALSE u Excelu, a u HotXLS-u je bilo TRUE. Od v2.384.67 kriterijumi > i <, zajedno s običnim tekstualnim poređenjem i sortiranjem, koriste Excelovu word-sort kolaciju pod trenutnim lokalom korisnika, i ta dva su se ponovo poklopila

Zašto je Find cele ćelije promašio abcb?

Find cele ćelije promašio je abcb jer je matcher stao na prvom mestu gde je obrazac potrošen, umesto da se vrati unazad do poslednje *. Matcher delimičnog poklapanja iza Replace-a vraća se čim se obrazac iscrpi; Find cele ćelije iskoristio ga je pa zatim zahtevao da poklapanje pokrije celu ćeliju: a*b naspram abcb stao je posle ab, potrošio 2 od 4 znaka i bio odbijen. Od v2.384.60 matcher cele ćelije je odvojena implementacija koja „obrazac gotov, tekst nije“ tretira kao još jedno nepoklapanje i pokušava ponovo od poslednje zvezdice, pa a*b poklapa abcb a a?b*b poklapa axbyb, kao što Excel 16 Find radi sa uključenim „Match entire cell contents“

Isto izdanje promenilo je tildu. Excel 16 Find, i u režimu cele ćelije i u delimičnom, tretira ~ kao zaštitu za bilo koji sledeći znak: a~b nalazi ab, a~~b nalazi a~b, i tilda na kraju se ignoriše, pa se q~ ponaša kao q. Stariji HotXLS matcher priznavao je samo ~*, ~? i ~~ kao zaštićene znakove, pa je a~b nalazio tekst a~b. Find obrazac od jedne ~ je sam po sebi nestabilan u Excelu, poklapa svaku ćeliju kao prazan obrazac, i HotXLS to ne oponaša

U XLSX engine-u pretraga je TXLSXWorksheet.FindText s TXLSXFindOptions skupom: lxfUseWildcards uključuje *, ? i ~, lxfWholeCell traži da se poklopi cela ćelija, a lxfMatchCase čini poređenje osetljivim na velika i mala slova. Bez lxfUseWildcards svaki je znak, pa i zvezdica, literal. Find gleda samo tekstualne vrednosti; numeričke ćelije se preskaču, i ćelije formula se preskaču osim ako je postavljen lxfSearchFormulas, kada se pretražuje tekst formule. Sidro zadato s StartRow i StartCol je uključivo, pa Find All petlja prebaci jednu kolonu 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 se vraća unazad
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b je zaštićeno b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ je jedna literalna tilda

    // Delimično poklapanje, Find All: sidorna ćelija se uključuje, pa korak preko 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;

    // Zamena s wildcard-om cele ćelije prepravlja samo literalni a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Delimična petlja nalazi sva četiri reda, uključujući abc, jer u delimičnom režimu a*b samo mora da se pojavi negde unutar ćelije. FindTextIn i ReplaceTextIn primaju iste opcije plus prozor FirstRow, FirstCol, LastRow, LastCol, programski ekvivalent pretrage unutar izbora. Klasični engine izlaže ista pravila kroz overload s tri booleana, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus odgovarajući ReplaceText overload, s rezultatima reda i kolone počevši 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;

Šta je stari DOS-mask matcher pogrešio?

Stari matcher pogrešio je specijalne znakove, jer je DOS fajl maska drugi jezik od Excel džoker znakova. Pre v2.384.52 funkcije kriterijuma i database funkcije slale su svaki obrazac u MatchesMask, matcher fajl maski u jedinici lxMasks. Njegova sintaksa se preklapa s Excelovom za uobičajene slučajeve, pa se problem krio, ali razilazi se tamo gde pravi podaci postaju zanimljivi:

• [x] se čitao kao skup znakova, pa je COUNTIF(A1:A10,"[x]") brojao ćelije s x umesto teksta u zagradi, a "[a-z]" je poklapao svaku ćeliju od jednog slova
• Nije postojala tilda-zaštita, pa "a~*b" nije mogao da poklopi literalnu zvezdicu
• Deformisana maska, poput nezatvorene zagrade, digla je izuzetak koji je pozivač progutao kao „nema poklapanja“, pretvarajući grešku u kucanju u kriterijumu u tiho pogrešan zbir
• Na strani pretrage, MATCH i XLOOKUP priznavali su samo ~*, ~? i ~~ kao zaštićene znakove, pa je MATCH("a~b",…,0) nalazio literalni a~b umesto ab

Ako su vaše radne sveske koristile samo * i ? nad običnim alfanumeričkim podacima, rezultati su već bili ispravni i neće se promeniti. Ako sadrže zagrade, tilde, kolone mešanih tipova pod "<>text", ili DSUM kriterijume zapisane kao gole reči, njihovo preračunavanje s v2.384.64 ili kasnijom može promeniti zbirove, i novi su zbirovi oni koje Excel pokazuje. Ista razlika između toga kako Excel čuva kriterijum i kako ga poredi iskače kod sačuvanih filtera, o čemu se govori u HotXLS tekstu o BIFF8 AutoFilter DOPER kriterijumima

Brzi podsetnik: pravila Excel džoker znakova u HotXLS-u

• COUNTIF, SUMIF, AVERAGEIF i porodica *IFS koriste džoker znakove samo kad kriterijum sadrži * ili ?; inače poredaju cele stringove bez razlikovanja velikih i malih slova i ~ je literal (od v2.384.52)
• MATCH s tipom poklapanja 0 i XLOOKUP s match_mode 2 uvek koriste džoker znakove, pa a~b nalazi ab, a literal traži a~~b (od v2.384.52)
• U wildcard režimu ~ štiti bilo koji sledeći znak, a ~ na kraju se odbacuje; [ i ] su obični znakovi
• "<>text" broji brojeve, booleane, greške i prazne ćelije; goli "<>" broji neprazne ćelije, uključujući rezultate =""
• DSUM i ostale database funkcije tretiraju običan tekst kao „počinje sa“; =text i <>text poredaju ceo unos (od v2.384.64)
• Find cele ćelije s lxfUseWildcards i lxfWholeCell vraća se unazad, pa a*b poklapa abcb; Find i Replace tretiraju ~ kao zaštitu za bilo koji znak (od v2.384.60)
• Tekstualni red u kriterijumima > i < prati Excelovu word-sort kolaciju, interpunkcija pre slova (od v2.384.67)

Excel kompatibilnost u engine-u formula je pretežno granični slučajevi poput ovih, mereni protiv Excela a ne pogađani iz dokumentacije. HotXLS izračunava COUNTIF, MATCH, XLOOKUP, DSUM i ostatak svoje biblioteke funkcija nativno u Delphi-ju i C++Builder-u, u klasičnom engine-u i XLSX engine-u, bez instaliranog Excela. Detalji, izdanja i probno preuzimanje su na stranici HotXLS Delphi spreadsheet component