HotXLS gösterilen hassasiyet: Excel yuvarlama kuralları

Excel precision as displayed, saklanan her sayıyı sayı biçiminin gösterdiği ondalıklara yuvarlar: değerin işaretine uyan biçim bölümü, her % için iki fazla ondalık, her binlik ölçekleme virgülü için üç eksik, sıfırdan uzağa yarım yuvarlama. HotXLS aynı kuralı iki Delphi motorunda da uygular; TXLSXWorkbook.FullPrecision ya da TXLSWorkbook.UseFullPrecision False iken. Bir satırlık iş gibi görünür; ta ki bir müşteri, dışa aktardığınız fatura toplamlarının Excel'le bir kuruş uyuşmadığını ya da [ss].00 biçimli bir süre sütununun sıfıra çöktüğünü bildirene dek. İkisi de oldu ve ikisi de o kurallardan birini yanlış almaya dayanır. v2.384.57'den beri iki motor, beklenen değerleri Excel 16'da Workbook.PrecisionAsDisplayed açıkken ölçülmüş tek bir gerçeklemeyi paylaşır

Precision as displayed bir çalışma kitabında gerçekte neyi değiştirir?

Precision as displayed, hesaplama motoruna sayıları hesaplandıkları gibi değil göründükleri gibi saklamasını söyleyen tek bir çalışma kitabı düzeyi bayraktır. Excel arayüzünde Dosya, Seçenekler, Gelişmiş, "Bu çalışma kitabı hesaplanırken" altında, "Set precision as displayed" olarak durur. Diskte bir bittir. Bir BIFF8 dosyası onu CalcPrecision record'unda taşır ($000E, [MS-XLS] §2.4.35); fFullPrec alanı normal tam hassasiyet için 1, seçenek açıkken 0'dır. Bir XLSX paketi onu workbook.xml'deki calcPr elementinin fullPrecision attribute'u olarak taşır; ECMA-376 Part 1'de tanımlanır, varsayılan true'dur ve fullPrecision="0" yuvarlamayı açar

Bayrak bir görüntü tercihi değildir. Kutuyu işaretlediğinizde Excel, verinin kalıcı olarak doğruluk kaybedeceği uyarısını verir ve bunu kasteder: değerler görüntülenen hassasiyetlerine yeniden yazılır ve kesilen basamaklar gider. Kutuyu sonra temizlemek eski basamakları geri getirmez. 12.3% diye gösterilen 0.1234, sonsuza dek 0.123 olur

HotXLS bayrağı iki biçimde de okur yazar ve iki motorda da açar:

• XLSX motorunda TXLSXWorkbook.FullPrecision: Boolean; calcPr/@fullPrecision'den yüklenir ve oraya kaydedilir
• Klasik motorda TXLSWorkbook.UseFullPrecision: Boolean (IXLSWorkbook üzerinde de); CalcPrecision record'undan yüklenir ve oraya kaydedilir
• İkisi de True'ya varsayılan; güvenli, yıkıcı olmayan mod budur ve Excel'in varsayılanıdır

HotXLS'in yuvarlamayı nerede uyguladığı önemlidir. HotXLS, bir değeri hesapladığı noktada yuvarlar: her formül sonucu, Recalculate sırasında ve talep üzerine değerlendirme sırasında, hücrenin cache değeri olarak saklanmadan önce görüntülenen hassasiyetine yuvarlanır. Value üzerinden atadığınız sabitler verildikleri gibi saklanır. Çıktınızın, kutu işaretlendikten sonra Excel'in sakladığını aynen üretmesi gerekiyorsa o sabitleri yazmadan önce kendiniz yuvarlayın; örneğin daha sonra gösterilen yardımcı ile

Excel kaç ondalık tutulacağına nasıl karar verir?

Excel tutulacak ondalık sayısını, değeri gösteren belirli biçim bölümünden türetir; biçim string'inin bütününden değil. Aşağıdaki kurallar Excel 16'da ölçüldü ve iki HotXLS motoru için lxNumFormat'teki XlsApplyDisplayedPrecision'in gerçeklediği şeydir

