Teknik Makale

HotXLS gösterilen hassasiyet: Excel yuvarlama kuralları

Excel'in precision as displayed özelliği, 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 ekstra ondalık, her binlik ölçekleme virgülü için üç eksik, eşitlikleri sıfırdan uzağa yuvarlama. HotXLS, TXLSXWorkbook.FullPrecision ya da TXLSWorkbook.UseFullPrecision False olduğunda aynı kuralı iki Delphi motorunda da uygular. Bu, bir müşteri dışa aktardığınız fatura toplamlarının Excel ile bir kuruş farklı olduğunu ya da [ss].00 biçimli bir süre sütununun sıfıra düştüğünü bildirene kadar tek satırlık bir iş gibi görünür. İki şikâyet de kurallardan birini yanlış uygulamaya dayanır. İki HotXLS motoru, beklenen değerleri Workbook.PrecisionAsDisplayed açıkken Excel 16'da ö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, çalışma kitabı düzeyinde tek bir bayraktır. Excel arayüzünde Dosya, Seçenekler, Gelişmiş, "Bu çalışma kitabını hesaplarken" altında, "Hassasiyeti gösterildiği gibi ayarla" olarak durur. Diskte tek bittir. BIFF8 dosyası bunu CalcPrecision kaydında taşır ($000E, [MS-XLS] §2.4.35); kaydın fFullPrec alanı normal tam hassasiyet için 1, seçenek açıkken 0'dır. XLSX paketi bunu workbook.xml'deki calcPr öğesinin fullPrecision özniteliği olarak taşır; ECMA-376 Part 1'de tanımlıdı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:

  • TXLSXWorkbook.FullPrecision: Boolean XLSX motorunda; calcPr/@fullPrecision'den yüklenir ve oraya kaydedilir
  • TXLSWorkbook.UseFullPrecision: Boolean Klasik motorda (IXLSWorkbook üzerinde de); CalcPrecision kaydından yüklenir ve oraya kaydedilir
  • İkisinin de varsayılanı True'dur; bu, güvenli, yıkıcı olmayan kip 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, tutulan ondalık sayısını değeri gösteren belirli biçim bölümünden çıkarır; biçim dizgesinin bütününden değil. Aşağıdaki kurallar Excel 16'da ölçülmüştür ve iki HotXLS motorunun lxNumFormat içindeki XlsApplyDisplayedPrecision işlevinin gerçeklediği kurallardır

  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 çok bölümlü bir biçim, negatif değerler için ikinci, tam olarak sıfır için üçüncü bölümü kullanır. Geri kalan her şey birinci bölümü kullanır
  2. Ondalık yer tutucularını sayın. O bölümdeki ondalık noktasından sonraki her 0, # veya ?, bir tutulan ondalık ekler
  3. Yüzde işareti başına iki ekleyin. 0.0%, 0.1234'ü 12.3% olarak gösterir; dolayısıyla saklanan değer gördüğünüzün yüzde biri olur ve bir değil üç ondalık tutar
  4. Ölçekleme virgülü başına üç çıkarın. Son tam sayı yer tutucusundan sonra gelen virgül (0,, 0.0,, 0,.0) gösterimi 1000'e böler. 0.0,, 12345.678'i 12.3 olarak gösterir; dolayısıyla Excel bir eksi üç, yani negatif bir sayı tutar: değer yüzliklere yuvarlanır ve 12300 olarak saklanır. #,##0'daki gibi tam sayı yer tutucuları arasındaki virgül, düz basamak gruplamadır ve hiçbir şeyi değiştirmez
  5. Sayısal olmayan bölümlere dokunmayın. General, tarih ve saat bölümleri ([h], [mm] ve [ss] geçen süreler dâhil), bilimsel, kesir ve metin bölümleri ile hiç basamak yer tutucusu olmayan bölümler tam hassasiyeti tutar
