Precision as Displayed HotXLS: Aturan Rounding Excel

Precision as displayed milik Excel membulatkan setiap angka tersimpan ke tempat desimal yang ditampilkan number format-nya: seksi format yang cocok dengan tanda nilai, dua tempat desimal ekstra per %, tiga lebih sedikit per koma penskalaan ribuan, membulatkan half away from zero. HotXLS menerapkan aturan yang sama di kedua engine Delphi-nya ketika TXLSXWorkbook.FullPrecision atau TXLSWorkbook.UseFullPrecision False. Kedengarannya seperti satu baris, sampai customer melaporkan total invoice hasil ekspor Anda berselisih satu sen dengan Excel, atau kolom durasi dalam format [ss].00 runtuh menjadi nol. Keduanya benar-benar terjadi, dan keduanya menelusuri balik ke salah satu aturan itu yang keliru. Sejak v2.384.57, kedua engine berbagi satu implementasi yang nilai harapannya diukur di Excel 16 dengan Workbook.PrecisionAsDisplayed dinyalakan

Apa yang sebenarnya diubah precision as displayed di sebuah workbook?

Precision as displayed adalah satu flag level workbook yang memerintahkan calculation engine menyimpan angka sebagaimana tampaknya, bukan sebagaimana dihitung. Di UI Excel ia berada di bawah File, Options, Advanced, "When calculating this workbook", sebagai "Set precision as displayed". Di disk ia satu bit. File BIFF8 membawanya di record CalcPrecision ($000E, [MS-XLS] §2.4.35), yang field fFullPrec-nya bernilai 1 untuk full precision normal dan 0 ketika opsi menyala. Paket XLSX membawanya sebagai atribut fullPrecision dari elemen calcPr di workbook.xml, didefinisikan di ECMA-376 Part 1, yang default-nya true dan fullPrecision="0" menyalakan rounding

Flag itu bukan preferensi tampilan. Saat Anda mencentang kotaknya, Excel memperingatkan bahwa data akan kehilangan akurasi secara permanen, dan itu serius: nilai ditulis ulang ke presisi tampilnya, dan digit yang terpotong lenyap. Mengosongkan kotaknya belakangan tak mengembalikan digit lama. 0.1234 yang tampil sebagai 12.3% menjadi 0.123 untuk selamanya

HotXLS membaca dan menulis flag itu di kedua format serta memaparkannya di kedua engine:

TXLSXWorkbook.FullPrecision: Boolean di engine XLSX, dimuat dari dan disimpan ke calcPr/@fullPrecision
TXLSWorkbook.UseFullPrecision: Boolean di engine Classic (juga di IXLSWorkbook), dimuat dari dan disimpan ke record CalcPrecision
Keduanya default True, mode aman yang tak destruktif sekaligus default Excel

Tempat HotXLS menerapkan rounding itu penting. HotXLS membulatkan di titik ia menghitung sebuah nilai: setiap hasil formula dibulatkan ke presisi tampilnya sebelum disimpan sebagai cached value sel, selama Recalculate dan selama evaluasi on-demand. Konstanta yang Anda assign lewat Value disimpan persis seperti diberikan. Kalau output Anda harus mereproduksi apa yang disimpan Excel setelah kotaknya dicentang, bulatkan konstanta-konstanta itu sendiri sebelum menulisnya, misalnya dengan helper yang ditunjukkan belakangan

Bagaimana Excel memutuskan berapa tempat desimal yang dipertahankan?

Excel menurunkan jumlah desimal yang dipertahankan dari seksi format spesifik yang menampilkan nilai, bukan dari format string secara keseluruhan. Aturan-aturan di bawah diukur di Excel 16 dan itulah yang diimplementasikan XlsApplyDisplayedPrecision di lxNumFormat untuk kedua engine HotXLS

