Delphi'de XLOOKUP ve XMATCH İkili Arama Modları

A1:A7deki 1, 3, 5, 5, 5, 7, 9 anahtarları üzerinden XMATCH(5, A1:A7, 0, 2) formülü 3 döndürür. Yalnızca son argümanı -1e çevirin, aynı formül 5 döndürür. İlk sürümü, birinin başka bir alana göre yeniden sıraladığı bir sütuna doğrultun; sheette açıkça görünen bir 5, #N/A olarak geri gelebilir. Üç cevap da doğrudur. XMATCHin son argümanı - XLOOKUPın altıncı argümanı gibi - aynı anda iki şeydir: hangi kopyanın tercih edileceğine dair bir talimat ve verinin nasıl sıralandığına dair bir söz

Burada yönlendiren rapor, arama modlarını nadiren anar. Üretilmiş bir workbookun, bir avuç satırda birinin beklediğiyle uyuşmadığını söyler ve o satırların ortak bir yanı vardır: tekrarlanan bir anahtar, komşu seçmek zorunda kalan yaklaşık bir eşleşme ya da formül yazıldıktan sonra sırası değişen bir arama sütunu. Aşağıdaki bölümler kuralları tek tek işler; her formülün ürettiği kesin sonuçla birlikte

XLOOKUP hangi match_mode ve search_mode değerlerini kabul eder?

HotXLS, yerel Delphi ve C++Builder spreadsheet componenti, XLOOKUP ile XMATCHi tam olarak dört eşleşme modu ve dört arama modu kabul eden tek bir paylaşılan arama rutini üzerinden değerlendirir. match_mode 0 (tam), -1 (tam ya da bir sonraki küçük), 1 (tam ya da bir sonraki büyük) ya da 2 (joker) olabilir. search_mode 1 (baştan sona tarama), -1 (sondan başa tarama), 2 (artan veri üzerinde ikili arama) ya da -2 (azalan veri üzerinde ikili arama) olabilir. Başka her sayı #VALUE! döndürür; hiçbir şey en yakın yasal moda sıkıştırılmaz. Argümanları vermemek, çoğu elle yazılan formülün kullandığı match mode 0 ile search mode 1i seçer

Argüman sayıları hepsinden önce denetlenir. XLOOKUP üç ile altı, XMATCH iki ile dört argüman alır ve o aralıkların dışındaki bir çağrı, değerlendirme başlamadan #VALUE!dir. Mode argümanlarının kendileri sıradan worksheet ifadeleridir; HotXLS her birini sayıya çevirir, NaN, sonsuzluk ya da 32 bitlik tamsayı aralığının dışındaki bir değer için #VALUE! döndürür ve sonra sıfıra doğru kırpar. 1.5 search mode 1 olur, -1.5 -1 olur, 2.9 2 olur. Kırpma, mode aritmetikle hesaplanmış bir hücreden geldiğinde ısırır: 2 görünen ama 1.9999999 olarak saklanan bir değer doğrusal bir tarama seçer. Modu bir literat olarak yazın ya da hesaplanmış olanı ROUNDa sarın

Aşağıdaki program, küçük bir kur tablosu ve o kuralları tetikleyen beş arama yazar. Yorumlar, her formül hücresinin Recalculateten sonra taşıdığı değeri verir; yukarıdaki kurallardan türetilmiştir

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Rates');          // Sheets[1]
    Sheet.Cells[1, 1].Value := 10;  Sheet.Cells[1, 2].Value := 0.25;
    Sheet.Cells[2, 1].Value := 20;  Sheet.Cells[2, 2].Value := 0.20;
    Sheet.Cells[3, 1].Value := 30;  Sheet.Cells[3, 2].Value := 0.15;
    Sheet.Cells[4, 1].Value := 40;  Sheet.Cells[4, 2].Value := 0.10;

    // Varsayılanlar: match_mode 0, search_mode 1
    Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)';               // 0.15
    // Tam ya da bir sonraki küçük, artan anahtarlar üzerinde ikili arama
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5, search_mode 1e kırpılır
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3 bir arama modu değildir
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // Joker eşleşmesi ikili bir aramada koşamaz
    Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)';  // #VALUE!

    if Book.Recalculate = lxOk then
      Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

