HotXLS Excel-wildcards: COUNTIF, MATCH, DSUM en Find

De HotXLS Delphi Component leest dezelfde pattern-string op vier verschillende manieren, omdat Excel 16 dat ook doet. In COUNTIF en SUMIF is de tekst a~b een letterlijke waarde tenzij het criterium ook * of ? bevat; in wildcardmodus van MATCH en XLOOKUP is de tilde altijd een escape, dus a~b vindt ab; in DSUM en de andere databasefuncties betekent kale tekst begint met; en Find op hele cellen moet terug kunnen springen naar de laatste *. HotXLS volgt deze gemeten regels sinds v2.384.52, v2.384.60 en v2.384.64

De bugrapporten in dit gebied noemen nooit wildcards. Ze zeggen dat een door een server gegenereerd rapport een paar rijen minder telt dan hetzelfde bestand herberekend in Excel, of dat een onderdeelnummer met een tilde door de ene formule wordt gevonden en door de volgende wordt genegeerd. De oorzaak is een matcher die ervan uitgaat dat een pattern overal hetzelfde betekent. Excel werkt niet zo, dus een engine wiens gecachte resultaten met Excel moeten overeenkomen kan dat ook niet. Vóór v2.384.52 stuurde HotXLS elk criterium door een DOS-achtig bestandsmasker, wat alledaagse patronen goed deed en de randgevallen stilletjes fout

Waarom betekent één pattern-string vier verschillende dingen in Excel?

Eén pattern-string betekent vier verschillende dingen omdat Excel vier matchregels van vier features heeft geërfd en ze nooit heeft geharmoniseerd. De criteriumfuncties (COUNTIF, SUMIF, AVERAGEIF en de familie *IFS) beslissen per criterium of wildcards überhaupt gelden. De lookupfuncties (MATCH met match type 0, XLOOKUP met match_mode 2) passen ze altijd toe. De databasefuncties (DSUM, DCOUNTA en compagnie) volgen het Advanced Filter, waar een kaal woord een prefix is. Het Find-dialoogvenster heeft zijn eigen modi voor hele cellen en gedeeltelijke matches. De tabel hieronder zet uiteen welke cellen bij elk patroon matchen tegen één kolom met a~b, ab, AB, abc, abcb, a*b en axb, met elke functie in zijn default hoofdletterongevoelige modus

Patroon	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP modus 2	DSUM-criterium	Find, hele cel, wildcards aan
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	zelfde als COUNTIF	elke entry, abc inbegrepen	zelfde als COUNTIF
a~b	alleen a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	alleen a*b	alleen a*b	alleen a*b	alleen a*b
=ab	ab, AB	niet van toepassing	ab, AB	niet van toepassing

De rij a~b is degene waar COUNTIF en MATCH het oneens zijn, en onderdeelnummers en handgetypte codes bevatten vaker tildes dan iedereen verwacht. De rij a*b toont de andere val: abc matcht voor DSUM maar niet voor COUNTIF, omdat de databasefunctie stilletjes een * toevoegt. De DSUM-entries voor ab, a*b en =ab komen rechtstreeks uit runs in Excel 16; de DSUM-entry voor a~b volgt uit dezelfde prefixregel, want de toegevoegde * maakt van het criterium een wildcardpatroon waarin ~b een ge-escapete b is

Wanneer schakelt COUNTIF over naar wildcardmodus?

COUNTIF schakelt alleen naar wildcardmodus wanneer de criteriumtekst * of ? bevat, ge-escapet of niet. Zonder een van beide tekens vergelijkt Excel het criterium met elke cel als hele string, hoofdletterongevoelig, en een tilde is gewoon een tilde, dus COUNTIF(A1:A7,"a~b") telt de cel die letterlijk a~b bevat. Voeg één sterretje toe en de betekenis kantelt: in "a~b*" escapet de tilde nu de b, leest het patroon als ab gevolgd door wat dan ook, en de cel a~b wordt niet langer meegeteld. HotXLS past deze regel sinds v2.384.52 in beide engines toe, via één criteria-matcher in lxCalc die wordt gedeeld door COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS en de databasefuncties

