Bir hücreye =NORM.DIST(115,100,15,TRUE) yazın ve Excel resmiyet olmadan 0.8413447 döndürür. Çağrı bir arama gibi okunur. Öyle değildir. Bu tek sayının arkasında, kapalı formu olmayan bir integral olan kümülatif normal dağılım vardır ve CHISQ.INV.RT ile BETA.DIST arkasında, dikkatli bir kütüphanenin elle tahmin etmek yerine değerlendirmesi gereken özel fonksiyonlar bulunur. Excel uyumluluğu iddia eden bir e-tablo bileşeni, bu değerleri Excel'in gösterdiği son basamağa kadar yeniden üretmelidir, bu da yalnızca fonksiyon adlarını değil sayısal yöntemleri de yeniden üretmek anlamına gelir。
HotXLS bu istatistiksel fonksiyonlardan elliden fazlasını uygulamaktadır ve bunları doğru kılan çalışma formül çubuğundan neredeyse tamamen görünmezdir. Bu, motorun bunları nasıl hesapladığına dair bir turdur: paylaşılan özel fonksiyon çekirdeği, aritmetiği kararlı tutan dallanma kararları ve ortak durum kırık satıra asla dokunmadığı için uzun süre kuyrukta gizlenen bir ters normal hatası。
Bir çalışma sayfası çağrısı, arkasında elli dağılım
Fonksiyonlar, bir istatistik çalışma kitabının başvurduğu aileleri kapsar. Normal ailesi, NORM.DIST ve tersleriyle birlikte NORM.S.DIST; gama ve ki-kare ailesi, GAMMA.DIST, CHISQ.DIST, CHISQ.DIST.RT, CHISQ.INV.RT; beta ailesi, BETA.DIST ve BETA.INV; örnekleme dağılımları T.DIST, T.DIST.2T, F.DIST ve F.INV; kesikli çift BINOM.DIST and POISSON.DIST; ve CONFIDENCE.T ile CONFIDENCE.NORM gibi çıkarım yardımcıları vardır. Arayanın koltuğundan her biri tek bir formüldür. Hücrelere girdileri ayarlar, çalışma kitabından değerlendirmesini ister ve sonucu okursunuz。
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;Çalışma kitabındaki Calculate yöntemi, canlı sayfaya karşı geçici bir formülü derler ve değerlendirir ve bir Variant geri verir. İlk denemede insanları şaşırtan bir ayrıntı: Calculate arkasındaki formül ayrıştırıcı, noktalı virgülü bağımsız değişken ayırıcısı olarak alır, bu nedenle =SUM(A1,B1) değil =SUM(A1;B1) olur. Depolanan hücre formülleri Excel standardı virgülü korur. Aynı değerlendirici aşağıdaki her istatistiksel fonksiyonu gönderir, bu nedenle bunlardan biri Calculate'de çalıştığında, geri kalanı da aynı yolu izler。
Geri kalan her şeyin üzerine inşa edildiği iki fonksiyon
Bu kümedeki çoğu kümülatif dağılım, kendi tanımlarını toplayarak veya entegre ederek hesaplanmaz. P(a, x) olarak yazılan regüle edilmiş alt eksik gama (regularized lower incomplete gamma) ve Ix(a, b) olarak yazılan regüle edilmiş eksik beta (regularized incomplete beta) olmak üzere iki özel fonksiyondan hesaplanırlar. Dahili olarak bunlar dispatcher'ların dayandığı yardımcılardır ve zincir kısadır. Ki-kare CDF, df/2 biçimine ve 2 ölçeğine sahip gama CDF'dir. Gama CDF'si doğrudan P(a, x)'tir. t, F ve binom kümülatif fonksiyonlarının tümü, doğru bağımsız değişkenlerde regüle edilmiş eksik betanın değerleridir. Poisson'un CDF'si üst eksik gama Q'dur. Gama ve beta fonksiyonlarını iyi uygulayın ve bir düzine dağılım doğruluklarını ücretsiz olarak devralacaktır。
"Regüle edilmiş" (regularized) kelimesi işin aslıdır. Ham eksik gama faktöriyel gibi büyür ve ham beta integrali, cevap vermeden çok önce yetersiz taşabilir (underflow) veya taşabilir (overflow). Regüle edilmiş formlar tam gama veya betaya bölünür, bu nedenle tamamen sıfır ile bir arasındaki aralıkta yaşarlar, bu da tam olarak bir olasılığın kapladığı aralıktır. Bu normalleştirme, aynı rutinin iki serbestlik derecesine sahip bir ki-kareye ve iki yüz serbestlik derecesine sahip olana, aradaki terimler bir double'ın sonundan kaçmadan hizmet etmesini sağlayan şeydir. Ayrıca neden yoğunluk terimlerinin uzun bir kuyruğunu toplayarak bir CDF hesaplamadığınızı da açıklar: her terim kendi yuvarlama hatasını taşır, seri çalıştıkça hatalar birikir ve regüle edilmiş özel fonksiyon bunun yerine hızla yakınsayan bir seri veya sürekli kesir değerlendirerek toplamı tamamen devre dışı bırakır。
Köşegenin altında seriler, üzerinde sürekli kesir
Eksik gama rutini, herhangi bir şeyi hesaplamadan önce tek bir karar verir: x'i a + 1 ile karşılaştırır. Bu sınır rastgele değildir. P(a, x)'in güç serisi açılımı, x a'ya göre küçük olduğunda hızlı, x büyük olduğunda ise yavaş ve sonunda yararsız bir şekilde yakınsar. Sürekli kesir ise tam tersi karaktere sahiptir. Dolayısıyla motor, a + 1'in altındaki x için güç serisini ve a + 1'deki veya üzerindeki x için Lentz sürekli kesrini kullanır ve her daldan yalnızca iyi olduğu işi yapması istenir。
Sürekli kesrin tek bir korumaya ihtiyacı vardır. Lentz yöntemi, çalışan bir pay ve paydayı taşıyarak ve her adımda paydayı ters çevirerek çalışır ve eğer ikisinden biri sıfıra yaklaşırsa ters çevirme işlemi patlar. Çözüm küçük bir tabandır: ne zaman bir ara terim büyüklük olarak kabaca 1e-30'un altına düşerse, 1e-30'a sıkıştırılır; bu da yakınsanan değeri bozmadan yinelemeyi sınırlı tutar. Aynı sıkıştırma, aynı nedenle eksik betanın sürekli kesrinde de görünür. Yük taşıyan işler yapan küçük bir sabittir, kararlı bir değerlendirme ile sıfırdan ayırt edilemeyen bir şeye bölünme arasındaki farktır。
Üst kuyruk, Q(a, x), sadece 1 eksi P(a, x)'tir ve Poisson'un kümülatif dalı bu şekilde hesaplanır: ortalaması λ olan en fazla k olayın olasılığı Q(k + 1, λ) değeridir. Bunu k + 1 Poisson terimini toplamak yerine üst eksik gama üzerinden yönlendirmek, yine birçok küçük terimi biriktirmek yerine yakınsak bir ifadeyi değerlendirme seçimidir。
Faktöriyel taşması olmadan kesikli kütleler
Kesikli dağılımlar farklı bir tehlikeyi ortaya çıkarır. Binom olasılık kütlesi bir binom katsayısı içerir ve elli iki-seçim-yirmi altı katsayısı muazzam bir tamsayıdır. Bunu doğrudan oluşturursanız, pay, onu mantıklı bir olasılığa geri getirecek olan bölünmeden önce double değerini taşırır. Motor bunu asla oluşturmaz. Faktöriyelleri log-gama fonksiyonu aracılığıyla log alanında hesaplar, logları toplar ve çıkarır, başarı ve başarısızlık olasılıklarının logunu katlar ve en sonunda bir kez üs alır。
// 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));Log-gama fonksiyonunun kendisi, tüm pozitif eksen boyunca doğru olan ve değerlendirilmesi ucuz bir Lanczos yaklaşımıdır. Her büyük nicelik nihai Exp değerine kadar logaritması olarak tutulduğundan, rutinin şimdiye kadar somutlaştırdığı en büyük sayı, en fazla bir olan olasılığın kendisidir. Poisson'un kütle fonksiyonu, paydadaki faktöriyelin yerini alan tek log-gama terimiyle aynı tarifi izler. Kapalı formlar uç noktalarda özel olarak ele alınır, burada p tam olarak sıfır veya birdir, bu nedenle kod asla Ln(0) çağırmaz. HotXLS, BINOM.DIST(5,10,0.5,FALSE) için 0.2460938 ve kümülatif POISSON.DIST(2,2,TRUE) için 0.6766764 döndürerek yazdırdığı basamaklar boyunca Excel ile eşleşir。
İleri eğriyi paranteze alarak tersler
Ters dağılım fonksiyonu tersi soruyu sorar: bir olasılık verildiğinde, CDF'si buna eşit olan değeri bulun. Bu kümedeki yalnızca bir tersin hızlı ve doğrudan bir formülü vardır. Ters standart normal olan NORM.S.INV, tüm aralık boyunca kabaca bir double hassasiyetine kadar doğru olan, merkezi bir bölgeye ve iki kuyruğa bölünmüş bir çift polinom oranı olan Acklam rasyonel yaklaşımını kullanır. Yineleme içermeyen kapalı formda bir değerlendirmedir。
Diğer terslerin böyle bir formülü yoktur, bu nedenle motor bunları sayısal olarak tersine çevirir. Dağılımın desteğinden seçilen düşük ve yüksek bir sınırla cevabı paranteze alır, ardından ikiye böler: orta noktada ileri CDF'yi değerlendirin, hedef olasılığı kapalı tutan sınırı hareket ettirin ve aralık daralana kadar tekrarlayın. Gama ve ki-kare tersleri için parantez sıfırdan başlar ve şekil ile ölçekten oluşturulmuş cömert bir üst tahmin, olasılık henüz kapalı değilse üst sınırı ikiye katlar. t tersi dışa doğru genişleyen simetrik sınırları paranteze alır; F tersi negatif olmayan bir aralıkta ikiye böler. Maliyet, çağrı başına birkaç düzine CDF değerlendirmesidir ki bu e-tablo hızında görünmezdir ve faydası, her tersin tam olarak tersine çevirdiği ileri fonksiyon kadar doğru olmasıdır. Bu nedenle CHISQ.DIST(CHISQ.INV(0.7,5),5,TRUE) gibi bir dönüş yolculuğu 0.7 değerini kıl payı döndürür。
Kuyrukta gizlenen 10 tabanına göre logaritma
İşte anlatmaya değer hata budur, çünkü uzun süre hayatta kalan türdendir. Acklam ters normal rutininin üç dalı vardır. Olasılık yaklaşık 0.025 ile 0.975 arasında olduğunda kullanılan geniş merkezi dal, girdiyi içinde hiçbir yerde logaritma olmayan bir polinom oranından geçirir. Çok küçük veya çok büyük olasılıklar için olan iki kuyruk dalı, önce girdinin logaritmasını alır; çünkü kuyruk, p'nin doğal logaritmasının eksi karekökü gibi davranır。
Kuyruk dalının erken bir versiyonu, doğal logaritmanın olması gereken yerde 10 tabanına göre logaritma aldı. İkisi yaklaşık 2.30'luk sabit bir faktör kadar farklılık gösterir, bu nedenle kuyruk sonuçları tutarlı ve büyük bir farkla yanlıştı. Yine de fonksiyon her sıradan kontrolde iyi görünüyordu, çünkü sıradan kontroller ortada yaşıyor. NORM.S.INV(0.5) sıfırdır, NORM.S.INV(0.975) ders kitabı niteliğindeki 1.959964'tür ve her ikisi de hiçbir şekilde logaritma çağırmayan merkezi polinomdan geçer. Hata, yalnızca bir olasılık bir kuyruğa geçtiğinde ortaya çıktı, örneğin -3.0902323 döndürmesi gereken ve bunun yerine doğal logaritma ile 10 tabanına göre logaritma oranı yakınlarında geri dönen NORM.S.INV(0.001). Güven aralığı yardımcıları da dahil olmak üzere kuyruğundaki ters normale bağlı olan herhangi bir fonksiyon aynı sapmayı devraldı. Ders sıradan ve pahalıdır: dallanma yapısına sahip bir fonksiyonun her dalın içinde test noktalarına ihtiyacı vardır, çünkü doğru bir ortak yol kırık ve nadir olanı neşeyle maskeleyecektir. Çözüm, 10 tabanına göre logdan doğal loga tek bir belirteç değişikliğiydi ve kuyruk değerleri Excel'inkilere oturdu。
x'in işareti t-dağılımının kuyruğuna karar verir
Student t kümülatif fonksiyonu, geriye doğru alınması kolay bir incelik taşır. Değeri, df / (df + x²) değerinde değerlendirilen regüle edilmiş eksik betadan gelir, ancak bu beta değeri x'e kadar olan kümülatif olasılık değil, x büyüklüğünün ötesindeki kuyruktaki olasılıktır. t-dağılımının simetrik şekli, dönüşümün x'in sıfırın hangi tarafına düştüğüne bağlı olduğu anlamına gelir。
// 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 meanSıfırın üzerindeki x için kümülatif olasılık, simetrik kuyruğun yarısı eksi birdir; sıfırın altındaki x için bu kuyruğun yarısıdır; sıfırda ise tam olarak yarımdır. Beta değerini doğrudan döndürürseniz, sıfır olmayan herhangi bir x için eğrinin tüm gövdesi kadar saparak dağılımın yanlış tarafını bildirmiş olursunuz. Sağ kuyruk ve iki kuyruklu varyantlar aynı dal üzerine inşa edilir, bu nedenle T.DIST.2T(1,1) 0.5 ve T.DIST(1,1,TRUE) 0.75 olarak geri döner ve ters T.INV bu düzeltilmiş CDF'ye karşı ikiye bölünür, böylece gidiş-dönüş kapanır。
Bunlarının hiçbiri hücreden görünmez ve amaçlanan sonuç da budur. Bir formül yazar ve Excel ile uyuşan bir sayı okursunuz. Motoru kendi mantığınızla genişletiyorsanız, bir fonksiyon kaydetmenin mekaniği formül motoru ve özel fonksiyonlar kılavuzumuzda ele alınmıştır ve formüllerin sayfalara ve tanımlanmış aralıklara nasıl ulaştığı ise tanımlanmış adlar ve sayfalar arası formüller makalesinde ele alınmıştır. Tüm bunlar, bu blogun başka yerlerinde ele alınan okuma, yazma, grafik çizme ve biçimlendirme API'lerinin yanı sıra Delphi ve C++Builder için HotXLS e-tablo bileşeninin içinde gönderilir。