Rantai Pembanding, Sel Kosong, dan SUMIF di HotXLS Delphi

HotXLS Delphi Component mengevaluasi =1<2<3 sebagai FALSE, jawaban yang sama dengan Excel 16, karena sejak v2.384.3 parser formula-nya melipat operator pembanding dari kiri ke kanan: 1<2 menjadi TRUE, dan TRUE<3 adalah FALSE karena boolean berada di atas semua number. Release yang sama membuat operand kosong setara dengan 0 dan "", dan membiarkan SUMIF meregangkan rentang sum satu sel ke bentuk rentang kriterianya. Masing-masing terlihat seperti hal sepele sampai workbook yang dihitung di Delphi tidak sepakat dengan workbook yang sama yang dibuka di Excel

Ketidaksamaan itu biasanya bermula dari formula yang ditulis seseorang pakai intuisi. Seseorang mengetik =0<B2<100 untuk memeriksa bahwa sebuah quantity berada dalam rentang, Excel diam-diam menjawab FALSE untuk setiap baris, dan sheet ikut terkirim dengan bug itu yang sudah matang di dalamnya. Calculation engine tidak berhak memperbaiki maksud pengguna; tugasnya menghasilkan nilai yang akan dihasilkan Excel, supaya hasil cache yang HotXLS tulis ke berkas cocok dengan apa yang Excel perlihatkan setelah recalculation. Sebelum v2.384.3 HotXLS menjawab TRUE untuk pemeriksaan rentang itu di setiap baris, salah ke arah sebaliknya, dan laporan yang dibuat di server akan bertentangan dengan laporan yang sama yang dibuka di desktop

Mengapa =1<2<3 mengembalikan FALSE di Excel?

Excel mengembalikan FALSE karena membaca rantai pembanding sebagai (1<2)<3, dan TRUE di dalamnya lalu kalah kontes peringkat tipe melawan number 3. Parser HotXLS lama membaca teks yang sama sebagai 1<(2<3): TXLSSyntax.Parse_expr di lxFormula.pas meng-parse satu operand, melihat token pembanding, lalu berekursi ke Parse_expr untuk sisi kanan, yang menjadikan operator itu right-associative. Hasilnya 1<TRUE, dan number berada di bawah boolean, jadi hasilnya TRUE. Kesalahannya simetris: =3>2>1 adalah TRUE di Excel dan FALSE di HotXLS, dan =1=1=TRUE adalah TRUE di Excel dan FALSE sebelum fix. Regresi CalculateFormula_ComparisonChainsFoldLeftToRight menyematkan tujuh formula semacam itu terhadap nilai yang dikembalikan Excel 16, dan menjalankan semuanya lewat kedua arsitektur engine, TXLSWorkbook klasik dan TXLSXWorkbook native XLSX, memakai metode Calculate yang dijelaskan di gambaran umum formula engine HotXLS

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');
  // Yang Excel 16 kembalikan:  FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate mengevaluasi terhadap sheet aktif dan
    // mengembalikan Null saat workbook sama sekali tidak punya sheet
    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;

Fix itu mengubah Parse_expr menjadi loop dengan bentuk yang sama seperti yang sudah dipakai Parse_expr1 untuk +, -, dan &. Ia meng-parse operand pertama dengan Parse_expr1, dan selama token berikutnya adalah salah satu dari =, <>, <, >, <=, atau >=, ia membuat node pembanding, menempelkan hasil kiri yang terakumulasi sebagai anak pertama, meng-parse operand berikutnya dengan Parse_expr1 alih-alih Parse_expr, dan menjadikan node baru itu hasil kiri untuk ronde berikutnya. Dua detail mudah salah saat mengubah rekursi menjadi iterasi, dan keduanya ada di catatan maintainer: node terakumulasi harus diserahterimakan (lChild := Item; Item := nil) dengan urutan itu, dan jalur error harus Exit setelah membebaskan node setengah jadi, bukan keluar dari loop dan mengembalikan pohon yang menggantung

Bagaimana HotXLS memeringkat number, teks, dan boolean dalam pembanding?