Pilih seksi menurut tanda. Format dua seksi memakai seksi kedua untuk nilai negatif. Format dengan tiga seksi atau lebih memakai yang kedua untuk nilai negatif dan yang ketiga untuk nol persis. Sisanya memakai seksi pertama
Hitung placeholder desimal. Setiap 0, #, atau ? setelah titik desimal di seksi itu menambah satu desimal yang dipertahankan
Tambah dua per tanda persen. 0.0% menampilkan 0.1234 sebagai 12.3%, jadi nilai tersimpannya seperseratus dari yang Anda lihat dan mempertahankan tiga desimal, bukan satu
Kurangi tiga per koma penskalaan. Koma setelah placeholder integer terakhir (0,, 0.0,, 0,.0) membagi tampilan dengan 1000. 0.0, menampilkan 12345.678 sebagai 12.3, jadi Excel mempertahankan satu desimal dikurangi tiga, hitungan negatif: nilai dibulatkan ke ratusan dan disimpan sebagai 12300. Koma di antara placeholder integer, seperti di #,##0, adalah pengelompokan digit biasa dan tak mengubah apa pun
Biarkan seksi non-numerik. Seksi General, tanggal dan waktu (termasuk elapsed [h], [mm], dan [ss]), scientific, fraction, dan seksi teks, serta seksi tanpa placeholder digit apa pun mempertahankan full precision

Hitungan digit berasal dari seksi yang cocok dengan tanda, plus dua per persen dan minus tiga per koma penskalaan, dan hitungan negatif membulatkan ke puluhan atau ratusan; seksi General dan tanggal dibiarkan

Diukur terhadap Excel 16, inilah nilai yang kini disimpan kedua engine HotXLS untuk hasil formula di tiap format:

Format angka	Nilai terhitung	Nilai tersimpan	Aturan yang berlaku
`0.0%`	0.1234	0.123	Satu desimal plus dua untuk tanda persen
`0`	2.5	3	Half away from zero, bukan ke genap
`0`	-2.5	-3	Half away from zero juga di sisi negatif
`0.00;(0.0)`	-1.2345	-1.2	Seksi negatif menampilkan satu desimal
`0.00;(0.0)`	1.2345	1.23	Seksi positif menampilkan dua desimal
`#,##0.0`	1234.5678	1234.6	Koma pengelompokan, tanpa penskalaan
`0.0,`	12345.678	12300	Satu desimal dikurangi tiga: bulatkan ke ratusan
`0.0%;(0.00%)`	-0.0125	-0.0125	Seksi negatif mempertahankan dua plus dua desimal
`0.00`	1.005	1.01	Toleransi atas error representasi biner
`0;-0;0.0`	0.5	1	Bukan nol, jadi seksi positif yang memutuskan

Baris terakhir adalah jebakan yang menarik. Nilai 0.5 dibulatkan ke bilangan bulat, dan seksi nol tak pernah berperan, karena Excel memilih seksi dari nilai terhitung sebelum membulatkan. Satu keterbatasan yang jujur di sisi HotXLS: seksi dipilih hanya menurut tanda, jadi format yang seksinya membawa kondisi kurung kustom seperti [>=1000] tetap dipecah menurut tanda. Periksa format semacam itu terhadap Excel kalau penting bagi Anda

Mengapa 1.005 dibulatkan ke 1.01 dan bukan ke 1.00?

Excel membulatkan 1.005 di sel 0.00 menjadi 1.01 padahal double terdekat dari 1.005 sedikit di bawah titik tengah, dan HotXLS mencocokkannya dengan toleransi beberapa ulp. Literal 1.005 tak terwakili dalam floating point biner. Double IEEE 754 terdekat adalah 1.00499999999999989341858963598497211933135986328125, dan dikali 100 hasilnya 100.49999999999999. Floor(x * 100 + 0.5) / 100 ala buku teks karenanya mengembalikan 1.00, yang berselisih dengan angka yang diketik user, dengan yang ditampilkan Excel, dan dengan yang disimpan Excel

Delphi menambah twist sendiri. System.Round membulatkan seri ke genap, jadi Round(2.5) adalah 2 dan Round(3.5) adalah 4. Itu banker's rounding, default yang masuk akal untuk statistik dan aturan yang salah di sini: Excel menyimpan 3 untuk 2.5 di sel 0 dan -3 untuk -2.5. Implementasi HotXLS bekerja pada nilai absolut, menambah 0.5 plus toleransi relatif 2^-51 kali nilai ter-skala (beberapa ulp pada magnitudo itu, tak pernah kurang dari dua ulp dari 1.0), memotong, menskala balik, dan memulihkan tanda. Fungsi berikut adalah ilustrasi mandiri dari prinsip itu, bukan kode library-nya sendiri, dan ia menangani hitungan digit negatif untuk koma penskalaan dengan cara yang sama:

