파워 쿼리와 VLookup 비교(1)

1. 문제

아래와 같은 매출자료를 바탕으로 여러 가지 검색 및 집계를 해보겠습니다.

매출자료.xlsx

0.01MB

 

2.Vlookup 함수의 장점 및 한계

가. 장, 단점

VLookup 함수는 찾을 값을 빨리 찾아주는 기능이 있는데,

찾고 자 하는 값이 찾을 범위의 첫 번째 열에 있어야 하고, 반환받을 값은 인덱스가 +여야지 -면, 다시 말해 찾을 범위의 첫 번째 열의 왼쪽에 있으면 안 됩니다.

그리고, 중복된 값이 있어도 첫 번째 값만 반환해 주는 한계가 있습니다.

 

나. VLookup 함수를 이용한 예시

(1) A지점에 해당하는 매출일자 및 매출액 찾기

 

(가) 매출일자 찾기

E2셀에 A지점이라고 입력하고, F2셀과 G2셀에 A지점에 해당하는 매출일자와 매출액을 구해보겠습니다.

 

먼저 매출일자를 구하는 수식은 아래와 같습니다.

=VLOOKUP(E2,$A$2:$C$14,2,0)

 

찾을 값은 A열에 있고, 매출일자는 두 번째, 매출액은 세 번째에 있으므로

첫 번째 인수인 찾을 값을 E2셀로 지정하고, 찾을 범위는 A2셀에서 C14셀까지 마우스로 끈 다음 F4키를 눌러 절대참조 형식으로 바꾸고, 매출일자를 찾는 것이므로 2를 입력하고, 네 번째 인수는 정확히 일치여부인데, 정확히 일치가 False, 0입니다.

 

그런데 반환값이 날짜가 아니라 45293으로 표시되는데, 날짜는 1900-01-01가 숫자 1이고, 이때부터의 연속된 번호가 날짜입니다. 따라서, 표시 형식을 간단한 날짜로 바꾸면 날짜로 바뀝니다. 45293이 2024-01-02이랍니다.

 

그리고, 위 수식에서 E2에서 E는 오른쪽으로 이동하더라도, 다시 말해 매출일자를 구해기 위해 수식을 복사하더라도 고정되어야 하므로 $E2라고 혼합참조형식으로 바꾸는 것이 좋습니다.

 

(나) 매출액 찾기

F2셀 오른쪽 아래 네모 표시 채우기 핸들을 G2셀까지 오른쪽으로 끌면 수식이 복사되고, 마찬가지로 2024-01-02가 반환됩니다. 따라서 인덱스에 해당하는 2를 3으로 수정합니다.

 

G2셀을 클릭하고, 세 번째 인수를 3으로 바꾸면 이상한 1982-02-18로 표시되는데, 표시형식에서 ,(쉼표, 회계)를 눌러 숫자로 바꿉니다.

 

위와 같이 A지점에 해당하는 데이터가 여러 개이지만 맨 처음 데이터만 표시됩니다.

 

(2) A지점, 특정일자에 해당하는 매출액 찾기

이제 데이터가 하나만 있도록 지점명과 매출일자를 입력하면 데이터가 고유하기 때문에 맨 처음 데이터를 보여주더라도 맞는 값입니다.

 

날짜인데 숫자로 표시된다면 F열의 F글자 부분을 클릭한 후 간단한 날짜로 바꾸고, 매출액에 쉼표가 안 들어가 있다면 마찬가지로 G열 부분을 클릭한 후 표시형식 그룹에서 쉼표를 클릭합니다.

 

F3셀에 1/5라고 입력합니다. 연도를 입력하지 않으면 올해의 연도가 자동으로 반영됩니다.

그리고, G3셀에 두 가지 조건을 찾는 수식을 입력하고 찾을 범위를 입력해야 하는데 첫 번째 열에 지점명과 매출일자가 결합된 데이터가 없으므로 C열을 클릭한 후 마우스 오른쪽 버튼을 누르고 삽입 메뉴를 눌러 B열과 C 열 사이에 열을 추가합니다.

 

그랬더니 매출일자는 값이 변하지 않는데, 매출일자의 인덱스가 3에서 4로 바뀌어서 값이 없는 것으로 표시됩니다.

 

이제 C열에 지점명과 매출일자를 연결해서 넣어야 하므로 C2셀에 =A2&B2라고 입력하고, 채우기 핸들을 더블 클릭합니다. 날짜가 2024-01-02로 표시되지 않지만 숫자라도 괜찮습니다.

 

이제 H2셀에 =VLOOKUP(F3&G3,$C$2:$D$14,2,0)라고 입력합니다.

A지점의 1/5일 자 매출액 32,000이 맞게 구해졌습니다.

 

(3) Index + Match 함수 이용

위와 같이 2가지 조건을 만족하는 값을 찾는데 결합된 데이터가 없다면

index + match 함수를 사용하는 것이 하나의 대안입니다.

=INDEX(D2:D14,MATCH(F3&G3,A2:A14&B2:B14,0))

 

VLookup 함수를 위와 같이 사용해서 =VLOOKUP(F3&G3,A2:A14&B2:B14,3,0)라고 찾을 범위를 &로 결합하면 반환받을 값의 인덱스를 지정할 수가 없어서 원하는 값을 얻을 수 없습니다.

 

매출자료(vlookup).xlsx

0.01MB

 

 

파워 쿼리에 대해서는 다음 편에서 다루도록 하겠습니다.