[엑셀꿀팁] Vlookup, Index & match, 파워쿼리 병합 비교 해보자

오늘은 회사 동기의 요청으로 엑셀에서 가장 많이 쓰는 vlookup기능을 설명하면서 vlookup의 심화버전도 함께 보여드리겠습니다. 

 

이와 같은 함수를 사용하는 이유는 원본 데이터에서 필요한 데이터를 보기 위해 사용합니다. 

다양한 목적이 있겠지만, 실무에서 일하다보면 꼭 필요하니 목적성에 대해서는 길게 설명하지 않겠습니다. 

 

---

목차 

1. vlookup 사용법 기본
2. vlookup & match 사용 응용
3. Index & match 사용법
4. 파워쿼리 병합

---

1. vlookup 사용법 기본

- 마이크로 소프트 기준 정의-

테이블 또는 행에 따라 범위를 찾아야 하는 경우 VLOOKUP을 사용 합니다. 

가장 간단한 형식의 VLOOKUP 함수는 다음과 같은 의미입니다.

=VLOOKUP(찾아보는 항목, 반환할 값을 포함하는 범위의 열 번호, 대략 또는 정확한 일치를 반환합니다. 1/TRUE 또는 0/FALSE로 표시).

저희는 원본 데이터 중에서 '코뿌리,에레브, 붐볼 ... 등' 의 체력, 공격, 방어, 스피드, 특수의 값을 알아보고 싶습니다. 

먼저 VLOOKUP(찾을 값, 테이블 범위, 열 번호, 일치상태) 중 찾을 값을 지정해야 합니다. 

 

먼저 코뿌리의 체력값을 반환하고 싶은 경우, 코뿌리를 찾을 값으로 지정해야 합니다. 

코뿌리는 B2에 위치하고 있습니다. 

=VLOOKUP($B2,테이블 범위, 열 번호, 일치상태) 입니다. (※ 열에 '$' 표시는 고정시키는 용도입니다. 현재 B열을 고정시켜야 하므로 B열 앞에 $ 표시 해두었습니다. VLOOKUP시 자주 사용하니 꼭 다른 블로그 등을 통해 확인하여 주세요.)

 

다음은 테이블 범위를 찾아야 합니다.

테이블 범위는 코뿌리 및 전체 참조할 수 있는 데이터를 찾아야 합니다.

현재 저는 '참조값'이라는 시트에 포켓몬 전체 데이터가 있습니다. 

전체 데이터를 불러오겠습니다. 

 

=VLOOKUP($B2,참조값!$B$2:$I$152,열 번호, 일치상태)

참조값 (2) 시트에서 데이터가 저장되어 있습니다.

값을 들고올 때 주의해야될 부분은 찾을 값의 열부터 들고와야 한다는 점입니다. 

저희는 코뿌리를 가진 '포켓몬'이라는 열부터 들고와야 하기 때문에 B2열 부터 들고왔습니다.

=VLOOKUP($B2,참조값!$B$2:$I$152,열 번호, 일치상태)

 

위와 마찬가지로 $를 쓴 이유는 테이블을 고정하고 싶기 때문입니다. 

(※ 이해가 잘 안되시면, 반드시 다른 글을 참고 부탁드립니다. 다음에 참조 관련하여 블로그에 올리겠습니다.) 

B2:I152를 참고한다는 말은 B열 2행에 있는 셀부터 I열 152행의 셀까지 참고한다는 뜻입니다.

B열 2행은 '이상해씨'라는 셀 값이며, I열 152행은 뮤의 특수값 입니다. 

 

다음은 열 번호 입니다. 

저희가 확인하고 싶은 건 일차적으로 코뿌리의 체력 값입니다.

=VLOOKUP($B2, '참조값 (2)'!$B$2:$I$152, 4, 일치상태)

 

코뿌리의 체력 값은 코뿌리로부터 오른쪽으로 4번째에 위치하고 있습니다. 원본의 순서가 중요합니다(여기서는 참조값 시트) 

가끔 너무 많은 열이 있을 경우 일일이 헤아리기가 힘듭니다. 

그럴 경우 꿀팁으로 알려드리면 범위 지정 시 엑셀에서 친절하게 몇행 몇열이다를 알려주는 작은 창이 보입니다. 

 

 

152R X 4C는 152행 4열 이라는 뜻입니다

 

바로 위의 그림을 보게되면 152R X 4C 라고 나타납니다. 152Row X 4Column 입니다. 

이로부터 저희는 체력이라는 열은 포켓몬으로부터 네 번째에 위치한다는 걸 알 수 있습니다. 

 

=VLOOKUP($B2, '참조값 (2)'!$B$2:$I$152, 4, 일치상태) 

 

Vlookup의 세번째 조건까지 완성하였습니다. 

 

마지막은 일치상태입니다. 

유사일치(True)와 정확히 일치(False)가 있습니다. 

 

여담이지만 컴퓨터 언어에서 Boolean 자료형이 있습니다. True는 1로 False는 0으로 나타냅니다. 

엑셀에서도 동일하게 적용됩니다.

저희는 실무상 정확히 일치를 98% 이상 사용할 것이기 때문에 그냥 0으로 입력합니다. (False도 동일합니다.)

 

=VLOOKUP($B2,'참조값 (2)'!$B$2:$I$152,4,0)

=VLOOKUP($B2,'참조값 (2)'!$B$2:$I$152,4,False)

 

이제 코뿌리의 체력값을 불러올 수 있습니다 !!! 

 

코뿌리의 방어, 스피드, 특수 등 다른 속성만 불러올 수 있으면 다른 포켓몬에도 동일하게 수식이 적용될 것 같습니다. 

 

코뿌리의 공격

=VLOOKUP($B2,'참조값 (2)'!$B$2:$I$152,5,False)

 

코뿌리의 방어

=VLOOKUP($B2,'참조값 (2)'!$B$2:$I$152,6,False)

 

코뿌리의 스피드

=VLOOKUP($B2,'참조값 (2)'!$B$2:$I$152,7,False)

 

코뿌리의 특수

=VLOOKUP($B2,'참조값 (2)'!$B$2:$I$152,8,False)

 

참조해야할 열의 값만 바꿔주면 그에 맞는 데이터를 반환해줍니다. 

 

근데 열을 일일이 바꿔주는 건 또 너무 번거로우니 다음 번에는 match함수를 이용해 한 번에 열을 변경할 수 있는 함수에 대해 배워보겠습니다 ~~