들어가기에 앞서..
일단 이번 시간에도 들어가기에 앞서 Tip을 하나 써본다. 어떻게 보면 Tip이 아니라 가장 기본이 되는 내용이다. 데이터를 정리할 때 웬만하면 기준이 되는 내용을 가로로 나열하는게 좋다. 무슨 말이냐고? 예를들어 전교생 데이터를 입력한다고 치자. 그러면 A1부터 A3000 쭉쭉쭉 밑으로 써내려가는 것이다. 그리고 옆으로는(가로 b1, c1 등등)그에 맞는 세부사항들을 기록해야 나중에 정리하기가 쉽다.
Index&match 함수
지난 번 글에서 Vlookup에 대해서 설명하는 시간이 있었다. 수많은 데이터 속에서 특정 기준에 부합하는 가로열에 있는 결과값을 찾는 일이다. 이 때 중복 기준이 발생하면 데이터가 꼬인다. Vlookup의 업그레이드 버전으로 중복되는 기준에도 정확한 값을 찾아낼 수 있는 함수 조합이다. 아무리 생각해도 함수는 백날 말로 설명해봐야 어떨 때 쓰는지 너무 어렵다. 예제나 실전 상황에 투입되어야 편한 것 같다.
Vlookup 복습
전교생 3천명에 대한 데이터가 있다고 치자. 그중에서 A라는 학생의 취미를 알고 싶을 경우에 쓴다. 이름이 A열에 있고 취미가 C열에 있다면 함수 수식은 =vlookup(찾고자하는칸,A:C,3,false)라고 생각했다면 성공이다. 그런데 인간은 동명이인이 존재한다.
앗! 이런 경우는 어떻게 해?
Vlookup 함수의 한계는 중복값이 있을 경우 제일 처음에 나오는 값을 알려준다. 여러개가 있다고 오류가 나거나 하지 않으니 처음 설계할 때부터 유의해야 한다. 그래서 단순히 기준 셀에 있는 데이터를 사용하는게 index&match 함수다. 그럼 학생의 이름 말고 다른 기준이 하나 더 있을 때 고유값을 가지면 된다.
예제를 통해서 살펴보자. 학년도 반도 장래희망도 다른 최익현 학생이 2명 있다. 학생 이름 하나만을 기준으로 Vlookup 함수를 써서 데이터를 정리한다면 1학년 최익현 학생의 장래희망은 가수에서 축구선수로 바뀌어 있을 것이다.
자, 이런 경우 여러 기준을 조합하여 값을 찾을 수 있다. 그게 바로 Index함수와 Match함수를 섞어서 사용하는 것이다. 아래 표를 보면 함수식이 복잡해 진것을 볼 수 있다.
함수식
=INDEX($G$3:$G$6,MATCH(1,(I3=$B$3:$B$6)*(J3=$C$3:$C$6)*(K3=$D$3:$D$6)*(L3=$E$3:$E$6),0))
쉽게 설명하면!
=index(찾고싶은 값, match(1,(기준=범위)*(기준=범위),0)) 이렇게 쓰여진다. match 함수 뒤에는 지정하고 싶은 조건만큼 설정할 수 있어서 계속 추가할 수 있다. 지금은 이름, 학년, 반, 번호를 모두 충족하도록 설정한 함수식이다. 여기서 유의할점은 바뀌지 않는 범위는 꼭 절대값 $ 설정을 걸어야 한다는 점이다. 보통 데이터가 정렬되면 모든 칸이 채워져 있기 때문에(비어있더라도) 같은 행을 기준으로 절대값 참조를 걸어주는게 중요하다.
index&match 혼합 사용법에 대해서 알아보았다.
다중 조건에 만족하는 결과값을 얻을 때 매우 효율적으로 사용되는 함수기 때문에 알아두면 좋다.
'회사생활 > EXCEL' 카테고리의 다른 글
| Vlookup 함수 사용법, 직장인 필수 EXCEL! (0) | 2023.04.08 |
|---|
댓글