rand(life)
배열수식, AGGREGATE 함수 본문
오늘은 오랜만에 배열수식
아래와 같은 표에서 가장 최근에 출고한 날짜를 찾는 함수이다
주의할 것은 날짜가 정렬이 되어있지 않다는 것.
날짜 |
입고 |
출고 |
재고 |
06월 01일 |
|
20 |
100 |
07월 01일 |
50 |
|
150 |
08월 01일 |
|
60 |
90 |
07월 13일 |
30 |
|
120 |
07월 31일 |
|
40 |
80 |
07월 14일 |
20 |
|
100 |
배열수식을 이용한다.
{=MAX(IF(C4:C9<>"",A4:A9))}
C4:C9<>""을 먼저 계산하여
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE} 를 만들고
그 다음에 A4:A9의 값인
{42887;42917;42948;42929;42947;42930}
와 함께 IF에 의해 합치면
IF({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}, {42887;42917;42948;42929;42947;42930})가 되어 그 결과
{42887;FALSE;42948;FALSE;42947;FALSE}
이렇게 된다. 이 중에서 Max함수에 의해 제일 큰 값에 해당하는
42948 을 날짜 셀서식으로 변환하면 8월1일이 나온다.
하지만 엑셀 2010버전에 있는 함수를 이용하여 다음과 같이 가능한데
=AGGREGATE(14,6,A4:A9/(C4:C9<>""),1)
이 함수는 특정값을 제외하고 계산할 수 있는 함수이다
요약하면, average, sum, count, counta, max, min, product, large, small 등의 함수가 하는 기능을 사용할 수 있고
무시하는 것은
숨겨진행, 오류값 등을 무시할 수 있다.
=AGGREGATE(14,6,A4:A9/(C4:C9<>""),1)
위의 수식에서 숫자가 말하는 의미는 다음과 같다
14 = large 함수
6 = 오류값 무시
즉, C4:C9의 범위 중 숫자가 있는 셀의 True/False값(0,1)을 분모로
같은 범위의 날짜 있는 셀값을 분자로 하면
False가 있는 부분은 0으로 나누니 DIV/0 오류가 난다
오류가 있는 부분은 무시하고, Large함수로 첫번째로 큰 값을 구하면 8월1일이다
