HotXLS Excel-jokertecken: COUNTIF, MATCH, DSUM och Find

HotXLS Delphi Component läser samma mönstersträng på fyra olika sätt, för Excel 16 gör det. I COUNTIF och SUMIF är texten a~b literal om inte kriteriet också innehåller * eller ?; i MATCH och XLOOKUP jokerteckenläge är tilden alltid ett escape, så a~b hittar ab; i DSUM och de andra databasfunktionerna betyder vanlig text "börjar med"; och Sök på hel cell måste backtracka in i den sista *. HotXLS följer dessa uppmätta regler sedan v2.384.52, v2.384.60 och v2.384.64

Buggrapporterna på det här området nämner aldrig jokertecken. De säger att en servergenererad rapport räknar ett par rader färre än samma fil omräknad i Excel, eller att ett artikelnamn med en tilde hittas av en formel och ignoreras av nästa. Orsaken är en matchare som antar att ett mönster betyder en sak överallt. Excel fungerar inte så, så en motor vars cachade resultat måste stämma med Excel kan inte göra det heller. Före v2.384.52 skickade HotXLS varje kriterium genom en filmask i DOS-stil, som fick vardagsmönstren rätt och kantfallen tyst fel

Varför betyder en mönstersträng fyra olika saker i Excel?

En mönstersträng betyder fyra olika saker för att Excel ärvde fyra matchningsregler från fyra funktioner och aldrig enade dem. Kriteriefunktionerna (COUNTIF, SUMIF, AVERAGEIF och *IFS-familjen) avgör per kriterium om jokertecken gäller alls. Uppslagsfunktionerna (MATCH med matchningstyp 0, XLOOKUP med match_mode 2) tillämpar dem alltid. Databasfunktionerna (DSUM, DCOUNTA och vänner) följer Advanced Filter, där ett bart ord är ett prefix. Sök-dialogen har egna lägen för hel cell och partiell. Tabellen nedan listar vilka celler som matchar varje mönster mot en kolumn med a~b, ab, AB, abc, abcb, a*b och axb, med varje funktion i sitt standardskiftlägesokänsliga läge

Mönster	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP läge 2	DSUM-kriterium	Sök, hel cell, jokertecken på
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	samma som COUNTIF	varje post, abc inkluderad	samma som COUNTIF
a~b	bara a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	bara a*b	bara a*b	bara a*b	bara a*b
=ab	ab, AB	ej tillämpligt	ab, AB	ej tillämpligt

Raden a~b är den där COUNTIF och MATCH inte håller med, och artikelnamn och handskrivna koder innehåller tilder oftare än någon anar. Raden a*b visar den andra fällan: abc matchar för DSUM men inte för COUNTIF, för databasfunktionen lägger tyst till en *. DSUM-posterna för ab, a*b och =ab kommer rakt från Excel 16-körningar; DSUM-posten för a~b följer av samma prefixregel, eftersom den tillagda * gör kriteriet till ett jokerteckenmönster där ~b är ett escaped b

När växlar COUNTIF in i jokerteckenläge?

COUNTIF växlar in i jokerteckenläge bara när kriterietexten innehåller * eller ?, escaped eller inte. Utan något av dem jämför Excel kriteriet med varje cell som en hel sträng, skiftlägesokänsligt, och en tilde är bara en tilde, så COUNTIF(A1:A7,"a~b") räknar cellen som literal håller a~b. Lägg till en enda stjärna och betydelsen vänder: i "a~b*" escapar tilden nu b, mönstret läses som "ab följt av vad som helst", och cellen a~b räknas inte längre. HotXLS har tillämpat den regeln i båda motorer sedan v2.384.52, genom en enda kriteriematchare i lxCalc som delas av COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS och databasfunktionerna