Diagram rounding HotXLS: 2.5 dibulatkan half away from zero menjadi 3 dan -2.5 menjadi -3, tempat System.Round Delphi memberi jawaban banker 2 dan -2, dan karena double terdekat dari 1.005 duduk tepat di bawah titik tengah, toleransi beberapa ulp itulah yang mengubah 1.00 berbasis floor menjadi jawaban Excel 1.01 — Excel membulatkan seri menjauh dari nol dan memaafkan error representasi biner dengan toleransi kecil; kedua detail itu terukur, dan melewatkan salah satunya menyimpan 2 untuk 2.5 atau 1.00 untuk 1.005, satu sen dari Excel

// Sketsa prinsip: bulatkan half away from zero ke ADigits tempat desimal,
// dengan toleransi beberapa ulp agar 1.005 mencapai 1.01.
// ADigits < 0 membulatkan ke puluhan, ratusan, ... ("0.0," menghasilkan -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dua ulp dari 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // di luar presisi double: biarkan nilainya
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // penskalaan akan overflow
    Scaled := Abs(AValue) * Scale;
  end
  else
    Scaled := Abs(AValue) / Scale;
  Eps := Scaled * Tolerance;
  if Eps < Tolerance then
    Eps := Tolerance;
  Scaled := Int(Scaled + 0.5 + Eps); // half away from zero, bukan Round()
  if ADigits >= 0 then
    Result := Scaled / Scale
  else
    Result := Scaled * Scale;
  if AValue < 0 then
    Result := -Result;
end;

// RoundAsDisplayed(1.005, 2)      = 1.01   (berbasis Floor: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 digit)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 digit)

Toleransi itu adalah trade-off yang disengaja. Nilai yang benar-benar dua ulp di bawah setengah langkah juga ikut dibulatkan ke atas, tapi pada jarak itu selisihnya tak bisa dibedakan dari error representasi, dan memperlakukannya sebagai setengah langkah itulah yang membuat desimal hasil ketikan berperilaku seperti yang diharapkan user

Apa yang salah sebelum v2.384.57?

Sebelum v2.384.57, engine XLSX dan engine Classic masing-masing punya kode precision-as-displayed sendiri, dan masing-masing salah dengan cara berbeda. Kalau Anda memproduksi workbook dengan opsi menyala, inilah gejala yang harus dicari di file hasil build lama

Engine XLSX: hanya seksi pertama, tanpa persen, banker's rounding

Jalur XLSX lama meminta jumlah desimal format string secara keseluruhan, yang hanya melihat seksi pertama dan mengabaikan %, lalu membulatkan dengan Round. 0.1234 di 0.0% tersimpan sebagai 0.1, yaitu 10% alih-alih 12.3% di layar. 2.5 di 0 tersimpan sebagai 2 alih-alih 3. Nilai negatif di format seperti 0.00;(0.0) dibulatkan ke dua desimal seksi positif. Sejak v2.384.57, engine XLSX memanggil routine bersama yang sama dengan engine Classic, yang juga mendapat dukungan koma penskalaan di rilis itu

Engine Classic: TRUE menjadi -1

Engine Classic mengawal rounding-nya dengan VarIsNumeric, dan VarIsNumeric mengembalikan True untuk Variant varBoolean. Mengonversi Variant itu dengan Double(V) menghasilkan -1, karena Boolean True bergaya COM tersimpan sebagai -1. Formula seperti =A1>0 di sel berformat 0.00 karenanya keluar dari penghitungan ulang sebagai angka -1. Sejak v2.384.57, hasil Boolean dikecualikan sebelum tes numerik apa pun, dan hasil logical tetap hasil logical di kedua engine

Format elapsed time terbaca sebagai warna (v2.384.9)