1. Bölümü işarete göre seçin. İki bölümlü bir biçim, negatif değerler için ikinci bölümü kullanır. Üç ya da daha fazla bölümlü bir biçim, negatifler için ikinciyi ve tam sıfır için üçüncüyü kullanır. Gerisi birinci bölümü kullanır
2. Ondalık yer tutucularını sayın. O bölümde ondalık noktasından sonraki her 0, # ya da ? bir tutulan ondalık ekler
3. Her yüzde işareti için iki ekleyin. 0.0%, 0.1234'ü %12,3 gösterir; dolayısıyla saklanan değer gördüğünüzün yüzde biridir ve üç ondalık tutar, bir değil
4. Her ölçekleme virgülü için üç çıkarın. Son tam sayı yer tutucusundan sonraki bir virgül (0,, 0.0,, 0,.0) gösterimi 1000'e böler. 0.0,, 12345.678'i 12.3 gösterir; dolayısıyla Excel bir ondalık eksi üçü tutar, ki bu negatif bir sayıdır: değer yüzlerlere yuvarlanır ve 12300 diye saklanır. Tam sayı yer tutucuları arasındaki bir virgül, #,##0'daki gibi, sade basamak gruplamasıdır ve hiçbir şeyi değiştirmez
5. Sayısal olmayan bölümleri rahat bırakın. General; tarih ve saat bölümleri (geçen süre [h], [mm], [ss] dâhil); bilimsel, kesir ve metin bölümleri ve hiçbir basamak yer tutucusu taşımayan bölümler tam hassasiyeti korur

Excel 16'ya karşı ölçüldü; işte iki HotXLS motorunun da bugün her biçimdeki bir formül sonucu için sakladığı değerler:

Sayı biçimi	Hesaplanan değer	Saklanan değer	Uygulanan kural
0.0%	0.1234	0.123	Yüzde işareti için bir ondalık artı iki
0	2.5	3	Sıfırdan uzağa yarım, çift olana değil
0	-2.5	-3	Negatif tarafta da sıfırdan uzağa yarım
0.00;(0.0)	-1.2345	-1.2	Negatif bölüm tek ondalık gösterir
0.00;(0.0)	1.2345	1.23	Pozitif bölüm iki ondalık gösterir
#,##0.0	1234.5678	1234.6	Gruplama virgülü, ölçekleme yok
0.0,	12345.678	12300	Bir ondalık eksi üç: yüzliklere yuvarla
0.0%;(0.00%)	-0.0125	-0.0125	Negatif bölüm iki artı iki ondalık tutar
0.00	1.005	1.01	İkili gösterim hatası için tolerans
0;-0;0.0	0.5	1	Sıfır değil, dolayısıyla pozitif bölüm karar verir

Son satır hoş bir tuzaktır. 0.5 değeri bir tam sayıya yuvarlanır ve sıfır bölümü hiç devreye girmez; çünkü Excel bölümü, yuvarlamadan önce hesaplanan değerden seçer. HotXLS tarafında dürüst bir sınır: bölümler yalnızca işaretle seçilir; dolayısıyla bölümleri [>=1000] gibi özel köşeli koşullar taşıyan bir biçim yine de işaretle bölünür. Böyle biçimler sizin için önemliyse Excel'e karşı denetleyin

1.005 neden 1.00'a değil 1.01'e yuvarlanır?

Excel, 0.00 hücresindeki 1.005'i 1.01'e yuvarlar; 1.005'e en yakın double, orta noktanın hafif altında olsa bile. HotXLS bunu birkaç ulp'lik bir toleransla eşler. 1.005 literali ikili kayan noktada temsil edilemez. En yakın IEEE 754 double 1.00499999999999989341858963598497211933135986328125'dir ve 100 ile çarpımı 100.49999999999999 verir. Ders kitabı tarzı bir Floor(x * 100 + 0.5) / 100 bu yüzden 1.00 döndürür; kullanıcının yazdığı sayıyla, Excel'in gösterdiğine ve Excel'in sakladığıyla çelişir