Inuti jokerteckenläget är escapereglerna desamma som överallt annars i Excel: ~ gör nästa tecken literal vad det än är, så ~b betyder b och ~~ betyder en tilde, och en tilde allra sist i mönstret släpps, så "a*~" uppför sig som "a*". Hakparenteser är aldrig speciella. Ett kriterium "[x]" räknar celler som håller de tre tecknen [x], och "[a-z]" räknar ingenting på vanlig data. TXLSXWorkbook.Calculate utvärderar en formelsträng mot det aktiva bladet och returnerar en Variant, det snabbaste sättet att kontrollera de här reglerna mot dina egna 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-summa namnger sina rader
    end;
    Sheet.Cells[8, 1].Value := 5;                // ett tal; A9 förblir tomt

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    ingen * eller ?: vanlig text, cellen a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    jokerteckenläge: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    jokertecken över hela strängen, abc utesluten
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  varje rad utom abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    den literala a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    talet 5 och tomma A9 räknas
    Show('=COUNTIF(A1:A9,"<>")');       // 8    icke-tomma celler
  finally
    Book.Free;
  end;
end.

Vad räknar "<>text"?

Ett kriterium "<>text" räknar varje cell som inte är den texten, och i Excel 16 omfattar det tal, booleska värden, felvärden och tomma celler. Ett bara "<>" är en helt annan fråga: det betyder "inte en tom cell", så det hoppar över tomma celler men räknar varje värde, inklusive den tomma text som en formel som ="" returnerar. Den gamla HotXLS-koden fick textceller rätt men inte tal: en Variant-olikhet fick Delphi att konvertera 'ab' till ett tal, konverteringen kastade ett undantag, en hanterare svalde det som "ingen match", och numeriska celler föll tyst ur räkningen. Tomcellssidan av den här historien, inklusive vad en tom operand är lika med i en vanlig jämförelse, tas upp i hur HotXLS hanterar jämförelsekedjor, tomma celler och SUMIF

Varför hittar MATCH ab när du söker efter a~b?

MATCH hittar ab när du söker efter a~b för att MATCH med matchningstyp 0 och XLOOKUP med match_mode 2 alltid är i jokerteckenläge, så tilden är ett escape även när mönstret inte innehåller något * eller ?. Excel 16 bekräftar det på ett område med två celler som håller a~b och ab: MATCH("a~b",D1:D2,0) returnerar 2, och på ett område som bara håller a~b returnerar samma anrop #N/A. För att slå upp den literala texten a~b måste du skriva "a~~b". Under tiden returnerar COUNTIF(D1:D2,"a~b") över samma två celler 1 och räknar den andra cellen. Samma sträng, samma område, motsatt cell

Det är därför HotXLS håller de två besluten isär i stället för att lägga dem bakom en enda ingång "matcha ett mönster". Matcharen i sig är delad: sedan v2.384.52 kör MATCH, XLOOKUP och kriteriefunktionerna samma backtracking-matchare, med samma escape-hantering och samma regel för avslutande tilde. Vad som skiljer är porten framför den. Kriterievägen frågar först "innehåller den här texten * eller ??"; uppslagsvägen frågar aldrig. Att slå ihop de två skulle fixa en familj och bryta den andra, och båda riktningarna kontrolleras mot Excel 16-värden i båda motorer. Jokerteckensuppslag har också en förutsättning av egen: XLOOKUP avvisar jokerteckensmatchning kombinerad med ett binärsökläge, en regel som beskrivs i HotXLS-guiden till XLOOKUP- och XMATCH-söklägen

Hur läser DSUM och databasfunktionerna ett vanligt textkriterium?

DSUM och de andra databasfunktionerna läser ett textkriterium utan inledande =, < eller > som "börjar med", med jokertecken fortfarande aktiva. Det är Advanced Filter-regeln, och den skiljer sig från COUNTIF med flit. Excel 16 uppmättes över en Name-kolumn som håller abc, ab, xab, AB, a~b och a*b: kriteriet ab matchar abc, ab och AB; =ab matchar bara ab och AB; <>ab är en olikhet över hela posten; a*b och a? är prefixmönster också; >ab är en vanlig jämförelse. Före v2.384.64 matchade HotXLS ab exakt, så en DSUM över den testdatan returnerade 10 där Excel returnerar 11

