HotXLS Excel Wildcards: COUNTIF, MATCH, DSUM og Find

HotXLS Delphi Component læser den samme mønsterstreng på fire forskellige måder, fordi Excel 16 gør det. I COUNTIF og SUMIF er teksten a~b literal, medmindre kriteriet også indeholder * eller ?; i MATCH og XLOOKUP-wildcard-tilstand er tilden altid et escape, så a~b finder ab; i DSUM og de andre databasefunktioner betyder ren tekst "begynder med"; og hel-celle-Find må backtracke ind i den sidste *. HotXLS følger disse målte regler siden v2.384.52, v2.384.60 og v2.384.64

Bug-rapporterne på dette område nævner aldrig wildcards. De lyder, at en servergenereret rapport tæller et par rækker færre end samme fil genberegnet i Excel, eller at et varenummer med en tilde findes af én formel og ignoreres af den næste. Årsagen er en matcher, der antager, at et mønster betyder én ting overalt. Excel arbejder ikke sådan, så en motor, hvis cachede resultater skal stemme med Excel, kan det heller ikke. Før v2.384.52 sendte HotXLS hvert kriterium gennem en DOS-agtig filmaske, som fik hverdagsmønstre rigtigt og edge cases lydløst forkert

Hvorfor betyder én mønsterstreng fire forskellige ting i Excel?

Én mønsterstreng betyder fire forskellige ting, fordi Excel arvede fire matchregler fra fire features og aldrig forenede dem. Kriteriefunktionerne (COUNTIF, SUMIF, AVERAGEIF og *IFS-familien) beslutter pr. kriterium, om wildcards overhovedet gælder. Opslagsfunktionerne (MATCH med match type 0, XLOOKUP med match_mode 2) anvender dem altid. Databasefunktionerne (DSUM, DCOUNTA og venner) følger Advanced Filter, hvor et blottet ord er et præfiks. Find-dialogen har sine egne hel-celle- og delvise tilstande. Tabellen nedenfor viser, hvilke celler der matcher hvert mønster mod én kolonne med a~b, ab, AB, abc, abcb, a*b og axb, med alle funktioner i deres default, versal-ufølsomme tilstand

Mønster	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	DSUM-kriterium	Find, hel celle, wildcards til
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	same as COUNTIF	hver post, abc inkluderet	same as COUNTIF
a~b	kun a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	kun a*b	kun a*b	kun a*b	kun a*b
=ab	ab, AB	ikke anvendelig	ab, AB	ikke anvendelig

a~b-rækken er den, hvor COUNTIF og MATCH er uenige, og varenumre og håndtastede koder indeholder tilder oftere, end nogen regner med. a*b-rækken viser den anden fælde: abc matcher for DSUM, men ikke for COUNTIF, fordi databasefunktionen lydløst tilføjer en *. DSUM-posterne for ab, a*b og =ab kommer direkte fra Excel 16-kørsler; DSUM-posten for a~b følger af samme præfiksregel, da den tilføjede * gør kriteriet til et wildcard-mønster, hvor ~b er et escaped b

Hvornår skifter COUNTIF til wildcard-tilstand?

COUNTIF skifter til wildcard-tilstand, kun når kriterieteksten indeholder * eller ?, escaped eller ej. Uden et af tegnene sammenligner Excel kriteriet med hver celle som hel streng, uden versalfølsomhed, og en tilde er bare en tilde, så COUNTIF(A1:A7,"a~b") tæller cellen, der bogstaveligt indeholder a~b. Tilføj én eneste stjerne, og betydningen vender: i "a~b*" escaper tilden nu b, mønsteret læses som "ab efterfulgt af hvad som helst", og cellen a~b tælles ikke længere. HotXLS har anvendt denne regel i begge motorer siden v2.384.52 gennem én kriteriematcher i lxCalc, delt af COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS og databasefunktionerne

