Technisch artikel

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

Met a~b in D1 en ab in D2 geeft =COUNTIF(D1:D2,"a~b") 1 terug, en de cel die hij telt is D1. =MATCH("a~b",D1:D2,0) geeft 2 terug, wat naar D2 wijst. Dezelfde pattern-string, dezelfde twee cellen, en Excel kiest een andere cel afhankelijk van welke functie het patroon leest. Een spreadsheet-engine die wil dat zijn gecachte resultaten met Excel overeenkomen moet die splitsing reproduceren, samen met twee regels erbij: databasefuncties zoals DSUM lezen kale tekst als begint met, en Find op hele cellen moet terug kunnen springen naar de laatste *

Rapporten die dit tegenkomen noemen zelden wildcards. Ze zeggen dat een door een server gegenereerd werkboek twee rijen minder telt dan hetzelfde bestand herberekend op een desktop, of dat een onderdeelnummer met een tilde door de ene formule wordt gevonden en door de volgende wordt genegeerd. Elk patroon door een DOS-achtig bestandsmasker halen doet de alledaagse gevallen goed, dus de afwijking verschijnt pas zodra echte productcodes ~ of [ gaan bevatten

Eén pattern-string, vier Excel-regelsets

Excel heeft zijn matchregels nooit geharmoniseerd; elke functie bracht zijn eigen mee. De criteriumfuncties (COUNTIF, SUMIF, AVERAGEIF en de *IFS-familie) 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 de rest) erven de Advanced Filter-regel, waarin een kaal woord een prefix is. Het Find-dialoogvenster heeft eigen modi voor hele cellen en gedeeltelijke matches. HotXLS Delphi Component evalueert elke familie volgens zijn eigen regel, in zowel de klassieke engine als de XLSX-engine. De tabel laat zien welke cellen elk patroon matchen tegen één kolom met a~b, ab, AB, abc, abcb, a*b en axb, elke functie in zijn standaard hoofdletterongevoelige modus

PatroonCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP modus 2DSUM-criteriumFind, hele cel, wildcards aan
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbzelfde als COUNTIFelke entry, abc inbegrepenzelfde als COUNTIF
a~balleen a~bab, ABab, AB, abc, abcbab, AB
a~*balleen a*balleen a*balleen a*balleen a*b
=abab, ABniet van toepassingab, ABniet van toepassing

Twee rijen verdienen een tweede blik. In de rij a~b komen COUNTIF en MATCH op verschillende cellen uit, en handgetypte codes bevatten vaker tildes dan iedereen van tevoren plant. In de rij a*b matcht abc voor DSUM maar niet voor COUNTIF, omdat de databasefunctie stilletjes een * achter het criterium plakt. De DSUM-entry voor a~b volgt uit diezelfde toegeplakte ster: zodra hij erstaat is het criterium een wildcardpatroon, en ~b is simpelweg een ge-escapete b

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 wordt het criterium met elke cel als hele string vergeleken, hoofdletterongevoelig, en is een tilde een gewoon teken, dus COUNTIF(A1:A7,"a~b") telt de cel die letterlijk a~b bevat. Eén sterretje kantelt de betekenis. In "a~b*" escapet de tilde nu de b, leest het patroon als ab gevolgd door wat dan ook, en valt de cel a~b uit de telling. HotXLS past deze poort in COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS en de databasefuncties op dezelfde manier toe

HotXLS-diagram van de wildcard-poort: COUNTIF en SUMIF passen wildcards alleen toe wanneer het criterium een sterretje of vraagteken bevat, dus a~b telt de letterlijke cel en geeft 1 terug, terwijl MATCH type 0 en XLOOKUP modus 2 altijd in wildcardmodus staan, dus a~b vindt ab op positie 2
De poort is het hele verschil: COUNTIF vraagt eerst om een sterretje of vraagteken voordat hij een tilde als escape behandelt, MATCH vraagt nooit, dus één pattern-string telt de ene cel en vindt de andere

Binnen de wildcardmodus is de escaperegel kort. ~ 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. De snelste manier om dit alles tegen uw eigen data te checken is TXLSXWorkbook.Calculate, wat een formulestring tegen het actieve werkblad evalueert en een Variant teruggeeft; een gloednieuw werkboek maakt het eerst toegevoegde werkblad het actieve

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');            // eerste werkblad, dus het actieve
    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 "<>text" eigenlijk telt

