[함수추가] INDEX함수 : 범위속에서 지정한 만큼의 위치에 있는 값 [오른쪽 열 기준으로 왼쪽열을 찾을때 (VLOOKUP함수의 반대 방향)]
INDEX함수가 상당히 자주 쓰이는 유용한 함수인데 작년에 빠뜨려서 이번에 보충합니다.
엑셀 도움말에는 다음과 같이 되어있습니다.
INDEX함수에는 사실, 배열형과 참조형이 있으며, 위의 도움말은 배열형에 대한 설명입니다. 하지만, 참조형을 지금 알 필요는 없을 듯하여 배열형만을 설명하겠습니다. 당분간은 INDEX함수를 쓰실때 다음 그림처럼 배열형이나 참조형중에 선택하라는 메시지가 나오면 배열형(위에 있는것)을 선택하십시오.
간단하게 INDEX함수의 사용법을 설명하면
입니다. 셀범위 내에서 몇번째 가로줄(행)과 몇번째 세로줄(열)이 만나는 곳의 값을 찾는 것입니다. 가로줄이나 세로줄이 한 줄 밖에 없다면, 그 부분은 생략해도 됩니다.
INDEX함수를 사용하는 예를 하나 들어보겠습니다. 위와 같이 반, 번호별로 학생들의 성적을 나열한 표가 있습니다. B26셀에는 반과 번호를 입력하면 해당 학생의 점수를 보여주는 수식이 다음과 같이 입력되어있습니다.
수식의 의미는 도움말에서 보신 것처럼,
따라서 3반 12번 학생의 점수인 95점을 출력합니다.
주의할 점은, 두번째와 세번째 인수인 행번호와 열번호는 셀주소가 아니고, 주어진 범위 내에서의 위치를 말한다는 것입니다.
즉, 위에서
=INDEX(E6:J22,E25,E24) 라는 수식은 결국
=INDEX(E6:J22,12,3)을 의미하는데,
셀주소상에서 세로로 12번째인 12행과 가로 세번째인 C열이 만나는 C12셀의 값을 출력하는 것이 아니라,
주어진 범위 E6:J22 내에서 세로로 12번째(17행), 가로로 세번째(G열)이 만나는 G17셀의 값을 출력하는 것입니다.
INDEX함수가 유용하게 사용되는 예를 하나 들어보겠습니다.
엑셀에서 사용빈도수가 높은 함수의 순위를 매기면 10위안에 들어갈 거라고 확신하는 VLOOKUP함수는 한 열에서 특정 값을 찾아서, 그 오른쪽에 있는 값을 나타내주는 함수입니다.
그런데, 왼쪽에 있는 값을 찾아야할 때도 있습니다만 VLOOKUP함수에서는 반드시 기준열의 오른쪽으로만 찾지, 왼쪽으로 찾지는 않기 때문에 가끔은 불편합니다.
위의 그림과 같은 데이타가 있을때 학번을 기준으로해서, 그 학번의 학생이 몇 점인지 몇 등인지는 VLOOKUP함수를 이용하면 쉽게 구할 수 있습니다.
하지만, 순위를 기준으로해서, 그 순위의 학생이 누구인지를 찾는 방법은 VLOOKUP함수로 되지 않습니다. 기준열이 되는 순위가 지금 데이타에서는 오른쪽에 위치 있어서 VLOOKUP함수가 기준열에서 왼쪽의 데이터는 찾지 못하기 때문입니다.
물론, 위의 표를 순위순서대로 정렬한 다음에 복사해 붙이면 되긴 합니다만, 작업할 분량이 많을때 이런 간단한 것도 수식으로 해결해준다면 일손을 많이 덜게 되는 것이지요.
B16셀에 들어가는 수식은 다음과 같습니다.
이 수식은 크게 두 부분으로 이루어져 있습니다.
=INDEX(A$2:A$12,----- 부분과
MATCH($A16,$D$2:$D$12,0)) 부분입니다.
INDEX함수의 설명에서, 가로줄이나 세로줄이 한 줄 밖에 없다면, 그 부분은 생략해도 된다고 했었지요. 따라서
=INDEX(A$2:A$12,-----
에서 첫번째 인수로 A2:A12의 범위를 주었기에, 세로줄은 A열 하나 밖에 없습니다. 따라서 뒤의 ----- 부분에는 세로줄(행)의 번호만 주면 어느 위치에 있는 셀인지를 파악할 수 있습니다.
위에서----- 로 표현한 부분에는 다음 수식이 들어갔습니다.
MATCH($A16,$D$2:$D$12,0))
이 수식을 계산한 값이 INDEX함수에서 몇번째 열에 있는 셀을 찾을 것인지를 알려줍니다.
MATCH함수는 첫번째 인수를 두번째 인수로 주어진 범위속에서 몇번째 위치에 있는지를 찾는 함수입니다. 즉, A16셀의 값이 D2:D12의 범위중에 몇번째에 있는지를 알려줍니다. D2:D12의 범위속에서 A16셀에 있는 값(1)은 네 번째에 있으므로, 4를 반환합니다.
즉, MATCH($A16,$D$2:$D$12,0)의 결과값은 4입니다. 우리가 값을 구하고자하는 함수
=INDEX(A$2:A$12,MATCH($A16,$D$2:$D$12,0))
에서 MATCH($A16,$D$2:$D$12,0)자리에 4를 대입해봅시다.
=INDEX(A$2:A$12,4)
그러면 A2:A12의 범위중에서 4번째에 있는 값을 찾으라는 의미이며, A2:A12범위 중에서 4번째에 있는 값은 10104입니다. 따라서 10104가 최종적인 결과값으로 나옵니다.
위의 수식
=INDEX(A$2:A$12,MATCH($A16,$D$2:$D$12,0))
에서, A2:A12 와 A16이 아니라 A$2:A$12 와 $A16 으로 표현한 이유를 생각해 봅시다.
엑셀 도움말에는 다음과 같이 되어있습니다.
INDEX(array,row_num,column_num)
array 배열 상수나 셀 범위입니다.
column_num 값을 반환할 배열의 열을 선택합니다. column_num을 생략하면 row_num을 지정해야 합니다.
주의
array 배열 상수나 셀 범위입니다.
- 배열에 행이나 열이 하나만 있을 때는 row_num이나 column_num 인수를 생략할 수 있습니다.
- 배열에 행과 열이 두 개 이상 있을 때 row_num이나 column_num만 사용하면 배열의 전체 행이나 열이 하나의 배열로 반환됩니다.
column_num 값을 반환할 배열의 열을 선택합니다. column_num을 생략하면 row_num을 지정해야 합니다.
주의
- row_num과 column_num 인수를 모두 사용하면 row_num과 column_num이 교차하는 셀의 값이 반환됩니다.
- row_num이나 column_num을 0으로 설정하면 전체 열이나 전체 행에 대한 값의 배열이 각각 반환됩니다. 배열로 반환된 값을 사용하려면 행에 대한 가로 셀 범위와 열에 대한 세로 셀 범위에 INDEX 함수를 배열 수식 (배열 수식: 하나 이상의 값 집합에 대해 여러 가지 계산을 수행하고 하나 또는 여러 개의 결과를 반환하는 수식입니다. 배열 수식은 중괄호 { } 안에 표시되고 Ctrl+Shift+Enter를 눌러 입력합니다.)으로 입력합니다. 배열 수식을 입력하려면 Ctrl+Shift+Enter를 누릅니다.
- row_num과 column_num은 반드시 배열에 있는 셀이어야 합니다. 그렇지 않으면 #REF! 오류 값이 반환됩니다.
INDEX함수에는 사실, 배열형과 참조형이 있으며, 위의 도움말은 배열형에 대한 설명입니다. 하지만, 참조형을 지금 알 필요는 없을 듯하여 배열형만을 설명하겠습니다. 당분간은 INDEX함수를 쓰실때 다음 그림처럼 배열형이나 참조형중에 선택하라는 메시지가 나오면 배열형(위에 있는것)을 선택하십시오.
간단하게 INDEX함수의 사용법을 설명하면
index(셀범위,가로줄번호,세로줄번호)
입니다. 셀범위 내에서 몇번째 가로줄(행)과 몇번째 세로줄(열)이 만나는 곳의 값을 찾는 것입니다. 가로줄이나 세로줄이 한 줄 밖에 없다면, 그 부분은 생략해도 됩니다.
INDEX함수를 사용하는 예를 하나 들어보겠습니다. 위와 같이 반, 번호별로 학생들의 성적을 나열한 표가 있습니다. B26셀에는 반과 번호를 입력하면 해당 학생의 점수를 보여주는 수식이 다음과 같이 입력되어있습니다.
=INDEX(E6:J22,E25,E24)
수식의 의미는 도움말에서 보신 것처럼,
| =INDEX(E6:J22, | E6:J22 범위내에서 | |
| E25, | E25셀에 들어있는 값만큼 세로로 내려와 | 현재 E25셀에는 12가 입력되어있으므로, 아래로 12번째 가로줄 |
| E24) | E24셀에 들어있는 값만큼 가로로 이동한 셀에 있는 값을 출력한다 | 현재 E24셀에는 3이 입력되어있으므로, 오른쪽으로 3번째 세로줄 |
따라서 3반 12번 학생의 점수인 95점을 출력합니다.
주의할 점은, 두번째와 세번째 인수인 행번호와 열번호는 셀주소가 아니고, 주어진 범위 내에서의 위치를 말한다는 것입니다.
즉, 위에서
=INDEX(E6:J22,E25,E24) 라는 수식은 결국
=INDEX(E6:J22,12,3)을 의미하는데,
셀주소상에서 세로로 12번째인 12행과 가로 세번째인 C열이 만나는 C12셀의 값을 출력하는 것이 아니라,
주어진 범위 E6:J22 내에서 세로로 12번째(17행), 가로로 세번째(G열)이 만나는 G17셀의 값을 출력하는 것입니다.
INDEX함수가 유용하게 사용되는 예를 하나 들어보겠습니다.
엑셀에서 사용빈도수가 높은 함수의 순위를 매기면 10위안에 들어갈 거라고 확신하는 VLOOKUP함수는 한 열에서 특정 값을 찾아서, 그 오른쪽에 있는 값을 나타내주는 함수입니다.
그런데, 왼쪽에 있는 값을 찾아야할 때도 있습니다만 VLOOKUP함수에서는 반드시 기준열의 오른쪽으로만 찾지, 왼쪽으로 찾지는 않기 때문에 가끔은 불편합니다.
위의 그림과 같은 데이타가 있을때 학번을 기준으로해서, 그 학번의 학생이 몇 점인지 몇 등인지는 VLOOKUP함수를 이용하면 쉽게 구할 수 있습니다.
하지만, 순위를 기준으로해서, 그 순위의 학생이 누구인지를 찾는 방법은 VLOOKUP함수로 되지 않습니다. 기준열이 되는 순위가 지금 데이타에서는 오른쪽에 위치 있어서 VLOOKUP함수가 기준열에서 왼쪽의 데이터는 찾지 못하기 때문입니다.
물론, 위의 표를 순위순서대로 정렬한 다음에 복사해 붙이면 되긴 합니다만, 작업할 분량이 많을때 이런 간단한 것도 수식으로 해결해준다면 일손을 많이 덜게 되는 것이지요.
B16셀에 들어가는 수식은 다음과 같습니다.
=INDEX(A$2:A$12,MATCH($A16,$D$2:$D$12,0))
이 수식은 크게 두 부분으로 이루어져 있습니다.
=INDEX(A$2:A$12,----- 부분과
MATCH($A16,$D$2:$D$12,0)) 부분입니다.
INDEX함수의 설명에서, 가로줄이나 세로줄이 한 줄 밖에 없다면, 그 부분은 생략해도 된다고 했었지요. 따라서
=INDEX(A$2:A$12,-----
에서 첫번째 인수로 A2:A12의 범위를 주었기에, 세로줄은 A열 하나 밖에 없습니다. 따라서 뒤의 ----- 부분에는 세로줄(행)의 번호만 주면 어느 위치에 있는 셀인지를 파악할 수 있습니다.
위에서----- 로 표현한 부분에는 다음 수식이 들어갔습니다.
MATCH($A16,$D$2:$D$12,0))
이 수식을 계산한 값이 INDEX함수에서 몇번째 열에 있는 셀을 찾을 것인지를 알려줍니다.
MATCH함수는 첫번째 인수를 두번째 인수로 주어진 범위속에서 몇번째 위치에 있는지를 찾는 함수입니다. 즉, A16셀의 값이 D2:D12의 범위중에 몇번째에 있는지를 알려줍니다. D2:D12의 범위속에서 A16셀에 있는 값(1)은 네 번째에 있으므로, 4를 반환합니다.
즉, MATCH($A16,$D$2:$D$12,0)의 결과값은 4입니다. 우리가 값을 구하고자하는 함수
=INDEX(A$2:A$12,MATCH($A16,$D$2:$D$12,0))
에서 MATCH($A16,$D$2:$D$12,0)자리에 4를 대입해봅시다.
=INDEX(A$2:A$12,4)
그러면 A2:A12의 범위중에서 4번째에 있는 값을 찾으라는 의미이며, A2:A12범위 중에서 4번째에 있는 값은 10104입니다. 따라서 10104가 최종적인 결과값으로 나옵니다.
[실습과제]
다음 파일을 열어, 주어진 문제를 해결하는 수식을 작성해 봅시다.위의 수식
=INDEX(A$2:A$12,MATCH($A16,$D$2:$D$12,0))
에서, A2:A12 와 A16이 아니라 A$2:A$12 와 $A16 으로 표현한 이유를 생각해 봅시다.
댓글
댓글 쓰기