Inde i wildcard-tilstanden er escapereglerna de samme som overalt andet i Excel: ~ gør det næste tegn literal, uanset hvad det er, så ~b betyder b og ~~ betyder én tilde, og en tilde helt til sidst i mønsteret droppes, så "a*~" opfører sig som "a*". Firkantede klammer er aldrig særlige. Et kriterium som "[x]" tæller celler, der indeholder de tre tegn [x], og "[a-z]" tæller intet på almindelige data. TXLSXWorkbook.Calculate evaluerer en formelstreng mod det aktive ark og returnerer en Variant, den hurtigste måde at tjekke disse regler mod dine egne data

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 ... så en SUMIF-total navngiver sine rækker
    end;
    Sheet.Cells[8, 1].Value := 5;                // et tal; A9 forbliver tom

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    ingen * eller ?: ren tekst, cellen a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard-tilstand: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard over hele strengen, abc udeladt
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  alle rækker undtagen abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    den literale a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    tallet 5 og tomme A9 tælles
    Show('=COUNTIF(A1:A9,"<>")');       // 8    celler, der ikke er tomme
  finally
    Book.Free;
  end;
end.

Hvad tæller "<>text"?

Et "<>text"-kriterium tæller hver celle, der ikke er den tekst, og i Excel 16 tæller det tal, booleaner, fejlværdier og tomme celler. Et blottet "<>" er et helt andet spørgsmål: det betyder "ikke en tom celle", så det springer tomme celler over, men tæller hver værdi, inklusive den tomme tekst, en formel som ="" returnerer. Den gamle HotXLS-kode fik tekstceller rigtigt, men ikke tal: en Variant-ulighed fik Delphi til at konvertere 'ab' til et tal, konverteringen kastede en exception, en handler slugte den som "no match", og numeriske celler faldt lydløst ud af tællingen. Denne histories tomme-celle-side, inklusive hvad en tom operand svarer til i en almindelig sammenligning, er dækket i hvordan HotXLS håndterer sammenligningskæder, tomme celler og SUMIF

Hvorfor finder MATCH ab, når du søger efter a~b?

MATCH finder ab, når du søger efter a~b, fordi MATCH med match type 0 og XLOOKUP med match_mode 2 altid er i wildcard-tilstand, så tilden er et escape, selv når mønsteret ikke indeholder * eller ?. Excel 16 bekræfter det på et område med to celler, a~b og ab: MATCH("a~b",D1:D2,0) returnerer 2, og på et område, der kun indeholder a~b, returnerer samme kald #N/A. For at slå den literale tekst a~b op, skal du skrive "a~~b". I mellemtiden returnerer COUNTIF(D1:D2,"a~b") over de samme to celler 1 og tæller den anden celle. Samme streng, samme område, modsat celle

Det er derfor, HotXLS holder de to beslutninger adskilt i stedet for bag ét "match et mønster"-indgangspunkt. Selve matcheren er delt: siden v2.384.52 kører MATCH, XLOOKUP og kriteriefunktionerne den samme backtracking-matcher, med samme escape-håndtering og samme regel om afsluttende tilde. Det, der adskiller, er porten foran den. Kriterievejen spørger først "indeholder denne tekst * eller ??"; opslagsvejen spørger aldrig. At fusionere de to ville fikse den ene familie og knække den anden, og begge retninger tjekkes mod Excel 16-værdier i begge motorer. Wildcard-opslag har også en forudsætning af egen art: XLOOKUP afviser wildcard-matching kombineret med en binær søgetilstand, en regel beskrevet i HotXLS-guiden til XLOOKUP- og XMATCH-søgetilstande

Hvordan læser DSUM og databasefunktionerne et klartekst-kriterium?

DSUM og de andre databasefunktioner læser et tekstkriterium uden foranstillet =, < eller > som "begynder med", med wildcards stadig aktive. Det er Advanced Filter-reglen, og den afviger fra COUNTIF med vilje. Excel 16 målt over en Name-kolonne med abc, ab, xab, AB, a~b og a*b: kriteriet ab matcher abc, ab og AB; =ab matcher kun ab og AB; <>ab er en hel-post-ulighed; a*b og a? er også præfiksmønstre; >ab er en almindelig sammenligning. Før v2.384.64 matchede HotXLS ab eksakt, så en DSUM over de testdata returnerede 10, hvor Excel returnerer 11

