Excel에서 통계 이상값을 강조 표시하는 방법

데이터 분석을 수행할 때 일반적으로 값이 일부 중앙 데이터 포인트(중앙값)를 중심으로 클러스터링된다고 가정합니다. 그러나 때로는 일부 값이 중심점에서 너무 멀리 떨어져 있습니다. 이러한 값을 이상값이라고 합니다(예상 범위 밖에 있음). 이상치는 통계 분석을 왜곡하여 데이터에 대해 잘못된 결론을 내리거나 오해의 소지가 있는 결론을 내릴 수 있습니다.

몇 가지 간단한 수식과 조건부 서식을 사용하여 데이터의 이상값을 강조 표시할 수 있습니다.

이상치를 식별하는 첫 번째 단계는 범위의 통계적 중심을 정확히 찾아내는 것입니다. 이 정확한 위치를 지정하려면 1사분위수와 3사분위수를 찾는 것으로 시작합니다. 분위 각 그룹은 데이터의 25 %를 차지하여, 네 개의 그룹으로 동일한 데이터 세트의 통계적 분할된다. 컬렉션의 상위 25%는 1사분위수로 간주되고 하위 25%는 4사분위수로 간주됩니다.

Excel에서는 QUARTILE 함수를 사용하여 사분위수 값을 쉽게 얻을 수 있습니다. 이 함수에는 데이터 범위와 원하는 사분위수라는 두 개의 인수가 필요합니다.

표시된 예에서 셀 E3 및 E4의 값은 B3:B20 범위의 데이터에 대한 1사분위수 및 3사분위수입니다.

이 두 사분위수를 사용하면 1사분위수에서 3사분위수를 빼서 데이터 집합의 통계적 50%를 계산할 수 있습니다. 이 통계적 50%를 사분위수 범위(IQR)라고 합니다. 그림 9-18은 E5 셀의 IQR을 표시합니다.

이제 문제는 중간 50%에서 어느 정도의 가치가 있고 여전히 "합리적인" 가치로 간주될 수 있습니까? 통계학자들은 일반적으로 IQR*1.5를 사용하여 합리적인 상부 및 하부 울타리를 설정할 수 있다는 데 동의합니다.

아래쪽 울타리는 1사분위수 – IQR*1.5와 같습니다.

상단 울타리는 3사분위수 + IQR*1.5와 같습니다.

보시다시피 셀 E7과 E8은 최종 상단 및 하단 울타리를 계산합니다. 위쪽 울타리보다 크거나 아래쪽 울타리보다 작은 값은 이상값으로 간주됩니다.

이 시점에서 조건부 서식 규칙을 구현하기 쉽습니다.

이 기본 서식 규칙을 작성하려면 다음 단계를 따르세요.

대상 범위의 데이터 셀(이 예에서는 B3:B20 셀)을 선택하고 Excel 리본의 홈 탭을 클릭한 다음 조건부 서식 → 새 규칙을 선택합니다.

새 서식 규칙 대화 상자가 열립니다.

대화 상자 맨 위에 있는 목록 상자에서 수식을 사용하여 서식을 지정할 셀 결정 옵션을 클릭합니다.

이 선택 항목은 지정한 수식을 기반으로 값을 평가합니다. 특정 값이 TRUE로 평가되면 조건부 서식이 해당 셀에 적용됩니다.

수식 입력 상자에 여기에 표시된 수식을 입력합니다.

OR 함수를 사용하여 대상 셀(B3)의 값을 각각 $E$7 및 $E$8 셀에 있는 위쪽 및 아래쪽 펜스와 비교합니다. 대상 셀이 위쪽 울타리보다 크거나 아래쪽 울타리보다 작으면 이상값으로 간주되어 TRUE로 평가되어 조건부 서식을 트리거합니다.

=OR(B3<$e$8,b3>$E$7)

수식에서 대상 셀(B3)에 대한 절대 참조 달러 기호($)를 제외합니다. 셀 참조를 입력하는 대신 B3 셀을 클릭하면 Excel에서 자동으로 셀 참조를 절대 참조로 만듭니다. Excel에서 각 셀의 자체 값을 기반으로 이 서식 규칙을 적용해야 하므로 대상 셀에 절대 참조 달러 기호를 포함하지 않는 것이 중요합니다.

포맷 버튼을 클릭합니다.

그러면 대상 셀의 글꼴, 테두리 및 채우기 서식을 지정하기 위한 전체 옵션이 있는 셀 서식 대화 상자가 열립니다. 서식 옵션 선택을 완료한 후 확인 버튼을 클릭하여 변경 사항을 확인하고 새 서식 규칙 대화 상자로 돌아갑니다.

새 서식 규칙 대화 상자로 돌아가서 확인 버튼을 클릭하여 서식 규칙을 확인합니다.

조건부 서식 규칙을 편집해야 하는 경우 서식이 지정된 범위 내의 데이터 셀에 커서를 놓고 홈 탭으로 이동하여 조건부 서식 → 규칙 관리를 선택하기만 하면 됩니다. 조건부 서식 규칙 관리자 대화 상자가 열립니다. 편집할 규칙을 클릭한 다음 규칙 편집 버튼을 클릭합니다.