★ 二次元正規分布関数をExcelのVBAで計算 ★

4108.　二次元正規分布関数をExcelのVBAで計算　BPS　2004/08/30 (月) 15:11
├4110.　Re: 二次元正規分布関数をExcelのVBAで計算　青木繁伸　2004/08/30 (月) 17:09
│└4114.　Re^2: 二次元正規分布関数をExcelのVBAで計算　BPS　2004/08/30 (月) 18:12
│　└4120.　Re^4: 二次元正規分布関数をExcelのVBAで計算　青木繁伸　2004/08/30 (月) 22:15
│　　└4121.　Re^5: 二次元正規分布関数をExcelのVBAで計算　青木繁伸　2004/08/30 (月) 22:16
│　　　└4125.　Re^6: 二次元正規分布関数をExcelのVBAで計算　BPS　2004/08/31 (火) 12:25
└4109.　Re: 二次元正規分布関数をExcelのVBAで計算　ひの　2004/08/30 (月) 15:56
　└4111.　Re^2: 二次元正規分布関数をExcelのVBAで計算　BPS　2004/08/30 (月) 17:17

4108.　二次元正規分布関数をExcelのVBAで計算　BPS　　2004/08/30 (月) 15:11
代用特性で工程を管理するときのαとβを計算するため二次元分布関数を計算する方法が必要になりました。インターネットで検索す ると近似値を計算するC++のソースがあったので，VBAの関数に置き換えようとしたのですがうまくいきませんでした。（値が大きくなると1.0を越えて しまう）
どこかにVBAで計算の方法が公開されてないでしょうか？

　　　　　[このページのトップへ]

4110.　Re: 二次元正規分布関数をExcelのVBAで計算　青木繁伸　　2004/08/30 (月) 17:09
> 代用特性で工程を管理するときのαとβを計算するため二次元分布関数を計算する方法が必要になりました。
　中略
> どこかにVBAで計算の方法が公開されてないでしょうか？

何をどのように計算するプログラムなのか，私には書かれている情報ではわからないのですが，皆さんには伝わりますか。

値が小さいところではちゃんと計算できるのでしょうか。
そもそも，C++ のプログラムでは，値が大きくなってもちゃんと計算できるのでしょうか。
そのソースは，どこにあったものなんでしょうね。

良くわからないことばかりです。

　　　　　[このページのトップへ]

4114.　Re^2: 二次元正規分布関数をExcelのVBAで計算　BPS　　2004/08/30 (月) 18:12
> 値が小さいところではちゃんと計算できるのでしょうか。
> そもそも，C++ のプログラムでは，値が大きくなってもちゃんと計算できるのでしょうか。
> そのソースは，どこにあったものなんでしょうね。
>

Reありがとうございます。説明不足であり申し訳ありませんでした。
計算プログラムは以下のサイトにあった内容です。相関係数を0.844，計算式中のa,bを-3.0から3.0にして計算結果を表にしてみましたが，互いの分布関数の積に近い値がでましたが，a,bの値が大きくなると1.0を越えてしまいました。

http://finance.bi.no/~bernt/gcc_prog/algoritms_v1/algoritms/node59.html

　　　　　[このページのトップへ]

4120.　Re^4: 二次元正規分布関数をExcelのVBAで計算　青木繁伸　　2004/08/30 (月) 22:15
ちょっと，移植の際に間違いが。。
N <- function (a, b, rho) {
    if (a <= 0 && b <= 0 && rho <= 0) { 
        f <- function (x, y, aprime, bprime, rho) {
            exp(aprime*(2*x-aprime) + bprime*(2*y-bprime) + 2 * rho * (x-aprime) * (y-bprime))
        }
        aprime <- a/sqrt(2*(1-rho*rho))
        bprime <- b/sqrt(2*(1-rho*rho))
        A <- c(0.3253030, 0.4211071, 0.1334425, 0.006374323)
        B <- c(0.1337764, 0.6243247, 1.3425378, 2.2626645)
        return(sum(outer(1:4, 1:4, function(i, j) A[i]*A[j]*f(B[i], B[j], aprime, bprime, rho))) * ( sqrt(1-rho*rho)/pi))
    }
    else  if (a*b*rho <= 0) {
        if (a <= 0 && b >= 0 && rho >= 0) {
            return(pnorm(a)-N(a, -b, -rho))
        }
        else if (a >= 0 && b <= 0 && rho >= 0) {
            return(pnorm(b)-N(-a, b, -rho))
        }
        else if (a >= 0 && b >= 0 && rho <= 0) {
            return(pnorm(a)+pnorm(b)-1+N(-a, -b, rho))
        }
    }
    else  if (a*b*rho >= 0) {
        denum <- sqrt(a^2-2*rho*a*b+b^2)
        return(N(a, 0, ((rho*a-b)*sign(a))/denum)+N(b,0,((rho*b-a)*sign(b))/denum)-(1-sign(a)*sign(b))/4)
    }
    return(-99.9) # should never get here
}

