HotXLS Excel wildcard'ları: COUNTIF, MATCH, DSUM ve Find

HotXLS Delphi Component aynı örüntü string'ini dört farklı biçimde okur; çünkü Excel 16 öyle yapar. COUNTIF ile SUMIF'te a~b metni bir literaldir, ölçüt ayrıca * ya da ? içermiyorsa; MATCH ve XLOOKUP wildcard modunda tilde daima bir kaçıştır, dolayısıyla a~b, ab'yi bulur; DSUM ve öteki database fonksiyonlarında düz metin "ile başlar" demektir; ve tüm hücreli Find son *'a geri dönmek zorundadır. HotXLS bu ölçülmüş kuralları v2.384.52, v2.384.60 ve v2.384.64'ten beri izler

Bu alandaki hata bildirimleri wildcard'lardan hiç söz etmez. Söylenen şudur: sunucuda üretilen bir rapor, aynı dosyanın Excel'de yeniden hesaplanışından birkaç satır eksik sayıyor; ya da tilde taşıyan bir parça numarasını bir formül buluyor, öbürü yok sayıyor. Sebep, bir örüntünün her yerde tek şey ifade ettiğini varsayan bir matcher'dır. Excel öyle çalışmaz; dolayısıyla cache'lenmiş sonuçları Excel'le anlaşmak zorunda olan bir motor da çalıştıramaz. v2.384.52 öncesinde HotXLS her ölçütü DOS tarzı bir dosya maskesinden geçiriyordu; gündelik örüntüleri doğru, uç durumları ise sessizce yanlış veriyordu

Bir örüntü string'i Excel'de neden dört farklı şey ifade eder?

Bir örüntü string'i dört farklı şey ifade eder, çünkü Excel dört eşleme kuralını dört özellikten devraldı ve hiçbir zaman bunları tekilleştirmedi. Ölçüt fonksiyonları (COUNTIF, SUMIF, AVERAGEIF ve *IFS ailesi), wildcard'ların hiç uygulanıp uygulanmayacağına ölçüt başına karar verir. Arama fonksiyonları (match tipi 0'lı MATCH, match_mode 2'li XLOOKUP) onları daima uygular. Database fonksiyonları (DSUM, DCOUNTA ve ahbapları) Advanced Filter'ı izler; orada çıplak bir kelime bir önektir. Find iletişim kutusunun kendi tüm-hücre ve kısmi modları vardır. Aşağıdaki tablo, a~b, ab, AB, abc, abcb, a*b ve axb tutan tek bir sütuna karşı her örüntünün hangi hücreleri eşlediğini listeler; her fonksiyon varsayılan harf duyarsız modunda

Örüntü	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mod 2	DSUM ölçütü	Find, tüm hücre, wildcard açık
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	COUNTIF ile aynı	her girdi, abc dâhil	COUNTIF ile aynı
a~b	yalnız a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	yalnız a*b	yalnız a*b	yalnız a*b	yalnız a*b
=ab	ab, AB	uygulanamaz	ab, AB	uygulanamaz

a~b satırı, COUNTIF ile MATCH'in anlaşmadığı satırdır ve parça numaraları ile elle yazılan kodlar, kimsenin beklemediğinden daha sık tilde taşır. a*b satırı öbür tuzağı gösterir: abc, DSUM için eşler ama COUNTIF için eşlemez; çünkü database fonksiyonu sessizce bir * ekler. ab, a*b ve =ab'in DSUM girdileri doğrudan Excel 16 koşularından gelir; a~b'nin DSUM girdisi aynı önek kuralından çıkar, çünkü eklenen *, ölçütü ~b'nin kaçırılmış bir b olduğu bir wildcard örüntüsüne çevirir

COUNTIF ne zaman wildcard moduna geçer?

COUNTIF yalnızca ölçüt metni kaçırılmış ya da kaçırılmamış bir * ya da ? içerdiğinde wildcard moduna geçer. O iki karakter de yokken Excel, ölçütü her hücreyle bütün bir string olarak, harfleri yok sayarak kıyaslar ve tilde sadece tilde'dir; dolayısıyla COUNTIF(A1:A7,"a~b"), a~b'yi harfi harfine tutan hücreyi sayar. Tek bir yıldız ekleyin, anlam tersine döner: "a~b*"'da tilde artık b'yi kaçırır, örüntü "ab ve ardından herhangi bir şey" okunur ve a~b hücresi artık sayılmaz. HotXLS bu kuralı v2.384.52'den beri iki motorda da uyguluyor; COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS ve database fonksiyonlarının paylaştığı lxCalc'teki tek bir ölçüt matcher'ı üzerinden

