회사생활 10년간 기획부서에서 다양한 분석과 보고를 했던 경험을 토대로, 회사에서 자주 쓰는 함수, 기능, 편의사항, 다양한 분석사례, 보고자료로써의 PPT 작성법 등을 포스팅하려고 합니다. 실제 분석/보고 했던 유형들을 사례로 같이 소개도 하고자 합니다.
그 세 번째 시간으로...
많은 사람들이 가장 먼저 배운다는 VLOOKUP, HLOOKUP에 대해 설명드리겠습니다.
VLOOKUP, HLOOKUP (MATCH)
아래 표는 00 전자의 10월 마감 실적 현황 표입니다.
위의 10월 마감 실적 현황에서 '성남' & '강남' 점의 10월 마감 매출만 노란색 음영에 가져와 보겠습니다.
=VLOOKUP(특정값, 찾고자 하는 값이 있는 범위, 오른쪽으로 이동 횟수, 숫자 0) 이렇게 구성되어 있으니,
=VLOOKUP(K5,$D$5:$I$10,6,0)로 작성하면 됩니다.
위와 같이 작성된 '성남' 점의 10월 매출 셀 (L5)을 복사하여 아래 '강남' 점 매출 셀 (노란 음영)에 붙이면, 똑같이 '강남' 점의 10월 매출이 추출됩니다. 아까 $ 표시를 통해 고정한 범위는 이동 없이 그대로 행열 모두 고정되어 있는 것을 확인할 수 있습니다.
다음으로 HLOOKUP에 대해 설명드리겠습니다.
VLOOKUP이 왼쪽에서 오른쪽으로 값을 찾는 함수라고 하면, HLOOKUP 은 위에서 아래로 값을 찾는 함수라고 생각하면 됩니다. 함수 형식은 VLOOKUP과 동일합니다.
아래 표는 12월 1주 차 요일별 매출, 객수, 객단가 현황입니다.
오른쪽 세 개 요일들의 매출을 HLOOKUP으로 가져와보겠습니다.
=HLOOKUP(특정값, 찾고자 하는 값이 있는 범위, 아래쪽으로 이동 횟수, 숫자 0) 이렇게 구성되어 있으니,
=HLOOKUP(H10,$B$4:$J$7,2,0)로 작성하면 됩니다.
지금까지 VLOOKUP과 HLOOKUP에 대해 설명드렸습니다.
하지만, 이들 함수 사용에 있어 실제 업무에서는 조금 불편한 부분? 한마디로 '단점'이 존재합니다.
1) 함수 내에 이동 횟수를 '3'으로 입력해 놓은 상태에서, 표 중간에 열 하나를 추가하여 새로운 항목을 넣게 되는 경우에는 이동 횟수가 한 번 더 늘어나기 때문에 이동 횟수 '3'을 '4'로 수기 변경해줘야 합니다. 위에 표가 간단해서 그렇지, 몇만 몇십만 행의 RAW라면 하나하나 수식들을 찾아서 '3'을 '4'로 수기 변경해야 합니다.
2) 아래와 같이 VLOOKUP 사용하여 매출실적을 가져온 상태에서, 해당 수식을 수량, 오픈일 항목에 복사 붙여 넣기 한다고 해도 그대로 수량 값, 오픈일 값이 추출되지 않습니다. 매출에서 이동 횟수를 '6'으로 수기 입력했기 때문에, 해당 수식을 복사해서 단순히 수량 쪽에 붙여 넣는다면, '수량' 항목은 위 표에 지정한 범위 안에서 4번째 열이기 때문에 이동 횟수를 '6'에서 '4'로 수기 변경해야 정상 값이 나옵니다.
참 유용한 함수이긴 하나, 하나하나 수기로 이동 횟수를 변경해야 한다는 게 참 번거롭습니다. 함수를 쓰는 이유가 전혀 없게 되는 거죠. 만약 다른 RAW 데이터에서 아래 노란색 음영 내용을 모두 VLOOKUP으로 가져온다면? 이동 횟수 변경을 5~6번 해야 합니다. 아니 항목이 더 많으면 100번도 해야 할 수도 있습니다.
그래서 방법은 두 가지가 있습니다.
1) '이동 횟수' 입력 대신에, MATCH 함수를 입력
● MATCH (특정값, 특정값이 포함되어 있는 범위, 숫자 0)
: 해석 - 특정값이 포함되어 있는 범위 내에서 해당 특정값이 몇 번째에 있는지 숫자로 추출하는 함수
- 범위를 세로로 지정 시 아래로 몇 번째, 가로로 지정 시 오른쪽으로 몇 번째인지 숫자로 추출됨.
MATCH 함수의 정의는 위와 같습니다.
아래 표에 현재 VLOOKUP 내 이동 횟수 숫자를 수기로 입력하지 않고, MATCH 함수를 통해 '매출' 항목이 '대리점명' 항목에서 오른쪽으로 몇 번째에 있는지를 숫자로 추출하게 해 놨습니다.
=MATCH (특정값, 특정값이 포함되어 있는 범위, 숫자 0) 형식 임으로,
=MATCH(G13,$D$5:$I$5,0)를 작성하여 이동 횟수 입력 위치에 대신 넣었습니다.
그래서 MATCH 함수를 포함한 VLOOKUP 최종 식은?
=VLOOKUP($F14,$D$5:$I$10, MATCH(G13,$D$5:$I$5,0),0) 이렇게 됩니다.
그럼 위 수식을 '수량' 항목 쪽에 복사 붙여 넣기 하면, '수량' 항목이 '대리점명' 항목에서 4번째에 있기 때문에 숫자 ' 4'로 자동 변경되어 VLOOKUP이 완성됩니다. 그럼 '성남' 점 수량의 값이 제대로 나오게 됩니다. 아래와 같습니다.
2) INDEX / MATCH 함수 활용
해당 내용은 다른 INDEX 함수 설명 포스팅에서 자세히 다루도록 하겠습니다. 한마디로, VLOOKUP / HLOOKUP 대신에 INDEX / MATCH 함수를 쓰면 아무리 표 중간에 행 / 열을 하나 추가한다고 해도 추가된 것만큼 같이 움직이기 때문에 결괏값이 정상적으로 나옵니다. 그래서 나중에는 VLOOKUP / HLOOKUP을 쓰지 않고, INDEX / MATCH 함수만 쓰게 될 것입니다.
지금까지 VLOOKUP / HLOOKUP에 대해 설명드렸습니다.
가장 많은 사람들이 또 분석할 때면 꼭 쓰는 함수이나, 단점이 있는 함수로서, MATCH 함수로 추가 보완하여 함께 사용하면 실무에서 더욱더 빠르고 편하게 VLOOKUP / HLOOKUP을 사용할 수 있습니다.
그럼 다은 포스팅에 뵙겠습니다.
읽어 주셔서 감사합니다.
'알아두면 유용한 이야기 > 엑셀' 카테고리의 다른 글
| 10년차 직장인이 알려주는 엑셀 꿀팁 : 엑셀에서 PPT로 자동 연결시켜 쉽게 붙이기 (feat.연결하여 붙여넣기) (0) | 2024.03.24 |
|---|---|
| 10년차 직장인이 알려주는 엑셀함수 : COUNT, COUNTA (COUNTIF) (0) | 2022.12.19 |
| 10년차 직장인이 알려주는 엑셀함수 : SUM, AVERAGE(CAGR) (0) | 2022.12.18 |
댓글