Binnen de wildcardmodus zijn de escaperegels dezelfde als overal elders in Excel: ~ maakt het volgende teken letterlijk wat het ook is, dus ~b betekent b en ~~ betekent één tilde, en een tilde helemaal aan het eind van het patroon valt weg, dus "a*~" gedraagt zich als "a*". Vierkante haken zijn nooit bijzonder. Een criterium "[x]" telt cellen die de drie tekens [x] bevatten, en "[a-z]" telt niets op gewone data. TXLSXWorkbook.Calculate evalueert een formulestring tegen het actieve werkblad en geeft een Variant terug, de snelste manier om deze regels tegen uw eigen data te checken

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 ... zodat een SUMIF-totaal zijn rijen noemt
    end;
    Sheet.Cells[8, 1].Value := 5;                // een getal; A9 blijft leeg

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    geen * of ?: kale tekst, de cel a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcardmodus: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard over de hele string, abc uitgesloten
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  elke rij behalve abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    de letterlijke a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    het getal 5 en de lege A9 tellen mee
    Show('=COUNTIF(A1:A9,"<>")');       // 8    cellen die niet leeg zijn
  finally
    Book.Free;
  end;
end.

Wat telt "<>text"?

Een criterium "<>text" telt elke cel die niet die tekst is, en in Excel 16 omvat dat getallen, booleans, foutwaarden en lege cellen. Een kale "<>" is een heel andere vraag: hij betekent geen lege cel, dus hij slaat lege cellen over maar telt elke waarde, inclusief de lege tekst die een formule als ="" teruggeeft. De oude HotXLS-code had tekstcellen goed maar getallen niet: een Variant-ongelijkheid liet Delphi 'ab' naar een getal converteren, de conversie gooide een exception, een handler slokte die op als geen match, en numerieke cellen vielen stilletjes uit de telling. De kant van lege cellen in dit verhaal, inclusief waaraan een lege operand gelijk is in een gewone vergelijking, staat bij hoe HotXLS vergelijkingsketens, lege cellen en SUMIF behandelt

Waarom vindt MATCH ab wanneer u naar a~b zoekt?

MATCH vindt ab wanneer u naar a~b zoekt omdat MATCH met match type 0 en XLOOKUP met match_mode 2 altijd in wildcardmodus staan, dus de tilde is een escape ook als het patroon geen * of ? bevat. Excel 16 bevestigt het op een bereik van twee cellen met a~b en ab: MATCH("a~b",D1:D2,0) geeft 2 terug, en op een bereik dat alleen a~b bevat geeft dezelfde aanroep #N/A terug. Om de letterlijke tekst a~b op te zoeken moet u "a~~b" schrijven. Ondertussen geeft COUNTIF(D1:D2,"a~b") over dezelfde twee cellen 1 terug en telt dus de andere cel. Dezelfde string, hetzelfde bereik, de andere cel

Daarom houdt HotXLS de twee beslissingen uit elkaar in plaats van ze achter één entrypoint voor patroonmatching te zetten. De matcher zelf wordt gedeeld: sinds v2.384.52 draaien MATCH, XLOOKUP en de criteriumfuncties dezelfde backtracking-matcher, met dezelfde escape-afhandeling en dezelfde regel voor een tilde aan het eind. Wat verschilt is de poort ervoor. Het criteriumpad vraagt eerst bevat deze tekst een * of ?; het lookuppad vraagt het nooit. De twee samenvoegen zou de ene familie fixen en de andere breken, en beide richtingen worden in beide engines gecontroleerd tegen waarden uit Excel 16. Wildcard-lookups hebben bovendien een eigen voorwaarde: XLOOKUP wijst wildcard-matching in combinatie met een binary search mode af, een regel die staat beschreven in de HotXLS-gids over de zoekmodi van XLOOKUP en XMATCH

Hoe lezen DSUM en de databasefuncties een kaal tekstcriterium?

DSUM en de andere databasefuncties lezen een tekstcriterium zonder voorafgaande =, < of > als begint met, met wildcards nog steeds actief. Dat is de regel van het Advanced Filter, en hij wijkt met opzet af van COUNTIF. Gemeten in Excel 16 over een Name-kolom met abc, ab, xab, AB, a~b en a*b: het criterium ab matcht abc, ab en AB; =ab matcht alleen ab en AB; <>ab is een ongelijkheid over de hele entry; a*b en a? zijn ook prefixpatronen; >ab is een gewone vergelijking. Vóór v2.384.64 matchte HotXLS ab exact, dus een DSUM over die testdata gaf 10 terug waar Excel 11 teruggeeft

