최소자승법(LSM)은 회귀 분석 분야에 속합니다. 다른 단순한 기능으로 주어진 기능을 대략적으로 표현할 수 있으므로 많은 응용 프로그램이 있습니다. LSM은 관찰을 처리하는 데 매우 유용할 수 있으며 무작위 오류가 포함된 다른 측정 결과에서 일부 수량을 추정하는 데 적극적으로 사용됩니다. 이 기사에서는 Excel에서 최소 제곱 계산을 구현하는 방법을 배웁니다.
특정 예에 대한 문제 설명
두 개의 지표 X와 Y가 있다고 가정합니다. 또한 Y는 X에 의존합니다. OLS는 회귀 분석의 관점에서 우리에게 관심이 있기 때문에 (Excel에서는 그 방법이 내장 함수를 사용하여 구현됨) 즉시 진행해야합니다 특정 문제를 고려합니다.
따라서 X는 평방 미터로 측정된 식료품점의 판매 영역이고 Y는 수백만 루블로 정의된 연간 매출액입니다.
매장에 하나 이상의 소매 공간이 있는 경우 매장의 회전율(Y)을 예측해야 합니다. 분명히 Y = f (X) 함수는 증가하고 있습니다. 대형 슈퍼마켓이 마구간보다 더 많은 상품을 판매하기 때문입니다.
예측에 사용된 초기 데이터의 정확성에 대한 몇 마디
n개의 매장에 대한 데이터로 구성된 테이블이 있다고 가정해 보겠습니다.
수학적 통계에 따르면 적어도 5-6개의 개체에 대한 데이터를 조사하면 결과가 다소 정확할 것입니다. 또한 "비정상" 결과는 사용할 수 없습니다. 특히 엘리트 소규모 부티크는 "masmarket"급 대형 매장의 매출보다 몇 배나 더 큰 매출을 올릴 수 있습니다.
방법의 본질
테이블 데이터는 점 M 1 (x 1, y 1), ... M n (x n, y n)으로 데카르트 평면에 표시될 수 있습니다. 이제 문제의 해결책은 점에 가능한 한 가깝게 지나가는 그래프를 갖는 근사 함수 y = f (x)의 선택으로 축소됩니다. M 1, M 2, .. M n .
물론 다항식을 사용할 수 있습니다. 높은 온도, 그러나이 옵션은 구현하기 어려울뿐만 아니라 감지해야 할 주요 추세를 반영하지 않기 때문에 단순히 올바르지 않습니다. 가장 합리적인 해결책은 실험 데이터에 가장 근접한 직선 y = ax + b, 보다 정확하게는 계수 - a 및 b를 찾는 것입니다.
정확도 점수
근사치의 경우 정확도 평가가 특히 중요합니다. e 나는 점에 대한 기능적 값과 실험적 값 사이의 차이(편차)를 나타냅니다 x i , 즉 e i = y i - f (xi).
분명히 근사치의 정확성을 평가하기 위해 편차의 합을 사용할 수 있습니다. 고려 중인 모든 지점에서의 합 e i. 그러나 긍정적 인 편차와 함께 실제로 부정적인 편차가 있기 때문에 모든 것이 그렇게 단순하지는 않습니다.
편차 모듈 또는 해당 제곱을 사용하여 문제를 해결할 수 있습니다. 후자의 방법이 가장 널리 사용됩니다. 회귀 분석 (Excel에서는 두 가지 기본 제공 함수를 사용하여 구현)을 비롯한 많은 영역에서 사용되며 오랫동안 효과가 입증되었습니다.
최소제곱법
아시다시피 Excel에는 선택한 범위에 있는 모든 값의 값을 계산할 수 있는 자동 합계 함수가 내장되어 있습니다. 따라서 표현의 값을 계산하는 데 방해가 되는 것은 없습니다(e 1 2 + e 2 2 + e 3 2 + ... e n 2).
수학적 표기법으로 보면 다음과 같습니다.
처음에 직선을 사용하여 근사하기로 결정했으므로 다음과 같습니다. 
따라서 X와 Y 사이의 특정 관계를 가장 잘 설명하는 직선을 찾는 작업은 두 변수의 함수 중 최소값을 계산하는 것과 같습니다.
이를 위해서는 새로운 변수 a 및 b와 관련하여 0 편도함수와 동일시하고 다음 형식의 2개의 미지수가 있는 2개의 방정식으로 구성된 기본 시스템을 푸는 것이 필요합니다.
2로 나누고 합계를 조작하는 것을 포함하여 간단한 변환 후 다음을 얻습니다.
예를 들어 Cramer의 방법으로 해결하면 특정 계수 a * 및 b * . 이것은 최소값입니다. 즉, 특정 영역에서 매장의 회전율을 예측하려면 문제의 예에 대한 회귀 모델인 직선 y = a * x + b *가 적합합니다. 물론 정확한 결과를 찾을 수는 없지만 특정 지역에 대해 신용으로 상점을 구매하면 갚을 수 있는지에 대한 아이디어를 얻는 데 도움이 될 것입니다.
Excel에서 최소 제곱 방법을 구현하는 방법
Excel에는 최소 제곱의 값을 계산하는 기능이 있습니다. 형식은 TREND(알려진 Y 값, 알려진 X 값, 새 X 값, 상수)입니다. Excel에서 OLS를 계산하는 공식을 테이블에 적용해 보겠습니다.
이를 위해 Excel에서 최소 제곱법을 사용한 계산 결과가 표시되어야 하는 셀에 "=" 기호를 입력하고 "TREND" 기능을 선택합니다. 열리는 창에서 해당 필드를 채우고 다음을 강조 표시합니다.
- Y에 대한 알려진 값의 범위(이 경우 회전율 데이터)
- 범위 x 1 , … x n , 즉 소매 공간의 크기;
- 회전율의 크기를 찾아야하는 x의 알려진 값과 알려지지 않은 값 (워크 시트에서의 위치에 대한 정보는 아래 참조).
또한 수식에는 논리 변수 "Const"가 있습니다. 해당 필드에 1을 입력하면 b \u003d 0으로 가정하여 계산을 수행해야 함을 의미합니다.
둘 이상의 x 값에 대한 예측을 알아야 하는 경우 수식을 입력한 후 "Enter"를 누르지 말고 "Shift" + "Control" + "Enter" 조합을 입력해야 합니다("Enter" ) 키보드에서.
일부 기능
회귀 분석은 인형도 접근할 수 있습니다. 알 수 없는 변수 배열의 값을 예측하는 Excel 공식인 "TREND"는 최소 제곱 방법에 대해 들어본 적이 없는 사람도 사용할 수 있습니다. 작업의 일부 기능을 아는 것만으로도 충분합니다. 특히:
- 변수 y의 알려진 값 범위를 하나의 행 또는 열에 배치하면 알려진 x 값을 가진 각 행(열)이 프로그램에서 별도의 변수로 인식됩니다.
- TREND 창에 알려진 x가 있는 범위가 지정되지 않은 경우 Excel에서 함수를 사용하는 경우 프로그램은 이를 정수로 구성된 배열로 간주하며 그 수는 주어진 값의 범위에 해당합니다. 변수 y의
- "예측" 값의 배열을 출력하려면 경향 표현식을 배열 수식으로 입력해야 합니다.
- 새로운 x 값이 지정되지 않으면 TREND 함수는 이를 알려진 값과 동일하다고 간주합니다. 지정하지 않으면 배열 1이 인수로 사용됩니다. 2; 삼; 4;…, 이미 주어진 매개변수 y가 있는 범위에 상응합니다.
- 새로운 x 값을 포함하는 범위는 주어진 y 값을 가진 범위와 같거나 더 많은 행 또는 열을 가져야 합니다. 즉, 독립 변수에 비례해야 합니다.
- 알려진 x 값을 가진 배열은 여러 변수를 포함할 수 있습니다. 그러나 하나만 이야기하는 경우 주어진 x 및 y 값의 범위가 적절해야 합니다. 변수가 여러 개인 경우에는 주어진 y 값의 범위가 하나의 열 또는 하나의 행에 맞아야 합니다.
예측 기능
Excel의 회귀 분석은 여러 기능을 사용하여 구현됩니다. 그 중 하나는 "예측"입니다. TREND와 유사합니다. 즉, 최소 제곱법을 사용하여 계산한 결과를 제공합니다. 그러나 Y의 값을 알 수 없는 하나의 X에 대해서만 가능합니다.
이제 선형 추세에 따라 지표의 미래 가치를 예측할 수 있는 인형용 Excel 공식을 알게 되었습니다.
최소 제곱법은 일련의 두 숫자 집합과 가장 근접하게 일치하는 선형 방정식을 구성하기 위한 수학적 절차입니다. 이 방법의 목적은 총 제곱 오차를 최소화하는 것입니다. Excel에는 이 방법을 계산에 적용하는 데 사용할 수 있는 도구가 있습니다. 어떻게 되는지 봅시다.
Excel에서 방법 사용
o 해 찾기 추가 기능 활성화
o 작업 조건
o 결정
Excel에서 방법 사용
최소 제곱법(LSM)은 한 변수가 다른 변수에 의존하는 것을 수학적으로 설명하는 것입니다. 예측에 사용할 수 있습니다.
해 찾기 추가 기능 활성화
Excel에서 OLS를 사용하려면 추가 기능을 활성화해야 합니다. "해결책 찾기", 기본적으로 비활성화되어 있습니다.
1. 탭으로 이동 "파일".
2. 섹션 이름을 클릭합니다. "옵션".
3. 열리는 창에서 하위 섹션의 선택을 중지합니다. "추가 기능".
4. 블록에서 "제어", 창 하단에 있는 스위치를 위치로 설정합니다. "Excel 추가 기능"(값이 다른 경우) 버튼을 클릭합니다. "가다...".
5. 작은 창이 열립니다. 옵션 옆에 체크 표시를 하십시오 "해결책 찾기". 버튼을 클릭 좋아요.
이제 기능 솔루션 찾기가 활성화되고 해당 도구가 리본에 나타납니다.
수업: Excel에서 솔루션 찾기
문제의 조건
특정 예에서 LSM의 적용을 설명하겠습니다. 두 줄의 숫자가 있습니다. 엑스그리고 와이, 순서는 아래 이미지에 표시됩니다.
이 종속성은 다음 함수로 가장 정확하게 설명할 수 있습니다.
동시에, x=0y또한 동등하다 0 . 따라서 이 방정식은 종속성으로 설명할 수 있습니다. y=nx.
차이의 최소 제곱합을 찾아야 합니다.
해결책
방법의 직접 적용에 대한 설명으로 넘어 갑시다.
1. 첫 번째 값의 왼쪽으로 엑스숫자를 넣어 1 . 이것은 계수의 첫 번째 값의 대략적인 값입니다. N.
2. 열 오른쪽으로 와이다른 열 추가 nx. 이 열의 첫 번째 셀에 계수를 곱하는 공식을 작성합니다. N첫 번째 변수의 셀에 엑스. 동시에 이 값은 변경되지 않으므로 절대 계수를 사용하여 필드에 대한 링크를 만듭니다. 우리는 버튼을 클릭 입력하다.
3. 채우기 핸들을 사용하여 이 수식을 아래 열의 표 전체 범위에 복사합니다.
4. 별도의 셀에서 값의 제곱 차이의 합을 계산합니다. 와이그리고 nx. 이렇게 하려면 버튼을 클릭하십시오. "함수 삽입".
5. 열린 상태에서 "기능 마법사"항목을 찾고 "SUMMKVRAZN". 그것을 선택하고 버튼을 클릭하십시오 좋아요.
6. 인수 창이 열립니다. 현장에서 "Array_x" 와이. 현장에서 "배열_y"열 셀 범위 입력 nx. 값을 입력하려면 필드에 커서를 놓고 시트에서 적절한 범위를 선택하기만 하면 됩니다. 입력 후 버튼 클릭 좋아요.
7. 탭으로 이동 "데이터". 도구 상자의 리본에서 "분석"버튼을 클릭 "해결책 찾기".
8. 도구의 매개변수 창이 열립니다. 현장에서 "목적 함수 최적화"수식으로 셀 주소 지정 "SUMMKVRAZN". 매개변수에서 "전에"스위치를 위치로 설정하십시오. "최저한의". 현장에서 "변화하는 세포"계수 값으로 주소 지정 N. 버튼을 클릭 "해결책을 찾기".
9. 솔루션이 계수 셀에 표시됩니다. N. 함수의 최소 제곱이 되는 것은 이 값입니다. 결과가 사용자를 만족시키면 버튼을 클릭합니다. 좋아요추가 창에서.
보시다시피 최소 제곱법의 적용은 다소 복잡한 수학적 절차입니다. 우리는 가장 간단한 예를 통해 실제로 그것을 보여주었지만 훨씬 더 복잡한 경우가 있습니다. 그러나 Microsoft Excel 도구 키트는 가능한 한 계산을 단순화하도록 설계되었습니다.
http://multitest.semico.ru/mnk.htm
일반 조항
절대값의 숫자가 작을수록 직선(2)이 더 잘 선택됩니다. 직선 선택 정확도의 특성(2)으로 제곱합을 구할 수 있습니다.
S의 최소 조건은
 | (6) |
 | (7) |
