Teknik Makale

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

D1'de a~b, D2'de ab varken =COUNTIF(D1:D2,"a~b") 1 döndürür ve saydığı hücre D1'dir. =MATCH("a~b",D1:D2,0) ise 2 döndürür; bu da D2'yi gösterir. Aynı örüntü dizisi, aynı iki hücre; yine de Excel, örüntüyü okuyan işleve göre başka bir hücre seçer. Önbellek sonuçlarının Excel'le uyuşmasını isteyen bir elektronik tablo motoru bu ayrımı ve iki kuralı daha yeniden üretmek zorundadır: DSUM gibi database fonksiyonları çıplak metni "ile başlar" sayar ve tüm hücreli Find son *'a geri dönmek zorundadır

Bununla karşılaşan hata raporları wildcard'ı ender anar. Sunucuda üretilmiş bir çalışma kitabının, aynı dosya masaüstünde yeniden hesaplandığında iki satır fazla saydığını ya da tilde içeren bir parça numarasını bir formülün bulup öbürünün yok saydığını söylerler. Her örüntüyü DOS tarzı bir dosya maskesinden geçirmek günlük durumları doğru tutar; sapma ancak gerçek ürün kodları ~ ya da [ içermeye başlayınca görünür olur

Tek örüntü dizisi, dört Excel kural kümesi

Excel eşleme kurallarını hiçbir zaman tekilleştirmedi; her özellik kendi kuralını getirdi. Ölçüt fonksiyonları (COUNTIF, SUMIF, AVERAGEIF ve *IFS ailesi) her ölçüt için wildcard'ların hiç uygulanıp uygulanmayacağına karar verir. Arama fonksiyonları (eşleme tipi 0 olan MATCH, match_mode 2 olan XLOOKUP) bunları daima uygular. Database fonksiyonları (DSUM, DCOUNTA ve gerisi) Advanced Filter kuralını devralır; orada çıplak bir sözcük önektir. Find iletişim kutusunun da kendi tüm-hücre ve kısmi modları vardır. HotXLS Delphi Component her aileyi kendi kuralıyla, hem klasik motorda hem XLSX motorunda değerlendirir. Tablo, a~b, ab, AB, abc, abcb, a*b ve axb tutan tek bir sütuna karşı her örüntüyle hangi hücrelerin eşleştiğini listeler; her fonksiyon varsayılan büyük/küçük harf duyarsız modundadır

ÖrüntüCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP mod 2DSUM ölçütüFind, tüm hücre, wildcard açık
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbCOUNTIF ile aynıher girdi, abc dâhilCOUNTIF ile aynı
a~byalnız a~bab, ABab, AB, abc, abcbab, AB
a~*byalnız a*byalnız a*byalnız a*byalnız a*b
=abab, ABuygulanamazab, ABuygulanamaz

İki satır ikinci bir bakışı hak eder. a~b satırında COUNTIF ile MATCH başka hücrelere düşer ve elle yazılan kodlar, kimsenin planladığından çok daha sık tilde içerir. a*b satırında abc, DSUM için eşleşir, COUNTIF için başarısız olur; çünkü database fonksiyonu ölçüte sessizce bir * ekler. a~b'nin DSUM satırı da aynı eklenen yıldızdan gelir: yıldız oradayken ölçüt bir wildcard örüntüsüdür ve ~b yalnızca kaçırılmış bir b'dir

COUNTIF ne zaman wildcard moduna geçer?

COUNTIF, yalnızca ölçüt metni * ya da ? içerdiğinde — kaçırılmış olsun olmasın — wildcard moduna geçer. Bu karakterlerin ikisi de yokken ölçüt her hücreyle bütün dizgi olarak, büyük/küçük harf duyarsız biçimde karşılaştırılır ve tilde sıradan bir karakterdir; bu yüzden COUNTIF(A1:A7,"a~b") a~b'yi harfi harfine tutan hücreyi sayar. Tek bir yıldız anlamı çevirir. "a~b*" içinde tilde artık b'yi kaçırır, örüntü "ab ve ardından herhangi bir şey" olarak okunur ve a~b hücresi sayım dışı kalır. HotXLS bu kapıyı COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS ve database fonksiyonlarının hepsine aynı biçimde uygular

