■ 목차
1. 함수 설명
2. Correl 함수의 구문 / 사용방법
3. Correl 함수 사용해 보기 - 기온과 아이스크림 판매량의 상관관계 구하기
4. Correl 함수 - 상관계수 해석하기
5. Correl 함수 - 상관계수 응용하기 : 추세선 그리기, R-제곱 값과의 관계
6. 3개 이상의 상관관계 구하기 - 데이터 분석의 상관 분석
** 핵심 요약
- 두 변수(x, y)의 값들이 선형적으로 어느 정도 상관관계를 가지는지 숫자로 표현하는 것이 상관계수(Correlation Coefficient)입니다.
- 엑셀 Correl 함수로 상관계수(Correlation)를 구할 수 있는데 어떻게 사용하고 어떤 의미를 가지며 결과를 어떻게 해석을 해야 하는지 간단하게 알아보도록 하겠습니다.
- 그리고 마지막으로 x, y 값으로 만들어지는 분산형 차트는 어떻게 만들 수 있는지 보도록 하겠습니다.
1. 함수 설명
- Correl 함수는 Correlation (연관성, 상관관계) 의미로 "상관계수(Correlation Coefficient)"를 구해주는 함수입니다.
- 가장 일반적인 사례로 "기온과 아이스크림의 판매량"은 어떤 상관관계가 있는가를 "일자별 기온 데이터와 아이스크림 판매량 데이터", 2가지 데이터로
- Correl 함수를 사용하게 되면 우리는 두 변수가 숫자로 얼마큼 상관관계가 있는지 알 수 있게 됩니다.
- 생산 현장 같은 경우에는 같은 시점에 측정한 여러 가지 제조조건의 변수 데이터를 품질 특성 값과 함께 상관관계를 구하여
- 어떤 공정 조건을 중요하게(=상관관계가 높은) 관리를 해야 하는지를 그리고 어떤 수치로 관리해야 하는지를 확인할 수 있습니다.
- 2 변수(x, y) 간의 상관관계는 Correl 함수로 쉽게 구할 수가 있지만
- 3개 이상의 여러 개의 변수 간의 상관관계를 한 번에 분석하기 위해서는 "통계 데이터 분석 - 상관관계"를 이용해야 합니다.
- 그리고 "통계 데이터 분석"의 경우 먼저 "분석 도구"라는 추가 기능을 등록해 줘야 사용을 할 수 있는데 순서대로 확인을 해보도록 하겠습니다.
2. Correl 함수의 구문 / 사용방법
- 아래 구문은 엑셀 도움말의 설명 내용입니다. 우리는 엑셀 함수의 구문을 다 외울 수도 없고 그럴 필요도 없습니다.
- 다만 엑셀 도움말과 함수사용 시 표시되는 풍선도움말의 용어들에 익숙해질 필요가 있기 때문에 기회가 될 때마다 편하게 봐주시기 바랍니다.
- CORREL(array1, array2)
- array1 필수 요소입니다. 셀 값의 범위입니다.
- array2 필수 요소입니다. 셀 값의 두 번째 범위입니다.
- 첫 번째 인수는 array1으로 보통 엑셀에서 array이라고 하면 배열 = 여러 개의 데이터를 의미하고 x, y 값인 경우 x 값이 입력되어 있는 셀 범위에 해당하는 셀 참조 주소를 지정해 줍니다.
- 두 번째 인수 array2도 동일하게 y 값이 입력되어 있는 셀 범위에 해당하는 셀 참조 주소를 지정해 줍니다.
- Correl 함수는 2개 변수(x, y)의 "숫자" 데이터의 상관관계를 표시하는 함수이기 때문에 텍스트, 논리값, 빈 셀 등 숫자가 아닌 것은 모두 무시하게 됩니다.
- 0의 경우는 당연히 숫자이기 때문에 포함이 되어 계산이 됩니다.
- 그리고 array1과 array2는 동일한 개수가 지정이 되어야 합니다. 그래야 2 변수 간의 상관관계를 계산해 주는데
- 만약 array1은 9개의 데이터가 array2는 10개의 데이터로 서로 다르게 지정하는 경우 Correl 함수는 오류값을 반환하게 됩니다.
- 하지만 array1, 2 모두 10개의 데이터(셀 주소)를 지정했지만 특정 한 개의 데이터가 텍스트 값 등으로 무시가 되는 경우
- x, y 값이 쌍으로 모두 무시가 되기 때문에 x, y 중 한 개의 데이터만 측정이 되더라도 나머지 데이터는 그냥 빈 셀로 입력을 해주면 엑셀이 쌍이 아닌 데이터는 무시하고 상관계수를 알아서 계산해 주게 됩니다.
3. Correl 함수 사용해 보기 - 기온과 아이스크림 판매량의 상관관계 구하기
- 기온과 아이스크림 판매량 데이터를 가지고 기온에 따른 아이스크림 판매량이 얼마큼 상관관계가 있는지를 확인해 보도록 하겠습니다.
- 여기서 2개의 변수로 "x = 기온", "y = 판매량"이 되고 이들 데이터가 "선형"인 경우로 가정하여 얼마 큼의 상관관계가 있는지를 숫자로 확인해 보는 작업입니다.
- 1) 함수 입력 : " = correl ( "
- 수식 입력을 위한 등호 ( " = " )와 함수 correl를 입력하고 인수 입력을 위해서 괄호 열기를 키보드로 입력합니다.
- 2) 첫 번째 인수 array1 입력하기 : " = correl (B3:B10, "
- 첫 번째 인수는 array1으로 x 값에 해당하는 데이터 셀 범위의 주소 "B3:B10"을 마우스 드래그로 선택해서 입력해 줍니다.
- 그리고 인수 구분자 쉼표 ( " , " )를 키보드로 입력합니다.
- 3) 두 번째 인수 array2 입력하기 : " = correl (B3:B10, C3:C10)"
- 두 번째 인수는 array2로 y 값에 해당하는 데이터 셀 범위의 주소 "C3:C10"을 마우스 드래그로 선택해서 입력해 줍니다.
- 그리고 괄호 닫기를 입력하고 "Enter"키를 눌러서 수식 입력을 마무리합니다.
- 결괏값의 소수점 자릿수는 리본메뉴의 "자릿수 늘림 / 줄임" 메뉴를 사용해서 적당히 조정을 해줍니다.
4. Correl 함수 - 상관계수 해석하기
- 상관계수를 구하는 작업은 엑셀 Correl 함수로 어렵지 않게 구할 수 있습니다.
- 그러나 진짜 중요한 것은 우리가 이 결괏값을 어떻게 해석하고 응용하는가 하는 부분입니다.
- 먼저 상관관계 결괏값은 -1 ~ 1의 값을 가지게 됩니다.
- 음수는 역의 상관관계를 의미하며 즉 한쪽이 증가하면 다른 한쪽은 감소한다는 의미이고
- 양수는 양의 상관관계로 한쪽이 증가하면 다른 한쪽도 같이 증가한다는 의미입니다.
- 그리고 숫자가 1에 가까울수록 강한 상관관계를 의미하게 됩니다. 일반적인 수치값의 해석은 아래와 같습니다.
- 앞에서 구한 기온과 아이스크림 판매량은 상관 계수 0.94 정도이기 때문에 "매우 강한 양의 상관과계"를 가지는 것으로 해석을 할 수 있고
- 기온이 올라가게 되면 아이스크림 판매량이 같이 많아질 것으로 기대할 수 있습니다.
5. Correl 함수 - 상관계수 응용하기 : 추세선 그리기, R-제곱 값과의 관계
- 추세선을 그리기 위해서는 x, y 값을 차트로 표시를 해줘야 합니다.
- 이때 사용되는 차트는 "분산형" 차트입니다. 분산형 차트를 사용해 줘야 x, y 값을 각각의 축에 적용해서 그래프를 그리는 것이 가능합니다.
- 분산형 차트에서 전체적인 상관관계의 정도를 눈으로 볼 수가 있고
- 추세선을 삽입하여 더욱 자세하고 직관적으로 x, y 두 변수의 관계 정도를 확인할 수 있습니다.
- 그리고 추세선에 "수식"을 표시하고 수식을 활용하여 특정 기온에서 아이스크림 매출액을 얼마쯤 될 것인지 추정을 할 수 있습니다.
- 그렇게 추정된 값이 얼마나 신뢰를 가지는가는 R-제곱값 ( = R의 자승값 = 상관계수의 자승값 = 상관계수 * 상관계수)을 가지고 해석을 할 수가 있습니다.
- 보통 R-제곱 값이 0.65 이상인 경우 상관관계를 가지고 있다고 판단을 하고 수식을 이용한 추정을 하곤 합니다.
- 1) x, y 분산형 차트 그리기
- 데이터 영역을 선택하고 분산형 차트를 선택해서 차트를 그리게 됩니다.
- 메뉴 패스(리본 메뉴) : (데이터 영역 선택) > "삽입" 탭 > "차트" 그룹 > "분산형"
- 2) 추세선 삽입 - 수식, R-제곱 값 포함
- 차트에서 추세선을 추가하기 위해서는 먼저 차트에서 그래프를 정확하게 선택을 해줘야 합니다.
- 선택된 그래프의 추세선이 추가되기 때문이고 추세선을 추가하면서 옵션으로 "수식", "R-제곱값"을 표시하는 것으로 체크를 하도록 하겠습니다.
- 메뉴 패스 : (차트의 그래프 선택) > (마우스 우클릭) > "추세선 추가"
- 3) 추세선 - 수식, R-제곱 값 응용하기
- ① 상관계수 ^2 = R-제곱 값
- Correl 함수로 구한 상관계수를 제곱하게 되면 바로 R-제곱 값이 됩니다.
- R-제곱 값은 상관관계수를 제곱해서 양의 수로 표시한 값으로 동일하게 상관관계 정도를 해석하는 용도로 사용이 되고
- R-제곱 값이 0.65 이상인 경우 상관관계의 의미를 가지고 수식을 활용하게 됩니다.
-② 추세선 수식 - 특정 기온에서 아이스크림 판매량 추정하기
- 추세선 수식을 사용하여 만약 기온이 30 ℃의 아이스크림 판매량을 추정하고자 한다면
-" x = 기온, y = 아이스크림 판매량"으로 수식을 활용하여 341천 개의 수량으로 아래와 같이 추정이 가능합니다.
6. 3개 이상의 상관관계 구하기 - 데이터 분석의 상관 분석
- x, y와 같이 2개 계열 간의 상관관계는 Correl 함수로 구하는 것이 가능하지만
- 데이터 계열이 3개 이상이 되게 되면 "데이터 분석" 기능을 사용해줘야 합니다.
- 그렇게 어려운 것도 아니고 데이터 범위와 결괏값을 표시할 위치만 정해주면 나머지는 엑셀이 알아서 자동으로 계산해 줍니다. ^^
- 다만 우리는 이러한 상관계수를 어떻게 해석하고 잘 사용할 것인지 대해서만 고민을 해주시면 됩니다.
- "데이터 분석" 메뉴를 처음 사용하는 경우에는 먼저 엑셀 메뉴로 등록을 해주는 것이 필요합니다.
- 1) (처음 사용 시) "데이터 분석" 메뉴 등록하기
- 메뉴 패스 : "파일" 탭 > "옵션" > "추가 기능" > "관리 - 이동" > "분석 도구"
- 2) 분석 도구 - "상관 분석" 사용하기
- "데이터 분석" 추가 기능이 설치가 되었다면 3가지 변수에 대한 상관관계를 구해 보도록 하겠습니다.
- 메뉴 패스 : (데이터 범위 선택) > "데이터" 탭 > "분석" 그룹 > "데이터 분석" > "상관 분석"
- 먼저 적용하고자 하는 데이터 범위를 선택하고 메뉴 패스를 통해 "상관 분석" 기능을 선택합니다.
- 데이터를 먼저 선택을 하지 않아도 나중에 데이터 범위를 선택을 할 수 있지만
- 먼저 선택하고 "상관 분석" 메뉴를 적용하는 경우 엑셀이 "입력 범위"를 자동으로 지정해 주게 됩니다.
- ① "입력 범위" - 데이터가 있는 셀 범위의 주소를 입력합니다.
- 원하는 셀 범위를 마우스로 드래그해서 선택하게 되면 자동으로 선택된 범위의 셀 주소가 표시되게 됩니다
- ② "데이터 방향" : 금번과 같은 경우는 데이터가 열( = 세로) 방향으로 정리가 되어 있습니다. 데이터가 정리된 방향을 선택해 줍니다.
- ③ "첫째 행 이름표 사용" : 데이터의 첫 번째 행이 계열의 이름으로 데이터 분석에서 제외할 경우 선택합니다.
- ④ "출력 범위" : 결괏값이 출력될 위치를 선택하는 부분입니다. 현재 시트에서 적당한 위치의 셀을 마우스로 선택하여 지정하거나
- 다른 워크시트, 통합문서에 결괏값을 출력하는 것이 가능합니다.
- "아이스크림 판매량"과 "기온"관의 상관계수는 0.939 수준으로 "강한 양의 상관관계가 있는" 것으로 나오며
- "습도" 같은 경우는 - 0.69 수준으로 "음의 상관관계가 있는" 것으로 나타납니다.
- 또한 "습도"와 "기온"의 상관계수도 확인이 가능한데 이 경우 -0.49 수준으로 "약한 음의 상관관계가 있는" 것으로 통계적으로 표현이 됩니다.
- 이 처럼 3개의 변수 ("아이스크림 판매량", "기온", "습도") 모두에 대한 상관관계를 한 번에 확인할 수 있게 됩니다.
- 이 처럼 3개 이상의 변수의 상관계수는 "데이터 분석 - 상관관계" 메뉴를 사용하거나 2개씩 Correl 함수를 사용합니다.
- 2개의 변수인 경우 보통은 "분산형" 차트와 "추세선"으로 일단 상관관계가 있어 보이는지 눈으로 먼저 확인을 하고
- 상관관계를 보이는 경우 "수식", "R-제곱" 값으로 상관관계의 정도를 확인하는 방식으로 많이 사용하는 것 같습니다.
- 물론 데이터의 개수가 많을수록 결과의 신뢰도는 높아집니다. 반대로 몇 개 안 되는 데이터의 경우는 그냥 경향성이나 참조용 정도로 사용하시는 것이 좋을 듯합니다.
** 핵심 요약 : Correl 함수 사용법 - 상관계수 구하기
1. 함수 설명
- Correl 함수는 Correlation (연관성, 상관관계)를 의미하며 "상관계수(Correlation Coefficient)"를 구해주는 함수이다.
- 2개 변수(x, y) 간의 상관관계는 Correl 함수로 쉽게 구할 수가 있지만
- 3개 이상의 여러 개의 변수 간의 상관관계를 한 번에 분석하기 위해서는 "통계 데이터 분석 - 상관분석"를 이용해야 한다.
2. Correl 함수의 구문 / 사용방법
- CORREL(array1, array2)
- array1 필수 요소입니다. 셀 값의 범위입니다.
- array2 필수 요소입니다. 셀 값의 두 번째 범위입니다.
- 첫 번째 인수는 array1으로 보통 엑셀에서 array이라고 하면 배열 = 여러 개의 데이터를 의미하고 x 값이 입력되어 있는 셀 범위에 해당하는 셀 참조 주소를 지정해 준다.
- 두 번째 인수 array2도 동일하게 y 값이 입력되어 있는 셀 범위에 해당하는 셀 참조 주소를 지정해 준다.
- Correl 함수는 2개 변수(x, y)의 "숫자" 데이터의 상관관계를 표시하는 함수이기 때문에 텍스트, 논리값, 빈 셀 등 숫자가 아닌 것은 모두 무시를 하게 된다.
- array1과 array2는 동일한 개수가 지정이 되어야 쌍으로 상관관계 분석이 가능하다.
- 한 개의 값이 무시되는 경우 x, y 값이 쌍으로 모두 무시가 되기 때문에 x, y 중 한 개의 데이터만 측정이 되더라도 그냥 입력을 해주자.
3. Correl 함수 사용해 보기 - 기온과 아이스크림 판매량의 상관관계 구하기
- 1) 함수 입력 : " = correl ( "
- 2) 첫 번째 인수 array1 입력하기 : " = correl (B3:B10, "
- 3) 두 번째 인수 array2 입력하기 : " = correl (B3:B10, C3:C10)"
4. Correl 함수 - 상관계수 해석하기
- 상관관계 결괏값은 -1 ~ 1의 값을 가지게 되고 -1 혹은 1에 가까울수록 강한 상관관계를 의미한다.
- 또한 측정한 데이터의 개수가 많을수록 결괏값의 신뢰도는 올라가게 된다.
5. Correl 함수 - 상관계수 응용하기 : 추세선 그리기, R-제곱 값과의 관계
- 추세선을 그리기 위해서는 x, y 값을 차트로 표시해 주는 "분산형" 차트를 사용한다.
- "R-제곱값" = "Correl 함숫값의 제곱값"과 동일하며 0.65 이상인 경우 상관관계를 가진다고 해석한다.
- 1) x, y 분산형 차트 그리기
- 2) 추세선 삽입 - 수식, R-제곱 값 포함
- 3) 추세선 - 수식, R-제곱 값 응용하기
- (상관계수)^2 = "R-제곱 값"이 된다.
- 추세선의 수식으로 특정 기온에서의 아이스크림 판매량을 추정할 수 있다.
6. 3개 이상의 상관관계 구하기 - 데이터 분석의 상관 분석
- 1) (처음 사용 시) "데이터 분석" 메뉴 등록하기
- 메뉴 패스 : "파일" 탭 > "옵션" > "추가 기능" > "관리 - 이동" > "분석 도구"
- 2) 분석 도구 - "상관 분석" 사용하기
- 메뉴 패스 : (데이터 범위 선택) > "데이터" 탭 > "분석" 그룹 > "데이터 분석" > "상관 분석"
- 3개 이상의 경우 2개씩 Correl 함수를 사용하거나 "데이터 분석"을 사용한다.
- 2개의 경우 먼저 "분산형" 차트와 "추세선"을 그려 상관관계가 있는지 눈으로 보고 "R-제곱값", "수식"을 응용하자.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글