'엑셀 할 줄 안다'의 기준은 vlookup이라고 해도 좋을 만큼, 브이룩업은 엑셀 업무에서 가장 많이 쓰이는 함수 중에 하나입니다. 하지만 직관적으로 이해하기엔 헷갈리는 부분이 많습니다. Vlookup 함수를 예시와 함께 이해해보겠습니다.
vlookup은 세로로 된 표에서 지정한 열의 같은 행 데이터를 돌려주는 함수입니다.
예를 들어보겠습니다. 오른쪽의 표를 보고, 왼쪽 표의 가격을 채워넣고 싶습니다.
이렇게 데이터가 적을 때는 그냥 오른쪽 표 보고 왼쪽 표 채워넣으면 되지만, 데이터가 1,000행 넘어갈 때는 쉽지 않습니다. 이럴 때 사용하는 함수가 vlookup입니다. vlookup 사용법을 순서대로 알려드리겠습니다.
1. vlookup으로 구하고 싶은 값을 생각한다.
저희는 왼쪽 표에서 품목의 가격 을 구하고 싶습니다.
2. 구하고 싶은 값이 모두 포함된 표를 찾는다. (참조표)
오른쪽표를 이제부터 참조표라고 불러보겠습니다. 저희가 원하는 정보인 품목과 가격 정보가 모두 들어있는 표입니다.
3. 식을 작성한다.
=VLOOKUP(①$B3,②$E$3:$F$8,③2,④0)
①$B3 : 1에서 vlookup으로 구하고 싶은 것은 '품목의 가격'이었습니다. '품목의 가격' 중 품목이 있는 셀입니다. f4를 세번 눌러 열고정 해줍니다.
②$E$3:$F$8 : 2에서 구한 참조표로, f4로 범위 고정해줍니다.
※ 참조표의 첫 열은 ① 과 같은 값이여야 합니다. ex) 예제의 ① = 품목, ② = 참조표의 첫 열 = 품목)
③2 : 열 값을 표기해줍니다. 예제에서 참조표 내 2번째 열이 가격이므로, 2를 입력해줍니다.
품목이 1열, 가격이 2열입니다.
④0: 일치여부, 대체로 0을 사용합니다.
정리하자면
=vlookup(이거의 값 좀 구하려 하는데, 이 표에서, n번째 열 값 가져와줘, 똑같은 것만!)
이렇게 되겠습니다.
식을 제대로 작성했는데 #N/A 가 나타난 다면 다음 경우를 의심해봐야합니다.
① 찾으려는 값의 품목과 참조표의 첫 열이 일치하지 않는 경우
② 참조표에 찾으려는 값이 존재하지 않는 경우
(ex) 예제의 경우 참조표에서 딸기가 없다면, 딸기 가격에 #N/A가 뜨게 됩니다.)
③ F4 고정을 하지 않아 값이 밀린 경우
(고정하지 않는 경우 한칸씩 밀려 잘못된 경우를 참조하여, #N/A가 뜨는 경우가 있습니다.)
조금 어려운 예제도 알려드립니다.
먼저 #N/A가 뜨는 경우 중 ①번 CASE입니다.
난 '품목의 가격'을 구해야 하는데 참조표의 첫 열이 가격입니다.
이런 경우 어떻게 처리하는 것이 좋을까요?
참조표의 열 순서를 바꿔주는 것으로 해결할 수 있습니다.
즉, 가격열(예제의 E열) 선택 → CTRL + X(잘라내기) → 첫 열 다음 열에 붙여넣기 순서로 진행 후
첫 예제와 동일하게 VLOOKUP 사용해 주시면 됩니다.
이제 실무에서 진짜로 사용하곤 했던 최종 예시도 알려드리고 글을 마무리하겠습니다.
VLOOKUP을 이용하여 왼쪽 표를 채우려고 합니다.
어떻게 하는 것이 좋을 지 한 번 고민해보시고, 아래 방법 대로 따라오시길 바랍니다.
1. 품목을 정렬해줍니다.
11월 품목, 12월 품목을 합쳐 한 줄로 만든 후, 데이터 > 중복된 항목 제거를 사용하면
중복되지 않는 11월 품목과 12월 품목을 정렬할 수 있습니다.
2. 11월 매출을 먼저 구해보겠습니다.
구하고자 하는 것은 '품목의 매출'입니다.
=VLOOKUP(품목, 참조표($F$3:$G$7), 2(매출이 2열에 있음),0)
3. 마찬가지로 12월 매출을 구해보겠습니다.
=VLOOKUP(품목, 참조표($I$3:$J$8), 2(매출이 2열에 있음),0)
4. 이렇게 구하면 #N/A 값이 발생합니다.
당연합니다. 11월에는 육회비빔밥, 해물라면이 참조표에 없고, 12월에는 탕수육이 참조표에 없습니다.
5. 표를 정리해줍니다.
그래서 VLOOKUP 등 함수를 사용하고 난 뒤에는
값 복사(ctrl + c) → 값 붙여넣기 (ctrl + alt + v , 값 선택)
작업을 통해 수식을 값으로 만들고, 오류값을 확인하여 정비해줍니다.
저는 ctrl + h를 통해 #N/A 값을 0으로 바꿔주었습니다.
매출증감 열의 경우 12월 매출 - 11월 매출 식을 활용하여 구했습니다.
※오류값은 상황에 따라 다르게 해결하면 됩니다. 가령 참조표가 잘못된 데이터라면 참조표를 수정해야하고, 육회비빔밥이 12월 신메뉴라면 11월 값은 미판매로 수정해도 됩니다. 예시에서는 0으로 바꿔주면 매출 증감 등 추이를 볼 때 편리하기 때문에 0으로 바꿔주었습니다.
브이룩업이 뭔데 이렇게 괴롭히나 싶지만, 한 번 익혀두면 정말 기계처럼 많이 쓰는 함수인 것 같습니다. 다소 복잡한 예시도 알려드렸지만 어떻게 쓰는 지만 알면 다양한 방식으로 활용할 수 있습니다.
그럼 오늘도 파이팅입니다:)
'마케터 아린 > 일잘러 아린' 카테고리의 다른 글
| 무료 목업 사이트 추천, 목업파일 쓰는 법 (0) | 2022.03.11 |
|---|---|
| 노션 한 줄에 표 두 개 넣는 법, 노션 서식 자유롭게 꾸미기 (0) | 2022.02.16 |
| [PPT 강의] 특정 자음만 칠하기, 숫자 그래프 만들기 (0) | 2022.02.15 |
| [노션 강의] 노션 필터링된 값 합만 구하기, 노션 함수 이용하기 (0) | 2022.02.14 |
| 자주 사용하는 엑셀 단축키 모음과 찐 실무 꿀팁 (0) | 2022.02.04 |
