Excel에서 백분위수 순위를 기반으로 데이터를 강조 표시하는 방법

백분위수 순위는 샘플의 다른 데이터 값과 관련된 특정 데이터 값의 순위를 나타냅니다. 백분위수는 표준화된 테스트의 성능을 결정하는 데 가장 많이 사용됩니다. 한 어린이가 표준화된 시험에서 90번째 백분위수에 해당하는 점수를 받았다면, 이는 해당 자녀의 점수가 시험을 치르는 다른 어린이의 90%보다 높다는 것을 의미합니다.

그것을 보는 또 다른 방법은 그 아이의 점수가 시험을 보는 모든 아이들의 상위 10퍼센트 안에 든다고 말하는 것입니다.

백분위수는 데이터 분석에서 전체 그룹과 관련하여 주제의 성과를 측정하는 방법으로 자주 사용됩니다. 예를 들어 연간 수익을 기반으로 각 직원의 백분위수 순위를 결정합니다.

Excel에서는 PERCENTILE 함수를 사용하여 주요 백분위수 순위를 쉽게 얻을 수 있습니다. 이 함수에는 데이터 범위와 보고자 하는 백분위수 점수라는 두 가지 인수가 필요합니다.

표시된 예에서 셀 D7의 값은 B3:B20 범위의 데이터를 기반으로 75번째 백분위수를 가져오는 다음 수식의 결과입니다.

=백분위수($B$3:$B$20,0.75)

이 공식은 $52,651 이상의 수익을 가진 직원이 성과 상위 75%에 속한다는 것을 알려줍니다.

D16 셀의 값은 B3:B20 범위의 데이터를 기반으로 25번째 백분위수를 가져오는 다음 수식의 결과입니다.

=백분위수($B$3:$B$20,0.25)

이 공식은 $24,656 미만의 수익을 가진 직원이 성과자의 하위 25%에 속한다는 것을 알려줍니다.

이러한 백분위수 마커를 사용하여 이 예제에서는 75번째 백분위수의 모든 값이 녹색으로 표시되고 25번째 백분위수의 모든 값이 빨간색으로 표시되도록 조건부 서식을 적용합니다.

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

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

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

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

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

수식 입력 상자에 이 단계에서 표시된 수식을 입력합니다.

이 수식에서는 대상 셀(B3)의 데이터가 25번째 백분위수 내에 있는지 여부를 평가합니다. 그렇다면 조건부 서식이 적용됩니다.

=B3<>

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

포맷 버튼을 클릭합니다.

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

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

이 시점에서 조건부 서식 규칙 관리자 대화 상자에 있어야 합니다.

새 규칙 버튼을 클릭합니다.

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

대화 상자 맨 위에 있는 목록 상자에서 수식을 사용하여 서식을 지정할 셀 결정 옵션을 클릭합니다. 이 선택 항목은 지정한 공식을 기반으로 값을 평가합니다. 특정 값이 TRUE로 평가되면 조건부 서식이 해당 셀에 적용됩니다.

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

이 수식에서는 대상 셀(B3)의 데이터가 75번째 백분위수 내에 있는지 평가하고 있습니다. 그렇다면 조건부 서식이 적용됩니다.

=B3>=백분위수($B$3:$B$20,0.75)

포맷 버튼을 클릭합니다.

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

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

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