조견표에서 일치하는 값 찾기(3) - Aggregate 함수

indirect(name)(final)2.xlsx

0.01MB

 
이번에는 Aggregate 함수를 이용해 조건에 맞는 값을 찾아보겠습니다.
 

1. Aggregate함수의 구문

Aggregate 함수의 구문은 
 
참조형인 경우는
AGGREGATE(function_num, options, ref1, [ref2], …)

배열형인 경우는 
AGGREGATE(function_num, options, array, [k])
입니다.
 
function은 합계는 9, 최댓값은 14이며, 오류값을 무시하는 options는 2, 3, 6, 7이 있습니다.
 
 
Aggregate 함수의 보다 자세한 설명은 아래 글을 참고하기 바랍니다.
https://lsw3210.tistory.com/entry/Aggregate%EC%98%B5%EC%85%98-%EC%A7%91%EA%B3%84-%ED%95%A8%EC%88%981
 
 

2. Microsoft 사이트 도움말 오류(?)

Microsoft의 공식 도움말을 보면 Options는 Required(필수 요소)라고 하면서 그 아래 Note(주석)을 보면 "이 함수는 배열 인수에 계산이 포함된 경우 숨긴 행, 중첩된 부분합 또는 중첩된 집계는 무시되지 않는다고 하면서 

 
예로 =AGGREGATE(14,3,A1:A100*(A1:A100>0),1))가 들어져 있는데 
이 수식은 적용 시 문제가 없고,

 
오히려 배열형이 아닌 참조형에 해당하는 첫 번째 function_num이 9일 경우 #VALUE! 에러가 발생합니다.

 
설명이 잘못된 듯합니다.
 
function_num이 9이더라도 계산을 하지 않고, C3:C8이라고 참조할 범위만 지정하면 에러 없이 C3셀부터 C8셀까지의 합계가 구해집니다.

 
 

3. Aggregate 함수를 이용해 조건에 맞는 요금 구하기

 

가. function_num 14(Large) 이용하기

그동안 SumIfs함수나 Sum(if 배열 수식 등을 이용해 조건에 맞는 요금을 구했으나,
위와 같이 function_num에 9를 넣는 경우에 배열 수식 사용을 할 수 없으므로,
최댓값을 구하는 function_num 14를 사용해야 합니다.
 
모든 조건을 만족해야 하므로 조건을 *로 연결하는 것은 그동안과 같고 다른 점은 최댓값을 찾을 요금범위도 *로 연결해야 한다는 것입니다.
 
따라서, 수식은 아래와 같습니다.
=AGGREGATE(14,3,(부하=C$11)*(요금구분=A13)*(계절=B13)*요금,1)

끝에 있는 숫자 1은 첫 번째로 큰 수를 구하라는 것입니다. 조건에 맞는 요금이 1개이기 때문에 2라고 하면 0이 구해집니다. 
 
또한 C$11라고 뒤만 $표시를 한 것은 한 줄아래에 수식을 복사하더라도 11행은 변경되지 않도록 하는 것입니다. 나머지는 아래로 내려갈 때 자동으로 행이 +1이 돼야 하므로 $를 붙이지 않았습니다.
 
따라서, D13셀의 채우기 핸들을 D14셀까지 끌어서 수식을 복사하면 D14셀의 수식은
=AGGREGATE(14,3,(부하=C$11)*(요금구분=A14)*(계절=B14)*요금,1)이 됩니다.

 
 
D13셀의 수식과 D14셀의 수식을 비교해 보면 C$11은 변경되지 않고, A13과 B13은 A14와 B14로 1 증가한 것을 알 수 있습니다.

D13셀의 수식	=AGGREGATE(14,3,(부하=C$11)*(요금구분=A13)*(계절=B13)*요금,1)
D14셀의 수식	=AGGREGATE(14,3,(부하=C$11)*(요금구분=A14)*(계절=B14)*요금,1)

 
부하 구분을 셀별로 비교하므로 A4, A6, A8셀을 지우면 조건에 부합하는 요금(중간부하 선택요금)이 없어서 D13셀의 값이 0이 됩니다. 그러나, D14셀은 (중간부하 일반요금)을 찾기 때문에 해당하는 요금 30이 있어서 여전히 표시됩니다.

 

나. function_num 15(Small) 이용하기

마우스로 끌어서 (부하=C$11)*(요금구분=A14)*(계절=B14)*요금 을 범위를 잡은 후

 
F9키를 눌러 값을 확인해 보면 조건에 부합하지 않는 것은 0이고, 조건에 부합하는 것만 30이라는 것을 알 수 있습니다. 따라서, 큰 값 기준으로는 1, 첫 번째가 되고, 작은 값 기준으로 하면 C3셀에서 E8셀까지의 셀 개수를 인수로 넣어야 합니다.

 
셀의 개수를 Count를 이용해 구할 수 있고, 3셀에서 E8셀까지는 이름을 요금이라고 지정했으므로
수식이 =AGGREGATE(15,3,(부하=C$11)*(요금구분=A14)*(계절=B14)*요금,COUNT(요금))가 됩니다.

 
 
이렇게 값을 구하는 방법이 여러 개 있다는 것이 엑셀을 다루는 또 하나의 묘미입니다.

indirect(name)(final)3.xlsx

0.01MB