안녕하세요! gbgg 입니다. 능력 있는 직장인이 되는 법! 엑셀 자동화 강의 아홉 번째 시간입니다!
지난 초급편 1,2에서 초급 기초 함수들을 설명드렸는데요,
오늘은 중급용 함수 몇 가지와 그를 활용한 예제를 다뤄보겠습니다.
VLOOKUP이나 INDEX, MATCH 같은 함수는 엑셀 업무가 잦은 직장인들이 정말 많이 사용하는 함수입니다.
이 함수들의 결과값이 어떻게 달라지는지 또 언제 활용해야 하는지 자세히 설명해드리고자 합니다.
활용 예제와 함께 잘 설명드릴게요!
VLOOKUP과 HLOOKUP함수는 많이 들어보셨을 텐데요,
범위 내 데이터를 검색해서 같은 행/열에 있는 값을 반환한다. 설명만 보면 굉장히 헷갈립니다.
간단한 예제를 통해 어떤 기능을 하는지 한번 살펴보겠습니다.
위와 같은 형태의 데이터에서 한 가지 데이터(이름) 밖에 알지 못할 때
그 옆의 셀에 있는 값들을 알고 싶으면 VLOOKUP을 사용하면 됩니다.
VLOOKUP의 V 뜻이 vertical이라서 세로라고 헷갈리실 수 있는데
수직으로 데이터를 검사한다는 뜻으로 생각하시면 됩니다.
HLOOKUP은 위와 같은 형태의 데이터에서 한 가지 데이터(이름)밖에 알지 못할 때,
그 위아래에 있는 값들을 가져와야 할 때 사용됩니다.
마찬가지로 HLOOKUP의 H는 horizontal 즉 가로로 데이터를 검사한다는 뜻으로 생각하시면 됩니다.
이 두 함수 중에 VLOOKUP은 데이터 형태가 수직으로 쌓여가는 형식이 많기 때문에 더 많이 사용됩니다.
그렇다면 더 자주 사용되는 VLOOKUP을 어떻게 사용하는지 한번 알아볼까요?
- 검색할 값 : 검색할 값입니다. 위 표를 참조하자면 "리플"이 되겠죠?
- 검색할 범위 : 검색할 값을 어디서 찾을 것인지 그 "전체" 범위를 지정해주면 됩니다.
- 반환 값 위치 : "전체" 범위의 가로 셀 개수가 5라면 1~5의 번호로 원하는 위치를 출력할 수 있습니다.
- 일치 여부 : TRUE나 FALSE 값을 넣을 수 있는데 TRUE는 근삿값, FALSE는 정확한 값을 찾아냅니다. 보통 FALSE를 많이 사용합니다.
자 한번 따라 해 보겠습니다.
| =VLOOKUP(D12,D3:F9,2,FALSE) |
자 이렇게 범위 내에서 "리플"이라는 항목이 있는 부분을 찾아 검색한 후 그 오른쪽에 있는 연락처 값을 가져와 보았습니다. 그렇다면 연락처가 아닌 주소를 가져오려면 어떻게 해야 할까요?
| =VLOOKUP(D12,D3:F9,3,FALSE) |
반환 값인 숫자 2를 3으로만 바꿔주면 3번째 셀인 주소 값을 가져올 수 있습니다.
바로 위 그림과 같이 결과가 나오게 됩니다.
혹시 이상한 점을 느끼셨나요?
왜 전체 범위를 지정할 때 날짜 부분은 포함하지 않았을까요?
여기서 VLOOKUP과 HLOOKUP의 한계가 드러납니다.
VLOOKUP, HLOOKUP 함수는 애초에 설계될 때부터 지정한 범위의"첫 번째 열" 값만 검색할 수 있도록 만들어졌습니다.
왜 그런 것인지는 모르지만 이 문제 때문에 기존 데이터 형식을 바꾸거나 위 그림처럼 날짜 부분을 아예 포함하지 않는 등 다른 방법을 써야 합니다.
그렇다면 여기서 한번 정리해보겠습니다.
자 그럼 이제 INDEX, MATCH 함수를 활용하여 데이터를 찾아보겠습니다.
- 반환 값 범위 : 반환받을 값. 즉 검색한 값을 통해 받고 싶은 값의 범위를 지정합니다.
- 검색할 값 : 알고 있는 값. 즉 위에서는 "리플"을 알고 있기 때문에 선택해주면 됩니다.
- 값 범위 : 이 "리플"이라는 값이 들어가 있는 테이블의 범위를 지정해주면 됩니다.
- 0 : 마찬가지로 0은 일치, 1은 보다 작음, -1은 보다 큼인데 대부분 0을 사용합니다.
복잡해 보이지만 VLOOKUP보다 오히려 더 간단합니다.
그럼 아까의 데이터 형태에서 VLOOKUP에서 찾지 못했던 날짜 값을 찾아보겠습니다.
| =INDEX(C3:C7,MATCH(D12,D3:D7,0)) |
아래 사진 기준으로 코드는 위와 같습니다.
자 아래와 같이 데이터를 잘 찾아내는 것을 확인할 수 있습니다.
VLOOKUP에서는 데이터 범위의 가장 왼쪽 부분만 검색할 수 있었으나 INDEX, MATCH 함수를 활용하면
위치에 상관없이 같은 행에 있는 데이터를 찾아낼 수 있습니다.
- 혹시 아래와 같이 값이 나온다면?
셀 서식에 들어가 일반 형식으로 되어있는 것을 날짜 형식으로 바꾸시면 됩니다.
엑셀 자동화
어쩌면 데이터를 다룰 때 필수라고 볼 수 있는 VLOOKUP 함수와 INDEX, MATCH 함수를 다루어 보았는데요.
이미 많이 사용하고 계신 분도 많으실 것 같고 처음 접해보시는 분들도 있을 텐데
이 두 함수의 차이를 명확히 정의해 놓은 글들이 많이 없어 두 가지 경우를 예시를 통해 비교해보았습니다.
혹시 이해가 가지 않는 부분이 있다면 댓글 남겨주세요!
능력 있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때 까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글