HotXLS og Excel-jokertegn: COUNTIF, MATCH, DSUM og Find

HotXLS Delphi Component leser samme mønsterstreng på fire ulike måter, fordi Excel 16 gjør det. I COUNTIF og SUMIF er teksten a~b litteral så sant kriteriet ikke også inneholder * eller ?; i MATCH- og XLOOKUP-jokertegnmodus er tilden alltid en escape, så a~b finner ab; i DSUM og de andre databasefunksjonene betyr ren tekst «begynner med»; og helcelle-Find må backtracke inn i den siste *. HotXLS følger disse målte reglene siden v2.384.52, v2.384.60 og v2.384.64

Feilrapportene på dette området nevner aldri jokertegn. De sier at en servergenerert rapport teller et par rader færre enn samme fil rekalkulert i Excel, eller at et varenummer som inneholder en tilde blir funnet av én formel og ignorert av den neste. Årsaken er en matcher som antar at et mønster betyr én ting overalt. Excel fungerer ikke slik, så en motor hvis bufrede resultater må stemme med Excel, kan det heller ikke. Før v2.384.52 sendte HotXLS hvert kriterium gjennom en DOS-aktig filmaske, som fikk hverdagsmønstrene riktige og kanttilfellene stille feil

Hvorfor betyr én mønsterstreng fire ulike ting i Excel?

Én mønsterstreng betyr fire ulike ting fordi Excel arvet fire matcheregler fra fire funksjoner og aldri samlet dem. Kriteriefunksjonene (COUNTIF, SUMIF, AVERAGEIF og *IFS-familien) avgjør per kriterium om jokertegn gjelder i det hele tatt. Oppslagsfunksjonene (MATCH med match type 0, XLOOKUP med match_mode 2) bruker dem alltid. Databasefunksjonene (DSUM, DCOUNTA og venner) følger Advanced Filter, der et bart ord er et prefiks. Find-dialogen har sine egne helcelle- og delvis-moduser. Tabellen under lister hvilke celler som matcher hvert mønster mot én kolonne som inneholder a~b, ab, AB, abc, abcb, a*b og axb, med hver funksjon i sin standardmodus uten hensyn til store og små bokstaver

Mønster	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP modus 2	DSUM-kriterium	Find, hel celle, jokertegn på
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	samme som COUNTIF	hver oppføring, abc inkludert	samme som 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 aktuelt	ab, AB	ikke aktuelt

a~b-raden er den der COUNTIF og MATCH er uenige, og varenumre og håndskrevne koder inneholder tilder oftere enn noen venter. a*b-raden viser den andre fellen: abc matcher for DSUM men ikke for COUNTIF, fordi databasefunksjonen i stillhet legger til en *. DSUM-oppføringene for ab, a*b og =ab kommer rett fra Excel 16-kjøringer; DSUM-oppføringen for a~b følger av samme prefiksregel, siden den tillagte * gjør kriteriet til et jokertegnmønster der ~b er en escaped b

Når går COUNTIF inn i jokertegnmodus?

COUNTIF går inn i jokertegnmodus bare når kriterieteksten inneholder * eller ?, escaped eller ikke. Uten noe av tegnene sammenligner Excel kriteriet med hver celle som hel streng, uten hensyn til store og små bokstaver, og en tilde er bare en tilde, så COUNTIF(A1:A7,"a~b") teller cellen som litteralt holder a~b. Legg til én enkelt stjerne og betydningen flipper: i "a~b*" escaper tilden nå b, mønsteret leses som «ab etterfulgt av hva som helst», og cellen a~b telles ikke lenger. HotXLS har brukt denne regelen i begge motorer siden v2.384.52, gjennom én kriteriematcher i lxCalc delt av COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS og databasefunksjonene