HotXLS memeringkat tipe campuran seperti Excel: semua number lebih kecil dari semua nilai teks, dan semua nilai teks lebih kecil dari semua boolean. TXLSCalculator.CompareVariants di lxCalc.pas mengklasifikasi kedua operand dengan GetRetValueType ke enumerasi TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), dan saat kedua kelas berbeda ia cukup membandingkan ordinalnya, jadi urutan deklarasi enum itulah aturan lintas tipenya. Di dalam satu kelas pembandingannya yang natural, dengan satu twist khas Excel untuk teks: kedua string melewati lxUpperCase dulu, jadi ="abc"="ABC" adalah TRUE. Peringkat inilah sebabnya hasil rantai tidak bisa dipikirkan tanpanya. TRUE<3 bukan koersi TRUE menjadi 1, itu boolean dibandingkan dengan number, dan boolean yang menang. Tanggal adalah serial number bagi engine (varDate terklasifikasi sebagai xlNumberValue), jadi tanggal selalu di bawah teks apa pun, termasuk teks yang kebetulan tampak seperti tanggal

Sel kosong setara dengan apa dalam pembanding?

Sel kosong yang dipakai sebagai operand pembanding setara 0 saat sisi lain number, setara "" saat sisi lain teks, dan sejak v2.384.53 setara FALSE saat sisi lain nilai logis, jadi dengan A1 kosong, =A1=0, =A1="", dan =A1=FALSE semuanya TRUE. TXLSCalculator.CompareVarValues, yang melayani keenam operator pembanding, mensubstitusi kosongnya sebelum memanggil CompareVariants: kalau tepat satu operand Null, ia menjadi WideString('') saat pasangannya string, False saat pasangannya boolean, dan 0 selain itu. Dua kosong tetap saling setara tanpa substitusi. Jalur aritmetika sudah lama mengubah kosong menjadi 0, itulah sebabnya =A1+1 menghasilkan 1, tapi CompareVariants mempertahankan Null sebagai peringkat terbawahnya sendiri, di bawah semua number, dan operator pembanding memakai peringkat itu langsung

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 sengaja dibiarkan kosong

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True: kosong dibandingkan sebagai 0
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False; True sebelum v2.384.3
end;

Baris terakhir itulah yang menyakitkan di praktik. Di bawah peringkat lama, kosong lebih kecil dari semua number, termasuk yang negatif, jadi =IF(A1<0,"overdrawn","ok") melabeli setiap sel saldo kosong sebagai overdrawn, dan =A1=0 bernilai FALSE untuk sel yang oleh pengguna mana pun akan disebut nol. Satu batas tersisa setelah v2.384.3: substitusi hanya memilih antara 0 dan string kosong, jadi kosong yang dibandingkan dengan boolean menjadi 0, yang berperingkat di bawah TRUE maupun FALSE, dan =A1=FALSE di A1 kosong mengevaluasi ke FALSE. Sejak HotXLS 2.384.53 kosong yang dibandingkan dengan nilai logis diperlakukan sebagai FALSE di kedua engine XLS dan XLSX, seperti yang dilakukan Excel: dengan A1 kosong, =A1=FALSE dan =A1<TRUE mengembalikan TRUE dan =A1=TRUE mengembalikan FALSE. Itu juga berarti pembanding tidak bisa membedakan kosong dari FALSE, di Excel maupun di HotXLS; saat sheet butuh pembedaan itu, uji dengan ISBLANK atau =A1=""

Mengapa SUMIF dengan rentang sum satu sel mengembalikan 0?