Fixen fick kringgå villkorsparsaren, som viker ihop både ab och =ab till samma likhetsvillkor. HotXLS inspekterar därför den råa kriterietexten innan det litar på det parsade villkoret: ett textkriterium vars första tecken inte är =, < eller > får en * tillagd och går genom jokerteckensmatcharen, och allt annat behåller sin jämförelse över hela posten. En praktisk notis när du bygger kriterieområden i kod: i XLSX-motorn lagrar tilldelningen av strängen '=ab' till TXLSXCell.Value text, medan den klassiska TXLSWorkbook-motorn kompilerar ett värde som börjar med = som en formel om du inte prefixar det med en apostrof

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';            // kriterierubrik i D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // förblir text i XLSX-motorn
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (börjar med)
    // =ab  -> 6    ab, AB (hela posten)
    // <>ab -> 121  allt utom ab och AB
    // a*b  -> 127  a*b* matchar alla sju, abc inkluderad
    // a~*  -> 32   bara den literala a*b
  finally
    Book.Free;
  end;
end;

En relaterad skillnad överlevde prefixfixen och spelar roll på äldre byggen. Textjämförelser som >ab använde kodpunktsordning, medan Excel lägger skiljetecken före bokstäver, så "a~b">"ab" är FALSE i Excel och var TRUE i HotXLS. Sedan v2.384.67 använder >- och <-kriterierna, tillsammans med vanlig textjämförelse och sortering, Excels word sort-kollation under den aktuella användarens locale, och de två håller med igen

Varför missade Sök på hel cell abcb?

Sök på hel cell missade abcb för att matcharen stannade vid den första punkten där mönstret tog slut i stället för att backtracka in i den sista *. Matcharen för partiell matchning bakom Ersätt returnerar så snart mönstret är förbrukat; Sök på hel cell återanvände den och krävde sedan att matchningen skulle täcka hela cellen: a*b mot abcb stannade efter ab, konsumerade 2 av 4 tecken och avvisades. Sedan v2.384.60 är matcharen för hel cell en separat implementation som behandlar "mönstret tog slut, texten inte gjorde det" som en missmatchning till och försöker igen från den sista stjärnan, så a*b matchar abcb och a?b*b matchar axbyb, som Excel 16 Sök gör med "Matcha hela cellinnehållet" ikryssat

Samma utgåva ändrade tilden. Excel 16 Sök, i både hel-cell- och partiellt läge, behandlar ~ som ett escape för vilket tecken som följer: a~b hittar ab, a~~b hittar a~b, och en avslutande tilde ignoreras, så q~ uppför sig som q. Den äldre HotXLS-matcharen kände bara igen ~*, ~? och ~~ som escapes, så a~b hittade texten a~b. Ett Sökmönster av en enda ~ är instabilt i Excel självt, matchar vilken cell som helst som ett tomt mönster, och HotXLS imiterar inte det

I XLSX-motorn är sökningen TXLSXWorksheet.FindText med en TXLSXFindOptions-uppsättning: lxfUseWildcards slår på *, ? och ~, lxfWholeCell kräver att hela cellen matchar, och lxfMatchCase gör jämförelsen skiftlägeskänslig. Utan lxfUseWildcards är varje tecken, stjärnan inkluderad, literal. Sök tittar bara på textvärden; numeriska celler hoppas över, och formelceller hoppas över om inte lxfSearchFormulas är satt, i vilket fall formeltexten söks. Ankaret som ges av StartRow och StartCol är inklusivt, så en Find All-loop stegar en kolumn förbi varje träff

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 avvisad, abcb backtrackar
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b är ett escaped b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ är en literal tilde

    // Partiell matchning, Find All: ankarcellen räknas med, så stega förbi varje träff
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // raderna 1, 2, 3 och 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Hela-cell-ersättning med jokertecken skriver bara om den literala a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Den partiella loopen hittar alla fyra rader, inklusive abc, för i partiellt läge behöver a*b bara förekomma någonstans inuti cellen. FindTextIn och ReplaceTextIn tar samma alternativ plus ett fönster av FirstRow, FirstCol, LastRow, LastCol, den programmatiska motsvarigheten till att söka inom ett urval. Den klassiska motorn exponerar samma regler genom en överlagring med tre booleska värden, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus en motsvarande ReplaceText-överlagring, med rad- och kolumnresultat med bas 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;