Bug ketiga duduk di model number-format alih-alih di rounding. Parser mengklasifikasi setiap token berkurung yang bukan kondisi sebagai warna, sehingga [h], [mm], dan [ss] tak pernah menandai seksinya sebagai date/time. Tampilan tak terpengaruh, karena formatting berjalan di jalur terpisah, tapi precision as displayed mengandalkan flag itu untuk melewati nilai waktu. Durasi lima detik adalah 5/86400 hari, sekitar 0.0000579, dan format seperti [ss].00 tampak seperti angka dua desimal biasa, sehingga dengan FullPrecision off, durasinya dibulatkan menjadi 0.00 hari. Sejak v2.384.9, run berkurung berupa satu huruf h, m, atau s di-parse sebagai token elapsed time dan seksinya diperlakukan sebagai date/time. Rilis yang sama memperbaiki deteksi menit di h:mm, tempat titik dua di antara token dulu menyembunyikan jam dari parser

Diagram HotXLS atas salah parse elapsed time: lima detik tersimpan sebagai pecahan hari yang kecil di sel berformat token ss berkurung, yang oleh parser lama terbaca sebagai warna dan ditandai sebagai angka dua desimal biasa, sehingga precision as displayed membulatkan durasinya ke 0.00 sampai ia di-parse sebagai seksi elapsed time — Formatting berjalan di jalurnya sendiri, jadi sel tampak benar sementara nilai tersimpannya terbulatkan ke nol; satu huruf berkurung h, m, atau s adalah token elapsed time, bukan warna, dan seksi itu mempertahankan full precision

Mengaktifkan precision as displayed di HotXLS dari Delphi

Untuk mendapat nilai tersimpan setara Excel, set flag itu sebelum penghitungan ulang yang harus menghormatinya, lalu baca hasil ter-cache atau simpan. Di engine XLSX, FullPrecision adalah flag polos: mengubahnya tak meng-invalidasi hasil yang sudah disimpan oleh Recalculate sebelumnya, jadi set tepat setelah Create atau Open dan sebelum Recalculate pertama. Contohnya memakai formula karena di sanalah HotXLS menerapkan rounding:

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Sh := Wb.Sheets.Add('Totals');
    Sh.Cells[1, 1].Value := 0.1234;
    Sh.Cells[2, 1].Value := 2.5;
    Sh.Cells[3, 1].Value := 12345.678;

    Sh.Cells[1, 2].Formula := '=A1';
    Sh.Cells[1, 2].NumberFormat := '0.0%';   // tampil 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // tampil 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // tampil 12.3 (ribuan)

    // Harus diset sebelum Recalculate pertama di engine XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Hasil ter-cache kini cocok dengan Excel 16: 0.123, 3, dan 12300.
    // Konstanta di kolom A mempertahankan full precision-nya.
    Assert(Abs(Double(Sh.Cells[1, 2].Value) - 0.123) < 1E-12);
    Assert(Double(Sh.Cells[2, 2].Value) = 3);
    Assert(Double(Sh.Cells[3, 2].Value) = 12300);

    Wb.SaveAs('totals.xlsx'); // menulis <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Engine Classic berperilaku sama, dengan satu kenyamanan: meng-assign TXLSWorkbook.UseFullPrecision menandai setiap formula di dependency graph dirty, sehingga Recalculate berikutnya mengevaluasi ulang seluruh workbook di bawah aturan baru. Mengubah NumberFormat saat opsi menyala juga menandai sel formula yang terdampak dirty, karena format kini menentukan nilai tersimpannya. Perhatikan bahwa Recalculate Classic mengembalikan jumlah sel formula yang tak bisa ia evaluasi, jadi nol berarti sukses:

