توابع آماری اکسل در دلفی: NORM ،CHISQ ،BETA

عبارت =NORM.DIST(115,100,15,TRUE) را در یک سلول بنویسید تا اکسل بدون هیچ معطلی مقدار 0.8413447 را برگرداند. این فراخوانی شبیه به یک جستجوی ساده به نظر می‌رسد، اما این‌طور نیست. در پس این عدد، توزیع نرمال تجمعی (cumulative normal distribution) قرار دارد که انتگرالی بدون شکل بسته است، و در پس توابع CHISQ.INV.RT و BETA.DIST توابع خاصی قرار دارند که یک کتابخانه دقیق باید آنها را ارزیابی کند، نه اینکه به صورت دستی تخمین بزند. یک کامپوننت صفحه‌گسترده که ادعای سازگاری با اکسل را دارد باید این مقادیر را تا آخرین رقم اعشاری که اکسل نشان می‌دهد تولید کند، که این به معنای بازتولید روش‌های محاسبات عددی است، نه فقط نام توابع

ابزار HotXLS بیش از پنجاه مورد از این توابع آماری را پیاده‌سازی می‌کند، و کارهایی که برای تصحیح آنها انجام شده است تقریباً به طور کامل از نوار فرمول پنهان است. این یک راهنما درباره نحوه محاسبه آنها توسط موتور محاسباتی است: هسته مشترک توابع خاص، تصمیم‌گیری‌های شاخه‌ای که محاسبات ریاضی را پایدار نگه می‌دارند، و یک باگ مربوط به توزیع نرمال معکوس که برای مدت طولانی در بخش دنباله (tail) پنهان شده بود زیرا موارد متداول هرگز با آن بخش خراب مواجه نمی‌شدند

یک فراخوانی کاربرگ، پنجاه توزیع در پس آن

توابع شامل خانواده‌هایی هستند که در یک کارپوشه آماری مورد استفاده قرار می‌گیرند. خانواده توزیع نرمال شامل NORM.DIST و NORM.S.DIST به همراه معکوس‌های آنها؛ خانواده گاما و کای‌اسکوئر (chi-square) شامل 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. از دید فراخوان‌کننده، هر کدام یک فرمول ساده هستند. شما ورودی‌ها را در سلول‌ها قرار می‌دهید، از کارپوشه می‌خواهید آن را ارزیابی کند و نتیجه را می‌خوانید

متد Calculate روی کارپوشه، یک فرمول موقت را در برابر شیت پویا کامپایل و ارزیابی کرده و یک مقدار Variant برمی‌گرداند. یک نکته در اولین تلاش ممکن است باعث سردرگمی شود: پارسر فرمول در پس Calculate علامت سمی‌کالن را به عنوان جداکننده آرگومان خود در نظر می‌گیرد، بنابراین فرمول به صورت =SUM(A1;B1) نوشته می‌شود و نه =SUM(A1,B1). فرمول‌های ذخیره‌شده سلول‌ها ویرگول استاندارد اکسل را حفظ می‌کنند. همان ارزیاب هر تابع آماری را در زیر ارسال می‌کند، بنابراین وقتی یکی از آنها در Calculate کار کند، بقیه نیز از همان مسیر پیروی می‌نمایند

var
  wb: IXLSWorkbook;
  sh: IXLSWorksheet;
begin
  wb := TXLSWorkbook.Create;
  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
end;

دو تابعی که همه چیز بر پایه آنها ساخته شده است

بیشتر توابع تجمعی در این مجموعه از طریق جمع‌بندی یا انتگرال‌گیری تعاریف خودشان محاسبه نمی‌شوند. آنها با استفاده از دو تابع خاص محاسبه می‌گردند: تابع گامای ناقص پایین نرمال‌شده (regularized lower incomplete gamma) که به صورت P(a, x) نوشته می‌شود، و تابع بتای ناقص نرمال‌شده (regularized incomplete beta) که به صورت I_x(a, b) نوشته می‌شود. در بخش داخلی، اینها توابع کمکی هستند که متدها به آنها تکیه می‌کنند، و زنجیره کوتاهی دارند. تابع CDF کای‌اسکوئر همان CDF گاما با پارامتر شکل df/2 و پارامتر مقیاس ۲ است. CDF گاما مستقیماً برابر با P(a, x) است. توابع تجمعی t، F و دوجمله‌ای همگی مقادیری از بتای ناقص نرمال‌شده در آرگومان‌های صحیح هستند. CDF پوآسون همان گامای ناقص بالا Q است. توابع گاما و بتا را به درستی پیاده‌سازی کنید تا چندین توزیع دقت خود را به طور رایگان از آنها به ارث ببرند

