Teknik Makale

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

=0<B2<100 olarak yazılmış bir aralık denetimi, Excel'de B2'nin 50 tuttuğu satır dahil her satırda FALSE döndürür. Excel önce 0<B2yi değerlendirir, TRUE alır ve sonra TRUE'nin 100'den küçük olup olmadığını sorar; mantıksal bir değer her sayının üstünde sıralanır, cevap B2'nin içeriğinden bağımsız olarak FALSE'tır. Boş hücrelerin ve SUMIFin sürprizleri aynı türdendir: boş bir hücre aynı anda 0'a, ""e ve FALSE'a eşittir ve =SUMIF(A1:A10,">5",B1), B sütununun on hücresini toplar

Böyle bir workbook'u Excel dışında yeniden hesaplayan bir hesaplama motoru, açıkça yazarın kastettiği olmasalar bile o cevapları yeniden üretmek zorundadır. Dosyada saklanan önbelleklenmiş sonuç, kimse F9'a basmadan önce bir görüntüleyicinin, bir posta ön izlemesinin ya da alt akış bir içe aktarmanın gördüğü şeydir. Sunucu aralık denetimi için TRUE yazarken Excel sonra FALSE gösterirse aynı rapor, nerede açıldığına bağlı olarak iki farklı sayı taşır ve kimseye hangisine güvenileceği belli olmaz. Yazarın formülünü düzeltmek yazarın işidir

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

Excel, =1<2<3 için FALSE döndürür; çünkü altı karşılaştırma operatörünün tamamı tek bir öncelik düzeyini paylaşır ve soldan sağa birleşir, formül (1<2)<3 anlamına gelir ve içerideki TRUE, 3 sayısıyla yapılan tür sıralama yarışını kaybeder. HotXLS Delphi Component karşılaştırma zincirlerini her iki workbook motorunda da — klasik TXLSWorkbook ile XLSX yerlisi TXLSXWorkbook — aynı şekilde ayrıştırır. Ayrıştırıcı bir operan okur ve sonraki token =, <>, <, >, <= ya da >= olduğu sürece, şu ana dek ayrıştırılan her şeyi sol çocuğu, sonraki operanı sağ çocuğu yaparak bir karşılaştırma düğümü kurar ve o düğümü sonraki tura taşır. +, - ve & için kullandığı döngü aynı kalıptır

Tuzak, ders kitabı gramerinde oturur. expr = operand op expr olarak yazıldığında bir recursive-descent ayrıştırıcı sağ taraf için kendi içine döner ve operatörü sessizce sağ-bağlantılı kılar. O okumayla =1<2<3, 1<(2<3) olur; o da 1<TRUE olur; o da TRUE olur. Hata iki yönde de işler: =3>2>1, Excel'de TRUE, sağ bağlantıyla FALSE'tur ve =1=1=TRUE aynı şekilde ters döner. Parantezli biçimler iki durumda da doğru çıkar; bu yüzden hata, yalnızca (a<b)<cyi denetleyen herhangi bir testin içinden kayar

Calculate metodu bir formül dizisini hücreye koymadan değerlendirir; bu da bir zincir listesinin tamamını iki motorda denetlemenin çabuk yolu yapar. Metot, HotXLS formül motoru genel bakışında daha derinlemesine ele alınır

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk, lxCalc içinde tanımlıdı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'in cevapları:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;   // interface üzerinden referans sayımlı
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate etkin sayfaya karşı değerlendirir ve
    // workbook henüz sayfa taşımadığında 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;
=1<2<3 için iki ayrıştırma ağacı: sağ-bağlantılı bir ayrıştırma 1<(2<3)ü TRUE değerlendirirken soldan sağa katlamak (1<2)<3ü FALSE değerlendirir; çünkü Excel her sayıyı metnin altına, her metin değerini booleanın altına sıralar
İki HotXLS motoru da karşılaştırma zincirlerini soldan sağa katlar — bir boolean her sayıyı geçer; TRUE'nin 3'e kaybetmesi, zincirli aralık denetimini FALSE yapan şeydir

Sayılar, metin ve booleanlar: türler arası sıralama

Bir karşılaştırmanın iki tarafı farklı türlerdeyken Excel birini ötekine çevirmez. Onun yerine türleri sıralar: her sayı her metin değerinden küçüktür ve her metin değeri her booleandan küçüktür. HotXLS iki operanı da sayı, metin ya da boolea olarak sınıflandırır ve sınıflar farklılaştığında yalnızca sınıfları karşılaştırır. =TRUE>99999 TRUE'dur, ="1">2 TRUE'dur ve =FALSE>"zzz" de TRUE'dur. Zincirli karşılaştırmaları keyfî hissettiren parça budur. TRUE<3, 1'e zorlanıp 3 ile karşılaştırılmış TRUE değildir; bir sayıyla karşılaştırılmış bir boolean'dır ve boolean kazanır

