Precision as displayed w HotXLS: reguły zaokrąglania Excela

Precision as displayed w Excelu zaokrągla każdą przechowywaną liczbę do miejsc dziesiętnych, które pokazuje jej format liczbowy: bierze sekcję formatu pasującą do znaku wartości, dodaje dwa miejsca na każdy %, odejmuje trzy na każdy przecinek skalujący tysiące i zaokrągla połowę od zera. HotXLS stosuje tę samą regułę w obu swoich silnikach Delphi, gdy TXLSXWorkbook.FullPrecision albo TXLSWorkbook.UseFullPrecision jest False. Brzmi jak jedna linijka, dopóki klient nie zgłosi, że sumy twojej wyeksportowanej faktury różnią się od Excela o centa, albo że kolumna czasów trwania w [ss].00 zapadła się do zera. Oba się zdarzyły, i oba wiodą do jednej z tych reguł odczytanej źle. Od v2.384.57 oba silniki dzielą jedną implementację, której oczekiwane wartości zostały wyzmierzone w Excelu 16 z włączonym Workbook.PrecisionAsDisplayed

Co precision as displayed faktycznie zmienia w skoroszycie?

Precision as displayed to jedna flaga na poziomie skoroszytu, która mówi silnikowi obliczeń: zapisuj liczby tak, jak wyglądają, a nie tak, jak zostały policzone. W interfejsie Excela siedzi pod File, Options, Advanced, „When calculating this workbook”, jako „Set precision as displayed”. Na dysku to jeden bit. Plik BIFF8 niesie go w rekordzie CalcPrecision ($000E, [MS-XLS] §2.4.35), którego pole fFullPrec ma 1 dla normalnej pełnej precyzji i 0, gdy opcja jest włączona. Pakiet XLSX niesie go jako atrybut fullPrecision elementu calcPr w workbook.xml, zdefiniowanym w ECMA-376 Part 1, gdzie domyślnie jest true, a fullPrecision="0" włącza zaokrąglanie

Ta flaga nie jest preferencją wyświetlania. Gdy zaznaczysz to pole, Excel ostrzega, że dane trwale stracą dokładność, i traktuje to serio: wartości są przepisywane do swojej wyświetlanej precyzji, a ucięte cyfry znikają. Odznaczenie pola później nie przywraca starych cyfr. 0.1234 pokazywane jako 12.3% staje się 0.123 na zawsze

HotXLS czyta i zapisuje tę flagę w obu formatach i wystawia ją w obu silnikach:

• TXLSXWorkbook.FullPrecision: Boolean w silniku XLSX, wczytywana z calcPr/@fullPrecision i do niego zapisywana
• TXLSWorkbook.UseFullPrecision: Boolean w silniku Classic (także na IXLSWorkbook), wczytywana z rekordu CalcPrecision i do niego zapisywana
• Obie domyślnie mają True — bezpieczny, nieniszczący tryb i zarazem domyślny stan Excela

Miejsce, w którym HotXLS stosuje zaokrąglenie, ma znaczenie. HotXLS zaokrągla w punkcie, w którym liczy wartość: wynik każdej formuły jest zaokrąglany do swojej wyświetlanej precyzji, zanim zostanie zapisany jako cache'owana wartość komórki — podczas Recalculate i podczas wyliczania na żądanie. Stałe, które przypiszesz przez Value, są zapisywane dokładnie takie, jakie są. Jeśli twoje wyjście ma odtwarzać to, co Excel zapisze po zaznaczeniu pola, zaokrąglij te stałe sam przed zapisem, na przykład pomocnikiem pokazanym niżej

Jak Excel decyduje, ile miejsc dziesiętnych zachować?

Excel wyprowadza liczbę zachowywanych miejsc z konkretnej sekcji formatu, która wyświetla wartość, a nie z całego formatu naraz. Poniższe reguły zostały wyzmierzone w Excelu 16 i to właśnie implementuje XlsApplyDisplayedPrecision w lxNumFormat dla obu silników HotXLS