De fix moest om de conditionparser heen werken, die zowel ab als =ab samenvoegt tot dezelfde gelijkheidsvoorwaarde. HotXLS bekijkt daarom de rauwe criteriumtekst voordat hij de geparsede voorwaarde vertrouwt: een tekstcriterium waarvan het eerste teken niet =, < of > is, krijgt een * achteraan en gaat door de wildcard-matcher, en al het andere houdt zijn vergelijking over de hele entry. Een praktische opmerking als u criteriumbereiken in code bouwt: in de XLSX-engine slaat toewijzing van de string '=ab' aan TXLSXCell.Value tekst op, terwijl de klassieke TXLSWorkbook-engine een waarde die met = begint als formule compileert tenzij u er een apostrof voor zet

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';            // criteriumkop in D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // blijft tekst in de XLSX-engine
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (begint met)
    // =ab  -> 6    ab, AB (hele entry)
    // <>ab -> 121  alles behalve ab en AB
    // a*b  -> 127  a*b* matcht alle zeven, abc inbegrepen
    // a~*  -> 32   alleen de letterlijke a*b
  finally
    Book.Free;
  end;
end;

Eén verwant verschil overleefde de prefixfix en doet ertoe op oudere builds. Tekstvergelijkingen zoals >ab gebruikten de code-point-volgorde, terwijl Excel leestekens vóór letters zet, dus "a~b">"ab" is FALSE in Excel en was TRUE in HotXLS. Sinds v2.384.67 gebruiken de criteria > en <, samen met gewone tekstvergelijking en sortering, de woord-sorteringscollation van Excel onder de huidige gebruikerslocale, en de twee zijn het weer eens

Waarom sloeg Find op hele cellen abcb over?

Find op hele cellen sloeg abcb over omdat de matcher stopte op het eerste punt waar het patroon op was in plaats van terug te springen naar de laatste *. De matcher voor gedeeltelijke matches achter Replace keert terug zodra het patroon is uitgeput; Find op hele cellen hergebruikte hem en eiste daarna dat de match de hele cel bestreek: a*b tegen abcb stopte na ab, had 2 van de 4 tekens verbruikt en werd afgewezen. Sinds v2.384.60 is de matcher voor hele cellen een aparte implementatie die patroon op, tekst niet als één mismatch meer telt en het opnieuw probeert vanaf het laatste sterretje, dus a*b matcht abcb en a?b*b matcht axbyb, zoals Find in Excel 16 doet met Match entire cell contents aangevinkt

Dezelfde release veranderde de tilde. Find in Excel 16 behandelt, in zowel de modus hele cel als gedeeltelijk, ~ als een escape voor elk volgend teken: a~b vindt ab, a~~b vindt a~b, en een tilde aan het eind wordt genegeerd, dus q~ gedraagt zich als q. De oudere HotXLS-matcher herkende alleen ~*, ~? en ~~ als escapes, dus a~b vond de tekst a~b. Een Find-patroon van één enkele ~ is instabiel in Excel zelf, hij matcht elke cel alsof het patroon leeg is, en HotXLS imiteert dat niet

In de XLSX-engine is de zoekopdracht TXLSXWorksheet.FindText met een set TXLSXFindOptions: lxfUseWildcards zet *, ? en ~ aan, lxfWholeCell eist dat de hele cel matcht, en lxfMatchCase maakt de vergelijking hoofdlettergevoelig. Zonder lxfUseWildcards is elk teken, sterretje inbegrepen, letterlijk. Find kijkt alleen naar tekstwaarden; numerieke cellen worden overgeslagen, en formulecellen ook, tenzij lxfSearchFormulas is gezet, in welk geval de formulatext wordt doorzocht. Het anker dat StartRow en StartCol geven is inclusief, dus een Find All-lus stapt één kolom voorbij elke 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 afgewezen, abcb backtrackt
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b is een ge-escapete b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ is één letterlijke tilde

    // Gedeeltelijke match, Find All: de ankercel is inbegrepen, dus stap voorbij elke hit
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // rijen 1, 2, 3 en 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Wildcard-vervanging op hele cellen herschrijft alleen de letterlijke a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