HotXLS wildcard kapısı şeması: COUNTIF ile SUMIF wildcard'ları yalnızca ölçüt bir yıldız ya da soru işareti içerdiğinde uygular, dolayısıyla a~b literal hücreyi sayar ve 1 döndürür; MATCH tip 0 ile XLOOKUP mod 2 ise daima wildcard modundadır, dolayısıyla a~b, 2 konumundaki ab'yi bulur
Farkın tamamı kapıda: COUNTIF bir tildeyi kaçış saymadan önce yıldız ya da soru işareti sorar, MATCH hiç sormaz; dolayısıyla tek bir örüntü string'i bir hücreyi sayar ve öbürünü bulur

Wildcard modunun içinde kaçış kuralı kısadır. ~, ardından gelen karakteri ne olursa olsun literal yapar; ~b demek b demektir, ~~ demek tek tilde demektir; örüntünün en sonundaki tilde düşer, bu yüzden "a*~" davranışta "a*" ile aynıdır. Köşeli ayraçlar asla özel değildir. "[x]" ölçütü [x] üç karakterini tutan hücreleri sayar ve "[a-z]" sıradan veride hiçbir şey saymaz. Tüm bunları kendi verinizle sınamanın en hızlı yolu TXLSXWorkbook.Calculate'dir; etkin sayfaya karşı bir formül dizisi değerlendirir ve Variant döndürür; yeni bir çalışma kitabı, ilk eklenen sayfasını etkin yapar

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');            // ilk sayfa, dolayısıyla etkin
    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" aslında neyi sayar?

"<>text" ölçütü o metin olmayan her hücreyi sayar ve Excel'de buna sayılar, boole değerleri, hata değerleri ve boş hücreler dahildir. Çıplak "<>" bambaşka bir şey sorar: "boş olmayan hücre". Boş hücreleri atlar ama her değeri sayar; ="" gibi bir formülün döndürdüğü boş metin dâhil. Delphi tuzağı Variant karşılaştırmasında oturur. 'ab' değerini sayısal bir hücreyle karşılaştıran Delphi, 'ab''yi sayıya çevirmeye çalışır, çevirim istisna oluşturur ve istisnayı "eşleşme yok" diye yutan bir işleyici sayısal hücreleri sayım dışına atar. HotXLS onları sayar; bu yüzden örneğin son iki satırı 7 ve 8 döndürür. Bunun boş hücre tarafı, boş bir işlenenin sıradan bir karşılaştırmada 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 ele alınır

a~b ararken MATCH neden ab'yi bulur?

Eşleme tipi 0 ile MATCH ve match_mode 2 ile XLOOKUP daima wildcard modundadır; tilde, örüntü * ya da ? içermese bile bir kaçıştır. Girişteki iki hücreli aralıkta MATCH("a~b",D1:D2,0) 2 döndürür. Yalnızca a~b tutan bir aralıkta aynı çağrı #N/A döndürür. Literal a~b metnini aramak "a~~b" ister. Arama anahtarları kullanıcı girdisinden ya da dış bir sistemden geliyorsa ve tilde içerebiliyorsa, formülü kurmadan önce her tildeyi ikileyin; sonradan eksik bir satırı açıklamaya çalışmaktan ucuzdur o tek StringReplace

HotXLS bu iki kararı ayrı tutar. MATCH, XLOOKUP ve ölçüt fonksiyonları tek bir geri dönüşlü eşleyici paylaşır; aynı kaçış işlemesi, aynı sondaki-tilde kuralı. Farklı olan önündeki kapıdır. Ölçüt yolu önce metnin * ya da ? içerip içermediğini sorar. Arama yolu hiç sormaz. İki aile tarafından da kullanılan tek bir "örüntü eşle" giriş noktası, kapı hangi tarafa kurulsa bir aileyi doğru öbürünü yanlış yapardı. Wildcard aramalarının bir koşulu daha vardır: XLOOKUP, match_mode 2 ikili arama moduyla birleştirildiğinde #VALUE! döndürür; bu, XLOOKUP ve XMATCH arama modları üzerine HotXLS kılavuzunda ele alınır

DSUM ölçütleri: çıplak metin "ile başlar" demektir

DSUM ve öbür database fonksiyonları, başında =, < ya da > olmayan bir metin ölçütünü, wildcard'lar hâlâ etkinken önek olarak okur. Bu Advanced Filter kuralıdır ve COUNTIF'ten bilerek farklıdır. abc, ab, xab ve AB tutan bir Name sütununda ab ölçütü abc, ab ve AB ile eşleşir; =ab ise yalnızca ab ve AB ile eşleşir. <>ab bütün-girdi eşitsizliğidir, a*b ile a? de önek örüntüsüdür ve >ab sıradan bir metin karşılaştırmasıdır. xab asla ab ile eşleşmez; kural "ile başlar"dır, "içerir" değil