1. Wybierz sekcję po znaku. Format dwusekcyjny używa drugiej sekcji dla wartości ujemnych. Format o trzech lub więcej sekcjach używa drugiej dla ujemnych i trzeciej dla dokładnego zera. Wszystko inne idzie do pierwszej sekcji
2. Policz placeholdery miejsc dziesiętnych. Każdy 0, # albo ? po przecinku dziesiętnym w tej sekcji dodaje jedno zachowane miejsce
3. Dodaj dwa na znak procenta. 0.0% pokazuje 0.1234 jako 12.3%, więc zapisana wartość to setna tego, co widzisz, i zachowuje trzy miejsca, nie jedno
4. Odejmij trzy na przecinek skalujący. Przecinek po ostatnim placeholderze części całkowitej (0,, 0.0,, 0,.0) dzieli wyświetlaną wartość przez 1000. 0.0, pokazuje 12345.678 jako 12.3, więc Excel zachowuje jedno miejsce minus trzy, czyli liczbę ujemną: wartość jest zaokrąglana do setek i zapisywana jako 12300. Przecinek między placeholderami części całkowitej, jak w #,##0, to zwyczajne grupowanie cyfr i niczego nie zmienia
5. Zostaw sekcje nienumeryczne w spokoju. Sekcje General, daty i czasu (łącznie z czasem upływu [h], [mm] i [ss]), naukowe, ułamkowe i tekstowe oraz sekcje bez żadnego placeholdera cyfry zachowują pełną precyzję

Wyzmierzone względem Excela 16, to są wartości, które oba silniki HotXLS zapisują teraz dla wyniku formuły w każdym z formatów:

Format liczbowy	Wartość policzona	Wartość zapisana	Reguła, która wchodzi
0.0%	0.1234	0.123	Jedno miejsce plus dwa za znak procenta
0	2.5	3	Połowa od zera, nie do parzystej
0	-2.5	-3	Połowa od zera także po stronie ujemnej
0.00;(0.0)	-1.2345	-1.2	Sekcja ujemna pokazuje jedno miejsce
0.00;(0.0)	1.2345	1.23	Sekcja dodatnia pokazuje dwa miejsca
#,##0.0	1234.5678	1234.6	Przecinek grupujący, bez skalowania
0.0,	12345.678	12300	Jedno miejsce minus trzy: zaokrąglenie do setek
0.0%;(0.00%)	-0.0125	-0.0125	Sekcja ujemna zachowuje dwa plus dwa miejsca
0.00	1.005	1.01	Tolerancja błędu reprezentacji binarnej
0;-0;0.0	0.5	1	Nie zero, więc decyduje sekcja dodatnia

Ostatni wiersz to niezła pułapka. Wartość 0.5 zaokrągla się do liczby całkowitej, a sekcja zera nigdy nie wchodzi do gry, bo Excel wybiera sekcję na podstawie wartości policzonej, przed zaokrągleniem. Jedno uczciwe ograniczenie po stronie HotXLS: sekcje są wybierane wyłącznie po znaku, więc format, którego sekcje niosą własne warunki nawiasowe, jak [>=1000], jest nadal dzielony po znaku. Sprawdź takie formaty względem Excela, jeśli są dla ciebie istotne

Dlaczego 1.005 zaokrągla się do 1.01, a nie do 1.00?

Excel zaokrągla 1.005 w komórce 0.00 do 1.01, choć double najbliższy 1.005 leży minimalnie poniżej punktu pośredniego, i HotXLS odwzorowuje to tolerancją rzędu kilku ulp. Literał 1.005 nie da się przedstawić w binarnym zmiennoprzecinkowym. Najbliższy double IEEE 754 to 1.00499999999999989341858963598497211933135986328125, a mnożenie przez 100 daje 100.49999999999999. Podręcznikowe Floor(x * 100 + 0.5) / 100 zwraca więc 1.00 — wbrew liczbie wpisanej przez użytkownika, temu, co pokazuje Excel, i temu, co Excel zapisuje

Delphi dokłada własną odsłonę. System.Round zaokrągla remisy do parzystej, więc Round(2.5) daje 2, a Round(3.5) daje 4. To banker's rounding — rozsądny domyślny wybór w statystyce i zła reguła tutaj: Excel zapisuje 3 dla 2.5 w komórce 0 i -3 dla -2.5. Implementacja HotXLS pracuje na wartości bezwzględnej, dodaje 0.5 plus relatywną tolerancję 2^-51 razy wartość przeskalowana (kilka ulp przy tej wielkości, nigdy mniej niż dwa ulp od 1.0), obcina, skaluje z powrotem i przywraca znak. Poniższa funkcja to samodzielna ilustracja tej zasady, nie kod biblioteki, i ujemne liczby miejsc dla przecinków skalujących traktuje tak samo:

// Szkic zasady: zaokrąglij połową od zera do ADigits miejsc dziesiętnych,
// z tolerancją kilku ulp, żeby 1.005 doszło do 1.01.
// ADigits < 0 zaokrągla do dziesiątek, setek, ... ("0.0," daje -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dwa ulp od 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // poza precyzją double: zostaw wartość w spokoju
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // skalowanie przepełniłoby zakres
    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); // połowa od zera, nie 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   (przez 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 miejsca)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 miejsca)

