الدقة كما معروضة في HotXLS: قواعد التقريب في Excel

تقرّب ميزة الدقة كما معروضة في Excel كل رقم مخزن إلى المنازل العشرية التي يعرضها تنسيق أرقامه: قسم التنسيق المطابق لإشارة القيمة، ومنزلتان إضافيتان لكل %، وثلاث منزليات أقل لكل فاصلة توسيع آلاف، بالتقريب نصفاً بعيداً عن الصفر. يطبق HotXLS القاعدة نفسها في محركيه من Delphi معاً حين تكون TXLSXWorkbook.FullPrecision أو TXLSWorkbook.UseFullPrecision بقيمة False. يبدو ذلك سطراً واحداً حتى يبلّغ عميلٌ أن مجاميع فواتيرك المصدرة تخالف Excel بقرش واحد، أو أن عمود مدد زمنية بصيغة [ss].00 انهار إلى الصفر. الحادثان وقعا، وكلاهما يعود إلى إخطاء إحدى تلك القواعد. ومنذ v2.384.57 يتشارك المحركان تنفيذاً واحداً قِيست قيمه المتوقعة في Excel 16 مع تفعيل Workbook.PrecisionAsDisplayed

ماذا تغيّر بميزة الدقة كما معروضة فعلاً في مصنف؟

الدقة كما معروضة علمٌ واحد على مستوى المصنف يخبر محرك الحساب أن يخزن الأرقام كما تبدو لا كما حُسبت. في واجهة Excel تجلس تحت File ثم Options ثم Advanced، ضمن «When calculating this workbook»، باسم «Set precision as displayed». وعلى القرص هي بتة واحدة. يحمل ملف BIFF8 العلمَ في سجل CalcPrecision ‏($000E، [MS-XLS] §2.4.35) حيث يساوي حقل fFullPrec ‏1 للدقة الكاملة العادية و 0 حين تكون الخاصية مفعلة. وتحمل حزمة XLSX العلمَ خاصيةَ fullPrecision لعنصر calcPr في workbook.xml المعرفة في ECMA-376 الجزء 1، حيث يكون الافتراضي true وتشغّل fullPrecision="0" التقريب

العلم ليس تفضيل عرض. حين تؤشر المربع يحذرك Excel من أن البيانات ستفقد الدقة بلا رجعة، وهو يقصد ذلك: تُعاد كتابة القيم بدقتها المعروضة، والخانات التي قُطعت ذهبت. وإزالة الإشارة لاحقاً لا تعيد الخانات القديمة. فالقيمة 0.1234 المعروضة 12.3% تصير 0.123 للأبد

يقرأ HotXLS العلم ويكتبه في الصيغتين ويعرضه في المحركين:

• ‏TXLSXWorkbook.FullPrecision: Boolean على محرك XLSX، محمَّل من calcPr/@fullPrecision ومحفوظ إليه
• ‏TXLSWorkbook.UseFullPrecision: Boolean على المحرك الكلاسيكي ‏(وأيضاً على IXLSWorkbook)، محمَّل من سجل CalcPrecision ومحفوظ إليه
• كلاهما افتراضه True، وهو الوضع الآمن غير المدمّر وافتراضي Excel

أين يطبق HotXLS التقريب له وزنه. يقرّب HotXLS عند النقطة التي يحسب فيها القيمة: كل نتيجة صيغة تُقرَّب إلى دقتها المعروضة قبل أن تُخزن قيمةً مخبأة للخلية، أثناء Recalculate وأثناء التقييم عند الطلب. أما الثوابت التي تسندها عبر Value فتخزن كما أعطيت تماماً. فإن كان على ناتجك أن يستنسخ ما يخزنه Excel بعد تفعيل المربع، قرّب تلك الثوابت بنفسك قبل كتابتها، مثلاً بالمساعد المعروض لاحقاً

كيف يقرر Excel عدد المنازل العشرية التي يُبقاها؟