Inni jokertegnmodus er escape-reglene de samme som overalt annet i Excel: ~ gjør neste tegn litteralt hva det enn er, så ~b betyr b og ~~ betyr én tilde, og en tilde helt på slutten av mønsteret droppes, så "a*~" oppfører seg som "a*". Hakeparenteser er aldri spesielle. Et kriterium "[x]" teller celler som holder de tre tegnene [x], og "[a-z]" teller ingenting på vanlige data. TXLSXWorkbook.Calculate evaluerer en formelstreng mot det aktive arket og returnerer en Variant, den raskeste måten å sjekke disse reglene mot 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 ... slik at en SUMIF-sum navngir radene sine
    end;
    Sheet.Cells[8, 1].Value := 5;                // et tall; A9 forblir tomt

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    ingen * eller ?: ren tekst, cellen a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    jokertegnmodus: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    helstreng-jokertegn, abc ekskludert
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  hver rad unntatt abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    den litterale a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    tallet 5 og tomme A9 teller
    Show('=COUNTIF(A1:A9,"<>")');       // 8    celler som ikke er tomme
  finally
    Book.Free;
  end;
end.

Hva teller «<>tekst»?

Et "<>text"-kriterium teller hver celle som ikke er den teksten, og i Excel 16 inkluderer det tall, booleans, feilverdier og tomme celler. Et bart "<>" er et helt annet spørsmål: det betyr «ikke en tom celle», så det hopper over tomme celler men teller hver verdi, inkludert den tomme teksten en formel som ="" returnerer. Den gamle HotXLS-koden fikk tekstceller riktig men ikke tall: en Variant-ulikhet fikk Delphi til å konvertere 'ab' til et tall, konverteringen reiste et unntak, en handler svelget det som «ingen match», og numeriske celler falt stille ut av tellingen. Tomcelle-siden av denne historien, inkludert hva en tom operand er lik i en vanlig sammenligning, er dekket i hvordan HotXLS håndterer sammenligningskjeder, tomme celler og SUMIF

Hvorfor finner MATCH ab når du søker etter a~b?

MATCH finner ab når du søker etter a~b fordi MATCH med match type 0 og XLOOKUP med match_mode 2 alltid er i jokertegnmodus, så tilden er en escape selv når mønsteret ikke inneholder * eller ?. Excel 16 bekrefter det på et område med to celler som holder a~b og ab: MATCH("a~b",D1:D2,0) returnerer 2, og på et område som bare holder a~b returnerer samme kall #N/A. Skal du slå opp den litterale teksten a~b, må du skrive "a~~b". I mellomtiden returnerer COUNTIF(D1:D2,"a~b") over de samme to cellene 1, og teller den andre cellen. Samme streng, samme område, motsatt celle

Det er derfor HotXLS holder de to avgjørelsene adskilt i stedet for bak ett «matcher et mønster»-inngangspunkt. Matcheren selv er delt: siden v2.384.52 kjører MATCH, XLOOKUP og kriteriefunksjonene samme backtracking-matcher, med samme escape-håndtering og samme regel for avsluttende tilde. Det som er annerledes, er porten foran den. Kriteriestien spør først «inneholder denne teksten * eller ??»; oppslagsstien spør aldri. Å slå sammen de to ville fikset én familie og knuse den andre, og begge retningene sjekkes mot Excel 16-verdier i begge motorer. Jokertegn-oppslag har også en egen forutsetning: XLOOKUP avviser jokertegn-matching kombinert med en binærsøkmodus, en regel beskrevet i HotXLS-guiden om XLOOKUP- og XMATCH-søkmoduser

Hvordan leser DSUM og databasefunksjonene et rent tekstkriterium?

DSUM og de andre databasefunksjonene leser et tekstkriterium uten ledende =, < eller > som «begynner med», med jokertegn fortsatt aktive. Det er Advanced Filter-regelen, og den skiller seg fra COUNTIF med vilje. Excel 16 målt over en Name-kolonne som holder abc, ab, xab, AB, a~b og a*b: kriteriet ab matcher abc, ab og AB; =ab matcher bare ab og AB; <>ab er en ulikhet over hele oppføringen; a*b og a? er prefiksmønstre også; >ab er en vanlig sammenligning. Før v2.384.64 matchet HotXLS ab eksakt, så en DSUM over de testdataene returnerte 10 der Excel returnerer 11

