Index와 Aggregate 함수의 결합

 

아래와 같이 성명과 점수가 있을 때 점수를 내림차순으로 표시하고, 점수에 해당하는 성명을 추출하는 것을 수식을 통해 해 보겠습니다.

 

성적에 해당하는 성명 구하기.xlsx

0.01MB

 

 

 

1. 정렬

데이터탭에서 정렬 명령을 누른 후

정렬 기준으로 점수를 선택하고, 정렬 순서를 내림차순으로 지정하고 확인 버튼을 누르면 

 

 

쉽게 구할 수 있습니다.

 

 

2. index와 Aggregate 함수 이용하기

먼저 Ctrl + Z키를 눌 정렬하기 이전 상태로 되돌립니다.

 

가. 필요성

Vlookup함수로 찾기 어려운 일치하는 값을 찾을 때 index와 match함수를 이용하는데,

match함수는 일치하는 값 또는 유사한 값 하나만의 위치를 찾아주기 때문에

위와 같이 동일한 값이 여러 개 있을 때는 적용하기 어려운 점이 있습니다.

 

나. 논리

index 함수의 구문은 INDEX(array, row_num, [column_num])으로

array를 지정한 다음 row_num을 지정하는데,

match함수와 결합할 때처럼 row_num을 aggregate 함수롤 지정하는 것입니다.

 

다. 점수 내림차순 정렬

일단 내림차순으로 정렬한 다음 행 수를 구해야 하므로

먼저 점수를 내림차순으로 정렬하는 수식을 Aggregate 함수를 이용해 만들면

 

=AGGREGATE(14,2,$B$2:$B$11,ROW(1:10))가 됩니다.

14는 Large, 다시 말해 점수는 큰 것부터 구하는 것이며

2는 오류값을 무사하라는 것이고,

$B$2:$B$11은 점수가 있는 범위입니다.

ROW(1:10)는 첫 번째부터 10번째까지 순서대로 가져오라는 의미입니다.

 

라. 점수에 해당하는 성명 구하기

Aggregate 함수는 숫자만을 반환하기 때문에

이름을 바로 구할 수는 없고 index함수와 결합하는 것인데,

Aggregate 함수를 사용할 때도 점수를 먼저 내림차순으로 정렬하고(14-Large),

점수가 일치할 때는 행 수가 적은 것부터 구해야(15-Small) 하기 때문에

Aggregate 함수를 두 번 사용해야 합니다.

 

또한 작은 값부터 표시할 때는 False일 경우 0이 되기 때문에 1/(조건식)*(구하는 값) 식으로 False일 때 #DIV/0!(0으로 나눌 수 없음) 에러가 발생하도록 해야 합니다.

 

완성된 수식은

=INDEX($A$2:$A$11,AGGREGATE(15,2,1/($B$2:$B$11=AGGREGATE(14,2,$B$2:$B$11,ROW()-1))*(ROW($B$2:$B$11)-1),COUNTIF($D$2:D2,D2))) 입니다.

 

(1) index 함수 수식

하나씩 설명하면

=INDEX($A$2:$A$11에서 $A$2:$A$11는 성명이 있는 범위이고, row_num은 Aggregate 이하에서 구합니다.

 

(2) row_num 구하기

AGGREGATE(15,2,1/($B$2:$B$11=AGGREGATE(14,2,$B$2:$B$11,ROW()-1))*(ROW($B$2:$B$11)-1),COUNTIF($D$2:D2,D2))가 row_num을 구하는 부분인데

 

Aggregate 함수를 두 번 사용했습니다.

 

(2-1) 큰 점수부터 내림차순으로 점수 구하기

점수를 아래 식을 이용해 먼저 구합니다.

AGGREGATE(14,2,$B$2:$B$11,ROW()-1))

'다'에서 구한 식과 다른 점은 ROW(1:10)으로 한꺼번에 지정하지 않고 (ROW()-1)로 행 수에서 1을 뺀 값, 다시 말해 하나만 지정했다는 것입니다.

 

ROW(1:10)으로 지정하면 가장 적은 점수에 해당하는 연개소문이 반환되는 문제점이 있습니다.

 

AGGREGATE(14,2,$B$2:$B$11,ROW()-1))를 마우스로 선택한 다음 F9키를 누르면 가장 큰 수인 100이 구해집니다.

 

(2-2) 점수가 일치하는 행수 구하기

AGGREGATE(15,2,1/($B$2:$B$11=AGGREGATE(14,2,$B$2:$B$11,ROW()-1))*(ROW($B$2:$B$11)-1),COUNTIF($D$2:D2,D2))에서

AGGREGATE(14,2,$B$2:$B$11,ROW()-1)부분을 100으로 바꾸면

 

AGGREGATE(15,2,1/($B$2:$B$11=100)*(ROW($B$2:$B$11)-1),COUNTIF($D$2:D2,D2))가 됩니다.

 

1/($B$2:$B$11=100)는 점수가 100인 경우는 1이 반환되고, 아니면 #DIV/0! 에러가 발생합니다.

 

(ROW($B$2:$B$11)-1)에서 ROW($B$2:$B$11)는 $B$2:$B$11의 행수를 반환하므로 2~11이 되며, 여기서 1을 빼면 1부터 10이 됩니다.

 

1/($B$2:$B$11=100)*(ROW($B$2:$B$11)-1)는 100점인 것의 행수를 반환합니다.

 

COUNTIF($D$2:D2,D2)는 같은 점수의 개수를 구하는 것으로 동일 점수가 있을 경우 해당 개수를 반환합니다. 다시 말해 100점은 1이며, 96점의 첫 번째 점수는 1, 두 번째 96점은 2가 됩니다.

 

따라서, 아래 수식의 결과는

AGGREGATE(15,2,1/($B$2:$B$11=AGGREGATE(14,2,$B$2:$B$11,ROW()-1))*(ROW($B$2:$B$11)-1),COUNTIF($D$2:D2,D2))

 

100점일 경우 첫 번째 행수를 반환하므로 5가 되고,

96점일 경우는 첫 번째는 2, 두 번째는 10이 반환됩니다.

 

(3) 행 수에 해당하는 성명 구하기

100점인 경우 row_num이 5가 되므로

=INDEX($A$2:$A$11,5)를 하면 이승만이 구해집니다.

 

96점인 경우 행수 3과 10이 구해지므로

각각 이성계와 임꺽정이 구해집니다.

 

 

3. 수식 - 수식 계산 명령 이용하기

 E6셀을 선택한 상태에서 수식 탭의 수식 계산 명령을 누르면

 

단계적으로 계산과정을 확인할 수 있습니다.

첫 번째로 ROW()를 구하는데, 아래 계산 버튼을 누르면

 

6이 구해지고, 6-1을 계산합니다. 또한 점수 범위가 배열 형태로 보입니다.

 

이런 식으로 계속 계산 버튼을 누르면 점수범위에서 96점과 일치하는지를 판단하고,

 

숫자가 96점인 경우는 1이 되고, 나머지는 #DIV/0! 에러가 발생하는 단계도 표시됩니다.

 

(ROW($B$2:$B$11)-1)과 곱해져서 3과 10이 구해집니다.

 

그리고, 아래는 Aggregate 함수의 최종 값을 구하는 단계인데, 3과 10중 2번째로 작은 값을 구하므로 10이 반환됩니다.

 

따라서, INDEX($A$2:$A$11,10)은 

 

임꺽정이 되는 것입니다.

 

 

성적에 해당하는 성명 구하기.xlsx

0.01MB