HotXLS Excel wildcardsi: COUNTIF, MATCH, DSUM in Find

Komponenta HotXLS Delphi bere isti vzorčni niz na štiri različne načine, ker tudi Excel 16. V COUNTIF in SUMIF je besedilo a~b dobesedno, razen če kriterij vsebuje tudi * ali ?; v wildcard načinu MATCH in XLOOKUP je tilda vedno ubežni znak, zato a~b najde ab; v DSUM in drugih funkcijah baz podatkov čisto besedilo pomeni »se začne z«; iskanje po celih celicah pa se mora vrniti nazaj v zadnji *. HotXLS tem izmerjenim pravilom sledi od v2.384.52, v2.384.60 in v2.384.64

Hroščja poročila s tega področja nikoli ne omenjajo wildcards. Pravijo, da strežniško generirano poročilo prešteje par vrstic manj kot ista datoteka, preračana v Excelu, ali da številko dela s tildo najde ena formula in jo ignorira naslednja. Vzrok je ujemalec, ki domneva, da vzorec povsod pomeni isto. Excel ne deluje tako, zato tudi pogon, katerega predpomnjeni rezultati se morajo ujemati z Excelom, ne more delovati tako. Pred v2.384.52 je HotXLS vsak kriterij prežene skozi datotečno masko po vzoru DOS, ki je vsakdanje vzorce zadela prav, mejne primere pa tiho zgrešila

Zakaj en vzorčni niz v Excelu pomeni štiri različne stvari?

En vzorčni niz pomeni štiri različne stvari, ker je Excel podedoval štiri pravila ujemanja od štirih funkcij in jih nikoli ni poenotil. Kriterijske funkcije (COUNTIF, SUMIF, AVERAGEIF in družina *IFS) za vsak kriterij posebej odločijo, ali se wildcardsi sploh uporabijo. Iskalne funkcije (MATCH z vrsto ujemanja 0, XLOOKUP z match_mode 2) jih vedno uporabijo. Funkcije baz podatkov (DSUM, DCOUNTA in prijatelji) sledijo naprednemu filtru (Advanced Filter), kjer je gola beseda predpona. Pogovorno okno Find ima svoja načina cele celice in delnega ujemanja. Spodnja tabela našteje, katere celice ujame vsak vzorec nad enim stolpcem z a~b, ab, AB, abc, abcb, a*b in axb, vse funkcije pa v privzetem načinu brez ločevanja velikosti črk

Vzorec	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP način 2	Kriterij DSUM	Find, cela celica, wildcardsi vključeni
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	isto kot COUNTIF	vsak vnos, vključno z abc	isto kot 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 velja	ab, AB	ne velja

Vrstica a~b je tista, kjer se COUNTIF in MATCH razhajata, številke delov in ročno vtipikane kode pa vsebujejo tilde pogosteje, kot kdorkoli pričakuje. Vrstica a*b kaže drugo past: abc se ujame za DSUM, ne pa za COUNTIF, ker funkcija baz podatkov tiho pripne *. Vnosa DSUM za ab, a*b in =ab prideta naravnost iz pognanih Excelov 16; vnos DSUM za a~b sledi istemu pravilu predpone, saj pripeti * kriterij spremeni v wildcard vzorec, v katerem je ~b ubežen b

Kdaj COUNTIF preklopi v wildcard način?

COUNTIF preklopi v wildcard način samo, kadar besedilo kriterija vsebuje * ali ?, ubežena ali ne. Brez teh znakov Excel primerja kriterij z vsako celico kot celoten niz, brez ločevanja velikosti črk, tilda pa je samo tilda, zato COUNTIF(A1:A7,"a~b") prešteje celico, ki dobesedno drži a~b. Dodajte eno zvezdico in pomen se obrne: v "a~b*" tilda zdaj ubeži b, vzorec se bere kot »ab, mu sledi karkoli«, celica a~b pa se ne šteje več. HotXLS to pravilo uporablja v obeh pogonih od v2.384.52, skozi enega kriterijskega ujemalca v lxCalc, ki si ga delijo COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS in funkcije baz podatkov

