Excel에서 이상값 함수를 사용하는 방법

데이터를 분석할 때 이상치는 결과를 왜곡하고 신뢰성을 떨어뜨릴 수 있습니다. 예를 들어, 하나 이상의 값이 대부분의 데이터보다 상당히 높으면 평균이 높아져 데이터 전체가 반영되지 않을 수 있습니다. 따라서 이러한 외부 데이터 요소를 제거하는 것은 신뢰할 수 있는 통계 계산을 수행하는 중요한 단계입니다. 전용 이상값 기능이 없으면 Excel에서 이상값 테스트를 수행하는 가장 쉬운 방법은 사분위수 범위를 사용하여 이상값을 식별할 수 있지만 Trimmean 함수를 사용하여 동일한 근사치를 구할 수도 있습니다. 결과.

데이터의 사분위수 범위는 상자 수염 그림에서 "상자"가 차지하는 범위 또는 그 이상입니다. 정확히는 데이터의 1사분위수에서 3사분위수 값을 뺀 결과 사분위수. Excel에는 데이터의 사분위수를 계산하는 기본 제공 기능이 포함되어 있습니다. 여분의 셀을 찾아 "=QUARTILE([데이터 범위], [사분위수])"를 입력하고 셀 범위를 입력합니다. "[data range]"라고 표시된 데이터와 "[quartile 숫자]."

예를 들어, 셀 A2에서 A101까지의 데이터가 있고 첫 번째 사분위수 값을 원하는 경우 "=QUARTILE(A2:A101, 1)"을 입력하여 값을 찾습니다. 인수의 첫 번째 부분은 마우스로 해당 셀을 강조 표시할 수 있지만 쉼표 뒤에는 원하는 사분위수를 적어야 합니다. 데이터가 동일한 3사분위수에 대해 "=QUARTILE(A2:A101, 3)"을 입력하여 결과를 얻습니다.

다른 빈 셀을 사용하여 세 번째 사분위수 셀의 값에서 첫 번째 사분위수 셀의 값을 뺍니다. 첫 번째 사분위수가 셀 C2에 있고 세 번째 사분위수가 셀 D2에 있는 경우 "=D2-C2"를 입력하여 결과를 얻습니다. 이것은 사분위수 범위입니다.

이상값을 찾기 위해 이제 이상값 공식에서 사분위수 범위를 사용할 수 있습니다. 이 범위에서는 데이터의 상한이 3사분위수에 사분위수 범위의 1.5배를 더한 값, 하한선은 1사분위수에서 1사분위수 값에서 1.5배를 뺀 값입니다. 범위.

첫 번째 사분위수 값이 C2에 있는 경우 세 번째 사분위수 값은 셀 D2에 있고 사분위수 범위는 다음과 같습니다. 셀 E2에서 하한값을 찾으려면 "=C2-(1.5 * E2)"를 입력하고 상한값을 찾으려면 "=D2+(1.5 * E2)"를 입력합니다. 한계. 일반적으로 하한값을 찾으려면 "=[1사분위수] – (1.5 * [사분위수 범위])"를 입력하고 상한값을 찾으려면 "=[제3사분위수] + (1.5 * [사분위수 범위])"를 입력합니다.

하한값 이하 또는 상한값 이상은 모두 이상값입니다.

Excel에서 이상값 테스트를 완료하려면 논리적 "OR" 함수를 사용하여 데이터 클래스의 어떤 값이 이상값인지 효율적인 방식으로 식별합니다. "=OR([데이터 셀]>[상한], [데이터 셀]$F$2, A2

A2의 값이 상한보다 높거나 하한보다 낮으면 값이 이상값임을 나타내는 "TRUE"가 표시됩니다. 수식이 있는 셀의 오른쪽 하단 모서리를 클릭하여 이 수식을 아래로 드래그하고 각각에 대해 동일한 계산을 수행하기 위해 최종 데이터 셀 옆에서 완료되도록 아래로 드래그합니다. 데이터 포인트.

데이터를 강조 표시하고 "홈" 탭의 "스타일" 섹션에서 "조건부 서식"으로 이동하여 이상값의 서식을 변경할 수도 있습니다. "새 규칙"을 선택하고 "수식을 사용하여 서식을 지정할 셀 결정" 옵션을 강조 표시합니다. 이전 단락과 동일한 수식을 입력한 다음 "서식" 옵션을 클릭하여 특이치에 대한 고유한 서식을 선택합니다.

"Trimmean" 함수는 이상값을 식별하는 더 간단한 방법입니다. 함수를 사용하려면 "=TRIMMEAN([data range], [proportion to trim])"을 입력하고 "[data range]" 대신 데이터를 포함하는 셀 범위와 소수점 이하 자릿수 "[트림 비율]"이라고 표시된 부분에서 트리밍하려는 비율입니다. 이렇게 하면 상단과 하단의 극단값을 제거한 다음 이를 기반으로 평균을 계산합니다. 남은. 따라서 10%를 잘라내면 평균을 계산하기 전에 상위 5%와 하위 5%를 제거합니다.

데이터가 A2에서 A101까지 실행되고 값의 극한 5%를 잘라내려면 "=TRIMMEAN(A2:A101, 0.05)"를 입력하여 조정된 평균을 찾습니다. 대신 "=TRIMMEAN(A2:A101, 0.15)"를 작성하여 15%를 트리밍할 수 있습니다.