يشتق Excel عدد المنازل المحفوظة من قسم التنسيق المعيّن الذي يعرض القيمة، لا من سلسلة التنسيق ككل. والقواعد أدناه قيسَت في Excel 16 وهي ما ينفذه XlsApplyDisplayedPrecision في lxNumFormat لمحركي HotXLS معاً

1. اختر القسم بالإشارة. تنسيق بقسمين يستخدم القسم الثاني للقيم السالبة. وتنسيق بثلاثة أقسام فأكثر يستخدم الثاني للسالب والثالث للصفر التام. وكل ما عداهما يستخدم القسم الأول
2. عُدّ مواضع المنازل العشرية. كل 0 أو # أو ? بعد العلامة العشرية في ذلك القسم يضيف منزلة محفوظة
3. أضف اثنتين لكل علامة نسبة. تعرض 0.0% القيمة 0.1234 على هيئة 12.3%، فالقيمة المخزنة جزء من مئة مما تراه وتبقي ثلاث منازل عشرية لا واحدة
4. اطرح ثلاثاً لكل فاصلة توسيع. الفاصلة بعد آخر موضع عدد صحيح ‏(0, و 0.0, و 0,.0) تقسم العرض على 1000. تعرض 0.0, القيمة 12345.678 على هيئة 12.3، فيبقي Excel منزلة عشرية ناقص ثلاثاً، وهو عدد سالب: تقرَّب القيمة إلى المئات وتخزن 12300. والفاصلة بين مواضع الأعداد الصحيحة، كما في #,##0، تجميع خانات عادي ولا يغيّر شيئاً
5. اترك الأقسام غير العددية وشأنها. أقسام General والتاريخ والوقت ‏(بما فيها المدة [h] و [mm] و [ss]) والعلمية والكسور والنص، والأقسام بلا أي موضع خانة، تبقي دقتها الكاملة

مقيسةً على Excel 16، هذه هي القيم التي يخزنها محركا HotXLS الآن لنتيجة صيغة في كل تنسيق:

تنسيق الأرقام	القيمة المحسوبة	القيمة المخزنة	القاعدة المنطبقة
0.0%	0.1234	0.123	منزلة عشرية زائد اثنتين لعلامة النسبة
0	2.5	3	النصف بعيداً عن الصفر، لا إلى الزوجي
0	-2.5	-3	النصف بعيداً عن الصفر على الجانب السالب أيضاً
0.00;(0.0)	-1.2345	-1.2	القسم السالب يعرض منزلة عشرية
0.00;(0.0)	1.2345	1.23	القسم الموجب يعرض منزلتين عشريتين
#,##0.0	1234.5678	1234.6	فاصلة تجميع، بلا توسيع
0.0,	12345.678	12300	منزلة عشرية ناقص ثلاث: تقريب إلى المئات
0.0%;(0.00%)	-0.0125	-0.0125	القسم السالب يبقي منزلتين زائد اثنتين
0.00	1.005	1.01	تسامح مع خطأ التمثيل الثنائي
0;-0;0.0	0.5	1	ليست صفراً، فيحسم القسم الموجب

الصف الأخير فخ جميل. القيمة 0.5 تقرب إلى عدد صحيح، وقسم الصفر لا يدخل اللعبة قط، لأن Excel ينتقي القسم من القيمة المحسوبة قبل التقريب. وحدٌّ صريح على جانب HotXLS: تُختار الأقسام بالإشارة وحدها، فالتنسيق الذي تحمل أقسامه شروط أقواس مخصصة مثل [>=1000] يُقسَم بالإشارة مع ذلك. افحص مثل تلك التنسيقات مقابل Excel إن كانت تهمك

لماذا تقرَّب 1.005 إلى 1.01 لا إلى 1.00؟

يقرب Excel القيمة 1.005 في خلية 0.00 إلى 1.01 رغم أن الـ double الأقرب إلى 1.005 يقع تحت نقطة المنتصف قليلاً، ويطابق HotXLS ذلك بتسامح بضعة ulps. فالعدد الحرفي 1.005 لا يمكن تمثيله في الفاصلة العائمة الثنائية. الـ double الأقرب في IEEE 754 هو 1.00499999999999989341858963598497211933135986328125، وضربه في 100 يعطي 100.49999999999999. لذا تعيد Floor(x * 100 + 0.5) / 100 الكتابية 1.00، مخالفةً العدد الذي كتبه المستخدم، وما يعرضه Excel، وما يخزنه Excel