Fiksen måtte omgå betingelsesparseren, som bretter både ab og =ab inn i samme likhetsbetingelse. HotXLS inspiserer derfor den rå kriterieteksten før den stoler på den parsede betingelsen: et tekstkriterium hvis første tegn ikke er =, < eller > får en * lagt til og går gjennom jokertegn-matcheren, og alt annet beholder sin sammenligning over hele oppføringen. Én praktisk merknad når du bygger kriterieområder i kode: i XLSX-motoren lagrer tildelingen av strengen '=ab' til TXLSXCell.Value tekst, mens den klassiske TXLSWorkbook-motoren kompilerer en verdi som starter med = som en formel med mindre du setter 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';            // kriterieheader i D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // forblir tekst i XLSX-motoren
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (begynner med)
    // =ab  -> 6    ab, AB (hel oppføring)
    // <>ab -> 121  alt unntatt ab og AB
    // a*b  -> 127  a*b* matcher alle sju, abc inkludert
    // a~*  -> 32   bare den litterale a*b
  finally
    Book.Free;
  end;
end;

Én relatert forskjell overlevde prefiksfiksen og betyr noe på eldre bygg. Tekstsammenligninger som >ab brukte kodepunktrekkefølge, mens Excel legger tegnsetting foran bokstaver, så "a~b">"ab" er FALSE i Excel og var TRUE i HotXLS. Siden v2.384.67 bruker >- og <-kriteriene, sammen med vanlig tekstsammenligning og sortering, Excels ordsorteringskollasjon under gjeldende brukerlocale, og de to er enige igjen

Hvorfor manglet helcelle-Find abcb?

Helcelle-Find savnet abcb fordi matcheren stoppet på det første punktet der mønsteret var brukt opp i stedet for å backtracke inn i den siste *. Delvis-match-matcheren bak Replace returnerer så snart mønsteret er oppbrukt; helcelle-Finn gjenbrukte den og krevde deretter at treffet dekket hele cellen: a*b mot abcb stoppet etter ab, konsumerte 2 tegn av 4, og ble avvist. Siden v2.384.60 er helcelle-matcheren en egen implementasjon som behandler «mønsteret slutt, teksten ikke» som én mismatch til og prøver igjen fra den siste stjernen, så a*b matcher abcb og a?b*b matcher axbyb, slik Excel 16 Find gjør med «Match entire cell contents» avkrysset

Samme utgivelse endret tilden. Excel 16 Find, i både helcelle- og delvis-modus, behandler ~ som en escape for ethvert følgende tegn: a~b finner ab, a~~b finner a~b, og en avsluttende tilde ignoreres, så q~ oppfører seg som q. Den eldre HotXLS-matcheren anerkjente bare ~*, ~? og ~~ som escapes, så a~b fant 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 etterligner ikke det

I XLSX-motoren er søket TXLSXWorksheet.FindText med et TXLSXFindOptions-sett: lxfUseWildcards slår på *, ? og ~, lxfWholeCell krever at hele cellen matcher, og lxfMatchCase gjør sammenligningen kasussensitiv. Uten lxfUseWildcards er hvert tegn, stjernen inkludert, litteralt. Find ser bare på tekstverdier; numeriske celler hoppes over, og formelceller hoppes over med mindre lxfSearchFormulas er satt, i hvilket tilfelle formelteksten søkes. Ankeret gitt av StartRow og StartCol er inklusivt, så en Find All-løkke går én kolonne forbi hvert treff

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 avvist, abcb backtrack-er
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b er en escaped b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ er én litteral tilde

    // Delvis match, Find All: ankercellen er inkludert, så gå forbi hvert treff
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // radene 1, 2, 3 og 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Helcelle-jokertegn-erstatning omskriver bare den litterale a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Den delvise løkken finner alle fire radene, inkludert abc, fordi a*b i delvis modus bare må forekomme et sted inni cellen. FindTextIn og ReplaceTextIn tar de samme valgene pluss et vindu av FirstRow, FirstCol, LastRow, LastCol, det programatiske motstykket til å søke innenfor et utvalg. Den klassiske motoren eksponerer samme regler gjennom en overload med tre booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), pluss en matchende ReplaceText-overload, med 1-baserte rad- og kolonneresultater:

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;