search_mode 2, sheette olan bir anahtarı neden ıskalar?

Search mode 2, HotXLSye arama vektörünün zaten artan sırada sıralı olduğunu söyler ve motor ona inanır. İddiayı denetlemek, sütunun tamamı üzerinde bir geçiş gerektirirdi; ikili aramanın varlık nedeni tam olarak o maliyetden kaçmaktır. Bu yüzden iniş her sorguda aralığı ikiye böler ve bittiği yerde durur. Microsoft, XLOOKUP ve XMATCH için aynı sözleşmeyi belgeler: ikili modlar sıralı veri ister, aksi hâlde geçersiz sonuç verir. SpreadsheetML formül maddesi olan ISO/IEC 29500-1 §18.17, daha eski LOOKUP ile VLOOKUPu kendi artan-sıra gereksinimleriyle anlatır; XLOOKUP ile XMATCH daha sonra geldi ve future-function öneki altında dosyada _xlfn.XLOOKUP ile _xlfn.XMATCH olarak saklanır

Şekil, XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2)yi 40, 10, 30 anahtarları üzerinde izler. İlk sorgu 2. satıra düşer, 10 okur; 10, 40ın altında olduğundan 40ı tutan satır dâhil 1 ve 2. satırları çöpe atar. 3. satır 30 okur, o da 40ın altındadır ve aralık boşalır. Formül "missing" döndürür. Search mode 1 ile aynı çağrı, 40ı 1. satırda bulur

"Artan" ayrıca ekranda göründüğünden daha özgül bir şey demektir. İkili yol, değerleri karşılaştırmadan önce türe göre sıralar: önce sayılar, sonra metin, sonra boolelar, sonra hata değerleri, sonra boşluklar. Sayısal parça kodlarından oluşup üç hücrede kodları metin olarak tutan bir sütun, ne kadar derli toplu görünse de o kural altında artan değildir. Metin, büyük/küçük harfe bakılmadan karşılaştırılır ve Windowsda sıra, CompareStringW üzerinden kullanıcı yerelinden gelir; bu, tireleri ve aksanlı harfleri bayt-sırası sıralamasından farklı ele alır. İkili bir collation ile sıralanmış bir veritabanından gelen anahtar sütunu bu yüzden tam olarak noktalama içeren kodlarda sıralama sözünü çiğner

Boşlukların en sona sıralanması tek taraflı bir sonuç doğurur. Altında boş satırlar olan artan bir anahtar sütunu hâlâ artandır; search mode 2yi A2:A5001 gibi aşırı büyük bir aralığa doğrultmak zararsızdır. Ardında boş satırlar gelen azalan bir sütun ise artık azalan değildir ve search mode -2 düpedüz boşlukların içine yürür

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3 9, 7, 5 tutar; B4:B10 boştur
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk ve VarIsError(R.Value): sonuç #N/A.
  // İlk sorgu boş B5i okur; boşluklar her sayının üstünde sıralanır,
  // iniş sağa gitmeye devam eder ve bir daha dönmez

  // Aralığı dolu satırlara kırpın, aynı çağrı çalışır
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // Doğrusal tarama boşlukları atlar ve anahtarı nerede ise orada bulur
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

Yinelenen anahtarlar nereye düşer?

XMATCH ile XLOOKUP, bir kopya dizisinin sabit bir ucuna düşer ve ucu arama modu seçer. Search mode 2 altında ikili iniş, karşılaştığı her eşit anahtarı kaydeder ve sola daralmayı sürdürür; sonuç, dizinin en düşük indeksidir. Search mode -2 altında, azalan veri üzerinde, konumu kaydeder ve sağa daraltır; en yüksek indeksi verir. Doğrusal modlar, tarama yönündeki ilk isabeti döndürür: search mode 1 için en düşük indeks, search mode -1 için en yüksek

Tutarsızlık raporlarının belirli satırlarda toplanmasının nedeni budur. Anahtarları benzersiz bir örnek dosya, dört arama modunun tamamında aynı cevabı verir; bu yüzden ondan kurulan her test geçer. Üretim verisi bir müşteri kodunu tekrarladığı anda modlar tam olarak tekrarlanan satırlarda anlaşmazlığa düşer ve formülleri yazan kod hiç değişmemiştir