ويضيف Delphi لفتته الخاصة. تقرب System.Round التعادلات إلى الزوجي، فـ Round(2.5) تساوي 2 و Round(3.5) تساوي 4. ذلك تقريب المصرفيين، افتراض معقول للإحصاء والقاعدة الخطأ هنا: يخزن Excel القيمة 3 لـ 2.5 في خلية 0 و -3 لـ -2.5. يعمل تنفيذ HotXLS على القيمة المطلقة، ويضيف 0.5 زائد تسامحاً نسبياً بمقدار 2^-51 من القيمة المتوسعة (بضعة ulps عند ذلك القدر، لا تقل عن ulp اثنتين من 1.0 قط)، ويقطع، ويعيد التوسيع للخلف، ويستعيد الإشارة. والدالة التالية رسم مستقل بذاته لذلك المبدأ، لا كود المكتبة نفسه، وتعالج أعداد الخانات السالبة لفواصل التوسيع بالطريقة نفسها:

// رسم مبدئي: تقريب النصف بعيداً عن الصفر إلى ADigits منزلة عشرية،
// بتسامح بضعة ulps حتى تبلغ 1.005 قيمة 1.01.
// ‏ADigits < 0 يقرب إلى العشرات والمئات... ‏("0.0," تعطي -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // ‏2^-51، ulps اثنان من 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // ما بعد دقة double: اترك القيمة وشأنها
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // التوسيع سيفيض
    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); // نصف بعيداً عن الصفر، لا 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   ‏(بالأساس 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 خانة)
// RoundAsDisplayed(12345.678, -2) = 12300  ‏("0.0,": 1 - 3 خانة)

التسامح مقايضة مقصودة. القيمة التي تقع فعلاً ulp اثنتين تحت خطوة نصفية تقرب هي أيضاً إلى الأعلى، لكن عند تلك المسافة يتعذر تمييز الفرق عن خطأ التمثيل، ومعاملتها خطوةً نصفية هي ما يجعل المنازل العشرية المكتوبة تتصرف كما يتوقع المستخدمون

ما الذي أخطأ قبل v2.384.57؟

قبل v2.384.57 كان لمحرك XLSX والمحرك الكلاسيكي كود الدقة كما معروضة خاص بكل منهما، وكان كل منهما خطأً بطريقة مختلفة. فإن كنت تنتج مصنفات والخاصية مفعلة، فهذه الأعراض التي تبحث عنها في ملفات البناءات الأقدم

محرك XLSX: القسم الأول وحده، بلا نسب مئوية، وتقريب المصرفيين

كان مسار XLSX القديم يطلب عدد منازل سلسلة التنسيق ككل، فنظر إلى القسم الأول وحده وتجاهل %، ثم قرّب بـ Round. فالقيمة 0.1234 في 0.0% خزنت 0.1، أي 10% بدل 12.3% على الشاشة. والقيمة 2.5 في 0 خزنت 2 بدل 3. والقيم السالبة في تنسيق مثل 0.00;(0.0) قُرّبت إلى منزلتَي القسم الموجب. ومنذ v2.384.57 يستدعي محرك XLSX الروتين المشترك نفسه الذي يستدعيه المحرك الكلاسيكي، وهو الذي اكتسب دعم فواصل التوسيع في ذلك الإصدار أيضاً

المحرك الكلاسيكي: صار TRUE يساوي -1

كان المحرك الكلاسيكي يحرس تقريبه بـ VarIsNumeric، و VarIsNumeric تعيد True لـ Variant من نوع varBoolean. وتحويل ذلك الـ Variant بـ Double(V) يعطي -1، لأن Boolean بصيغة COM خزنته True تساوي -1. فصيغة مثل =A1>0 في خلية منسقة 0.00 خرجت من إعادة الحساب العددَ -1. ومنذ v2.384.57 تُستبعد النواتج المنطقية قبل أي فحص عددي، وتبقى النتيجة المنطقية نتيجةً منطقية في المحركين

