HotXLS'te Karşılaştırma Zincirleri, Boş Hücreler ve SUMIF

HotXLS Delphi Component, =1<2<3 ifadesini FALSE olarak değerlendirir; Excel 16'nın verdiği cevap da budur, çünkü v2.384.3'ten beri formül ayrıştırıcısı karşılaştırma operatörlerini soldan sağa katlar: 1<2 TRUE olur ve TRUE<3, Boolean her sayının üstünde sıralandığı için FALSE'tur. Aynı sürüm boş bir işleneni hem 0 hem de "" ile eşit kılar ve SUMIF'in tek hücrelik toplam aralığını ölçüt aralığının biçimine büyütmesine izin verir. Bunların her biri, Delphi'de hesaplanan bir çalışma kitabı, Excel'de açılan aynı çalışma kitabıyla çelişene dek önemsiz ayrıntı gibi görünür

Çelişki genellikle birinin sezgiyle yazdığı bir formülle başlar. Biri bir miktarın aralıkta olduğunu denetlemek için =0<B2<100 yazar, Excel her satır için sessizce FALSE der ve sayfa o hatayla gömülü olarak yola çıkar. Bir hesaplama motorunun kullanıcının niyetini düzeltme lüksü yoktur; görevi Excel'in üreteceği değeri üretmektir, böylece HotXLS'in dosyaya yazdığı önbelleklenmiş sonuç, yeniden hesaplamadan sonra Excel'in gösterdiğiyle eşleşir. v2.384.3 öncesi HotXLS o aralık denetimine her satırda TRUE diyordu; ters yönde yanlıştı ve sunucuda üretilen bir rapor, masaüstünde açılan aynı raporla çelişirdi

=1<2<3 Excel'de neden FALSE döndürür?

Excel FALSE döndürür, çünkü karşılaştırma zincirini (1<2)<3 olarak okur ve içerideki TRUE, 3 sayısına karşı tür sıralaması yarışını kaybeder. Eski HotXLS ayrıştırıcısı aynı metni 1<(2<3) olarak okuyordu: lxFormula.pas içindeki TXLSSyntax.Parse_expr bir işlenen ayrıştırıyor, karşılaştırma token'ı görünce sağ taraf için Parse_expr'a özyinelemeye giriyordu; bu da operatörü sağ ilişkili kılıyordu. Sonuç 1<TRUE oluyor, sayı Boolean'ın altındaydı, dolayısıyla sonuç TRUE'ydu. Hata simetriktir: =3>2>1 Excel'de TRUE, HotXLS'te FALSE'tu; =1=1=TRUE Excel'de TRUE, düzeltmeden önce FALSE'tu. CalculateFormula_ComparisonChainsFoldLeftToRight regresyonu, yedi formülü Excel 16'nın döndürdüğü değerlere karşı sabitler ve hepsini her iki motor mimarisiyle, klasik TXLSWorkbook ile XLSX yerli TXLSXWorkbook üzerinden, HotXLS formül motoru genel bakışında anlatılan Calculate yöntemiyle çalıştırır

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // Excel 16'nın döndürdüğü:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate etkin sayfaya göre değerlendirir ve
    // çalışma kitabında hiç sayfa yokken Null döndürür
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

Düzeltme, Parse_expr'ı Parse_expr1'in +, - ve & için hâlihazırda kullandığı biçimde bir döngüye çevirir. İlk işleneni Parse_expr1 ile ayrıştırır ve sıradaki token =, <>, <, >, <= ya da >='den biri oldukça karşılaştırma düğümü kurar, birikmiş sol sonucu ilk çocuk olarak ekler, sıradaki işleneni Parse_expr yerine Parse_expr1 ile ayrıştırır ve yeni düğümü bir sonraki turun sol sonucu yapar. Özyinelemeyi yinelemeye çevirirken ters gitmesi kolay iki ayrıntı vardı ve ikisi de sürücü notlarında: birikmiş düğüm o sırayla devredilmelidir (lChild := Item; Item := nil) ve hata yolu, yarım kurulmuş düğümü serbest bıraktıktan sonra döngüden düşüp başıboş bir ağaç döndürmek yerine Exit etmelidir

HotXLS bir karşılaştırmada sayıları, metinleri ve Boolean'ları nasıl sıralar?

