엑셀 최소자승법 함수 - egsel choesojaseungbeob hamsu

�ּ��ڽ¹�

1. �ּ��ڽ¹�� ʿ�� ?

�Ϲ��  �� , �� x (�� Independent Variable)�� ذ��,
�׿� �� 谪 y (��Ӻ�� Dependent Variable)�� (x,y)�� ´�.
�� Nȸ �ݺ��Ͽ� (x

1,y1), (x2,y2), ... (xn,yn)�� ͸� Ȯ��ߴٰ� ��.
�� ͵�� Ģ�� Ѵٸ�, �� ƹ�� ǹ̸� �� Ѵ�.
��, ��͵�� 뼺�� Ǵ��ϱ� ��ؼ� �� ؾ�� ۾��,
�� 谡 �ִ��, �� ִٸ� � ��踦 �� ִ�� ã�ƺ�� ̴�.
��踦 �Լ�� ǥ�� ִٸ�, "�� 迡�� ͸� �м��ߴ�� ̷� ��Ģ�� ִ��."
�� , ��⼭ �ϳ�� ź��ϴ� ��̴�.

�ּ��ڽ¹��̶�, �� 踦 ��Ÿ�� Լ� y=f(x)�� ã�� ϳ�� ִ�.

2. �ּ��ڽ¹� (Method of Least Squares) �̶�?

Nȸ �� y

1,y2,...,yn��  �ٸ� �� x1,x2,...xn �� Լ�� ,
�� yi�� Լ�� f(xi)�� ̸� ��

�� ּҰ� �ǵ�� ϴ� �Լ� f(x)�� ϴ� �� ּ��ڽ¹�� ̴�.
�̷�� ؼ� �� Լ� y=f(x)�� 迡 �� Լ�� ִ�. ��ظ� �� ׸�� 캸��.
�� ׸�� ǥ�õ� �� (xi,yi)�̰�, �� (xi,f(xi))�� ּ��ڽ¹�� , �� Ÿ�� Լ��̴�. ��, �� Լ�� (��-�Լ��)�� (�� )�� ּҰ� �Ǵ� ��̶�� ִ�.

3. �� Ʈ�� Լ� ��ϱ�

�� ͸� ��Ͽ� �� 踦 ��Ÿ�� Լ�� ã�� ,
�Լ�� ã�� ϰ� �ִ� �� ϴ� ��̴�.
�Ϲ�� θ� ��ϰ� �ִ� �� α׷�� Ʈ �� ̿��ϸ� �� Լ�� ִ�.
��Լ� �Ӹ� �ƴ϶�, 2��~5�� ׽İ� �ŵ��, ��, �α��Լ� ��,
�� Ͽ� �� Լ�� ǥ��ش�.
�׷��, �Լ�� Ǵ� �� ¿��, �� ϴ� �� ٶ�� ʴ�.
�ϴ�, ��Ʈ�� ̿�� Լ� �� ϰ� �� ϰ��,
�ּ��ڽ¹�� Ȱ�� �� Ǵ�� ڿ� �ڼ�� Ͽ�� ݵ�� о�� .
�� ϴ� ��ü��, Ư��ð�� ӵ�� Ͽ� �� ̺��̴�.
�� ͸� �� Ʈ�� ۼ��ϰ�, ��Լ�� غ��.
�ϴ�, �� ͸� �� Sheet�� Է�� ش� �� Ѵ�.

�޴�� [��]->[��Ʈ] �Ǵ� ��

�� Ͽ� ��Ʈ��縦 ��Ѵ�.
[ǥ�� ] �ǿ�� л�� Ʈ�� , ��Ʈ �� 񱳸� �� [��ħ]�� Ŭ��Ѵ�.
([��]�� Ŭ��Ͽ� �� , ��ϰ� �� ϴ� �� ϴ�.)

�Ʒ�� Ʈ�� ۼ�� ̴�.
��͸� ǥ�� 콺 ��͸� �� ư�� Ŭ��Ͽ� �߼�� ߰�� Ѵ�.

[��] �ǿ�� ϰ�,
[�ɼ�] �ǿ�� Ʈ�� ǥ�� R-�� Ʈ�� ǥ�ø� üũ�� Ȯ�� .

�� ͸� �� Լ�� .
�׷�� 9.8343�� ߷°��ӵ�� ġ�Ѵ�.
y�� -38.16�� ʱ� �� ӵ�� ʱ� ��̴�.
R�� ǥ�� Լ�� ͸� �󸶳� ��ϰ� ǥ��ϴ°� ��Ÿ�� ̸�,
1 �� ȭ�� Լ�� ǹ��Ѵ�. R�� ޺κп�� ٽ� �� ̴�.

��Ʈ�� Ͽ� �˾ƺ�� Ѵ�. (��ʰ��. ��Ʈ ��)

4. �ּ��ڽ¹�� 

1) �� Լ� y=a+bx ��

�� ּ��ڽ¹�� Ͽ� �� Լ�� غ��.
��⼭�� Լ�� ּ��ڽ¹�� ϰ�� Ѵ�.
�׸��, �� Ȱ��Ͽ�, �� ٷ�� ó�� .

�� ׸�� ǥ�� Լ�� Ÿ�� غ��.
�� Լ��, �� ̰� �� ּ�� ̴�.
�ּ��ڽ¹�� ּ�ȭ�ϱ� �� ̴�.
(�� ״�� ϸ� �� Ǳ� �� Ѵ�.
�� 밪�� , �� ̺а�� ߻�� ִ�.)
�� (Residual)�� ϸ�, �� ǥ�� ִ�.

