조건에 맞는 값을 내림차순 또는 오름차순으로 찾기(2)

다. 조건에 맞는 데이터를 큰 값부터 표시하기

(1) 방법 1

j1셀에 인구수 내림차순이라고 씁니다.

 

그리고, j2셀에 Aggregate 함수를 이용해 수식을 작성합니다.

 

Aggregate 함수의 의미와 구문에 대해서는 이 글을 참고 바랍니다.

 

(1) Aggregate 함수의 첫 번째 인수

내림 차순이므로 첫 번째 인수로는 14(Large)를 선택하고,

 

(2) Aggregate 함수의 두 번째 인수

두 번째 인수로는 오류값을 무시하는 2를 선택합니다.

 

(3) Aggregate 함수의 세 번째 인수

세 번째 인수에 조건을 입력하는데

F, G열과 인구수가 있는 E열을 살펴보면

 

서울특별시인 경우 F열은 '서울특별시'여야 하고,

G열은 '공백이면 안되며',

조건에 맞는 'E열을 값'을 가져와야 합니다.

 

배열 수식에서 두 개 조건이 모두 만족해야 하는 And 조건일 때는 *를 사용하므로

=AGGREGATE(14,2,(까지 입력한 후 

F2셀을 클릭하고, Shift + Ctrl + ↓키를 눌러 맨 아래까지 범위를 지정한 후 F4키를 눌러 절대 참조 형식으로 지정합니다.

=AGGREGATE(14,2,($F$2:$F$293

 

그리고 =을 입력한 다음 행정구역명(대)가 있는 i2셀을 지정하고 F4키를 눌러 절대 참조 형식으로 지정한 후 괄호를 닫습니다.

=AGGREGATE(14,2,($F$2:$F$293=$I$2)

 

이제 G열이 공백이면 안되므로

*를 입력한 다음 (를 열고 G2셀을 마우스로 클릭한 다음 Shift + Ctrl + ↓키를 눌러 맨 아래까지 범위를 지정한 후 F4키를 눌러 절대 참조 형식으로 지정합니다.

=AGGREGATE(14,2,($F$2:$F$293=$I$2)*($G$2:$G$293

 

그리고, 빈 셀이면 안되므로 <>(같지 않음 비교 연산자) 다음에 " "(공백 한 칸)을 입력하고 괄호를 닫습니다.

=AGGREGATE(14,2,($F$2:$F$293=$I$2)*($G$2:$G$293<>"")

 

이제 E열의 인구수를 가져오면 되므로

*(를 입력하고 E2셀부터 E293셀까지를 절대 참조 형식으로 입력하고 괄호를 닫습니다.

=AGGREGATE(14,2,($F$2:$F$293=$I$2)*($G$2:$G$293<>"")*($E$2:$E$293)

 

(4) Aggregate 함수의 네 번째 인수

네 번째 인수는 순번을 지정하는 것이므로 1을 지정하면 되는데,

아래로 복사할 때 하나씩 증가하도록

행 수를 구하는 row함수를 이용하는데, 2행이 1이 되어야 하므로 row()-1로 입력합니다.

 

그리고, 괄호를 닫고 엔터키를 누르면(배열 함수이므로 Ctrl + Shift + Enter키를 누를 필요 없음)

j2셀에 0이 표시되는데, i2셀이 선택되지 않아서 그렇습니다.

 

i2셀을 선택한 다음 Alt + ↓키를 누르거나,

i2셀 오른쪽의 콤보 상자 버튼을 누른 후 서울특별시를 선택하면

가장 큰 값이 286569가 구해집니다. 

 

홈 탭에서 ,(쉼표)를 눌러 천 단위 구분 기호를 삽입합니다.

 

그리고, 다섯 번째까지 인구수를 표시하도록 j2셀의 채우기 핸들을 j6셀까지 끕니다.

 

다른 행정구역을 선택하면 순서대로 5개가 표시됩니다.

 

(2) 방법 2

k값을 하나씩 지정할 필요없이 row(1:5)로 지정하면

배열 수식으로 작용하여 한번만 수식을 입력하더라도 5개가 한꺼번에 표시됩니다.

 

그러나, Microsoft 365 버전보다 낮다면

5개 셀을 선택하고, 위 수식을 입력한 다음 Shift + Ctrl + Enter키를 눌러야 합니다.

 

조건에 맞는 데이터 내림차순 또는 오름차순으로 구하기(2).xlsx

0.03MB