Aggregate(옵션 적용 집계) 함수(5) - 조건이 여러 개인 경우

Aggregate 함수 사용시 두가지 이상 조건을 만족하는 조건을 설정하는 방법을 알아보겠습니다.

 

조건이 1개인 경우는 1/(조건)*(원하는 값 범위)로 했는데,

2개이상인 경우는 1/(((조건1)*(조건2)...)*(원하는 값 범위) 식으로 여러 개의 조건을 괄호로 감싸야 합니다.

 

aggregate5.xlsx

0.01MB

 

 

 

아래 왼쪽 데이터에서 오른쪽 판매일과 지점명이 일치하는 데이터를 찾아 크기순으로 나열해 보겠습니다.

 

 

1. 판매일 조건보다 작고 지점명이 일치하는 판매금액을 큰 값부터 표시하기

 

① H2셀에 커서를 놓고,

② =ag까지 입력하고 탭 키를 눌러 =AGGREGATE(까지 입력합니다.

③ 그리고, 함수명 중에 14번 Large를 아래 화살표키 또는 PgDn키를 눌러 이동한 후 탭키를 누릅니다.

 

④ ,를 누르면 옵션이 표시되는데 오류값 무시가 포함된 번호 아무거나 누릅니다. 2를 누르겠습니다.

 

⑤ 그리고, 다시 쉼표(,)를 누른 다음 1/((판매일 범위<=F2)*(지점명 범위=G2))*(판매금액 범위)를 입력합니다.

 

여기서 주의할 점은 판매일 범위와 지점명 범위는 변하면 안되므로 F4키를 눌러 절대 참조 형식으로 입력하고,

판매일 조건 또는 지점명 조건은 아래로 수식을 복사할 때 행에 해당하는 2가 변하면 안되므로 2에만 $표시를 붙여 혼합참조형식으로 입력해야 한다는 것입니다.

그리고, 괄호도 조건 2개를 감싸는 괄호가 하나 더 있어야 한다는 점도 주의해야 합니다.

 

그러면 아래와 같이 입력됩니다.

=AGGREGATE(14,2,1/(($B$2:$B$16<=F$2)*($C$2:$C$16=G$2))*($D$2:$D$16)

 

⑥ 쉼표를 입력하고 순번에 해당하는 k값을 입력해야 하는데 1을 만들려면 현재 행 수 2에서 1을 빼면 되므로

ROW()-1이라고 입력하면 됩니다.

⑦ 그리고 괄호를 닫은 다음 엔터 키를 누르면 판매일이 2015/12/31이하이고 지점명이 A인 것 중에서 가장 큰 값을 반환하므로 654,000이 구해집니다.

⑧ H2셀의 채우기 핸들을 H7셀가지 끌면 두 번째, 세 번째로 큰 값은 구해지는데, A 지점명이 3개이기 때문에 네 번째부터는 해당하는 값이 없어서 #NUM!(숫자 표시 오류)가 표시됩니다.

 

⑨ H5셀부터 H7셀까지 마우스로 끌어서 범위를 선택한 후 Delete키를 눌러 지웁니다.

 

 

 

2. 판매일이 판매일1과 판매일2 사이이고, 지점명이 지점명1 또는 지점명2인 것 중 작은 값부터 표시하기

 

And조건인 경우는 * 연산자로 두 가지이상 조건을 연결하고, Or조건인 경우는 + 연산자로 연결합니다.

 

따라서 수식은

1/(((판매일 범위 >= 판매일1) * (판매일 범위 <= 판매일2)) * ((지점명 범위=지점명1) + (지점명 범위=지점명2)))*(판매금액 범위)

가 됩니다.

 

이번에는 작은 값부터 표시할 것이므로 함수는 Small함수 15를 입력하고, k값은 현재 행이 13이기 때문에 -12를 해야 합니다.

 

그러면 최종 수식은 아래와 같은데

=AGGREGATE(15,2,1/((($B$2:$B$16>=F$13)*($B$2:$B$16<=G$13))*(($C$2:$C$16=H$13)+($C$2:$C$16=I$13)))*$D$2:$D$16,ROW()-12)

 

수식에서 끝에 있는 판매금액 범위는 괄호로 감싸지 않아도 정확한 값이 구해집니다.

 

3. 괄호를 잘 사용해야 하는 이유

 

가. 수식 오류 1

위 수식에서 값을 구할 범위를 제외한 조건은 괄호를 하나라도 없애면 이상한 값이 구해집니다.

아래는 ($B$2:$B$16>=F$13)에서 양쪽 괄호를 제거한 것입니다.

이렇게 하면 $B$2:$B$16>=F$13*($B$2:$B$16<=G$13)이기 때문에 *가 먼저 실행되고 그 다음 >=가 실행되기 때문입니다.

 

따라서, $B$2:$B$16>=F$13*($B$2:$B$16<=G$13)의 결과값을 확인하기 위해 아래와 같이 범위를 잡은 후 F9키를 누르면

 

{FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

라고, 

12번째 2019/04/29는 날짜 조건에 부합하지 않는대도 True라고 반환돼서 288,000이란 값이 반환된 것입니다.

 

나. 수식 오류 2

조건 여러 개를 감싸는 가장자리 괄호를 제거하면 0이 표시됩니다.

 

1/(($B$2:$B$16>=F$13)*($B$2:$B$16<=G$13))*(($C$2:$C$16=H$13)+($C$2:$C$16=I$13))*$D$2:$D$16까지 범위를 잡은 다음 F9키를 누르면

 

계산값이

{#DIV/0!;#DIV/0!;551000;607000;0;654000;905000;690000;0;751000;796000;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

로 5번째 데이터의 지점명이 C로서 지점명 조건에 부합하지 않아서 0이 반환되기 때문에 가장 작은 값으로 0이 구해진 것입니다.

 

따라서, 괄호를 잘 사용해야 합니다.

 

원래대로 수식을 복원한 후 J13셀의 채우기 핸들을 J20셀까지 끌면

조건에 맞는 판매금액이 작은 값부터 표시되는데, 8번째에 해당하는 작은 값이 없기 때문에 #NUM!에러가 발생합니다.

 

J20셀을 선택한 후 Delete키를 눌러 완성합니다.

aggregate5(완성).xlsx

0.01MB