De gedeeltelijke lus vindt alle vier de rijen, abc inbegrepen, want in de gedeeltelijke modus hoeft a*b alleen ergens in de cel voor te komen. FindTextIn en ReplaceTextIn nemen dezelfde opties plus een venster FirstRow, FirstCol, LastRow, LastCol, het programmatische equivalent van zoeken binnen een selectie. De klassieke engine stelt dezelfde regels bloot via een overload met drie booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus een bijpassende ReplaceText-overload, met resultaten in rijen en kolommen vanaf 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;

Wat deed de oude DOS-masker-matcher verkeerd?

De oude matcher had de speciale tekens verkeerd, want een DOS-bestandsmasker is een andere taal dan een Excel-wildcard. Vóór v2.384.52 gaven de criteriumfuncties en de databasefuncties elk patroon door aan MatchesMask, een bestandsmasker-matcher in de unit lxMasks. Diens syntaxis overlapt voor gewone gevallen met die van Excel, wat de reden is dat het probleem verborgen bleef, maar hij wijkt af waar echte data interessant wordt:

• [x] werd gelezen als een tekenset, dus COUNTIF(A1:A10,"[x]") telde cellen met x in plaats van de tekst met haken, en "[a-z]" matchte elke cel van één letter
• Er was geen tilde-escape, dus "a~*b" kon geen letterlijk sterretje matchen
• Een misvormd masker, zoals een niet-gesloten haak, gooide een exception die de aanroeper opslokte als geen match, waarmee een typefout in een criterium een stilletjes fout totaal werd
• Aan de lookupkant behandelden MATCH en XLOOKUP alleen ~*, ~? en ~~ als escapes, dus MATCH("a~b",…,0) vond de letterlijke a~b in plaats van ab

Gebruikten uw workbooks alleen ooit * en ? op kale alfanumerieke data, dan waren de resultaten al goed en veranderen ze niet. Bevatten ze haken, tildes, kolommen met gemengde typen onder "<>text", of als kale woorden geschreven DSUM-criteria, dan kan herberekenen met v2.384.64 of later totalen veranderen, en de nieuwe totalen zijn degene die Excel toont. Hetzelfde onderscheid tussen hoe Excel een criterium opslaat en hoe hij het vergelijkt speelt ook bij opgeslagen filters, behandeld in het HotXLS-artikel over BIFF8 AutoFilter DOPER-criteria

Snelnaslag: de Excel-wildcardregels in HotXLS

• COUNTIF, SUMIF, AVERAGEIF en de familie *IFS gebruiken wildcards alleen wanneer het criterium * of ? bevat; anders vergelijken ze hele strings hoofdletterongevoelig en is ~ letterlijk (sinds v2.384.52)
• MATCH met match type 0 en XLOOKUP met match_mode 2 gebruiken altijd wildcards, dus a~b vindt ab en de letterlijke waarde heeft a~~b nodig (sinds v2.384.52)
• In wildcardmodus escapet ~ elk volgend teken en valt een ~ aan het eind weg; [ en ] zijn gewone tekens
• "<>text" telt getallen, booleans, fouten en lege cellen; een kale "<>" telt cellen die niet leeg zijn, resultaten ="" inbegrepen
• DSUM en de andere databasefuncties behandelen kale tekst als begint met; =text en <>text vergelijken de hele entry (sinds v2.384.64)
• Find op hele cellen met lxfUseWildcards en lxfWholeCell backtrackt, dus a*b matcht abcb; Find en Replace behandelen ~ als een escape voor elk teken (sinds v2.384.60)
• De tekstvolgorde in de criteria > en < volgt de woord-sorteringscollation van Excel, leestekens vóór letters (sinds v2.384.67)

Excel-compatibiliteit in een formule-engine is grotendeels randgevallen zoals deze, gemeten tegen Excel in plaats van geraden uit documentatie. HotXLS evalueert COUNTIF, MATCH, XLOOKUP, DSUM en de rest van zijn functiebibliotheek native in Delphi en C++Builder, in zowel de klassieke engine als de XLSX-engine, zonder Excel geïnstalleerd. Details, edities en de proefdownload staan op de HotXLS Delphi spreadsheet component-pagina