Hva fikk den gamle DOS-maske-matcheren feil?

Den gamle matcheren fikk spesialtegnene feil, fordi en DOS-filmaske er et annet språk enn et Excel-jokertegn. Før v2.384.52 sendte kriteriefunksjonene og databasefunksjonene hvert mønster til MatchesMask, en filmaske-matcher i lxMasks-enheten. Syntaksen overlapper med Excels for vanlige tilfeller, noe som er grunnen til at problemet forble skjult, men den avviker der virkelige data blir interessante:

• [x] ble lest som et tegnsett, så COUNTIF(A1:A10,"[x]") telte celler som holder x i stedet for den parenteserte teksten, og "[a-z]" matchet enhver encellers bokstavcelle
• Det fantes ingen tilde-escape, så "a~*b" kunne ikke matche en litteral asterisk
• En feilformet maske, som en uavsluttet klamme, reiste et unntak som kalleren svelget som «ingen match», og gjorde en skrivefeil i et kriterium til en stille feil sum
• På oppslagssiden behandlet MATCH og XLOOKUP bare ~*, ~? og ~~ som escapes, så MATCH("a~b",…,0) fant den litterale a~b i stedet for ab

Bruker arbeidsbøkene dine bare * og ? på vanlige alfanumeriske data, var resultatene allerede riktige og endres ikke. Inneholder de klammer, tilder, kolonner med blandede typer under "<>text", eller DSUM-kriterier skrevet som bare ord, kan rekalkulering med v2.384.64 eller senere endre summer, og de nye summene er de Excel viser. Samme skille mellom hvordan Excel lagrer et kriterium og hvordan det sammenligner det, dukker opp for lagrede filtre, diskutert i HotXLS-artikkelen om BIFF8 AutoFilter DOPER-kriterier

Hurtigreferanse: Excel-jokertegnregler i HotXLS

• COUNTIF, SUMIF, AVERAGEIF og *IFS-familien bruker jokertegn bare når kriteriet inneholder * eller ?; ellers sammenligner de hele strenger uten hensyn til store og små bokstaver, og ~ er litteral (siden v2.384.52)
• MATCH med match type 0 og XLOOKUP med match_mode 2 bruker alltid jokertegn, så a~b finner ab og den litterale trenger a~~b (siden v2.384.52)
• I jokertegnmodus escaper ~ ethvert neste tegn og en avsluttende ~ droppes; [ og ] er vanlige tegn
• "<>text" teller tall, booleans, feil og tomme celler; et bart "<>" teller celler som ikke er tomme, =""-resultater inkludert
• DSUM og de andre databasefunksjonene behandler ren tekst som «begynner med»; =text og <>text sammenligner hele oppføringen (siden v2.384.64)
• Helcelle-Find med lxfUseWildcards og lxfWholeCell backtrack-er, så a*b matcher abcb; Find og Replace behandler ~ som en escape for ethvert tegn (siden v2.384.60)
• Tekstrekkefølgen i >- og <-kriterier følger Excels ordsorteringskollasjon, tegnsetting foran bokstaver (siden v2.384.67)

Excel-kompatibilitet i en formelmotor er mest kanttilfeller som disse, målt mot Excel i stedet for gjettet ut fra dokumentasjon. HotXLS evaluerer COUNTIF, MATCH, XLOOKUP, DSUM og resten av funksjonsbiblioteket nativt i Delphi og C++Builder, i både den klassiske motoren og XLSX-motoren, uten Excel installert. Detaljer, utgaver og prøveversjonen finner du på siden for HotXLS Delphi regnearkkomponent