Wildcard modunun içindeki kaçış kuralları Excel'in gerisindeki her yerde olduğu gibidir: ~, ister ne olursa, sonraki karakteri literal yapar; ~b demek b demektir, ~~ bir tilde demektir ve örüntünün en sonundaki tilde düşer; dolayısıyla "a*~", "a*" gibi davranır. Köşeli parantezler hiçbir zaman özel değildir. "[x]" ölçütü, [x] üç karakterini tutan hücreleri sayar ve "[a-z]" sıradan veride hiçbir şey saymaz. TXLSXWorkbook.Calculate, bir formül string'ini aktif sayfaya karşı değerlendirir ve bir Variant döndürür; bu kuralları kendi verinizde denemenin en hızlı yoludur

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 ... SUMIF toplamı satırlarını adlandırır
    end;
    Sheet.Cells[8, 1].Value := 5;                // bir sayı; A9 boş kalır

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    * ya da ? yok: düz metin, a~b hücresi
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard mod: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    bütün-string wildcard, abc dışarıda
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  abc (8) dışındaki her satır
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    literal a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    5 sayısı ve boş A9 sayılır
    Show('=COUNTIF(A1:A9,"<>")');       // 8    boş olmayan hücreler
  finally
    Book.Free;
  end;
end.

"<>text" neyi sayar?

Bir "<>text" ölçütü, o metin olmayan her hücreyi sayar ve Excel 16'da buna sayılar, boolean'lar, hata değerleri ve boş hücreler dâhildir. Çıplak bir "<>" bambaşka bir sorudur: "boş olmayan hücre" demektir; dolayısıyla boş hücreleri atlar ama her değeri sayar, ="" gibi bir formülün döndürdüğü boş metin dâhil. Eski HotXLS kodu metin hücrelerini doğru alıyordu ama sayıları değil: bir Variant eşitsizliği, Delphi'yi 'ab''yi sayıya çevirtti, çevirim exception fırlattı, bir handler onu "eşleşme yok" diye yuttu ve sayısal hücreler sayımdan sessizce düştü. Bu hikâyenin boş hücre yanı; sıradan bir karşılaştırmada boş bir operandın neye eşit olduğu dâhil, HotXLS'in karşılaştırma zincirlerini, boş hücreleri ve SUMIF'i nasıl ele aldığında işlenir

a~b ararken MATCH neden ab'yi bulur?

a~b ararken MATCH ab'yi bulur, çünkü match tipi 0'lı MATCH ile match_mode 2'li XLOOKUP daima wildcard modundadır; dolayısıyla örüntü hiçbir * ya da ? içermese bile tilde bir kaçıştır. Excel 16 bunu, a~b ile ab tutan iki hücrelik bir aralıkta doğrular: MATCH("a~b",D1:D2,0) 2 döndürür ve yalnızca a~b tutan bir aralıkta aynı çağrı #N/A döndürür. Literal metin a~b'yi aramak için "a~~b" yazmak zorundasınız. O sırada aynı iki hücrede COUNTIF(D1:D2,"a~b") 1 döndürür, öbür hücreyi sayarak. Aynı string, aynı aralık, karşıt hücre

Bu yüzden HotXLS iki kararı, tek bir "örüntü eşle" giriş noktası ardına saklamak yerine ayrı tutar. Matcher'ın kendisi paylaşımlıdır: v2.384.52'den beri MATCH, XLOOKUP ve ölçüt fonksiyonları aynı geri dönen matcher'ı, aynı kaçış işlemesini ve aynı sondaki-tilde kuralını koşturur. Farklı olan önündeki kapıdır. Ölçüt yolu önce "bu metin * ya da ? içeriyor mu?" diye sorar; arama yolu hiç sormaz. İkisini birleştirmek bir aileyi düzeltip öbürünü kırardı ve iki yön de her iki motorda Excel 16 değerlerine karşı denetlenir. Wildcard aramalarının kendine ait bir ön koşulu daha vardır: XLOOKUP, wildcard eşlemesinin binary search moduyla birleşimini reddeder; kural, HotXLS'in XLOOKUP ve XMATCH arama modları rehberinde tanımlanır

DSUM ve database fonksiyonları düz metin bir ölçütü nasıl okur?

DSUM ve öteki database fonksiyonları, başında =, < ya da > taşımayan bir metin ölçütünü "ile başlar" diye okur; wildcard'lar hâlâ aktiftir. Advanced Filter kuralı budur ve COUNTIF'ten bilerek ayrılır. abc, ab, xab, AB, a~b ve a*b tutan bir Name sütunu üzerinden Excel 16 ölçüldü: ab ölçütü abc, ab ve AB'yi eşler; =ab yalnızca ab ile AB'yi; <>ab bütün-girdi bir eşitsizliktir; a*b ile a? de önek örüntüleridir; >ab sıradan bir karşılaştırmadır. v2.384.64 öncesinde HotXLS ab'yi tam eşliyordu; dolayısıyla o test verisi üzerinde bir DSUM, Excel'in 11 döndürdüğü yerde 10 döndürüyordu