Fixet måtte arbejde rundt om betingelsesparseren, som folder både ab og =ab ind i den samme lighedsbetingelse. HotXLS undersøger derfor rå kriterietekst, før den stoler på den parsede betingelse: et tekstkriterium, hvis første tegn ikke er =, < eller >, får en * tilføjet og sendes gennem wildcard-matcheren, og alt andet beholder sin hel-postsammenligning. En praktisk bemærkning, når du bygger kriterieområder i kode: i XLSX-motoren gemmer tildeling af strengen '=ab' til TXLSXCell.Value tekst, mens den klassiske TXLSWorkbook-motor kompilerer en værdi, der starter med =, som en formel, medmindre du sætter en apostrof foran

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';            // kriterie-header i D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // forbliver tekst i XLSX-motoren
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (begynder med)
    // =ab  -> 6    ab, AB (hel post)
    // <>ab -> 121  alt undtagen ab og AB
    // a*b  -> 127  a*b* matcher alle syv, abc inkluderet
    // a~*  -> 32   kun den literale a*b
  finally
    Book.Free;
  end;
end;

Én relateret forskel overlevede præfiksfixet og tæller på ældre builds. Tekstsammenligninger som >ab brugte kodepunkt-orden, mens Excel sætter tegnsætning før bogstaver, så "a~b">"ab" er FALSE i Excel og var TRUE i HotXLS. Siden v2.384.67 bruger >- og <-kriterierne sammen med almindelig tekstsammenligning og sortering Excels word sort-collation under det aktuelle brugerlocale, og de to er igen enige

Hvorfor missede hel-celle-Find abcb?

Hel-celle-Find missede abcb, fordi matcheren stoppede ved det første punkt, hvor mønsteret var brugt op, i stedet for at backtracke ind i den sidste *. Delvis-match-matcheren bag Replace returnerer, så snart mønsteret er opbrugt; hel-celle-Find genbrugte den og krævede derefter, at matchet dækkede hele cellen: a*b mod abcb stoppede efter ab, brugte 2 tegn ud af 4 og blev afvist. Siden v2.384.60 er hel-celle-matcheren en separat implementering, der behandler "mønster slut, tekst gjorde ikke" som endnu en mismatch og prøver igen fra den sidste stjerne, så a*b matcher abcb og a?b*b matcher axbyb, som Excel 16 Find gør med "Match entire cell contents" sat

Samme udgivelse ændrede tilden. Excel 16 Find, i både hel-celle- og delvis tilstand, behandler ~ som et escape for ethvert følgende tegn: a~b finder ab, a~~b finder a~b, og en afsluttende tilde ignoreres, så q~ opfører sig som q. Den ældre HotXLS-matcher genkendte kun ~*, ~? og ~~ som escapes, så a~b fandt teksten a~b. Et Find-mønster på en enkelt ~ er ustabilt i Excel selv og matcher enhver celle som et tomt mønster, og HotXLS efterligner det ikke

I XLSX-motoren er søgningen TXLSXWorksheet.FindText med et TXLSXFindOptions-sæt: lxfUseWildcards tænder for *, ? og ~, lxfWholeCell kræver, at hele cellen matcher, og lxfMatchCase gør sammenligningen versalfølsom. Uden lxfUseWildcards er hvert tegn, stjernen inkluderet, literal. Find kigger kun på tekstværdier; numeriske celler springes over, og formelceller springes over, medmindre lxfSearchFormulas er sat, i hvilket tilfælde formelteksten søges. Ankeret givet af StartRow og StartCol er inklusivt, så en Find All-løkke går én kolonne forbi hvert 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 afvist, abcb backtracker
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b er et escaped b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ er én literal tilde

    // Delvis match, Find All: anker-cellen er inkluderet, så gå forbi hvert hit
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // rækkerne 1, 2, 3 og 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Hel-celle-wildcard-erstatning omskriver kun den literale a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Den delvise løkke finder alle fire rækker, inklusive abc, fordi a*b i delvis tilstand kun skal optræde et sted inde i cellen. FindTextIn og ReplaceTextIn tager de samme options plus et FirstRow-, FirstCol-, LastRow-, LastCol-vindue, den programmatiske ækvivalent til at søge inden for en markering. Den klassiske motor eksponerer de samme regler gennem en overload med tre booleaner, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus en tilsvarende ReplaceText-overload, med række- og kolonneresultater fra 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;