Uygulamayı zorlayan kısım şudur: işleci kırpan bir ölçüt ayrıştırıcı, ab ile =ab'yi aynı eşitlik sınaması görür. İkisini ayıran yalnızca ham ölçüt metnidir. HotXLS bu yüzden ayrıştırılmış koşula güvenmeden önce o ham metne bakar: ilk karakteri =, < ya da > olmayan bir metin ölçütüne bir * eklenir ve wildcard eşleyiciden geçirilir; gerisi bütün-girdi karşılaştırmasını korur

DSUM ölçüt kuralının HotXLS şeması: çıplak bir metin ölçütüne yıldız eklenir ve önek olarak eşler, böylece ab, ab, AB, abc ve abcb'ye ulaşır; eşittir ab bütün girdiyi kıyaslar, açılı ayraçlı ab ikisini de dışlar ve tilde yıldız, literal a*b olarak kalır; ölçülen DSUM toplamları 30, 6, 121 ve 32
Excel, database fonksiyonları için Advanced Filter kuralını devraldı: çıplak metin "ile başlar" demektir; baştaki eşittir ya da eşit-değildir işareti bütün girdiyi kıyaslar; HotXLS, ayrıştırılmış koşula güvenmeden önce ham ölçüt metnini inceler

Kod içinde ölçüt aralığı kurmanın motor-özgü bir tuzağı vardır. XLSX motorunda '=ab' dizgisini TXLSXCell.Value'a atamak metin saklar; ölçüt hücresinin gerektirdiği tam da budur. Klasik TXLSWorkbook motoru, = ile başlayan bir dizgi değerini, önüne kesme işareti koymadıkça formül olarak derler. Çalışma anında kurulan ölçüt aralıkları için XLSX motoru hataya daha az açıktır. Aşağıdaki döngü, D2'ye beş ölçüt takar ve her seferinde aynı DSUM'u yeniden hesaplar

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;

>ab gibi sıralama ölçütleri bambaşka bir kurala uyar. Düz kod-nokta karşılaştırması "a~b">"ab" için TRUE üretir; çünkü ~, Unicode'da tüm harflerin üstünde durur. Excel noktalama işaretlerini harflerin önüne koyar ve FALSE döndürür. Windows'ta HotXLS, > ve < metin ölçütlerini, sıradan metin karşılaştırmasını ve sıralamayı, geçerli kullanıcı yereli için sistemin sözcük-sıralama collation'ı ile değerlendirir; böylece üçü birbiriyle ve Excel'le uyuşur

Delphi'de tüm hücreli Find: son yıldıza geri dönmek

Tüm hücreli Find için cazip kısayol, Replace'in arkasındaki kısmi eşleyiciyi yeniden kullanıp eşleşmenin bütün hücreyi kaplamasını istemektir. Bu, sıradan veride başarısız olur. Kısmi eşleyici, örüntü tükenir tükenmez döner; a*b'nin abcb'ye uygulanması ab'den sonra durur, 4 karakterin 2'sini tüketmiş olur ve hücre reddedilir. HotXLS, tüm hücreli Find'u ayrı bir eşleyiciyle çalıştırır; bu eşleyici "örüntü bitti, metin bitmedi" durumunu bir eşleşmeme daha sayar ve son yıldızdan yeniden dener. Bununla a*b, abcb ile eşleşir ve a?b*b, axbyb ile eşleşir; tıpkı Excel Find'un "hücre içeriğinin tamamıyla eşleş" kutusu işaretliyken yaptığı gibi

Tüm hücreli wildcard Find geri dönüşünün HotXLS şeması: a*b örüntüsü abcb hücresinde a ile b'yi tüketir ve örüntü tükenince duran bir matcher hücreyi reddeder; HotXLS matcher'ı ise örüntünün metin kalmışken bitmesini bir eşleşmeme daha sayar ve bütün hücre eşlenene dek son yıldızdan yeniden dener
Tüm hücreli bir eşleme, örüntü tükendiğinde bitmiş değildir; artan metni bir eşleşmeme daha saymak matcher'ı son yıldıza geri gönderir; a*b'nin Excel Find gibi abcb'ye ulaşması böyle olur

