파워 쿼리와 Vlookup, Index+Match 함수 비교(1)

엑셀에서 자주 사용하는 함수로는 Vlookup과 Index, Macth함수가 있는데,

자꾸 사용하다 보면 익숙해지지만, 처음에는 어떻게 사용하는지 막막한 함수입니다.

 

엑셀을 사용하기 전에는 간단한 사칙연산과 합계, 평균 등만을 사용하다가

Vlookup함수의 인수로 4가지가 들어가야 한다는 것이 벽으로 다가옵니다.

 

이러한 것을 간단하게 사용할 수 있게 하는 것이 파워 쿼리의 쿼리 병합 기능입니다.

 

아래와 같이 일자별, 지점별 매출액 데이터가 있고, 두 번째는 지점별 지점장 데이터, 세 번째는 일자별, 지점별 인원수 데이터가 있다고 할 경우 지점별 지점장과 인원수를 구하려면 아래와 같이 Vlookup 또는 Index, Match함수를 사용해서 구해야 합니다.

파워쿼리와 vlookup,index+match.xlsx

0.02MB

 

 

 

1. 해당 지점의 지점장 알아내기 - Vlookup 함수

이것이 대표적인 Vlookup함수의 사용예입니다.

 

지점에 해당하는 지점장 데이터가 세로로 배치되어 있고, 해당 지점의 지점장을 알아내기 위해서는 왼쪽 표의 지점명에 해당하는 지점장을 오른쪽 표에서 가져오면 됩니다.

 

지점장과 인원수를 구하려고 하는데, D열에만 입력이 가능하므로 D와 E열(열 이름 D를 클릭한 후 열 이름 E까지 끔)을 선택한 다음 삽입 메뉴를 눌러 빈 열을 2개 삽입니다.

 

D1셀에 지점장이라고 입력합니다. 그러면 표가 D열까지 확장됩니다.

 

D2셀에 Vlookup 수식을 입력하는데,

인수는 찾을 값, 찾을 범위, 순번, 유사 또는 정확히 일치 여부 순으로 4개를 입력합니다.

 

D2셀을 기준으로 설명하면 찾을 값은 B2이고,

찾을 범위는 지점명과 지점장이 있는 G2셀에서 H4셀까지 연속된 범위이고,

순번은 찾을 범위에서 지점명이 1, 지점장이 2이므로 2가 되며,

유사 일치 또는 정확히 일치여부는 유사 일치가 기본값 True이고, 정확히 일치여부가 False인데, 정확히 일치해야 하는 하므로 False 또는 0이라고 입력합니다. 

 

D2셀에 =vlookup( 이라고 입력하고, B2셀을 클릭하면 [@지점명]이라고 입력됩니다.

 

, (쉼표)를 입력한 후 마우스로 끌어서 G2셀에서 H4셀까지 선택하면 G2:H4가 아니라 표이름인 표2가 입력됩니다.

이번에는 세 번째 인수로 2를 입력하고, 네 번째 인수로 0을 입력한 다음 괄호를 닫습니다.

 

그리고, 엔터 키를 누르면 표가 아닌 범위일 때와 달리 D열에 수식이 한꺼번에 입력됩니다.

 

2. 해당 일자, 지점의 인원수 구하기

 

가. Vlookup 함수를 이용하는 경우

 

(1) 실패 사례

Vlookup함수는 두 가지 조건에 맞는 값을 찾을 수 없습니다.

다시 말해 E2셀에 =VLOOKUP([@일자]&[@지점명],표3,3,0)라고 입력하면 #N/A 오류가 발생합니다.

 

(2) 성공 사례

이 때는 A열을 클릭하고 삽입 메뉴를 눌러 열 하나를 추가한 다음 A2셀에 =을 입력한 다음 A2셀을 클릭한 다음 &(결합 연산자)를 입력하고, B2셀을 클릭하면 

=표1[@일자]&표1[@지점명] 이 입력되는데, 복사해서 A10셀까지 채웁니다.

 

그리고, 찾음 범위에도 같은 값이 있어야 하므로

K2셀 =표3[@일자]&표3[@지점명]라고 입력하고,

복사해서 K18셀까지 채웁니다.

 

그리고, F2셀에

=VLOOKUP(A2,$K$2:$N$19,4,0)라고 입력하면

마찬가지로 , F10셀까지 수식이 자동으로 채워지고, 원하는 값인 인원수가 알맞게 구해집니다.

 

나. Index + Match 함수를 이용하는 경우

index함수는 일정한 범위에서 정해진 행과 열에 해당하는 값을 반환하는(구해주는) 함수이고,

Match함수는 일정한 범위에서 정확한 값의 위치를 반환하는(구해주는) 함수입니다.

 

위와 같은 경우 A열과 K열과 같이 일자와 지점명을 결합한 열을 별도로 만들 필요 없이

index와 match함수를 결합해서 원하는 인원수를 구할 수 있습니다.

 

match함수로 일자(B열)와 지점명(C열)에 해당하는 값을 찾는데, 찾을 범위를 L열과 M열을 &로 결합해서 지정하고, 찾는 방법은 정확히 일치해야 하므로 세 번째 인수로 0을 입력합니다.

 

그러면 O2셀의 수식은 =MATCH([@일자]&[@지점명],표3[일자]&표3[지점명],0)이 되고, G2셀의 값 1은 인원수의 첫 번째 행을 가리키며, G3셀의 값 4는 인원수의 네 번째 행을 가리킵니다.

 

이제 N열에서 원하는 행의 값을 index 함수를 이용해 구하면 됩니다.

index함수에서 첫 번째 인수는 구할 값의 범위인 N2셀에서 N18셀까지, 인원수이고, 

두 번째 인수는 위에서 Match함수로 구한 값입니다.

다시 말해 Match 함수 앞에 =INDEX(표3[인원수], 를 입력하고, 맨 끝에서 괄호만 닫으면 됩니다.

완성된 수식은 =INDEX(표3[인원수],MATCH([@일자]&[@지점명],표3[일자]&표3[지점명],0)) 입니다.

 

F열에서 Vlookup함수로 구한 인원수와 G에서 index와 Match함수를 결합해 구한 인원수가 같습니다.

 

표의 장점은 해당 표의 데이터가 늘어나더라도 범위가 자동으로 변경된다는 것입니다.

 

다시 말해 B11셀에 1/13과 A를 입력하면

표를 이용한 G11셀의 값은 9라고 제대로 구해지는데,

F11셀의 수식은 A열을 참조하는데 A11셀에 값이 없기 때문에 값이 제대로 구해지지 않는 차이점이 있습니다.

 

파워쿼리와 vlookup, index+match함수(완성).xlsx

0.02MB