SUMIF mengembalikan 0 karena HotXLS menjepit iterasi ke yang lebih kecil dari kedua rentang, sementara Excel mempertahankan bentuk rentang kriteria dan hanya memakai rentang sum untuk sel pojok kiri atasnya. =SUMIF(A1:A10,">5",B1) karena itu berarti B1:B10 di Excel, kenyamanan yang diandalkan banyak template buatan tangan. Worker bersama TXLSCalculator.GetValueItemRange2 dulu mengecilkan hitungan baris dan kolomnya ke milik rentang nilai, yang menyusutkan contoh jadi satu-satunya uji A1 terhadap B1. v2.384.3 melepas jepitan itu: loop kini menyusuri rentang kriteria dan membaca tiap nilai pada offset yang sama dari pojok kiri atas rentang sum. Karena CalcSumIF dan CalcAverageIF sama-sama memanggil worker itu, AVERAGEIF mendapat resize yang sama, dan rentang sum yang lebih besar dari rentang kriteria dipangkas ke bentuk kriteria karena alasan yang sama. Argumen kriteria di tengah adalah argumen kelas nilai dan kedua argumen luar kelas referensi, pembedaan yang dibahas di artikel tentang implicit intersection dan kelas argumen

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;          // kolom kriteria: 1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // amount: 100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // rentang sum satu sel
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // rentang sum eksplisit
    if Book.Recalculate = lxOk then
      // D1 dan D2 sama-sama 4000 (600+700+800+900+1000); D1 dulu 0 sebelum v2.384.3
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT dan YEARFRAC: dua koreksi yang lebih senyap

INDIRECT kini menghormati argumen keduanya, dan teks setelah referensi valid menjadi error alih-alih diabaikan. Dengan a1 FALSE, teksnya di-parse sebagai R1C1 absolut, jadi =INDIRECT("R2C3",FALSE) membaca C2; kode lama mengabaikan flag itu, membaca "R2" sebagai kolom R baris 2, dan diam-diam mengembalikan sel yang salah. Flag didispatch menurut tipe variannya (boolean, number, atau teks) karena mengonversi varian string langsung ke Double melempar exception. Teks R1C1 relatif seperti R[1]C[1] mengembalikan #REF!, karena INDIRECT tidak punya asal sel formula untuk menresolusinya, dan teks A1 dengan karakter ekor, "B2 junk", juga mengembalikan #REF!. YEARFRAC dengan basis 0 kini menerapkan aturan NASD akhir-Februari yang sudah diterapkan DAYS360: saat kedua tanggal adalah hari terakhir Februari, hari akhirnya menjadi 30, lalu start di hari terakhir Februari menjadi 30. Dari 2024-02-29 ke 2025-02-28 hitungannya kini 360 hari, pecahan tepat 1, di mana Days360US sebelumnya menghitung 359

Apa yang dijamin fix-fix ini, dan apa pelajarannya?

Perilaku rantai pembanding dijamin oleh tes yang membandingkan kedua engine dengan nilai yang diukur di Excel 16, dan tes itu ada karena deskripsi pertama tentang fix-nya salah. Catatan release v2.384.3 semula mengatakan bahwa pelipatan kiri-ke-kanan membuat =1<2<3 TRUE, yang persis itulah yang dihasilkan parser right-associative lama dan kebalikan dari yang dikembalikan Excel maupun kode baru. Tidak ada yang mengevaluasi contohnya; itu ditulis dari intuisi "1 kurang dari 2 kurang dari 3". Catatan itu dikoreksi dan tes tujuh formula ditambahkan di commit lanjutan, dan aturan yang keluar darinya berlaku bagi siapa pun yang mendokumentasikan semantik spreadsheet: jalankan contohnya di Excel sebelum Anda menuliskan nilai yang diharapkan. Substitusi operand kosong dan resize SUMIF mengikuti perilaku Excel yang sama, termasuk kasus kosong-versus-boolean sejak v2.384.53, dan agregat kondisional yang juga harus melewati baris terfilter atau tersembunyi mengikuti aturan terpisah di artikel baris tersembunyi SUBTOTAL dan AGGREGATE

HotXLS adalah komponen spreadsheet Delphi dan C++Builder native yang membaca, mengkalkulasi ulang, dan menulis XLS, XLSX, ODS, dan CSV tanpa Excel terpasang, dan aturan pembanding, kosong, serta SUMIF yang dibahas di sini tinggal di calculation engine yang dibagi kedua arsitektur workbook. Daftar fungsi lengkap dan opsi lisensi ada di halaman produk HotXLS Delphi spreadsheet component