var
  Wb: TXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  try
    Sh := Wb.Sheets.Add;
    Sh.Range['A1', 'A1'].Value := -1.2345;
    Sh.Range['B1', 'B1'].Formula := '=A1';
    Sh.Range['B1', 'B1'].NumberFormat := '0.00;(0.0)';
    Sh.Range['C1', 'C1'].Formula := '=A1<0';
    Sh.Range['C1', 'C1'].NumberFormat := '0.00';

    Wb.UseFullPrecision := False; // menandai setiap formula dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: seksi negatif "(0.0)" menampilkan satu desimal
    // C1 tetap Boolean True (build sebelum v2.384.57 menyimpan -1)
    Wb.SaveAs('report.xls'); // record CalcPrecision dengan fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Kedua engine juga menghormati flag yang datang bersama file. Buka workbook yang disimpan dengan opsi menyala dan FullPrecision atau UseFullPrecision sudah False, sehingga Recalculate setelah memuat membulatkan persis seperti yang akan dilakukan Excel. Kalau Anda hanya perlu membaca angka yang sudah disimpan Excel, Anda bisa melewati penghitungan ulang sepenuhnya, seperti dijelaskan di membaca cached value formula tanpa penghitungan ulang. Untuk cara serial number dan format tanggal berinteraksi dengan model format yang menggerakkan pemeriksaan date/time, lihat serial tanggal Excel, sistem 1904, dan numFmt di Delphi

Kapan precision as displayed sebaiknya dinyalakan, dan kapan tidak?

Nyalakan precision as displayed hanya ketika angka tersimpan workbook harus sama dengan angka tampilnya, dan Anda menerima kehilangan digit ekstra selamanya. Kasus sah klasiknya adalah jadwal keuangan yang kolom-kolom jumlah terbulatkan harus berjumlah total terbulatkan di layar, tanpa pecahan sen tersembunyi yang menghasilkan total meleset satu di tempat terakhir. Menyesuaikan workbook milik customer yang sudah menyetel opsinya adalah alasan bagus yang satunya, dan HotXLS mempertahankan flag itu saat round-trip sehingga Anda tak diam-diam mengembalikan mereka ke full precision

Hindari di sebagian besar situasi lain:

Data engineering dan sains. Membulatkan hasil pengukuran karena seseorang memilih format dua desimal untuk laporan menghancurkan informasi yang tak bisa dipulihkan oleh perubahan format belakangan mana pun
Persen dengan format kasar. Format 0% hanya mempertahankan dua desimal dari rasio tersimpan, sehingga 0.1234 menjadi 0.12, dan setiap formula di hilir yang membaca sel itu bekerja dengan 0.12
Tampilan ter-skala. Format 0, atau 0.0, yang dipakai menampilkan ribuan membulatkan nilai tersimpan ke ribuan atau ratusan, yang jarang menjadi maksud orang yang memilih format itu
Template bersama. Flag itu berlaku seluruh workbook. Siapa pun yang belakangan menambah sheet mewarisi perilakunya, biasanya tanpa tahu ia menyala

Kalau yang Anda inginkan sebenarnya adalah hasil terbulatkan di beberapa sel tertentu, tulis ROUND ke formula-formula itu saja. ROUND eksplisit, lokal di sel, terlihat oleh siapa pun yang membaca formula, dan dievaluasi oleh formula engine HotXLS seperti fungsi lain, tanpa efek samping seluruh workbook

Referensi cepat precision as displayed

Flag file: CalcPrecision $000E dengan fFullPrec = 0 di BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" di XLSX (ECMA-376 Part 1)
Sakelar HotXLS: TXLSXWorkbook.FullPrecision := False dan TXLSWorkbook.UseFullPrecision := False, keduanya default True
Seksi: dipilih menurut tanda nilai terhitung; seksi ketiga hanya untuk nol persis
Digit: placeholder desimal, plus dua per %, minus tiga per koma penskalaan; hitungannya bisa negatif
Rounding: half away from zero dengan toleransi beberapa ulp, sehingga 2.5 menghasilkan 3, -2.5 menghasilkan -3, dan 1.005 menghasilkan 1.01
Dilewati: General, date/time dan elapsed time, scientific, fraction, teks, Boolean, dan error value
Cakupan di HotXLS: hasil formula saat dihitung; konstanta disimpan apa adanya saat di-assign
Engine XLSX: set FullPrecision sebelum Recalculate pertama; setter Classic me-dirty-kan ulang semua formula sendiri
Versi: disamakan dengan Excel 16 di kedua engine sejak v2.384.57; format elapsed time terlindungi sejak v2.384.9

HotXLS membaca, menulis, dan menghitung workbook XLS dan XLSX secara native dari Delphi dan C++Builder, termasuk opsi kalkulasi workbook yang dibahas di sini. Detail, edisi, dan unduhan trial ada di halaman komponen spreadsheet Delphi HotXLS