Re: Accessで回帰分析をおこなう

[SETO Sohei]

しもだ　やすゆき <@discussions.microsoft.com> wrote in article
<1886D85D-54B3-4AEB...@microsoft.com> ;
>Access2000を使っています。
>あるデータベースからクエリを作成し、そのクエリをもとに回帰分析をしたいと思っています。
>Excelだと、slope関数やintercept関数でXの傾きやY切片を求めることが出来るのですが、Accessのレ
>ポート上でこれらの関数を実行しようとすると関数自体を認識しないようで結果が表示されません。
>これら統計関数を使用するには特別な操作が必要なのでしょうか？

Access の組み込み関数と, Excel のワークシート関数は,
全くの別物と考えた方が良いです。

* * *

VBAを使えば, Access から Excel のワークシート関数を呼び出す方法も
有るには有ります。

Office 2000 であれば, Access VB Editor の参照設定で,
[Microsoft Excel 9.0 Object Library] にチェックを入れ,
Excel.Applcation.WorksheetFunction.Intercept(～）
といったコーディングで参照することが可能かと思われます。

# Slope() と Intercept() が WorksheetFunction オブジェクトに
# 含まれていることは確認できましたが, そこから先は未試行です。

--
SETO Sohei [ PGP Key ID:0x5DF0FA4D ]
Gobo-city, Wakayama, JAPAN
mailto: s...@creamy.nax.ne.jp

[SETO Sohei]

補足です。

Access のヘルプでも INTERCEPT() や SLOPE()が出てきますが,
これはAccess Web コンポーネントのスプレッドシート・コンポーネントでのみ
使用できるものです。（Excel のワークシート関数とは, また別物です）
通常のレポート/クエリからは利用できません。

もし, スプレッドシートコンポーネント内で,
INTERCEPT()やSLOPE()が使用できないという話であれば msowcf.dll の
(Microsoft Office Web Component Function Library、だったかな？)
の組み込みが必要になります。

[海星]

　SQL集合関数を使って、以下のように相関回帰分析用のクエリを作ってみました。3つのクエリを使いますが、各フィールド名はご自分のデータベースの仕様に当てはめてみてください。
　なお、統計テーブルには、x と y
のフィールドがあるものとします。また、標本の分母は(レコード件数-1)の値でいいでしょうか、それとも単純にレコード件数でしょうか、希望する仕様で相関係数の計算式を修正してみてください。

1 平均クエリ
SELECT avg([x]) AS 平均x, avg([y]) AS 平均y FROM 統計テーブル;

2 共分散因子クエリ
SELECT [x]-[平均x] AS 偏差x, [y]-[平均x] AS 偏差y, [偏差x]*[偏差y] AS 偏差xy FROM 統計テーブル,
平均クエリ;

3 相関回帰クエリ
SELECT Sum([偏差xy])/StDev([x])/StDev([y])/(Count(*)-1) AS 相関係数,
[相関係数]*StDev([x])/StDev([y]) AS 傾きb, Avg([y])-[傾きb]*Avg([x]) AS 切片a FROM
統計テーブル, 共分散因子クエリ;

　以上の3つのクエリをSQLビューででも流し込んで作成し、相関回帰クエリを呼び出せば、相関係数・傾きb・切片aが取り出せるようになります。

--
海星

[海星]

　誠に済みませんが、表計算ソフトで分析値を検証したところ、間違っていました。初歩的な間違いで、傾きの分子分母が逆でした。更に、基本的にSQL集合関数は単独で使わないと、テーブルの結合でレコードがふくらむ現象が起こり、件数や標準偏差が違った値になってしまいました。したがって、各指数を単独に求めるようにした以下の8本のクエリの組み合わせで正しい分析値が導き出されます。

1 標本件数クエリ
SELECT Count([x]) AS 標本件数 FROM 統計テーブル;

2 平均xクエリ
SELECT Avg([x]) AS 平均x FROM 統計テーブル;

3 平均yクエリ
SELECT Avg([y]) AS 平均y FROM 統計テーブル;

4 標準偏差xクエリ
SELECT StDev([x]) AS 標準偏差x FROM 統計テーブル;

5 標準偏差yクエリ
SELECT StDev([y]) AS 標準偏差y FROM 統計テーブル;

6 共分散単子クエリ
SELECT [x]-[平均x] AS 偏差x, [y]-[平均x] AS 偏差y, [偏差x]*[偏差y] AS 共分散単子
FROM 統計テーブル, 平均xクエリ, 平均yクエリ;

7 共分散合計クエリ
SELECT Sum([共分散単子]) AS 共分散 FROM 共分散単子クエリ;

8 相関回帰クエリ
SELECT [共分散]/([標本件数]-1)/[標準偏差x]/[標準偏差y] AS 相関係数,
[相関係数]*[標準偏差y]/[標準偏差x] AS 傾きb, [平均y]-[傾きb]*[平均x] AS 切片a
FROM 標本件数クエリ,平均xクエリ, 平均yクエリ,
標準偏差yクエリ, 標準偏差xクエリ, 共分散合計クエリ;

　以上です。二十数年前、統計ゼミで相関・回帰の公式の変形に取り組んでいた頃を懐かしみつつ研究してみました。

--
海星

[海星]

　再々、済みません。６つめの共分散単子クエリが間違えていました。偏差yのフィールド式が [y]-[平均x] としていますが、 [y]-[平均y]
が正しい式です。

誤

> 6 共分散単子クエリ
> SELECT [x]-[平均x] AS 偏差x, [y]-[平均x] AS 偏差y, [偏差x]*[偏差y] AS 共分散単子
> FROM 統計テーブル, 平均xクエリ, 平均yクエリ;

正
6 共分散単子クエリ
SELECT [x]-[平均x] AS 偏差x, [y]-[平均y] AS 偏差y, [偏差x]*[偏差y] AS 共分散単子
> FROM 統計テーブル, 平均xクエリ, 平均yクエリ;
--
海星