엑셀실무 MD 매출분석 VLOOKUP INDEX MATCH LARGE함수 사용

오늘은 매출표를 보고 간단하게 분석할 때 알아두면 유용한 함수들을 가져와봤습니다. 이번에 알려드릴 함수는 VLOOKUP, INDEX, MATCH,LARGE,SMALL 함수로 응용하기 쉬운 함수들이니 MD 초보자분들도 이해하기 쉬울 겁니다. 그럼 엑셀실무에 유용한 함수들 바로 사용해 보러 가시죠!

 

① 단가표가 따로 존재한다? VLOOKUP 이용해 단가 넣기!

	A	B	C	D	E	F	G	H
1	판매채널	상품명	입고량	판매량	재고	단가	판매율	매출
2	11번가	키위	1775	1293	482		73%	10,344,000
3	위메프	바나나	1768	1340	428		76%	6,700,000
4	지그재그	자몽	1874	1452	422		77%	7,260,000
5	에이블리	딸기	1643	1154	489		70%	3,462,000

 

단가표
	A	B
6	딸기	6000
7	자몽	8000
8	바나나	3000
9	키위	5000

중국에서 제품이 들어올 경우 물류비, 관세로 인해 단가가 매번 달라져 따로 단가표를 만들기도 하고, 자체 제작상품이 많을 경우에도 단가표를 따로 정리하는 경우가 있습니다. 이 때 어떤 제품의 단가를 불러오고 싶다면 VLOOKUP함수를 이용하면 됩니다.

VLOOKUP함수는 데이터 자료에서 필요한 값을 뽑아내는 함수로 찾을값은 항상 왼쪽에 위치해 있어야 사용 가능합니다. 위의 표를 예를 들어 과일들의 단가를 알고 싶습니다. 단가표를 보니 과일들이 전부 표 왼쪽에 위치해 있죠? 이럴 경우 VLOOKUP함수 사용이 가능합니다. 만약 찾을 값이 왼쪽에 위치해 있지 않다면 INDEX, MATCH함수를 사용해야 하고요!

아무튼 F2값 즉, 키위의 단가를 알고 싶다면 다음의 식으로 적으면 됩니다.

 

=VLOOKUP(찾을값,데이터 포함 전체범위,열번호,0 or1) 

=VLOOKUP("B2", $A$6:$B$9,2,0)

 

· 찾을값 : "키위" 이렇게 적어도 되고 키위가 B2 자리에 있으니 B2라고 적어도 됩니다.

· 데이터 범위 : 키위의 단가를 알고 싶으면 어떤 데이터를 참고하느냐? 당연히 단가표를 참고해야 하니 단가표 전체범위 A6:B9를 지정해 줍니다. 여기서 절대참조인 $를 해준 이유는 단가표 위치는 움직이면 안 되기 때문입니다. 바나나든 자몽이든 동일한 단가표 내에서 값을 찾기 때문에!

· 열번호 : 단가가 위치한 열번호를 입력해 줍니다. 우리가 지정한 범위 A6:B9에서 단가는 2열에 있으니 숫자 2 입력!

· 마지막 0 or 1의 경우 0은 정확히 일치하는 값, 1은 유사값을 말하는데요. 보통 정확히 일치하는 값을 쓰기 때문에 0을 쓴다라고 외워 두면 됩니다.

 

② 상·하위 3개 매출 합계, 매출 평균 구할 땐 LARGE, SMALL 함수 이용!

매출 견인 역할을 하는 제품들은 보통 베스트 10에 드는 제품들입니다. 베스트 제품들의 매출 합이 총매출 대비 얼마큼 차지하는지 비교하기도 하는데 만약 상위 TOP3의 매출 합이나 평균을 알고 싶다면 어떻게 해야 할까요?

 

일단 합계는 SUM 함수를, 평균은 AVERAGE 함수를 사용합니다. 상위 값을 구할 땐 LARGE함수를 사용하고요!

LARGE는 LARGE(범위, 몇 번째로 큰 값)로 지정한 범위에서 몇 번째 큰 값을 가져올 거냐 할 때 쓰는 함수입니다. 예를 들어 내가 A1:A5 범위에서 2번째로 큰 값을 가져오고 싶다면 =LARGE(A1:A5,2) 라고 적으면 됩니다. 5번째로 큰 값을 가져오고 싶다면 =LARGE(A1:A5,5) 라고 적으면 되지요!

 

	A	B	C	D	E	F	G	H
1	판매채널	상품명	입고량	판매량	재고	단가	판매율	매출
2	11번가	키위	1775	1293	482	5,000	73%	10,344,000
3	위메프	바나나	1768	1340	428	3,000	76%	6,700,000
4	지그재그	자몽	1874	1452	422	8,000	77%	7,260,000
5	에이블리	딸기	1643	1154	489	6,000	70%	3,462,000

하지만 우리는 첫 번째, 두 번째, 세 번째 큰 값을 모두 가져와 그 합을 알고 싶습니다. 이럴 경우에는 위 표를 예시로

=SUM(LARGE(H2:H5,{1,2,3})) 라고 적으면 됩니다. 쉽죠? 대괄호와 몇 개 가져올 건지만 기억하시면 됩니다!

상위 2개의 매출 합계를 구하고 싶다면 =SUM(LARGE(H2:H5),{1,2})) 라고 적으면 되고요.

상위 3개의 평균을 알고 싶다면 SUM 대신 AVERAGE를 쓰면 됩니다. =AVERAGE(LARGE(H2:H5,{1,2,3}))

 

