Excel의 정규 방정식(통계)

Question 1

게시된 답변에 대한 귀하의 의견에서 제기한 문제를 다루는 대안은 다음과 같습니다.math.stackexchange

귀하의 모델은 다음과 같습니다

y = B - 특급(a*x)

그리고 당신이 가진 것은,선험적으로, B = 21이라고 결정했습니다.

모델은 y(i)와 x(i) 값 사이의 정확한 관계가 아니므로 일반적으로 오류항 e(i)를 추가하고 모델을 다음과 같이 표현합니다.

y(i) = B - exp(a*x(i)) + e(i)

각 x 값에 대해 모델에 의해 추정된 y 값은 y'(i)로 표시됩니다. 여기서

y'(i) = B - exp(a*x(i))

그만큼최소제곱법실제 y(i) 값과 해당 추정값 또는 y'(i) 값 간의 차이의 제곱합을 최소화하는 a 값을 선택하는 것을 목표로 합니다.

y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

따라서 y(i)와 y'(i) 값 사이의 차이의 제곱의 합은 다음과 같습니다.

합계[(y(i) - y'(i))^2] = 합계[e(i)^2]

우변은 오차 항의 제곱합을 합한 것이므로 오차 제곱합이라고 합니다.ESS.

다음을 통해 y(i) 값을 z(i) 값으로 변환하는 과정

z(i) = LN(21 - y(i))

선형 모델을 생성합니다

z = 도끼

이를 통해 LINEST함수를 사용하여 "최적 적합" 값을 추정할 수 있습니다.ㅏ. 답변(또는 math.stackexchange에 대한 질문)에 제공된 데이터를 사용하여 가장 적합한 값은 다음과 같습니다.ㅏ0.147233입니다. 정규 방정식을 구현한 답변에서 얻은 것과 동일한 답변입니다.

이 값과 연관된 ESSㅏ8.27991입니다. 그러나 이 값은 ESS가 달성할 수 있는 최소값이 아닙니다. 그럴 때 발생합니다.ㅏ0.149140의 값을 취하고 해당 ESS는 6.66073입니다.

아래 스크린샷은 계산을 보여줍니다.

추정된 y'(i)와 관련 오차 제곱 및 ESS 값은 모델 y = 21 - exp(a*x)의 두 가지 버전에 대해 표시됩니다.

버전 1에서는ㅏLINEST변환된 모델 z = ax를 기반으로 하는 접근 방식을 사용하여 파생됩니다 . 버전 2에서는ㅏ(변환되지 않은) 모델의 ESS를 최소화하는 값입니다. 이 값이 어떻게ㅏ얻은 내용은 아래와 같습니다.

y = mx + c와 같은 선형 모델의 경우 정규 방정식은 ESS를 최소화하는 m 및 c 값을 추정하는 편리한 방법을 제공합니다. 함수는 LINEST무엇보다도 정규 방정식을 구현합니다.

비선형 모델(예: y = 21 - exp(a*x))의 경우 일반적으로 이러한 편리한 방정식이 존재하지 않으므로 다음 값을 찾기 위해 다른 방법을 사용해야 합니다.ㅏESS를 최소화한 것입니다.

한 가지 접근 방식은 검색 방법을 사용하는 것입니다.ㅏ가장 작은 ESS가 되는 것을 선택하세요.

이것이 바로 다음 스크린샷이 보여주는 내용입니다. Microsoft가 부르는 것을 사용합니다.데이터 테이블. 데이터 테이블은 데이터 테이블이 아니기 때문에 이는 이름을 잘못 선택하는 것입니다. 오히려 계산 내에서 하나 또는 두 개의 요소가 변경될 때 계산 값이 어떻게 변경되는지 확인하기 위한 도구입니다. "가정 분석" 아이콘에서 "데이터 테이블..."을 선택하면 데이터 메뉴의 예측 그룹에 있는 리본에서 찾을 수 있습니다.

데이터 테이블을 생성하고 사용하는 Microsft의 문서는 매우 형편없기 때문에 약간의 요리책 접근 방식을 제공하겠습니다.

데이터 테이블 자체는 N2:O23 범위에서 제공됩니다.
셀 O2에는 탐색할 계산된 값이 포함되어 있습니다. 이 셀에는 =J4값과 관련된 ESS인 수식이 포함되어 있습니다 .ㅏ셀에 J3.
다양한 가치의 가능성ㅏ범위에 배치되고 N3:N23ESS 결과가 셀에 표시됩니다 O3:O23. 이는 21개의 가능한 값을 제공합니다.ㅏ. 이는 임의의 선택이며, 데이터 테이블에는 더 많거나 더 적은 수의 가능한 값이 포함될 수 있습니다.
일단ㅏ값이 제자리에 있으면 범위를 선택 N2:O23하고 데이터 메뉴의 예측 그룹에 있는 "가정 분석" 아이콘에서 "데이터 테이블..."을 선택하여 데이터 테이블 대화 상자를 시작합니다.
대화 상자에서 $J$3"열 입력 셀:" 필드에 입력하고 "확인" 버튼을 누릅니다.
이제 범위는 O3:O23다음 값에 해당하는 ESS 값으로 채워집니다.ㅏ안에 N3:N23. 값을 변경하면 의 N3:N23ESS 값이 업데이트됩니다 O3:O23.

그만큼ㅏ값은 N3:N23입력하는 것이 아니라 공식으로 설정됩니다. 값은 점점 더 미세한 값 세트를 살펴보는 검색 전략을 사용하여 설정됩니다.ㅏ.

21ㅏ의 값은 N3:N23다음을 기준으로 합니다.본부위치 11의 값 - 셀 N13- 위와 아래의 셀은 연속적으로 다릅니다.증가21개 값의 전체 범위가 오름차순으로 정렬되도록 합니다.

검색 전략은 여러 단계를 거치며, 단계 번호는 셀의 값에 의해 제어됩니다 O1.

1단계에서는본부R3값은 0.15(셀에서 ) 로 설정되고증가S30.001(셀에서 ) 로 설정되어 N3:N230.14에서 0.16 사이의 값을 제공합니다. 이 범위는 버전 1 값을 기준으로 선택됩니다.ㅏ, 최소 ESS 값이 이 범위에 속할 것으로 예상됩니다.

이것이 사실임이 입증되었습니다. 21개 값에 대해ㅏ0.14에서 시작하여 0.001에서 0.16까지 증가하면 해당 ESS 값은 39 이상에서 시작됩니다(ㅏ0.14), 다음과 같이 감소합니다.ㅏ까지 증가ㅏ0.149(ESS가 6.66972일 때)의 값을 가지며, 이후 증가하여 70이 넘는 ESS 값에 도달할 때ㅏ0.16이다. 이는 다음의 가치를 보여줍니다.ㅏESS를 최소화하는 것은 0.149 부근이다.

(값 범위 내에서 최소값이 발견되는 경우가 입증되지 않았다면 ESS 값은 최소값을 범위의 한쪽 끝에 두면서 모두 증가하거나 감소했을 것입니다. 이 경우본부(셀의 R3) 값을 조정해야 합니다.증가S3중간 범위 최소값을 찾을 때까지 값(셀에 있음 )

의 값 범위에 대해 N3:N23셀 O27과 N27각각은 최소 ESS 값과ㅏ최소한의 것을 생산하는 것입니다.

의 가치ㅏ최소한의 것을 생산하고 새로운 것을 제공합니다.본부검색의 다음 단계에 대한 값입니다. 새로운증가는 10배 감소한 이전 값입니다. 이러한 새로운 값은본부그리고증가값은 열의 "제어 테이블"에 수동으로 입력되고 R단계 S번호는 셀에서 수동으로 1씩 증가됩니다 O1.

검색은 연속적인 단계를 통해 진행되며, ESS 값을 실질적으로 줄일 수 없을 때 종료됩니다.

스크린샷은 검색 2단계의 결과를 보여줍니다.

Answer