Znotraj wildcard načina so pravila ubeže enaka kot povsod drugje v Excelu: ~ naredi naslednji znak dobesednega, kar koli že je, zato ~b pomeni b, ~~ pa eno tilo, tilda na samem koncu vzorca pa se odvrže, zato se "a*~" obnaša kot "a*". Oglati oklepaji nikoli niso posebni. Kriterij "[x]" prešteje celice, ki držijo tri znake [x], "[a-z]" pa na običajnih podatkih ne prešteje nič. TXLSXWorkbook.Calculate vrednoti niz formule na aktivnem listu in vrne Variant — najhitrejši način, da ta pravila preverite na svojih podatkih

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 ... zato vsota SUMIF poimenuje svoje vrstice
    end;
    Sheet.Cells[8, 1].Value := 5;                // številka; A9 ostane prazna

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    brez * ali ?: čisto besedilo, celica a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard način: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard celega niza, brez abc
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  vsaka vrstica razen abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    dobesedni a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    šteje številka 5 in prazna A9
    Show('=COUNTIF(A1:A9,"<>")');       // 8    neprazne celice
  finally
    Book.Free;
  end;
end.

Kaj prešteje »<>text«?

Kriterij "<>text" prešteje vsako celico, ki ni to besedilo, v Excelu 16 pa to vključuje številke, logične vrednosti, vrednosti napak in prazne celice. Gol "<>" je povsem drugo vprašanje: pomeni »ni prazna celica«, zato preskoči prazne celice, šteje pa vsako vrednost, vključno s praznim besedilom, ki ga vrne formula, kot je ="". Stara koda HotXLS je besedilne celice dobila prav, številskih pa ne: neenakost Variant je naredila, da je Delphi pretvoril 'ab' v številko, pretvorba je sprožila izjemo, ročajevalnik pa jo je pogoltnil kot »brez ujemanja«, številčne celice pa so tiho izpadle iz štetja. Prazno-celična stran te zgodbe, vključno s tem, čemu je enak prazen operand v navadni primerjavi, je pokrita v kako HotXLS opravi s primerjalnimi verigami, praznimi celicami in SUMIF

Zakaj MATCH najde ab, ko iščete a~b?

MATCH najde ab, ko iščete a~b, ker sta MATCH z vrsto ujemanja 0 in XLOOKUP z match_mode 2 vedno v wildcard načinu, tilda pa je ubežni znak tudi, ko vzorec ne vsebuje * ali ?. Excel 16 to potrdi na obsegu dveh celic z a~b in ab: MATCH("a~b",D1:D2,0) vrne 2, na obsegu, ki drži samo a~b, pa isti klic vrne #N/A. Da poiščete dobesedno besedilo a~b, morate napisati "a~~b". Medtem COUNTIF(D1:D2,"a~b") nad istima dvema celicama vrne 1 in prešteje drugo celico. Isti niz, isti obseg, nasprotna celica

Zato HotXLS drži ti dve odločitvi narazen, namesto da bi ju skril za enim vstopom »ujemi vzorec«. Ujemalec sam je deljen: od v2.384.52 MATCH, XLOOKUP in kriterijske funkcije poganjajo istega zaskočnega ujemalca, z enako obravnavo ubežev in istim pravilom končne tilde. Razlikuje se pregrada pred njim. Kriterijska pot najprej vpraša »ali to besedilo vsebuje * ali ??«; iskalna pot pa nikoli ne vpraša. Zlitje obeh bi popravilo eno družino in zlomilo drugo, obe smeri pa sta preverjeni proti vrednostim Excel 16 v obeh pogonih. Wildcard iskanja imajo tudi svoj pogoj: XLOOKUP zavrne wildcard ujemanje v kombinaciji z binarnim iskalnim načinom, pravilo, opisano v vodniku HotXLS po iskalnih načinih XLOOKUP in XMATCH

Kako DSUM in funkcije baz podatkov berejo kriterij iz čistega besedila?

DSUM in druge funkcije baz podatkov berejo besedilni kriterij brez vodilnega =, < ali > kot »se začne z«, wildcardsi pa ostanejo aktivni. To je pravilo naprednega filtra in se od COUNTIF razlikuje namenoma. Izmerjeno v Excelu 16 nad stolpcem Name z abc, ab, xab, AB, a~b in a*b: kriterij ab ujame abc, ab in AB; =ab ujame samo ab in AB; <>ab je neenakost celega vnosa; a*b in a? sta prav tako vzorca predpon; >ab je navadna primerjava. Pred v2.384.64 je HotXLS ujel ab točno, zato je DSUM nad temi preskusnimi podatki vrnil 10, kjer Excel vrne 11

