دوال إحصائية لـ Excel في دلفي: NORM، CHISQ، BETA

اكتب =NORM.DIST(115,100,15,TRUE) في خلية وسيعيد Excel‏ 0.8413447 دون أي تعقيد. تبدو المكالمة وكأنها مجرد بحث. ولكنه ليس كذلك. فوراء هذا الرقم الفردي يكمن التوزيع الطبيعي التراكمي، وهو تكامل ليس له شكل مغلق، ووراء CHISQ.INV.RT و BETA.DIST توجد دوال خاصة يتعين على أي مكتبة دقيقة تقييمها، وليس مجرد تقريبها يدوياً. يجب على مكون جداول البيانات الذي يدعي التوافق مع Excel إعادة إنتاج هذه القيم إلى آخر رقم يظهره Excel، مما يعني إعادة إنتاج الأساليب العددية، وليس فقط أسماء الدوال

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

استدعاء ورقة عمل واحد، وخمسون توزيعاً خلفه

تمتد الدوال لتشمل العائلات التي يحتاجها دفتر عمل الإحصاء. هناك العائلة الطبيعية، NORM.DIST و NORM.S.DIST مع مقلوباتها؛ وعائلة غاما وكاي تربيع، GAMMA.DIST و CHISQ.DIST و CHISQ.DIST.RT و CHISQ.INV.RT؛ وعائلة بيتا، BETA.DIST و BETA.INV؛ وتوزيعات المعاينة T.DIST و T.DIST.2T و F.DIST و F.INV؛ والزوج المنفصل BINOM.DIST و POISSON.DIST؛ ومساعدات الاستدلال مثل CONFIDENCE.T و CONFIDENCE.NORM. من مقعد المستدعي، تكون كل منها صيغة واحدة. حيث تقوم بتعيين المدخلات في الخلايا، وتطلب من دفتر العمل التقييم، وتقرأ النتيجة

var
  wb: IXLSWorkbook;
  sh: IXLSWorksheet;
begin
  wb := TXLSWorkbook.Create;
  try
    sh := wb.Sheets.Add;
    sh.Range['A1', 'A1'].Value := 115;   // observation
    sh.Range['A2', 'A2'].Value := 100;   // mean
    sh.Range['A3', 'A3'].Value := 15;    // standard deviation

    // The XLS formula parser uses ';' as the argument separator.
    Writeln(wb.Calculate('=NORM.DIST(A1;A2;A3;TRUE())'));   // 0.8413447
    Writeln(wb.Calculate('=CHISQ.INV.RT(0.05;10)'));        // 18.3070381
    Writeln(wb.Calculate('=BETA.DIST(0.5;2;3;TRUE())'));    // 0.6875
  finally
    // object lifetime managed
  end;
end;

تقوم طريقة Calculate في دفتر العمل بتجميع وتقييم صيغة مخصصة مقابل الورقة النشطة وتسليم Variant. هناك تفصيل واحد يقع فيه الناس في المحاولة الأولى: يأخذ المحلل اللغوي للصيغ خلف Calculate الفاصلة المنقوطة كفاصل لمعاملاته، لذا تكون الصيغة =SUM(A1;B1) وليس =SUM(A1,B1). بينما تحتفظ صيغ الخلايا المخزنة بالفاصلة القياسية في Excel. يقوم نفس المقيم بإرسال كل دالة إحصائية أدناه، لذا بمجرد أن تعمل إحدى هذه الدوال في Calculate، فإن الباقي يتبع نفس المسار

الدالتان اللتان يبنى عليهما كل شيء آخر

معظم التوزيعات التراكمية في هذه المجموعة لا يتم حسابها عن طريق جمع أو تكامل تعريفاتها الخاصة. بل يتم حسابها من دالتين خاصتين: دالة غاما غير المكتملة السفلية المنتظمة، وتكتب P(a, x)، ودالة بيتا غير المكتملة المنتظمة، وتكتب I_x(a, b). داخلياً، هذه هي المساعدات التي يعتمد عليها المرسلون، والسلسلة قصيرة. دالة كاي تربيع CDF هي دالة غاما CDF ذات الشكل df/2 والمقياس 2. ودالة غاما CDF هي P(a, x) مباشرة. وتعد دالتا التراكم t و F والتراكم ثنائي الحدين جميعهما قيم دالة بيتا غير المكتملة المنتظمة عند المعاملات الصحيحة. ودالة بواسون CDF هي دالة غاما غير المكتملة العلوية Q. نفذ دالتي غاما وبيتا بشكل جيد وسترث عشرات التوزيعات دقتهما مجاناً

