Aggregate(옵션 적용 집계) 함수(3) - Index, Row, Code, Char 함수와 결합

aggregate2(완성).xlsx

0.01MB

 

 

 

나. 반환되는 값이 문자인 경우

 

(1) '1 * 문자'라서 에러 발생

H2셀의 채우기 핸들을 왼쪽으로 끌어 G2셀에 수식을 복사하면 #NUM!에러가 발생합니다.

 

G2셀의 수식을 살펴보면 =AGGREGATE(15,2,1/($B$2:$B$16=E2)*($D$2:$D$16),1)으로

E2가 F2여야 하고, 지점명이 C열에 있으므로 C2:C16이어야 하므로 수정합니다.

 

그래도 여전히 #NUM!에러가 발생합니다.

 

따라서, 1/부터 $C$16)까지 범위를 잡고 F9를 누르면

{#VALUE!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 라고 계산 결괏값이 표시됩니다.

 

첫번째가 true이므로 지점명이 나와야 하는데, *를 하다 보니 '1 * 문자'라서 #VALUE!(수식에 오류가 있음) 에러가 발생한 것이고, 나머지는 지점명이 일치하지 않아 #DIV/0!(0으로 나눌 수 없음)에러가 발생했습니다.

 

(2) 해결 방안 1

 

(가) 줄 수 구하기

*(곱하기) 다음에 문자가 아닌 숫자, 여기서는 줄 수를 나타내도록 하고, index함수를 이용해 일정한 범위내에서 줄 수에 해당하는 값을 반환하도록 해야 합니다.

 

따라서, 수식에서 곱하기 다음을 row함수를 이용해 표시하는데 2행부터 시작하므로 -1을 해야 원하는 줄 수가 구해집니다.

이에 해당하는 수식은 row(c2:c16)-1이 됩니다.

 

(나) index 함수 사용하기

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

배열을 입력하고, 줄 수와 열 수를 지정하는데 열 수는 선택 사항입니다.

 

배열은 범위를 포함하므로 ,지점명에 해당하는 C2셀에서 C16 셀까지 범위를 지정하고,

row_num은 Aggregate함수로 구하면 됩니다.

 

따라서, 전체 수식은

=INDEX($C$2:$C$16,AGGREGATE(15,2,1/($B$2:$B$16=F2)*ROW($C$2:$C$16)-1,1))

가 됩니다.

 

$C$2:$C$16가 index함수의 array가 되고,

AGGREGATE(15,2,1/($B$2:$B$16=F2)*ROW($C$2:$C$16)-1,1)로 해당 줄 수가 반환됩니다.

 

① Aggregate 함수의 3번째 인수 중 $B$2:$B$16=F2는 판매일이 2014-11-06일인 것을 찾아 True 또는 False를 반환해주는 것으로, 이 범위를 잡고 F9키를 누르면

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

첫번째만 True이고, 나머지는 모두 False입니다.

원래대로 돌리기 위해 Esc키를 누릅니다.

 

② ROW($C$2:$C$16)의 값을 알아보기 위해 범위를 잡고 F9키를 누르면

{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

2부터 16까지 줄 수가 반환됩니다.

원래대로 돌리기 위해 Esc키를 누릅니다.

 

③ 이제 1/($B$2:$B$16=F2)*ROW($C$2:$C$16)-1를 범위로 선택하고 F9키를 누르면

{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

과 같이 날짜가 일치하는 첫번째만 1이 반환되고, 나머지는 1/False이기 때문에 #DIV/0! 에러가 반환됩니다.

 

④ Esc키를 누른 다음 Aggregate 함수 전체

AGGREGATE(15,2,1/($B$2:$B$16=F2)*ROW($C$2:$C$16)-1,1)

를 범위로 잡고 F9키를 누르면

줄(행) 수 1이 반환됩니다.

 

⑤  이제 index부터 끝까지 범위를 잡고 F9키를 누르면

C2셀에서 C16셀중 첫번째 행의 값을 반환하므로 A가 반환됩니다. 큰 따옴표로 둘러싸인 것은 A가 변수가 아니라 문자이기 때문입니다.

원래대로 돌리기 위해 Esc키를 누릅니다.

 

(다) G3셀과 G4셀에 적용하기

G3셀의 경우 해당하는 날짜 2개중 첫번째로 큰 지점명을 반환해야 하므로 k값이 1이 되고,

G4셀은 k값이 2가 되어야 합니다.

 

먼저 G2셀의 채우기 핸들을 G4셀까지 끕니다.

그리고, G3셀의 수식을 확인해 보면 

=INDEX($C$2:$C$16,AGGREGATE(15,2,1/($B$2:$B$16=F3)*ROW($C$2:$C$16)-1,1))

으로 Aggregate함수의 k값이 1로 맞는데

 

G4셀의 수식에서 k값을 확인해보면 똑같이 1이므로 이것을 2로 수정합니다.

 

그러면 아래와 같은 수식이 됩니다.

=INDEX($C$2:$C$16,AGGREGATE(15,2,1/($B$2:$B$16=F4)*ROW($C$2:$C$16)-1,2))

 

Aggregate함수내 함수가 15로 Small이기 때문에 8행과 9행중 첫번째 작은 값은 8, 두번째 작은 값이 9이므로 C가 반환됩니다.

 

H3셀의 경우는 금액을 기준으로 했으므로 309,000이 먼저 표시되었습니다.

 

(3) 해결 방안 2

문자에 대한 아스키코드 값을 구해주는 Code함수와 아스키코드에 대한 문자를 반환해주는 Char함수를 이용해 지점명ㅇ을 순서대로 구할 수 있습니다.

 

(가) Code함수와 Char함수

Code함수는 아스키코드표에서 해당하는 문자(Char)에 해당하는 code값(Dec)을 십진수를 반환해주고, Char함수는 Code함수와 반대로 숫자를 Char(문자)로 반환해줍니다.

위 표에서 A를 찾아보면 65이고 a는 97로 다르며, 숫자 1은 49입니다.

따라서, Code("A")하면 65를 반환하고, Char(65)하면 A가 반환됩니다.

 

(나) Code함수와 Char함수 활용하기

문자를 곱하면 에러가 발생하기 때문에 Code함수를 이용해 숫자로 바꾼 후 곱하면 에러가 발생하지 않습니다. 그리고, 이들 숫자를 Char함수를 이용해 다시 문자로 바꾸면 원하는 지점명을 구할 수 있습니다.

 

① J2셀에서 F2셀의 날짜에 해당하는 지점명 중 가장 작은 값 구하기

작성된 수식은 아래와 같습니다.

아래 수식을 J2셀에 붙여넣으면

=CHAR(AGGREGATE(15,2,1/($B$2:$B$16=$F2)*CODE($C$2:$C$16),1))

 

이번에는 F9가 아니라 수식 탭 > 수식 분석 그룹 > 수식 계산 명령으로 수식 계산 과정을 살펴보겠습니다.

 

J2셀에 커서를 놓고 수식 계산 명령을 누르면 $F2아래에 커서가 있고, 아래 계산 버튼을 누르면

 

$B$2:$B$16=$F2 부분이 날짜를 비교하는 부분인데 모두 숫자로 표시됩니다. 다시 계산 버튼을 누르면

 

날짜를 비교한 결과가 True, False, False 등으로 표시되는데, 첫번째만 날짜가 같기 때문에 True입니다. 다시 계산 버튼을 누르면

 

True,False 값 바깥에 있던 괄호가 없어집니다. 다시 계산 버튼을 누르면

 

1/True, False 등의 결과가 1, #DIV/0! 등의 값으로 반환됩니다. !/True는 True가 1이기 때문에 1, 1/False는 False가 0이기 때문에 0으로 나눌 수 없다는 #DIV/0! 에러가 반환되는 것입니다. 다시 계산 버튼을 누르면

 

CODE($C$2:$C$16)의 계산 값 65,66 등의 코드값으로 표시됩니다. 다시 계산 버튼을 누르면

 

1*65, #DIV/0!*66 등의 결과값이 반환되는데, #DIV/0!와 곱한 값은 똑같이 #DIV/0!에러가 반환됩니다. 다시 계산 버튼을 누르면, 

 

Aggregate함수의 결과값, 다시 말해 에러를 제외한 값 중 가장 작은 값인 65를 반환합니다. 다시 계산 버튼을 누르면

 

Char(65)의 결과값 A가 반환됩니다. 이제 계산과정이 모두 끝났으므로 닫기 버튼을 누릅니다.

 

② J3, J4셀에 수식 채우기

J2셀의 채우기 핸들을 J4셀까지 끌면 수식이 복사되는데 결과값이 모두 B입니다. 이것은 k값이 모두 1이라서 그런 것이므로 J3셀의 k값을 2로 바꾸면

 

C가 반환됩니다.

aggregate3(완성).xlsx

0.01MB