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
| 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 |
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
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
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“
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 mitxstatt 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)mussabfinden, nicht das literalea~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