HotXLS karma türleri Excel gibi sıralar: her sayı her metin değerinden, her metin değeri her Boolean'dan küçüktür. lxCalc.pas içindeki TXLSCalculator.CompareVariants, her iki işleneni GetRetValueType ile TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) sayımına sınıflandırır ve iki sınıf farklıysa doğrudan sıra numaralarını karşılaştırır; o enum'un bildirim sırası, türler arası kuraldır. Tek sınıfın içinde karşılaştırma doğal olandır; metin için Excel'e özgü bir kıvrımla: her iki dize de önce lxUpperCase'ten geçer, böylece ="abc"="ABC" TRUE'dur. Zincir sonucu bu sıralama olmadan akıl yürütülemez; nedeni de budur. TRUE<3, TRUE'nun 1'e zorlanması değildir; Boolean'ın bir sayıyla karşılaştırılmasıdır ve Boolean kazanır. Tarihler motor için seri sayılardır (varDate, xlNumberValue olarak sınıflanır), dolayısıyla bir tarih her metnin altındadır; tarih gibi görünen metinler dahil

Boş bir hücre bir karşılaştırmada neye eşittir?

Karşılaştırma işleneni olarak kullanılan boş bir hücre, öteki taraf sayıyken 0'a, metinken ""'e eşittir ve v2.384.53'ten beri öteki taraf mantıksal bir değerkende FALSE'a eşittir; A1 boşken =A1=0, =A1="" ve =A1=FALSE üçü de TRUE'dur. Altı karşılaştırma operatörüne de hizmet veren TXLSCalculator.CompareVarValues, CompareVariants'ı çağırmadan önce boşluğun yerine başkasını koyar: tam olarak bir işlenen Null ise, ortağı dizeyken WideString(''), ortağı Boolean'ken False, aksi hâlde 0 olur. İki boşluk, yerine koyma olmadan birbirine hâlâ eşittir. Aritmetik yol boşluğu hep 0'a çevirirdi; =A1+1'in 1 vermesinin nedeni budur; oysa CompareVariants Null'u, her sayının altındaki kendi en düşük sırasıyla tutuyordu ve karşılaştırma operatörleri o sırayı doğrudan kullanıyordu

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 bilinçli olarak boş bırakıldı

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: boş, 0 olarak karşılaştırılır
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; v2.384.3 öncesi True'ydu
end;

Pratikte can yakan son satırdır. Eski sıralama altında boşluk, negatifler dahil her sayıdan küçüktü; böylece =IF(A1<0,"overdrawn","ok") her boş bakiye hücresini overdrawn damgalıyordu ve herhangi bir kullanıcının sıfır diyeceği bir hücre için =A1=0 FALSE'tu. v2.384.3'ten sonra bir sınır kaldı: yerine koyma yalnızca 0 ile boş dize arasında seçim yapıyordu; Boolean'la karşılaştırılan bir boşluk 0 oluyordu, 0 ise hem TRUE hem FALSE'ın altında sıralanıyordu ve boş A1'de =A1=FALSE FALSE'a değerleniyordu. HotXLS 2.384.53'ten beri mantıksal değerle karşılaştırılan bir boşluk, Excel gibi, hem XLS hem XLSX motorunda FALSE sayılır: A1 boşken =A1=FALSE ile =A1<TRUE TRUE, =A1=TRUE ise FALSE döndürür. Bu aynı zamanda karşılaştırmanın boşluğu FALSE'dan ayırt edemediği anlamına gelir; Excel'de de HotXLS'te de. Bir sayfa bu ayrımı gerektiriyorsa ISBLANK ile ya da =A1="" ile test edin

Tek hücrelik toplam aralıklı SUMIF neden 0 döndürdü?