Popravek se je moral izogniti razčlenjevalniku pogojev, ki tako ab kot =ab zloži v isti pogoj enakosti. HotXLS zato pregleda surovo besedilo kriterija, preden zaupa razčlenjenemu pogoju: besedilni kriterij, katerega prvi znak ni =, < ali >, dobi pripet * in gre skozi wildcard ujemalec, vse ostalo pa obdrži primerjavo celega vnosa. Ena praktična opomba, ko gradite obsege kriterijev v kodi: v pogonu XLSX dodelitev niza '=ab' na TXLSXCell.Value shrani besedilo, klasični pogon TXLSWorkbook pa vrednost, ki se začne z =, prevede kot formulo, če je ne predpostavite z opustkom

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';            // glava kriterijev v D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // v pogonu XLSX ostane besedilo
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (se začne z)
    // =ab  -> 6    ab, AB (cel vnos)
    // <>ab -> 121  vse razen ab in AB
    // a*b  -> 127  a*b* ujame vseh sedem, vključno z abc
    // a~*  -> 32   samo dobesedni a*b
  finally
    Book.Free;
  end;
end;

Ena povezana razlika je preživela popravek predpone in šteje pri starejših gradnjah. Besedilne primerjave, kot je >ab, so uporabile vrstni red kodnih točk, Excel pa postavi ločila pred črke, zato je "a~b">"ab" v Excelu FALSE, v HotXLS pa je bilo TRUE. Od v2.384.67 kriterija > in <, skupaj z navadno besedilno primerjavo in razvrščanjem, uporabljata Excelovo kolacijo word sort pod trenutnim uporabniškim localeom in spet se ujemata

Zakaj je Find na cele celice spregledal abcb?

Find na cele celice je spregledal abcb, ker se je ujemalec ustavil na prvem mestu, kjer je bil vzorec porabljen, namesto da bi se vrnil nazaj v zadnji *. Ujemalec delnega ujemanja za Replace se vrne takoj, ko je vzorec izčrpan; Find na cele celice ga je ponovno uporabil in nato zahteval, da ujemanje pokrije celotno celico: a*b proti abcb se je ustavil po ab, porabil 2 od 4 znakov in bil zavrnjen. Od v2.384.60 je ujemalec celih celic ločena implementacija, ki »vzorec končan, besedilo ne« obravnava kot še eno neujemanje in poskusi znova od zadnje zvezdice, zato a*b ujame abcb, a?b*b pa axbyb, kot to dela Excel 16 Find z označeno možnostjo »Match entire cell contents«

Ista izdaja je spremenila tildo. Excel 16 Find, tako v načinu celih celic kot delnem, obravnava ~ kot ubežni znak za kateri koli sledeči znak: a~b najde ab, a~~b najde a~b, končna tilda pa je ignorirana, zato se q~ obnaša kot q. Starejši ujemalec HotXLS je kot ubežne priznal samo ~*, ~? in ~~, zato je a~b našel besedilo a~b. Find vzorec iz ene same ~ je nestabilen že v samem Excelu, ujame katero koli celico kot prazen vzorec, HotXLS pa tega ne posnema

V pogonu XLSX je iskanje TXLSXWorksheet.FindText z množico TXLSXFindOptions: lxfUseWildcards vključi *, ? in ~, lxfWholeCell zahteva, da se ujame cela celica, lxfMatchCase pa primerjavo naredi občutljivo na velikost. Brez lxfUseWildcards je vsak znak, tudi zvezdica, dobesedni. Find gleda samo besedilne vrednosti; številčne celice preskoči, celice formul pa preskoči, dokler ni nastavljen lxfSearchFormulas, v tem primeru pa se išče besedilo formule. Sidro, dano s StartRow in StartCol, je vključno, zato zanka Find All korakne en stolpec mimo vsakega zadetka

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 zavrnjen, abcb se vrne nazaj
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b je ubežen b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ je ena dobesedna tilda

    // Delno ujemanje, Find All: sidrna celica je vključena, korak torej mimo vsakega zadetka
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // vrstice 1, 2, 3 in 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Zamenjava wildcard po celih celicah prepisuje samo dobesedni a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Delna zanka najde vse štiri vrstice, vključno z abc, ker mora biti v delnem načinu a*b le nekje znotraj celice. FindTextIn in ReplaceTextIn sprejemata isti možnosti plus okno FirstRow, FirstCol, LastRow, LastCol, programski ustreznik iskanja znotraj izbora. Klasični pogon izpostavi ista pravila prek preobremenitve s tremi logičnimi vrednostmi, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus ustrezno preobremenitev ReplaceText, z rezultati vrstic in stolpcev od 1 naprej:

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;

