내용
상관 분석은 1차 값이 2차 값에 의존하는 정도를 결정하는 데 사용되는 일반적인 연구 방법입니다. 스프레드시트에는 이러한 유형의 연구를 구현할 수 있는 특별한 도구가 있습니다.
상관분석의 본질
두 개의 서로 다른 양 사이의 관계를 결정할 필요가 있습니다. 즉, 초의 변화에 따라 값이 어느 방향(작게/크게)으로 변하는지 드러낸다.
상관분석의 목적
상관 계수의 식별이 시작되면 종속성이 설정됩니다. 이 방법은 상관 관계를 사용하여 계산된 지표가 하나만 있기 때문에 회귀 분석과 다릅니다. 간격이 +1에서 -1로 변경됩니다. 양수이면 첫 번째 값이 증가하면 두 번째 값이 증가합니다. 음수이면 첫 번째 값이 증가하면 두 번째 값이 감소합니다. 계수가 높을수록 더 강한 값이 두 번째 값에 영향을 줍니다.
중요! 0번째 계수에서는 양 사이에 관계가 없습니다.
상관 계수의 계산
여러 샘플에 대한 계산을 분석해 보겠습니다. 예를 들어, 광고 판촉 및 판매량에 대한 지출이 별도의 열에 월별로 설명되어 있는 표 형식 데이터가 있습니다. 표를 기반으로 광고 판촉 비용에 대한 판매량 의존도를 알아 보겠습니다.
방법 1: 함수 마법사를 통해 상관 관계 확인
CORREL – 상관 분석을 구현할 수 있는 기능입니다. 일반 양식 – 코렐(massiv1;massiv2). 자세한 지침 :
- 계산 결과를 표시할 셀을 선택해야 합니다. 텍스트 필드 왼쪽에 있는 "함수 삽입"을 클릭하여 수식을 입력합니다.
- 기능 마법사가 열립니다. 여기서 당신이 찾아야 할 CORREL, 그것을 클릭 한 다음 "확인"을 클릭하십시오.
- 인수 창이 열립니다. "Array1" 줄에 값의 첫 번째 간격의 좌표를 입력해야 합니다. 이 예에서 이것은 판매 값 열입니다. 이 열에 있는 모든 셀을 선택하기만 하면 됩니다. 마찬가지로 "Array1" 줄에 두 번째 열의 좌표를 추가해야 합니다. 이 예에서는 광고 비용 열입니다.
- 모든 범위를 입력한 후 "확인" 버튼을 클릭합니다.
계수는 작업 시작 시 표시된 셀에 표시되었습니다. 얻은 결과는 0,97입니다. 이 표시기는 두 번째 값에 대한 첫 번째 값의 높은 의존성을 반영합니다.
방법 2: 분석 도구를 사용하여 상관 관계 계산
상관 관계를 결정하는 또 다른 방법이 있습니다. 여기서는 분석 패키지에 있는 기능 중 하나를 사용합니다. 사용하기 전에 도구를 활성화해야 합니다. 자세한 지침:
- "파일"섹션으로 이동하십시오.
- "설정"섹션을 클릭해야하는 새 창이 열립니다.
- "추가 기능"을 클릭하십시오.
- 하단에서 "관리" 요소를 찾습니다. 여기서 컨텍스트 메뉴에서 "Excel 추가 기능"을 선택하고 "확인"을 클릭해야 합니다.
- 특별한 추가 기능 창이 열렸습니다. "분석 패키지" 요소 옆에 체크 표시를 하십시오. "확인"을 클릭합니다.
- 활성화에 성공했습니다. 이제 데이터로 이동합니다. "데이터 분석"을 클릭해야하는 "분석"블록이 나타납니다.
- 나타나는 새 창에서 "Correlation" 요소를 선택하고 "OK"를 클릭합니다.
- 화면에 분석 설정 창이 나타납니다. "입력 간격" 줄에는 분석에 참여하는 절대적으로 모든 열의 범위를 입력해야 합니다. 이 예에서는 "판매 가치" 및 "광고 비용" 열입니다. 출력 표시 설정은 처음에 새 워크시트로 설정되어 결과가 다른 시트에 표시됩니다. 선택적으로 결과의 출력 위치를 변경할 수 있습니다. 모든 설정을 마친 후 "확인"을 클릭하십시오.
최종 점수가 나왔습니다. 결과는 첫 번째 방법인 0,97과 동일합니다.
MS Excel에서 다중 상관 계수의 정의 및 계산
여러 수량의 종속성 수준을 식별하기 위해 여러 계수가 사용됩니다. 앞으로 결과는 상관 행렬이라는 별도의 표에 요약됩니다.
자세한 안내 :
- "데이터" 섹션에서 이미 알려진 "분석" 블록을 찾고 "데이터 분석"을 클릭합니다.
- 나타나는 창에서 "Correlation" 요소를 클릭하고 "OK"를 클릭합니다.
- "입력 간격" 행에서 소스 테이블의 XNUMX개 이상의 열에 대한 간격을 입력합니다. 범위는 수동으로 입력하거나 LMB로 간단히 선택할 수 있으며 원하는 라인에 자동으로 나타납니다. "그룹화"에서 적절한 그룹화 방법을 선택합니다. "출력 매개변수"에서 상관 관계 결과가 표시될 위치를 지정합니다. "확인"을 클릭합니다.
- 준비가 된! 상관 행렬이 구축되었습니다.
Excel의 쌍 상관 계수
Excel 스프레드시트에서 쌍 상관 계수를 올바르게 그리는 방법을 알아보겠습니다.
Excel에서 쌍 상관 계수 계산
예를 들어 x 및 y 값이 있습니다.
X는 종속변수이고 y는 독립변수입니다. 이 지표들 사이의 관계의 방향과 강도를 찾는 것이 필요합니다. 단계별 지침:
- 함수를 사용하여 평균값을 구해보자 심장.
- 각각 계산해보자 х и xavg, у и 평균 «-» 연산자를 사용합니다.
- 계산된 차이를 곱합니다.
- 이 열의 지표 합계를 계산합니다. 분자는 찾은 결과입니다.
- 차이의 분모 계산 х и x-평균, y и y-중간. 이를 위해 제곱을 수행합니다.
- 기능 사용 오토섬마, 결과 열에서 표시기를 찾습니다. 우리는 곱셈을 합니다. 기능 사용 ROOT 결과를 제곱합니다.
- 분모와 분자의 값을 사용하여 몫을 계산합니다.
- CORREL은 복잡한 계산을 방지할 수 있는 통합 기능입니다. "Function Wizard"로 이동하여 CORREL을 선택하고 표시기 배열을 지정합니다. х и у. 얻은 값을 표시하는 그래프를 작성합니다.
Excel의 쌍별 상관 계수 행렬
쌍을 이루는 행렬의 계수를 계산하는 방법을 분석해 보겠습니다. 예를 들어, XNUMX개의 변수로 구성된 행렬이 있습니다.
단계별 지침 :
- "데이터" 탭의 "분석" 블록에 있는 "데이터 분석"으로 이동합니다. 표시되는 목록에서 상관 관계를 선택합니다.
- 필요한 모든 설정을 지정합니다. "입력 간격" – XNUMX개 열의 간격입니다. "출력 간격" – 합계를 표시하려는 위치입니다. "확인" 버튼을 클릭합니다.
- 선택한 장소에 상관 행렬이 구축되었습니다. 행과 열의 각 교집합은 상관 계수입니다. 좌표가 일치하면 숫자 1이 표시됩니다.
Excel에서 관계 및 상관 관계를 결정하는 CORREL 함수
CORREL – 두 배열 간의 상관 계수를 계산하는 데 사용되는 함수입니다. 이 기능의 모든 기능에 대한 네 가지 예를 살펴보겠습니다.
Excel에서 CORREL 함수를 사용하는 예
첫 번째 예입니다. XNUMX년 동안 회사 직원들의 평균 급여와 환율에 대한 정보가 있는 판이 있습니다. 이 두 수량 간의 관계를 식별할 필요가 있습니다. 테이블은 다음과 같습니다.
계산 알고리즘은 다음과 같습니다.
표시된 점수는 1에 가깝습니다. 결과:
결과에 대한 조치 영향의 상관 계수 결정
두 번째 예. 두 명의 입찰자가 XNUMX일 간의 프로모션을 위해 두 개의 다른 대행사에 접근했습니다. 매일 사회 투표를 실시하여 각 지원자에 대한 지원 정도를 결정했습니다. 인터뷰 대상자는 두 지원자 중 한 명을 선택하거나 모두를 반대할 수 있습니다. 각 광고 프로모션이 지원자 지원 정도에 어느 정도 영향을 미치는지 판단할 필요가 있으며, 어느 회사가 더 효율적입니다.
아래 공식을 사용하여 상관 계수를 계산합니다.
- =코렐(A3:A17;B3:B17).
- =코렐(A3:A17;C3:C17).
결과 :
얻은 결과로부터 1차 지원자에 대한 지지도는 광고홍보가 진행되는 날마다 증가하여 상관계수가 1에 가까워짐을 알 수 있다. 5일에는 긍정적인 경향이 있었다. 그런 다음 신뢰의 정도가 감소했고 XNUMX일째에는 초기 지표 아래로 떨어졌습니다. 낮은 점수는 승진이 지원에 부정적인 영향을 미쳤음을 나타냅니다. 표 형식으로 고려되지 않은 다른 부수적인 요소도 지표에 영향을 미칠 수 있음을 잊지 마십시오.
동영상 조회수와 리포스트 상관관계에 따른 콘텐츠 인기도 분석
세 번째 예. YouTube 비디오 호스팅에서 자신의 비디오를 홍보하는 사람은 소셜 네트워크를 사용하여 채널을 광고합니다. 그는 소셜 네트워크의 재게시 횟수와 채널의 조회수 사이에 약간의 관계가 있음을 알아차렸습니다. 스프레드시트 도구를 사용하여 미래의 성능을 예측할 수 있습니까? 리포스트 횟수에 따른 동영상 조회수 예측을 위해 선형회귀식을 적용하는 것이 타당한지 확인할 필요가 있다. 값이 있는 테이블:
이제 아래 공식에 따라 두 지표 사이의 관계가 있는지 확인해야 합니다.
0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"강한 직접 관계";"강한 역 관계");"약하거나 없는 관계")' class='formula'>
결과 계수가 0,7보다 높으면 선형 회귀 함수를 사용하는 것이 더 적절합니다. 이 예에서는 다음을 수행합니다.
이제 그래프를 작성합니다.
이 방정식을 적용하여 200, 500 및 1000주에서 조회수를 결정합니다. =9,2937*D4-206,12. 다음 결과를 얻습니다.
함수 예보 예를 들어 XNUMX개의 재게시가 있는 경우 현재 조회수를 결정할 수 있습니다. 우리는 다음을 적용합니다: 0,7;PREDICTION(D7;B3:B8;A3:A8);"값은 관련이 없습니다.")' class='formula'>. 다음 결과를 얻습니다.
Excel에서 CORREL 함수 사용의 기능
이 기능에는 다음과 같은 기능이 있습니다.
- 빈 셀은 고려되지 않습니다.
- 부울 및 텍스트 유형 정보가 포함된 셀은 고려되지 않습니다.
- 이중 부정 "-"는 숫자 형식의 논리 값을 설명하는 데 사용됩니다.
- 연구된 어레이의 셀 수가 일치해야 합니다. 그렇지 않으면 #N/A 메시지가 표시됩니다.
상관계수의 통계적 유의성 평가
상관 계수의 유의성을 테스트할 때 귀무 가설은 지표의 값이 0인 반면 대안은 그렇지 않다는 것입니다. 검증에는 다음 공식이 사용됩니다.
결론
스프레드시트의 상관관계 분석은 간단하고 자동화된 프로세스입니다. 이를 수행하려면 필요한 도구의 위치와 프로그램 설정을 통해 활성화하는 방법만 알면 됩니다.