// A1:A7 1, 3, 5, 5, 5, 7, 9 tutar (artan, üçlü bir dizi)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, ileri yönde ilk isabet
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, geri yönde ilk isabet
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, dizinin en düşük indeksi

// B1:B7 9, 7, 5, 5, 5, 3, 1 tutar (azalan)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, dizinin en yüksek indeksi

Yaklaşık eşleşme ikinciliyi nasıl seçer?

Hiçbir hücre arama değerine eşit olmadığında match mode -1, hedeften büyük olmayan en büyük değeri; match mode 1 ise küçük olmayan en küçük değeri döndürür. Doğrusal modlarda HotXLS bütün vektörü tarar ve en iyi adayı tutar; böylece yaklaşık eşleşme sıralanmamış veride de çalışır. Bir hücre, ayakta duran adayı yalnızca kesinlikle daha iyi olduğunda değiştirir; yalnızca eşit olduğunda asla. Aynı ikinci değeri taşıyan birkaç hücre arasında tutulan, tarama sırasında ilk karşılalandır

A1:A6deki 10, 30, 20, 30, 50, 40 anahtarlarını alın. XMATCH(35, A1:A6, -1, 1) 2 döndürür: ilk 30 aday olur, ikinci 30 yalnızca eşitlenir. Taramayı XMATCH(35, A1:A6, -1, -1) ile ters çevirin, sonuç 4tür; çünkü geriye tarama öbür 30la önce karşılaşır. XMATCH(35, A1:A6, 1, 1) 6 döndürür; 40, 35in üstündeki en küçük değer olarak 50yi yener. Sıralı veride ikili modlar aynı komşuya bir avuç sorguda ulaşır: 10, 20, 30, 40, 50 üzerinde XMATCH(35, A1:A5, -1, 2) 3, XMATCH(35, A1:A5, 1, 2) 4 döndürür

Ne bir tam isabet ne de kabul edilebilir bir komşu çıkmazsa XLOOKUP, verilmişse if_not_found argümanını, verilmemişse #N/A döndürür. XMATCHin yedek argümanı yoktur ve daima #N/A döndürür

Joker karakterler ile ikili arama neden bir arada olamaz?

Bir joker deseni, bir sıralamada konum sahibi değildir. Match mode 2, bir hücrenin bir maskaya uyup uymadığını sorar; bu evet-hayır sorusudur. İkili iniş ise hangi yarının tutulacağını söyleyen üç yollu bir cevap ister. ACME-*in verilen bir hücrenin solunda mı sağında mı olduğunu savunulabilir biçimde söylemenin yolu yoktur; HotXLS de match mode 2 ile search mode 2 ya da -2 kombinasyonu için tahmin yürütmek yerine #VALUE! döndürür. Jokerlerle search mode 1 ya da -1 kullanın ve doğrusal maliyeti kabul edin

Match mode 2 daima joker kurallarını uygular: * herhangi bir karakter dizisini, ? tek karakteri temsil eder ve ~ sonraki karakteri literal yapar. Dolayısıyla XLOOKUP("PN~*", ...), PN* metninin kendisini bulur; ürün kodları gerçekten yıldız taşıdığında bu önemlidir. Match mode 0da doğrusal tarama metni büyük/küçük harfe duyarsız karşılaştırır ve ikili yol, sıralama karşılaştırması beraberlik bildirdiğinde iki değeri eşit sayar. O karşılaştırma da büyük/küçük harfe duyarsızdır ama yukarıda anlatılan aynı yerel-duyarlı sıralamadır; bir ikili aramanın meşru olarak kullanabileceği tek bağlantı budur. Arama-yoğun sheetler, artımlı yeniden hesaplamanın ardındaki bağımlılık takibinin bir düzenlemeden sonra neyi yeniden değerlendireceğini de şekillendirir

Şekil hataları ve taşan dönüş dilimleri

