Delphi ve C++Builder için yerel elektronik tablo bileşeni olan HotXLS, XLOOKUP ve XMATCH'i tek bir paylaşılan arama çekirdeği üzerinden değerlendirir. Bu çekirdek dört eşleşme modunu (-1, 0, 1, 2) ve dört arama modunu (-2, -1, 1, 2) kabul eder, mutlak arama modu 2 olduğunda logaritmik bir ikili iniş çalıştırır ve diğer her kombinasyonu bir formül hatasıyla reddeder
Sizi buraya gönderen hata raporu asla "arama modu" demez. Sunucu tarafında üretilen çalışma kitabının, dokuz bin satırdan belki dört tanesinde, Excel'de açılan aynı dosyadan farklı bir sayı gösterdiğini söyler. O dört satırın her zaman ortak bir şeyi vardır: tekrarlanan bir arama anahtarı, bir komşu seçmek zorunda kalan yaklaşık bir eşleşme, veya birinin geçen hafta farklı bir sütuna göre sıraladığı bir arama sütunu. Arama fonksiyonları, bir formül motorunun aritmetik olmaktan çıkıp bir sözleşme olmaya başladığı yerdir ve sözleşmenin çoğu çağıranın hiç okumadığı maddeleri vardır
XLOOKUP gerçekte hangi mod sayılarını kabul eder?
Her birinden tam olarak dört tane, başka hiçbir şey yok. HotXLS, tek bir hücreye dokunmadan önce match_mode'u -1, 0, 1 ve 2'ye ve search_mode'u -2, -1, 1 ve 2'ye karşı doğrular ve başka herhangi bir değer, en yakın yasal moda sıkıştırılmak yerine #VALUE! döndürür. Dört eşleşme modu, tam eşleşme için 0, tam eşleşme veya bir sonraki küçük için -1, tam eşleşme veya bir sonraki büyük için 1 ve joker karakter için 2'dir; dört arama modu, ileri doğrusal tarama için 1, geri doğrusal tarama için -1, artan veri üzerinde ikili arama için 2 ve azalan veri üzerinde ikili arama için -2'dir. Bunları atlamak, neredeyse her gerçek formülün kullandığı eşleştirme olan eşleşme modu 0 ve arama modu 1'i seçer. Argüman sayıları aynı şekilde denetlenir: XLOOKUP üç ila altı argüman alır ve XMATCH iki ila dört alır ve bu aralıkların dışındaki her şey, değerlendirme başlamadan önce bir #VALUE!'dır
// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
(RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
(RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
Result := lxErrorValue; // #VALUE!
Exit;
end;
if Abs(RequestedSearchMode) = 2 then
begin
if RequestedMatchMode = 2 then // wildcards cannot ride a binary descent
begin
Result := lxErrorValue;
Exit;
end;
// ... O(log n) descent over the lookup vector
end;Bir adım önce, bilinmeye değer daha sessiz bir kontrol vardır. Mod argümanları çalışma sayfası ifadeleri olarak gelir, dolayısıyla HotXLS onları bir sayıya zorlar, NaN ve sonsuzu reddeder ve sonra sayının kendi yuvarlanmış değerine eşit olmasını talep eder. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) gizli bir arama modu 2 değil, bir #VALUE!'dır. Bu, mod yuvarlama ağırlıklı bir hesaplamanın ürettiği bir hücreden geldiğinde önemlidir, ki bu elle yazılmış çalışma kitaplarından çok üretilmiş çalışma kitaplarında daha yaygındır
search_mode 2 sıralanmamış veri üzerinde neden yanlış cevap verir?
Çünkü tam olarak istediğinizi yapıyor. Arama modu 2, motora arama vektörünün zaten artan sırada olduğunu söyler ve bir ikili arama, onu kullanma nedenini yok edecek bir O(n) geçişi olmadan bu iddiayı doğrulayamaz. HotXLS bu yüzden çağırana güvenir, aralığı yarıya böler ve iniş nereye inerse onu döndürür. Sıralanmamış girdide cevap bir hata değildir, sessizce yanlıştır ve bu, motorda bir kusur değil bir sözleşme ihlalidir
Microsoft, XLOOKUP ve XMATCH için aynı asimetriyi belgeler: ikili modlar sıralanmış veri gerektirir ve aksi halde geçersiz sonuçlar üretir. SpreadsheetML formül dilbilgisini tanımlayan ISO 29500-1 madde 18.17, daha eski LOOKUP ve VLOOKUP açıklamalarını kendi artan sıra gereksinimleriyle taşır ve XLOOKUP ile XMATCH, o metinden çok sonra geldikleri için, dosyada gelecek-fonksiyon kuralı altında _xlfn.XLOOKUP ve _xlfn.XMATCH olarak seyahat ederler. Farklı nesil, aynı pazarlık: çağıran sıralama değişmezini sağlar, motor logaritmayı sağlar
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates');
Sheet.Cells[1, 1].Value := 40; Sheet.Cells[1, 2].Value := 0.10;
Sheet.Cells[2, 1].Value := 10; Sheet.Cells[2, 2].Value := 0.25;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
// Forward linear scan: finds key 40 wherever it sits
Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
// Binary ascending: the promise was broken, the key is never visited
Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;İkinci formülü izleyin, hata tamamen mekaniktir. İniş orta hücreyi yoklar, 10 okur, 10'un 40'tan küçük olduğuna karar verir, 40'ı gerçekten tutan satır dahil sol yarıyı atar, 30'u yoklar, tekrar atar ve aralık biter. Excel de aynı şekilde davranır, ki mesele budur: yanlış cevabı yeniden üretmek bir uyumluluk gereksinimidir, bir nezaket değil. Sıralama önermesi ayrıca "artan sayılar"dan daha katıdır, çünkü karşılaştırıcı değerleri önce türe göre sıralar, önce sayılar, sonra metin, sonra boolean'lar, sonra hata değerleri, sonra boşluklar sırasıyla ve ancak bundan sonra bir tür içinde karşılaştırır. Üç hücresi metin depolayan sayısal parça kodları sütunu, ekranda nasıl görünürse görünsün bu karşılaştırıcı altında artan değildir ve ikili modlar onu memnuniyetle yanlış okuyacaktır
Tekrarlanan anahtarlar nereye iner?
Tekrarlanan çalıştırmanın deterministik bir ucuna ve hangi ucun şansa değil arama moduna bağlı olduğu yere. İkili iniş, arama modu 2 altında eşit bir anahtara çarptığında konumu kaydeder ve sonra sola daraltmaya devam eder, dolayısıyla sonuç çalıştırmanın en düşük indeksidir; azalan veri üzerinde arama modu -2 altında, konumu kaydeder ve sağa daraltır, dolayısıyla sonuç en yüksek indekstir. Doğrusal modlar daha basittir: arama modu 1 ileri giderken ilk isabeti döndürür, arama modu -1 geri giderken ilk isabeti döndürür. Bu, açılış paragrafındaki dört satırlık tutarsızlığı üreten ayrıntıdır, çünkü anahtarları benzersiz olan bir çalışma kitabı, dört arama modunun tamamı altında özdeş cevaplar verir ve yazdığınız her testte temiz bir örnek dosyadan farkı gizler. Üretim verisine bir tekrarlanan müşteri kodu ekleyin ve modlar tam olarak tekrarlanan satırlarda anlaşmazlığa düşmeye başlar: motorda hiçbir şey değişmedi, girdi yalnızca bir küme olmaktan çıkıp bir çoklu küme oldu
// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, lowest index of the run
// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, highest index of the runYaklaşık eşleşme, ikinciyi nasıl seçer?
Tam eşleşme aramasının yanında en iyi adayı tutup yalnızca hiçbir tam isabet görünmediğinde onu döndürerek. HotXLS, match_mode -1'i "hedeften büyük olmayan en büyük değer" ve match_mode 1'i "daha küçük olmayan en küçük değer" olarak ele alır ve her ikisi de ilk kabul edilebilir komşuda durmak yerine tüm taranan bölge üzerinde çözülür. İkili yolda aynı fikir inişten bedavaya çıkar: aşan veya eksik kalan her adım adayı günceller, dolayısıyla nihai aday, anahtarın eklenmiş olacağı konumun yanındaki sınır elemandır
// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
((RequestedMatchMode = 1) and (CompareResult >= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
begin
CandidateIndex := ScanIndex;
CandidateValue := CurrentValue;
end;
end;İç koşulu yakından okuyun, çünkü beraberlik kırma orada yaşar. Mevcut adayın yerini yeni bir hücre yalnızca kesinlikle daha iyi olduğunda alır, yalnızca eşit olduğunda asla değil, dolayısıyla aynı ikinci değeri tutan birkaç hücre arasından tutulan, tarama sırasında ilk karşılaşılandır: ileri taramada en düşük indeks, geri taramada en yüksek. XLOOKUP ve XMATCH ne tam bir isabet ne de kabul edilebilir bir komşu bulursa, XLOOKUP, sağlandığında if_not_found argümanına ve sağlanmadığında #N/A'ya geri döner, XMATCH ise her zaman #N/A verir
Joker karakterler ve ikili arama neden bir arada bulunamaz
Çünkü bir joker karakter deseni bir sırada bir konum değildir. Eşleşme modu 2, bir hücrenin bir maskeyle eşleşip eşleşmediğini sorar ve maske eşleştirme evet veya hayır yanıtı verir; bir ikili iniş, hangi yarının tutulacağını söyleyen üç yönlü bir yanıta ihtiyaç duyar. ACME-*'in belirli bir hücrenin solunda mı sağında mı yattığını sormanın savunulabilir bir yolu yoktur, dolayısıyla HotXLS, bir sıralama tahmin edip makul saçmalık üretmek yerine, match_mode 2'yi search_mode 2 veya -2 ile birleşince önceden #VALUE! ile reddeder. İki yol ayrıca değerleri farklı şekilde karşılaştırır, ki bu ayrımı güçlendirir: doğrusal tarama eşitliğe büyük/küçük harf duyarsız bir metin karşılaştırmasıyla, veya joker karakterler açıkken maske eşleştirmesiyle karar verir, ikili iniş ise eşitliğe sıralama karşılaştırıcısından bir sıfır isteyerek karar verir. Bu, katmanlamanın bir kazası değil kasıtlıdır, çünkü ikili yol yalnızca gerçekte gezindiği ilişkiyi kullanabilir. Joker karakterlere ihtiyacınız varsa, arama modu 1 veya -1'i kullanın ve doğrusal maliyeti kabul edin, ki bu, artımlı yeniden hesaplamanın arkasındaki bağımlılık takibinin kritik yolunuzdan uzak tutmak için tasarlandığı aynı değiş tokuştur
Şekil hataları: iki boyutlu aralıklar ve uyuşmayan dönüş vektörleri
Her iki fonksiyon da gerçekten tek boyutlu bir arama aralığı gerektirir. Sağlanan aralık aynı anda birden fazla satır ve birden fazla sütun kapsıyorsa, HotXLS sizin adınıza bir eksen seçmek yerine #VALUE! döndürür ve tek satırlı veya tek sütunlu bir aralık kendi uzun ekseni boyunca okunur. XLOOKUP ikinci bir şekil kuralı ekler: dönüş aralığı, eşleşen eksen boyunca arama aralığıyla tam olarak aynı uzunlukta olmalıdır, dolayısıyla 500 satır üzerinde dikey bir arama, 499 satırlık bir dönüş aralığıyla eşleştirildiğinde, son satırda sessizce çözülen bir kayma değil bir hatadır. Dönüş aralığı dikey bir arama için bir sütundan geniş olduğunda, veya yatay bir arama için bir satırdan yüksek olduğunda, XLOOKUP eşleşen tüm dilimi bir dizi olarak geri verir ve diğer dinamik dizi fonksiyonlarıyla aynı kurallar altında komşu hücrelere taşar, taşma aralıkları ve dinamik diziler üzerine makalede anlatıldığı gibi. Bu, bir tablodan tek bir formülle tüm bir kaydı çekmek için gerçekten yararlıdır ve aynı zamanda tutmak istediğiniz bir sütunu üzerine yazmanın en hızlı yoludur
Ekranı kimse izlemezken bir mod seçmek
Sunucu tarafı üretim, etkileşimli kullanımdan daha katı bir politikayı hak eder, çünkü bir toplamın yanlış göründüğünü fark edecek bir insan yoktur. Savunulabilir varsayılan, eşleşme modu 0 ile arama modu 1'dir: doğrusal, tam, sıralamadan bağımsız ve bir sayfayı yeniden sıralayarak geçersiz kılınması imkânsız. Arama modu 2'ye yalnızca aynı kod yolunun aynı koşuda, aynı sütun üzerinde sıralamayı da ürettiği yerlerde uzanın ve o bağımlılığı formülün yanına yazın, çünkü farklı bir anahtara göre sıralanmış bir sütunda ikili arama, güvenli görünen yanlış bir sayıyı hesaplamanın en ucuz yoludur. Arama gerçekten sıcak ve veri gerçekten sıralı olduğunda getiri gerçektir: iniş, n yerine log n mertebesinde hücre okur ve bu okumaların her biri tam bir çalışma kitabı hücre çözümlemesinden geçer, dolayısıyla tasarruf, talimat sayısının önerdiğinden daha büyüktür
Problemin şekli bir aramadan çok bir alan kuralına yakınsa, özel çalışma sayfası fonksiyonları üzerine makalede ele alınan, kendi Pascal kodunuza bir geri çağırma genellikle yerleşiklerin herhangi bir akıllı düzenlemesini geride bırakır. Burada anlatılan XLOOKUP ve XMATCH uygulamaları, ürün sayfası Delphi ve C++Builder için tam desteklenen fonksiyon referansını taşıyan standart HotXLS Delphi elektronik tablo bileşeni ile birlikte gönderilir