Delphi'nin kendine göre bir kıvrımı daha var. System.Round, eşitlikleri çift olana yuvarlar; Round(2.5) 2 ve Round(3.5) 4 olur. Bu banker yuvarlamasıdır; istatistik için makul bir varsayılan ama burada yanlış kural: Excel, 0 hücresinde 2.5 için 3 ve -2.5 için -3 saklar. HotXLS gerçeklemesi mutlak değer üzerinde çalışır, ölçeklenmiş değerin 2^-51 katı kadar göreli bir toleransla 0.5 ekler (o büyüklükte birkaç ulp; asla 1.0'ın iki ulp'inden az değil), keser, geri ölçekler ve işareti geri koyar. Aşağıdaki fonksiyon o ilkenin kendi kendine yeten bir gösterimidir, kütüphane kodunun kendisi değildir; ölçekleme virgüllerinin negatif basamak sayılarını da aynı biçimde ele alır:

// İlke taslağı: ADigits ondalığına sıfırdan uzağa yarım yuvarlama,
// 1.005'in 1.01'e ulaşması için birkaç ulp'lik toleransla.
// ADigits < 0 onluklara, yüzliklere yuvarlar ... ("0.0," -2 verir)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, 1.0 için iki ulp
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // double hassasiyetinin ötesinde: değere dokunma
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // ölçekleme taşardı
    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); // sıfırdan uzağa yarım, Round() değil
  if ADigits >= 0 then
    Result := Scaled / Scale
  else
    Result := Scaled * Scale;
  if AValue < 0 then
    Result := -Result;
end;

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

Tolerans kasıtlı bir tavizdir. Bir yarım adımın gerçekten iki ulp altında olan bir değer de yukarı yuvarlanır; ama o mesafede fark, gösterim hatasından ayırt edilemez ve onu yarım adım saymak, yazılan ondalıkların kullanıcıların beklediği gibi davranmasını sağlayan şeydir

v2.384.57 öncesinde ne yanlış gidiyordu?

v2.384.57 öncesinde XLSX motoru ile Klasik motorun her birinin kendi precision-as-displayed kodu vardı ve her biri başka türlü yanlıştı. Seçenek açıkken çalışma kitapları üretiyorsanız eski derlemelerin ürettiği dosyalarda aranacak belirtiler şunlar

XLSX motoru: yalnızca ilk bölüm, yüzde yok, banker yuvarlaması

Eski XLSX yolu, biçim string'inin bütününün ondalık sayısını istiyordu; bu, yalnızca ilk bölüme bakıyor ve %'i yok sayıyordu, sonra Round ile yuvarlıyordu. 0.0%'teki bir 0.1234, 0.1 olarak saklanıyordu; ekrandaki %12,3 yerine %10. 0'daki bir 2.5, 3 yerine 2 olarak saklanıyordu. 0.00;(0.0) gibi bir biçimdeki negatif değerler, pozitif bölümün iki ondalığına yuvarlanıyordu. v2.384.57'den beri XLSX motoru, Klasik motorla aynı paylaşılan rutini çağırır; o da o sürümde ölçekleme virgülü desteği kazandı

Klasik motor: TRUE, -1 oldu

Klasik motor yuvarlamasını VarIsNumeric ile koruyordu ve VarIsNumeric, bir varBoolean Variant için True döndürür. O Variant'ı Double(V) ile çevirmek -1 verir; çünkü COM tarzı bir Boolean True, -1 olarak saklanır. 0.00 biçimli bir hücredeki =A1>0 gibi bir formül bu yüzden yeniden hesaplamadan -1 sayısı olarak çıkıyordu. v2.384.57'den beri Boolean sonuçlar herhangi bir sayısal testten önce dışlanır ve mantıksal bir sonuç, iki motorda da mantıksal sonuç olarak kalır

Geçen süre biçimleri renk diye okunuyordu (v2.384.9)