Tek bir sınıfın içinde karşılaştırma doğal olandır; metin için bir kıvrımla: dize karşılaştırması büyük küçük harfi yok sayar, ="abc"="ABC" Excel'de de HotXLS'te de TRUE döndürür. Tarihler motora seri numaralarıdır; yani bir tarih, yalnızca tarihe benzeyen bir metin hücresi dahil, her metnin altında sıralanır. Hücrelerinin yarısı dize olarak gelmiş içe aktarılmış bir tarih sütunu iki ayrı bantta sıralanır ve karşılaştırılır; =A2>DATE(2026,1,1), dize hücrelerinin her biri için TRUE döndürür

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

Karşılaştırma operanı olarak kullanılan boş bir hücre, öteki tarafın türünü alır: bir sayıya karşı 0'a, metne karşı ""e ve mantıksal değere karşı FALSE'a eşittir. A1 boşken =A1=0, =A1="" ve =A1=FALSE, Excel'de hep TRUE'dur. HotXLS aynı ikameyi iki operanı sıralamadan önce yapar; altı karşılaştırma operatörünün tamamı için ve iki motorda. İki operan da boşken hiçbir ikame olmaz ve eşit karşılaştırılırlar

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;                  // ilk sayfa etkin olan olur
  Sh.Range['B1', 'B1'].Value := 5;      // A1 bilerek boş bırakılır

  Writeln(VarToStr(Wb.Calculate('=A1=0')));      // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));     // True
  Writeln(VarToStr(Wb.Calculate('=A1=FALSE')));  // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));     // True: boş 0 olarak karşılaştırılır
  Writeln(VarToStr(Wb.Calculate('=A1<0')));      // False: 0, 0'ın altında değildir
end;

Pratikte ısıran satır sondur. Aritmetik zaten boşu 0 sayar; =A1+1 her motorde 1 verir. Kestirmenin yanlış gittiği yer karşılaştırmadır. "Boş"u, her sayının altında, kendi en düşük sırası olarak tutan bir motor =A1<0 için TRUE der ve sonra =IF(A1<0,"overdrawn","ok") her boş bakiye hücresini overdrawn diye etiketler. Yalnızca 0 ya da boş dizeyi ikame etmek de yetmez: bir booleana karşı boş, hem TRUE'nin hem FALSE'ın altında sıralanan 0 olurdu ve =A1=FALSE boş bir hücrede FALSE çıkardı

HotXLS boş operan ikamesi: boş bir A1, 0 ile ve boş metinle eşit karşılaştırır; booleana karşı boş FALSE olarak karşılaştırılır, böylece =A1=FALSE Excel'deki gibi TRUE olur; en düşük sıra olarak tutulan bir boş ise =A1<0ı her boş bakiye için TRUE yapardı
İkame, öteki operanın türüne uyar: 0, boş dize ya da FALSE — ham bir Null sıralaması, her boş bakiyeyi overdrawn diye etiketleyecek olandı

Plana alınmaya değer bir sonucu var. Boş, bir mantıksal değere karşı FALSE olarak karşılaştırıldığı için =A1=FALSE, Excel'de de HotXLS'te de boş bir hücreyi FALSE tutan hücreden ayırt edemez ve =A1=0, boşu sıfırdan ayırt edemez. Birinin gerçekten bir değer girip girmediğini bilmesi gereken şablonlar ISBLANK(A1) ile sınamalıdır. =A1="" biçimi daha zayıftır; boş bir dize döndüren bir formül için de TRUE'dur

Tek hücreli toplam aralıklı SUMIF neden tüm sütunu kapsar?

Excel ölçüt aralığının biçimini korur ve toplam aralığını yalnızca sol üst hücresi için kullanır; =SUMIF(A1:A10,">5",B1), B1:B10 demektir. Elde kurulu pek çok şablon o kısaltmaya, yazarları bilmeksizin dayanır. Bariz uygulama — yürüyüşü iki aralıktan küçüğe kıspmak — örneği A1'in B1'e karşı tek bir sınamasına indirger ve 0 döndürür. HotXLS ölçüt aralığını yürür ve her değeri, toplam aralığının sol üst köşesinden aynı satır ve sütun ofsetinde okur. AVERAGEIF aynı yürüyüşten geçer ve aynı büyütümü alır; ölçüt aralığından büyük bir toplam aralığı ise ölçüt biçimine kırpılır

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ücreli toplam aralığı
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)';  // açık toplam aralığı
    Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)';  // aynı büyütüm
    if Book.Recalculate = lxOk then
      // D1 ve D2: 4000 (600+700+800+900+1000); D3: 800
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ',
        VarToStr(Sheet.Cells[2, 4].Value), ' ', VarToStr(Sheet.Cells[3, 4].Value));
  finally
    Book.Free;
  end;