کلمه «نرمال‌شده» (regularized) نکته اصلی است. تابع گامای ناقص خام مانند یک فاکتوریل رشد می‌کند و انتگرال بتای خام می‌تواند بسیار قبل از رسیدن به پاسخ نهایی دچار سرریز منفی (underflow) یا سرریز مثبت (overflow) شود. اشکال نرمال‌شده بر گامای کامل یا بتای کامل تقسیم می‌شوند، بنابراین کاملاً در بازه صفر تا یک قرار می‌گیرند که دقیقاً همان محدوده‌ای است که یک احتمال اشغال می‌کند. این نرمال‌سازی چیزی است که به یک تابع اجازه می‌دهد برای کای‌اسکوئر با دو درجه آزادی و دیگری با دویست درجه آزادی بدون انحراف متغیرهای میانی عمل کند. این همچنین توضیح می‌دهد که چرا شما یک CDF را با جمع کردن دنباله طولانی از عبارت‌های چگالی محاسبه نمی‌کنید: هر عبارت خطای گرد کردن خود را دارد، با اجرای سری خطاها انباشته می‌شوند، و تابع خاص نرمال‌شده با ارزیابی یک سری همگرا یا یک کسر مسلسل به جای آن، فرآیند جمع زدن را کاملاً دور می‌زند

سری‌های زیر قطر، کسر مسلسل بالای آن

تابع گامای ناقص قبل از هر محاسباتی یک تصمیم می‌گیرد: مقدار x را با a + 1 مقایسه می‌کند. این مرز تصادفی نیست. بسط سری توانی P(a, x) زمانی که x نسبت به a کوچک باشد سریع همگرا می‌شود، و زمانی که x بزرگ باشد به آرامی و در نهایت به شکلی بی‌فایده همگرا می‌گردد. کسر مسلسل ویژگی معکوس دارد. بنابراین موتور محاسباتی از سری توانی برای xهای کوچک‌تر از a + 1 و از کسر مسلسل لنز (Lentz continued fraction) برای xهای بزرگ‌تر یا مساوی a + 1 استفاده می‌کند، و از هر شاخه خواسته می‌شود تا تنها کاری را که در آن عملکرد خوبی دارد انجام دهد

کسر مسلسل به یک بخش محافظ نیاز دارد. متد لنز با حمل صورت و مخرج جاری و معکوس کردن مخرج در هر مرحله کار می‌کند، و اگر هر کدام به سمت صفر بروند فرآیند معکوس‌سازی با خطا مواجه می‌شود. راه حل یک کف عددی بسیار کوچک است: هرگاه بزرگی یک عبارت میانی به کمتر از حدود 1e-30 برسد، روی 1e-30 محدود می‌شود، که تکرار را بدون به هم زدن مقدار همگرا شده، محدود نگه می‌دارد. همین محدودیت در کسر مسلسل بتای ناقص نیز به همین دلیل ظاهر می‌شود. این یک ثابت کوچک است که کار بسیار مهمی انجام می‌دهد، تفاوتی بین یک ارزیابی پایدار و تقسیم بر چیزی که از صفر قابل تشخیص نیست

دنباله بالایی، یعنی Q(a, x)، به سادگی برابر با ۱ منهای P(a, x) است، و این روشی است که شاخه تجمعی پوآسون محاسبه می‌شود: احتمال حداکثر k رویداد با میانگین λ برابر با Q(k + 1, λ) است. هدایت آن از طریق گامای ناقص بالایی به جای جمع زدن k + 1 عبارت پوآسون، مجدداً انتخابی برای ارزیابی یک عبارت همگرا به جای انباشته کردن موارد کوچک بسیار است

