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

라. 조건에 맞는 데이터를 작은 값부터 표시하기

(1) 가장 작은 값이 0으로 표시되는 문제

j2셀의 채우기 핸들을 오른쪽으로 끈 후

K2셀의 Aggregate 함수의 첫 번째 인수를 작은 값부터 표시하도록 15-Small로 바꾸고 엔터키를 누르면,

가장 작은 값이 0이 아닌데 0이 나옵니다.

 

따라서, 조건에 해당하는 부분을 마우스로 끌어서 선택 후

 

F9키를 눌러서 계산값을 확인하 조건에 맞지 않을 경우 0이 돼서 그렇습니다.

 

(2) 행정구역명(대)는 맞고 행정구역명(중)이 불일치할 때 문제

Esc키를 눌러 수식을 원래로 돌려놓고, 수식 맨 앞에 1/를 추가하고 엔터키를 누르면

될 줄 알았는데,

 

False일 경우 #DIV/0!로 표시되는데,

첫 번째 #DIV/0! 다음이 0입니다.

왜 그런가 하고 살펴보니 서울특별시이면서 구 명칭이 없을 때에 0으로 반환되는 문제입니다.

 

1/ 다음 조건 2개를 괄호로 감싸서 구 명칭이 없을 때에도 False로 #DIV/0! 에러가 발생하도록 해야 합니다.

수식을 수정하면 아래와 같고,

=AGGREGATE(15,2,1/(($F$2:$F$293=$I$2)*($G$2:$G$293<>" "))*($E$2:$E$293),ROW()-1)

 

결과가 잘 나옵니다.

 

K1셀에 '인구수 오름차순'이라고 입력하고, K2셀의 채우기 핸들을 K6까지 끕니다.

 

(3) 인구수가 0일 때 문제

그런데 인천광역시를 선택하면 위 세 줄이 0으로 표시됩니다.

 

인천광역시의 인구수를 확인해 보니 출장소 세 군데의 인구수가 0입니다.

 

다시 K2셀의 수식에 인구수가 0이 아니어야 한다는 것을 조건으로 추가하면

=AGGREGATE(15,2,1/(($F$2:$F$293=$I$2)*($G$2:$G$293<>" ")*($E$2:$E$293>0))*($E$2:$E$293),ROW()-1)

 

이제 K2셀의 채우기 핸들을 더블 클릭하면 순서대로 인구수가 표시됩니다.

 

(4) 전국일 경우의 문제

전국을 선택하니 내림차순일 때는 0으로 문제가 없는데, 오름차순일 때는 #NUM! 에러가 발생합니다.

따라서, 수식 맨 앞에 iferror함수를 추가하고, 맨 나중에 0이라고 추가해야 합니다.

=IFERROR(AGGREGATE(15,2,1/(($F$2:$F$293=$I$2)*($G$2:$G$293<>" ")*($E$2:$E$293>0))*($E$2:$E$293),ROW()-1),0)

 

(배열 수식 적용)

Microsoft 365라면

=IFERROR(AGGREGATE(15,2,1/(($F$2:$F$293=$I$2)*($G$2:$G$293<>" ")*($E$2:$E$293>0))*($E$2:$E$293),ROW(1:5)),0)라고 첫번째부터 다섯번째까지의 값을 한꺼번에 가져오도록 k값을 row(1:5)로 수정하고 엔터키를 누르면 5개의 값이 한꺼번에 구해지고, 파란색 선으로 테두리가 그려집니다.

 

Microsoft 365 이전 버전이라면

먼저 5개 셀을 선택하고, 아래 수식을 입력한 후

=IFERROR(AGGREGATE(15,2,1/(($F$2:$F$293=$I$2)*($G$2:$G$293<>" ")*($E$2:$E$293>0))*($E$2:$E$293),ROW(1:5)),0)

Shift + Ctrl + Enter키를 눌러 입력합니다.

 

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

0.03MB