In Excel liefert ="a-b">"ab" TRUE, und ="a~b"<"ab" liefert ebenfalls TRUE. Übergibt man dieselben Paare an Delphis CompareText, kippen beide Antworten, weil der Bindestrich in der Codepunkt-Ordnung unter den Buchstaben liegt und die Tilde darüber. Excel vergleicht Text mit der case-insensitiven „word sort“ der Windows-User-Locale, in der Bindestrich und Apostroph nur Gleichstände brechen und jedes andere Satzeichen vor Ziffern und Buchstaben sortiert
Die Symptome erwähnen Collation selten. Ein COUNTIF(A:A,">M") auf dem Server zählt zwei Zeilen mehr als dieselbe Formel in Excel. Eine vom Reporting-Dienst sortierte Preisliste bringt X-100 an eine Stelle, an die Excel sie nie legen würde. VLOOKUP("ABC",...) liefert #N/A, obwohl die Spalte nachweislich abc enthält. Alle drei laufen auf dieselbe Frage hinaus: Wenn beide Operanden Text sind, welcher ist dann der kleinere? Excel hat eine präzise Antwort, und es ist selten die, die Delphi-Code per Voreinstellung gibt
Nach welcher Regel vergleicht Excel zwei Textstrings?
Excel vergleicht zwei Textwerte mit der Wortsortierung der aktuellen Windows-User-Locale, ohne Groß-/Kleinschreibung. Wortsortierung ist die Default-Collation der Windows-NLS-Vergleichsfunktionen: Buchstaben vergleichen in sprachlicher Ordnung statt nach Codepunkt, é landet also zwischen e und f, und Groß- und Kleinschreibung fallen zusammen. Zwei Zeichen bekommen eine Sonderbehandlung. Der Bindestrich - und der Apostroph ' werden im ersten Durchlauf übersprungen, wodurch co-op direkt neben coop landet; erst wenn die übrigen Zeichen gleichziehen, entscheiden ihre Präsenz und ihre Position über die Reihenfolge. Jedes andere Satzeichen zählt mit vollem Gewicht und sortiert vor den Ziffern, und die Ziffern sortieren vor den Buchstaben
Die Tabelle stellt Excels Urteil für IF(A<B,...) neben die zwei Vergleiche, die ein Delphi-Entwickler höchstwahrscheinlich zuerst schreibt
| A vs. B | Excel / HotXLS | CompareStr (ordinal) | CompareText |
|---|---|---|---|
"a-b" vs. "ab" | größer | kleiner | kleiner |
"a'b" vs. "ab" | größer | kleiner | kleiner |
"a~b" vs. "ab" | kleiner | größer | größer |
"a_b" vs. "ab" | kleiner | kleiner | größer |
"ab" vs. "AB" | gleich | größer | gleich |
"é" vs. "f" | kleiner | größer | größer |
"Z" vs. "f" | größer | kleiner | größer |
Zwei Konsequenzen werden regelmäßig übersehen. Weil der Bindestrich nur Gleichstände bricht, ist ="a-b"="ab" FALSE: Die beiden Strings liegen in einer Sortierung direkt nebeneinander und sind trotzdem ungleich. Und weil die Groß-/Kleinschreibung komplett wegfällt, sind ab, AB und Ab für jeden Vergleichsoperator ein einziger Schlüssel. Unter einer englischen User-Locale sortiert eine Spalte mit 20 Widerlingswörtern so: a b, a.b, a_b, a~b, a0, a1b, ab / AB / Ab, ab-, a'b, a-b, -ab, ab1, abc, b, e, é, f, Z. Innerhalb der ab-Gruppe entscheidet die Position des übersprungenen Zeichens: Bindestrich am Ende zuerst, Bindestrich am Anfang zuletzt
Die Collation selbst gegen Excel prüfen
Excels Dokumentation nennt die verwendete Collation nirgends, deshalb ist der verlässliche Weg, das auf der eigenen Maschine zu bestätigen, Excel über einen Schwung Stringpaare urteilen zu lassen und seine Urteile mit CompareStringW unter verschiedenen Flag-Sets zu vergleichen. Die Paare kommen in zwei Spalten, Excel wertet für jede Zeile =IF(A1<B1,-1,IF(A1=B1,0,1)) aus, und dieselben Paare laufen durch die API. Erzeugenswerte Paare mischen ASCII-Satzeichen, Ziffern, beide Schreibweisen, Leerzeichen, akzentuierte Buchstaben wie é, ß und ä, CJK-Zeichen, Fullwidth-Formen und das geschützte Leerzeichen, mit reichlich Near-Misses, die sich in genau einem Zeichen unterscheiden. Zufallswörter aus einem Wörterbuch sind eine schlechte Stichprobe, denn schlichte Buchstaben sortieren unter jeder Regel dieses Artikels identisch
Die Flag-Sets führen zu klar unterschiedlichen Ergebnissen:
NORM_IGNORECASEallein, mitLOCALE_USER_DEFAULT, ist die schlichte Wortsortierung und die, der Excel folgtSORT_STRINGSORTzusätzlich schaltet auf String-Sort um, wo Bindestrich und Apostroph zu gewöhnlichen Symbolen mit vollem Gewicht werden, so dass jedes Paar im Stil vona-b/abgenau umgekehrt herauskommtNORM_IGNOREWIDTHzusätzlich macht einen Fullwidth-Buchstaben gleich seinem Halfwidth-Pendant, und Excel hält die beiden auseinander
Eine Falle sitzt im Testharness selbst. Excel behandelt einen führenden Apostroph in einer Zelle als „Als Text speichern“-Präfix, eine Zelle, deren gesamte Eingabe ein ' ist, endet also leer, und jedes so gebaute Paar meldet einen Mismatch, der mit Collation nichts zu tun hat. Solche Paare baut man mit String-Literalen in der Formel (="'") oder überspringt sie
Warum liegen CompareText und ordinaler Vergleich daneben?
CompareText und der ordinale Vergleich verfehlen Excels Ordnung, weil sie UTF-16-Codeeinheiten vergleichen und die Codepunkt-Ordnung Satzeichen quer über das Alphabet streut. Der Bindestrich ist U+002D und der Apostroph U+0027, beide unter jedem Buchstaben, ein ordinaler Vergleich nennt "a-b" also kleiner als "ab", wo Excel den Bindestrich als Gleichstand-Brecher behandelt. Die Tilde ist U+007E, über jedem Buchstaben, wodurch "a~b" größer herauskommt, das Gegenteil von Excel. CompareText fügt eine zweite Verzerrung eigener Regie hinzu: Es faltet nur a..z in Großbuchstaben und vergleicht dann Codeeinheiten, und weil der Unterstrich (U+005F) zwischen den Groß- und Kleinbuchstaben-Bereichen liegt, verschiebt das Falten "a_b" von unterhalb "ab" nach oberhalb. Keine der beiden Funktionen hat auch nur eine Ahnung, dass é zwischen e und f gehört
Die üblichen Delphi-Werkzeuge fallen auf beiden Seiten der Linie:
CompareStr, der String-<-Operator undTComparer<string>.Defaultsind ordinal und case-sensitiv,TArray.Sort<string>ohne Comparer setztZalso vorfCompareTextundSameTextsind ordinal nach reiner ASCII-FaltungAnsiCompareTextundWideCompareTextin der Delphi-RTL unter Windows rufenCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)auf, den Aufruf, der zu Excel passt. Eine sortierteTStringListmit ihren Defaults (UseLocaleTrue,CaseSensitiveFalse) läuft überAnsiCompareTextund stimmt deshalb ebenfalls mit Excel- Auf POSIX-Zielen route die Delphi-RTL
AnsiCompareTextdurch einen ICU-Collator, einen anderen Algorithmus mit anderen Satzeichen-Regeln. Free PascalsAnsiCompareTextunter Windows konvertiert in die ANSI-Codepage und ruftCompareStringAauf, was jedes Zeichen verliert, das diese Codepage nicht darstellen kann
Die locale-aware-RTL-Funktionen sind unter Windows also zufällig richtig, einfach wegen ihrer heutigen Implementierung. Code, der mit Excel übereinstimmen muss, sollte den API-Aufruf explizit machen und das auch dazu sagen. Das härtere Problem zeigt sich in jeder Formel-Engine, in der jeder Codepfad greift, was gerade am nächsten liegt: Großbuchstaben und Codepunkt-Vergleich in den =- und <-Operatoren, ein case-sensitiver Variant-Vergleich im >- / <-Zweig der COUNTIF-Kriterien, noch einer in VLOOKUP (genau deshalb findet VLOOKUP("ABC",A1:A20,1,FALSE) abc nicht) und ein Locale-Aufruf in der Bereichssortierung. Jeder Pfad ist für sich vertretbar. Zusammen erzeugen sie drei verschiedene Ordnungen über dieselbe Spalte
Ein Vergleich hinter Formeln, Lookups und Sortierung in HotXLS
HotXLS schickt jeden Text-gegen-Text-Vergleich in seinem Berechnungs- und Sortiercode durch eine einzige Funktion, XlsCompareText in lxStandard.pas, die CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) aufruft und CSTR_EQUAL abzieht. Die sechs Vergleichsoperatoren nutzen sie, ebenso die elementweisen Vergleiche in Array-Formeln, die >-, <-, >=- und <=-Zweige der COUNTIF-artigen Kriterien und der Datenbankfunktionen, VLOOKUP und HLOOKUP im exakten wie im approximativen Modus, die Ordnung hinter den Dynamic-Array-Funktionen und XLOOKUP / XMATCH sowie SortRange auf TXLSWorksheet wie auf TXLSXWorksheet. Die Bereichssortierung auf dieselbe Funktion zu setzen war der entscheidende Design-Punkt. Ein approximativer VLOOKUP auf Text liefert nur dann brauchbare Antworten, wenn die Spalte in der Ordnung sortiert wurde, in der der Lookup vergleicht, und eine gemeinsame Funktion macht es unmöglich, dass diese beiden Ordnungen auseinanderdriften
Am schnellsten sieht man die Regel aus Delphi an TXLSXWorkbook.Calculate, das einen Formel-String gegen das aktive Blatt auswertet und das Ergebnis als Variant zurückgibt
uses
System.Variants, lxHandleX;
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
Book.Sheets.Add('Data'); // Calculate wertet gegen das aktive Blatt aus
Writeln(VarToStr(Book.Calculate('="a-b">"ab"'))); // True: der Bindestrich bricht nur Gleichstände
Writeln(VarToStr(Book.Calculate('="a-b"="ab"'))); // False: Gleichstand gebrochen, nicht gleich
Writeln(VarToStr(Book.Calculate('="a~b"<"ab"'))); // True: Satzeichen zuerst
Writeln(VarToStr(Book.Calculate('="ABC"="abc"'))); // True: Groß-/Kleinschreibung ignoriert
finally
Book.Free;
end;
end;
Die Wortsortierung greift erst, wenn beide Operanden Text sind. Gemischte Typen folgen einer festen Rangordnung, in der jede Zahl unter jedem Textwert und jeder Textwert unter jedem Boolean liegt, wie in dem Artikel zu Vergleichsketten, leeren Operanden und SUMIF beschrieben. Wildcard-Kriterien wie "a*" oder "=ab" sind Muster- und Gleichheitstests mit eigenen Regeln, behandelt in der Anleitung zu Excel-Wildcards in COUNTIF, MATCH und DSUM; die hier besprochene Collation entscheidet über die Ordnungsoperatoren
Das nächste Beispiel lädt die 20 Wörter in Spalte A eines Blatts, prüft drei Kriterien-Zählungen und einen Lookup und sortiert dann die Spalte mit TXLSXWorksheet.SortRange. Die erwarteten Zählungen folgen direkt aus der weiter oben gezeigten Sortierfolge: elf Wörter sortieren hinter der ab-Gruppe, elf vor a-b, und die drei Mitglieder der ab-Gruppe kommen für >=AB zu den elf dazu
const
Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
#$00E9, 'e', 'f', 'Z');
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
i: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Words');
for i := 0 to High(Words) do
Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);
// Erwartet aus der Wortsortier-Reihenfolge: 11, 11, 14
Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));
// Findet abc: der Lookup vergleicht case-insensitiv wie Excel
Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
Book.Recalculate;
Writeln(VarToStr(Sheet.Cells[1, 3].Value)); // abc
// Eine Schlüsselspalte, aufsteigend: a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
Sheet.SortRange(1, 1, 20, 1, [1], [False]);
for i := 1 to 20 do
Writeln(VarToStr(Sheet.Cells[i, 1].Value));
finally
Book.Free;
end;
end;
SortRange fährt einen stabilen Merge-Sort, ab, AB und Ab, die gleich vergleichen, behalten also ihre relative Reihenfolge von vor der Sortierung. Leere Zellen rutschen bei aufsteigendem wie bei absteigendem Schlüssel ans Ende, genau dorthin, wo Excel sie ablegt
Wie treffe ich Excels Sortierordnung in meinem eigenen Delphi-Code?
Um Excels Textordnung außerhalb einer Arbeitsmappe zu reproduzieren, ruft man CompareStringW mit LOCALE_USER_DEFAULT und NORM_IGNORECASE auf und lässt SORT_STRINGSORT und NORM_IGNOREWIDTH weg. Ein Blick auf den Rückgabewert lohnt sich. Die API liefert kein signiertes Ergebnis, sondern CSTR_LESS_THAN (1), CSTR_EQUAL (2) oder CSTR_GREATER_THAN (3), und 0, wenn der Aufruf scheitert. Abzüglich 2 ergibt das die gewohnte negativ / null / positiv-Konvention, aber erst, nachdem der 0-Fall behandelt wurde, sonst wird aus einem fehlgeschlagenen Aufruf -2, ein stilles „kleiner“
uses
Winapi.Windows, System.SysUtils, System.Generics.Defaults,
System.Generics.Collections;
// Excels Textordnung: Wortsortierung der User-Locale, ohne Groß-/Kleinschreibung
function ExcelCompareText(const A, B: string): Integer;
var
R: Integer;
begin
R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
PWideChar(A), Length(A), PWideChar(B), Length(B));
if R = 0 then
RaiseLastOSError; // 0 ist ein Fehler, kein Vergleichsergebnis
Result := R - CSTR_EQUAL; // 1/2/3 werden -1/0/1
end;
var
Keys: TArray<string>;
begin
Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
TArray.Sort<string>(Keys, TComparer<string>.Construct(
function(const L, R: string): Integer
begin
Result := ExcelCompareText(L, R);
end));
// a~b, ab / AB (gleich, in beliebiger Reihenfolge), a-b, -ab, abc
end;
Wenn HotXLS ohnehin in der uses-Klausel steht, ist lxStandard.XlsCompareText öffentlich und liefert dieselbe Ordnung. Die beiden unterscheiden sich nur beim fehlgeschlagenen API-Aufruf: Die Funktion oben wirft eine Exception, während XlsCompareText auf einen Vergleich der großgeschriebenen Strings nach Codeeinheit ausweicht, damit eine Neuberechnung nie mittendrin abbricht. Für eine eigenständige Sortierung ist das Werfen die bessere Wahl, denn eine still falsche Ordnung ist schwerer aufzuspüren als eine Exception
TArray.Sort ist nicht stabil, gleiche Schlüssel wie ab und AB können also in beliebiger Reihenfolge herauskommen. Wenn die ursprüngliche Reihenfolge gleicher Schlüssel zählt, sortiert man ein Index-Array und benutzt die ursprüngliche Position als Sekundärschlüssel
Manchmal soll eine Spalte Excels Ordnung bewusst ignorieren. Teilenummern sind der Klassiker: X-100 und X100 sind verschiedene Codes und sollten nach Codepunkt sortieren. SortRange hat einen Overload, der ein TXLSSortCompareEvent nimmt, deklariert als function(const Left, Right: Variant): Integer of object, und ihn an Stelle des eingebauten Vergleichs benutzt
uses
System.SysUtils, System.Variants, lxStandard, lxHandleX;
type
TPartNumberOrder = class
function Compare(const Left, Right: Variant): Integer;
end;
function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
// Ein eigener Comparer bekommt auch leere Zellen (als Null): selbst einsortieren
if VarIsNull(Left) or VarIsNull(Right) then
Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
Result := CompareStr(VarToStr(Left), VarToStr(Right)); // ordinal, case-sensitiv
end;
var
Sheet: TXLSXWorksheet; // ein gefülltes Blatt, Zeilen 2..501, Spalten A..D
Order: TPartNumberOrder;
begin
// ...
Order := TPartNumberOrder.Create;
try
// keyed auf Spalte A, aufsteigend
Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
xlsSortExcelLike, Order.Compare);
finally
Order.Free;
end;
end;
Bei einem eigenen Comparer reicht SortRange die rohen Schlüsselwerte durch und überlässt die Platzierung der Leerzellen einem selbst, genau deshalb kümmert sich der Comparer oben zuerst um Null. Für einen absteigenden Schlüssel negiert die Sortierung, was der Comparer liefert, in dieser Richtung rutschen die Leerzellen also an den Anfang, sofern der Comparer dem nicht Rechnung trägt. Eine so sortierte Spalte liegt nicht mehr in der Ordnung, die ein approximativer VLOOKUP oder ein Binary-Search-XLOOKUP erwartet; was in diesen Modi auf anders sortierten Daten schiefgeht, behandelt die Anleitung zu den Binary-Search-Modi von XLOOKUP und XMATCH
Warum kann dieselbe Arbeitsmappe auf einer anderen Maschine anders sortieren?
Eine Arbeitsmappe kann auf einer anderen Maschine anders sortieren, weil Excels Textordnung von der Windows-User-Locale abhängt und HotXLS dieser Abhängigkeit bewusst folgt. Wortsortierung ist sprachspezifisch. Die schwedische Collation etwa stellt ä hinter z, während Englisch und Deutsch es neben a behalten, eine von der Kollegin in Stockholm neu berechnete Datei kann also ein anderes COUNTIF(...,">y") liefern als dieselbe Datei auf einem Desktop in Chicago. HotXLS reicht LOCALE_USER_DEFAULT durch, damit seine Antworten auf derselben Maschine denen von Excel gleichen. Jede fest gepinnte Locale sähe ordentlicher aus und würde HotXLS auf jeder anders konfigurierten Maschine von Excel abweichen lassen
Für die serverseitige Generierung hat das drei praktische Konsequenzen:
- Die Locale, die zählt, gehört zum Account, unter dem der Prozess läuft. Ein Windows-Dienst oder ein IIS-Application-Pool kann andere Regionaleinstellungen haben als der Entwickler-Desktop, was man in der IDE sieht, ist also nicht automatisch das, was die Produktion berechnet
- Zwischengespeicherte Formelergebnisse in der Datei spiegeln die Locale der generierenden Maschine. Excel berechnet mit seiner eigenen Locale neu, ein Wert kann sich also ändern, wenn die Datei anderswo geöffnet und neu berechnet wird; Excel verhält sich bei seinen eigenen Dateien genauso
- Locales uneinig sind sie vor allem bei akzentuierten Buchstaben, bei Buchstabenpaaren, die manche Sprachen als einen einzigen Buchstaben behandeln, und bei nicht-lateinischen Schriften. Testdaten aus schlichten englischen Wörtern decken den Unterschied nie auf, also ein paar Namen wie
ÅngströmundÖbergin die Stichprobe
Der Excel-konforme Pfad ist der Windows-Pfad: Jeder Win32- und Win64-Build, ob kompiliert mit Delphi, C++Builder oder Lazarus und Free Pascal, ruft dasselbe CompareStringW auf. Zeigt ein Build auf eine Nicht-Windows-Plattform, nutzt XlsCompareText das WideCompareText der RTL, dessen Ordnung aus der Collation-Bibliothek der jeweiligen Plattform kommt
Gewöhnliche Wörter sortieren unter jeder Regel gleich, deshalb überlebt eine falsche Collation den Test oft, bis Teilenummern mit Bindestrich, Satzeichen oder akzentuierte Kundennamen auftauchen. HotXLS liefert Excels Antwort auf solche Eingaben in beiden Engines, XLS wie XLSX, von den Vergleichsoperatoren bis hin zu SortRange. Unterstützte Delphi- und C++Builder-Versionen, Lizenzierung und den Trial-Download listet die HotXLS-Delphi-Excel-Component-Seite