Kaj je stari ujemalec z DOS masko zadel narobe?

Stari ujemalec je zadel narobe posebne znake, ker je datotečna maska DOS drug jezik kot Excelov wildcard. Pred v2.384.52 so kriterijske funkcije in funkcije baz podatkov vsak vzorec podale MatchesMask, ujemalcu datotečnih mask iz enote lxMasks. Njegova sintaksa se z Excelovo prekriva pri običajnih primerih, zato je težava ostala skrita, divergira pa tam, kjer pravi podatki postanejo zanimivi:

• [x] je bil bran kot množica znakov, zato je COUNTIF(A1:A10,"[x]") preštel celice, ki držijo x, namesto oglatooklepajnega besedila, "[a-z]" pa se je ujemal z vsako enočrkovno celico
• Ubeža s tilo ni bilo, zato "a~*b" ni mogel ujeti dobesedne zvezdice
• Deformirana maska, na primer nezaprli oklepaj, je sprožila izjemo, ki jo je klicatelj pogoltnil kot »brez ujemanja«, tipkarska napaka v kriteriju pa se je spremenila v tiho napačno vsoto
• Na iskalni strani sta MATCH in XLOOKUP kot ubežne priznala samo ~*, ~? in ~~, zato je MATCH("a~b",…,0) našel dobesedni a~b namesto ab

Če so vaši delovni zvezki na čisto alfanumeričnih podatkih uporabljali samo * in ?, so bili rezultati že pravi in se ne bodo spremenili. Če vsebujejo oglate oklepaje, tilde, stolpce mešanih tipov pod "<>text" ali kriterije DSUM, zapisane kot gole besede, jih lahko ponovni preračun z v2.384.64 ali novejšo spremeni v vsotah — nove vsote pa so tiste, ki jih pokaže Excel. Ista razlika med tem, kako Excel kriterij shrani in kako ga primerja, se pojavi pri shranjenih filtrih, obravnavanih v članku HotXLS o kriterijih DOPER v BIFF8 AutoFilter

Hiter pregled: pravila Excel wildcards v HotXLS

• COUNTIF, SUMIF, AVERAGEIF in družina *IFS uporabijo wildcards samo, kadar kriterij vsebuje * ali ?; sicer primerjajo celoten niz brez ločevanja velikosti črk, ~ pa je dobeseden (od v2.384.52)
• MATCH z vrsto ujemanja 0 in XLOOKUP z match_mode 2 vedno uporabita wildcards, zato a~b najde ab, dobesedno besedilo pa potrebuje a~~b (od v2.384.52)
• V wildcard načinu ~ ubeži kateri koli naslednji znak, končna ~ pa se odvrže; [ in ] sta običajna znaka
• "<>text" prešteje številke, logične vrednosti, napake in prazne celice; gol "<>" prešteje neprazne celice, vključno z rezultati =""
• DSUM in druge funkcije baz podatkov obravnavajo čisto besedilo kot »se začne z«; =text in <>text primerjata cel vnos (od v2.384.64)
• Find na cele celice s lxfUseWildcards in lxfWholeCell se vrača nazaj, zato a*b ujame abcb; Find in Replace obravnavata ~ kot ubežni znak za kateri koli znak (od v2.384.60)
• Besedni vrstni red v kriterijih > in < sledi Excelovi kolaciji word sort, ločila pred črkami (od v2.384.67)

Združljivost z Excelom v formulskem pogonu so večinoma prav takšni mejni primeri, izmerjeni proti Excelu, ne uganjeni iz dokumentacije. HotXLS vrednoti COUNTIF, MATCH, XLOOKUP, DSUM in ostalo svojo knjižnico funkcij izvorno v Delphiju in C++Builderju, v klasičnem pogonu in pogonu XLSX, brez nameščenega Excela. Podrobnosti, izdaje in preizkusni prenos so na strani komponente HotXLS Delphi preglednic