INDEX, MATCH는 지금까지 다루지 않은 ITQ 엑셀 출제 함수 중 하나입니다. 이 녀석들도 심심치 않게 출제되고 있는데요 이상하게 제가 고른 출제 시험에서는 등장하지 않았네요. 각각 강좌를 따로 만들어도 되겠지만 보통 INDEX, MATCH가 같이 출제되고 있으니 같이 알아볼까 합니다.
예제 파일을 보면 F13셀은 모든 제품명이 데이터 유효성 검사를 통해 선택하여 바꿀 수 있게 되어 있습니다.
일단 데이터 유효성 검사를 무시한 채 진행하겠습니다.
G13셀에 시티즈플래티넘의 가격을 반환하고 싶으면 간단하게 참조하면 됩니다. G13셀을 클릭한 뒤 수식입력줄에서 =G3을 작성하면 끝이 됩니다. 셀의 위치값을 "="과 함께 사용하면 간단하게 끝나는 것이죠. 그럼 셀의 위치값을 순서로 풀어서 설명하면 어떻게 될까요?
G3셀은 열로 보자면 시트의 좌측을 기준으로 7번째 위치해 있으며
행으로 보자면 시트의 상단으로부터 3번째 위치해 있죠.
즉 기준점은 워크시트의 좌측 상단(A1셀의 좌측 상단)이라는 것입니다.
더 풀어서 말하자면 기준점에서 열로는 7번째 칸, 행으로는 3번째 칸이라고 표현할 수 있겠습니다.
이렇게 셀의 위치를 순서로 풀어서 찾아가는 함수가 바로 INDEX입니다.
그런데 만약 기준점이 바뀌면 어떻게 될까요? 당연히 열과 행의 수치가 바뀌게 될 것입니다.
그림처럼 만약 기준점이 B2셀의 좌측 상단이 된다면 열로는 6번째 행으로는 2번째,
기준점이 G3이 된다면 열로는 1번째 행으로도 1번째가 됩니다.
이제 함수를 정의하겠습니다.
INDEX
- array - 배열 상수나 셀 범위(배열에 행이나 열이 하나만 있을때는 row_num이나 column_num 생략 가능)
- row_num - array에서 반환할 요소의 행 순서(array가 1개의 행으로 이루어졌을때 생략)
- column_num - array에서 반환할 요소의 열의 순서(array에 1개의 열로 이루어졌을때 생략)
여기서 array는 지금까지 해왔던 범위로 생각하시면 됩니다. 중요한 건 범위에서 행과 열의 순서를 가져와야 하기 때문에 찾을 요소까지 범위를 모두 포함하고 있어야 된다는 것입니다.
표 전체에서 찾을 것이고, 지금처럼 판매가격을 찾는다면 array의 범위는 B2:G10까지로 정하면 됩니다.
만약 위 문제에서 판매가격이 아니라 소비전력을 찾는 것이라면 범위는 B2:F10까지로,
만약 출시연도를 찾는 것이라면 B2:D10까지로 선택하면 됩니다.
하지만 지금은 굳이 표 전체에서 찾을 필요가 없습니다. 판매가격만 찾는 것이기 때문에 표의 범위는 필요 없습니다. 판매가격 열 즉 G3:G12의 범위에서만 찾으면 불필요한 범위의 검색을 피할 수 있습니다.
- G13셀 클릭하여 선택
- 수식입력줄에서 함수 작성 => =INDEX(
- array인수(범위 G3:G10) 참조 => =INDEX(G3:G10
- 콤마 입력 후 반환할 값의 행 순서 작성 => =INDEX(G3:G10,1
- 엔터 입력으로 마무리 => =INDEX(G3:G10,1)
tip. 한 개의 열만으로 범위를 지정했기 때문에 3번째 인수 column_num은 작성할 필요가 없습니다.
시티즈플래티넘의 판매가격이 잘 반영되었습니다.
여기까지 데이터 유효성 검사를 무시한 채 단순히 참조하면 될 것을 굳이 INDEX함수로 만들어 봤습니다. 사실 실무에서 한가지 데이터의 특정 값을 찾을때 이렇게 사용하는 것은 매우 비효율적인 일입니다. 그냥 참조하고 끝이죠.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
이제부터 데이터 유효성 검사한 셀(F13)을 고려해 보겠습니다. 데이터 유효성 검사를 목록으로 진행했다는 것은 항목을 바꾸겠다는 뜻이죠. 당연히 항목이 바뀌었으니 판매가격도 바뀌어야 합니다. 이런 경우 ITQ 엑셀 시험에서는 VLOOKUP함수를 사용하여 판매가격을 변경하는 형태의 문제가 매번 출제되고 있습니다. 하지만 지금은 INDEX를 사용해야 합니다. 이런 경우 INDEX만으로 VLOOKUP과 같은 효과를 사용할 수 없습니다. 하나의 함수가 더 필요한데 그게 바로 MATCH입니다.
조금 더 위의 예제를 살펴보겠습니다.
제품명이 시티즈플래티넘에서 프라시스와이로 바뀌면 INDEX함수에서 바뀌는 것을 뭘까요? 인수 row_num입니다. 1에서 3이 돼야 되겠죠. 씽킹캡슐머신이라면 7일 돼야 됩니다. 위의 경우에서는 제품명이 바뀔 때마다 INDEX함수의 인수 row_num이 바뀌어야 결과가 반영되는데 이것을 자동화해 주는 함수가 바로 MATCH가 되겠습니다.
MATCH
- lookup_value - lookup_array에서 찾을 값
- lookup_array - 검색할 범위
- match_type - 선택 요소로 일치여부에 따라 1,0,-1 선택. 생략시 1적용
1 : lookup_value 이하의 수에서 최대값
0 : lookup_value와 같은 첫번째 값
-1 : lookup_value 이상의 수에서 최소
현재 시트즈플래티넘의 순서는 제품명 범위(C3:C10)에서 1번째 위치에 해당합니다. 판매가격은 판매가격 범위(G3:G10)에서 동일하게 1번째 위치에 해당하죠. 당연히 제품명이 씽킹캡슐머신이 되면 순서는 제품명도 판매가격도 7째로 순서가 되겠죠.
즉 찾을 행의 row_num은 제품명 범위(C3:C10)에서 찾을 제품명의 MATCH 결과와 동일해지게 됩니다.
- G13셀을 클릭하여 선택 => =INDEX(G3:G10,1)
- 수식입력줄에서 INDEX함수의 인수 row_num(1)을 지우고 MATCH함수 작성 => =INDEX(G3:G10,MATCH()
- lookup_value(찾을 값 F13셀)을 참조 => =INDEX(G3:G10,MATCH(F13)
- 콤마 입력 후 lookup_array(찾을 범위 C3:C10)을 참조 => =INDEX(G3:G10,MATCH(F13,C3:C10)
- 콤마 입력 후 match_type을 작성(여기서는 사실 생략해도 됩니다. 숫자가 아니고 동일 값이 없기 때문)
=> =INDEX(G3:G10,MATCH(F13,C3:C10,0) - 최종 MATCH함수의 닫힘 괄호까지 입력한 후 엔터로 완성합니다. => =INDEX(G3:G10,MATCH(F13,C3:C10,0))
결과 제품명이 바뀔때마다 판매가격을 찾아주는 문제가 해결되었습니다.
위에서도 언급했지만 시험에서는 VLOOKUP으로 반드시 출제되는 형태입니다. 하지만 다른 시험 조건으로 간혹 출제되고 있으니 이 방법도 꼭 알아두시길 바랍니다. 현 시점에서 엑셀은 XLOOKUP이라는 함수를 추가했으며 이 함수가 VLOOKUP의 여러가지 문제를 해결할수 있게되었는데 아직 ITQ시험에서는 출제되고 있지는 않습니다.
INDEX, MATCH의 사용은 바로 이 XLOOKUP과 유사하다고 볼 수 있겠습니다. 아마도 언제가는 VLOOKUP대신 XLOOKUP이 출제되리라 생각하고요. 조마간 XLOOKUP에 대해서도 추가 강좌 진행하도록 하겠습니다.
'강좌 및 설정 > 엑셀 ITQ 출제 함수' 카테고리의 다른 글
| 엑셀 함수 중첩 IF - (ITQ : 2024103_A형) (0) | 2024.07.30 |
|---|---|
| 엑셀 함수 ROUND - (ITQ : 2024103_A형) (0) | 2024.07.30 |
| 엑셀 함수 ROUND, ROUNDDOWN, ROUNDUP (0) | 2024.07.29 |
| 엑셀 함수 SUMIF - (ITQ : 2024105_A형) (0) | 2024.07.21 |
| 엑셀 함수 ~IF 인수 criteria의 (부)등호 처리 - (ITQ : 2024105_A형) (0) | 2024.07.21 |