Technischer Artikel

HotXLS Excel-Wildcards: COUNTIF, MATCH, DSUM und Find

Mit a~b in D1 und ab in D2 liefert =COUNTIF(D1:D2,"a~b") 1, und die gezählte Zelle ist D1. =MATCH("a~b",D1:D2,0) liefert 2 und zeigt damit auf D2. Gleicher Muster-String, gleiche zwei Zellen, und Excel pickt je nachdem, welche Funktion das Muster liest, eine andere Zelle heraus. Eine Spreadsheet-Engine, deren gecachte Ergebnisse mit Excel übereinstimmen sollen, muss diesen Split nachbauen, zusammen mit zwei weiteren Regeln: Datenbankfunktionen wie DSUM lesen nackten Text als „beginnt mit“, und ganzzelliges Find muss ins letzte * zurückverfolgen können

Berichte, die darauf stoßen, erwähnen Wildcards selten. Sie sagen, ein serverseitig erzeugtes Workbook zählt zwei Zeilen weniger als dieselbe Datei neu berechnet auf dem Desktop, oder dass eine Teilenummer mit Tilde von der einen Formel gefunden und von der nächsten ignoriert wird. Jedes Muster durch eine DOS-Dateimaske zu jagen trifft die Alltagsfälle, deshalb zeigt sich die Drift erst, wenn echte Produktcodes anfangen, ~ oder [ zu enthalten

Ein Muster-String, vier Excel-Regelwerke

Excel hat seine Match-Regeln nie vereinheitlicht; jedes Feature brachte seine eigenen mit. Die Kriterienfunktionen (COUNTIF, SUMIF, AVERAGEIF und die *IFS-Familie) entscheiden pro Kriterium, ob Wildcards überhaupt gelten. Die Lookup-Funktionen (MATCH mit Match-Typ 0, XLOOKUP mit match_mode 2) wenden sie immer an. Die Datenbankfunktionen (DSUM, DCOUNTA und der Rest) erben die Advanced-Filter-Regel, nach der ein nacktes Wort ein Präfix ist. Der Find-Dialog hat seine eigenen Ganzzelle- und Teilmatch-Modi. Die HotXLS Delphi Component wertet jede Familie nach ihrer eigenen Regel aus, in der klassischen Engine wie in der XLSX-Engine. Die Tabelle listet, welche Zellen auf jedes Muster passen, gegen eine Spalte mit a~b, ab, AB, abc, abcb, a*b und axb, jede Funktion in ihrem case-insensitiven Standardmodus

MusterCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP Modus 2DSUM-KriteriumFind, ganze Zelle, Wildcards an
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbwie COUNTIFjeder Eintrag, abc eingeschlossenwie COUNTIF
a~bnur a~bab, ABab, AB, abc, abcbab, AB
a~*bnur a*bnur a*bnur a*bnur a*b
=abab, ABnicht anwendbarab, ABnicht anwendbar

Zwei Zeilen verdienen einen zweiten Blick. In der a~b-Zeile landen COUNTIF und MATCH auf verschiedenen Zellen, und handgetippte Codes enthalten öfter Tilden, als irgendjemand einplant. In der a*b-Zeile matcht abc für DSUM und scheitert an COUNTIF, weil die Datenbankfunktion dem Kriterium still einen * anhängt. Der DSUM-Eintrag für a~b folgt aus ebendiesem angehängten Stern: Sobald er da ist, ist das Kriterium ein Wildcard-Muster, und ~b ist nur ein escaptes b

Wann schaltet COUNTIF in den Wildcard-Modus?

COUNTIF schaltet nur dann in den Wildcard-Modus, wenn der Kriterientext * oder ? enthält, escaped oder nicht. Ohne eines der beiden Zeichen wird das Kriterium zellenweise als ganzer String verglichen, case-insensitiv, und eine Tilde ist ein gewöhnliches Zeichen, sodass COUNTIF(A1:A7,"a~b") die Zelle zählt, die wörtlich a~b hält. Ein einziger Stern kippt die Bedeutung. In "a~b*" escapet die Tilde jetzt das b, das Muster liest sich als „ab, gefolgt von irgendwas“, und die a~b-Zelle fällt aus der Zählung raus. HotXLS wendet dieses Gate in COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS wie in den Datenbankfunktionen an

HotXLS-Wildcard-Gate-Diagramm: COUNTIF und SUMIF wenden Wildcards nur an, wenn das Kriterium einen Stern oder ein Fragezeichen enthält, a~b zählt also die literale Zelle und liefert 1, während MATCH Typ 0 und XLOOKUP Modus 2 stets im Wildcard-Modus sind, a~b also ab an Position 2 findet
Das Gate ist der ganze Unterschied: COUNTIF fragt nach einem Stern oder Fragezeichen, bevor es eine Tilde als Escape behandelt, MATCH fragt nie, sodass ein Muster-String die eine Zelle zählt und die andere findet

Im Wildcard-Modus ist die Escape-Regel kurz. ~ macht das nächste Zeichen literal, welches auch immer, also heißt ~b schlicht b und ~~ eine Tilde; eine Tilde ganz am Ende des Musters fällt weg, "a*~" verhält sich also wie "a*". Eckige Klammern haben keine Sonderrolle. Ein Kriterium "[x]" zählt Zellen, die die drei Zeichen [x] halten, und "[a-z]" zählt bei gewöhnlichen Daten nichts. Am schnellsten prüfen Sie das alles gegen Ihre eigenen Daten mit TXLSXWorkbook.Calculate, das einen Formel-String gegen das aktive Sheet auswertet und ein Variant zurückgibt; in einem frischen Workbook ist das zuerst hinzugefügte Sheet das aktive

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');            // erstes Sheet, daher aktiv
    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 ... damit benennt eine SUMIF-Summe ihre Zeilen
    end;
    Sheet.Cells[8, 1].Value := 5;                // eine Zahl; A9 bleibt leer

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    kein * oder ?: plain text, die Zelle a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    Wildcard-Modus: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    Wildcard über den ganzen String, abc ausgeschlossen
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  alle Zeilen außer abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    das literale a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    die Zahl 5 und das leere A9 zählen
    Show('=COUNTIF(A1:A9,"<>")');       // 8    nicht-leere Zellen
  finally
    Book.Free;
  end;
end.

Was „<>text“ wirklich zählt

Ein "<>text"-Kriterium zählt jede Zelle, die nicht dieser Text ist, und in Excel schließt das Zahlen, Booleans, Fehlerwerte und leere Zellen ein. Ein nacktes "<>" fragt etwas ganz anderes: „keine leere Zelle“. Es überspringt leere Zellen, zählt aber jeden Wert, inklusive des leeren Texts, den eine Formel wie ="" zurückgibt. Die Delphi-Falle steckt im Variant-Vergleich. Vergleicht man 'ab' mit einer numerischen Zelle, versucht Delphi, 'ab' in eine Zahl zu konvertieren, die Konvertierung wirft, und ein Handler, der die Exception als „kein Match“ schluckt, wirft numerische Zellen aus der Zählung. HotXLS zählt sie mit, deshalb liefern die letzten beiden Zeilen des Beispiels 7 und 8. Die Leerzellen-Seite davon, inklusive der Frage, woran ein leerer Operand in einem gewöhnlichen Vergleich gleichkommt, behandelt wie HotXLS Vergleichsketten, leere Zellen und SUMIF behandelt

Warum findet MATCH ab, wenn man nach a~b sucht?

MATCH mit Match-Typ 0 und XLOOKUP mit match_mode 2 sind immer im Wildcard-Modus, die Tilde ist also ein Escape, selbst wenn das Muster kein * oder ? enthält. Auf dem Zweizellen-Bereich aus dem Einstieg liefert MATCH("a~b",D1:D2,0) 2. Auf einem Bereich, der nur a~b hält, liefert derselbe Aufruf #N/A. Den literalen Text a~b zu suchen braucht "a~~b". Wenn Lookup-Keys aus Nutzereingaben oder einem externen System stammen und eine Tilde enthalten könnten, verdoppeln Sie jede Tilde, bevor Sie die Formel bauen; dieses eine StringReplace ist billiger, als später eine fehlende Zeile erklären zu müssen

HotXLS hält diese beiden Entscheidungen auseinander. MATCH, XLOOKUP und die Kriterienfunktionen teilen sich einen Backtracking-Matcher mit derselben Escape-Behandlung und derselben Trailing-Tilde-Regel; unterschiedlich ist nur das Gate davor. Der Kriterien-Pfad fragt zuerst, ob der Text * oder ? enthält. Der Lookup-Pfad fragt nie. Ein einzelner „Match-auf-Muster“-Einstieg für beide würde die eine Familie richtig und die andere falsch bedienen, egal wie das Gate stünde. Wildcard-Lookups tragen eine weitere Bedingung: XLOOKUP liefert #VALUE!, wenn match_mode 2 mit einem binären Suchmodus kombiniert wird — das behandelt der HotXLS-Leitfaden zu XLOOKUP- und XMATCH-Suchmodi

DSUM-Kriterien: nackter Text heißt „beginnt mit“

DSUM und die anderen Datenbankfunktionen lesen ein Textkriterium ohne führendes =, < oder > als Präfix, mit weiterhin aktiven Wildcards. Das ist die Advanced-Filter-Regel, und sie unterscheidet sich mit Absicht von COUNTIF. Über einer Namensspalte mit abc, ab, xab und AB matcht das Kriterium ab auf abc, ab und AB, während =ab nur auf ab und AB matcht. <>ab ist eine Ungleichheit über den ganzen Eintrag, a*b und a? sind ebenfalls Präfixmuster, und >ab ist ein gewöhnlicher Textvergleich. xab matcht nie auf ab; die Regel lautet „beginnt mit“, nicht „enthält“

Das Vertrackte für eine Implementierung: Ein Kriterien-Parser, der den Operator abtrennt, sieht in ab und =ab denselben Gleichheitstest. Nur der rohe Kriterientext unterscheidet beide. HotXLS sieht sich deshalb diesen rohen Text an, bevor es der geparsten Bedingung vertraut: Ein Textkriterium, dessen erstes Zeichen nicht =, < oder > ist, bekommt einen * angehängt und läuft durch den Wildcard-Matcher, alles andere behält seinen Vergleich über den ganzen Eintrag

HotXLS-Diagramm der DSUM-Kriterien-Regel: Ein nacktes Textkriterium bekommt einen Stern angehängt und matcht als Präfix, ab erreicht also ab, AB, abc und abcb, equals ab vergleicht den ganzen Eintrag, Winkelklammer ab schließt beide aus, und eine Tilde mit Stern überlebt als literales a*b, mit den ausgemessenen DSUM-Summen 30, 6, 121 und 32
Excel hat die Advanced-Filter-Regel für die Datenbankfunktionen geerbt: Nackter Text bedeutet „beginnt mit“, ein führendes Gleichheits- oder Ungleichheitszeichen vergleicht den ganzen Eintrag; HotXLS inspiziert den rohen Kriterientext, bevor es der geparsten Bedingung vertraut

Beim Aufbau eines Kriterienbereichs im Code gibt es eine engine-spezifische Falle. In der XLSX-Engine speichert die Zuweisung des Strings '=ab' an TXLSXCell.Value Text — genau das, was eine Kriterienzelle braucht. Die klassische TXLSWorkbook-Engine kompiliert einen String-Wert, der mit = beginnt, als Formel, außer man stellt ein Apostroph voran. Für zur Laufzeit gebaute Kriterienbereiche ist die XLSX-Engine die weniger fehleranfällige Wahl. Die Schleife unten tauscht fünf Kriterien in D2 durch und berechnet jedes Mal dasselbe DSUM neu

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';            // Kriterien-Header in D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // bleibt Text in der XLSX-Engine
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (beginnt mit)
    // =ab  -> 6    ab, AB (ganzer Eintrag)
    // <>ab -> 121  alles außer ab und AB
    // a*b  -> 127  a*b* matcht alle sieben, abc eingeschlossen
    // a~*  -> 32   nur das literale a*b
  finally
    Book.Free;
  end;
end;

Ordnungskriterien wie >ab folgen noch mal einer anderen Regel. Ein nackter Code-Punkt-Vergleich macht "a~b">"ab" zu TRUE, weil ~ in Unicode über jedem Buchstaben sitzt. Excel stellt Zeichensetzung vor die Buchstaben und liefert FALSE. Unter Windows wertet HotXLS >- und <-Textkriterien, gewöhnliche Textvergleiche und Sortierung mit der Word-Sort-Kollation des Systems für das aktuelle Benutzer-Locale aus, sodass alle drei untereinander und mit Excel übereinstimmen

Ganzzelliges Find in Delphi: Backtracking ins letzte Sternchen

Der verlockende Shortcut für ganzzelliges Find ist, den Teilmatch-Matcher hinter Replace wiederzuverwenden und zusätzlich zu verlangen, dass der Match die ganze Zelle abdeckt. An gewöhnlichen Daten scheitert das. Ein Teilmatch-Matcher kehrt zurück, sobald das Muster aufgebraucht ist, also stoppt a*b gegen abcb nach ab, hat 2 von 4 Zeichen konsumiert, und die Zelle wird abgelehnt. HotXLS leitet ganzzelliges Find durch einen separaten Matcher, der „Muster zu Ende, Text nicht“ als eine weitere Nichtübereinstimmung behandelt und vom letzten Stern neu ansetzt. Damit matcht a*b auf abcb und a?b*b auf axbyb, wie Excel Find mit angehaktem „Match entire cell contents“

HotXLS-Diagramm des Backtracking bei ganzzelligem Wildcard-Find: Das Muster a*b konsumiert a und b in der Zelle abcb, und ein Matcher, der aufhört, sobald das Muster aufgebraucht ist, lehnt die Zelle ab, während der HotXLS-Matcher „Muster zu Ende, Text übrig“ als eine weitere Nichtübereinstimmung behandelt und vom letzten Stern neu ansetzt, bis die ganze Zelle matcht
Ein ganzzelliger Match ist nicht fertig, wenn das Muster aufgebraucht ist; übrigen Text als eine weitere Nichtübereinstimmung zu behandeln schickt den Matcher zum letzten Stern zurück — so erreicht a*b das abcb wie Excel Find

Excel Find behandelt in beiden Modi, ganzzellig und teilweise, ~ als Escape für jedes folgende Zeichen: a~b findet ab, a~~b findet a~b, und eine Tilde am Ende wird ignoriert, q~ verhält sich also wie q. Ein Matcher, der nur ~*, ~? und ~~ kennt, ließe a~b auf den Text a~b passen — die gegenteilige Zelle. HotXLS escapet jedes Zeichen hinter einer Tilde, im Find und Replace wie in Formeln

In der XLSX-Engine ist der Aufruf TXLSXWorksheet.FindText mit einem TXLSXFindOptions-Set. lxfUseWildcards schaltet *, ? und ~ ein; lxfWholeCell verlangt, dass die ganze Zelle matcht; lxfMatchCase macht den Vergleich case-sensitiv. Ohne lxfUseWildcards ist jedes Zeichen literal, Stern eingeschlossen. Find schaut nur auf Textwerte. Numerische Zellen werden übersprungen, und Formelzellen ebenfalls, außer mit lxfSearchFormulas — dann wird der Formeltext durchsucht. lxfBackward und lxfByColumns ändern die Scan-Reihenfolge. Der Anker aus StartRow und StartCol zählt mit, deshalb muss eine Find-All-Schleife jeweils eine Spalte hinter den Treffer springen

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 abgelehnt, abcb per Backtracking
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b ist ein escaptes b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ ist eine literale Tilde

    // Teilmatch, Find All: Die Ankerzelle zählt mit, also hinter jeden Treffer springen
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // Zeilen 1, 2, 3 und 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Ganzzelliger Wildcard-Replace schreibt nur das literale a~b um
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Die Teilmatch-Schleife meldet alle vier Zeilen, abc eingeschlossen, denn im Teilmatch-Modus muss a*b nur irgendwo in der Zelle auftreten. FindTextIn und ReplaceTextIn nehmen dieselben Optionen plus ein FirstRow-, FirstCol-, LastRow-, LastCol-Fenster — die programmatische Version der Suche innerhalb einer Auswahl; für ein offenes Ende 0 durchreichen. Die klassische Engine exponiert dieselben Match-Regeln über einen Overload mit drei Booleans, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus einen passenden ReplaceText-Overload. Seine Zeilen- und Spaltenergebnisse sind 1-basiert, und wie die XLSX-Version überspringt er Formelzellen und Nicht-Text-Werte

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;

Warum eine DOS-Dateimaske der falsche Matcher für Excel-Kriterien ist

Ein Dateimasken-Matcher wie MatchesMask spricht eine andere Sprache als Excel-Wildcards, auch wenn sich beide bei * und ? überlappen. Genau diese Überlappung ist der Grund, warum er gewählt wird und warum seine Fehler unbemerkt bleiben, bis die Daten interessant werden:

  • [x] wird als Zeichensatz gelesen, COUNTIF(A1:A10,"[x]") zählte also Zellen mit x statt des geklammerten Texts, und "[a-z]" matchte jede Ein-Buchstaben-Zelle
  • Es gibt keinen Tilde-Escape, ein "a~*b" kann daher kein literales Sternchen matchen
  • Eine kaputte Maske, etwa eine ungeschlossene Klammer, wirft eine Exception, die ein Aufrufer leicht als „kein Match“ schluckt und aus einem Tippfehler im Kriterium eine falsche Summe macht
  • Auf der Lookup-Seite reicht ein Escape-Set nur mit ~*, ~? und ~~ ebenfalls nicht: MATCH("a~b",…,0) muss ab finden, nicht das literale a~b

Wenn Ihre Workbooks nur * und ? auf schlicht alphanumerischen Daten verwenden, sind sich alle diese Matcher einig, und das alles ist egal. Eckige Klammern, Tilden, gemischte Spaltentypen unter "<>text" und als nackte Wörter geschriebene DSUM-Kriterien sind die Stellen, an denen die Summen eines einfacheren Matchers von denen Excels abdriften — und genau diese Fälle übt der Code oben durch. Gespeicherte Filter werfen eine verwandte Frage auf: wie Excel ein Kriterium speichert im Vergleich dazu, wie es es vergleicht — das diskutiert der HotXLS-Artikel zu BIFF8-AutoFilter-DOPER-Kriterien

HotXLS wertet COUNTIF, MATCH, XLOOKUP, DSUM und den Rest seiner Funktionsbibliothek nativ in Delphi und C++Builder aus, in der klassischen Engine wie in der XLSX-Engine, ohne Excel-Installation auf der Maschine. Editionen, Dokumentation und die Trial-Version finden sich auf der HotXLS Delphi Spreadsheet Component-Seite