Üçüncü hata, yuvarlamada değil sayı-biçimi modelinde oturuyordu. Ayrıştırıcı, koşul olmayan her köşeli parantezli token'ı renk sınıflandırıyordu; dolayısıyla [h], [mm] ve [ss], bölümlerini hiçbir zaman tarih/saat olarak işaretlemedi. Görüntü etkilenmedi, çünkü biçimlendirme ayrı bir yolda koşar; ama precision as displayed, saat değerlerini atlamak için o bayrağa bel bağlar. Beş saniyelik bir süre, günün 5/86400'üdür; yaklaşık 0.0000579. [ss].00 gibi bir biçim sıradan iki ondalıklı bir sayıya benziyordu; dolayısıyla FullPrecision kapalıyken süre 0.00 güne yuvarlanıyordu. v2.384.9'dan beri tek bir h, m ya da s harfinin köşeli parantezli dizisi bir geçen-süre token'ı diye ayrıştırılır ve bölüm tarih/saat sayılır. Aynı sürüm, token'lar arasındaki iki nokta üst üste saat bilgisi ayrıştırıcıdan kaçırdığı h:mm'deki dakika saptamasını da düzeltti

HotXLS'te precision as displayed'i Delphi'den açmak

Excel eşdeğeri saklanan değerler için bayrağı, onu izleyecek olan yeniden hesaplamadan önce set edin; sonra cache'lenmiş sonuçları okuyun ya da kaydedin. XLSX motorunda FullPrecision düz bir bayraktır: değiştirmek, önceki bir Recalculate'in çoktan sakladığı sonuçları geçersizleştirmez; dolayısıyla Create ya da Open'un hemen ardından ve ilk Recalculate'ten önce set edin. Örnek formülleri kullanır; çünkü HotXLS yuvarlamayı orada uygular:

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%';   // 12.3% gösterir
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // 3 gösterir
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // 12.3 gösterir (binlik)

    // XLSX motorunda ilk Recalculate'ten önce set edilmeli
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Cache'lenmiş sonuçlar artık Excel 16 ile aynı: 0.123, 3 ve 12300.
    // A sütunundaki sabitler tam hassasiyetlerini korur.
    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'); // <calcPr fullPrecision="0"/> yazar
  finally
    Wb.Free;
  end;
end;

Klasik motor aynı davranır, tek bir kolaylıkla: TXLSWorkbook.UseFullPrecision ataması, bağımlılık grafiğindeki her formülü kirli işaretler; dolayısıyla bir sonraki Recalculate, bütün çalışma kitabını yeni kural altında yeniden değerlendirir. Seçenek açıkken bir NumberFormat değiştirmek de etkilenen formül hücrelerini kirli işaretler; çünkü biçim artık saklanan değere karar verir. Klasik Recalculate, değerleyemediği formül hücrelerinin sayısını döndürür; dolayısıyla sıfır başarı demektir:

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; // her formülü kirli işaretler
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: negatif bölüm "(0.0)" tek ondalık gösterir
    // C1 Boolean True kalır (v2.384.57 öncesi derlemeler -1 saklıyordu)
    Wb.SaveAs('report.xls'); // fFullPrec = 0'lı CalcPrecision record'u
  finally
    Wb.Free;
  end;
end;

İki motor da dosyayla gelen bayrağı izler. Seçenek açıkken kaydedilmiş bir çalışma kitabı açın; FullPrecision ya da UseFullPrecision çoktan False'tur ve yükleme sonrası bir Recalculate, Excel'in yapacağı tam biçimde yuvarlar. Excel'in çoktan sakladığı sayıları okumakla yetinecekseniz yeniden hesaplamayı tümüyle atlayabilirsiniz; yeniden hesaplama olmadan cache'lenmiş formül değerlerini okuma yazısında anlatıldığı gibi. Seri sayıların ve tarih biçimlerinin, tarih/saat kontrolünü süren biçim modeliyle nasıl etkileştiği için Excel tarih serileri, 1904 sistemi ve Delphi'de numFmt yazısına bakın