end;
HotXLS SUMIF ve AVERAGEIF büyütümü: =SUMIF(A1:A10,">5",B1), eşleşen ofsetlerde B1den B10a okuyarak on satırlık ölçüt aralığını yürür ve 4000 sonucunu bulur; tek hücreli toplam aralığına kırpılmış olsaydı 0 döndürürdü
Excel yalnızca toplam aralığının sol üst köşesini ödünç alır ve ölçüt biçimini korur; B1 geçen bir şablon B1:B10 demektir — SUMIF ile AVERAGEIF on ofsetin tamamını yürür ve aşırı büyük bir aralığı aynı şekilde kırpar

Kendinizin yazdığı şablonlar için toplam aralığının tamamını açıkça yazın. Motor kısaltmaya saygı duyar; çünkü mevcut dosyalar ona bağlıdır, ama formüldeki B1, sayfayı sonradan devralan kişiye tek bir hücre gibi okunur ve o kişi usulen yalnızca tek bir tutarın eklendiğini varsayar. Ortadaki ölçüt tek bir değer olarak değerlendirilirken iki dış argüman referanstır; argüman sınıfları arasındaki o ayrım örtük kesişim ve argüman sınıfları yazısında açıklanır. Filtrelenmiş ya da gizlenmiş satırları da atlaması gereken koşullu toplamlar başkaca kurallara uyar; SUBTOTAL ve AGGREGATE gizli satır yazısında ele alınır

INDIRECT referans biçimleri ve YEARFRAC basis 0

Daha küçük iki işlev aynı tür tuzağı taşır. INDIRECT isteğe bağlı ikinci bir argüman alır, a1; FALSE iken metin bir R1C1 referansıdır: =INDIRECT("R2C3",FALSE) C2 okur. Bayrağı yok sayan bir ayrıştırıcı "R2"yi R sütunu, 2. satır diye okur — bu gerçek bir hücredir — formül yanlış yerden inandırıcı bir değer döndürür. HotXLS bayrağı bir boolean, bir sayı (sıfır FALSE demektir) ya da "FALSE" metni olarak okur ve R1C1 metnini çözümlemeden önce çevirir; R[1]C[1] gibi köşeli parantezli ofsetler, formülü tutan hücreden ölçülür. Geçerli bir referanstan sonra arta kalan bir şey taşıyan metin — "B2 junk" gibi — B2'yi sessizce kullanmak yerine #REF! döndürür

Basis 0 ile YEARFRAC, US (NASD) 30/360 gün sayımını kullanır; DAYS360un uyguladığı Şubat sonu kuralları dahil: iki tarih de Şubat'ın son günü olduğunda bitiş günü 30 olur, sonra Şubat'ın son gününde bir başlangıç 30 olur. 2024-02-29'dan 2025-02-28'e bu 360 gün ve tam olarak 1 kesir verir. O kuralları atlayan düz bir 30/360 sayımı 359 gün ve 0.99722 verir; fark, Şubat sonunu kateden faiz birikimlerinde ortaya çıkar

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['C2', 'C2'].Value := 'row 2, column 3';

  Writeln(VarToStr(Wb.Calculate('=INDIRECT("R2C3",FALSE)')));   // satır 2, sütun 3
  Writeln(VarToStr(Wb.Calculate('=ISERROR(INDIRECT("B2 junk"))')));  // True: #REF!
  Writeln(VarToStr(Wb.Calculate(
    '=YEARFRAC(DATE(2024,2,29),DATE(2025,2,28),0)')));           // 1
end;

Böyle bir davranışı belgelendirirken ya da test ederken beklenen değerleri Excel'in kendisinden alın, formülün söylediğine görünmekten değil. "1, 2'den küçüktür, 2, 3'ten küçüktür" cümlesi =1<2<3in TRUE olduğunu ima eder; sağ-bağlantılı bir ayrıştırıcının tam olarak vereceği cevap budur ve Excel'in döndürdüğünün tersidir. Formülleri, Excel'in gösterdiği değerlerle yan yana listeleyen kısa bir tablo bu yazıdaki dört vakayı da yakalar: zincirler, boşlar, SUMIF büyütümü ve Şubat gün sayımı

HotXLS, makinede Excel olmaksızın Delphi ve C++Builder'dan XLS, XLSX, ODS ve CSV okur, yeniden hesaplar ve yazar; yukarıdaki karşılaştırma, boş hücre ve koşullu toplam kuralları, iki workbook sınıfının da paylaştığı hesaplama motoruna aittir. Desteklenen işlev listesi, sürümler ve lisanslama HotXLS Delphi spreadsheet component sayfasındadır