تنسيقات المدة الزمنية قُرئت ألواناً ‏(v2.384.9)

جلس العيب الثالث في نموذج تنسيق الأرقام لا في التقريب. صنّف المحلل كل رمز بين حاصرين ليس شرطاً لوناً، فلم تسمّ [h] و [mm] و [ss] قط قسماً تاريخاً/وقتاً. لم يتأثر العرض، لأن التنسيق يجري على مسار منفصل، لكن الدقة كما معروضة تعتمد ذلك العلم لتخطي قيم الوقت. مدة خمس ثوانٍ هي 5/86400 من اليوم، نحو 0.0000579، وتنسيق مثل [ss].00 بدا عدداً عادياً بمنزلتين عشريتين، فعند إيقاف FullPrecision قُرّبت المدة إلى 0.00 يوماً. ومنذ v2.384.9 يُفسَّر مدى محاصر لمحرف h أو m أو s واحد رمزَ مدةٍ زمنية ويعامل القسم تاريخاً/وقتاً. وأصلح الإصدار نفسه كشف الدقائق في h:mm، حيث كانت النقطتان بين الرمزين تحجبان الساعة عن المحلل

تفعيل الدقة كما معروضة في HotXLS من Delphi

للحصول على قيم مخزنة معادلةً لـ Excel، اضبط العلم قبل إعادة الحساب التي يجب أن تحترمها، ثم اقرأ النواتج المخبأة أو احفظ. على محرك XLSX يكون FullPrecision علماً صريحاً: تغييره لا يبطل النواتج التي خزنها Recalculate سابق، فاضبطه مباشرة بعد Create أو Open وقبل أول Recalculate. يستخدم المثال صيغاً لأن ذلك هو موضع تطبيق HotXLS للتقريب:

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%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // تعرض 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // تعرض 12.3 ‏(بالآلاف)

    // يجب ضبطه قبل أول Recalculate على محرك XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // النواتج المخبأة تطابق الآن Excel 16: ‏0.123 و 3 و 12300.
    // الثوابت في العمود A تحفظ دقتها الكاملة.
    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"/>
  finally
    Wb.Free;
  end;
end;

يتصرف المحرك الكلاسيكي كذلك، مع راحة واحدة: إسناد TXLSWorkbook.UseFullPrecision يوسم كل صيغة في مخطط الاعتماديات متسخةً، فتعيد Recalculate التالية تقييم المصنف كله تحت القاعدة الجديدة. وتغيير NumberFormat والخاصية مفعلة يوسم خلايا الصيغ المتأثرة متسخةً أيضاً، لأن التنسيق يقرر الآن القيمة المخزنة. ولاحظ أن Recalculate الكلاسيكية تعيد عدد خلايا الصيغ التي لم تستطع تقييمها، فالصفر يعني النجاح:

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; // يوسم كل صيغة متسخة
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // ‏B1 = -1.2: القسم السالب "(0.0)" يعرض منزلة عشرية واحدة
    // تبقى C1 منطقية True ‏(البناءات قبل v2.384.57 خزنت -1)
    Wb.SaveAs('report.xls'); // سجل CalcPrecision مع ‏fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

يحترم المحركان أيضاً العلم القادم مع ملف. افتح مصنفاً حُفظ والخاصية مفعلة يكون FullPrecision أو UseFullPrecision بقيمة False سلفاً، فيقرّب Recalculate بعد التحميل بالضبط كما سيفعل Excel. وإن كنت تحتاج قراءة الأرقام التي خزنها Excel سلفاً فقط، يمكنك تخطي إعادة الحساب كلياً، كما في قراءة قيم الصيغ المخبأة دون إعادة حساب. وللاطلاع على تفاعل الأرقام التسلسلية وتنسيقات التاريخ مع نموذج التنسيق الذي يقود فحص التاريخ/الوقت، راجع أرقام التواريخ التسلسلية في Excel ونظام 1904 و numFmt في Delphi