SUMIF 0 döndürdü, çünkü HotXLS yinelemeyi iki aralıktan küçüğüne kısıtlıyordu; Excel ise ölçüt aralığının biçimini korur ve toplam aralığını yalnızca sol üst hücresi için kullanır. Dolayısıyla =SUMIF(A1:A10,">5",B1) Excel'de B1:B10 demektir; elle kurulu birçok şablon bu kolaylığa yaslanır. Paylaşılan işçi TXLSCalculator.GetValueItemRange2, satır ve sütun sayılarını değer aralığınıkine küçültüyordu; bu da örneği A1'in B1'e karşı tek bir sınamasına indiriyordu. v2.384.3 kısıtı kaldırır: döngü artık ölçüt aralığını gezer ve her değeri toplam aralığının sol üst köşesinden aynı ofsette okur. CalcSumIF ile CalcAverageIF aynı işçiyi çağırdığı için AVERAGEIF de aynı büyütmeden yararlanır ve ölçüt aralığından büyük bir toplam aralığı aynı gerekçeyle ölçüt biçimine kırpılır. Ortadaki ölçüt argümanı değer sınıfı, dıştaki ikisi referans sınıfıdır; ayrım örtük kesişim ve argüman sınıfları makalesinde ele alınıyor

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // ölçüt sütunu: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // tutarlar: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // tek hücrelik toplam aralığı
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // açık toplam aralığı
    if Book.Recalculate = lxOk then
      // D1 ile D2 de 4000'dir (600+700+800+900+1000); D1 v2.384.3 öncesinde 0'dı
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT ve YEARFRAC: iki daha sessiz düzeltme

INDIRECT artık ikinci argümanına uyar ve geçerli bir referansın ardından gelen metin yok sayılmak yerine hatadır. a1 FALSE iken metin mutlak R1C1 olarak ayrıştırılır; =INDIRECT("R2C3",FALSE) C2'yi okur. Eski kod bayrağı yok sayıyor, "R2"yi R sütunu 2. satır olarak okuyor ve sessizce yanlış hücreyi döndürüyordu. Bayrak, varyant türüne göre dağıtılır (boolean, sayı ya da metin); çünkü bir dize varyantını doğrudan Double'a çevirmek istisna fırlatır. R[1]C[1] gibi göreli R1C1 metni #REF! döndürür; INDIRECT'in onu çözümleyecek bir formül hücresi kökeni yoktur ve sonda karakterler taşıyan A1 metni, "B2 junk", da #REF! döndürür. 0 tabanlı YEARFRAC artık DAYS360'in hâlihazırda uyguladığı NASD şubat sonu kurallarını uygular: her iki tarih de şubatın son günüyken bitiş günü 30 olur, sonra başlangıç şubatın son günüyken o da 30 olur. 2024-02-29'dan 2025-02-28'e sayı artık 360 gün, tam 1'lik kesirdir; eski Days360US 359 sayıyordu

Bu düzeltmeler neyi garanti eder ve ders neydi?

Karşılaştırma zinciri davranışı, her iki motoru Excel 16'da ölçülmüş değerlerle karşılaştıran bir testle garanti edilir ve o test vardır, çünkü düzeltmenin ilk anlatımı yanlıştı. v2.384.3 sürüm notu başta soldan sağa katlamanın =1<2<3'ü TRUE yaptığını söylüyordu; bu, tam olarak eski sağ ilişkili ayrıştırıcının ürettiği şeydi ve Excel'in de yeni kodun da döndürdüğünün tersiydi. Örneği kimse değerlendirmemişti; "1, 2'den küçüktür, 2 de 3'ten küçüktür" sezgisinden yazılmıştı. Not düzeltildi ve yedi formüllü test bir izleyen commit'te eklendi; ortaya çıkan kural, elektronik tablo anlam bilimi belgeleyen herkese uygulanır: beklenen değeri yazmadan önce örneği Excel'de koşturun. Boş işlenen yerine koyması ile SUMIF büyütmesi aynı Excel davranışını izler; v2.384.53'ten beri boşluk-Boolean durumu dahil. Filtrelenmiş ya da gizlenmiş satırları da atlaması gereken koşullu toplamalar ise SUBTOTAL ve AGGREGATE gizli satır makalesindeki ayrı kuralları izler

HotXLS, Excel kurulu olmaksızın XLS, XLSX, ODS ve CSV okuyan, yeniden hesaplayan ve yazan yerli bir Delphi ve C++Builder elektronik tablo bileşenidir; burada anlatılan karşılaştırma, boşluk ve SUMIF kuralları her iki çalışma kitabı mimarisinin paylaştığı hesaplama motorunda yaşar. Tam fonksiyon listesi ve lisans seçenekleri HotXLS Delphi elektronik tablo bileşeni ürün sayfasındadır