Precision as displayed ne zaman açılmalı, ne zaman açılmamalı?

Precision as displayed'i yalnızca çalışma kitabının saklanan sayıları gösterilen sayılarına eşit olmak zorundaysa ve fazladan basamakları sonsuza dek kaybetmeyi kabullenüyorsanız açın. Klasik meşru durum, yuvarlanmış tutar sütunlarının ekrandaki yuvarlanmış toplama eklenmesi gereken, son basamakta bir kayma üreten gizli kuruş kesirlerinin olmadığı bir finansal çizelgedir. Müşterinin seçenek çoktan set edilmiş mevcut çalışma kitabıyla eşleşmek öbür iyi nedendir; HotXLS bayrağı round-trip'te korur ki onları sessizce tam hassasiyete geri döndürmeyin

Öteki çoğu durumda kaçının:

• Mühendislik ve bilimsel veri. Bir ölçümü, biri rapor için iki ondalıklı biçim seçti diye yuvarlamak, sonraki hiçbir biçim değişikliğinin geri getiremeyeceği bilgiyi yok eder
• Kaba biçimli yüzdeler. Bir 0% biçimi, saklanan oranın yalnızca iki ondalığını tutar; 0.1234, 0.12 olur ve hücreyi okuyan akış aşağısındaki her formül 0.12 ile çalışır
• Ölçeklenmiş görüntüler. Binlikleri göstermek için kullanılan bir 0, ya da 0.0, biçimi, saklanan değeri binliklere ya da yüzliklere yuvarlar; biçimi seçen kişinin kastettiği nadiren budur
• Paylaşılan şablonlar. Bayrak çalışma kitabı genişidir. Sonradan bir sheet ekleyen herkes davranışı devralır; çoğunlukla açık olduğundan habersiz

Asıl istediğiniz birkaç belirli hücrede yuvarlanmış sonuçlarsa, onun yerine o formüllere ROUND yazın. ROUND açıktır, hücreye özgüdür, formülü okuyan herkese görünürdür ve HotXLS formül motoru tarafından öteki her fonksiyon gibi değerlendirilir; çalışma kitabı geniş yan etkisi yoktur

Precision as displayed hızlı başvurusu

• Dosya bayrağı: BIFF8'de fFullPrec = 0'lı CalcPrecision $000E ([MS-XLS] §2.4.35), XLSX'te calcPr fullPrecision="0" (ECMA-376 Part 1)
• HotXLS anahtarları: TXLSXWorkbook.FullPrecision := False ile TXLSWorkbook.UseFullPrecision := False, ikisinin de varsayılanı True
• Bölüm: hesaplanan değerin işaretine göre seçilir; üçüncü bölüm yalnızca tam sıfır için
• Basamaklar: ondalık yer tutucuları, her % için iki artı, her ölçekleme virgülü için üç eksi; sayı negatif olabilir
• Yuvarlama: birkaç ulp'lik toleransla sıfırdan uzağa yarım; 2.5 üçü, -2.5 eksi üçü ve 1.005, 1.01'i verir
• Atlanan: General, tarih/saat ve geçen süre, bilimsel, kesir, metin, Boolean ve hata değerleri
• HotXLS'te kapsam: hesaplandıkları hâldeki formül sonuçları; sabitler atandıkları gibi saklanır
• XLSX motoru: FullPrecision'i ilk Recalculate'ten önce set edin; Klasik setter bütün formülleri kendisi yeniden kirletir
• Sürümler: iki motorda da v2.384.57'den beri Excel 16 ile eşleşir; geçen süre biçimleri v2.384.9'dan beri korunur

HotXLS, XLS ve XLSX çalışma kitaplarını Delphi ve C++Builder'dan yerel olarak okur, yazar ve hesaplar; burada işlenen çalışma kitabı hesaplama seçenekleri dâhil. Ayrıntılar, sürümler ve deneme indirmesi HotXLS Delphi spreadsheet component sayfasında