Düzeltmenin koşul ayrıştırıcının etrafından dolanması gerekiyordu; o, ab ile =ab'yi aynı eşitlik koşuluna katlar. HotXLS bu yüzden ayrıştırılmış koşula güvenmeden önce ham ölçüt metnini inceler: ilk karakteri =, < ya da > olmayan bir metin ölçütüne bir * eklenir ve wildcard matcher'dan geçirilir; gerisi bütün-girdi karşılaştırmasını korur. Kodda ölçüt aralıkları kurarken pratik bir not: XLSX motorunda '=ab' string'ini TXLSXCell.Value'ya atamak metin saklar; klasik TXLSWorkbook motoru ise = ile başlayan bir değeri, başına kesme işareti koymadıkça formül olarak derler

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';            // D1'deki ölçüt başlığı
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // XLSX motorunda metin olarak kalır
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (ile başlar)
    // =ab  -> 6    ab, AB (bütün girdi)
    // <>ab -> 121  ab ve AB dışındaki her şey
    // a*b  -> 127  a*b* yediyi de eşler, abc dâhil
    // a~*  -> 32   yalnız literal a*b
  finally
    Book.Free;
  end;
end;

İlgili bir fark önek düzeltmesini aştı ve eski derlemelerde önem taşır. >ab gibi metin karşılaştırmaları kod noktası düzenini kullanıyordu; Excel ise noktalamayı harflerin önüne koyar, dolayısıyla "a~b">"ab" Excel'de FALSE'tur ve HotXLS'te TRUE'ydu. v2.384.67'den beri > ve < ölçütleri, sıradan metin karşılaştırması ve sıralamayla birlikte, geçerli kullanıcı locale'i altında Excel'in word-sort collation'ını kullanır ve ikisi yeniden anlaşır

Tüm hücreli Find neden abcb'yi kaçırdı?

Tüm hücreli Find abcb'yi, matcher örüntünün tükendiği ilk noktada durup son *'a geri dönmediği için kaçırdı. Replace'in ardındaki kısmi eşleme matcher'ı, örüntü tükendiği anda döner; tüm hücreli Find onu yeniden kullandı ve sonra eşlemenin tüm hücreyi kaplamasını istedi: abcb'ye karşı a*b, ab'den sonra durdu, 4 karakterden 2'sini tüketti ve reddedildi. v2.384.60'tan beri tüm hücreli matcher, "örüntü bitti, metin bitmedi" durumunu bir eşleşmeme daha sayıp son yıldızdan yeniden deneyen ayrı bir gerçeklemedir; böylece a*b, abcb'yi ve a?b*b, axbyb'yi eşler; Excel 16 Find'ın "Match entire cell contents" işaretliyken yaptığı gibi

Aynı sürüm tildeyi de değiştirdi. Excel 16 Find, hem tüm hücre hem kısmi modda ~'yi izleyen herhangi bir karakter için kaçış sayar: a~b, ab'yi bulur; a~~b, a~b'yi bulur ve sondaki tilde yok sayılır, dolayısıyla q~, q gibi davranır. Eski HotXLS matcher'ı yalnızca ~*, ~? ve ~~'yi kaçış olarak tanırdı; dolayısıyla a~b, a~b metnini buluyordu. Tek bir ~'den ibaret bir Find örüntüsü Excel'in kendisinde kararsızdır; boş bir örüntü gibi her hücreyi eşler ve HotXLS bunu taklit etmez

XLSX motorunda arama, bir TXLSXFindOptions setiyle TXLSXWorksheet.FindText'tir: lxfUseWildcards *, ? ve ~'yi açar, lxfWholeCell tüm hücrenin eşleşmesini ister ve lxfMatchCase karşılaştırmayı harf duyarlı yapar. lxfUseWildcards olmadan her karakter, yıldız dâhil, literaltır. Find yalnızca metin değerlerine bakar; sayısal hücreler atlanır ve lxfSearchFormulas set edilmedikçe formül hücreleri de atlanır; set edilirse formül metni aranır. StartRow ile StartCol'in verdiği çapa dâhildir; dolayısıyla bir Find All döngüsü her isabetten bir sütun sonrasına adımlar

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 reddedildi, abcb geri döner
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b kaçırılmış bir b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ tek bir literal tilde

    // Kısmi eşleme, Find All: çapa hücre dâhildir, her isabeti geçin
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // satır 1, 2, 3 ve 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Tüm hücreli wildcard replace yalnızca literal a~b'yi yeniden yazar
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Kısmi döngü, abc dâhil dört satırın hepsini bulur; çünkü kısmi modda a*b'nin hücrenin içinde bir yerde geçmesi yeterlidir. FindTextIn ile ReplaceTextIn, aynı seçenekleri artı bir FirstRow, FirstCol, LastRow, LastCol penceresi alır; seçim içinde aramanın programatik karşılığıdır. Klasik motor aynı kuralları üç boolean'lı bir overload üzerinden açar: TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), artı eşleşen bir ReplaceText overload'u; sonuçlar 1 tabanlı satır ve sütun:

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;