Hvad tog den gamle DOS-maske-matcher forkert?

Den gamle matcher tog de særlige tegn forkert, fordi en DOS-filmaske er et andet sprog end et Excel-wildcard. Før v2.384.52 sendte kriteriefunktionerne og databasefunktionerne hvert mønster til MatchesMask, en filmaske-matcher i lxMasks-unitet. Dens syntaks overlapper med Excels for almindelige tilfælde, hvilket er grunden til, at problemet forblev skjult, men den afviger dér, hvor rigtige data bliver interessante:

• [x] blev læst som et tegnsæt, så COUNTIF(A1:A10,"[x]") tællede celler med x i stedet for den klamrede tekst, og "[a-z]" matchede enhver encellet-bogstav-celle
• Der fandtes ingen tilde-escape, så "a~*b" kunne ikke matche en literal stjerne
• En misdannet maske, som en uafsluttet klamme, kastede en exception, som kalderen slugte som "no match", og forvandlede en tastefejl i et kriterium til et lydløst forkert total
• På opslagssiden behandlede MATCH og XLOOKUP kun ~*, ~? og ~~ som escapes, så MATCH("a~b",…,0) fandt den literale a~b i stedet for ab

Brugte dine workbooks kun * og ? på almindelige alfanumeriske data, var resultaterne allerede rigtige og ændres ikke. Indeholder de klammer, tilder, blandet-type-kolonner under "<>text" eller DSUM-kriterier skrevet som bare ord, kan genberegning med v2.384.64 eller senere ændre totaler, og de nye totaler er dem, Excel viser. Samme skel mellem, hvordan Excel gemmer et kriterium, og hvordan det sammenligner det, dukker op for gemte filtre, drøftet i HotXLS-artiklen om BIFF8 AutoFilter DOPER-kriterier

Hurtig reference: Excel-wildcard-regler i HotXLS

• COUNTIF, SUMIF, AVERAGEIF og *IFS-familien bruger wildcards kun, når kriteriet indeholder * eller ?; ellers sammenligner de hele strenge uden versalfølsomhed, og ~ er literal (siden v2.384.52)
• MATCH med match type 0 og XLOOKUP med match_mode 2 bruger altid wildcards, så a~b finder ab, og den literale kræver a~~b (siden v2.384.52)
• I wildcard-tilstand escaper ~ ethvert næste tegn, og en afsluttende ~ droppes; [ og ] er almindelige tegn
• "<>text" tæller tal, booleaner, fejl og tomme celler; et blottet "<>" tæller celler, der ikke er tomme, =""-resultater inkluderet
• DSUM og de andre databasefunktioner behandler ren tekst som "begynder med"; =text og <>text sammenligner hele posten (siden v2.384.64)
• Hel-celle-Find med lxfUseWildcards og lxfWholeCell backtracker, så a*b matcher abcb; Find og Replace behandler ~ som et escape for ethvert tegn (siden v2.384.60)
• Tekstordenen i >- og <-kriterier følger Excels word sort-collation, tegnsætning før bogstaver (siden v2.384.67)

Excel-kompatibilitet i en formelmotor er for det meste edge cases som disse, målt mod Excel frem for gættet ud fra dokumentation. HotXLS evaluerer COUNTIF, MATCH, XLOOKUP, DSUM og resten af sit funktionsbibliotek nativt i Delphi og C++Builder, i både den klassiske motor og XLSX-motoren, uden Excel installeret. Detaljer, udgaver og trial-download står på siden om HotXLS Delphi spreadsheet-komponenten