내가 원하는 데이터 매칭하여 찾기
- VLOOKUP 함수 이용하기
아무도 알려주지 않아서 실무에서 혼자 맨땅에 헤딩하면서 습득한 내용을 다룹니다.
(최적화된 방법보다는 그냥 제가 할 줄 아는 방법을 다룬 것이라 이 블로그에 있는 방법이 가장 효율적인 방법은 아닐 수 있습니다!)
안녕하세요 데이빗입니다.
지난 포스팅까지 초급 편을 다루었고, 이제 중급 편 시작하도록 하겠습니다.
중급 편부터는 조금 더 복잡한 작업을 가능하게 해주는 함수들에 대해서 알아볼 예정입니다.
반복해서 말씀드리지만 초급, 중급, 그리고 고급은 지극히 주관적인 제 판단에 의한 것이고 어떠한 공식적인 근거도 없습니다. (당당)
이번 포스팅에서 다룰 함수는 제 구글 스프레드(그리고 엑셀) 인생에서 첫 충격을 가져다준 VLOOKUP 함수입니다.
그전까지는 정말 기초적으로 더하고 빼고 숫자 세고 정도로만 구글 스프레드시트를 이용하던 저에게 데이터베이스라는 것을 만들고, 그 데이터베이스를 이용해서 다양한 가공을 시도하게 해 준 (저 개인에게) 혁신적인 함수입니다.
먼저 VLOOKUP 함수에 대한 정의부터 보도록 하겠습니다.
셀(Cell)에 '=VLOOKUP('을 입력하면 아래와 같이 나옵니다.
먼저 찾을 키(Key) 값이 있습니다. 어떤 값을 찾고 싶은지를 입력하는 곳입니다.
다음으로 범위를 설정합니다. 위 예시에서는 C3부터 E7까지의 영역을 범위로 설정했습니다.
다음으로 색인을 입력하는데요, 말하자면 범위에서 키(Key) 값을 찾았을 때 같은 행(Row)에서 몇 번째 열(Column)의 값을 가져올 것인지를 정하는 것입니다.
여기에서 주의해야 할 점은, 키(Key) 값은 설정한 범위의 첫 번째 열(Column)에서만 검색을 하게 됩니다.
네, 설명을 봐도 이해 안 되시죠? 저도 그렇습니다.
예를 들어 설명하면 아래와 같습니다.
범위로 설정한 C3부터 E7까지의 영역 중 첫 번째 열(Column)인 C3부터 C7까지의 영역에서 키(Key) 값으로 입력한 '사과'를 찾습니다.
3행(Row)에 '사과'가 있네요. (C3)
그러면 이제 범위로 설정한 C3부터 E7까지의 영역 중 키(Key) 값인 '사과'가 있는 C3부터 E3까지의 영역 중 색인에 입력한 값에 해당하는 열의 값을 불러옵니다.
숫자 2를 입력했으니 C3부터 E3까지의 영역 중 두 번째 열(Column)인 D3의 값을 불러옵니다. (과일)
만약 숫자 3을 입력한다면 세 번째 열(Column)인 E3의 값을 불러올 것입니다. (빨간색)
마지막으로 설명 안 한 부분이 [정렬됨] 부분인데요, 이 부분은 그냥 무조건 FALSE로 사용한다고 이해하셔도 좋습니다.
그 정의는 검색하는 키(Key) 값이 정확히 일치하는 경우 색인된 값을 불러올지(FALSE), 정확히 일치하지 않는 경우 유사한 값을 기반으로 색인된 값을 불러올지를 결정하는 것인데요(TRUE 혹은 공란), 실제 해당 함수를 활용하다 보면 정확히 일치하는 경우에만 불러와야 신뢰도를 확보할 수 있어서 (제 기준) 거의 모든 경우 FALSE로 이용하게 됩니다.
유사한 기능으로 HLOOKUP 함수가 있습니다.
VLOOKUP이 열(Column) 방향 검색, 즉 키(Key) 값을 범위의 위아래로 훑으면서 찾는 함수라면, HLOOKUP은 행(Row) 방향 검색, 즉 키(Key) 값을 범위의 좌우로 훑으면서 찾는 함수입니다.
그렇다면 이 복잡하고 어려운 함수를 왜 알아야 할까요?
어디에 활용할 수 있을까요?
저는 항목들을 분류하고 그루핑 할 때 유용하게 활용합니다.
만약 가계부를 쓴다고 할 때, 다양한 물품을 구매할 수 있을 텐데 반복적으로 구매하는 경우도 있을 것입니다.
그때마다 사과 - 과일, 바나나 - 과일, 대파 - 채소... 이런 식으로 추가 정보를 기입하는 것이 번거로울 수 있습니다.
물론 가계부라고 가정했으니 번거로운 수준으로 끝나겠지만, 만약 마트를 운영하는 장표를 관리한다면 어떨까요? 수천 개의 브랜드의 물품들을 일괄적으로 분류한다면 번거로운 정도가 아니라 불가능에 가까울 수 있습니다.
그럴 때 위와 같이 범례표를 만들어서 수식을 걸어주면 단번에 해결할 수 있는 장점이 있습니다.
(이제야 밝힐 수 있는 커버로 사용한 재활용 이미지... 재분류 한다는 뜻 입니다!)
아래 예제에서 확인해 보도록 하겠습니다.
구글 스프레드시트 예제 >> 링크
(물론 아래와 같이 구매하는 사람은 없겠지만... 예시라고만 생각해주시면 좋겠습니다.)
예제의 내용과 같이 1/4분기 동안 사과도 사고 바나나도 사고 대파도 사면서 여러 가지 장을 봤다고 가정해 보겠습니다.
그러면 1/4분기 동안 과일을 구매하는데 지출한 비용은 총얼마일까요?
위와 같은 경우라면 두 가지 방법이 있겠습니다.
첫 번째는 과일에 해당하는 사과, 바나나, 샤인머스켓을 구매하는데 지출한 값을 각각 SUMIF 함수를 이용하여 계산한 뒤 합하여 총금액을 계산할 수 있겠습니다.
두 번째는 C3에서 E7까지의 범례를 참고하여 K열(Column)의 물품에 따라 각각 분류값을 입력해줄 수 있겠습니다.
이 또한 두 가지 방법이 있을 것인데, 하나는 일일이 입력하는 것이고 또 하나는 위에서 함께 알아본 VLOOKUP 함수를 이용하는 것입니다.
물론 위 예제의 경우 개수가 많지 않으니 일일이 입력하는 것도 그렇게 어렵지 않겠지만, VLOOKUP 함수를 익힐 겸 이용해 보도록 하겠습니다.
예제에서 O열(Column)을 참고하시기 바랍니다.
먼저 키(Key) 값은 K열(Column)에 있는 물품명이 될 것입니다.
그리고 참조할 범위는 C3에서 E7까지의 범례일 것인데, VLOOKUP 함수를 이용하는 모든 셀(Cell)에 대해 동일한 범위를 참조할 것이므로 절대참조를 해주도록 하겠습니다.
* 절대참조 하는 방법이 궁금하다면?! >> 구글 스프레드시트 - 초급편 (4)
색인은 마찬가지로 2를, [정렬됨] 값은 FALSE를 주도록 하겠습니다.
이렇게 식을 적용하면 성공적으로 '과일'이라는 값을 불러오는 것을 볼 수 있으며, 모든 셀에 붙여 넣기를 해주면 성공적으로 분류값을 입력해 줄 수 있으나, 한 가지 문제가 발생하는 것을 볼 수 있습니다.
바로 아래 이미지와 같은 무수히 많은 오류 값입니다.
구글 스프레드시트에서 나타내는 오류에 대해서는 추후 별도로 다룰 예정이므로 여기에서는 깊게 다루지 않겠습니다.
이번 포스팅에서는 VLOOKUP 함수와 그 활용에 대해 알아보았습니다.
다음 포스팅에서는 VLOOKUP 함수와 유사하게 동작할 수 있는 INDEX-MATCH 함수에 대해 알아보도록 하겠습니다.
'구글 스프레드시트' 카테고리의 다른 글
| 구글 스프레드시트 - 중급편 (3) (구글 스프레드시트의 오류 이해하기) (0) | 2023.01.04 |
|---|---|
| 구글 스프레드시트 - 중급편 (2) (INDEX-MATCH 함수 이해하기) (0) | 2022.12.29 |
| 구글 스프레드시트 - 초급편 (11) (WEEKNUM 함수 이해하기) (0) | 2022.11.03 |
| 구글 스프레드시트 - 초급편 (10) (TODAY, WEEKDAY 함수 이해하기) (0) | 2022.10.18 |
| 구글 스프레드시트 - 초급편 (9) (날짜 및 시간 분리하기) (0) | 2022.10.02 |
댓글