방정식 (6) 및 (7)은 다음 형식으로 작성할 수 있습니다.
 | (8) |
 | (9) |
방정식 (8)과 (9)에서 실험값 x i 및 y i로부터 a와 b를 쉽게 찾을 수 있습니다. 방정식 (8) 및 (9)에 의해 정의된 직선 (2)는 최소 제곱법에 의해 얻어진 직선이라고 합니다(이 이름은 제곱합 S가 최소임을 강조합니다). 직선 (2)가 결정되는 식 (8) 및 (9)를 정규 방정식이라고 합니다.
정규 방정식을 컴파일하는 간단하고 일반적인 방법을 나타낼 수 있습니다. 실험 포인트 (1)과 방정식 (2)를 사용하여 a와 b에 대한 방정식 시스템을 작성할 수 있습니다.
| y 1 \u003d 도끼 1 +b, | ||
| y2=ax2+b, ... | (10) | |
| yn=axn+b, |
각 방정식의 왼쪽과 오른쪽 부분에 첫 번째 미지 a의 계수(즉, x 1 , x 2 , ..., x n)를 곱하고 결과 방정식을 더하면 첫 번째 정규 방정식(8)이 됩니다.
각 방정식의 왼쪽과 오른쪽에 두 번째 미지수 b의 계수를 곱합니다. 1만큼, 결과 방정식을 추가하여 두 번째 정규 방정식 (9)을 생성합니다.
정규 방정식을 구하는 이 방법은 일반적입니다. 예를 들어 다음 함수에 적합합니다.
상수 값이며 실험 데이터(1)에서 결정해야 합니다.
k에 대한 연립방정식은 다음과 같이 쓸 수 있습니다.
최소 제곱법을 사용하여 선(2)을 찾습니다.
해결책.우리는 찾는다:
X i =21, y i =46.3, x i 2 =91, x i y i =179.1.
방정식 (8)과 (9)91a+21b=179.1을 씁니다.
21a+6b=46.3, 여기에서 우리는
a=0.98 b=4.3.
특징 간의 확률적 관계를 연구하는 방법 중 하나는 회귀 분석입니다.
회귀 분석은 다른(또는 다른) 변수(feature-factors)의 값이 알려진 경우 임의 변수(feature-result)의 평균값을 찾는 데 사용되는 회귀 방정식의 파생입니다. 여기에는 다음 단계가 포함됩니다.
- 연결 형태 선택(분석 회귀 방정식의 유형);
- 방정식 매개변수 추정;
- 분석 회귀 방정식의 품질 평가.
선형 쌍 관계의 경우 회귀 방정식은 y i =a+b·xi +u i 형식을 취합니다. 이 방정식 a 및 b의 매개변수는 통계적 관찰 데이터 x 및 y에서 추정됩니다. 이러한 평가의 결과는 다음 방정식입니다. 여기서 , - 매개변수 a 및 b의 추정치 , - 회귀 방정식(계산된 값)에 의해 얻은 유효 기능(변수)의 값.
매개변수 추정에 가장 일반적으로 사용되는 것은 다음과 같습니다. 최소 제곱법(LSM).
최소 제곱 방법은 회귀 방정식의 매개변수에 대한 최상의(일관되고 효율적이며 편향되지 않은) 추정치를 제공합니다. 그러나 임의 항(u)과 독립 변수(x)에 대한 특정 가정이 충족되는 경우에만 가능합니다(OLS 가정 참조).
최소자승법에 의한 선형쌍방정식의 매개변수 추정 문제다음과 같이 구성됩니다. 매개 변수의 추정치를 얻기 위해 , 유효 기능의 실제 값의 제곱 편차의 합 - y 계산 된 값에서 - 최소입니다.
공식적으로 OLS 기준다음과 같이 작성할 수 있습니다. 
.
최소 제곱 방법의 분류
- 최소제곱법.
- 최대 우도 방법(일반 고전 선형 회귀 모델의 경우 회귀 잔차의 정규성이 가정됨).
- GLSM의 일반화된 최소자승법은 오류 자기상관의 경우와 이분산성의 경우에 사용됩니다.
- 가중 최소 제곱법(이분산 잔차가 있는 GLSM의 특수한 경우).
본질을 설명하다 최소 제곱의 고전적인 방법을 그래픽으로. 이를 위해 직각좌표계에서 관측 데이터(xi , y i , i=1;n)에 따라 도트 플롯을 작성합니다(이러한 도트 플롯을 상관 필드라고 함). 상관 필드의 점에 가장 가까운 직선을 찾아봅시다. 최소 제곱 방법에 따르면 상관 필드의 점과 이 선 사이의 제곱 수직 거리의 합이 최소가 되도록 선이 선택됩니다. 
이 문제의 수학적 표기법: 
.
y i 및 x i =1...n의 값은 우리에게 알려져 있으며 이는 관찰 데이터입니다. 함수 S에서 그것들은 상수입니다. 이 함수의 변수는 매개변수 - , 의 필수 추정치입니다. 변수가 2개인 함수의 최소값을 찾으려면 각 매개변수에 대해 이 함수의 편도함수를 계산하고 이를 0과 동일시해야 합니다. 
.
결과적으로 우리는 2개의 정규 선형 방정식 시스템을 얻습니다. 
이 시스템을 풀면 필요한 매개변수 추정치를 찾습니다. 
회귀 방정식의 매개 변수 계산의 정확성은 합계를 비교하여 확인할 수 있습니다(계산 반올림으로 인해 일부 불일치가 가능함).
모수 추정치를 계산하기 위해 표 1을 작성할 수 있습니다.
회귀 계수 b의 부호는 관계의 방향을 나타냅니다(b > 0이면 관계가 직접적이며 b이면<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
공식적으로 매개변수 a의 값은 x가 0인 경우 y의 평균값입니다. 부호 인자가 0 값을 갖지 않거나 가질 수 없는 경우 매개변수 a에 대한 위의 해석은 의미가 없습니다.
기능 간 관계의 견고성 평가
선형 쌍 상관 계수 - r x,y 를 사용하여 수행됩니다. 다음 공식을 사용하여 계산할 수 있습니다. 
. 또한 선형 쌍 상관 계수는 회귀 계수 b로 결정할 수 있습니다. 
.
쌍 상관 선형 계수의 허용 가능한 값 범위는 -1에서 +1까지입니다. 상관 계수의 부호는 관계의 방향을 나타냅니다. r x, y >0이면 연결이 직접적입니다. 만약 r x, y<0, то связь обратная.
이 계수가 모듈러스에서 1에 가깝다면 피처 간의 관계는 상당히 가까운 선형 관계로 해석될 수 있습니다. 해당 모듈러스가 1 ê r x , y ê =1과 같으면 기능 간의 관계는 기능적 선형입니다. 특징 x와 y가 선형적으로 독립이면 r x,y는 0에 가깝습니다.
표 1을 사용하여 r x,y를 계산할 수도 있습니다.
얻은 회귀 방정식의 품질을 평가하기 위해 이론적 결정 계수가 계산됩니다 - R 2 yx: 
,
여기서 d 2는 회귀 방정식으로 설명되는 분산 y입니다.
e 2 - 잔차(회귀 방정식으로 설명되지 않음) 분산 y ;
s 2 y - 전체(전체) 분산 y .
결정 계수는 총 변동(분산) y에서 회귀(및 결과적으로 계수 x)로 설명되는 결과 특성 y의 변동(분산) 비율을 특성화합니다. 결정 계수 R 2 yx는 0에서 1까지의 값을 취합니다. 따라서 값 1-R 2 yx는 모델 및 사양 오류에서 고려되지 않은 다른 요인의 영향으로 인한 분산 y의 비율을 나타냅니다.
쌍을 이룬 선형 회귀 R 2 yx =r 2 yx .
최소제곱법(LSM)
n개의 미지수가 있는 m개의 선형 방정식 시스템은 다음과 같은 형식을 갖습니다.
세 가지 경우가 가능합니다. m m>n이고 시스템이 일관성이 있는 경우 행렬 A에는 최소한 m - n개의 선형 종속 행이 있습니다. 여기서 솔루션은 n개의 선형 독립 방정식(존재하는 경우)을 선택하고 공식 X=A -1 CV를 적용하여 얻을 수 있습니다. 즉, 이전에 해결된 문제로 문제를 줄입니다. 이 경우 결과 솔루션은 항상 나머지 m - n 방정식을 충족합니다. 그러나 컴퓨터를 사용할 때는 보다 일반적인 접근 방식인 최소 제곱법을 사용하는 것이 더 편리합니다. 최소 제곱의 대수 방법은 선형 방정식 시스템을 푸는 방법으로 이해됩니다. 유클리드 규범을 최소화하여 도끼? 비? > . (1.2) 몇 가지 실험을 고려해 봅시다. 예를 들어, 온도 Q(t)가 측정됩니다. 측정 결과를 배열로 제공 실험 조건이 알려진 오류로 측정이 수행되는 것과 같다고 가정합니다. 이 경우 온도 변화 법칙 Q(t)는 다항식을 사용하여 구합니다. P(t) = + + + ... +, 등식으로 정의된 값 E(, ...,)를 고려하여 알 수 없는 계수 ...를 결정합니다. 가우스 대수 엑셀 근사 최소값을 취했습니다. 제곱합이 최소화되므로 이 방법을 데이터에 맞는 최소 제곱이라고 합니다. P(t)를 식으로 바꾸면 값이 최소가 되도록 배열을 정의하는 작업을 설정해 보겠습니다. 최소 제곱법을 사용하여 배열을 정의합니다. 이를 위해 편도함수를 0과 동일시합니다. m × n 행렬을 입력하면 A = (), i = 1, 2..., m; j = 1, 2, ..., n, 여기서 I = 1, 2..., m; j = 1, 2, ..., n, 서면 평등은 다음과 같은 형식을 취합니다. 행렬을 사용한 연산 측면에서 서면 평등을 다시 작성해 봅시다. 정의에 따라 행렬에 열을 곱한 값이 있습니다. 전치 행렬의 경우 비슷한 관계가 다음과 같습니다. 다음 표기법을 소개합니다. 벡터 Ax의 i 번째 구성 요소를 표시합니다. 작성된 행렬 등식에 따라 다음을 갖게 됩니다. 행렬 형식에서 이 동등성은 다음과 같이 다시 쓸 수 있습니다. A T x= A T B (1.3) 여기서 A는 직사각형 m×n 행렬입니다. 또한 데이터 근사 문제에서는 원칙적으로 m > n입니다. 식(1.3)을 정규방정식이라고 합니다. 처음부터 벡터의 유클리드 표준을 사용하여 동등한 행렬 형식으로 문제를 작성하는 것이 가능했습니다. 우리의 목표는 이 함수를 x에서 최소화하는 것입니다. 솔루션 지점에서 최소값에 도달하려면 이 지점에서 x에 대한 1차 도함수는 0과 같아야 합니다. 이 함수의 도함수는 다음과 같습니다. 2A T B + 2A T 액스 따라서 해는 다음 선형 방정식 시스템을 충족해야 합니다. (AT A)x = (AT B). 이러한 방정식을 정규 방정식이라고 합니다. A가 m × n 행렬이면 A>A - n × n은 행렬입니다. 정규 방정식 행렬은 항상 정사각형 대칭 행렬입니다. 또한 (A>Ax, x) = (Ax, Ax) ? 0. 논평. 때때로 (1.3) 형식의 방정식에 대한 해는 시스템 Ax = B에 대한 해라고 합니다. 여기서 A는 최소 제곱법에 의한 직사각형 m × n(m > n) 행렬입니다. 최소 제곱 문제는 데이터 포인트에서 모델 곡선까지의 수직 거리를 최소화하는 것으로 그래픽으로 해석할 수 있습니다(그림 1.1 참조). 이 아이디어는 모든 근사 오류가 관측 오류에 해당한다는 가정에 기반합니다. 설명 변수에도 오류가 있는 경우 데이터에서 모델까지의 유클리드 거리를 최소화하는 것이 더 적절할 수 있습니다. 아래 Excel에서 OLS를 구현하는 알고리즘은 모든 초기 데이터가 이미 알려져 있다고 가정합니다. 왼쪽부터 시스템의 행렬 방정식 AЧX=B의 두 부분에 시스템 АТ의 전치 행렬을 곱합니다. A T AX \u003d A T B 그런 다음 왼쪽 방정식의 두 부분에 행렬 (A T A) -1을 곱합니다. 이 행렬이 존재하면 시스템이 정의됩니다. 사실을 고려하여 (A T A) -1 * (A T A) \u003d E, 우리는 X \u003d (AT A) -1 A T B. 결과 행렬 방정식은 m>n에 대해 n개의 미지수를 갖는 m개의 선형 방정식 시스템에 대한 솔루션입니다. 위의 알고리즘을 특정 예제에 적용하는 것을 고려하십시오. 예. 시스템을 해결하는 데 필요하게하십시오 Excel에서 이 문제에 대한 수식 표시 모드의 솔루션 시트는 다음과 같습니다. 계산 결과: 원하는 벡터 X는 E11:E12 범위에 있습니다. 주어진 선형 방정식 시스템을 풀 때 다음 함수가 사용되었습니다. 1. MINUTE - 배열에 저장된 행렬의 역행렬을 반환합니다. 구문: NBR(배열). 배열은 행과 열의 수가 동일한 숫자 배열입니다. 2. MULTIP - 행렬의 곱을 반환합니다(행렬은 배열에 저장됨). 결과는 array1과 같은 수의 행과 array2와 같은 수의 열이 있는 배열입니다. 구문: MULT(배열1, 배열2). Array1, array2 -- 곱한 배열. 배열 범위의 왼쪽 상단 셀에 함수를 입력한 후 수식이 포함된 셀부터 배열을 선택하고 F2 키를 누른 다음 CTRL+SHIFT+ENTER 키를 누릅니다. 3. TRANSPOSE - 셀의 세로 세트를 가로 셀로 또는 그 반대로 변환합니다. 이 함수를 사용한 결과는 행 수가 원래 배열의 열 수와 같고 열 수가 초기 배열의 행 수와 같은 배열입니다. 4.1. 내장 함수 사용 계산 회귀계수기능을 사용하여 수행 라인스트(값_y; 값_x; 콘스트; 통계), 값_y- y 값의 배열, 값_x- 선택적 값 배열 엑스배열인 경우 엑스생략하면 다음과 같은 크기의 배열(1;2;3;...)로 가정합니다. 값_y, 콘스트- 상수가 필요한지 여부를 나타내는 부울 값 비 0과 같았습니다. 만약 콘스트의미가 있다 진실또는 생략된 다음 비일반적인 방식으로 계산됩니다. 만약 인수 콘스트거짓이면 비 0으로 가정하고 값 ㅏ관계가 되도록 선택 y=도끼. 통계- 추가 회귀 통계를 반환해야 하는지 여부를 나타내는 부울 값입니다. 만약 인수 통계의미가 있다 진실, 다음 함수 라인스트추가 회귀 통계를 반환합니다. 만약 인수 통계의미가 있다 거짓말하다또는 생략된 경우 함수 라인스트계수만 반환 ㅏ그리고 영구적 비. 함수의 결과라는 점을 기억해야 합니다. 라인스트()값 세트 - 배열입니다. 계산을 위해 상관 계수기능이 사용됩니다 코렐(어레이1;어레이2), 상관 계수의 값을 반환합니다. 여기서 어레이1- 값의 배열 와이, 어레이2- 값의 배열 엑스. 어레이1그리고 어레이2크기가 같아야 합니다. 실시예 1. 탐닉 와이(엑스) 표에 나와 있습니다. 짓다 회귀선그리고 계산 상관 계수. MS Excel 시트에 값 테이블을 입력하고 산점도를 작성해 봅시다. 워크시트는 그림과 같은 형식을 취합니다. 2. 회귀 계수의 값을 계산하기 위해 ㅏ그리고 비셀 선택 A7:B7,기능 마법사와 범주로 전환해 보겠습니다. 통계기능을 선택 라인스트. 그림과 같이 나타나는 대화 상자를 채우십시오. 3을 누르고 좋아요. 결과적으로 계산된 값은 셀에만 나타납니다. A6(그림 4). 값이 셀에 표시되도록 하려면 B6편집 모드로 들어가야 합니다(키 F2)그런 다음 키 조합을 누릅니다. CTRL+SHIFT+ENTER. 셀당 상관 계수 값을 계산하려면 C6다음 공식이 도입되었습니다. C7=코렐(B3:J3;B2:J2). 회귀 계수 알기 ㅏ그리고 비함수의 값을 계산 와이=도끼+비주어진 엑스. 이를 위해 공식을 소개합니다. B5=$A$7*B2+$B$7 범위에 복사 С5:J5(그림 5). 다이어그램에 회귀선을 그려 봅시다. 차트에서 실험 포인트를 선택하고 마우스 오른쪽 버튼을 클릭한 다음 명령을 선택합니다. 초기 데이터. 표시되는 대화 상자(그림 5)에서 탭을 선택합니다. 열버튼을 클릭하십시오 추가하다. 그림과 같이 입력 필드를 채우십시오. 6 버튼을 누른다 좋아요. 회귀선이 실험 데이터 플롯에 추가됩니다. 기본적으로 그래프는 평활선으로 연결되지 않은 점으로 표시됩니다. 회귀선의 모양을 변경하려면 다음 단계를 수행하십시오. 선 그래프를 나타내는 점을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택합니다. 차트 유형그림과 같이 산점도 유형을 설정합니다. 7. 선 종류, 색상, 굵기는 다음과 같이 변경할 수 있습니다. 다이어그램에서 선을 선택하고 마우스 오른쪽 버튼을 누른 다음 상황에 맞는 메뉴에서 명령을 선택합니다. 데이터 계열 형식…다음으로, 예를 들어 그림과 같이 설정합니다. 8. 모든 변환의 결과로 하나의 그래픽 영역에서 실험 데이터의 그래프와 회귀선을 얻습니다(그림 9). 4.2. 추세선을 사용합니다. MS Excel의 다양한 근사 종속성 구성은 차트 속성으로 구현됩니다. 추세선. 실시예 2. 실험 결과 일부 표 종속성이 결정되었습니다. 근사 종속성을 선택하고 구축합니다. 표 형식 및 맞춤 분석 종속성의 그래프를 작성합니다. 문제 해결은 초기 데이터 입력, 산점도 구성 및 이 플롯에 추세선 추가 단계로 나눌 수 있습니다. 이 프로세스를 자세히 살펴 보겠습니다. 워크시트에 초기 데이터를 입력하고 실험 데이터를 플로팅합니다. 그런 다음 차트에서 실험 포인트를 선택하고 마우스 오른쪽 버튼을 클릭하고 명령을 사용합니다. 추가하다엘 추세선(그림 10). 나타나는 대화 상자를 통해 근사 종속성을 구축할 수 있습니다. 이 창의 첫 번째 탭(그림 11)은 근사 종속 유형을 나타냅니다. 두 번째 항목(그림 12)은 구성 매개변수를 정의합니다. 근사 의존성의 이름; 앞으로 (뒤로) 예측 N단위(이 매개변수는 추세선을 확장하는 데 필요한 앞으로(뒤로) 단위 수를 결정함); 선과 곡선의 교차점 표시 여부 y=상수; 다이어그램에 근사 함수를 표시할지 여부(다이어그램 매개변수에 방정식 표시) 다이어그램에 표준 편차 값을 배치할지 여부(파라미터는 근사 신뢰도 값을 다이어그램에 배치). 근사 종속성으로 2차 다항식을 선택하고(그림 11) 그래프에서 이 다항식을 설명하는 방정식을 도출해 보겠습니다(그림 12). 결과 다이어그램은 그림에 나와 있습니다. 13. 마찬가지로 추세선다음과 같은 종속성의 매개변수를 선택할 수 있습니다. 선의 와이=a∙x+비, 대수 와이=ln(엑스)+비, 기하급수적 와이=에브, 힘 와이=xb, 다항식 와이=a∙x 2 +b∙x+씨, 와이=a∙x 3 +b∙x 2 +c∙x+d등등, 최대 6차 다항식까지, 선형 필터링. 4.3. 결정자 사용 결정 블록을 사용하여 최소 제곱법으로 매개변수를 선택하는 MS Excel의 구현이 상당히 흥미롭습니다. 이 기술을 사용하면 모든 종류의 함수 매개변수를 선택할 수 있습니다. 다음 문제의 예에서 이 가능성을 고려해 봅시다. 실시예 3. 실험 결과, 표에 제시된 종속성 z(t) 종속 계수 선택 Z(t)=4에서 +Bt 3 +Ct 2 +Dt+K최소제곱법으로. 이 문제는 5변수 함수의 최소값을 찾는 문제와 동일합니다. 최적화 문제를 해결하는 과정을 고려하십시오(그림 14). 값을 보자 ㅏ, 안에, 와 함께, 디그리고 에게세포에 저장 A7:E7. 함수의 이론적 값을 계산하십시오. 지(티)=At4+Bt3+Ct2+Dt+K주어진 티(B2:J2).
이를 위해 셀에서 B4첫 번째 점(셀 B2): B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7. 이 수식을 범위에 복사 C4:J4가로 좌표가 셀에 저장되는 점에서 함수의 예상 값을 얻습니다. B2:J2. 셀에 B5실험 포인트와 계산 포인트 간의 차이의 제곱을 계산하는 공식을 소개합니다. B5=(B4-B3)^2, 범위에 복사 С5:J5. 셀에서 F7총 2차 오차(10)를 저장합니다. 이를 위해 공식을 소개합니다. F7 = 합계(B5:J5). 명령을 사용하자 Service®솔루션 찾기제약 조건 없이 최적화 문제를 해결합니다. 그림에 표시된 대화 상자에서 적절한 입력 필드를 채우십시오. 14 그리고 버튼을 누르세요 달리다. 해결책이 발견되면 그림 1과 같은 창이 나타납니다. 15. 결정 블록의 결과는 셀에 출력됩니다. A7:E7매개변수 값기능 지(티)=At4+Bt3+Ct2+Dt+K. 세포에서 B4:J4우리는 얻는다 기대 함수 값출발점에서. 셀에서 F7유지됩니다 총 제곱 오차. 범위를 선택하면 동일한 그래픽 영역에 실험점과 적합선을 표시할 수 있습니다. B2:J4, 부르다 차트 마법사그런 다음 포맷 모습차트를 받았습니다. 쌀. 17은 계산이 완료된 후 MS Excel 워크시트를 표시합니다. 5. 참조 1. Alekseev E.R., Chesnokova O.V., Mathcad12, MATLAB7, Maple9 패키지의 전산 수학 문제 해결. – NT Press, 2006.–596s. :아픈. – (튜토리얼) 2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, 공학 및 수학적 문제 해결. –M., BINOM, 2008.–260s. 3. I. S. Berezin 및 N. P. Zhidkov, Computational Methods, Moscow: Nauka, 1966. 4. Garnaev A.Yu., 경제 및 금융에서 MS EXCEL 및 VBA 사용. - 상트페테르부르크: BHV - Petersburg, 1999.-332p. 5. B. P. Demidovich, I. A. Maron 및 V. Z. Shuvalova, 수치 분석 방법.–M.: Nauka, 1967.–368p. 6. Korn G., Korn T., 과학자 및 엔지니어를 위한 수학 핸드북.–M., 1970, 720p. 7. Alekseev E.R., Chesnokova O.V. MS EXCEL에서 실험실 작업을 수행하기 위한 지침. 모든 전문 분야의 학생들을 위한 것입니다. 도네츠크, DonNTU, 2004. 112쪽.대수적 최소제곱
실험 데이터 분석
엑셀의 OLS
와이
0.5
1.5
2.5
3.5
엑스
2.39
2.81
3.25
3.75
4.11
4.45
4.85
5.25
쌀. 6
0.15
0.16
0.17
0.18
0.19
0.20
4.4817
4.4930
5.4739
6.0496
6.6859
7.3891
0,66
0,9
1,17
1,47
1,7
1,74
2,08
2,63
3,12
38,9
68,8
64,4
66,5
64,95
59,36
82,6
90,63
113,5