Een criterium "<>text" telt elke cel die niet die tekst is, en in Excel omvat dat getallen, booleans, foutwaarden en lege cellen. Een kale "<>" stelt een heel andere vraag: geen lege cel. Hij slaat lege cellen over maar telt elke waarde, inclusief de lege tekst die een formule als ="" teruggeeft. De Delphi-valkuil zit in de Variant-vergelijking. Vergelijk 'ab' met een numerieke cel en Delphi probeert 'ab' naar een getal te converteren, de conversie gooit een exception, en een handler die die opslokt als geen match haalt numerieke cellen uit de telling. HotXLS telt ze mee, dus de laatste twee regels van het voorbeeld geven 7 en 8 terug. 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 met match type 0 en XLOOKUP met match_mode 2 staan altijd in wildcardmodus, dus de tilde is een escape ook als het patroon geen * of ? bevat. Op het bereik van twee cellen uit de opening geeft MATCH("a~b",D1:D2,0) 2 terug. Op een bereik dat alleen a~b bevat geeft dezelfde aanroep #N/A terug. De letterlijke tekst a~b opzoeken doet u met "a~~b". Wanneer lookup-keys uit gebruikersinvoer of een extern systeem komen en een tilde kunnen bevatten, verdubbel elke tilde voordat u de formule bouwt; die ene StringReplace is goedkoper dan later een ontbrekende rij uitleggen

HotXLS houdt deze twee beslissingen uit elkaar. MATCH, XLOOKUP en de criteriumfuncties delen één 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 of de tekst een * of ? bevat. Het lookuppad vraagt het nooit. Eén entrypoint voor patroonmatching dat door beide wordt gebruikt zou de ene familie goed doen en de andere fout, welke kant de poort ook op stond. Wildcard-lookups hebben één voorwaarde extra: XLOOKUP geeft #VALUE! terug wanneer match_mode 2 wordt gecombineerd met een binary search mode, wat staat beschreven in de HotXLS-gids over de zoekmodi van XLOOKUP en XMATCH

DSUM-criteria: kale tekst betekent "begint met"

DSUM en de andere databasefuncties lezen een tekstcriterium zonder voorafgaande =, < of > als prefix, met wildcards nog steeds actief. Dat is de regel van het Advanced Filter, en hij wijkt met opzet af van COUNTIF. Over een Name-kolom met abc, ab, xab en AB matcht het criterium ab abc, ab en AB, terwijl =ab alleen ab en AB matcht. <>ab is een ongelijkheid over de hele entry, a*b en a? zijn ook prefixpatronen, en >ab is een gewone tekstvergelijking. xab matcht nooit ab; de regel is begint met, niet bevat

Het lastige voor een implementatie is dat een criteriumparser die de operator stript ab en =ab als dezelfde gelijkheidstest ziet. Alleen de rauwe criteriumtekst houdt ze uit elkaar. HotXLS kijkt daarom naar die rauwe tekst voordat hij de geparsede voorwaarde vertrouwt: een tekstcriterium waarvan het eerste teken geen =, < of > is, krijgt een * achteraan en gaat door de wildcard-matcher, en al het reste houdt zijn vergelijking over de hele entry

HotXLS-diagram van de DSUM-criteriumregel: een kaal tekstcriterium krijgt een sterretje achteraan en matcht als prefix, dus ab bereikt ab, AB, abc en abcb, =ab vergelijkt de hele entry, <>ab sluit beide uit, en een tilde-sterretje overleeft als de letterlijke a*b, met de bijbehorende DSUM-totalen 30, 6, 121 en 32
Excel heeft de Advanced Filter-regel voor databasefuncties geërfd: kale tekst betekent begint met, terwijl een voorafgaand gelijkteken of niet-gelijkteken de hele entry vergelijkt; HotXLS bekijkt de rauwe criteriumtekst voordat hij de geparsede voorwaarde vertrouwt

Een criteriumbereik in code bouwen kent één enginespecifieke valkuil. In de XLSX-engine slaat het toewijzen van de string '=ab' aan TXLSXCell.Value tekst op, en dat is precies wat een criteriumcel nodig heeft. De klassieke TXLSWorkbook-engine compileert een stringwaarde die met = begint als formule, tenzij u er een apostrof voor zet. Voor criteriumbereiken die tijdens runtime worden gebouwd is de XLSX-engine de keuze met de minste kans op fouten. De lus hieronder wisselt vijf criteria in D2 om en herrekent telkens dezelfde DSUM

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;

Sorteercriteria zoals >ab volgen nog weer een andere regel. Een kale code-point-vergelijking maakt "a~b">"ab" TRUE, want ~ staat in Unicode boven elke letter. Excel zet leestekens vóór letters en geeft FALSE terug. Onder Windows evalueert HotXLS de tekstcriteria > en <, gewone tekstvergelijking en sortering met de woord-sorteringscollation van het systeem voor de huidige gebruikerslocale, zodat alle drie met elkaar én met Excel overeenkomen