Eski DOS-maskeli matcher neyi yanlış yaptı?

Eski matcher özel karakterleri yanlış işliyordu; çünkü DOS dosya maskesi, Excel wildcard'ından başka bir dildir. v2.384.52 öncesinde ölçüt fonksiyonları ile database fonksiyonları her örüntüyü lxMasks unit'indeki bir dosya-maskesi matcher'ı olan MatchesMask'a veriyordu. Söz dizimi sıradan durumlarda Excel'inkiyle örtüşür; sorunun gizli kalmasının nedeni budur ama gerçek verinin ilginçleştiği yerde ayrışır:

• [x], bir karakter seti diye okunuyordu; dolayısıyla COUNTIF(A1:A10,"[x]"), köşeli parantezli metin yerine x tutan hücreleri sayıyor ve "[a-z]", tek harfli her hücreyi eşliyordu
• Tilde kaçışı yoktu; dolayısıyla "a~*b", literal bir yıldızı eşleyemiyordu
• Kapatılmamış bir parantez gibi hatalı kurgulu bir maske, çağıranın "eşleşme yok" diye yuttuğu bir exception fırlatıyordu; ölçütteki bir yazım hatası sessizce yanlış bir toplam oluyordu
• Arama tarafında MATCH ile XLOOKUP yalnızca ~*, ~? ve ~~'yi kaçış sayıyordu; dolayısıyla MATCH("a~b",…,0), ab yerine literal a~b'yi buluyordu

Çalışma kitaplarınız sade alfanumerik veri üzerinde yalnızca * ve ? kullandıysa sonuçlar çoktan doğrudur ve değişmeyecektir. Köşeli parantezler, tildeler, "<>text" altında karışık tipli sütunlar ya da çıplak kelime olarak yazılmış DSUM ölçütleri taşıyorlarsa, onları v2.384.64 ve sonrasıyla yeniden hesaplamak toplamları değiştirebilir ve yeni toplamlar, Excel'in gösterdikleridir. Excel'in bir ölçütü nasıl sakladığı ile nasıl kıyasladığı ayrımı, kayıtlı filtreler için de gündeme gelir; HotXLS'in BIFF8 AutoFilter DOPER ölçütleri yazısında işlenir

Hızlı başvuru: HotXLS'te Excel wildcard kuralları

• COUNTIF, SUMIF, AVERAGEIF ve *IFS ailesi, wildcard'ları yalnızca ölçüt * ya da ? içerdiğinde kullanır; aksi hâlde bütün string'leri harf duyarsız kıyaslar ve ~ literaltır (v2.384.52'den beri)
• Match tipi 0'lı MATCH ile match_mode 2'li XLOOKUP daima wildcard kullanır; dolayısıyla a~b, ab'yi bulur ve literal için a~~b gerekir (v2.384.52'den beri)
• Wildcard modunda ~, izleyen herhangi bir karakteri kaçırır ve sondaki ~ düşer; [ ile ] sıradan karakterlerdir
• "<>text", sayıları, boolean'ları, hataları ve boş hücreleri sayar; çıplak bir "<>", boş olmayan hücreleri sayar, ="" sonuçları dâhil
• DSUM ve öteki database fonksiyonları düz metni "ile başlar" sayar; =text ile <>text bütün girdiyi kıyaslar (v2.384.64'ten beri)
• lxfUseWildcards ile lxfWholeCell'li tüm hücreli Find geri döner; dolayısıyla a*b, abcb'yi eşler; Find ile Replace, ~'yi herhangi bir karakterin kaçışı sayar (v2.384.60'tan beri)
• > ve < ölçütlerinde metin düzeni, Excel'in word-sort collation'ını izler; noktalama harflerden önce (v2.384.67'den beri)

Bir formül motorunda Excel uyumluluğu çoğunlukla böylesi uç durumlardır; dokümantasyondan tahmin değil, Excel'e karşı ölçülür. HotXLS, COUNTIF, MATCH, XLOOKUP, DSUM ve fonksiyon kütüphanesinin gerisini Delphi ve C++Builder'da, klasik motor ile XLSX motorunun ikisinde de, Excel kurulu olmadan yerel olarak değerlendirir. Ayrıntılar, sürümler ve deneme indirmesi HotXLS Delphi spreadsheet component sayfasında