�� Ͱ� �� , ytrue�� ytrue=a+bx �� ǥ�� ְ�, �� .

��⼭, ytrue=a+bx �� Ϲ�� , a�� b�� ǵ�� Ѵ�.
��Ϳ� �� ã�� ؼ�� ִ�� ٿ�� Ѵ�.
��, a�� b�� ּ�ȭ�ϴ� �� Ǿ�� Ѵ�.
�� (��,square)�� ּ�ȭ(least)�ϴ� ��(method)�̶�� ǹ̿��
�ּ��ڽ¹�(Method of least squares)�� Ī�� ̴�.
�� a,b�� غ�� . (��κ�� ڼ�� ϰ� �� ϵ�� Ѵ�)
�� ּ�ȭ�ϱ� �� a,b�� ϱ� ��ؼ��, a,b �� ̺�� 0 �� Ǹ� �ȴ�. ��,

�� ϴ� a,b�� ϸ� �� .

2) �� x,y,a,b �� ǥ��

�� x �� հ�� ̿�� ǥ��

x�� .

��Ӻ�� y �� ǥ�� y�� x�ʹ� �ټ� �ٸ�� Ѵ�.
��Ӻ�� ϸ�, ��Ӻ�� ǥ�� .

�հ�� 1/(N-1)�� 1/(N-2)�� ̿� �� Ѵ�.
a,b�� ǥ�� Ӻ�� y�� ǥ��κ�� Ǹ�, �� .

��) ��⼭ ��ϴ� ǥ�� Ʈ �ۼ� �� ϴ� ǥ�ؿ�� Ȯ�� ټ� ��̰� �� ϵ�� Ѵ�.

3) r��

�� 踦 �� , �� 踦 ǥ��ϴ� �Լ� �Ӹ�� ƴ϶�, ��

�Լ�� 󸶳� ��踦 �� ǥ��ϰ� �ִ�� Ÿ�� ʿ��ϴ�.
��κ�� ȸ�� α׷�� b �� y-�� a �� ռ��, �ܼ�� ƴ� r �̶�� ϴ� �� Ͽ� ��ġ�� ǹ̷� ǥ�� ش�.
�� r �� 0 �� 1 �� . �� Ͱ� �� Ȯ�� ġ�� r=1 �� ȴ�.
��ġ�� r �� 1 �� ´�.
�� ׷�� ǥ�� Ͽ� �� r=0 �� ȴ�.
�̰�� 谡 �� Ѵ�.
�� r �� ϱ� ��, �� y �� .
�׷�� 󿡼� y �� Ÿ�� y �� ߰� �� ̸� �� .
y �� ϴ� ��, �� ش� ��͸� ǥ�� ִ� �� ־�� ̸�,
�̸� �� ٸ� �� ϱ� ��̴�.

�� հ�� ̸� �� ϸ�, �� ǿ� �� 0 �� ȴ�.
��, �̸� �ذ��ϱ� �� Ͽ� �� װ�� Ѵ�.

...(4-3-1)

�� , �� Լ�� 󸶳� �� ϴ� �� ̴�.
�� Ͱ� ��տ� ��ϴ� �� ǹ��ϰ�,
Ŭ �� Ͱ� �а� ��Ǿ� �� Ѵ�.
��, ��Ϳ� �� Ͽ�, �� x �� ϴ� y

cal ��

...(4-3-2)

�� Ѵ�.

.........(4-3-3)

(4-3-1)�� (4-3-3) �� ܼ�� ̰�,
�� ͵�� ǥ��ϱ⿡ �󸶳� �� ϴ� �� ִ�.
�� ִ�.

.........(4-3-4)

��

�� (�־�� )��ŭ ��ϴٸ�, (4-3-4) �� 0 �� ̴�.

.........(4-3-5)

��

�� ̶�� ϸ�, �� ycal�� yi�� Ͽ� (4-3-4) �� .

.........(4-3-6)

(4-3-4)�� (4-3-1)�� ٷ� �� r��̴�. (�� Normalize �Ѵٰ� �Ѵ�.)

..........(4-3-7)

�� 翬�� 0 �� 1 �� ´�.
0 �� , �� ǥ�� Ѵٴ� �� ϸ�,
1 �� Ͱ� �� (��͸� ��ǥ�ϴ� �� )�� Ѵ�.
�ð�� ظ� ��ؼ� �Ʒ�� ׸�� ϸ�,
(A)�� r=1 �̸�, (B)�� r �� 1�� , (C)�� r �� 0 �� , (D)�� r=0 �̴�.

5. �� ̿�� ּ��ڽ¹� (Method of Least Squares) Ȱ��

�� ؼ� �ּ��ڽ¹�� ߿�� , �� Sheet �̴�.
�Ʒ�� ׸��, ��ϴ� ��ü�� Ư��ð�� ӵ�� Ͽ� �� ̺�� ̿��Ͽ�,
�ּ��ڽ¹�� ؼ� �߷°��ӵ�� ϴ� ��ũ��Ʈ�̴�.
�� ͸� �� Է��ϴ� �κ��̸�, �� ԷµǾ� �ִ�.
�� ȯ�Ͽ� ��Ͽ�� ǥ�� ϵ�� Ѵ�.
�� ͸� �Է��ϸ�, �ڵ�� a (y-��)��, b (��)�� ǰ�,
�� r�� ǥ�õ� ��̴�.
��Ʈ�� ̿��Ͽ� ��Լ�� Ͱ� �� ִ�.

1) �� ũ��Ʈ �Է� ��

2) ��  - ��, �Լ�, ��, �ڵ�ä�� ʰ��¸� ��Ѵ�.