جرم‌های گسسته بدون سرریز فاکتوریل

توزیع‌های گسسته خطر متفاوتی را به همراه دارند. یک تابع جرم احتمال دوجمله‌ای شامل یک ضریب دوجمله‌ای است، و ضریب برای عبارت «بیست و شش از پنجاه و دو» یک عدد صحیح بسیار بزرگ است. اگر آن را مستقیماً بنویسید، صورت کسر قبل از انجام تقسیمی که آن را به یک احتمال منطقی بازگرداند، در یک متغیر double سرریز می‌کند. موتور محاسباتی هرگز آن را مستقیم تولید نمی‌کند. این متد فاکتوریل‌ها را در فضای لگاریتمی از طریق تابع لگاریتم-گاما (log-gamma) محاسبه می‌کند، لگاریتم‌ها را جمع و تفریق می‌نماید، لگاریتم احتمال‌های موفقیت و شکست را به آن اضافه می‌کند و در نهایت یک بار به توان می‌رساند

// 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));

خود تابع لگاریتم-گاما یک تخمین لانچوس (Lanczos approximation) است که در کل محور مثبت دقیق بوده و هزینه پردازشی کمی دارد. از آنجا که هر مقدار بزرگ تا زمان اجرای Exp نهایی به صورت لگاریتم نگه داشته می‌شود، بزرگ‌ترین عددی که این متد تولید می‌کند خود احتمال است که حداکثر یک می‌باشد. تابع جرم پوآسون نیز از همین فرمول پیروی می‌کند، با این تفاوت که عبارت لگاریتم-گامای واحد جایگزین فاکتوریل در مخرج کسر می‌شود. فرمول‌های بسته در لبه‌ها که p دقیقاً صفر یا یک است به صورت حالت خاص مدیریت می‌شوند، بنابراین کد هرگز متد Ln(0) را فراخوانی نمی‌کند. HotXLS مقدار 0.2460938 را برای BINOM.DIST(5,10,0.5,FALSE) و 0.6766764 را برای فرمول تجمعی POISSON.DIST(2,2,TRUE) برمی‌گرداند، که با ارقام چاپی اکسل مطابقت دارد

معکوس‌ها با محدود کردن منحنی مستقیم

یک تابع توزیع معکوس سؤال معکوس را می‌پرسد: با داشتن یک احتمال، مقداری را پیدا کنید که CDF آن با آن برابر باشد. تنها یک معکوس در این مجموعه دارای یک فرمول مستقیم سریع است. تابع NORM.S.INV که معکوس توزیع نرمال استاندارد است، از یک تخمین گویای آکلام (Acklam rational approximation) استفاده می‌کند؛ یک جفت نسبت چندجمله‌ای که تقریباً تا دقت یک متغیر double در کل محدوده دقیق است و به یک ناحیه مرکزی و دو دنباله تقسیم می‌شود. این یک ارزیابی فرمول بسته بدون تکرار است

سایر معکوس‌ها چنین فرمولی ندارند، بنابراین موتور محاسباتی آنها را به صورت عددی معکوس می‌کند. این متد پاسخ را با یک حد پایین و بالا که از دامنه توزیع انتخاب شده است محدود می‌کند، سپس نصف می‌کند: CDF مستقیم را در نقطه میانی ارزیابی می‌کند، هر کدام از مرزها را که احتمال هدف را محصور نگه می‌دارد جابجا می‌نماید، و این کار را تکرار می‌کند تا بازه باریک شود. برای معکوس‌های گاما و کای‌اسکوئر، بازه از صفر و یک تخمین بالایی بزرگ ساخته‌شده از شکل و مقیاس شروع می‌شود و در صورتی که احتمال هنوز محصور نشده باشد، مرز بالایی را دو برابر می‌کند. معکوس t بازه‌های متقارنی را که به سمت بیرون گسترش می‌بند محدود می‌کند؛ معکوس F در یک بازه غیرمنفی نصف می‌شود. هزینه پردازشی آن چند ده ارزیابی CDF در هر فراخوانی است که در سرعت صفحه‌گسترده نامحسوس است، و مزیت آن این است که هر معکوس دقیقاً به اندازه تابع مستقیمی که معکوس می‌کند دقیق است. به همین دلیل است که یک رفت و برگشت مانند CHISQ.DIST(CHISQ.INV(0.7,5),5,TRUE) مقدار ۰.۷ را با دقت بسیار بالا برمی‌گرداند