Vad gjorde den gamla matcharen med DOS-mask fel?

Den gamla matcharen fick specialtecknen fel, för en DOS-filmask är ett annat språk än ett Excel-jokertecken. Före v2.384.52 skickade kriteriefunktionerna och databasfunktionerna varje mönster till MatchesMask, en filmaskmatchare i enheten lxMasks. Dess syntax överlappar med Excels för vanliga fall, vilket är varför problemet förblev gömt, men den avviker där verklig data blir intressant:

• [x] lästes som en teckenuppsättning, så COUNTIF(A1:A10,"[x]") räknade celler som höll x i stället för den hakparenteserade texten, och "[a-z]" matchade vilken enbokstavscell som helst
• Det fanns ingen tilde-escape, så "a~*b" kunde inte matcha en literal asterisk
• En felaktig mask, som en olyckad hakparentes, kastade ett undantag som anroparen svalde som "ingen match", vilket förvandlade ett stavfel i ett kriterium till en tyst felaktig summa
• På uppslagssidan behandlade MATCH och XLOOKUP bara ~*, ~? och ~~ som escapes, så MATCH("a~b",…,0) hittade den literala a~b i stället för ab

Om dina arbetsböcker bara någonsin använt * och ? på vanlig alfanumerisk data var resultaten redan rätt och ändras inte. Innehåller de hakparenteser, tilder, kolumner med blandade typer under "<>text" eller DSUM-kriterier skrivna som bara ord kan omräkning med v2.384.64 eller senare ändra totaler, och de nya totalerna är de Excel visar. Samma distinktion mellan hur Excel lagrar ett kriterium och hur det jämför det dyker upp för sparade filter, som tas upp i HotXLS-artikeln om BIFF8 AutoFilter DOPER-kriterier

Snabbreferens: Excel-jokerteckensregler i HotXLS

• COUNTIF, SUMIF, AVERAGEIF och *IFS-familjen använder jokertecken bara när kriteriet innehåller * eller ?; i annat fall jämför de hela strängar skiftlägesokänsligt och ~ är literal (sedan v2.384.52)
• MATCH med matchningstyp 0 och XLOOKUP med match_mode 2 använder alltid jokertecken, så a~b hittar ab och den literala behöver a~~b (sedan v2.384.52)
• I jokerteckenläge escapar ~ vilket nästa tecken som helst och en avslutande ~ släpps; [ och ] är vanliga tecken
• "<>text" räknar tal, booleska värden, fel och tomma celler; ett bara "<>" räknar icke-tomma celler, =""-resultat inkluderade
• DSUM och de andra databasfunktionerna behandlar vanlig text som "börjar med"; =text och <>text jämför hela posten (sedan v2.384.64)
• Sök på hel cell med lxfUseWildcards och lxfWholeCell backtrackar, så a*b matchar abcb; Sök och Ersätt behandlar ~ som ett escape för vilket tecken som helst (sedan v2.384.60)
• Textordning i >- och <-kriterier följer Excels word sort-kollation, skiljetecken före bokstäver (sedan v2.384.67)

Excel-kompatibilitet i en formelmotor är mest kantfall som de här, uppmätta mot Excel i stället för gissade från dokumentation. HotXLS utvärderar COUNTIF, MATCH, XLOOKUP, DSUM och resten av sitt funktionsbibliotek nativt i Delphi och C++Builder, i både den klassiska motorn och XLSX-motorn, utan Excel installerat. Detaljer, utgåvor och nedladdningen av testversionen finns på sidan för HotXLS Delphi spreadsheet component