كلمة "منتظم" (regularized) هي النقطة الأساسية. فدالة غاما غير المكتملة الخام تنمو مثل المضروب، وتكامل بيتا الخام يمكن أن يفيض أو يقل تحت الحد المسموح به قبل وقت طويل من وصول الإجابة. بينما تُقسم الأشكال المنتظمة على غاما أو بيتا الكاملة، لذا فهي تعيش بالكامل في الفترة من صفر إلى واحد، وهو بالضبط النطاق الذي يشغله الاحتمال. هذا التطبيع هو ما يسمح لنفس الروتين بخدمة كاي تربيع بدرجتين من الحرية وآخر بمائتين دون أن تنفد الحدود المتوسطة من نهاية متغير double. وهو يوضح أيضاً سبب عدم حساب دالة CDF عن طريق جمع ذيل طويل من شروط الكثافة: فكل شرط يحمل خطأ التقريب الخاص به، وتتراكم الأخطاء أثناء تشغيل السلسلة، وتتجنب الدالة الخاصة المنتظمة المجموع بالكامل عن طريق تقييم سلسلة سريعة التقارب أو كسر مستمر بدلاً من ذلك

متسلسلات تحت القطر، وكسور مستمرة فوقه

يتخذ روتين غاما غير المكتمل قراراً واحداً قبل حساب أي شيء: فهو يقارن x بـ a + 1. هذا الحد ليس عشوائياً. فتوسيع متسلسلة القوى لـ P(a, x) يتقارب بسرعة عندما يكون x صغيراً بالنسبة لـ a، وببطء، وبلا فائدة في النهاية، عندما يكون x كبيراً. وللكسر المستمر طابع معاكس. لذلك يستخدم المحرك متسلسلة القوى لـ x الأقل من a + 1 وكسر لينتز المستمر لـ x المساوية أو الأكبر من a + 1، ويُطلب من كل فرع القيام بالعمل الذي يتقنه فقط

يحتاج الكسر المستمر إلى حارس واحد. تعمل طريقة لينتز عن طريق حمل بسط ومقام جاريين وعكس المقام عند كل خطوة، وإذا اقترب أحدهما من الصفر فإن العكس ينفجر. الحل هو حد أدنى صغير جداً: فكلما انخفض حد متوسط عن حوالي 1e-30 في الحجم، يتم تثبيته عند 1e-30، مما يبقي التكرار محدوداً دون إزعاج القيمة المتقاربة. يظهر نفس التثبيت في كسر بيتا غير المكتمل المستمر لنفس السبب. إنه ثابت صغير يقوم بعمل كبير، وهو الفرق بين تقييم مستقر وقسمة على شيء لا يمكن تمييزه عن الصفر

الذيل العلوي، Q(a, x)، هو ببساطة 1 ناقص P(a, x)، وهكذا يتم حساب فرع بواسون التراكمي: احتمال وجود k من الأحداث على الأكثر بمتوسط λ هو Q(k + 1, λ). إن توجيهه من خلال غاما غير المكتملة العلوية بدلاً من جمع شروط بواسون k + 1 هو، مرة أخرى، خيار لتقييم تعبير متقارب واحد بدلاً من تجميع العديد من التعبيرات الصغيرة

الكتل المنفصلة دون فيضان المضروب

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

// Binomial probability mass, evaluated entirely in log space.
// LnGammaF(n+1) is ln(n!); the three log-factorials form ln(C(n,k)),
// and the whole exponent is built before a single Exp call.
//   ln P(X=k) = ln(n!) - ln(k!) - ln((n-k)!) + k*ln(p) + (n-k)*ln(1-p)
result := Exp(LnGammaF(nt + 1) - LnGammaF(kk + 1) - LnGammaF(nt - kk + 1)
  + kk * Ln(pp) + (nt - kk) * Ln(1 - pp));

دالة لوغاريتم غاما نفسها هي تقريب لانكزوس، وهي دقيقة عبر المحور الموجب بأكمله ورخيصة التقييم. ونظراً لأن كل كمية كبيرة يتم الاحتفاظ بها كلوغاريتم خاص بها حتى Exp النهائي، فإن أكبر رقم ينتجه الروتين هو الاحتمال نفسه، وهو على الأكثر واحد. تتبع دالة كتلة بواسون نفس الوصفة، حيث يحل مصطلح لوغاريتم غاما الفردي محل المضروب في المقام. وتعد الأشكال المغلقة حالات خاصة عند الحواف، حيث تكون p صفراً أو واحداً تماماً، لذا لا يستدعي الكود أبداً Ln(0). يعيد HotXLS قيمة 0.2460938 لـ BINOM.DIST(5,10,0.5,FALSE) و 0.6766764 لـ POISSON.DIST(2,2,TRUE) التراكمية، بما يطابق Excel عبر الأرقام التي يطبعها

المقلوبات عن طريق حصر المنحنى الأمامي

تسأل دالة التوزيع العكسي السؤال المعاكس: بالنظر إلى الاحتمال، ابحث عن القيمة التي تساويها دالة CDF الخاصة بها. دالة عكسية واحدة فقط في هذه المجموعة لها صيغة مباشرة سريعة. وهي NORM.S.INV، التوزيع الطبيعي المعياري العكسي، والتي تستخدم تقريب أكلم الكسري، وهو زوج من نسب متعددات الحدود دقيق تقريباً لدرجة الدقة لمتغير double عبر النطاق بأكمله، وينقسم إلى منطقة مركزية وذيلين. إنه تقييم ذو شكل مغلق بدون تكرار