Tolerancja to świadomy kompromis. Wartość, która naprawdę leży dwa ulp poniżej połówki, też zaokrągli się w górę, ale na tej odległości różnicy nie odróżnisz od błędu reprezentacji, a potraktowanie jej jak połówki to właśnie to, co sprawia, że wpisane dziesiętne zachowują się tak, jak użytkownicy oczekują

Co było nie tak przed v2.384.57?

Przed v2.384.57 silnik XLSX i silnik Classic miały każdy swój kod precision-as-displayed i każdy był zły po swojemu. Jeśli produkujesz skoroszyty z włączoną opcją, to są objawy, których szukaj w plikach ze starszych buildów

Silnik XLSX: tylko pierwsza sekcja, bez procenta, zaokrąglanie bankowe

Stara ścieżka XLSX pytała o liczbę miejsc całego formatu naraz, co zaglądało tylko do pierwszej sekcji i ignorowało %, a potem zaokrąglało przez Round. 0.1234 w 0.0% było zapisywane jako 0.1, czyli 10% zamiast 12.3% z ekranu. 2.5 w 0 było zapisywane jako 2 zamiast 3. Wartości ujemne w formacie typu 0.00;(0.0) były zaokrąglane do dwóch miejsc sekcji dodatniej. Od v2.384.57 silnik XLSX woła tę samą współdzieloną procedurę co silnik Classic, która przy okazji zyskała w tym wydaniu obsługę przecinków skalujących

Silnik Classic: TRUE stawało się -1

Silnik Classic strzegł swojego zaokrąglenia przez VarIsNumeric, a VarIsNumeric zwraca True także dla Variantu varBoolean. Konwersja tego Variantu przez Double(V) daje -1, bo Boolean True w stylu COM jest zapisywane jako -1. Formuła typu =A1>0 w komórce sformatowanej 0.00 wychodziła więc z przeliczania jako liczba -1. Od v2.384.57 wyniki Boolean są odsiewane przed jakimkolwiek testem numerycznym, a wynik logiczny pozostaje logicznym w obu silnikach

Formaty czasu upływu czytane jako kolory (v2.384.9)

Trzeci bug siedział w modelu formatów liczbowych, nie w zaokrąglaniu. Parser klasyfikował każdy token w nawiasach, który nie był warunkiem, jako kolor, więc [h], [mm] i [ss] nigdy nie oznaczały swojej sekcji jako daty/czasu. Wyświetlanie nie ucierpiało, bo formatowanie biegnie osobną ścieżką, ale precision as displayed polega na tej fladze, żeby omijać wartości czasu. Czas trwania pięciu sekund to 5/86400 dnia, około 0.0000579, a format typu [ss].00 wyglądał jak zwyczajna liczba z dwoma miejscami, więc przy wyłączonym FullPrecision czas trwania był zaokrąglany do 0.00 dnia. Od v2.384.9 ciąg w nawiasach z pojedynczą literą h, m albo s jest parsowany jako token czasu upływu, a sekcja jest traktowana jako data/czas. To samo wydanie naprawiło wykrywanie minut w h:mm, gdzie dwukropek między tokenami skrywał godziny przed parserem

Włączanie precision as displayed w HotXLS z Delphi

Żeby dostać zapisane wartości równoważne z Excelem, ustaw flagę przed przeliczeniem, które ma jej usłuchać, a potem odczytaj cache'owane wyniki albo zapisz plik. W silniku XLSX FullPrecision to zwykła flaga: jej zmiana nie unieważnia wyników, które wcześniejszy Recalculate już zapisał, więc ustaw ją zaraz po Create albo Open i przed pierwszym Recalculate. Przykład używa formuł, bo właśnie tam HotXLS stosuje zaokrąglenie:

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%';   // pokazuje 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // pokazuje 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // pokazuje 12.3 (tysiące)

    // Trzeba ustawić przed pierwszym Recalculate w silniku XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Cache'owane wyniki zgadzają się teraz z Excelem 16: 0.123, 3 i 12300.
    // Stałe w kolumnie A zachowują pełną precyzję.
    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'); // zapisuje <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Silnik Classic zachowuje się tak samo, z jedną wygodą: przypisanie TXLSWorkbook.UseFullPrecision oznacza każdą formułę w grafie zależności jako brudną, więc następny Recalculate przelicza cały skoroszyt pod nową regułą. Zmiana NumberFormat przy włączonej opcji także oznacza dotknięte komórki z formułami jako brudne, bo format teraz decyduje o zapisanej wartości. Zauważ, że klasyczny Recalculate zwraca liczbę komórek z formułami, których nie udało się wyliczyć, więc zero znaczy sukces:

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; // oznacza każdą formułę jako brudną
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: sekcja ujemna "(0.0)" pokazuje jedno miejsce
    // C1 zostaje Boolean True (buildy sprzed v2.384.57 zapisywały -1)
    Wb.SaveAs('report.xls'); // rekord CalcPrecision z fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Oba silniki honorują też flagę przychodzącą z plikiem. Otwórz skoroszyt zapisany z włączoną opcją, a FullPrecision albo UseFullPrecision już jest False, więc Recalculate po wczytaniu zaokrągla dokładnie tak, jak by to zrobił Excel. Jeśli potrzebujesz tylko odczytać liczby, które Excel już zapisał, możesz pominąć przeliczanie w ogóle — opisuje to artykuł o czytaniu cache'owanych wartości formuł bez przeliczania. O tym, jak numery seryjne i formaty dat współgrają z modelem formatów napędzającym sprawdzanie daty/czasu, pisze artykuł o datach seryjnych Excela, systemie 1904 i numFmt w Delphi

