본 글에서 사용한 코드는 오빠두엑셀님의 글을 참고해 작성했습니다. + 본 글은 개발자 시점에서 작성된 글입니다.
배열 함수 설명
배열 함수는 일반 함수와 달리 배열 안에 있는 값을 연산한다. 배열 함수는 Ctrl + Shift + Enter를 통해 입력하며, { }로 감싸진 형태로 표시된다.
| 가격 | 보유 개수 |
| 3000 | 5 |
| 2000 | 4 |
| 7000 | 6 |
만약 위 표에서 총가격을 구하는 문제를 풀어보자. 일반 함수는 아래와 같이 문제를 해결한다.
| 3000 | 5 | = A1 * B1 |
| 2000 | 4 | = A2 * B2 |
| 7000 | 6 | = A3 * B3 |
| = SUM(C1:C3) |
가격과 개수를 곱한 후 모두 더한다. 그런데 배열 함수를 사용하면 아래와 같이 표현할 수 있다.
| 3000 | 5 |
| 2000 | 4 |
| 7000 | 6 |
| { = SUM(A1:A3 * B1:B3) } | |
배열 함수 안에서 "A1:A3 * B1:B3"는 { A1*B1, A2*B2, A3*B3 }라는 배열이 된다. 따라서 SUM을 이용해 배열 값을 모두 더하면 총가격을 구할 수 있다. 이를 이용해 N번째를 VLOOKUP 하는 문제를 풀어보자.
문제 정의
- 문제: 아래 표에서 3번째 301을 찾으시오.
- 답: D
| key | value |
| 301 | A |
| 302 | B |
| 301 | C |
| 301 | D |
| 303 | E |
301을 key로 가지는 값은 A, C, D가 있다. 이 중 3번째 301은 D이다.
문제 해결
아이디어의 핵심은 인덱스를 찾는 것이다. 과정을 코드로 정리해보면 아래와 같다.
'0. 문제 조건
keys = {301, 302, 301, 301, 303}
values = {"A", "B", "C", "D", "E"}
targetKey = 301 '찾는 값
n = 3 'N번째
'1. 1부터 시작해 모든 key-value에 대해 index 배열을 생성한다.
indexArray = {1, 2, 3, 4, 5}
'2. key가 조건에 만족하는 경우만 필터링한다.
'filteredArray: {1, FALSE, 3, 4, FALSE}
filteredArray = IF(targetKey=keys, indexArray)
'3. 필터링된 index 중 N번째 값을 가져온다.
'targetIndex: 4
targetIndex = SMALL(filteredArray, n)
'4. 전체 value에서 index를 이용해 값을 가져온다.
targetValue = INDEX(values, targetIndex)index 배열 생성
ROW를 이용해 1부터 배열을 생성한다. 이는 value 인덱스를 의미한다.
'ROW(셀) -> 행 번호
{ =ROW("value 범위") - ROW("첫 value 셀") + 1 }ROW는 행 번호를 반환하기 때문에 값이 1부터 시작하지 않을 수 있다. 따라서 배열 첫 값으로 빼주면 0부터 시작하는 배열이 생성된다. 여기서 모든 값에 1을 더해주면 1부터 시작하는 배열을 만들 수 있다. 계산 과정을 자세히 살펴보면 아래 표와 같다.
| { = ROW(B2:B6) } | { 2, 3, 4, 5, 6 } |
| = ROW(B2) | 2 |
| { = ROW(B2:B6) - ROW(B2) } | { 0, 1, 2, 3, 4 } |
| { = ROW(B2:B6) - ROW(B2) + 1 } | { 1, 2, 3, 4, 5 } |
조건 필터링하기
배열을 입력 받아 조건을 만족하는 값만 남겨둔다. Python이나 Js의 filter과 비슷하다.
'IF(조건절, 필터링할 배열)
{ =IF("찾는 key" = "key 전체", "index 배열") }| key 전체 | 301 | 302 | 301 | 301 | 303 |
| 조건절 결과 | TRUE | FALSE | TRUE | TRUE | FALSE |
| index 배열 | 1 | 2 | 3 | 4 | 5 |
| 함수 결과 | 1 | FALSE | 3 | 4 | FALSE |
이렇게 하면 조건에 맞는 값의 index만 남게 된다.
N번째 index 찾기
조건을 만족하는 값의 index만 남았다. 여기서 n번째 작은 값을 찾으면 n번째에 있는 값의 index를 얻을 수 있다.
'SMALL(배열, n) -> 배열에서 n번째 작은 값
{ =SMALL("필터링된 배열", N) }| 배열 | { 1, 3, 4 } |
| = SMALL(배열, 2) | 3 |
| = SMALL(배열, 3) | 4 |
드디어 결과 값의 index를 찾았다.
결과 값 불러오기
INDEX 함수는 범위에서 N번째 값을 가져온다.
'INDEX(찾을 범위, N) -> N번째 값
{ =INDEX("value 범위", "찾는 값 index") }