Excel'de ="a-b">"ab" TRUE döndürür; ="a~b"<"ab" de TRUE döndürür. Aynı çiftleri Delphi'nin CompareText'ine verdiğinizde iki yanıt da tersine döner; çünkü kod noktası düzeninde kısa çizgi harflerin altında, tilda ise üstünde yer alır. Excel metni, Windows kullanıcı locale'inin büyük-küçük harf duyarsız "word sort"u ile karşılaştırır; burada kısa çizgi ve kesme işareti yalnızca eşitlik bozar, öteki tüm noktalama işaretleri rakamlardan ve harflerden önce gelir
Belirtiler çoğu zaman collation'dan söz etmez. Sunucuda hesaplanan bir COUNTIF(A:A,">M"), Excel'deki aynı formülün bulduğundan iki satır fazla sayar. Raporlama servisinin sıraladığı bir fiyat listesi X-100'ü Excel'in koymayacağı bir yere koyar. VLOOKUP("ABC",...), sütunda açıkça abc dururken #N/A döndürür. Üçü de tek bir soruya indirgenir: iki taraf da metinken hangisi küçük? Excel'in net bir yanıtı vardır ve Delphi kodunun varsayılan olarak verdiği yanıt çoğu zaman o değildir
Excel iki metin string'ini karşılaştırmak için hangi kuralı kullanır?
Excel iki metin değerini, geçerli Windows kullanıcı locale'inin word sort'u ile, büyük-küçük harfi yok sayarak karşılaştırır. Word sort, Windows NLS karşılaştırma fonksiyonlarının varsayılan collation'ıdır: harfler kod noktasına göre değil dilbilgisel sıraya göre kıyaslanır; dolayısıyla é, e ile f arasına düşer ve büyük-küçük harf birbirine katlanır. İki karakter özel işlem görür. Kısa çizgi - ile kesme işareti ' ilk turda atlanır; bu yüzden co-op ile coop yan yana düşer ve geri kalan karakterler eşitlendiğinde sırayı onların varlığı ve konumu belirler. Öteki tüm noktalama tam ağırlıkla sayılır ve rakamlardan, rakamlar da harflerden önce sıralanır
Tablo, IF(A<B,...) için Excel'in verdiği kararı, bir Delphi geliştiricisinin en önce yazması muhtemel iki karşılaştırmayla yan yana koyar
| A vs B | Excel / HotXLS | CompareStr (ordinal) | CompareText |
|---|---|---|---|
"a-b" vs "ab" | büyük | küçük | küçük |
"a'b" vs "ab" | büyük | küçük | küçük |
"a~b" vs "ab" | küçük | büyük | büyük |
"a_b" vs "ab" | küçük | küçük | büyük |
"ab" vs "AB" | eşit | büyük | eşit |
"é" vs "f" | küçük | büyük | büyük |
"Z" vs "f" | büyük | küçük | büyük |
İki sonucu kaçırmak kolaydır. Kısa çizgi yalnızca eşitlik bozduğundan ="a-b"="ab" FALSE'tur: iki string sıralamada bitişik komşudur ama eşit değildir. Ve harf durumu tamamen yok sayıldığından ab, AB ve Ab, her karşılaştırma operatörü için tek anahtardır. İngilizce kullanıcı locale'inde 20 çetrefilli kelimelik bir sütun şöyle sıralanır: 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. ab grubunun içinde sırayı atlanan karakterin konumu belirler: sondaki kısa çizgi önce, baştaki en sona
Collation'ı Excel'de kendiniz doğrulamak
Excel'in dokümantasyonu kullandığı collation'ı hiç adıyla anmaz; kendi makinenizde emin olmanın güvenilir yolu, bir yığın string çiftini Excel'e kararlaştırtıp verdiği kararları CompareStringW'nin farklı bayrak setleri altındaki kararlarıyla kıyaslamaktır. Çiftleri iki sütuna koyun, Excel'e her satır için =IF(A1<B1,-1,IF(A1=B1,0,1)) hesaplatın, aynı çiftleri API'den geçirin. Üretilmeye değer çiftler; ASCII noktalaması, rakamlar, iki harf durumu, boşluklar, é, ß, ä gibi aksanlı harfler, CJK karakterleri, tam genişlikli biçimler ve bölünemez boşluk içerir; tek karakter farkla ayrılan near-miss çiftleri bol bol olsun. Sözlükten seçilmiş rastgele kelimeler zayıf bir örnektir; düz harfler bu yazıdaki her kural altında aynı sıralanır
Bayrak setleri birbirinden belirgin biçimde farklı sonuçlar verir:
- Yalnız
NORM_IGNORECASE,LOCALE_USER_DEFAULTile, düz word sort'tur ve Excel'in izlediği kural budur SORT_STRINGSORTeklemek string sort'a geçirir; kısa çizgiyle kesme işareti tam ağırlıklı sıradan simge olur vea-b/abtarzı her çift ters yönde çıkarNORM_IGNOREWIDTHeklemek, tam genişlikli harfi yarım genişlikli biçimiyle eşitler; Excel bu ikisini ayrı tutar
Bir tuzak da test düzeneksinin kendisinde oturur. Excel, hücreye başa yazılan kesme işaretini "metin olarak sakla" öneki sayar; dolayısıyla girdisi tamamen ' olan bir hücre boş kalır ve bu yolla kurulmuş her çift, collation'la hiç ilgisi olmayan bir uyuşmazlık bildirir. Böyle çiftleri formülde string literaliyle kurun (="'") ya da tamamen atlayın
CompareText ile ordinal karşılaştırma neden yanlış yapar?
CompareText ile ordinal karşılaştırma, Excel'in sırasını şünden ıskalar: UTF-16 kod birimlerini kıyaslarlar ve kod noktası düzeni noktalamayı alfabenin çeşitli yerlerine serper. Kısa çizgi U+002D, kesme işareti U+0027'dir; ikisi de her harfin altındadır, dolayısıyla ordinal karşılaştırma "a-b"'yi "ab"'den küçük sayar; oysa Excel kısa çizgiyi eşitlik bozucu tutar. Tilda U+007E ile her harfin üstündedir; bu da "a~b"'yi büyütür, Excel'in tam tersi. CompareText üstüne ikinci bir bozulma daha ekler: yalnız a..z aralığını büyük harfe katlar, sonra kod birimlerini kıyaslar; alt çizgi (U+005F) büyük harf ile küçük harf aralıklarının arasına düştüğünden, katlama "a_b"'yi "ab"'nin altından üstüne taşır. Ne é'nin e ile f arasına gittiğini ikisi de bilmez
Alışılmış Delphi araçları çizginin iki yanına da düşer:
CompareStr, string<operatörü veTComparer<string>.Defaultordinal ve harf duyarlıdır; dolayısıyla comparersızTArray.Sort<string>Z'yif'den önce koyarCompareTextveSameText, yalnız-ASCII harf katlamasından sonra ordinaldır- Windows'ta Delphi RTL'sindeki
AnsiCompareTextveWideCompareText,CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)çağırır; Excel'le uyuşan çağrı budur. Varsayılanlarıyla (UseLocaleTrue,CaseSensitiveFalse) sıralı birTStringListdeAnsiCompareText'ten geçer ve Excel'le aynı fikirdedir - POSIX hedeflerinde Delphi RTL'si
AnsiCompareText'i bir ICU collator'üne yollar; farklı bir algoritmadır, noktalama kuralları da farklıdır. Free Pascal'ın Windows'takiAnsiCompareText'i ANSI kod sayfasına çeviripCompareStringAçağırır; o kod sayfasının temsil edemediği her karakter kaybolur
Demek ki locale duyarlı RTL fonksiyonları Windows'ta bugünkü gerçeklemeleri yüzünden doğru çalışıyor; sözleşmeden değil. Excel'le uyuşması gereken kod, API çağrısını açıkça yapmalı ve bunu belirtmeli. Asıl zor problem, bir formül motorunda her kod yolunun elinin altındaki karşılaştırmayı kapmasıdır: = ve < operatörlerinde büyük harfe çevirip kod noktası kıyası, COUNTIF ölçütünün > / < kolunda harf duyarlı Variant kıyası, VLOOKUP içinde başka bir tane (VLOOKUP("ABC",A1:A20,1,FALSE)'un abc'yi bulamaması tam da buradan gelir), aralık sıralamasında ise locale çağrısı. Her yol tek başına savunulabilir. Bir arada, aynı sütun üzerinde üç ayrı düzen üretirler
HotXLS'te formüllerin, aramaların ve sıralamanın arkasında tek karşılaştırma
HotXLS, hesaplama ve sıralama kodundaki metin-metne her karşılaştırmayı tek bir fonksiyondan geçirir: lxStandard.pas'taki XlsCompareText, CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) çağırıp CSTR_EQUAL'i çıkarır. Onu kullananlar: altı karşılaştırma operatörü, dizi formüllerinde eleman bazlı karşılaştırmalar, COUNTIF tarzı ölçütlerin ve veritabanı fonksiyonlarının >, <, >= ve <= kolları, tam ve yaklaşık kipte VLOOKUP ile HLOOKUP, dinamik dizi fonksiyonlarının ve XLOOKUP / XMATCH'in arkasındaki sıralama yardımcıları, ayrıca TXLSWorksheet ve TXLSXWorksheet üzerindeki SortRange. Aralık sıralamasını aynı fonksiyona bağlamak tasarımın belirleyici noktasıydı. Metin üzerinde yaklaşık VLOOKUP, ancak sütun aramanın kıyasladığı düzende sıralıysa anlamlı sonuç verir; tek fonksiyon paylaşımı bu iki düzenin birbirinden kopmasını imkânsız kılar
Kuralı Delphi'den görmenin en hızlı yolu TXLSXWorkbook.Calculate'tir; aktif sayfaya karşı bir formül string'ini değerlendirir ve sonucu Variant olarak döndürür
uses
System.Variants, lxHandleX;
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
Book.Sheets.Add('Data'); // Calculate aktif sayfaya karşı değerlendirir
Writeln(VarToStr(Book.Calculate('="a-b">"ab"'))); // True: kısa çizgi yalnızca eşitlik bozar
Writeln(VarToStr(Book.Calculate('="a-b"="ab"'))); // False: eşitlik bozuldu, eşit değiller
Writeln(VarToStr(Book.Calculate('="a~b"<"ab"'))); // True: noktalama önce
Writeln(VarToStr(Book.Calculate('="ABC"="abc"'))); // True: harf durumu yok sayıldı
finally
Book.Free;
end;
end;
Word sort, iki taraf da metin olduktan sonra devreye girer. Karışık tipler sabit bir sıralamayı izler: her sayı her metin değerinin altında, her metin değeri her boolean'ın altındadır; ayrıntı karşılaştırma zincirleri, boş operandlar ve SUMIF yazısındadır. "a*" ya da "=ab" gibi joker ölçütler ise kendine kuralları olan örüntü ve eşitlik testleridir; onlar COUNTIF, MATCH ve DSUM'da Excel jokerleri kılavuzunda işlenir, burada konuştuğumuz collation yalnızca sıralama operatörlerine hükmeder
Sonraki örnek, 20 kelimeyi bir sayfanın A sütununa yükler, üç ölçüt sayımı ve bir arama yapar, sonra sütunu TXLSXWorksheet.SortRange ile sıralar. Beklenen sayımlar, yukarıda gösterilen sıralı düzenden doğrudan çıkar: on bir kelime ab grubundan sonra, on bir kelime a-b'den önce sıralanır; >=AB için ab grubunun üç üyesi de on birli gruba katılır
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]);
// Word-sort düzeninden beklenen: 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")')));
// abc bulur: arama harf duyarsız kıyaslar, tıpkı Excel gibi
Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
Book.Recalculate;
Writeln(VarToStr(Sheet.Cells[1, 3].Value)); // abc
// Tek anahtar sütun, artan: 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 kararlı bir merge sort çalıştırır; dolayısıyla kıyasça eşit olan ab, AB ve Ab, sıralamadan önceki göreli düzenlerini korur. Boş hücreler, anahtar artan da azalan da olsa alta gider; Excel de onları oraya koyar
Kendi Delphi kodumda Excel'in sıralama düzenini nasıl eşlerim?
Excel'in metin düzenini çalışma kitabı dışında yeniden üretmek için CompareStringW'yi LOCALE_USER_DEFAULT ve NORM_IGNORECASE ile çağırın; SORT_STRINGSORT ile NORM_IGNOREWIDTH'i eklemeyin. Dönüş değerine dikkat. API işaretli bir sonuç döndürmez; CSTR_LESS_THAN (1), CSTR_EQUAL (2) ya da CSTR_GREATER_THAN (3) döndürür, çağrı başarısızsa 0. İkiden çıkarmak alışılmış negatif / sıfır / pozitif kuralını verir; ama önce 0 durumu ele alınmalı, yoksa başarısız çağrı -2'ye dönüşür ve "küçük" diye okunur
uses
Winapi.Windows, System.SysUtils, System.Generics.Defaults,
System.Generics.Collections;
// Excel'in metin düzeni: kullanıcı-locale word sort, harf duyarsız
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 başarısızlıktır, karşılaştırma sonucu değil
Result := R - CSTR_EQUAL; // 1/2/3, -1/0/1 olur
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 (eşit, herhangi bir sırada), a-b, -ab, abc
end;
HotXLS zaten uses satırındaysa lxStandard.XlsCompareText herkese açıktır ve aynı düzeni döndürür. İkisi yalnızca başarısız API çağrısında ayrışır: yukarıdaki fonksiyon raise eder, XlsCompareText ise string'leri büyük harfe çevirip kod birimi düzeyinde kıyaslayarak sürer; böylece bir yeniden hesaplama yarım yolda kesilmez. Bağımsız bir sıralama için raise etmek daha iyidir; sessizce yanlış bir düzen, bir istisnadan daha zor izlenir
TArray.Sort kararlı değildir; dolayısıyla ab ile AB gibi eşit anahtarlar her iki sırada da çıkabilir. Eşit anahtarların özgün sırası önemliyse, bir indeks dizisini sıralayın ve özgün konumu ikincil anahtar yapın
Bazen bir sütunun bilerek Excel'in düzenini yok sayması gerekir. Klasik örnek parça numaralarıdır: X-100 ile X100 farklı kodlardır ve kod noktasına göre sıralanmalıdır. SortRange'in TXLSSortCompareEvent alan bir overload'u vardır; function(const Left, Right: Variant): Integer of object imzalı bu metot, gömülü karşılaştırmanın yerine kullanılır
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
// Özel bir comparer boş hücreleri de alır (Null olarak): yerleştirmeyi siz yapın
if VarIsNull(Left) or VarIsNull(Right) then
Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
Result := CompareStr(VarToStr(Left), VarToStr(Right)); // ordinal, harf duyarlı
end;
var
Sheet: TXLSXWorksheet; // dolu bir sheet, satır 2..501, sütun A..D
Order: TPartNumberOrder;
begin
// ...
Order := TPartNumberOrder.Create;
try
// A sütununa göre anahtarlı, artan
Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
xlsSortExcelLike, Order.Compare);
finally
Order.Free;
end;
end;
Özel bir comparer verildiğinde SortRange ham anahtar değerlerini olduğu gibi geçer ve boş yerleşimini size bırakır; yukarıdaki comparer ilk iş Null'u ele aldığı için. Azalan anahtarda sıralama, comparer'ın döndürdüğünü ters çevirir; o yönde boş hücreler, comparer önlem almadıkça en üste çıkar. Bu yolla sıralanmış bir sütun, artık yaklaşık VLOOKUP'un ya da binary-search XLOOKUP'un beklediği düzende değildir; farklı düzende sıralanmış veride bu kiplerde ne ters gider, XLOOKUP ve XMATCH binary search kipleri kılavuzunda işlenir
Aynı çalışma kitabı başka bir makinede neden farklı sıralanabilir?
Bir çalışma kitabı başka makinede farklı sıralanabilir; çünkü Excel'in metin düzeni Windows kullanıcı locale'ine bağlıdır ve HotXLS bu bağımlılığı bilerek izler. Word sort dile özgüdür. Örneğin İsveççe collation, ä'yı z'den sonra koyar; İngilizce ve Almanca ise onu a'nın yanında tutar. Dolayısıyla Stockholm'deki bir meslektaşın yeniden hesapladığı dosya, Chicago'daki masaüstündeki aynı dosyadan farklı bir COUNTIF(...,">y") döndürebilir. HotXLS, aynı makinede Excel'le birebir aynı sonucu versin diye LOCALE_USER_DEFAULT geçirir. Sabit bir locale'e çakmak daha düzgün görünür ama HotXLS'i, ayarı farklı her makinede Excel'le çelişkiye sokar
Sunucu tarafı üretim için bunun üç pratik sonucu var:
- Sayılan locale, prosesin altında koştuğu hesabın locale'idir. Bir Windows servisinin ya da IIS uygulama havuzunun bölgesel ayarları geliştiricinin masaüstünden farklı olabilir; IDE'de görülen sonuçlar, production'ın hesaplayacağı sonuçlar olmak zorunda değildir
- Dosyaya yazılan önbelleklenmiş formül sonuçları, üreten makinenin locale'ini yansıtır. Excel kendi locale'iyle yeniden hesaplar; dosya başka yerde açılıp yeniden hesaplandığında bir değer değişebilir. Excel kendi dosyalarında da aynı şekilde davranır
- Locale'ler çoğunlukla aksanlı harflerde, bazı dillerin tek harf saydığı harf çiftlerinde ve Latin olmayan alfabelerde ayrışır. Düz İngilizce kelimelerden kurulu test verisi farkı asla ortaya çıkarmaz; örnek veriye
ÅngströmveÖberggibi birkaç isim koyun
Excel'le uyuşan yol Windows'takidir: Delphi, C++Builder, Lazarus ve Free Pascal ile derlenen her Win32 ve Win64 build, aynı CompareStringW'yi çağırır. Build Windows dışı bir platformu hedeflediğinde XlsCompareText, RTL'nin WideCompareText'ini kullanır; onun düzeni o platformun kendi collation kütüphanesinden gelir
Sıradan kelimeler her kural altında aynı sıralanır; yanlış bir collation bu yüzden testten sağ çıkarak, kısa çizgili parça kodları, noktalama ya da aksanlı müşteri adları gelene dek yaşar. HotXLS bu girdilerde Excel'in yanıtını verir, XLS ve XLSX motorlarının ikisinde de, karşılaştırma operatörlerinden SortRange'e kadar. Desteklenen Delphi ve C++Builder sürümleri, lisanslama ve deneme indirmesi HotXLS Delphi Excel component sayfasındadır