Kiedy włączyć precision as displayed, a kiedy nie?

Włączaj precision as displayed tylko wtedy, gdy zapisane liczby skoroszytu mają się równać liczbom wyświetlanym, i akceptujesz wieczną utratę dodatkowych cyfr. Klasyczny uprawniony przypadek to harmonogram finansowy, w którym kolumny zaokrąglonych kwot muszą sumować się do zaokrąglonej sumy z ekranu, bez ukrytych ułamków centa produkujących sumę odbiegającą o jeden na ostatnim miejscu. Dopasowanie się do istniejącego skoroszytu klienta, który już ma tę opcję ustawioną, to drugi dobry powód, a HotXLS zachowuje flagę przy rundzie, więc nie przełączysz nikogo po cichu z powrotem na pełną precyzję

W większości innych sytuacji unikaj jej:

• Dane inżynierskie i naukowe. Zaokrąglenie pomiaru dlatego, że ktoś wybrał format z dwoma miejscami do raportu, niszczy informację, której żadna późniejsza zmiana formatu nie przywróci
• Procenty przy grubych formatach. Format 0% zachowuje tylko dwa miejsca zapisanego stosunku, więc 0.1234 staje się 0.12, a każda formuła dalej, czytająca komórkę, pracuje z 0.12
• Wyświetlanie przeskalowane. Format 0, albo 0.0, użyty do pokazywania tysięcy zaokrągla zapisaną wartość do tysięcy albo setek, co rzadko jest intencją osoby, która format wybrała
• Szablony współdzielone. Flaga działa na cały skoroszyt. Każdy, kto później doda arkusz, dziedziczy to zachowanie, zwykle nie wiedząc, że jest włączone

Jeśli naprawdę chcesz zaokrąglonych wyników w kilku konkretnych komórkach, wpisz do tych formuł ROUND. ROUND jest jawny, lokalny dla komórki, widoczny dla każdego czytającego formułę i wyliczany przez silnik formuł HotXLS jak każda inna funkcja, bez efektów ubocznych na cały skoroszyt

Ściąga: precision as displayed

• Flaga w pliku: CalcPrecision $000E z fFullPrec = 0 w BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" w XLSX (ECMA-376 Part 1)
• Przełączniki HotXLS: TXLSXWorkbook.FullPrecision := False i TXLSWorkbook.UseFullPrecision := False, oba domyślnie True
• Sekcja: wybierana po znaku policzonej wartości; trzecia sekcja tylko dla dokładnego zera
• Miejsca: placeholdery dziesiętne, plus dwa na %, minus trzy na przecinek skalujący; liczba może być ujemna
• Zaokrąglenie: połowa od zera z tolerancją kilku ulp, więc 2.5 daje 3, -2.5 daje -3, a 1.005 daje 1.01
• Omijane: General, data/czas i czas upływu, naukowe, ułamkowe, tekstowe, wartości Boolean i błędy
• Zakres w HotXLS: wyniki formuł w chwili liczenia; stałe są zapisywane takie, jakie przypiszesz
• Silnik XLSX: ustaw FullPrecision przed pierwszym Recalculate; setter w Classic sam oznacza wszystkie formuły jako brudne
• Wersje: dopasowane do Excela 16 w obu silnikach od v2.384.57; formaty czasu upływu chronione od v2.384.9

HotXLS czyta, zapisuje i przelicza skoroszyty XLS i XLSX natywnie z Delphi i C++Buildera, łącznie z opcjami obliczeń skoroszytu opisanymi tutaj. Szczegóły, wydania i pobranie wersji próbnej znajdziesz na stronie komponentu arkuszy kalkulacyjnych HotXLS dla Delphi