반대로 하위는 LARGE의 반대 SMALL을 적어주면 됩니다. SMALL은SMALL(범위, 몇 번째로 작은값)로 지정한 범위에서 몇 번째 작은 값을 가져올 거냐 할 때 쓰는 함수입니다. 

첫 번째, 두 번째, 세 번째 작은 값을 모두 가져와 그 합을 알고 싶다면 =SUM(SMALL(H2:H5,{1,2,3})) 

하위 3개의 평균을 알고 싶다면 =AVERAGE(SMALL(H2:H5,{1,2,3}))

 

 

③ 판매량 1위, 꼴찌 상품을 알고 싶다면 INDEX, MATCH 함수 사용

	A	B	C	D	E	F	G	H
1	판매채널	상품명	입고량	판매량	재고	단가	판매율	매출
2	11번가	키위	1775	1293	482	5,000	73%	10,344,000
3	위메프	바나나	1768	1340	428	3,000	76%	6,700,000
4	지그재그	자몽	1874	1452	422	8,000	77%	7,260,000
5	에이블리	딸기	1643	1154	489	6,000	70%	3,462,000

판매량이 1위인 상품의 이름을 알고 싶을 경우에는 어떻게 해야 할까요? 일단 판매량 1위가 무엇인지 찾아야겠죠? 앞서 우리가 상위 매출을 찾을 때 LARGE 함수를 사용했듯이 판매량 1위도 LARGE함수를 사용합니다.

= LARGE(범위, 몇 번째로 큰 값)

= LARGE(D2:D5,1)

= 1452

판매량 범위에서 첫 번째로 큰 값이 바로 판매량 1위이고 값은 1452가 나옵니다.

 

자 그렇다면 1452를 기록한 상품명의 이름을 찾으려면 어떻게 해야 할까요? 바로 몇 행 몇 열에 어떤 값이 있는지 말해주는 INDEX MATCH 함수를 사용하면 됩니다. INDEX와 MATCH함수는 짝꿍이라고 생각하면 되는데요! 이전 포스팅에서도 언급했지만 INDEX안에 MATCH가 들어간 공식으로 그냥 외우면 편합니다. (더 자세한 내용은 아래 링크 클릭▼)

직장인 엑셀 INDEX MATCH 함수 사용법

 

MATCH함수는 몇 행 또는 몇 열에 있는지를 알려주는 함수로 =MATCH(찾을 값, 지정범위, 0 or 1 or -1) 라고 적습니다.

지정범위 뒤에 0,1,-1 개념은 위의 VLOOKUP처럼 일치값이냐 유사값이냐를 묻는 건데요! 보통 0을 쓰기 때문에 0을 쓴다고 외우면 됩니다. 우리가 답을 도출할 때 보통 일치값을 찾기 때문이죠!

아무튼 우리는 1452라는 값이 몇 행 또는 몇 열에 있는지 알고 싶습니다. 참고로 지정범위를 세로로 지정하면 행번호가 나오고 가로로 지정하면 열번호가 나옵니다. 우리가 엑셀에 데이터를 입력할 때 보통 세로 방향으로 적기 때문에 MATCH함수를 사용할 땐 보통 행번호가 나옵니다. 헷갈린다면 'MATCH함수는 행번호 찾을 때 쓴다!'라고 외워도 됩니다.

=MATCH(찾을 값, 지정범위, 0 or 1 or -1)

=MATCH(LARGE(D2:D5,1),D2:D5,0)

=3

 

· 찾을값 : 판매량 1위를 찾는 거니 아까 전에 사용한 LARGE함수 식을 그대로 복붙 해서 넣어줍니다.  

· 데이터 범위 : 판매량 범위를 지정해 줍니다.

· 마지막 0 or 1의 경우 0은 정확히 일치하는 값, 1은 유사값을 말하는데요. 보통 정확히 일치하는 값을 쓰기 때문에 VLOOKUP처럼 0을 쓴다라고 외워 두면 됩니다.

 

우리는 지정범위를 세로로 지정했기 때문에 결괏값은 행번호가 나옵니다. 값이 3이 나왔으니 3열에 1452가 있구나!라고 알아냈습니다. 그럼 이제 열번호를 찾아서 상품명을 찾아냅시다!

 

INDEX함수는 =INDEX(지정범위,행번호,열번호)로 지정범위 내에서 행과 열번호를 입력하면 그 위치값을 불러옵니다.

=INDEX(B2:B5,MATCH(LARGE(D2:D5,1),D2:D5,0),1)

=자몽

 

· 지정범위 : 판매량 1위의 상품 이름을 찾는 거니 상품명 범위를 지정해 줍니다. 

· 행번호 : MATCH함수를 통해 행번호를 찾았죠? 그대로 복붙 해서 넣어줍니다. 

· 열번호 :  지정범위 B2:B5는 열이 1개죠? 그래서 1을 적습니다. 보통 1을 적을 때 생략하기도 합니다.

 

 

판매량 1위인 상품이름을 찾았다면 판매량 꼴찌 상품은 어떻게 찾을까요? 판매량 꼴찌이니 SMALL을 쓰면 됩니다. 마지막 공식에 LARGE를 SMALL로만 바꾸면 끝!

=INDEX(B2:B5,MATCH(SMALL(D2:D5,1),D2:D5,0),1)

=딸기

 

 

---

이것으로 매출 분석 때 여러 방면으로 활용하기 좋은 VLOOKUP, INDEX, MATCH, LARGE, SMALL함수에 대해 알아봤습니다. 다음 포스팅도 유익한 정보로 찾아뵙겠습니다. 끝까지 읽어주셔서 감사드립니다.

 

 

[ 참고하면 좋은 글 ]

엑셀실무 VLOOKUP INDEX MATCH 사용법