Aggregate(옵션 적용 집계) 함수(2) - Vlookup 함수의 한계 해결

Aggregate(옵션 적용 집계) 함수(1)에서는 참조형(Sum, Subtotal, Aggregate 등)에 대해서만 살펴봤는데,
이번에는 배열형에 대해 알아보겠습니다.

배열형이 적용되는 함수는 LARGE, SMALL, PERCENTILE.INC(, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC인데, Large, Small함수에 대해서만 다루겠습니다.

Vlookup함수는 범위에서 일치하는 첫 번째 값만 처리가능한데, Large 또는 Small함수는 같은 값이 여러 개일 경우라도 순차적으로 값을 반환해주는 장점이 있습니다.

aggregate2.xlsx

0.01MB

 

1. Vlookup함수의 한계

위와 같은 경우 같은 판매일이 한 개인 경우는 그에 해당하는 지점명과 판매금액을 구할 수 있으나,
같은 판매일이 두 개이상인 경우에도 첫번째 판매일에 대한 지점명고 판매금액만을 반환합니다.

G2셀의 수식은 =VLOOKUP($F2,$B$2:$D$16,COLUMN(B2),0)이고,
H2셀의 수식은 G2셀의 채우기 핸들을 오른쪽으로 끌면 =VLOOKUP($F2,$B$2:$D$16,COLUMN(C2),0)으로

① 첫 번째 인수인 찾는 값의 열은 F열로 고정이어야 하는데, 행은 바뀌어야 하므로 F열만 고정하는 혼합참조형식으로 입력했고,
② 두 번째 인순인 찾을 범위는 B2에서 D16까지로 고정이어야 하므로 절대 참조 형식으로 입력했으며,
③ 세 번째 인수인 열의 순번은 오른쪽으로 이동할 경우 1이 증가해야 하므로 Column함수를 이용해서 열 번호를 반환하도록 column(b2)라고 입력했는데, 따라서 열 B의 숫자 2가 반환됩니다. 오른쪽으로 끌면 column(c2)가 되므로 C열 3이 반환됩니다.
이때 행은 중요하지 않지만 오른쪽으로 이동하거나 내려갈 때 자동적으로 행과 열이 변하도록 상대 참조 형식으로 입력했습니다.
④ 네 번째 인수는 정확한 값을 찾도록 0(False)을 입력했습니다.

위 표를 보면 2014-11-06은 하나이므로 문제가 없는데,
2016-03-16은 2개인데, 첫 번째로 일치하는 B지점, 690,000만 반환하고, 두 번째로 일치하는 C지점, 309,000은 구할 수 없습니다.

이때 사용할 수 있는 것이 Aggregate함수이며, 함수는 Large, Small 2개 모두 사용할 수 있는데, Large는 큰 것부터, Small은 작은 것부터 반환합니다.

배열행에서 중요한 것은 조건에 부합하지 않을 때 에러를 반환해야 한다는 것입니다.
다시 말해 판매일을 기준으로 검색한다고 하면 판매일이 일치하는 경우는 True, 다른 경우는 False를 반환하도록 하는데, False는 에러는 아니므로 1/(수식)을 사용해서 False인 경우에는 에러를 발생하도록 해야 합니다.
=1/false 하면 false는 0이기 때문에 #DIV/0!(0으로 나눌 수 없음) 에러가 발생합니다. 그렇지만 =1/true는 true가 1이기 때문에 1을 반환합니다.

2. Aggregate 함수 사용하기

가. 반환되는 값이 숫자인 경우

반환되는 값이 숫자인 경우와 문자인 경우 처리 방법이 다릅니다.
금액을 기준으로 큰 값부터 또는 작은 값부터 표시해 보겠습니다.

(1) Large 함수 사용

(가) 같은 날짜가 한 개인 경우
=ag까지 입력하면 AGGREGATE함수가 표시되므로 탭키를 누릅니다. 그러면 =AGGREGATE(까지 입력됩니다.

이제 AGGREGATE함수에서 사용 가능한 함수의 목록이 표시되는데 아래 화살표키 또는 PgDn키를 눌러 내려간 후 Large함수에서 탭 키를 누릅니다.

,(쉼표)를 누르면 옵션이 표시되는데 오류 값이 들어 있는 2, 3, 6, 7중 하나를 선택하면 됩니다. 2를 선택하겠습니다.

그리고, 쉼표를 입력하면 인수 입력 방식이 2줄로 표시되는데 첫 번째 줄 array, k가 배열형입니다.

조건에 맞지 않는 경우 오류를 발생시켜야 하므로 1/(를 입력한 후

날짜에 해당하는 B2에서 B16을 선택하고 F4키를 누릅니다.

조건은 날짜가 F2셀과 같은 것이니까
=f2라고 입력하고(마우스로 F2셀을 클릭해도 됨), 괄호를 닫은 다음

금액에 해당하는 값을 반환받기 위해 * 기호를 입력한 후 괄호를 열고 D2에서 D16까지 선택한 후 F4키를 누릅니다.

그리고 괄호를 닫고 가장 큰 값을 구하기 위해 쉼표(,)를 입력한 후 1이라고 입력하고 괄호를 닫습니다.

그리고, Enter키를 누르면 516000이 구해집니다.

(나) 같은 날짜가 2개 이상인 경우
H2셀의 채우기 핸들을 아래로 끕니다.
H3셀과 H4셀의 값이 같은데 그것은 k의 값이 1로 같아서 그런 것입니다.

따라서, H4셀의 k값을 2로 바꾸고

엔터 키를 누르면 두 번째로 큰 값 309,000이 구해집니다.

(2) Small 함수 사용
Small함수를 사용할 때는 Small함수의 번호가 15이므로 14를 15로만 바꾸면 됩니다.
H2셀부터 H4셀까지 선택한 후

Ctrl + H(바꾸기) 키를 누른 다음

찾을 내용에 14, 바꿀 내용에 15를 입력하고 모두 바꾸기 버튼을 누릅니다. 그러면 '3개 항목이 바뀌었습니다'란 메시지가 표시되는데, H3셀과 H4셀을 보면 작은 값부터 표시하는 것으로 변경되었습니다.

확인 버튼을 누르고, 닫기 버튼을 누릅니다. Aggregate 함수의 첫 번째 인수가 15입니다.

(3) 지점명 별로 판매금액을 큰 값부터 표시하기
F9셀과 F10셀을 모두 A로 변경합니다.

그리고, h2셀을 복사해서 G8셀부터 G10셀까지 선택한 후 Ctrl + V키를 누르거나, 마우스 오른쪽 버튼을 누른 후 붙여넣기 아이콘을 누릅니다.

그러면 G8셀의 수식이 =AGGREGATE(15,2,1/($B$2:$B$16=E8)*($D$2:$D$16),1)인데 조건이 맞지 않아 #NUM! 에러가 발생합니다.

G8셀을 클릭한 다음, 지점명은 B2에서 B16이 아니라 C2에서 C16에 있으므로 이것으로 바꾸고,
찾는 지점명은 F8셀에 있으므로 E8을 F8로 수정합니다.

그리고 엔터키를 누르면 A지점에 해당하는 판매금액 중 가장 작은 값인 288,000이 구해집니다.

이제 수식을 아래로 복사해야 하는데, 채우기 핸들을 그대로 끌면 마지막 인수 k의 값이 1로 고정되므로,
아래로 내려갈 때 자동으로 숫자가 2, 3으로 바뀌도록 하려면
row함수를 이용하면 됩니다.
8행이 1이어야 하며, row() 함수는 수식이 들어가 있는 G8셀의 행값인 8을 반환하므로 -7을 해야 1이 됩니다.

따라서, G8셀의 수식에서 1을 row()-7로 수정하고 엔터키를 누릅니다. 마찬가지로 288,000이 구해집니다.

이제 G8셀의 채우기 핸들을 아래로 끌면 두 번째, 세 번째로 작은 판매금액이 표시됩니다.

이번에는 A지점이 모두 7개이므로 F10셀과 G10셀을 마우스로 끌어서 선택한 다음 G10셀의 채우기 핸들을 14행까지 끌면 A지점에 해당하는 판매금액이 작은 값부터 모두 표시됩니다.

aggregate2(완성).xlsx

0.01MB

문자로된 지점명을 구하는 것은 다음 편에서 알아보도록 하겠습니다.