لگاریتم مبنای ۱۰ که در دنباله پنهان شده بود

در اینجا باگی وجود دارد که ارزش گفتن دارد، زیرا از آن دسته باگ‌هایی است که برای مدت طولانی باقی می‌ماند. متد نرمال معکوس آکلام سه شاخه دارد. شاخه مرکزی عریض، که هرگاه احتمال بین حدود 0.025 و 0.975 باشد استفاده می‌شود، ورودی را از طریق یک نسبت چندجمله‌ای بدون هیچ لگاریتمی در آن اجرا می‌کند. دو شاخه دنباله، برای احتمال‌های بسیار کوچک یا بسیار بزرگ، ابتدا یک لگاریتم از ورودی می‌گیرند، زیرا دنباله مانند ریشه دوم قرینه لگاریتم طبیعی p رفتار می‌کند

نسخه اولیه شاخه دنباله، یک لگاریتم مبنای ۱۰ را در جایی که لگاریتم طبیعی تعلق داشت، می‌گرفت. این دو با یک ضریب ثابت حدود ۲.۳۰ با هم تفاوت دارند، بنابراین نتایج دنباله با یک اختلاف ثابت و قابل توجه اشتباه بودند. با این حال، تابع در هر بررسی ساده درست به نظر می‌رسید، زیرا بررسی‌های ساده در مرکز قرار دارند. مقدار NORM.S.INV(0.5) صفر است، NORM.S.INV(0.975) مقدار استاندارد 1.959964 است، و هر دو مورد از چندجمله‌ای مرکزی عبور می‌کنند که اصلاً لگاریتمی را فراخوانی نمی‌کند. خطا تنها زمانی ظاهر می‌شد که یک احتمال وارد یک دنباله می‌شد، مثلاً NORM.S.INV(0.001) که باید -3.0902323 را برگرداند، اما مقدار متفاوتی را برمی‌گرداند. هر تابعی که در دنباله خود به توزیع نرمال معکوس بستگی داشت، از جمله توابع کمکی بازه اطمینان، همین انحراف را به ارث می‌برد. درس این تجربه ساده اما هزینه‌بر است: یک تابع با ساختار شاخه‌ای به نقاط آزمایش در داخل هر شاخه نیاز دارد، زیرا یک مسیر رایج صحیح به راحتی یک مسیر خراب نادر را پنهان می‌کند. راه حل، تغییر یک توکن از لگاریتم مبنای ۱۰ به لگاریتم طبیعی بود، و مقادیر دنباله بلافاصله با مقادیر اکسل هماهنگ شدند

علامت x دنباله توزیع t را تعیین می‌کند

تابع تجمعی t استودنت ظرافتی دارد که به راحتی می‌توان آن را اشتباه گرفت. مقدار آن از بتای ناقص نرمال‌شده ارزیابی‌شده در 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

هیچ‌کدام از این‌ها از داخل سلول قابل مشاهده نیست، و این همان نتیجه مورد نظر است. شما یک فرمول می‌نویسید و عددی را می‌خوانید که با اکسل مطابقت دارد. اگر در حال توسعه موتور محاسباتی با منطق خود هستید، مکانیسم ثبت یک تابع در بخش راهنمای ما درباره موتور فرمول و توابع سفارشی پوشش داده شده است، و نحوه دسترسی فرمول‌ها به شیت‌ها و محدوده‌های نام‌گذاری‌شده در مقاله مربوط به نام‌های تعریف‌شده و ارجاعات بین‌شیتی توضیح داده شده است. تمام این امکانات در داخل HotXLS spreadsheet component برای دلفی و سی‌پلاس‌پلاس‌بیلدر، در کنار APIهای خواندن, نوشتن، نمودارسازی و قالب‌بندی که در بخش‌های دیگر این وبلاگ پوشش داده شده‌اند، ارائه می‌شوند