Find op hele cellen in Delphi: terug naar het laatste sterretje

De verleidelijke shortcut voor Find op hele cellen is de gedeeltelijke matcher achter Replace te hergebruiken en daarna te eisen dat de match de hele cel bestrijkt. Dat faalt op gewone data. Een gedeeltelijke matcher keert terug zodra het patroon is uitgeput, dus a*b tegen abcb stopt na ab, heeft 2 van de 4 tekens verbruikt, en de cel wordt afgewezen. HotXLS draait Find op hele cellen door een aparte matcher die patroon op, tekst niet als één mismatch meer telt en het opnieuw probeert vanaf het laatste sterretje. Daarmee matcht a*b abcb en matcht a?b*b axbyb, zoals Excel Find doet met Match entire cell contents aangevinkt

HotXLS-diagram van het backtracken van wildcard-Find op hele cellen: het patroon a*b verbruikt a en b in de cel abcb en een matcher die stopt zodra het patroon op is wijst de cel af, terwijl de HotXLS-matcher patroon-op-met-tekst-over als één extra mismatch telt en het opnieuw probeert vanaf het laatste sterretje totdat de hele cel matcht
Een match op de hele cel is niet klaar wanneer het patroon op is; overgebleven tekst als één extra mismatch tellen stuurt de matcher terug naar het laatste sterretje, en zo bereikt a*b abcb zoals Excel Find

Excel Find behandelt, in zowel de modus hele cel als de gedeeltelijke modus, ~ 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. Een matcher die alleen ~*, ~? en ~~ kent zou a~b de tekst a~b laten vinden, juist de tegenovergestelde cel. HotXLS escapet elk teken na een tilde, in Find en Replace net als in formules

In de XLSX-engine is de aanroep TXLSXWorksheet.FindText met een set TXLSXFindOptions. lxfUseWildcards zet *, ? en ~ aan; lxfWholeCell eist dat de hele cel matcht; 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. lxfBackward en lxfByColumns veranderen de scanvolgorde. Het anker dat StartRow en StartCol geven is inclusief, dus een Find All-lus moet één kolom voorbij elke hit stappen

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 meldt 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, de programmatische versie van zoeken binnen een selectie; geef 0 door voor een grens om die open te laten. De klassieke engine stelt dezelfde matchregels bloot via een overload met drie booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus een bijpassende ReplaceText-overload. Zijn rij- en kolomresultaten zijn 1-based, en zoals de XLSX-variant slaat hij formulecellen en niet-tekstwaarden over

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;

Waarom een DOS-bestandsmasker de verkeerde matcher voor Excel-criteria is

Een bestandsmasker-matcher zoals MatchesMask spreekt een andere taal dan Excel-wildcards, ook al overlappen de twee op * en ?. Juist die overlap is waarom hij wordt gepakt, en waarom zijn fouten verborgen blijven tot de data interessant wordt:

  • [x] wordt gelezen als een tekenset, dus COUNTIF(A1:A10,"[x]") zou cellen tellen die x bevatten in plaats van de tekst met haken, en "[a-z]" zou elke cel met één letter matchen
  • Er is geen tilde-escape, dus "a~*b" kan geen letterlijk sterretje matchen
  • Een misvormd masker, zoals een niet-gesloten haak, gooit een exception die een aanroeper makkelijk als geen match opslokt, waarmee een typo in een criterium een verkeerd totaal wordt
  • Aan de lookupkant schiet een escapeset die beperkt is tot ~*, ~? en ~~ ook tekort: MATCH("a~b",…,0) moet ab vinden, niet de letterlijke a~b

Gebruiken uw workbooks alleen ooit * en ? op kale alfanumerieke data, dan zijn alle matchers het eens en doet het hele verhaal er niet toe. Haken, tildes, kolommen met gemengde typen onder "<>text" en als kale woorden geschreven DSUM-criteria zijn de plekken waar de totalen van een eenvoudigere matcher van die van Excel wegdriften, en dat zijn precies de gevallen die de code hierboven doorspelt. Opgeslagen filters roepen een verwante vraag op over hoe Excel een criterium opslaat versus hoe hij het vergelijkt, behandeld in het HotXLS-artikel over BIFF8 AutoFilter DOPER-criteria

HotXLS evalueert COUNTIF, MATCH, XLOOKUP, DSUM en de rest van zijn functiebibliotheek native in Delphi en C++Builder, in de klassieke engine en de XLSX-engine, zonder Excel-installatie op de machine. Edities, documentatie en de trial-build staan op de HotXLS Delphi spreadsheet component-pagina