متى تفعل الدقة كما معروضة ومتى لا تفعل؟

فعل الدقة كما معروضة فقط عندما يجب أن تساوي الأرقام المخزنة في المصنف أرقامَها المعروضة، وتقبل بفقدان الخانات الإضافية إلى الأبد. الحالة المشروعة الكلاسيكية جدول مالي يجب أن تضاف فيه أعمدة المبالغ المدورة إلى المجموع المدور على الشاشة، دون كسور قرش مخفية تجعل المجموع منحرفاً بمقدار واحد في آخر موضع. ومطابقة مصنف عميل قائم مفعَّل فيه الخيار أصلاً هو السبب الآخر الجيد، ويحفظ HotXLS العلم عبر الدورة حتى لا تعيدهم بصمت إلى الدقة الكاملة

وتجنبه في أغلب المواضع الأخرى:

• بيانات الهندسة والعلوم. تقريب قياس لأن أحداً اختار تنسيقاً بمنزلتين عشريتين لتقريرٍ يدمّر معلومة لا يستطيع أي تغيير تنسيق لاحق أن يستعيدها
• النسب المئوية بالتنسيقات الخشنة. تنسيق 0% يبقي منزلتين عشريتين فقط من النسبة المخزنة، فتصير 0.1234 هي 0.12، وكل صيغة لاحقة تقرأ الخلية تعمل بـ 0.12
• العروض الموسعة. تنسيق 0, أو 0.0, يعرض الآلاف ويقرّب القيمة المخزنة إلى الآلاف أو المئات، وهو نادراً ما يقصده من اختار التنسيق
• القوالب المشتركة. العلم على مستوى المصنف كله. أي أحد يضيف ورقة لاحقاً يرث السلوك، عادة دون أن يعرف أنه مفعَّل

إن كان ما تريده فعلاً نواتج مدورة في خلايا محددة قليلة، فاكتب ROUND في تلك الصيغ بدلاً من ذلك. ‏ROUND صريحة، ومحلية للخلية، ومرئية لمن يقرأ الصيغة، ويقيّمها محرك صيغ HotXLS كأي دالة أخرى، من دون آثار جانبية على مستوى المصنف

مرجع سريع للدقة كما معروضة

• علم الملف: سجل CalcPrecision ‏$000E مع fFullPrec = 0 في BIFF8 ‏([MS-XLS] §2.4.35)، و calcPr fullPrecision="0" في XLSX ‏(ECMA-376 الجزء 1)
• مفاتيح HotXLS: ‏TXLSXWorkbook.FullPrecision := False و TXLSWorkbook.UseFullPrecision := False، وكلاهما افتراضه True
• القسم: يُنتقى بإشارة القيمة المحسوبة؛ والقسم الثالث للصفر التام وحده
• الخانات: مواضع المنازل العشرية، زائد اثنتين لكل %، ناقص ثلاث لكل فاصلة توسيع؛ ويمكن أن يكون العدد سالباً
• التقريب: نصف بعيداً عن الصفر بتسامح بضعة ulps، فتعطي 2.5 ناتج 3 و -2.5 ناتج -3 و 1.005 ناتج 1.01
• المتخطاة: General والتاريخ/الوقت ومدة الزمن والعلمي والكسور والنص والقيم المنطقية وقيم الخطأ
• النطاق في HotXLS: نتائج الصيغ وقت حسابها؛ والثوابت تخزن كما سُندت
• محرك XLSX: اضبط FullPrecision قبل أول Recalculate؛ والمعيّن الكلاسيكي يعيد توسيم كل الصيغ بنفسه
• الإصدارات: مطابقة لـ Excel 16 في المحركين منذ v2.384.57؛ وتنسيقات المدة الزمنية محمية منذ v2.384.9

يقرأ HotXLS مصنفات XLS و XLSX ويكتبها ويحسبها أصلياً من Delphi و C++Builder، بما في ذلك خيارات حساب المصنف المغطاة هنا. والتفاصيل والإصدارات وتنزيل النسخة التجريبية في صفحة مكوّن HotXLS Delphi للجداول الحسابية