Gösterilen hassasiyet kurallarının HotXLS şeması: biçim bölümünü değerin işaretine göre seçin; ondalık noktasından sonraki basamak yer tutucularını sayın; her yüzde işareti için iki ondalık ekleyin; her binlik ölçekleme virgülü için üç çıkarın ki sayı negatife düşebilsin; General ile tarih-saat bölümlerini tümüyle atlayın; sonra sıfırdan uzağa yarım yuvarlayın
Basamak sayısı işarete uyan bölümden gelir; yüzde başına iki artar, ölçekleme virgülü başına üç azalır ve negatif bir sayı onluklara ya da yüzliklere yuvarlar; General ile tarih bölümlerine dokunulmaz

Excel 16'ya karşı ölçüldüğünde, iki HotXLS motorunun her biçimde bir formül sonucu için sakladığı değerler bunlardır:

Sayı biçimiHesaplanan değerSaklanan değerUygulanan kural
0.0%0.12340.123Bir ondalık artı yüzde işareti için iki
02.53Sıfırdan uzağa yarım, çift olana değil
0-2.5-3Negatif tarafta da sıfırdan uzağa yarım
0.00;(0.0)-1.2345-1.2Negatif bölüm bir ondalık gösterir
0.00;(0.0)1.23451.23Pozitif bölüm iki ondalık gösterir
#,##0.01234.56781234.6Gruplama virgülü, ölçekleme yok
0.0,12345.67812300Bir ondalık eksi üç: yüzliklere yuvarla
0.0%;(0.00%)-0.0125-0.0125Negatif bölüm iki artı iki ondalık tutar
0.001.0051.01İkili gösterim hatası için tolerans
0;-0;0.00.51Sıfır değil; dolayısıyla pozitif bölüm karar verir

Son satır güzel bir tuzaştır. 0.5 değeri 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 bölümleri işarete göre seçer; dolayısıyla bölümleri [>=1000] gibi özel köşeli parantez koşulları taşıyan bir biçimde saklanan değerleri, önemliyse Excel ile karşılaştırın

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:

HotXLS yuvarlama şeması: 2.5, sıfırdan uzağa yarım yuvarlamayla 3'e, -2.5 ise -3'e gider; Delphi System.Round banker yanıtları olan 2 ile -2'yi verir ve 1.005'e en yakın double, orta noktanın hemen altında durduğu için birkaç ulp'lik tolerans, taban temelli 1.00'ı Excel'in yanıtı 1.01'e çeviren şeydir
Excel eşitlikleri sıfırdan uzağa yuvarlar ve ikili gösterim hatasını küçük bir toleransla affeder; iki ayrıntı da ölçülebilir ve birini atlamak 2.5 için 2'yi ya da 1.005 için 1.00'ı saklar; Excel'den bir kuruş uzakta
// İ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

Hangi gerçekleme tuzakları precision as displayed'i bozar?

Üç tuzak, precision as displayed uyumsuzluklarının çoğunu açıklar ve her biri ekranla çelişen bir değer saklar. Kuralı kendiniz gerçekliyorsanız ya da seçenek açıkken üretilmiş çalışma kitaplarını denetliyorsanız, aranacak belirtiler bunlardır

Yalnızca birinci bölüm, yüzde yok, banker yuvarlaması

Cazip kısayol, biçim dizgesinin bütününün ondalık sayısını ister; bu, yalnızca ilk bölüme bakar, %'i yok sayar ve sonra Round ile yuvarlar. 0.0%'teki bir 0.1234 böylece 0.1 olarak saklanır; ekrandaki 12.3% yerine 10% olur. 0'daki bir 2.5, 3 yerine 2 olarak saklanır. 0.00;(0.0) gibi bir biçimdeki negatif değerler, pozitif bölümün iki ondalığına yuvarlanır. HotXLS, iki motoru da işarete göre bölüm seçen, yüzde işaretlerini ve ölçekleme virgüllerini sayan ve eşitlikleri sıfırdan uzağa yuvarlayan tek bir paylaşılan yordamdan geçirir

TRUE, -1 olur

Yuvarlamayı VarIsNumeric ile korumak ikinci tuzahtır; çünkü 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, yeniden hesaplamadan -1 sayısı olarak çıkar. HotXLS Boolean sonuçları her sayısal testten önce ayırır; dolayısıyla mantıksal bir sonuç iki motorda da mantıksal sonuç olarak kalır