res <- numeric(49)
count <- 0
for (a in -3:3) {
    for (b in -3:3) {
        count <- count+1
        res[count] <- N(a, b, 0.844)
    }
}
ans <- matrix(res, 7,7)
data.list(ans)

　　　　　[このページのトップへ]

4121.　Re^5: 二次元正規分布関数をExcelのVBAで計算　青木繁伸　　2004/08/30 (月) 22:16
答え
0.00045792   0.001229189  0.001348780  0.001349898  0.001349898  0.001349898  0.001349898 
0.001229189  0.01118913   0.02173499   0.02274453   0.02275013   0.02275013   0.02275013 
0.001348780  0.02173499   0.1047509    0.1557154    0.1586457    0.1586553    0.1586553 
0.001349898  0.02274453   0.1557154    0.409905     0.4970602    0.4999944    0.5 
0.001349898  0.02275013   0.1586457    0.4970602    0.7874404    0.8403296    0.8413436 
0.001349898  0.02275013   0.1586553    0.4999944    0.8403296    0.9656889    0.9771292 
0.001349898  0.02275013   0.1586553    0.5          0.8413436    0.9771292    0.9977581
ですかしら。
MacintoshのVBAは再帰関数がうまく動かない？

　　　　　[このページのトップへ]

4125.　Re^6: 二次元正規分布関数をExcelのVBAで計算　BPS　　2004/08/31 (火) 12:25
ありがとうございました。

先生のプログラムと私のVBAのプログラムを見比べてVBAへ置き換える時の間違いがわかりました。それで再計算した結果，先生の結果に対し誤差は平均5ppm程度になりました。VBAでも実用上は問題なさそうです。
どうもありがとうございました。
0.00045792 0.00122926 0.00134885 0.00134997 0.00134997 0.00134997 0.00134997
0.00122926 0.01118913 0.02173492 0.02274446 0.02275006 0.02275006 0.02275006
0.00134885 0.02173492 0.10475093 0.15571545 0.15864566 0.15865526 0.15865526
0.00134997 0.02274446 0.15571545 0.40990497 0.49706019 0.49999440 0.50000000
0.00134997 0.02275006 0.15864566 0.49706019 0.78744041 0.84032959 0.84134362
0.00134997 0.02275006 0.15865526 0.49999440 0.84032959 0.96568900 0.97712923
0.00134997 0.02275006 0.15865526 0.50000000 0.84134362 0.97712923 0.99775799

　　　　　[このページのトップへ]

4109.　Re: 二次元正規分布関数をExcelのVBAで計算　ひの　　2004/08/30 (月) 15:56
C++のソースがあるなら，それで書いてDLLにしておいてExcelから呼び出せばよいでしょう。

　　　　　[このページのトップへ]

4111.　Re^2: 二次元正規分布関数をExcelのVBAで計算　BPS　　2004/08/30 (月) 17:17
> C++のソースがあるなら，それで書いてDLLにしておいてExcelから呼び出せばよいでしょう。
回答ありがとうございます。
簡 単な計算のプログラムしか経験がなくシステムの知識も乏しいのでDLLの利用についてよくわかりません。私にとってC++をExcelのFunction プロシジャーに置き換えることの方が簡単に思い作ってみようとしたのですが。この機会にDLLについても勉強したいと思います。
ありがとうございました。

　　　　　[このページのトップへ]

● 「統計学関連なんでもあり」の過去ログ--- 030 の目次へジャンプ
● 「統計学関連なんでもあり」の目次へジャンプ
● 直前のページへ戻る