Die HotXLS Delphi Component liest denselben Muster-String auf vier verschiedene Arten, weil Excel 16 es auch tut. In COUNTIF und SUMIF ist der Text a~b literal, sofern das Kriterium nicht zusätzlich * oder ? enthält; in MATCH und XLOOKUP im Wildcard-Modus ist die Tilde stets ein Escape, a~b findet also ab; in DSUM und den anderen Datenbankfunktionen bedeutet schlichter Text „beginnt mit“; und ganzzelliges Find muss ins letzte * zurückspringen. HotXLS folgt diesen ausgemessenen Regeln seit v2.384.52, v2.384.60 und v2.384.64
Die Bug-Reports aus diesem Umfeld erwähnen nie Wildcards. Sie lauten: Ein servergenerierter Report zählt ein paar Zeilen weniger als dieselbe Datei in Excel neu berechnet, oder eine Teilenummer mit Tilde wird von der einen Formel gefunden und von der nächsten ignoriert. Die Ursache ist ein Matcher, der annimmt, ein Muster bedeute überall dasselbe. Excel arbeitet nicht so, und eine Engine, deren zwischengespeicherte Ergebnisse mit Excel übereinstimmen müssen, kann es auch nicht. Vor v2.384.52 schickte HotXLS jedes Kriterium durch eine DOS-artige Dateimaske, die Alltagsmuster richtig und die Randfälle stillschweigend falsch behandelte
Warum bedeutet ein Muster-String in Excel vier verschiedene Dinge?
Ein Muster-String bedeutet vier verschiedene Dinge, weil Excel vier Matching-Regeln aus vier Features geerbt hat und sie nie vereinheitlichte. 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 Freunde) folgen dem Advanced Filter, wo ein nacktes Wort ein Präfix ist. Der Find-Dialog hat seine eigenen ganzzelligen und Teilmatch-Modi. Die folgende Tabelle listet, welche Zellen jedes Muster gegen eine Spalte mit a~b, ab, AB, abc, abcb, a*b und axb matcht, mit jeder Funktion in ihrem Default-Modus ohne Groß-/Kleinschreibung
| Muster | COUNTIF / SUMIF | MATCH(…,0) / XLOOKUP Modus 2 | DSUM-Kriterium | Find, ganze Zelle, Wildcards an |
|---|---|---|---|---|
ab | ab, AB | ab, AB | ab, AB, abc, abcb | ab, AB |
a*b | a~b, ab, AB, abcb, a*b, axb | wie COUNTIF | jeder Eintrag, abc eingeschlossen | wie COUNTIF |
a~b | nur a~b | ab, AB | ab, AB, abc, abcb | ab, AB |
a~*b | nur a*b | nur a*b | nur a*b | nur a*b |
=ab | ab, AB | nicht anwendbar | ab, AB | nicht anwendbar |
Die a~b-Zeile ist die, in der COUNTIF und MATCH auseinandergehen, und Teilenummern und handgetippte Codes enthalten öfter Tilden, als man denkt. Die a*b-Zeile zeigt die andere Falle: abc matcht für DSUM, aber nicht für COUNTIF, weil die Datenbankfunktion stillschweigend ein * anhängt. Die DSUM-Einträge für ab, a*b und =ab stammen direkt aus Excel-16-Läufen; der DSUM-Eintrag für a~b folgt aus derselben Präfix-Regel, denn das angehängte * macht das Kriterium zu einem Wildcard-Muster, in dem ~b ein escaptes b ist
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 vergleicht Excel das Kriterium mit jeder Zelle als ganzen String, ohne Groß-/Kleinschreibung, und eine Tilde ist einfach eine Tilde, COUNTIF(A1:A7,"a~b") zählt also die Zelle, die wörtlich a~b hält. Ein einziger Stern dazu, und die Bedeutung kippt: In "a~b*" escapet die Tilde jetzt das b, das Muster liest sich als „ab gefolgt von irgendwas“, und die Zelle a~b wird nicht mehr gezählt. HotXLS wendet diese Regel in beiden Engines seit v2.384.52 an, durch einen einzigen Kriterien-Matcher in lxCalc, den sich COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS und die Datenbankfunktionen teilen
Innerhalb des Wildcard-Modus gelten dieselben Escape-Regeln wie überall sonst in Excel: ~ macht das nächste Zeichen literal, welches auch immer, ~b bedeutet also b und ~~ eine Tilde, und eine Tilde am allerenden des Musters fällt weg, "a*~" verhält sich also wie "a*". Eckige Klammern sind nie etwas Besonderes. Ein Kriterium "[x]" zählt Zellen, die die drei Zeichen [x] halten, und "[a-z]" zählt auf gewöhnlichen Daten nichts. TXLSXWorkbook.Calculate wertet einen Formelstring gegen das aktive Blatt aus und liefert ein Variant, der schnellste Weg, diese Regeln gegen die eigenen Daten zu prüfen
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 ... damit eine SUMIF-Summe ihre Zeilen benennt
end;
Sheet.Cells[8, 1].Value := 5; // eine Zahl; A9 bleibt leer
Show('=COUNTIF(A1:A7,"a~b")'); // 1 kein * oder ?: schlichter 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 jede Zeile 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 zählt „<>text“?
Ein "<>text"-Kriterium zählt jede Zelle, die nicht dieser Text ist, und in Excel 16 schließt das Zahlen, Booleans, Fehlerwerte und leere Zellen ein. Ein nacktes "<>" ist eine ganz andere Frage: Es bedeutet „keine leere Zelle“, überspringt also leere Zellen, zählt aber jeden Wert, eingeschlossen den leeren Text, den eine Formel wie ="" zurückgibt. Der alte HotXLS-Code traf Textzellen, aber keine Zahlen: Eine Variant-Ungleichung brachte Delphi dazu, 'ab' in eine Zahl zu konvertieren, die Konvertierung warf eine Exception, ein Handler schluckte sie als „kein Treffer“, und numerische Zellen fielen stillschweigend aus der Zählung. Die Leerzellen-Seite dieser Geschichte, eingeschlossen die Frage, woran ein leerer Operand in einem gewöhnlichen Vergleich gleichzieht, behandelt wie HotXLS Vergleichsketten, leere Zellen und SUMIF behandelt
Warum findet MATCH ab, wenn man nach a~b sucht?
MATCH findet ab, wenn man nach a~b sucht, weil MATCH mit Match-Typ 0 und XLOOKUP mit match_mode 2 stets im Wildcard-Modus sind, die Tilde also ein Escape ist, selbst wenn das Muster kein * oder ? enthält. Excel 16 bestätigt es an einem Zweizellen-Bereich mit a~b und ab: MATCH("a~b",D1:D2,0) liefert 2, und auf einem Bereich, der nur a~b hält, liefert derselbe Aufruf #N/A. Um den literalen Text a~b nachzuschlagen, müssen Sie "a~~b" schreiben. Derweil liefert COUNTIF(D1:D2,"a~b") über dieselben zwei Zellen 1 und zählt damit die andere Zelle. Derselbe String, derselbe Bereich, die entgegengesetzte Zelle
Genau deshalb hält HotXLS die zwei Entscheidungen auseinander, statt sie hinter einem einzigen „matche ein Muster“-Einstiegspunkt zu verstecken. Der Matcher selbst ist geteilt: Seit v2.384.52 fahren MATCH, XLOOKUP und die Kriterienfunktionen denselben Backtracking-Matcher, mit derselben Escape-Behandlung und derselben Tilde-am-Ende-Regel. Anders ist nur das Gate davor. Der Kriterien-Pfad fragt zuerst „enthält dieser Text * oder ??“; der Lookup-Pfad fragt nie. Beides zusammenzulegen würde die eine Familie fixen und die andere brechen, und beide Richtungen sind in beiden Engines gegen Excel-16-Werte geprüft. Wildcard-Lookups haben zudem eine eigene Vorbedingung: XLOOKUP weist Wildcard-Matching kombiniert mit einem Binary-Search-Modus zurück, eine Regel, die die HotXLS-Anleitung zu den XLOOKUP- und XMATCH-Suchmodi beschreibt
Wie lesen DSUM und die Datenbankfunktionen ein Textkriterium ohne Vorzeichen?
DSUM und die anderen Datenbankfunktionen lesen ein Textkriterium ohne führendes =, < oder > als „beginnt mit“, mit weiterhin aktiven Wildcards. Das ist die Advanced-Filter-Regel, und sie unterscheidet sich mit Absicht von COUNTIF. Excel 16, ausgemessen über eine Name-Spalte mit abc, ab, xab, AB, a~b und a*b: Das Kriterium ab matcht abc, ab und AB; =ab matcht nur ab und AB; <>ab ist eine Ungleichung über den ganzen Eintrag; a*b und a? sind ebenfalls Präfix-Muster; >ab ist ein gewöhnlicher Vergleich. Vor v2.384.64 matchte HotXLS ab exakt, ein DSUM über diese Testdaten lieferte also 10, wo Excel 11 liefert
Der Fix musste um den Condition-Parser herumarbeiten, der ab und =ab in dieselbe Gleichheitsbedingung faltet. HotXLS inspiziert deshalb den rohen Kriterientext, bevor es der geparsten Bedingung vertraut: Ein Textkriterium, dessen erstes Zeichen nicht =, < oder > ist, bekommt ein * angehängt und läuft durch den Wildcard-Matcher, alles andere behält seinen ganzen-Eintrag-Vergleich. Ein praktischer Hinweis, wenn Sie Kriterienbereiche im Code bauen: In der XLSX-Engine speichert die Zuweisung des Strings '=ab' an TXLSXCell.Value Text, während die klassische TXLSWorkbook-Engine einen mit = beginnenden Wert als Formel kompiliert, sofern Sie kein Apostroph voransetzen
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;Ein verwandter Unterschied überlebte den Präfix-Fix und zählt auf älteren Builds. Textvergleiche wie >ab liefen in Codepunkt-Ordnung, während Excel Satzeichen vor Buchstaben setzt, "a~b">"ab" ist in Excel also FALSE und war in HotXLS TRUE. Seit v2.384.67 folgen die >- und <-Kriterien zusammen mit dem gewöhnlichen Textvergleich und der Sortierung Excels Wortsortierungs-Collation unter der aktuellen User-Locale, und die beiden ziehen wieder gleich
Warum verpasste ganzzelliges Find das abcb?
Ganzzelliges Find verpasste abcb, weil der Matcher an der ersten Stelle anhielt, an der das Muster aufgebraucht war, statt ins letzte * zurückzuspringen. Der Teilmatch-Matcher hinter Replace kehrt zurück, sobald das Muster erschöpft ist; ganzzelliges Find wiederverwendete ihn und verlangte dann, dass der Treffer die gesamte Zelle abdeckt: a*b gegen abcb blieb nach ab stehen, hatte 2 von 4 Zeichen konsumiert und wurde abgelehnt. Seit v2.384.60 ist der ganzzellige Matcher eine eigene Implementierung, die „Muster zu Ende, Text nicht“ als einen weiteren Mismatch behandelt und vom letzten Stern aus erneut probiert, a*b matcht also abcb und a?b*b matcht axbyb, wie Excel 16 Find mit angehaktem „Match entire cell contents“
Dieselbe Version änderte die Tilde. Excel 16 Find behandelt, in beiden Modi, ganzzellig wie Teilmatch, ~ 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. Der ältere HotXLS-Matcher erkannte nur ~*, ~? und ~~ als Escapes, a~b fand also den Text a~b. Ein Find-Muster aus einer einzigen ~ ist in Excel selbst instabil, es matcht jede Zelle wie ein leeres Muster, und HotXLS imitiert das nicht
In der XLSX-Engine ist die Suche TXLSXWorksheet.FindText mit einem TXLSXFindOptions-Set: lxfUseWildcards schaltet *, ? und ~ ein, lxfWholeCell verlangt, dass die ganze Zelle matcht, und lxfMatchCase macht den Vergleich case-sensitiv. Ohne lxfUseWildcards ist jedes Zeichen literal, der Stern eingeschlossen. Find schaut nur auf Textwerte; numerische Zellen werden übersprungen, und Formelzellen werden übersprungen, sofern lxfSearchFormulas nicht gesetzt ist, in dem Fall wird der Formeltext durchsucht. Der Anker aus StartRow und StartCol zählt mit, eine Find-All-Schleife springt also eine Spalte hinter jeden Treffer
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 findet alle vier Zeilen, abc eingeschlossen, denn im Teilmatch-Modus muss a*b nur irgendwo in der Zelle auftauchen. FindTextIn und ReplaceTextIn nehmen dieselben Optionen plus ein FirstRow-, FirstCol-, LastRow-, LastCol-Fenster, das programmatische Äquivalent zur Suche innerhalb einer Auswahl. Die klassische Engine legt dieselben Regeln über einen Overload mit drei Booleans offen, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus einen passenden ReplaceText-Overload, mit 1-basierten Zeilen- und Spaltenergebnissen:
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;Was bekam der alte DOS-Masken-Matcher falsch?
Der alte Matcher bekam die Sonderzeichen falsch, denn eine DOS-Dateimaske ist eine andere Sprache als eine Excel-Wildcard. Vor v2.384.52 reichten die Kriterienfunktionen und die Datenbankfunktionen jedes Muster an MatchesMask weiter, einen Dateimasken-Matcher in der Unit lxMasks. Seine Syntax überlappt sich für gewöhnliche Fälle mit Excels, deshalb blieb das Problem verborgen, aber sie weicht genau dort ab, wo echte Daten interessant werden:
[x]wurde als Zeichensammlung gelesen,COUNTIF(A1:A10,"[x]")zählte also Zellen mitxstatt des eingeklammerten Texts, und"[a-z]"matchte jede Einbuchstaben-Zelle- Es gab kein Tilde-Escape,
"a~*b"konnte also keinen literalen Stern matchen - Eine malformed Maske, etwa eine ungeschlossene Klammer, warf eine Exception, die der Aufrufer als „kein Treffer“ schluckte, ein Tippfehler in einem Kriterium wurde so zu einer stillschweigend falschen Summe
- Auf der Lookup-Seite behandelten
MATCHundXLOOKUPnur~*,~?und~~als Escapes,MATCH("a~b",…,0)fand also das literalea~bstattab
Haben Ihre Arbeitsmappen jemals nur * und ? auf schlichten alphanumerischen Daten benutzt, stimmten die Ergebnisse bereits und ändern sich nicht. Enthalten sie Klammern, Tilden, gemischte Typspalten unter "<>text" oder als nackte Wörter geschriebene DSUM-Kriterien, kann eine Neuberechnung mit v2.384.64 oder später die Summen ändern, und die neuen Summen sind die, die Excel zeigt. Derselbe Unterschied zwischen Excels Speicherung eines Kriteriums und seinem Vergleich taucht bei gespeicherten Filtern auf, behandelt in dem HotXLS-Artikel zu den BIFF8-AutoFilter-DOPER-Kriterien
Kurzreferenz: Excel-Wildcard-Regeln in HotXLS
COUNTIF,SUMIF,AVERAGEIFund die*IFS-Familie nutzen Wildcards nur, wenn das Kriterium*oder?enthält; sonst vergleichen sie ganze Strings ohne Groß-/Kleinschreibung, und~ist literal (seit v2.384.52)MATCHmit Match-Typ 0 undXLOOKUPmit match_mode 2 nutzen stets Wildcards,a~bfindet alsoab, und das Literale brauchta~~b(seit v2.384.52)- Im Wildcard-Modus escapet
~jedes folgende Zeichen, und eine abschließende~fällt weg;[und]sind gewöhnliche Zeichen "<>text"zählt Zahlen, Booleans, Fehler und leere Zellen; ein nacktes"<>"zählt nicht-leere Zellen,=""-Ergebnisse eingeschlossenDSUMund die anderen Datenbankfunktionen behandeln schlichten Text als „beginnt mit“;=textund<>textvergleichen den ganzen Eintrag (seit v2.384.64)- Ganzzelliges Find mit
lxfUseWildcardsundlxfWholeCellbacktracked,a*bmatcht alsoabcb; Find und Replace behandeln~als Escape für jedes Zeichen (seit v2.384.60) - Die Textordnung in
>- und<-Kriterien folgt Excels Wortsortierungs-Collation, Satzeichen vor Buchstaben (seit v2.384.67)
Excel-Kompatibilität in einer Formel-Engine ist meistens ein Haufen Randfälle wie diese, ausgemessen gegen Excel statt aus der Dokumentation geraten. HotXLS evaluiert COUNTIF, MATCH, XLOOKUP, DSUM und den Rest seiner Funktionsbibliothek nativ in Delphi und C++Builder, in der klassischen wie in der XLSX-Engine, ohne installiertes Excel. Details, Editionen und den Testdownload finden Sie auf der HotXLS Delphi spreadsheet component page