İki fonksiyon da tek boyutlu bir arama aralığı ister. Aynı anda birden çok satır ve birden çok sütuna yayılan bir aralık #VALUE! döndürür; HotXLS sizin adınıza bir eksen seçmez. XLOOKUP ikinci bir kural ekler: dönüş aralığı, eşleşen eksen boyunca arama aralığıyla aynı uzunlukta olmalıdır. 500 satırlık bir dikey aramaya eşlenmiş 499 satırlık bir dönüş aralığı hatadır ve son satır asla sessizce atılmaz. Arama aralığı tek bir hücre olduğunda, aramanın dikey mi yatay mı sayılacağına dönüş aralığının şekli karar verir

Dikey bir aramada bir sütundan geniş bir dönüş aralığı, XLOOKUPa eşleşen bütün satırı bir dizi olarak geri verdirir. Dizi, öbür dinamik dizi fonksiyonlarıyla aynı kurallar altında komşu hücrelere taşar; spill aralıkları ve dinamik diziler yazısı onu kapsar. Taşan dilimin içindeki boş hücreler 0 olarak geri gelir. Tek bir formülle bir tablodan bütün bir kaydı çekmek kullanışlıdır ve aynı zamanda, formülün sağında durmakta olan bir sütunun üzerine yazmanın en hızlı yoludur

Kimsenin izlemediği workbooklar için mod seçmek

Sunucu tarafı üretim, etkileşimli kullanımdan daha katı bir politikayı hak eder; çünkü sonuç gönderilmeden önce ona bakan kimse yoktur. Orada match mode 0 ile search mode 1i yerinde bırakın: eşleşme tamdır, tarama satır sırasını yok sayar ve sheeti yeniden sıralamak cevabı değiştiremez. Search mode 2, yalnızca aynı kodun sıralamayı ürettiği yerde - aynı çalıştırmada, aynı sütun üzerinde - yeri vardır. Başka bir anahtarla sıralanmış bir sütun üzerinde ikili arama, kendinden emin yanlış bir sayı hesaplamanın en ucuz yoludur

Bir aramayı gerçekten ikili moda aldığınızda, sıralamayı formülü yaydığınız noktada kendiniz denetleyin. Aşağıdaki fonksiyon HotXLSin parçası değildir; kendi kodunuza ekleyeceğiniz bir korumadır. Tek bir tabloya karşı çok sayıda tam arama için, workbook özelliği RecalcLookupCache, Recalculatein bir aralığın ilk taramasını sonraki aramalara yeniden kullandırır. Önbellek, geçişte OFFSET, INDIRECT, döngüsel referanslar ya da What-If veri tabloları olduğunda kendini kapatır; sonuçlar her iki durumda da aynıdır

// Kendi korumanız, HotXLS APIsi değil: ona dayanan bir search_mode 2
// formülü yaymadan önce sayısal anahtar sütununun artan olduğunu teyit edin
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
  LastRow: Integer): Boolean;
var
  Row: Integer;
  Prev, Cur: Variant;
begin
  Result := False;
  Prev := Sheet.Cells[FirstRow, Col].Value;
  if not VarIsNumeric(Prev) then Exit;
  for Row := FirstRow + 1 to LastRow do
  begin
    Cur := Sheet.Cells[Row, Col].Value;
    if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit;  // metin, boş ya da sıra dışı
    Prev := Cur;
  end;
  Result := True;
end;

// Rates sheeti: anahtarlar A2:A5001de, kurallar B2:B5001de
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
  if UseBinary then
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
  else
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);

Koruma bilerek tutucudur: tek bir boş ya da metin hücre, bütün sütunu doğrusal yola geri gönderir. Sıralı, sıcak bir sütunda ikili modun getirisi gerçektir; arama başına en fazla n yerine kabaca log₂ n okuma, 5.000 anahtar için en çok 13 sorgu demektir

Bir arama aslında kılık değiştirmiş bir iş kuralı olduğunda - istisnalı kademeli bir kur gibi - kendi Pascal kodunuza bir callback, yerleşik fonksiyonların her düzenlemesinden genellikle daha nettir; özel worksheet fonksiyonları yazısı bir tanesini nasıl kaydedeceğinizi gösterir. XLOOKUP, XMATCH ve burada anlatılan formül motorunun geri kalanı, HotXLS Delphi spreadsheet componentin parçasıdır; ürün sayfası, Delphi ve C++Builder için desteklenen worksheet fonksiyonlarını listeler