목차
1. 상관관계에 대해서
높은 품질의 제품은 정확한 계측기술로 얻을 수 있다는게 개인적인 생각입니다.
하지만 비용을 적게 들이는 평가 기술을 개발해야 제품에 적용할 수 있습니다.
그러기 위해서는 여러가지 요인의 상관성을 분석을 해야 하는데 회귀분석이라고 합니다.
그러나 저는 이 과정을 Fitting이라고 부릅니다. 이걸 잘하면 비용 절감을 효율적으로 이룰 수 있습니다.
상황은 보통 아래와 같습니다.
CASE ①. 외부 계측기로 1시간 측정해야 하는 A와 내부에서 계측 가능한 B
CASE ②. 완성품으로 알 수 있는 A와 단품에서 알 수 있는 B
B는 상대적으로 비용이 적게 드는 평가 항목이니 상관성을 잘 확보하면 A를 측정하는 것과 같은 효과를 낼 수 있습니다.
2. Fitting 하기
예시 DATA 를 분산형 그래프로 그려 보겠습니다.
보통의 경우 A가 더 정확하지만 B로 진행할때 절약이 됩니다.
아래 DATA를 분산형 그래프로 그리면 A가 증가하면 B도 증가한다는 것을 알 수 있습니다.
그런데 정확하게 얼마가 들어가느냐를 알기 위해서 추세선을 그립니다.
수식을 챠트에 표시하게 하면 "Y=2.1223x + 2.3716" 이라는 방정식을 얻었습니다.
또 상관계수 R2 = 0.9523도 얻을 수 있습니다.
실험의 오차가 있겠지만 그래도 B는 A의 2배가 약간 넘는 것으로 보이네요.
3. 상관계수 R2에 대해서
또 R2 값이 주어지는데 이는 실제 실험한 DATA가 이 추세선에서 벗어 나는 정도입니다.
RMS방식으로 완벽하게 일치하면 값이 1로 나옵니다.
기준이야 적용하기 마련이지만 0.95이상이면 A와 B는 충분히 "상관있다"고 결론 내릴 수 있습니다.
1차 함수, 즉 선형성만 확인하는 경우가 많습니다. 하지만 상황에 따라서 다차방적식을 고려해야 하죠.
추세선 속성에서 [다항식]을 선택하면 차수를 높일 수 있습니다.
추세선 옵션 아래에서 "R-제곱 값을 차트에 표시"하면 얻을 수 있습니다.
3차까지 높였을 때 0.96까지 R2가 상승하는 것을 알 수 있습니다.
다만 4차 이후에는 변곡점의 모양으로 봤을때 큰수에서 신뢰성이 떨어집니다.
사실 차수가 높아지면 숫자가 커질때 문제가 될 수 있습니다.
고차 방정식의 경우 ① X 축을 0~1까지 스케일링 해서 사용할 수 있을때 혹은
② DATA의 크기가 한도가 있고 거의 거기까지 실험을 했을 때 사용할 수 있습니다.
이것을 못할 땐 그냥 선형방정식만 보고 판단하는게 더 나을 수 있다는 것이죠.
4. Linest 함수
그래프를 일일히 그리지 않고 바로 숫자로 분석하는 Linest 함수를 소개합니다.
LINEST(known_y's, [known_x's], [const], [stats])
- known_y's : 필수 요소입니다. y = mx + b 식에서 이미 알고 있는 y 값의 집합입니다.
- [known_x's] : 선택 요소입니다. y = mx + b 식에서 이미 알고 있는 x 값의 집합입니다.
입력하지 않으면 1, 2, 3, 4, … 이 들어갑니다. - [const] : b가 True이면 정상적으로 계산 False면 0으로 고정됩니다.
- [stats] : 선택요소로 True면 추가 통계값을 반환합니다.
Linest는 2개이상의 결과값을 반환합니다.
=LINEST(C23:C32,B23:B32,1,0) 라는 수식을 적으면 "Y=2.125x + 2.354" 의 2개 숫자를 반환합니다.
그래서 INDEX 함수를 추가로 사용해야 합니다. 아래 표를 보시면 INDEX의 활용법을 알수 있습니다.
| 수식 | =INDEX(LINEST(C23:C32,B23:B32,1,0),1) | =INDEX(LINEST(C23:C32,B23:B32,1,0),2) |
| 항목 | a | b |
| 값 | 2.125 | 2.354 |
다항식을 적용하면 좀더 길어집니다.
12차까지 적용할 수 있으면 하는 방법은
LINEST(known_y's, [known_x's]^{1,2,3, ... , 12}, [const], [stats])
이 다차방정식으로 들어가면 여러가지 솔루션을 마련할 수 있습니다.
예시 : =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),1)
| 수식 | =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),1) | =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),2) | =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),3) | =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,0),4) |
| 항목 | a | b | c | d |
| 값 | -0.045 | 0.714 | -1.067 | 5.795 |
5. Linest 함수의 추가요소(R2 값 얻기)
LINEST(known_y's, [known_x's], [const], [stats])
Linest의 구성중 [stats]가 TRUE(1)를 알아 보겠습니다.
[stats]을 1이나 True로 입력하면 입체적인 DATA가 됩니다.
예시 : =LINEST(C21:C30,B21:B30^{1,2,3},1,1)
이렇게 되면 2차 행렬 형식으로 index를 작성하면 됩니다.
여기서 우리가 가장 궁금한 R2는 (3,1)입니다. 이걸 가장 많이 사용합니다.
입력 방법 : =INDEX(LINEST(C23:C32,B23:B32^{1,2,3},1,1),3,1)
각 항목을 도움말을 그대로 사용했습니다.
| 통계 | 설명 |
| m1,m2,...,mn, b | 계수 m1,m2,...,mn입니다. |
| se1,se2,...,sen | 계수 m1,m2,...,mn에 대한 표준 오차값입니다. |
| seb | 상수 b에 대한 표준 오차값(const가 FALSE이면 seb = #N/A)입니다. |
| R2 | 결정 계수입니다. y 값의 추정값과 실제값을 비교하며 값의 범위는 0부터 1까지입니다. 계수가 1이면 표본에 완전한 상관 관계가 성립하고 y 값의 추정값과 실제값 사이에는 아무런 차이가 없습니다. 결정 계수가 0이면 해당 회귀 방정식은 y 값을 예측하는 데 아무 도움이 되지 않습니다. 2가 계산된 방법에 대한 자세한 내용은 이 항목의 의 1부에서 "비고"를 참조하세요. |
| sey | y 추정값에 대한 표준 오차입니다. |
| F | F 통계량 또는 F-관측값입니다. F 통계량을 사용하여 종속 변수와 독립 변수 사이에서 관측된 관계가 우연히 발생된 것인지 여부를 확인할 수 있습니다. |
| dy | 자유도입니다. 자유도를 사용하여 통계 테이블에서 F-critical 값을 찾을 수 있습니다. 표에서 찾은 값을 LINEST에서 반환한 F 통계와 비교하여 모델에 대한 신뢰 수준을 파악합니다. |
| ssreg | 회귀 제곱의 합입니다. |
| ssresid | 잔차 제곱의 합입니다. ssreg와 ssresid를 계산하는 방법에 대한 자세한 내용은 뒷부분의 "주의"를 참조하세요. |
'엑셀' 카테고리의 다른 글
| 엑셀 VBA로 같은 폴더의 파일이름 추출하고 변경하는 소스코드(하위폴더 포함, name 사용하기) (0) | 2023.02.09 |
|---|---|
| 엑셀 숫자 자리수 표시하는 방법(ROUND 함수, 셀 서식, 양식 작성 사용) (0) | 2023.02.07 |
| 엑셀 막대 그래프와 꺽은 선형을 합치는 혼합형 챠트 그리는 법(데이터 관리) (0) | 2023.02.06 |
| 엑셀(Excel) 함수 Weeknum과 Weekday를 사용해서 주차 세기(ISOWeeknum) (0) | 2023.02.03 |
| 엑셀(Excel) VBA에서 시트(Sheet)를 제어하는 명령어의 정리 (0) | 2023.02.02 |