엑셀 RANK 기능
Excel의 RANK 함수는 동일한 목록의 다른 값과 비교하여 숫자 값의 순서 (또는 순위)를 반환합니다. 즉, 어떤 값이 가장 높은지, 두 번째로 높은지 등을 알려줍니다.
정렬된 목록에서 특정 숫자의 순위는 해당 위치가 됩니다. Excel의 RANK 함수는 가장 큰 값 (내림차순으로 정렬 된 것처럼) 또는 가장 작은 값 (오름차순으로 정렬 된 것처럼)으로 시작하는 순위를 결정할 수 있습니다.
Excel RANK 함수의 구문은 다음과 같습니다.
Number (필수) - 순위를 찾으려는 값입니다.
Ref (필수) - 순위를 매길 숫자 값 목록입니다. 숫자 배열 또는 숫자 목록에 대한 참조로 제공될 수 있습니다.
Order (선택 사항) - 값의 순위를 지정하는 숫자입니다.
- 0이거나 생략된 경우 값은 내림차순으로, 즉 가장 큰 값에서 가장 작은 순서로 순위가 매겨집니다.
- 1 또는 0이 아닌 다른 값의 경우 값은 오름차순(즉, 가장 작은 값에서 가장 큰 값)으로 정렬됩니다.
엑셀 RANK. EQ 기능
RANK.EQ는 Excel 2010에 도입된 RANK 기능의 개선된 버전입니다. RANK와 동일한 구문을 가지며 동일한 논리로 작동합니다. 여러 값의 순위가 동일할 경우 가장 높은 순위가 모든 값에 할당됩니다.
Excel 2007 이하 버전에서는 항상 RANK 함수를 사용해야합니다. Excel 2010, Excel 2013 및 Excel 2016에서는 RANK 또는 RANK.EQ를 사용할 수 있습니다. 그러나 RANK를 사용하는 것이 좋습니다. RANK는 언제든지 중단될 수 있기 때문에 EQ입니다.
엑셀 RANK. AVG 기능
RANK. AVG는 Excel 2010, Excel 2013, Excel 2016 이상에서만 사용할 수 있는 Excel에서 순위를 찾는 또 다른 기능입니다.
다른 두 함수와 동일한 구문을 사용합니다.
차이점은 둘 이상의 숫자에 동일한 순위가 있는 경우 평균 순위가 반환된다는 것입니다(AVG는 "평균"을 나타냄).
Excel의 RANK에 대해 알아야 할 4가지 사항
- Excel의 모든 순위 공식은 양수 및 음수, 0, 날짜 및 시간 값과 같은 숫자 값에 대해서만 작동합니다. ref 인수의 숫자가 아닌 값은 무시됩니다.
- 모든 RANK 함수는 중복 값에 대해 동일한 순위를 반환하고 아래 예제와 같이 후속 순위를 건너뜁니다.
- Excel 2010 이상 버전에서는 RANK 함수가 RANK.EQ 및 RANK.AVG로 대체되었습니다. 이전 버전과의 호환성을 위해 RANK는 여전히 모든 버전의 Excel에서 작동하지만 이후에는 사용할 수 없을 수도 있습니다.
- ref에서 번호를 찾을 수 없는 경우 Excel Rank 함수는 #N/A 오류를 반환합니다.
기본 Excel 순위 수식 (최고에서 최저로)
Excel의 순위 데이터에 대해 더 잘 이해하려면 다음 스크린 샷을 살펴보십시오.
세 수식 모두 B 열의 숫자를 내림차순으로 순위를 매깁니다(order 인수는 생략됨).
모든 버전의 Excel 2003 - 2016:
=RANK($B2,$B$2:$B$7)
Excel 2010 - 2016에서:
=RANK.EQ($B2,$B$2:$B$7)
=RANK.AVG($B2,$B$2:$B$7)
차이점은 이러한 수식이 중복 값을 처리하는 방식에 있습니다. 보시다시피 동일한 점수가 B5 및 B6 셀에 두 번 나타나며 후속 순위에 영향을 미칩니다.
- RANK 및 RANK입니다. EQ 공식은 두 중복 점수에 2등급을 부여합니다. 다음으로 높은 점수 (Daniela)는 4 위입니다. 3등급은 누구에게도 주어지지 않습니다.
- RANK입니다. AVG 수식은 백그라운드에서 각 중복에 서로 다른 순위(이 예제의 경우 2 및 3)를 할당하고 해당 순위의 평균(2.5)을 반환합니다. 다시 말하지만, 3 순위는 누구에게도 할당되지 않습니다.
Excel에서 RANK를 사용하는 방법 - 수식 예제
Excel에서 가장 낮은 순위에서 가장 높은 순위로 순위를 매기는 방법
위의 예에서 볼 수 있듯이 숫자의 순위를 가장 높은 것에서 가장 낮은 것으로 지정하려면 순서 인수가 0으로 설정되거나 생략(기본값)된 Excel 순위 수식 중 하나를 사용합니다.
다른 숫자와 비교하여 숫자의 순위를 오름차순으로 정렬하려면 선택적 세 번째 인수에 1 또는 0이 아닌 다른 값을 입력합니다.
예를 들어, 학생들의 100미터 달리기 시간의 순위를 매기려면 아래 공식 중 하나를 사용할 수 있습니다.
=RANK(B2,$B$2:$B$7,1)
=RANK.EQ(B2,$B$2:$B$7,1)
절대 셀 참조를 사용하여 ref 인수의 범위를 잠그므로 열 아래로 수식을 복사할 때 변경되지 않습니다.
결과적으로 가장 낮은 값(가장 빠른 시간)이 1위가 되고 가장 큰 값(가장 느린 시간)이 가장 낮은 순위인 6이 됩니다. 동일한 시간(B2 및 B7)에는 동일한 순위가 부여됩니다.
Excel에서 데이터의 순위를 고유하게 지정하는 방법
모든 Excel 순위 함수는 동일한 값의 항목에 대해 동일한 순위를 반환합니다. 원하지 않는 경우 다음 공식 중 하나를 사용하여 타이 브레이크 상황을 해결하고 각 숫자에 고유한 순위를 부여합니다.
최고에서 최저까지 고유한 순위
학생들의 수학 점수를 내림차순으로 고유하게 순위를 매기려면 다음 공식을 사용하십시오.
=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1
가장 낮은 것부터 가장 높은 것까지 고유한 순위
100미터 경주 결과의 순위를 중복 없이 오름차순으로 매기려면 다음 공식을 사용합니다.
=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1
두 수식의 유일한 차이점은 RANK의 순서 인수입니다.
EQ 기능: 값을 내림차순으로 순위를 매기려면 생략하고, 오름차순으로 순위를 매기려면 1을 생략합니다.
두 수식 모두에서 상대 및 절대 셀 참조를 영리하게 사용하는 COUNTIF 함수가 트릭을 수행합니다. 즉, COUNTIF를 사용하여 숫자의 셀을 포함하여 위의 셀에서 순위가 매겨진 숫자의 발생 횟수를 확인합니다. 수식을 입력하는 맨 위 행에서 범위는 단일 셀($B$2:B2)로 구성됩니다.
그러나 첫 번째 참조($B$2)만 잠그기 때문에 마지막 상대 참조(B2)는 수식이 복사된 행에 따라 변경됩니다. 따라서 7행의 경우 범위가 $B$2:B7로 확장되고 B7의 값이 위의 각 셀과 비교됩니다.
따라서 모든 1번째 발생에 대해 COUNTIF는 1을 반환합니다. 수식 끝에서 1을 빼면 원래 순위가 복원됩니다.
두 번째 발생의 경우 COUNTIF는 2를 반환합니다. 2을 빼면 순위가 1포인트씩 증가하여 중복을 방지할 수 있습니다. 동일한 값이 1번 발생하는 경우 COUNTIF()-3은 순위에 1를 추가하는 식입니다.
Excel RANK 관계를 끊는 대체 솔루션
Excel에서 숫자의 순위를 고유하게 지정하는 또 다른 방법은 두 개의 COUNTIF 함수를 더하는 것입니다.
- 첫 번째 함수는 각각 내림차순 또는 오름차순으로 순위를 매기는지 여부에 따라 순위를 매길 숫자보다 크거나 작은 값의 수를 결정합니다.
- 두 번째 함수(위의 예에서와 같이 "확장 범위"$B$2:B2 포함)는 숫자와 동일한 값의 수를 가져옵니다.
예를 들어 가장 높은 숫자에서 가장 낮은 숫자로 고유하게 순위를 매기려면 다음 공식을 사용합니다.
=COUNTIF($B$2:$B$7,">"&$B2)+COUNTIF($B$2:B2,B2)
아래 스크린샷에서 볼 수 있듯이 타이브레이크가 성공적으로 해결되고 각 학생에게 고유한 순위가 할당됩니다.
여러 기준에 따라 Excel에서 순위 지정
앞의 예는 Excel RANK 타이 브레이크 상황에 대한 두 가지 작업 솔루션을 보여주었습니다. 그러나 동일한 숫자가 목록에서의 위치만을 기준으로 다르게 순위가 매겨지는 것은 불공평해 보일 수 있습니다. 순위를 높이려면 동점일 경우 고려해야 할 기준을 하나 더 추가할 수 있습니다.
샘플 데이터 세트에서 C 열에 총점을 추가하고 다음과 같이 순위를 계산해 보겠습니다.
- 첫째, 수학 점수 (주요 기준)로 순위를 매기십시오.
- 동점일 경우 총점(2차 기준)으로 나눕니다.
이를 위해 일반 RANK/RANK를 사용합니다. EQ 공식을 사용하여 순위를 찾고 COUNTIFS 함수를 사용하여 동점을 깰 수 있습니다.
=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,">"&$C2)
위의 예와 비교할 때 이 순위 공식은 더 객관적입니다. Timothy는 그의 총점이 Julia보다 높기 때문에 2위를 차지했습니다.
이 공식의 작동 원리
수식의 RANK 부분은 분명하며 COUNTIFS 함수는 다음을 수행합니다.
- 첫 번째 criteria_range/기준 쌍($B$2:$B$7,$B 2)은 순위를 매기는 값의 발생 횟수를 계산합니다. 절대 참조로 범위를 수정하지만 수식이 각 행의 값을 개별적으로 확인하도록 기준의 행($B 2)을 잠그지 않습니다.
- 두 번째 criteria_range/기준 쌍($C$2:$C$7,">"&$C 2)은 순위가 매겨지는 값의 총 점수보다 큰 총 점수 수를 찾습니다.
COUNTIFS는 AND 논리와 함께 작동하기 때문에, 즉 지정된 조건을 모두 충족하는 셀만 계산하기 때문에 동일한 수학 점수를 가진 다른 학생이 더 높은 총점을 갖지 못하기 때문에 Timothy에 대해 0을 반환합니다. 따라서 디모데의 순위는 RANK로 돌아왔습니다. EQ는 변경되지 않습니다. Julia의 경우 COUNTIFS 함수는 수학 점수가 같은 한 학생의 총점이 더 높기 때문에 1을 반환하므로 순위 번호가 1씩 증가합니다. 수학 점수가 같고 총점이 Timothy와 Julia보다 낮은 학생이 한 명 더 있으면 순위가 2씩 올라가는 식입니다.
여러 기준으로 숫자의 순위를 매기는 대체 솔루션
RANK 또는 RANK 대신. EQ 함수를 사용하는 경우 COUNTIF를 사용하여 주요 기준을 확인하고 COUNTIFS 또는 SUMPRODUCT를 사용하여 타이 브레이크를 해결할 수 있습니다.
=COUNTIF($B$2:$B$7,">"&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,">"&$C2)+1
=COUNTIF($B$2:$B$7,">"&B2)+SUMPRODUCT(--($C$2:$C$7=C2),--($B$2:$B$7>B2))+1
이 공식의 결과는 위에 표시된 것과 정확히 동일합니다.
Excel에서 백분위 수 순위를 계산하는 방법
통계에서 백분위수(또는 백분위수)는 주어진 데이터 세트에서 특정 비율의 값이 속하는 값입니다. 예를 들어, 학생의 70%가 시험 점수와 같거나 낮으면 백분위수 순위는 70입니다.
Excel에서 백분위수 순위를 얻으려면 RANK 또는 RANK를 사용합니다. EQ 함수를 0차수가 아닌 인수를 사용하여 숫자의 순위를 가장 작은 것부터 가장 큰 것 순으로 순위를 매긴 다음 순위를 숫자 개수로 나눕니다.
따라서 일반 Excel 백분위수 순위 공식은 다음과 같습니다.
학생들의 백분위수 순위를 계산하기 위해 공식은 다음과 같은 모양을 취합니다.
=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)
결과가 올바르게 표시되도록 하려면 백분율 형식을 수식 셀로 설정해야 합니다.
인접하지 않은 셀에서 숫자의 순위를 매기는 방법
인접하지 않은 셀의 순위를 매겨야 하는 경우 해당 셀을 Excel Rank 수식의 ref 인수에 참조 공용 구조체 형식으로 직접 제공하여 $ 기호로 참조를 잠급니다.
예를 들어:
=RANK(B2,($B$2,$B$4,$B$6))
순위가 지정되지 않은 셀의 오류를 방지하려면 다음과 같이 IFERROR 함수에서 RANK를 래핑합니다.
=IFERROR(RANK(B2,($B$2,$B$4,$B$6)), "")
중복 번호에도 순위가 지정되지만 B5 셀은 수식에 포함되지 않습니다.
인접하지 않은 여러 셀의 순위를 매겨야 하는 경우 위의 수식이 너무 길어질 수 있습니다. 이 경우 솔루션은 명명된 범위를 정의하고 수식에서 해당 이름을 참조하는 것입니다.
=IFERROR(RANK(B2,range), "")
Excel에서 그룹별로 순위를 매기는 방법
일종의 데이터 구조로 구성된 항목으로 작업할 때 데이터는 다양한 그룹에 속할 수 있으며 각 그룹 내의 숫자에 개별적으로 순위를 매길 수 있습니다. Excel RANK 함수는 이 문제를 해결할 수 없으므로 더 복잡한 SUMPRODUCT 공식을 사용합니다.
내림차순으로 그룹별 순위:
=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1
오름차순으로 그룹별 순위:
=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1
- A2 : A7은 번호에 할당 된 그룹입니다.
- C2:C7은 순위를 매길 숫자입니다.
이 예에서는 첫 번째 공식을 사용하여 각 그룹의 숫자를 가장 큰 것부터 가장 작은 것 순으로 순위를 매깁니다.
기본적으로 공식은 2가지 조건을 평가합니다.
- 먼저 그룹(A2=$A$2:$A$7)을 확인합니다. 이 부분은 범위 요소가 A2와 동일한 그룹에 속하는지 여부에 따라 TRUE 및 FALSE 배열을 반환합니다.
- 둘째, 점수를 확인합니다. 가장 큰 값에서 가장 작은 값(내림차순)으로 값의 순위를 매기려면 조건(C2<$C$2:$C$11)을 사용하여 C2보다 크거나 같은 셀에 대해 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.
Microsoft Excel 용어에서 TRUE = 1 및 FALSE = 0이므로 두 배열을 곱하면 1과 0의 배열이 제공되며 여기서 1은 두 조건이 모두 충족되는 행에 대해서만 반환됩니다.
그런 다음 SUMPRODUCT는 1과 0의 배열 요소를 더하므로 각 그룹에서 가장 큰 숫자에 대해 0을 반환합니다. 그리고 결과에 1을 더하여 1로 순위를 매기기 시작합니다.
그룹 내 숫자를 가장 작은 값에서 가장 큰 값(오름차순)으로 순위를 매기는 공식은 동일한 논리로 작동합니다. SUM PRODUCT는 특정 그룹에서 두 번째 조건(C2>$C$2:$C$7)을 충족하는 숫자가 없기 때문에 해당 그룹에서 가장 작은 숫자에 대해 0을 반환합니다. 다시 공식 결과에 1을 추가하여 0 순위를 1 순위로 바꿉니다.
SUM PRODUCT 대신 SUM 함수를 사용하여 배열 요소를 추가할 수 있습니다.
그러나 이를 위해서는 Ctrl + Shift + Enter를 통해 완료된 배열 공식을 사용해야 합니다.
예를 들어:
=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1
양수와 음수를 구분하여 순위를 매기는 방법
숫자 목록에 양수 값과 음수 값이 모두 포함되어 있으면 Excel RANK 함수는 즉시 모든 값의 순위를 매깁니다. 하지만 양수와 음수를 따로 순위를 매기고 싶다면 어떻게 해야 할까요?
A2에서 A10 셀의 숫자가 있는 경우 다음 수식 중 하나를 사용하여 양수 및 음수 값에 대한 개별 순위를 가져옵니다.
양수 내림차순 순위 지정:
=IF($A2>0,COUNTIF($A$2:$A$10,">"&A2)+1,"")
오름차순 양수 순위 지정:
=IF($A2>0,COUNTIF($A$2:$A$10,">0")-COUNTIF($A$2:$A$10,">"&$A2),"")
음수를 내림차순으로 순위를 매깁니다.
=IF($A2<0,COUNTIF($A$2:$A$10,"<0")-COUNTIF($A$2:$A$10,"<"&$A2),"")
음수 오름차순 순위 지정:
=IF($A2<0,COUNTIF($A$2:$A$10,"<"&$A2)+1,"")
결과는 다음과 유사합니다.
먼저 양수의 순위를 내림차순으로 분류하는 공식을 분석해 보겠습니다.
- IF 함수의 논리 테스트에서 숫자가 0보다 큰지 여부를 확인합니다.
- 숫자가 0보다 크면 COUNTIF 함수는 순위가 매겨지는 숫자보다 높은 값의 개수를 반환합니다.이 예에서 A2에는 두 번째로 높은 양수가 포함되며 COUNTIF는 2을 반환하므로 이 양수보다 큰 숫자가 하나만 있습니다. 1이 아닌 1로 순위를 시작하기 위해 수식 결과에 0을 더하여 A1에 대해 2의 순위를 반환합니다.
- 숫자가 0보다 크면 수식은 빈 문자열("")을 반환합니다.
양수를 오름차순으로 순위를 매기는 공식은 약간 다르게 작동합니다.
숫자가 0보다 크면 첫 번째 COUNTIF는 데이터 세트에 있는 양수의 총 개수를 가져오고 두 번째 COUNTIF는 해당 숫자보다 큰 값의 수를 찾습니다. 그런 다음 전자에서 후자를 빼고 원하는 순위를 얻습니다. 이 예제에는 5개의 양수 값이 있으며 그 중 1개는 A2보다 큽니다. 따라서 1에서 5를 빼면 A4에 대해 2의 순위가 됩니다.
음수의 순위를 매기는 공식은 유사한 논리를 기반으로 합니다.
0 값을 무시하고 Excel에서 데이터 순위를 매기는 방법
순위 공식은 양수, 음수 및 0의 모든 숫자를 처리하는 Excel입니다. 그러나 어떤 경우에는 0 값을 무시하는 데이터를 가진 셀의 순위를 매기려고 합니다. 웹에서 이 작업에 대한 몇 가지 가능한 해결책을 찾을 수 있지만, 제 생각에 Excel RANKIF 공식은 가장 보편적인 것입니다:
0을 무시하고 내림차순으로 순위를 매기는 숫자:
=IF($B2=0,"",IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))
0을 무시하고 오름차순으로 순위를 매기는 숫자:
=IF($B2=0,"",IF($B2>0,RANK($B2,$B$2:$B$10,1) - COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))
여기서 B2:B10은 순위를 매길 숫자 범위입니다.
이 공식의 가장 좋은 점은 양수와 음수 모두에 대해 아름답게 작동하여 순위에서 0 값을 제외한다는 것입니다.
이 공식의 작동
원리 언뜻 보기에 공식이 약간 까다로워 보일 수 있습니다. 자세히 살펴보면 논리는 매우 간단합니다.
Excel RANK IF 수식이 0을 무시하고 가장 큰 숫자에서 가장 작은 숫자로 순위를 매기는 방법은 다음과 같습니다.
- 첫 번째 IF는 숫자가 0인지 확인하고, <>이면 빈 문자열을 반환합니다.IF($B 2=0,"", ...)
- 숫자가 0이 아닌 경우 두 번째 IF는 <>보다 큰지 확인하고, <>보다 크면 일반 RANK/RANK입니다. EQ 기능은 순위를 계산합니다.IF($B 2>0,순위($B 2,$B$2:$B$10),...)
- 숫자가 0보다 작으면 0 카운트로 순위를 조정합니다. 이 예제에서는 양수 4개와 0 2개가 있습니다. 따라서 B10에서 음수가 가장 큰 경우 Excel RANK 공식은 7을 반환합니다. 하지만 우리는 0을 건너뛰기 때문에 순위를 2점 조정해야 합니다. 이를 위해 순위에서 0의 수를 뺍니다: RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))
절대값으로 Excel에서 순위를 계산하는 방법
양수 및 음수 값 목록을 처리할 때 부호를 무시하고 절대값으로 숫자의 순위를 매겨야 할 필요가 있을 수 있습니다.
이 작업은 아래 공식 중 하나를 사용하여 수행할 수 있으며, 그 중심에는 숫자의 절대값을 반환하는 ABS 함수가 있습니다.
순위 ABS 내림차순:
=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>""))+1
순위 ABS 오름차순:
=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>""))+1
결과적으로 음수는 양수인 것처럼 순위가 매겨집니다.
N개의 가장 큰 값 또는 가장 작은 값을 얻는 방법
순위가 아닌 가장 크거나 작은 값의 실제 N 수를 얻으려면 각각 LARGE 또는 SMALL 함수를 사용하십시오.
예를 들어, 다음 공식을 사용하여 학생들의 상위 3개 점수를 얻을 수 있습니다.
=LARGE($B$2:$B$7, $D3)
여기서 B2:B7은 점수 목록이고 D3은 원하는 순위입니다.
또한 INDEX MATCH 공식을 사용하여 학생의 이름을 검색할 수 있습니다(상위 3개 항목에 중복 점수가 없는 경우).
=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))
마찬가지로 SMALL 함수를 사용하여 하위 3개 값을 가져올 수 있습니다.
=SMALL($B$2:$B$7, $D3)
'워드프로세서 > 엑셀' 카테고리의 다른 글
| 엑셀 나누기 #DIV/0 처리 (0) | 2023.06.14 |
|---|---|
| 엑셀 Lookup함수 - 조회 (0) | 2023.06.13 |
| 엑셀 알파벳순 정렬 (0) | 2023.06.13 |
| 엑셀 파레토 차트(Pareto chart) (0) | 2023.06.13 |
| 엑셀 분산 계산하기 (0) | 2023.06.13 |