Excel Find, hem tüm hücre hem kısmi modda ~ işaretini ardından gelen 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, bu yüzden q~ davranışta q ile aynıdır. Yalnızca ~*, ~? ve ~~ bilen bir eşleyici, a~b'nin a~b metnini bulmasını sağlardı; tam ters hücreyi. HotXLS tildeyi, formüllerde olduğu gibi Find ve Replace'te de her karakter için kaçırır

XLSX motorunda çağrı, bir TXLSXFindOptions kümesiyle TXLSXWorksheet.FindText'tir. lxfUseWildcards *, ? ve ~'yi açar; lxfWholeCell bütün hücrenin eşleşmesini ister; lxfMatchCase karşılaştırmayı büyük/küçük harf duyarlı yapar. lxfUseWildcards olmadan yıldız dâhil her karakter literaltır. Find yalnızca metin değerlere bakar. Sayısal hücreler atlanır; formül hücreleri, lxfSearchFormulas kurulmadıkça atlanır — kurulunca formül metni aranır. lxfBackward ile lxfByColumns tarama sırasını değiştirir. StartRow ve StartCol'un verdiği çapa dahildir; bu yüzden bir Find All döngüsü her isabetin bir sütun ötesine geçmek zorundadır

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 bildirir; çünkü kısmi modda a*b'nin hücre içinde bir yerde bulunması yeterlidir. FindTextIn ile ReplaceTextIn aynı seçenekleri artı bir FirstRow, FirstCol, LastRow, LastCol penceresi alır; seçim içinde aramanın programatik hâli budur. Açık kalan her sınır için 0 verin. Klasik motor aynı eşleme kurallarını üç boole alan bir aşırı yüklemeyle sunar: TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell); yanına da eşleşen bir ReplaceText aşırı yüklemesi koyar. Satır ve sütun sonuçları 1 tabanlıdır ve XLSX sürümü gibi formül hücrelerini ve metin olmayan değerleri atlar

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;

DOS dosya maskesi neden Excel ölçütleri için yanlış eşleyicidir?

MatchesMask gibi bir dosya-maskesi eşleyici, Excel wildcard'larıyla başka bir dil konuşur; ikisi * ile ? üzerinde örtüşse bile. O örtüşme, tam da onun neden seçildiğinin ve hatalarının neden veri ilginçleşene dek gizli kaldığının açıklamasıdır:

  • [x] bir karakter kümesi olarak okunur; bu yüzden COUNTIF(A1:A10,"[x]"), ayraçlı metin yerine x tutan hücreleri sayardı ve "[a-z]" tek harfli her hücreyle eşleşirdi
  • Tilde kaçışı yoktur; bu yüzden "a~*b" literal bir yıldızla eşleşemez
  • Kapanmamış ayraç gibi bozuk bir maske, çağıranın kolayca "eşleşme yok" diye yutacağı bir istisna oluşturur; ölçütteki bir yazım yanlışını yanlış bir toplama çevirir
  • Arama tarafında da ~*, ~? ve ~~ ile sınırlı bir kaçış kümesi yetersiz kalır: MATCH("a~b",…,0), literal a~b'yi değil ab'yi bulmak zorundadır

Çalışma kitaplarınız yalnızca sade alfasayısal veride * ile ? kullanıyorsa bu eşleyicilerin hepsi aynı sonuca varır ve bunların hiçbiri önemli değildir. Ayraçlar, tildeler, "<>text" altında karma tipli sütunlar ve çıplak sözcük yazılmış DSUM ölçütleri, basit bir eşleyicinin toplamlarının Excel'den sapacağı yerlerdir; yukarıdaki kod da tam olarak bu durumları çalıştırır. Kayıtlı filtreler, Excel'in bir ölçütü nasıl sakladığı ile onu nasıl karşılaştırdığı arasında ilgili bir soru doğurur; bu, BIFF8 AutoFilter DOPER ölçütleri üzerine HotXLS yazısında ele alınır

HotXLS, COUNTIF, MATCH, XLOOKUP, DSUM ve işlev kütüphanesinin gerisini Delphi ve C++Builder içinde, klasik motorla XLSX motorunda, makinede Excel kurulu olmadan yerel olarak değerlendirir. Sürümler, belgeler ve deneme yapısı HotXLS Delphi spreadsheet component sayfasında listelenir