المقلوبات الأخرى ليس لها مثل هذه الصيغة، لذلك يقوم المحرك بعكسها عددياً. فهو يحصر الإجابة بحد أدنى وحد أقصى يتم اختيارهما من دعم التوزيع، ثم ينصف: يقيم دالة CDF الأمامية عند نقطة المنتصف، وينقل أي من الحدين اللذين يبقيان الاحتمال المستهدف محصوراً، ويكرر ذلك حتى تضيق الفترة. بالنسبة لمقلوبات غاما وكاي تربيع، يبدأ الحصر عند الصفر وتقدير علوي سخي مبني من الشكل والمقياس، مع مضاعفة الحد العلوي إذا لم يكن الاحتمال محصوراً بعد. ويحصر مقلوب t حدوداً متماثلة تتسع نحو الخارج；بينما ينصف مقلوب F على فترة غير سالبة. التكلفة هي بضع عشرات من تقييمات CDF لكل استدعاء، وهو أمر غير مرئي بسرعة جدول البيانات، والفائدة هي أن كل مقلوب دقيق تماماً مثل الدالة الأمامية التي يعكسها. ولهذا السبب فإن رحلة ذهاب وإياب مثل CHISQ.DIST(CHISQ.INV(0.7,5),5,TRUE) تعيد 0.7 بدقة متناهية

اللوغاريتم ذو الأساس 10 الذي اختبأ في الذيل

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

أخذت نسخة مبكرة من فرع الذيل لوغاريتماً ذا أساس 10 حيث ينبغي أن يكون اللوغاريتم الطبيعي. يختلف الاثنان بعامل ثابت يبلغ حوالي 2.30, لذا كانت نتائج الذيل خاطئة بهامش ثابت وكبير. ومع ذلك بدت الدالة جيدة في كل فحص عشوائي، لأن الفحوصات العشوائية تعيش في المنتصف. قيمة NORM.S.INV(0.5) هي صفر، وقيمة NORM.S.INV(0.975) هي القيمة المذكورة في الكتب الدراسية 1.959964، ويمر كلاهما عبر متعدد الحدود المركزي الذي لا يستدعي لوغاريتماً على الإطلاق. لم يظهر الخطأ إلا بمجرد عبور الاحتمال إلى الذيل، مثل NORM.S.INV(0.001)، والتي يجب أن تعيد -3.0902323 وجاءت بدلاً من ذلك قريبة من نسبة اللوغاريتم الطبيعي مقابل أساس 10 الخاطئة. وأي دالة تعتمد على التوزيع الطبيعي العكسي في ذيلها، بما في ذلك مساعدات فترات الثقة، ورثت نفس الانحراف. الدرس بسيط ومكلف: الدالة ذات هيكل الفروع تحتاج إلى نقاط اختبار داخل كل فرع، لأن المسار المشترك الصحيح سيخفي ببهجة مساراً نادراً معطلاً. كان الحل عبارة عن تغيير رمز واحد من لوغاريتم الأساس 10 إلى اللوغاريتم الطبيعي، وانطبقت قيم الذيل مع قيم Excel

إشارة x تحدد ذيل توزيع t

تحمل دالة التراكم لتوزيع t لـ Student تفصيلاً دقيقاً يسهل فهمه بشكل عكسي. تأتي قيمتها من دالة بيتا غير المكتملة المنتظمة المقيمة عند df / (df + x²)، ولكن قيمة بيتا تلك هي الاحتمال في الذيل وراء حجم x، وليس الاحتمال التراكمي حتى x. ويعني الشكل المتماثل لتوزيع t أن التحويل يعتمد على أي جانب من الصفر يقع فيه x

// Student t CDF. ib is the regularized incomplete beta at df/(df+x*x),
// which measures the symmetric tail. The cumulative value depends on
// the sign of x; returning ib unconverted gives the wrong tail.
ib := BetaIF(df / 2, 0.5, df / (df + x * x));
if x > 0 then
  result := 1 - 0.5 * ib        // above the mean: one minus half the tail
else if x < 0 then
  result := 0.5 * ib            // below the mean: half the tail
else
  result := 0.5;                // exactly at the mean

لا شيء من هذا مرئي من الخلية، وهذه هي النتيجة المقصودة. أنت تكتب صيغة وتقرأ رقماً يتوافق مع Excel. إذا كنت تقوم بتوسيع المحرك بمنطقك الخاص، فإن آليات تسجيل الدالة مغطاة في جولتنا حول محرك الصيغ والدوال المخصصة، وتتم تغطية طريقة وصول الصيغ عبر الأوراق والنطاقات المسماة في المقال الخاص بالأسماء المسماة والمراجع عبر الأوراق. كل هذا يتم شحنه داخل مكون جداول البيانات HotXLS لـ Delphi و C++Builder، إلى جانب واجهات برمجة تطبيقات القراءة والكتابة والرسم البياني والتنسيق المغطاة في مكان آخر من هذه المدونة