説明変量xと目的変量yとの関係を示す1次式 y=ax+b
を回帰直線(regression
line),
aを回帰係数(regression coefficient)と言う.
サンプルデータの残差の2乗和Q(a, b)を最小とするようy=ax+bを求める(=最小2乗法)
N組のデータ (x1, y1),(x2,
y2),・・・・,(xn,
yn)
回帰直線 y=ax+b
残差の2乗和 Q(a,b)=(a*x1+b-y1)2+(a*x2+b-y2)2+.....+(a*xn+b-yn)2
極値の問題として偏微分すれば,a, bは以下のようになる.
SCREEN 12
CLS
'Data Input
num = 0
DO
PRINT "Data No."; num + 1; " (End:Enter)"
INPUT "X= "; tmpX
IF tmpX = "" THEN EXIT DO
INPUT "Y= "; tmpY
num = num + 1
X(num) = VAL(tmpX)
Y(num) = VAL(tmpY)
IF num = 1 THEN
xmax = X(num): xmin = X(num)
ymax = Y(num): ymin = Y(num)
ELSE
IF X(num) > xmax THEN xmax
= X(num)
IF Y(num) > ymax THEN ymax
= Y(num)
IF X(num) < xmin THEN
xmin = X(num)
IF Y(num) < ymin THEN
ymin = Y(num)
END IF
LOOP
IF num < 2 THEN
PRINT "Number of data must be more than 2!"
END
END IF
'Draw correlation diagram
xmargin = (xmax - xmin) / 10
ymargin = (ymax - ymin) / 10
CLS
VIEW (210, 20)-(610, 420), 7, 12
WINDOW (xmin - xmargin, ymax + ymargin)-(xmax + xmargin, ymin - ymargin)
LINE (xmin, ymin)-(xmin, ymin - ymargin)
LINE (xmin, ymin)-(xmin - xmargin, ymin)
LINE (xmin - xmargin, ymax)-(xmax, ymax)
LINE (xmax, ymin - ymargin)-(xmax, ymax)
LOCATE 1, 26: PRINT "Y"
LOCATE 4, 19: PRINT USING "#####.#"; ymax
LOCATE 25, 19: PRINT USING "#####.#"; ymin
LOCATE 28, 28: PRINT USING "#####.#"; xmin
LOCATE 28, 69: PRINT USING "#####.#"; xmax
LOCATE 28, 78: PRINT "X"
'Draw points
FOR n = 1 TO num
CIRCLE (X(n), Y(n)), .01 * (xmax - xmin), 0
NEXT
'average
sumX = 0: sumY = 0
FOR n = 1 TO num
sumX = sumX + X(n)
sumY = sumY + Y(n)
NEXT
avx = sumX / num
avy = sumY / num
LOCATE 1, 1
PRINT "N="; num
PRINT "Heikin "
PRINT " X:"; avx
PRINT " Y:"; avy
'Hyoujun Hensa (standard deviation)
sumX = 0: sumY = 0
FOR n = 1 TO num
sumX = (X(n) - avx) ^ 2 + sumX
sumY = (Y(n) - avy) ^ 2 + sumY
NEXT
sdx = SQR(sumX / num)
sdy = SQR(sumY / num)
PRINT "Hyoujun Hensa"
PRINT " X:"; sdx
PRINT " Y:"; sdy
'Soukan Keisuu(correlation coefficient)
sum = 0
FOR n = 1 TO num
sum = (X(n) - avx) * (Y(n) - avy) + sum
NEXT
r = sum / num / sdx / sdy
PRINT "Soukan Keisuu "
PRINT " r = "; r
'以下の囲みの部分を前回のプログラムに追加する.
'Kaiki Chokusen
DIM sigmaX AS SINGLE, sigmaY AS SINGLE, sigmaX2 AS SINGLE, sigmaXY AS SINGLE DIM a AS SINGLE, b AS SINGLE sigmaX = 0 sigmaY = 0 sigmaX2 = 0 sigmaXY = 0 FOR n = 1 TO num
a = (num * sigmaXY - sigmaX * sigmaY) / (num * sigmaX2 - sigmaX ^ 2)
LINE (xmin, a * xmin + b)-(xmax, a * xmax + b), 1
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
延べ床面積とは,各居住室の床面積のほか,その住宅に含まれる玄関,台所,便所,浴室,
押し入れなども含めた床面積の合計をいいます.
最大値 MAX(セル範囲)
最小値 MIN(セル範囲)
平均 AVERAGE(セル範囲)
中央値 MEDIAN(セル範囲)
分散 VAR(セル範囲)
標準偏差 STDEV(セル範囲)
歪度 SKEW(セル範囲)
尖度 KURT(セル範囲)
ツール>>アドインで分析ツールにチェックする.
ツール>>分析ツールを選択し,基本統計量を選択する.
入力元,出力オプションを設定すれば,自動的に基本統計量が出力される.
結果は配列として出力されるので,2つのセルを選んだ状態で関数を入力し,Ctrl+Shift+Enter