엑셀함수 VLOOKUP 의 재발견

77_엑셀 초급 강좌 – 함수 중급 (4)

엑셀에서 IF다음으로 중요한 함수가 이 VLOOKUP 함수라고 생각합니다. 엑셀이 데이터베이스가 아님에도 데이터베이스 용도로 활용 할 수 있게 해주는 일등 공신이 이 함수 입니다 (사실 메모리는 SELECT 쿼리 보다 훨씬 많이 잡아 먹습니다. ).이 함수의 옵션에 대해서 확실히 하고 가기 위해 먼저 근사값을 기준으로 설명을 드리려 합니다. 우리가 세금을 낼 때 연봉 1억이상 연봉 5000만원 이상 다 세율이 다르게 적용 됩니다. 이때 수입에 대한 세율을 표로 만들어 자동으로 적용하기 위해선 VLOOKUP 함수의 근사값 옵션이 사용됩니다.

77연습파일.xlsx

77연습파일_완성본.xlsx

1. 문제 파악하기

   VLOOKUP 의 V는 VERTICAL의 약자 즉 수직으로 내려가면서 찾을 데이터가 정렬된 경우에 쓰입니다. 지금 예제 파일에서 세율이 세로로 정리 되어있기 때문에 HLOOKUP 이 아니라 VLOOPUP을 써야 합니다. (HLOOKUP 의 H는Horizontal즉 수평선, 가로 란 뜻입니다).

   지금 수입에 대한 세율이 표로 나와 있고 그 표를 기준으로 직원들의 세금을 계산 하려고 합니다.

   

2. VLOOKUP 함수 적용하기

   VLOOKUP 함수는 3가지의 인수를 최소한으로 하고 4번째 인수는 옵션값입니다. 지금 저희가 적용하려는 함수는 근사값이므로 4번째 옵션은 필요가 없습니다. 인수를 설명을 할 땐 항상 예제를 기준으로 설명을 하는게 이해가 쉬우므로 저희의 예제 파일을 기준으로 설명을 드리겠습니다.

   VLOOKUP ( 각자 수입, 수입에 대한 세율 표, 세율 표에서 세율의 열 번호, 옵션)

   함수를 타입 하기 시작하면 함수가 보입니다.

   

    

   이때 탭을 누르시면 함수의 나머지 부분이 입력되고 괄호가 열립니다.

    

   H2를 클릭하시고 쉼표, 그리고 세율이 적용된 테이블을 선택해 줍니다.

   

    

   여기서 테이블을 이렇게 적용 했다면 이 상태에서 F4를 눌러서 절대 참조를 해줘야 겠죠? 한번만 누르면 됩니다. 사실 열 참조를 하면 되지만 설명할 부분이 있어서 이렇게 일단 하게요.

    

   F4를 눌러서 절대 참조로 바꾸어 줍니다.

   

    

   쉼표를 누르고 세율이 몇 번째 열에 있죠? 네. 2번째 열에 있습니다. 그대로 괄호를 닫지 않고 엔터를 치시면 자동 괄호가 닫힙니다.

   

    

   세율이 적용되었음으로 채우기 핸들로 가서 내리지 말고 더블클릭을 해주시면 됩니다.

   

    

    

   세율이 적용 되었으므로 이제 백분율 표시만 해주시면 됩니다.

   

    

   백분율 표시를 눌러주시면 이제 세율이 바르게 표기 됩니다.

   

    

3. 표를 적용 해서 적용 하기

   예전에 테이블의 개념에 대해 설명 드린적이 있고 이후의 이점은 차후 차차 설명한다고 했습니다. 오늘 또 다른 이점을 설명 드리려고 합니다.

    

   제목행이 없는 경우 다음과 같이 범위를 지정해서 왼쪽 시트위에 있는 이름상자에 가서 이름을 정해주면 테이블이 됩니다.

   

    

   테이블이 적용 되면 그 시트에서 따로 행과 열 번호에 상관없이 독립적으로 위치를 정의 할 수 있습니다.

    

   또 테이블이 정의되면 이렇게 테이블 이름을 넣을 때 자동 완성 됩니다.

   

    

    

   테이블이름으로 함수를 구성하면 이점이 이렇게 함수를 알아 보기가 쉽게 됩니다. 직관적이죠.

   

    

    

   채우기 핸들 더블 클릭으로 수식을 복사 하였습니다.

   

    

    

   테이블로 정의를 하게 되면 절대 참조 상대 참조를 걱정할 필요가 없습니다. 또 함수 위치를 바꾸어도 수식이 깨지지 않습니다.

   

    

    

   이렇게 테이블이 자유롭게 이동이 가능 합니다.

   

    

   만약 내가 어떤 테이블을 만들었는지 몇 년이 지나 잘 모르겠다면 검색 창에 가시면 내가 정의한 모든 테이블을 볼 수 있습니다.

    

   이름상자 옆에 있는 화살표를 클릭하시면 지금 나의 시트에 세율 하나의 테이블이 있는걸 알 수 있습니다.

   

    

    

   이후에 테이블에 관한 이점이 생각 나는데로 계속 소개해 드리겠습니다. 같은 VLOOPUP 함수라도 $사인으로 복잡하게 함수를 만드는 것보다 테이블로 깔끔하게 만들면 간지나자나요.

    

4. VLOOPUP 함수의 필수 요건

   많은 사람들이 이 근사값 옵션을 사용할 때 지나치기 쉬운 사실이 있습니다. 근사값 옵션으로 원하는 값을 찾기 위해선 반드시 테이블이 오름차순으로 정리가 되있어야 합니다. 만약에 중간에 오름차순이 아닌 것이 하나라도 있거나 내림차순으로 정리되어 있다면 값을 얻기는 하지마 엉터리 값이 얻어집니다.

    

   만약 오름 차순 아닌 값이 하나라도 있다면 이렇게 세율이 엉터리로 얻어 집니다.

   

   VLOOKUP 함수는 엑셀의 IF문 만큼이나 활용도가 높으므로 반드시 숙달 되는 것이 엑셀의 고수가 되는 길 입니다. 또 참조할 영역을 지정할 때 절대 참조를 사용하는 것이 제일 폼이 안 나고 안좋은 방법이며 전체 열을 지정하거나 테이블을 지정하는 것이 훨씬 더 좋은 방법 입니다. 참조하는 영역의 행이 늘어나도 테이블이나 열은 영역 지정을 새로 해주실 필요가 없습니다.