Geçen süre biçimleri renk olarak okunur

Üçüncü tuzak, yuvarlamada değil sayı-biçimi modelinde oturur. Koşul olmayan her köşeli parantezli simgeyi renk sınıflandıran bir ayrıştırıcı, [h], [mm] ve [ss] bölümlerini asla tarih/saat olarak işaretlemez. Görüntüleme etkilenmez; çünkü biçimlendirme ayrı bir yoldan koşar, ama precision as displayed zaman değerlerini atlamak için o bayrağa güvenir. Beş saniyelik bir süre, günün 5/86400'ü, yani yaklaşık 0.0000579'dur ve [ss].00 gibi bir biçim bu durumda sıradan iki ondalıklı bir sayı gibi görünür; dolayısıyla FullPrecision kapalıyken süre 0.00 güne yuvarlanır. HotXLS, tek bir h, m veya s harfinin köşeli parantezli dizisini geçen-süre simgesi olarak ayrıştırır ve bölümü tarih/saat sayar. h:mm'deki dakika algısı da aynı özene ihtiyaç duyar; çünkü simgeler arasındaki iki nokta, saati ayrıştırıcıya gizleyebilir

HotXLS'te geçen sürenin yanlış ayrıştırılması şeması: köşeli parantezli ss simgesiyle biçimlenmiş bir hücrede beş saniye, günün çok küçük bir kesiri olarak saklanır; koşul olmayan her köşeli parantezi renk sayan bir ayrıştırıcı bunu düz iki ondalıklı bir sayı olarak işaretler ve bölüm geçen süre olarak ayrıştırılmadıkça precision as displayed süreyi 0.00'a yuvarlar
Biçimlendirme kendi yolundan koşar; dolayısıyla saklanan değer sıfıra yuvarlanırken hücre doğru görünebilir. Köşeli parantezli tek bir h, m veya s harfi bir geçen süre simgesidir, renk değildir ve bölüm tam hassasiyeti tutar

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)" bir ondalık gösterir
    // C1 Boolean True olarak kalır, -1 sayısı değil
    Wb.SaveAs('report.xls'); // fFullPrec = 0'lı CalcPrecision kaydı
  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 veriler. Birisi rapor için iki ondalıklı bir biçim seçti diye bir ölçümü yuvarlamak, sonraki hiçbir biçim değişikliğinin geri getiremeyeceği bilgiyi yok eder
  • Kaba biçimli yüzdeler. 0% biçimi saklanan oranın yalnızca iki ondalığını tutar; 0.1234, 0.12 olur ve hücreyi okuyan aşağı akıştaki her formül 0.12 ile çalışır
  • Ölçeklenmiş gösterimler. Binlikleri göstermek için kullanılan bir 0, veya 0.0, biçimi, saklanan değeri binliklere ya da yüzliklere yuvarlar; bu genellikle biçimi seçen kişinin kastettiği şey değildir
  • Paylaşılan şablonlar. Bayrak çalışma kitabı genişindedir. Sonradan bir sayfa ekleyen herkes bu davranışı devralır; genellikle açıldığını bilmeden

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 ile CalcPrecision $000E ([MS-XLS] §2.4.35), XLSX'te calcPr fullPrecision="0" (ECMA-376 Part 1)
  • HotXLS anahtarları: TXLSXWorkbook.FullPrecision := False ve 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 olarak 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; böylece 2.5, 3 verir; -2.5, -3 verir ve 1.005, 1.01 verir
  • Atlananlar: General, tarih/saat ve geçen süre, bilimsel, kesir, metin, Boolean ve hata değerleri
  • HotXLS'te kapsam: hesaplandıkları anda formül sonuçları; sabitler atandıkları gibi saklanır
  • XLSX motoru: FullPrecision'i ilk Recalculate'ten önce set edin; Klasik motorun setter'ı tüm formülleri kendisi yeniden kirli yapar
  • Başvuru: beklenen değerler Excel 16'da ölçüldü ve iki motor tarafından paylaşılıyor

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